Header Ads

  • Recent Posts

    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.

    Post Top Ad

    Post Bottom Ad