To get page level access in PeopleSoft:
-------------------------------------------------------------------------------------------------------------------------------------------------------
select x.menuname, x.barname, x.baritemname, x.pnlgrpname, x.itemname, x.itemlabel ,
(select distinct a1.classid from psroleclass a1, PSAUTHITEM x1 where x1.menuname = x.menuname and x1.barname = x.barname and x1.baritemname = x.baritemname and x1.pnlitemname = x.pnlitemname and a1.classid = x1.classid and rolename = 'HM_GPR_SUPER_ADMIN')HM_GPR_SUPER_ADMIN,
(select distinct a2.classid from psroleclass a2, PSAUTHITEM x2 where x2.menuname = x.menuname and x2.barname = x.barname and x2.baritemname = x.baritemname and x2.pnlitemname = x.pnlitemname and a2.classid = x2.classid and rolename = 'HM_GPR_ADVANCE_ADMIN')HM_GPR_ADVANCE_ADMIN,
(select distinct a3.classid from psroleclass a3, PSAUTHITEM x3 where x3.menuname = x.menuname and x3.barname = x.barname and x3.baritemname = x.baritemname and x3.pnlitemname = x.pnlitemname and a3.classid = x3.classid and rolename = 'HM_GPR_ADMIN')HM_GPR_ADMIN,
(select distinct a4.classid from psroleclass a4, PSAUTHITEM x4 where x4.menuname = x.menuname and x4.barname = x.barname and x4.baritemname = x.baritemname and x4.pnlitemname = x.pnlitemname and a4.classid = x4.classid and rolename = 'HM_GPR_BONUS_ADMIN')HM_GPR_BONUS_ADMIN,
(select distinct a5.classid from psroleclass a5, PSAUTHITEM x5 where x5.menuname = x.menuname and x5.barname = x.barname and x5.baritemname = x.baritemname and x5.pnlitemname = x.pnlitemname and a5.classid = x5.classid and rolename = 'HM_GPR_SETUP_ADMIN')HM_GPR_SETUP_ADMIN,
(select distinct a6.classid from psroleclass a6, PSAUTHITEM x6 where x6.menuname = x.menuname and x6.barname = x.barname and x6.baritemname = x.baritemname and x6.pnlitemname = x.pnlitemname and a6.classid = x6.classid and rolename = 'HM_GPR_SETUP_READ')HM_GPR_SETUP_READ,
(select distinct a7.classid from psroleclass a7, PSAUTHITEM x7 where x7.menuname = x.menuname and x7.barname = x.barname and x7.baritemname = x.baritemname and x7.pnlitemname = x.pnlitemname and a7.classid = x7.classid and rolename = 'HM_GPR_BANK_UPDATE')HM_GPR_BANK_UPDATE
from (select distinct a.menuname, a.barname, a.baritemname, a.pnlitemname, p.pnlgrpname, p.itemname, p.itemlabel
FROM PSAUTHITEM a, pspnlgroup p
Where ((a.baritemname = p.pnlgrpname and a.pnlitemname = p.pnlname)
or (a.pnlitemname = p.pnlname and p.pnlgrpname = p.pnlname)
or (a.baritemname = p.pnlgrpname and p.pnlgrpname = p.pnlname))
-- where p.pnlgrpname = a.baritemname
-- and p.itemname = a.pnlitemname
--and pnlitemname = 'GP_DURATION'
and classid in (select distinct classid from psroleclass
where rolename like 'HM_GPR_%'and rolename not in ( 'HM_GPR_TEMP', 'HM_GPR_QUERY', 'HM_GPR_USERS'))
order by menuname, barname, baritemname, pnlgrpname, itemname) x
-------------------------------------------------------------------------------------------------------------------------------------------------------
-- security matrix read/write
select x.menuname, x.barname, x.baritemname, x.pnlgrpname, x.itemname, x.itemlabel ,
(select distinct decode(x1.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a1, PSAUTHITEM x1 where x1.menuname = x.menuname and x1.barname = x.barname and x1.baritemname = x.baritemname and x1.pnlitemname = x.pnlitemname and a1.classid = x1.classid and rolename = 'HM_GPR_SUPER_ADMIN')HM_GPR_SUPER_ADMIN,
(select distinct decode(x2.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a2, PSAUTHITEM x2 where x2.menuname = x.menuname and x2.barname = x.barname and x2.baritemname = x.baritemname and x2.pnlitemname = x.pnlitemname and a2.classid = x2.classid and rolename = 'HM_GPR_ADVANCE_ADMIN')HM_GPR_ADVANCE_ADMIN,
(select distinct decode(x3.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a3, PSAUTHITEM x3 where x3.menuname = x.menuname and x3.barname = x.barname and x3.baritemname = x.baritemname and x3.pnlitemname = x.pnlitemname and a3.classid = x3.classid and rolename = 'HM_GPR_ADMIN')HM_GPR_ADMIN,
(select distinct decode(x4.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a4, PSAUTHITEM x4 where x4.menuname = x.menuname and x4.barname = x.barname and x4.baritemname = x.baritemname and x4.pnlitemname = x.pnlitemname and a4.classid = x4.classid and rolename = 'HM_GPR_BONUS_ADMIN')HM_GPR_BONUS_ADMIN,
(select distinct decode(x5.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a5, PSAUTHITEM x5 where x5.menuname = x.menuname and x5.barname = x.barname and x5.baritemname = x.baritemname and x5.pnlitemname = x.pnlitemname and a5.classid = x5.classid and rolename = 'HM_GPR_SETUP_ADMIN')HM_GPR_SETUP_ADMIN,
(select distinct decode(x6.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a6, PSAUTHITEM x6 where x6.menuname = x.menuname and x6.barname = x.barname and x6.baritemname = x.baritemname and x6.pnlitemname = x.pnlitemname and a6.classid = x6.classid and rolename = 'HM_GPR_SETUP_READ')HM_GPR_SETUP_READ,
(select distinct decode(x7.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a7, PSAUTHITEM x7 where x7.menuname = x.menuname and x7.barname = x.barname and x7.baritemname = x.baritemname and x7.pnlitemname = x.pnlitemname and a7.classid = x7.classid and rolename = 'HM_GPR_BANK_UPDATE')HM_GPR_BANK_UPDATE
from (select distinct a.menuname, a.barname, a.baritemname, a.pnlitemname, p.pnlgrpname, p.itemname, p.itemlabel
FROM PSAUTHITEM a, pspnlgroup p
Where ((a.baritemname = p.pnlgrpname and a.pnlitemname = p.pnlname)
or (a.pnlitemname = p.pnlname and p.pnlgrpname = p.pnlname)
or (a.baritemname = p.pnlgrpname and p.pnlgrpname = p.pnlname))
-- where (a.pnlitemname = p.pnlname or a.baritemname = pnlname)
--where p.pnlgrpname = a.baritemname
--and p.itemname = a.pnlitemname
--and pnlitemname = 'GP_DURATION'
and classid in (select distinct classid from psroleclass
where rolename like 'HM_GPR_%'and rolename not in ( 'HM_GPR_TEMP', 'HM_GPR_QUERY', 'HM_GPR_USERS'))
order by menuname, barname, baritemname, pnlitemname, itemname) x
