Hack 37. Reconcile Invoices and Remittances

You have a pile of unpaid invoices and a file of payments from your bank's system. Figuring out which payment is for which invoice can be a headache.

The lists shown in Table 5-18 and Table 5-19 should match, but sometimes people don't follow the correct procedures (and sometimes they make mistakes). The custAcc column contains the bank account number you expect a customer to use when making a payment. This should match with a value in the payerAC column, which contains the actual payments received. The BACS table comes from your bank's automated system. The invoice table comes from your local system.

Table 5-18. The invoice table

id	cust	custAcc	amount
1001	Elmer	8003	19.99
1002	Daffy	4004	20.99
1003	Coyote	8015	11.22
1004	Dick		10.49

Table 5-19. The BACS table

payerAC	amount	payeeRef
8003	19.99	1001
4004	20.99	Ref 1002
1001	17.22	8015:1003
7003	10.94	Dick D.

A table showing BACS payments coming in is available, and this should match the outstanding invoices. You can make a number of verifications:

1. You should have each customer's account number on the invoice system, where the customer account matches the payerAC so that you know you have the right payment.
2. The payer can put the corresponding invoice number into the payeeRef field.

A number of things can go wrong:

1. Customers' bank details may change without notice.
2. Some customers share a payment service, so they may appear to have the same bank account number. This might be because they are using the same third-party payment service, but it could also be that they are both with the same parent company. Nevertheless, you need to treat them as separate accounts.
3. When completing the payeeRef field, the users may use an inconsistent style for entering data, or perhaps the software they use does not allow them to do as instructed.
4. Customers may not pay the exact amount that you are expecting.

Matching the invoices to the payments is a messy task. It requires human judgment; it often requires a few phone calls too. However, you can automate the simple cases and provide assistance with the more complex ones.

5.14.1. Find the Exact Matches

In an ideal situation, you will find that the customer account numbers match, the payeeRef matches the invoice number, and the amount paid matches the amount of the invoice total. With luck and a stable client list, this will occur in a majority of cases:

mysql> SELECT * FROM invoice JOIN bacs
 -> ON invoice.id = bacs.payeeref
 -> AND invoice.custAcc = bacs.payerAC
 -> AND invoice.amount = bacs.amount;
+------+-------+---------+--------+---------+--------+----------+
| id | cust | custAcc | amount | payerAC | amount | payeeRef |
+------+-------+---------+--------+---------+--------+----------+
| 1001 | Elmer | 8003 | 19.99 | 8003 | 19.99 | 1001 |
+------+-------+---------+--------+---------+--------+----------+

Unfortunately, you've got only one match. The two matching rows should be copied to another table and deleted so that the number of potential matches for the rest of your investigation is reduced.

5.14.2. Invoice Numbers Do Not Match

The data you needed to make the match is in payeeRef 'Ref 1002' but the match wasn't made because the format is not exactly right. You can weaken the JOIN condition to allow for "junk" on either side of the required value. A LIKE condition will do the job:

mysql> SELECT * FROM invoice JOIN bacs
 -> ON bacs.payeeRef LIKE CONCATENATE('%',invoice.id,'%')
 -> AND invoice.custAcc = bacs.payerAC
 -> AND invoice.amount = bacs.amount;
+------+-------+---------+--------+---------+--------+----------+
| id | cust | custAcc | amount | payerAC | amount | payeeRef |
+------+-------+---------+--------+---------+--------+----------+
| 1002 | Daffy | 4004 | 20.99 | 4004 | 20.99 | Ref 1002 |
+------+-------+---------+--------+---------+--------+----------+

You can have high confidence in the matches made so far, but now you are going to have to intervene manually.

5.14.3. Find Possible Matches

At this stage, the bookkeeper needs to see the two lists side by side, with each sorted by the amount. She can find the likely matches where the two values are identical. Of course, you can do this in SQL.

If a value has been entered incorrectly, a single digit error is one of the most likely results. You can compare every possible pair of numbers and look for a single digit difference. The most likely candidates are 1 and 7, which often get confused when handwritten.

Having removed the two known matches from both tables, you are left with the values 11.22 and 10.49 in the invoice table and 17.22 and 10.94 in the BACS table. You can list all of the four possible pairings with a cross-join:

mysql> SELECT invoice.amount AS invoice, bacs.amount AS bacs,
 -> ABS(invoice.amount-bacs.amount) AS diff
 -> FROM invoice CROSS JOIN bacs;
+---------+-------+------+
| invoice | bacs | diff |
+---------+-------+------+
| 11.22 | 17.22 | 6.00 |
| 10.49 | 17.22 | 6.73 |
| 11.22 | 10.94 | 0.28 |
| 10.49 | 10.94 | 0.45 |
+---------+-------+------+

Notice that the pair with a single digit difference (11.22 and 17.22) has a difference of 6.00, which has exactly one nonzero digit. This property is characteristic of a single digit error:

mysql> SELECT invoice.amount AS invoice, bacs.amount AS bacs,
 -> ABS(invoice.amount-bacs.amount) AS diff,
 -> REPLACE(ABS(invoice.amount-bacs.amount),'0','')
 -> FROM invoice CROSS JOIN bacs;
+---------+-------+------+-------------------------------------------------+
| invoice | bacs | diff | REPLACE(ABS(invoice.amount-bacs.amount),'0','') |
+---------+-------+------+-------------------------------------------------+
| 11.22 | 17.22 | 6.00 | 6. |
| 10.49 | 17.22 | 6.73 | 6.73 |
| 11.22 | 10.94 | 0.28 | .28 |
| 10.49 | 10.94 | 0.45 | .45 |
+---------+-------+------+-------------------------------------------------+

Because removing the zeros leaves the one nonzero digit plus the decimal point, you complete the test using LIKE '_ _':

mysql> SELECT invoice.amount AS invoice, bacs.amount AS bacs,
 -> ABS(invoice.amount-bacs.amount) AS diff
 -> FROM invoice CROSS JOIN bacs
 -> WHERE REPLACE(ABS(invoice.amount-bacs.amount),'0','')
 -> LIKE '_ _';
+---------+-------+------+
| invoice | bacs | diff |
+---------+-------+------+
| 11.22 | 17.22 | 6.00 |
+---------+-------+------+

The other most likely error is a transposition [Hack #38].