Variance Write-Back and ERP Adjustment Posting Permalink to this section#

↑ Part of Exception Handling & Discrepancy Resolution.

Once a discrepancy has cleared triage and been assigned a resolution — auto-accepted inside a tolerance band, approved by a reviewer, or written off — the reconciliation pipeline has exactly one job left: get that decision into the system of record. This is variance write-back, the stage that converts a resolved exception into a balanced journal entry and posts it to the ERP general ledger without corrupting the book of record and without ever posting it twice. The problem looks trivial from the outside — “just call the ERP API” — right up until an orchestrator retries a timed-out request, a message-at-least-once queue redelivers a resolution, or a scheduled replay of yesterday’s failed batch posts a second offsetting entry against a period that is already closed.

Every variance arriving at this stage has already been triaged by the Exception Queue Design and Triage workflow and carries a final resolution. Write-back does not re-decide whether the variance is real — that judgment happened upstream, driven by the tolerance windows enforced across Matching & Reconciliation Algorithms or by a human sign-off. Its only job is to translate an approved decision into a debit and a credit that balance, post them exactly once, and leave a trail an auditor can walk back to the source exception.

Core Concept & Decision Criteria Permalink to this section#

Two orthogonal design decisions shape this stage. The first is routing: does an approved variance post directly to the ERP, or does it land in a staged queue awaiting a second sign-off before commit? The second is adjustment type: price variance, quantity variance, FX rounding residue, and write-off each carry a different GL account mapping, a different materiality profile, and a different tolerance for automation. Conflating the two — treating every adjustment type as equally safe to auto-post, or staging everything regardless of size — either introduces posting lag on immaterial FX residue or removes human judgment from adjustments with physical inventory consequences.

The adjustment type determines which GL accounts a journal entry touches and how much scrutiny it warrants before commit. Price variance debits or credits a dedicated variance account against AP clearing and is usually small enough to automate below a threshold. Quantity variance implies a mismatch between what was received and what was invoiced, which has physical-count implications beyond the ledger, so it is rarely a direct-post candidate even when the dollar amount is small. FX rounding residue is sub-cent noise from currency conversion — covered upstream by the Multi-Currency Reconciliation Frameworks — and is safe to automate because its magnitude is bounded by the settlement currency’s minor unit. Write-offs close out an exception that will never reconcile cleanly and almost always require dual approval because they represent an accepted loss, not a corrected error.

The discipline that keeps all four types safe is identical regardless of routing: every posted adjustment carries a deterministic adjustment_id, the posting ledger is append-only, and a correction is always a new offsetting entry, never a mutation of a prior row. The narrower case of posting only the small, tolerance-band deltas that fall out of automated matching — rather than the full range of adjustment types covered here — is handled specifically in Posting Tolerance Adjustments to the General Ledger.

Implementation Permalink to this section#

The reference adapter separates three concerns that are easy to accidentally couple: building a balanced journal entry from a resolved variance, computing a deterministic idempotency key, and committing the entry through an ERP client that is only ever called once per key. Decimal governs every monetary value; the idempotency key is a hash of the content of the resolution, not a random identifier, so a redelivered message or a retried call always resolves to the same key and never produces a second posting.

import hashlib
import logging
from dataclasses import dataclass
from datetime import datetime
from decimal import Decimal, ROUND_HALF_UP
from enum import Enum
from typing import Optional, Protocol

logger = logging.getLogger("recon.writeback")


class AdjustmentType(str, Enum):
    PRICE_VARIANCE = "price_variance"
    QTY_VARIANCE = "qty_variance"
    FX_ROUNDING = "fx_rounding"
    WRITE_OFF = "write_off"


# GL account pairs keyed by adjustment type; debit/credit swap on reversal.
GL_ACCOUNT_MAP: dict[AdjustmentType, tuple[str, str]] = {
    AdjustmentType.PRICE_VARIANCE: ("5210-price-variance", "2000-ap-clearing"),
    AdjustmentType.QTY_VARIANCE: ("1300-inventory-clearing", "2000-ap-clearing"),
    AdjustmentType.FX_ROUNDING: ("7040-fx-rounding-suspense", "2000-ap-clearing"),
    AdjustmentType.WRITE_OFF: ("6900-variance-write-off", "2000-ap-clearing"),
}


@dataclass(frozen=True)
class ResolvedVariance:
    """A triaged exception carrying a final, approved resolution."""
    exception_id: str
    po_line_id: str
    adjustment_type: AdjustmentType
    amount: Decimal            # positive = amount owed increases
    currency: str
    resolved_by: str
    resolved_at: datetime
    approval_status: str       # "auto_approved" | "manually_approved"


@dataclass(frozen=True)
class JournalLine:
    account: str
    debit: Decimal
    credit: Decimal


@dataclass(frozen=True)
class JournalEntry:
    adjustment_id: str
    exception_id: str
    lines: tuple[JournalLine, JournalLine]
    currency: str
    memo: str
    reversal_of: Optional[str] = None


def quantize_amount(amount: Decimal, minor_units: int = 2) -> Decimal:
    """Round to the ledger currency's legal minor-unit precision."""
    exp = Decimal(1).scaleb(-minor_units)
    return amount.quantize(exp, rounding=ROUND_HALF_UP)


def adjustment_id_for(variance: ResolvedVariance) -> str:
    """
    Deterministic key built from resolution content, not a random UUID or
    message id — a redelivered or retried message always yields the same
    key, so it can never post twice.
    """
    payload = "|".join([
        variance.exception_id,
        variance.adjustment_type.value,
        str(quantize_amount(variance.amount)),
        variance.currency,
    ])
    return hashlib.sha256(payload.encode()).hexdigest()[:32]


def build_journal_entry(variance: ResolvedVariance) -> JournalEntry:
    """Translate an approved resolution into a balanced two-line entry."""
    debit_acct, credit_acct = GL_ACCOUNT_MAP[variance.adjustment_type]
    amt = quantize_amount(variance.amount)
    adj_id = adjustment_id_for(variance)
    lines = (
        JournalLine(account=debit_acct, debit=amt, credit=Decimal("0.00")),
        JournalLine(account=credit_acct, debit=Decimal("0.00"), credit=amt),
    )
    logger.info(
        "journal_built adj_id=%s exc=%s type=%s amt=%s %s",
        adj_id, variance.exception_id, variance.adjustment_type.value, amt, variance.currency,
    )
    return JournalEntry(
        adjustment_id=adj_id,
        exception_id=variance.exception_id,
        lines=lines,
        currency=variance.currency,
        memo=f"Recon adjustment {variance.adjustment_type.value} for {variance.po_line_id}",
    )


class PostingLedger(Protocol):
    """Persistence boundary onto the append-only posting_ledger table."""
    def already_posted(self, adjustment_id: str) -> Optional[dict]: ...
    def record_posting(self, entry: JournalEntry, erp_batch_id: str) -> None: ...


class ERPClient(Protocol):
    """The concrete ERP integration (SOAP, REST, or RFC — adapter-specific)."""
    def submit_journal(self, entry: JournalEntry) -> str: ...  # returns erp_batch_id


class ERPPostingAdapter:
    """Idempotent bridge between resolved variances and the ERP GL."""

    def __init__(self, ledger: PostingLedger, erp: ERPClient, dry_run: bool = True) -> None:
        self.ledger = ledger
        self.erp = erp
        self.dry_run = dry_run

    def post(self, entry: JournalEntry) -> dict:
        existing = self.ledger.already_posted(entry.adjustment_id)
        if existing is not None:
            logger.info(
                "post_skipped_duplicate adj_id=%s prior_batch=%s",
                entry.adjustment_id, existing.get("erp_batch_id"),
            )
            return {"status": "duplicate", **existing}

        if self.dry_run:
            logger.info(
                "post_preview adj_id=%s exc=%s (dry_run, not committed)",
                entry.adjustment_id, entry.exception_id,
            )
            return {"status": "preview", "adjustment_id": entry.adjustment_id}

        erp_batch_id = self.erp.submit_journal(entry)
        self.ledger.record_posting(entry, erp_batch_id)
        logger.info("post_committed adj_id=%s batch=%s", entry.adjustment_id, erp_batch_id)
        return {"status": "posted", "adjustment_id": entry.adjustment_id, "erp_batch_id": erp_batch_id}

    def reverse(self, original: JournalEntry) -> JournalEntry:
        """Build a swapped-side offsetting entry; never mutates the original row."""
        swapped = tuple(
            JournalLine(account=line.account, debit=line.credit, credit=line.debit)
            for line in original.lines
        )
        reversal = JournalEntry(
            adjustment_id=hashlib.sha256(
                f"reversal|{original.adjustment_id}".encode()
            ).hexdigest()[:32],
            exception_id=original.exception_id,
            lines=swapped,
            currency=original.currency,
            memo=f"Reversal of {original.adjustment_id}: {original.memo}",
            reversal_of=original.adjustment_id,
        )
        logger.warning(
            "reversal_built adj_id=%s reversal_of=%s exc=%s",
            reversal.adjustment_id, original.adjustment_id, original.exception_id,
        )
        return reversal

The posting ledger itself enforces idempotency at the storage layer, not just in application code — a unique constraint on adjustment_id means a race between two concurrent workers processing the same redelivered message resolves to one row, not two:

CREATE TABLE IF NOT EXISTS posting_ledger (
    adjustment_id     TEXT PRIMARY KEY,
    exception_id      TEXT NOT NULL,
    po_line_id        TEXT,
    adjustment_type   TEXT NOT NULL,
    debit_account     TEXT NOT NULL,
    credit_account    TEXT NOT NULL,
    amount            NUMERIC(18, 4) NOT NULL,
    currency          CHAR(3) NOT NULL,
    journal_memo      TEXT NOT NULL,
    reversal_of       TEXT REFERENCES posting_ledger(adjustment_id),
    posting_status    TEXT NOT NULL DEFAULT 'posted',
    erp_batch_id      TEXT NOT NULL,
    posted_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Idempotent insert: a retried post is a no-op, never a second row.
INSERT INTO posting_ledger (
    adjustment_id, exception_id, po_line_id, adjustment_type,
    debit_account, credit_account, amount, currency,
    journal_memo, reversal_of, erp_batch_id
) VALUES (
    %(adjustment_id)s, %(exception_id)s, %(po_line_id)s, %(adjustment_type)s,
    %(debit_account)s, %(credit_account)s, %(amount)s, %(currency)s,
    %(journal_memo)s, %(reversal_of)s, %(erp_batch_id)s
)
ON CONFLICT (adjustment_id) DO NOTHING
RETURNING adjustment_id, posted_at;

Notice what is deliberately excluded from the adjustment_id payload: resolved_at and resolved_by. Including a timestamp would make the key unique per delivery attempt instead of per resolution, which defeats the entire purpose — a retried call with a fresh timestamp would sail past the uniqueness check and post twice. The key is built only from facts that describe what is being posted, never when or by which attempt it was posted. Every write to this table should additionally be governed by the same append-only discipline documented in Audit Trail and Compliance for Reconciliation, so the posting ledger and the broader compliance log agree on what happened and when.

Configuration & Threshold Calibration Permalink to this section#

Routing decisions are governed by a single materiality threshold rather than a blanket auto-post or staged-approval policy per adjustment type. A variance is eligible for direct posting only when both its size and its type clear the bar:

Quantity variance and write-off are intentionally excluded from the right-hand set regardless of amount — their approval path is staged sign-off by policy, not by threshold. This mirrors the same materiality-first thinking used for Setting Quantity and Price Tolerance Windows upstream: a threshold absorbs noise, it does not replace judgment on adjustment types with operational consequences beyond the ledger.

Validate the account map before every deploy: a missing entry in GL_ACCOUNT_MAP for a new adjustment type should fail the batch loudly, not fall back to a default suspense account that silently accumulates unclassified variance.

Orchestration & Integration Permalink to this section#

Write-back sits at the tail of the reconciliation pipeline, downstream of triage and resolution. Its input is a stream of ResolvedVariance records — some produced automatically by tolerance matching, some produced by a human approving a queued exception — and its outputs are the posting ledger and the ERP’s own journal batch. Because the adapter is idempotent by construction, the orchestrator does not need special-case retry logic for this stage: a failed or timed-out call can simply be retried with the same input and it will either commit once or return the prior result. That property is what lets write-back share the same retry and backoff machinery documented in Dead-Letter Queues and Retry Orchestration instead of needing a bespoke exactly-once layer.

Batch write-back runs against the ERP’s posting window, not the reconciliation pipeline’s own cadence — most ERPs accept journal batches on a schedule tied to period-close cutoffs, and a write-back run queued outside that window should hold in a pending state rather than force a post into a closed period. Run every batch through the adapter in dry_run=True first in any environment where the ERP connection profile has changed, and diff the preview output against the expected debit/credit totals before flipping to commit mode. Because the adjustment type determines the GL account pair, a schema or chart-of-accounts change upstream should trigger a full dry-run pass across a sample of historical exceptions before it reaches production traffic.

Debugging & Pipeline Recovery Permalink to this section#

Duplicate-post detection starts at the adjustment_id uniqueness constraint, but the constraint only catches an exact-key collision — it will not catch a case where the same underlying variance was resolved twice with two different amounts, which produces two distinct, both-valid-looking adjustment_id values. Detect that pattern by grouping the posting ledger on exception_id and flagging any group with more than one non-reversal row; a legitimate correction should always appear as an explicit reversal-then-repost pair, never as two independent postings against the same exception.

• Duplicate skip observed on a first-time posting: the resolution was likely redelivered before the first attempt’s response was acknowledged. Confirm the erp_batch_id returned by the skip matches a real, committed ERP batch — if it does not, the ledger row was written but the ERP call itself failed partway, which needs manual reconciliation against the ERP’s own batch log, not a blind retry.
• Reversal needed on a bad posting: never delete or update the original posting_ledger row. Call reverse() against the original JournalEntry, post the resulting swapped-side entry as a brand-new adjustment_id, and let both rows stand — the original, wrong entry and its reversal — so the ledger’s history matches what the ERP actually recorded at each point in time.
• Mismatched debit/credit totals in a dry-run diff: almost always a stale entry in GL_ACCOUNT_MAP, not a Decimal rounding bug, since every amount already passes through quantize_amount before the journal lines are built.
• Exceptions stuck at the approval gate: check whether theta_auto was recalibrated without redeploying the adapter — a stale threshold in a config cache silently reclassifies auto-eligible variances as staged, inflating the manual approval backlog.

Every posting, skip, and reversal should emit a structured audit record — adjustment_id, exception_id, adjustment_type, debit_account, credit_account, amount, posting_status, erp_batch_id, reversal_of — consistent with the schema patterns in Audit Trail and Compliance for Reconciliation. Alert on any exception whose exception_id shows more than one non-reversal posting row, and on any spike in duplicate-skip volume, which usually signals an upstream delivery or retry misconfiguration rather than a problem with the adapter itself.

FAQ Permalink to this section#

Why key the adjustment id on the resolution content instead of a random UUID? Permalink to this section#

A random UUID or a message id changes on every delivery attempt, so a retried call or a redelivered queue message would generate a brand-new key and post a second time. Hashing the exception id, adjustment type, amount, and currency means the same resolved variance always produces the same key, so a retry converges to the same row instead of a duplicate one.

What happens if the same exception is resolved twice with two different amounts? Permalink to this section#

That produces two different adjustment_id values, since the amount is part of the hash input — and that is correct, because it is not the same resolution. It must arrive as an explicit reversal of the first posting followed by a fresh post of the corrected amount, never as two independent postings sitting side by side against the same exception with no linkage between them.

Should dry-run be the default in every environment? Permalink to this section#

Yes in staging and UAT, and yes in production immediately after any change to the ERP connection profile, chart of accounts, or threshold configuration. Flip to commit mode only after a dry-run pass against a representative sample of resolved variances has been diffed against the expected debit and credit totals.

Why does quantity variance rarely get direct-posted, even for small dollar amounts? Permalink to this section#

Quantity variance implies a mismatch between what physically arrived and what was invoiced, which has inventory and cycle-count implications beyond the general ledger. A price variance of the same dollar size is a pure financial correction; a quantity variance of that size might indicate a picking error, damaged goods, or a fraudulent shipment, so it is routed to staged approval regardless of its threshold-eligible size.

How do you reverse a bad posting without breaking the audit trail? Permalink to this section#

Never delete or update the original row in the posting ledger. Build a new journal entry with the debit and credit lines swapped, give it its own deterministic adjustment_id, and link it back to the original via reversal_of. Both the mistaken entry and its reversal remain visible in the ledger permanently, which is what lets an auditor reconstruct exactly what the ERP showed at any point in time.

Related Permalink to this section#

• Exception Queue Design and Triage
• Dead-Letter Queues and Retry Orchestration
• Posting Tolerance Adjustments to the General Ledger
• Audit Trail and Compliance for Reconciliation
• ← Back to Exception Handling & Discrepancy Resolution