Auto Lock Box - Basic Concepts and Implementation Steps

Auto Lock Box Implementation :

What is Lock Box :

=> Lockbox is a service offered by banks to companies in which the company receives payments from their customers by mail to a post office box and the bank picks up the payments from the customers and deposits it in the company’s bank account.

Lock Box - Simple Definition:

So , as per Lockbox functionality , basically the customer goes and deposit the money directly into your Bank account for the due invoices and the Banker provides a data file to you. You need to have Lock box set up to create a Transmission Format which matches the data file format in your Instance.

What is Auto Lock Box in Oracle :

=> Auto Lock box Is A Service That Commercial Banks Offer , where the bank processes Corporate Customers To Enable Them To Outsource Their Accounts Receivable Payment Processing.

=> Auto Lock box eliminates Manual Data Entry By Automatically Processing Receipts That Are Sent Directly To Your Bank. You Can Also Use Auto Lock box For Historical Data Conversion.

=>The Bank normally sends a Flat file (text file) to the company that gives all the details of the deposits made in the bank account.

=>The details captured in the flat file depend on the arrangement between the bank and the company. This flat file is referred to as the Lockbox file.

=>The company can then import this Lockbox file in their system to create receipts and apply these receipts to the open invoices.

=>Oracle Receivables provide a standard functionality to import the lockbox file to create the receipts.

Oracle AR Lockbox functionality Setup Overview:

1) Bank and Bank Accounts: You define your internal bank accounts in Accounts Receivables. This is the bank account where the customer payments are deposited.

2) Receipt Class: The Receipt Class determines the processing steps for the receipts and you assign Receipt Methods to your Receipt Class. The processing steps for any Receipt include confirmation, remittance and reconciliation.

3) Receipt Method: Receipt Method is assigned to a receipt class and it determines how to account for the receipts using the Receipt Class. For one Receipt Class, you can have more than one Receipt Methods. You associate bank accounts and the GL account combinations for Cash, Remittance, and Bank Charges etc with the Receipt Method.

4) Receipt Source: You define Receipt Batch Sources to provide default values for Receipt Class, Receipt Method and the Remittance Bank Account. Your Receipt Source also determines if the batch numbering system is manual or automatic.

5) Lockbox: Define a lockbox for your Lockbox service from each bank. The lockbox setup includes a Lockbox number (You get a Lockbox number from your bank).

6) Lockbox Transmission Formats: Oracle Receivables Auto Lockbox uses the Transmission format while importing the data from the lockbox file into Receivables. Transmission formats indicate how the data in the Lockbox file is organized.

7) Auto Cash Rule Sets: Auto Cash Rule Sets determines the sequence of Auto Cash Rules that Post Quick Cash program uses to apply the receipt amount to the customer account open items.

 8) Control file: A Control file is a sql loader file to load the lockbox file in Receivables payment interface table (ar_payments_interface_all). This file should have the ‘.ctl’ extension and should be placed in the $AR_TOP/bin directory. Input Data 1) Lockbox file: The only input data you need for the lockbox process is the Lockbox file (flat file). You receive this file from the bank. If you are using the Lockbox functionality to convert AR receipts from legacy system, you can generate this file yourself.

**************************************************************************

-- Loads Lockbox flat file in to oracle

/************************************************************************************************

--* arplbox.ctl script *

*************************************************************************************************

--Program : Manual AutoLockBox US

--Description : This is a Customized Control File for Auto Lockbox US.It Imports Receipt

-- data from the flat file in to ar_payments_interface_all table

--Notes : This Control file should be in Sync with Transmission format defined.

************************************************************************************************/

LOAD DATA

APPEND

-- Type 2 - Lockbox Header

INTO TABLE AR_PAYMENTS_INTERFACE

WHEN RECORD_TYPE = '2'

( STATUS CONSTANT 'AR_PLB_NEW_RECORD'

,RECORD_TYPE POSITION(01:01) CHAR

,DESTINATION_ACCOUNT POSITION(02:11) CHAR

,ORIGINATION POSITION(12:21) CHAR

,LOCKBOX_NUMBER POSITION(22:27) CHAR

)

-- Type 6 - Payment record

INTO TABLE AR_PAYMENTS_INTERFACE

WHEN RECORD_TYPE = '6'

( STATUS CONSTANT 'AR_PLB_NEW_RECORD'

,RECORD_TYPE POSITION(01:01) CHAR

,LOCKBOX_NUMBER POSITION(02:05) CHAR

,BATCH_NAME POSITION(06:08) CHAR

,ITEM_NUMBER POSITION(09:11) CHAR

,REMITTANCE_AMOUNT POSITION(12:21) DECIMAL external

,TRANSIT_ROUTING_NUMBER POSITION(22:30) CHAR

,ACCOUNT POSITION(31:44) CHAR

,CHECK_NUMBER POSITION(45:54) CHAR

,DEPOSIT_DATE POSITION(55:60) DATE 'YYMMDD' NULLIF DEPOSIT_DATE=BLANKS

)

-- Type 4 - Overflow

INTO TABLE AR_PAYMENTS_INTERFACE

WHEN RECORD_TYPE = '4'

( STATUS CONSTANT 'AR_PLB_NEW_RECORD'

,RECORD_TYPE POSITION(01:01) CHAR

,LOCKBOX_NUMBER POSITION(02:05) CHAR

,BATCH_NAME POSITION(06:08) CHAR

,ITEM_NUMBER POSITION(09:11) CHAR

,OVERFLOW_SEQUENCE POSITION(12:14) CHAR

,OVERFLOW_INDICATOR POSITION(15:15) CHAR

,INVOICE1 POSITION(16:30) CHAR

,CUSTOMER_NUMBER POSITION(34:41) CHAR

,AMOUNT_APPLIED1 POSITION(42:51) DECIMAL external

"DECODE(:AMOUNT_APPLIED1,0,NULL,:AMOUNT_APPLIED1)"

)

Sample Data and the Key Fields Explanation :

2US MainA/C1234567890L123

6L123ABC0010071935604                       L150      180420

4L123ABC0010019100405         003000026377193504

Lock Box Processing Steps:

           

Running Lockbox

Navigation: Interfaces > Lockbox

For Sample Run, AR Invoice with Invoice Number 100405 was created for Customer 2637 . (Refer to Data File )

1. Place the Data file in any suitable path in the Application Server.

Here, the sample file “Lockbox_File_Sample.csv”  has been placed in the /home/applmgr/ folder.

2. Place the Control File in the $AR_TOP/bin folder

3. Go to Interfaces-> Lock box and Submit the Lock Box Import as follows :

Once the Lockbox Import is submitted, the Concurrent Program Request ID is populated and the concurrent program “Process Lockboxes” is triggered.

Refer to the Output Report, the receipt has been created successfully.

After the Receipt L150 has been created successfully, the Receipt has been auto applied to the invoice 100405 we had created and the Balance Due for the Invoice is now zero.

Receipt History from the Front End :

Maintain Transmission Data:

In Case there are any Validation Errors, Use the Lockbox Transmission Data window to delete and edit transmission data imported into Receivables from your bank using Lockbox.

You can correct your lockbox data in this window for receipts that fail validation, then resubmit the validation step to import these receipts.

Use the Lockbox Execution report to help you determine which transmission records you need to correct to ensure that your validation processes succeed.

If you are updating information, be sure to update only those fields that have data corresponding to the transmission format used to submit the import process.