PeopleSoft Learning

Identify navigation in PeopleSoft by a Process name and/or Job name

There are many SQL statements in different blogs to identify the navigation for a process.
But I have modified that to get the Navigation for a Job.
Single SQL works for both Process and Job.


SELECT DISTINCT RTRIM( REVERSE ( SYS_CONNECT_BY_PATH(REVERSE (portal_label), '>-')) ,' >- ') PATH
      FROM PSPRSMDEFN
     WHERE portal_name = 'EMPLOYEE'
       AND portal_prntobjname = 'PORTAL_ROOT_OBJECT'
START WITH portal_uri_seg2 IN (
                       SELECT DISTINCT pnlgrpname
                       FROM ps_prcsdefnpnl
                       WHERE prcsname = UPPER(:1)
                       UNION
                       Select Distinct PNLGRPNAME
                       FROM PS_PRCSJOBPNL
                       Where PRCSJOBNAME = UPPER(:1) )
CONNECT BY PRIOR portal_prntobjname = portal_objname;


Hope this will be useful.