TESTING SITE
Skeleton Entity Data Set — Version 02.10
The full specification for SKL 02.10 can be found on the documentation site.
Main Content
SKL version 02.10: Rules
| Name | Class | Priority | Bulk | Message | Mark | Description | SQL |
|---|---|---|---|---|---|---|---|
| HrGenDtMissing | Missing | High | No | Missing data - GenDt $GenDt.q | HR.GenDt | Missing data - Data File Generation Date (GenDt) | select State, GenDt from HR where GenDt is null |
| StStateNameMissing | Missing | High | No | Missing data - StateName $StateName.q | ST.StateName | Missing data - State/Territory Name (StateName) | select State, StateName from ST where StateName is null |
| RegRegNameMissing | Missing | High | No | Missing data - RegName $RegName.q | REG.RegName | Missing data - Region Name (RegName) | select State, RegId, RegName from REG where RegName is null |
| OrgOrgNameMissing | Missing | High | No | Missing data - OrgName $OrgName.q | ORG.OrgName | Missing data - Organisation Name (OrgName) | select State, RegId, OrgId, OrgName from ORG where OrgName is null |
| HospSectorMissing | Missing | High | No | Missing data - Sector $Sector.q | HOSP.Sector | Missing data - Sector (Sector) | select State, RegId, OrgId, HospId, Sector from HOSP where Sector is null |
| HospHospNameMissing | Missing | High | No | Missing data - HospName $HospName.q | HOSP.HospName | Missing data - Hospital Name (HospName) | select State, RegId, OrgId, HospId, HospName from HOSP where HospName is null |
| ClusClusNameMissing | Missing | High | No | Missing data - ClusName $ClusName.q | CLUS.ClusName | Missing data - Service Unit Cluster Name (ClusName) | select State, RegId, OrgId, ClusId, ClusName from CLUS where ClusName is null |
| AdmiTargetPopMissing | Missing | High | No | Missing data - TargetPop $TargetPop.q | ADMI.TargetPop | Missing data - Target Population (TargetPop) | select State, RegId, OrgId, HospId, AdmiId, TargetPop from ADMI where TargetPop is null |
| AdmiAdmiNameMissing | Missing | High | No | Missing data - AdmiName $AdmiName.q | ADMI.AdmiName | Missing data - Admitted Patient Service Unit Name (AdmiName) | select State, RegId, OrgId, HospId, AdmiId, AdmiName from ADMI where AdmiName is null |
| AmbuTargetPopMissing | Missing | High | No | Missing data - TargetPop $TargetPop.q | AMBU.TargetPop | Missing data - Target Population (TargetPop) | select State, RegId, OrgId, ClusId, AmbuId, TargetPop from AMBU where TargetPop is null |
| AmbuSectorMissing | Missing | High | No | Missing data - Sector $Sector.q | AMBU.Sector | Missing data - Sector (Sector) | select State, RegId, OrgId, ClusId, AmbuId, Sector from AMBU where Sector is null |
| AmbuAmbuNameMissing | Missing | High | No | Missing data - AmbuName $AmbuName.q | AMBU.AmbuName | Missing data - Ambulatory Service Unit Name (AmbuName) | select State, RegId, OrgId, ClusId, AmbuId, AmbuName from AMBU where AmbuName is null |
| ResiTargetPopMissing | Missing | High | No | Missing data - TargetPop $TargetPop.q | RESI.TargetPop | Missing data - Target Population (TargetPop) | select State, RegId, OrgId, ClusId, ResiId, TargetPop from RESI where TargetPop is null |
| ResiSectorMissing | Missing | High | No | Missing data - Sector $Sector.q | RESI.Sector | Missing data - Sector (Sector) | select State, RegId, OrgId, ClusId, ResiId, Sector from RESI where Sector is null |
| ResiResiNameMissing | Missing | High | No | Missing data - ResiName $ResiName.q | RESI.ResiName | Missing data - Residential Service Unit Name (ResiName) | select State, RegId, OrgId, ClusId, ResiId, ResiName from RESI where ResiName is null |
| OrgBarren | Barren | High | No | ORG has no CLUS or HOSP records | ORG | Barren Organisation - has no associated Hospitals or Service Unit Clusters | select State, RegId, OrgId from OrgHospCount join OrgClusCount using(State, RegId, OrgId) where OrgHospCount.Count = 0 and OrgClusCount.Count = 0 |
| ClusBarren | Barren | High | No | CLUS has no AMBU or RESI records | CLUS | Barren Cluster - has no associated Residential or Ambulatory service units | select State, RegId, OrgId, ClusId from ClusAmbuCount join ClusResiCount using(State, RegId, OrgId, ClusId) where ClusAmbuCount.Count = 0 and ClusResiCount.Count = 0 |
| RegOpened | Historical | High | No | Reg opened, $RegName.qt (RegId: $RegId) not in historical data | REG | Region Opened - A matching Region was not found in the historical data | select State, RegId, REG.RegName from main.REG left join hist.REG as hist_entity using (State, RegId) where hist_entity.RegId is null |
| RegClosed | Historical | High | Yes | Reg closed, historical $hist_name.qt (RegId: $hist_RegId) no longer exists $hist_context | ST | Region Closed - A historical Region was not found in current data | select hist_entity.State as State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.RegName as hist_name from hist.REG as hist_entity left join main.REG using (State, RegId) where REG.RegId is null |
| RegRenamed | Historical | High | No | Reg renamed from $hist_name.qt to $RegName.qt | REG.RegName | Region Renamed - Region Name differs between historical and current data | select State, RegId, REG.RegName, hist_entity.RegName as hist_name from REG join hist.REG as hist_entity using(State, RegId) where not sloppy_match(REG.RegName, hist_entity.RegName) |
| OrgOpened | Historical | High | No | Org opened, $OrgName.qt (OrgId: $OrgId) not in historical data | ORG | Organisation Opened - A matching Organisation was not found in the historical data | select State, RegId, OrgId, ORG.OrgName from main.ORG left join hist.ORG as hist_entity using (State, RegId, OrgId) where hist_entity.OrgId is null |
| OrgClosed | Historical | High | Yes | Org closed, historical $hist_name.qt (OrgId: $hist_OrgId) no longer exists $hist_context | ST | Organisation Closed - A historical Organisation was not found in current data | select hist_entity.State as State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.OrgId as hist_OrgId, ' (' ||RegName || ')' as hist_context, hist_entity.OrgName as hist_name from hist.ORG as hist_entity left join main.ORG using (State, RegId, OrgId) join hist.REG using (State, RegId) where ORG.OrgId is null |
| OrgRenamed | Historical | High | No | Org renamed from $hist_name.qt to $OrgName.qt | ORG.OrgName | Organisation Renamed - Organisation Name differs between historical and current data | select State, RegId, OrgId, ORG.OrgName, hist_entity.OrgName as hist_name from ORG join hist.ORG as hist_entity using(State, RegId, OrgId) where not sloppy_match(ORG.OrgName, hist_entity.OrgName) |
| HospOpened | Historical | High | No | Hosp opened, $HospName.qt (HospId: $HospId) not in historical data | HOSP | Hospital Opened - A matching Hospital was not found in the historical data | select State, RegId, OrgId, HospId, HOSP.HospName from main.HOSP left join hist.HOSP as hist_entity using (State, RegId, OrgId, HospId) where hist_entity.HospId is null |
| HospClosed | Historical | High | Yes | Hosp closed, historical $hist_name.qt (HospId: $hist_HospId) no longer exists $hist_context | ST | Hospital Closed - A historical Hospital was not found in current data | select hist_entity.State as State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.OrgId as hist_OrgId, hist_entity.HospId as hist_HospId, ' (' ||OrgName || '; ' || RegName || ')' as hist_context, hist_entity.HospName as hist_name from hist.HOSP as hist_entity left join main.HOSP using (State, RegId, OrgId, HospId) join hist.ORG using (State, RegId, OrgId) join hist.REG using (State, RegId) where HOSP.HospId is null |
| HospRenamed | Historical | High | No | Hosp renamed from $hist_name.qt to $HospName.qt | HOSP.HospName | Hospital Renamed - Hospital Name differs between historical and current data | select State, RegId, OrgId, HospId, HOSP.HospName, hist_entity.HospName as hist_name from HOSP join hist.HOSP as hist_entity using(State, RegId, OrgId, HospId) where not sloppy_match(HOSP.HospName, hist_entity.HospName) |
| ClusOpened | Historical | High | No | Clus opened, $ClusName.qt (ClusId: $ClusId) not in historical data | CLUS | Service Unit Cluster Opened - A matching Service Unit Cluster was not found in the historical data | select State, RegId, OrgId, ClusId, CLUS.ClusName from main.CLUS left join hist.CLUS as hist_entity using (State, RegId, OrgId, ClusId) where hist_entity.ClusId is null |
| ClusClosed | Historical | High | Yes | Clus closed, historical $hist_name.qt (ClusId: $hist_ClusId) no longer exists $hist_context | ST | Service Unit Cluster Closed - A historical Service Unit Cluster was not found in current data | select hist_entity.State as State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.OrgId as hist_OrgId, hist_entity.ClusId as hist_ClusId, ' (' ||OrgName || '; ' || RegName || ')' as hist_context, hist_entity.ClusName as hist_name from hist.CLUS as hist_entity left join main.CLUS using (State, RegId, OrgId, ClusId) join hist.ORG using (State, RegId, OrgId) join hist.REG using (State, RegId) where CLUS.ClusId is null |
| ClusRenamed | Historical | High | No | Clus renamed from $hist_name.qt to $ClusName.qt | CLUS.ClusName | Service Unit Cluster Renamed - Service Unit Cluster Name differs between historical and current data | select State, RegId, OrgId, ClusId, CLUS.ClusName, hist_entity.ClusName as hist_name from CLUS join hist.CLUS as hist_entity using(State, RegId, OrgId, ClusId) where not sloppy_match(CLUS.ClusName, hist_entity.ClusName) |
| AdmiOpened | Historical | High | No | Admi opened, $AdmiName.qt (AdmiId: $AdmiId) not in historical data | ADMI | Admitted Patient Service Unit Opened - A matching Admitted Patient Service Unit was not found in the historical data | select State, RegId, OrgId, HospId, AdmiId, ADMI.AdmiName from main.ADMI left join hist.ADMI as hist_entity using (State, RegId, OrgId, HospId, AdmiId) where hist_entity.AdmiId is null |
| AdmiClosed | Historical | High | Yes | Admi closed, historical $hist_name.qt (AdmiId: $hist_AdmiId) no longer exists $hist_context | ST | Admitted Patient Service Unit Closed - A historical Admitted Patient Service Unit was not found in current data | select hist_entity.State as State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.OrgId as hist_OrgId, hist_entity.HospId as hist_HospId, hist_entity.AdmiId as hist_AdmiId, ' (' ||HospName || '; ' || OrgName || '; ' || RegName || ')' as hist_context, hist_entity.AdmiName as hist_name from hist.ADMI as hist_entity left join main.ADMI using (State, RegId, OrgId, HospId, AdmiId) join hist.HOSP using (State, RegId, OrgId, HospId) join hist.ORG using (State, RegId, OrgId) join hist.REG using (State, RegId) where ADMI.AdmiId is null |
| AdmiRenamed | Historical | High | No | Admi renamed from $hist_name.qt to $AdmiName.qt | ADMI.AdmiName | Admitted Patient Service Unit Renamed - Admitted Patient Service Unit Name differs between historical and current data | select State, RegId, OrgId, HospId, AdmiId, ADMI.AdmiName, hist_entity.AdmiName as hist_name from ADMI join hist.ADMI as hist_entity using(State, RegId, OrgId, HospId, AdmiId) where not sloppy_match(ADMI.AdmiName, hist_entity.AdmiName) |
| ResiOpened | Historical | High | No | Resi opened, $ResiName.qt (ResiId: $ResiId) not in historical data | RESI | Residential Service Unit Opened - A matching Residential Service Unit was not found in the historical data | select State, RegId, OrgId, ClusId, ResiId, RESI.ResiName from main.RESI left join hist.RESI as hist_entity using (State, RegId, OrgId, ClusId, ResiId) where hist_entity.ResiId is null |
| ResiClosed | Historical | High | Yes | Resi closed, historical $hist_name.qt (ResiId: $hist_ResiId) no longer exists $hist_context | ST | Residential Service Unit Closed - A historical Residential Service Unit was not found in current data | select hist_entity.State as State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.OrgId as hist_OrgId, hist_entity.ClusId as hist_ClusId, hist_entity.ResiId as hist_ResiId, ' (' ||ClusName || '; ' || OrgName || '; ' || RegName || ')' as hist_context, hist_entity.ResiName as hist_name from hist.RESI as hist_entity left join main.RESI using (State, RegId, OrgId, ClusId, ResiId) join hist.CLUS using (State, RegId, OrgId, ClusId) join hist.ORG using (State, RegId, OrgId) join hist.REG using (State, RegId) where RESI.ResiId is null |
| ResiRenamed | Historical | High | No | Resi renamed from $hist_name.qt to $ResiName.qt | RESI.ResiName | Residential Service Unit Renamed - Residential Service Unit Name differs between historical and current data | select State, RegId, OrgId, ClusId, ResiId, RESI.ResiName, hist_entity.ResiName as hist_name from RESI join hist.RESI as hist_entity using(State, RegId, OrgId, ClusId, ResiId) where not sloppy_match(RESI.ResiName, hist_entity.ResiName) |
| AmbuOpened | Historical | High | No | Ambu opened, $AmbuName.qt (AmbuId: $AmbuId) not in historical data | AMBU | Ambulatory Service Unit Opened - A matching Ambulatory Service Unit was not found in the historical data | select State, RegId, OrgId, ClusId, AmbuId, AMBU.AmbuName from main.AMBU left join hist.AMBU as hist_entity using (State, RegId, OrgId, ClusId, AmbuId) where hist_entity.AmbuId is null |
| AmbuClosed | Historical | High | Yes | Ambu closed, historical $hist_name.qt (AmbuId: $hist_AmbuId) no longer exists $hist_context | ST | Ambulatory Service Unit Closed - A historical Ambulatory Service Unit was not found in current data | select hist_entity.State as State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.OrgId as hist_OrgId, hist_entity.ClusId as hist_ClusId, hist_entity.AmbuId as hist_AmbuId, ' (' ||ClusName || '; ' || OrgName || '; ' || RegName || ')' as hist_context, hist_entity.AmbuName as hist_name from hist.AMBU as hist_entity left join main.AMBU using (State, RegId, OrgId, ClusId, AmbuId) join hist.CLUS using (State, RegId, OrgId, ClusId) join hist.ORG using (State, RegId, OrgId) join hist.REG using (State, RegId) where AMBU.AmbuId is null |
| AmbuRenamed | Historical | High | No | Ambu renamed from $hist_name.qt to $AmbuName.qt | AMBU.AmbuName | Ambulatory Service Unit Renamed - Ambulatory Service Unit Name differs between historical and current data | select State, RegId, OrgId, ClusId, AmbuId, AMBU.AmbuName, hist_entity.AmbuName as hist_name from AMBU join hist.AMBU as hist_entity using(State, RegId, OrgId, ClusId, AmbuId) where not sloppy_match(AMBU.AmbuName, hist_entity.AmbuName) |
| HospSectorChanged | Historical | High | No | Hospital Sector changed from $hist_Sector to $Sector | HOSP.Sector | Sector Changed - Sector value for Hospital differs between historical and current data | select State, RegId, OrgId, HospId, HOSP.Sector, hist_HOSP.Sector as hist_Sector from HOSP join hist.HOSP as hist_HOSP using(State, RegId, OrgId, HospId) where HOSP.Sector != hist_HOSP.Sector |
| AdmiTargetPopChanged | Historical | High | No | Admitted Patient Service Unit TargetPop changed from $hist_TargetPop to $TargetPop | ADMI.TargetPop | Target Population Changed - Target Population value for Admitted Patient Service Unit differs between historical and current data | select State, RegId, OrgId, HospId, AdmiId, ADMI.TargetPop, hist_ADMI.TargetPop as hist_TargetPop from ADMI join hist.ADMI as hist_ADMI using(State, RegId, OrgId, HospId, AdmiId) where ADMI.TargetPop != hist_ADMI.TargetPop |
| ResiTargetPopChanged | Historical | High | No | Residential Service Unit TargetPop changed from $hist_TargetPop to $TargetPop | RESI.TargetPop | Target Population Changed - Target Population value for Residential Service Unit differs between historical and current data | select State, RegId, OrgId, ClusId, ResiId, RESI.TargetPop, hist_RESI.TargetPop as hist_TargetPop from RESI join hist.RESI as hist_RESI using(State, RegId, OrgId, ClusId, ResiId) where RESI.TargetPop != hist_RESI.TargetPop |
| ResiSectorChanged | Historical | High | No | Residential Service Unit Sector changed from $hist_Sector to $Sector | RESI.Sector | Sector Changed - Sector value for Residential Service Unit differs between historical and current data | select State, RegId, OrgId, ClusId, ResiId, RESI.Sector, hist_RESI.Sector as hist_Sector from RESI join hist.RESI as hist_RESI using(State, RegId, OrgId, ClusId, ResiId) where RESI.Sector != hist_RESI.Sector |
| AmbuTargetPopChanged | Historical | High | No | Ambulatory Service Unit TargetPop changed from $hist_TargetPop to $TargetPop | AMBU.TargetPop | Target Population Changed - Target Population value for Ambulatory Service Unit differs between historical and current data | select State, RegId, OrgId, ClusId, AmbuId, AMBU.TargetPop, hist_AMBU.TargetPop as hist_TargetPop from AMBU join hist.AMBU as hist_AMBU using(State, RegId, OrgId, ClusId, AmbuId) where AMBU.TargetPop != hist_AMBU.TargetPop |
| AmbuSectorChanged | Historical | High | No | Ambulatory Service Unit Sector changed from $hist_Sector to $Sector | AMBU.Sector | Sector Changed - Sector value for Ambulatory Service Unit differs between historical and current data | select State, RegId, OrgId, ClusId, AmbuId, AMBU.Sector, hist_AMBU.Sector as hist_Sector from AMBU join hist.AMBU as hist_AMBU using(State, RegId, OrgId, ClusId, AmbuId) where AMBU.Sector != hist_AMBU.Sector |