PeopleSoft Wiki

works only for oracle database currenty
SELECT distinct A.ROLENAME, A.CLASSID,IN2.navigation,'/EMPLOYEE/ERP/c/'||MI.MENUNAME ||'.'||PNLGRPNAME ||'.GBL' AS NAVIGATION1,
C.MENUNAME, C.BARNAME, C.BARITEMNAME, C.PNLITEMNAME, C.DISPLAYONLY, C.AUTHORIZEDACTIONS, D.PAGEACCESSDESCR,B.ROLENAME
FROM PSROLECLASS A, PSROLEDEFN B, PSAUTHITEM C, PSPGEACCESSDESC D ,PSMENUITEM MI,
(select SYS_CONNECT_BY_PATH(A.PORTAL_LABEL,'->') navigation, '/EMPLOYEE/ERP/c/' || PORTAL_URI_SEG1 || '.' || PORTAL_URI_SEG2 || '.' || PORTAL_URI_SEG3 url
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
portal_name = 'EMPLOYEE' and
portal_objname <> portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME AND
portal_Reftype = A.PORTAL_REFTYPE and
portal_objname = A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
start with A.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
connect by prior A.portal_objname = A.portal_prntobjname
) IN2
WHERE A.ROLENAME = B.ROLENAME
AND A.CLASSID = C.CLASSID
AND C.MENUNAME NOT IN ('WEBLIB_MENU')
AND C.BARNAME NOT IN ('FieldFormula')
—AND A.ROLENAME = 'GL_GENERIC_ROLE'
AND D.AUTHORIZEDACTIONS = C.AUTHORIZEDACTIONS
AND C.MENUNAME = MI.MENUNAME
AND C.BARNAME = MI.BARNAME
AND C.BARITEMNAME = MI.ITEMNAME
and url='/EMPLOYEE/ERP/c/'||MI.MENUNAME ||'.'||PNLGRPNAME ||'.GBL' and URL like '%CSG_PROMOTION_DEF1%'