SQL-021: Ebill lookup for EOB generation (Ebill, short-circuited CbpBill FK)

EobBuilder.GeneratePaymentEobs queries Ebill filtered by the identifier of its parent CbpBill (short-circuited via the Bill navigation property to the BILL_ID foreign key, with no join or read of CBP_BILL) for the current chunk of bill IDs, loading ebills in batches of 900 to respect the Oracle 1000-item IN-list limit.

Classification

Field	Value
SQL ID	SQL-021
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle*
Schema	[unqualified]**
Command type	N/A — ORM (NHibernate generates SQL at runtime from the ClassMaps in Data/SAIF.Data.NHibernate/Mappings/**)
Business object	EobBuilder
Criteria property	N/A — ORM
Lifecycle method	GeneratePaymentEobs

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

LINQ statement

Source: Domain/SAIF.Domain/Claims/Ebilling/EobBuilder.cs:125

// Domain/SAIF.Domain/Claims/Ebilling/EobBuilder.cs:124-128
var chunkOfBillIds = billIds.Skip(completed).Take(Math.Min(step, (billIds.Count() - completed))).ToList();
var ebillLookup = factory.GetRepository<Ebill>().Query()
    .Where(x => chunkOfBillIds.Contains(x.Bill.Id)).ToList();

ebillLookup.ForEach(x => ebills.Add(x.Bill.Id, x));

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
WHERE E.BILL_ID IN (:chunkOfBillIds)

Translation notes: - x.Bill.Id is the identifier of the referenced CbpBill → short-circuited to E.BILL_ID (EbillMap.cs:14); no join to CBP_BILL. - Entity materialization: every EbillMap column is selected; HasMany collections (Services, HealthInformations, Documents, EobAdjustments) are lazy and not selected. - The statement runs once per 900-id chunk (while loop lines 122-131). ToList() is the materialization point; ebillLookup.ForEach(x => ebills.Add(x.Bill.Id, x)) (line 128) runs in memory and reads the proxy identifier without a load.

Operations and processes

• OP-DOM-12 EobBuilder.GenerateEobs — loads ebills and provider locations to build the EOB data set (see Operations and processes)

Business rules

• DOM-BR-120
• DOM-BR-121
• DOM-BR-122
• DOM-BR-123
• DOM-BR-124
• DOM-BR-125
• DOM-BR-126

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• Domain/SAIF.Domain/Claims/Ebilling/EobBuilder.cs