SQL-006: Bill status history cascade insert (CbpBillStatus)

Bill.ModifyStatus appends a new status-history row whenever a bill's status changes. The insert into CBP_BILL_STATUS is carried out by the CbpBill.StatusHistory cascade (Cascade.AllDeleteOrphan) at unit-of-work flush* rather than an explicit repository call.

Classification

Field	Value
SQL ID	SQL-006
Operation type	Create*
Execution method	ORM (cascade on 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	Bill
Criteria property	N/A — ORM
Lifecycle method	ModifyStatus

Tables

Table	Access	Columns used
CBP_BILL_STATUS	INSERT	BILL_STATUS_ID, UPDATE_NUM, STATUS_EFF_DATE, NEXT_REVIEW_DATE, RESPONSIBLE_PARTY_ID, UPDATE_USER, UPDATE_DATE, CBP_BILL_STATUS_CODE, BILL_ID

LINQ statement

Source: Domain/SAIF.Domain/Model/Claims/Bills/Bill.cs:183

// Domain/SAIF.Domain/Model/Claims/Bills/Bill.cs:172-183
var billStatusCode = BillStatusCode.GetStatusCode(_billQueryProvider, data.Status);
var billStatus = new CbpBillStatus
{
    NextReviewDate = data.NextReviewDate,
    StatusEffectiveDate = data.EffectiveDate,
    ResponsiblePartyId = data.ResponsiblePartyId,
    BillStatus = billStatusCode,
    UpdateDate = DateTime.Now,
    UpdateUser = data.CurrentUser
};

DataItem.AddStatus(billStatus);

// Data/SAIF.Data/Model/Claims/Bills/CbpBill.cs:127-131
public virtual void AddStatus(CbpBillStatus status)
{
    status.Bill = this;
    StatusHistory.Add(status);
}

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 BILL_STATUS_SEQ.NEXTVAL
FROM DUAL

INSERT INTO CBP_BILL_STATUS (
  BILL_STATUS_ID,
  UPDATE_NUM,
  STATUS_EFF_DATE,
  NEXT_REVIEW_DATE,
  RESPONSIBLE_PARTY_ID,
  UPDATE_USER,
  UPDATE_DATE,
  CBP_BILL_STATUS_CODE,
  BILL_ID)
VALUES (
  :Id,
  :UpdateNumber,
  :StatusEffectiveDate,
  :NextReviewDate,
  :ResponsiblePartyId,
  :UpdateUser,
  :UpdateDate,
  :BillStatus,
  :Bill)

Translation notes: - Stateful session (OracleUnitOfWorkFactory → StatefulSessionStrategy, confirmed via Legends): the child row is not inserted by an explicit repository call; it is inserted by NHibernate cascade when the owning entity is saved/flushed. Cascade source: CbpBill.StatusHistory (CbpBillMap.cs:87-90). - :Id is the value fetched from BILL_STATUS_SEQ (GeneratedBy.Native("BILL_STATUS_SEQ") on Oracle). - :UpdateNumber is the initial version value NHibernate seeds on insert (version column mapped with Version(...)). - Reference bind variables (:<Property>) carry the referenced entity's identifier value. - The C# code assigns DateTime.Now client-side before flush; the value is bound as an ordinary parameter (no server date function). - The CbpBill.AddStatus doc comment (CbpBill.cs:118-123) states that CBP_BILL.CURRENT_* columns are maintained as a side effect; no C# code in AddStatus sets them, so that update is not part of this inferred SQL (mechanism unknown). - BillStatusCode.GetStatusCode(...) (line 172) is a separate read. The optional audit note (lines 185+) is SQL-002.

Operations and processes

• OP-DOM-13 Bill behaviours — ModifyStatus maps a status to its code and inserts a bill-status row (see Operations and processes)

Business rules

• DOM-BR-130
• DOM-BR-131
• DOM-BR-132

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/Model/Claims/Bills/Bill.cs