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
