Lockbox Basics

Auto Lockbox in Oracle Receivables

Introduction

AutoLockbox is a service that commercial banks offer corporate customers to enable them to outsource their accounts receivable payment processing. This feature eliminates the need for manual data entry by automatically processing receipts that are sent directly to your bank.

You can also use AutoLockbox for historical data conversion to transfer receipts from your previous accounting system into Oracle Receivables.

AutoLockbox is a three-step process:

1. Import: Reads and formats the data from your bank file and stores it into interface table AR_PAYMENTS_INTERFACE_ALL using a SQL *Loader script.

2. Validation: Checks data in AR_PAYMENTS_INTERFACE_ALL table for compatibility with Receivables. Once validated, the data is transferred into QuickCash tables (AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL) . After this process completes, you can query your receipts in the QuickCash window and change how they will be applied before submitting the final step, Post QuickCash.

3. Post QuickCash: Applies the receipts and updates your customer’s balances.

These steps can be submitted one at a time, or all at the same time from the submit Lockbox Processing window.

Responsibility: Receivables Manager

Navigation: Interfaces > Lockbox

Dataflow

Prerequisite Setups

• Define Bank and Bank Branches
• Define Receipt Class & Source
• Define Lockbox
• Define Transmission Format

1. Define Bank & Bank Branches

Responsibility: Cash Management Manager

Navigation: Setup > Bank > Banks

Navigation: Setup > Banks > Bank Accounts

     B.1  Define Receipt Class

              Responsibility: Receivables Manager  Navigation: Setup > Receipts > Receipt Classes

Define Receipt classes to determine the required processing steps for receipts to which you assign receipt methods with this class.

Assign Bank Account To Receipt Method

Receivables uses receipt methods to account for the receipt entries. One can assign multiple banks to each receipt method, but only one bank account can be primary account for each currency.

        B.2  Define Receipt Source

        Navigation: Setup > Receipts > Receipt Sources

Define receipt batch sources and assign the receipt class, receipt method, and remittance bank account fields to this source.

• Receipt batch source type should be Manual.
• Receipt batch sources can use either automatic or manual batch numbering. (Should be Automatic Batch numbering if to be used for Lockbox process).

3. Define Lockbox – Bank Tab

→Navigation: Setup > Receipts > Lockboxes > Lockboxes > Bank Tab

Define Lockboxes to use the Receivables Autolockbox program

• Select an operating unit.
• Enter the lockbox Number provided by your bank.
• Enter the receipt Batch Source for this lockbox. You must enter a batch source that uses automatic numbering. Receivable enters the bank name and account, address, contact person, and accounting flexfield information associated with this batch source.
• Enter the Bank Origination Number provided by your bank. This number uniquely identifies the bank branch that sends you lockbox information.

Define Lockbox – Receipts Tab

Define Lockbox – GL Date Source

Define Lockbox – Match Receipts By

Choose a Match Receipts By method. (If Autoassociate is set to Yes)

• Transaction Number: Match receipts with transaction numbers.
• Balance Forward Billing Number: Match receipts with balance forward billing numbers. To use this method, the customer must be enabled for balance forward billing. Lockbox uses the balance forward billing number to identify the customer. Post QuickCash then uses this customer’s AutoCash Rule Set to determine how to apply the receipt to each invoice.
• Sales Order: Lockbox uses this number to determine the corresponding invoice number.
• Purchase Order: Lockbox uses this number to determine the corresponding invoice number.
• Hook: Match receipts to any other type of matching number that is passed with this transmission. This is a custom matching method that you define. Lockbox uses this number to determine the corresponding invoice number.

Define Lockbox – Match On Corresponding Date

Choose whether to Match on Corresponding Date for transactions in this Lockbox transmission.

• Always: Always verify that the date for the transaction or other matched item is the same as the date specified in this transmission.
• Duplicates Only: Only verify that the matching date and the specified date are the same if duplicate matching numbers were found and Lockbox needs to determine which is correct.
• Never: Ignore the specified date. This is the default value.

Define Lockbox – Transactions Tab

Choose how Lockbox will handle Invalid Transaction Number: If the receipt record is associated with multiple invoices, but one of the invoices is invalid. Depending on how you set this option, Lockbox will:

1. Post Partial Amount as Unapplied: Apply the receipt to the valid transactions, then import the remaining receipt amount with a status of Unapplied.
2. Reject Entire Receipt: Do not import the receipt (it will remain in the AR_PAYMENTS_INTERFACE table).

You need to edit the invalid record(s) in the Lockbox Transmission Data window, and then resubmit the Validation step for the receipt before Lockbox can import it into Receivables interim tables.

Select the appropriate line level cash application option:

1. None: Receivables does not perform line level cash application for the Lockbox run.

None is the default line level cash application option for new setups and migrated data.

2. Oracle Lease Management: Receivables calls Oracle Lease Management to resolve the matching numbers and populate the invoice, invoice lines, and actual amounts to be applied to the invoice lines.
3. Custom: Receivables calls a seeded custom program to resolve the matching numbers and populate the invoice, invoice lines, and the actual amounts to be applied to the invoice lines.

4. Transmission Format – Valid Record Types

Navigation: Setup > Receipts > Lockboxes > Transmission Formats

Following are valid record types:

1. Batch Header: A Batch Header marks the beginning of a specific batch.

Batch Headers usually contain information such as batch number, deposit date, and lockbox number.

2. Batch Trailer: A Batch Trailer marks the end of a specific batch.

Batch Trailers usually contain information such as batch number, lockbox number, batch record count, and batch amount.

3. Lockbox Header: A Lockbox Header marks the beginning of a specific lockbox.

Lockbox Headers usually contain information such as destination account and origination number.

4. Lockbox Trailer: A Lockbox Trailer marks the end of a specific lockbox.

Lockbox Trailers usually contain information such as lockbox number, deposit date, lockbox amount, and lockbox record count.

5. Overflow Receipt: An Overflow Payment usually contains invoice information for a specific payment such as batch number, item number, sequence number, overflow indicator, invoice number, debit memo number, or chargeback number, and debit item amounts.

Receivable combines the overflow and payment records to create a logical record to submit payment applications.

6. Receipt: A Payment usually contains information such as MICR number, batch number, item number, check number, and remittance amount.

7. Service Header: Service Header records contain general information about your transmission.

8. Transmission Header: A Transmission Header marks the beginning of a specific data file.

Transmission Headers usually contain information such as destination account, origination number, deposit date, and deposit time.

9. Transmission Trailer: A Transmission Trailer marks the end of a specific data file.

Transmission Trailers usually contain information such as total record count.

Transmission Fields

Define AutoCash Rule Sets

Navigation: Setup > Receipts > AutoCash Rule Sets

Define AutoCash Rule Sets to determine the sequence of rules that Post QuickCash uses to update Customers account balances.

Open Balance Calculation

1. Enter the type of Discount you want to automatically give to your customer for this AutoCash Rule Set. Choose one of the following Discount options:

• Earned Only: Your customer can take earned discounts according to the receipt terms of sale. You negotiate earned discount percentages when you define specific receipt terms. You can enter this option if Allow Unearned Discounts is set to Yes in the System Options window. In this case, Receivables only allows earned discounts for this AutoCash Rule Set.
• Earned and Unearned: Your customer can take both earned and unearned discounts. An unearned discount is one taken after the discount period passes.
• You cannot choose this option if the system option Unearned Discounts is set to No.
• None: Your customer cannot take discounts (this is the default).

2. Check the Items in Dispute check box if you want to include transactions in dispute when calculating your customer’s open balance.
3. Check the Finance Charges if you wish to include late charges when calculating your customer’s open balance.

Automatic Matching Rule

Define the Automatic Matching Rule for this AutoCash Rule set.

1. If this rule set will include the Apply to the Oldest Invoice First rule, choose how you want to apply any Remaining Remittance Amount. Receivable uses this value to determine how to enter the remaining amount of the receipt if none of the AutoCash Rules within this rule set apply.

• Choose ‘Unapplied’ to mark remaining receipt amounts as Unapplied.
• Choose ‘On-Account’ to place remaining receipt amounts On-Account.

2. To automatically apply partial receipts when using the Apply to the Oldest Invoice First rule, check the Apply Partial Receipts check box.

A partial receipt is one in which the receipt minus the applicable discount does not close the debit item to which this receipt is applied.

The applicable discount that Receivables uses for this rule depends upon the value you entered in the Discounts field for this AutoCash Rule Set. If you exclude late charges (by setting Finance Charges to No) and the amount of your receipt is equal to the amount of the debit item to which you are applying this receipt minus the late charges, Receivables defines this receipt as a partial receipt.  In this case, Receivables does not close the debit item because the late charges for this debit item are still outstanding.

If Apply Partial Receipts is set to No, this AutoCash Rule Set will not apply partial receipts and will either mark the remaining receipt amount ‘Unapplied’ or place it on-account, depending on the value you entered in the Remaining Remittance Amount field.

AutoCash Rules

1. Enter a Sequence number to specify the order of each rule in this AutoCash Rule Set (optional). Receivables uses the rule assigned to sequence 1, then sequence 2, and so on when applying receipts using this AutoCash Rule Set.

2. Enter one or more AutoCash Rules for this AutoCash rule set. Choose from the following AutoCash rules:

• 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. This rule uses the values you specified for this AutoCash Rule Set’s open balance calculation to determine your customer’s oldest outstanding debit item. Post QuickCash uses the next rule in the set if any of the following are true:

• all of your debit and credit items are closed.
• the entire receipt amount is applied.
• it encounters a partial receipt application and Allow Partial Receipts is set to No for this AutoCash Rule Set.
• the next oldest debit item includes late charges and Finance Charges is set to No for this AutoCash Rule Set

This rule marks any remaining receipt amount ‘Unapplied’ or places it on-account, depending on the value you entered in the Remaining Remittance Amount field for this AutoCash Rule set .

• 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. This rule calculates your customer’s account balance by using the values you specified for this AutoCash Rule Set’s open balance calculation and the number of Discount Grace Days in this customer’s profile class. This rule also includes all of this customer’s debit and credit items when calculating their account balance. This rule ignores the value of the Apply Partial Receipts option.

This AutoCash Rule uses the following equation to calculate the open balance for each debit item:

Open Balance = Original Balance + Late Charges – Discount

Receivable then adds the balance for each debit item to determine the customer’s total account balance. The ‘Clear the Account’ rule uses this equation for each invoice, chargeback, debit memo, credit memo, and application of an Unapplied or On-Account receipt to a debit item.

• 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.

A debit item is considered past due if its due date is earlier than the receipt deposit date. This rule considers credit items (i.e. any pre-existing, unapplied receipt or credit memo) to be past due if the deposit date of the receipt is either the same as or later than the deposit date of this pre-existing receipt or credit memo. In this case, this rule uses a pre-existing receipt or credit memo before the current receipt for your AutoCash receipt applications.

If this AutoCash Rule Set’s open balance calculation does not include late charges or disputed items, and this customer has past due items that are in dispute or items with balances that include late charges, this rule will not close these items. This rule ignores the value of the Apply Partial Receipts option.

• 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.

A debit item is considered past due if the invoice due date is earlier than the deposit date of the receipt you are applying. For credit memos, Receivables uses the credit memo date to determine whether to include these amounts in the customer’s account balance.

Credit memos do not have payment terms, so they are included in each group.

• 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. This rule uses the values that you enter for this AutoCash Rule Set’s open balance calculation to determine the remaining amount due of this customer’s debit items. For example, if Finance Charges is No for this rule set and the amount of this receipt is equal to the amount due for a debit item minus its late charges, this rule applies the receipt to that debit item.

If this rule cannot find a debit item that matches the receipt amount, Post QuickCash looks at the next rule in the set. This rule ignores the value of the Apply Partial Receipts option.

Running Lockbox Process

   Invoice

Running Lockbox

Responsibility: Receivables Manager      Navigation: Interfaces > Lockbox

Maintain Transmission Data

Responsibility: Receivables Manager     Navigation: Receipt > Lockbox > Maintain Transmission Data

Select Receipt Button

Select Applications Button

Submit Validation

Maintain Batches

Select Receipts Button

Submit PostQuickCash

Receipt Created

Application Details

Invoice

AutoLockbox Validation – In Detail

AutoLockbox Validation Process

      •Validates data in the interface table

      •Ensures data compatible with Receivables

How does it validate?

     •Transmission Format is setup in Receivables

     •Matches with data file format from the bank

AutoLockbox Validation – Header/Trailer

•Transmission format contains receipt records

•Origination number is valid (if provided)

•Record counts & amounts correct for

–Lockbox

–Batch

–Transmission

AutoLockbox Validation – Receipt/Payment Level

•Customer number is valid

•Customer & MICR (Magnetic Ink Character Recognition) numbers reference the same customer

•Check number & remittance amount provided

•Item number specified – uniquely identifies receipt record

•No duplicate receipts within a batch

•General Ledger (GL) date is in an open or future accounting period

AutoLockbox Validation – Payment/Overflow Payment Level

•Invoice information is valid

•Where application amount specified, transaction number entered

•Sum of Amount Applied columns for receipt does not exceed remittance amount

•Overflow sequence is specified

•Overflow indicator is specified

Customer Identification For Lockbox Receipts

If Customer is identified for receipt in lockbox transmission then receipt is created for that customer otherwise receipt is created as unidentified.

MICR #

If customer is not provided then lockbox validation process will look for customer MICR# in the lockbox transmission.

If MICR# is provided and it is existing MICR# then customer is identified for creating receipt otherwise Autoassociate is being used for further processing.

AutoAssociate

If AutoAssociate is setup in Lockbox Transaction Screen setup and if all transaction for a payment record (receipt) belongs to same customer then customer is identified and receipt is created otherwise receipt is imported as Unidentified.

Lockbox Receipt Application – Using Matching Numbers

•AutoAssociate identifies customers using matching numbers that can be:

   •Transaction Number

   •Sales Order

   •Purchase Order

   •Balance Forward Billing Number

   •Custom Defined Number

•Matching Numbers are passed in the column ar_payments_interface_all.Invoice(n)

Here Invoice 1 to 8 could be Transaction Number, Sales Order, Purchase Order or Balance Forward Billing Number, means same field is used for all matching numbers

Lockbox Receipt Application – Using Billing Location

When using Billing Location to identify customers for receipts:

    •Include Billing Location in Transmission Format

    •Set Require Billing Location For Receipts System Option to Yes

    •Set Require Billing Location Lockbox Setup Option to Yes

Define System Options – Require Billing Location

Navigation: Setup > System > System Options

Define Lockboxes – Require Billing Location

Navigation: Setup > Receipts > Lockboxes > Lockboxes

Applying Receipts Via Lockbox

Transmission includes customer and invoice information:

Applying Receipts Via Lockbox – Using Matching Numbers

 • Set AutoAssociate to Yes

 • Specify Match Receipts By

   – Determines which type of number to search for during Validation

   – Identifies the customer using the matched transaction

   – Applies the receipt during Post QuickCash

Applying Receipts Via Lockbox – Match Receipts By Options

Applying Receipts Via Lockbox – Match On Corresponding Date

•Check transaction date before matching receipts with transactions

•Options are:

    –Always – Always check for transaction date

    –Duplicates Only – Check for transaction date only when there are duplicates

    –Never – Never check for transaction date

Applying Receipts Via Lockbox – Using AutoCash Rules

•Post QuickCash uses AutoCash Rules to apply receipts that could not be applied using Matching Rules

•To use AutoCash Rules:

     –Include MICR or customer number in transmission

     –Do not include matching numbers in transmission

•If included, Post QuickCash will apply receipt to matching transaction

    –Specify an AutoCash Rule set for Customer’s Profile

•If not set, Receivables uses the AutoCash Rule set from System Options

Set AutoCash Rule Set – Customer Account Profile

Navigation > Customers > Search

AutoCash Rule Set – System Options

Navigation > Setup > System > System Options

AutoLockbox Validation Process – Related Setups

•Allow Payment of Unrelated Transactions

   –Determines if Lockbox applies receipts to invoices of unrelated customers

•Invalid Transaction Number Handling

   –Determines how Lockbox handles identified receipts that cannot be applied to transactions

•Post Partial Amount as Unapplied

•Reject Entire Receipt

Lockbox Setup – Invalid Transaction Number Handling

References:

Oracle Support Doc ID 1374423.1

Oracle Advisor Wedcast “Lockbox Basics: Setup, Data Model & Troubleshooting” By Vijaya Meduri

Oracle Advisor Wedcast “AutoLockbox Validation: Case Studies For Customer Identification & Receipt Application” By Phanilatha Tota