PeopleSoft records having same list of fields included in the definition structure

We do know "Find Definition Reference" in PeopleSoft application designer is one of the feature which every developer uses to identify a particular definition's usage. This information is to determine the impacts of modifying a particular definition and dependent definitions.

In recent time I came across a situation in which I need to identify all the PeopleSoft records that have some particular fields included in the same record definition structure. I was unable to achieve this just by using Find Definition Reference. Thought of writing a SQL query based on the PeopleTools table PSRECFIELD which will identify all those records that has the same list of fields included in the definition structure.

In my below example query I tried to identify those ELM records that has both the fields LM_PERSON_ID and LM_HR_EMPLID in record definition. You can replace these field names with the field combinations you wanted to search.

select * from

(select SUB.RECNAME

, LISTAGG(SUB.FIELDNAME, ',')

WITHIN GROUP (ORDER BY SUB.FIELDNAME) as LIST

FROM PSRECFIELD SUB

WHERE FIELDNAME IN ('LM_PERSON_ID','LM_HR_EMPLID')

GROUP BY SUB.RECNAME) T

where (T.LIST LIKE '%LM_PERSON_ID%'

AND T.LIST LIKE '%LM_HR_EMPLID%')

 

Note: LISTAGG function throws error in some the oracle versions. In such cases you can achieve the above result by using the below SQL query by using Oracle function "connect by prior".

 

In my below example query I tried to identify those HCM records that has the fields EMPLID, EFFDT, EFFSEQ and EMPL_RCD  in the record definition. You can replace these field names with the field combinations you wanted to search.

 

select * from

(select

   RECNAME,

   substr(SYS_CONNECT_BY_PATH(FIELDNAME, ','),2) LIST

from

   (

   select

     RECNAME,

     FIELDNAME,

     count(*) OVER ( partition by RECNAME ) cnt,

     ROW_NUMBER () OVER ( partition by RECNAME order by FIELDNAME) seq

   from

     PSRECFIELD

   where

     RECNAME is not null

      AND FIELDNAME IN ('EMPLID','EFFDT','EFFSEQ','EMPL_RCD'))

where

   seq=cnt

start with

   seq=1

connect by prior

   seq+1=seq

and prior

   RECNAME=RECNAME) T

   where (T.LIST LIKE '%EMPLID%'

   AND T.LIST LIKE '%EFFDT%'

   AND T.LIST LIKE '%EFFSEQ%'

   AND T.LIST LIKE '%EMPL_RCD%')