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:
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
|
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.
|
|
→Navigation: Setup > Receipts > Lockboxes > Lockboxes > Bank Tab Define Lockboxes to use the Receivables Autolockbox program
|
|
|
|
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)
|
|
Define Lockbox – Match On Corresponding Date Choose whether to Match on Corresponding Date for transactions in this Lockbox transmission.
|
|
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:
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:
None is the default line level cash application option for new setups and migrated data.
|
|
Navigation: Setup > Receipts > Lockboxes > Transmission Formats Following are valid record types:
Batch Headers usually contain information such as batch number, deposit date, and lockbox number.
Batch Trailers usually contain information such as batch number, lockbox number, batch record count, and batch amount.
Lockbox Headers usually contain information such as destination account and origination number.
Lockbox Trailers usually contain information such as lockbox number, deposit date, lockbox amount, and lockbox record count. |
Receivable combines the overflow and payment records to create a logical record to submit payment applications.
Transmission Headers usually contain information such as destination account, origination number, deposit date, and deposit time.
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
|
Automatic Matching Rule Define the Automatic Matching Rule for this AutoCash Rule set.
|
|
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
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:
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 . |
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.
|
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.
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. |
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 |