Thursday, July 30, 2015

SQL to find the Navigation of a PeopleSoft Process

SQL to find the navigation to the run control page by Process Name in PeopleSoft.

SELECT E.PRCSTYPE, E.PRCSNAME, F.DESCR, DECODE( H.PORTAL_LABEL,' ','','','','Root','Root ','Root > ' || H.PORTAL_LABEL) || ' > ' || G.PORTAL_LABEL || ' > ' || C.PORTAL_LABEL || ' > ' || B.PORTAL_LABEL || ' > ' || A.PORTAL_LABEL || ' > ' || D.PORTAL_LABEL , D.DESCR254, E.PNLGRPNAME,D.PORTAL_NAME,D.PORTAL_REFTYPE,D.PORTAL_OBJNAME,F.PRCSTYPE,F.PRCSNAME
FROM PSPRSMDEFN D, PS_PRCSDEFNPNL E, PS_PRCSDEFN F, PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN G, PSPRSMDEFN H
WHERE E.PNLGRPNAME = D.PORTAL_URI_SEG2
AND E.PRCSTYPE = F.PRCSTYPE
AND E.PRCSNAME = F.PRCSNAME
AND D.PORTAL_NAME = 'EMPLOYEE'
AND D.PORTAL_REFTYPE = 'C'
AND E.PNLGRPNAME > ' '
AND A.PORTAL_NAME = 'EMPLOYEE'
AND A.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME
AND B.PORTAL_NAME = 'EMPLOYEE'
AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME
AND C.PORTAL_NAME (+) = 'EMPLOYEE'
AND C.PORTAL_OBJNAME(+) = B.PORTAL_PRNTOBJNAME
AND G.PORTAL_NAME(+) = 'EMPLOYEE'
AND G.PORTAL_OBJNAME(+) = C.PORTAL_PRNTOBJNAME
AND H.PORTAL_NAME(+) = 'EMPLOYEE'
AND H.PORTAL_OBJNAME(+) = G.PORTAL_PRNTOBJNAME
AND E.PRCSNAME ='Process Name'
ORDER BY 1, 2, 4

No comments:

Post a Comment