Prophet 21 Open Order and Backorder SQL Query Generator

-- Pattern only. Confirm table and column names in your schema before use.
-- Read-only: use a reporting login.
SELECT TOP 300
    h.order_no,
    h.customer_id,
    h.order_date,
    DATEDIFF(day, h.order_date, GETDATE()) AS age_days,
    l.line_no,
    l.qty_ordered,
    l.qty_canceled,
    (l.qty_ordered - l.qty_canceled) AS qty_open_gross
FROM oe_hdr h
INNER JOIN oe_line l
    ON l.order_no = h.order_no
WHERE h.order_date >= '2026-01-01'
  AND h.customer_id = 100245
  AND DATEDIFF(day, h.order_date, GETDATE()) >= 7
ORDER BY h.order_date, h.order_no, l.line_no;

-- 1. Join your shipped or invoiced quantity source to subtract what has already shipped.
-- 2. Add the completed or closed flag columns that your system uses on header and line.
-- 3. Exclude quotes, returns and cancelled orders using your order type fields.
-- 4. Add expected ship date if you want to separate past due from future.