Define the decision and input records
Compute completed refunds per payment before computing net paid per invoice. Joining invoices, payments and individual refunds in one flat aggregate can multiply values. Use due_on earlier than the fixed report date: an invoice due on the report date is not overdue under this example policy.
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.
| id | amount_cents | due_on | status |
|---|---|---|---|
| I1 | 100000 | 2026-10-01 | issued |
| I2 | 50000 | 2026-10-20 | issued |
| I3 | 20000 | 2026-10-02 | issued |
| I4 | 30000 | 2026-09-01 | void |
CREATE TABLE customers(id TEXT PRIMARY KEY, name TEXT);
CREATE TABLE invoices(id TEXT PRIMARY KEY, customer_id TEXT, amount_cents INTEGER, due_on TEXT, status TEXT);
CREATE TABLE payments(id TEXT PRIMARY KEY, invoice_id TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE refunds(id TEXT PRIMARY KEY, payment_id TEXT, amount_cents INTEGER, status TEXT);
INSERT INTO customers VALUES ('C1','Acorn'),('C2','Birch');
INSERT INTO invoices VALUES ('I1','C1',100000,'2026-10-01','issued'),('I2','C2',50000,'2026-10-20','issued'),('I3','C9',20000,'2026-10-02','issued'),('I4','C1',30000,'2026-09-01','void');
INSERT INTO payments VALUES ('P1','I1',40000,'settled'),('P2','I1',20000,'settled'),('P3','I2',50000,'settled'),('P4','I3',20000,'failed');
INSERT INTO refunds VALUES ('R1','P1',10000,'completed'),('R2','P1',5000,'pending'),('R3','P3',50000,'completed');Run the query and compare the result
WITH refunded AS (
SELECT payment_id,SUM(amount_cents) AS cents FROM refunds
WHERE status='completed' GROUP BY payment_id
), paid AS (
SELECT p.invoice_id,SUM(p.amount_cents-COALESCE(r.cents,0)) AS cents
FROM payments p LEFT JOIN refunded r ON r.payment_id=p.id
WHERE p.status='settled' GROUP BY p.invoice_id
)
SELECT i.id,i.due_on,i.amount_cents-COALESCE(p.cents,0) AS outstanding_cents
FROM invoices i LEFT JOIN paid p ON p.invoice_id=i.id
WHERE i.status='issued' AND i.due_on<'2026-10-10'
AND i.amount_cents-COALESCE(p.cents,0)>0 ORDER BY i.id;| id | due_on | outstanding_cents |
|---|---|---|
| I1 | 2026-10-01 | 50000 |
| I3 | 2026-10-02 | 20000 |
I1 has HK$500.00 outstanding: HK$1,000.00 less HK$600.00 settled payments plus HK$100.00 completed refunds. I3 remains HK$200.00 unpaid because its payment failed. I2 is not overdue even though a full refund leaves its balance open.
Apply the result within its limits
For this fixture a refund reopens the invoice balance. Some systems issue a credit note that reduces the invoice amount instead; that ledger needs a different formula. No tax treatment, late fees or currency conversion is modelled.
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.