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

# SQL-116: Retrieve CbpBill

This ORM statement performs a read operation on `CbpBill` via `IUnitOfWork.GetRepository<T>()`, executed from `Services/SAIF.Services/Claims/Bills/BillQueryService.cs (L84, L208, L296)`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-116 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | N/A (ORM-generated) |
| **Business object** | `CbpBill` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `GetBillEntryBillsByCriteria, SearchByCriteria, SearchByResponsiblePartyId` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `billStatusExcludedList` | Input | ** | N/A | `x.BillStatus.Code` IN (billStatusExcludedList) (`Contains`) → column CURRENT_BILL_STATUS_CODE |
| `DateTime.Today` | Input | ** | N/A | `x.CurrentNextReviewDate <= …` → column CURRENT_NEXT_REVIEW_DATE |
| `searchValues` | Input | ** | N/A | `x.CurrentResponsiblePartyId` IN (searchValues) (`Contains`) → column CURRENT_RESPONSIBLE_PARTY_ID |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CBP_BILL](../database-tables.md#cbp_bill) | SELECT | `BILL_ID`, `UPDATE_NUM`, `PATIENT_REFERENCE_NUM`, `MAIL_ROOM_RECEIVED_DATE`, `SAIF_CLAIM_NUM`, `SAIF_CLAIM_CHK_VALUE`, `WORKER_FIRST_NAME`, `WORKER_MIDDLE_INITIAL`, `WORKER_LAST_NAME`, `WORKER_PHONE_NUM`, `WORKER_BIRTH_DATE`, `WORKER_ADDRESS_LINE1`, `WORKER_ADDRESS_LINE2`, `WORKER_ADDRESS_LINE3`, `WORKER_ADDRESS_LINE4`, `GENDER_CODE`, `PATIENT_ACCOUNT_NUM`, `PROVIDER_TAX_ID`, `PROVIDER_ADDRESS_LINE1`, `PROVIDER_ADDRESS_LINE2`, `PROVIDER_CITY_NAME`, `PROVIDER_STATE_CODE`, `PROVIDER_ZIP_CODE`, `PROVIDER_LICENSE_NUM`, `ADMISSION_DATE`, `DISCHARGE_DATE`, `STATEMENT_FROM_DATE`, `STATEMENT_THRU_DATE`, `WORKER_TAX_ID`, `TOWID`, `REBILL_FLAG`, `CURRENT_BILL_STATUS_ID`, `PRIORITY_NUM`, `UPDATE_USER`, `UPDATE_DATE`, `REFERRING_PROVIDER_NAME`, `BILL_TOTAL_AMT`, `DRG_CODE`, `BILL_PACKAGE_QUANTITY`, `WORKFLOW_BYPASS_CODE`, `CURRENT_RESPONSIBLE_PARTY_ID`, `CURRENT_STATUS_EFF_DATE`, `CURRENT_UPDATE_USER`, `CURRENT_NEXT_REVIEW_DATE`, `UB_BILL_TYPE_CODE`, `CBP_ADMISSION_TYPE_CODE`, `IMAGE_SEQ_NUM`, `CLAIM_ID`, `BILL_TYPE_CODE`, `PROVIDER_LOCATION_SEQ_NUM`, `CURRENT_BILL_STATUS_CODE`, `CBP_BILL_ENTRY_SOURCE_CODE` |

## LINQ statement

Source: `Services/SAIF.Services/Claims/Bills/BillQueryService.cs:84`

```csharp
// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:84-85
            var query = unitOfWork.GetRepository<CbpBill>().Query()
                .Where(x => !billStatusExcludedList.Contains(x.BillStatus.Code));

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:89-90
                query = query.Where(x => x.Id == criteria.BillId);
                bills = query.Take(301).ToList();

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:112-113
                    query = query.Where(x => billIds.Contains(x.Id));
                    bills = query.Take(301).ToList();

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:120
                    query = query.Where(x => x.SaifClaimNumber == criteria.ClaimNumber);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:123
                    query = query.Where(x => x.BillStatus.Code == criteria.BillStatusCode);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:126
                    query = query.Where(x => x.BillType.Code == criteria.BillTypeCode);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:129
                    query = query.Where(x => x.ProviderTaxId == criteria.ProviderTaxId);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:132
                    query = query.Where(x => x.BillingProvider.Id == criteria.ProviderLocationId);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:139
                        query = query.Where(x => x.StatementFromDate.Value <= criteria.DateRangeServiceThruDate);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:143-144
                        query = query.Where(x => x.StatementFromDate.Value >= criteria.DateRangeServiceFromDate ||
                                    x.StatementThruDate.Value >= criteria.DateRangeServiceFromDate);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:148-153
                        query = query.Where(x => (x.StatementFromDate.Value <= criteria.DateRangeServiceFromDate &&
                                                  x.StatementThruDate.Value >= criteria.DateRangeServiceFromDate)
                                                 || (x.StatementFromDate.Value >= criteria.DateRangeServiceFromDate &&
                                                     x.StatementThruDate.Value <= criteria.DateRangeServiceThruDate)
                                                 || (x.StatementFromDate.Value <= criteria.DateRangeServiceThruDate &&
                                                     x.StatementThruDate.Value >= criteria.DateRangeServiceThruDate));

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:156
                bills = query.Take(301).ToList();

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:208
            var query = unitOfWork.GetRepository<CbpBill>().Query();

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:211
                query = query.Where(x => x.Id == criteria.BillId);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:215
                    query = query.Where(x => x.ProviderTaxId == criteria.ProviderTaxId);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:221
                    query = query.Where(x => x.BillingProvider.Id == provLocId);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:225
                    query = query.Where(x => x.BillStatus.Code == criteria.BillStatusCode);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:228
                    query = query.Where(x => x.BillType.Code == criteria.BillTypeCode);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:231
                    query = query.Where(x => x.WorkerTaxId == criteria.WorkerTaxId);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:234
                    query = query.Where(x => x.SaifClaimNumber.Equals(criteria.ClaimNumber.ToUpper()));

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:241
                        query = query.Where(x => x.MailRoomReceivedDate.Value <= filterThruDateWithMaxTime);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:243
                        query = query.Where(x => x.MailRoomReceivedDate.Value >= criteria.DateRangeReceivedFromDate);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:245-246
                        query = query.Where(x => x.MailRoomReceivedDate.Value >= criteria.DateRangeReceivedFromDate &&
                                            x.MailRoomReceivedDate.Value <= filterThruDateWithMaxTime);

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:250
            var bills = query.Take(300).ToList();

// Services/SAIF.Services/Claims/Bills/BillQueryService.cs:296-298
            var bills = unitOfWork.GetRepository<CbpBill>().Query()
                                  .Where(x => searchValues.Contains(x.CurrentResponsiblePartyId)
                                              && ((x.CurrentNextReviewDate <= DateTime.Today) || displayAll)).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
-- Services/SAIF.Services/Claims/Bills/BillQueryService.cs:84 (SearchByCriteria)
SELECT *
FROM (
  SELECT
    CB.BILL_ID,
    CB.UPDATE_NUM,
    CB.PATIENT_REFERENCE_NUM,
    CB.MAIL_ROOM_RECEIVED_DATE,
    CB.SAIF_CLAIM_NUM,
    CB.SAIF_CLAIM_CHK_VALUE,
    CB.WORKER_FIRST_NAME,
    CB.WORKER_MIDDLE_INITIAL,
    CB.WORKER_LAST_NAME,
    CB.WORKER_PHONE_NUM,
    CB.WORKER_BIRTH_DATE,
    CB.WORKER_ADDRESS_LINE1,
    CB.WORKER_ADDRESS_LINE2,
    CB.WORKER_ADDRESS_LINE3,
    CB.WORKER_ADDRESS_LINE4,
    CB.GENDER_CODE,
    CB.PATIENT_ACCOUNT_NUM,
    CB.PROVIDER_TAX_ID,
    CB.PROVIDER_ADDRESS_LINE1,
    CB.PROVIDER_ADDRESS_LINE2,
    CB.PROVIDER_CITY_NAME,
    CB.PROVIDER_STATE_CODE,
    CB.PROVIDER_ZIP_CODE,
    CB.PROVIDER_LICENSE_NUM,
    CB.ADMISSION_DATE,
    CB.DISCHARGE_DATE,
    CB.STATEMENT_FROM_DATE,
    CB.STATEMENT_THRU_DATE,
    CB.WORKER_TAX_ID,
    CB.TOWID,
    CB.REBILL_FLAG,
    CB.CURRENT_BILL_STATUS_ID,
    CB.PRIORITY_NUM,
    CB.UPDATE_USER,
    CB.UPDATE_DATE,
    CB.REFERRING_PROVIDER_NAME,
    CB.BILL_TOTAL_AMT,
    CB.DRG_CODE,
    CB.BILL_PACKAGE_QUANTITY,
    CB.WORKFLOW_BYPASS_CODE,
    CB.CURRENT_RESPONSIBLE_PARTY_ID,
    CB.CURRENT_STATUS_EFF_DATE,
    CB.CURRENT_UPDATE_USER,
    CB.CURRENT_NEXT_REVIEW_DATE,
    CB.UB_BILL_TYPE_CODE,
    CB.CBP_ADMISSION_TYPE_CODE,
    CB.IMAGE_SEQ_NUM,
    CB.CLAIM_ID,
    CB.BILL_TYPE_CODE,
    CB.PROVIDER_LOCATION_SEQ_NUM,
    CB.CURRENT_BILL_STATUS_CODE,
    CB.CBP_BILL_ENTRY_SOURCE_CODE
  FROM CBP_BILL CB
  WHERE CB.CURRENT_BILL_STATUS_CODE NOT IN (:billStatusExcludedList)
    AND CB.BILL_ID = :BillId -- applied only when criteria.BillId > 0 (L89)
    AND CB.BILL_ID IN (:billIds) -- applied only when criteria.CheckNumber > 0 and billIds.Any() (L112)
    AND CB.SAIF_CLAIM_NUM = :ClaimNumber -- else-branch; applied only when ClaimNumber is not blank (L120)
    AND CB.CURRENT_BILL_STATUS_CODE = :BillStatusCode -- else-branch; only when BillStatusCode is not blank (L123)
    AND CB.BILL_TYPE_CODE = :BillTypeCode -- else-branch; only when BillTypeCode is not blank (L126)
    AND CB.PROVIDER_TAX_ID = :ProviderTaxId -- else-branch; only when ProviderTaxId is not blank (L129)
    AND CB.PROVIDER_LOCATION_SEQ_NUM = :ProviderLocationId -- else-branch; only when ProviderLocationId > 0 (L132)
    AND CB.STATEMENT_FROM_DATE <= :DateRangeServiceThruDate -- DatesEntered, thru date only (L139)
    AND (CB.STATEMENT_FROM_DATE >= :DateRangeServiceFromDate -- DatesEntered, from date only (L143-144)
      OR CB.STATEMENT_THRU_DATE >= :DateRangeServiceFromDate)
    AND ((CB.STATEMENT_FROM_DATE <= :DateRangeServiceFromDate -- DatesEntered, both dates (L148-153)
        AND CB.STATEMENT_THRU_DATE >= :DateRangeServiceFromDate)
      OR (CB.STATEMENT_FROM_DATE >= :DateRangeServiceFromDate
        AND CB.STATEMENT_THRU_DATE <= :DateRangeServiceThruDate)
      OR (CB.STATEMENT_FROM_DATE <= :DateRangeServiceThruDate
        AND CB.STATEMENT_THRU_DATE >= :DateRangeServiceThruDate))
)
WHERE ROWNUM <= 301

-- Services/SAIF.Services/Claims/Bills/BillQueryService.cs:208 (GetBillEntryBillsByCriteria)
SELECT *
FROM (
  SELECT
    CB.BILL_ID,
    CB.UPDATE_NUM,
    CB.PATIENT_REFERENCE_NUM,
    CB.MAIL_ROOM_RECEIVED_DATE,
    CB.SAIF_CLAIM_NUM,
    CB.SAIF_CLAIM_CHK_VALUE,
    CB.WORKER_FIRST_NAME,
    CB.WORKER_MIDDLE_INITIAL,
    CB.WORKER_LAST_NAME,
    CB.WORKER_PHONE_NUM,
    CB.WORKER_BIRTH_DATE,
    CB.WORKER_ADDRESS_LINE1,
    CB.WORKER_ADDRESS_LINE2,
    CB.WORKER_ADDRESS_LINE3,
    CB.WORKER_ADDRESS_LINE4,
    CB.GENDER_CODE,
    CB.PATIENT_ACCOUNT_NUM,
    CB.PROVIDER_TAX_ID,
    CB.PROVIDER_ADDRESS_LINE1,
    CB.PROVIDER_ADDRESS_LINE2,
    CB.PROVIDER_CITY_NAME,
    CB.PROVIDER_STATE_CODE,
    CB.PROVIDER_ZIP_CODE,
    CB.PROVIDER_LICENSE_NUM,
    CB.ADMISSION_DATE,
    CB.DISCHARGE_DATE,
    CB.STATEMENT_FROM_DATE,
    CB.STATEMENT_THRU_DATE,
    CB.WORKER_TAX_ID,
    CB.TOWID,
    CB.REBILL_FLAG,
    CB.CURRENT_BILL_STATUS_ID,
    CB.PRIORITY_NUM,
    CB.UPDATE_USER,
    CB.UPDATE_DATE,
    CB.REFERRING_PROVIDER_NAME,
    CB.BILL_TOTAL_AMT,
    CB.DRG_CODE,
    CB.BILL_PACKAGE_QUANTITY,
    CB.WORKFLOW_BYPASS_CODE,
    CB.CURRENT_RESPONSIBLE_PARTY_ID,
    CB.CURRENT_STATUS_EFF_DATE,
    CB.CURRENT_UPDATE_USER,
    CB.CURRENT_NEXT_REVIEW_DATE,
    CB.UB_BILL_TYPE_CODE,
    CB.CBP_ADMISSION_TYPE_CODE,
    CB.IMAGE_SEQ_NUM,
    CB.CLAIM_ID,
    CB.BILL_TYPE_CODE,
    CB.PROVIDER_LOCATION_SEQ_NUM,
    CB.CURRENT_BILL_STATUS_CODE,
    CB.CBP_BILL_ENTRY_SOURCE_CODE
  FROM CBP_BILL CB
  WHERE CB.BILL_ID = :BillId -- applied only when criteria.BillId > 0 (L211); otherwise the predicates below apply
    AND CB.PROVIDER_TAX_ID = :ProviderTaxId -- only when ProviderTaxId is not blank (L215)
    AND CB.PROVIDER_LOCATION_SEQ_NUM = :provLocId -- only when ProviderLocationId is not blank (L221)
    AND CB.CURRENT_BILL_STATUS_CODE = :BillStatusCode -- only when BillStatusCode is not blank (L225)
    AND CB.BILL_TYPE_CODE = :BillTypeCode -- only when BillTypeCode is not blank (L228)
    AND CB.WORKER_TAX_ID = :WorkerTaxId -- only when WorkerTaxId is not blank (L231)
    AND CB.SAIF_CLAIM_NUM = :ClaimNumber -- only when ClaimNumber is not blank (L234)
    AND CB.MAIL_ROOM_RECEIVED_DATE <= :filterThruDateWithMaxTime -- ReceivedDatesEntered and empty from date (L241)
    AND CB.MAIL_ROOM_RECEIVED_DATE >= :DateRangeReceivedFromDate -- ReceivedDatesEntered and empty thru date (L243)
    AND (CB.MAIL_ROOM_RECEIVED_DATE >= :DateRangeReceivedFromDate -- ReceivedDatesEntered, both dates (L245-246)
      AND CB.MAIL_ROOM_RECEIVED_DATE <= :filterThruDateWithMaxTime)
)
WHERE ROWNUM <= 300

-- Services/SAIF.Services/Claims/Bills/BillQueryService.cs:296 (SearchByResponsiblePartyId)
SELECT
  CB.BILL_ID,
  CB.UPDATE_NUM,
  CB.PATIENT_REFERENCE_NUM,
  CB.MAIL_ROOM_RECEIVED_DATE,
  CB.SAIF_CLAIM_NUM,
  CB.SAIF_CLAIM_CHK_VALUE,
  CB.WORKER_FIRST_NAME,
  CB.WORKER_MIDDLE_INITIAL,
  CB.WORKER_LAST_NAME,
  CB.WORKER_PHONE_NUM,
  CB.WORKER_BIRTH_DATE,
  CB.WORKER_ADDRESS_LINE1,
  CB.WORKER_ADDRESS_LINE2,
  CB.WORKER_ADDRESS_LINE3,
  CB.WORKER_ADDRESS_LINE4,
  CB.GENDER_CODE,
  CB.PATIENT_ACCOUNT_NUM,
  CB.PROVIDER_TAX_ID,
  CB.PROVIDER_ADDRESS_LINE1,
  CB.PROVIDER_ADDRESS_LINE2,
  CB.PROVIDER_CITY_NAME,
  CB.PROVIDER_STATE_CODE,
  CB.PROVIDER_ZIP_CODE,
  CB.PROVIDER_LICENSE_NUM,
  CB.ADMISSION_DATE,
  CB.DISCHARGE_DATE,
  CB.STATEMENT_FROM_DATE,
  CB.STATEMENT_THRU_DATE,
  CB.WORKER_TAX_ID,
  CB.TOWID,
  CB.REBILL_FLAG,
  CB.CURRENT_BILL_STATUS_ID,
  CB.PRIORITY_NUM,
  CB.UPDATE_USER,
  CB.UPDATE_DATE,
  CB.REFERRING_PROVIDER_NAME,
  CB.BILL_TOTAL_AMT,
  CB.DRG_CODE,
  CB.BILL_PACKAGE_QUANTITY,
  CB.WORKFLOW_BYPASS_CODE,
  CB.CURRENT_RESPONSIBLE_PARTY_ID,
  CB.CURRENT_STATUS_EFF_DATE,
  CB.CURRENT_UPDATE_USER,
  CB.CURRENT_NEXT_REVIEW_DATE,
  CB.UB_BILL_TYPE_CODE,
  CB.CBP_ADMISSION_TYPE_CODE,
  CB.IMAGE_SEQ_NUM,
  CB.CLAIM_ID,
  CB.BILL_TYPE_CODE,
  CB.PROVIDER_LOCATION_SEQ_NUM,
  CB.CURRENT_BILL_STATUS_CODE,
  CB.CBP_BILL_ENTRY_SOURCE_CODE
FROM CBP_BILL CB
WHERE CB.CURRENT_RESPONSIBLE_PARTY_ID IN (:searchValues)
  AND ((CB.CURRENT_NEXT_REVIEW_DATE <= :today) OR :displayAll = 1)
```

Translation notes:
- SearchByCriteria (L84-156): the three branches (BillId / CheckNumber / else) are mutually exclusive, so only the comments of the branch taken apply; each ends with `Take(301)` -> `ROWNUM <= 301`. In the CheckNumber branch no SQL runs when `billIds` is empty. `billStatusExcludedList` is the local list `{"PEN","DIS"}` (L80) -> list bind `NOT IN (:billStatusExcludedList)`. `billIds` is built in memory from the GW financial API response (L95-108) and capped at 1000 entries to avoid ORA-01795.
- GetBillEntryBillsByCriteria (L208-250): when BillId is not supplied and no optional criterion is set, the statement has no `WHERE`. `Take(300)` -> `ROWNUM <= 300`. `provLocId` is parsed client-side (L220); `criteria.ClaimNumber.ToUpper()` is evaluated client-side (bind `:ClaimNumber`); `filterThruDateWithMaxTime` is a client-side local (L238).
- `x.StatementFromDate.Value` / `x.MailRoomReceivedDate.Value` unwrap nullable columns -> plain column references.
- SearchByResponsiblePartyId (L296-298): `displayAll` references only a method argument and is pre-evaluated client-side to a constant; rendered as `:displayAll = 1` to show intent. `DateTime.Today` -> `:today` (may be evaluated client-side).
- `result.MoreItemsExist` checks (L160, L251) run in memory.

## Operations and processes
- BillQueryService.GetBillEntryBillsByCriteria — see [Operations and processes](../operations-processes.md)
- BillQueryService.SearchByCriteria — see [Operations and processes](../operations-processes.md)
- BillQueryService.SearchByResponsiblePartyId — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Claims/Bills/BillQueryService.cs (L84, L208, L296)`