A query that will take a PeopleSoft role name as an input and returns all pages that could be access by that role. The query will also indicate what kind of operations a user can perform on that page. Example, Add Update/Display and so forth.
SELECT b.menuname, b.barname, b.baritemname, d.pnlname, c.pageaccessdescr
FROM psroleclass a,
psauthitem b,
pspgeaccessdesc c,
pspnlgroup d,
psmenuitem e
WHERE a.classid = b.classid
AND d.pnlgrpname = e.pnlgrpname
AND b.menuname = e.menuname
AND b.barname = e.barname
AND b.baritemname = e.itemname
AND a.rolename = :rolename
AND b.authorizedactions = c.authorizedactions
GROUP BY b.menuname, b.barname, b.baritemname, d.pnlname, c.pageaccessdescr
ORDER BY d.pnlname;