PeopleSoft - Revisión de Permisos y Accesos

Yo sé que este query esta muuy largo... pero esta muy digerido. Te da por usuario, roles, listas de permisos, rutas en el portal (inglés y español), menú y componente al cuál se tiene acceso.


SELECT AD.ROLEUSER, AC.ROLENAME, AC.CLASSID, 
rtrim(ltrim(rtrim(ltrim(rtrim(ltrim(rtrim(ltrim(rtrim(ltrim(rtrim(ltrim(rtrim(ltrim( G.PORTAL_LABEL)) || ' > ' )) || rtrim(ltrim( F.PORTAL_LABEL)) || ' > ')) || rtrim(ltrim( E.PORTAL_LABEL )) || ' > ')) || rtrim(ltrim( D.PORTAL_LABEL)) || ' > ')) || rtrim(ltrim( C.PORTAL_LABEL)) || ' > ' )) || rtrim(ltrim( B.PORTAL_LABEL)) || ' > ')) || rtrim(ltrim( A.PORTAL_LABEL)) AS Ruta_Ingles, 
rtrim(ltrim(rtrim(ltrim(rtrim(ltrim(rtrim(ltrim(rtrim(ltrim(rtrim(ltrim(rtrim(ltrim(GL.PORTAL_LABEL)) || ' > ' )) || rtrim(ltrim(FL.PORTAL_LABEL)) || ' > ')) || rtrim(ltrim(EL.PORTAL_LABEL )) || ' > ')) || rtrim(ltrim(DL.PORTAL_LABEL)) || ' > ')) || rtrim(ltrim(CL.PORTAL_LABEL)) || ' > ' )) || rtrim(ltrim(BL.PORTAL_LABEL)) || ' > ')) || rtrim(ltrim(AL.PORTAL_LABEL)) AS Ruta_Español,
A.PORTAL_URI_SEG1 AS Menu, 
A.PORTAL_URI_SEG2 as Componente 
  FROM ((((((((
  (PSPRSMDEFN A LEFT OUTER JOIN PSPRSMDEFNLANG AL 
           ON A.PORTAL_NAME = AL.PORTAL_NAME 
                       AND A.PORTAL_REFTYPE = AL.PORTAL_REFTYPE 
  AND A.PORTAL_OBJNAME = AL.PORTAL_OBJNAME 
   ) LEFT OUTER JOIN  
   (PSPRSMDEFN B LEFT OUTER JOIN PSPRSMDEFNLANG BL 
        ON B.PORTAL_NAME = BL.PORTAL_NAME 
AND B.PORTAL_REFTYPE = BL.PORTAL_REFTYPE 
AND B.PORTAL_OBJNAME = BL.PORTAL_OBJNAME 
) ON  A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME AND (A.PORTAL_NAME = 'EMPLOYEE' AND A.PORTAL_NAME = B.PORTAL_NAME )
  ) LEFT OUTER JOIN 
  (  PSPRSMDEFN C LEFT OUTER JOIN PSPRSMDEFNLANG CL 
             ON C.PORTAL_NAME = CL.PORTAL_NAME 
AND C.PORTAL_REFTYPE = CL.PORTAL_REFTYPE 
AND C.PORTAL_OBJNAME = CL.PORTAL_OBJNAME 
) ON B.PORTAL_NAME = C.PORTAL_NAME 
AND B.PORTAL_REFTYPE = C.PORTAL_REFTYPE 
AND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME 
AND B.PORTAL_NAME = C.PORTAL_NAME
) LEFT OUTER JOIN 
              (PSPRSMDEFN D LEFT OUTER JOIN  PSPRSMDEFNLANG DL 
            ON D.PORTAL_NAME    = DL.PORTAL_NAME 
                              AND D.PORTAL_REFTYPE = DL.PORTAL_REFTYPE 
                              AND D.PORTAL_OBJNAME = DL.PORTAL_OBJNAME 
   ) ON  C.PORTAL_NAME = D.PORTAL_NAME 
    AND C.PORTAL_REFTYPE = D.PORTAL_REFTYPE 
    AND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME 
    AND C.PORTAL_NAME = D.PORTAL_NAME 
  ) LEFT OUTER JOIN 
   (PSPRSMDEFN E LEFT OUTER JOIN PSPRSMDEFNLANG EL 
            ON E.PORTAL_NAME = EL.PORTAL_NAME 
   AND E.PORTAL_REFTYPE = EL.PORTAL_REFTYPE 
   AND E.PORTAL_OBJNAME = EL.PORTAL_OBJNAME 
  ) ON D.PORTAL_NAME = E.PORTAL_NAME 
   AND D.PORTAL_REFTYPE = E.PORTAL_REFTYPE 
   AND D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME 
  ) LEFT OUTER JOIN 
    (  PSPRSMDEFN F LEFT OUTER JOIN PSPRSMDEFNLANG FL 
               ON F.PORTAL_NAME    = FL.PORTAL_NAME 
  AND F.PORTAL_REFTYPE = FL.PORTAL_REFTYPE 
  AND F.PORTAL_OBJNAME = FL.PORTAL_OBJNAME 
    ) ON  E.PORTAL_NAME = F.PORTAL_NAME 
     AND E.PORTAL_REFTYPE = F.PORTAL_REFTYPE 
  AND E.PORTAL_PRNTOBJNAME = F.PORTAL_OBJNAME 
  ) LEFT OUTER JOIN 
    (PSPRSMDEFN G LEFT OUTER JOIN PSPRSMDEFNLANG GL 
             ON G.PORTAL_NAME = GL.PORTAL_NAME 
                              AND G.PORTAL_REFTYPE = GL.PORTAL_REFTYPE 
                              AND G.PORTAL_OBJNAME = GL.PORTAL_OBJNAME 
   ) ON F.PORTAL_NAME = G.PORTAL_NAME 
    AND F.PORTAL_REFTYPE = G.PORTAL_REFTYPE 
    AND F.PORTAL_PRNTOBJNAME = G.PORTAL_OBJNAME 
  ) LEFT OUTER JOIN 
    (PSPRSMDEFN H LEFT OUTER JOIN PSPRSMDEFNLANG HL 
             ON H.PORTAL_NAME = HL.PORTAL_NAME 
    AND H.PORTAL_REFTYPE = HL.PORTAL_REFTYPE 
    AND H.PORTAL_OBJNAME = HL.PORTAL_OBJNAME 
   ) ON  G.PORTAL_NAME = H.PORTAL_NAME 
    AND G.PORTAL_REFTYPE = H.PORTAL_REFTYPE 
    AND G.PORTAL_PRNTOBJNAME = H.PORTAL_OBJNAME 
  ) LEFT OUTER JOIN 
    (PSPRSMDEFN I LEFT OUTER JOIN PSPRSMDEFNLANG IL 
             ON I.PORTAL_NAME    = IL.PORTAL_NAME 
    AND I.PORTAL_REFTYPE = IL.PORTAL_REFTYPE 
                              AND I.PORTAL_OBJNAME = IL.PORTAL_OBJNAME 
  ) ON  H.PORTAL_NAME = I.PORTAL_NAME 
   AND H.PORTAL_REFTYPE = I.PORTAL_REFTYPE 
   AND H.PORTAL_PRNTOBJNAME = I.PORTAL_OBJNAME 
), 
 PSPRSMPERM AB, 
 PSROLECLASS AC, 
 PSROLEUSER AD 
  where  B.PORTAL_REFTYPE  = 'F' 
     AND A.PORTAL_NAME     = AB.PORTAL_NAME 
     AND A.PORTAL_REFTYPE  = AB.PORTAL_REFTYPE 
     AND A.PORTAL_OBJNAME = AB.PORTAL_OBJNAME 
AND AC.CLASSID = AB.PORTAL_PERMNAME 
AND AC.ROLENAME = AD.ROLENAME 
AND A.PORTAL_URI_SEG2 <> ' ' 
 AND AD.ROLEUSER = 'VP1'
 order by 1,4,2,3

Leer una página WEB desde VB

Esto puede ser muy útil para quitar publicidad y esas cosas que no queremos en nuestras páginas... o hasta incluso .. meter publicidad si queremos (me imagino por ejemplo algún cyber.. o lo que se les ocurra)

' Declaraciones a nivel público

Public Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Public Declare Function InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal sURL As String, ByVal sHeaders As String, ByVal lHeadersLength As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long

Public Declare Function InternetReadFile Lib "wininet.dll" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer

Public Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer

'Nuestras constantes
Public Const IF_FROM_CACHE = &H1000000
Public Const IF_MAKE_PERSISTENT = &H2000000
Public Const IF_NO_CACHE_WRITE = &H4000000
Public Const BUFFER_LEN = 256

' Nuestra función de lectura

Public Function LeeURL(sURL As String) As String

Dim sBuffer As String * BUFFER_LEN, iResult As Integer, sData As String

Dim hInternet As Long, hSession As Long, lReturn As Long

'obtener el identificador de la conexión a Internet actual

hSession = InternetOpen("vb wininet", 1, vbNullString, vbNullString, 0)

'obtener el identificador de la url

If hSession Then hInternet = InternetOpenUrl(hSession, sURL, vbNullString, 0, IF_NO_CACHE_WRITE, 0)

'si tenemos el identificador, a continuación, iniciar la lectura de la página web

If hInternet Then

    'obtenemos el primer bloque y lo vamos guardando.

    iResult = InternetReadFile(hInternet, sBuffer, BUFFER_LEN, lReturn)

    sData = sBuffer

    'mientras haya más datos los seguimos guardando

    Do While lReturn <> 0

        iResult = InternetReadFile(hInternet, sBuffer, BUFFER_LEN, lReturn)

        sData = sData + Mid(sBuffer, 1, lReturn)

        DoEvents

    Loop

End If

'cerramos la conexión

iResult = InternetCloseHandle(hInternet)

LeeURL = sData

End Function

' Ahora solo la llamamos y podemos guardar en una variable de texto, de ahí la podemos mandar a nuestro objeto de IExplorer o hacer lo que queramos con ello

....

Url = "www.google.com"

CodigoPaginaHTML = LeeURL(Url)

....