Skip to content

SQL-186: Retrieve EmployeeAuthorityLimit

This statement covers five distinct EmployeeAuthorityLimit reads called from ClaimAuthorityService: a single-row lookup by employee/function/purpose/process (AddEditAuthorityLimit), a lookup by primary key (TryGetEmployeeAuthorityLimit), a list of expired limits (GetExpiredAuthorities), a join against Employee for terminated employees who still hold a limit (GetTerminatedAuthorities), and a per-employee, per-process list ordered by function description (GetAuthorityLimits). The Employee join in GetTerminatedAuthorities uses the externally mapped ADP_EMPLOYEE table, whose columns beyond RPT_MGR_EMPLID are unresolved.

Classification

Field Value
SQL ID SQL-186
Operation type Read
Execution method ORM (NHibernate LINQ)
Database Oracle
Schema [unqualified]**
Command type N/A (ORM-generated)
Business object ClaimAuthorityService
Criteria property N/A — ORM
Lifecycle method AddEditAuthorityLimit, GetAuthorityLimits, GetExpiredAuthorities, GetTerminatedAuthorities, TryGetEmployeeAuthorityLimit

Parameters

Parameter Direction Type Max length Description
:employeeId Input String N/A authorityEdit.EmployeeId / a.EmployeeId == employeeId — filters EMPLOYEE_EMPLID
:functionTypeCode Input String N/A authorityEdit.FunctionType.Code — filters CLAIM_BUS_FUNCTION_CODE
:purposeType Input Integer (enum) N/A purposeType — filters CLAIM_AUTH_PURPOSE_CODE
:businessProcessCode Input String N/A ((int)businessProcess).ToString(), client-side — filters CLAIM_BUS_PROCESS_CODE
:id Input Integer N/A id — filters CL_CLM_EMP_BUS_FUN_LIM_SEQ_NUM
:runDate Input DateTime N/A runDate — filters TRUNC(EXP_DATE)
:businessProcess Input String N/A businessProcess — filters CLAIM_BUS_PROCESS_CODE

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 CL_CLM_EMP_BUS_FUN_LIM_SEQ_NUM, EMPLOYEE_EMPLID, CLAIM_BUS_FUNCTION_CODE, CLAIM_AUTH_PURPOSE_CODE, CLAIM_BUS_PROCESS_CODE
CL_CLAIM_BUS_PROCESS_CODE JOIN Code (join), CODE_DESCR (order) — statement (4) only
CL_CLAIM_BUS_FUNCTION_CODE JOIN Code (join), CODE_DESCR (order) — statements (4), (5)
ADP_EMPLOYEE** JOIN Id (join), UserId, EmployeeStatusCode — statement (4) only, C# property names, column mapping unresolved

LINQ statement

Source: Services/SAIF.Services/Claims/Authority/ClaimAuthorityService.cs:72, with further fragments at lines 260, 339, 353, 356, and 523.

// Services/SAIF.Services/Claims/Authority/ClaimAuthorityService.cs:72
var currentLimit = _unitOfWork.GetRepository<EmployeeAuthorityLimit>().Query()
    .FirstOrDefault(limit => limit.EmployeeId == authorityEdit.EmployeeId
                 && limit.FunctionType.Code == authorityEdit.FunctionType.Code
                 && limit.PurposeType == purposeType
                 && limit.BusinessProcess.Code == ((int)businessProcess).ToString());

// Services/SAIF.Services/Claims/Authority/ClaimAuthorityService.cs:260
var authorityLimit = _unitOfWork.GetRepository<EmployeeAuthorityLimit>().Query().SingleOrDefault(x => x.Id == id);

// Services/SAIF.Services/Claims/Authority/ClaimAuthorityService.cs:339
var expiredAuthorityLimits = _unitOfWork.GetRepository<EmployeeAuthorityLimit>().Query()
    .Where(limit =>
           limit.ExpirationDate != null && limit.ExpirationDate.Value.Date < runDate)
    .OrderBy(x => x.BusinessProcess.Code).ThenBy(x => x.EmployeeId);

// Services/SAIF.Services/Claims/Authority/ClaimAuthorityService.cs:353
var limits = _unitOfWork.GetRepository<EmployeeAuthorityLimit>().Query();

// Services/SAIF.Services/Claims/Authority/ClaimAuthorityService.cs:356
var terminatedLimits = emp.Where(e => e.EmployeeStatusCode != "A")
    .Join(limits, e => e.Id, l => l.EmployeeId, (e, l) => new { employee = e, limits = l })
    .OrderBy(e => e.employee.UserId)
    .ThenBy(l => l.limits.BusinessProcess.Description)
    .ThenBy(l => l.limits.FunctionType.Description)
    .ThenBy(l => l.limits.PurposeType)
    .ToList();

// Services/SAIF.Services/Claims/Authority/ClaimAuthorityService.cs:523
var authorizations = _unitOfWork.GetRepository<EmployeeAuthorityLimit>().Query()
    .Where(a => a.EmployeeId == employeeId && a.BusinessProcess.Code == businessProcess)
    .OrderBy(a => a.FunctionType.Description).ThenBy(x => x.PurposeType).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
-- (1) L72 — AddEditAuthorityLimit
SELECT * FROM (
    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_FUNCTION_CODE = :functionTypeCode
      AND  L.CLAIM_AUTH_PURPOSE_CODE = :purposeType
      AND  L.CLAIM_BUS_PROCESS_CODE = :businessProcessCode
) WHERE ROWNUM <= 1;

-- (2) L260 — TryGetEmployeeAuthorityLimit
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.CL_CLM_EMP_BUS_FUN_LIM_SEQ_NUM = :id
-- NHibernate expects at most one row

-- (3) L339 — GetExpiredAuthorities (enumerated by Mapper.Map at L344)
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.EXP_DATE IS NOT NULL
  AND  TRUNC(L.EXP_DATE) < :runDate
ORDER BY L.CLAIM_BUS_PROCESS_CODE, L.EMPLOYEE_EMPLID;

-- (4) L353/L356 — GetTerminatedAuthorities: Employee JOIN limits (same statement as SQL-177)
SELECT E.Id, E.UserId /* ...remaining EmployeeMap columns */,                              -- unresolved mapping: Employee.*
       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   ADP_EMPLOYEE E
INNER JOIN CL_CLAIM_EMP_BUS_FUNC_LIMIT L ON L.EMPLOYEE_EMPLID = E.Id                    -- unresolved mapping: Employee.Id
INNER JOIN CL_CLAIM_BUS_PROCESS_CODE BP ON BP.Code = L.CLAIM_BUS_PROCESS_CODE
INNER JOIN CL_CLAIM_BUS_FUNCTION_CODE FT ON FT.Code = L.CLAIM_BUS_FUNCTION_CODE
WHERE  E.EmployeeStatusCode <> 'A'                                                        -- unresolved mapping: Employee.EmployeeStatusCode
ORDER BY E.UserId, BP.CODE_DESCR, FT.CODE_DESCR, L.CLAIM_AUTH_PURPOSE_CODE;

-- (5) L523 — GetAuthorityLimits
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
INNER JOIN CL_CLAIM_BUS_FUNCTION_CODE FT ON FT.Code = L.CLAIM_BUS_FUNCTION_CODE
WHERE  L.EMPLOYEE_EMPLID = :employeeId
  AND  L.CLAIM_BUS_PROCESS_CODE = :businessProcess
ORDER BY FT.CODE_DESCR, L.CLAIM_AUTH_PURPOSE_CODE;

Translation notes: - ** = unresolved column mapping (C# property names shown) on ADP_EMPLOYEE, resolved from Legends documentation of SAIF.Framework.Data.NHibernate. - FirstOrDefault uses the Oracle10 dialect wrapper SELECT * FROM (...) WHERE ROWNUM <= 1. - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned. - limit.ExpirationDate.Value.Date translates to TRUNC(EXP_DATE) under the Oracle dialect. - PurposeType is a custom enum type mapped to a numeric code (ApproveReserves=1, InitiateReserves=2, Initiate=3, Approve=4). - In statements (1), (3), and the WHERE of (5), BusinessProcess.Code / FunctionType.Code are reference identifiers → FK columns, so no join is added; CL_CLAIM_BUS_PROCESS_CODE is joined only in statement (4), and in (3) the OrderBy sorts by the FK column without a join. - Statement (4) is the same composed join documented on SQL-177; the Employee column mapping is unresolved.

Operations and processes

Business rules

Sources

  • Services/SAIF.Services/Claims/Authority/ClaimAuthorityService.cs (L72, L260, L339, L353, L523)