Navigation access in Portal for permission lists/operators in PeopleTools >= 8.40

—Oracle Sql
—The Peoplesoft Navigation menus, PS menus and components a specific user can access
SELECT DISTINCT Dev.PATH MENU_PATH, Dev.portal_objname PORTAL_OBJECT, Dev.PORTAL_URI_SEG2 PS_COMPONENT, Dev.PORTAL_URI_SEG1 PS_MENU, a.CLASSID PERMISSION_LIST,
DECODE(a.DISPLAYONLY,1,’TRUE’,0,’FALSE’) DISPLAYONLY,
(CASE
WHEN a.AUTHORIZEDACTIONS=1 THEN ‘ADD’
WHEN a.AUTHORIZEDACTIONS=2 THEN ‘UPDATE,DISPLAY’
WHEN a.AUTHORIZEDACTIONS=3 THEN ‘ADD,UPDATE, DISPLAY’
WHEN a.AUTHORIZEDACTIONS=4 THEN ‘UPDATE/DISPLAY ONLY’
WHEN a.AUTHORIZEDACTIONS=5 THEN ‘ADD,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=6 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=7 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=8 THEN ‘CORRECTION’
WHEN a.AUTHORIZEDACTIONS=9 THEN ‘ADD,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=10 THEN ‘UPDATE,DISPLAY,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=11 THEN ‘ADD, UPDATE/DISPLAY ALL, CORRECTION’
WHEN a.AUTHORIZEDACTIONS=12 THEN ‘UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=13 THEN ‘ADD, UPDATE, DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=14 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=15 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=128 THEN ‘DATA ENTRY’
ELSE ‘UNKNOWN’
END ) AUTHORIZEDACTIONS
FROM (select LTRIM(SYS_CONNECT_BY_PATH (PORTAL_LABEL, ‘>’),’>’) PATH, portal_objname,PORTAL_URI_SEG1,PORTAL_URI_SEG2 from psprsmdefn where level >1
connect by nocycle prior portal_objname= portal_prntobjname
start with portal_objname= ‘PORTAL_ROOT_OBJECT’ order by level ) Dev
inner join PSMENUITEM b ON Dev.PORTAL_URI_SEG2=b.PNLGRPNAME AND Dev.PORTAL_URI_SEG1 =b.MENUNAME
inner join PSAUTHITEM a ON a.menuname = b.menuname AND a.baritemname = b.itemname
inner join PSOPRCLS p ON a.CLASSID=p.OPRCLASS
where p.OPRID=’YourOPRID’
and rtrim(ltrim(Dev.PORTAL_URI_SEG2)) is NOT NULL and rtrim(ltrim(Dev.PORTAL_URI_SEG1)) is NOT NULL

—The Peoplesoft Navigation menus, PS menus and components a specific permission list gives access to
SELECT DISTINCT Dev.PATH MENU_PATH, Dev.portal_objname PORTAL_OBJECT, Dev.PORTAL_URI_SEG2 PS_COMPONENT, Dev.PORTAL_URI_SEG1 PS_MENU, a.CLASSID PERMISSION_LIST,
DECODE(a.DISPLAYONLY,1,’TRUE’,0,’FALSE’) DISPLAYONLY,
(CASE
WHEN a.AUTHORIZEDACTIONS=1 THEN ‘ADD’
WHEN a.AUTHORIZEDACTIONS=2 THEN ‘UPDATE,DISPLAY’
WHEN a.AUTHORIZEDACTIONS=3 THEN ‘ADD,UPDATE, DISPLAY’
WHEN a.AUTHORIZEDACTIONS=4 THEN ‘UPDATE/DISPLAY ONLY’
WHEN a.AUTHORIZEDACTIONS=5 THEN ‘ADD,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=6 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=7 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=8 THEN ‘CORRECTION’
WHEN a.AUTHORIZEDACTIONS=9 THEN ‘ADD,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=10 THEN ‘UPDATE,DISPLAY,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=11 THEN ‘ADD, UPDATE/DISPLAY ALL, CORRECTION’
WHEN a.AUTHORIZEDACTIONS=12 THEN ‘UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=13 THEN ‘ADD, UPDATE, DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=14 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=15 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=128 THEN ‘DATA ENTRY’
ELSE ‘UNKNOWN’
END ) AUTHORIZEDACTIONS
FROM (select LTRIM(SYS_CONNECT_BY_PATH (PORTAL_LABEL, ‘>’),’>’) PATH, portal_objname,PORTAL_URI_SEG1,PORTAL_URI_SEG2 from psprsmdefn where level >1
connect by nocycle prior portal_objname= portal_prntobjname
start with portal_objname= ‘PORTAL_ROOT_OBJECT’ order by level ) Dev
inner join PSMENUITEM b ON Dev.PORTAL_URI_SEG2=b.PNLGRPNAME AND Dev.PORTAL_URI_SEG1 =b.MENUNAME
inner join PSAUTHITEM a ON a.menuname = b.menuname AND a.baritemname = b.itemname
where a.CLASSID=’YourPermissionList’
and rtrim(ltrim(Dev.PORTAL_URI_SEG2)) is NOT NULL and rtrim(ltrim(Dev.PORTAL_URI_SEG1)) is NOT NULL