Skip to content

SQL-188: Retrieve EmployeeAuthorityPaymentLimitHistory via GetClaimAuthorityLimitHistory

This statement retrieves the authority-limit history rows for an employee and business process, joined to the payment-type code table so the results can be ordered by its description. It is called from ClaimAuthorityService.GetClaimAuthorityLimitHistory.

Classification

Field Value
SQL ID SQL-188
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 GetClaimAuthorityLimitHistory

Parameters

Parameter Direction Type Max length Description
:employeeId Input String N/A employeeId — filters EMPLOYEE_EMPLID
:businessProcess Input String N/A businessProcess — filters CLAIM_BUS_PROCESS_CODE

Tables

Table Access Columns used
CL_CLAIM_EMP_BUS_FUNC_LIM_HIST SELECT CL_CLM_EMPBUSFUNLIMHISTSEQNUM, 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; order UPDATE_DATE
CL_CLAIM_BUS_FUNCTION_CODE JOIN Code (join), CODE_DESCR (order)

LINQ statement

Source: Services/SAIF.Services/Claims/Authority/ClaimAuthorityService.cs:512

var authorizations = _unitOfWork.GetRepository<EmployeeAuthorityPaymentLimitHistory>().Query()
    .Where(a => a.EmployeeId == employeeId && a.BusinessProcess.Code == businessProcess)
    .OrderBy(a => a.PaymentType.Description).ThenByDescending(x => x.UpdateDate).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 H.CL_CLM_EMPBUSFUNLIMHISTSEQNUM,
       H.CL_CLM_EMP_BUS_FUN_LIM_SEQ_NUM,
       H.AUTHORIZED_BY_EMPLID,
       H.EMPLOYEE_EMPLID,
       H.EXP_DATE,
       H.MAX_AMOUNT,
       H.UPDATE_DATE,
       H.UPDATE_USER,
       H.UPDATE_NUM,
       H.CLAIM_AUTH_PURPOSE_CODE,
       H.CLAIM_BUS_PROCESS_CODE,
       H.CLAIM_BUS_FUNCTION_CODE
FROM   CL_CLAIM_EMP_BUS_FUNC_LIM_HIST H
INNER JOIN CL_CLAIM_BUS_FUNCTION_CODE PT ON PT.Code = H.CLAIM_BUS_FUNCTION_CODE
WHERE  H.EMPLOYEE_EMPLID = :employeeId
  AND  H.CLAIM_BUS_PROCESS_CODE = :businessProcess
ORDER BY PT.CODE_DESCR, H.UPDATE_DATE DESC

Translation notes: - a.BusinessProcess.Code is the referenced entity's identifier → FK column on CL_CLAIM_EMP_BUS_FUNC_LIM_HIST; CL_CLAIM_BUS_PROCESS_CODE is not joined. - OrderBy(a => a.PaymentType.Description) requires the join to CL_CLAIM_BUS_FUNCTION_CODE because Description is a non-identifier property.

Operations and processes

Sources

  • Services/SAIF.Services/Claims/Authority/ClaimAuthorityService.cs (L512)