Professional SQL Server™ 2005 Integration Services

19.2. Business Problem

Just working with the low-quality payment data involves a significant amount of manual lookup to match payments to customers and invoices. Since Company ABC is growing, the volume of payments is exceeding their capacity to continue to process payments manually. If the invoice number was always received with the payment, an automated process could easily identify the customer from the invoice and make some decisions about the payment by comparing the paid and billed amounts. So far, attempts at automating even the paper invoice through the mail have failed because customers don't always send in copies of invoices, or they send in old, outdated invoices. Using the bank lockbox has helped ease the burden of getting the deposits processed, but the bank makes mistakes too, truncating and transposing customer name or invoice data. Opening up the company to wires and PayPal accounts has really complicated matters, since very little corroborating data is provided in these transactions.

Approximately 60% of the incoming payments can't be automatically identified using a strict compare of invoice number and payment amount. The good news is that they can almost all be manually identified by a small group of subject matter experts (SMEs) who really understand the process and know how to use the corporate data. The bad news is that once a customer and invoice are identified by the SMEs, the method of making the match is not recorded. The next month the process of identification ...