Oracle R12 Receivables Auto Lockbox (Inbound) Process Details

Using Auto Lockbox

Auto Lockbox (or Lockbox) is a service that commercial banks offer corporate customers to enable them to outsource their accounts receivable payment processing. A lockbox operation can process millions of transactions a month.

Auto Lockbox eliminates manual data entry by automatically processing receipts that are sent directly to your bank. You specify how you want this information transmitted and Receivables ensures that the data is valid before creating QuickCash receipt batches. You can automatically identify the customer who remitted the receipt and optionally use Auto Cash rules to determine how to apply the receipts to your customer's outstanding debit items.

You can also use Auto Lockbox for historical data conversion. For example, you can use Auto Lockbox to transfer receipts from your previous accounting system into Receivables. Auto Lockbox ensures that the receipts are accurate and valid before transferring them into Receivables. Auto Lockbox is a three step process:

1.      Submit Import:

During this step, Lockbox reads and formats the data from your bank file into Auto Lockbox tables using an SQL *Loader script.

2.      Submit Validation:

The validation program checks data in the Auto Lockbox tables for compatibility with Receivables. Once validated, the data is transferred into QuickCash tables. At this point, you can optionally query your receipts in the QuickCash window and change how they will be applied before submitting the final step, Post QuickCash.

3.      Submit Post QuickCash:

This step applies the receipts and updates your customer's balances. See: Post QuickCash. 

These steps can be submitted individually or at the same time from the submit Lockbox Processing window. After you run Post QuickCash, Receivables treats the receipts like any other receipts; you can reverse and reapply them and apply any unapplied, unidentified, or on-account amounts.

Auto Lockbox Validation

Receivables validates the data you receive from the bank to ensure that the entire file was received, there are no duplicate receipts within a batch, and that customers and invoices are valid.

Auto Lockbox also validates all of your data for compatibility with Receivables. Auto Lockbox validates your data by ensuring that the columns in AR_PAYMENTS_INTERFACE reference the appropriate values and columns in Receivables.

Transmission Level Validation:

Auto Lockbox validates your lockbox transmission to ensure that transmission information corresponds to your transmission format. The following attributes are validated:

1.      Transmission format contains receipt records

2.      Lockbox number is part of the transmission format or you specify it when you submit Auto Lockbox from the Submit Lockbox window

3.      GL date is in an open accounting period

4.      Total transmission record count and amount that you supply must match the actual receipt count and amount that is determined by Auto Lockbox

5.      Origination number is valid if it is provided

Lockbox Level Validation:

Auto Lockbox validates your lockbox records to ensure that lockbox information corresponds to your transmission format. The following attributes are validated:

1.      Lockbox number is specified in either the Lockbox Header or the Lockbox Trailer, and is valid

2.      Lockbox batch count is correct if it is provided 

3.      Lockbox amount is correct if it is provided 

4.      Lockbox record count is correct if it is provided 

5.      Origination number is valid if it is provided

6.      No duplicate lockbox numbers

Batch Level Validation:

Auto Lockbox validates your batch records to ensure that batch information corresponds to your transmission format. The following attributes are validated:

Batch name exists on batch records

Batch name is unique within the transmission

Batch amount is correct

Batch record count is correct

Lockbox number exists on batch records if this number is part of the transmission format

Receipt Level Validation:

Auto Lockbox validates your receipt records to ensure that receipt information corresponds to your transmission format. The following attributes are validated:

Remittance amount is specified

Check number is specified

Item number is specified and is unique within a batch, a lockbox, or the transmission, depending on the transmission format

Lockbox number is specified (if this number is not part of the Lockbox Header or the Lockbox Trailer of the transmission format) and batches are not imported

Batch name is specified (if either Batch Headers or Batch Trailers are part of the transmission format)

Account number is specified (if Transit Routing Number is part of the transmission format)

Invoice1-8 are either valid or are left blank

Attention:

If you are using matching numbers and a receipt record indicates that multiple transactions will be paid by this receipt, Lockbox assumes that all of the transactions are the same type (e.g. invoices, sales orders, purchase orders, etc.). For example, if the first 2 transactions are invoices, Lockbox will successfully match them with this receipt. However, if the next transaction is not an invoice, Lockbox will either import the remaining receipt amount as unidentified or reject the entire receipt (depending your Lockbox definition).

Installment1-8 are either valid installment numbers or are left blank

Invoice, debit memo, credit memo, deposit, on-account credit, or chargeback number derived from the matching number does not belong to a guarantee or receipt

 Transaction number is entered where an application amount is specified

Sum of all of the Amount Applied columns for a receipt does not exceed the remittance amount

Customer number is valid (refer to Customer Validation below)

Customer number and MICR number both reference the same customer (if both are provided)

Receipt date is specified

Payment method is valid

Currency is valid (refer to Currency Validation below

Overflow Level Validation:

Auto Lockbox validates your overflow records to ensure that overflow information corresponds to your transmission format. The following attributes are validated:

Batch name is specified (if either Batch Headers or Batch Trailers are part of the transmission format)

Lockbox number is specified (if either the Batch Header or the Batch Trailer are not specified and the transmission format includes lockbox number)

Item number is specified and matches a receipt record

Overflow indicator is specified (unless it is the last overflow record)

Overflow sequence is specified

Invoice1-8 are valid invoice numbers (these numbers are optional, and can be left blank)

Attention:

If you are using matching numbers and a receipt record indicates that multiple transactions will be paid by this receipt, Lockbox assumes that all of the transactions are the same type (e.g. invoices, sales orders, purchase orders, etc.). For example, if the first 2 transactions are invoices, Lockbox will successfully match them with this receipt. However, if the next transaction is not an invoice, Lockbox will either import the remaining receipt amount as unidentified or reject the entire receipt (depending your Lockbox definition).

Installment1-8 are either valid installment numbers or are left blank

Transaction number derived is entered where an application amount is specified

Customer Validation:

Auto Lockbox can either validate your customer data based on the following attributes, or mark the receipt as ‘Unidentified' if no match is found:

Customer number is valid

MICR number is valid

Bill-To customer is from an Auto Associated invoice (if Auto Associate is enabled)

Currency Validation:

Receivables lets you process receipts in multiple currencies. If you pass the currency code, exchange rate type, and receipt date, Auto Lockbox will try to determine the exchange rate. If itis unable to determine the exchange rate, the receipt will fail validation. Receivables also supports cross currency deposits. This implies that receipts in your lockbox can be either in the same currency as that of the bank account, or in any other currency, provided the bank account is in your functional currency and its Multiple Currency Receipts field is set to Yes (Bank Accounts window, Receivables Options alternative region).

Running Auto Lockbox 

Run Auto Lockbox to submit your lockbox transmission processes and transfer payment information from your bank files into Receivables. Submit Auto Lockbox from the Submit Lockbox Processing window.

Use Auto Lockbox to import your invoice-related receipts. You must process non-invoice related receipts (such as investment income) through the Receipts window using a receipt type of 'Miscellaneous'. You can import, validate, and run Auto Lockbox all in one step, or perform the steps separately using the same window. For example, you can import data into Receivables and review it before validating it within Receivables. Upon examination and approval, you can submit the validation step and Receivables will automatically validate your data and create QuickCash receipt batches.

Caution:

When you receive your bank file, be sure to name the file and move it to the appropriate directory. You will need to specify the location of your bank file when you submit Auto Lockbox. If you receive daily files from your bank, be careful not to overwrite the files from the previous day.

Receivables uses SQL*Loader to load information from your bank files into Auto Lockbox tables. For SQL*Loader to load your bank file properly, each logical record that your bank sends to you must end with a carriage return; otherwise, SQL*Loader displays an error message when you initiate Auto Lockbox.

Attention:

If you are using the automatic receipts feature, Auto Lockbox ignores all transactions in this transmission that are selected for automatic receipt (i.e. transactions assigned to a payment method whose associated receipt class has Creation Method set to 'Automatic').

QuickCash lets you enter and apply receipts quickly by only requiring you to provide minimal information. QuickCash also provides an extra level of control for entering high volume receipts because it does not immediately affect your customer's account. When you enter receipts and applications in a QuickCash batch, Receivables stores them in an interim table. After reviewing a QuickCash batch for accuracy, you run Post QuickCash to update your customer's account balances.

QuickCash lets you apply your receipts to one or many transactions, use Auto Cash rules, place receipts on-account, or enter them as unidentified or unapplied. After your n Post QuickCash, Receivables treats the receipts like any other receipts; you can reverse and reapply them and apply any unapplied, unidentified, or on-account amounts. You must batch QuickCash receipts. For receipt batches you enter in the QuickCash window, Receivables does not update the status, applied, on account, unapplied, and unidentified fields until you save the batch. If you do not identify the customer for a receipt, Receivables automatically assigns the receipt a status of Unidentified.

Additional Information:

You cannot add miscellaneous receipts to a QuickCash batch

Post Quick Cash

When you enter receipts in the QuickCash window or import them using Auto Lockbox, Receivables stores them in interim tables. You can then use the QuickCash window to review each receipt and use the Applications window to ensure that the application information is correct. After you approve the receipts and their applications, run Post QuickCash to update your customer's account balances.

You can choose which QuickCash or Lockbox batches to review. For example, you may want to review only the receipts entered by your data entry clerks or the data files sent by your bank. The following diagram summarizes how Post QuickCash transfers receipts and applications from interim tables into Receivables.