SQL-297: Retrieve SecurityOverride¶

This statement retrieves SecurityOverride rows from four call sites in SecurityOverrideService, all via NHibernate LINQ against CL_SECURITY_OVERRIDE: a single-row lookup by ID (GetSecurityOverrideById, UpdateOverride), a full or filtered list read (GetSecurityOverrides), and a single-row lookup by ID before deletion (DeleteOverride). These reads back the retrieval, update, and deletion of claim security overrides that grant or restrict access to a claim.

Classification¶

Field	Value
SQL ID	SQL-297
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	`GetSecurityOverrideById`, `GetSecurityOverrides`, `UpdateOverride`, `DeleteOverride`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:id`	Input	long	N/A	`x.Id == id` (`GetSecurityOverrideById`, `UpdateOverride`, `DeleteOverride`)
`:today`	Input	DateTime	N/A	`x.ExpDate >= DateTime.Today` (`GetSecurityOverrides`, only when `!includeExpired`)

Tables¶

Table	Access	Columns used
CL_SECURITY_OVERRIDE	SELECT	`CL_OVERRIDE_SECURITY_SEQ_NUM`, `CLAIM_ID`, `EMPLID`, `EFF_DATE`, `EXP_DATE`, `WORKER_CONFLICT_FLAG`, `CREATE_DATE`, `CREATE_USER`, `UPDATE_DATE`, `UPDATE_USER`, `UPDATE_NUM`, `HOSTILE_WORKER_CODE`

LINQ statement¶

Source: Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:43

// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:43
var secOverride = Mapper.Map<Core.Contracts.Claims.SecurityOverrides.SecurityOverride>(unitOfWork.GetRepository<Data.Model.Claims.SecurityOverrides.SecurityOverride>().Query().FirstOrDefault(x => x.Id == id));

// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:50-53
var query = unitOfWork.GetRepository<Data.Model.Claims.SecurityOverrides.SecurityOverride>().Query();
if (!includeExpired)
    query = query.Where(x => x.ExpDate >= DateTime.Today);
var list = Mapper.Map<Core.Contracts.Claims.SecurityOverrides.SecurityOverride[]>(query);

// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:137
var origData = unitOfWork.GetRepository<Data.Model.Claims.SecurityOverrides.SecurityOverride>().Query().FirstOrDefault(x => x.Id == id);

// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:160-161
var securityOverride = unitOfWork.GetRepository<Data.Model.Claims.SecurityOverrides.SecurityOverride>().Query()
           .SingleOrDefault(x => x.Id == id);

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
-- GetSecurityOverrideById (L43) and UpdateOverride (L137): FirstOrDefault
SELECT *
FROM (
  SELECT
    CSO.CL_OVERRIDE_SECURITY_SEQ_NUM,
    CSO.CLAIM_ID,
    CSO.EMPLID,
    CSO.EFF_DATE,
    CSO.EXP_DATE,
    CSO.WORKER_CONFLICT_FLAG,
    CSO.CREATE_DATE,
    CSO.CREATE_USER,
    CSO.UPDATE_DATE,
    CSO.UPDATE_USER,
    CSO.UPDATE_NUM,
    CSO.HOSTILE_WORKER_CODE
  FROM CL_SECURITY_OVERRIDE CSO
  WHERE CSO.CL_OVERRIDE_SECURITY_SEQ_NUM = :id
)
WHERE ROWNUM <= 1

-- DeleteOverride (L160): SingleOrDefault
SELECT
  CSO.CL_OVERRIDE_SECURITY_SEQ_NUM,
  CSO.CLAIM_ID,
  CSO.EMPLID,
  CSO.EFF_DATE,
  CSO.EXP_DATE,
  CSO.WORKER_CONFLICT_FLAG,
  CSO.CREATE_DATE,
  CSO.CREATE_USER,
  CSO.UPDATE_DATE,
  CSO.UPDATE_USER,
  CSO.UPDATE_NUM,
  CSO.HOSTILE_WORKER_CODE
FROM CL_SECURITY_OVERRIDE CSO
WHERE CSO.CL_OVERRIDE_SECURITY_SEQ_NUM = :id
-- NHibernate expects at most one row

-- GetSecurityOverrides (L50), includeExpired = true: enumerated by Mapper.Map at L53
SELECT
  CSO.CL_OVERRIDE_SECURITY_SEQ_NUM,
  CSO.CLAIM_ID,
  CSO.EMPLID,
  CSO.EFF_DATE,
  CSO.EXP_DATE,
  CSO.WORKER_CONFLICT_FLAG,
  CSO.CREATE_DATE,
  CSO.CREATE_USER,
  CSO.UPDATE_DATE,
  CSO.UPDATE_USER,
  CSO.UPDATE_NUM,
  CSO.HOSTILE_WORKER_CODE
FROM CL_SECURITY_OVERRIDE CSO

-- GetSecurityOverrides (L50-52), includeExpired = false
SELECT
  CSO.CL_OVERRIDE_SECURITY_SEQ_NUM,
  CSO.CLAIM_ID,
  CSO.EMPLID,
  CSO.EFF_DATE,
  CSO.EXP_DATE,
  CSO.WORKER_CONFLICT_FLAG,
  CSO.CREATE_DATE,
  CSO.CREATE_USER,
  CSO.UPDATE_DATE,
  CSO.UPDATE_USER,
  CSO.UPDATE_NUM,
  CSO.HOSTILE_WORKER_CODE
FROM CL_SECURITY_OVERRIDE CSO
WHERE CSO.EXP_DATE >= :today

Translation notes: - L52 conditionally appends Where(x => x.ExpDate >= DateTime.Today) when !includeExpired; both shapes shown. - WORKER_CONFLICT_FLAG is YesNoType. - First/FirstOrDefault → Oracle10 dialect row limit SELECT * FROM (...) WHERE ROWNUM <= 1. - Single/SingleOrDefault → no row limit; NHibernate throws if more than one row is returned. - The Delete at L165-166 is a separate write statement.

Operations and processes¶

SecurityOverrideService.DeleteOverride — see Operations and processes
SecurityOverrideService.GetSecurityOverrideById — see Operations and processes
SecurityOverrideService.GetSecurityOverrides — see Operations and processes
SecurityOverrideService.UpdateOverride — see Operations and processes

Business rules¶

SVC-BR-CLM-142 Claim must be valid

Sources¶

Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs (L43, L50, L137, L160)