SQL-256: Retrieve PharmacyEligibilityReport¶

PharmacyEligibilityService.GetEligibleClaims and GetFirstFillClaims read ordered sets of PharmacyEligibilityReport rows filtered by eligibility status and transmit-date conditions, using LINQ queries over an NHibernate-backed repository. NHibernate translates these expressions into the ordered SELECT statements shown below at execution time.

Classification¶

Field	Value
SQL ID	SQL-256
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	`GetEligibleClaims`, `GetFirstFillClaims`

Parameters¶

None — both statements filter on literal eligibility status codes and date comparisons; no bind variables reference caller-supplied values.

Tables¶

Table	Access	Columns used
CL_PHARMACY_ELIGIBILITY_REPORT	SELECT	`CLAIM_ID`, `UPDATE_DATE`, `UPDATE_NUM`, `UPDATE_USER`, `TRANSMIT_DATE`, `MEDIMPACT_TRANSMIT_DATE`, `ELIGIBILITY_EFF_DATE`, `PREV_ELIGIBILITY_EFF_DATE`, `PREV_ELIGIBILITY_STATUS_CODE`, `ELIGIBILITY_STATUS_CODE`

LINQ statement¶

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

// Services/SAIF.Services/Claims/PharmacyElilgbility/PharmacyEligibilityService.cs:91-93
_eligibilityReports = _unitOfWork.GetRepository<PharmacyEligibilityReport>()
  .Query().Where(x => x.EligibilityStatus.Code != PharmacyEligibilityStatusCode.Ineligible
  && x.EligibilityStatus.Code != PharmacyEligibilityStatusCode.MajorisPilot).OrderBy(o => o.ClaimId).AsEnumerable().ToList();

// Services/SAIF.Services/Claims/PharmacyElilgbility/PharmacyEligibilityService.cs:119-124
_eligibilityReports = _unitOfWork.GetRepository<PharmacyEligibilityReport>()
    .Query().Where(x => x.EligibilityStatus.Code == PharmacyEligibilityStatusCode.Ineligible
                        && x.MedImpactTransmitDate.HasValue &&
                        x.MedImpactTransmitDate.Value != DateTime.MinValue
                        && ((!x.TransmitDate.HasValue || x.TransmitDate.Value == DateTime.MinValue)))
                        .OrderBy(o => o.ClaimId).AsEnumerable().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
-- GetEligibleClaims (L91)
SELECT
  CPER.CLAIM_ID,
  CPER.UPDATE_DATE,
  CPER.UPDATE_NUM,
  CPER.UPDATE_USER,
  CPER.TRANSMIT_DATE,
  CPER.MEDIMPACT_TRANSMIT_DATE,
  CPER.ELIGIBILITY_EFF_DATE,
  CPER.PREV_ELIGIBILITY_EFF_DATE,
  CPER.PREV_ELIGIBILITY_STATUS_CODE,
  CPER.ELIGIBILITY_STATUS_CODE
FROM CL_PHARMACY_ELIGIBILITY_REPORT CPER
WHERE CPER.ELIGIBILITY_STATUS_CODE <> 'I'
  AND CPER.ELIGIBILITY_STATUS_CODE <> 'M'
ORDER BY CPER.CLAIM_ID

-- GetFirstFillClaims (L119)
SELECT
  CPER.CLAIM_ID,
  CPER.UPDATE_DATE,
  CPER.UPDATE_NUM,
  CPER.UPDATE_USER,
  CPER.TRANSMIT_DATE,
  CPER.MEDIMPACT_TRANSMIT_DATE,
  CPER.ELIGIBILITY_EFF_DATE,
  CPER.PREV_ELIGIBILITY_EFF_DATE,
  CPER.PREV_ELIGIBILITY_STATUS_CODE,
  CPER.ELIGIBILITY_STATUS_CODE
FROM CL_PHARMACY_ELIGIBILITY_REPORT CPER
WHERE CPER.ELIGIBILITY_STATUS_CODE = 'I'
  AND CPER.MEDIMPACT_TRANSMIT_DATE IS NOT NULL
  AND CPER.MEDIMPACT_TRANSMIT_DATE <> TO_DATE('0001-01-01', 'YYYY-MM-DD')
  AND ((CPER.TRANSMIT_DATE IS NULL
    OR CPER.TRANSMIT_DATE = TO_DATE('0001-01-01', 'YYYY-MM-DD')))
ORDER BY CPER.CLAIM_ID

Translation notes: - Constants resolved from Data/SAIF.Data/Model/Claims/CodeTables/PharmacyEligibilityStatusCode.cs: Ineligible = "I" (L15), MajorisPilot = "M" (L26). - CLAIM_ID is mapped as both Id and Map → selected once. - .AsEnumerable().ToList() is the materialization point; the OrderBy precedes it and is SQL. - DateTime.MinValue is inlined as the literal 0001-01-01. - x.EligibilityStatus.Code is the identifier of PharmacyEligibilityStatusCode (PharmacyEligibilityStatusCodeMap.cs:13, CODE) and short-circuits to FK ELIGIBILITY_STATUS_CODE; CL_PHARMACY_ELIG_STATUS_CODE is therefore not joined or accessed.

Operations and processes¶

PharmacyEligibilityService.GetEligibleClaims — see Operations and processes
PharmacyEligibilityService.GetFirstFillClaims — see Operations and processes

Sources¶

Services/SAIF.Services/Claims/PharmacyElilgbility/PharmacyEligibilityService.cs:91-93
Services/SAIF.Services/Claims/PharmacyElilgbility/PharmacyEligibilityService.cs:119-124