SQL to Retrieve Navigation of a Process in PeopleSoft

Following SQL is used to find out all Processes Defined in PeopleSoft System. Additionally this query also provides Components attached with Process Definition and Navigation from where this process can be executed. This query is tested on Oracle platform.

Select S.prcsname,S.prcstype, S.pnlgrpname,N.navigation From (Select A.prcsname, A.prcstype,B.pnlgrpname
From PS_PRCSDEFN A, PS_PRCSDEFNPNL B
Where A.prcsname = B.prcsname) S LEFT OUTER JOIN (SELECT REPLACE(navigation,”,’ > ‘) as navigation
,COMPONENT_NAME
FROM (SELECT SYS_CONNECT_BY_PATH (a.portal_label,’>>’) navigation
, ‘/EMPLOYEE/ERP/c/’ || a.portal_uri_seg1 || ‘.’ || a.portal_uri_seg2 || ‘.’ || a.portal_uri_seg3 url
,  a.portal_uri_seg1  MENU_NAME
, a.portal_uri_seg2  COMPONENT_NAME
, a.portal_objname   portal_objname
, a.portal_prntobjname  portal_prntobjname
, a.portal_uri_seg3 portal_uri_seg3
, a.portal_reftype portal_reftype
,a.portal_label portal_label
FROM (SELECT DISTINCT a.portal_name
, a.portal_label
, a.portal_objname
, a.portal_prntobjname
, a.portal_uri_seg1
, a.portal_uri_seg2
, a.portal_uri_seg3
, a.portal_reftype
FROM psprsmdefn a
WHERE portal_name = ‘EMPLOYEE’
AND portal_objname <> portal_prntobjname
AND NOT EXISTS (SELECT ‘X’
FROM psprsmsysattrvl
WHERE portal_name = a.portal_name
AND portal_reftype = a.portal_reftype
AND portal_objname = a.portal_objname
AND portal_attr_nam = ‘PORTAL_HIDE_FROM_NAV’
AND a.portal_objname NOT IN (‘CO_NAVIGATION_COLLECTIONS’,’PORTAL_BASE_DATA’))) a
WHERE portal_uri_seg2 LIKE ‘%%’
START WITH a.portal_prntobjname = ‘PORTAL_ROOT_OBJECT’
CONNECT BY PRIOR a.portal_objname = a.portal_prntobjname)
WHERE navigation NOT LIKE ‘%Navigation Collections%’) N
ON (N.COMPONENT_NAME = S.pnlgrpname) order by S.prcsname;

If you want to list out Processes for any specific component then replace following part of SQL with name of component.

WHERE portal_uri_seg2 LIKE ‘%%’ —–>   WHERE portal_uri_seg2 LIKE ‘%COMP_NAME%’

From PeopleSoft v9 onwards, one can easily find out navigation of component from PIA itself. One can also use above SQL – marked in BLUE to find out the same. Just Provide Component name by replacing part of SQL as mentioned above.

If there is no Component associated with Process Definition, then above SQL will not retrieve that Process.

Following SQL can be used to find out Processes with no component attached in Process Definition.

Select PRCSNAME, prcstype From PS_PRCSDEFN WHERE PRCSNAME NOT IN (Select PRCSNAME From PS_PRCSDEFNPNL);