The ERP and the billing system both record invoices, in different shapes.
billing_invoices (invoice_no, amount_cents, billed_on) is typed: an integer invoice number, the amount in cents, a DATE.
erp_invoices (erp_ref, amount_text, invoice_date_text) is all text, typed in by hand:
- •
erp_ref — the invoice number, possibly with leading zeros and surrounding spaces (' 000101 ' is invoice 101). Anything else that isn't all digits can't be read. - •
amount_text — the amount in currency units, possibly with thousands separators ('1,234.50', '2,000'). Anything else (a currency symbol, letters) can't be read. - •
invoice_date_text — day first: DD/MM/YYYY. An impossible date can't be read.
Report every problem, one row each, checked in this order (the first that applies):
1. erp_unparseable — an ERP row where any of the three fields can't be read 2. missing_in_billing — an ERP row whose invoice number billing doesn't have 3. missing_in_erp — a billing invoice whose number no ERP row's erp_ref reads as 4. amount_mismatch — the two amounts differ 5. date_mismatch — the two dates differ
Invoices that agree aren't listed. The check must report bad data, not fail on it.
Columns: invoice_no (the number, NULL when an ERP reference can't be read), erp_ref (the ERP row's original text, NULL for missing_in_erp), issue. Sort by invoice_no with NULLs last, then erp_ref.