Finding the Navigation of PeopleSoft Components - Part 1 - SQL Query to find navigation of PS Component

How to find online navigation of a PeopleSoft Component/Page?

After opening each PeopleSoft page in Application designer, we find the PeopleSoft components in which the page it is used by definition reference. From each PeopleSoft component, in Application Designer we have to again do a 'Find Definition Reference' and get the Menu names. For standard, most frequently used and delivered PeopleSoft Menus we know the navigations by virtue of our experience. However for least used Menus and custom menus it is extremely excruciating to find the menu navigation. For a person with less knowledge to the application finds it even more difficult to get to the navigations of PeopleSoft Pages/Components.

Thanks to my colleague Vijay who shared me the below queries after lot of googling. I am finding my job easy with both these SQL queries to find out navigations of custom PeopleSoft components. Thanks to the original authors of both these SQL queries.

Running the below SQL for any component gives the navigation for the corresponding PeopleSoft component.

SQL Query 1:
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 = 'NAME_OF_THE_PS_COMPONENT'
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

The above SQL gives navigations of most of the PeopleSoft components. However the query does not return all the PeopleSoft components. I could not spend time in figuring out why this query does not return navigations for few PS components. Thanks to the alternate SQL which I have, this is still stopping me from analyzing and fixing the issue in the above SQL.

SQL Query 2:
Below is the alternate SQL which is more robust in my opinion.
SELECT REPLACE(navigation,'',' > ') "PIA Navigation"
, url
, MENU_NAME
, COMPONENT_NAME
, portal_objname
, portal_prntobjname
, portal_uri_seg3
, portal_label
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 'NAME_OF_THE_PS_COMPONENT'
START WITH a.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY PRIOR a.portal_objname = a.portal_prntobjname)
WHERE navigation NOT LIKE '%Navigation Collections%'

7 comments:

  1. excellent.. this is very helpful :)

    ReplyDelete
  2. Excellent - Thanks So much

    ReplyDelete
  3. Hi ,

    Can you plz let me know what are the parameters I have to pass exactly for the 2nd query.

    Thanks,
    Priyanka

    ReplyDelete
  4. Hi,
    Replace 'NAME_OF_THE_PS_COMPONENT' in the qery with your required PeopleSoft component

    Shyam

    ReplyDelete
  5. Useful once again

    ReplyDelete
  6. Your first query worked great, and was easy enough to replicate in PSQuery. Thanks much!

    ReplyDelete
  7. Here is a version I wrote for HR 9.0
    SELECT E.PRCSTYPE, E.PRCSNAME, F.DESCR, DECODE( H.PORTAL_LABEL,' ','','','','Root','Root ','Root > ' || H.PORTAL_LABEL) || ' > ' || G.PORTAL_LABEL || ' > ' || C.PORTAL_LABEL || ' > ' || B.PORTAL_LABEL || ' > ' || A.PORTAL_LABEL || ' > ' || D.PORTAL_LABEL , D.DESCR254, E.PNLGRPNAME,D.PORTAL_NAME,D.PORTAL_REFTYPE,D.PORTAL_OBJNAME,F.PRCSTYPE,F.PRCSNAME
    FROM PSPRSMDEFN D, PS_PRCSDEFNPNL E, PS_PRCSDEFN F, PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN G, PSPRSMDEFN H
    WHERE E.PNLGRPNAME = D.PORTAL_URI_SEG2
    AND E.PRCSTYPE = F.PRCSTYPE
    AND E.PRCSNAME = F.PRCSNAME
    AND D.PORTAL_NAME = 'EMPLOYEE'
    AND D.PORTAL_REFTYPE = 'C'
    AND E.PNLGRPNAME > ' '
    AND A.PORTAL_NAME = 'EMPLOYEE'
    AND A.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME
    AND B.PORTAL_NAME = 'EMPLOYEE'
    AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME
    AND C.PORTAL_NAME (+) = 'EMPLOYEE'
    AND C.PORTAL_OBJNAME(+) = B.PORTAL_PRNTOBJNAME
    AND G.PORTAL_NAME(+) = 'EMPLOYEE'
    AND G.PORTAL_OBJNAME(+) = C.PORTAL_PRNTOBJNAME
    AND H.PORTAL_NAME(+) = 'EMPLOYEE'
    AND H.PORTAL_OBJNAME(+) = G.PORTAL_PRNTOBJNAME
    ORDER BY 1, 2, 4

    ReplyDelete

Please refrain for marketing messages and unnecessary back links.