menu_query.sql

SELECT DISTINCT d.roleuser, 
                a.rolename, 
                b.classid                  AS PERMISSION_LIST, 
                b.menuname, 
                b.baritemname              AS "COMPONENT NAME", 
                b.pnlitemname, 
                c.descr                    AS "PAGE DESCRIPTION", 
                Decode(b.displayonly, 1, 'Y', 
                                      'N') AS "DISPLAY ONLY?", 
                e.navigation 
FROM   sysadm.psroleclass a, 
       sysadm.psauthitem b, 
       sysadm.pspnldefn c, 
       sysadm.psroleuser d, 
       (SELECT LEVEL0.portal_label 
               || ' > ' 
               || LEVEL1.portal_label 
               || ' > ' 
               || LEVEL2.portal_label 
               || ' > ' 
               || level3.portal_label AS navigation, 
               level3.portal_uri_seg2 AS component 
        FROM   psprsmdefn level3, 
               psprsmdefn level2, 
               psprsmdefn level1, 
               psprsmdefn LEVEL0 
        WHERE  level3.portal_prntobjname = level2.portal_objname 
               AND level2.portal_prntobjname = level1.portal_objname 
               AND level1.portal_prntobjname = LEVEL0.portal_objname 
               AND level3.portal_name = level2.portal_name 
               AND level2.portal_name = level1.portal_name 
               AND level1.portal_name = LEVEL0.portal_name) e 
WHERE  a.classid = b.classid 
       AND b.pnlitemname = c.pnlname 
       AND a.rolename = d.rolename 
       --AND d.roleuser = 'USER' 
       --AND b.baritemname = 'COMPONENT NAME' 
       --AND d.rolename = 'ROLENAME' 
       --AND b.classid = 'MENU NAME' 
       AND e.component = b.baritemname 
ORDER  BY 1, 
          2, 
          3, 
          4, 
          5, 

          6;