DEV Community

Samcorp
Samcorp

Posted on

Reconciling Inventory Across Three Systems During Cutover

The ERP migration looked ready. Customers, products, and purchase orders were loaded, and the interfaces were green.

Reconciling Inventory Across Three Systems During Cutover

Then we compared inventory across three systems—and the numbers did not match.

That was when the real ERP cutover problem began.

Three systems gave us three different answers.

Legacy ERP:     184,732 units
WMS:            183,941 units
New ERP:        185,106 units
Enter fullscreen mode Exit fullscreen mode

Every number was technically explainable.

None was acceptable for go-live.

That became the most important lesson from the ERP cutover:

Inventory reconciliation is not about proving that one total matches. It is about proving that every system agrees on what each quantity means at the exact moment ownership changes.

Here is how we approached the problem.

Note: This is a representative engineering post-mortem. The systems, quantities, and examples are illustrative rather than customer production data.

The Three Systems

Our inventory existed across three operational systems.

Legacy ERP

The financial system of record.

It knew about:

  • item balances,
  • inventory valuation,
  • purchase orders,
  • transfers,
  • adjustments.

Warehouse Management System

The operational warehouse view.

It knew exactly where stock physically existed:

Warehouse
Bin
Lot
Serial
Inventory status
Enter fullscreen mode Exit fullscreen mode

New ERP

The system we were preparing to activate.

It needed a correct opening inventory position before new transactions could start.

At first, the reconciliation looked straightforward:

Legacy ERP
     ↓
Compare
     ↓
WMS
     ↓
Load
     ↓
New ERP
Enter fullscreen mode Exit fullscreen mode

A structured ERP implementation should include trial migrations, reconciliation, integration testing, business sign-off, and post-go-live monitoring—not just the final data load.
It was not.

Inventory Was Not One Number

Our first mistake was comparing total quantity.

Suppose all three systems reported roughly:

185,000 units
Enter fullscreen mode Exit fullscreen mode

That looked encouraging.

But total quantity could hide serious errors.

For example:

SKU Location Legacy ERP WMS New ERP
A100 Chicago 500 500 500
A100 Dallas 300 250 300
B220 Chicago 200 250 200

The grand total still matched.

Operationally, it was wrong.

So the reconciliation grain became:

SKU
+
Warehouse
+
Inventory status
+
Unit of measure
Enter fullscreen mode Exit fullscreen mode

For controlled items, we went deeper:

SKU
+
Location
+
Lot / Serial
+
Status
Enter fullscreen mode Exit fullscreen mode

A matching grand total was no longer enough.

The First Variance: Timing

The biggest source of differences was not bad data.

It was timing.

Consider this sequence:

10:01  ERP export starts
10:03  Warehouse ships 20 units
10:05  WMS snapshot starts
10:08  ERP receives shipment confirmation
Enter fullscreen mode Exit fullscreen mode

Now compare both systems.

They represent different moments.

Neither system is necessarily wrong.

This is why reconciliation without a defined cutoff timestamp is meaningless.

We introduced one rule:

All inventory comparisons must represent
the same business moment.
Enter fullscreen mode Exit fullscreen mode

That meant defining:

  • when warehouse transactions stop,
  • when ERP posting stops,
  • when interfaces pause,
  • which transactions belong before cutover,
  • which transactions belong after cutover.

The timestamp became part of the reconciliation evidence.

We Needed a Real Freeze Window

Initially, we hoped interfaces could continue running while we reconciled.

That created a moving target.

Every time we fixed one variance, another transaction changed the balance.

The final cutover sequence became stricter:

Stop warehouse transactions
        ↓
Pause integrations
        ↓
Complete in-flight messages
        ↓
Freeze legacy inventory
        ↓
Take final snapshots
        ↓
Reconcile
        ↓
Load new ERP
        ↓
Reconcile again
        ↓
Go / No-Go
Enter fullscreen mode Exit fullscreen mode

The freeze was inconvenient.

But a reconciliation against continuously changing inventory was worse.

A controlled ERP data migration strategy needs a defined freeze window, final delta handling, reconciliation rules, and clear go/no-go criteria before production starts.

The Second Variance: Inventory Status

The WMS did not simply know that 100 units existed.

It knew their status.

For one SKU:

Available:       70
Allocated:       15
Quality Hold:    10
Damaged:          5
-------------------
Physical:       100
Enter fullscreen mode Exit fullscreen mode

The legacy ERP might display:

On Hand: 100
Enter fullscreen mode Exit fullscreen mode

while the new ERP imported:

Available: 100
Enter fullscreen mode Exit fullscreen mode

The totals matched.

The business meaning did not.

That could allow damaged or quarantined inventory to be sold.

So we created explicit status mappings.

WMS AVAILABLE      → ERP AVAILABLE
WMS QA_HOLD        → ERP QUALITY_HOLD
WMS DAMAGED        → ERP BLOCKED
WMS ALLOCATED      → ERP RESERVED
Enter fullscreen mode Exit fullscreen mode

Unknown statuses were not defaulted.

They became cutover exceptions.

That decision prevented silent inventory corruption.

The Third Variance: Units of Measure

Then we found quantities that were mathematically correct but represented different units.

One system stored:

1 CASE
Enter fullscreen mode Exit fullscreen mode

Another stored:

12 EACH
Enter fullscreen mode Exit fullscreen mode

A third had:

1
Enter fullscreen mode Exit fullscreen mode

without enough context.

The reconciliation needed both:

Quantity
+
Unit of measure
Enter fullscreen mode Exit fullscreen mode

before comparison.

So we normalized quantities into the agreed base unit.

Conceptually:

function toBaseUnit(quantity, conversionFactor) {
  return quantity * conversionFactor;
}
Enter fullscreen mode Exit fullscreen mode

Then:

2 CASES × 12 EACH
=
24 EACH
Enter fullscreen mode Exit fullscreen mode

Only normalized quantities were used in reconciliation.

The Fourth Variance: In-Flight Transactions

Some inventory existed between states.

Examples included:

  • warehouse transfers,
  • receipts not yet posted,
  • shipments picked but not confirmed,
  • purchase receipts awaiting interface processing,
  • adjustments waiting in an integration queue.

These records were dangerous because every system could legitimately represent them differently.

Consider an inter-warehouse transfer:

Warehouse A
-100 units

In Transit
+100 units

Warehouse B
0 units until receipt
Enter fullscreen mode Exit fullscreen mode

If the new ERP loaded only warehouse balances and ignored in-transit stock, those 100 units effectively disappeared.

We therefore created a separate reconciliation category for in-flight inventory.

It could not hide inside ordinary on-hand quantities.

We Built a Canonical Inventory Snapshot

Comparing System A directly with System B and then System B with System C became difficult.

Each system used different field names and definitions.

Instead, we transformed every extract into the same structure.

Conceptually:

{
  "sku": "A100",
  "warehouse": "WH-01",
  "status": "AVAILABLE",
  "uom": "EA",
  "quantity": 250,
  "snapshotTime": "cutover"
}
Enter fullscreen mode Exit fullscreen mode

Then the process became:

Legacy ERP ──┐
             │
WMS ─────────┼──→ Canonical Format
             │          ↓
New ERP ─────┘      Reconciliation
Enter fullscreen mode Exit fullscreen mode

That made comparison much simpler.

The reconciliation engine did not need to understand three ERP/WMS schemas.

It only needed to understand one canonical inventory model.

The Reconciliation Was Automated

Forty thousand inventory combinations cannot be reliably compared in spreadsheets during a stressful cutover weekend.

We generated a reconciliation dataset.

Conceptually:

SELECT
    sku,
    warehouse,
    status,
    legacy_qty,
    wms_qty,
    new_erp_qty,
    legacy_qty - wms_qty AS legacy_wms_variance,
    wms_qty - new_erp_qty AS wms_new_variance
FROM inventory_reconciliation
WHERE
    legacy_qty <> wms_qty
    OR wms_qty <> new_erp_qty;
Enter fullscreen mode Exit fullscreen mode

The output looked more like this:

SKU Warehouse Status Legacy WMS New ERP Result
A100 WH01 Available 500 500 500 PASS
B210 WH01 Available 120 118 118 INVESTIGATE
C330 WH02 Hold 40 40 0 FAIL
D115 WH03 Available 72 72 72 PASS

Now the team reviewed exceptions rather than manually checking every record.

Every Variance Needed a Reason

We did not immediately “fix” every mismatch.

First, we classified it.

Our variance categories became:

TIMING
UOM_MAPPING
STATUS_MAPPING
SKU_MAPPING
IN_FLIGHT_TRANSACTION
MISSING_TRANSACTION
DUPLICATE_LOAD
MANUAL_ADJUSTMENT
UNKNOWN
Enter fullscreen mode Exit fullscreen mode

This was extremely useful.

For example:

SKU: A-1044
Variance: +24
Reason: UOM_MAPPING
Action: Correct case-to-each conversion
Enter fullscreen mode Exit fullscreen mode

versus:

SKU: B-2291
Variance: -3
Reason: TIMING
Action: Include receipt posted before cutoff
Enter fullscreen mode Exit fullscreen mode

A number without a reason is just another number.

A classified variance tells you what to fix.

We Added Control Totals

Detailed reconciliation caught SKU-level problems.

Control totals told us whether the entire migration was structurally sound.

We checked totals such as:

Total SKU/location combinations
Total physical quantity
Total available quantity
Total blocked quantity
Total inventory value
Total serialized units
Total lot-controlled quantities
Enter fullscreen mode Exit fullscreen mode

For example:

Expected inventory value:  $12,482,911.40
New ERP inventory value:   $12,482,911.40
Variance:                  $0.00
Enter fullscreen mode Exit fullscreen mode

But we never treated a matching inventory value as proof that quantities were correct.

Two errors can cancel each other out.

Detailed reconciliation and control totals served different purposes.

We Rehearsed the Cutover

The final reconciliation logic was not created during go-live.

We ran mock cutovers first.

Each rehearsal tested:

  • extraction duration,
  • load duration,
  • transaction freeze,
  • integration shutdown,
  • inventory mapping,
  • reconciliation scripts,
  • exception handling,
  • business sign-off,
  • rollback timing.

Every rehearsal revealed something.

One exposed an unmapped warehouse.

Another found duplicate SKU aliases.

Another showed that an integration queue could still post transactions after the supposed freeze.

That was exactly why we rehearsed.

The purpose of a mock cutover is not to prove the plan is good.

It is to discover where the plan is wrong while failure is still cheap.

Our Go/No-Go Rule Became Explicit

One of the worst things a cutover team can hear at 2 AM is:

“The numbers look close enough.”

We defined acceptance rules before the final weekend.

Conceptually:

Critical inventory variance:
Must equal zero.

Unknown SKU mapping:
Must equal zero.

Unknown inventory status:
Must equal zero.

Unprocessed integration messages:
Must equal zero.

Inventory valuation:
Must reconcile to approved control total.
Enter fullscreen mode Exit fullscreen mode

Some organizations may define tolerances for particular non-critical controls.

The important part is deciding them before the cutover.

The person under schedule pressure should not also be inventing the acceptance criteria.

The Final ERP Cutover Flow

The final runbook looked approximately like this:

1. Stop business transactions
             ↓
2. Drain integration queues
             ↓
3. Freeze legacy systems
             ↓
4. Capture final inventory snapshots
             ↓
5. Normalize all three datasets
             ↓
6. Run source reconciliation
             ↓
7. Investigate exceptions
             ↓
8. Load opening inventory
             ↓
9. Reconcile new ERP
             ↓
10. Business sign-off
             ↓
11. Go / No-Go decision
             ↓
12. Resume operations
Enter fullscreen mode Exit fullscreen mode

The migration script was only one step.

The reconciliation process was what made the cutover trustworthy.

What Went Wrong

Looking back, our biggest mistakes were simple.

We Compared Totals Too Early

A matching grand total hid location- and status-level errors.

We Did Not Define the Cutoff Precisely Enough

Systems representing different moments cannot be reconciled meaningfully.

We Treated Inventory Status as a Label

It was actually business logic.

We Underestimated In-Flight Transactions

Inventory between locations or process states needed explicit treatment.

We Relied Too Much on Spreadsheets

Spreadsheets were useful for investigation, but automated reconciliation was safer for repeatable cutover validation.

We Started With Data Movement Instead of Reconciliation Design

Knowing how data would be proved correct should have come before writing the final migration process.

What Worked

The strongest decisions were:

  • define one cutoff timestamp,
  • freeze transactions before final reconciliation,
  • normalize every system into one inventory model,
  • compare at SKU/location/status level,
  • normalize units of measure,
  • isolate in-flight inventory,
  • automate variance detection,
  • classify every exception,
  • establish control totals,
  • rehearse the full cutover,
  • define go/no-go rules before go-live.

The architecture became:

Legacy ERP ──┐
             │
WMS ─────────┼──→ Normalize
             │       ↓
New ERP ─────┘   Reconcile
                     ↓
                 Exceptions
                     ↓
                  Sign-Off
Enter fullscreen mode Exit fullscreen mode

Simple architecture.

Strict controls.

The Biggest Lesson

The hardest part of inventory migration was not copying inventory into the new ERP.

It was agreeing on what inventory meant.

Was stock:

On hand?
Available?
Allocated?
In transit?
On hold?
Damaged?
Received but not posted?
Picked but not shipped?
Enter fullscreen mode Exit fullscreen mode

Three systems could all be correct according to their own definitions and still disagree with each other.

That is why reconciliation has to start with business meaning, not database columns.

Final Takeaway

A safe ERP cutover should not ask only:

Did the inventory load succeed?

It should ask:

Did we capture the same point in time?

Do SKU and location quantities agree?

Do statuses mean the same thing?

Were units normalized?

Are in-flight transactions accounted for?

Does inventory value reconcile?

Can every remaining variance be explained?
Enter fullscreen mode Exit fullscreen mode

If those questions cannot be answered, the inventory is not reconciled.

The most important artifact from our cutover was not the migration script.

It was the evidence that allowed the business to say:

These opening balances are trustworthy.

That is what reconciliation is really for.

Top comments (0)