By YX Fulfillment
An order line says four units. Two separate receipts each show one returned unit against that line. Join the receipts directly to the order line and the order quantity appears twice, so summing it gives eight.
Here is a synthetic reconciliation for one SKU. Four units were ordered on the first line and two on the second. Two distinct units from the first line were received as returns in separate receipts. A third parcel has a return approval but has not yet arrived, so it doesn't count toward received units.
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE order_line (id TEXT PRIMARY KEY, quantity INTEGER NOT NULL);
CREATE TABLE return_receipt (
id TEXT PRIMARY KEY, line_id TEXT NOT NULL, units INTEGER NOT NULL, state TEXT NOT NULL
);
INSERT INTO order_line VALUES ('L1',4), ('L2',2);
INSERT INTO return_receipt VALUES ('R1','L1',1,'received'),
('R2','L1',1,'received'),
('R3','L2',1,'approved');
""")
wrong = db.execute("""
SELECT SUM(l.quantity), SUM(r.units)
FROM order_line AS l
LEFT JOIN return_receipt AS r ON r.line_id=l.id AND r.state='received'
""").fetchone()
correct = db.execute("""
WITH receipts AS (
SELECT line_id, SUM(units) AS returned_units
FROM return_receipt WHERE state='received'
GROUP BY line_id
)
SELECT SUM(l.quantity), SUM(COALESCE(r.returned_units,0))
FROM order_line l LEFT JOIN receipts r ON r.line_id=l.id
""").fetchone()
assert wrong == (10, 2)
assert correct == (6, 2)
print("From raw join:", wrong)
print("From one row per line:", correct)
Output from a local run on Python 3.9.6
From raw join: (10, 2)
From one row per line: (6, 2)
Pre-aggregating receipts to one row per order line keeps the order quantity from repeating. The corrected report shows six ordered units and two received returns. SUM(DISTINCT l.quantity) is also unreliable as a remedy because two different lines can legitimately contain equal quantities.
Confirm the intended date basis before you group over time. Orders placed today divided by returns received today is a flow comparison across different cohorts. To estimate returns against orders from a given week, select those order lines first and use a stated receipt observation window. Reconciliation should retain both raw measures with their definitions even when the UI displays a derived ratio.
Top comments (0)