ERP Export Synchronization: Posting Approved Expenses to the General Ledger Exactly Once

ERP export synchronization is the component that carries a fully approved expense record out of the reimbursement system and into the general ledger of SAP Concur, Workday, or Oracle NetSuite — exactly once, with every debit and credit mapped to the right account, cost center, and subsidiary. Within the broader Reimbursement Routing & Approval Sync framework, this stage is the last deterministic hop before money becomes a booked liability, so a dropped, duplicated, or misposted export is not a cosmetic bug — it is a restatement risk. This guide owns the transactional outbox that stages exports, the exactly-once posting protocol that survives retries and partial failures, the field mapping that turns a canonical record into ledger dimensions, and the acknowledgement reconciliation that proves the ERP actually accepted what was sent. It consumes approved records from Approval Chain Routing, hands settled postings to Payment Batch Reconciliation, and treats anything the ERP rejects as an exception rather than a silent drop.

The engineering objective is not raw throughput — it is provable posting, where a record reaches the ledger once and only once, and where every acknowledgement is matched back to the export that produced it before the liability is treated as booked.

Problem Framing & Root Causes

Most ERP integration failures trace to the same architectural mistake: treating the export as a fire-and-forget HTTP call issued in the same code path that approves the expense. That design has no safe answer to the two hard questions of distributed posting — did the ERP receive it? and did I already send it? — and every field-level bug compounds the moment those two are ambiguous.

Dual-write inconsistency. When the reimbursement database commit and the ERP API call are two separate operations, any crash between them leaves the two systems disagreeing: the expense is marked exported but never landed, or it landed but the local row still looks pending and a retry double-posts it. There is no atomic transaction spanning your database and a third-party API, so the only correct design stages the intent to export inside the same transaction that approves the record.
Non-idempotent retries. Network timeouts are indistinguishable from failures — a request can succeed at the ERP while the acknowledgement is lost in transit. A naive retry then books the same journal twice. Exactly-once posting is impossible without an idempotency key that the ERP uses to collapse a replay onto the original document.
Mapping drift. A canonical amount maps to the wrong GL account when the category taxonomy changes but the mapping table does not, or to the wrong subsidiary when an employee transfers cost centers mid-period. The export succeeds, the journal balances, and the misposting is invisible until a controller reconciles the trial balance weeks later. Deterministic, version-pinned mapping — the concern of mapping expense records to NetSuite journal entries — is what keeps the debit and credit landing where the chart of accounts expects them.
Unreconciled acknowledgements. Firing exports without matching every acknowledgement back to an outbox row means failures accumulate silently. A batch of fifty postings that returns forty-nine ACKs and one dropped connection must surface the missing one, not average it away.

The remedy is a transactional outbox that makes the export atomic with approval, an idempotency contract that makes retries safe, deterministic mapping that makes postings correct, and acknowledgement reconciliation that makes success verifiable.

Design Constraints & Prerequisites

This component is stateful only in its outbox table; the dispatcher itself is a stateless worker that can run in multiple replicas as long as it claims rows with row-level locking. It assumes upstream approval has already produced a canonical record with a stable primary key, a settled currency amount in minor units, and a resolved cost center. Money is never represented as a float — postings use Decimal or integer minor units end to end, because a fraction-of-a-cent drift in a journal line is an out-of-balance error the ERP will reject outright.

Constraint	Requirement	Rationale
Atomicity	Approved record and its outbox event commit in one local transaction	Removes the dual-write gap between the database and the ERP API
Idempotency	Deterministic key derived from the record identity, sent on every attempt	Lets the ERP collapse a replayed post onto the original document
Money representation	`Decimal` or integer minor units; per-line and per-journal balance asserted	A float rounding drift makes the journal fail the ERP’s debit-equals-credit check
Mapping source	Version-pinned account and cost-center map, loaded as config-as-code	Prevents silent misposting when the taxonomy changes under the exporter
Acknowledgement	Every attempt records the ERP document id or a typed error before commit	Turns “sent” into “confirmed posted” with matching evidence
Compliance	Every export decision logged immutably with a payload hash	Satisfies Sarbanes-Oxley control evidence requirements

The canonical record contract itself is owned upstream by Core Policy Architecture & Taxonomy Design, and the validation that a record is even eligible to post — no open anomalies, no duplicate flags — is enforced by Automated Policy Validation & Anomaly Flagging before it reaches this stage. This component trusts those guarantees and refuses to re-litigate them; its job begins the instant a record is marked approved.

Production Python Implementation

The exporter is four composable stages: an outbox writer that commits atomically with approval, a relay dispatcher that claims and posts unsent rows, a deterministic GL mapper, and an acknowledgement reconciler. Each is independently testable and emits structured audit metadata.

The transactional outbox event

The outbox pattern turns “call the ERP” into “insert a row.” The approval transaction writes both the expense state change and an export_outbox row atomically; if either fails, both roll back, and there is never an approved-but-unqueued or queued-but-unapproved record. The idempotency key is derived deterministically from the record identity so that the same expense always produces the same key, no matter how many times the dispatcher retries.

from __future__ import annotations

import hashlib
import json
from dataclasses import dataclass, field, asdict
from datetime import datetime, timezone
from decimal import Decimal
from typing import Any


@dataclass(frozen=True)
class ApprovedExpense:
    """Canonical, fully approved record eligible for ledger posting."""
    expense_id: str
    employee_id: str
    amount_minor: int          # settled amount in minor units, never float
    currency: str              # ISO 4217
    category_code: str
    cost_center: str
    subsidiary: str
    incurred_on: str           # ISO 8601 date
    approved_at: str           # ISO 8601 timestamp, UTC
    policy_version: str


def idempotency_key(expense: ApprovedExpense) -> str:
    """Deterministic export key: stable across every retry of one expense.

    Derived from the immutable identity of the approved record plus the
    policy version, so a re-approval under a new policy is a distinct post
    while a mere retry of the same approval collapses to one document.
    """
    material = f"{expense.expense_id}|{expense.approved_at}|{expense.policy_version}"
    return "exp-" + hashlib.sha256(material.encode("utf-8")).hexdigest()[:32]


@dataclass
class OutboxEvent:
    """One pending export, staged inside the approval transaction."""
    idempotency_key: str
    expense: ApprovedExpense
    status: str = "PENDING"                 # PENDING | POSTED | DEAD_LETTER
    attempts: int = 0
    erp_document_id: str | None = None
    created_at: str = field(
        default_factory=lambda: datetime.now(timezone.utc).isoformat()
    )

    def payload_hash(self) -> str:
        """SHA-256 over the canonical export payload for the audit ledger."""
        body = json.dumps(asdict(self.expense), sort_keys=True, default=str)
        return hashlib.sha256(body.encode("utf-8")).hexdigest()


def stage_export(cursor: Any, expense: ApprovedExpense) -> OutboxEvent:
    """Insert the outbox row in the SAME transaction that approves the expense.

    The caller must NOT commit until both the expense state change and this
    row are written; a single INSERT ... ON CONFLICT DO NOTHING makes the
    staging itself idempotent if approval is retried.
    """
    event = OutboxEvent(idempotency_key=idempotency_key(expense), expense=expense)
    cursor.execute(
        """
        INSERT INTO export_outbox (idempotency_key, payload, status, attempts, created_at)
        VALUES (%s, %s, %s, %s, %s)
        ON CONFLICT (idempotency_key) DO NOTHING
        """,
        (
            event.idempotency_key,
            json.dumps(asdict(expense), default=str),
            event.status,
            event.attempts,
            event.created_at,
        ),
    )
    return event

The ON CONFLICT DO NOTHING clause means re-running the approval — after a rollback, a redelivery, or an operator replay — never stages the same export twice. This is the first of two idempotency layers; the second lives at the ERP.

The relay dispatcher

A separate worker polls the outbox for PENDING rows, claims them with FOR UPDATE SKIP LOCKED so multiple replicas never grab the same row, maps each to a journal, and posts it. The dispatcher is deliberately dumb: it does not decide business rules, only deliver, observe, record.

from __future__ import annotations

import logging
from datetime import datetime, timezone
from typing import Any, Callable, Protocol

logger = logging.getLogger("expense.erp.dispatcher")

MAX_ATTEMPTS = 6


class ErpClient(Protocol):
    def post_journal(self, *, idempotency_key: str, journal: dict) -> dict: ...


class PermanentPostingError(Exception):
    """ERP rejected the payload for a reason a retry cannot fix."""


def dispatch_pending(
    conn: Any,
    erp: ErpClient,
    to_journal: Callable[[dict], dict],
    batch_size: int = 100,
) -> list[dict]:
    """Claim unsent outbox rows, post each to the ERP, record the outcome.

    Returns one audit record per attempt. Row-level SKIP LOCKED makes the
    dispatcher safe to run in parallel replicas.
    """
    results: list[dict] = []
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT idempotency_key, payload, attempts
            FROM export_outbox
            WHERE status = 'PENDING'
            ORDER BY created_at
            LIMIT %s
            FOR UPDATE SKIP LOCKED
            """,
            (batch_size,),
        )
        rows = cur.fetchall()
        for idem_key, payload, attempts in rows:
            journal = to_journal(payload)
            now = datetime.now(timezone.utc).isoformat()
            try:
                ack = erp.post_journal(idempotency_key=idem_key, journal=journal)
                cur.execute(
                    "UPDATE export_outbox SET status='POSTED', erp_document_id=%s,"
                    " attempts=%s, posted_at=%s WHERE idempotency_key=%s",
                    (ack["document_id"], attempts + 1, now, idem_key),
                )
                logger.info("erp_posted", extra={"idem_key": idem_key,
                                                 "document_id": ack["document_id"]})
                results.append({"idem_key": idem_key, "route": "POSTED",
                                "document_id": ack["document_id"], "at": now})
            except PermanentPostingError as exc:
                cur.execute(
                    "UPDATE export_outbox SET status='DEAD_LETTER', attempts=%s,"
                    " last_error=%s WHERE idempotency_key=%s",
                    (attempts + 1, str(exc), idem_key),
                )
                logger.error("erp_dead_letter", extra={"idem_key": idem_key})
                results.append({"idem_key": idem_key, "route": "DEAD_LETTER",
                                "reason": str(exc), "at": now})
            except Exception as exc:  # transient: leave PENDING for safe replay
                status = "DEAD_LETTER" if attempts + 1 >= MAX_ATTEMPTS else "PENDING"
                cur.execute(
                    "UPDATE export_outbox SET status=%s, attempts=%s,"
                    " last_error=%s WHERE idempotency_key=%s",
                    (status, attempts + 1, repr(exc), idem_key),
                )
                logger.warning("erp_retryable", extra={"idem_key": idem_key,
                                                       "attempts": attempts + 1})
                results.append({"idem_key": idem_key, "route": "REPLAY",
                                "attempts": attempts + 1, "at": now})
        conn.commit()
    return results

A transient failure leaves the row PENDING, so the next poll retries it under the same idempotency_key. That is what makes the retry safe: the ERP sees a key it has already booked and returns the original document id instead of creating a second journal. The safe-replay contract is developed in full under idempotent ERP sync with retry-safe exports.

Deterministic GL field mapping

Mapping turns a canonical record into a balanced double-entry journal: a debit to the expense account and an offsetting credit to the accounts-payable clearing account, both tagged with cost-center and subsidiary dimensions. The map is loaded from config, version-pinned, and asserted to balance before anything is posted.

from __future__ import annotations

from decimal import Decimal
from typing import TypedDict


class JournalLine(TypedDict):
    account: str
    debit_minor: int
    credit_minor: int
    cost_center: str
    subsidiary: str


# Version-pinned map: category_code -> GL expense account. Config-as-code.
CATEGORY_TO_ACCOUNT: dict[str, str] = {
    "AIRFARE": "6100",
    "LODGING": "6110",
    "MEALS": "6120",
    "GROUND_TRANSPORT": "6130",
    "SUPPLIES": "6400",
}
AP_CLEARING_ACCOUNT = "2100"


def build_journal(payload: dict) -> dict:
    """Map a canonical export payload to a balanced double-entry journal.

    Debit the mapped expense account, credit AP clearing; both lines carry
    the cost-center and subsidiary dimensions. The journal is asserted to
    balance in minor units before it can leave this function.
    """
    account = CATEGORY_TO_ACCOUNT.get(payload["category_code"])
    if account is None:
        raise KeyError(f"no GL account mapped for category {payload['category_code']!r}")

    amount = int(payload["amount_minor"])
    lines: list[JournalLine] = [
        {"account": account, "debit_minor": amount, "credit_minor": 0,
         "cost_center": payload["cost_center"], "subsidiary": payload["subsidiary"]},
        {"account": AP_CLEARING_ACCOUNT, "debit_minor": 0, "credit_minor": amount,
         "cost_center": payload["cost_center"], "subsidiary": payload["subsidiary"]},
    ]

    debits = sum(line["debit_minor"] for line in lines)
    credits = sum(line["credit_minor"] for line in lines)
    if debits != credits:
        raise ValueError(f"unbalanced journal: debit {debits} != credit {credits}")

    return {
        "external_ref": payload["expense_id"],
        "currency": payload["currency"],
        "memo": f"Reimbursement {payload['expense_id']} ({payload['category_code']})",
        "lines": lines,
        "balanced_minor": str(Decimal(debits)),
    }

Keeping the map in version-pinned config rather than code means a controller can add a category-to-account row without a redeploy, and the policy_version carried on the record lets an auditor reconstruct exactly which mapping was in force when a journal was posted. The dimension model — subsidiaries, cost centers, and currency handling for a specific ERP — is elaborated in the NetSuite mapping guide.

Acknowledgement reconciliation

Posting is only half the contract; the export is not “done” until an acknowledgement is matched back to its outbox row. Reconciliation sweeps recently dispatched rows, confirms each carries an ERP document id, and surfaces any that were dispatched but never acknowledged.

from __future__ import annotations

import logging
from typing import Any

logger = logging.getLogger("expense.erp.reconcile")


def reconcile_acknowledgements(conn: Any, window_minutes: int = 30) -> dict:
    """Match dispatched rows to ERP acknowledgements; surface the gaps.

    A row marked POSTED must carry an erp_document_id; a PENDING row past the
    window is a stuck export that needs a replay or escalation, not a silent
    drop. Returns counts plus the ids of any unresolved rows.
    """
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT status, idempotency_key, erp_document_id
            FROM export_outbox
            WHERE created_at >= now() - (%s || ' minutes')::interval
            """,
            (window_minutes,),
        )
        rows = cur.fetchall()

    posted = [r for r in rows if r[0] == "POSTED"]
    missing_doc = [r[1] for r in posted if not r[2]]
    stuck = [r[1] for r in rows if r[0] == "PENDING"]
    dead = [r[1] for r in rows if r[0] == "DEAD_LETTER"]

    report = {
        "posted": len(posted),
        "missing_document_id": missing_doc,   # posted but no ack: investigate
        "stuck_pending": stuck,               # awaiting replay
        "dead_letter": dead,                  # awaiting manual correction
    }
    if missing_doc or stuck:
        logger.warning("erp_reconcile_gap", extra=report)
    else:
        logger.info("erp_reconcile_clean", extra={"posted": len(posted)})
    return report

A POSTED row without a document id is the single most important signal this stage produces: it means the local state and the ledger may disagree, and it must be investigated before the corresponding payment is released to Payment Batch Reconciliation.

Configuration Reference

Expose every tunable through environment variables or a centralized policy store so finance can adjust posting behavior without a code change. Pin the ERP SDK and the mapping table version together — the ERP’s document model and the account map must move in lockstep.

Key	Type	Default	Rationale
`ERP_TARGET`	enum	`NETSUITE`	Which ledger to post to: `NETSUITE`, `WORKDAY`, `CONCUR`
`EXPORT_BATCH_SIZE`	int	`100`	Outbox rows claimed per dispatch; raise for throughput, lower to bound blast radius
`EXPORT_MAX_ATTEMPTS`	int	`6`	Retry ceiling before a transient failure is dead-lettered
`EXPORT_BACKOFF_BASE_MS`	int	`500`	Base for exponential backoff between replay attempts
`RECONCILE_WINDOW_MIN`	int	`30`	Lookback for the acknowledgement sweep; must exceed worst-case ERP latency
`MAPPING_VERSION`	str	`2026.07`	Pinned account/cost-center map version, recorded on every posting
`AP_CLEARING_ACCOUNT`	str	`2100`	Offsetting credit account for the reimbursement liability
`EXPORT_ON_MISSING_MAP`	enum	`DEAD_LETTER`	Behavior when a category has no mapped account; never `SKIP`

Validation & Testing

Test the two invariants that matter: that a replay never double-posts, and that a journal never leaves the mapper unbalanced. Use fixtures drawn from real failure modes — a lost acknowledgement, a category with no mapped account, a duplicate approval.

import json


def test_idempotency_key_is_stable_across_retries():
    from copy import deepcopy
    base = dict(expense_id="E-1", employee_id="U-9", amount_minor=12500,
                currency="USD", category_code="MEALS", cost_center="CC-100",
                subsidiary="SUB-US", incurred_on="2026-07-01",
                approved_at="2026-07-02T10:00:00+00:00", policy_version="v3")
    a = ApprovedExpense(**base)
    b = ApprovedExpense(**deepcopy(base))
    assert idempotency_key(a) == idempotency_key(b)


def test_journal_balances_and_maps_account():
    payload = {"expense_id": "E-1", "amount_minor": 12500, "currency": "USD",
               "category_code": "MEALS", "cost_center": "CC-100",
               "subsidiary": "SUB-US"}
    journal = build_journal(payload)
    debits = sum(l["debit_minor"] for l in journal["lines"])
    credits = sum(l["credit_minor"] for l in journal["lines"])
    assert debits == credits == 12500
    assert journal["lines"][0]["account"] == "6120"       # MEALS
    assert journal["lines"][1]["account"] == "2100"       # AP clearing


def test_unmapped_category_raises_not_silently_posts():
    payload = {"expense_id": "E-2", "amount_minor": 900, "currency": "USD",
               "category_code": "UNLISTED", "cost_center": "CC-1",
               "subsidiary": "SUB-US"}
    try:
        build_journal(payload)
        assert False, "expected KeyError for unmapped category"
    except KeyError:
        pass

Gate deployments on these assertions in CI. Add a replay test that posts the same idempotency key twice against a stub ERP and asserts one document id is returned both times, and a reconciliation test that flags a POSTED row with a null document id.

Operational Runbook

Provision the outbox. Create export_outbox with a unique constraint on idempotency_key and an index on (status, created_at) so the dispatcher’s claim query stays fast under backlog.
Deploy the dispatcher behind the writer. Ship the outbox writer first and let rows accumulate as PENDING; only then enable the dispatcher, so a bad map version cannot post before you have verified it.
Run replicas safely. FOR UPDATE SKIP LOCKED lets you scale dispatcher replicas horizontally; confirm no two replicas ever hold the same row by watching for duplicate document_id values in the audit ledger.
Set alert thresholds. Page when the stuck_pending count from reconciliation is non-zero past one window, when the dead-letter rate deviates more than 3σ from the trailing 30-day baseline, or when any POSTED row lacks a document id.
Handle the dead-letter queue. Treat dead-letter rows as manual exceptions: correct the mapping or the record, then re-stage the export under a fresh approval rather than mutating the posted row in place.
Roll back safely. Because staging uses ON CONFLICT DO NOTHING and the ERP deduplicates on the idempotency key, replaying a batch after a rollback is idempotent end to end; to pause posting, stop the dispatcher and let the outbox hold — no record is lost, only delayed.

ERP export synchronization is a deterministic compliance control, not a best-effort sync. A transactional outbox, an idempotency contract honored on both sides, version-pinned mapping, and reconciled acknowledgements give finance teams a posting stage that lands every approved expense in the ledger exactly once — and proves it did.

Reimbursement Routing & Approval Sync — the parent guide this posting stage completes
Idempotent ERP sync with retry-safe exports — the safe-replay contract in depth
Mapping expense records to NetSuite journal entries — dimensions, subsidiaries, and currency in a real ERP
Payment Batch Reconciliation — consumes confirmed postings and matches them to settlement
Approval Chain Routing — produces the approved records this stage exports
Automated Policy Validation & Anomaly Flagging — gates eligibility before a record can post
Core Policy Architecture & Taxonomy Design — owns the canonical record contract and category taxonomy