— replace CSGC_CMPRND_TBL with your record for which you want the hierarchy
SELECT distinct r2.*
,p3.navigation,p2.DESCR254
FROM
sysadm.PSPNLGROUP A
, sysadm.PSPNLFIELD B
, sysadm.PSRECDEFN C
,(
select distinct EDITTABLE as recname,'impacts'||SYS_CONNECT_BY_PATH(EDITTABLE,'->') as PROMPT_RECORD_TREE
from psrecfield r1
where (SUBSTR(EDITTABLE,1,1) <> '%' and EDITTABLE<> ' ')
START with EDITTABLE='CSGC_CMPRND_TBL'
CONNECT BY NOCYCLE PRIOR recname=EDITTABLE
union
select distinct EDITTABLE as recname,'impacted'||SYS_CONNECT_BY_PATH(EDITTABLE,'->') as PROMPT_RECORD_TREE
from psrecfield r1
where (SUBSTR(EDITTABLE,1,1) <> '%' and EDITTABLE<> ' ')
START with recname='CSGC_CMPRND_TBL'
CONNECT BY NOCYCLE PRIOR EDITTABLE = recname order by 2 asc
) 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
