Inventory Reconciliation: Physical, WMS and ERP

What reconciliation is, and what it is not

Inventory reconciliation is the act of explaining the difference between two or more records of the same stock. The output of a reconciliation is not a matching number. It is a decomposition: this much of the difference is caused by X, this much by Y, and this much is not yet explained.

The residual — the part nothing accounts for — is the entire point of the exercise. Everything else is arithmetic.

Most distribution businesses hold three records of the same inventory, and they disagree by design as well as by accident.

Physical: What is in the building. Observed by counting, and only true at the instant it was observed. A physical figure without a timestamp is not comparable to anything.
WMS: What the warehouse system records, location by location, updated continuously as work is confirmed. The operational truth, and the only one of the three that knows where stock is.
ERP: What the financial system records, usually at site and item level, updated in batches from the WMS. The valuation truth, and structurally behind the WMS by up to one interface cycle at any moment.

Because the ERP is fed from the WMS in batches, the two are supposed to disagree between runs. A reconciliation process that treats every ERP-to-WMS difference as an error will spend most of its effort on differences that were never wrong, and will lose the ones that were in the noise it generated.

A worked example: one item, three balances

An item is counted at 07:00. The case pack is 20 eaches and a pallet is 48 cases, so a full pallet is 960 eaches. All three balances are captured at the same moment.

Three-way reconciliation · one item · 07:00 Illustrative arithmetic

Physical, WMS and ERP balances with each pairwise difference decomposed
Comparison	Difference	Explained by	Action
Physical counted	4,860 EA	Blind count, 07:00	The only observation in the set.
WMS on hand	4,980 EA	Location sum, 07:00	—
ERP on hand	5,940 EA	Item balance, 07:00	—
ERP − WMS	+960 EA	One pallet issued at 23:52, after the 23:30 run	Timing. Verify it closes on the next run; adjust nothing.
WMS − Physical	+120 EA	Nothing yet — 6 cases exactly	Investigate. A pack multiple is not a counting error.
ERP − Physical	+1,080 EA	The sum of the two rows above	Never treat as one number. It is two unrelated findings.

Apparent exposure 1,080 EA · genuinely unexplained 120 EA · quantity that should be adjusted before investigation → 0 EA

The headline number is 1,080 eaches and it is the least useful figure in the table. Decomposed, it is two entirely separate facts.

960 eaches are in flight. A pallet was issued in the WMS at 23:52, after the 23:30 interface run and before the next one. The ERP has not been told yet. Nothing is wrong; the systems are eventually consistent and this difference will close on its own. The only correct action is to note it and confirm it closed.

120 eaches are unexplained — and they are exactly six cases, which is the detail that matters. A pack multiple is not what random counting error looks like. This 120 is worth a transaction replay; the 960 is worth a single confirmation.

Now consider the common failure. A reconciliation that compares ERP to physical, finds 1,080 and posts an adjustment for 1,080 does three things wrong at once: it corrects a difference that would have corrected itself, it creates a new error when the in-flight message lands, and it destroys the evidence attached to the 120 that was genuinely worth investigating. The books balance and every underlying fact is now worse.

Timing effects that are not errors

Before investigating any difference, eliminate the ones that are structural. All of these are normal, all of them close by themselves, and all of them are routinely reported as discrepancies.

Interface batch lag. Transactions posted in the WMS between two interface runs. The default explanation for almost every ERP-to-WMS difference, and the first to test.
Different capture instants. A WMS extract at 07:00 compared with an ERP report run at 09:15 describes two different mornings. Unless both timestamps are recorded, the comparison means nothing.
Count-to-post gap. A location counted at 06:00 and posted at 14:00 in a location that shipped at 09:00. The variance is manufactured by the process measuring it — covered further in cycle count discrepancies.
Goods received not invoiced. Physically present and receipted, financially not yet recognised. A real and expected difference between operational and financial views.
In-transit and inter-site transfers. Issued from one site and not yet received at the other. Belongs to neither location's on-hand and is frequently counted by both or by neither.
Allocated but not picked. Committed to an order, physically still on the shelf. Whether this reduces available stock differs between systems by design.
Status and hold differences. Quarantine, damage and QA hold quantities that one system excludes from on-hand and the other includes.
Period cut-off. A transaction landing either side of a close, correctly, in two different periods.

The single most valuable habit in reconciliation is to classify every difference as in-flight or unexplained before doing anything else, and to test the classification by re-pulling after the next interface run. A difference that closes was timing. A difference that survives is a finding — and the population of differences that never close is usually small, specific and highly actionable.

A repeatable reconciliation workflow

Nine steps, in order. Steps one to three are setup and are where most reconciliations are lost before they start.

Step 01

Fix a single instant, and record it

Every balance in the comparison must be as at the same timestamp, or the offsets must be written down and carried through the arithmetic. This is the most common reason a reconciliation produces differences nobody can explain: the numbers describe different moments.

Step 02

Write down the interface schedule

Batch run times, direction, document types, and the boundaries either side of your chosen instant. Without them, in-flight traffic cannot be distinguished from loss, and the entire exercise reduces to guessing.

Step 03

Agree the definitions

Does on-hand include allocated stock? Held and quarantined stock? Damaged? In-transit? The two systems will answer differently and both answers are legitimate. Reconcile the definitions before reconciling the numbers, or you will spend the day explaining a difference that is a semantic choice.

Step 04

Compute all three pairwise differences per item

ERP against WMS, WMS against physical, ERP against physical. Per item, never in aggregate — a site-level total lets offsetting errors cancel, which is the fastest way to reconcile to zero while being wrong everywhere.

Step 05

Classify every ERP-to-WMS difference

In-flight or unexplained, using the interface message log rather than inference. Each in-flight difference should correspond to identifiable messages sitting between the two systems at your chosen instant.

Step 06

Re-pull after the next run and confirm closure

The test that makes the classification real. Everything you called in-flight should now be zero. Anything still open was never timing, and it moves to the investigation list with strong evidence already attached.

Step 07

Check count timing before believing a physical variance

For every WMS-to-physical difference, compare the count timestamp with the post timestamp and pull the transactions in between. Variances manufactured by the count process are common and are indistinguishable from real ones in a summary report.

Step 08

Replay what survives

Whatever is left has no structural explanation. Rebuild the item's balance from a trusted endpoint and find the first divergence — the standard replay. Pack-multiple differences go to the front of the queue.

Step 09

Adjust only what has a name, and track the rest

Post corrections for differences whose mechanism is identified, with the mechanism in the reason code. Everything still unexplained stays on a tracked open list as a quantity, not as an adjustment. A reconciliation that ends with no open items and no named causes has not reconciled anything.

Signs the reconciliation is not working

It always balances to zero. Real operations produce unexplained residue. A process that never reports any is closing differences rather than explaining them.
The output is a single adjustment figure. The deliverable of a reconciliation is a decomposition. One number is a bookkeeping entry.
The same items appear every period. Recurrence is the signature of a mechanism, and it means the previous periods' adjustments resolved nothing.
Nobody can state the interface schedule. Without it, in-flight traffic and permanent loss are indistinguishable, and the classification step is fictional.
Differences are computed at site level. Item-level errors cancel in aggregate. A site that reconciles to within 0.1% may be wrong on most of its items.
Balances come from reports run hours apart. Timestamps not matched, so the difference partly measures the gap between the two extracts.
The residual is written off rather than tracked. Writing off ends the investigation and re-creates the same residual next period.
Reason codes on reconciliation adjustments are generic. A period-end adjustment coded RECON records that someone reconciled, and nothing else — see adjustment analysis.

Where the real differences come from

Once timing is eliminated, the residual almost always has one of these behind it.

Dropped interface messages: A transaction that errored and was never requeued, or was consumed and silently rejected. Signature: a difference that never closes and compounds across periods, invisible as an exception in both systems. The interface error log is the only place it exists.
Replayed messages: The mirror image — a message posted twice after a timeout and retry. Signature: a whole-transaction difference in the ERP with a single corresponding WMS transaction, sharing a source reference.
Definition drift: The two systems include different statuses in on-hand, and the definition changed on one side without the other. Signature: a difference that appears on a date and applies to every item with held or damaged stock.
Receiving and UOM faults upstream: The WMS balance was wrong before the ERP ever saw it, so both systems agree and both are wrong. Reconciliation between systems cannot detect this — only the physical count can. See receiving errors and unit-of-measure errors.
Manual entries made directly in the ERP: A financial correction posted in the ERP with no corresponding warehouse transaction. Permanently unreconcilable by design, and frequently the explanation for a difference nobody in the warehouse can account for.
Inter-site transfers: Issued by one site, not yet received by the other, and owned by neither in between. A structural blind spot that grows with the number of sites.
Genuine physical loss: The residual after everything else is eliminated. This is the only defensible way to arrive at a shrink figure, and it is arrived at by subtraction rather than by assumption.

Data and fields to inspect

WMS location inventory snapshot — item, location, LPN, quantity, UOM, status, allocated quantity, captured at a stated instant.
ERP item balance — site, item, quantity, valuation, status buckets, and the report's own as-at timestamp.
Physical count detail — location, item, counted quantity, count timestamp, post timestamp, blind flag, counter.
Interface schedule and message log — batch run times, message ID, direction, document type, status, error text, retry count. The single most important export for this work and the one least often requested.
WMS transaction history for the window either side of the reconciliation instant, with transaction and posting timestamps separate.
Status and hold definitions in both systems — which statuses each counts as on-hand.
In-transit and inter-site transfer records — issued quantity, received quantity, issue and receipt timestamps, owning site.
Goods-received-not-invoiced report — to separate an expected financial timing difference from an operational one.
ERP manual journal entries against inventory — user, date, amount, narrative. Differences with no warehouse transaction usually live here.
Adjustment history in both systems — including reconciliation adjustments from prior periods, so recurrence can be measured.

Reconciliation vs. adjustment

These are opposite activities that share a calendar slot, which is how they came to share a meaning.

Reconciliation explains a difference. Its output is a decomposition, a set of named causes, and a residual. It adds information.

Adjustment erases a difference. Its output is two numbers that agree. It removes information — specifically, the evidence that would have identified the cause.

An adjustment is a legitimate final step of a reconciliation, once a difference has a named mechanism. It is not a substitute for one. The distinction is visible in what a period-end pack contains: a reconciliation produces a list of causes with quantities, and an adjustment-led process produces a total.

The practical consequence is that adjustment-led reconciliation is self-perpetuating. Each period's adjustment removes the trail that would have let the next period's investigator recognise the pattern, so the mechanism survives every cycle and the same items reappear indefinitely. Sites in this state usually have both excellent-looking period-end balances and a persistent sense that the numbers cannot be trusted — and both impressions are correct, because the balances are accurate as at the close and were made accurate by hand.

One test settles which activity a process is actually performing: after this period's reconciliation, can anyone say why the difference occurred? If the answer is a quantity rather than a mechanism, it was an adjustment. Reading the adjustment log as evidence rather than as housekeeping is covered in inventory adjustment analysis, and the broader method in root cause analysis.

The operational takeaway

Three balances, three pairwise differences, and an order of operations. Match the timestamps, write down the interface schedule, agree what on-hand means in each system — then decompose per item, never in aggregate.

Classify every system-to-system difference as in-flight or unexplained before touching it, and prove the classification by re-pulling after the next run. The in-flight set closes itself. What is left is small, specific and worth real investigation, and it is the only honest route to a shrink figure: arrived at by subtraction, after every structural explanation has been eliminated.

Adjust last, adjust only what has a name, and keep the unexplained residual as a tracked quantity. A reconciliation that ends with nothing open has not finished — it has stopped. Related: what a WMS data audit examines, and how to measure whether any of it is improving.