Cash Receipts Automation

jversusj

I searched the forum and had not seen much discussion about this topic. I have been tasked with automating our cash receipts process as much as possible, using a flat file of data provided by our bank.

The flat file itself is an easy read via dataport. It is a fixed width file containing a finite number of fields.
Remitter Name (30 characters)
Document No (20 characters)
Document Amount (12 chars)
Check Amount (12 characters)
Check Number (20 characters)
Deposit date (8 chars MMDDYYYY)

The file can contain multiple records per check, so I thought it would make sense to import into a Payment Header and Payment Lines table to get the data in NAV in a structured format. My thought was to then run a routine per payment line to look up the document no. and define the bill-to customer no. If the document did not exist or was no longer open in the CLE, I would flag it and generate some sort of exception log so the AR staff could process it manually. My next thought was to run a routine to build Cash Receipt Journal entries off of the payment lines. I would only create the CRJ, the AR Staff would have to review the exceptions and address them in the batch prior to posting. Here is where I am getting lost.

I could have a single check paying multiple accounts. The payee could be consuming a credit from one account and applying it under another account, or the payee may not communicate the document no. to pay at all. I then begin to wonder if I will have more exceptions to handle than successes. Essentially, I am having major concerns trying to build the journal line and apply it to the proper documents all while balancing the deposit.

I should note that we are running NAV 3.7B code base.

Has anyone else done this? Can you please share your general approach (I am not looking for your code)?

Thanks in advance for any tips or advice.