Delete Active UM Staff Record if Also Active UM Faculty

What Does It Do?

The OUD-DS3 best practice is for only the primary UM employment to be recorded as an affiliation.

This integrity check identifies active UM faculty who also have an active UM staff affiliation so that the staff record can be deleted. 

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-AFFILIATION-Delete Active Staff Affltn if Also Faculty(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries constituents with a U-M Faculty - Active affiliation who also have a U-M Staff - Active affiliation. The staff affiliation is deleted.

Resources and References

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

Snapshot GUID: bbdw.usr_dim_cnst_affltn.constituentsystemid