Inactivate Affiliations for Deceased Constituents

What Does It Do?

The OUD-DS3 best practice is for affiliations to have an end date (be inactive) for deceased constituents.

This integrity check identifies active affiliations on deceased constituent records so that they can be given an end date and inactivated. 

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[INTEGRITY_AFFILIATION_Deceased with Active Affiliations(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries affiliations that do not have a start date (i.e., are active) on constituents who are deceased. It will fill in the affiliation end date with the run date of the integrity check to inactivate the affiliation record.

Resources and References

S:\IDS\MES\SQL Scripts\DATA QUALITY\AFFILIATION

Snapshot GUID: BBDW.USR_DIM_CNST_AFFLTN.USR_CNST_AFFLTN_SYS_KEY