Production order status

1-- core.traede.production_order_status
2-- Every production order line with what has been received, what is still
3-- outstanding, and how far the supplier's dates have slipped.
4-- Grain: one row per traede_account x production order line.
5-- Depends on: staging.traede.production_order, .production_order_line, .variant, .product
6--
7-- This is the inbound side of the order book: what is coming from suppliers, when,
8-- and whether it is late. Join to core.traede.order_book on variant_id to see open
9-- customer demand against incoming supply.
10--
11-- SLIPPAGE compares the current expected arrival with the date originally
12-- promised. Positive means later than promised. A production order that was never
13-- re-planned has both dates equal and a slippage of zero, not NULL.
14--
15-- OVERDUE means the expected arrival has passed and units are still outstanding on
16-- an order that is not received, booked or closed. Statuses are Traede's own
17-- enum; an account with custom workflows may need to extend the list.
18--
19-- Costs are in the supplier's currency, which the connector does not sync. Do not
20-- sum outstanding_cost_amount across suppliers that invoice in different
21-- currencies.
22
23SELECT
24    po.traede_account,
25    po.production_order_id,
26    po.production_order_number,
27    l.production_order_line_id,
28    po.status,
29    po.supplier_id,
30
31    l.product_id,
32    p.item_number,
33    p.product_name,
34    l.variant_id,
35    v.sku,
36
37    DATE(po.created_at)                     AS order_date,
38    po.original_expected_departure_date,
39    po.expected_departure_date,
40    po.original_expected_arrival_date,