Skip to content

SQL-187: Retrieve EmployeeAuthorityLimit via InitiateEmployeeList

This statement retrieves EmployeeAuthorityLimit rows for the subpoena-payment business process and function type, purpose "Initiate", with an expiration date that is either null or in the future. It is called from SubpoenaService.InitiateEmployeeList to build the list of employee ids passed to SQL-184.

Classification

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

Parameters

Parameter Direction Type Max length Description
: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 CLAIM_BUS_PROCESS_CODE, CLAIM_BUS_FUNCTION_CODE, CLAIM_AUTH_PURPOSE_CODE, EXP_DATE

LINQ statement

Source: Services/SAIF.Services/Legal/Subpoenas/SubpoenaService.cs:313

var limits = _unitOfWork.GetRepository<EmployeeAuthorityLimit>()
    .Query().Where(x =>
        x.BusinessProcess.Code == AuthorityBusinessProcessCode.Payments
        && x.FunctionType.Code == AuthorityPaymentTypeCode.Subpoena
        && x.PurposeType == EmployeeAuthorityLimit.AuthorityPurposeTypes.Initiate
        && (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.CLAIM_BUS_PROCESS_CODE = '2'        -- AuthorityBusinessProcessCode.Payments
  AND  L.CLAIM_BUS_FUNCTION_CODE = '16'          -- AuthorityPaymentTypeCode.Subpoena
  AND  L.CLAIM_AUTH_PURPOSE_CODE = 3              -- AuthorityPurposeTypes.Initiate
  AND  (L.EXP_DATE IS NULL OR L.EXP_DATE > :today)

Translation notes: - AuthorityBusinessProcessCode.Payments = '2', AuthorityPaymentTypeCode.Subpoena = '16' (Data/SAIF.Data/Model/Claims/CodeTables/AuthorityPaymentTypeCode.cs:10), AuthorityPurposeTypes.Initiate = 3. C# const/enum values are shown as literals for readability; NHibernate LINQ still sends them as bind parameters. - x.BusinessProcess.Code / x.FunctionType.Code are identifier short-circuits → FK columns on CL_CLAIM_EMP_BUS_FUNC_LIMIT; CL_CLAIM_BUS_PROCESS_CODE and CL_CLAIM_BUS_FUNCTION_CODE are not joined. - Materialized by .ToList(); the ids projection at L320 and the follow-on Employee lookup (SQL-184) run afterward, in memory / as a separate statement.

Operations and processes

Sources

  • Services/SAIF.Services/Legal/Subpoenas/SubpoenaService.cs (L313)