Requirements Orders & Material Status

Requirements Orders & Material Status — Technical Spec

app/ro.py, inquiry_screen() / query_orders(), template ro_inquiry.html, screen key ro_inquiry, banner text from base.html: Requirements Orders & Material Status.

Route

Filters are ANDed. search: if the string isdigit() it matches h.order_no = %s OR h.source_id = %s OR h.job_name ILIKE %% OR h.bom_name ILIKE %%; otherwise job_name/bom_name ILIKE only. ro_status and ro_type are exact matches against h.ro_status / h.ro_type.

Data

Reads the ro database (ro_db()), views only:

• ro_header_v — order_no, job_name, bom_name, ro_type, date_required, ro_status, source_system, source_id, priority_code, source_status, source_status_at, fp_job_id.
• ro_detail_v — left-joined for two aggregates per order: line_count = count(d.line_key) and not_complete = count(*) filter (where d.line_state not in LINE_DONE_STATES).

LINE_DONE_STATES = ('Complete','Invoiced','Cancelled','Closed Short') (module constant in ro.py) is passed as a bound tuple parameter, not inlined SQL — the same constant backs the open chip in LINE_FILTERS on the order detail screen, so both screens agree on "open".

RO_TYPES = Residential | Commercial | Service | Truck — must match ro_header.ro_type's live CHECK constraint. It previously read ["Production","Service"], values the constraint rejects, so the Type filter could never match a row; fixed 2026-09-04. RO_STATUSES = Open | In Process | Complete | Cancelled | Empty; ro_status and line_state are both derived in the views, not stored columns — the view DDL (ro_schema_v4_0.sql) is not in this repo, see Known limits.

Inbox badge

_inbox_awaiting_count_safe(conn) runs select count(*) from ro_inbox where status = 'Awaiting Review' inside a try/except; on any error it rolls back and returns None, which hides the badge rather than rendering 0. This is deliberate: ro_inbox ships in a later DDL (create_inbox_tables.sql) and RO Inquiry must still render on a box where it has not been applied.

Links out of the screen

Permissions

Depends(current_user) only — any logged-in user of any role can load this screen. The ro_view action key exists in ACTION_DEFS (default: all four roles) but is not enforced on this route; see Known limits. No writes happen here at all.

Known limits / gaps

• ro_view is never checked. /ro/inquiry, /ro/issues* and the RO detail screens gate on current_user alone. Since ro_view defaults to all roles the behaviour is the same today, but tightening the permission in the DB would have no effect. [found 2026-09-06]
• The view DDL that derives ro_status, line_state, qty_open etc. lives only on the droplet / in ro_schema_v4_0.sql; it is not in this repo, so the exact derivation rules are documented from seed_ro_demo.sql's ladder comments and state_pill() in ro_order.html, not from the SQL itself.
• No paging: past LIST_LIMIT (1000) rows the only way to reach an order is to filter.
• No sortable columns and no Excel export on this screen (the Item Master grid has both).

Permission gate (added 2026-09-06)

Every route in ro.py now depends on ro_user() instead of current_user directly. ro_user() calls require_action(user, "ro_view"), so revoking View Requirements Orders screens on the Field Permissions screen actually blocks the RO screens (403) — before this the key existed and nothing read it. All four roles hold ro_view by default, so nothing changed for anyone until someone revokes it. Pick and Rush screens live in ro_pick.py / rush.py and keep their own ro_write / rush_* checks.

Sortable columns and issue badges (2026-09-07)

query_orders() left-joins an aggregate of ro_issue (status='Open', order_no is not null) as open_issues, grouped alongside the header columns. The template renders it as a badge on the Status cell linking to /ro/issues/{order_no}. Sorting is client-side over the rendered rows only (the list is capped at LINE_LIMIT server-side); Lines and Not Complete are deliberately excluded, per Dave.

BuilderTrend job status is not available — bt_document (the only BT data the app can see) carries doc_id/bt_job_id/job_name/title/folder/url only, no status field. The heading therefore stays "FP Job Status".