Peoplesoft Menu Listing or Search

SQL for Menu Searching

Here is a very handy way to either export a full listing of the PeopleSoft Menu or search for a specific menu item.

----  Menu Look-up
SELECT SRCH.PORTAL_LABEL LABEL
   , NVL(SRCH.PORTAL_URI_SEG2, ' ') COMPNOENT
   , SRCH.PORTAL_OBJNAME
  --L1.PORTAL_LABEL,  L2.PORTAL_LABEL,  L3.PORTAL_LABEL,  
  --L4.PORTAL_LABEL,  L5.PORTAL_LABEL,  L6.PORTAL_LABEL
  ,'Main Menu > ' || L1.PORTAL_LABEL || decode(L1.PORTAL_REFTYPE, 'F', ' > ')
  || L2.PORTAL_LABEL  || DECODE(L2.PORTAL_REFTYPE, 'F', ' > ')
  || L3.PORTAL_LABEL  || DECODE(L3.PORTAL_REFTYPE, 'F', ' > ')
  || L4.PORTAL_LABEL  || DECODE(L4.PORTAL_REFTYPE, 'F', ' > ')
  || L5.PORTAL_LABEL  || DECODE(L5.PORTAL_REFTYPE, 'F', ' > ')
  || L6.PORTAL_LABEL NAV_PATH,
  decode(SRCH.PORTAL_REFTYPE, 'F', ' ', 'C', SRCH.DESCR254) DESCR
FROM PSPRSMDEFN L1
  LEFT JOIN PSPRSMDEFN L2
  ON L2.PORTAL_NAME         =L1.PORTAL_NAME
  AND L2.PORTAL_PRNTOBJNAME = L1.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L3
  ON L3.PORTAL_NAME         =L2.PORTAL_NAME
  AND L3.PORTAL_PRNTOBJNAME = L2.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L4
  ON L4.PORTAL_NAME         =L3.PORTAL_NAME
  AND L4.PORTAL_PRNTOBJNAME = L3.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L5
  ON L5.PORTAL_NAME         =L4.PORTAL_NAME
  AND L5.PORTAL_PRNTOBJNAME = L4.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L6
  ON L6.PORTAL_NAME         =L5.PORTAL_NAME
  AND L6.PORTAL_PRNTOBJNAME = L5.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN SRCH
  ON SRCH.PORTAL_NAME       = 'EMPLOYEE'
  AND SRCH.PORTAL_OBJNAME   
    = NVL(L6.PORTAL_OBJNAME,NVL(L5.PORTAL_OBJNAME,NVL(L4.PORTAL_OBJNAME, 
           NVL(L3.PORTAL_OBJNAME,(NVL(L2.PORTAL_OBJNAME,NVL(L1.PORTAL_OBJNAME,' ')))))))
WHERE L1.PORTAL_NAME ='EMPLOYEE' AND L1.PORTAL_PRNTOBJNAME = 'PORTAL_ROOT_OBJECT'
--*************************************************************************************--
--  Search for Items
--*************************************************************************************--
  --AND SRCH.PORTAL_URI_SEG2    = 'MCM_ADV_SUMMARY'     -- Component
  --AND SRCH.PORTAL_LABEL       LIKE 'PeopleTools%'     -- Label
  --AND L1.PORTAL_OBJNAME       = 'PT_PEOPLETOOLS'      -- Folder/Ref Object Name
ORDER BY L1.PORTAL_REFTYPE DESC,  L1.PORTAL_LABEL,
  L2.PORTAL_REFTYPE DESC,  L2.PORTAL_LABEL,  L3.PORTAL_REFTYPE DESC,  L3.PORTAL_LABEL,
  L4.PORTAL_REFTYPE DESC,  L4.PORTAL_LABEL,  L5.PORTAL_REFTYPE DESC,  L5.PORTAL_LABEL,
  L6.PORTAL_REFTYPE DESC,  L6.PORTAL_LABEL ;