Determine Portal Navigation for all custom processes

The below SQL lists the complete portal navigation along with the process name. I built this SQL when we had to provide this list to our client to review processes that could be retired. The output is useful for generating an inventory or assisting in KEEP/DROP decisions during upgrade.

Here it is:

SELECT NAVIGATION, A1.PRCSNAME

FROM

(SELECT LPAD(‘–’,2*(LEVEL-1)) || PORTAL_LABEL “NAVIGATION”, PORTAL_URI_SEG2 FROM (SELECT PORTAL_LABEL, PORTAL_PRNTOBJNAME, PORTAL_OBJNAME, PORTAL_URI_SEG2 FROM PSPRSMDEFN A

WHERE PORTAL_NAME = ‘EMPLOYEE’ ) B

WHERE B.PORTAL_PRNTOBJNAME != ‘ ‘

START WITH (B.PORTAL_URI_SEG2 IN

(SELECT D.PNLGRPNAME

FROM PSMENUITEM A, PSMENUDEFN B, PS_PRCSDEFNPNL C, PSPNLGROUP D

WHERE A.MENUNAME=B.MENUNAME

AND A.PNLGRPNAME = C.PNLGRPNAME

AND A.PNLGRPNAME = D.PNLGRPNAME

AND C.PRCSNAME IN

(SELECT PRCSNAME FROM PS_PRCSDEFN

WHERE LASTUPDOPRID !=

 Read More About  Determine Portal Navigation