Which confirmed order lines still need delivery?

Define the decision and input records

An order can contain a complete line and a partial line. Reduce completed deliveries to one total per line and compare with ordered quantity. Pending delivery rows do not reduce the remaining quantity. The line-level output gives the dispatch team an actionable SKU-linked record, while a header-only report can hide partial fulfilment.

All records below are synthetic. Create these tables in a fresh SQLite database before running the read-only query. Quantities are units; monetary values are integer HKD cents, so divide by 100 when presenting HK dollars.

CREATE TABLE orders(id TEXT PRIMARY KEY, status TEXT);
CREATE TABLE order_lines(id TEXT PRIMARY KEY, order_id TEXT, sku TEXT, ordered_qty INTEGER, unit_cents INTEGER);
CREATE TABLE deliveries(id TEXT PRIMARY KEY, line_id TEXT, qty INTEGER, status TEXT);
CREATE TABLE billed_lines(id TEXT PRIMARY KEY, line_id TEXT, qty INTEGER, status TEXT);
INSERT INTO orders VALUES ('O1','confirmed'),('O2','confirmed'),('O3','cancelled');
INSERT INTO order_lines VALUES ('L1','O1','A',5,1000),('L2','O1','B',3,2000),('L3','O2','C',4,500),('L4','O3','A',1,1000);
INSERT INTO deliveries VALUES ('D1','L1',2,'completed'),('D2','L1',2,'completed'),('D3','L2',3,'completed'),('D4','L3',1,'pending'),('D5','L4',1,'completed');
INSERT INTO billed_lines VALUES ('BL1','L1',2,'issued'),('BL2','L2',3,'issued'),('BL3','L1',1,'void');

Run the query and compare the result

WITH shipped AS (
 SELECT line_id,SUM(qty) AS qty FROM deliveries WHERE status='completed' GROUP BY line_id
)
SELECT l.order_id,l.id AS line_id,l.ordered_qty,COALESCE(s.qty,0) AS delivered_qty,
 l.ordered_qty-COALESCE(s.qty,0) AS remaining_qty
FROM order_lines l JOIN orders o ON o.id=l.order_id
LEFT JOIN shipped s ON s.line_id=l.id
WHERE o.status='confirmed' AND COALESCE(s.qty,0)<l.ordered_qty
ORDER BY l.order_id,l.id;

O1 still needs one unit of L1; L2 is complete and omitted. O2 needs all four units of L3 because its one-unit delivery is pending. O3 is cancelled and excluded. These are outstanding delivery quantities, which should be compared with available stock before promising a dispatch date.

Apply the result within its limits

This model has no backorder cancellation, substitutions, returned deliveries or authorised overdelivery. Those require signed quantity events and separate exception checks. An empty result means no shortfall under these rules, not proof every order is correct.

The supplied setup and result were executed with SQLite. Adapt the schema and status rules to a test copy of your system, then agree the expected result with the process owner. These examples have not been tested on other database platforms.