---
title: "SQL-256: Retrieve PharmacyEligibilityReport"
summary: Read operation against PharmacyEligibilityReport via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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](../database-tables.md#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`

```csharp
// 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.

```sql
-- 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](../operations-processes.md)
- PharmacyEligibilityService.GetFirstFillClaims — see [Operations and processes](../operations-processes.md)

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