SQL-257: Retrieve PharmacyEligibilityStatusCode

This statement reads a single CL_PHARMACY_ELIG_STATUS_CODE row by its literal status code, used to set the eligibility status on a pharmacy first-fill claim. It is issued via NHibernate LINQ from Services/SAIF.Services/Claims/PharmacyElilgbility/PharmacyEligibilityService.cs, once with the FirstFill code (ProcessFirstFillReports) and once with the Ineligible code (UpdatePharmElig).

Classification

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

Parameters

None. Each read filters on a literal status code ('1' for FirstFill, 'I' for Ineligible); no runtime-bound parameters are used.

Tables

Table	Access	Columns used
CL_PHARMACY_ELIG_STATUS_CODE	SELECT	CODE, CODE_DESCR, UPDATE_DATE, UPDATE_USER, UPDATE_NUM

LINQ statement

Source: Services/SAIF.Services/Claims/PharmacyElilgbility/PharmacyEligibilityService.cs:134

// Services/SAIF.Services/Claims/PharmacyElilgbility/PharmacyEligibilityService.cs:134-136
firstFillClaim.EligibilityStatus =
    _unitOfWork.GetRepository<PharmacyEligibilityStatusCode>().Query()
    .FirstOrDefault(x => x.Code == PharmacyEligibilityStatusCode.FirstFill);

// Services/SAIF.Services/Claims/PharmacyElilgbility/PharmacyEligibilityService.cs:229-230
firstFillClaim.EligibilityStatus =_unitOfWork.GetRepository<PharmacyEligibilityStatusCode>().Query()
    .FirstOrDefault(x => x.Code == PharmacyEligibilityStatusCode.Ineligible);

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
-- ProcessFirstFillReports (L135): FirstFill
SELECT *
FROM (
  SELECT
  CPESC.CODE,
  CPESC.CODE_DESCR,
  CPESC.UPDATE_DATE,
  CPESC.UPDATE_USER,
  CPESC.UPDATE_NUM
  FROM CL_PHARMACY_ELIG_STATUS_CODE CPESC
  WHERE CPESC.CODE = '1'
)
WHERE ROWNUM <= 1

-- UpdatePharmElig (L229): Ineligible
SELECT *
FROM (
  SELECT
  CPESC.CODE,
  CPESC.CODE_DESCR,
  CPESC.UPDATE_DATE,
  CPESC.UPDATE_USER,
  CPESC.UPDATE_NUM
  FROM CL_PHARMACY_ELIG_STATUS_CODE CPESC
  WHERE CPESC.CODE = 'I'
)
WHERE ROWNUM <= 1

Translation notes: - Constants from PharmacyEligibilityStatusCode.cs: FirstFill = "1" (L12), Ineligible = "I" (L15). - The subsequent Update(firstFillClaim) at PharmacyEligibilityService.cs:235 writes CL_PHARMACY_ELIGIBILITY_REPORT; it is a separate statement, not part of this Read.

Operations and processes

• PharmacyEligibilityService.ProcessFirstFillReports — see Operations and processes
• PharmacyEligibilityService.UpdatePharmElig — see Operations and processes

Sources

• Services/SAIF.Services/Claims/PharmacyElilgbility/PharmacyEligibilityService.cs (L135, L229)