Which delivered order quantities have not been billed?

Define the decision and input records

Both deliveries and billed lines can be split, so aggregate each independently before joining the order line. A flat join would multiply split delivery rows by split billing rows. Compare quantities rather than just checking whether any invoice exists: a partially billed line still has unbilled units.

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
), billed AS (
 SELECT line_id,SUM(qty) AS qty FROM billed_lines WHERE status='issued' GROUP BY line_id
)
SELECT l.id,l.order_id,s.qty AS delivered_qty,COALESCE(b.qty,0) AS billed_qty,
 (s.qty-COALESCE(b.qty,0))*l.unit_cents AS unbilled_cents
FROM order_lines l JOIN orders o ON o.id=l.order_id
JOIN shipped s ON s.line_id=l.id LEFT JOIN billed b ON b.line_id=l.id
WHERE o.status='confirmed' AND s.qty>COALESCE(b.qty,0) ORDER BY l.id;

L1 delivered four units but billed only two. At 1000 cents per unit, the remaining two represent HK$20.00 in this fixture. L2 is fully billed; the pending delivery for L3 is excluded. Verify delivery acceptance and billing terms before preparing another invoice.

Apply the result within its limits

The amount uses a single unit price with no discounts, returns or tax. It is a follow-up exposure, not recognised revenue. Cancelled orders are omitted even if a delivery exists; investigate those contradictions separately.

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.