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
Posts
