Purchasing Workbench

Purchasing Workbench — technical

Router: app/po.py, APIRouter(prefix="/po"). Own database connection (ro_db(), imported from ro.py) for ro_* reads; a second connection (im2_db()) for item-master facts. Same cross-database pattern ro.py already uses in fill_item_attributes().

Row universe

One row per SKU (inventory) or per distinct description (non-inventory — see Grouping below), built from:

1. ro_detail_v filtered to line_state not in (Complete, Invoiced, Cancelled, Closed Short) — every open demand line, INV and NONINV.
2. Every items row where qty_on_hand < min_qty (raw, not projected), even with zero open demand, added if not already present from step 1.

Recommended qty (brief rule 2, calc_rec_qty()) [P2, updated 2026-09-05/06]

projected_OH = OH − net_demand + qty_on_po rec = max(0, req_qty − OH − qty_on_po − qty_on_buy_lists + (Max − OH if projected_OH < Min else 0))

• qty_on_buy_lists (added 2026-09-05, Dave/Viktor item 5) is this SKU's quantity already committed to an open saved buy list (po_buy_list.status = 'Saved', po_buy_line.line_status <> 'Dropped'), from _qty_on_buy_lists_by_sku(). It is subtracted from the final result only — it does not enter projected_OH, so it never changes the min/max replenishment trigger.
• OH is floored at 0 before use (Dave 2026-09-04): a negative on-hand is a count error and must not inflate the buy.
• Non-inventory: rec = max(0, req_qty), no replenishment term and no OH/min/max/buy-list tracking. req_qty is qty_open

(qty_required − qty_issued + qty_returned) already GENERATED by ro_detail_v — this module never reads or writes qty_source / qty_adj / qty_required.

Grouping (_aggregate_demand())

INV lines group by sku. NONINV lines are never merged across RO lines — each keys on its own (order_no, line_key), one Workbench row per RO line, even when two lines share identical description text (Viktor, 2026-09-04: two non-inventory lines can carry different cost and vendor, and merging by description silently drops the job/class that drives the buy). A NONINV row's URL/drill-down key is f"N:{order_no}:{line_key}"; routes accept it via a {sku:path} converter and parse it with _parse_noninv_key(). Because a NONINV row now carries a real (order_no, line_key), its supply drill-down (GET .../supply) looks up ro_po_link directly by that pair instead of always returning empty — ro_po_link is keyed for both line types per the schema comment on that table.

Cost, vendor, delivery date defaults

• Cost: items.purchase_cost (INV) or ro_detail.est_unit_cost (NONINV) — both unloaded base costs. Nothing in this file applies freight or tax.
• Vendor: items.primary_vendor; if null, falls back to the most recent ro_po_link.vendor_name for that SKU (_last_bought_vendor()) — no QuickBooks call. Falls back further to ro_detail.suggested_vendor if neither exists. vendor_flagged is true whenever an item has no primary vendor, regardless of which fallback filled it in.
• Delivery date: today + items.lead_time_days working days (add_working_days() — weekends only, no holiday calendar).

On-PO qty

_on_po_by_sku(): sum(qty_ordered − qty_received) from every ro_po_link row joined to a ro_detail line for that SKU where po_status <> 'Closed' — independent of which RO drove the PO, since an open PO covers replenishment regardless of the order that triggered it.

Rush priority, age and default sort [P2 — folded in from the Rush addendum, 2026-09-06]

• Priority per row is the highest priority among every RO contributing demand to it (_highest_priority(), _PRIORITY_ORDER = {R:0, 1..5}) — an INV row aggregates by SKU, so this is a per-row maximum, not a per-RO value. rush_since is the ro_created_at of the oldest still-open Rush RO behind the row; rush_age is _format_age()'s just now / Xm / Xh Ym / Xd Yh.
• priority querystring param (validated against _PRIORITY_ORDER) filters the aggregated rows in filter_workbench_rows(); the template offers it as an All priorities / Rush (R) / 1–5 dropdown plus a Rush only chip.
• Default sort: build_workbench_rows() returns rows sorted (0 if priority=='R' else 1, rush_since, sku, description) — Rush first, oldest wait first. An explicit column sort (sort_workbench_rows, WB_SORT_COLUMNS = {sku, vendor, delivery}) fully replaces it.
• A Rush row is never hidden by the include_zero filter, even at rec_qty == 0 (Dave, 2026-09-05).
• Demand drill-down returns each contributing RO's priority_code plus can_set_rush (can(user, "rush_priority_set")); base.html's renderDemandTable() / wireRushToggles() render the per-RO Set Rush / Clear Rush buttons shared with the SKU detail page.
• POST /ro/{order_no}/priority (in ro.py) is the only writer: exactly 'R' or NULL, require_action(user, "rush_priority_set") (default admin/level1), one ro_audit row (rush_priority_set / rush_priority_clear). FP-sourced 1–5 values stay read-only everywhere.
• Not touched by the Rush build: calc_rec_qty(), POST /workbench/save, the CC logic, buy_list_display_status(). notify.notify_release() exists but still has no live call site.

Filters and paging

Vendor / RO # / required-by date range filter the raw ro_detail_v rows before aggregation (cheap, pushed to SQL). Below-min-only, priority, include_zero and inventory/non-inventory filter the aggregated, already-computed row list in Python (filter_workbench_rows()), since below-min depends on the computed OH/min comparison. include_zero (querystring zero_ok, chip Include 0 to order, default OFF) drops rows with rec_qty == 0 and not qty_edited, except Rush rows. Pagination (PAGE_SIZE = 200) is also done in Python, after computing the full filtered working set — see the interface note below.

Interface point for the tuned demand/supply query

build_workbench_rows(ro_conn, im2_conn, *, order_no=None, date_from=None, date_to=None) is the one seam: it returns a list of dicts, one per row, each carrying at least sku, row_key, line_type, description, uom, req_qty, oh, on_po, min_qty, max_qty, rec_qty, cost, vendor, vendor_flagged, delivery_date, extended_cost, order_nos, earliest_required, below_min, priority, rush_since, rush_age, qty_on_buy_lists, qty_edited, bins, order_no/line_key (NONINV only). [P2, 2026-09-06] Every route calls this function and nothing else for row data — a hand-tuned or materialized-view-backed version can replace the body of this function without touching any route or template, as long as it returns the same shape.

Permissions

Viewing: any authenticated role. Editing a row (client-side, pre-save) or saving a buy list: can_write() — role in (admin, level1, level2), a fixed threshold per the brief, not backed by role_action_perms the way ro.py's ro_write is (flag to product if this should become admin-configurable later).

Drill-down endpoints

System constant

WB_COUNT_BEFORE_RELEASE is im2.py's wb_count_before_release setting (added to SETTING_DEFS / BOOL_SETTINGS alongside allow_auto_po, default Yes), read via im2.setting_bool(). Editable from the existing Settings screen — no new UI was built for it.

Ver 3 - 2026-09-07 - non-inventory supply, and vendor overrides

Both from Dave's PO testing: "ordering a Non-Inv item leaves it ready to order and doesn't take it off the WB", "it also doesn't remember the vendor changed in the WB", and after three test releases of one line: "all the POs are recorded so it's over ordered".

• calc_rec_qty()'s NONINV branch was max(0, req_qty) - it ignored supply entirely, because before row_key existed a saved buy line could not be traced back to its RO line. It is now max(0, req_qty - on_po - qty_on_buy_lists).
• _noninv_on_po(conn, keys) sums qty_ordered - qty_received from ro_po_link per (order_no, line_key), excluding Cancelled/Closed POs. NONINV rows are the only ones linked to a PO, so this is the whole supply picture for them.
• _noninv_on_buy_lists(conn, keys) does the same from po_buy_line.row_key ('N:<order_no>:<line_key>') for lists still Saved, ignoring Dropped/Cancelled/Ordered.
• build_workbench_rows() now populates on_po and qty_on_buy_lists for NONINV rows (they used to be forced to None), so the columns and the drill-down agree.
• New table po_row_override(row_key primary key, vendor, updated_by, updated_at) with POST /po/workbench/vendor. _row_vendor_overrides() applies it in build_workbench_rows(); an override also clears vendor_flagged for the row. Vendor only, deliberately: item cost stays owned by the Item Master (Dave, 2026-09-03). The grid commits on the input's change event (po_workbench.html Ver 8); blank deletes the override row.

Shared table sort (2026-09-07)

Column sorting is one implementation in templates/base.html (Dave, 2026-09-07: "all screens that have tables like this need sorts on the appropriate columns"). A table opts in with class="sorttable"; every thead th becomes sortable except those with class="nosort"/class="toggle", an empty heading, or a checkbox in the heading. Client-side only, over the rows already rendered. Details: tech/table_sorting.md.

Cycle-count flag is inventory-only (2026-09-07, Dave)

A non-inventory line is bought for one RO line and holds no stock, so a count request on it means nothing and only parks the line in Pending Count, which then blocks release. Enforced in three places, not one:

1. templates/po_workbench.html (Ver 10) renders a dash, not an input.f-cc, when r.line_type == 'NONINV' — so the row is invisible to the #ccAll select-all and is never posted as cc.
2. templates/po_buylist.html (Ver 8) does the same for a saved line.
3. po.py save sets cc = key in ccs and line_types[i] != 'NONINV', and POST /po/buylist/{list_id}/line/{n}/edit returns 400 for field=cc on a NONINV line.

Two pre-existing rows (list 4, lines 1–2) were cleared from Pending Count to Ready when this shipped.