Query to find the list of users having access to component, action along with Navigation details

 

SELECT DISTINCT roleclass.rolename,
authitem.menuname,
authitem.menulabel, 
authitem.baritemname,
authitem.itemlabel,
DECODE(authitem.DISPLAYONLY, 0, 'N', 1, 'Y') AS "Display_Only",
CASE authitem.AUTHORIZEDACTIONS
    WHEN 1 THEN 'Add'
    WHEN 2 THEN 'Update/Display'
    WHEN 3 THEN 'Add, Update/Display'
    WHEN 4 THEN 'Update/Display All'
    WHEN 5 THEN 'Add, Update/Display All'
    WHEN 6 THEN 'Update/Display, Update/Display All'
    WHEN 7 THEN 'Add, Update/Display, Update/Display All'
    WHEN 8 THEN 'Correction'
    WHEN 9 THEN 'Add, Correction'
    WHEN 10 THEN 'Update/Display, Correction'
    WHEN 11 THEN 'Add, Update/Display, Correction'
    WHEN 12 THEN 'Update/Display All, Correction'
    WHEN 13 THEN 'Add, Update/Display All, Correction'
    WHEN 14 THEN 'Update/Display, Update/Display All, Correction'
    WHEN 15 THEN 'Add, Update/Display, Update/Display All, Correction'
    ELSE 'SPECIAL' END AS "Authorized_Actions",
authitem.PNLGRPNAME,
CASE InStr(nav.PATH_TO_COMPONENT, 'Root>', 1)
  WHEN 0 THEN nav.PATH_TO_COMPONENT
  ELSE SubStr(nav.PATH_TO_COMPONENT, InStr(nav.PATH_TO_COMPONENT, 'Root>', 1) + 5, length(nav.PATH_TO_COMPONENT) - InStr(nav.PATH_TO_COMPONENT, 'Root>', 1) + 5)
  END AS navigation
from
(SELECT
DISTINCT a.CLASSID, a.MENUNAME, b.MENULABEL , a.BARITEMNAME,c.itemlabel ,
a.DISPLAYONLY,                                                                               
a.AUTHORIZEDACTIONS,
c.PNLGRPNAME
FROM PSAUTHITEM a, PSMENUDEFN b, PS_ACLCOMPONENT_V2 c
where a.MENUNAME = b.MENUNAME
AND a.menuname =  c.menuname
AND a.barname = c.barname
AND a.baritemname = c.baritemname) authitem ,
psroleclass roleclass,
(SELECT a.PORTAL_URI_SEG2 as component
, E.PORTAL_LABEL || '>' || D.PORTAL_LABEL || '>' ||C.PORTAL_LABEL ||
'>' || B.PORTAL_LABEL || '>' || A.PORTAL_LABEL as PATH_TO_COMPONENT
, A.PORTAL_URLTEXT
, A.PORTAL_ISPUBLIC as Active
, A.LASTUPDOPRID
, A.LASTUPDDTTM
, A.PORTAL_URI_SEG2
FROM PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN D, PSPRSMDEFN
E
WHERE 1=1
AND A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME(+)
AND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME(+)
AND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME(+)
AND D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME(+)
AND (E.PORTAL_NAME IS NULL OR E.PORTAL_NAME = 'EMPLOYEE')
AND (A.PORTAL_NAME = 'EMPLOYEE' OR A.PORTAL_NAME IS NULL )
AND (B.PORTAL_NAME = 'EMPLOYEE' OR B.PORTAL_NAME IS NULL )
AND (C.PORTAL_NAME = 'EMPLOYEE' OR C.PORTAL_NAME IS NULL )
AND (D.PORTAL_NAME = 'EMPLOYEE' OR D.PORTAL_NAME IS NULL )
) nav
WHERE  roleclass.rolename =
AND authitem.classid = roleclass.classid
AND  authitem.PNLGRPNAME =  nav.PORTAL_URI_SEG2(+)
ORDER BY rolename, menuname, baritemname