Skip to content

SQL-294: Retrieve RTWCase via CanAccessRTWClaim

This statement retrieves an RTWCase matching a given claim ID and RTW load code via NHibernate LINQ, executed from Services/SAIF.Services/Claims/RTW/RTWService.cs (L48) and materialized with SingleOrDefault. It backs CanAccessRTWClaim, which authorization checks use to confirm that a claim is a return-to-work case eligible for the current load code before granting access.

Classification

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

Parameters

Parameter Direction Type Max length Description
:claimId Input long N/A Claim identifier (x.Id == claimId)
:rtwLoadCode Input string N/A RTW load code (x.RtwLoadCode.Code == rtwLoadCode)

Tables

Table Access Columns used
RTW_CASE SELECT CLAIM_ID, CURRENT_CASE_STATUS_CODE, EMPLOYED_BY_OTHER_FLAG, EMPLOYMENT_TYPE_CODE, REFUSED_BJO_FLAG, RELEASE_STATUS_CODE, UNDOCUMENTED_WORKER_FLAG, UNEMPLOYED_REASON_CODE, UNION_WORKER_FLAG, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, UPDATE_NUM, POLICY_NUM, RTW_LOAD_CODE

LINQ statement

Source: Services/SAIF.Services/Claims/RTW/RTWService.cs:48

// Services/SAIF.Services/Claims/RTW/RTWService.cs:48-50
var rtwCase = UnitOfWork.GetRepository<RTWCase>().Query()
    .SingleOrDefault(x => x.Id == claimId
    && x.RtwLoadCode.Code == rtwLoadCode);

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
  RC.CLAIM_ID,
  RC.CURRENT_CASE_STATUS_CODE,
  RC.EMPLOYED_BY_OTHER_FLAG,
  RC.EMPLOYMENT_TYPE_CODE,
  RC.REFUSED_BJO_FLAG,
  RC.RELEASE_STATUS_CODE,
  RC.UNDOCUMENTED_WORKER_FLAG,
  RC.UNEMPLOYED_REASON_CODE,
  RC.UNION_WORKER_FLAG,
  RC.CREATE_USER,
  RC.CREATE_DATE,
  RC.UPDATE_USER,
  RC.UPDATE_DATE,
  RC.UPDATE_NUM,
  RC.POLICY_NUM,
  RC.RTW_LOAD_CODE
FROM RTW_CASE RC
WHERE RC.CLAIM_ID = :claimId
  AND RC.RTW_LOAD_CODE = :rtwLoadCode
-- NHibernate expects at most one row

Translation notes: - Runs only when currentUser.IsAuthorized("RTWExternalUpdate") (C# guard). - YesNoType flags: EMPLOYED_BY_OTHER_FLAG, REFUSED_BJO_FLAG, UNDOCUMENTED_WORKER_FLAG, UNION_WORKER_FLAG. - Single/SingleOrDefault → no row limit; NHibernate throws if more than one row is returned.

Operations and processes

Sources

  • Services/SAIF.Services/Claims/RTW/RTWService.cs (L48)