--- title: "SQL-017: CBP bill service cascade insert (CbpBillService)" summary: Attaches each generated service line to the new CbpBill; inserted by cascade at flush. status: draft owner: SAIF Doc Gen last_reviewed: 2026-09-24 library: SAIF.Domain reference_type: package --- # SQL-017: CBP bill service cascade insert (CbpBillService) `GenerateCbpBills.GenerateBillServiceData` builds each service line for the new `CbpBill` and attaches it to the bill's `Services` collection. The `CBP_BILL_SERVICE` insert is carried by the `CbpBill.Services` cascade (`Cascade.AllDeleteOrphan`) at flush*. ## Classification | Field | Value | | --- | --- | | **SQL ID** | SQL-017 | | **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** | `GenerateCbpBills` | | **Criteria property** | N/A — ORM | | **Lifecycle method** | `GenerateBillServiceData` | ## 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/Ebilling/GenerateCBPBills.cs:202` ```csharp // Domain/SAIF.Domain/Claims/Ebilling/GenerateCBPBills.cs:201-202 billResults.Services += 1; cbpBill.AddService(buildBillService); // 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`, fired by `uow.GetRepository().Add(newCbpBill)` (SQL-016, line 34). - The service object is built at `GenerateCBPBills.cs:128-200` (object initializer and conditional assignments; not reproduced). - Composite key: `:Bill` → `BILL_ID`, `:Id` → `BILL_SERVICE_SEQ_NUM` (assigned by `CbpBill.AddService`, `CbpBill.cs:110-113`). `References(Bill).Not.Insert()` → `BILL_ID` written once. - `:UpdateNumber` is the initial version value NHibernate seeds on insert (version column mapped with `Version(...)`). - Reference bind variables (`:`) carry the referenced entity's identifier value. ## Operations and processes - OP-DOM-06 GenerateCbpBills.Generate — adds the new CbpBill and its services/procedures/diagnoses to the repository (see [Operations and processes](../operations-processes.md)) ## Business rules - [DOM-BR-050](../business-rules.md#dom-br-050-cbp-generation) - [DOM-BR-051](../business-rules.md#dom-br-051-cbp-generation) - [DOM-BR-052](../business-rules.md#dom-br-052-cbp-generation) - [DOM-BR-053](../business-rules.md#dom-br-053-cbp-generation) - [DOM-BR-054](../business-rules.md#dom-br-054-cbp-generation) - [DOM-BR-055](../business-rules.md#dom-br-055-cbp-generation) - [DOM-BR-056](../business-rules.md#dom-br-056-cbp-generation) - [DOM-BR-057](../business-rules.md#dom-br-057-cbp-generation) - [DOM-BR-058](../business-rules.md#dom-br-058-cbp-generation) - [DOM-BR-059](../business-rules.md#dom-br-059-cbp-generation) - [DOM-BR-060](../business-rules.md#dom-br-060-cbp-generation) - [DOM-BR-061](../business-rules.md#dom-br-061-cbp-generation) - [DOM-BR-062](../business-rules.md#dom-br-062-cbp-generation) - [DOM-BR-063](../business-rules.md#dom-br-063-complete) - [DOM-BR-064](../business-rules.md#dom-br-064-cbp-generation) > **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/GenerateCBPBills.cs