PeopleSoft Wiki

Another variation , includes descr of child record
Select t.PARENT_RECORD ,t.CHILD_RECORD,r.recdescr,t.PARENT_KEY,t.CHILD_KEY from psrecdefn r ,(
select P.RECNAME AS PARENT_RECORD ,C.RECNAME AS CHILD_RECORD,P.KEYSTRING AS PARENT_KEY ,c.KEYSTRING AS CHILD_KEY
from (select RECNAME,max(ltrim(sys_connect_by_path(FIELDNAME,','),',')) keystring
from (select RECNAME,FIELDNAME,
row_number() over(partition by RECNAME order by KEYPOSN) rn
from PSKEYDEFN where indexid='_')
start with rn = 1
connect by prior rn = rn -1
and prior RECNAME = RECNAME
group by RECNAME) P ,(select RECNAME,max(ltrim(sys_connect_by_path(FIELDNAME,','),',')) keystring
from (select RECNAME,FIELDNAME,
row_number() over(partition by RECNAME order by KEYPOSN) rn
from PSKEYDEFN where indexid='_')
start with rn = 1
connect by prior rn = rn -1
and prior RECNAME = RECNAME
group by RECNAME) C
where instr(c.keystring,P.keystring)<>0
and p.recname='VENDOR'
) t
where r.recname= t.CHILD_RECORD

joining with record description.
SELECT P.RECNAME AS PARENT_RECORD ,
C.RECNAME AS CHILD_RECORD,
P.KEYSTRING AS PARENT_KEY ,
c.KEYSTRING AS CHILD_KEY,
r1.recdescr as child_rec_descr, r1.descrlong as child_rec_descr_long
FROM
(SELECT RECNAME,
MAX(ltrim(sys_connect_by_path(FIELDNAME,','),',')) keystring
FROM
(SELECT RECNAME,
FIELDNAME,
row_number() over(partition BY RECNAME order by KEYPOSN) rn
FROM PSKEYDEFN
WHERE indexid='_'
)
START WITH rn = 1
CONNECT BY prior rn = rn -1
AND prior RECNAME = RECNAME
GROUP BY RECNAME
) P ,
(SELECT RECNAME,
MAX(ltrim(sys_connect_by_path(FIELDNAME,','),',')) keystring
FROM
(SELECT RECNAME,
FIELDNAME,
row_number() over(partition BY RECNAME order by KEYPOSN) rn
FROM PSKEYDEFN
WHERE indexid='_' and (recname not like '%AET' or recname not like '%TAO')
)
START WITH rn = 1
CONNECT BY prior rn = rn -1
AND prior RECNAME = RECNAME
GROUP BY RECNAME
) C, PSRECDEFN r1
WHERE instr(c.keystring,P.keystring)<>0
AND p.recname ='CA_DETAIL'
and c.recname=r1.recname