Solution Summary


Company ABC has made the need to resolve this payment processing hurdle their top priority. They already have a custom software application that gives users the ability to break the bulk payments down to an employee level, but the application requires that the customer and invoice be identified. The solution is to use SSIS to develop a package that can process data from these heterogeneous data sources. This solution should require no human intervention on items that can be identified as paid-as-billed items. The solution should be as "smart" as possible and be able to identify bank items that have been manually identified before. Items that can't be identified will still need to be processed manually, but it is expected that this number should drop 20 to 40 percent.

In preparation for the design of the SSIS package, specification documents for the two input files, ACH and Lockbox, have been gathered. The files contain multiple payment transactions and can be sent by both the bank and ACH clearinghouse to specific folders. Your process should be able to access these folders and continuously look for new files to process. When a file is located in the input folder, it needs to be validated for proper format, for previous processing, so a file is not processed more than once, and for each payment to summarize and balance to the total deposit. Files not meeting these criteria will be rejected. Once a file is verified, each payment in the file should be examined for matches to existing invoices. If no match is found, the data should be examined against previously matched data. Matched data will flow into a completed queue to be broken into employee-level charges by another piece of software. Unmatched data will flow into a working queue that will require user intervention. User matches will be stored for future matching. Finally, statistics will be created for the input for reporting purposes. Figure 19-1 is a diagram of the business solution.

image from book
Figure 19-1



Professional SQL Server 2005 Integration Services
Wireless Java : Developing with Java 2, Micro Edition
ISBN: 189311550X
EAN: 2147483647
Year: 2006
Pages: 182

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net