Turning a flat order backlog into a plan the floor can pick

Run the whole chain as one recorded job. One button drives the customer's planning and scheduling runs, waits for each, folds the committed jobs into the order list and reports fifteen stages into a run record. Every order gets a go or no-go build-readiness verdict with a reason, and picks run from a queue in SQL Server.

The order backlog arrived as a daily feed from the customer's systems. It said what had been ordered and when it was due, and very little else — not whether the parts were on hand, not whether the job had been released to the floor, not whether anyone had already looked at it. Answering those questions meant running the planning system by hand, exporting the result, and reconciling that export against a workbook somebody maintained on the side.

The feed had a trap in it. It never deletes a row. When an order comes off the customer's live backlog, its row simply stops being refreshed — so anything reading the table as a whole would accumulate dead orders forever and never notice one had gone.

• The planning and scheduling runs execute as background jobs the browser cannot see finish
• Job numbers only exist after a planning run, so nothing could be matched before one
• The go / no-go call, the planners' status column and their comments were all kept by hand in a workbook alongside the exported data
• Picking an order meant walking a handheld terminal through the same screens, unit by unit

One button that runs the whole wave planning chain, and a run record that says exactly where it has got to. The pipeline drives the customer's planning system through the planning run with the screen automation underneath, waits for it, drives the scheduling run, waits for that, narrows the job list to the jobs that are committed and ready to run, exports it, and then folds the export back into the order list — reconciling against the latest feed, matching jobs onto orders, pulling order status, processed and pending quantities and on-hand stock out of the warehouse system, and finally computing a go / no-go build-readiness flag with a reason attached.

Fifteen stages report their own progress into the run record. The screen polls it every five seconds and draws a timeline, with a countdown on the two stages known to be long waits. Reload the page halfway through and the timeline reattaches to the run still in flight, because the state lives in the database rather than the browser.

The second half is the pick queue. An order number and a drop pallet go in from a form; the processor marks each order as in progress in the shared queue table before a worker touches it. The picker screen refreshes every five seconds and shows five states, a duration, a running item count, the latest log line, and the full step-by-step log for any order on request.

• One planning run at a time, enforced by a row in the run table rather than an in-process flag, so a second request is refused outright instead of queuing behind the first
• The same reconcile runs unattended every two hours and skips its turn while a planning run is active
• A run that crashed without reporting failure counts as stale after an hour, so it cannot block the unattended reconcile forever
• Enrichment can be re-run from the last export on its own, so a failure in the last mile does not cost another full browser run
• The queue lives in SQL rather than in the web application's memory, so the pickers can be run as a standalone operating-system process that keeps working while the application is stopped — with an environment flag that shuts off the in-process worker so only one of them is consuming the queue
• Every step of a pick is written to a log against the order, so a failed run is read back line by line instead of reproduced

The hard part of this pipeline was never the clicking. It was that the planning and scheduling runs are background jobs on the customer's server: the browser fires them and returns immediately, and there is no reliable on-screen signal that they are done. Watching the page would have meant guessing.

So it doesn't. The automation records the exact instant it confirmed each run, then polls the planning system's own background-task history for a row whose task name matches and whose submission time falls inside a two-minute window either side of that instant. The obvious key — the task number — is a global sequence shared with every other user of that system, so it identifies nothing. The submission timestamp is what isolates our run from somebody else's. Each poll opens an autocommitting connection, because a pooled connection sitting on a transaction snapshot will cheerfully report a job as still running long after it has finished.

The wait is bounded at ten minutes, and every poll line goes to both the server log and the run's status feed. The operator watching the timeline sees what an engineer would see: the server's own clock, the row that matched, and whether its completion time is still empty.

Everything that writes to the order list follows one rule: the machine owns the system columns and never touches the columns a person typed into. A reconcile refreshes dates, quantities, statuses and part numbers on every surviving order, adds the ones that appeared and deletes the ones that fell off the feed. Status, planner comment, owner and resolution date are left exactly as they were, and only disappear when the order they belong to does.

What counts as 'fell off' is decided from the single most recent load timestamp in the feed table rather than from the table's contents — which is the only thing that makes deletion safe against a source that never deletes anything itself. And if that table comes back empty, the reconcile refuses to run at all rather than concluding that every order has vanished and taking every planner's notes with it. The enrichment steps that follow are update-only: they never insert and never delete, so re-running one is harmless, and a failed enrichment cannot invalidate a reconcile that has already committed.

The build-readiness rule was derived, not specified. There was no document — there was a report somebody maintained by hand. We reproduced its go / no-go column from the underlying data until the two agreed on every order but one, and that one turned out to be a manual override typed into the sheet. What came out is narrower than anyone would have written from scratch: an order is a go when it is already built or has been released to the floor, and everything else is a no-go with a categorical reason. Holds and on-hand coverage were deliberately left out — the reference report keeps an already-built order on the go side whatever happens to it afterwards, and an order that is neither built nor released lands on no-go regardless. Encoding what the report actually did mattered more than encoding what it looked like it ought to do.

The backlog is a live view rather than a snapshot somebody remembered to refresh. It reconciles itself every two hours whether or not anyone presses anything, planning runs on demand behind a visible timeline instead of twelve minutes of silence, and every order carries a build-readiness verdict with a reason rather than an opinion. Nine pivot and drill-down views are generated from the same data and export to a workbook that matches what is on screen; they are 3PL service and floor reports drawn from live data instead of a weekly export. The reconcile and the enrichment are batch jobs that restart cleanly: re-running one is harmless.

On the picking side, the work is a queue with a record instead of a sequence in somebody's head, which is what 3PL software that works the warehouse floor should look like. A stop request is honoured between steps and reports how much was picked before it landed. An order that genuinely needs a person — a split pick where serial numbers have to be scanned by hand — stops and escalates with the item, the pallet and the number of serials required attached, instead of failing with a stack trace and leaving someone to work out what happened. The same queue-and-log pattern fits most pick, pack and ship workflows.

Planning and picking, before and after