SC ReconciliationReconciliation Automation
  1. Home
  2. Exceptions
  3. Variance Write-Back and ERP Adjustment Posting
  4. Posting Tolerance Adjustments to the General Ledger

Posting Tolerance Adjustments to the General Ledger

↑ Part of Variance Write-Back and ERP Adjustment Posting.

Once a price or quantity variance clears its approval threshold and a reviewer signs off on the write-off, the reconciliation pipeline still has one dangerous step left: turning that approval into an actual journal entry in the general ledger (GL). This is the one place in the pipeline where a bug does not just mislabel a record — it moves real money in the books. A retried post that double-books a $4.12 rounding variance is immaterial in isolation, but a retried post that double-books a batch of freight-variance write-offs after a network timeout is a reconciliation break that a controller has to explain at month-end close. This page covers the narrow mechanics of posting an already-approved tolerance adjustment: building a balanced debit/credit entry from a variance record, guarding the post with an idempotency key so retries are safe, running in dry-run mode before committing, and emitting a clean reversal when an adjustment is later rescinded.

Operational Trigger Signals

Posting to the GL is the terminal step for a variance, so it should fire only when all of the following are true — never on a raw match-tolerance breach:

  1. Adjustment is in APPROVED status — the variance cleared the review workflow (manual sign-off or an auto-approval rule under the quantity and price tolerance windows) and carries an approver identity and timestamp.
  2. Adjustment amount is within the posting ceiling — most ERPs cap what a reconciliation service account may post without a secondary control; amounts above the ceiling route to a manual GL journal instead of the automated poster.
  3. No prior successful post exists for the same adjustment_id — checked against the posting ledger before a single database write is attempted, not inferred from application state.
  4. The accounting period is still open — posting into a closed period fails at the ERP and must be redirected to the next open period with a note, not silently retried.

When these four hold, the adjustment is ready for the posting step described below. If any one fails, the record stays in APPROVED and is surfaced on the next posting run rather than forced through.

Step-by-Step Implementation

Treat posting as two separable operations — build and commit — so the entry can be constructed, validated, and logged in dry-run mode before anything touches the ERP or the posting ledger:

  1. Load the approved adjustment — pull the variance amount, adjustment type, PO/invoice references, and approver metadata for one adjustment_id.
  2. Resolve GL accounts — map the adjustment type to a debit and credit account pair from the configuration table below; never hardcode account numbers in the posting function.
  3. Build the balanced entry — construct a journal entry object where the sum of debit lines equals the sum of credit lines to the cent, using Decimal throughout.
  4. Compute the idempotency key — derive a deterministic key from adjustment_id and the adjustment’s version so a retried call always produces the identical key.
  5. Dry-run validate — render the entry and check balance, account existence, and open-period status without writing to the posting ledger or calling the ERP.
  6. Commit and log — on commit, check the idempotency key against the posting ledger inside the same transaction as the insert, post to the ERP, and record the result.
  7. Handle rescission separately — if an already-posted adjustment is later reversed by a reviewer, emit an equal-and-opposite entry that references the original posting rather than deleting or mutating it.

The block below implements steps 3–7. It keeps all monetary arithmetic in Decimal, derives the idempotency key before any I/O, and treats dry_run as a first-class mode rather than a flag checked after the fact:

PYTHON
import hashlib
import logging
from dataclasses import dataclass, field
from datetime import date, datetime
from decimal import ROUND_HALF_UP, Decimal
from enum import Enum

logger = logging.getLogger(__name__)

CENT = Decimal("0.01")


class AdjustmentType(str, Enum):
    PRICE_FAVORABLE = "PRICE_FAVORABLE"
    PRICE_UNFAVORABLE = "PRICE_UNFAVORABLE"
    QTY_SHRINKAGE = "QTY_SHRINKAGE"
    QTY_OVERAGE = "QTY_OVERAGE"
    FREIGHT_VARIANCE = "FREIGHT_VARIANCE"
    ROUNDING_FX = "ROUNDING_FX"


# adjustment_type -> (debit_account, credit_account); see Configuration Reference for meaning
GL_ACCOUNT_MAP: dict[AdjustmentType, tuple[str, str]] = {
    AdjustmentType.PRICE_FAVORABLE: ("2100-AP-CLEARING", "5010-PURCH-PRICE-VAR"),
    AdjustmentType.PRICE_UNFAVORABLE: ("5010-PURCH-PRICE-VAR", "2100-AP-CLEARING"),
    AdjustmentType.QTY_SHRINKAGE: ("5040-INV-SHRINK-EXP", "1310-INVENTORY-RM"),
    AdjustmentType.QTY_OVERAGE: ("1310-INVENTORY-RM", "5040-INV-SHRINK-EXP"),
    AdjustmentType.FREIGHT_VARIANCE: ("5210-FREIGHT-VAR", "2100-AP-CLEARING"),
    AdjustmentType.ROUNDING_FX: ("7900-FX-ROUNDING", "2100-AP-CLEARING"),
}


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


@dataclass(frozen=True)
class JournalEntry:
    idempotency_key: str
    adjustment_id: str
    entry_date: date
    lines: list[JournalLine] = field(default_factory=list)

    def is_balanced(self) -> bool:
        """A journal entry may never post unless debits equal credits to the cent."""
        total_debit = sum((line.debit for line in self.lines), Decimal("0"))
        total_credit = sum((line.credit for line in self.lines), Decimal("0"))
        return total_debit.quantize(CENT) == total_credit.quantize(CENT)


def compute_idempotency_key(adjustment_id: str, version: int) -> str:
    """Deterministic key: same adjustment + version always yields the same key,
    so a retried post (network timeout, worker crash, at-least-once redelivery)
    never depends on wall-clock time or process state to detect a duplicate.
    """
    raw = f"gl-post:{adjustment_id}:v{version}".encode("utf-8")
    return hashlib.sha256(raw).hexdigest()


def build_journal_entry(
    adjustment_id: str,
    adjustment_type: AdjustmentType,
    amount: Decimal,
    version: int,
    entry_date: date,
) -> JournalEntry:
    """Build a balanced two-line journal entry for one approved tolerance adjustment.

    amount is always positive; the account pair in GL_ACCOUNT_MAP already encodes
    which side (debit vs credit) a favorable or unfavorable variance hits.
    """
    debit_account, credit_account = GL_ACCOUNT_MAP[adjustment_type]
    posted_amount = amount.quantize(CENT, rounding=ROUND_HALF_UP)

    entry = JournalEntry(
        idempotency_key=compute_idempotency_key(adjustment_id, version),
        adjustment_id=adjustment_id,
        entry_date=entry_date,
        lines=[
            JournalLine(debit_account, posted_amount, Decimal("0"),
                        f"Tolerance write-off {adjustment_id} ({adjustment_type.value})"),
            JournalLine(credit_account, Decimal("0"), posted_amount,
                        f"Tolerance write-off {adjustment_id} ({adjustment_type.value})"),
        ],
    )
    if not entry.is_balanced():
        # Should be unreachable given the two-line construction above; guard anyway
        # because a future multi-line entry (split freight + price) could break it.
        raise ValueError(f"unbalanced entry for adjustment {adjustment_id}")
    return entry


def post_journal_entry(conn, entry: JournalEntry, dry_run: bool = True) -> str:
    """Post a validated journal entry, guarded by the idempotency key.

    In dry_run mode, validates and logs the entry but performs no writes.
    In commit mode, the idempotency check and insert happen inside one
    transaction so a concurrent retry cannot slip past the check.
    """
    if not entry.is_balanced():
        raise ValueError(f"refusing to post unbalanced entry {entry.adjustment_id}")

    if dry_run:
        logger.info(
            "DRY RUN entry=%s adjustment=%s lines=%d — no write performed",
            entry.idempotency_key, entry.adjustment_id, len(entry.lines),
        )
        return entry.idempotency_key

    with conn.transaction():
        existing = conn.execute(
            "SELECT gl_document_id FROM gl_posting_ledger WHERE idempotency_key = %s",
            (entry.idempotency_key,),
        ).fetchone()
        if existing:
            logger.info(
                "skip duplicate post: adjustment=%s already posted as gl_document_id=%s",
                entry.adjustment_id, existing[0],
            )
            return existing[0]

        gl_document_id = _post_to_erp(entry)  # ERP client call; raises on rejection
        conn.execute(
            """
            INSERT INTO gl_posting_ledger
                (idempotency_key, adjustment_id, gl_document_id, entry_date, posted_at, reversal_of)
            VALUES (%s, %s, %s, %s, %s, NULL)
            """,
            (entry.idempotency_key, entry.adjustment_id, gl_document_id,
             entry.entry_date, datetime.utcnow()),
        )
        logger.info(
            "posted adjustment=%s as gl_document_id=%s idempotency_key=%s",
            entry.adjustment_id, gl_document_id, entry.idempotency_key,
        )
        return gl_document_id


def build_reversal_entry(original: JournalEntry, reversal_date: date) -> JournalEntry:
    """Mirror-image entry for a rescinded adjustment: swap debit and credit on
    every line, keep the same accounts, and never mutate or delete the original.
    """
    reversed_lines = [
        JournalLine(line.account, line.credit, line.debit, f"REVERSAL: {line.memo}")
        for line in original.lines
    ]
    return JournalEntry(
        idempotency_key=compute_idempotency_key(f"{original.adjustment_id}-REV", 1),
        adjustment_id=original.adjustment_id,
        entry_date=reversal_date,
        lines=reversed_lines,
    )


def _post_to_erp(entry: JournalEntry) -> str:
    """Placeholder for the ERP-specific client call (SAP BAPI, NetSuite REST, etc.)."""
    raise NotImplementedError

The reversal path deliberately reuses build_journal_entry’s line shape but never calls post_journal_entry on the original entry a second time — it builds a brand-new entry with its own idempotency key so the reversal itself is retry-safe, and the posting ledger row for the reversal carries reversal_of pointing back at the original gl_document_id.

Approved variance to audited GL post An approved tolerance variance is mapped to a debit and credit account pair and built into a balanced journal entry. The entry's idempotency key is checked against the posting ledger; if a matching key already exists the prior GL document id is returned unchanged, otherwise the entry posts to the ERP and a new posting ledger row is written to the append-only audit ledger. Approved variance Resolve GL accounts adjustment type map Build balanced entry debit = credit, Decimal dry_run=True: log only Key already in ledger? Post to ERP get gl_document_id Audit ledger commit yes: return prior id no

Configuration Reference

The adjustment-type-to-account map is the single source of truth the posting function reads from; changing which accounts absorb a variance type is a configuration change, never a code edit inside the posting function itself:

Adjustment type Debit account Credit account When it applies
PRICE_FAVORABLE 2100-AP-CLEARING 5010-PURCH-PRICE-VAR Invoiced price below PO price, within tolerance
PRICE_UNFAVORABLE 5010-PURCH-PRICE-VAR 2100-AP-CLEARING Invoiced price above PO price, within tolerance
QTY_SHRINKAGE 5040-INV-SHRINK-EXP 1310-INVENTORY-RM Received quantity below shipped, within tolerance
QTY_OVERAGE 1310-INVENTORY-RM 5040-INV-SHRINK-EXP Received quantity above shipped, within tolerance
FREIGHT_VARIANCE 5210-FREIGHT-VAR 2100-AP-CLEARING Landed freight cost variance on the same PO line
ROUNDING_FX 7900-FX-ROUNDING 2100-AP-CLEARING Sub-cent or FX conversion rounding artifacts

The posting ledger row is the durable record of what was actually sent to the ERP, independent of the reconciliation service’s own state. It should be written in the same transaction as the idempotency check, never after an unguarded ERP call:

SQL
CREATE TABLE IF NOT EXISTS gl_posting_ledger (
    id             BIGSERIAL PRIMARY KEY,
    idempotency_key CHAR(64) NOT NULL UNIQUE,
    adjustment_id  TEXT NOT NULL,
    gl_document_id TEXT NOT NULL,
    entry_date     DATE NOT NULL,
    posted_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    reversal_of    BIGINT REFERENCES gl_posting_ledger(id)
);

-- The UNIQUE constraint on idempotency_key is the real guard: even if the
-- application-level SELECT-then-INSERT check races under concurrent workers,
-- the database rejects the second insert and the caller catches the
-- unique-violation as a confirmed duplicate rather than a new post.
INSERT INTO gl_posting_ledger
    (idempotency_key, adjustment_id, gl_document_id, entry_date, reversal_of)
VALUES
    ('a3f1c9...64hex', 'ADJ-2026-004821', 'GLDOC-889213', '2026-07-15', NULL)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING gl_document_id;

For the balance identity itself, the posting function’s guard is exactly:

∑i=1ndi  =  ∑i=1nci\sum_{i=1}^{n} d_i \;=\; \sum_{i=1}^{n} c_i

where did_i and cic_i are the debit and credit amounts on line ii of the entry. Every posting function should assert this before the first write, not trust the caller to have built a balanced entry.

Debugging & Recovery

Because a GL post moves money, recovery here is about proving what happened, not just retrying until it works:

  • Duplicate post suspected — query gl_posting_ledger by adjustment_id; if a gl_document_id already exists for the current version, the poster is working correctly and any apparent duplicate is two calls converging on the same idempotency key, not two postings. Compare against the ERP’s own document list to rule out a bypass of the posting function (a manual journal entered outside the pipeline).
  • ERP rejects the post — a rejected post (closed period, invalid account, credit limit) must never be retried blindly; catch the ERP error, log it against the adjustment_id without writing a posting ledger row, and route the adjustment back to APPROVED status with a rejection reason rather than leaving it silently stuck.
  • Reversal needed after the fact — always call build_reversal_entry against the original entry’s line shape; never hand-edit the original posting ledger row. The reversal_of foreign key is what lets an auditor walk from any GL document back to what it corrected and forward to what corrected it.
  • Balance mismatch caught at dry-run — a failed is_balanced() check almost always means the account map returned a lopsided pair for a new adjustment type that was added to the enum without a matching GL_ACCOUNT_MAP entry; fail loud at build time rather than posting an unbalanced entry and hoping the ERP catches it.
  • Cross-checking against the source variance — the full chain from raw exception to posted GL document should be reconstructable from the audit trail and compliance log; if your posting ledger and audit log disagree on which adjustments posted, treat that divergence itself as an incident, not a formatting difference. Consider modeling gl_posting_ledger itself as an append-only ledger design so posted rows are never updated in place, only reversed by a new row.

FAQ

Why check the idempotency key inside the database transaction instead of just checking application state before calling post?

Application state (an in-memory flag, a cache entry) does not survive a worker crash or a horizontally scaled retry from a second process, and a message queue with at-least-once delivery guarantees exactly the redelivery scenario that breaks a check performed outside the transaction. Checking — and relying on a UNIQUE constraint as the final backstop — inside the same transaction as the insert means the database itself refuses the second write even if two workers race past the application-level check at the same instant.

Can I post a batch of approved adjustments as one journal entry instead of one entry per adjustment?

You can, but compute one idempotency key per adjustment and store one posting ledger row per adjustment even when they are grouped into a single ERP document, because a rescission almost always targets one adjustment, not the whole batch. If the batch itself needs to be reversible as a unit, add a batch_id column to gl_posting_ledger in addition to, not instead of, the per-adjustment key.

What happens if the accounting period closes between approval and posting?

The poster should check the period status before attempting the ERP call and, on a closed period, redirect the entry to the next open period with an explicit late_posting memo rather than silently retrying against a period that will keep rejecting it. This is a routing decision, not a retry-timing decision — retrying against a closed period will never succeed no matter how many attempts remain, which is the same distinction the exception handling section draws between transient and permanent failures elsewhere in the pipeline.