yes, you can if you are using Oracle database. customer validation rules are allowed in Oracle, there is an enhancement request for DB2/sqlserver.
Make sure the peer table is populated before the child table.
BTW in the query above , I am not sure what C_BO_PRTY_RLE_PHN_COMM_XREF role is , it is not being used, unless you are making sure that party has a C_BO_PRTY_RLE_PHN_COMM_XREF row.
Thanks Danantha for response .
This is the custom validation rule we are using.
It is working as expected but only when we are running revalidate job expectation is it should automatically refresh the trust.
FROM C_BO_PRTY_XREF X,
C_BO_PRTY_RLE_EMPLYMNT_XREF B,C_BO_PRTY_RLE_PHN_COMM_XREF C
AND I.ROWID_SYSTEM = C.ROWID_SYSTEM
AND B.EMPLYMNT_STS IN ('INA','TER')
Please make sure the corresponding xref records in C_BO_PRTY_RLE_EMPLYMNT_XREF, C_BO_PRTY_RLE_PHN_COMM_XREF are there and have AND B.EMPLYMNT_STS IN ('INA','TER') , when the load job for this table is run.
The issue is resolved after using the below conditions,
Need Custom validation rule, while load of C_PARTY_PHONE table data, check the existing data present in parent and/or peer table like here, C_PARTY_STATUS and downgrade the trust
FROM C_PARTY_STATUS_XREF B
AND B.ROWID_SYSTEM = S.ROWID_SYSTEM
AND B.STATUS IN ('INACTIVE')
The trust for incoming C_PARTY_PHONE data will be downgraded, if the same source has contributed value INACTIVE, in Peer Table C_PARTY_STATUS(status column).