---
title: "SQL-015: Ebill service cascade insert (EbillService)"
summary: Attaches a parsed 837 service line to its parent Ebill; inserted by cascade at flush.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Domain
reference_type: package
---

# SQL-015: Ebill service cascade insert (EbillService)

While parsing an 837 file, `File837Extractor.ProcessFile` attaches each parsed service line to its parent `Ebill`. The `EBILL_SERVICE` insert is carried by the `Ebill.Services` cascade (`Cascade.AllDeleteOrphan`) when the batch's unit of work flushes*.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-015 |
| **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** | `File837Extractor` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `ProcessFile` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [EBILL_SERVICE](../database-tables.md#ebill_service) | INSERT | `EBILL_SERVICE_SEQ_NUM`, `UPDATE_NUM`, `EBILL_SERVICE_SUB_NUM`, `LINE_CONTROL_NUM`, `BILL_SERVICE_DATE`, `BILL_SERVICE_END_DATE`, `CHARGE_AMOUNT`, `SERVICE_QUANTITY`, `UNIT_TYPE_CODE`, `SERVICE_MODIFIER_CODE1`, `SERVICE_MODIFIER_CODE2`, `SERVICE_MODIFIER_CODE3`, `SERVICE_MODIFIER_CODE4`, `BILL_SERVICE_CODE`, `NATIONAL_DRUG_CODE`, `TOOTH_CODE`, `RENDERING_PROVIDER_NPI`, `REFERRING_PROVIDER_NPI`, `DIAGNOSIS_POINTER_CODE1`, `DIAGNOSIS_POINTER_CODE2`, `DIAGNOSIS_POINTER_CODE3`, `DIAGNOSIS_POINTER_CODE4`, `REVENUE_CODE`, `SERVICE_LOCATION_CODE`, `DISPENSE_AS_WRITTEN_CODE`, `DAYS_SUPPLY_QTY`, `UPDATE_USER`, `UPDATE_DATE`, `PRESCRIBER_LAST_NAME`, `PRESCRIBER_FIRST_NAME`, `TAX_CHARGED_AMT`, `COMPOUND_DRUG_FLAG`, `PRESCRIPTION_WRITTEN_DATE`, `EBILL_SEQ_NUM`, `REFERRING_PROVIDER_LOC_SEQ_NUM`, `RENDERING_PROVIDER_LOC_SEQ_NUM` |

## LINQ statement

Source: `Domain/SAIF.Domain/Claims/Ebilling/File837Extractor.cs:225`. `_myService` is constructed at `File837Extractor.cs:214`.

```csharp
// Domain/SAIF.Domain/Claims/Ebilling/File837Extractor.cs:214-225
_myService = new EbillService()
{
    Bill = _myEbill,
    SubNumber = lxSegment.ServiceNumber,
    UpdateDate = DateTime.Now,
    UpdateUser = _profileService.GetCurrentUser().UserId,
    ReferringProviderNpi = _billReferringProviderNpi,
    RenderingProviderNpi = _billRenderingProviderNpi,
    IsCompoundDrug = false
};

_myEbill.AddService(_myService);

// Data/SAIF.Data/Model/Claims/Ebilling/Ebill.cs:73-77
public virtual void AddService(EbillService service)
{
    service.Bill = this;
    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
SELECT EBILL_SERVICE_SEQ.NEXTVAL
FROM DUAL

INSERT INTO EBILL_SERVICE (
  EBILL_SERVICE_SEQ_NUM,
  UPDATE_NUM,
  EBILL_SERVICE_SUB_NUM,
  LINE_CONTROL_NUM,
  BILL_SERVICE_DATE,
  BILL_SERVICE_END_DATE,
  CHARGE_AMOUNT,
  SERVICE_QUANTITY,
  UNIT_TYPE_CODE,
  SERVICE_MODIFIER_CODE1,
  SERVICE_MODIFIER_CODE2,
  SERVICE_MODIFIER_CODE3,
  SERVICE_MODIFIER_CODE4,
  BILL_SERVICE_CODE,
  NATIONAL_DRUG_CODE,
  TOOTH_CODE,
  RENDERING_PROVIDER_NPI,
  REFERRING_PROVIDER_NPI,
  DIAGNOSIS_POINTER_CODE1,
  DIAGNOSIS_POINTER_CODE2,
  DIAGNOSIS_POINTER_CODE3,
  DIAGNOSIS_POINTER_CODE4,
  REVENUE_CODE,
  SERVICE_LOCATION_CODE,
  DISPENSE_AS_WRITTEN_CODE,
  DAYS_SUPPLY_QTY,
  UPDATE_USER,
  UPDATE_DATE,
  PRESCRIBER_LAST_NAME,
  PRESCRIBER_FIRST_NAME,
  TAX_CHARGED_AMT,
  COMPOUND_DRUG_FLAG,
  PRESCRIPTION_WRITTEN_DATE,
  EBILL_SEQ_NUM,
  REFERRING_PROVIDER_LOC_SEQ_NUM,
  RENDERING_PROVIDER_LOC_SEQ_NUM)
VALUES (
  :Id,
  :UpdateNumber,
  :SubNumber,
  :LineControlNumber,
  :ServiceDate,
  :ServiceEndDate,
  :ChargeAmount,
  :ServiceQuantity,
  :UnitTypeCode,
  :ServiceModifierCode1,
  :ServiceModifierCode2,
  :ServiceModifierCode3,
  :ServiceModifierCode4,
  :ServiceCode,
  :NationalDrugCode,
  :ToothCode,
  :RenderingProviderNpi,
  :ReferringProviderNpi,
  :DiagnosisPointerCode1,
  :DiagnosisPointerCode2,
  :DiagnosisPointerCode3,
  :DiagnosisPointerCode4,
  :RevenueCode,
  :ServiceLocationCode,
  :DispenseAsWrittenCode,
  :DaysSupplyQauntity,
  :UpdateUser,
  :UpdateDate,
  :PrescriberLastName,
  :PrescriberFirstName,
  :TaxChargedAmount,
  :IsCompoundDrug,
  :PrescriptionWrittenDate,
  :Bill,
  :ReferringProvider,
  :RenderingProvider)
```

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: `Ebill.Services` (`EbillMap.cs:42-46`); the parent `Ebill` itself is transient here and reaches the session through its own parent chain (not traced beyond `EbillMap`).
- `:Id` is the value fetched from `EBILL_SERVICE_SEQ` (`GeneratedBy.Native("EBILL_SERVICE_SEQ")` on Oracle).
- `:UpdateNumber` is the initial version value NHibernate seeds on insert (version column mapped with `Version(...)`).
- `:IsCompoundDrug` is bound as `'Y'`/`'N'` (`YesNoType`); `false` at creation.
- 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).
- Only `Bill`, `SubNumber`, `UpdateDate`, `UpdateUser`, `ReferringProviderNpi`, `RenderingProviderNpi`, `IsCompoundDrug` are set at line 214-223; later 837 segments populate the rest before flush.

## Operations and processes
- OP-DOM-10 File837Extractor.ProcessFile — persists the ebill structures through the repository (inferred from repository calls)* (see [Operations and processes](../operations-processes.md))

## Business rules
- [DOM-BR-100](../business-rules.md#dom-br-100-837-parse)
- [DOM-BR-101](../business-rules.md#dom-br-101-837-parse)
- [DOM-BR-102](../business-rules.md#dom-br-102-837-parse)
- [DOM-BR-103](../business-rules.md#dom-br-103-837-parse)
- [DOM-BR-104](../business-rules.md#dom-br-104-837-parse)
- [DOM-BR-105](../business-rules.md#dom-br-105-837-parse)
- [DOM-BR-106](../business-rules.md#dom-br-106-837-parse)
- [DOM-BR-107](../business-rules.md#dom-br-107-837-parse)
- [DOM-BR-108](../business-rules.md#dom-br-108-837-parse)

> **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/File837Extractor.cs