INV/i — Inventory, Warehouse & MRP

SteelFrame X application operation manual  ·  ← back to Operation Manuals  ·  Sign On

INV/i is an inventory / warehouse-management / MRP application: goods receipts with a weighted-average cost roll, stock issues with an oversell veto, bin-to-bin putaways, a net-requirements MRP planning run, and a period cost roll that posts a balanced GL revaluation. It is a pure SQL PL application — the entire business layer is DB2 for i tables, scalar functions, stored procedures and triggers created from plain SQL/SQL-PL DDL, with no 5250 screen and no DDS. The application is driven from STRSQL (interactive CALL), from a batch driver, or through embedded EXEC SQL CALL in an SQLRPGLE program. This manual is the reference for the operator who runs the receiving/issuing/MRP/cost-roll cycle and for the developer maintaining the routines. It is grounded entirely in the committed source (sqlpl-app-inv/src/schema.sql, routines.sql, seed.mjs, and the test/inv_daily.mjs / test/inv_volume.mjs drivers). Everything runs in library INVLIB.

A. Overview & Architecture ↑ top

A.1 What it does

INV/i manages the physical and cost life of stocked inventory:

• Goods receipt — post an inbound receipt into a specific warehouse/bin, adding to on-hand and rolling the item's weighted-average cost forward (old qty×old cost + new qty×new cost, over total). Receipts against an on-hold item, an unknown bin, or a bin that would exceed capacity are rejected.
• Stock issue — post an outbound issue out of a bin, driven through a hard oversell veto: a BEFORE trigger refuses any update that would drive on-hand negative, caught cleanly by the procedure's EXIT handler.
• Putaway (bin-to-bin) — move quantity from one bin to another for the same item, capacity-checked at the destination, written as a matched pair of ledger rows that net to zero (a pure internal transfer, creating/destroying nothing).
• MRP planning run — net every active item's on-hand and allocated against total open demand within a 90-day horizon, and where short, write a planned order rounded up to reorder-quantity multiples with a release date backed off by lead time.
• Cost roll — recompute each active item's weighted-average cost from ledger history, and where it has moved, revalue on-hand stock and post a balanced GL entry (debit inventory asset, credit cost variance) so the ledger always nets to zero.
• Available-to-promise & reorder decisions — scalar functions expose on-hand, allocated, ATP (never negative), and a compound reorder-quantity decision for reporting and planning.

A.2 SQL-first architecture: everything is SQL PL

Unlike the RPG+DDS applications in this catalogue, INV/i has no RPG business layer and no display file. Every table is created via plain SQL DDL (schema.sql), and every rule — receiving, the cost roll, the oversell veto, putaway, MRP netting, GL posting — is implemented as a compound (BEGIN…END) DB2 for i scalar function, stored procedure, or table trigger in routines.sql. There is no orchestration layer between the operator and the logic: the operator (or a scheduler, or a report tool) calls the procedures directly.

• Functions (5) — read-only scalar computations (on-hand, allocated, ATP, reorder qty, weighted-average cost from the ledger), several themselves compound with local variables, loops and conditionals.
• Procedures (6) — the transactional verbs: receive, issue, putaway, MRP run, cost roll. Each returns an OUT numeric return code and (most) an OUT message; none raises an unhandled exception to the caller for an expected business rejection.
• Triggers (5) — data-layer enforcement independent of the procedures: BEFORE-UPDATE vetoes (negative on-hand, over-allocation), AFTER audit rows on every accepted on-hand change and insert, and an INSTEAD OF UPDATE on a summary view for quick manual corrections.

The benefit for operations: the rules are one auditable place (the SQL-PL routines), reachable identically from STRSQL, a batch CALL, or an SQLRPGLE EXEC SQL CALL; and every money/quantity movement is journaled in an append-only ledger keyed by a source-document token, making re-posting a safe no-op.

A.3 Component & flow

  RECEIVING / ISSUING          PLANNING                 PERIODIC
  -------------------          --------                 --------
  SP_RECEIVE_STOCK  --+        SP_MRP_RUN   --reads-->   SP_COST_ROLL
    rolls WAVGCOST    |          ITMAST (active)           FN_WAVG_COST per item
  SP_ISSUE_STOCK  ----+        MRPDMD (demand)             revalue -> GLENTRY (+/-)
    oversell veto     |          |                         GLACCT balances
  SP_PUTAWAY  --------+          v
    net-zero pair     |        MRPPLAN (planned orders)
                      |
                      v
        STKBAL (on-hand / allocated, per item x whse x bin)
          |   \                                triggers on STKBAL:
          |    +--> STKLEDG (append-only ledger,   TR_STKBAL_VETO       (75010)
          |         one row per R/I/A, RUNTOKEN    TR_STKBAL_ALLOC_VETO (75011)
          |         = idempotency key)             TR_STKBAL_AUDIT   -> STKAUDIT
          +------> STKAUDIT (trigger-written)      TR_STKBAL_INSERT_AUDIT
                                                   TR_STKSTATUS_IOI (view)

A single receipt flows: operator stages an RCPTHDR row (status E) → calls SP_RECEIVE_STOCK(rcptno) → the procedure validates hold/bin/capacity, updates or inserts the STKBAL row, rolls ITMAST.WAVGCOST, writes one STKLEDG row keyed by the receipt number, and flips the header to Posted. The STKBAL change fires the audit trigger into STKAUDIT. Re-calling with the same receipt number is a no-op (RSTAT='P' already).

A.4 Object inventory

Object	Type	Role
ITMAST	Table	Item master (reorder policy, standard & weighted-average cost, status).
BINMAST	Table	Bin master (warehouse/bin, capacity, bin type).
STKBAL	Table	On-hand / allocated balance per item × whse × bin.
STKLEDG	Table	Append-only movement ledger (idempotency key RUNTOKEN,RUNSEQ).
RCPTHDR	Table	Inbound receipts staged for posting.
ISSHDR	Table	Outbound issues staged for posting.
MRPDMD	Table	Demand requirements feeding an MRP run.
MRPPLAN	Table	MRP run output (planned orders).
GLACCT / GLENTRY	Table	Simple GL account balances & the cost-roll's posted entries.
STKAUDIT	Table	Trigger-written on-hand change audit trail.
STKSTATUS	View	Per-item total-on-hand summary; updatable via INSTEAD OF trigger.
FN_* (5)	SQL functions	On-hand, allocated, ATP, reorder qty, ledger weighted-average cost.
SP_* (6)	SQL procedures	Receive / issue / putaway / MRP run / cost roll.
TR_* (5)	Triggers	Oversell veto, over-alloc veto, audit (update & insert), INSTEAD OF view.

The full catalogue is 5 functions + 6 procedures + 5 triggers, over 11 tables and 1 view, with three unique indexes carrying the ledger/plan/GL idempotency keys. Sections D and F expand each. There is no program object and no DDS member — the SQLRPGLE reach shown in the daily driver (INVCALL) is a test-built caller, not a shipped part of the application.

B. Online Access (there is no screen) ↑ top

B.1 STRSQL CALL invocation

Honest statement: INV/i has no 5250 screen, no menu, and no DDS display file. It is a pure SQL PL application — its "online" surface is the DB2 for i SQL layer itself. An operator interacts with it by starting an interactive SQL session (STRSQL) and issuing CALL statements and queries against the routines and tables in library INVLIB. The same calls run unchanged from a batch driver, from a scheduler, or through embedded EXEC SQL CALL in an SQLRPGLE program. Before invoking anything, the job's library list must include INVLIB — the tested jobs run with LIBL = QSYS QGPL INVLIB QTEMP and CURLIB = INVLIB.

Because these are SQL procedures, "run a transaction" means "type a CALL and read the OUT parameters" — there is no data-entry panel; the input is a staged header row (for receive/issue) or literal CALL arguments (for putaway/MRP/cost-roll). Every procedure reports its outcome through OUT parameters: a numeric return code (0 = success) and, for the posting procedures, a human-readable message.

To do this	Type in STRSQL
Post a staged goods receipt	CALL INVLIB.SP_RECEIVE_STOCK('RC0001', ?, ?)
Post a staged stock issue	CALL INVLIB.SP_ISSUE_STOCK('IS0001', ?, ?)
Move stock bin-to-bin (putaway)	CALL INVLIB.SP_PUTAWAY('MV0001','ITM001','WH1','A01','WH1','B01',20,20260804, ?, ?)
Run MRP for a planning token	CALL INVLIB.SP_MRP_RUN('MRPRUN1', 20260801, ?, ?)
Run the cost roll	CALL INVLIB.SP_COST_ROLL('CROLL1', ?, ?)
Check available-to-promise	SELECT FN_ATP('ITM001') FROM SYSIBM.SYSDUMMY1
See per-item total on-hand	SELECT * FROM INVLIB.STKSTATUS

The receive and issue procedures take only the document number and read every detail (item, warehouse, bin, quantity, cost, date) from the staged RCPTHDR / ISSHDR row, so a typical online flow is a two-step: INSERT the header, then CALL the posting procedure. Putaway, MRP and cost-roll take their arguments inline.

-- stage then post a receipt in one STRSQL session
INSERT INTO INVLIB.RCPTHDR (RCPTNO, ITEMNO, WHSE, BIN, QTY, UNITCOST, RDATE)
  VALUES ('RC0001', 'ITM001', 'WH1', 'A01', 100, 10.0000, 20260801);
CALL INVLIB.SP_RECEIVE_STOCK('RC0001', ?, ?);   -- OUT rc, OUT msg  ->  0 / 'posted'

B.2 Controls & audit

INV/i does not model a four-eyes maker–checker workflow: a document posted through SP_RECEIVE_STOCK / SP_ISSUE_STOCK is applied immediately. The control posture is data-layer enforcement + an append-only ledger + a trigger-written audit trail, all independent of who calls the procedure:

• Idempotency by document token. Every ledger row carries RUNTOKEN (the source document number) under a unique index (RUNTOKEN, RUNSEQ). Receive/issue set the header status to P on success and short-circuit to return code 1 ("already posted") on a re-call; putaway guards on an existing ledger row for the move number; MRP guards per (RUNTOKEN, ITEMNO). Re-posting any completed document changes nothing.
• Oversell veto (hard). TR_STKBAL_VETO (BEFORE UPDATE) SIGNALs SQLSTATE 75010 if an on-hand update would go negative — the last line of defence even if a caller bypasses SP_ISSUE_STOCK and updates STKBAL directly. The procedure's EXIT handler catches it and reports return code 5 cleanly rather than aborting.
• Over-allocation veto. TR_STKBAL_ALLOC_VETO (BEFORE UPDATE) SIGNALs 75011 if allocated would exceed on-hand — you cannot promise more than is physically present.
• Status gating. On-hold items (ITMAST.ISTAT='H') reject receipts (rc 2) and issues (rc 2) and are excluded from MRP and cost-roll cursors (both filter ISTAT='A'). Unknown bins reject (rc 3); over-capacity receipts/putaways reject (rc 4).
• Audit trail (STKAUDIT). Every accepted on-hand change is recorded automatically: TR_STKBAL_AUDIT (AFTER UPDATE OF QTYOH) writes an old→new row; a brand-new balance row (first receipt into a bin) is audited 0→opening by TR_STKBAL_INSERT_AUDIT. The veto fires before the update, so a rejected oversell writes no audit row.
• Balanced GL. The cost roll writes a matched debit/credit pair per revalued item under one RUNTOKEN, so GLENTRY and the GLACCT balances always net to zero across a run.
• Channel gating via the view. TR_STKSTATUS_IOI (INSTEAD OF UPDATE on STKSTATUS) lets warehouse staff correct an item's total on-hand without knowing which bin holds it: the delta is applied to the item's single largest bin, keeping the view consistent with the detail table. That correction is a deliberate un-ledgered adjustment (no STKLEDG row), documented as such in the reconciliation model (section E).

C. Batch / Cycle Procedures ↑ top

INV/i has no monolithic nightly job. Instead, activity runs as procedure calls grouped into a natural cycle: continuous receiving/issuing/putaway through the working day, then periodic planning (an MRP run) and revaluation (a cost roll). Every procedure is parameterised at the CALL — there is no control-row indirection; receive/issue read their detail from the staged header, and MRP/cost-roll take a run token (and, for MRP, an as-of date) inline. A batch driver is simply a job that issues these CALL statements in order.

-- a batch cycle is a sequence of CALLs (schematic):
CALL INVLIB.SP_RECEIVE_STOCK('RCnnnn', ?, ?);   -- per staged receipt
CALL INVLIB.SP_ISSUE_STOCK  ('ISnnnn', ?, ?);   -- per staged issue
CALL INVLIB.SP_PUTAWAY(...);                     -- per bin move
CALL INVLIB.SP_MRP_RUN('MRPRUN1', 20260801, ?, ?);   -- one planning run
CALL INVLIB.SP_COST_ROLL('CROLL1', ?, ?);            -- one revaluation

C.1 Full procedure / trigger set

Object	Kind	Purpose	Key inputs	Outputs / effect	Frequency
SP_RECEIVE_STOCK	proc	Post one goods receipt; roll weighted-average cost.	RCPTHDR row keyed by P_RCPTNO.	OUT rc (0/1/2/3/4), OUT msg; STKBAL +qty, ITMAST.WAVGCOST, one STKLEDG(R), header→P.	Continuous.
SP_ISSUE_STOCK	proc	Post one stock issue with oversell veto.	ISSHDR row keyed by P_ISSNO.	OUT rc (0/1/2/3/5), OUT msg; STKBAL −qty, one STKLEDG(I), header→P; rc 5 on oversell (handler-caught).	Continuous.
SP_PUTAWAY	proc	Move qty bin-to-bin, same item, capacity-checked.	move no, item, from whse/bin, to whse/bin, qty, date (all inline).	OUT rc (0/1/3/4/5), OUT msg; two STKLEDG(A) rows (RUNSEQ 0/1) netting to zero.	Continuous.
SP_MRP_RUN	proc	Net-requirements plan over every active item.	run token, as-of date (horizon = as-of + 90).	OUT planned, OUT skipped; one MRPPLAN row per short item.	Periodic (per planning run).
SP_COST_ROLL	proc	Recompute WAVG from ledger; revalue & post balanced GL.	run token.	OUT revalued, OUT unchanged; ITMAST.WAVGCOST, GLENTRY debit/credit pair, GLACCT balances.	Periodic (per revaluation).
TR_STKBAL_VETO	trigger	BEFORE UPDATE — refuse on-hand going negative.	fires on any STKBAL update.	SIGNAL SQLSTATE 75010 when N.QTYOH < 0.	Automatic.
TR_STKBAL_ALLOC_VETO	trigger	BEFORE UPDATE — refuse allocated > on-hand.	fires on any STKBAL update.	SIGNAL SQLSTATE 75011 when N.QTYALLOC > N.QTYOH.	Automatic.
TR_STKBAL_AUDIT	trigger	AFTER UPDATE OF QTYOH — audit each change.	fires on QTYOH update.	INSERT STKAUDIT (old→new qty, CHGBY='TRIGGER').	Automatic.
TR_STKBAL_INSERT_AUDIT	trigger	AFTER INSERT — audit a new balance row.	fires on STKBAL insert.	INSERT STKAUDIT (0→opening qty).	Automatic.
TR_STKSTATUS_IOI	trigger	INSTEAD OF UPDATE on the STKSTATUS view.	fires on view update.	Applies the total-on-hand delta to the item's largest bin in STKBAL.	On demand (manual correction).

C.2 Cycle detail

Receiving — SP_RECEIVE_STOCK

Reads the staged RCPTHDR row. If already posted → rc 1 (no-op). Rejects an on-hold item (rc 2), an unknown bin (rc 3), or a receipt that would push the bin's current on-hand past BINMAST.CAPACITY (rc 4) — each rejection also flips the header to R. On acceptance it adds to (or inserts) the STKBAL row, rolls the item's weighted-average cost, and posts one STKLEDG(R) row keyed by the receipt number.

Weighted-average cost roll at receipt (from inv_daily oracle):
  RC0001  ITM001  A01  100 @ 10.0000  -> on-hand 100,  wavg 10.0000
  RC0002  ITM001  A01   50 @ 13.0000  -> on-hand 150,
          wavg = (100*10 + 50*13) / 150 = 1650/150 = 11.0000

Issuing — SP_ISSUE_STOCK

Reads the staged ISSHDR row. Already posted → rc 1; on-hold item → rc 2; no balance row in that bin → rc 3. Otherwise it deducts on-hand with a plain UPDATE STKBAL — and that update drives the BEFORE trigger TR_STKBAL_VETO, which SIGNALs 75010 if the result would go negative. An EXIT HANDLER FOR SQLSTATE '75010' catches it, marks the header R, and returns rc 5 ("oversell rejected") — the whole CALL still succeeds (SQLCODE 0) with a business return code, rather than aborting on an unhandled condition. On acceptance it posts one STKLEDG(I) row (signed −qty) at the item's current weighted-average cost.

Putaway — SP_PUTAWAY

An internal transfer of the same item between bins. Guards: already-posted move token → rc 1; insufficient stock in the source bin → rc 5; destination bin not found → rc 3; destination over capacity → rc 4. On acceptance it deducts the source bin, adds/inserts the destination bin, and writes two STKLEDG(A) rows under one RUNTOKEN — RUNSEQ 0 (source, −qty) and RUNSEQ 1 (destination, +qty) — so the pair nets to zero and the item's total on-hand is conserved.

MRP run — SP_MRP_RUN

A FOR-cursor over active items. For each, it nets alloc + demand(due ≤ as-of+90) − on-hand, floored so it never drops below the safety-stock shortfall. If net requirement is positive it rounds up to a whole multiple of the item's REORDQ (integer ceil-divide) and writes one MRPPLAN row with a release date = due − lead days, unless a row already exists for this (RUNTOKEN, ITEMNO) (then it counts a skip). Idempotent per token.

Worked example (inv_daily): ITM002, on-hand 0, safety 10, reorder qty 60, demand 45 @ 20260820
  netreq  = alloc(0) + demand(45) - onhand(0) = 45   (>= safety floor 10)
  planqty = ceil(45/60) * 60 = 60
  release = due(20260820) - lead(3d) = 20260817
  -> MRPPLAN row: onhand=0 totdmd=45 netreq=45 planqty=60 due=20260820 rel=20260817

Cost roll — SP_COST_ROLL

A FOR-cursor over active items. For each it recomputes the weighted-average cost straight from ledger receipts (FN_WAVG_COST) and compares to the stored WAVGCOST. If it moved by more than a rounding epsilon (0.0001), it revalues on-hand at the delta, updates ITMAST.WAVGCOST, and posts a balanced pair of GLENTRY rows — debit INVASSET by the delta, credit COSTVAR by the same — and mirrors both into GLACCT.BAL. Items whose cost is unchanged (or never received, so ledger WAVG is 0) count as unchanged with no GL. Because the incremental roll at receipt time and the full ledger recompute are two independent computations, a run right after receipts (before any issue) reports everything unchanged — the two agree bit-for-bit.

C.3 Ordering & idempotency

• Receipts before issues (per item/bin). An issue reads the specific whse/bin STKBAL row; issuing from a bin that has not yet received stock returns rc 3.
• Post activity before the MRP run. MRP nets against current on-hand and allocated, so run the day's receipts/issues first, then the planning run, or the plan reflects stale positions.
• Receipts before the cost roll. The cost roll recomputes weighted-average cost from ledger receipts; run it after the receipts whose prices you want reflected.
• Idempotency is per document/token, not per run. Re-posting any completed receipt/issue/putaway, or re-running MRP or the cost roll with the same token, changes nothing further — the whole-cycle re-run in inv_daily proves not one balance or ledger row moves on a second pass.

D. Data Files (data dictionary) ↑ top

All tables are in library INVLIB, grounded in schema.sql. Dates are stored as INT in YYYYMMDD form; quantities are INT; costs are DECIMAL(11,4); GL amounts are DECIMAL(13,2) and signed (debit positive, credit negative).

ITMAST — Item master (PK ITEMNO)

Field	Type	Meaning
ITEMNO	VARCHAR(10)	Item number (PK).
DESCR	VARCHAR(40)	Item description.
UOM	CHAR(3)	Unit of measure.
REORDPT	INT	Reorder point.
REORDQ	INT	Standard reorder quantity (fixed EOQ-ish lot size).
LEADDAYS	INT	Replenishment lead time in days (MRP release-date offset).
SAFETYSTK	INT	Safety-stock floor.
STDCOST	DECIMAL(11,4)	Standard cost (last PO / receipt price basis).
WAVGCOST	DECIMAL(11,4)	Rolling weighted-average cost (default 0).
ISTAT	CHAR(1)	A active, H hold (no receipts/issues; excluded from MRP & cost roll). Default A.

BINMAST — Bin master (PK WHSE, BIN)

Field	Type	Meaning
WHSE	CHAR(3)	Warehouse (PK part 1).
BIN	VARCHAR(8)	Bin (PK part 2).
CAPACITY	INT	Maximum on-hand the bin can hold (checked on receipt/putaway).
BINTYPE	CHAR(1)	S storage, R receiving, Q quarantine. Default S.

STKBAL — Stock balance (PK ITEMNO, WHSE, BIN)

Field	Type	Meaning
ITEMNO / WHSE / BIN	VARCHAR(10) / CHAR(3) / VARCHAR(8)	The balance's item and location (PK).
QTYOH	INT	Quantity on hand (default 0; never negative — trigger-vetoed).
QTYALLOC	INT	Quantity allocated to open issues (default 0; ≤ QTYOH — trigger-vetoed).

STKLEDG — Movement ledger (surrogate LSEQ; unique RUNTOKEN,RUNSEQ)

Field	Type	Meaning
LSEQ	INT identity	Generated-always surrogate key.
MVTYPE	CHAR(1)	R receipt, I issue, A adjustment (putaway leg), C cost-roll.
ITEMNO / WHSE / BIN	VARCHAR/CHAR	What moved and where.
QTY	INT	Signed: +receipt/adjust-in, −issue/adjust-out.
UNITCOST	DECIMAL(11,4)	Unit cost at the movement.
MVDATE	INT	Movement date (YYYYMMDD).
RUNTOKEN	VARCHAR(20)	Source document number — the idempotency key.
RUNSEQ	INT	Disambiguates >1 ledger row per document (putaway writes 0 and 1). Default 0.

Unique index STKLEDG_TOKEN (RUNTOKEN, RUNSEQ) enforces idempotency: a document can never post twice.

RCPTHDR — Inbound receipts staged for posting (PK RCPTNO)

Field	Type	Meaning
RCPTNO	VARCHAR(10)	Receipt document number (PK; becomes the ledger RUNTOKEN).
ITEMNO / WHSE / BIN	VARCHAR/CHAR	Item and destination location.
QTY	INT	Quantity received.
UNITCOST	DECIMAL(11,4)	Receipt unit cost (drives the WAVG roll).
RDATE	INT	Receipt date (YYYYMMDD).
RSTAT	CHAR(1)	E entered, P posted, R rejected. Default E.

ISSHDR — Outbound issues staged for posting (PK ISSNO)

Field	Type	Meaning
ISSNO	VARCHAR(10)	Issue document number (PK; becomes the ledger RUNTOKEN).
ITEMNO / WHSE / BIN	VARCHAR/CHAR	Item and source location.
QTY	INT	Quantity to issue.
IDATE	INT	Issue date (YYYYMMDD).
ISTAT	CHAR(1)	E entered, P posted, R rejected (oversell / hold / no balance). Default E.

MRPDMD — Demand requirements (surrogate DMDID)

Field	Type	Meaning
DMDID	INT identity	Generated-always demand row id.
ITEMNO	VARCHAR(10)	Item the demand is for.
DUEDATE	INT	Demand due date (YYYYMMDD).
QTYREQ	INT	Quantity required (independent + dependent demand, pre-netted by the caller).

MRPPLAN — MRP run output (surrogate PLANID; unique RUNTOKEN,ITEMNO)

Field	Type	Meaning
PLANID	INT identity	Generated-always plan row id.
RUNTOKEN	VARCHAR(20)	Planning-run token (idempotency key with ITEMNO).
ITEMNO	VARCHAR(10)	Planned item.
ONHAND	INT	On-hand at run time.
TOTDMD	INT	Total demand within horizon.
NETREQ	INT	Net requirement (safety-floored).
PLANQTY	INT	Planned order qty (rounded up to REORDQ multiples).
DUEDATE	INT	Earliest demand due date driving the order.
RELDATE	INT	Release date = DUEDATE − lead time.

Unique index MRPPLAN_TOKEN (RUNTOKEN, ITEMNO) makes a re-run with the same token a no-op per item.

GLACCT / GLENTRY — Simple GL (PK ACCTNO / surrogate GLSEQ)

Field	Type	Meaning
GLACCT.ACCTNO	VARCHAR(10)	Account number (PK). Seeded: INVASSET, COSTVAR.
GLACCT.DESCR / BAL	VARCHAR(40) / DECIMAL(13,2)	Account name and running balance (default 0).
GLENTRY.GLSEQ	INT identity	Generated-always entry id.
GLENTRY.ACCTNO	VARCHAR(10)	Posted account.
GLENTRY.AMT	DECIMAL(13,2)	Signed amount (debit +, credit −).
GLENTRY.RUNTOKEN / RUNSEQ	VARCHAR(20) / INT	Cost-roll token & sequence (unique index).
GLENTRY.GLDESC	VARCHAR(60)	Entry description (e.g. cost roll revalue ITM001).

Unique index GLENTRY_TOKEN (RUNTOKEN, RUNSEQ). The cost roll always posts a matched debit/credit pair, so SUM(AMT)=0 and SUM(BAL)=0 hold across every run.

STKAUDIT — On-hand audit trail (surrogate AUDSEQ)

Field	Type	Meaning
AUDSEQ	INT identity	Generated-always audit id.
ITEMNO / WHSE / BIN	VARCHAR/CHAR	The balance that changed.
OLDQTY / NEWQTY	INT	On-hand before / after the change.
CHGBY	VARCHAR(20)	Actor label; stamped constant 'TRIGGER' (SQLPL-PLAT-04).

STKSTATUS — Per-item on-hand summary view

Defined as ITMAST LEFT JOIN STKBAL grouped by item, exposing ITEMNO, DESCR, TOTONHAND (COALESCE(SUM(QTYOH),0)). Updatable only through TR_STKSTATUS_IOI: an UPDATE STKSTATUS SET TOTONHAND = … applies the delta to the item's single largest bin, so the view stays consistent with the detail table.

Relationships

• STKBAL.(ITEMNO,WHSE,BIN) references ITMAST.ITEMNO and BINMAST.(WHSE,BIN) by convention (validated in the procedures, not declared FKs).
• STKLEDG is the append-only journal of every STKBAL movement; RUNTOKEN ties each row to its source document (RCPTHDR/ISSHDR/putaway move number).
• MRPDMD feeds SP_MRP_RUN, which writes MRPPLAN (keyed by RUNTOKEN,ITEMNO).
• SP_COST_ROLL writes GLENTRY (paired) and mirrors GLACCT.BAL; STKAUDIT is trigger-written off STKBAL.
• STKSTATUS is a derived view over ITMAST/STKBAL.

E. Operations Runbook ↑ top

E.1 The daily cycle

Pre-checks: confirm the job's library list includes INVLIB (LIBL = QSYS QGPL INVLIB QTEMP, CURLIB = INVLIB); confirm the item, bin and GL master data are seeded (the tested set is 3 items — ITM001 widget, ITM002 gadget, ITM003 on-hold — 3 bins, and GL accounts INVASSET/COSTVAR seeded to zero).

1. For each inbound receipt: INSERT the RCPTHDR row, then CALL INVLIB.SP_RECEIVE_STOCK('RCnnnn', ?, ?). Confirm rc 0/'posted'.
2. For each outbound issue: INSERT the ISSHDR row, then CALL INVLIB.SP_ISSUE_STOCK('ISnnnn', ?, ?). Confirm rc 0; an rc 5 is a legitimate oversell rejection, not a failure.
3. For each bin move: CALL INVLIB.SP_PUTAWAY(...). Confirm rc 0 (or a legitimate capacity/stock veto rc 4/5).
4. Run the planning pass: CALL INVLIB.SP_MRP_RUN('MRPRUN<date>', <asof>, ?, ?); review the MRPPLAN rows for that token.
5. Run the revaluation: CALL INVLIB.SP_COST_ROLL('CROLL<date>', ?, ?); confirm GL nets to zero.

Post-checks after the cycle:

• Every accepted posting returned rc 0; rejections carry the expected code (2 hold, 3 bin, 4 capacity, 5 oversell) and left the STKBAL row unchanged.
• No STKBAL row is negative: SELECT COUNT(*) FROM INVLIB.STKBAL WHERE QTYOH < 0 returns 0.
• No row over-allocated: SELECT COUNT(*) FROM INVLIB.STKBAL WHERE QTYALLOC > QTYOH returns 0.
• The audit trail captured each on-hand change (STKAUDIT), and no audit row is negative (the veto fires before the update).
• GL balances: SELECT COALESCE(SUM(AMT),0) FROM INVLIB.GLENTRY and SELECT COALESCE(SUM(BAL),0) FROM INVLIB.GLACCT both return 0.

E.2 Volume cycle & reconciling figures

The volume driver (inv_volume.mjs) runs the same procedures at scale — 40 items (every 10th on hold), 12 bins, five simulated business days of receipts/issues/putaways (several hundred documents), one MRP run and one cost roll per day, each re-run for idempotency — and reconciles the engine against an independent JS oracle computed from the same seed data. These are the figures an operator reconciles after a period:

• Conservation (per item) — engine on-hand equals the ledger-derived closing: SUM(QTYOH) = SUM(QTY) over that item's STKLEDG rows (R+I+A, signed). Putaway A legs net to exactly zero. The one caveat: a manual correction through STKSTATUS is un-ledgered by design, so add those deltas when reconciling (the daily driver does exactly this: opening 0 + receipts + issues + moves + the one +5 manual correction = closing on-hand).
• Ledger idempotency — no receipt/issue RUNTOKEN has more than one ledger row; a putaway has exactly two (RUNSEQ 0/1). Verify with a GROUP BY RUNTOKEN HAVING COUNT(*) > 1 that returns nothing for MVTYPE IN ('R','I').
• MRP planned count — equals the number of active items whose safety-floored net requirement is positive; each planned row's onhand/totdmd/netreq/planqty matches the hand-derived netting (planqty = ceil(netreq/REORDQ) × REORDQ). A same-token re-run plans 0 and skips the rest.
• Cost roll & GL — revalued + unchanged equals the active-item count; a re-run with no new activity revalues 0; and GLENTRY / GLACCT always net to zero (matched debit/credit pairs).
• Vetoes hold at volume — every deliberate oversell is rejected (rc 5) with on-hand unchanged; every deliberate over-allocation is rejected (75011); every on-hold item's receipt is refused (rc 2).

Ad-hoc position review: SELECT * FROM INVLIB.STKSTATUS gives per-item total on-hand; SELECT FN_ATP('ITEM'), FN_ONHAND, FN_ALLOC, and FN_REORDER_QTY answer availability and reorder questions without a report program.

E.3 Failure & re-run rules

Every procedure reports through its OUT return code; a business rejection is a non-zero code with SQLCODE still 0 (the routine handles the condition internally), not a hard failure. Genuine SQL errors surface as a negative SQLCODE with an application SQLSTATE (section F.6).

Situation	Behaviour	Action
Re-post a completed receipt/issue	Header is already P; procedure returns rc 1.	Safe no-op — on-hand and the ledger are untouched. Idempotent by document.
Re-run a completed putaway	Ledger already has rows for the move number; returns rc 1.	Safe no-op; stock not moved again.
Oversell (issue > on-hand)	BEFORE trigger SIGNALs 75010; EXIT handler catches it.	Header marked R, rc 5, on-hand unchanged, no ledger row. Correct the qty and re-stage.
Receipt into a full bin	rc 4; header marked R; nothing landed.	Re-stage to a bin with capacity, or move stock out first.
Receipt/issue against on-hold item	rc 2; header marked R.	Release the item (ISTAT='A') or route elsewhere, then re-stage.
MRP re-run, same token	Existing (RUNTOKEN,ITEMNO) rows are skipped.	0 planned / N skipped — idempotent. Use a fresh token to plan again against current positions.
Cost roll re-run, no new activity	Ledger WAVG unchanged → 0 revalued, no GL entries.	Safe; GL stays balanced. Use a fresh token; a same-token re-run is blocked by the GLENTRY unique index if it tried to post.

F. Developer Reference ↑ top

The complete SQL surface, from schema.sql and routines.sql. All objects are in library INVLIB. Signatures are given exactly as declared; return codes and SQLSTATEs are consolidated in F.6.

F.1 Tables & the view (11 tables, 3 unique indexes, 1 view)

Object	Key	Notes
ITMAST	PK ITEMNO	Item master; WAVGCOST rolled by receipts & the cost roll.
BINMAST	PK (WHSE,BIN)	Bin capacity enforced by receive/putaway.
STKBAL	PK (ITEMNO,WHSE,BIN)	On-hand/allocated; carries all three balance triggers.
STKLEDG	IDENTITY LSEQ; UQ (RUNTOKEN,RUNSEQ)	Append-only movement ledger; idempotency key.
RCPTHDR	PK RCPTNO	Staged receipts; RSTAT E/P/R.
ISSHDR	PK ISSNO	Staged issues; ISTAT E/P/R.
MRPDMD	IDENTITY DMDID	Demand feeding SP_MRP_RUN.
MRPPLAN	IDENTITY PLANID; UQ (RUNTOKEN,ITEMNO)	MRP output; per-token idempotency.
GLACCT	PK ACCTNO	Account balances; seeded INVASSET, COSTVAR.
GLENTRY	IDENTITY GLSEQ; UQ (RUNTOKEN,RUNSEQ)	Cost-roll postings; matched debit/credit pairs.
STKAUDIT	IDENTITY AUDSEQ	Trigger-written on-hand audit.
STKSTATUS	View	ITMAST LEFT JOIN STKBAL grouped; updatable via INSTEAD OF.

F.2 Functions (5)

FN_ONHAND (P_ITEMNO VARCHAR(10)) RETURNS INT

Total on-hand for an item across all bins: SELECT COALESCE(SUM(QTYOH),0) INTO V FROM STKBAL WHERE ITEMNO=P_ITEMNO. Compound body with a local variable.

FN_ALLOC (P_ITEMNO VARCHAR(10)) RETURNS INT

Total allocated for an item across all bins (COALESCE(SUM(QTYALLOC),0)).

FN_ATP (P_ITEMNO VARCHAR(10)) RETURNS INT

Available-to-promise = FN_ONHAND − FN_ALLOC, floored at 0 (never negative). Calls the two scalar functions and clamps.

FN_REORDER_QTY (P_ITEMNO VARCHAR(10)) RETURNS INT

Compound decision function. Reads REORDPT, REORDQ, SAFETYSTK; sets PROJ = on-hand − allocated. If PROJ > REORDPT returns 0 (no reorder). Else computes the safety shortfall SHORT = SAFETYSTK − PROJ (floored 0) and returns SHORT if it exceeds REORDQ, otherwise REORDQ.

FN_WAVG_COST (P_ITEMNO VARCHAR(10)) RETURNS DECIMAL(11,4)

Recomputes weighted-average cost straight from ledger receipts only (MVTYPE='R'), independent of the stored WAVGCOST column — used by SP_COST_ROLL to verify/refresh it. Deliberately a FOR-cursor loop accumulating TOTQTY/TOTCOST (not a single aggregate) to exercise the loop form. Returns 0 if no receipts; otherwise (TOTCOST * 1.0) / TOTQTY — the * 1.0 forces genuine decimal division (SQLPL-PLAT-01).

F.3 Procedures (6)

SP_RECEIVE_STOCK (IN P_RCPTNO VARCHAR(10), OUT P_RC INT, OUT P_MSG VARCHAR(80))

Posts one goods receipt from its RCPTHDR row. Guards in order: already posted → rc 1; item on hold → rc 2 (header→R); bin not in BINMAST → rc 3 (header→R); current + qty > CAPACITY → rc 4 (header→R). On acceptance: update-or-insert STKBAL; roll WAVGCOST as ((oldQty×oldCost + qty×cost) * 1.0) / (oldQty+qty) (or the receipt cost if prior on-hand ≤ 0); INSERT one STKLEDG('R', …, RUNTOKEN=P_RCPTNO, RUNSEQ=0); header→P; rc 0 / 'posted'.

SP_ISSUE_STOCK (IN P_ISSNO VARCHAR(10), OUT P_RC INT, OUT P_MSG VARCHAR(80))

Posts one issue from its ISSHDR row. Declares EXIT HANDLER FOR SQLSTATE '75010' (marks header R, rc 5). Guards: already posted → rc 1; item on hold → rc 2; no STKBAL row in that bin → rc 3. Otherwise UPDATE STKBAL SET QTYOH = QTYOH − qty — which drives TR_STKBAL_VETO; if it would go negative the veto SIGNALs 75010 and the handler reports rc 5 cleanly. On success INSERT one STKLEDG('I', …, −qty) at the item's WAVGCOST; header→P; rc 0.

SP_PUTAWAY (IN P_MOVENO, P_ITEMNO, P_FROMWHSE, P_FROMBIN, P_TOWHSE, P_TOBIN, P_QTY, P_MVDATE; OUT P_RC, P_MSG)

Bin-to-bin transfer of one item. Guards: existing ledger rows for P_MOVENO → rc 1; source on-hand NULL or < qty → rc 5; destination bin absent → rc 3; destination total + qty > CAPACITY → rc 4. On acceptance: deduct source, add/insert destination, and INSERT two STKLEDG('A', …) rows under one RUNTOKEN — RUNSEQ 0 (source, −qty), RUNSEQ 1 (destination, +qty). rc 0 / 'moved'.

SP_MRP_RUN (IN P_RUNTOKEN VARCHAR(20), IN P_ASOF INT, OUT P_PLANNED INT, OUT P_SKIPPED INT)

FOR-cursor over ITMAST WHERE ISTAT='A' (columns aliased CI_*, see the note). Per item: totdmd = SUM(QTYREQ) where DUEDATE ≤ P_ASOF+90; netreq = (alloc + totdmd) − onhand, floored so it is at least the safety-stock shortfall. If netreq > 0: mult = (netreq + REORDQ − 1) / REORDQ (integer ceil-divide, min 1), planqty = mult × REORDQ, rel = due − lead; INSERT one MRPPLAN row unless one exists for (RUNTOKEN,ITEMNO) (then skip). Idempotent per token.

SP_COST_ROLL (IN P_RUNTOKEN VARCHAR(20), OUT P_REVALUED INT, OUT P_UNCHANGED INT)

FOR-cursor over active items. Per item: newcost = FN_WAVG_COST(item). If newcost > 0 AND ABS(newcost − oldcost) ≥ 0.0001: delta = (newcost − oldcost) × onhand; UPDATE WAVGCOST; INSERT a paired GLENTRY — ('INVASSET', +delta, …, RUNSEQ n) and ('COSTVAR', −delta, …, RUNSEQ n+1); UPDATE GLACCT.BAL for both; count revalued. Else count unchanged. The pairing guarantees GL nets to zero.

F.4 Triggers (5)

TR_STKBAL_VETO — BEFORE UPDATE ON STKBAL, WHEN (N.QTYOH < 0)

SIGNAL SQLSTATE 75010 ('oversell rejected: on-hand cannot go negative'). The hard floor under on-hand, independent of any procedure.

TR_STKBAL_ALLOC_VETO — BEFORE UPDATE ON STKBAL, WHEN (N.QTYALLOC > N.QTYOH)

SIGNAL SQLSTATE 75011 ('allocation cannot exceed on-hand'). Cannot promise more than is present.

TR_STKBAL_AUDIT — AFTER UPDATE OF QTYOH ON STKBAL (BEGIN ATOMIC)

INSERT STKAUDIT with (O.QTYOH → N.QTYOH, CHGBY='TRIGGER'). Fires only on a QTYOH change, and only after the BEFORE vetoes have passed — so a rejected oversell writes no audit row.

TR_STKBAL_INSERT_AUDIT — AFTER INSERT ON STKBAL

INSERT STKAUDIT for a brand-new balance row (first receipt into a bin), from 0 to its opening quantity.

TR_STKSTATUS_IOI — INSTEAD OF UPDATE ON STKSTATUS (BEGIN ATOMIC)

Computes delta = N.TOTONHAND − O.TOTONHAND, finds the item's single largest bin (ORDER BY QTYOH DESC FETCH FIRST 1 ROW ONLY), and applies the whole delta there with an UPDATE STKBAL — keeping the summary view consistent with the detail table. That detail update itself trips TR_STKBAL_AUDIT, so the manual correction is audited too.

F.5 SQL-PL patterns

The application exercises the SQL-PL surface deliberately; the idioms worth knowing when maintaining it:

• Compound scalar functions. Functions carry local DECLAREs, IF branches and even a FOR-cursor loop (FN_WAVG_COST, FN_REORDER_QTY), not just a single RETURN expression.
• FOR-cursor loops driving DML. SP_MRP_RUN and SP_COST_ROLL iterate a FOR … AS SELECT … DO … END FOR over active items, doing per-row SELECT INTO, function calls, and INSERT/UPDATE inside the loop.
• EXIT HANDLER catching a trigger SIGNAL. SP_ISSUE_STOCK declares DECLARE EXIT HANDLER FOR SQLSTATE '75010' so a BEFORE-trigger veto becomes a clean return code instead of an unhandled abort — the model for turning a data-layer guard into a business result.
• Idempotency keys. A unique index on (RUNTOKEN, RUNSEQ) (or (RUNTOKEN, ITEMNO)) plus a NOT EXISTS/status guard makes re-posting a no-op; the RUNSEQ lets one document write more than one ledger row (putaway's two legs, the cost roll's debit/credit pair).
• Balanced GL posting. Every revaluation writes a matched +delta/−delta pair under one token, so SUM(AMT)=0 is a structural invariant, not a checked one.
• Decimal-division guard. Any DECIMAL / DECIMAL that could see two whole operands is written (… * 1.0) / … to avoid integer truncation on this platform (SQLPL-PLAT-01).
• Cursor-column aliasing. The production MRP/cost-roll cursors alias their select list (CI_*) so no cursor-column name collides with an INSERT/UPDATE target column inside the loop (SQLPL-PLAT-06 workaround; the engine gap is fixed, the volume driver re-tests the un-aliased shape).
• SQLRPGLE reach. The routines are callable unchanged from embedded SQL: exec sql call INVLIB.SP_RECEIVE_STOCK(:rcptno, :rc, :msg); and exec sql select FN_ATP('ITM001') into :atp from sysibm.sysdummy1; (proven by the daily driver's INVCALL member).

F.6 SQLSTATE & return-code table

Application SQLSTATEs (raised by triggers)

SQLSTATE	Raised by	Meaning
75010	TR_STKBAL_VETO	On-hand cannot go negative (oversell). Caught by SP_ISSUE_STOCK's EXIT handler → rc 5.
75011	TR_STKBAL_ALLOC_VETO	Allocation cannot exceed on-hand. Surfaces to the caller as an SQL error.

Procedure return codes (OUT P_RC)

rc	Procedures	Meaning
0	all	Success ('posted' / 'moved').
1	RECEIVE / ISSUE / PUTAWAY	Already posted — idempotent no-op.
2	RECEIVE / ISSUE	Item on hold (ISTAT='H'); header marked R.
3	RECEIVE / ISSUE / PUTAWAY	Bin/balance not found (unknown bin, or no STKBAL row / dest bin absent).
4	RECEIVE / PUTAWAY	Bin over capacity; header marked R (receive).
5	ISSUE / PUTAWAY	Oversell rejected (issue, via 75010 handler) / insufficient source stock (putaway).

MRP and the cost roll have no failure return codes — they report counts (planned/skipped, revalued/unchanged). A same-token re-run of the cost roll that attempted to post again would trip the GLENTRY_TOKEN unique index; in normal operation nothing is re-posted because unchanged costs write no GL.

G. Glossary ↑ top

Allocated (QTYALLOC)

Quantity reserved against open issues; capped at on-hand by TR_STKBAL_ALLOC_VETO. Subtracted from on-hand to give ATP.

ATP — Available to Promise

On-hand minus allocated, floored at zero (FN_ATP): what can still be promised to new demand.

Bin / capacity

A storage location within a warehouse (BINMAST), with a maximum on-hand (CAPACITY) enforced on receipt and putaway.

Cost roll

Recomputing weighted-average cost from ledger history and, where it moved, revaluing on-hand and posting a balanced GL entry (SP_COST_ROLL).

Conservation

The invariant that an item's on-hand equals the signed sum of its ledger movements (plus any un-ledgered STKSTATUS correction) — the core reconciliation check.

GL (INVASSET / COSTVAR)

The simple two-account general ledger the cost roll posts to; every revaluation is a matched debit (inventory asset) and credit (cost variance) so the ledger nets to zero.

Idempotency key (RUNTOKEN)

The source-document number carried on every ledger/plan/GL row under a unique index, so re-posting the same document is a safe no-op.

Issue

An outbound stock movement out of a bin (SP_ISSUE_STOCK), vetoed if it would drive on-hand negative.

MRP — Material Requirements Planning

Netting on-hand and allocated against demand to decide planned orders (SP_MRP_RUN), sized to reorder-quantity multiples and released back by lead time.

Net requirement (NETREQ)

(allocated + demand) − on-hand, floored so it never drops below the safety-stock shortfall; a positive value triggers a planned order.

On hold (ISTAT='H')

An item state that refuses all receipts and issues and is excluded from MRP and cost-roll cursors.

Oversell veto

The BEFORE-UPDATE trigger (75010) that refuses any on-hand update going negative — the hard floor under stock, independent of the issue procedure.

Putaway

An internal bin-to-bin transfer of the same item (SP_PUTAWAY), written as two ledger legs that net to zero.

Receipt

An inbound stock movement into a bin (SP_RECEIVE_STOCK), which also rolls the item's weighted-average cost.

STKLEDG (ledger)

The append-only journal of every stock movement (R/I/A/C), the source of truth for conservation and cost recomputation.

STRSQL

Interactive SQL — the IBM i facility from which an operator CALLs the routines and queries the tables, since INV/i has no 5250 screen.

Weighted-average cost (WAVGCOST)

The rolling average unit cost, moved forward at each receipt and re-derivable from gross receipts by FN_WAVG_COST; the two computations agree only before any issue consumes received stock.