Receivable AutoLockbox for receipt import in Oracle Apps R12

What is auto lockbox in oracle applications?

Lockbox is a service provided by banks by which your company gets the customers payments directly to a lockbox interface tables and creates receipt for the payments deposited into your account. If you have an Auto Lockbox, the bank records the information that you request such as check number, check amount, numbers and amount for the invoices to be paid.
Oracle provides you with the tools to:
* Oracle interface tables for the data received from the bank
* Validate the data to see if it is accurate, complies with the controls provided
* Correct the data
* Apply the receipts to the customer’s open invoices.


A typical Lockbox transmission contains various different records, each with relevant data. Controls are provided at each level to ensure that the transmission was successful and to verify that the count and dollar amounts are consistent with what the bank indicated. These controls are at the transmission, Lockbox, batch and receipt levels. The records also contain information such as your bank account (by Lockbox) and the details for the receipts the bank received. The Lockbox may be used for checks, wires and any other receipts that you receive. You define what the data from the bank will look like and how you will use it.
Oracle Accounts Receivables Module provides the feature such that the customer can directly make the payment for their invoices in the Bank.The Bank would send a datafile in a  agreed transmission format which we we import in Receivables through AutoLockbox.After successful import the Receipts get created in the final stage.
This is a three step process
1) Validate the data file
2) Import the data file which created the Post Batch
3) Run the Post Batch  (ie Post Quick Cash) which would actually create the Receipts and applies against the Invoice on the Information provided in the data file.
Following are the main tables for auto lockbox

AR_PAYMENTS_INTERFACE_ALL
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RCPT_LINES_ALL
AR_CASH_RECEIPTS_ALL
AR_CASH_RECEIPT_HISTORY_ALL
AR_DISTRIBUTIONS_ALL
AR_RECEIVABLE_APPLICATIONS_ALL
AR_PAYMENT_SCHEDULES_ALL

Setup:

We  need to setup the following before running the Lockbox process:

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 AutoLockbox 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) AutoCash Rule Sets: AutoCash Rule Sets determines the sequence of AutoCash Rules that Post QuickCash 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.

AutoCash

AutoCash Rule Sets determines the sequence of AutoCash Rules that Post QuickCash program uses to apply the receipt amount to the customer account open items. If none of the AutoCash Rules apply, the remaining amount is placed as Unapplied or On-Account depending on the Remaining Remittance Amount option specified in your

AutoCash Rule Set.

You can provide more than one AutoCash Rule with a sequence number. Oracle Receivables will try to apply the AutoCash Rules in the order of the Sequence Number. The available AutoCash Rules are as follows:

• Clear the Account: Post QuickCash uses this rule only if your customer's account balance exactly matches the amount of the receipt. If the receipt amount does not exactly match this customer's account balance, Post QuickCash uses the next rule in the set.

• Apply to the Oldest Invoice First: This rule matches receipts to debit and credit items starting with the oldest item first. This rule uses the transaction due date when determining which transaction to apply to first.

• Clear Past Due Invoices: This rule is similar to the 'Clear the Account' rule because it applies the receipt to your customer's debit and credit items only if the total of these items exactly matches the amount of this receipt. However, this rule only applies the receipt to items that are currently past due.

• Clear Past Due Invoices Grouped by Payment Term: This rule is similar to the 'Clear Past Due Invoices' rule, but it first groups past due invoices by their payment term, and then uses the oldest transaction due date within the group as the group due date. When using this rule, Receivables can only apply the receipt if the receipt amount exactly matches the sum of your customer's credit memos and past due invoices.

• Match Payment with Invoice: This rule applies the receipt to a single invoice, debit memo, or chargeback that has a remaining amount due exactly equal to the receipt amount.

Submit Autolockbox process

We submit the Lockbox Process to create receipts and apply them as per the Lockbox data file. The Lockbox is a three step process. Step 1 is importing the data file into Oracle Receivables. Step 2 is validation of the data and Step 3 is the PostQuick Cash process. You can run all these three steps individually or at the same time.

Step 1: Import Process

• New Transmission: If you are importing a new file, check the New Transmission check box. If you are resubmitting an existing transmission, leave this check box unchecked.

• Transmission Name: Provide a transmission name to your Lockbox Submission. If it is not a new transmission, select the Transmission Name from the List of values.

• Submit Import: Check the Submit Import check box to import the lockbox file.

• Data File: Provide the entire path of the location where the Lockbox file is placed.

• Control File: Name of the control file. Do not suffix the file name with .ctl. The control file should be present in the $AR_TOP/bin directory

• Transmission Format: Select the required Transmission Format from the List of values.

Step 2: Validation Process

• Submit Validation: Check this check box if you want to submit the Validation process.

• Lockbox: Specify the lockbox number if Submit Validation is Yes and the lockbox number is not specified in your lockbox file. If your transmission format includes the Lockbox Number, Oracle Receivables skips this field.

• GL Date: Enter the date to post the receipt and batch records in this lockbox transmission to your general ledger in the GL Date field. If you defined your GL Date as 'Constant Date' in the Lockboxes window, you must enter a GL Date; if you specified a GL Date of 'Deposit Date' or 'Import Date', Receivables uses this as the GL date.

• Report Format: Specify All if you want a detail validation report of all records. Specify Rejects Only if you only want details of the records that failed validation

• Complete Batches Only: Check this option if you want Receivables to create receipts only if all the receipt records of the batch are valid. If this option is unchecked, receivables will create receipts for the valid records and the non valid records will be rejected.

• Allow Payment of Unrelated Invoices: Check this option if you want Receivables to apply Payments to unrelated Invoices (Invoices belonging to unrelated Customers)

• Post Partial Amount as unapplied: If this option is enabled, Lockbox process applies the Receipt to valid transactions and the remaining receipt amount that could not be applied because of invalid transaction numbers is considered as unapplied amount. This option is generally set to Yes.

• Reject Entire Receipt: If there are any invalid transaction numbers in the Lockbox file, the entire receipt is rejected. The receipts data remains in the ar_payments_interface_all table. You can update the invalid record using the Maintain Lockbox Transmission Data window.

Step 3: PostQuick Cash Process

• Submit PostQuick Cash: Check the Submit PostQuick Cash check box, if you want the Lockbox process to run PostQuick Cash process. When Receipts are created using AutoLockbox, Receivables stores the receipt information in interim tables. Running PostQuick Cash applies the receipt and updates your customer’s receivable balance.