Reconcile gateway payments with bank settlements

A finance team reconciles what the payment gateway recorded against what the bank actually settled. Money can be missing on either side.

Compare gateway_payments with bank_settlements on order_ref. Return only the exceptions: order_ref, gateway_amount, settled_amount and recon_status, where recon_status is:

• MISSING_SETTLEMENT when the gateway has the order but the bank does not
• UNEXPECTED_SETTLEMENT when the bank has it but the gateway does not
• AMOUNT_MISMATCH when both have it but the amounts differ

Orders that match exactly must not appear. Order by order_ref.

1. Step 1: A `FULL OUTER JOIN` keeps unmatched rows from both tables, padding the missing side with NULLs.
2. Step 2: `COALESCE(g.order_ref, b.order_ref)` gives one reference column regardless of which side the row came from.
3. Step 3: The `WHERE` clause keeps only rows where one side is missing or the amounts differ, which drops the clean matches.
4. Step 4: The `CASE` order checks the NULL sides first; a NULL amount would never satisfy `<>` on its own.

SELECT COALESCE(g.order_ref, b.order_ref) AS order_ref,
       g.amount          AS gateway_amount,
       b.settled_amount  AS settled_amount,
       CASE
         WHEN b.order_ref IS NULL THEN 'MISSING_SETTLEMENT'
         WHEN g.order_ref IS NULL THEN 'UNEXPECTED_SETTLEMENT'
         ELSE 'AMOUNT_MISMATCH'
       END AS recon_status
FROM gateway_payments g
FULL OUTER JOIN bank_settlements b ON b.order_ref = g.order_ref
WHERE g.order_ref IS NULL
   OR b.order_ref IS NULL
   OR g.amount <> b.settled_amount
ORDER BY order_ref;