Order book

1-- core.traede.order_book
2-- Every sales order line with what has been shipped and invoiced against it, and
3-- what is still open.
4-- Grain: one row per traede_account x order x variant (an order line).
5-- Depends on: staging.traede.order, .order_line, .customer, .variant, .product
6--
7-- This is the wholesale order book: pre-orders taken at a trade show or in the B2B
8-- shop, waiting for stock to arrive and ship. Sum open_net_amount for the value of
9-- confirmed orders not yet sent; group by created or approved date for order
10-- intake.
11--
12-- CURRENT STATE. The connector syncs the order as it is now, so the book cannot be
13-- rebuilt for a past date. Materialise this model on a schedule and keep the runs
14-- if you want to trend it.
15--
16-- WHAT COUNTS AS OPEN: an approved order that is not cancelled, with units ordered
17-- that have not yet been shipped. Traede's `shipped` rises when units are sent to
18-- the warehouse for picking, so "open" here means not yet released to the
19-- warehouse. Draft and selection orders (internal_status) are returned but never
20-- open, because they are not confirmed yet.
21--
22-- REMOVED LINES. If a line is deleted from an order in Traede, whether its row
23-- disappears from the warehouse depends on the sync: a full re-sync with soft
24-- deletes enabled marks it deleted, an incremental sync may leave it in place. If the
25-- order book looks too high for a handful of edited orders, that is the first
26-- place to look.
27
28SELECT
29    o.traede_account,
30    o.order_id,
31    o.order_number,
32    l.variant_id,
33    l.product_id,
34    p.item_number,
35    p.product_name,
36    v.sku,
37
38    o.order_type,
39    o.internal_status,
40    o.status,