SELECT 'SELECT '''
||'RecDescr.'
||A.RECDESCR
||'-Record.'
||A.RECNAME
||'.FieldName.'
||d.COLS1
||'->'
||'''||'
||B.FIELDNAME||','||d.COLS1
||', COUNT(1) OVER() AS CNT_OF_OBJECTS1 FROM '
|| DECODE(SUBSTR(B.recname, 1,2),'PS','','PS_')
||A.RECNAME
|| ' WHERE LASTUPDOPRID <> ''PPLSOFT'';' as Details
—, B.RECNAME, B.FIELDNAME, A.RECDESCR
FROM PSRECDEFN A,
PSRECFIELDALL B ,
(SELECT TABLE_NAME ,
LISTAGG (COLUMN_NAME, ', ') WITHIN GROUP (
ORDER BY TABLE_NAME, COLUMN_ID) AS COLS1
FROM USER_TAB_COLS
WHERE column_id<4
GROUP BY TABLE_NAME
) d
WHERE ( A.RECNAME = B.RECNAME
AND A.OBJECTOWNERID = 'PPT')
AND A.RECTYPE =0
AND b.recname IN
(SELECT DISTINCT recname FROM PSRECFIELDALL c WHERE FIELDNAME='LASTUPDOPRID'
)
AND A.RECNAME NOT LIKE '%AUD%'
AND b.FIELDNAME LIKE '%DESC%'
AND (DECODE(SUBSTR(B.recname, 1,2),'PS','','PS_')
||A.RECNAME=d.TABLE_NAME)
ORDER BY B.RECNAME;
SELECT * FROM PSRECFIELD;
Get the customization in Peoplesoft
Summary:
Execute the below sql in oracle and then again output should be executed in oracle to get the customizations