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

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

-- 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<CbpBill>().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 (:<Property>) 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)

Business rules

• DOM-BR-050
• DOM-BR-051
• DOM-BR-052
• DOM-BR-053
• DOM-BR-054
• DOM-BR-055
• DOM-BR-056
• DOM-BR-057
• DOM-BR-058
• DOM-BR-059
• DOM-BR-060
• DOM-BR-061
• DOM-BR-062
• DOM-BR-063
• DOM-BR-064

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