11g Release 1 (11.1.3)
Part Number E20375-03
Contents
Previous
Next
|
Oracle® Fusion
Applications
Financials Implementation Guide 11g Release 1 (11.1.3) Part Number E20375-03 |
Contents |
Previous |
Next |
This chapter contains the following:
Define Receipt Classes and Methods
Define Transmission Formats for Lockbox
Define Application Exception Rule Sets
Define Customer Paying Relationship Assignments
When you apply a payment or credit memo to a transaction, the application rule set determines how Oracle Fusion Receivables reduces the balance due on the line, tax, freight, and late charges amounts on a transaction.
Receivables uses the application rule set assigned to the transaction type to process payment applications. If no application rule set is assigned, then Receivables uses the system options application rule set.
You can arrange the order of the line types and application rules in an application rule set according to your needs. Each line type must appear in an application rule set, and appear only once. The Overapplication rule is always last in the sequence.
The Line First - Tax After rule first applies the payment to the open line amount, and then applies the remaining amount to the associated tax.
If the payment is greater than the sum of the line and tax, Receivables attempts to close each open item by applying the remaining amount in the following order, stopping when the payment has been fully applied:
Freight
Late charges
Any remaining receipt amount is applied using the Overapplication rule.
The Line and Tax Prorate rule applies a proportionate amount of the payment to the open line and tax amount for each line.
If the payment is greater than the sum of the open line and tax amounts, Receivables attempts to close each open item by applying the remaining amount in the following order, stopping when the payment has been fully applied:
Freight
Late charges
Any remaining receipt amount is applied using the Overapplication rule.
The Prorate All rule applies a proportionate amount of the payment to each open amount associated with a debit item (for example, any line, tax, freight, and late charge amounts for this item).
Receivables uses the following formula to determine the applied amount:
Applied Amount = open application line type amount / sum of application line types in rule details * Receipt Amount
Any remaining receipt amount is applied using the Overapplication rule.
The Overapplication rule is always the last rule in an application rule set. This rule applies any remaining receipt amount after the balance due for all charges has been reduced to zero.
If the transaction type for the debit item allows overapplication, Receivables applies the remaining amount to the lines, making the balance due negative. If the transaction type for the debit item does not allow overapplication, you can either place the remaining amount on-account or leave it unapplied.
Note
Lockbox uses the AutoCash rule set to determine how to apply the remaining amount.
These examples show how Oracle Fusion Receivables uses each application rule in an application rule set to apply a payment to a transaction.
Invoice 123 contains these details:
|
Field |
Value |
|---|---|
|
Line |
$1000 |
|
Tax |
$140 |
|
Freight |
$200 |
|
Total |
$1340 |
Your customer remits a partial payment of $1040 for this invoice. This table shows how Receivables applies the payment using each of the three application rules:
|
Application Rule |
Total Amount Applied |
Line Amount Applied |
Tax Amount Applied |
Freight Amount Applied |
|---|---|---|---|---|
|
Line First - Tax After |
1040 |
1000 |
40 |
0 |
|
Line and Tax Prorate |
1040 |
912.28 |
127.72 |
0 |
|
Prorate All |
1040 |
776.12 |
108.66 |
155.22 |
This table shows the calculations used by each application rule:
|
Application Rule |
Calculations |
|---|---|
|
Line First - Tax After |
|
|
Line and Tax Prorate |
|
|
Prorate All |
|
The Line First - Tax After rule first applies the payment to the line amount, reducing the balance due to zero. Receivables then applies the remaining amount ($40) to the tax charges, reducing the open tax amount to $100. Since the payment is not enough to close these items, the freight balance is not affected.
This table compares each line type before and after you apply an amount using the Line First - Tax After rule:
|
Transaction Amount |
Remaining Amount |
Line Items |
Line Items Remaining |
Tax |
Tax Remaining |
Freight |
Freight Remaining |
|---|---|---|---|---|---|---|---|
|
$1340 |
$300 |
$1000 |
$0 |
$140 |
$100 |
$200 |
$200 |
The Line and Tax Prorate rule applies a proportionate amount to the open line and tax charges. Since the amount applied is not enough to close these items, the freight balance is not affected.
This table compares each line type before and after you apply an amount using the Line and Tax Prorate rule:
|
Transaction Amount |
Remaining Amount |
Line Items |
Line Items Remaining |
Tax |
Tax Remaining |
Freight |
Freight Remaining |
|---|---|---|---|---|---|---|---|
|
$1340 |
$300 |
$1000 |
$87.72 |
$140 |
$12.28 |
$200 |
$200 |
This table shows the calculations used to arrive at the proportionate amounts:
|
Item |
Calculations |
|---|---|
|
Line Items |
1000 - 912.28 = 87.72 Amount Line Items - Line Amount Applied = Open Line Amount |
|
Tax |
140 - 127.72 = 12.28 Tax Original - Tax Amount Applied = Open Tax Amount |
The Prorate All rule applies a proportionate amount of the receipt to the line, tax, and freight for this transaction.
This table compares each line type before and after you apply an amount using the Prorate All rule:
|
Transaction Amount |
Remaining Amount |
Line Items |
Line Items Remaining |
Tax |
Tax Remaining |
Freight |
Freight Remaining |
|---|---|---|---|---|---|---|---|
|
$1340 |
$300 |
$1000 |
$223.88 |
$140 |
$31.34 |
$200 |
$44.78 |
This table shows the calculations used to arrive at the proportionate amounts:
|
Item |
Calculations |
|---|---|
|
Line Items |
1000 - 776.12 = 223.88 Amount Line Items - Line Amount Applied = Open Line Amount |
|
Tax |
140 - 108.66 = 31.34 Tax Original - Tax Amount Applied = Open Tax Amount |
|
Freight |
200 - 155.22 = 44.78 Freight Original - Freight Amount Applied = Open Freight Amount |
This example shows how Oracle Fusion Receivables uses the Prorate All application rule to apply a payment to a transaction that has mixed sign balances, that is, not all of the charges that make up the transaction have the same sign (positive or negative).
When you apply a payment to a transaction that has mixed sign balances, Receivables applies the payment only to those amounts that have the same sign as the payment. For example, if the payment is for a positive amount (that is, it is not a credit memo), Receivables only reduces the charges that have a positive balance. Any negative balances are not affected.
As with transactions having a same sign balance, Receivables applies any remaining amounts according to the Overapplication rule assigned to the application rule set.
Invoice 101 contains these details:
|
Field |
Value |
|---|---|
|
Line |
<$100> |
|
Tax |
$100 |
|
Freight |
$30 |
|
Late Charges |
$10 |
Assume that you are using the Prorate All application rule. Your customer remits a receipt of $100. Receivables prorates the positive receipt amount among the positive tax, freight, and late charges amounts. The line amount of <$100> is not affected.
This table shows the new balance of Invoice 101 after the receipt application:
|
Field |
Value |
|---|---|
|
Line |
<$100> |
|
Tax |
$28.56 |
|
Freight |
$8.58 |
|
Late Charges |
$2.86 |
This table compares each line type for this invoice before and after you apply the payment using the Prorate All rule:
|
Line Items |
Line Items Remaining |
Tax |
Tax Remaining |
Freight |
Freight Remaining |
Late Charges |
Late Charges Remaining |
|---|---|---|---|---|---|---|---|
|
<$100> |
<$100> |
$100 |
$28.56 |
$30 |
$8.58 |
$10.00 |
$2.86 |
This table shows the amount applied to each line type:
|
Total Amount Applied |
Line Amount Applied |
Tax Amount Applied |
Freight Amount Applied |
Late Charges Amount Applied |
|---|---|---|---|---|
|
100 |
0 |
71.44 |
21.42 |
7.14 |
This table shows the calculations used by the Prorate All application rule:
|
Item |
Calculations |
|---|---|
|
Tax |
100 - (21.42 + 7.14) = 71.44 |
|
Freight |
(30 * 100) / 140 = 21.42 |
|
Late Charges |
3(10.00 * 100) / 140 = 7.14 |
The tax treatment option on the application rule set determines how to reduce the tax amount in relation to the line amount when a payment is applied.
The tax treatment options are:
Prorate: Proportionately reduce the net amount of the line and associated tax amounts.
Before: First reduce the open tax amount, then apply any remaining amount to the line.
After: Reduce the open line amount, then apply any remaining amount to the associated tax.
You assign a rounding correction to one of the line types (Line, Freight, Charges) belonging to an application rule set. When an amount is prorated across more than one line type, the application rule set uses the line type you indicate to account for the rounding adjustment. The line amount of the designated line type is adjusted accordingly to account for any rounding corrections within the rule set.
Define a remittance method and clearance method for each receipt class. These settings determine the remittance and clearing behavior for receipts with a given receipt class.
Remittance Methods
Use the remittance method to determine the accounts that Oracle Fusion Receivables uses for automatic receipts that you create using the receipt method assigned to this receipt class.
Standard
Use the remittance account for automatic receipts assigned to a receipt method with this receipt class.
Factoring
Use the factoring account for automatic receipts assigned to a receipt method with this receipt class.
Standard and Factoring
Receivables selects receipts assigned to this receipt class for remittance regardless of the batch remittance method. In this case, you can specify either of these remittance methods when creating your remittance batches.
No Remittance
For Manual receipts only. Remittance is not required for manual receipts assigned to this receipt class.
Clearance Methods
Use the clearance method to require receipts created using a receipt method assigned to this receipt class to be reconciled before posting them to the general ledger cash account.
Directly
This method is for receipts that you do not expect to be remitted to the bank and subsequently cleared.
It is assumed that these receipts are cleared at the time of receipt entry and require no further processing.
By Automatic Clearing
Use this method to clear receipts using the Clear Receipts Automatically program.
Note
You can also clear receipts using this method in Oracle Fusion Cash Management.
By Matching
Use this method to clear receipts manually in Cash Management.
Define the attributes of an automatic receipt method to determine how automatic receipts are processed against selected transactions.
There are these points to consider when defining an automatic receipt method:
Receipts Inherit Transaction Numbers
Number of Receipts Rule
Receipt Maturity Date Rule
Automatic Print Template
Lead Days
Customer Payment Method
If you are using One per Invoice as the Number of Receipts Rule, you can enable the Receipts inherit transaction numbers option to ensure that the automatic receipt number is always the same as the transaction number to which it is applied. Enabling this option helps track automatic receipts.
Do not enable this option if you want to use document numbers with automatic receipts.
The Number of Receipts Rule determines the way in which the automatic receipt process creates and applies receipts against transactions.
Select one of these rules:
One per Customer: Create one receipt for each customer.
One per Customer Due Date: Create one receipt for each customer and due date. This option creates several payments for a customer if the invoices of the customer have several due dates.
One per Invoice: Create one receipt for each invoice.
One per Site: Create one receipt for each customer site.
One per Site Due Date: Create one receipt for each customer site and due date.
Important
The Number of Receipts Rule assumes an additional grouping by payment instrument. For example, if you use the One per Customer rule, and two invoices belonging to the same customer are to be paid with different credit cards, the automatic receipt process create two receipts, one for each credit card number.
Use the Receipt Maturity Date Rule to pay invoices that have different due dates with a single receipt.
Select Earliest to use the earliest due date of all of the invoices that the receipt covers as the receipt maturity date. Select Latest to use the latest due date of all of the invoices that the receipt covers as the receipt maturity date.
When you remit a receipt, Oracle Fusion Receivables uses the maturity date to determine when to transfer funds from the customer bank account to your remittance bank account.
Enter the automatic print template to use for transmissions using this receipt method.
Receivables provides one standard receipt print template to format the output of payment selection and creation programs when you create the receipt document. To use a different receipt print template, you must copy and modify this standard receipt print template.
The number of lead days is the number of days before the invoice due date that an invoice can be selected for application by the automatic receipt process using this receipt method.
This option is useful, for example, when customer approval is required. You can set the value to the number of days normally required to receive approval.
Select the funds capture payment method that the customer will use to remit payment for automatic receipts using this receipt method.
Oracle Fusion Payments predefines funds capture payment methods, but you can define your own.
Define fund transfer error handling to automatically correct errors encountered either during credit card authorization or payment capture, or during bank account transfer.
To define fund transfer error handling for a given receipt method, you map the error codes from a third party credit card provider or financial institution to the corrective actions in Oracle Fusion Receivables for each category of transaction.
When an error occurs during payment processing of automatic receipts with the specified receipt method, Receivables applies the corrective action that corresponds to the given third party error code.
Map each error code to a corresponding action for each category of transaction. This table indicates the corrective actions available for each category:
|
Category |
Available Actions |
|---|---|
|
Invoice, Debit Memo, Credit Memo |
Clear Payment Information, Retry |
|
Receipt |
Retry, Reverse Receipt |
|
Refund |
Retry, Reverse Receipt |
You map a credit card provider error code of GW-0062 to the Invoice category and the Retry action.
When credit card authorization fails and the credit card provider returns the error code of GW-0062 for multiple transactions, then Receivables automatically deletes this error code on these failed transactions. These transactions then become available for inclusion in the next automatic receipt batch.
Define remittance bank account information for each receipt method assigned to a receipt class. Remittance bank account information includes the general ledger accounts to use when you enter or apply receipts.
If you remit receipts in one currency only, you can enter more than one remittance bank account for a receipt method but you must mark one account as the primary bank account for the receipt method.
If you remit receipts in more than one currency for a receipt method, then you must enter at least one remittance bank account per currency and mark one account per currency as primary.
During receipt entry and processing, Oracle Fusion Receivables uses the primary bank account as the default remittance bank account for the receipt. You can accept this value or enter any other bank account defined for the receipt method that is in the same currency as the receipt.
If the receipt class of the receipt method allows factoring, you can specify the number of Risk Elimination Days for factored receipts for a given bank account.
When you factor receipts, Receivables creates a short term debt to account for risk in case of customer default. When you clear or risk eliminate these receipts, the debt is cleared after the receipt maturity date plus the number of risk elimination days that you enter.
Use an Automatic creation method to use the automatic receipt process to create a batch of receipts from selected transactions. For these receipts, Oracle Fusion Payments is responsible for the funds capture process. Use a Manual creation method for receipts either entered manually or imported using lockbox.
The receipt methods assigned to a receipt class account for receipt entries and applications. They also determine the customer remittance bank information.
The receipt class determines the processing steps that are required for receipts. These steps include automatic or manual creation, the remittance method, the bank clearance method, and whether receipts require confirmation by the customer.
Legal entities are linked to remittance and internal bank accounts. You set up remittance banks in Oracle Fusion Cash Management. The receipt method determines which remittance bank account, and therefore which legal entity, is assigned to a receipt.
All receipts inherit the legal entity from the bank account, and all refunds inherit the legal entity of the original receipt.
You can perform receipt applications across legal entities, if the receipt and the transactions to which it is applied are in the same business unit. Receipt applications and receipt clearing across legal entities is recorded in the subledger as intercompany accounting.
The automatic receipt and remittance processes assign a number to an automatic receipt or remittance batch by adding +1 to the previous batch number. When you define the Automatic Receipts receipt source, enter a number in the Batch Number Starts After field as the starting point from which to increment the next automatic receipt batch number.
For example, to number automatic receipt batches starting with 1000, enter the number 999. The automatic receipt process adds +1 to each subsequent number as new batches are created.
You only use receipt sources with receipt and remittance batches. This includes automatic receipt batches, lockbox receipts, and receipts created via spreadsheet.
The Automatic Receipts receipt source is used automatically by the automatic receipt and remittance processes. You do not need to enter this receipt source.
Use lockbox to create receipts in Oracle Fusion Receivables from data supplied by your remittance bank and apply receipts to customer transactions.
The lockbox process has three steps:
Import Data: Lockbox reads and formats the data from your bank file into the interim table using an SQL*Loader script.
Validate Data: Receivables validates the data in the interim table for compatibility, then transfers the data to the receipts tables.
Post Receipts: Apply receipts and update customer balances.
You can submit these steps individually or at the same time. After you post lockbox receipts, Receivables treats these receipts like any other receipts: you can reverse and reapply them, and apply any unapplied, unidentified, or on-account amounts.
This diagram illustrates the lockbox process:
You use the Process Receipts Through Lockbox: Import program to read and format data from the bank file into the AR_PAYMENTS_INTERFACE_ALL table using an SQL*Loader script.
Before you can run the Process Receipts Through Lockbox Import program, you must ensure that you have completed these steps:
Review the environment properties file to get the directory path for the $AR_TOP and $APPLBIN environment variables. $APPLBIN is usually set up as APPLBIN=bin.
The environment properties file is located at $FA_HOME/../../instance/ess/config/ where $FA_HOME is in the format //fusionapps/applications/.
Ensure that the directory path $AR_TOP/$APPLBIN exists for the installation. If the $APPLBIN subdirectory does not exist under $AR_TOP, then create this subdirectory and provide write access.
Place the SQL*Loader control file (.ctl) in the $AR_TOP/$APPLBIN subdirectory.
Enter the control file name in the Control File parameter of the Process Receipts Through Lockbox Import program. The fully qualified path is not necessary.
Enter the full path of the data file in the Data File parameter.
Receivables uses the lockbox transmission format that you specify in the Process Receipts Through Lockbox: Import program submission to ensure that data is correctly transferred from the bank file into the AR_PAYMENTS_INTERFACE_ALL table. The transmission format contains information such as the customer account number, bank account number, the amount of each receipt to apply, and transaction numbers to which to apply each receipt.
The validation process includes these checks:
No duplicate entries exist.
Customer and receipt information is valid.
Amounts to apply do not exceed the receipt amount.
Columns in the AR_PAYMENTS_INTERFACE_ALL table reference the correct values and columns in Receivables.
If the receipt and transaction currencies are different, Receivables also requires specific application information to determine the conversion rate between the two currencies.
Receivables transfers the receipts that pass validation to the AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL interim tables. At this point, you can optionally review receipts and change how they are to be applied before posting.
Use the Process Receipts Through Lockbox: Report to review all pass and fail validations for a lockbox transmission. Receipts that fail validation remain in the AR_PAYMENTS_INTERFACE_ALL table until you manually correct the import errors. You can then resubmit the validation step for these receipts.
Use the Post Receipt Batch program to post lockbox receipt batches that have passed import and validation.
When you process a lockbox receipt batch, Receivables matches receipts to transactions and applies the receipts automatically. In cases where receipts are not applied automatically, Receivables generates a list of recommended transactions for receipt application to complete the process manually.
During the Import step of lockbox processing, Oracle Fusion Receivables stores receipt data from your bank file in the AR_PAYMENTS_INTERFACE_ALL table. Each column in the AR_PAYMENTS_INTERFACE_ALL table contains information needed to run lockbox successfully.
Receivables assigns a value to this column during the import process.
AR_PAYMENTS_INTERFACE_S.NEXTVAL
The import file must leave this column blank.
None.
Receivables assigns a values to this column during the import process.
Current system date.
The import file must leave this column blank.
None.
Receivables assigns a value to this column during the import process.
FND_GLOBAL.WHO_USER_NAME
The import file must leave this column blank.
AR_BATCHES.CREATED_BY, AR_INTERIM_CASH_RECEIPTS.CREATED_BY, or AR_INTERIM_CASH_RECEIPT_LINES.CREATED_BY.
Receivables assigns a value to this column during the import process.
Unknown.
The import file must leave this column blank.
None.
Receivables assigns a value to this column during the import process.
FND_GLOBAL.WHO_USER_NAME
The import file must leave this column blank.
None.
Receivables assigns a value to this column during the import process.
Current system date.
The import file must leave this column blank.
None.
Enter the record type. For example, if this is a batch header record, and your bank uses the value 3 to identify batch headers, enter 3 in this column.
AR_TRANS_RECORD_FORMATS.RECORD_IDENTIFIER
Find out from the bank what character they use to identify each record type. Keep in mind that not all banks use all of the record types. Assign values to identify the following types of records: TRANSMISSION HEADER, TRANSMISSION TRAILER, LOCKBOX HEADERS, LOCKBOX TRAILERS, BATCH HEADERS, BATCH TRAILERS, PAYMENT RECORDS, PAYMENT OVERFLOW RECORDS, and SERVICE HEADER.
None.
Enter the value AR_PLB_NEW_RECORD_INF for all records inserted into this table. The sample SQL*Loader control files that Receivables provides populate this column.
FND_MESSAGES.MESSAGE_NAME
The import file must leave this column blank.
None.
Receivables assigns a value to this column during the import process.
FND_JOB.REQUEST_ID
The import file must leave this column blank.
None.
Receivables assigns a value to this column during the import process.
AR_TRANSMISSIONS.TRANSMISSION_ID
The import file must leave this column blank.
None.
Enter the account number of the sending bank.
Lockbox data file or lockbox transmission format.
None.
AR_TRANSMISSIONS.DESTINATION
Enter the transit routing number of the sending bank.
Lockbox data file or lockbox transmission format.
If this value is included in a transmission header or trailer, you must have the same value here.
AR_TRANSMISSIONS.ORIGIN
Enter the date on which the transmission was deposited into your bank account. This date can be on any of the record types in the transmission.
Each unique deposit date determines a batch of transmission records. For example, if you enter two unique deposit dates for the transmission, lockbox divides the transmission into two batches of receipts.
Lockbox data file or lockbox transmission format.
None.
AR_BATCHES.DEPOSIT_DATE
Receivables assigns a value to this column during the import process.
Derived from deposit date, import date, or entered date.
The import file must leave this column blank.
AR_BATCHES.GL_DATE, AR_INTERIM_CASH_RECEIPTS.GL_DATE, or AR_CASH_RECEIPT_HISTORY.GL_DATE.
Enter the time the deposit was made.
Lockbox data file or lockbox transmission format.
None.
None.
Enter the number of records in the import file. Include all of the types of records in the count: headers, trailers, receipts and overflow records.
Lockbox data file or lockbox transmission format.
If the transmission format includes the transmission header or trailer, lockbox counts all records in this transmission. The validated count includes all receipts and detail records transferred to the interim table.
AR_TRANSMISSIONS.COUNT
Enter the amount of the transmission.
Lockbox data file or lockbox transmission format.
The sum of all of the receipt amounts within the transmission.
AR_TRANSMISSIONS.AMOUNT
Receivables assigns a value to this column during the import process.
Program counts the number of records transferred successfully.
The import file must leave this column blank.
AR_TRANSMISSIONS.VALIDATED_COUNT
Receivables assigns a value to this column during the import process.
Program totals the receipt amounts of records transferred successfully.
The import file must leave this column blank.
AR_TRANSMISSIONS.VALIDATED_AMOUNT
For lockbox header or trailer records, enter the lockbox name or number specified by the bank.
For batch header and trailer records, enter the lockbox number assigned to receipts in the batch.
For receipt records, enter the lockbox number assigned to receipts.
For overflow records, enter the number of the lockbox for the receipt.
Provided by the bank or entered by the user.
This column is mandatory on all lockbox headers and trailers. If the lockbox number is included in the lockbox transmission format, it must appear on every batch record. If the lockbox number is included in the lockbox transmission format and you do not have batch records, it must be entered for every receipt record. If the lockbox number is included in the lockbox transmission format and you do not have any batch records, you must enter this number for each receipt and overflow record.
None.
Enter the number of batches in the lockbox.
Lockbox data file or lockbox transmission format.
None.
None.
Enter the number of payment records in the lockbox.
Lockbox data file or lockbox transmission format.
Do not include payment overflow records.
None.
Enter the total value of the receipts in the lockbox.
Lockbox data file or lockbox transmission format.
None.
None.
For batch header and trailer records, enter the name or number that the bank uses to identify the batch.
For receipt records, enter the batch name for this receipt.
For overflow records, enter the batch for this overflow record.
Lockbox data file or lockbox transmission format.
This column is required for each batch header and trailer record. If the batch name is included in your format, it must be entered for every receipt record. Each unique batch name determines a batch of transmission records. For example, if you enter two unique batch names for your transmission, lockbox divides your transmission into two batches of receipts. If the batch name is included in your format, you must enter this name for each overflow record.
AR_BATCHES.LOCKBOX_BATCH_NAME
Enter the total value of all receipts in this batch.
Lockbox data file or lockbox transmission format.
None.
AR_BATCHES.CONTROL_AMOUNT
Enter the number of receipt records in this batch.
Lockbox data file or lockbox transmission format.
None.
AR_BATCHES.CONTROL_COUNT
Enter a sequential number to indicate the location of each receipt or overflow record in the batch.
Lockbox data file or lockbox transmission format.
This column is required, even if the lockbox transmission format does not have batch, lockbox, or transmission records. The item number must be unique within a batch, a lockbox (if batches are not provided), or within a transmission (if neither batches nor lockboxes are provided). All overflow records for a receipt must have the same item number as the receipt record. You must enter an item number for each overflow record to reference the receipt.
None.
Enter the currency for each receipt.
Lockbox data file or lockbox transmission format.
None.
AR_BATCHES.CURRENCY_CODE or AR_INTERIM_CASH_RECEIPTS.CURRENCY_CODE.
For receipts, enter the conversion rate to use for this currency.
Lockbox data file or lockbox transmission format.
None.
AR_BATCHES.EXCHANGE_RATE or AR_INTERIM_CASH_RECEIPTS.EXCHANGE_RATE.
Enter the conversion rate type to use for this receipt: Corporate, Spot, or User.
Derived from the lockbox definition or lockbox transmission format.
None.
AR_BATCHES.EXCHANGE_RATE_TYPE or AR_INTERIM_CASH_RECEIPTS.EXCHANGE_RATE_TYPE.
Enter the value of each receipt in the batch.
Lockbox data file or lockbox transmission format.
A value is required for each receipt record.
AR_INTERIM_CASH_RECEIPTS.AMOUNT
Enter the transit routing number from the receipt.
Lockbox data file or lockbox transmission format.
This column is optional, but you must enter this number if you enter the account number. Receivables uses the transit routing number and account number together to identify the customer MICR number.
AP_BANK_BRANCHES.BANK_NAME, AP_BANK_BRANCHES.BANK_BRANCH_NAME, or AP_BANK_BRANCHES.BANK_NUM
Enter the bank account number from the receipt.
Lockbox data file or lockbox transmission format.
This column is optional, but you must enter this number if you enter the transit routing number.
AP_BANK_ACCOUNTS.BANK_ACCOUNT_NUM
Receivables assigns a value to this column during the import process.
AP_BANK_ACCOUNT_USES.EXTERNAL_BANK_ACCOUNT_ID
The import file must leave this column blank.
AR_INTERIM_CASH_RECEIPTS.CUSTOMER_BANK_ACCOUNT_ID
Date a receipt is expected to clear.
Lockbox data file or lockbox transmission format.
None.
AR_INTERIM_CASH_RECEIPTS.ANTICIPATED_CLEARING_DATE
Enter the check number printed on the receipt.
Lockbox data file or lockbox transmission format.
A value is required for each receipt record.
AR_INTERIM_CASH_RECEIPTS.RECEIPT_NUMBER or AR_CASH_RECEIPTS.RECEIPT_NUMBER.
Receivables assigns a value to this column during the import process.
Program determines the type.
The import file must leave this column blank.
AR_INTERIM_CASH_RECEIPTS.SPECIAL_TYPE
Indicates whether the AutoApply process is used during lockbox processing.
Lockbox data file or lockbox transmission format.
None.
AR_INTERIM_CASH_RECEIPTS.AUTOAPPLY_FLAG
Enter the customer account number.
Lockbox data file or lockbox transmission format.
This column is optional.
None.
Receivables uses this column to indicate overflow records for the current receipt. You must enter a value for all overflow records.
Lockbox data file or lockbox transmission format.
To identify the last overflow record, enter a value that is different from the overflow indicator in the transmission format. For example, in the BAI transmission format, 0 indicates an overflow record. You have three overflow records for a receipt, the first two records have 0 as the overflow indicator and the third record has 9. Since the third record is not 0, it is identified as the last overflow record.
None.
Enter a sequential number to indicate the order of overflow records.
Lockbox data file or lockbox transmission format.
Within each receipt, the Overflow Sequence usually begins with 1.
None.
Receivables assigns a value to this column during the import process.
Program determines it.
The import file must leave this column blank.
AR_INTERIM_CASH_RECEIPTS.PAY_FROM_CUSTOMER or AR_CASH_RECEIPTS.PAY_FROM_CUSTOMER.
Enter the customer bill-to site for this receipt and include the bill-to site in the transmission format.
Lockbox data file or lockbox transmission format.
If the Require billing location for receipts system option is enabled, you must enter a value in this column. If the Require billing location for receipts system option is not enabled, you only have to enter a value in this column if the Require billing location option on the lockbox record is enabled.
None.
Receivables assigns a value to this column during the import process.
Program determines it.
The import file must leave this column blank.
AR_INTERIM_CASH_RECEIPTS.SITE_USE_ID or AR_CASH_RECEIPTS.CUSTOMER_SITE_USE_ID.
For receipt records, enter the date that is written on the check.
Lockbox data file or lockbox transmission format.
If you are using MICR numbers to identify customers, this date must be equal to or earlier than the date of the lockbox submission. Otherwise, the receipts are processed as unidentified.
AR_INTERIM_CASH_RECEIPTS.RECEIPT_DATE, AR_INTERIM_CASH_RECEIPTS.EXCHANGE_DATE, AR_CASH_RECEIPTS.RECEIPT_DATE, or AR_CASH_RECEIPTS.EXCHANGE_DATE.
Enter the receipt method to associate with a receipt.
Lockbox data file or lockbox transmission format.
Receipt methods contain information about the bank, bank account, and receipt accounts. The receipt method in this column must be the same as the receipt method assigned to the batch source for the lockbox.
None.
Receivables assigns a value to this column during the import process.
Program determines it.
The import file must leave this column blank.
AR_INTERIM_CASH_RECEIPTS.RECEIPT_METHOD_ID or AR_CASH_RECEIPTS.RECEIPT_METHOD_ID.
For receipt records and overflow records, optionally enter the invoice numbers that a receipt is applied to.
Lockbox data file or lockbox transmission format.
You do not have to start with INVOICE1, nor use all eight of the INVOICE columns on a record before you create a receipt record or an overflow record. You can find a list of valid values in AR_PAYMENT_SCHEDULES.TRX_NUMBER. You can supply invoice numbers without specifying the amount applied to each invoice.
None.
For matching dates.
None.
For resolved matching numbers
Program determines it.
None.
For resolved matching dates
Program determines it.
None.
For a match resolved.
Program determines it.
GD: No description or validation information is provided.
None.
For resolved matching installments.
Program determines it.
None.
Status of the invoices that a receipt is applied to.
Program determines it.
None.
None.
For batch header and trailer records and for receipt records, enter any free-form comments.
Lockbox transmission format.
For receipt records, Receivables stores this data but does not display these comments on the receipt.
AR_BATCHES.COMMENTS or AR_INTERIM_CASH_RECEIPTS.COMMENTS
Enter the descriptive flexfield category information for this receipt.
Lockbox data file or lockbox transmission format.
Descriptive flexfield category used to transfer additional information about a receipt.
AR_INTERIM_CASH_RECEIPTS.ATTRIBUTE1-15 or AR_CASH_RECEIPTS.ATTRIBUTE1-15.
Enter the descriptive flexfield attributes of the category designated in the ATTRIBUTE_CATEGORY column.
Lockbox data file or lockbox transmission format.
Use this column to transfer additional information about a receipt. For example, if the bank enters and transmits the customer name, use an attribute column to import this name.
AR_INTERIM_CASH_RECEIPTS.ATTRIBUTE1-15 or AR_CASH_RECEIPTS.ATTRIBUTE1-15.
For receipt records and overflow records, enter the installment numbers of invoices with multiple payment schedules that a receipt is applied to.
Lockbox data file or lockbox transmission format.
If you do not specify the installment number for an invoice with multiple payment schedules, then Receivables applies the receipt amount to the oldest payment schedule first. The installment number must be on the same record as the associated invoice number.
None.
The alternate name of the customer.
Lockbox data file.
None.
None.
The name of the customer bank. Used for receipt records.
Lockbox data file.
None.
None.
The name of the customer bank branch. Used for receipt records.
Lockbox data file.
None.
None.
The name of the bank that received the payment. Used for receipt records.
Program determines it.
None.
None.
The transaction code of the bank.
Program determines it.
None.
None.
Enter the amount of the receipt to apply to the invoice. If the receipt currency and the transaction currency are different, enter the amount of the receipt to apply in the transaction currency.
Lockbox data file or derived from AMOUNT_APPLIED_FROM and EXCHANGE_RATE.
If you provide invoice numbers without specifying the amount applied to each invoice, Receivables applies the receipt to the invoices starting with the oldest receipt schedule first. The value of the AMOUNT_APPLIED column must be on the same record as the invoice number to which it is applied. For example, you cannot have all of the invoice numbers on the receipt record and all of the amounts applied on the overflow record.
AR_INTERIM_CASH_RECEIPTS_ALL.AMOUNT_APPLIED (if a single application) or AR_INTERIM_CASH_RCPT_LINES_ALL.PAYMENT_AMOUNT (if multiple applications).
For receipt and overflow records, if the receipt currency and the transaction currency are different, enter the amount of the receipt to apply in the receipt currency.
Lockbox data file or derived from AMOUNT_APPLIED_FROM and EXCHANGE_RATE.
None.
AR_INTERIM_CASH_RECEIPTS_ALL.AMOUNT (if a single application) or AR_INTERIM_CASH_RCPT_LINES_ALL.AMOUNT_APPLIED_FROM (if multiple applications).
For receipt and overflow records, if the receipt currency and the transaction currency are different, enter the currency of the transaction. This column is used for cross currency receipt applications.
Lockbox data file or derived from AR_PAYMENT_SCHEDULES_ALL.
This column is optional. If null, lockbox derives this value from AR_PAYMENT_SCHEDULES_ALL.
AR_INTERIM_CASH_RECEIPTS_ALL.INVOICE_CURRENCY_CODE (if a single application) or AR_INTERIM_CASH_RCPT_LINES_ALL.INVOICE_CURRENCY_CODE (if multiple applications).
For receipt and overflow records, if the receipt currency and the transaction currency are different, enter the exchange rate used to convert the receipt to the transaction currency.
Lockbox data file or derived from AMOUNT_APPLIED_FROM and EXCHANGE_RATE.
This value is used for cross currency receipt applications when the receipt and transaction currencies do not have a fixed exchange rate.
TRANS_TO_RECEIPT_RATE.
For customer reference.
For customer reason codes.
Oracle Fusion Receivables supplies the packaged procedure arp_lockbox_hook.cursor_for_matching_rule, which you can use to add your own custom matching rule with lockbox.
You can create a customer matching rule, for example, if you need to match matching numbers and dates passed to lockbox with numbers and dates in your own custom tables (custom_table.custom_number and custom_table.custom_date) instead of or in addition to standard matching options. You can also use this feature to match with other numbers and dates in the existing Receivables tables.
This procedure expects a row in the AR_LOOKUPS table with lookup_type = ARLPLB_MATCHING_OPTION and valid values for other columns required for using a customized matching rule. The master program arp_process_lockbox will fetch that row and, if it finds it to be one of the non-standard rows, it will pass the control to this procedure with the corresponding lookup_code in your database.
The procedure should return a string that Dynamic SQL can use to open and parse a cursor. You need to create this SQL string to replace the string named p_cursor_string.
Your string should have the following restrictions:
You should only use the following bind variables:
b_current_matching_number - This retrieves a value of a matching_number passed in the overflow or payment record.
b_current_matching_date - This retrieves a value of a matching_date passed in the overflow or payment record.
b_current_installment - This retrieves a value for the installment number (if any) passed in the overflow or payment record.
b_customer_id - If the customer is identified using a customer number or an MICR number, the program enforces that the matching_number is for the same customer (unless the value is Y in b_pay_unrelated_customers).
b_pay_unrelated_customers - When you submit lockbox, the program prompts you to select whether to allow payments for unrelated customers. This variable retrieves a value Y or ' based on the value that you select.
b_lockbox_matching_option - The value of this variable matches to the value of ar_lookups.lookup_code. It is also stored in ar_customer_profiles.lockbox_matching_option and in ar_lockboxes.lockbox_matching_option.
b_use_matching_date - This variable is assigned a value NEVER, ALWAYS, or FOR_DUPLICATES, depending upon the value of the Match on Corresponding Date option for your lockbox (in ar_lockboxes).
b_receipt_date - This retrieves a value of receipt_date passed in the payment record.
b_cust_acct_rel_set_id - This retrieves the set ID assigned to the reference group Customer Account Relationship for the business unit for which the lockbox is run.
If you are customizing lockbox using this procedure, be sure that this procedure returns a string that can create a valid cursor and that the SQL returns one and only one row (neither zero nor more than one).
The program expects three return values from the SQL statement in the following order:
Customer_Id (NUMBER(15))
Invoice Number (VARCHAR2(20))
Invoice Date (DATE)
The program expects that the combination of invoice number and invoice date is unique in ar_payment_schedules.
You do not have to use all the bind variables that are provided in your SQL statement. For example:
ct.trx_date ' ||
'from custom_table ct ' ||
'where ct.matching_number = :b_current_matching_number '
||
'and ct.matching_date = :b_current_matching_date
';
If the SQL statement does not match with the given matching number and matching date (optional), the statement must return the following:
customer_id = -9999,
trx_number = null,
trx_date = null.
If the statement matches to multiple customers but with the same transaction numbers, it must return customer_id = -7777. In this case the procedure ignores trx_number and trx_date.
Note
The program calling this procedure does not expect it to return any errors, because the definition of a cursor is a one-time procedure and, if done carefully, should not error.
Below is the packaged procedure arp_lockbox_hook.cursor_for_matching_rule that Receivables provides:
PROCEDURE CURSOR_FOR_MATCHING_RULE(p_matching_option IN
VARCHAR2,p_cursor_string OUT VARCHAR2) IS
BEGIN
arp_util.debug('arp_lockbox_hook.cursor_for_matching_rule()+');
p_cursor_string := 'select -9999, NULL, NULL from dual';
arp_util.debug('arp_lockbox_hook.cursor_for_matching_rule()+');
RETURN;
END cursor_for_matching_rule;
END arp_lockbox_hook;
COMMIT;
EXIT;
During lockbox and manual receipt processing, Oracle Fusion Receivables uses the settings of the Match Receipts By rule to identify the document type to use to match receipts to transactions when customer information is not available.
The five document types used to match receipts to transactions are:
Transaction number
Sales order number
Purchase order number
Balance forward billing number
Shipping reference
Receivables attempts to match the number of an open debit item to the number of the document type designated for matching according to your implementation:
If the matched number is a transaction number, Receivables searches for the first transaction with this number and applies the receipt to that transaction.
If the matched number is a sales order number, Receivables searches for the first transaction that belongs to this sales order and applies the receipt to that transaction.
If the matched number is a sales order number, Receivables searches for the first transaction that belongs to this purchase order and applies the receipt to that transaction.
If the matched number is a balance forward bill number, Receivables identifies the customer of this balance forward bill and applies the receipt to the transactions included on the bill using the Clear Past Due Invoices Grouped by Payment Terms AutoCash rule belonging to the active AutoCash rule set.
If the matched number is a shipping reference, such as a waybill number, Receivables searches for the first transaction that belongs to the shipping document and applies the receipt to that transaction.
Note
Receivables allows more than one transaction per sales order or purchase order. If the Match Receipts By rule is Sales Order or Purchase Order, Receivables matches with the first transaction that it finds.
When Receivables finds a document type with the same number as the current search, the process checks the locations where Match Receipts By rules are enabled in this order:
Customer bill-to site
Customer
Lockbox (for lockbox processing)
System options
Receivables looks for a rule that matches the document type of the number in the current search, and stops when a value is found. For example, if Receivables finds a matching transaction number in the first search, it checks the customer site for the Match Receipts By rule. If the rule is set to Transaction, Receivables matches the receipt with this transaction and applies the receipt.
If the Match Receipts By rule at the customer site is a document type other than Transaction, Receivables searches for a number that matches this document type.
If there are no values assigned at the customer site or customer level:
For lockbox processing, Receivables uses either the Match Receipts By rule assigned to the lockbox or, if the Use match criteria to determine customer option is enabled, the entire document type hierarchy.
For manual receipt processing, Receivables uses the Match Receipts By settings on the business unit system options.
If Receivables cannot find a match after searching each document type, the process applies the receipt using the AutoCash rule set defined for the customer.
If the AutoCash rule set is unable to apply the receipt, Receivables assigns the receipt a status of Unapplied. You must then manually apply the receipt.
Here are two examples of using matching rules.
Example 1: During lockbox processing, a receipt record indicates that a receipt should be applied to open debit item 12345. Receivables first searches for a transaction (invoice, debit memo, chargeback) with this number. Receivables finds an invoice with this number, so the process checks the value of the Match Receipts By parameter at the customer site. The Match Receipts By rule is null for this customer site, so Receivables checks the setting in the customer profile. Match Receipts By is set to Transaction in the customer profile, so Receivables matches and applies the receipt to the invoice.
Example 2: Using the same receipt record information as Example 1, assume that Receivables fails to find a transaction with the number 12345. The process then searches for a sales order with this number. Receivables does not find a sales order with this number, so it now searches for and finds a purchase order with number 12345. Receivables then checks the Match Receipts By rule at the customer site. The Match Receipts By rule is null for this customer site, so Receivables checks the setting in the customer profile. The rule is also null in the customer profile, so Receivables checks the rule for the lockbox. The Match Receipts By rule is set to Purchase Order Number for this lockbox, so the process matches the receipt with this purchase order and applies the receipt to the transaction.
Receipt sources are created and assigned to a particular business unit. Lockboxes are created and assigned to a particular lockbox set. The receipt sources available for use with a given lockbox are limited to the receipts sources created and assigned to the business units that belong to the lockbox set.
Use the Batch Size field to enter the number of receipts to assign to each receipt batch during lockbox processing. For example, if you have 991 receipts, and you set the batch size to 10, the lockbox process creates 99 batches with 10 receipts and one batch with one receipt.
If you do not want the lockbox process to separate your lockbox submission into multiple receipt batches, complete these steps:
Enter a number that is larger than the number of receipts in the lockbox transmission for this lockbox.
Enable the Complete batches only option when you submit your lockbox transmission.
The value in the Accounting Date Source field determines the accounting date to apply to the receipts in the lockbox.
The accounting date source values for lockbox processing are:
Constant Date: Use the accounting date that you enter in the lockbox transmission.
If you do not enter an accounting date, the lockbox process does not validate your data.
Deposit Date: Use the deposit date that you enter in the lockbox transmission. This is the date that your remittance bank deposits your receipts.
If you do not enter a deposit date, the lockbox process displays an error and prompts for a deposit date to submit the lockbox.
Import Date: Use the date you import receipts.
If lockbox uses AutoApply, then Oracle Fusion Receivables attempts to match customers to receipts and match receipts to transactions based on your setup.
If lockbox cannot fully apply a receipt due to invalid transaction numbers, then Receivables manages the additional amounts according to the Post Partial Amount as Unapplied option. Receipts are applied to valid transactions, and the remaining receipt amounts are marked as Unapplied.
The first step in lockbox processing is validating the data imported from your bank file using the lockbox file transmission.
The lockbox process validates the data that you receive from the bank to ensure that the entire file was received, that there are no duplicate receipts within a batch, and that the customers and transactions are valid.
Lockbox also validates that all data is compatible with Oracle Fusion Receivables by ensuring that the columns in the AR_PAYMENTS_INTERFACE_ALL table reference the appropriate values and columns in Receivables.
Lockbox checks for duplicate receipts and transactions.
Duplicate receipts have the same receipt number, amount, currency, and customer account number. Lockbox does not allow duplicate receipts within the same receipt source for the same customer. This is the same validation Receivables performs when you manually enter receipts.
Transaction numbers are only required to be unique within a receipt source. A customer can have duplicate transaction numbers as long as they belong to different receipt sources. However, lockbox cannot automatically apply a payment to these transactions.
If a customer has more than one transaction in the system with the same number, then lockbox cannot determine to which transaction to apply the payment. In this case, the receipt is either left as Unapplied (if the customer account number or MICR number is provided) or Unidentified (if the customer account number or MICR number is not provided).
You can manually apply the receipt according to the transaction recommendations that Receivables presents according to your implementation.
When you import a bank file, lockbox completes the following validations:
Transmission Level Validations
Lockbox Level Validations
Batch Level Validations
Receipt Level Validations
Overflow Level Validations
Customer Validations
Currency Validation
Lockbox validates the lockbox transmission to ensure that transmission information corresponds to the transmission format. The following attributes are validated:
Transmission format contains receipt records.
Either the lockbox number is part of the transmission format, or you specify the lockbox number when you submit the lockbox.
Accounting date is in an open accounting period.
Total transmission record count and amount that you supply must match the actual receipt count and amount that is determined by lockbox. If the transmission format includes the transmission header or trailer, lockbox counts all records in this transmission. The validated count includes all receipts and detail records transferred to the interim table.
Origination number is valid, if it is provided.
Lockbox validates the lockbox records to ensure that lockbox information corresponds to the transmission format. The following attributes are validated:
If the transmission format includes the transmission header or trailer, ensure that the lockbox number is included and is valid.
Lockbox batch count is correct, if it is provided.
Lockbox amount is correct, if it is provided.
Lockbox record count is correct, if it is provided.
Origination number is valid, if it is provided.
No duplicate lockbox numbers.
Lockbox validates the batch records to ensure that batch information corresponds to the 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.
Lockbox validates the receipt records to ensure that receipt information corresponds to the 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 trailer of the transmission format) and batches are not imported.
Batch name is specified, if either batch headers or 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 left blank.
Installment1-8 are either valid installment numbers or are left blank.
Invoice, debit memo, credit memo, on-account credit, or chargeback number derived from the matching number does not belong to a 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 account number is valid.
Customer account number and MICR number both reference the same customer, if both are provided.
Receipt date is specified.
Receipt method is valid.
Currency is valid.
Lockbox validates the overflow records to ensure that overflow information corresponds to the transmission format. The following attributes are validated:
Batch name is specified, if either batch headers or trailers are part of the transmission format.
Lockbox number is specified, if either the batch header or trailer is not specified and the transmission format includes the 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 either valid or are left blank.
Installment1-8 are either valid installment numbers or are left blank.
Transaction number derived is entered where an application amount is specified.
Important
For Receipt and Overflow validations of Invoice1-8: If you are using matching numbers and a receipt record indicates that multiple transactions are to be paid by this receipt, lockbox assumes that all of the transactions are the same document type, such as invoices, sales orders, or purchase orders.
For example, if the first 2 transactions are invoices, lockbox successfully matches them with this receipt. However, if the next transaction is not an invoice, lockbox either imports the remaining receipt amount as Unidentified or rejects the entire receipt, depending on the lockbox definition.
If lockbox imports the remaining receipt amount as Unapplied, then Receivables retains the invalid matching numbers.
Lockbox can either validate customer data based on the following attributes or mark the receipt as Unidentified if no match is found:
Customer account number is valid.
MICR number is valid.
Bill-to customer is from a matched invoice, if matching is enabled.
Receivables lets you process receipts in multiple currencies. If you pass the currency, conversion type, and receipt date, lockbox attempts to determine the conversion rate. If lockbox is unable to determine the conversion rate, the receipt will fail validation.
Transmission formats specify how data in a lockbox bank file is organized for import into the Oracle Fusion Receivables interface tables. The transmission format is used by the validation program to ensure that data is transferred correctly.
Transmission Formats
Receivables provides five transmission formats. You can define new transmission formats based on the formats provided by Receivables.
You use an SQL*Loader control file to import data from bank files to Receivables. If you define a different transmission format or edit the existing Default or Convert formats, you must edit the SQL*Loader control file before you can import data into Receivables.
Example (arxmpl.ctl)
This format contains an example of lockbox header information, receipt records, and overflow receipt records.
Default (ardeft.ctl)
This is a standard BAI (Bank Administration Institute) format used by most banks.
Convert (arconv.ctl)
This format is used for transferring payment information from other systems.
Cross Currency (arxcurr.ctl)
This default format used for importing cross currency receipts.
Zengin (arzeng.ctl)
This format is used to import bank files in the Japanese Zengin format.
Enter the record types to include in a lockbox transmission format. Use the Identifiercolumn to uniquely identify each record type.
Your bank file might not contain all of these record types. You should define your transmission format to only include the record types you actually use.
Record Types
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.
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.
Lockbox Header
A Lockbox Header marks the beginning of a specific lockbox. Lockbox Headers usually contain information such as destination account and origination number.
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.
Overflow Receipt
An Overflow Payment usually contains invoice information for a specific payment, such as batch number; item number; sequence number; overflow indicator; invoice, debit memo , or chargeback number; and debit item amounts. Receivables combines the overflow and payment records to create a logical record to submit payment applications.
Receipt
A Receipt usually contains payment information such as MICR number, batch number, item number, check number, and remittance amount.
Service Header
Service Header records contain general information about the transmission.
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.
Transmission Trailer
A Transmission Trailer marks the end of a specific data file. Transmission Trailers usually contain information such as total record count.
Enter the lockbox transmission field types to use to identify the characteristics of each lockbox transmission record type.
You specify the size, order, and format of each transmission record. The lockbox transmission program only validates the fields that you define in your transmission format. The transmission format must be fully compatible with how you organize data in your lockbox file.
Field Types
Account
Your customer bank account. The bank account number and the transit routing number make up your customer MICR number.
Alternate Name
The alternate name for this customer.
Amount Applied 1 to 8
The amount applied to each invoice, debit memo, or chargeback. Each payment or overflow payment record can accommodate up to eight debit item numbers. For cross currency applications, this is the amount to apply in the transaction currency.
Amount Applied From 1 to 8
Used for cross currency receipt applications, this is the amount applied to each transaction in the receipt currency. Each payment or overflow payment record can accommodate up to eight debit item numbers.
Attribute 1 to 15
Use attributes to enter descriptive flexfield segments. Attributes can only be assigned to payment records.
Bank Transaction Code
A code defined for each account that is used by your bank to uniquely identify the kind of transaction in a bank statement (for example, debit, credit, void). This is also used by Oracle Fusion Cash Management to determine the receipt effective date.
Batch Amount
The total receipt batch amount for a specific bank batch.
Batch Name
The name of the batch for a specific bank batch.
Batch Record Count
The total number of payment records in a specific bank batch. The total number of all batch record counts equals the Lockbox Record Count. This does not include overflow payments, headers, or trailers.
Billing Location
Your bank will be able to transmit the billing location of the payment. You must only specify the field name and the field positions that the billing location occupies in the transmitted data file.
Comment
Any comments you want to associate with this transmission.
Conversion Rate
The conversion rate associated with this payment, if you are using lockbox to import foreign currency receipts.
Conversion Type
The conversion type used to convert a foreign currency receipt to the ledger currency.
Currency Code
The currency of the payment. For cross currency payments, you can also enter the Invoice Currency Code. If you do not enter a value in this field, lockbox derives the currency from the information that is provided in the Amount Applied 1 to 8 and Amount Applied From 1 to 8 fields.
Customer Bank Branch Name
The name of the customer bank branch.
Customer Bank Name
The name of the customer bank.
Customer Number
The identification number of the customer who submitted a payment.
Customer Reason 1 to 8
The customer reason why a payment shows a discrepancy.
Customer Reference 1 to 8
Customer comments about this payment.
Deposit Date
The date the bank receives and deposits the customer payment.
Deposit Time
The time at which the bank receives and deposits the customer payment.
Destination Account
Your business bank account. Your business may have more than one bank account.
Effective Date
The date on which the bank determines a customer balance to apply interest (used by Cash Management).
Invoice 1 to 8
The invoices, debit memos, and chargebacks to which you apply your payment. Each payment or overflow payment record can accommodate up to eight debit item numbers.
Invoice 1 to 8 Installment
The installment number for this invoice.
Invoice Currency Code 1 to 8
The currency of the transaction. This field is used for cross currency receipt applications. This field is optional.
Item Number
A sequence number that your bank assigns to a specific payment. This number associates an invoice with a receipt.
Lockbox Amount
The total payment amount in a specific lockbox.
Lockbox Batch Count
The total number of bank batches in a specific lockbox.
Lockbox Number
The identification number for a specific lockbox.
Lockbox Record Count
The number of payment records in a specific lockbox. This does not include overflow payments, headers, or trailers.
Matching Date 1-8
The dates to use to match receipts with transactions, if you are using the Match on Corresponding Dateoption for this lockbox.
Origination
The bank origination number provided by your bank. This number uniquely identifies the bank branch that sends you lockbox information.
Overflow Indicator
Indicates whether there are any additional overflow records for this payment.
Overflow Sequence
A sequence number that your bank assigns to each overflow payment.
Receipt Method
The receipt method associated with this lockbox.
Payment Number
The identification number of a payment. For example, a check number.
Receipt Date
The date your customer made a payment.
Record Identifier
A number that identifies the kind of transmission record.
Remittance Amount
The amount of a payment.
Remittance Bank Branch Name
The name of the bank branch from which this payment originated.
Remittance Bank Name
The name of the bank from which this payment originated.
Status
The status of this payment.
Total Record Count
The total number of transmission records in a bank file. This includes headers, trailers, payments, and overflow records.
Trans to Receipt Rate 1 to 8
The converison rate used to convert the receipt amount from the receipt currency to the transaction currency. This field is used for cross currency receipt applications, when the receipt and transaction currencies do not have a fixed rate. If the currencies have a fixed rate, this field is optional (in this case, lockbox derives the rate to use).
Transit Routing Number
The number that uniquely identifies your customer bank. The transit routing number and the customer bank account number make up the customer MICR number.
Transmission Amount
The total amount of payments for a bank file.
Yes, by using the Format Amount field setting. If you set this field to Yes, lockbox rounds the format amount to the same degree of precision and the same number of decimal places as your ledger currency.
For example, if your ledger currency is USD (precision = 2) and you set this option to Yes, a value of 50000 in the bank data file is formatted as 500.00. If you set the option to No, then this value is not formatted and would appear as 50000.
An overflow record stores additional receipt information that cannot fit on the receipt record. This is typically additional invoice numbers and the amount of the receipt to apply to each invoice.
Each overflow record must have a receipt record as a parent. If there are multiple overflow records for a receipt record, each overflow record is assigned an overflow sequence.
During receipt processing, Oracle Fusion Receivables applies receipts to transactions based on the transaction information provided. In cases where the transaction information provided does not exactly match the transaction numbers available in the system, the AutoApply process will attempt to find as close a match as possible and either apply the receipt to the transaction automatically or present one or more transactions as recommendations for manual receipt application.
In like manner, during lockbox processing it may happen that a lockbox contains incomplete or inaccurate customer information. The AutoApply process will attempt to match a customer to a receipt and present one or more customers as recommendations for the receipt.
The AutoMatch rule set provides information that is used by the AutoApply process to complete the process of applying receipts to transactions. The settings in the AutoMatch rule set provide these recommendations:
Customer recommendations: The matching process recommends customers for lockbox receipts that have invalid or missing customer information.
Transaction recommendations: The matching process recommends one or more transactions for receipt application for both lockbox and manual receipts.
Recommendations are based on matching threshold levels defined in the AutoMatch rule set. These threshold settings determine the percentage level necessary to consider a customer or transaction for receipt recommendation:
Customer Recommendation Threshold: The qualifying percentage necessary to add customer information to a receipt. If the calculated score for a customer account number is above this threshold, then the AutoApply process adds this customer information to the receipt.
Minimum Match Threshold: The qualifying percentage necessary to recommend a transaction for receipt application. If the calculated score for one or more transactions is above this threshold, the AutoApply process recommends the transactions for receipt application, in order of the highest percentage match.
Note
The minimum match threshold must be less than the customer recommendation threshold and the combined weighted threshold.
Combined Weighted Threshold: The qualifying percentage necessary for the AutoApply process to apply a receipt to a transaction automatically. This percentage is the sum of the qualifying percentages defined for the supplied customer information, transaction information, and actual transaction amount that is considered for receipt matching.
Days of Closed Invoices Threshold: Determines which closed transactions to include in the AutoMatch process. All transactions that were closed on or before the number of days provided as the threshold value are considered for application or recommendation.
The threshold qualifying percentages defined in the AutoMatch rule set are compared to the resulting scores of each customer account number or transaction number that is analyzed by the matching process.
The matching process derives a recommendation in this way:
If applicable, remove characters and spaces from the number as defined by the AutoMatch rule set string handling.
Apply the formula (Levenshtein algorithm) to the resulting string to obtain the score. This formula is:
1 - (number of changes required to make the recommended string match the provided string / length of the larger string)
Compare the resulting score to the applicable threshold.
The transaction number 10010 is provided by lockbox for a receipt application. This number does not exist, but Receivables finds the number AR10001. The recommendation for this number is calculated in this way:
The AutoMatch rule set string handling settings indicate that the first two characters are to be removed from a string under consideration.
Receivables removes AR, leaving the number 10001.
It will take one change for 10001 to match 10010. Therefore, the score for this match is (1 - 1/5) = 80%.
The 80% score exceeds the Combined Weighted Threshold value of 70%, so the receipt is automatically applied to transaction AR10001.
The combined weighted thresholds on the AutoMatch rule set provide the qualifying percentage necessary for automatic receipt application. This qualifying percentage considers the importance, or weight, that the AutoApply process should give to the three main components of a transaction: customer information, transaction information, and transaction amount.
Enter the weight, as a percentage, that the AutoApply process should give to successful matching of customer, transaction, and amount information, when considering a transaction for automatic receipt application.
The total of the three percentages must equal 100. In most cases, you would not give equal weight to each component of a transaction. That is, you would not enter 33, 33, 34 as the three weighted values. In order for AutoApply to apply a receipt to a transaction automatically, the weighted thresholds you enter should reflect your standard business practices.
Enter these weighted thresholds:
Customer Weight: Enter the weight to give to matching the customer information on the transaction.
Transaction Weight: Enter the weight to give to identifying the correct transaction, either by correct matching or by the Match Receipts By rule document reference.
Amount Weight: Enter the weight to give to matching the open balance amount. Because the parts of a transaction amount can play a part in this decision, you can use the Amount Weight Exceptions region to provide additional granularity to the open balance amount considered.
The AutoApply process calculates the percentage score for each match between customer, transaction, and amount. It then derives a final score for each weighted threshold as a percentage of the weighted threshold values, and adds these results to obtain a final score, which is compared against the combined weighted threshold value.
For example, you enter these weighted threshold values:
|
Weight |
Value |
|---|---|
|
Combined Weighted Threshold |
75% |
|
Customer |
20% |
|
Transaction |
70% |
|
Amount |
10% |
During lockbox processing, a transaction is presented for receipt application with these details:
Customer account number: 1001
Amount due remaining : 127
Amount after discount: 120.65
Tax remaining: 20
Freight Remaining: 7
Unearned Discount: 6.35
Lockbox presents a receipt for application with these details:
Customer account number: 1005
Reference amount: 120.65
AutoApply performs these calculations:
Customer match: The match between account number 1001 and 1005 has a calculated score of 75%. Since the customer weighted threshold value is 20%, this provides a final score of 15%.
Transaction match: The transaction match has a calculated score of 86%. Since the transaction weighted threshold value is 70%, this provides a final score of 60.20%.
Amount match: The transaction amount match is 100%. Since the amount weighted threshold value is 10%, this provides a final score of 10%.
The sum of the three final scores is 85.20%, which is greater than the Combined Weighted Threshold value of 75%. Therefore, AutoApply automatically applies the receipt to the transaction.
Note
If the score were below the Combined Weighted Threshold value but above the Minimum Match Threshold value, then AutoApply generates recommendations for user review. If the score were below the Minimum Match Threshold value, then AutoApply does not generate recommendations.
The values you enter in the Amount Weight Exceptions region qualify the meaning of the open balance amount that is considered by the Amount Weight threshold.
Enter threshold percentages to qualify the weight to give to different portions of the open balance amount on a transaction.
Enter these amount weight exception thresholds:
Net of Tax Weight: Enter the weight to give to the total transaction amount net of tax but including freight.
Net of Tax and Freight Weight: Enter the weight to give to the total transaction amount net of tax and freight.
Net of Freight Weight: Enter the weight to give to the total transaction amount net of freight but including tax.
Unearned Discount Weight: Enter the weight to give to transaction amounts that include an unearned discount.
If there is an exact match between the receipt amount and transaction amount, AutoApply uses the Amount Weight threshold value without reference to the amount weight exceptions.
If there is not an exact match between the receipt amount and transaction amount, AutoApply looks for the best match among all of the amount weight exceptions to derive a percentage score.
For example, you enter these amount weight exception threshold values:
|
Weight |
Value |
|---|---|
|
Net of Tax Weight |
70% |
|
Net of Tax and Freight Weight |
70% |
|
Net of Freight Weight |
80% |
|
Unearned Discount Weight |
60% |
During lockbox processing, a transaction is presented for receipt application with these details:
Customer account number: 1001
Amount due remaining : 127
Amount after discount: 120.65
Tax remaining: 20
Freight Remaining: 7
Unearned Discount: 6.35
Lockbox presents a receipt for application with these details:
Customer account number: 1005
Reference amount: 120
Because there is not an exact match between the transaction amount and the receipt amount, AutoApply looks for the closest match among the amount weight exceptions to derive an amount weight. There is an exact match between the receipt amount and the transaction amount net of freight (127 - 7 = 120). AutoApply therefore uses the Net of Freight Weight threshold value of 80% as the calculated score for the Amount Weight threshold.
The String Handling region provides rules that assist in the search for transaction matches against the Match Receipt By document reference.
The rules indicate what part of a string to remove in order to compare the resulting stripped number against the matching numbers provided during receipt processing. Matching recommendations for the string are processed according to the weighted threshold values of the AutoMatch rule set.
You can define rules for transaction strings and remittance strings.
Use the string handling settings to indicate what part of a string to remove before string comparison. This is typically an alphanumeric prefix or suffix, or a string of zeros used to pad numbers to equal length.
Enter these settings:
String Location: Indicate whether to remove characters and digits from the front or back of the string.
String Value: Indicate the type of characters and digits to remove: zeros, empty spaces or any value, including zeros, spaces and any character, digit, or special character.
Number of Characters: Indicate the number of places to remove.
If the string handling settings for a transaction number are:
String Location: Front.
String Value: Any.
Number of Characters: 5.
Then the string ABC: 10044 is converted to 10044. The string handling process removed the first 5 characters from the string: the alphanumeric characters ABC, the colon (:), and the space.
If the string handling settings for a transaction number are:
String Location: Back.
String Value: Zero.
Number of Characters: 3.
Then the string:
ABC: 10044000 is converted to ABC: 10044.
985660000 is converted to 985660.
985660000000003 is not processed. This is because AutoApply looks for zeros at the end of the string but finds a number instead.
Use the Exception Rules region to indicate how to process each overpayment and underpayment condition.
Each exception rule consists of a condition, the amount and percentage that applies to the condition, and the action to take when this condition arises. The Action field contains the receivables activities that you have defined for Adjustment, Receipt Write-off, or Refund. The underpayment or overpayment amount is accounted for in the general ledger accounts belonging to the applicable receivables activity.
The User Review Required option indicates whether the action is processed automatically or requires manual review and approval.
For example, the exception rule:
Over Payment >= 100 Refund
means that if a receipt application overpays a transaction by $100 or more, then the customer should receive a refund.
The exception rule:
Under Payment < 5 Write-off
means that if a receipt application results in an underpayment of less than $5, then the remaining amount can be written off.
You can use the Percent field with the Amount field to further refine the scope of a condition. If you use both fields, then both conditions must be met in order to apply the rule.
For example, the exception rule:
Under Payment < 5 5% Write-off
means that if a receipt application results in an underpayment that is both less than $5 and also less than 5% of the open balance, then the remaining amount can be written off. In this case, if a $10 open balance has a $4 underpayment, the above rule does not apply, because $4 is 40% of the open balance. If the $4 underpayment were for an open balance of $100, then the rule does apply, because $4 is 4% of the open balance.
Use an application exception rule set to manage remaining amounts after lockbox processing.
After lockbox processes and applies receipts, the AutoApply process uses the application exception rule set to determine how to manage over and under payments:
If there is an overpayment, the application exception rule indicates whether to refund the amount to the customer, place the amount on account, write off the amount, or leave the amount unapplied.
If there is an underpayment, the application exception rule indicates whether to allow write off of the remaining open balance amount on the transaction.
During lockbox processing, the AutoMatch rule set provides recommendations for matching receipts to transactions based on the transaction information provided. The AutoApply process attempts to match receipts to transactions and either apply receipts automatically or present for manual processing transaction recommendations for receipt application.
In both cases, after customer payments are processed, overpayment or underpayment amounts may remain. The AutoApply process then uses the details of the application exception rule set either to process overpayments and underpayments automatically or to present overpayments and underpayments for user review and manual adjustment.
The customer hierarchy describes the structure of an enterprise or portion of an enterprise. These structures are defined and maintained in the FND_HIER tables. There are three predefined hierarchies: Customer hierachy, Trading Community party hierarchy, and Duns and Bradstreet hierarchy.
You assign a paying relationship to a hierarchy to indicate how the parties in a hierarchy can manage each other's customer payments. There are two paying relationships:
Pay Any: Any party within the relationship can pay for the accounts of any other party within the relationship.
Pay Below: A parent-child relationship whereby each party can pay for its own transactions and the transactions of all parties that are lower in the hierarchy (children, grandchildren, and so on).
This diagram illustrates the customer hierarchy in Acme Corporation:
If this Acme Corporation hierarchy is assigned a Pay Any paying relationship, then:
Acme Worldwide can pay for Acme USA, Acme Japan and Acme West.
Acme USA can pay for Acme Worldwide, Acme Japan, and Acme West.
Acme Japan can pay for Acme Worldwide, Acme USA, and Acme West.
Acme West can pay for Acme Worldwide, Acme USA, and Acme Japan.
If this Acme Corporation hierarchy is assigned a Pay Below paying relationship, then:
Acme Worldwide can pay for Acme USA, Acme Japan, Acme West, and its own transactions.
Acme USA can pay for Acme West and its own transactions.
Acme Japan can pay for its own transactions.
An account relationship is a flat relationship between two customer accounts only. In an account relationship, either one account can pay for the transactions of another account (one-way) or both accounts can pay for the transactions of each other (reciprocal).
A paying relationship makes use of a hierarchical structure within an enterprise to allow all corresponding accounts and transactions that are associated with one party to be accessible to other parties in the structure. In a paying relationship either one account can pay for the transactions of accounts lower in the hierarchy (Pay Below) or all accounts anywhere in the hierarchy can pay for the transactions of any other party (Pay Any).
Then the active paying relationship assignments for this hierarchy are no longer valid. You must enter an end date for each related paying relationship assignment that is on or before the date the customer hierarchy type was deactivated.
|
 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
