SQL-210: Retrieve InjuryClaim¶

This page covers three related InjuryClaim reads in SecurityOverrideService: GetClaimData(string) looks up a claim by claim number, GetClaimData(long) looks up a claim by identifier, and IsValidClaim probes for the existence of a claim by claim number (only run when the claim number is non-null). All three are issued through NHibernate LINQ against the InjuryClaim entity from Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:175, 179, 183.

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`:claimNumber`	Input	string	N/A	(1) `GetClaimData(string)`
`:claimId`	Input	long	N/A	(2) `GetClaimData(long)`
`:claimNumUpper`	Input	string	N/A	(3) `claimNum.ToUpper()`, evaluated client-side because it does not reference `x`

Tables¶

Table	Access	Columns used
CL_INJURY_CLAIM	SELECT	all 100 mapped columns (entity load) for (1)-(2); (3) probes existence only; filter on CLAIM_ID / SAIF_CLAIM_NUM

LINQ statement¶

Source: Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:175, Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:179, Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:183

// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:175
return unitOfWork.GetRepository<InjuryClaim>().Query().FirstOrDefault(x => x.ClaimNumber == claimNumber);

// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:179
return unitOfWork.GetRepository<InjuryClaim>().Query().FirstOrDefault(x => x.Id == claimId);

// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:183
return (claimNum != null
        && unitOfWork.GetRepository<InjuryClaim>().Query()
       .Any(x => x.ClaimNumber == claimNum.ToUpper()));

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
-- (1) L175 — GetClaimData(string)
SELECT * FROM (
  SELECT IC.CLAIM_ID,
       IC.UPDATE_NUM,
       IC.SAIF_CLAIM_NUM,
       IC.SAIF_CLAIM_CHK_VALUE,
       IC.WCD_CLAIM_NUM,
       IC.OWCP_CLAIM_NUM,
       IC.UPDATE_DATE,
       IC.UPDATE_USER,
       IC.RECEIVED_DATE,
       IC.RECEIVED_801_DATE,
       IC.TIN_AUTHORIZED_USE_FLAG,
       IC.AGE_DOI_YEARS,
       IC.GED_FLAG_CODE,
       IC.EDUCATION_YEARS,
       IC.EMPR_RELATIVE_FLAG,
       IC.INTERESTED_PARTY_FLAG_CODE,
       IC.WORKER_DEPARTMENT_DESCR,
       IC.EMPR_PROVIDED_WORKER_AREA_TXT,
       IC.EMPR_PROV_WORKER_DEPT_DESCR,
       IC.EMPR_PROV_PAYROLL_CLASS_CODE,
       IC.EMPR_PROV_PAYROLL_SUB_CODE,
       IC.EMPR_PROVIDED_SHIFT_CODE,
       IC.SHIFT_START_TIME,
       IC.SHIFT_END_TIME,
       IC.SAIF_PROV_PAYROLL_CLASS_CODE,
       IC.SAIF_PROV_PAYROLL_SUB_CODE,
       IC.WORKER_TENURE_DAY,
       IC.WORKER_TENURE_MONTH,
       IC.WORKER_TENURE_YEAR,
       IC.INPATIENT_FLAG_CODE,
       IC.INITIAL_TREATMENT_DATE,
       IC.EMPLOYMENT_INJURY_CODE,
       IC.HANDICAP_WORKER_RESERVE_FLAG,
       IC.MULTIPLE_WORKER_FLAG_CODE,
       IC.NCE_DATA_FLAG,
       IC.KNOWLEDGE_DATE,
       IC.INMATE_NUM,
       IC.INMATE_SUPERVISOR_KNOW_DATE,
       IC.DISABILITY_DATE,
       IC.WORKER_HIRE_DATE,
       IC.LEAVE_WORK_TIME,
       IC.POLICY_SUB_CODE,
       IC.CDA_FLAG,
       IC.LAST_RESERVE_REVIEW_DATE,
       IC.LAST_RESERVE_REVIEW_USER,
       IC.LAST_VOC_RESERVE_REVIEW_DATE,
       IC.LAST_VOC_RESERVE_REVIEW_USER,
       IC.AWARD_AUTO_RECALCULATION_FL_CD,
       IC.WORKER_OCCUPATION_DESCR,
       IC.ACTIVE_CLAIM_FLAG,
       IC.INJURY_DATE,
       IC.INJURY_TIME,
       IC.HARDWARE_CAUSED_FLAG_CODE,
       IC.PERSON_CAUSED_FLAG_CODE,
       IC.CITY_NAME,
       IC.STATE_CODE,
       IC.ZIP_CODE,
       IC.ZIP_CODE_EXT,
       IC.HIGH_SEAS_FLAG,
       IC.SAIF_EVENT_DESCR,
       IC.HEALTH_INSURANCE_PROV_NAME,
       IC.HEALTH_INSURANCE_ID,
       IC.HEALTH_INSURANCE_GROUP_NUM,
       IC.MULTIPLE_EMPR_FLAG,
       IC.MULTIPLE_EMPR_WAGE_AMT,
       IC.LEAVE_WORK_DATE,
       IC.COUNTY_CODE,
       IC.FEDERAL_8F_FLAG,
       IC.CDA_ISSUE_DATE,
       IC.DCS_ISSUE_DATE,
       IC.MEDICARE_ORM_TERM_DATE,
       IC.PREFERRED_WORKER_ID,
       IC.OUTSTANDING_RESERVE_FLAG,
       IC.EXTERNAL_VIEW_FLAG,
       IC.FULLY_FRAUDULENT_FLAG,
       IC.DENIED_FINAL_FLAG,
       IC.POLICY_NUM,
       IC.CLAIM_RESP_CODE,
       IC.COMPENSABILITY_RESP_CODE,
       IC.FORM_CODE,
       IC.CLAIM_STATUS_CODE,
       IC.CLAIM_TYPE_CODE,
       IC.JURISDICTION_CODE,
       IC.PRIMARY_BODY_PART_CODE,
       IC.PRIMARY_BODY_SIDE_CODE,
       IC.PRIMARY_INJURY_NATURE_CODE,
       IC.COVERAGE_CODE,
       IC.DCS_FULL_PART_CODE,
       IC.FEDERAL_INJURY_LOCATION_CODE,
       IC.OWCP_SERVICE_OFFICE_CODE,
       IC.INJURED_ON_PREMISES_CODE,
       IC.INJURY_EVENT_CODE,
       IC.INJURY_SOURCE_CODE,
       IC.INJ_OCCUPATIONAL_DISEASE_CODE,
       IC.REQUEST_TYPE_CODE,
       IC.MCO_ELIGIBILITY_REASON_CODE,
       IC.MCO_ELIGIBILITY_STATUS_CODE,
       IC.VOCATIONAL_RESP_CODE,
       IC.WORKER_ID,
       IC.MCO_ID
  FROM   CL_INJURY_CLAIM IC
  WHERE  IC.SAIF_CLAIM_NUM = :claimNumber
) WHERE ROWNUM <= 1;

-- (2) L179 — GetClaimData(long)
SELECT * FROM (
  SELECT IC.<all 100 mapped CL_INJURY_CLAIM columns, as in (1)>
  FROM   CL_INJURY_CLAIM IC
  WHERE  IC.CLAIM_ID = :claimId
) WHERE ROWNUM <= 1;

-- (3) L184 — IsValidClaim (only when claimNum != null; short-circuit in C#)
SELECT * FROM (
  SELECT 1
  FROM   CL_INJURY_CLAIM IC
  WHERE  IC.SAIF_CLAIM_NUM = :claimNumUpper
) WHERE ROWNUM <= 1;

Translation notes: - FirstOrDefault → Oracle10 dialect wraps the query in SELECT * FROM (...) WHERE ROWNUM <= 1. - Any(...) → existence probe (SELECT 1 ... WHERE ROWNUM <= 1); returns bool. - In (3), claimNum != null && is a C# short-circuit: when it is null no SQL is issued.

Operations and processes¶

SecurityOverrideService.GetClaimData — see Operations and processes
SecurityOverrideService.IsValidClaim — see Operations and processes

Sources¶

Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs (L175, L179, L184)