Main Content

CMHC version 04.20: Rules

Table detailing the rules for specification: CMHC
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
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
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
ServSUNameMissing Missing High No Missing data - SUName $SUName.q SERV.SUName Missing data - Service Unit Name (SUName) select State, RegId, OrgId, ClusId, SUId, SUName from SERV where SUName is null
ServSectorMissing Missing High No Missing data - Sector $Sector.q SERV.Sector Missing data - Service Unit Sector (Sector) select State, RegId, OrgId, ClusId, SUId, Sector from SERV where Sector is null
ServTargetPopMissing Missing High No Missing data - TargetPop $TargetPop.q SERV.TargetPop Missing data - Target Population (TargetPop) select State, RegId, OrgId, ClusId, SUId, TargetPop from SERV where TargetPop is null
PerSexMissing Missing High No Missing data - Sex $Sex.q PER.Sex Missing data - Sex (Sex) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, Sex from PER where Sex is null
PerDoBMissing Missing High No Missing data - DoB $DoB.q PER.DoB Missing data - Date of Birth (DoB) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, DoB from PER where DoB is null
PerDoBFlagMissing Missing High No Missing data - DoBFlag $DoBFlag.q PER.DoBFlag Missing data - Estimated Date of Birth Flag (DoBFlag) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, DoBFlag from PER where DoBFlag is null
PerIndigStMissing Missing High No Missing data - IndigSt $IndigSt.q PER.IndigSt Missing data - Indigenous Status (IndigSt) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, IndigSt from PER where IndigSt is null
ConContDtMissing Missing High No Missing data - ContDt $ContDt.q CON.ContDt Missing data - Service Contact Date (ContDt) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDt from CON where ContDt is null
ConLegalStMissing Missing High No Missing data - LegalSt $LegalSt.q CON.LegalSt Missing data - Mental Health Legal Status (LegalSt) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, LegalSt from CON where LegalSt is null
ConContDurMissing Missing High No Missing data - ContDur $ContDur.q CON.ContDur Missing data - Mental Health Service Contact Duration (ContDur) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur from CON where ContDur is null
ConContParticMissing Missing High No Missing data - ContPartic $ContPartic.q CON.ContPartic Missing data - Mental Health Service Contact-Patient/Client Participation Indicator (ContPartic) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContPartic from CON where ContPartic is null
ConContSessTypeMissing Missing High No Missing data - ContSessType $ContSessType.q CON.ContSessType Missing data - Mental Health Service Contact-Session Type (ContSessType) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContSessType from CON where ContSessType is null
ConMaritalStMissing Missing High No Missing data - MaritalSt $MaritalSt.q CON.MaritalSt Missing data - Marital Status (MaritalSt) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, MaritalSt from CON where MaritalSt is null
ConPersSLAMissing Missing High No Missing data - PersSLA $PersSLA.q CON.PersSLA Missing data - Area of Usual Residence (PersSLA) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, PersSLA from CON where PersSLA is null
PerCoBMissing Missing High No Missing data - CoB $CoB.q PER.CoB Missing data - Country of Birth (CoB) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, CoB from PER where CoB is null
ConDxPrincMissing Missing High Yes Missing data - DxPrinc $DxPrinc.q CON.DxPrinc Missing data - Principal Diagnosis (DxPrinc) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc from CON where DxPrinc is null
LowAge Anomaly Low Yes Age is less than 1 years ($Age) CON Age at Contact is less than 1 years select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, Age from ConAge where Age < 1
HighAge Anomaly Low Yes Age is greater than 110 years CON Age at Contact is greater than 110 years select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, Age from ConAge where Age > 110
LowAgeMarriageLow Anomaly Low Yes Age is 13 to 15 years and MaritalSt is $MaritalSt CON.MaritalSt Age at Contact is 13 to 15 years and Marital Status is not 1 or 6 (Never married or Not stated) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, Age, MaritalSt from CON join ConAge using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId) where Age < 16 and Age >= 13 and MaritalSt NOT IN ('1','6')
LowAgeMarriageHigh Anomaly Medium Yes Age is less than 13 years and MaritalSt is $MaritalSt CON.MaritalSt Age at Contact is less than 13 years and Marital Status is not 1 or 6 (Never married or Not stated) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, Age, MaritalSt from CON join ConAge using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId) where Age < 13 and MaritalSt NOT IN ('1','6')
BadSLAProp Anomaly Low No Greater than 5% ($prop.perc) of SLAs are invalid ($BadCount in total) HR.State Greater than 5% of SLAs (PersSLA) are invalid select State, count(*) as AllCount, bad.BadCount, sd_div_safe(bad.BadCount, count(*), 3) as prop from CON cross join ( select count(*) as BadCount from error join rule on (rule.id = error.rule) where rule.name = 'Domain' and error.field = 'PersSLA' ) as bad group by State, bad.BadCount having sd_div_safe(bad.BadCount, count(*), 3) > 0.05
SectorPrivate Invalid High No Sector is $Sector.qt (Private) SERV.Sector Service Unit Sector must be 1 (Public) select State, RegId, OrgId, ClusId, SUId, Sector from SERV where Sector = '2'
LowContDur Anomaly Low Yes Duration is less than 5 minutes but greater than 0 minutes ($ContDur) CON.ContDur Contact Duration is less than 5 minutes but greater than 0 minutes select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur from CON where ContDur > 0 and ContDur < 5
HighContDur Anomaly Low Yes Duration is greater than 300 minutes ($ContDur) CON.ContDur Contact Duration is greater than 300 minutes select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDur from CON where ContDur > 300
ContDtBeforeDoB Inconsistent High No ContDt ($ContDt.dmy) is before DoB ($DoB.dmy) CON.ContDt ContDt is before DoB select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, ContDt, DoB from CON join PER using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId) where DoB != '9999-09-09' and ContDt < DoB
ServConCountByMonth No select State, RegId, OrgId, ClusId, SUId, extract(month FROM ContDt) AS ContDtMonth, extract(year FROM ContDt) AS ContDtYear, count(*) as ContDtCount from SERV join CON using (State, RegId, OrgId, ClusId, SUId) group by State, RegId, OrgId, ClusId, SUId, ContDtMonth, ContDtYear
ServConCount No select State, RegId, OrgId, ClusId, SUId, sum(ContDtCount) as ServConCountTotal from ServConCountByMonth group by State, RegId, OrgId, ClusId, SUId
StConCount No select sum(ServConCountTotal) as StConCountTotal from ServConCount
PerDoBCount No select sum(DoBCount) as PerDoBCountTotal, avg(DoBCount) as PerDoBCountAvg from ( select count(*) as DoBCount from PER where DoB <> '9999-09-09' group by DoB ) as tmpperdobcount
HighIndigNonAustProp Anomaly High No Proportion of IndigSt not born in Australia is greater than 5% ($prop.perc) ORG Proportion of Indigenous Australians not born in Australia is greater than 5% select State, RegId, OrgId, prop from OrgIndigStPropNonAus where prop > 0.05
BadDxPrincF Inconsistent High No Principal Diagnosis ($DxPrinc) and Sex ($Sex) is not female CON.DxPrinc The following diagnosis codes should only apply to females: 'F53 ', 'F53.0 ', 'F53.1 ', 'F53.8 ', 'F53.9 ', 'F32.01', 'F32.11', 'F32.21', 'F32.31', 'F32.81', 'F32.91', 'O99.3 ', 'F52.5 ' select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc, Sex from CON join PER using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId) where DxPrinc in ('F53 ', 'F53.0 ', 'F53.1 ', 'F53.8 ', 'F53.9 ', 'F32.01', 'F32.11', 'F32.21', 'F32.31', 'F32.81', 'F32.91', 'O99.3 ', 'F52.5 ') and Sex != '2' and Sex != '3'
BadDxPrincM Inconsistent High No Principal Diagnosis ($DxPrinc) and Sex ($Sex) is not male CON.DxPrinc The following diagnosis codes should only apply to males: 'F52.4 ' select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc, Sex from CON join PER using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId) where DxPrinc in ('F52.4 ') and Sex != '1' and Sex != '3'
BadDxPrincPpm Inconsistent Low No Principal Diagnosis ($DxPrinc) and Age ($Age) not between 15 and 55 CON.DxPrinc The following diagnosis codes should only apply to ages 15-55: 'F53 ', 'F53.0 ', 'F53.1 ', 'F53.8 ', 'F53.9 ' select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc, Age from CON join ConAge using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId) where DxPrinc in ('F53 ', 'F53.0 ', 'F53.1 ', 'F53.8 ', 'F53.9 ') and Age not between 15 and 55
BadDxPrincAd Inconsistent Low No Principal Diagnosis ($DxPrinc) and Age ($Age) less than 15 CON.DxPrinc The following diagnosis codes should not apply to ages less than 15: 'F00.0 ', 'F00.1 ', 'F00.2 ', 'F00.9 ', 'F03 ', 'F52 ', 'F52.0 ', 'F52.1 ', 'F52.2 ', 'F52.3 ', 'F52.4 ', 'F52.5 ', 'F52.6 ', 'F52.7 ', 'F52.8 ', 'F52.9 ', 'F64.0 ', 'F64.1 ' select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc, Age from CON join ConAge using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId) where DxPrinc in ('F00.0 ', 'F00.1 ', 'F00.2 ', 'F00.9 ', 'F03 ', 'F52 ', 'F52.0 ', 'F52.1 ', 'F52.2 ', 'F52.3 ', 'F52.4 ', 'F52.5 ', 'F52.6 ', 'F52.7 ', 'F52.8 ', 'F52.9 ', 'F64.0 ', 'F64.1 ') and Age < 15
BadDxPrincCh Inconsistent Low No Principal Diagnosis ($DxPrinc) and Age ($Age) greater than 16 CON.DxPrinc The following diagnosis codes should not apply to ages greater than 16: 'F64.2 ' select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, DxPrinc, Age from CON join ConAge using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId) where DxPrinc in ('F64.2 ') and Age > 16
HighConCount Anomaly Low Yes Person has over 250 ($Count) contacts PER Person has over 250 contact records within a service unit select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, count(*) as Count from CON group by State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId having count(*) > 250
MedConDayCount Anomaly Low Yes Person has over 10 but less than 16 ($Count) contacts on one day ($ContDt.dmy); TotalContDur $TotalContDur mins PER Person has over 10 but less than 16 contact records within a service unit on a single day select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt, TotalContDur, Count from PER join ( select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt, sum(ContDur) as TotalContDur, count(*) as Count from CON group by State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt having count(*) > 10 and count(*) < 16 ) tmpperconcount using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId)
HighConDayCount Anomaly Medium No Person has 16 or more ($Count) contacts on one day ($ContDt.dmy); TotalContDur $TotalContDur mins PER Person has 16 or more contact records within a service unit on a single day select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt, TotalContDur, Count from PER join ( select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt, sum(ContDur) as TotalContDur, count(*) as Count from CON group by State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, ContDt having count(*) > 15 ) tmpperconcount using (State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId)
LowPartic Anomaly Low No Greater than 10% ($prop.perc) of Contacts have no client participation HR.State Greater than 10% of Contacts have no client participation (ContPartic is not '1') select State, prop from HrContParticProp where prop > 0.1
HighExtSLAProp Anomaly Low No Greater than 5% ($prop.perc) of Contacts have a PersSLA out of state HR.State Greater than 5% of Contacts a PersSLA out of state. (This check does not apply to ACT) select State, prop from HrPersSLAProp where State != '8' and prop > 0.05
HighEstDoBFlagProp Anomaly Low No Greater than 15% ($prop.perc) of Persons have an Estimated Date of Birth HR.State Greater than 15% of Persons have an Estimated Date of Birth select State, prop from HrDoBFlagPropRegistered where prop > 0.15
HighExtCoBProp Anomaly High No Greater than 50% ($prop.perc) of Persons have a CoB other than Australia HR.State Greater than 50% of Persons have a CoB other than Australia select State, prop from HrCoBPropNotAus where prop > 0.5
HighMissingLegalStProp Anomaly Low No Greater than 5% ($prop.perc) of Contacts have Missing as a legal status HR.State Greater than 5% of Contacts have Missing legal status select State, prop from HrLegalStPropRegistered where prop > 0.05
HighPersIdFlagProp Anomaly Low No Greater than 10% ($prop.perc) of Persons have a dummy PersId HR.State Greater than 10% of Persons have a dummy PersId select State, prop from HrPersIdFlagProp where prop > 0.1
PerSexDiffers Inconsistent High No Person has $attr_count values for Sex ($attr_vals) PER.Sex Person has multiple values for Sex (Sex) across one organisation select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, attr_count, attr_vals from PER join ( select State, RegId, OrgId, PersIdFlag, PersId, count(DISTINCT Sex) as attr_count, string_agg(DISTINCT Sex::TEXT, ',') as attr_vals from PER group by State, RegId, OrgId, PersIdFlag, PersId having count(DISTINCT Sex) > 1 ) as foo using (State, RegId, OrgId, PersIdFlag, PersId)
PerDoBDiffers Inconsistent High No Person has $attr_count values for DoB ($attr_vals) PER.DoB Person has multiple values for DoB (Date of Birth) across one organisation select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, attr_count, attr_vals from PER join ( select State, RegId, OrgId, PersIdFlag, PersId, count(DISTINCT DoB) as attr_count, string_agg(DISTINCT DoB::TEXT, ',') as attr_vals from PER group by State, RegId, OrgId, PersIdFlag, PersId having count(DISTINCT DoB) > 1 ) as foo using (State, RegId, OrgId, PersIdFlag, PersId)
PerDoBFlagDiffers Inconsistent High No Person has $attr_count values for DoBFlag ($attr_vals) PER.DoBFlag Person has multiple values for DoBFlag (Estimated Date of Birth Flag) across one organisation select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, attr_count, attr_vals from PER join ( select State, RegId, OrgId, PersIdFlag, PersId, count(DISTINCT DoBFlag) as attr_count, string_agg(DISTINCT DoBFlag::TEXT, ',') as attr_vals from PER group by State, RegId, OrgId, PersIdFlag, PersId having count(DISTINCT DoBFlag) > 1 ) as foo using (State, RegId, OrgId, PersIdFlag, PersId)
PerCoBDiffers Inconsistent High No Person has $attr_count values for CoB ($attr_vals) PER.CoB Person has multiple values for CoB (Country of Birth) across one organisation select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, attr_count, attr_vals from PER join ( select State, RegId, OrgId, PersIdFlag, PersId, count(DISTINCT CoB) as attr_count, string_agg(DISTINCT CoB::TEXT, ',') as attr_vals from PER group by State, RegId, OrgId, PersIdFlag, PersId having count(DISTINCT CoB) > 1 ) as foo using (State, RegId, OrgId, PersIdFlag, PersId)
PerIndigStDiffers Inconsistent High No Person has $attr_count values for IndigSt ($attr_vals) PER.IndigSt Person has multiple values for IndigSt (Indigenous Status) across one organisation select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, attr_count, attr_vals from PER join ( select State, RegId, OrgId, PersIdFlag, PersId, count(DISTINCT IndigSt) as attr_count, string_agg(DISTINCT IndigSt::TEXT, ',') as attr_vals from PER group by State, RegId, OrgId, PersIdFlag, PersId having count(DISTINCT IndigSt) > 1 ) as foo using (State, RegId, OrgId, PersIdFlag, PersId)
BadHrLegalSt12Counts Anomaly Medium No LegalSt '1' count ($Val1Count) exceeds '2' count ($Val2Count) HR Mental Health Legal Status (LegalSt) '1' (Involuntary patient) count exceeds '2' (Voluntary patient) count select State, Val1.Count as Val1Count, Val2.Count as Val2Count from HrLegalStInvolCount as Val1 join HrLegalStVolCount as Val2 using (State) where Val1.Count > Val2.Count
BadHrPersIdFlag10Counts Anomaly High No PersIdFlag '1' count ($Val1Count) exceeds '0' count ($Val0Count) HR Person Identifier Flag (PersIdFlag) '1' (Patient identifier is a "dummy" identifier) count exceeds '0' (Patient identifier is a genuine person identifier) count select State, Val1.Count as Val1Count, Val0.Count as Val0Count from HrPersIdFlagDummyCount as Val1 join HrPersIdFlagRealCount as Val0 using (State) where Val1.Count > Val0.Count
RegOpened Historical Medium 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 Medium No Reg closed, historical $hist_name.qt (RegId: $hist_RegId) no longer exists HR.State Region Closed - A historical Region was not found in current data select 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 Medium 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 Medium 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 Medium No Org closed, historical $hist_name.qt (OrgId: $hist_OrgId) no longer exists HR.State Organisation Closed - A historical Organisation was not found in current data select State, hist_entity.State as hist_State, hist_entity.RegId as hist_RegId, hist_entity.OrgId as hist_OrgId, hist_entity.OrgName as hist_name from hist.ORG as hist_entity left join main.ORG using (State, RegId, OrgId) where ORG.OrgId is null
OrgRenamed Historical Medium 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)
ClusOpened Historical Medium 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 Medium No Clus closed, historical $hist_name.qt (ClusId: $hist_ClusId) no longer exists HR.State Service Unit Cluster Closed - A historical Service Unit Cluster was not found in current data select 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.ClusName as hist_name from hist.CLUS as hist_entity left join main.CLUS using (State, RegId, OrgId, ClusId) where CLUS.ClusId is null
ClusRenamed Historical Medium 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)
ServOpened Historical Medium No Serv opened, $SUName.qt (SUId: $SUId) not in historical data SERV Service Unit Opened - A matching Service Unit was not found in the historical data select State, RegId, OrgId, ClusId, SUId, SERV.SUName from main.SERV left join hist.SERV as hist_entity using (State, RegId, OrgId, ClusId, SUId) where hist_entity.SUId is null
ServClosed Historical Medium No Serv closed, historical $hist_name.qt (SUId: $hist_SUId) no longer exists HR.State Service Unit Closed - A historical Service Unit was not found in current data select 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.SUId as hist_SUId, hist_entity.SUName as hist_name from hist.SERV as hist_entity left join main.SERV using (State, RegId, OrgId, ClusId, SUId) where SERV.SUId is null
ServRenamed Historical Medium No Serv renamed from $hist_name.qt to $SUName.qt SERV.SUName Service Unit Renamed - Service Unit Name differs between historical and current data select State, RegId, OrgId, ClusId, SUId, SERV.SUName, hist_entity.SUName as hist_name from SERV join hist.SERV as hist_entity using(State, RegId, OrgId, ClusId, SUId) where not sloppy_match(SERV.SUName, hist_entity.SUName)
ServTargetPopChanged Historical High No Service Unit TargetPop changed from $hist_TargetPop to $TargetPop SERV.TargetPop Target Population Changed - Target Population value for Service Unit differs between historical and current data select State, RegId, OrgId, ClusId, SUId, SERV.TargetPop, hist_SERV.TargetPop as hist_TargetPop from SERV join hist.SERV as hist_SERV using(State, RegId, OrgId, ClusId, SUId) where SERV.TargetPop != hist_SERV.TargetPop
ConInvolAndUnreg Inconsistent High Yes PersIdFlag is 1 (dummy) and LegalSt is 1 (involuntary) CON.LegalSt CON record with a dummy PersId (PersIdFlag 1) has an Involuntary legal status (LegalSt 1). Clients with an Involuntary legal status should be registered. PersIdFlag 1: Patient identifier is a "dummy" identifier LegalSt 1: Involuntary patient select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, RecordId, LegalSt from CON where PersIdFlag = '1' and LegalSt = '1'
PerDoBFlagAndDoB Inconsistent High Yes DoBFlag is 8 and DoB is not 09099999 ($BadDoB.ddmmyyyy) PER.DoB If date of birth flag (DoBFlag) is '8' (dummy date), date of birth (DoB) must be '09099999'. DoBFlag 8: Date of birth is a "dummy" date (ie, 09099999) select State, RegId, OrgId, ClusId, SUId, PersIdFlag, PersId, DoBFlag, DoB as BadDoB from PER where DoBFlag = '8' and DoB != '9999-09-09'
RegNotInSkl Skeleton High No Reg $name not in SKL data Region not in skeleton reference data - A matching Ambulatory Region was not found in the skeleton data
RegInSklOnly Skeleton High No Reg $name expected from SKL is missing Region appears in skeleton reference data only - A Region with matching Ids is expected based on the SKL data but is not present in this file
OrgNotInSkl Skeleton High No Org $name not in SKL data Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data
OrgInSklOnly Skeleton High No Org $name expected from SKL is missing Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file
OrgCANotInSkl Skeleton High No Org $name not in SKL data (TargetPop: CA) Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: CA)
OrgCAInSklOnly Skeleton High No Org $name not in SKL data (TargetPop: CA) Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file (TargetPop: CA)
OrgOldNotInSkl Skeleton High No Org $name not in SKL data (TargetPop: Old) Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: Old)
OrgOldInSklOnly Skeleton High No Org $name not in SKL data (TargetPop: Old) Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file (TargetPop: Old)
OrgYthNotInSkl Skeleton High No Org $name not in SKL data (TargetPop: Yth) Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: Yth)
OrgYthInSklOnly Skeleton High No Org $name not in SKL data (TargetPop: Yth) Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file (TargetPop: Yth)
OrgForNotInSkl Skeleton High No Org $name not in SKL data (TargetPop: For) Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: For)
OrgForInSklOnly Skeleton High No Org $name not in SKL data (TargetPop: For) Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file (TargetPop: For)
OrgGenNotInSkl Skeleton High No Org $name not in SKL data (TargetPop: Gen) Organisation not in skeleton reference data - A matching Ambulatory Organisation was not found in the skeleton data (TargetPop: Gen)
OrgGenInSklOnly Skeleton High No Org $name not in SKL data (TargetPop: Gen) Organisation appears in skeleton reference data only - A Organisation with matching Ids is expected based on the SKL data but is not present in this file (TargetPop: Gen)