Stock Reconciliation & Balance Correction

A design reference for reconciling physical counts against system stock, importing opening balances, and posting valuation adjustments

1. Requirements

1.1 Functional requirements

Let a warehouse user record a physical count — quantity, optionally a rate — per item/warehouse (and per batch/serial where tracked), posting only the difference against what the system currently believes is on hand.
Support two purposes on one document family: an ad-hoc Stock Reconciliation for an operating warehouse, and a one-time Opening Stock load that seeds balances where no prior movement history exists.
Pre-fill each line with the item’s current quantity/rate as of the chosen posting date/time, one row at a time or in bulk for a whole warehouse (including sub-warehouses), so staff correct mismatches rather than re-type a full count.
Support a barcode-scan mode where auto-fetching the existing quantity is suppressed, so repeated scans of one barcode accumulate rather than reset each time.
Resolve a “counted quantity” for serialized/batch-tracked items into a concrete set of serial numbers or batch allocations — deciding which units moved, not only how many.
Silently drop any line whose entered qty/rate already match the current balance, so a “recount confirmed no change” pass posts nothing.
Allow a correction dated in the past relative to movements already posted for that item/warehouse, with every later movement’s valuation recomputed rather than left to drift.
Require a legal entity and cost center for every correction, defaulting both from that entity’s stock-adjustment settings when blank.
Route the net value of every accepted difference to one designated ledger account, not the account of whatever movement it displaces.
Provide a lightweight, non-transactional lookup for spot-checking one item’s current balance and value (by item code or barcode) without opening a full document.
Reject a correction against reserved inventory (e.g., open sales-order stock reservations) until cleared, when it would change the counted quantity.

1.2 Non-functional requirements

Idempotent no-op filtering: a line whose counted values already agree with the ledger produces no posting at all.
Display consistency under reposting: the “current” figures shown on a submitted correction must always agree with what the ledger actually holds for that line, even after a later backdated correction forces history to be recomputed underneath it.
Per-line auditability: every line’s monetary swing must be individually traceable, not folded into a single document-level total.
Clean reversal on cancel: cancelling a correction must reverse exactly what it posted and restore the prior position.
Bulk scalability: a correction with a large number of lines must not block the submitting request; it should run as a background job instead.

1.3 Constraints

A correction cannot bypass the shared posting funnel that every other stock document uses — its lines become ordinary append-only movement rows processed by the same valuation machinery as a receipt, issue, or transfer.
A custom inventory-splitting dimension can only be set through a correction line when that item/warehouse combination currently carries no quantity at all — using a correction to retroactively edit the dimension breakdown of an existing, already-populated balance is rejected outright.
The ledger account a correction’s difference posts to must be an asset/liability-type account whenever the correction is an opening entry, or whenever no stock movement has ever been posted for the company yet — a profit-and-loss account is rejected in both of those cases.

The Balance Correction Voucher is one of several callers of the same posting funnel that a Movement Voucher (the general-purpose stock movement document covering receipts, issues, transfers, manufacture and subcontracting flows) also uses — it never writes the Stock Position Record directly. Reserved-quantity figures on that record are commitment/projection fields written directly by order documents, not by the funnel — exactly what the reservation guard in §3.4 checks against.

2.2 Count-to-correction flow

3. Deep Dive

3.1 Data model

Balance Correction Voucher (the reconciliation document) Header fields: legal entity (company), purpose (blank / Opening Stock / Stock Reconciliation), posting date/time (editable only under an explicit override flag), an optional default warehouse for bulk item-fetching, barcode-scan fields, a difference_amount total, and the destination expense_account (the Stock Adjustment Account, a Ledger Account) plus cost_center — both defaulted from the legal entity’s stock-adjustment settings when blank.

Balance Correction Line Each line carries item_code, warehouse, counted qty/valuation_rate (and their derived amount), plus a parallel “before reconciliation” block — current_qty, current_valuation_rate, current_amount, quantity_difference, amount_difference. Tracked items also carry serial_no/batch_no or a bundle reference, plus use_serial_batch_fields, reconcile_all_serial_batch, and allow_zero_valuation_rate flags. A representative line, before and after a count:

{ item_code: "ITEM-001", warehouse: "Main Store",
  qty: 42, valuation_rate: 55.00,                  // counted
  current_qty: 50, current_valuation_rate: 50.00,  // ledger balance just before posting
  quantity_difference: -8,
  amount_difference: (42 * 55.00) - (50 * 50.00)   // = -190.00
}

The current_* fields are frozen at submit time, but go stale if a later backdated correction forces this document’s own Stock Movement Entries to be recomputed. A resync step re-derives each line’s current_qty/current_valuation_rate/amount_difference straight from the (now-updated) entries tied to that line, and rewrites the document-level difference_amount to match — for non-tracked items by walking the ledger for the balance immediately preceding this correction; for serialized/batched items by reading the frozen bundle instead, since a backdated quantity change cannot be inserted ahead of a bundle a later correction already consumed.

Movement Voucher (the general stock movement document) and Movement Voucher Line Every line can carry a source warehouse, a target warehouse, or both; which is mandatory is governed by the Movement Purpose master list (Material Issue, Material Receipt, Material Transfer, Material Transfer for Manufacture, Material Consumption for Manufacture, Manufacture, Repack, Send to Subcontractor, Disassemble, Receive from Customer, Return Raw Material to Customer, Subcontracting Delivery, Subcontracting Return): receipt-shaped purposes (Material Receipt, Receive from Customer, Subcontracting Return) need only a target warehouse; issue-shaped ones (Material Issue, Material Consumption for Manufacture, Return Raw Material to Customer, Subcontracting Delivery) need only a source; transfer-shaped ones (Material Transfer, Send to Subcontractor, Material Transfer for Manufacture) need both on the same line; Manufacture/Repack/Disassemble instead assign source vs. target per line, based on whether it is the finished item (or a co-/by-product/scrap line) versus a raw-material input.

When a line carries both, the posting funnel derives two Stock Movement Entries from it — an outward leg at the source and an inward leg at the target, cross-referenced so the inward leg’s rate can be recalculated once the outward leg’s cost is known. This is the same “one line, two ledger rows” shape the Balance Correction Line uses for tracked items, reused for a different purpose (transfer vs. count correction).

Quick Balance Lookup A single-record, non-transactional utility: given a warehouse and item (or scanned barcode), it calls the same balance/valuation query the correction document uses to pre-fill, purely to display quantity and stock value. It never posts and carries no item table — a read-only companion for spot checks.

3.2 Algorithm — from a count to a posting

For each line on submit:
  1. If not serialized/batch-tracked, fetch current qty and valuation rate
     as of (posting_date, posting_time), excluding this document's own rows.
  2. If entered qty/rate already equal the fetched current qty/rate (and any
     specified serial numbers match too), drop the line. If every line is
     dropped this way, the whole submit is rejected ("no change").
  3. Otherwise compute this line's difference = (qty * rate) -
     (current_qty * current_valuation_rate), added into the document's
     running difference_amount.
  4. A line left entirely blank (no qty, no rate, no current quantity) whose
     ledger nonetheless carries a nonzero stock value is a value-only
     adjustment: a delta-only movement (actual_qty = 0, quantity unchanged)
     that clears the residual value.
  5. Non-tracked lines are handed to the funnel as a single movement row
     with actual_qty = 0 and qty_after_transaction hard-set to the entered
     qty — the row does not describe "how much moved"; it dictates what the
     balance must equal at that instant. The Valuation Replay Engine (its
     Moving Average branch, or one of the two FIFO/LIFO Cost Layer Queue
     implementations, per the item's valuation method) treats that as the
     new anchor and derives the Stock Valuation Delta and cost-layer
     changes from it, exactly as for any other stock document.
  6. Tracked lines cannot just assert a new absolute quantity, because
     specific serial numbers or batch allocations must actually be removed
     and re-added. Two movement rows are produced instead: an outward row
     consuming the entire current allocation at its current rate, then an
     inward row establishing the counted allocation at the counted rate —
     net-equivalent to the single-row anchor, but expressed as two ordinary
     deltas because the replay engine cannot "reassign" a serial/batch in
     place.

Two edge cases: a customer-provided item’s rate is forced to zero (the company never owned the stock); and a missing rate on a changed line falls back, in order, to the ledger’s current rate, a buying price list entry in the default currency, then the item master’s rate — throwing only if none resolve.

3.3 Fetch and submission contract

A small set of whitelisted operations lets the interactive form build a correction without staff retyping every item:

GET  get_items(warehouse, posting_date, posting_time, company[, item_code])
  → one item: current qty/rate at that item+warehouse.
  → whole warehouse (no item_code): every stock item held there or in its
    sub-warehouses, pre-populated with current qty/valuation rate; an
    "ignore empty stock" flag skips items currently at zero.

GET  get_stock_balance_for(item_code, warehouse, posting_date, posting_time
                            [, batch_no])
  → current qty + rate for one line: used to pre-fill it and to re-check it
    against what the user typed before deciding to drop it.

submit() / cancel()
  → synchronous for small documents; documents over 100 lines are enqueued
    as a background job instead (§4), returning immediately.

An “Opening Stock” correction reuses this same fetch, typically against a warehouse with no movement posted yet — get_items returns every stock item known there at zero, and the resulting lines become the warehouse’s first-ever balances.

3.4 Error handling

Reserved-stock guard: a line whose counted quantity differs from its current quantity is checked against open stock reservations for that item/warehouse; if reserved units exist, submit and cancel are blocked until the reservation clears. This reads the Stock Position Record’s reservation fields directly — the commitment side, not the ledger-derived side.
Opening/no-history account guard: a profit-and-loss destination account is rejected whenever the purpose is “Opening Stock” or no stock movement has ever been posted for the company yet — an opening or first-ever correction must land on an asset/liability account.
Dimension-on-existing-balance guard: a line supplying a custom inventory dimension is rejected if that item/warehouse already carries a nonzero quantity — dimensions can only be introduced when the balance is created for the first time.
Duplicate line guard: two lines for the same item/warehouse (and, where relevant, the same batch/serial/dimension) are rejected as duplicates before submit.
Negative-value guards: negative counted quantity and negative counted rate are both rejected.
Missing-rate guard: a nonzero counted quantity with no resolvable rate anywhere in the fallback chain (§3.2) throws rather than posting at an implicit zero.
Empty-document guard: if every line is dropped as a no-op (§3.2, step 2), submit is rejected with an explicit “no change” error rather than silently succeeding with nothing posted.
Backdating and reposting: submit/cancel always checks whether later Stock Movement Entries already exist for the same item/warehouse past this document’s posting instant. If so, a Valuation Repost Job is queued to recompute everything downstream — not scoped to just this document’s own rows, and run as checkpointed background work per the shared repost mechanism rather than blocking the submit itself.

4. Scale and Reliability

Asynchronous bulk submit/cancel: a correction with more than 100 lines is enqueued as a background job (a longer timeout budget for submit, shorter for cancel) instead of running inline. On failure, the system comments the error and reverts the document — to Draft on a failed submit, back to Submitted on a failed cancel — so a large batch fails visibly and recoverably.
Idempotent no-op filtering (§3.2) means a routine “recount confirms no change” cadence doesn’t bloat the ledger with zero-effect movement rows — only lines that actually differ reach the posting funnel.
Backdated corrections cascade: a correction filed earlier than movements already posted for that item/warehouse forces a Valuation Repost Job across everything later — not bounded to just the corrected item unless item-based reposting decomposes the recompute per item. Many backdated corrections in a busy warehouse can queue a large recompute backlog; this is the same shared repost mechanism every other backdated stock document relies on.
Read load isolation: the Quick Balance Lookup is read-only and single-record, so more staff spot-checking counts adds only query load — never posting load or a document lock.
Reservation checks turn a race into a block: rather than letting a correction and a concurrent stock reservation both assume the same units are available, the reserved-stock guard refuses the correction at submit/cancel time until the conflict is resolved by hand.

5. Trade-off Analysis

Decision	Trade-off
Represent a correction as an absolute anchor (`qty_after_transaction` set directly, `actual_qty` forced to zero) instead of a computed delta	Lets staff state “there are now 42 units” without subtracting themselves, landing on exactly the counted figure regardless of concurrent posting. Cost: `actual_qty` is deliberately meaningless on these rows, so downstream logic reading it alone (instead of the anchor semantics) misreads a reconciliation as “zero units moved.”
Serialized/batched lines posted as two ordinary delta legs instead of one anchor row	Reuses the same replay engine and cost-layer mechanics as every other movement, no serial/batch special case in valuation. Cost: a correction on a tracked item always produces two ledger rows instead of one, doubling the audit trail for one conceptual correction.
No-op lines silently dropped at validation time rather than posted as zero-effect entries	Keeps the ledger free of noise from routine “recount matched” runs. Cost: a saved document’s item table can end up shorter than what the user entered, surfaced only as a passing notice rather than a hard confirmation.
A correction’s difference always books to one shared Stock Adjustment Account rather than a cause-specific account	One place to see the aggregate cost of corrections. Cost: genuine shrinkage/loss and a simple data-entry fix land in the same account, with no attribution split.
Large corrections queue as an asynchronous background job past a fixed line-count threshold	Keeps a big physical-count upload from timing out the request. Cost: submit no longer confirms success synchronously — the user must check back, and a failure reverts the whole document rather than partially applying it.
Backdated corrections trigger an unconditional Valuation Repost Job rather than a scoped, item-only recheck (unless item-based reposting is separately enabled)	Guarantees every downstream valuation stays correct after a backdated fix. Cost: unpredictable background load — one backdated opening correction on a long-lived item can trigger a recompute spanning a large slice of history.

6. What to Revisit as the System Grows

Cause attribution on the Stock Adjustment Account: as correction volume grows, tagging why a difference occurred (miscount, damage, theft, data-entry fix) would let the shared account be split or reported on by cause rather than lumped into one balance.
Stronger surfacing of dropped no-op lines: the current behavior (silent removal plus a passing message) is easy to miss in a busy session; a harder confirmation step becomes more valuable as bulk-fetched corrections routinely include many unchanged lines.
Bounding backdated repost blast radius: item-based reposting already exists as an opt-in way to decompose one large recompute into per-item jobs; making that the default (or auto-selecting it based on how much later history exists) would reduce the chance of one backdated opening correction stalling the repost queue.
Explicit reservation-conflict resolution path: today the reserved-stock guard only blocks; as reconciliation volume grows in warehouses with active reservation-based fulfillment, a guided “release then reconcile then re-reserve” flow would cut manual back-and-forth.
This document runs somewhat above its calibration band: the source genuinely supports it, since a correction’s behavior only makes sense described alongside the general movement document it shares a posting funnel with (source/target warehouse taxonomy, the two-legged posting shape), and that comparison earns its space rather than padding the count-to-correction story on its own.