SQL-008: Bill regenerate service cascade insert (CbpBillService)

BillRegenerator.RegenBillServiceData clones each of the original bill's services onto the new cloned bill. The CBP_BILL_SERVICE rows are inserted by the CbpBill.Services cascade (Cascade.AllDeleteOrphan) at flush* rather than an explicit Add.

Classification

Field	Value
SQL ID	SQL-008
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	BillRegenerator
Criteria property	N/A — ORM
Lifecycle method	RegenBillServiceData

Tables

Table	Access	Columns used
CBP_BILL_SERVICE	INSERT	BILL_ID, BILL_SERVICE_SEQ_NUM, UPDATE_NUM, BILL_SERVICE_DATE, BILL_SERVICE_END_DATE, PROVIDER_SERVICE_CODE, BILL_SERVICE_TYPE_CODE, SERVICE_MODIFIER_CODE1, SERVICE_MODIFIER_CODE2, SERVICE_MODIFIER_CODE3, SERVICE_MODIFIER_CODE4, SERVICE_QUANTITY, CHARGE_AMT, MSRP_INVOICE_AMT, TOOTH_NUM, SERVICE_LOCATION_CODE, DAYS_SUPPLY_QTY, UPDATE_USER, UPDATE_DATE, DIAGNOSIS_POINTER_CODE1, DIAGNOSIS_POINTER_CODE2, DIAGNOSIS_POINTER_CODE3, DIAGNOSIS_POINTER_CODE4, SECONDARY_PROV_SERVICE_CODE, SECONDARY_BILL_SERV_TYPE_CODE, PRESCRIPTION_WRITTEN_DATE, PRESCRIBING_PROVIDER_ID, COMPOUND_DRUG_FLAG, PRESCRIBER_LAST_NAME, PRESCRIBER_FIRST_NAME, TAX_CHARGED_AMT, PRESCRIBING_PROVIDER_CODE, DISPENSE_AS_WRITTEN_CODE, PRESC_PROVIDER_LOC_SEQ_NUM, SERV_PROVIDER_LOC_SEQ_NUM, MSRP_INVOICE_CODE

LINQ statement

Source: Domain/SAIF.Domain/Claims/Bills/BillRegenerator.cs:118

// Domain/SAIF.Domain/Claims/Bills/BillRegenerator.cs:84-120
foreach (var service in bill.Services.OrderBy(x => x.Id))
{
    var cloneBillService = new CbpBillService
    {
        Id = service.Id,
        PrescribingProviderLocation = service.PrescribingProviderLocation,
        ServicingProviderLocation = service.ServicingProviderLocation,
        BillServiceDate = service.BillServiceDate,
        BillServiceEndDate = service.BillServiceEndDate,
        ProviderServiceCode = service.ProviderServiceCode,
        ServiceModifierCode1 = service.ServiceModifierCode1,
        ServiceModifierCode2 = service.ServiceModifierCode2,
        ServiceModifierCode3 = service.ServiceModifierCode3,
        ServiceModifierCode4 = service.ServiceModifierCode4,
        ServiceQuantity = service.ServiceQuantity,
        ChargeAmount = service.ChargeAmount,
        MsrpInvoiceAmount = service.MsrpInvoiceAmount,
        MsrpInvoiceCode = service.MsrpInvoiceCode,
        ToothNumber = service.ToothNumber,
        ServiceLocationCode = service.ServiceLocationCode,
        DaysSupplyQuantity = service.DaysSupplyQuantity,
        BillServiceTypeCode = service.BillServiceTypeCode,
        UpdateUser = currentUser,
        UpdateDate = DateTime.Now,
        PharmacyDispenseAsWrittenCode = service.PharmacyDispenseAsWrittenCode,
        SecondaryProvServiceCode = service.SecondaryProvServiceCode,
        DiagnosisPointerCode1 = service.DiagnosisPointerCode1,
        DiagnosisPointerCode2 = service.DiagnosisPointerCode2,
        DiagnosisPointerCode3 = service.DiagnosisPointerCode3,
        DiagnosisPointerCode4 = service.DiagnosisPointerCode4,
        SecondaryBillServiceTypeCode = service.SecondaryBillServiceTypeCode,
        CompoundDrugFlag = service.CompoundDrugFlag
    };

    clonebill.AddService(cloneBillService);

}

// Data/SAIF.Data/Model/Claims/Bills/CbpBill.cs:106-116
public virtual void AddService(CbpBillService service)
{
    service.Bill = this;

    if (service.Id == 0)
        service.Id = (Services.Count == 0)
            ? 1
            : Services.Max(x => x.Id) + 1;

    Services.Add(service);
}

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
INSERT INTO CBP_BILL_SERVICE (
  BILL_ID,
  BILL_SERVICE_SEQ_NUM,
  UPDATE_NUM,
  BILL_SERVICE_DATE,
  BILL_SERVICE_END_DATE,
  PROVIDER_SERVICE_CODE,
  BILL_SERVICE_TYPE_CODE,
  SERVICE_MODIFIER_CODE1,
  SERVICE_MODIFIER_CODE2,
  SERVICE_MODIFIER_CODE3,
  SERVICE_MODIFIER_CODE4,
  SERVICE_QUANTITY,
  CHARGE_AMT,
  MSRP_INVOICE_AMT,
  TOOTH_NUM,
  SERVICE_LOCATION_CODE,
  DAYS_SUPPLY_QTY,
  UPDATE_USER,
  UPDATE_DATE,
  DIAGNOSIS_POINTER_CODE1,
  DIAGNOSIS_POINTER_CODE2,
  DIAGNOSIS_POINTER_CODE3,
  DIAGNOSIS_POINTER_CODE4,
  SECONDARY_PROV_SERVICE_CODE,
  SECONDARY_BILL_SERV_TYPE_CODE,
  PRESCRIPTION_WRITTEN_DATE,
  PRESCRIBING_PROVIDER_ID,
  COMPOUND_DRUG_FLAG,
  PRESCRIBER_LAST_NAME,
  PRESCRIBER_FIRST_NAME,
  TAX_CHARGED_AMT,
  PRESCRIBING_PROVIDER_CODE,
  DISPENSE_AS_WRITTEN_CODE,
  PRESC_PROVIDER_LOC_SEQ_NUM,
  SERV_PROVIDER_LOC_SEQ_NUM,
  MSRP_INVOICE_CODE)
VALUES (
  :Bill,
  :Id,
  :UpdateNumber,
  :BillServiceDate,
  :BillServiceEndDate,
  :ProviderServiceCode,
  :BillServiceTypeCode,
  :ServiceModifierCode1,
  :ServiceModifierCode2,
  :ServiceModifierCode3,
  :ServiceModifierCode4,
  :ServiceQuantity,
  :ChargeAmount,
  :MsrpInvoiceAmount,
  :ToothNumber,
  :ServiceLocationCode,
  :DaysSupplyQuantity,
  :UpdateUser,
  :UpdateDate,
  :DiagnosisPointerCode1,
  :DiagnosisPointerCode2,
  :DiagnosisPointerCode3,
  :DiagnosisPointerCode4,
  :SecondaryProvServiceCode,
  :SecondaryBillServiceTypeCode,
  :PrescriptionWrittenDate,
  :PrescribingProviderId,
  :CompoundDrugFlag,
  :PrescriberLastName,
  :PrescriberFirstName,
  :TaxChargedAmount,
  :PrescribingProviderCode,
  :PharmacyDispenseAsWrittenCode,
  :PrescribingProviderLocation,
  :ServicingProviderLocation,
  :MsrpInvoiceCode)

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.Services (CbpBillMap.cs:79-82), fired when the cloned bill is added (SQL-007). - Composite key (CbpBillServiceMap.cs:13-15): :Bill (→ BILL_ID, the parent bill identifier) and :Id (→ BILL_SERVICE_SEQ_NUM, assigned in C#: copied from the source service, or computed by CbpBill.AddService when 0). No sequence. - References(x => x.Bill).Column("BILL_ID").Not.Insert() (CbpBillServiceMap.cs:55-57) — BILL_ID is written once, via the key. - :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.

Operations and processes

• OP-DOM-16 BillRegenerator.Regenerate — clones the bill and sets status Entered with responsible party SYSTEM (see Operations and processes)

Business rules

• DOM-BR-160

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/Bills/BillRegenerator.cs