BOMCOST/i — Multi-Level BOM Explosion & Standard-Cost Roll-Up

BOMCOST/i is a discrete-manufacturing engineering-and-cost application: an item master, a multi-level bill-of-materials with effectivity dates and phantom assemblies, a trigger-maintained where-used index, a recursive multi-level explosion, a recursive standard-cost roll-up, and a low-level-code (LLC) computation. Unlike LOANSVC/i there is no RPG orchestration layer and no 5250 screen — BOMCOST/i is pure SQL PL. Every object is a DB2 for i function, stored procedure, trigger or view, reached by CALL / SELECT from STRSQL, an SQL script, or an embedded-SQL host program. This manual is the reference for the operator who runs the explosion and cost-roll cycle, and for the developer maintaining the recursive SQL PL. It is grounded entirely in the committed source (sqlpl-app-bom/src/schema.sql, src/routines.sql, and the test/bom_daily.mjs driver).

A. Overview & Architecture ↑ top

A.1 What it does

BOMCOST/i answers the two core questions of discrete-manufacturing engineering and standard costing:

• What does it take to build one of these? (BOM explosion.) Given a top-level item, flatten its multi-level bill of materials into the full list of components and the cumulative quantity of each required to build one top item — honoring effectivity dates, scrap uplift, phantom assemblies, and diamond (re-converging) structures.
• What does it cost to build one? (Standard-cost roll-up.) Compute each manufactured item's standard cost as the sum, over its active components, of component-cost × scrap-inflated quantity-per — recursively, so a parent's cost rolls up from its children's rolled costs. Purchased items carry a given cost; manufactured items are rolled.
• Low-level codes (LLC). Compute, for every item, the deepest level at which it appears as a component in any top-level explosion — the classic MRP key used to sequence a bottom-up cost roll so every child is costed before its parents.
• Engineering-change control. BOM lines carry non-overlapping effectivity date ranges, so the same parent/component pair can hold several revisions; the active revision is selected as-of a date.
• Structural integrity. A where-used reverse index is maintained by trigger, and a cycle guard (both a procedure-level and a BEFORE-INSERT trigger form, using a WITH RECURSIVE membership test) refuses any BOM edge that would make an item its own descendant.

A.2 Pure-SQL-PL architecture (no RPG, no 5250 screen)

BOMCOST/i lives entirely in the data layer. There is no SQLRPGLE driver and no DDS display file — the LOANSVC/i "SQL PL owns the logic, RPG orchestrates" split does not apply here because there is no RPG side at all. Everything is:

• SQL PL routines. Two LANGUAGE SQL scalar functions, five stored procedures (two of them recursive), five triggers, and one view — all in routines.sql, all in schema BOMCOST. The recursion is expressed two ways: a recursive scalar function (FN_COST_ROLLUP calls itself) and a recursive stored procedure (PR_BOM_EXPLODE calls itself), plus WITH RECURSIVE common-table-expression queries inside the cycle guards.
• Base tables + two global temporary tables. Four persistent tables (ITMAST, BOMLINE, WHEREUSED, COSTROLL) and two session-scoped GTTs (EXPLOSION_WORK for the flattened explosion result, VISITED for the cycle guard).
• Any SQL caller. The routines are reached identically from interactive STRSQL, an SQL script run under a job, or embedded EXEC SQL CALL in a host program. The bundled test/bom_daily.mjs driver calls them exactly as an operator would — it is the de-facto reference client.

The benefit for operations: the entire BOM/cost engine is one auditable place (the SQL-PL routines), callable from anywhere, with no compiled RPG object to maintain. Everything runs in schema/library BOMCOST.

A.3 Component & flow

  DATA ENTRY (guarded)          READ MODELS                CYCLE (recompute)
  --------------------          -----------                -----------------
  PR_BOM_ADD_LINE  --INSERT-->  FN_ACTIVE_QTY              PR_LLC_COMPUTE
    WITH RECURSIVE cycle          (effective qty+scrap)      -> PR_BOM_EXPLODE_TOP (per top item)
    guard, then INSERT          FN_COST_ROLLUP (recursive)     -> MERGE MAX(LEVELNO) -> ITMAST.ITLLC
         |                        (per-item std cost)
         v                      PR_BOM_EXPLODE_TOP         PR_COST_ROLLUP_ALL
     BOMLINE  ---triggers--->     -> PR_BOM_EXPLODE           (LLC-desc order)
       |  \    TR_BOMLINE_CYCLE    (recursive, -> WORK)       -> FN_COST_ROLLUP per M item
       |   \   (BEFORE INSERT)                                -> MERGE COSTROLL + UPDATE ITMAST.ITROLLED
       |    +--TR_BOMLINE_WHEREUSED_INS/DEL --> WHEREUSED
       |
       v
     GTTs:  EXPLOSION_WORK (flattened per-run component list)
            VISITED        (RUNID-scoped path guard, popped on exit)

A single explosion flows: operator (or the LLC procedure) calls PR_BOM_EXPLODE_TOP(top, asof, runid) → it clears this run's EXPLOSION_WORK/ VISITED, opens a SAVEPOINT, and calls the recursive worker PR_BOM_EXPLODE → the worker walks the tree, inserting one EXPLOSION_WORK row per real (non-phantom) component at each level, pushing/popping VISITED nodes as it descends and returns. A cost roll flows: PR_COST_ROLLUP_ALL loops manufactured items in LLC-descending order and calls the recursive FN_COST_ROLLUP per item, upserting COSTROLL via MERGE.

A.4 Object inventory

The full catalogue is 2 functions + 5 procedures + 5 triggers, over 4 base tables, 2 GTTs and 1 view. Sections D and F expand each. A sixth trigger, TR_BOMLINE_TRANSITION_PROBE, exists only as a documented platform probe in the driver and is not part of the loaded routine set (see F.3).

B. Online / Access ↑ top

B.1 STRSQL / CALL access — there is no 5250 screen

Honest statement: BOMCOST/i has no interactive display file, no subfile, and no online transaction program. It is a pure SQL-PL package. The operator equivalent of "run BOMCOST/i" is "open an SQL session and CALL a procedure or SELECT a function". Before invoking anything, the job's library/schema list must include BOMCOST — the tested job runs with LIBL = QSYS QGPL BOMCOST QTEMP and CURLIB = BOMCOST. Because EXPLOSION_WORK and VISITED are global temporary tables (session-scoped), an explosion's results live only for the life of the SQL session that ran it — read them back in the same session/job.

The two integer parameters worth calling out are P_RUNID / P_RUNBASE: they scope the VISITED cycle-guard rows to one logical run, so concurrent or repeated calls do not collide on the shared GTT. Pass a distinct run id per top-level explosion (the LLC procedure uses 900000 + n; the cost roll uses P_RUNBASE + sequence). The P_DEPTH argument to FN_COST_ROLLUP is the recursion depth and is always seeded 1 by callers.

B.2 Controls & audit workflow

Honest statement: BOMCOST/i has no maker–checker approval workflow and no audit-trail table (there is no LNAUD analogue). It is an engineering/cost-modelling package, not a posting ledger, so its control posture is structural-integrity enforcement in the data layer rather than segregation-of-duties. The controls the application actually has:

• Cycle-guarded data entry (two layers). PR_BOM_ADD_LINE is the primary insert path; it runs a WITH RECURSIVE membership test before issuing the INSERT and raises SQLSTATE 90001 if the new edge would close a cycle, and 90004 if an item is made its own component. TR_BOMLINE_CYCLE is the BEFORE-INSERT trigger form of the same rule, so even a direct INSERT that bypasses the procedure is guarded (its cheap self-reference check is what the driver exercises safely — see the platform note in F.3).
• Where-used index kept in sync by trigger. TR_BOMLINE_WHEREUSED_INS and TR_BOMLINE_WHEREUSED_DEL insert/delete the reverse-index row automatically on every BOM line change, so WHEREUSED is always an exact denormalized mirror of BOMLINE (the driver verifies 7 edges → 7 where-used rows, and that a diamond-reused component lists both parents).
• Effectivity control. Every read of a BOM line is date-qualified (P_ASOF BETWEEN EFFFROM AND EFFTO), so an engineering change is a matter of closing one revision's EFFTO and opening the next revision's EFFFROM — history is preserved, and a cost/explosion run for a past date still sees the revision that was active then.
• Runtime cycle guard. Independently of the insert-time trigger, both recursive routines carry a VISITED-based runtime guard (SQLSTATE 90001) and a depth ceiling of 40 (SQLSTATE 90002), so a cyclic or runaway structure can never loop the engine forever.
• Read-only derived columns via the view. TR_COSTED_BOM_IOI (INSTEAD OF INSERT on V_COSTED_BOM) reroutes an insert to the base BOMLINE and silently drops the derived COMPCOST, so the rolled-cost column cannot be written through the view.

In sum, the control model is structural-integrity + effectivity + cycle-safety, enforced in the data layer — appropriate for a BOM/cost engine, not an approval gate.

C. Batch / Cycle Procedures ↑ top

BOMCOST/i has no scheduled RPG job; its "batch cycle" is a fixed sequence of SQL-PL procedure calls — explode, compute low-level codes, roll costs — run as-of a chosen date. Every procedure and function takes an explicit P_ASOF DATE (there is no control-row table like LOANSVC/i's LNCTL; the date is a call parameter), so a run is fully parameterised at the call site. The natural driver is an SQL script (or the bundled test/bom_daily.mjs) submitted as a job.

-- the daily/periodic recompute cycle, as-of a date, from STRSQL or a script
CALL BOMCOST.PR_LLC_COMPUTE(DATE '2026-08-01');           -- 1. refresh low-level codes
CALL BOMCOST.PR_COST_ROLLUP_ALL(DATE '2026-08-01', 100000); -- 2. roll every manufactured cost
-- on demand: explode a single top item for a report
CALL BOMCOST.PR_BOM_EXPLODE_TOP('TOP1', DATE '2026-08-01', 1);

C.1 Full procedure / trigger set

C.2 The explosion / cost-roll cycle in detail

Explosion — PR_BOM_EXPLODE_TOP → PR_BOM_EXPLODE

The entry procedure clears this run's EXPLOSION_WORK/VISITED rows, opens SAVEPOINT SP_EXPLODE, and calls the worker with the top item as both top and parent at level 1 with cumulative quantity 1. The worker, for each active child (date-qualified, ORDER BY COMPONENT): computes EXQTY = P_CUMQTY × qtyper/(1-scrappct); if the child is a phantom (PHANTOM='Y') it writes no row for the phantom itself but recurses into the phantom's children at the same level (classic phantom pass-through); otherwise it inserts one EXPLOSION_WORK row and, if the child is manufactured, recurses at level+1. A missing component master is skipped via a CONTINUE HANDLER FOR NOT FOUND rather than aborting. On any exception the entry procedure rolls back to the savepoint and clears the run's VISITED, so a partial explosion never leaks.

Hand-oracle explosion of 1x TOP1 (from test/bom_daily.mjs) -- 8 rows,
SUB2 is a phantom so it never gets its own row; SUB1 recurs via the diamond:
  L1  TOP1 -> SUB1    exqty 2.105263   (2/(1-0.05))
  L1  SUB2 -> PART3   exqty 4          (phantom pass-through, parent kept as SUB2)
  L1  SUB2 -> SUB1    exqty 1          (diamond: SUB1 reused under the phantom)
  L1  TOP1 -> PART4   exqty 5
  L2  SUB1 -> PART1   exqty 6.315789 + 3        = 9.315789 total (both SUB1 paths)
  L2  SUB1 -> PART2   exqty 2.339181 + 1.111111 = 3.450292 total (both SUB1 paths)

Low-level codes — PR_LLC_COMPUTE

Resets every ITLLC to 0, then loops every top-level item (one that is never itself a component in an active BOM line), exploding each into EXPLOSION_WORK and folding the rows into a shared '*LLCSCAN' bucket. A final MERGE sets each component's ITLLC to the MAX(LEVELNO) seen across all roots. Hand oracle: TOP1=0 SUB1=1 PART1=2 PART2=2 PART3=1 PART4=1.

Cost roll-up — PR_COST_ROLLUP_ALL

Cursors over manufactured items in ITLLC DESC, ITEM order (deepest first — the bottom-up MRP sequencing), calls FN_COST_ROLLUP(item, asof, runbase+seq, 1) per item, and MERGEs the result into COSTROLL (update-in-place or insert), then updates ITMAST.ITROLLED. Because FN_COST_ROLLUP also recurses on demand, the LLC ordering is a performance/clarity convention, not a correctness requirement. Hand oracle: SUB1=11.555556 SUB2=17.555556 TOP1=45.633041.

C.3 Ordering & dependencies

• Data entry before any run. BOM edges must be added (via PR_BOM_ADD_LINE or a guarded insert) so BOMLINE and WHEREUSED are populated before an explosion or roll means anything.
• LLC before the cost roll (recommended). PR_COST_ROLLUP_ALL orders by ITLLC, so run PR_LLC_COMPUTE first to get the deepest-first sequence. It is not a hard requirement (the recursive function costs children on demand), but it is the clean bottom-up order and what the driver runs.
• Same session for GTT reads. An explosion's EXPLOSION_WORK rows are session-scoped; read them back in the same SQL session/job that ran the explosion.
• Distinct run ids. Give each top-level explosion / cost item a distinct RUNID so the shared VISITED guard rows never collide (the procedures do this automatically: 900000+n for LLC, runbase+seq for the roll).
• Same as-of date across a cycle. Pass the same P_ASOF to LLC and the cost roll (and to any explosion you reconcile against them) so all three see the same effective revisions.

D. Data Files (data dictionary) ↑ top

All objects are in schema/library BOMCOST, grounded in schema.sql. Costs are DECIMAL at scale 6; quantities are DECIMAL; scrap is a fraction (0.02 = 2%); effectivity is a real DATE. Four tables persist; two are global temporary tables.

ITMAST — Item master (PK ITEM)

Component scrap is a property of the usage (the BOM line), not the item — so there is no scrap column here; it lives on BOMLINE.SCRAPPCT.

BOMLINE — BOM edges (PK BOMID identity; PARENT/COMPONENT per effectivity revision)

The same (PARENT, COMPONENT) may appear more than once with non-overlapping date ranges — an engineering change. Reads select the revision where asof BETWEEN EFFFROM AND EFFTO.

WHEREUSED — Reverse index (PK COMPONENT, PARENT, BOMID)

Denormalized "where is this part used" index, maintained in exact sync with BOMLINE by TR_BOMLINE_WHEREUSED_INS / _DEL. One WHEREUSED row per BOM edge.

COSTROLL — Rolled-cost result (PK ITEM)

Upserted by PR_COST_ROLLUP_ALL via MERGE — one row per item, update-in-place on re-run (no duplication).

EXPLOSION_WORK — Flattened explosion (global temporary table)

DECLARE GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS WITH REPLACE — session-scoped; rows survive commits within the session but not across sessions.

VISITED — Cycle-guard path (global temporary table)

A row is inserted when a recursive routine enters a node and deleted when it leaves, so a node found already present (same RUNID) is an ancestor — a cycle — and trips SQLSTATE 90001. Popping on exit is what lets a legal diamond (a component reached by two different paths) reconverge without a false cycle.

V_COSTED_BOM — Costed-BOM view

Joins BOMLINE to COSTROLL (LEFT JOIN on component), exposing BOMID, PARENT, COMPONENT, QTYPER, SCRAPPCT, PHANTOM, EFFFROM, EFFTO plus the derived COMPCOST (the component's rolled MATCOST). Insertable only through TR_COSTED_BOM_IOI (INSTEAD OF INSERT), which reroutes to BOMLINE and drops the read-only COMPCOST.

Relationships

• BOMLINE.PARENT and BOMLINE.COMPONENT reference ITMAST.ITEM (logical FK; a missing component master is skipped, not enforced, during explosion).
• WHEREUSED.BOMID → BOMLINE.BOMID (trigger-maintained mirror).
• COSTROLL.ITEM → ITMAST.ITEM (rolled cost per item); ITMAST.ITROLLED mirrors COSTROLL.MATCOST.
• EXPLOSION_WORK and VISITED are scratch GTTs keyed by run, not persisted relations.
• V_COSTED_BOM joins BOMLINE and COSTROLL.

E. Operations Runbook ↑ top

E.1 Run the explosion / cost roll-up

Pre-checks: confirm the job's schema list includes BOMCOST; decide the as-of date (the effective revisions are selected by it); use the same as-of date for the whole cycle.

1. Enter / revise BOM edges as needed through the guarded path: CALL BOMCOST.PR_BOM_ADD_LINE(parent, component, qtyper, scrappct, phantom, efffrom, effto). An engineering change is: UPDATE BOMLINE SET EFFTO='<day-before>' on the old revision, then PR_BOM_ADD_LINE the new revision with the new EFFFROM.
2. Recompute low-level codes: CALL BOMCOST.PR_LLC_COMPUTE(DATE '<asof>').
3. Roll all costs: CALL BOMCOST.PR_COST_ROLLUP_ALL(DATE '<asof>', <runbase>) — pass a fresh runbase (e.g. a timestamp-derived integer) each run.
4. Explode a top item on demand for a pick-list / cost report: CALL BOMCOST.PR_BOM_EXPLODE_TOP('<top>', DATE '<asof>', <runid>), then read EXPLOSION_WORK back in the same session.

Post-checks:

• Every CALL returns SQLCODE 0 (no SIGNALed 9000x SQLSTATE).
• WHEREUSED row count equals the BOMLINE row count (the trigger kept them in sync).
• EXPLOSION_WORK for a top item has the expected row count — a phantom never gets its own row, a diamond component appears once per reaching path; VISITED for that run is empty afterward (every node popped on a clean exit).
• COSTROLL has exactly one row per manufactured item; a re-run leaves the counts and costs unchanged (MERGE upsert, stable BOM → stable cost).

E.2 Reconciling figures

These are the same figures the bundled volume driver checks against an independent hand-derived oracle (Python Decimal cross-checked). Use them to reconcile a run:

Ad-hoc cross-check: a plain WITH RECURSIVE explosion of the same top item over BOMLINE should reproduce the cumulative quantities (note: a bare recursive CTE has no procedural IF, so it does not special-case phantoms — it lists a phantom as a real level-1 row, producing 9 rows where PR_BOM_EXPLODE produces 8; both are correct for their respective, documented semantics). See F.4 for the CTE.

E.3 Failure & re-run rules

Every routine either returns SQLCODE 0 or SIGNALs a specific application SQLSTATE (section F.5). The procedures are designed to be safely re-runnable.

F. Developer Reference ↑ top

The complete SQL-PL surface, from routines.sql. All objects are in schema BOMCOST. This is the fullest section: every function, procedure, trigger and view with its signature and semantics, then the recursive patterns and the SQLSTATE table.

F.1 Functions (2)

FN_ACTIVE_QTY (P_PARENT CHAR(15), P_COMPONENT CHAR(15), P_ASOF DATE) RETURNS DECIMAL(19,6)

The scrap-inflated effective quantity-per for a component under a parent as-of a date. Selects the active revision (P_ASOF BETWEEN EFFFROM AND EFFTO, FETCH FIRST 1 ROW ONLY); returns NULL if no line is effective; SIGNALs 90005 if scrap ≥ 1; otherwise returns QTYPER / (1 - SCRAPPCT). A phantom's own qty-per still applies here — it is the phantom's parent link that is transparent, handled in the explosion procedure, not in this function.

FN_COST_ROLLUP (P_ITEM CHAR(15), P_ASOF DATE, P_RUNID INTEGER, P_DEPTH INTEGER) RETURNS DECIMAL(19,6)

Recursive scalar function. Purchased (ITTYPE='P') → returns ITSTDCOST. Manufactured → opens a cursor over active children and returns Σ(FN_COST_ROLLUP(child) × FN_ACTIVE_QTY(item,child)). A phantom child is costed identically to a real one (PHANTOM changes only explosion listing, never costing arithmetic). Guards: depth > 40 → 90002; node already in VISITED for this run → 90001; unknown item → 90003. It inserts its node into VISITED on entry and deletes it on exit so a diamond re-convergence is not a false cycle. Each recursion frame wraps the child call in an EXIT HANDLER FOR SQLSTATE '90001' that closes its cursor and RESIGNALs with its own parent context appended — the RESIGNAL-across-frames pattern (F.4).

F.2 Procedures (5)

PR_BOM_ADD_LINE (IN P_PARENT, P_COMPONENT CHAR(15), P_QTYPER DECIMAL(13,4), P_SCRAPPCT DECIMAL(7,4), P_PHANTOM CHAR(1), P_EFFFROM DATE, P_EFFTO DATE)

Guarded BOM-edge insert — the app's primary write path. Refuses self-reference (90004). Runs a WITH RECURSIVE SUBTREE query asking "does P_PARENT appear anywhere in P_COMPONENT's own sub-tree?"; if so the edge would close a cycle → 90001. Only then does it INSERT into BOMLINE (firing the where-used and cycle triggers). Because the check runs before the INSERT, the candidate edge is never physically present while the CTE walks the graph.

PR_BOM_EXPLODE (IN P_TOPITEM, P_PARENT CHAR(15), P_LEVEL SMALLINT, P_CUMQTY DECIMAL(19,6), P_ASOF DATE, P_RUNID INTEGER)

Recursive stored procedure. Depth > 40 → 90002; parent already VISITED → 90001; else pushes parent to VISITED and opens a cursor over active children (ORDER BY COMPONENT). Per child: a nested CONTINUE HANDLER FOR NOT FOUND around the ITMAST lookup lets it skip a missing component (ITERATE); scrap ≥ 1 → 90005; V_EXQTY = P_CUMQTY × QTYPER/(1-SCRAPPCT). If the child is a phantom it writes no row and recurses at the same level; otherwise it inserts one EXPLOSION_WORK row and, if manufactured, recurses at level+1. Each recursive call is wrapped in an EXIT HANDLER FOR SQLSTATE '90001' that closes the cursor and RESIGNALs with context. Pops parent from VISITED on exit.

PR_BOM_EXPLODE_TOP (IN P_TOPITEM CHAR(15), P_ASOF DATE, P_RUNID INTEGER)

Explosion entry point. An EXIT HANDLER FOR SQLEXCEPTION rolls back to SAVEPOINT SP_EXPLODE, clears the run's VISITED, and RESIGNALs. Clears this run's EXPLOSION_WORK/VISITED, opens the savepoint, calls PR_BOM_EXPLODE(top, top, 1, 1, asof, runid), and releases the savepoint on success. Note: the standard ON ROLLBACK RETAIN CURSORS clause is not accepted by this engine's SAVEPOINT passthrough (SQLPL-PLAT-BOM-02); a bare SAVEPOINT is used and is sufficient (no cursor must survive the rollback boundary).

PR_LLC_COMPUTE (IN P_ASOF DATE)

Low-level-code recompute. Resets ITLLC=0; cursors over true top items (NOT EXISTS an active line using them as a component); explodes each via PR_BOM_EXPLODE_TOP (run ids 900000+n) and folds the rows into a shared '*LLCSCAN' bucket; a final MERGE ... WHEN MATCHED THEN UPDATE SET ITLLC = MAX(LEVELNO) sets each component's code. Resets V_DONE=0 before OPEN to keep a zero-row housekeeping DELETE's +100/02000 from tripping the loop's NOT-FOUND handler (see C.2 note).

PR_COST_ROLLUP_ALL (IN P_ASOF DATE, IN P_RUNBASE INTEGER)

Rolls every manufactured item. Cursors over ITTYPE='M' in ITLLC DESC, ITEM order (bottom-up); calls FN_COST_ROLLUP(item, asof, P_RUNBASE + seq, 1); MERGEs the result into COSTROLL (matched → update MATCOST/COSTDATE; not matched → insert with the item's current ITLLC); then UPDATE ITMAST SET ITROLLED. Purchased items keep their given ITSTDCOST and are not re-rolled.

F.3 Triggers (5) & the view

TR_BOMLINE_WHEREUSED_INS — AFTER INSERT on BOMLINE, FOR EACH ROW

Inserts (N.COMPONENT, N.PARENT, N.BOMID) into WHEREUSED — the reverse index is grown with every BOM edge.

TR_BOMLINE_WHEREUSED_DEL — AFTER DELETE on BOMLINE, FOR EACH ROW

Deletes the WHEREUSED row for O.BOMID — the reverse index is cascaded out when a BOM edge is removed.

TR_BOMLINE_CYCLE — BEFORE INSERT on BOMLINE, FOR EACH ROW

The trigger form of the cycle guard: refuses N.PARENT = N.COMPONENT (90004) and, via the same WITH RECURSIVE SUBTREE membership test as PR_BOM_ADD_LINE, an edge that would make an item its own descendant (90001). It proves the guard also holds for a direct insert that bypasses the procedure.

TR_COSTED_BOM_IOI — INSTEAD OF INSERT on V_COSTED_BOM, FOR EACH ROW

Redirects an insert against the costed-BOM view to base BOMLINE, silently dropping the derived read-only COMPCOST.

V_COSTED_BOM — view

BOMLINE LEFT JOIN COSTROLL on component, exposing the BOM columns plus COMPCOST = C.MATCOST.

F.4 Recursive SQL-PL patterns

BOMCOST/i deliberately exercises the recursive corners of SQL PL. The reusable patterns:

1. Recursive stored procedure (procedure calls itself)

Each recursion level opens its own cursor over that level's children and, for a manufactured child, CALLs itself at level+1; a deep tree nests one cursor per stack frame. A phantom recurses at the same level (no row, no depth). The pattern:

-- inside PR_BOM_EXPLODE, per child (abridged)
SET V_EXQTY = P_CUMQTY * (V_QTY / (1 - V_SCRAP));
IF V_PHANTOM = 'Y' THEN
  CALL PR_BOM_EXPLODE(P_TOPITEM, V_COMP, P_LEVEL,     V_EXQTY, P_ASOF, P_RUNID);  -- same level
ELSE
  INSERT INTO EXPLOSION_WORK VALUES (P_TOPITEM, P_LEVEL, P_PARENT, V_COMP, V_EXQTY, 'N');
  IF V_TYPE = 'M' THEN
    CALL PR_BOM_EXPLODE(P_TOPITEM, V_COMP, P_LEVEL + 1, V_EXQTY, P_ASOF, P_RUNID); -- descend
  END IF;
END IF;

2. Recursive scalar function (function calls itself)

Cost is a sum over children of child-cost × effective qty; the child-cost is the same function recursing:

-- inside FN_COST_ROLLUP, per child (abridged)
SET V_EXQTY    = FN_ACTIVE_QTY(P_ITEM, V_COMP, P_ASOF);
SET V_CHILDCOST = FN_COST_ROLLUP(V_COMP, P_ASOF, P_RUNID, P_DEPTH + 1);
SET V_TOTAL     = V_TOTAL + (V_CHILDCOST * V_EXQTY);

3. VISITED-based cycle guard (push on entry, pop on exit)

A RUNID-scoped row marks every node currently on the path; finding a node already present is a cycle. Popping on exit is what makes a legal diamond (a component reached by two paths) not a false cycle:

SELECT COUNT(*) INTO V_HIT FROM VISITED WHERE RUNID = P_RUNID AND NODE = P_ITEM;
IF V_HIT > 0 THEN SIGNAL SQLSTATE '90001' SET MESSAGE_TEXT = '...cyclic BOM...'; END IF;
INSERT INTO VISITED (RUNID, NODE) VALUES (P_RUNID, P_ITEM);
   ... recurse over children ...
DELETE FROM VISITED WHERE RUNID = P_RUNID AND NODE = P_ITEM;   -- pop on the way out

4. RESIGNAL across recursion frames

Every frame catches 90001, closes its own cursor, and re-raises with its parent context appended, so the top-level caller sees the full path while every frame cleans up:

BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '90001'
    BEGIN
      CLOSE C_KIDS;
      RESIGNAL SQLSTATE '90001' SET MESSAGE_TEXT = '...cycle while costing ' || P_ITEM || ' -> ' || V_COMP;
    END;
  SET V_CHILDCOST = FN_COST_ROLLUP(V_COMP, P_ASOF, P_RUNID, P_DEPTH + 1);
END;

5. SAVEPOINT-based partial-explosion rollback

The entry procedure opens a savepoint and an SQLEXCEPTION handler rolls back to it so a failed explosion leaves no partial EXPLOSION_WORK:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN ROLLBACK TO SAVEPOINT SP_EXPLODE; DELETE FROM VISITED WHERE RUNID = P_RUNID; RESIGNAL; END;
DELETE FROM EXPLOSION_WORK WHERE TOPITEM = P_TOPITEM AND LEVELNO >= 0;
SAVEPOINT SP_EXPLODE;
CALL PR_BOM_EXPLODE(P_TOPITEM, P_TOPITEM, 1, 1, P_ASOF, P_RUNID);
RELEASE SAVEPOINT SP_EXPLODE;

6. WITH RECURSIVE common table expression (used as the cycle check, and for ad-hoc explosion)

The insert guards use a recursive CTE as the membership test itself (not a side probe); the same shape serves an ad-hoc multi-level explosion or upward where-used closure:

-- cycle-membership test in PR_BOM_ADD_LINE / TR_BOMLINE_CYCLE
WITH RECURSIVE SUBTREE (NODE) AS (
  SELECT COMPONENT FROM BOMCOST.BOMLINE WHERE PARENT = P_COMPONENT
  UNION ALL
  SELECT B.COMPONENT FROM BOMCOST.BOMLINE B JOIN SUBTREE S ON B.PARENT = S.NODE
)
SELECT NODE FROM SUBTREE WHERE NODE = P_PARENT;      -- any hit => would close a cycle

-- ad-hoc full explosion (phantom-UNAWARE: lists a phantom as a real level-1 row)
WITH RECURSIVE EXPL (LEVELNO, PARENT, COMPONENT, EXQTY) AS (
  SELECT 1, B.PARENT, B.COMPONENT, CAST(B.QTYPER/(1-B.SCRAPPCT) AS DECIMAL(19,6))
    FROM BOMCOST.BOMLINE B WHERE B.PARENT = 'TOP1' AND DATE '2026-08-01' BETWEEN B.EFFFROM AND B.EFFTO
  UNION ALL
  SELECT E.LEVELNO+1, B.PARENT, B.COMPONENT, CAST(E.EXQTY*(B.QTYPER/(1-B.SCRAPPCT)) AS DECIMAL(19,6))
    FROM EXPL E JOIN BOMCOST.BOMLINE B ON B.PARENT = E.COMPONENT
      AND DATE '2026-08-01' BETWEEN B.EFFFROM AND B.EFFTO
)
SELECT LEVELNO, PARENT, COMPONENT, EXQTY FROM EXPL ORDER BY LEVELNO, PARENT, COMPONENT;

7. MERGE upsert

Both PR_LLC_COMPUTE (fold MAX(LEVELNO) into ITMAST.ITLLC) and PR_COST_ROLLUP_ALL (upsert COSTROLL) use MERGE so a re-run updates in place rather than duplicating. COSTROLL.MATCOST is DECIMAL(17,6); a raw MERGE of an arithmetic expression may carry a documented decimal-quantization residual (SQLPL-SIM-FINDINGS.md), which is an informational platform note, not an application defect.

F.5 Application SQLSTATE table

G. Glossary ↑ top

BOM — Bill of Materials

The structured list of components (and their quantities) that make up a manufactured item. Held here in BOMCOST.BOMLINE (parent item → component item, quantity-per, scrap %).

Multi-level BOM explosion

Recursively expanding a parent item into all of its components at every level down to purchased/raw items, accumulating the effective quantity of each. Implemented with a WITH RECURSIVE CTE in PR_BOM_EXPLODE.

Standard-cost roll-up

Computing a manufactured item's total standard cost by summing the (extended) standard costs of its components bottom-up through the BOM levels — the cost analogue of the explosion. Result held in BOMCOST.COSTROLL.

Where-used

The inverse of explosion: for a given component, every parent/assembly that consumes it (directly or indirectly). Materialised in BOMCOST.WHEREUSED.

Level / low-level code

An item's deepest position in any BOM it participates in; roll-up must process items in low-level-code order so a component's cost is final before its parents are rolled.

Quantity-per

The quantity of a component required to make one unit of its immediate parent (before scrap).

Scrap / yield

An allowance for material lost in production; the effective component quantity is grossed up for scrap (see FN_ACTIVE_QTY). A scrap % ≥ 100% is rejected (SQLSTATE 90005).

Extended cost

Component unit standard cost × effective quantity-per; summed across a parent's lines to give the parent's rolled cost.

Cyclic BOM

An invalid structure where an item is (transitively) its own component. Guarded against by TR_BOMLINE_CYCLE / the recursion depth cap (self-reference rejected with SQLSTATE 90004).

WITH RECURSIVE (recursive CTE)

The SQL construct used to walk the BOM tree in a single set-based statement — a seed query unioned with a recursive reference that descends one level per iteration.

SQL PL

DB2-for-i procedural SQL (compound BEGIN…END bodies, variables, control flow, cursors, handlers, SIGNAL). BOMCOST/i's entire logic layer — there is no RPG/COBOL and no 5250 screen.

STRSQL

The interactive SQL entry point (Start SQL) from which an operator drives the explosion / roll-up procedures via CALL.