Inactivate UM Retiree Record if Active UM Faculty/Staff

What Does It Do?

The OUD-DS3 best practice is for retiree records to be end-dated if the constituent has active employment information.

This integrity check identifies active UM faculty and staff who also have a UM employment faculty/staff retiree affiliation so that the retiree record can be end-dated with the start date of the active appointment. 

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-AFFILIATION-End Date UM Retiree Record if UM Active(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries constituents with both U-M Staff - Active or U-M Faculty - Active and a U-M Staff - Retired or U-M Faculty - Retired affiliations. The retiree affiliation is given an end date that is the same as the start date of the active affiliation.

Resources and References

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

Snapshot GUID: bbdw.usr_dim_cnst_affltn.constituentsystemid