My Auto Cash Processing
Lockbox is a service that the commercial bank offers its corporate customers to mail their payments for processing.
By Sunil Swain
Introduction
This article, explain the approach I have adopted to simplify this process by leveraging the Oracle JD Edwards EnterpriseOne application tools and functionality with minimum modification. This automation will not only accelerate incoming cash flow by importing your bank’s proprietary lockbox file, but also match the payments up against your customer’s invoices and then processing the payment application batch. In addition, it eliminates the time-consuming error prone manual efforts of entering payments. What had taken hours, now takes minutes.
The most difficult aspect of processing lockbox transactions is accurately interpreting the supplied data file and identifying the customer and documents to which the payments applies. With this automation using the Lockbox Workbench, where you are presented with a visual environment to view the “system generated” applications, along with those that require more manual attention, facilitates this process. Once all payments are accurately accounted for, you can create the RB batch then post to J D Edwards receivables. The Workbench is built in JDE with flexibility in mind, so changes can be easily made. Historical lockbox files are easily viewed.
“Custom LockBox Workbench is an easy-to-use application windows, where the user can manage to pull the lockbox data from bank, stage, validate, match and post with online exception handling and reporting that helps to improve “Days Sales Outstanding” (DSO)and reduce float, reduce cost associated W/handling, improve efficiency , cash management and forecasting that improves customer satisfaction”
LockBox Workbench facilitates to download, map, and stage the lockbox electronic payments, highlight possible reasons for exception, upload to Z-processor, apply receipt and finally execute a reconciliation report with General Ledger. In this sense, it’s a front-end tool that seamlessly integrates with JDE Accounts Receivable application.
Let us discuss the lockbox scenario; in most cases the Banks are using a very standard format called BAI format for the lockbox. You need to contact your bank to send you the electronic layout of the lockbox file. These are set of records, which constitute the lockbox file layout, usually in an ASCII format text file.
Using the specification from the bank, a mapping process is developed to convert the text file to staging table. You can directly load the data from the staging table to the Z-table or you can load the staged data using EDI (system 47) table. In this case we utilize the JDE batch process R47111 (R5647111), which loads the data from F47XXX to Z-processor (F03B13Z1). I have cloned UBE R47111 to R5647111 to capture some additional information for the client and incorporate some validations.
Once this has been successfully loaded to Z-processor, the data is ready to be processed into receipt tables. The JDE batch process R03B551 uses other batch processes, which use different algorithms for the remittance process. In our process we chose the algorithm that is most compatible with our remittance data received from customer and keeping our business scenario in view.
This custom process is designed to handle multiple lockboxes from multiple banks with multiple transactions like lockbox checks, credit card payments, wire transfers, ACHS.
Download the Electronic File
This process could be achieved either by downloading the electronic file (ASCII text File) from Bank’s FTP site or HTTPS: login to the Bank site to download the file and save it in proper location. You may use some naming convention as appropriate to differentiate the various lockboxes, if you have more than one Bank or lockboxes to process.
LockBox Workbench
This process could be achieved either by downloading the electronic file (ASCII text File) from Bank’s FTP site or HTTPS: login to the Bank site to download the file and save it in proper location. You may use some naming convention as appropriate to differentiate the various lockboxes, if you have more than one Bank or lockboxes to process.
This is the main driver to execute the subsequent steps required to process payments. The user enters the date and Lockbox number to be processed, then clicks on the Load Lockbox tab. The load lockbox tab is a batch process that reads the header record in the ASCII file from Transfer Server downloaded from the bank. During this stage it validates if it is a duplicate file or if the file has been processed before. If no duplication found, it writes a record with lockbox number, date, time and the lockbox amount (total amount received in that lockbox). The status column in control record gets updated after each stage of the lockbox process is successfully completed.
Following are the different status used:
L= Load the control record for each lockbox to be processed
P= Process the lockbox information to staging table
V= Validation/Exception reporting and load to 47 EDI tables complete
U= Upload to Z-processor (F03B13Z1) complete
A= Apply receipts complete
In addition to above information this control table also maintains batch number, EDI document number, batch type, which gets updated at a later stage discussed below.
LockBox Data Staging
In addition to above information this control table also maintains batch number, EDI document number, batch type, which gets updated at a later stage discussed below.
Transmission header (F5603191): this contains both source and destination of all data contained therein.
Header (F5603195): It has the required information that is common to all detail records it precedes for a given deposit date.
Detail record (F5603194): Contains remittance information (routing transit, account number, check amount, and check number). Each record comes with one invoice. If any payment contains more than one invoice, then that check will be transmitted through the detail overflow record. Each batch has its own unique batch number and each transaction within the batch has its own sequential unique item number.
Payment header (F5603196): This store the total check amount for the payment received.
Batch Total record (F5603197): This is the total of detail records in the batch. This record contains the table dollar amount and the total number of checks processed.
Trailer record: marks the end of transmission and includes the total number of records transmitted. There are six table created to populate the above record to staging table.
F5603191 – Lockbox Header Information
F5603194 – Lockbox Remittance Detail
F5603196 – Lockbox Payment Detail
F5603197 – Lockbox Batch Total
F5603195 – lockbox Batch Header
LockBox Exception and Load to EDI Table
R5603201 reads through the staging table and validate each and every remittance received with customer ledger (F03B11). It generates an exception report and updates four EDI table F47111, F47112, F47113 and F47114. This batch process checks each remittance received through lockbox and validates Customer number, Open Amount, invoice number, sales order number and RMA with customer ledger. This report prints the possible error encountered during the validation process and cause of this error. It prints a control total at the end with total number of payment received and the batch record count for the lockbox. This control total must match the total amount captured in control table in 1st stage.
Some Possible Exceptions
Wrong Invoice Number
The remittance file received from the bank may have a wrong invoice number against a customer. The invoice number might exist in the system but the payer might be different than the one transmitted by the bank.
In this case the system will check if the invoice number exists against that payer, failing which it will create RU Doc type and will assign it to the miscellaneous account, which will be linked to General Ledger.
Also wrong invoice number such as “9999999” against a Payer will create RU Doc Type and will also show against the miscellaneous account.
RMA Documents
RM Document Types are returns from customer, which are negative amounts. These invoices have to be fixed using Browse/Fix tab.
Invoice Amount as Zero
At times the remittance received from the bank may have an invoice number against a payer with zero amounts. These invoices will remain unapplied in the system.
No Document Type
The remittance file might have invoice numbers without document type. In such cases the system will take the invoice number and search if such an invoice number exists for RM, RI and FO document type. If it doesn’t find the document type for that invoice number, by default it takes RI document type.
Amount received not matching Open Amount
In some cases the remittance amount received from the bank is either overpaid against that invoice or underpaid for that invoice. The validation report will show “Amount Overpaid” or “Amount Underpaid”.
Over Payment and Under Payment
If it is overpaid, the system will apply to the open amount and the rest will be unapplied.
If it is underpaid, the system will partially pay the invoice and for the rest it will create a chargeback.
Z-File To Load
The batch process (R47111) is designed to load data into the Electronic Receipts Input table (F03B13Z1) from (F47111, F47112, F47113 and F47114) automatically through custom programs working with data received from Bank. We cloned R47111 batch process and customized (R5647111) it as per our business need. The customization is done to incorporate multiple address book number in F03B14, if received from same customer with multiple remit-to. This writes 9B batch to F0011. Another customization was to update the status flag after the batch process is over.
Batch Review and Fix
This is a form inter-connect call P0011 to review the 9B batch. A simple and straightforward approach to review this batch is to go over applied (status=”A”) and unapplied (status=”U”). If any remittance is in status “U”, then it is a must to review. Why the transaction is in unapplied status? It could be due to any reason explained in section “possible exceptions”. The user needs to fix these exceptions here to generate a clean “RB” batch. In addition to reviewing “U” records, you must review status “A” records to make sure the records created are correct and good to proceed to next step. In case you missed any transaction then you can fix the same while reviewing “RB” batch.
Apply receipt and Create "RB" Batch
You may encounter instances where payment items did not apply at all due to wrong setup. In those instances, you need to return to the Electronic Receipts Input table (F03B13Z1) and make the necessary corrections to execute the update receipts register successfully. Be aware that you will have to look a bit harder for this situation since the system doesn’t share a lot of information.
Unprocessed Items that Were Applied
You should be aware that you would never achieve a 100% hit rate on electronic payments. An exceptionally good rate would be in the 80-85% range. What this means is that you will have daily work to do to investigate and clear unprocessed items from the payment application.
There may be instances where you don’t receive the electronic transmission, or part of it is missing and unrecoverable. In these instances you can enter data manually into the Electronic Receipts Input table once you have identified and verified the missing data.
After processing automatic receipts, you should review those receipts for items the system was unable to apply. Bear in mind this only occurs if the system cannot make a match even after working every algorithm in your execution list. Also bear in mind that the system will apply the payment to a customer account unless it can’t find a customer, in which case it will apply the payment to Miscellaneous Account.
The apply receipt driver UBE (R56B551) automatically triggers one more jobs of its own. In addition to triggering the apply receipt batch job, this updates the status flag to “A” and batch type as “RB” in control table.
The batch job R03B551 calls R03B50. Based on the algorithm method defined in Execution List, it calls different batch processes to apply the remittances to the open receipts.
- R03B50A
- R03B50D
- R03B50E
- R03B50F
- R03B50B
Post to General Ledger
The JDE batch program R09801 is used to post the RB batch to General Ledger. This can be executed manually by passing the batch number and batch type or a separate version can be setup if you want to post through scheduler.
Setup Consideration
There are three key steps taken into consideration while setting up the LockBox Process with Bank of America.
- Transaction Setup
- Master Setup
- Algorithm Setup
Transaction Setup
This includes the LockBox code for Bank, types of transactions, and Electronic file path and transactions versions while processing different LockBox. The UDC is created for better flexibility and control while processing the lockbox transactions.
UDC Setup
56/03 – LockBox
One record for each lockbox entered in the UDC. Also this UDC indicates the version in special handling to be called while processing the lockbox ASCII file to staging table. These versions are called by R5603200 and R5603201 for respective lockboxes. There are different versions set up for different lockboxes to incorporate separate Bank Account.
76/BC Bank Code
If your organization has multiple banks with multiple lockboxes then add one record for each bank in the UDC (Table2). This bank code used in the naming convention of ASCII text file received from bank. This technique is used if lockbox is received from multiple banks. This UDC is linked to processing option of P5603505.
This UDC is designed to identify different lockbox transaction type such as lockbox checks, wire, credit card, ACHS. We are only considering lockbox checks in this process. However other transaction types can be processed using the same application.
Master Setup
The master setup includes the A/R constant and Customer and customer Bank Account.
Auto Receipt Flag in A/R Constant
Switch on the auto receipt flag in A/R Constant for the company.
Auto Receipt Flag in Customer Master
Switch on the Auto Flag
Blank out payment Instrument field from C to blank
Bank Account Information
The third setup is the Bank Account Information in the Bank Transit Number Master table for each customer.
Click on Bank Account on the Exit Bar on the display above:
Note: You may setup the bank account, if you want to validate the routing and bank account of customer.
If you want to use auto cash, you must have the auto cash flag set in the Customer Master record AND the customer’s bank identified FOR EVERY CUSTOMER YOU WANT TO DO AUTO CASH FOR. In most instances, if you have a lockbox remittance address, you will most likely be using auto cash. That means virtually all your customer records will have to have these two items configured.
Algorithm Setup
The base method includes:
- Known invoice match with Amount
- Known invoice match without Amount
- Balance forward match
- Invoice selection match
- Combination invoice match
The base method defines different ways for the system to search for invoice matches during a cash application run.
Based on our data from the bank and the Business requirement we have used 2 algorithms:
- Known Invoice Match with Amount
- Known Invoice Match without Amount
Known Invoice Match with Amount
This algorithm will apply receipts to invoices where the invoices and invoice amounts to be paid are identified to the system. This obviously means the invoice number and payment amount must come in from the lockbox data to be included in the Electronic Receipts Input Table.
R03B50D | M71V0001 – Match All Invoices
Note: It is always good practice to create your own version. The version M710001 copied from JDE original version R03B50D|XJDE0001
Let’s look at the processing options for this version:
These options mean that any payment difference from what we have told the system to expect; i.e., the invoice amount does not equal the amount to apply in the Electronic Receipts file, will be handled as a Chargeback for underpayments. Here we are telling the system to handle overpayments as Unapplied Cash. The processing options in the Apply Receipts tab are used when the receipt amount does not equal the total amount to apply in the Electronic Receipts file.
In the Amount Match tab, it is best to leave these options blank. You should not have duplicate invoice numbers in your system, but if you do, leaving these options blank will ensure that an incorrect match will not occur. In the Match Priority tab, you indicate the order in which the matching process locates a document number.
Known Invoice Match without Amount:
This algorithm is used to apply receipts to known invoices but the amount to be applied to each invoice is not known. As before, you must have the invoice number on your incoming lockbox data to use this algorithm.
Let’s look at the processing options for this version:
R03B50E | M71V0001 – Match All Invoices without Amount
Since we are not attempting to apply cash based on invoice number AND amount, the system has to be told how to apply over- and underpayments, tolerances for minor write-offs, and the priority of the matching process.
There are three key steps taken into consideration while setting up the LockBox Process with Bank of America.
- Transaction Setup
- Master Setup
- Algorithm Setup
Transaction Setup
This includes the LockBox code for Bank, types of transactions, and Electronic file path and transactions versions while processing different LockBox.
- Bank Code for LockBox Driver
- Transaction Type
- LockBox Transaction Versions
P5603209 | M71V001 – LockBox Workbench
Defining Execution Lists
Execution lists determine the sequence in which the system executes the algorithms for a customer. One execution list can be applied to many customers, or you can assign a specific list to a specific customer that requires special payment considerations. Let’s take a look at the execution lists that are in our database. Click on Define Execution Lists on Menu and click FIND:
P03B50 – Work With Algorithm Execution Lists
The execution list 56ASTD created to process the LockBox data for most customers. Remember that the system will execute the searches in the sequence that is specified in the leftmost column. Now let’s look at the 56ASTD list:
NOTE: The success of your auto cash application will depend on the amount of incoming information that is contained in your Electronic Receipts Input Table (F03B13Z1). This must always be kept in mind when we configure the algorithms and execution lists and when you communicate with your lockbox bank.
Assigning Unique Execution Lists to Specific Customers
You may encounter a situation where the default algorithms will not fit a specific customer. In this instance, you will have to build custom algorithm(s) and assign them to a custom execution list that you then assign to a specific customer.
Note: If the default execution list is to be used, leave the Auto Receipts Execution List blank in the Customer Master.
Let’s look at the customer master first tab again to see where this information goes:
On the right hand side from the Auto Receipt check flag you see a field for a custom execution list.
How the System Determines which Execution List to Use
- The execution list that is populated in the Receipts Header table F03B13. If no list is found,
- The execution list specified in the Customer Master for that customer. If no list is found,
- The execution list specified in the processing options of the Apply Receipts to Invoices program.
NOTE: You must ALWAYS specify the default execution list in the Apply Receipts to Invoices program to avoid unpredictable results.
In order to include previously unmatched receipts in the current receipt application, enter a “1” in processing option #3.
Update the Receipts Register to the F03B13 and F03B14 Tables
The “Update Receipt Header” process creates records in receipt header and detail file based on the open invoice amount and the corresponding remittance received from the customer through lockbox. At same time it closes the batch in Z-table (F03B13Z1) and updates the status to “D” in 9B batch in batch control table (F0011).
Apply the Receipts Batch Process Setup
After you run the program in final mode, you can process Batch Cash Receipts to apply the transactions against open accounts receivable. If errors exist, the system will then flag records as Unprocessed Items for you to edit.
Purge Electronic Receipts from the Receipts Input Table
This is a housekeeping activity, which the user needs to run periodically to remove the old data to avoid database growth and better performance.
This purge process deletes the record from Electronic Data Interchange (EDI) table for those records already applied and posted to Accounts Receivable.
This will be controlled using the processing option; if the processing option does not have the version then the purging process will not take place. Enter the version to purge the records form EDI tables time to time.
House Keeping
You may purge your processed lockbox records from the EDI and Z-file either daily or periodically. If you want to do a daily purge, create a scheduler version and include to your schedule after the posting job completes, if you are doing your posting through nightly scheduler. Another option is you can perform this activity periodically- weekly once or monthly once.
R47118 will purge all the records from the F47111, F47112, F47113 and F47114 table
R03B0041A will purge the records from Z-table F03B13Z1
Conclusion
This technique will extend the flexibility of lockbox remittance processing in Accounts Receivable. Also there are areas in this process, which has room for improvements. This process can handle multiple remittance types from different banks with little modification and setup. This custom process I have designed for my clients to implement in JDE EnterpriseOne 8.11 and 9.2. However, you may need to tweak for your implementation.