Pick Review & Posting — Technical Spec

1. Routes

app/ro_pick.py (screen + orchestration) and app/ro_post.py (QuickBooks posting). Template ro_picks.html. All routes require _require_post → POST_ROLES = ("admin", "level1"), else 403.

POST /ro/pick/{pick_id}/save with action=post_now funnels into the same _do_post.

2. Pending / posted queries

_pending_pick_rows: ro_pick LEFT JOIN ro_header LEFT JOIN ro_pick_line, status = 'OPEN', having count(pl.qty_picked) > 0 — a session with no entered qty is never listed. job_name = coalesce(p.qb_customer_name, h.job_name), bom_name = coalesce(h.bom_name, 'Direct issue — no RO'). Ordered updated_at desc. purge_empty_picks() runs on each pending render.

_extended_cost: one batched ro_pick_line read plus one IM2 read of coalesce(purchase_cost, 0); per session sum(abs(qty_picked) * cost), rounded to 2dp. IM2 unreachable → costs {} → all zeros. Absolute value, so returns increase extended cost.

Posted tab: status='POSTED', newest 100 by posted_at, showing posted_by, posted_at, qb_invoice_number, qb_credit_number.

3. _do_post(pick_id, user, raise_on_error=True)

1. _require_post; POSTING_ENABLED false → 403 / {"ok": false, error: POST_BLOCKED_MSG}.
2. 404 if the session is missing; 400 if already POSTED or VOID.
3. Calls post_pick_to_qb(pick_id, posted_by=user["username"]) = ro_post.post(pick_id, posted_by=…).
4. Any exception: str(exc)[:400] written to ro_pick.post_error in a separate transaction, then 502 (single post) or an entry in the bulk errors list. The session stays OPEN — an OPEN session with post_error set is the failed state (there is no post_status column).

Bulk post loops _do_post(..., raise_on_error=False) and returns {"ok": true, "posted": n, "failed": m, "results": [...], "errors": [...]} — ok is true even when every session failed.

3a. POST /ro/picks/{pick_id}/line-qty

Form pick_line_id + qty (Dave, 2026-09-06). _require_post; 404 if the session or the line is missing, 400 if the session is not OPEN or qty is not a number.

• qty != 0 → ro_pick_line.qty_picked = qty, zeroed = false, updated_at = now().
• qty = 0 and manual → the row is deleted (the addition disappears).
• qty = 0 and an order line → qty_picked = null, zeroed = false (back to untouched; the requirement stays owed).
• Always an ro_audit row: action='pick_line_edit', operator = user, note pick_id=… sku=… <old> -> <qty n|addition removed|back to not picked> (Pick Review).
• Returns {ok, pick_id, removed: qty==0, has_qty: pick_has_qty(...)} so the screen can tell whether the session still has any quantity on it.

4. ro_post.post(pick_id, dry_run=False, posted_by=None)

One ro transaction (with conn:), SELECT … FOR UPDATE OF p.

1. Session must be OPEN, else PostError.
2. Class = coalesce(p.class_ref, h.class_ref). Service RO with no qb_customer_id is pinned to SERVICE_CUSTOMER_ID = 15326 ("SA:Admin") with SERVICE_CLASS_ID = 1202540. Otherwise a blank class is derived from ro_type via RO_TYPE_CLASS (service/residential/commercial) and written back to ro_header. No RO and no class → PostError (Dave's "prompt and force it or disallow").
3. qb_item_map (IM2 items.qb_item_id) for every SKU; any missing → PostError No QB item id for SKUs: … before any document is created.
4. Line bucketing: zeroed or qty_picked == 0 → zero bucket; qty_picked is null → skipped; line_type/ro_line_type == 'SURPLUS' → surplus bucket (no QB doc); positive → Invoice line; negative → CreditMemo line. Each line is Amount 0, SalesItemLineDetail with ItemRef, Qty abs(qty), UnitPrice 0, ClassRef = resolved class.
5. Customer: p.qb_customer_id else h.qb_customer_id else resolve_customer(job_name) — exact FullyQualifiedName then DisplayName; anything but exactly one match is a PostError. Resolved ids are written back to ro_header.
6. Creates the CreditMemo first, then the Invoice (Dave, 2026-09-06), with TxnDate = today, PrivateNote/CustomerMemo = RO {n} pick #{id} — material issue (no charge) (or the direct-issue wording). One ro_txn row per posted line (direction Issue/Return, qb_doc_type, qb_doc_id, qb_doc_number, source='Workbench', operator = ro_pick.operator).
7. Surplus lines: ro_txn row with qb_doc_type NULL, no QB document.
8. Zeroed lines: UPDATE ro_detail SET qty_adj = -qty_source, qty_source_at_adj = qty_source, adj_by, adj_at = now(), adj_note = 'zeroed on pick #{id} (substituted/not needed)' — never touches qty_source or the generated qty_required.
9. ro_pick → status='POSTED', posted_by = posted_by or head['operator'] (whoever pressed Post; ro_pick.operator stays the picker), posted_at = now(), plus both QB id / number pairs (either may be NULL).

Failure handling around those two calls:

• credit memo raises → PostError "… Nothing was written to QuickBooks." and the DB transaction rolls back; nothing exists in QB.
• invoice raises with a credit memo already posted → qb.delete_doc("CreditMemo", id, SyncToken) (QB operation=delete; invoices would be void). Returns True → PostError saying the credit memo was removed again and nothing is left in QuickBooks. Returns False → PostError naming the credit memo and telling the operator to void it in QuickBooks by hand before posting again.
• delete_doc never raises: it returns False on any HTTP or transport failure.

Returns {pick_id, class_ref, invoice_lines, credit_lines, zeroed, surplus, qb: {invoice|credit: {id, number}}}.

5. Discard

status → 'VOID' plus an ro_audit row (action='pick_discard', operator, note='pick_id=…; discarded, not posted'). Never a hard delete. 400 if the session is not OPEN. (Empty sessions are hard-deleted, by purge_empty_picks/delete_empty_pick, because there is nothing to audit.)

6. Known limits / gaps

• POSTING_ENABLED = False in ro_pick.py — no real QB document can be created until it is flipped; the whole post path is therefore untested in production from the UI.
• The SURPLUS bucket is dead code as of this build (nothing writes line_type='SURPLUS').
• _extended_cost uses abs(), so the totals row is a gross material value, not a net.
• Bulk post returns HTTP 200 with ok: true even when every session failed.
• Line editing is quantity-only: there is no route to change a SKU, a class or a line's line_key from this screen, and line-qty does not re-check qty_oh.
• If the invoice fails and the credit-memo delete also fails, the QB credit memo survives while the DB transaction rolls back — the operator message is the only record; nothing re-checks it later.
• ro_txn.operator is still ro_pick.operator (the picker), even though ro_pick.posted_by now records who pressed Post.

Batch discard (2026-09-06)

POST /ro/picks/discard with {"pick_ids": [...]} voids every selected session in one transaction and returns {"ok": true, "discarded": n}; any id that is missing or not OPEN produces a 400 naming it and nothing is voided. The screen no longer loops one request per id (Dave: partial completion under a "3 discarded, 1 failed" message). POST /ro/picks/{pick_id}/discard is unchanged for the per-row button.

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.