Posted by Nitin Pai
Comments (4)
August 21st, 2007

While working on my previous projects, I had some difficulties identifying the menu navigation for a process or report in PeopleSoft. So I developed this SQL that will help determine the navigation.

select distinct b.menugroup||’ > ‘ ||b.menulabel||

‘ > ‘||barlabel||’ > ‘||a.itemlabel||’ > ‘

||d.pnlgrpname||’ – ‘||prcstype||’ – ‘||prcsname“Navigation”

from psmenuitem a, psmenudefn b, ps_prcsdefnpnl c, pspnlgroup d

where a.menuname=b.menuname

and a.pnlgrpname = c.pnlgrpname

and a.pnlgrpname = d.pnlgrpname

and prcsname like upper(‘&prcsname’)

group by b.menuname, b.menugroup, b.menulabel, barlabel, a.itemlabel,

d.pnlgrpname, prcstype, prcsname



Mana&ge Assets > &Interface Asset Information > &Process > &Transaction Loader > RUN_AMIF1000 – Application Engine – AMIF1000

The above results also shows the component name, process type and finally the process name.

Comments (4)

Nitin Pai - October 1st, 2007

The below SQL will provide you the information that you have requested. Please note that this provides the menu navigation and not the portal navigation. SELECT A.ROLENAME, A.CLASSID, C.MENUNAME, C.BARNAME, C.BARITEMNAME, C.PNLITEMNAME, C.DISPLAYONLY, C.AUTHORIZEDACTIONS FROM PSROLECLASS A, PSAUTHITEM C WHERE A.ROLENAME = '&Role' AND A.CLASSID = C.CLASSID Thanks Nitin

satish - September 26th, 2007

Hi, Nice peiece of SQL.But i am looking the navigation for a given role. Eg:If i run a query against role,I should be getting all the page access to that role with menu navigation. Can u suggest? Satish.

Spamboy - August 22nd, 2007

In 8.9, you can also accomplish this just with the PSPRSMDEFN table -- since determining navigation based on a menu doesn't always jive with the portal navigations, that makes better sense. Here's a PL/SQL script to output four levels of navigation. Note: in at least HCM, nearly all OOtB navigations don't go more than six levels deep, so join PSPRSMDEFN two more times to get all possible levels. SELECT D.PORTAL_LABEL, C.PORTAL_LABEL, B.PORTAL_LABEL, A.PORTAL_LABEL FROM PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN D WHERE A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME (+) AND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME (+) AND B.PORTAL_NAME = C.PORTAL_NAME (+) AND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME (+) AND C.PORTAL_NAME = D.PORTAL_NAME (+) AND A.PORTAL_NAME = 'EMPLOYEE' -- To differentiate from CUSTOMER, MOBILE, or other portals AND A.PORTAL_URI_SEG2 = :1 -- Put upper-case Component Name here AND A.PORTAL_LABEL != ' '

Pete Finnigan - August 22nd, 2007

Thanks for the very useful code. I sometimes (not often) get to do security audits on Oracle databases that also support peoplesoft and one of the key areas i look at are roles, responsibilities, privileges and access assigned to individual groups of users. I dont have peoplesoft installed at present but will do so soon and will test this. Thanks for the script cheers Pete