BOM Explosion & Multi-Level Costing Engine

How a nested Bill of Materials is flattened into a single materials list, how raw-material cost turns into a finished-good cost, and why that cost rollup is not a live recursive walk

1. Requirements

1.1 Functional requirements

Represent a finished or semi-finished item’s recipe as a Bill of Materials (BOM): a quantity of one item produced from a table of raw-material lines, an optional table of manufacturing operations, and an optional table of co-products/by-products/scrap.
Allow a raw-material line to itself point at another BOM (a sub-assembly), so a finished good’s recipe can nest arbitrarily deep — a car’s BOM references an engine’s BOM, which references a piston’s BOM, and so on.
Produce, for any BOM, a single flattened materials list covering every raw material at every nesting depth, with quantities scaled to the depth they were consumed at — so a downstream consumer never has to walk the nested structure itself to answer “how much of item X does one unit of the finished good need, in total.”
Compute a total cost for a BOM from three independently selectable raw-material pricing sources (current stock valuation, last purchase price, or a price list), plus operation time-and-rate costs, plus an allocation of that raw-material cost across the finished good and any declared co-products/by-products.
Detect and reject a BOM that would recursively reference itself, directly or through any chain of sub-assemblies, while still allowing an item to legitimately appear as its own raw material for rework scenarios.
Maintain exactly one default BOM per item among its active, submitted BOMs, and reflect that choice on the item master.
Offer a guided, wizard-style path (the BOM Creator) for building a multi-level sub-assembly structure interactively and then generating and submitting every level’s BOM as a background batch, without requiring the user to author each nested BOM by hand in the correct order.
Support a Phantom BOM: a sub-assembly that is never itself manufactured or stocked — its materials are exploded straight into the parent’s flattened list and its own operations/cost are absorbed into the parent, as if the intermediate level did not exist.
Support a reusable Routing (a named, ordered sequence of operations with workstation and timing) that can be copied wholesale into a BOM’s own operations table, and a reusable Operation master (with its own sub-steps, used only to total expected time) that a routing or BOM operation row points at.

1.2 Non-functional requirements

Idempotent re-save: saving a BOM whose inputs have not changed must not alter its recorded cost, and repeated Cost Rollup calls against an unchanged tree must converge rather than oscillate.
Bounded recursion detection: cycle detection must terminate even for large sub-assembly graphs, and must reuse a cached child-lookup rather than re-querying the whole tree on every check.
Currency consistency: every sub-assembly referenced by a BOM line must be denominated in the same currency as the parent BOM; the whole tree is priced in one currency per BOM, converted to the company’s base currency at that BOM’s own exchange rate.
Immutability of submitted history vs. live cost: once submitted, a BOM’s raw-material lines and structure are protected by the usual amend workflow, but its computed cost fields remain refreshable in place through an explicit action, without an amendment.
Background-safe batch generation: the BOM Creator’s multi-level generation must not block the interactive session — it is queued and its outcome (success or a captured failure trace) is visible afterward, not returned synchronously.

1.3 Constraints

A BOM’s raw-material cost is computed level by level, not by a single recursive descent through the whole tree in one pass — each level either prices a sub-assembly line off current stock valuation of that sub-assembly item, or off the sub-assembly BOM’s own last-saved total cost, depending on a per-BOM setting. Either way, the parent trusts a persisted number on the child; it does not open and recompute the child’s own tree.
The flattened materials list is built the same way: a sub-assembly’s contribution to the parent’s flattened list is read from that sub-assembly’s own already-computed flattened list, not recomputed from the sub-assembly’s raw items on the spot.
Upward cost propagation — a change at a low level reaching a BOM several levels above it — happens only through an explicit, whitelisted action that walks parent links one level at a time, transitively, when invoked; it does not happen automatically on every save of a low-level BOM.
Only one raw-material pricing source is active per BOM at a time (stock valuation, last purchase rate, or price list); there is no per-line override of the pricing source within a single BOM.
Fixed-asset items are disallowed as BOM raw materials outright.

The BOM Creator is a separate staging record, not a BOM itself: a user builds a tree of finished goods, sub-assemblies, and raw materials interactively before any real BOM exists. This is a branching procedure, not a linear one:

Because the loop processes the reversed leaves-to-root order, a sub-assembly’s own BOM always exists — and its number is known — by the time the level that consumes it as a raw material is built, which is what lets the wizard wire in bom_no references automatically instead of asking the user to look them up.

3. Deep Dive

3.1 Data model

Bill of Materials (BOM) — the root record: one target item, a quantity that recipe produces (not necessarily 1), a currency and conversion rate to the company’s base currency, a raw-material pricing source (Valuation Rate, Last Purchase Rate, or Price List), an is_phantom_bom flag, an is_active/is_default pair, and the computed cost fields (raw_material_cost, operating_cost, secondary_items_cost, total_cost, each mirrored in base currency). Autonamed as a prefix plus the item code plus a zero-padded index, computed by scanning existing names for that item (ignoring amended documents) and truncating the item portion if the combined name would exceed the platform’s name-length ceiling. Optionally publishes a public-facing page (show_in_website) with its own image/description fields, independent of everything else described here.

BOM Item — one raw-material line: item code, quantity in the line’s own unit and in stock units, a rate and amount, an optional bom_no pointing at a sub-assembly, and flags do_not_explode (treat this line as a plain material even though a sub-assembly is linked), is_phantom_item (mirrors the linked sub-assembly’s own phantom flag), sourced_by_supplier and a customer-provided-item check (both force the line’s rate to zero), and allow_alternative_item (declares the line substitutable; the substitution mechanism itself belongs to shop-floor execution, not this document). A line can also carry an operation or operation_row_id tag, associating it with a specific BOM Operation row when semi-finished-goods tracking is in use.

BOM Operation — one manufacturing step: an operation reference, a workstation or workstation type, time_in_mins, batch_size, and the computed operating_cost/hour_rate pair (base-currency mirrors included). set_cost_based_on_bom_qty switches the cost basis from “cost per batch” to “cost per unit times the BOM’s own quantity.” is_final_finished_good and finished_good mark the one row (at most) that yields the tracked semi-finished output when track_semi_finished_goods is enabled on the BOM; backflush_from_wip_warehouse and skip_material_transfer affect shop-floor material movement, covered in the execution document.

BOM Secondary Item — one co-product, by-product, scrap, or “additional finished good” line, distinguished by a type field. Carries its own cost_allocation_per (a percentage of the BOM’s raw-material cost assigned to this line) and process_loss_per/process_loss_qty. An is_legacy line is a pre-existing scrap row exempted from the cost-allocation formula — it keeps whatever cost it already had rather than being recomputed.

BOM Explosion Item — the flattened materials list: one row per distinct item (keyed by item code, or by item-code-plus-operation when the BOM tracks operations), each carrying stock_qty already scaled to “quantity needed per one unit of the root BOM’s quantity” and a qty_consumed_per_unit derived field. This table is rebuilt in full on every save that recomputes cost (existing rows are deleted, contributions are recomputed, rows are reinserted) — it is a materialized flattening, not an incrementally-patched one.

BOM Tree (coined; the in-memory recursive representation) — a lightweight object, distinct from the Explosion table, built on demand by actually recursing into each sub-assembly BOM (fetching each one from cache) to construct a full parent/child tree with per-node quantities and exploded quantities. This is the one place true top-down recursion is used to walk a BOM’s structure — it exists for display and comparison (tree views, level-order traversal for reporting), not for cost computation, which uses the flatter, cache-trusting mechanisms described in §3.2.

Routing — a named, reusable operation sequence, holding its own BOM Operation rows. Saving a Routing recomputes each row’s operating cost from its workstation’s hourly rate and enforces that sequence_id values are non-decreasing down the table. A BOM with a routing reference and no operations yet can pull the routing’s entire operations table in one action, converting the hourly rate into the BOM’s own currency.

Operation — a master step definition (e.g., a named process), independent of any one BOM or Routing, holding a workstation default and a table of Sub-Operations, each just a name and a time_in_mins. The only computed effect of the sub-operations table is a summed total_operation_time on the Operation master; a duplicate sub-operation, or a sub-operation naming the parent Operation itself, is rejected. Sub-Operations otherwise have no independent behavior — a child table with a validation rule and a sum, nothing more.

BOM Creator — the guided staging wizard: a root item/quantity/phantom flag, plus a table of BOM Creator Items representing every item and sub-assembly the user has added across every level, linked to each other by string-valued fg_reference_id/parent_row_no fields rather than by real parent-child document links (a row’s fg_item names the finished good it belongs under; fg_reference_id names the specific staged row that is that finished good, when the finished good is itself a staged sub-assembly rather than the wizard’s own root item). A status field mirrors docstatus (Draft/Submitted/Cancelled) with two additional computed values: In Progress while the background generation job runs, and Completed once every row marked expandable has a bom_created flag set and a submitted BOM exists for the root item. A failed generation captures the full exception trace into an error_log field and sets status = Failed.

BOM Creator Item — one staged row: item code, quantity, an is_expandable flag (computed — true whenever this row’s item code also appears as some other row’s fg_item, meaning it has its own staged children), bom_created, and the same rate/cost fields a BOM Item carries. Deleting a node cascades: removing an expandable row also removes every row staged beneath it.

A BOM’s own status is exactly this three-state docstatus lifecycle; is_active/is_default are independent flags layered on top of Submitted, not additional states — a submitted BOM can be active-and-default, active-and-not-default, or inactive, and cancelling always forces both flags off.

3.2 Algorithm — how cost and the flattened list actually propagate

Per-BOM recomputation (every save). On every save, a BOM recomputes exactly three totals for itself: operating cost (summed across its own BOM Operation rows, or a flat rate-times-quantity if operations are switched off in favor of a single finished-good-based operating cost), raw-material cost (summed across its own BOM Item rows), and secondary-items cost (summed across its own BOM Secondary Item rows). total_cost = operating_cost + raw_material_cost - secondary_items_cost. Nothing about this pass opens or recomputes any other BOM.

Sub-Assembly Rate Mode — the detail the plan singles out. For a BOM Item row that names a sub-assembly (bom_no set), the rate used in the raw-material-cost sum is not automatically that sub-assembly’s own computed cost. Two modes exist, selected per parent BOM (with one forced exception):

Stock Valuation mode (default): the row is priced exactly like an ordinary raw material — off the sub-assembly item’s own current stock valuation (bin-level average, falling back to the last stock ledger valuation rate, falling back to the item master’s own valuation rate), or off last purchase rate / a price list, depending on the parent’s chosen pricing source. The sub-assembly’s own BOM cost plays no role at all in this mode; the parent is pricing the intermediate item as if it had simply been purchased.
BOM-Derived mode: enabled by a per-BOM checkbox (set_rate_of_sub_assembly_item_based_on_bom), or forced unconditionally whenever the line is a Phantom BOM item. The rate becomes sub_assembly.base_total_cost / sub_assembly.quantity — a single field read directly off the sub-assembly’s own record as it was last saved. If the sub-assembly’s own inputs changed since it was last saved, this read returns a stale number; nothing in this pass re-derives it.

Either way, a parent BOM’s raw-material cost computation touches exactly one level of the tree at a time. A three-level structure (finished good → sub-assembly → sub-sub-assembly) only reflects a change at the bottom level once each level above it has, in turn, been re-saved (or explicitly rolled up — see below) after the level below it.

Explosion (the flattened materials list) follows the identical pattern. Building a BOM’s flattened list walks its own BOM Item rows once: a plain raw material contributes itself directly; a sub-assembly row instead pulls that sub-assembly’s own already-persisted BOM Explosion Item rows (a plain read of that child’s flattened table, scaled by the quantity consumed), not a fresh recursive walk into the sub-assembly’s raw materials. So a three-level explosion is only complete and current if every level below the top was itself re-saved (and thus re-exploded) most recently — exactly the same staleness shape as the cost computation above, and for the same structural reason: both trust a persisted artifact on the child rather than opening it.

Cost Rollup — the one explicit, genuinely-recursive-in-effect action. A whitelisted action recomputes a BOM’s own cost and, when its total actually changed and the caller did not suppress it, looks up every other submitted BOM whose BOM Item table references this BOM by number and invokes the identical action on each of them. Because each of those invocations repeats the same “did my own total change? if so, look up my parents” check, a rollup triggered at a low level does reach arbitrarily far up a deep tree — but only because each level, on being invoked, chooses to invoke its own parents next. It is iterative propagation through repeated single-level calls, not one function recursively descending and ascending a tree in a single call frame. A plain save (validate(), on every document save) always suppresses the parent walk; only an explicit Cost Rollup call defaults to walking upward.

Recursion guard. Before accepting a save, the guard resolves the full set of BOMs reachable from this one (a breadth-first walk using an item-to-children cache, not the BOMTree’s own recursive constructor) and checks two conditions: this BOM’s own number must not appear as a child anywhere in that reachable set, and this BOM’s target item must not be used, with a different BOM attached, anywhere in that set — the same item may legitimately recur without a BOM attached (the do_not_explode escape hatch), which is how a rework scenario can consume a finished good as its own input without tripping the cycle check.

Cost allocation across secondary items. cost_allocation_per on the BOM itself plus cost_allocation_per on every non-legacy secondary item must sum to exactly 100; each secondary item’s own cost is then raw_material_cost * cost_allocation_per / 100, and the finished good’s own allocated share is raw_material_cost * finished_good_cost_allocation_per / 100. A legacy secondary item is excluded from both the sum requirement and the formula — it is priced however it already was.

Operating cost, two mutually exclusive modes. With operations enabled, each operation’s cost is hour_rate * time_in_mins / 60 (or a fixed per-unit cost times the BOM quantity, if that operation is flagged to price per BOM quantity instead of per batch), summed across all operation rows. Without operations, a flat operating_cost_per_bom_quantity * quantity applies instead. The two modes are exclusive per BOM, not additive.

3.3 API/interaction contract (illustrative)

POST /boms/{name}/get-routing
  → copies the linked Routing's operations table onto this BOM,
    converting hour_rate into this BOM's own currency

POST /boms/{name}/update-cost   (Cost Rollup)
  { update_parent: true|false, update_hour_rate: true|false }
  → recomputes this BOM's own totals
  → if update_parent and the total changed: repeats this call
    on every submitted BOM that references this one as a sub-assembly

POST /boms/{name}/add-raw-materials
  { operation_row_id, items: [...] }
  → attaches raw-material rows tagged to a specific operation
    (used when tracking semi-finished goods per operation)

GET  /boms/diff?bom1=...&bom2=...
  → row-level added/removed/changed comparison across
    items, operations, secondary items, and the flattened list

POST /bom-creator/{name}/add-sub-assembly
  { fg_reference_id, bom_item }
  → stages a new expandable node under an existing staged row

POST /bom-creator/{name}/submit
  → docstatus becomes Submitted; enqueues background generation
  → status becomes In Progress, then Completed or Failed

3.4 Error handling

Recursion raises a dedicated error naming which BOM would become both an ancestor and a descendant of itself, with a hint to use the do_not_explode line flag when the intent is legitimate reuse of a finished good as its own raw material.
Cross-currency sub-assembly reference is rejected at save time: a BOM Item’s linked sub-assembly must be priced in the same currency as the parent BOM.
Cost-allocation mismatch (finished good plus non-legacy secondary items not summing to exactly 100 percent) blocks save outright.
Fixed-asset raw materials are rejected outright, naming every offending item in one message.
Missing workstation on an operation row, zero or negative operation time, and (only at submit, not at draft save) an operation with track-semi-finished-goods enabled and no raw materials or sub-assembly BOM attached to it are each rejected with a row-specific message.
A missing price under the “Price List” or “Last Purchase Rate” pricing source does not block save — it raises an informational alert and leaves the line’s rate at zero, since a BOM is often built before purchase history or a price list entry exists.
Deactivating or cancelling a BOM still referenced by an active, submitted parent BOM is refused, so a live sub-assembly link can never point at a BOM that has been taken out of service.
A guided-creation background failure never loses information: the raised exception’s full trace is written onto the BOM Creator record itself and the wizard’s status becomes Failed, rather than a bare “something went wrong” leaving the user to guess which level failed.

4. Scale and Reliability

Level-by-level pricing keeps a single save cheap, on purpose. Because a save only recomputes the BOM in front of it — reading, not recomputing, whatever a referenced sub-assembly last recorded — the cost of saving one BOM does not grow with the depth of the tree beneath it. The cost of correctness is pushed instead onto whoever is responsible for re-saving (or explicitly rolling up) every affected level after a low-level change; there is no background process here that does this automatically. That gap is exactly what the batch tooling covering bulk BOM updates exists to close.
The flattened-materials rebuild is proportional to the immediate table, not the whole tree, for the same reason — it reads each sub-assembly’s own already-flattened list rather than re-flattening the sub-assembly’s raw items, so its cost scales with the number of direct lines, not with total nested depth.
Recursion detection reuses a cached per-BOM child lookup rather than re-querying the whole reachable set on every save; the cache is invalidated on update, so a check immediately after a structural change still sees current data without paying for a fresh query on every unrelated save of the same BOM.
Guided multi-level generation is deliberately asynchronous — a staged tree that will ultimately produce many submitted BOM documents runs as a queued background job rather than inline in the submit request, so a large multi-level structure does not tie up an interactive session, and a mid-run failure is captured rather than silently truncating the batch.
BOM comparison is computed on demand, not maintained incrementally — the row-level diff between two BOM versions walks both documents’ child tables at request time; nothing here maintains a running changelog.

5. Trade-off Analysis

Decision	Trade-off
Level-by-level cost computation instead of one recursive descent per save	Keeps a single BOM’s save cost independent of tree depth, at the cost of every level above a change needing its own explicit re-save or rollup — correctness across a deep tree is an operational responsibility, not an automatic guarantee.
Two Sub-Assembly Rate Modes (stock valuation by default, BOM-derived opt-in / forced for phantoms)	Lets most sub-assemblies price like ordinary purchased materials (matching how they’re actually stocked and consumed) while still allowing true cost-rollup pricing where wanted — at the cost of two BOMs referencing the same sub-assembly differently, which is easy to misread as inconsistency rather than a deliberate per-parent choice.
Flattened materials list rebuilt in full on every recomputing save rather than patched incrementally	Simple and always internally consistent for the BOM that owns it, at the cost of a full delete-and-reinsert on every save that touches cost, even when only one line changed.
Upward Cost Rollup as an explicit, opt-in action rather than an automatic trigger on every child save	Avoids an unbounded cascade of resaves rippling through every ancestor on every minor edit, at the cost of stale ancestor costs being invisible until someone remembers to roll them up.
Recursion guard walks a cached parent/child lookup (breadth-first, iterative) rather than the same recursive BOMTree used for display	Keeps cycle detection cheap and cache-friendly independent of how expensive a full display tree is to build, at the cost of maintaining two separate tree-walking mechanisms for what is conceptually one graph.
Guided multi-level creation as a separate staging document (BOM Creator) rather than authoring nested BOMs directly	Lets a user describe a whole multi-level structure before any BOM formally exists, and lets the system sequence creation bottom-up automatically — at the cost of a second document type and status lifecycle to reason about alongside the BOM itself, and a background job whose failure mode (a captured trace, not a live error) is less immediate than a normal validation error.
Phantom BOMs absorbed transparently into the parent’s flattened list and cost	Lets an intermediate grouping exist purely for BOM-authoring convenience without ever being manufactured or stocked in its own right, at the cost of a phantom sub-assembly’s own operations and materials becoming invisible as a distinct unit anywhere downstream that only reads the parent’s flattened view.

6. What to Revisit as the System Grows

An automatic, bounded propagation option for the common “I changed a leaf-level raw material rate and want every ancestor’s cost current” case, so correctness across a deep tree stops depending on someone remembering to trigger a rollup at each level — today’s explicit-only design is deliberately cheap but leaves staleness silent until acted on.
Surfacing staleness itself, e.g., a visible marker on a BOM whose sub-assembly lines were last priced or exploded before their referenced sub-assembly’s own last save, so a stale cost is at least detectable without recomputing everything to find out.
Unifying the two tree-walking mechanisms (the cached iterative lookup used for recursion detection, and the genuinely recursive BOM Tree used for display) if a future feature needs both cycle-safety and full-tree traversal in the same pass.
A visible audit trail for guided multi-level generation, beyond a single captured exception trace, once the BOM Creator is used for large enough structures that a partial failure midway through a many-level batch needs more than “read the trace and retry.”
Making Sub-Assembly Rate Mode a per-line choice rather than a per-BOM one, if real usage shows a single BOM legitimately wanting some sub-assemblies priced at stock valuation and others at BOM-derived cost simultaneously — today’s all-or-nothing setting (phantom items aside) cannot express that.