—Replace CSG341IO with your AE name
SELECT DISTINCT R2.*,
P3.NAVIGATION,
P2.DESCR254
FROM SYSADM.PSPNLGROUP A, SYSADM.PSPNLFIELD B, SYSADM.PSRECDEFN C, (
—
select DISTINCT E.OBJECTVALUE2 AE_Section ,P.OBJECTVALUE6 Step , DECODE(RECNAME,'RECORD',REFNAME,RECNAME) AS RECNAME from
PSPCMNAME E, PSPCMPROG P where E.OBJECTVALUE1 in
(
select OBJECTVALUE1 from PSPCMPROG
WHERE
OBJECTID1 = 66 AND
OBJECTID2 = 77 AND
OBJECTID4 = 20 AND
OBJECTID5 = 21 AND
OBJECTID6 = 78 AND
EXISTS
(SELECT 'X'
FROM PSAESTMTDEFN T
WHERE PSPCMPROG.OBJECTVALUE1 = T.AE_APPLID
AND PSPCMPROG.OBJECTVALUE2 = T.AE_SECTION
AND PSPCMPROG.OBJECTVALUE3 = T.MARKET
AND ((PSPCMPROG.OBJECTVALUE4 = T.DBTYPE) OR
PSPCMPROG.OBJECTVALUE4 = 'default' AND T.DBTYPE = ' ' AND T.AE_APPLID LIKE 'CSG341IO%')
)
) AND
P.OBJECTID1 =E.OBJECTID1
AND P.OBJECTID2 =E.OBJECTID2
AND P.OBJECTID3 =E.OBJECTID3
AND P.OBJECTID4 =E.OBJECTID4
AND P.OBJECTID5 =E.OBJECTID5
AND P.OBJECTID6 =E.OBJECTID6
AND P.OBJECTID7 =E.OBJECTID7
AND P.OBJECTVALUE1 =E.OBJECTVALUE1
AND P.OBJECTVALUE2 =E.OBJECTVALUE2
AND P.OBJECTVALUE3 =E.OBJECTVALUE3
AND P.OBJECTVALUE4 =E.OBJECTVALUE4
AND P.OBJECTVALUE5 =E.OBJECTVALUE5
AND P.OBJECTVALUE6 =E.OBJECTVALUE6
AND P.OBJECTVALUE7 =E.OBJECTVALUE7
—
) R2, PSPRSMDEFN P2, (
SELECT SYS_CONNECT_BY_PATH(A.PORTAL_LABEL, '->') NAVIGATION,
'/EMPLOYEE/ERP/c/' || PORTAL_URI_SEG1 || '.' || PORTAL_URI_SEG2 || '.' ||
PORTAL_URI_SEG3 URL,
PORTAL_URI_SEG2
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 ) P3
WHERE R2.RECNAME = B.RECNAME
AND A.PNLGRPNAME = P2.PORTAL_URI_SEG2
AND P2.PORTAL_URI_SEG2 = P3.PORTAL_URI_SEG2
AND B.PNLNAME IN (
SELECT DISTINCT PNLNAME
FROM PSPNLFIELD A1
WHERE A1.PNLNAME = A.PNLNAME UNION
SELECT SUBPNLNAME
FROM PSPNLFIELD A1
START
WITH PNLNAME = A.PNLNAME
AND FIELDTYPE = 11 CONNECT BY PRIOR SUBPNLNAME = PNLNAME
AND FIELDTYPE = 11 )
AND B.RECNAME <> ' '
AND B.ASSOCFIELDNUM = 0
AND B.FIELDNUM = (
SELECT MIN(D.FIELDNUM)
FROM SYSADM.PSPNLFIELD D
WHERE D.PNLNAME = B.PNLNAME
AND D.RECNAME = B.RECNAME
AND D.OCCURSLEVEL = B.OCCURSLEVEL)
AND C.RECNAME = B.RECNAME
ORDER BY 2
