Finding Menu navigation in Peoplesoft

Menu Navigation Using SQL

 

1. 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'

 

2. 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'

 

3) 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'

 

4) When particular field of a particular record is known

 

SELECT DISTINCT

       PFLD.RECNAME

     , PFLD.FIELDNAME

     , 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'

AND    PFLD.FIELDNAME   = 'field-name'

 

 

Article posted by

Ganesh A.M

 

 

 

0 comments: