select distinct table_name,num_rows,nv.Navigation from
(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 ) nv ,PSPNLGROUP b ,PSPNLFIELD c ,all_tables
where nv.PORTAL_URI_SEG2=b.PNLGRPNAME
and b.PNLNAME=c.PNLNAME
and c.recname =replace(table_name,'PS_','') and num_rows >0 and table_name like 'PS%'
and upper(nv.Navigation) like upper('%Payable%') and nv.Navigation not like '%Portal Objects%' order by nv.Navigation, num_rows desc
Quickly find the core tables in existing running module
Summary:
oftehn where we take over existing peoplesoft project . We want to understand how much scope of the implemenation. one of the way is to use row count in different tables