SQL-185: Retrieve EmployeeAuthorityLimit via UserAuthorityLevel

This statement retrieves the EmployeeAuthorityLimit rows for an employee's payment authority, filtering by employee, business process (payments), function type, and an expiration date that is either null or in the future. It is called from BillServiceHelper.UserAuthorityLevel.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:employeeId	Input	String	N/A	employeeId — filters EMPLOYEE_EMPLID
:paymentType	Input	String	N/A	paymentType — filters CLAIM_BUS_FUNCTION_CODE
:today	Input	DateTime	N/A	DateTime.Today, client-side — filters EXP_DATE

Tables

Table	Access	Columns used
CL_CLAIM_EMP_BUS_FUNC_LIMIT	SELECT	CL_CLM_EMP_BUS_FUN_LIM_SEQ_NUM, AUTHORIZED_BY_EMPLID, EMPLOYEE_EMPLID, EXP_DATE, MAX_AMOUNT, UPDATE_DATE, UPDATE_USER, UPDATE_NUM, CLAIM_AUTH_PURPOSE_CODE, CLAIM_BUS_PROCESS_CODE, CLAIM_BUS_FUNCTION_CODE; filters EMPLOYEE_EMPLID, CLAIM_BUS_PROCESS_CODE, CLAIM_BUS_FUNCTION_CODE, EXP_DATE

LINQ statement

Source: Services/SAIF.Services/Legal/Bills/BillServiceHelper.cs:52

var limits = _unitOfWork.GetRepository<EmployeeAuthorityLimit>()
    .Query().Where(x =>
        x.EmployeeId == employeeId
        && x.BusinessProcess.Code == AuthorityBusinessProcessCode.Payments
        && x.FunctionType.Code == paymentType
        && (x.ExpirationDate == null || x.ExpirationDate > DateTime.Today)).ToList();

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 L.CL_CLM_EMP_BUS_FUN_LIM_SEQ_NUM,
       L.AUTHORIZED_BY_EMPLID,
       L.EMPLOYEE_EMPLID,
       L.EXP_DATE,
       L.MAX_AMOUNT,
       L.UPDATE_DATE,
       L.UPDATE_USER,
       L.UPDATE_NUM,
       L.CLAIM_AUTH_PURPOSE_CODE,
       L.CLAIM_BUS_PROCESS_CODE,
       L.CLAIM_BUS_FUNCTION_CODE
FROM   CL_CLAIM_EMP_BUS_FUNC_LIMIT L
WHERE  L.EMPLOYEE_EMPLID = :employeeId
  AND  L.CLAIM_BUS_PROCESS_CODE = '2'                 -- AuthorityBusinessProcessCode.Payments
  AND  L.CLAIM_BUS_FUNCTION_CODE = :paymentType
  AND  (L.EXP_DATE IS NULL OR L.EXP_DATE > :today)

Translation notes: - AuthorityBusinessProcessCode.Payments = '2' (Data/SAIF.Data/Model/Claims/CodeTables/AuthorityBusinessProcess.cs:8). C# const values are shown as literals for readability; NHibernate LINQ still sends them as bind parameters. - x.BusinessProcess.Code / x.FunctionType.Code are the referenced entities' identifiers → FK columns on CL_CLAIM_EMP_BUS_FUNC_LIMIT; CL_CLAIM_BUS_PROCESS_CODE and CL_CLAIM_BUS_FUNCTION_CODE are not joined. - DateTime.Today is evaluated client-side and bound. - The subsequent purpose-type filtering (after L59) runs in memory over the materialized list.

Operations and processes

• BillServiceHelper.UserAuthorityLevel — see Operations and processes

Sources

• Services/SAIF.Services/Legal/Bills/BillServiceHelper.cs (L52)