Get PeopleSoft Navigation Lists (Component Based & Non Component Based)

-- SQL TO GET LIST OF ALL NAVIGATION MENU/COMPONENT EXCLUDING HIDDEN FOR ALL USER EXCEPT INCLUDED IN EXCEPTION LIST

 SELECT DISTINCT O.ROLEUSER,O.ROLENAME,R.CLASSID,
   A.PORTAL_NAME
  , A.PORTAL_OBJNAME
   , A.PORTAL_URI_SEG1 MENU_NAME
 , A.PORTAL_URI_SEG2 COMPONENT_NAME
 , A.PORTAL_URI_SEG3  MARKET
 , A.PORTAL_URI_SEG4
 , TRIM(((CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT( (CASE WHEN RTRIM(H.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(H.PORTAL_LABEL)
 ,' > ') ELSE ' ' END)
 , (CASE WHEN RTRIM(G.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(G.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(F.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(F.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(E.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(E.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(D.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(D.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(C.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(C.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(B.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(B.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(A.PORTAL_LABEL) <> 'Root' THEN ( (CASE WHEN A.PORTAL_CREF_USGT = 'LINK' THEN DECODE(RTRIM(A.PORTAL_LABEL)
 , ''
 , (  SELECT DISTINCT MAX(RTRIM(A1.PORTAL_LABEL))   FROM PSPRSMDEFN A1 WHERE A1.PORTAL_NAME = A.PORTAL_LINK_PORTAL
   AND A1.PORTAL_OBJNAME = A.PORTAL_LINKOBJNAME
   AND A1.PORTAL_NAME = A.PORTAL_NAME) , RTRIM(A.PORTAL_LABEL)) ELSE RTRIM(A.PORTAL_LABEL) END ) ) END) ) ))) AS NAVIGATION
  FROM
    (((((((PSPRSMDEFN A LEFT JOIN PSPRSMDEFN B ON B.PORTAL_NAME= A.PORTAL_NAME
   AND B.PORTAL_REFTYPE = 'F'
   AND B.PORTAL_OBJNAME= A.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN C ON C.PORTAL_NAME= B.PORTAL_NAME
   AND C.PORTAL_REFTYPE = 'F'
   AND C.PORTAL_OBJNAME= B.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN D ON D.PORTAL_NAME= C.PORTAL_NAME
   AND D.PORTAL_REFTYPE = 'F'
   AND D.PORTAL_OBJNAME= C.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN E ON E.PORTAL_NAME= D.PORTAL_NAME
   AND E.PORTAL_REFTYPE = 'F'
   AND E.PORTAL_OBJNAME= D.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN F ON F.PORTAL_NAME= E.PORTAL_NAME
   AND F.PORTAL_REFTYPE = 'F'
   AND F.PORTAL_OBJNAME= E.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN G ON G.PORTAL_NAME= F.PORTAL_NAME
   AND G.PORTAL_REFTYPE = 'F'
   AND G.PORTAL_OBJNAME= F.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN H ON H.PORTAL_NAME= G.PORTAL_NAME
   AND H.PORTAL_REFTYPE = 'F'
   AND H.PORTAL_OBJNAME= G.PORTAL_PRNTOBJNAME) ,PSROLECLASS R , PSAUTHITEM P,PSMENUITEM M, PSROLEUSER O
 WHERE  A.PORTAL_CREF_URLT NOT IN ('USCR','UGEN')
      AND NOT EXISTS (
 SELECT 'X'
  FROM PSPRSMSYSATTRVL ATTR
 WHERE ATTR.PORTAL_NAME = A.PORTAL_NAME
   AND PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
   AND ATTR.PORTAL_OBJNAME = A.PORTAL_OBJNAME)
   AND A.PORTAL_NAME = 'EMPLOYEE'
   AND R.CLASSID = P.CLASSID
   AND R.ROLENAME = O.ROLENAME
   AND P.MENUNAME = M.MENUNAME AND P.BARITEMNAME = M.ITEMNAME
AND M.MENUNAME = A.PORTAL_URI_SEG1
   AND M.PNLGRPNAME = A.PORTAL_URI_SEG2
   AND O.ROLEUSER  NOT IN ('000004'); --EXCLUDE SUPER/BATCH USER IDS


-- SQL TO GET LIST OF ALL NAVIGATION ISCRIPT AND OTHER (EXCEPT MENU/COMPONENT ) EXCLUDING HIDDEN FOR ALL USER EXCEPT INCLUDED IN EXCEPTION LIST

SELECT DISTINCT O.ROLEUSER,O.ROLENAME,R.CLASSID,
   A.PORTAL_NAME
  , A.PORTAL_OBJNAME
, A.PORTAL_URI_SEG1  RECORD_NAME
 , A.PORTAL_URI_SEG2  FIELD_NAME
 , A.PORTAL_URI_SEG3  PEOPLECODE_EVENT
, A.PORTAL_URI_SEG4  ISCRIPT_FUNCTION
 , TRIM(((CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT( (CASE WHEN RTRIM(H.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(H.PORTAL_LABEL)
 ,' > ') ELSE ' ' END)
 , (CASE WHEN RTRIM(G.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(G.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(F.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(F.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(E.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(E.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(D.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(D.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(C.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(C.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(B.PORTAL_LABEL) <> 'Root' THEN CONCAT(RTRIM(B.PORTAL_LABEL)
 ,' > ') ELSE ' ' END))
 , (CASE WHEN RTRIM(A.PORTAL_LABEL) <> 'Root' THEN ( (CASE WHEN A.PORTAL_CREF_USGT = 'LINK' THEN DECODE(RTRIM(A.PORTAL_LABEL)
 , ''
 , (  SELECT DISTINCT MAX(RTRIM(A1.PORTAL_LABEL))   FROM PSPRSMDEFN A1 WHERE A1.PORTAL_NAME = A.PORTAL_LINK_PORTAL
   AND A1.PORTAL_OBJNAME = A.PORTAL_LINKOBJNAME
   AND A1.PORTAL_NAME = A.PORTAL_NAME) , RTRIM(A.PORTAL_LABEL)) ELSE RTRIM(A.PORTAL_LABEL) END ) ) END) ) ))) AS NAVIGATION
  FROM PSPRSMPERM S, (((((((PSPRSMDEFN A LEFT JOIN PSPRSMDEFN B ON B.PORTAL_NAME= A.PORTAL_NAME
   AND B.PORTAL_REFTYPE = 'F'
   AND B.PORTAL_OBJNAME= A.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN C ON C.PORTAL_NAME= B.PORTAL_NAME
   AND C.PORTAL_REFTYPE = 'F'
   AND C.PORTAL_OBJNAME= B.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN D ON D.PORTAL_NAME= C.PORTAL_NAME
   AND D.PORTAL_REFTYPE = 'F'
   AND D.PORTAL_OBJNAME= C.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN E ON E.PORTAL_NAME= D.PORTAL_NAME
   AND E.PORTAL_REFTYPE = 'F'
   AND E.PORTAL_OBJNAME= D.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN F ON F.PORTAL_NAME= E.PORTAL_NAME
   AND F.PORTAL_REFTYPE = 'F'
   AND F.PORTAL_OBJNAME= E.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN G ON G.PORTAL_NAME= F.PORTAL_NAME
   AND G.PORTAL_REFTYPE = 'F'
   AND G.PORTAL_OBJNAME= F.PORTAL_PRNTOBJNAME) LEFT JOIN PSPRSMDEFN H ON H.PORTAL_NAME= G.PORTAL_NAME
   AND H.PORTAL_REFTYPE = 'F'
   AND H.PORTAL_OBJNAME= G.PORTAL_PRNTOBJNAME) ,PSROLECLASS R , PSROLEUSER O
 WHERE A.PORTAL_NAME = S.PORTAL_NAME
   AND A.PORTAL_REFTYPE = S.PORTAL_REFTYPE
   AND A.PORTAL_OBJNAME = S.PORTAL_OBJNAME
   AND A.PORTAL_CREF_URLT IN ('USCR','UGEN')
   AND ((S.PORTAL_PERMTYPE = 'P'
   AND R.CLASSID = S.PORTAL_PERMNAME)
    OR (S.PORTAL_PERMTYPE = 'R'
   AND R.ROLENAME = S.PORTAL_PERMNAME))
   AND NOT EXISTS (
 SELECT 'X'
  FROM PSPRSMSYSATTRVL ATTR
 WHERE ATTR.PORTAL_NAME = A.PORTAL_NAME
   AND PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
   AND ATTR.PORTAL_OBJNAME = A.PORTAL_OBJNAME)
   AND A.PORTAL_NAME = 'EMPLOYEE'
   AND R.ROLENAME = O.ROLENAME
   AND O.ROLEUSER NOT IN ('000004'); --EXCLUDE SUPER/BATCH USER IDS