PeopleSoft Navigation Path SQL Generator (PSPRSMDEFN)

-- Walk from each content reference for the component up to the root folder
SELECT CREF_NAME,
       MENU_NAME,
       LISTAGG(PORTAL_LABEL, ' > ') WITHIN GROUP (ORDER BY LVL DESC) AS NAV_PATH
FROM (
  SELECT PORTAL_LABEL,
         LEVEL AS LVL,
         CONNECT_BY_ROOT PORTAL_OBJNAME AS CREF_NAME,
         CONNECT_BY_ROOT PORTAL_URI_SEG1 AS MENU_NAME
  FROM PSPRSMDEFN
  START WITH PORTAL_NAME = 'EMPLOYEE'
         AND PORTAL_REFTYPE = 'C'
         AND PORTAL_URI_SEG2 = 'JOB_DATA'
  CONNECT BY PORTAL_OBJNAME = PRIOR PORTAL_PRNTOBJNAME
         AND PORTAL_NAME = PRIOR PORTAL_NAME
)
GROUP BY CREF_NAME, MENU_NAME
ORDER BY CREF_NAME;

SELECT LISTAGG(PORTAL_LABEL, ' > ') WITHIN GROUP (ORDER BY LVL DESC) AS NAV_PATH
FROM (
  SELECT PORTAL_LABEL, LEVEL AS LVL
  FROM PSPRSMDEFN
  START WITH PORTAL_NAME = 'EMPLOYEE'
         AND PORTAL_OBJNAME = 'HC_JOB_DATA_GBL'
  CONNECT BY PORTAL_OBJNAME = PRIOR PORTAL_PRNTOBJNAME
         AND PORTAL_NAME = PRIOR PORTAL_NAME
);

WITH NAV (CREF_NAME, PORTAL_NAME, PORTAL_OBJNAME, PORTAL_PRNTOBJNAME, NAV_PATH) AS (
  SELECT PORTAL_OBJNAME, PORTAL_NAME, PORTAL_OBJNAME, PORTAL_PRNTOBJNAME,
         CAST(PORTAL_LABEL AS NVARCHAR(4000))
  FROM PSPRSMDEFN
  WHERE PORTAL_NAME = 'EMPLOYEE'
    AND PORTAL_REFTYPE = 'C'
    AND PORTAL_URI_SEG2 = 'JOB_DATA'
  UNION ALL
  SELECT N.CREF_NAME, P.PORTAL_NAME, P.PORTAL_OBJNAME, P.PORTAL_PRNTOBJNAME,
         CAST(P.PORTAL_LABEL + N' > ' + N.NAV_PATH AS NVARCHAR(4000))
  FROM PSPRSMDEFN P
  JOIN NAV N
    ON P.PORTAL_OBJNAME = N.PORTAL_PRNTOBJNAME
   AND P.PORTAL_NAME = N.PORTAL_NAME
)
SELECT CREF_NAME, NAV_PATH
FROM NAV
WHERE PORTAL_OBJNAME = 'PORTAL_ROOT_OBJECT'
ORDER BY CREF_NAME;

SELECT DISTINCT RC.ROLENAME,
       A.CLASSID,
       A.MENUNAME,
       A.BARITEMNAME AS COMPONENT,
       A.PNLITEMNAME AS PAGE,
       A.DISPLAYONLY,
       A.AUTHORIZEDACTIONS
FROM PSAUTHITEM A
JOIN PSROLECLASS RC
  ON RC.CLASSID = A.CLASSID
WHERE A.MENUNAME = 'ADMINISTER_WORKFORCE_(GBL)'
  AND A.BARITEMNAME = 'JOB_DATA'
ORDER BY RC.ROLENAME, A.CLASSID, A.PNLITEMNAME;

SELECT DISTINCT RU.ROLEUSER,
       RU.ROLENAME,
       A.CLASSID,
       A.PNLITEMNAME AS PAGE,
       A.DISPLAYONLY
FROM PSAUTHITEM A
JOIN PSROLECLASS RC
  ON RC.CLASSID = A.CLASSID
JOIN PSROLEUSER RU
  ON RU.ROLENAME = RC.ROLENAME
WHERE A.MENUNAME = 'ADMINISTER_WORKFORCE_(GBL)'
  AND A.BARITEMNAME = 'JOB_DATA'
  AND RU.ROLEUSER = 'PS'
ORDER BY RU.ROLENAME, A.CLASSID;