SQL-012: EOB service adjustment insert (EbillEobAdjustment)

EobBuilder.CreateServiceAdjustments builds a service-level adjustment row (inverting the amount; group code MA/PR mapped to CO) and adds it to the repository during EOB generation.

Classification

Field	Value
SQL ID	SQL-012
Operation type	Create
Execution method	ORM (IRepository.Add)
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	EobBuilder
Criteria property	N/A — ORM
Lifecycle method	CreateServiceAdjustments

Tables

Table	Access	Columns used
EBILL_EOB_ADJUSTMENT	INSERT	EBILL_EOB_ADJUSTMENT_SEQ_NUM, ADJUSTMENT_AMT, EBILL_ADJUSTMENT_LEVEL_CODE, EBILL_ADJUSTMENT_REASON_CODE, EBILL_ADJUSTMENT_GROUP_CODE, EBILL_SEQ_NUM, AUDIT_SEQ_NUM, BILL_ID

LINQ statement

Source: Domain/SAIF.Domain/Claims/Ebilling/EobBuilder.cs:279

// Domain/SAIF.Domain/Claims/Ebilling/EobBuilder.cs:262-281
foreach (var auditedServiceAdjustment in auditedServiceAdjustments.Where(a => a.AuditAdjustmentAmount != 0))
{
    var adjustment = new EbillEobAdjustment()
    {
        Ebill = ebill,
        AuditedService = auditedServiceAdjustment.Service,
        AdjustmentAmount = auditedServiceAdjustment.AuditAdjustmentAmount * -1 ?? 0,
        LevelCode = "SERV",
        ReasonCode = auditedServiceAdjustment.AdjustmentType.DcbsAdjustmentReasonCode,
        //TODO: Temporary conversion MA or PR to CO
        GroupCode = (auditedServiceAdjustment.AdjustmentType.DcbsAdjustmentGroupCode == "MA")
                        ? "CO"
                        : (auditedServiceAdjustment.AdjustmentType.DcbsAdjustmentGroupCode == "PR")
                        ? "CO"
                        : auditedServiceAdjustment.AdjustmentType.DcbsAdjustmentGroupCode
    };

    factory.GetRepository<EbillEobAdjustment>().Add(adjustment);
    results.ServiceAdjustmentInserted++;
}

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_EOB_ADJUSTMENT_SEQ.NEXTVAL
FROM DUAL

INSERT INTO EBILL_EOB_ADJUSTMENT (
  EBILL_EOB_ADJUSTMENT_SEQ_NUM,
  ADJUSTMENT_AMT,
  EBILL_ADJUSTMENT_LEVEL_CODE,
  EBILL_ADJUSTMENT_REASON_CODE,
  EBILL_ADJUSTMENT_GROUP_CODE,
  EBILL_SEQ_NUM,
  AUDIT_SEQ_NUM,
  BILL_ID)
VALUES (
  :Id,
  :AdjustmentAmount,
  :LevelCode,
  :ReasonCode,
  :GroupCode,
  :Ebill,
  :AuditedServiceId,
  :AuditedServiceAudit)

Translation notes: - :Id is the value fetched from EBILL_EOB_ADJUSTMENT_SEQ (GeneratedBy.Native("EBILL_EOB_ADJUSTMENT_SEQ") on Oracle). - No version column mapped. - :AuditedServiceId / :AuditedServiceAudit → AUDIT_SEQ_NUM / BILL_ID. - The Where(a => a.AuditAdjustmentAmount != 0) (line 262) filters an in-memory IEnumerable, not SQL. The MA/PR → CO group-code conversion (lines 272-276) is computed in C# before binding :GroupCode.

Operations and processes

• OP-DOM-12 EobBuilder.GenerateEobs — builds the adjustments and remittance advices and inserts them (see Operations and processes)

Business rules

• DOM-BR-120
• DOM-BR-121
• DOM-BR-122
• DOM-BR-123
• DOM-BR-124
• DOM-BR-125
• DOM-BR-126

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