Select distinct lbltext,DESCR254,nav, recname,fieldname from (
SELECT SYS_CONNECT_BY_PATH (a.portal_label, '->') as Nav, a.portal_uri_seg2 from
PSPRSMDEFN a
where a.portal_label NOT LIKE '%Navigation Collections%'
START WITH a.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY nocycle PRIOR a.portal_objname = a.portal_prntobjname ) nv,
(SELECT A.LBLTEXT, A.RECNAME, A.FIELDNAME, C.PORTAL_LABEL, C.DESCR254, C.PORTAL_URLTEXT, C.PORTAL_URI_SEG2, B.PNLGRPNAME,A.PNLNAME
FROM PSPNLGROUP B, PSPNLFIELD A, PSPRSMDEFN C
WHERE B.PNLNAME = A.PNLNAME
AND B.PNLGRPNAME = C.PORTAL_URI_SEG2 ) np
where np.PORTAL_URI_SEG2=nv.PORTAL_URI_SEG2
and decode(substr(recname, 1,2),'PS','','PS_')||recname in (select table_name from ALL_tables where table_name like 'PS%' and num_rows>0)
and nav like '%Ledger%'
Identifying the tables and pages for given module which have data
Summary:
Identifying the tables and pages for given module which have data, so we can find out what is actually used in peoplesoft instance