Query to identify navigation PeopleSoft Process or Job

Developers while making changes to the already existing custom batch programs will be interested in running and testing the processes/jobs online. Clients might have created custom run control pages for these batch programs and registered in the required navigation as per business need. In this doodle I have posted the SQL query that will provide the run control page navigations for running a process or job. To use this query you should be aware of the exact process or job name.

Query for identify the navigation of the process:

SELECT LEVEL0.PORTAL_LABEL || ' > ' || LEVEL1.PORTAL_LABEL || ' > ' || LEVEL2.PORTAL_LABEL || ' > ' || level3.PORTAL_LABEL PATH_TO_COMPONENT

FROM PSPRSMDEFN level3

, PSPRSMDEFN level2

, PSPRSMDEFN level1

, PSPRSMDEFN LEVEL0

WHERE level3.PORTAL_URI_SEG2 IN (SELECT PNLGRPNAME FROM PS_PRCSDEFNPNL WHERE  PRCSNAME = 'Your Process Name')

AND level3.PORTAL_PRNTOBJNAME = level2.PORTAL_OBJNAME

AND level2.PORTAL_PRNTOBJNAME = level1.PORTAL_OBJNAME

AND level1.PORTAL_PRNTOBJNAME = LEVEL0.PORTAL_OBJNAME

AND level3.PORTAL_NAME = level2.PORTAL_NAME

AND level2.PORTAL_NAME = level1.PORTAL_NAME

AND level1.PORTAL_NAME = LEVEL0.PORTAL_NAME;

 

Query for identify the navigation of the job:

 

SELECT LEVEL0.PORTAL_LABEL || ' > ' || LEVEL1.PORTAL_LABEL || ' > ' || LEVEL2.PORTAL_LABEL || ' > ' || level3.PORTAL_LABEL PATH_TO_COMPONENT, LEVEL3.*

FROM PSPRSMDEFN level3

, PSPRSMDEFN level2

, PSPRSMDEFN level1

, PSPRSMDEFN LEVEL0

WHERE level3.PORTAL_URI_SEG2 IN (SELECT PNLGRPNAME FROM PS_PRCSJOBPNL WHERE  PRCSJOBNAME = 'Your Job Name')

AND level3.PORTAL_PRNTOBJNAME = level2.PORTAL_OBJNAME

AND level2.PORTAL_PRNTOBJNAME = level1.PORTAL_OBJNAME

AND level1.PORTAL_PRNTOBJNAME = LEVEL0.PORTAL_OBJNAME

AND level3.PORTAL_NAME = level2.PORTAL_NAME

AND level2.PORTAL_NAME = level1.PORTAL_NAME

AND level1.PORTAL_NAME = LEVEL0.PORTAL_NAME;