Reconciling invoices and payments

Reconciliation is a good test of readable business code: input errors, duplicate transactions, unmatched references and amount differences need different outcomes. A successful batch does not imply that every invoice was paid.

This example reconciles one in-memory batch in EUR. Amounts already contain at most two decimal places; it does not convert currencies, calculate tax, allocate a payment across invoices or write ledger entries. The result is an audit report for a later approval or posting step.

Input and audit models

Each invoice reference is unique within the batch. Payment identifiers identify transactions, so repeated identifiers must not be counted twice. A payment reference is matched using an explicit ordinal case-insensitive comparer. That data policy is separate from DirectSharp's case-insensitive source identifiers.

import Global.System
import Global.System.Collections.Generic

public class InvoiceEntry
    public Reference as String = ""
    public Amount as Decimal = 0
end class

public class PaymentEntry
    public Id as Guid = Guid.Empty
    public InvoiceReference as String = ""
    public Amount as Decimal = 0
end class

public enum ReconciliationState
    Unpaid = 0
    PartPaid = 1
    Balanced = 2
    Overpaid = 3
end enum

public class ReconciliationLine
    public Reference as String = ""
    public Expected as Decimal = 0
    public Received as Decimal = 0
    public State as ReconciliationState = ReconciliationState.Unpaid
end class

public class ReconciliationReport
    public Lines as List <of ReconciliationLine> = new List <of ReconciliationLine>()
    public Issues as List <of String> = new List <of String>()
end class

Zero is a useful default for a new amount, but this workflow requires positive invoices and payments. Credit notes, refunds and zero-value invoices need their own stated accounting policies.

Validating, grouping and comparing

Place the following class in InvoiceReconciler.ds. It uses only ordinary .NET collections and LINQ; there is no package-specific reconciliation API.

import Global.System
import Global.System.Collections.Generic
import Global.System.Linq

public class InvoiceReconciler
    public action Reconcile(
        invoices as List <of InvoiceEntry>,
        payments as List <of PaymentEntry>
    )
        returns ReconciliationReport
        require invoices is not nothing
        require payments is not nothing

        let report = new ReconciliationReport()
        let references = new HashSet <of String>(StringComparer.OrdinalIgnoreCase)
        for_each invoice in invoices
            require invoice is not nothing otherwise new ArgumentException("Invoice is required")
            require invoice.Reference is not whitespace otherwise new ArgumentException("Invoice reference is required")
            require invoice.Amount > 0 otherwise new ArgumentException("Invoice amount must be positive")
            require Decimal.Round(invoice.Amount, 2) = invoice.Amount otherwise new ArgumentException("Invoice amount has fractional cents")
            if not references.Add(invoice.Reference) then
                throw new ArgumentException("Duplicate invoice reference: {{invoice.Reference}}")
            end if
        end for_each

        let seenPayments = new HashSet <of Guid>()
        let accepted = new List <of PaymentEntry>()
        for_each payment in payments
            require payment is not nothing otherwise new ArgumentException("Payment is required")
            require payment.Id <> Guid.Empty otherwise new ArgumentException("Payment identifier is required")
            require payment.InvoiceReference is not whitespace otherwise new ArgumentException("Payment reference is required")
            require payment.Amount > 0 otherwise new ArgumentException("Payment amount must be positive")
            require Decimal.Round(payment.Amount, 2) = payment.Amount otherwise new ArgumentException("Payment amount has fractional cents")
            if not seenPayments.Add(payment.Id) then
                report.Issues.Add("Duplicate payment {{payment.Id}} ignored")
            elseif not references.Contains(payment.InvoiceReference) then
                report.Issues.Add("Payment {{payment.Id}} has unknown invoice {{payment.InvoiceReference}}")
            else
                accepted.Add(payment)
            end if
        end for_each

        let byInvoice = accepted.ToLookup(.InvoiceReference, StringComparer.OrdinalIgnoreCase)
        for_each invoice in invoices
            let received = byInvoice[invoice.Reference].Sum(.Amount)
            let line = new ReconciliationLine()
            line.Reference = invoice.Reference
            line.Expected = invoice.Amount
            line.Received = received
            if received = 0 then
                line.State = ReconciliationState.Unpaid
            elseif received < invoice.Amount then
                line.State = ReconciliationState.PartPaid
            elseif received = invoice.Amount then
                line.State = ReconciliationState.Balanced
            else
                line.State = ReconciliationState.Overpaid
            end if
            report.Lines.Add(line)
        end for_each
        return report
    end action
end class

Contracts reject malformed input because the calling boundary must supply a structurally valid batch. A repeated payment or an unknown invoice is an expected reconciliation issue, so it becomes a report entry. If an identifier appears twice with different details, the first structurally valid occurrence wins; later occurrences are reported, never added again. Even an unknown-reference occurrence reserves its identifier. A production import can instead quarantine the whole conflicting group before calling this action.

ToLookup builds an in-memory lookup once. An absent key produces an empty grouping, and the Decimal Sum overload returns zero for that grouping. .InvoiceReference and .Amount are typed selectors passed to normal LINQ APIs. No database query is involved. Decimal preserves decimal amounts; summation overflow propagates rather than manufacturing a balanced result.

References are not trimmed here. Normalize whitespace at the import boundary if the input format permits it. The explicit comparer makes INV-42 match inv-42, but does not make INV-42 the same reference. Output lines retain invoice input order, and issues retain payment input order. Callers must not mutate either list or its contained models during reconciliation.

Reading the report

public class ReconciliationPrinter
    public static action Print(report as ReconciliationReport)
        require report is not nothing
        for_each line in report.Lines
            Global.System.Console.WriteLine("{{line.Reference}}: {{line.State}}, expected {{line.Expected}}, received {{line.Received}} EUR")
        end for_each
        for_each issue in report.Issues
            Global.System.Console.WriteLine("Review: {{issue}}")
        end for_each
    end action
end class

This display uses the host's normal formatting culture. A machine-readable accounting export should select its formatting and schema explicitly. The report does not mark an invoice paid, consume a payment feed or create a database transaction.

Keep report tests separate from tests for any eventual posting transaction, import parser or currency conversion.