SQL-211: Retrieve InjuryClaim¶

This page covers six related InjuryClaim reads in TaskService, all filtering by claim number: GetClaimId and GetAdditionalInfoTask use FirstOrDefault, IsValidClaim uses Any as an existence probe, and CreateTask, CreateTaskStateless, and UpdateTask use SingleOrDefault. All are issued through NHibernate LINQ against the InjuryClaim entity from Services/SAIF.Services/Claims/Tasks/TaskService.cs:363, 382, 421, 507, 593, 680.

Classification¶

Field	Value
SQL ID	SQL-211
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`TaskService`
Criteria property	N/A — ORM
Lifecycle method	`CreateTask, CreateTaskStateless, GetAdditionalInfoTask, GetClaimId, IsValidClaim, UpdateTask`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:claimNumber`	Input	string	N/A	(1)/(2) `claimNumber`; (4) `taskEdit.ClaimNumber`; (5) `task.ClaimNumber`; (6) `taskCopy.ClaimNumber`
`:claimNum`	Input	string	N/A	(3) `IsValidClaim`

Tables¶

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

LINQ statement¶

Source: Services/SAIF.Services/Claims/Tasks/TaskService.cs:363, Services/SAIF.Services/Claims/Tasks/TaskService.cs:382, Services/SAIF.Services/Claims/Tasks/TaskService.cs:421, Services/SAIF.Services/Claims/Tasks/TaskService.cs:507, Services/SAIF.Services/Claims/Tasks/TaskService.cs:593, Services/SAIF.Services/Claims/Tasks/TaskService.cs:680

// Services/SAIF.Services/Claims/Tasks/TaskService.cs:363
var claim = _unitOfWork.GetRepository<InjuryClaim>().Query()
    .FirstOrDefault(x => x.ClaimNumber == claimNumber);

// Services/SAIF.Services/Claims/Tasks/TaskService.cs:382
task.Claim = _unitOfWork.GetRepository<InjuryClaim>().Query()
    .FirstOrDefault(x => x.ClaimNumber == claimNumber);

// Services/SAIF.Services/Claims/Tasks/TaskService.cs:421
return _unitOfWork.GetRepository<InjuryClaim>().Query()
    .Any(x => x.ClaimNumber == claimNum);

// Services/SAIF.Services/Claims/Tasks/TaskService.cs:507
newTask.Claim = _unitOfWork.GetRepository<InjuryClaim>().Query()
    .SingleOrDefault(x => x.ClaimNumber == taskEdit.ClaimNumber);

// Services/SAIF.Services/Claims/Tasks/TaskService.cs:593
taskData.Claim = _unitOfWork.GetRepository<InjuryClaim>().Query()
    .SingleOrDefault(x => x.ClaimNumber == task.ClaimNumber);

// Services/SAIF.Services/Claims/Tasks/TaskService.cs:680
taskData.Claim = _unitOfWork.GetRepository<InjuryClaim>().Query()
    .SingleOrDefault(x => x.ClaimNumber == taskCopy.ClaimNumber);

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) L363 GetClaimId, (2) L382 GetAdditionalInfoTask — FirstOrDefault by claim number
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;

-- (3) L421 — IsValidClaim
SELECT * FROM (
  SELECT 1 FROM CL_INJURY_CLAIM IC WHERE IC.SAIF_CLAIM_NUM = :claimNum
) WHERE ROWNUM <= 1;

-- (4) L507 CreateTask, (5) L593 CreateTaskStateless, (6) L680 UpdateTask — SingleOrDefault by claim number
SELECT IC.<all 100 mapped CL_INJURY_CLAIM columns, as in (1)>
FROM   CL_INJURY_CLAIM IC
WHERE  IC.SAIF_CLAIM_NUM = :claimNumber;

Translation notes: - FirstOrDefault → Oracle10 dialect wraps the query in SELECT * FROM (...) WHERE ROWNUM <= 1. - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - Any(...) → existence probe (SELECT 1 ... WHERE ROWNUM <= 1); returns bool. - In (1), claim.Id is read in memory (L366). - newTask.Claim.PolicyNumber / taskData.Claim.PolicyNumber (L511, L597, L686) read the loaded POLICY_NUM column in memory and feed a separate Policy query documented on another page; POLICY is not joined here.

Operations and processes¶

TaskService.CreateTask — see Operations and processes
TaskService.CreateTaskStateless — see Operations and processes
TaskService.GetAdditionalInfoTask — see Operations and processes
TaskService.GetClaimId — see Operations and processes
TaskService.IsValidClaim — see Operations and processes
TaskService.UpdateTask — see Operations and processes

Business rules¶

SVC-BR-CLM-100 Claim must exist

Sources¶

Services/SAIF.Services/Claims/Tasks/TaskService.cs (L363, L382, L421, L507, L593, L680)