Query to get pages based on user roles

get_portal_paths.sql

SELECT DISTINCT Y.MENUNAME
,Y.COMPONENTNAME
,Y.MARKET
,Y.PORTAL_LABEL AS LABEL
,Y.PORTAL_OBJNAME
,Y.PORTAL_PRNTOBJNAME
,Y.PORTAL_CNTPRV_NAM
,Y.VERSION
,Y.PORTAL_URL_CHKSUM
,TO_CHAR(E.PORTAL_NAVPATH) AS PATHNAME
,TO_CHAR(Y.PORTAL_URLTEXT)
FROM (
      SELECT X.*
      ,D.PORTAL_OBJNAME
      ,D.PORTAL_PRNTOBJNAME
      ,D.PORTAL_NAME
      ,D.PORTAL_LABEL
      ,D.PORTAL_URLTEXT
      ,D.PORTAL_CNTPRV_NAM
      ,D.VERSION
      ,D.PORTAL_URL_CHKSUM
      FROM (
            SELECT DISTINCT A.ROLENAME
            ,B.MENUNAME
            ,C.PNLGRPNAME AS COMPONENTNAME
            ,C.MARKET
            FROM PSROLECLASS A
            ,PSAUTHITEM B
            ,PSMENUITEM C
            WHERE A.ROLENAME IN ('PeopleSoft User','[role name 2]')
            AND A.CLASSID = B.CLASSID 
            AND B.MENUNAME = C.MENUNAME
            AND B.BARNAME = C.BARNAME 
            AND B.BARITEMNAME = C.ITEMNAME
            ) X
       LEFT OUTER JOIN PSPRSMDEFN D ON X.MENUNAME = D.PORTAL_URI_SEG1
                                   AND X.COMPONENTNAME = D.PORTAL_URI_SEG2 
                                   AND X.MARKET = D.PORTAL_URI_SEG3
                                   AND D.PORTAL_NAME = 'EMPLOYEE' 
                                   AND D.PORTAL_LINKOBJNAME = ' ' ) Y
   LEFT OUTER JOIN PSPRSMNAVINFO E ON Y.PORTAL_NAME = E.PORTAL_NAME 
                                 AND Y.PORTAL_OBJNAME = E.PORTAL_OBJNAME
WHERE E.PORTAL_NAVPATH IS NOT NULL
/