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

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

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

Business rules

• DOM-BR-100
• DOM-BR-101
• DOM-BR-102
• DOM-BR-103
• DOM-BR-104
• DOM-BR-105
• DOM-BR-106
• DOM-BR-107
• DOM-BR-108

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