PeopleSoft Wiki

The below query has added functionality ,if the prompt recrod is a view. then tables and view within view record are also used for record hierarchy

Select PROMPT,level ,RECNAME, sys_connect_by_path(EDITTABLE,'->') from (
select recname AS RECNAME, EDITTABLE,'PROMPT' as PROMPT from psrecfield where EDITTABLE <> ' '
union
select
REPLACE(NAME,'PS_')AS RECNAME,
REPLACE(REFERENCED_NAME, 'PS_') as EDITTABLE ,'VIEW' as PROMPT
from ALL_DEPENDENCIES
where referenced_type in ('TABLE','VIEW' ) and type in ('TABLE','VIEW' ))
Start with RECNAME ='JRNL_HEADER'
connect by nocycle prior EDITTABLE=RECNAME
order by level