---
title: "SQL-297: Retrieve SecurityOverride"
summary: Read operation against SecurityOverride via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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](../database-tables.md#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`

```csharp
// 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.

```sql
-- 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](../operations-processes.md)
- SecurityOverrideService.GetSecurityOverrideById — see [Operations and processes](../operations-processes.md)
- SecurityOverrideService.GetSecurityOverrides — see [Operations and processes](../operations-processes.md)
- SecurityOverrideService.UpdateOverride — see [Operations and processes](../operations-processes.md)

## Business rules
- [SVC-BR-CLM-142 Claim must be valid](../business-rules.md#svc-br-clm-142-claim-must-be-valid)

## Sources
- `Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs (L43, L50, L137, L160)`