Quick notes for Dunning Letter in EBS R12.

• The AR Dunning Letters is obsolete in R12.
• The AR Collections Workbench is obsolete in Release 12.
• You will need to install the Oracle Advanced Collections application.
• The basic Collections functionality is available in Oracle Advanced Collections without any extra cost.
  • However, if you wish to use the more complex functionality in the Oracle Advanced Collections, you will need to pay for license cost.
• In R12 Advanced collections, you will now need to run Oracle Advanced Collections Dunning Plans instead.
• To using the Dunning Plan basic functionality you donot requires Oracle Advanced Collections license.
• The Dunning process in R12 Collections is based on Scoring. You can set it up so that a particular score is associated to a Dunning Letter Plan. Then only allow that one customer to get that score.
• Dunning Plans in Advanced Collections replace Dunning Letters in 11i AR , thus dunning Plans will need to be setup.
• Staged Dunning Letters does not exist in R12 Collections rather can be used the Standard Dunning Plans in R12 of Collections. However, for a more complex timing and use of Dunning letters you will want to setup the highly configurable Strategies and send your letters in this manner.
• Customer interactions and correspondence, transaction processing, and dunning activities are consider as basic functionality whereas scoring, collections strategies, enhanced payment and promise processing, automated promise tracking, and automated collector work assignment are consider as advance feature of product.
• In R12, dunning functionality is executed by XML Publisher.
• These are some of seeded template for Dunning in R12.

List of seeded Template (ref:source)

• It is anticipated that Dunning Letters can be delivered via e-mail, fax or print.
• IEX is application code 782 is product code for Advance Collections.
• Dunning Plans include aging bucket parameters for all data levels (customer, account, bill-to, transaction) in Oracle Advanced Collections. That mean A collections manager can configure dunning plans that look at the oldest aged invoice for each customer, along with the score, and have the system send different dunning letters to customers in each aging bucket.This was first Released 11i.IEX.H.
• A migration white paper and migration scripts will be provided to Oracle Receivables Workbench customers moving to Oracle Advanced Collections.

Metalink Reference Note:

• Note 389443.1:Migrating to Oracle Advanced Collections An Overview for Oracle Receivables
• Note:454908.1:Migrating to Oracle Advanced Collections: An Overview for Oracle Receivables Users
• Note:727659.1:How Do You Setup Dunning Plans Now That You’ve Installed R12 Advanced Collections?
• Note:557452.1: What Products need to be Licensed/Installed for Collections to Compile Properly?
• Note:726427.1 :Cannot Print/Email/Fax Dunning Letters in R12, Why?
• Note 289801.1 :About Oracle Advanced Collections Mini-pack 11i.IEX.H

Every business runs more efficiently with a database that is well-maintained and up-to-date. With the Merge Customer Utility, you have the ability to easily clean up your existing data and consolidate multiple customer accounts as the need arises. Duplicate activity is eliminated by combining current duplicate accounts into one accurate account.

Read the rest of this entry »

As per TCA model,each party can have multiple customer accounts and you might chose to merge two of those. This is customer (account) merge as discussed in last post.

Within TCA model, the concept of “Customer” is separated into two layers: the Party layer and the Account layer

• CRM applications are referring to the Party layer when they refer to “Customer”
• Other applications, on the other hand, are considering to the Account layer, when they refer to “Customer”

Party Merge

Read the rest of this entry »

Auto lockbox Implementation been never been an easy task. Though it requires own set up to handle this functionality. Therefore Its is very important to understand some key points before implementing LockBox in Oracle.

1. How are you identifying the customer against which you apply the receipts? Are you using Auto associate, Customer Number or MICR number?
2. What do you have for the Match Receipts “By option” in Lockbox? Is this by Customer Bill-to-Site, Customer or Lockbox?
3. What do you have for Allow Payment of Unrelated Transactions when you submit lockbox and at the system level?
4. Do you Require Billing Location?
5. What do you have for Auto cash Rules?
6. What do you have for Remaining Remittance Amount? Do you want this as unapplied or Onaccount ?
7. Do you Apply Partial Receipts?
8. What do you have for Remainder Rule Set? How the setup is done ? Is it at customer site or customer profile level?
9. What do you have for Allow Payment of Unrelated Transactions?
10. Do you use Autoassociate?
11. What do you have for Invalid Transaction Number Handling? This mean how post partial amount as unapplied or you want reject entire receipt?
12. Are you expecting Miscellaneous Receipt through AutoLockbox?
13. Is customer expecting some Refunds with negative amount for returns to handled through Lockbox?
14. Is customer expecting any adjustment through Lockbox?
15. Are you using Lockbox standard format or have a different format? If not using standard format do you have details for transmission line details?

If you have already some more items need to be added, do share your experiences.:)

Similar Post

• Auto lockbox - Let’s connect with Bank
• Know 15 important things before you can do Auto lockbox setting?

Is this a one of the Business scenarios:

• Your company receiving payment from customers through mail.
• These mails containing cheque that come from customers who dispersed across a region or the country or out of country.
• Once Check received by AR department they normally submit all cheque with deposite slip
• Many time because of what ever reason, AR department hold checks over a day or two or longer.
• The totol number of customer payment per month would be 1000+.

If this is case and and your business is growing without AR work force, than get use of Lockbox services offered by Different Bank. You need to check with your banking partner, are they offering any such kind of service or not in your region.

LockBox - An Automated Banking Solution

In Financial term normally used by bank is called Lockbox, in which bank will improve your cash flow that enhancing company’s working Capital.This is technology driven technique in which you have your customers’ payments delivered directly to a special post office box instead of your business address and then bank will take care of all your remittance activity.

Its via ..PO Box

You might have one quick question at this stage, Is this same PO Box, that National Postal Department using?

The answer is no, as the key difference between the Lockbox post office box number and a regular post office box is that only your customers’ payments are delivered to the box. Instead of you picking up the payments, bank’s couriers have a key to the post office box, and they remove its contents and deliver your customers’ payments to your bank. Your bank opens the payments and then processes the payments for deposit directly into your bank account.

The Flow

The entire business Flow can be best understood as :

>> What additional things bank handle when you are using Lockbox

These are the some of the services, now a days most of bank offering in LockBox Feature. This Includes

• Automated capture of receivables and customer information as mention above
• Consolidation of paper-based and electronic payment streams, regardless of whether the lockbox provider was the original recipient of the electronic payment.
• Integration with accounting applications for automated receivables matching.
• Remote check capture for accelerating the processing and/or electronic conversion of payments received outside of a lockbox or the normal receivables process.
• In some of the case Bank provide customized business rules for handling industry-specific receivables
• Deposit reconcilement for tracking deposits from multiple departments.
• Manage electronic bill presentment and payment (e-bills).
• Collections.

Which Kind of business does Requires Lockbox

Lockbox is best used by the companies which have numerous customers base such as:

• Public Utilities company
• Mobile or Telecom for receiving customer Payment
• Professional Membership/Club
• health care

Benefits of using LockBox

The LockBox Utilizes two primary benefits :

1. Fund Collection
2. Remittance Information Delivery

On top of that major benfits are that company is able to recognize the funds more quickly and customer’s check is cashed bit faster.

AutolockBox setup:

1. Explore
   1. Explore your banking partner how they offer Lockbox and understand the cost.
   2. Explore the integration aspect of accounting system and lockbox integration, the next is to follow these steps:
2. Steps Involved
   1. Letting customer Know, about the channel of payment. The First you have to instruct customer to deliver there cheque and remittance advice to any any of the bank Lockbox PO Boxes. Most of bank will provide you a list of all there PO boxes.
   2. On receipt of cheque on the business day, the banking courier Partner collect items from each PO boxed and deliver to nearest Lockbox processing center.
   3. Once Cheuqe or remittance advice collected at processing center, data is captured from the cheque and remitted advice. End of day depends upon how bank provide the details you can get the data transmitted to your AR department. Most of bank now a days provide there Portal , you can download the data directly from the Portal.
3. Oracle Configuring :These are the basic Steps in Configuring the Autolockbox in Oracle
   • Maintain Control Parameters : How Autolockbox …This should be based out of these 15 points that was mention in this post.
   • Create Lockbox Addresses for Bank
     • Bank Details
     • Bank Data file Trasmision Format

These steps should be prerequisite as per this post.

The Importance of receivables Information

Thats true , the importance of receivable information can not be neglected.

Most of the bank passes these information,

1. Cheque/Payment Information
   • Amount and Currency
   • Cheque/Payment Number
   • issue date and value date
2. Remittance/invoice Information
   • Reference/invoice
   • Remitte’s Name

Once bank processed, a electronic File is transmitted and transferred to company which owns the lockbox.This files can be in one of two standard banking industry formats: BAI or BAI2 which is defined by Bank Administration Institute (BAI). In some cases and some countries if not following the standard , you can can also get use the transmitted via EDI using the ANSI X.12 823 for lockbox remittances.

Supported Format in Oracle AR

Oracle does provide some seeded file format that you can configure based out of your need.These are the list of Files:

• Example : The control file located under $AR_TOP/bin with name arxmpl.ctl. This format that contains an example of lockbox header information, several receipt records, and overflow receipt records.
• Default : A standard BAI (Bank Administration Institute) format used by most banks. Underline control file would be ardeft.ctl
• Convert :A standard format used for transferring payment information from other systems.Underline control file would be arconv.ctl
• Cross Currency :A default format used for importing cross currency receipts. Underline control file Format would be arxcurr.ctl
• Zengin : This is used to import bank files in the Japanese Zengin format.Underline control file Format would be arzeng.ctl

Global Bank & LockBox

Most of global(ABN, Bank of America, CitiBank, Deutsche, HSBC, JP Morgan and Standard Chartered) as well Local bank are offering Lockbox servics , therefore if you are planning to take an adavantage for lockbox services to boost customers’ bottom line, then Choice is Obvious.

Let me know, in case if you need some more input , I will share some more implemenation senarios.

Similar Post

• Auto lockbox - Let’s connect with Bank
• Know 15 important things before you can do Auto lockbox setting?
• Lockbox - Functional Prospective

In POS & Oracle integration , there was a need to unapply and reapply receipts which are already reconciled in Cash Management . The older SAP system, does work differently thus requirement was to achieve the same in oracle EBS.

Scenario input

To explore API availability to unapply and re-apply receipts which are already reconciled in cash management

Solution & Out of Box Functionlity

The receipt API’s work no matter what is the receipt status. A receipt that is confirmed can be applied or unapplied just the same as one that is already reconciled in Cash Management, there is no difference.

There is no connection between the application/unapply of a receipt and reconciliation in Cash management.

So you can unreconcile and then unapply or you can unapply and then unreconcile.

A receipt application refers to invoices/credit memos etc on which the amount sent by customer is closing the transaction balances.

A receipt reconciliation/unreconcile refers to the matching between a receipt and the statement sent by bank to confirm that the money paid by customer entered in your company account.

There is no link between these two business processes. A receipt can be applied/unapplied irrespective of its situation with Cash Management statement reconciliation.

The world of receipts API

As mention , in one of the post, there are 10+ API’s available to handle any scenarios of receipting process in oracle.

For transactions application and unapply

• Ar_receipt_api_pub.Apply: Applies a cash receipt to a particular installment of a debit item. The application can also be a cross currency application.
• Ar_receipt_api_pub.Unapply: Unapplies the application of a particular installment of a debit item against the specified cash receipt.

Suggested Reading

• Understanding AR Receipt, Point of sale, API’s & Intergration with Oracle Application
• Receipt & Cash Managment

Things you should Know about Order to Cash Cycle.

Order Management

• You enter sales and return orders within an operating unit.
• You define price list and discount names for the entire instance. You can also define price list lines, discount lines, and quantity breaks for the entire instance. If you have more than one validation organization in the instance, you may experience error messages when querying a price list if the inventory item is not valid in your operating unit’s OM validation organization. Thus it is best to define price lists by operating unit.
• You can check credit within an operating unit.
• You assign order lines and shipments to a specific warehouse (inventory organization).
• You pick release orders and print pick slips by operating unit.
• You ship confirm orders and print shipping documents by inventory organization. You can ship from another operating unit, even if in a different set of books.
• You receive returned goods in the inventory organization specified on the return.
• Inventory and Manufacturing integration functions, such as reservations, available to promise checking, sales order demand, and assemble to order job creation are processed in the appropriate inventory organization.

Order Management /Account Receivable

• You setup customers for the entire instance but addresses for each operating unit.
• You merge customers and addresses within an operating unit.
• You report supplier/customer netting within an operating unit.

Account Receivable

• You setup receivable account defaults within an operating unit.
• You produce invoices, statements, and dunning letters within an operating unit.
• You enter and track customer commitments within an operating unit.
• You setup bank accounts and associated cash accounts within an operating unit till Release 11i, as subsequent changes has been made in R12.
• Till Release 11i you can enter deposits and individual receipts by bank account within an operating unit.

Similar Post

• What is “Order To Cash” in Oracle?
• Understanding Multi-Organization Structure in EBS
• Understanding Multi-Organization Structure in EBS(Technical) - Part II

Receivables is integrated with the following modules Cash Management, General Ledger and ipayment in Oracle Financial and have a integration with OM , Pricing , Inventory module within EBS. This can be best understood as:

Lets take some of integration aspect of Oracle AR.

Projects to Receivables

Normally invoices are transferred from Projects to Receivables for billable projects by running the PRC: Interface Invoices to Receivables request from a Projects Responsibility.

Important data in the information flow are: Customer, Line Detail.

Receivables to Cash Management

Cash Management selects Receipts directly from the Receivables tables to reconcile with bank statements.

Important data in the information flow are: Receipt Number, Receipt Amount, Date, Customer, Application Detail.Cash Management selects Unpaid Invoices and Receipts directly from the Receivables tables as Inflow components in the Cash Forecast.

Important data in the information flow are: Amount.

Cash Management to Receivables

Cash Management directly updates Receivables tables with reconciled Receipt information. Important data in the information flow are: Amount Cleared, Receipt Cleared Date, Bank Charges, Bank Errors, Rate Gain/Loss.

Receivables to General Ledger

Invoices (including Debit Memos and Credit Memos), Receipts (including Miscellaneous), Adjustments, and Charge backs are transferred from Receivables to the General Ledger by running the General Ledger Interface request from a Receivables Responsibility. Important data in the information flow are: Code Combination, Journal Amount.

Shared Data

The following data is shared with other modules: Customers, Banks, Payments Terms, Tax Codes, Code Combinations, Units of Measure, Items, Categories, Employees.

Receivable Integration in OPM

The following diagram clearly explain the Receivable Integration in OPM.

This post is more on basic table structure of Oracle Account Receivable.

1.Table that holds AR Invoice data

The following tables can give most of the invoice information.

• RA_CUSTOMER_TRX_ALL stores invoice header information. RA_CUSTOMER_TRX_LINES_ALL stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines.
• The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts.
• RA_CUST_TRX_LINE_GL_DIST_ALL stores accounting distribution records for all transaction lines except bills receivable.The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line.
• RA_CUSTOMERS - Customer information
• RA_CUST_TRX_TYPES_ALL - Customer Transaction Type
• AR_PAYMENT_SCHEDULES_ALL
• RA_CUSTOMER_TRX_LINES_ALL - Transaction Line information
• MTL_SYSTEM_ITEMS - Base table for item

The ER Diagram for a customer Transaction can be easily understood as:

2.Customer Payment

These are the main tables which holds Customer Payment information

• AR_CASH_RECEIPTS_ALL stores one record for each receipt entry.
• AR_CASH_RECEIPT_HISTORY_ALL stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step., Possible statuses are Approved, Cleared,Confirmed, Remitted, and Reversed.
• AR_MISC_CASH_DISTRIBUTIONS_ALL stores all accounting entries for miscellaneous cash applications.
• AR_DISTRIBUTIONS_ALL stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments,credit memo applications, cash receipt applications, and bills receivable transactions.
• AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.
• Each row includes the amount applied, status, and accounting flexfield information.

The ER Diagram for a customer Payment can be easily understood as:

3. Accounting Link between the tables

• For Invoice:

ra_customers (This is for capturing customers information)
ra_customer_trx_all,
ra_cust_trx_types_all,
ar_payment_schedules_all,
ra_customer_trx_lines_all

and joins are customer_trx_id for ar_payment_schedules_all & ra_customer_trx_all & ra_customer_trx_lines_all .
ra_cust_trx_types_all.type IN (’INV’)

• For the Receipts:

ar_receivable_applications_all,
ra_customer_trx_all,
ra_customer_trx_lines_all,
ar_cash_receipts_all

ar_receivable_applications_all.application_type = ‘CASH’,
ar_receivable_applications_all.cash_receipt_id = ar_cash_receipts_all.cash_receipt_id,
ar_receivable_applications_all.applied_customer_trx_id = ra_customer_trx_all.customer_trx_id

This is in response of one offline mail as one of consultant asked some more information for Cash Clearing Account and Clearing Account, so thought to share here. His question was:

What is difference between “Cash Clearing Account” and clearing account in AR?

If you are using Oracle Cash Management, you need to define

• Bank Errors Account
• Bank Charges Account
• Cash Clearing Account

That mean for each bank account you plan on reconciling by using Cash Management. If you use Payables, you can override these accounts for
each payment document you define.Right.

If you set up Oracle Receivables to account for payments at clearing time, enter the cash clearing account you are associating with a bank account. When you create accounting entries for your unreconciled invoice payments, you credit your cash clearing account using this account. After you reconcile your invoice payments using Oracle Cash Management, when you create accounting entries for the cleared payments, you debit this cash clearing account and credit this bank account’s cash account. The account you enter here defaults to the Cash Clearing Account field in the GL Accounts region of the Payment Documents window.

If you are using future dated payment methods, enter the clearing account you are associating with a payment document. When you pay an invoice with a payment document that uses a Manual Future Dated or Future Dated payment method and post payments within Payables,Receivables automatically debits your liability account and credits this account. When you clear this payment using automatic or manual reconciliation within Oracle Receivables or Oracle Cash Management then post payments within Payables, Receivables debits this cash clearing account and credits this bank account’s cash account.

Clearing Accounts are used for AutoInvoice

The clearing account is for your imported transactions. Receivables uses the clearing account to hold any difference between the specified revenue amount and the selling price times the quantity for imported invoice lines. Receivables only uses the clearing account if you have enabled his feature for the invoice batch source of your imported transactions.

Normally, cash clearing account is used if your accounting is accrual Based.

Is there any relation between Cash Clearing and Reconciliation?

Cash clearing and reconciliation have no relation. You can use cash clearing if you want to account for the payment after its cleared in the bank and not on payment issue.

More over, you should note that reconciliation is a activity of matching a Payment / Receipt with bank statement.

Have a nice weekend.:)