Tech Talk

LIGHTLY TECHNICAL OBSERVATIONS ON PEOPLESOFT, SQR, AND RELATED AND UNRELATED TOPICS

Archives

Sameer Khanna was reading my recent post, Finding Objects on the Menu, in which I described a simple way of working up the structure tree of PeopleSoft objects by using Find Object References repeatedly to find the corresponding menu navigation. Sameer prefers another way: using SQL. His method starts with an object name and finds the path using PeopleTools tables.

One big advantage of the SQL method is that it can be used when starting from objects that are not managed by App Designer, such as process definitions. Another significant advantage is that it reveals the entire menu navigation path starting at Home, while on occasion you might open a menu object in App Designer and still not know how to find it from the top level. However, each object type requires different SQL (although they follow a general pattern). Three of them are shown below—but these should cover the most common situations. In any case, using whichever method you prefer should help you to find that unknown spot on the menu.

Here ("courtesy my technical lead," Sameer says) is some sample SQL. I modified these somewhat (and hopefully didn't break them!). The main difference is that Sameer's version reported only menu items that could be reached by someone, based on security, while the version shown here tries to report every path to a menu item, disregarding security. I thought that developers might be interested in all of these paths—but you might go through PIA and not see some of these menu items, depending on your security setup. If you want to add security to this SQL, join PSAUTHITEM (authorized menu items).

When a process name is known:

SELECT DISTINCT
       PRCS.PRCSTYPE
     , PRCS.PRCSNAME
     , PRCS.DESCR
     , PAGE.PNLGRPNAME as Component
     , 'Home > '
           || RTRIM(MENU.MENUGROUP)  || ' > '
           || RTRIM(MENU.MENULABEL)  || ' > '
           || RTRIM(ITEM.BARLABEL)   || ' > '
           || ITEM.ITEMLABEL as MenuPath
FROM   PSMENUDEFN     MENU
     , PSMENUITEM     ITEM
     , PS_PRCSDEFNPNL PAGE
     , PS_PRCSDEFN    PRCS
WHERE  MENU.MENUNAME    = ITEM.MENUNAME
AND    ITEM.PNLGRPNAME  = PAGE.PNLGRPNAME
AND    PAGE.PRCSTYPE    = PRCS.PRCSTYPE
AND    PAGE.PRCSNAME    = PRCS.PRCSNAME
AND    PRCS.PRCSNAME    = 'process-name'

When a record name is known:

SELECT DISTINCT
       PFLD.RECNAME
     , PFLD.PNLNAME as Page
     , 'Home > '
           || RTRIM(MENU.MENUGROUP)  || ' > '
           || RTRIM(MENU.MENULABEL)  || ' > '
           || RTRIM(ITEM.BARLABEL)   || ' > '
           || ITEM.ITEMLABEL as MenuPath
FROM   PSMENUDEFN     MENU
     , PSMENUITEM     ITEM
     , PSPNLGROUP     COMP
     , PSPNLFIELD     PFLD
WHERE  MENU.MENUNAME    = ITEM.MENUNAME
AND    ITEM.PNLGRPNAME  = COMP.PNLGRPNAME
AND    COMP.PNLNAME     = PFLD.PNLNAME
AND    PFLD.RECNAME     = 'record-name'

When a page name is known:

SELECT DISTINCT
       COMP.PNLNAME as Page
     , 'Home > '
           || RTRIM(MENU.MENUGROUP)  || ' > '
           || RTRIM(MENU.MENULABEL)  || ' > '
           || RTRIM(ITEM.BARLABEL)   || ' > '
           || ITEM.ITEMLABEL as MenuPath
FROM   PSMENUDEFN     MENU
     , PSMENUITEM     ITEM
     , PSPNLGROUP     COMP
WHERE  MENU.MENUNAME    = ITEM.MENUNAME
AND    ITEM.PNLGRPNAME  = COMP.PNLGRPNAME
AND    COMP.PNLNAME     = 'page-name'

This SQL should work at PeopleTools release 8.1x and should be close to what's needed at other releases. The examples given here use Oracle syntax. If you are using Microsoft SQL Server, replace the || with + . As always, test and modify as needed. Thanks, Sameer!