SQL-060: ClaimQueryProvider.GetClaimsMissingTaxId¶

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by ClaimQueryProvider.GetClaimsMissingTaxId.

Classification¶

Field	Value
SQL ID	SQL-060
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle*
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`ClaimQueryProvider`
Criteria property	N/A — ORM
Lifecycle method	`GetClaimsMissingTaxId`

Parameters¶

Parameter	Direction	Type	Max length	Description
`lookbackDate`	Input	DateTime	N/A	Method argument of `ClaimQueryProvider.GetClaimsMissingTaxId` bound into the ORM expression; NHibernate generates the bind variable at runtime

Tables¶

Table	Access	Columns used
CL_INJURY_CLAIM	SELECT	all 100 mapped `InjuryClaim` columns listed in the Sample SQL below; filter on RECEIVED_DATE; join on WORKER_ID
CL_CLAIM_PERSON	JOIN (implicit, via `Worker`)	PERSON_ID, PRIMARY_TAX_ID

LINQ statement¶

Source: Data/SAIF.Data/Queries/Claims/ClaimQueryProvider.cs:229-232. The repository is a class field, claimRepo, initialised from Factory.GetRepository<InjuryClaim>().Query() at Data/SAIF.Data/Queries/Claims/ClaimQueryProvider.cs:39.

var claimsMissingSSN = claimRepo.Where(injuryClaim =>
    injuryClaim.ReceivedDate > lookbackDate
    && (injuryClaim.Worker.PrimaryTaxId == " ")
    ).ToList();

Sample SQL¶

Inferred SQL: This SQL was inferred from the LINQ statement above and the NHibernate mappings. It is not concrete SQL from source code; NHibernate generates the executed statement at runtime, so aliases, column order, join syntax, and bind-parameter names may differ.

-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
SELECT
  CIC.CLAIM_ID,
  CIC.UPDATE_NUM,
  CIC.SAIF_CLAIM_NUM,
  CIC.SAIF_CLAIM_CHK_VALUE,
  CIC.WCD_CLAIM_NUM,
  CIC.OWCP_CLAIM_NUM,
  CIC.UPDATE_DATE,
  CIC.UPDATE_USER,
  CIC.RECEIVED_DATE,
  CIC.RECEIVED_801_DATE,
  CIC.TIN_AUTHORIZED_USE_FLAG,
  CIC.AGE_DOI_YEARS,
  CIC.GED_FLAG_CODE,
  CIC.EDUCATION_YEARS,
  CIC.EMPR_RELATIVE_FLAG,
  CIC.INTERESTED_PARTY_FLAG_CODE,
  CIC.WORKER_DEPARTMENT_DESCR,
  CIC.EMPR_PROVIDED_WORKER_AREA_TXT,
  CIC.EMPR_PROV_WORKER_DEPT_DESCR,
  CIC.EMPR_PROV_PAYROLL_CLASS_CODE,
  CIC.EMPR_PROV_PAYROLL_SUB_CODE,
  CIC.EMPR_PROVIDED_SHIFT_CODE,
  CIC.SHIFT_START_TIME,
  CIC.SHIFT_END_TIME,
  CIC.SAIF_PROV_PAYROLL_CLASS_CODE,
  CIC.SAIF_PROV_PAYROLL_SUB_CODE,
  CIC.WORKER_TENURE_DAY,
  CIC.WORKER_TENURE_MONTH,
  CIC.WORKER_TENURE_YEAR,
  CIC.INPATIENT_FLAG_CODE,
  CIC.INITIAL_TREATMENT_DATE,
  CIC.EMPLOYMENT_INJURY_CODE,
  CIC.HANDICAP_WORKER_RESERVE_FLAG,
  CIC.MULTIPLE_WORKER_FLAG_CODE,
  CIC.NCE_DATA_FLAG,
  CIC.KNOWLEDGE_DATE,
  CIC.INMATE_NUM,
  CIC.INMATE_SUPERVISOR_KNOW_DATE,
  CIC.DISABILITY_DATE,
  CIC.WORKER_HIRE_DATE,
  CIC.LEAVE_WORK_TIME,
  CIC.POLICY_SUB_CODE,
  CIC.CDA_FLAG,
  CIC.LAST_RESERVE_REVIEW_DATE,
  CIC.LAST_RESERVE_REVIEW_USER,
  CIC.LAST_VOC_RESERVE_REVIEW_DATE,
  CIC.LAST_VOC_RESERVE_REVIEW_USER,
  CIC.AWARD_AUTO_RECALCULATION_FL_CD,
  CIC.WORKER_OCCUPATION_DESCR,
  CIC.ACTIVE_CLAIM_FLAG,
  CIC.INJURY_DATE,
  CIC.INJURY_TIME,
  CIC.HARDWARE_CAUSED_FLAG_CODE,
  CIC.PERSON_CAUSED_FLAG_CODE,
  CIC.CITY_NAME,
  CIC.STATE_CODE,
  CIC.ZIP_CODE,
  CIC.ZIP_CODE_EXT,
  CIC.HIGH_SEAS_FLAG,
  CIC.SAIF_EVENT_DESCR,
  CIC.HEALTH_INSURANCE_PROV_NAME,
  CIC.HEALTH_INSURANCE_ID,
  CIC.HEALTH_INSURANCE_GROUP_NUM,
  CIC.MULTIPLE_EMPR_FLAG,
  CIC.MULTIPLE_EMPR_WAGE_AMT,
  CIC.LEAVE_WORK_DATE,
  CIC.COUNTY_CODE,
  CIC.FEDERAL_8F_FLAG,
  CIC.CDA_ISSUE_DATE,
  CIC.DCS_ISSUE_DATE,
  CIC.MEDICARE_ORM_TERM_DATE,
  CIC.PREFERRED_WORKER_ID,
  CIC.OUTSTANDING_RESERVE_FLAG,
  CIC.EXTERNAL_VIEW_FLAG,
  CIC.FULLY_FRAUDULENT_FLAG,
  CIC.DENIED_FINAL_FLAG,
  CIC.POLICY_NUM,
  CIC.CLAIM_RESP_CODE,
  CIC.COMPENSABILITY_RESP_CODE,
  CIC.FORM_CODE,
  CIC.CLAIM_STATUS_CODE,
  CIC.CLAIM_TYPE_CODE,
  CIC.JURISDICTION_CODE,
  CIC.PRIMARY_BODY_PART_CODE,
  CIC.PRIMARY_BODY_SIDE_CODE,
  CIC.PRIMARY_INJURY_NATURE_CODE,
  CIC.COVERAGE_CODE,
  CIC.DCS_FULL_PART_CODE,
  CIC.FEDERAL_INJURY_LOCATION_CODE,
  CIC.OWCP_SERVICE_OFFICE_CODE,
  CIC.INJURED_ON_PREMISES_CODE,
  CIC.INJURY_EVENT_CODE,
  CIC.INJURY_SOURCE_CODE,
  CIC.INJ_OCCUPATIONAL_DISEASE_CODE,
  CIC.REQUEST_TYPE_CODE,
  CIC.MCO_ELIGIBILITY_REASON_CODE,
  CIC.MCO_ELIGIBILITY_STATUS_CODE,
  CIC.VOCATIONAL_RESP_CODE,
  CIC.WORKER_ID,
  CIC.MCO_ID
FROM CL_INJURY_CLAIM CIC
  INNER JOIN CL_CLAIM_PERSON CCP ON CCP.PERSON_ID = CIC.WORKER_ID
WHERE CIC.RECEIVED_DATE > :lookbackDate
  AND CCP.PRIMARY_TAX_ID = ' '

Translation notes: - injuryClaim.Worker.PrimaryTaxId is a non-identifier property of the Worker key-reference, so this navigation is not short-circuited: it produces a real INNER JOIN CL_CLAIM_PERSON CCP ON CCP.PERSON_ID = CIC.WORKER_ID. - A single space ' ' is the literal compared. Rows whose PRIMARY_TAX_ID is NULL are not returned. - Only the CL_CLAIM_PERSON columns used in the predicate are read. The Worker entity itself is not eagerly fetched; it is lazy-loaded if touched later.

Operations and processes¶

Claim data-quality queries

Business rules¶

SD-BR-049 Missing tax id identified by a single space

Sources¶

Data/SAIF.Data/Queries/Claims/ClaimQueryProvider.cs:229