Production Planning & Material Requirement Planning

How finished-good demand from sales orders or material requests becomes a net-of-stock raw-material and sub-assembly requirement, and why the module runs at least three independently-implemented explosion walks rather than one

1. Requirements

1.1 Functional requirements

• Aggregate finished-good demand from either open sales orders or open manufacture-type material requests into a Production Plan: one row per finished good, per source document, per target warehouse.
• Optionally combine multiple demand rows for the same Bill of Materials into a single planning line, so ten sales orders each wanting the same assembled item produce one production requirement, not ten.
• Explode each finished-good requirement down through its Bill of Materials to produce two distinct outputs: a sub-assembly requirement list (intermediate items that themselves need to be built or subcontracted) and a raw-material requirement list (the actual purchasable/stockable items at the bottom of the tree) — netted against what is already on hand or already on order, not a gross bill-of-materials multiplication.
• Turn a submitted plan’s sub-assembly requirements into Work Orders (for items to build in-house) or subcontracted Purchase Orders (grouped by supplier, for items to have built externally), and its raw-material requirements into Material Requests, without double-creating either if run more than once.
• Let raw-material sourcing consider stock sitting in other warehouses, not just the target warehouse, converting what it finds there into internal transfer requests before falling back to a purchase or manufacture request for whatever remains unmet.
• Aggregate delivery demand across a date range (from sales order delivery schedules and material requests) into a Master Production Schedule, and back-schedule when each demand line must be released by subtracting a cumulative lead time — computed by walking down the item’s own Bill of Materials — from its delivery date.
• Let a manually authored demand forecast (fixed quantity per period, for a chosen set of items) feed into a Master Production Schedule as an alternative to real sales orders or material requests.
• Track a standing commercial commitment (a Blanket Order, selling or purchasing) against which individual sales or purchase orders can be raised over time, capped by a configurable overrun allowance.

1.2 Non-functional requirements

• Net, not gross, requirements: available stock and already-placed orders must reduce what a plan proposes to request or build; re-running the same plan against unchanged sales orders should not keep multiplying requirements.
• Idempotent regeneration: creating Work Orders or Material Requests from a plan a second time (after partial fulfillment) must account for quantity already ordered, not recreate the full original requirement.
• Traceability back to source demand: every generated Work Order, subcontracted Purchase Order, and Material Request must carry a back-reference to the specific plan row that produced it.
• Warehouse-scoped netting: “is there enough stock” is evaluated per warehouse (or an explicit tier of alternate warehouses), never as a single global stock figure.

1.3 Constraints

• The module implements multiple, independently-written recursive descents through a Bill of Materials rather than one shared explosion routine — a sub-assembly netting walk, a raw-material explosion walk, a read of the Bill of Materials’s own pre-flattened materials list, and a stock-aware sub-assembly-materials walk. Each solves a related but distinct sub-problem and takes different inputs; none is a thin wrapper around another.
• Operations and shop-floor scheduling are out of scope for a Production Plan itself — it only reaches into that machinery indirectly, at the moment it creates a Work Order.
• A Master Production Schedule’s demand-aggregation and lead-time back-scheduling are real, working calculations; its own submit action is not — see §3.2 and the defect noted there.

2. High-Level Design

2.1 Component diagram

2.2 Data flow — from a submitted plan to generated documents

3. Deep Dive

3.1 Data model

Production Plan — the root record: get_items_from (Sales Order or Material Request), warehouse scope, and a set of behavior toggles — combine_items (merge same-BOM demand rows), include_subcontracted_items, include_non_stock_items, ignore_existing_ordered_qty, consider_minimum_order_qty, include_safety_stock, skip_available_sub_assembly_item. Status (Draft/Submitted/Not Started/In Process/Completed/Closed/Cancelled/Material Requested) is derived the same way a Work Order’s is: In Process once anything has been produced or ordered, Completed only once every Assembly Requirement is fully produced and every linked, still-relevant Work Order also reads Completed, Material Requested if any raw-material row still shows a requested quantity, and Closed only via an explicit action, exempted from the automatic recomputation.

Assembly Requirement (the po_items row — one finished good) — planned_qty/pending_qty/produced_qty/ordered_qty, the Bill of Materials used, and (when the demand source was a sales order) a sales_order/sales_order_item back-reference. include_exploded_items controls whether this row’s raw-material pull uses multi-level explosion or stays at the top BOM level alone. A temporary_name and matching item_reference on the linked Production Plan Item Reference exist purely to fix up linkages between rows that were both constructed client-side, unsaved, in the same request — a plumbing detail, not planning logic.

Sub-Assembly Requirement (sub_assembly_items — an intermediate item the finished good’s BOM needs) — qty/required_qty/ordered_qty/received_qty/wo_produced_qty, a bom_level/indent pair recording how deep in the tree this row was found, and type_of_manufacturing: In House (default, becomes a Work Order), Subcontract (defaulted from the item’s own subcontracted-item flag; becomes a grouped Purchase Order), or Material Request (skips manufacturing entirely — this sub-assembly is simply requested as if it were a raw material).

Raw-Material Requirement (the mr_items row — the actual Material Request output) — quantity (the net figure actually proposed), required_bom_qty (the gross, pre-netting figure), projected_qty/actual_qty/ordered_qty/reserved_qty_for_production (a snapshot of the warehouse Bin at computation time), material_request_type, and sub_assembly_item_reference — populated only when this raw material’s demand was attributed back to a specific Sub-Assembly Requirement rather than directly to a finished good, which is what lets make_material_request avoid throwing when a plan’s raw materials were fetched before its sub-assemblies were.

Master Production Schedule — a separate, upstream document: aggregates delivery demand (from sales order line items, sales order per-line delivery schedules where a sales order uses more than one delivery date, and open material requests) grouped by (item_code, delivery_date) into one row per Demand Line, each carrying planned_qty, cumulative_lead_time, and a back-scheduled order_release_date.

Sales Forecast — the thinnest document in this plan: a manually authored demand generator, not a statistical forecast. For each selected item, it stamps out one row per period (weekly or monthly, for a chosen number of periods) with demand_qty fixed at 1.0 — there is no seasonality, trend, or historical-sales calculation anywhere in this record; “forecast” here means “a user-declared repeating demand quantity,” fed into a Master Production Schedule the same way a real sales order’s demand would be. Its status field defines an MPS Generated option that no code path in this record ever assigns — only Cancelled (via discard) is ever set; Planned is simply whatever the field defaults to.

Blanket Order — a standing commercial commitment (selling or purchasing), independent of both documents above: per-line qty and a live ordered_qty pulled by summing whatever submitted Sales Order or Purchase Order lines reference this Blanket Order. A customer- or supplier-specific item code cross-reference is copied onto each line at save time. An overrun guard, invoked from the Sales/Purchase Order side rather than from the Blanket Order itself, caps how far a new order’s quantity against one line can exceed that line’s remaining (unordered) quantity, by a configurable allowance percentage.

3.2 Algorithm — at least three explosion walks, not one

The Net Requirements Walk (sub-assembly netting, genuinely recursive). For each Assembly Requirement, this walk descends the Bill of Materials level by level (using the same child-listing helper the interactive BOM tree view uses), and at each sub-assembly node it can do something the raw-material walks never do: net the this-level demand against this-level available stock before deciding how much to pass down. When skip_available_sub_assembly_item is enabled, a sub-assembly with enough projected stock to cover its own demand contributes nothing to the requirement list at all, and the recursion continues downward only with whatever shortfall remains — a genuine gross-to-net conversion applied at every tier of the tree, not just at the leaves. Without that setting, the walk still records every sub-assembly at every level, but with the full gross requirement.

The Material Explosion Walk (raw materials, also genuinely recursive, and independent of the walk above). A second, separately implemented recursion descends from a Bill of Materials’s own raw-material rows, multiplying quantities down through each nested sub-assembly’s own Bill of Materials, honoring include_subcontracted_items (a subcontracted sub-assembly’s own materials are only pulled in if this flag is set — otherwise the subcontracted item itself is treated as the leaf) and phantom items (whose components are always pulled in, since a phantom sub-assembly is never itself a real stock movement). This walk reads live BOM Item rows and re-derives quantities itself; it does not consult the Bill of Materials’s own already-flattened materials list at all.

The Precomputed Explosion Read (an alternative to the walk above, not a variant of it). When multi-level explosion is requested and subcontracted items should be included, the module takes a different path entirely: one query against the Bill of Materials’s own pre-computed flattened materials table (the same one described in the costing-engine document), with no recursion at all. This is the same “trust what the child already computed” pattern that governs BOM-level costing — except here it is a deliberate alternative code path selected by a specific combination of flags, not a fallback used everywhere multi-level explosion is requested.

A fourth walk, specific to netting against sub-assemblies already resolved as sufficient. When skip_available_sub_assembly_item is active, raw-material pulling uses yet another recursive function, keyed off exactly which (item, BOM) pairs the Net Requirements Walk already decided still need building — so a sub-assembly with enough stock (skipped from the build list) also contributes nothing to the raw-material list, while a sub-assembly that does need building contributes its own materials, scaled to only the shortfall quantity, not the gross demand.

Net-requirement math for the raw-material list itself. Once a candidate item+warehouse quantity is known (from whichever walk produced it), ignore_existing_ordered_qty decides whether that quantity is proposed as-is or first offset against the warehouse’s own projected stock — and that offsetting draws from a single running dictionary keyed by (item, warehouse) shared across every Assembly Requirement being processed in the same run, so if two different finished goods on the same plan both need the same raw material, the second one to be processed only sees whatever projected stock the first one didn’t already claim. consider_minimum_order_qty then floors a positive net requirement up to the item’s minimum order quantity, and a whole-number unit of measure ceiling-rounds the result. If the caller also supplies a list of alternate warehouses, any remaining requirement is first satisfied by converting it into internal-transfer requests against whatever stock those warehouses actually hold, in the order supplied, before whatever is still unmet falls through to the item’s normal purchase or manufacture request type.

Master Production Schedule’s cumulative lead time (real, and the one point where this document does its own recursion). For each Demand Line’s item, the schedule walks the item’s default Bill of Materials, summing each raw material’s own lead-time master record — recursing into any raw material that itself has a sub-assembly Bill of Materials — and adds the top item’s own lead time on top. order_release_date is then the delivery date minus that summed lead time, rounded up to a whole day. This calculation is complete and correct as written.

The submit action that is supposed to act on all of this does not exist. Submitting a Master Production Schedule enqueues a background call to a method named make_mrp on the document — a method that appears nowhere else in the codebase; no such function is defined on the Master Production Schedule class, on any mixin it uses, or anywhere else in the module. The demand-aggregation and lead-time back-scheduling above are genuinely computed and stored on the schedule; nothing currently converts a submitted schedule into a Production Plan, Work Order, or Material Request, and the queued job will fail the moment a background worker picks it up. This document reports only what the code actually computes (demand lines and release dates) and does not assert any downstream generation step, because none exists.

3.3 API/interaction contract (illustrative)

POST /production-plans  { get_items_from: "Sales Order", sales_orders: [...] }
  → pulls Assembly Requirements from open sales order lines

POST /production-plans/{name}/get-sub-assembly-items
  → runs the Net Requirements Walk, populates Sub-Assembly Requirements

POST /production-plans/{name}/submit
  → reserves stock; updates Bin reserved-for-production counters

POST /production-plans/{name}/make-work-order
  → creates Work Orders for every Assembly Requirement and every
    In-House Sub-Assembly Requirement; groups Subcontract rows into
    Purchase Orders by supplier

POST /production-plans/{name}/make-material-request
  → creates Material Requests grouped by (sales order, request type)
    from the netted Raw-Material Requirements

POST /master-production-schedules/{name}/get-actual-demand
  → aggregates demand lines and back-schedules order_release_date

POST /master-production-schedules/{name}/submit
  → enqueues a call to a non-existent method (see §3.2) — has no
    observable effect beyond the enqueue itself

3.4 Error handling

• Insufficient sales order data: submitting or validating a plan against a sales order with no items eligible for production is rejected outright, naming the sales order.
• Missing planning inputs: an Assembly Requirement with no Bill of Materials, or a Sub-Assembly Requirement row with no item code selected, blocks further processing with a row-specific message.
• Over-production on generated Work Orders is swallowed, not surfaced per row: if creating a Work Order for one Assembly Requirement would exceed its sales order’s allowed overproduction, that specific creation is silently skipped (the error is caught, not re-raised) while the rest of the batch continues — a user sees “N Work Orders created” without necessarily being told which requirement was skipped and why.
• Duplicate generation is prevented by netting, not by a lock: re-running Work Order or Material Request creation nets against quantity already ordered/requested rather than refusing to run a second time — the guard is arithmetic, not a status check.
• Subcontracted items must actually be subcontracted: requesting a “Subcontracting” material request type for an item not flagged as a subcontracted item is rejected by name.
• Insufficient warehouse selection: enabling skip_available_sub_assembly_item without choosing a sub-assembly warehouse is rejected before the netting walk runs.

4. Scale and Reliability

• Four different recursive/explosion mechanisms mean four different places a fix must be applied. A correction to how phantom items, subcontracted items, or multi-level netting behave in one of these walks does not automatically apply to the others — each was written to answer a specific question (sub-assembly netting, raw-material explosion, precomputed flat-list reads, or sub-assembly-aware raw-material netting) and none delegates to another.
• The shared consumed-quantity dictionary makes multi-line netting order-dependent. Because available stock for a shared raw material is claimed by whichever Assembly Requirement is processed first within one run, the same set of demand rows can produce a (correctly netted, but differently distributed) requirement depending on row order — this is a property of the algorithm, not a bug, but it means “why did this specific line get the shortfall” can require knowing iteration order.
• Master Production Schedule’s real cost today is the recursive lead-time computation, not any downstream generation — since the downstream generation step does not run. If it is repaired, the natural next question is whether that generation should run inline (like Work Order creation from a Production Plan) or genuinely in the background it is already queued for.
• Work Order and Purchase Order creation from a plan is synchronous and best-effort per row — a plan with many Assembly and Sub-Assembly Requirement rows creates every possible document inline, catching and discarding failures per row rather than batching or queuing the work.

5. Trade-off Analysis

6. What to Revisit as the System Grows

• Repair or remove the Master Production Schedule’s submit action. As written, submitting one queues a call to a method that does not exist; either implement make_mrp to genuinely turn demand lines into a Production Plan (or equivalent), or remove the enqueue and the accompanying “documents are being created in the background” message so the document does not claim to do something it cannot.
• Consolidate the explosion mechanisms where their outputs must agree — at minimum, document (or enforce) that the Precomputed Explosion Read and the Material Explosion Walk are expected to produce the same quantities for the same inputs, since today nothing checks that a change to one keeps it consistent with the other.
• Make the order-dependence of shared-stock netting explicit, e.g., a deterministic ordering rule or a documented rationale for iteration order, once plans commonly carry many demand lines competing for the same raw material.
• Surface skipped Work Order creations individually rather than only a final list-created message, so a user can see which specific Sub-Assembly or Assembly Requirement failed to produce a Work Order and why.
• Give Sales Forecast an actual statistical basis (or rename it) if it is meant to inform real planning decisions rather than serve as a manual, fixed-quantity demand stub.