SQL-030: Ebill status update from 277 response (Ebill)¶

File277CaBuilder.GetBillForUpdating applies the 277 response set and status to the loaded ebill and sets FirstResponseBatch when it is not already set. NHibernate persists the change via dirty-check detection at flush.

Classification¶

Field	Value
SQL ID	SQL-030
Operation type	Update*
Execution method	ORM (dirty-check flush*)
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	`File277CaBuilder`
Criteria property	N/A — ORM
Lifecycle method	`GetBillForUpdating`

Tables¶

Table	Access	Columns used
EBILL	UPDATE	`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`, `EBILL_SEQ_NUM`

LINQ statement¶

Source: Domain/SAIF.Domain/Claims/Ebilling/File277CaBuilder.cs:179

// Domain/SAIF.Domain/Claims/Ebilling/File277CaBuilder.cs:175-185
var getBillForUpdate = ebillingQueryProvider.BillQueries.GetBill(billId);
var eval = EvaluateCbpBillStatus(getBillForUpdate, statusResponseResults);

var response = ebillingQueryProvider.BillQueries.GetEbillResponseSet(eval);
getBillForUpdate.ResponseSet = response;
getBillForUpdate.Status = response.Status;
if (getBillForUpdate.FirstResponseBatch == null) getBillForUpdate.FirstResponseBatch = batch277;
if (getBillForUpdate.LastResponseBatch == null && getBillForUpdate.ResponseSet.Status.Code != "PEND") getBillForUpdate.LastResponseBatch = batch277;
getBillForUpdate.UpdateDate = DateTime.Now;
getBillForUpdate.UpdateUser = userProfileService.GetCurrentUser().UserId;
return getBillForUpdate;

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
UPDATE EBILL
SET
  UPDATE_NUM = :newVersion,
  CLEARINGHOUSE_CONTROL_NUM = :ClearinghouseControlNumber,
  STATEMENT_FROM_DATE = :StatementFromDate,
  STATEMENT_THRU_DATE = :StatementThruDate,
  INJURY_DATE = :InjuryDate,
  ADMISSION_DATE = :AdmissionDate,
  DISCHARGE_DATE = :DischargeDate,
  ORIGINAL_BILL_ID = :OriginalBillId,
  BILL_TOTAL_AMOUNT = :TotalAmount,
  UB_BILL_TYPE_CODE = :UbBillTypeCode,
  SERVICE_LOCATION_CODE = :ServiceLocationCode,
  FACILITY_NPI = :FacilityNpi,
  SUBMITTER_CONTROL_NUM = :SubmitterControlNumber,
  TRANSACTION_SET_CONTROL_NUM = :TransactionSetControlNumber,
  UPDATE_USER = :UpdateUser,
  UPDATE_DATE = :UpdateDate,
  CLEARINGHOUSE_RECEIVED_DATE = :ClearinghouseReceivedDate,
  CBP_ADMISSION_TYPE_CODE = :CbpAdmissionTypeCode,
  BILL_ID = :Bill,
  EBILL_RECEIPT_BATCH_SEQ_NUM = :ReceiptBatch,
  EBILL_PATIENT_SEQ_NUM = :Patient,
  EBILL_STATUS_CODE = :Status,
  FACILITY_ADDRESS_SEQ_NUM = :FacilityAddress,
  EBILL_SET_TYPE_CODE = :SetType,
  EBILL_FIRST_RESPONSE_SEQ_NUM = :FirstResponseBatch,
  EBILL_LAST_RESPONSE_SEQ_NUM = :LastResponseBatch,
  EBILL_RESPONSE_SET_SEQ_NUM = :ResponseSet,
  EBILL_EOB_SEQ_NUM = :Eob
WHERE EBILL_SEQ_NUM = :Id
  AND UPDATE_NUM = :oldVersion

Translation notes: - No explicit Update() call: under the stateful strategy RepositoryBase.Update is a no-op and the UPDATE is emitted by NHibernate dirty-checking at Commit() flush (Legends: SAIF.Framework.Data.NHibernate RepositoryBase / business-rules). The mapping declares no DynamicUpdate(), so NHibernate sends every updatable mapped column, not only the ones changed here. - Version UPDATE_NUM. - FirstResponseBatch is set only when currently null (line 181); LastResponseBatch only when null and the response status is not PEND (line 182) — both columns are always present in the full-column UPDATE. - Prior page omitted EBILL_LAST_RESPONSE_SEQ_NUM, UPDATE_DATE, UPDATE_USER and used non-mapped names (RESPONSE_SET, STATUS, FIRST_RESPONSE_BATCH). - GetBill(billId) (line 175), GetEbillResponseSet(eval) (line 178) and the navigation getBillForUpdate.ResponseSet.Status.Code (line 182, lazy loads) are separate reads, not part of this SQL ID. - Bind variables for References carry the referenced entity's identifier; values not changed by this call site are re-sent unchanged. - The C# code assigns DateTime.Now client-side before flush; the value is bound as an ordinary parameter (no server date function).

Operations and processes¶

OP-DOM-11 File277CaBuilder.Build277ResponseFile — maps each bill's status to a 277 status category and sets response batch references (see Operations and processes)

Business rules¶

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