Peoplesoft - howto find the navigation to components using SQL

This record is used in setup manager - PS_PTLT_COMP_NAV  -  which defines Components Navigation. The field PTLT_TASK_CODE reflects component name, but there's ".GBL", or other Global extension like ".AUS". It doesn’t work for the transaction components like "PERSONAL_DATA".

For the transaction components, a platform-independent SQL snippet is found here

/* Provide navigation location for component */
SELECT a.portal_name,
       e.portal_label AS parent4_folder,
       d.portal_label AS parent3_folder,
       c.portal_label AS parent2_folder,
       b.portal_label AS parent_folder,
       a.portal_label AS component
FROM psprsmdefn a
LEFT JOIN psprsmdefn b ON b.portal_name = a.portal_name
AND b.portal_objname = a.portal_prntobjname
LEFT JOIN psprsmdefn c ON c.portal_name = b.portal_name
AND c.portal_objname = b.portal_prntobjname
LEFT JOIN psprsmdefn d ON d.portal_name = c.portal_name
AND d.portal_objname = c.portal_prntobjname
LEFT JOIN psprsmdefn e ON e.portal_name = d.portal_name
AND e.portal_objname = d.portal_prntobjname
WHERE a.portal_reftype = 'C'
  AND a.portal_uri_seg2 = 'ADM_APPL_MAINTNCE' -- Parameter to set:  (In this case I used the component - ADM_APPL_MAINTNCE)

Without too much effort, here I found another (my favorite) SQL:
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 = :1
  PRIOR portal_prntobjname = portal_objname

No comments: