Skip to content

SQL-100: EbillEobQueryProvider.GetCompletedZeroPaymentBillsForEob

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by EbillEobQueryProvider.GetCompletedZeroPaymentBillsForEob.

Classification

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

Parameters

Parameter Direction Type Max length Description
clearinghouse Input EbillClearinghouse N/A Method argument of EbillEobQueryProvider.GetCompletedZeroPaymentBillsForEob bound into the ORM expression; NHibernate generates the bind variable at runtime

Tables

Table Access Columns used
EBILL SELECT EBILL_SEQ_NUM, UPDATE_NUM, CLEARINGHOUSE_CONTROL_NUM, STATEMENT_FROM_DATE, STATEMENT_THRU_DATE, INJURY_DATE, ADMISSION_DATE, DISCHARGE_DATE, ORIGINAL_BILL_ID, BILL_TOTAL_AMOUNT, UB_BILL_TYPE_CODE, SERVICE_LOCATION_CODE, FACILITY_NPI, SUBMITTER_CONTROL_NUM, TRANSACTION_SET_CONTROL_NUM, UPDATE_USER, UPDATE_DATE, CLEARINGHOUSE_RECEIVED_DATE, CBP_ADMISSION_TYPE_CODE, BILL_ID, EBILL_RECEIPT_BATCH_SEQ_NUM, EBILL_PATIENT_SEQ_NUM, EBILL_STATUS_CODE, FACILITY_ADDRESS_SEQ_NUM, EBILL_SET_TYPE_CODE, EBILL_FIRST_RESPONSE_SEQ_NUM, EBILL_LAST_RESPONSE_SEQ_NUM, EBILL_RESPONSE_SET_SEQ_NUM, EBILL_EOB_SEQ_NUM
CBP_BILL JOIN BILL_ID, CURRENT_BILL_STATUS_CODE
CBP_AUDIT EXISTS BILL_ID
CBP_AUDIT SELECT (correlated SUM subquery) TOTAL_ALLOWED_AMT, BILL_ID
EBILL_BATCH JOIN EBILL_BATCH_SEQ_NUM, EBILL_CLEARINGHOUSE_SEQ_NUM

LINQ statement

Source: Data/SAIF.Data/Queries/Claims/Ebilling/EbillEobQueryProvider.cs:28

The repository is a class field initialised from Factory.GetRepository<Ebill>() at Data/SAIF.Data/Queries/Claims/Ebilling/EbillEobQueryProvider.cs:21.

// Data/SAIF.Data/Queries/Claims/Ebilling/EbillEobQueryProvider.cs:21
ebillRepository = Factory.GetRepository<Ebill>().Query();

// Data/SAIF.Data/Queries/Claims/Ebilling/EbillEobQueryProvider.cs:28
return ebillRepository
        .Where(e => e.Bill != null &&
                    e.Bill.BillStatus.Code == "COM" &&
                    e.Bill.Audits.Any() &&
                    e.Bill.Audits.Sum(a => a.TotalAllowedAmount) == 0 &&
                    e.Eob == null &&
                    e.ReceiptBatch.Clearinghouse == clearinghouse);

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
SELECT
  E.EBILL_SEQ_NUM,
  E.UPDATE_NUM,
  E.CLEARINGHOUSE_CONTROL_NUM,
  E.STATEMENT_FROM_DATE,
  E.STATEMENT_THRU_DATE,
  E.INJURY_DATE,
  E.ADMISSION_DATE,
  E.DISCHARGE_DATE,
  E.ORIGINAL_BILL_ID,
  E.BILL_TOTAL_AMOUNT,
  E.UB_BILL_TYPE_CODE,
  E.SERVICE_LOCATION_CODE,
  E.FACILITY_NPI,
  E.SUBMITTER_CONTROL_NUM,
  E.TRANSACTION_SET_CONTROL_NUM,
  E.UPDATE_USER,
  E.UPDATE_DATE,
  E.CLEARINGHOUSE_RECEIVED_DATE,
  E.CBP_ADMISSION_TYPE_CODE,
  E.BILL_ID,
  E.EBILL_RECEIPT_BATCH_SEQ_NUM,
  E.EBILL_PATIENT_SEQ_NUM,
  E.EBILL_STATUS_CODE,
  E.FACILITY_ADDRESS_SEQ_NUM,
  E.EBILL_SET_TYPE_CODE,
  E.EBILL_FIRST_RESPONSE_SEQ_NUM,
  E.EBILL_LAST_RESPONSE_SEQ_NUM,
  E.EBILL_RESPONSE_SET_SEQ_NUM,
  E.EBILL_EOB_SEQ_NUM
FROM EBILL E
  INNER JOIN CBP_BILL CB
    ON CB.BILL_ID = E.BILL_ID
  INNER JOIN EBILL_BATCH EB
    ON EB.EBILL_BATCH_SEQ_NUM = E.EBILL_RECEIPT_BATCH_SEQ_NUM
WHERE E.BILL_ID IS NOT NULL
  AND CB.CURRENT_BILL_STATUS_CODE = 'COM'
  AND EXISTS (
    SELECT 1
    FROM CBP_AUDIT CA
    WHERE CA.BILL_ID = CB.BILL_ID)
  AND (
    SELECT SUM(CA2.TOTAL_ALLOWED_AMT)
    FROM CBP_AUDIT CA2
    WHERE CA2.BILL_ID = CB.BILL_ID) = 0
  AND E.EBILL_EOB_SEQ_NUM IS NULL
  AND EB.EBILL_CLEARINGHOUSE_SEQ_NUM = :clearinghouse

Translation notes:

  • e.Bill != null becomes E.BILL_ID IS NOT NULL, with no join on its own. The join to CBP_BILL comes from e.Bill.BillStatus, which is a non-identifier property of CbpBill. BillStatus.Code is the status identifier (CbpBillStatusCodeMap.cs L14), so it short-circuits to CB.CURRENT_BILL_STATUS_CODE.
  • e.Bill.Audits is HasMany(...).KeyColumn("BILL_ID") (CbpBillMap.cs L72). Any() becomes EXISTS. The in-predicate Sum(a => a.TotalAllowedAmount) becomes a correlated scalar SUM subquery over CBP_AUDIT.
  • e.Eob == null becomes E.EBILL_EOB_SEQ_NUM IS NULL, with no join.
  • The method returns the un-materialized IQueryable as IEnumerable<Ebill>. SQL runs when the caller enumerates it.
  • NHibernate may write the correlated subqueries against E.BILL_ID instead of CB.BILL_ID. The two are equivalent, because the join is on that column.

Operations and processes

Business rules

Sources

  • Data/SAIF.Data/Queries/Claims/Ebilling/EbillEobQueryProvider.cs:28