---
title: "SQL-008: Bill regenerate service cascade insert (CbpBillService)"
summary: Clones each of the original bill's services onto the new cloned bill; inserted by cascade at flush.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Domain
reference_type: package
---

# 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](../database-tables.md#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`

```csharp
// 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.

```sql
-- 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](../operations-processes.md))

## Business rules
- [DOM-BR-160](../business-rules.md#dom-br-160-regenerate)

> **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