On-Hand Stock Types¶

Status: Implemented. Source: files/supply_runner.py (engine split), files/api/main.py (Overview KPIs + network map), files/allocation_runner.py (allocation supply pool).

Every on-hand row in master.on_hand carries a stock type (master.on_hand_type). The type's planning_type column is the planning-engine classification — the single field that decides whether a unit of stock is serviceable ("good") or unserviceable ("bad") everywhere in Mirabelle.

The one classification rule¶

All consumers use one definition:

Class	Rule	Meaning
Bad (unserviceable)	`planning_type = 'bad'`	In repair, scrap, otherwise unsellable. Feeds the repair pool; excluded from usable stock.
Good (serviceable)	`planning_type IS DISTINCT FROM 'bad'`	Everything else — including `AVAILABLE`, `good`, `new`, and rows with no type. Usable for demand, transfers, allocation.

The rule is case-sensitive SQL text matching, so the value must be written exactly bad (lowercase). BAD, Bad, or bad (trailing space) will NOT match — the stock is then silently treated as serviceable by every consumer.

Canonical `planning_type` values¶

Value	Class	Typical use
`bad`	Bad	Unserviceable stock: in repair / wash, scrap, quarantine-to-repair
`new`	Good (own bucket)	Newly purchased stock — allocation sequencing treats it after existing good stock
`good`	Good	Generic serviceable stock
`AVAILABLE`	Good	Available / unrestricted stock (the value used by the GPS tenants' `UNRESTRICTED` type)
(empty / NULL)	Good	Rows with no type are serviceable

Any other value is allowed and counts as good — define as many named types as your business needs (e.g. UNRESTRICTED, IN_REPAIR, SCRAP, QUARANTINE), as long as unserviceable types carry planning_type = 'bad'.

Where good / bad shows up¶

Surface	What it shows
Dashboard Overview KPI cards ("On-hand good / bad")	Good = serviceable qty (not `bad`); Bad = `bad` qty. Values use `unit_cost` → `item_location.cost_price` fallback.
Dashboard network map tooltips	Same split per site.
Supply engine (`files/supply_runner.py`)	`initial_inventory` = serviceable; `initial_oh_bad` = `bad` pool → REPAIR orders are sized from it; bad stock never ships on good-flow routes.
Allocation engine (`files/allocation_runner.py`)	On-hand serviceable rows enter the supply pool; bad rows never do.
Zone balancing (`files/zone_balancing.py`)	Excess is computed on serviceable stock only.

Case sensitivity incident (Aug 2026, GPS 2)

The GPS 2 tenant was seeded with planning_type = 'BAD' (uppercase) on its IN_REPAIR / SCRAP types. Result: the Overview showed no bad stock, and — worse — the supply engine counted ~69,600 in-repair/scrap units as serviceable inventory. The seed CSVs now use lowercase bad, and the tenant data was repaired. If a re-import ever reintroduces uppercase values, every good/bad split silently degrades again.

Seeding stock types¶

Stock types are seeded through the import schema (see Data Onboarding §10): stage import.on_hand_type, then run the import ETL step on_hand_type (upsert by xuid).

on_hand_type.csv:

Column	Required	Description
`xuid`	Yes	Your ERP type code (referenced by `on_hand.csv`'s `on_hand_type_xuid`)
`name`	Yes	Human-readable type name
`description`	No	Free text
`planning_type`	No	Engine classification — lowercase `bad` for unserviceable; anything else (e.g. `AVAILABLE`) = serviceable
`allocation_sequence`	No	Priority order for allocation (1 = highest)

on_hand.csv then references the type per stock row via on_hand_type_xuid.

Tenant example — GPS pooled crates (SAP storage locations)¶

The GPS tenants map SAP STORAGE_LOC codes to stock types. 1AVL ("Available") is good stock — as are direct-use, empty, and full crates:

SAP `STORAGE_LOC`	Meaning	Stock type	`planning_type`	Class
`1AVL`	Available	`UNRESTRICTED`	`AVAILABLE`	Good
`2DIR`	Direct-use	`UNRESTRICTED`	`AVAILABLE`	Good
`7EMP`	Empty	`UNRESTRICTED`	`AVAILABLE`	Good
`8FUL`	Full	`UNRESTRICTED`	`AVAILABLE`	Good
`3REP`	In repair	`IN_REPAIR`	`bad`	Bad
`REPR`	Repair plant	`IN_REPAIR`	`bad`	Bad
`5SCR`	Scrap	`SCRAP`	`bad`	Bad

Bad crates at a depot sit in its bad pool: the supply engine sizes RETURN and REPAIR orders from it — bad stock flows back through the reverse-logistics chain and re-enters the good pool at the repair yield.

Verifying a tenant¶

-- Type vocabulary + row/qty usage
SELECT t.id, t.xuid, t.planning_type, count(oh.id), COALESCE(SUM(oh.qty), 0)
FROM master.on_hand_type t
LEFT JOIN master.on_hand oh ON oh.type_id = t.id
GROUP BY 1, 2, 3 ORDER BY 1;

-- The Overview KPI split
SELECT
    SUM(CASE WHEN oht.planning_type IS DISTINCT FROM 'bad' THEN oh.qty ELSE 0 END) AS good_qty,
    SUM(CASE WHEN oht.planning_type = 'bad' THEN oh.qty ELSE 0 END) AS bad_qty
FROM master.on_hand oh
LEFT JOIN master.on_hand_type oht ON oht.id = oh.type_id;

Any unserviceable type whose planning_type is not exactly bad is a data bug — fix the type row (and the staging CSV) before trusting good/bad numbers.