SQL-013: EOB remittance advice insert (EbillEobRemittanceAdvice)

EobBuilder.CreateRemittanceAdvices builds the remittance-advice rows grouped by service and RARC and adds each to the repository during EOB generation.

Classification

Field	Value
SQL ID	SQL-013
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	CreateRemittanceAdvices

Tables

Table	Access	Columns used
EBILL_EOB_REMITTANCE_ADVICE	INSERT	EBILL_EOB_REMIT_ADVICE_SEQ_NUM, EDI_REMITTANCE_ADVICE_CODE, EBILL_SEQ_NUM, AUDIT_SEQ_NUM, BILL_ID

LINQ statement

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

// Domain/SAIF.Domain/Claims/Ebilling/EobBuilder.cs:288-306
foreach (
    var auditedServiceAdjustment in
        auditedServiceAdjustments.GroupBy(
            g => new { g.Service, RARC = g.AdjustmentType.EdiRemittanceAdviceCode }))
{
    if (!auditedServiceAdjustment.Key.RARC.IsNullOrEmpty())
    {

        var advice = new EbillEobRemittanceAdvice
        {
            Ebill = ebill,
            AuditedService = auditedServiceAdjustment.Key.Service,
            RemittanceAdviceCode = auditedServiceAdjustment.Key.RARC
        };

        factory.GetRepository<EbillEobRemittanceAdvice>().Add(advice);
        results.PaymentAdviceInserted++;
    }
}

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

INSERT INTO EBILL_EOB_REMITTANCE_ADVICE (
  EBILL_EOB_REMIT_ADVICE_SEQ_NUM,
  EDI_REMITTANCE_ADVICE_CODE,
  EBILL_SEQ_NUM,
  AUDIT_SEQ_NUM,
  BILL_ID)
VALUES (
  :Id,
  :RemittanceAdviceCode,
  :Ebill,
  :AuditedServiceId,
  :AuditedServiceAudit)

Translation notes: - :Id is the value fetched from EBILL_EOB_REMIT_ADVICE_SEQ (GeneratedBy.Native("EBILL_EOB_REMIT_ADVICE_SEQ") on Oracle). - No version column mapped. - :AuditedServiceId / :AuditedServiceAudit → AUDIT_SEQ_NUM / BILL_ID. - The GroupBy(...) (lines 290-291) and the RARC empty check run in memory over an IEnumerable; one INSERT per non-empty group.

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