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;