Identifying all child records of a record defintion

In certain situations we may have to change the key field of an existing record. Though it is not recommended to make any such key field changes in the PeopleSoft delivered records. You may have to change in custom records. When you plan on this key field change we do identify the downstream impacts.

 

Whether the record that you are planning to alter by adding one additional key field is used as parent record. In this case you also have to make sure to alter all the child records. In PeopleSoft any child record should contain all the key fields of the parent record plus one additional key field to maintain the parent child relationship. To maintain this you have to identify all the child records.

 

Ideally in application designer you can do find definition references which will show all the records that has this particular record as parent. Sometimes we may also be interested in knowing the entire Parent child hierarchy to understand the complete impacts. In this doodle I will share some queries that can be used to determine:

 

1) Child records of a record definition.

2) Parent record of a record definition.

 

Here I have used 'PER_ORG_ASGN' as an example record in my SQL's. Replace this with the record name that you wanted to analyze in your database.

 

Child records of a record

 

Below SQL will return all the records that have PER_ORG_ASGN as parent record in record properties in Application Designer.

 

select RECNAME from PSRECDEFN  where PARENTRECNAME = 'PER_ORG_ASGN'

 

Apart from the above direct child records, we will also be interested to know indirect child records. For example PER_ORG_ASGN is parent of JOB and JOB is parent of JOB_JR. To understand the complete hierarchy (i.e) direct and indirect child records, you can use the below SQL.

 

select ltrim(C.ChildPath,' > ') "Parent > Child" from

(select RECNAME,PARENTRECNAME,SYS_CONNECT_BY_PATH(RECNAME, '  > ') as ChildPath

from PSRECDEFN

start with RECNAME = 'PER_ORG_ASGN'

connect by PRIOR RECNAME = PARENTRECNAME) C

where RECNAME <> 'PER_ORG_ASGN'

 

Sample result of above SQL:

 

PER_ORG_ASGN  > JOB  > JOB_JR

 

Read the above result as JOB_JR is child of JOB and grandchild of PER_ORG_ASGN. For example, if you are planning to add new key field to PER_ORG_ASGN then you should also add the same field as key in both JOB  and JOB_JR to maintain the parent child relationship.

 

Parent record of a record

 

Below SQL will return the immediate parent record of a record definition which has been specified in the record properties in application designer.

 

select PARENTRECNAME from PSRECDEFN  where RECNAME = 'PER_ORG_ASGN'

 

Sometimes we will be interested in knowing the parent hierarchy (i.e) what is the immediate parent and grand parents. To get this parent hierarchy use the below SQL

 

select ltrim(C.ParentPath,' > ') "Child > Parent" from

(select RECNAME,PARENTRECNAME,SYS_CONNECT_BY_PATH(RECNAME, '  > ') as ParentPath

from PSRECDEFN

start with RECNAME = 'PER_ORG_ASGN'

connect by prior PARENTRECNAME = RECNAME) C

where PARENTRECNAME = ' '

 

Sample result of above SQL:

PER_ORG_ASGN  > PERSON

 

Read the above result as PERSON is parent of PER_ORG_ASGN.