SQL-073: ClaimSecurityOverrideProvider.HasClaimSecurityOverride¶

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by ClaimSecurityOverrideProvider.HasClaimSecurityOverride.

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`claimId`	Input	long	N/A	Method argument of `ClaimSecurityOverrideProvider.HasClaimSecurityOverride` bound into the ORM expression; NHibernate generates the bind variable at runtime
`employeeId`	Input	string	N/A	Method argument of `ClaimSecurityOverrideProvider.HasClaimSecurityOverride` bound into the ORM expression; NHibernate generates the bind variable at runtime

Tables¶

Table	Access	Columns used
CL_SECURITY_OVERRIDE	SELECT (existence check)	CLAIM_ID, EMPLID, EFF_DATE, EXP_DATE

LINQ statement¶

Source: Data/SAIF.Data/Queries/Claims/ClaimSecurityOverrideProvider.cs:18-23. The repository is a class field initialised from Factory.GetRepository<SecurityOverride>() at Data/SAIF.Data/Queries/Claims/ClaimSecurityOverrideProvider.cs:13.

var securityOverride = repository
   .Where(x => x.ClaimId == claimId)
   .Where(x => x.EmplId == employeeId)
   .Where(x => x.EffDate <= DateTime.Today)
   .Where(x => x.ExpDate >= DateTime.Today);
return securityOverride.Any();

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 1
FROM CL_SECURITY_OVERRIDE CSO
WHERE CSO.CLAIM_ID = :claimId
  AND CSO.EMPLID = :employeeId
  AND CSO.EFF_DATE <= :today
  AND CSO.EXP_DATE >= :today
  AND ROWNUM <= 1

Translation notes: - The chained Where calls combine with AND. - DateTime.Today is evaluated client-side (application server local date, midnight) and bound as a parameter; it is not translated to SYSDATE/TRUNC(SYSDATE). - Any() returns a boolean; no entity columns are hydrated. - Rows with NULL EXP_DATE do not match, so an open-ended override is not treated as active.

Operations and processes¶

Claim security override check

Business rules¶

SD-BR-020 Employee has claim security override only when override row matches claim and employee within EffDate to ExpDate window

Sources¶

Data/SAIF.Data/Queries/Claims/ClaimSecurityOverrideProvider.cs:18