Stock Reservation & Pick-List Fulfillment

A design reference for soft-locking stock against demand, and for allocating and packing it for dispatch

1. Requirements

1.1 Functional requirements

Soft-reserve available stock in a specific warehouse against an outstanding sales-order line, before the physical stock ledger is touched.
Support two granularities on the same record type: a plain quantity, or an exact assignment of serial numbers / batches for tracked items.
Never let a reservation exceed the order line’s still-undelivered quantity, or exceed what is actually free — actual stock minus whatever other reservations already claim.
Allow partial reservation when the full requested quantity isn’t available, as a configurable choice rather than an automatic fallback.
Advance a reservation’s own lifecycle automatically as delivery happens, so it self-closes without a separate manual step.
Let a warehouse team build a Pick List that aggregates demand across many sales-order (or manufacturing/material-request) lines, auto-suggests a source location per line — including batch or serial numbers — and tracks picked quantity separately from required quantity.
From a Pick List, either turn “picked” into a formal reservation for sales-order-linked lines, or generate the fulfilling document directly, depending on the list’s stated purpose.
Track fulfilment progress differently by purpose: percentage delivered for customer-facing lists, transferred quantity for internal-transfer lists.
Explode a bundled (“kit”) sales line into its real stock components, so reservation, picking and packing always operate on actual stock items, never the bundle label.
Provide a distinct, later-stage physical-packing record so a warehouse can subdivide a delivery into shipping cases with weights, independent of how it was picked.

1.2 Non-functional requirements

Concurrency safety: “quantity still free to reserve” must be computed under row-level locking, so two simultaneous reservation attempts against the same item/warehouse cannot both succeed against the same units.
Auditability: a reservation’s submit/cancel state must be unambiguous, and a submitted reservation must never be silently rewritten — amendment is disallowed outright; the only path is cancel-and-recreate.
Deliberately soft enforcement: reservation must not require locking rows in the physical stock-movement ledger, nor block unrelated stock-out transactions. This is an explicit design choice — see §3.3.
Consistency of derived counters: every place that displays “how much is reserved” (order line, warehouse balance, originating pick-list line) is kept in sync by direct, synchronous recomputation triggered from the reservation record’s own lifecycle, not a background job.

1.3 Constraints

Reservation is opt-in at the system level (one settings switch) and per demand line (a checkbox).
Reservation cannot be created against a group (non-leaf) warehouse.
A submitted reservation cannot be edited once any of it is delivered, or once it originated from a Pick List — both force cancel-and-recreate instead of in-place editing.
A sales order line already carrying reserved stock cannot be pulled into a new Pick List until that reservation is released — the two fulfilment paths are mutually exclusive per line, not layered.
The same reservation record type is reused, with voucher-type branching, for four internal manufacturing/subcontracting demand sources besides the sales order; this document focuses on the sales-order-to-delivery path and only sketches the manufacturing reuse.

2. High-Level Design

2.2 Allocation flow — how a Pick List chooses a location

The branching algorithm behind “suggest a source warehouse/batch/serial for this demand line”:

Reservation Entry — a submittable record, one per (item, warehouse, demand-line) claim. Fields identify the demand line served (a sales-order line, or one of four manufacturing/subcontracting demand types), track available_qty/voucher_qty/reserved_qty/delivered_qty, and (for the manufacturing-reuse types) transferred_qty/consumed_qty. reservation_based_on is “Qty” or “Serial and Batch” — in the latter case a child table of serial/batch sub-rows each carries its own delivered_qty, so five reserved serial numbers can be delivered two-at-a-time without losing track of which units shipped. A reservation can be chained to record that it was spawned from a Pick List line or an inbound supply event. Amendment is disallowed outright, and once any quantity is delivered — or the reservation is pick-list-sourced — it cannot be edited at all, only cancelled and recreated.

Pick List / Pick List Line — a Pick List aggregates demand line-by-line (purpose is Delivery, Material Transfer, or Material Transfer for Manufacture); its lines carry required qty, picked qty, reserved qty, delivered qty and transferred qty — three completion counters for three downstream fates. A line can reference a sales-order line, a Bundle Component Line when the sales line was a kit, or a material-request line for internal replenishment.

Bundle Component Line — auto-generated and rebuilt directly on the selling document (order, quotation, delivery document, invoice) whenever a line is a bundle/kit item. One row per real stock component, carrying its own actual/projected quantity snapshot from the Stock Position Record and its own ordered/picked/packed counters — this is the row reservation and picking actually operate against for a bundled sale, never the bundle’s own item code. Bundle completion is the limiting component: effective picked quantity is the minimum, across components, of (component picked qty ÷ component qty per bundle unit).

Packing Slip / Packing Slip Line — a separate, later-stage, physical-carton document created against one specific still-draft delivery document; unrelated to reservation or picking. Each line references either an ordinary delivery line or a Bundle Component Line (never both), recording how much has gone into a physical case (a case-number range, with net/gross weight), and writes packed_qty back onto the referenced line on save. It refuses a non-draft delivery document, an overlapping case-number range, and over-packing beyond a line’s remaining quantity. In short: the Bundle Component Line answers “what real stock makes up this sale”; the Packing Slip answers “how was it physically boxed” — unrelated mechanisms, neither derived from the other.

3.2 Reservation status lifecycle

The Closed/Partially Used branch only applies to the manufacturing-reuse voucher types, where a Stock Entry “transfers” reserved material into production rather than a delivery document “delivering” it. Cancelling a Closed reservation that a downstream production event already consumed is blocked until that consumption is undone first.

3.3 The soft lock, precisely

The only place a reservation constrains anything is when a new Reservation Entry is created or grown: the engine computes “quantity still free to reserve” as the Stock Position Record’s actual quantity at that warehouse, minus the sum of (reserved_qty - delivered_qty - transferred_qty - consumed_qty) across every other submitted entry for that item and warehouse, evaluated under row-level locking so two concurrent attempts cannot both succeed against the same units. That is the entire enforcement surface.

Nothing else consults outstanding reservations before letting stock leave a warehouse. An ordinary internal stock movement, a delivery document not linked back to the reserving sales-order line, or a stock-balance correction can all consume the very units a reservation claims, without being blocked or warned. The one place a delivery document does look at reservations is narrower than it sounds: when a delivery line carries a sales-order-line reference with an active reservation, the system forces the line’s warehouse to match the reservation’s warehouse — it constrains which warehouse the delivery draws from, not whether the stock is still physically there. This is why the lock is soft: it disciplines reservation bookkeeping and warehouse choice, not the physical stock-movement funnel described in the stock-ledger design.

3.4 Reserved-versus-delivered accounting, and the two aggregate counters

The Reservation Entry is the authoritative, per-instance record — the only place recording which claim exists, its serial/batch detail, and its own delivered/transferred/consumed progress. Two aggregate counters exist elsewhere, both written directly by the entry’s own lifecycle methods (submit, cancel, update-after-submit) — never derived from stock-movement history, and never touched outside that lifecycle:

The demand line’s own reserved-quantity counter — summed as (reserved - delivered - transferred - consumed) across every submitted entry targeting that exact line, pushed onto it each time a reservation is submitted, cancelled, or updated.
The Stock Position Record’s reservation-derived counter — the identical sum, grouped by item + warehouse across all demand lines, pushed the same way after every lifecycle event.

Both are pure re-aggregations, rewritten from scratch rather than incremented — self-correcting on a retried call, but not the same mechanism as each other. The Stock Position Record separately carries an older, independent “reserved” figure computed straight from open demand-line quantities regardless of whether reservation is switched on, and that older figure — not the reservation-derived one — is what actually feeds the record’s own available/projected-quantity arithmetic. A pick-list-originated reservation additionally rolls its total onto the originating Pick List Line’s own reserved-quantity field, a third place carrying a slice of the same number. None of the three derive from one another.

3.5 Delivery-side consumption

When a delivery document (or a stock-updating invoice) is submitted against a sales-order line, the system looks up that line’s outstanding reservations, oldest first, and consumes them: for a quantity-based reservation it reduces the gap between reserved_qty and delivered_qty by whatever is delivered, capped at what remains; for a serial/batch-based reservation, it matches actual serial numbers or batch quantities on the delivery against the reservation’s own sub-rows, incrementing each matched sub-row’s delivered_qty individually. After each entry updates, its status is recomputed and the Stock Position Record counter refreshed immediately — all before the stock ledger itself is posted for that delivery. Cancelling the delivery reverses the same walk.

3.6 Pick-list-to-reservation bridge

A Pick List’s “reserve picked stock” action computes, per sales-order line on the list, qty_to_reserve = picked_qty - stock_reserved_qty already on that line, and creates one Reservation Entry per line, chained back via the from_voucher fields. The reverse is guarded: a sales order already carrying reserved stock cannot get a new Pick List at all — picking and reserving are mutually exclusive per order line, not layered, though a Pick List can itself trigger reservation as an explicit follow-on step.

3.7 Error handling

Requesting more than the computed “free to reserve” amount throws with a breakdown of actual quantity, other outstanding reservations, remaining demand-line quantity, and the resulting ceiling.
Reservation against a group warehouse, a disabled batch, or with insufficient serial numbers available all throw before submit.
A Pick List rejects picking against an already-expired batch, or a picked quantity greater than the batch’s or warehouse’s actual balance.
Over-picking beyond a configured allowance throws both when building the Pick List and when the sales order’s own picked-percentage is recalculated.
A submitted reservation cannot be amended, and cannot be edited in place once delivered or pick-list-sourced — cancel-and-recreate is the only remedy.

4. Scale and Reliability

Concurrency control is row-level, not document-level. Both “quantity free to reserve” and “quantity already picked by other open Pick Lists” are computed under row locking at read time, but no single lock spans “check availability, then create the Pick List Line, then create the Reservation Entry.” Two writers racing the last unit via different entry points can each pass their own local check before one is rejected downstream.
Counter recomputation is synchronous and inline, not queued. Every entry submit/cancel/update writes the demand-line counter, the Stock Position Record counter, and (when applicable) the Pick List Line counter inside the same request — trustworthy without a background job, at the cost of extra synchronous work per lifecycle event.
No visible repair path for the reservation-derived balance counter. The Stock Position Record’s general “recalculate this row” tool rebuilds actual quantity, valuation, and the legacy order-based reserved figure, but not the reservation-derived counter — no one-click recompute exists if it drifts.
Aggregation cost scales with open reservation count per item/warehouse, since every availability check and counter refresh re-sums the live entry set rather than maintaining a running total — simple and self-correcting, but a full re-scan rather than an increment on every event.

5. Trade-off Analysis

Decision	Trade-off
Reservation is a soft, bookkeeping-only lock rather than a hard stock-level lock	Never blocks unrelated stock movement, but an unrelated stock-out can silently consume units a reservation still claims — the guarantee is “no double-reservation,” not “no double-use.”
One record type reused across five demand-voucher types (sales order plus four manufacturing/subcontracting flows)	Shared validation, status, and counter-rollup logic, at the cost of voucher-type branches scattered through the same methods.
Serial/batch reservations tracked as sub-rows with their own delivered quantity, not one entry per serial number	Compact records and simple aggregate math, at the cost of a second bookkeeping layer inside each entry.
Two independently-maintained aggregate counters (demand-line and warehouse level), plus a third on pick-list-originated lines	Each consumer reads a locally-relevant number without a join, but the same fact is duplicated three ways with no single canonical field.
Pick-list batch/serial selection is FIFO by creation date only, expired batches rejected as a separate late check	Simple, predictable order, but no proactive preference for stock closer to expiry among still-valid batches.
Reservation and plain picking are mutually exclusive per order line (hard block, not a warning)	Prevents a confusing dual-fulfilment state, at the cost of an explicit unreserve step before switching strategy.
Packing Slip is a fully separate, optional, late-stage document rather than folded into the Pick List or delivery document	Keeps carton bookkeeping out of the picking/reservation model, but a shipment’s “what’s in which box” data lives in a third place with its own rules.

6. What to Revisit as the System Grows

Add a maintenance/recompute action for the reservation-derived Stock Position Record counter, mirroring the general balance-recalculation tool that already exists for the record’s other fields.
Decide whether the reservation-derived counter should feed the same available/projected-quantity arithmetic the legacy order-based counter already feeds, or whether keeping them separate is intentional and worth documenting.
Consider expiry-aware ordering within pick-list batch selection, rather than creation-date-only FIFO among not-yet-expired batches.
For high-value or high-contention items, consider an opt-in hard lock mode, since the soft lock’s central gap — an unrelated stock-out consuming reserved units — may be unacceptable for some catalogs even though it is a reasonable default.
Extend concurrency protection to a single lock spanning “allocate on the Pick List” and “convert to a Reservation Entry,” rather than two independently-locked steps, once reservation volume makes the current race window a practical problem.