Skip to content

SQL-208: Retrieve InjuryClaim via GetClaimWorkerName

This statement loads a single InjuryClaim record by its claim identifier so that ClaimNotesService.GetClaimWorkerName can read the worker's name from the claim's Worker reference. It is issued through NHibernate LINQ against the InjuryClaim entity from Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:369.

Classification

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

Parameters

Parameter Direction Type Max length Description
:claimId Input long N/A x.Id == claimId

Tables

Table Access Columns used
CL_INJURY_CLAIM SELECT all 100 mapped columns (entity load); filter CLAIM_ID

LINQ statement

Source: Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:369

var claim = UnitOfWork.GetRepository<InjuryClaim>().Query().SingleOrDefault(x => x.Id == claimId);

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 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.CLAIM_ID = :claimId;

Translation notes: - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - claim.Worker.PrimaryFirstName/MiddleName/LastName (L373-375) are read in memory after the query; they trigger a separate lazy proxy load of Worker by WORKER_ID (InjuryClaimMap.cs:121) that is not part of this statement. - claim.ClaimNumber + claim.SaifClaimCheckCharacter (L376) are columns already loaded (SAIF_CLAIM_NUM, SAIF_CLAIM_CHK_VALUE).

Operations and processes

Sources

  • Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs (L369)