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	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

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

-- 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
• BillQueryService.SearchByCriteria — see Operations and processes
• BillQueryService.SearchByResponsiblePartyId — see Operations and processes

Sources

• Services/SAIF.Services/Claims/Bills/BillQueryService.cs (L84, L208, L296)