|
Category: Relationship
|
Frequency: Monthly
|
Platform: SQL Server
|
Owner: IDS
|
What Does It Do?
The OUD-DS3 best practice is for retiree relationship and employee records to have a blank end date - this is the constituent’s current “job”.
This integrity check identifies relationship and employment history records where the job schedule is “Retired” which have end dates so that the end dates can be removed from these records.
How Is This Done?
This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-RELATIONSHIP-Remove End Date from Retirees(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.
That view queries active individuals with an employment history record having a job schedule of ‘Retired’ and an end date. It provides the employment history record and corresponding organizational relationship record that are updated to remove the end dates.
The query removes records where the constituent already has an active (no end date) employment history record associated with that relationship because DART does not allow for multiple active employment history records with one organization.
The query removes the following inactive relationship types because they can not be fixed via batch: Spouse is Owner (SoftCredit), Spouse is Owner/CEO (SoftCrdt), Spouse is Trstee/Own(SoftCrdt), Web-Hidden Spouse is Employee. Translating inactive code values is on the IDS data quality backlog.
Resources and References
S:\IDS\MES\SQL Scripts\DATA QUALITY\RELATIONSHIP
Snapshot GUID: BBDW.DIM_CONSTITUENTRELATIONSHIPEMPLOYMENT.CONSTITUENTRELATIONSHIPEMPLOYMENTSYSTEMID