What is aged receivables

what is aged receivables

Tips For Implementing Oracle Receivables

Cathy Cakebread, Consultant


Tips for how to successfully implement Oracle Receivables including: converting data from your old system and how to take advantage of the tools that Oracle provides. Sales tax packages, setting up your lockbox and critical decisions regarding defining your Transaction Types and AutoAccounting. Typical customizations and procedures you may want to develop.

When it comes time to implement Oracle Receivables, you need to map out exactly how you want it to work in your environment before you jump into the task of actually implementing this system. You need to determine: how you will use the system, how to customize it for your needs, and what it really does. Because every business has unique needs, answering these questions is not always easy. But by properly identifying your specific needs and utilizing the capabilities in the product, you can be successful. Oracle Receivables has some inherent challenges, so I’ll provide tips you can use to make the system meet your business needs and give you suggestions on what to watch for. You’ll learn how to take advantage of the tools available to successfully implement Oracle Receivables in Release 10.


In most cases, you want to move copies of your data from your existing Accounts Receivable system (your legacy system) into Oracle Receivables so you have all the information you need in one place - this process is referred to as data conversion. DON’T UNDERESTIMATE the amount of time it takes to convert data from your legacy system into Oracle Receivables, I have seen it take from two to four months. How long your conversion takes will depend on: the ease with which you can select the data from your existing system, the number of legacy systems from which you will be extracting data, and the differences in the data structures between the legacy data and the Oracle data. At a minimum, you probably will be converting customers, including addresses and contacts; invoices; credit memos; payments; and adjustments.

Oracle provides three interface programs-Customer Interface, AutoInvoice, and Automatic Lockbox-that simplify data conversion. Customer Interface helps you load and update customers, addresses, contacts, telephone numbers, and credit rules (profiles). Use AutoInvoice for invoices, credit memos, on-account credits, and debit memos. You can also use AutoInvoice to import adjustments or write-offs as debit and/or credit memos. Oracle’s Automatic Lockbox feature can be used to import payment history even if you won’t be using Automatic Lockbox for your daily processing. These three programs are very complex and do hundreds of edits and cross-validations of your data to ensure that what you import will work. You can find more-detailed documentation in the Oracle Financials and Oracle Government Financials Open Interfaces manual set.


There are various theories on how much is enough when you are converting historical data. Critical questions include:

- How much old data do you need?

- How accurate is this data?

- How will you handle subsequent payments, credits, and adjustments if the invoices are not converted?

- How long will the legacy system be available for inquiries?

- How long after you create an invoice do you get returns, credits, or adjustments?

- Do you need the historical data in order to do credit reviews and reference checks on your customers based on historical performance?

- How much data do your auditors suggest you keep?

Most companies convert only open orders, but you can convert more data in Oracle Receivables. Start by converting all open invoices, no matter how old they are. In addition, convert all activity for the last one to two years, depending on how you answered the questions above. In practice, I bring in the invoices as the original amounts with as much of the actual detail as I can provide (part numbers, descriptions, quantities, and so on). Even if the part numbers are no longer valid, you can still provide a detailed description. I then use Automatic Lockbox to import and apply payments to these invoices - even if I won’t use the Automatic Lockbox feature in production. I use AutoInvoice to apply the credit memos or leave them as on-account. I import adjustments as credit memos or on- account credits (if the adjustment is negative) or as debit memos (if it’s positive).

Bringing in data with more detail and the related historical activity provides the following advantages: All of the details are in one place. You don’t have to hop back and forth between systems, which you’d have to do if the history were retained only on the legacy system. You don’t lose customer performance history (for credit references) or volume history (for volume discounts). You have the details for returns, credits, and returned checks. (How far back you need to go depends on how long after an invoice or receipt you generally get a return.) All of the information you need for your tax reporting and audits is in one place.

Before you start the conversion, it’s a good idea to clean up your master customer database, eliminating duplicates and doing general maintenance. If you trust your existing system’s merge program to properly join all of the related records for a specific customer, you should utilize it prior to converting to Oracle Receivables. The key is to convert customers in a way that allows you to easily match legacy invoices with the proper customers. Oracle helps by providing fields for your ‘old’ customer and address reference numbers, but if you’ve merged several customers, you will need to know which reference to use.


You can use the Customer Interface either as a one-time process for converting your data or for ongoing addition to and maintenance of customer information from a non-Oracle Receivables customer master. The first step is to define a customer and determine how your current definition relates to the Oracle Receivables definition of a customer. An Oracle Receivables customer may have many addresses, including several billing addresses. And a single address may have more than one use: It may be used for billing, shipping, or both (see Figure 1). You need to determine how this relates to your current definition of a customer and possibly rework your definition. For example, if you define one customer with several addresses, you can easily see the customer’s total exposure by using the standard reports and screens. If you treat different projects with the same customer separately, you may want to set up each project as a separate customer, to simplify tracking. Deciding on a customer definition is not a trivial aspect of your implementation. The final definition should include input from your accounts-receivable, order-entry, marketing, revenue-reporting, and billing departments.

If you have reasonably few customers and addresses and don’t really trust the data in your old system, you may wish to key the information directly into Oracle Receivables instead of writing a conversion program. A good case for this would be if you had few customers and had over-abbreviated entries to get them to fit in database fields that were too small. You can key information into a test environment once and then write a quick program to load it from your test system into your production system. Make sure you account for customers added between the time you create the test environment and when you go live. You may want to enter new customers into both systems during testing. Or, if you know the ending customer number of your last extract, do another extract using only those customer numbers higher than the last number extracted. Another option, if you have a reasonably small number of customers, is to download the customer information into a spreadsheet, and do whatever cleanup, assigning collectors etc. within the spreadsheet and then load the resulting data into Oracle Receivables.

When defining addresses, you have the option of letting Oracle Receivables assign a number to the location or giving it a value yourself. I suggest you choose the latter and provide a value such as the city name to serve as the location name. Doing this provides more useful information to the people who are actually entering orders, by helping them determine which address is which. If you have more than one customer in the same city, you may want to append something more to make each address unique.


- Postal Standard Compliance: You can get a free booklet from your post office that provides current U.S. Postal Service standards for bulk-mailing addresses. Try to incorporate these standards into your conversion. For example, put any suite and mail-stop information on the line above the actual street address.

- Customer Names: Use the full customer name rather than introducing any abbreviations. By using all-uppercase letters for customer names, you always know what case to use when querying or selecting by customer name.

- Free-Form Addresses: Oracle Receivables requires that city, state, and postal codes be separate fields. Meeting this requirement can be quite difficult and will have a significant impact on the time it takes to convert your data if the fields are not separate in your legacy system.

- Customer-Entry Control: When you are initially going live with Oracle Receivables, it is a good idea to allow only one or two highly trained staff members access to customer-entry and maintenance functions. This avoids confusion and helps you control what happens to your data. When entering new customers, always check first to see if the customer already exists. Set standards to ensure that you always enter the information in a consistent manner. For example, always use the name exactly as it appears on the purchase order or on the client’s business card. Also, heed the warning messages that appear when you enter a customer that ‘already exists’.

- Customer-Entry Screens: The Quick Customer Entry screen in Oracle Receivables is easy to use and comes up as the default when you are entering an order for an undefined customer. However, this screen omits numerous fields, including the Descriptive Flexfields. If you do not need the missing fields, use the Quick Customer Entry screen; otherwise, use the more complex Enter Customer Information screen. Always use the same screen to enter and maintain customer data so that the users always have a consistent method.


AutoInvoice is a complex tool that lets you convert invoices, credit memos, on-account credits, and debit memos into Oracle Receivables. On-account credits are tied to the customer but not to an invoice and are often called negative invoices in legacy systems. These transactions can come from your legacy system via conversion, an external billing system (ongoing), Oracle Order Entry, and/or Oracle Project Accounting. AutoInvoice ensures that the data you create in Oracle Receivables is valid, because it will not create transactions with invalid data. Once you load data into interface tables (generally using SQL*Loader), AutoInvoice checks it for validity and passes valid data into Oracle Receivables. Invalid data is retained in the interface tables until either the related data is fixed (for example, Sales Accounting Flexfields are entered for the parts indicated) or the bad data has been updated. Numerous fields used by AutoInvoice must first be defined within Oracle Receivables. If they are not, the imported data will fail the edits and real records will not be created. Be sure to coordinate your setups with the values you will import. Currently the only way to directly fix invalid data in the interface tables is to update it using SQL*Plus.

Oracle Receivables provides a default format for converting payments from your legacy system. This format is called arconv.ctl, can be found in the bin directory for AR. It is also defined as called CONVERT in the Define Transmission Format screen. You can modify these format definitions as needed-but be sure to keep them synchronized. To convert payments, you only have to provide about six basic fields. You can go the easy way and create one receipt for each application, or you can import as a single receipt with multiple invoices to be paid.


Use the information from the Oracle Financials and Oracle Government Financials Open Interfaces manual set to map the data from your legacy system to Oracle Receivables, field by field. The chapter ‘Integrating Oracle Order Entry with Oracle Receivables’ describes what Oracle Order Entry is passing when it converts shipped orders into invoices. (This gives you a good idea of what you should pass to AutoInvoice.) You can pass either a value (easier for conversion) or an ID (faster and what Order Entry mainly does). Specify what you will pass when you define your batch source (Define Batch Source screen), and use a different batch source for converted data than you do for the data you will be creating when you are live. That way, if something looks off, you know where it came from.

Transaction numbers are based on the batch source you use. Converted data should use the transaction numbers from your legacy system to ensure that payments are applied correctly. After the conversion, use a source with automatic numbering that has a starting number higher than those used for your converted data. You can build intelligence into the automatically created numbers. Start the numbers for each source with a different first digit, and make order numbers notably different from invoice numbers - use 200001 for orders and 500001 for invoices, for example. That way you can identify items from their numbers. Try to keep invoice numbers fewer than eight digits, so you will always see the full numbers on screens and in standard reports.

Processing data through AutoInvoice is iterative. You may need to make multiple changes before you get it exactly right. Be sure to use representative samples for early test conversions, and do at least two or three full loads prior to going live. Have your users verify that the data is correct; they will know if something does not look right. For complete loads of historical data, run a full aging on the legacy system when you extract the data, and then run a full aging in Oracle Receivables when you complete your import. If these are not identical, you need to find out why and determine what needs to be done to fix it. You can also create a temporary table with the invoice numbers and balances from the legacy system and then compare it with the numbers and balances in Oracle Receivables to determine what is different and why.


In converting historical data from legacy systems to Oracle Receivables, you’ll probably encounter several transaction-related issues. Knowing what issues could come up will help you prepare to deal with them:

- Negative lines on a positive invoice: Change the Transaction Type definitions (Define Transaction Types). Change the value for Creation Sign to Any Sign. Be sure to change it back to Positive Sign when you go live.

- Negative invoices in your legacy system: It is best to import these as credit memos in Oracle Receivables-you may want to use a special transaction type to identify them as former ‘negative invoices.’ I do not suggest that you create negative invoices in Oracle Receivables.

- General Ledger (GL) dates for historical data: You can either open all old periods or (preferably) create all converted invoices with the invoice date equal to the true invoice date and the GL date equal to a date in the prior to you go live period. Write a script that changes the GL dates to match the invoice dates once you have imported all of your data. Do this for credits and receipts as well.

- You are concerned that you will post to your General Ledger more than once: Either run GL Interface and delete the batch prior to posting it or write a script to update GL_POSTED_DATE and POSTING_CONTROL_ID in the applicable tables. This applies to all records, including invoices, credit memos, on-account credits, and receipts.

- It takes too long to convert data: Get the latest performance patches for AutoInvoice from Oracle Support. An alternative is to create a simple script to pre-load the part ID instead of letting the system derive it. You can also run AutoInvoice with multiple instances (spawned processes).


More than half of the screens in Oracle Receivables are setup screens, which gives you an idea of the number of decisions you’ll have to make. Most of the accounting activity is behind the scenes, so how you define your default Accounting Flexfields in the setup is critical. I often take screen shots of the applicable setup screens and make hard copies available in meetings where

we are discussing setup, so everyone can see what is being discussed. The following the most critical setup decisions.

See ‘Balancing Oracle Receivables and Where Did Those Accounts Come From Anyway’ in the Spring 1995 OAUG proceedings for more details on the impact of your setup decisions.


How you define your Transaction Types is critical to your implementation of Oracle Receivables. Transaction Types are used to differentiate various types of invoices, credit memos, and on-account credits. You can also use them in AutoAccounting to derive the segments for your key Accounting Flexfields. And, they can be employed to select what to see on the standard reports and they define which transactions will have tax or freight associated with them. Only the first four digits of the transaction type are visible on various reports and in most of the inquiry screens, so I suggest coding those digits to make them more meaningful. Ask yourself the following:

- Do I use different Receivables GL Accounts? If so, what distinguishes them?

- What do I need to report on most frequently-for example, international versus domestic activity, activity by office, or activity by sales channel?

- What is next most important? And so on.

Define the meaning of the first four digits based on these factors. For example, the first character might represent the office (S = San Jose, D = Dallas, P = Phoenix). The second might indicate whether this is an international (I) or domestic (D) sale. The third character could be the type of sale-internal (I), or via a distributor (D). And the fourth could be the record type (I = Invoice, D = Debit Memo, C = Credit Memo, B = Chargeback). Next, create every possible combination. Based on our example, if you see PIDI, you know this is a Phoenix international distributor invoice. If the office is most important, you can use the range of types that start with P when you run the standard reports. If you want international information only, you can run separate reports for international for each office. You can also use custom reports or ad hoc query tools to run reports by the other categories.


In Oracle Order Entry, when you define an Order Type, you also define the one Transaction Type associated with that Order Type. This would seem to imply that for every Transaction Type you need for Oracle Receivables, you also need an associated Order Type (the credit-memo type is derived based on the default Invoice Type). In most cases, this is not necessary or advisable. In Order Entry, you often have one set of needs for defining Order Types that usually has no correlation to your needs for Transaction Types. Also, you cannot change the Order Type once you create an order, so you want to minimize the options. But in Oracle Receivables, you want to run reports and default your accounting values based on numerous choices, so you need a way to use minimal numbers of Order Types and maximum numbers of Transaction Types. The solution is to create an intermediate program that runs after the Receivables Interface and before AutoInvoice to assign the proper Transaction Types as you have defined them, given the available information. You still need to define a default Transaction Type for each Order Type, but the intermediate program overrides the Transaction Type. Create a report set that includes both this program and AutoInvoice to ensure that these two processes always run together (deactivate the report that runs only AutoInvoice). This intermediate program will let you define each application based on its own requirements without forcing you to keep the two in sync. I do this in all situations in which I use Oracle Order Entry as well as Oracle Receivables. Defining the criteria the custom program uses to determine the Transaction Types may be as easy as using the value in a Descriptive Flexfield for the primary sales representative. Alternatively, you may have to derive each piece of the code based on different information sources. In any case, you just need to ensure that all of the information you need to make this determination is available in the system.

- Unearned Revenue (for deferred revenue recognition)

- Unbilled Receivable (for deferred receivable recognition)

- AutoInvoice Clearing (where the quantity * unit price is not equal to the extended price)

Possible sources of this information are the values you set up for:

Transaction Types


Standard Lines (Items)


And/or you can hard code values.

The actual sources available vary by account type. You can also choose to hard-code values. You can derive the value for one Accounting Flexfield segment from a different place than another segment in the same Accounting Flexfield.

The key to properly defining AutoAccounting is to find the lowest common denominator for each segment of each Accounting Flexfield to consistently default to the proper values. Would a sales rep sell goods or services for more than one company? Would he or she sell for more than one cost center? Is the Transaction Type tied to a single Receivables account? Do you have special revenue accounts identified by part or type of part? How can you combine the sources listed above to attain the proper combinations? I usually sketch this out on a white board and then try the setups on the system with different test data to ensure that it works as I expected it to.


If you charge U.S. sales tax, Oracle Receivables requires that you provide the applicable county for each of your addresses. If you are using a tax package, load the tax information first. Then you can write a script to determine each customer’s county, given the city, state, and postal code. Run this script prior to loading converted customer data. The taxing capabilities in Release 10 of Oracle Applications are much more complex than in previous releases. You probably need to use a tax package to keep up with rates by state, county, city, and postal code. You have two options: You can use a tax package for just the state, city, county, and postal code combinations and their related rates (you will receive regular updates), or you can use the tax hook (sometimes called the tax engine). The tax hook is a feature in Oracle Receivables that lets you use programs from your tax-package vendor to determine the tax rate. The tax hook then passes the rates to Oracle Receivables. The complexity in how you charge sales tax and where you sell will determine which option is best for you. You can define taxability at the item (part), customer, and customer-address levels. You can also define exceptions and exemptions. Exceptions are at the item and location level- for example, training is normally tax exempt, but it is taxable in New Jersey. Exemptions are at the item and/or customer level. For example, customer xyx is normally taxed but is exempt for a certain kind of service. When defining items (or parts), define categories for grouping similar items. You can write a script to set the default taxability for every item within a certain category -training items would default to tax-exempt, for example. You could also write scripts to update taxability by item. Define the taxability of new items as you add them.

When you’re using a tax package to define rates, that package’s vendor may provide rates for all states. If you do not charge sales tax in certain states, you may want to write a script to change the rates for those states to 0. You will need to run this script every time you load the latest rates.

If you don’t charge U.S. sales tax, you can implement Oracle Receivables without buying a tax package by changing the following setups and updating system options:

- Add a tax code with the type of VAT (Define Tax Rate Codes).

- Change Transaction Types to Tax Calculation = No (Define Transaction Types) - Change Tax Method to VAT

- Select State.City as the (Sales Tax) Location Flexfield Structure (Define System Options)

- Change Address Validation to Warning (Define System Options)


Skip this section if you do not have an automated lockbox system in which the bank sends or transmits electronic copies of your payments on a regular basis. The lockbox process is documented in the Oracle Financials and Oracle Government Financials Open Interfaces manuals. Note that there are certain fields that Oracle requires, whereas other fields are optional. Work with your bank to ensure that you are getting the data you need. Ask the bank for a copy of the layout for the data it is currently sending. Print a copy of a current data file from the bank, and verify that it matches the layout. Then print the default Oracle Receivables layout (it is in the bin directory for AR and is called ardeft.ctl and in the Define Transmission Formats screen), and compare the two layouts. Determine the following:

- Are you getting a check number?

- Are you getting the MICR Number (transit-routing and bank-account information)?

- Do you get both the invoice number and the amount to be paid for each invoice, or just the invoice numbers?

- How does the bank handle customer deductions (negative amounts)?

- Can more than 398 invoices be paid with one check?

I tend to use the default layout from Oracle Receivables whenever possible, but there are a few exceptions. If your customers tend to short-pay their invoices, include both the invoice number and the amount to be paid for each invoice. That way, the Automatic Lockbox process will apply the amount as indicated on the remittance advice instead of applying full balances until it runs out of money. Another exception is the Oracle Receivables default for the number of overflow records-a two-digit field. If you have four invoice numbers per overflow record, you can apply only one receipt to 398 invoices. To allow for more, expand this field by at least one digit (which then shifts everything else on the line). I have seen it take eight weeks for a bank to make just this change.

Work closely with your bank representative to:

- Define exactly what you want the bank to send.

- Describe how the data the bank sends will be laid out.

- Plan how you will test the data the bank sends you, to ensure that it will work.

- Test data in the new format.

- Ensure that the bank does not start sending data in the new format before you are ready (it helps to give the bank a good estimate of when you plan to go live).

- Determine the impact of the new layout on your monthly costs (the Oracle Receivables default is based on banking standards, so it may be cheaper than what you get now).

Start working with your bank as soon as you can, since you may need programming and other changes. I have seen it take three months to get a working test.


Most companies that implement Oracle Receivables customize the invoice print program based on their special business needs, layout differences, and the fact that the standard print program does not include detail notes from the order. In terms of formatting your invoice, you have several options:

- Use the standard Oracle Receivables layout.

- Work with a printing company to create a custom form.

- Have your printer vendor create programs that will create your form masks (logos, lines, and so on) as you print.

- Utilize the capability in Developer/2000 Reports 2.0/2.5 (the tools the standard program is written in) to provide logos, lines, and so on as part of the print program.

- Use your existing forms.

Whatever option you choose, make sure that the billing address actually appears in the window of your envelopes. Often, the standard company envelope will drive the layout, since you may already have thousands of them. This will also have an impact on statements and any other forms that will be sent through the mail. If you will be working with a printing company or hardware vendor, you need to decide on the layout right away. Custom forms and programs can take four to eight weeks to create, which has a serious impact on your implementation schedule. Make a decision and stick with it, since changes can be costly in terms of both time and money. Try to build flexibility into the factor you can control-the print program.

Also, establish standard terminology for the field headings for all forms-such as invoices, orders, and packing slips-especially those that are likely to go to the same customers. Note that the standard Oracle forms are not always consistent. For example, the shipping reference number is called Shipping Reference on the invoice and Waybill Number on the packing slip. The standard invoice print program is written in Developer/2000 Reports. Seemingly minor formatting changes can take a while to program (maybe days).

Currently, there is no way to pass notes (line-level or order-level notes) from Oracle Order Entry to Oracle Receivables. If you wish to print notes when you print the invoice, do the following: Define new note types in Order Entry: Invoice (for notes that will appear only on invoices) and All Forms (for notes that will appear on all special forms, including invoices). Decide where you want notes to print: In the header or footer, before each line, or after each line (by note type). Modify the print program: Include the notes with the type (Invoice or All Forms) from Order Entry, placing them where you want them. Line wrapping and page wrapping can make this quite a challenge. When you actually go live on Oracle Receivables, you may want to include a letter with your invoices that describes the new layout to your customers.


In most implementations I’ve worked on, the goal is to implement a vanilla (no customizations) system, or at least to minimize customizations. This is a great goal, but most companies include a few additional screens and reports:

- View Invoice Image: Provides a field to query on sales-order number and has a line at the bottom for balances.

- Aged Trial Balance: Ages by invoice date instead of by due date and/or includes PO Number, Sales Order Number, or both. These reference numbers may be used to select which data appears and how that data will be arranged (sorted). The standard reports do not include these fields.

- Credit Memo Register: Gives visibility of on-account credits and shows you to what invoices existing credit memos were applied.

- Sales by Product: Analyzes products sold and when.

You can minimize customizations by mapping the legacy reports and screens to those in Oracle Receivables. Then test to see if the standard features will work for you. This mapping document is also a useful training tool when you go live, because it shows the Oracle equivalent of the legacy reports.


Software can do only so much. To make your implementation successful, you also need to make policy and procedure changes an integral part of your implementation. This is an excellent time to review the way you work. You can often use the new software and its capabilities as an incentive to improve existing methods. If your implementation will include staff in multiple sites, it’s especially important to create new written procedures. Writing new procedures and training users should be part of your implementation plan. Be sure to include training on Oracle Receivables basics, such as navigation, querying, running reports, and reviewing reports online. Other functions that need to be dealt with procedurally but are not directly handled by Oracle Receivables or involve multiple screens and/or reports include:

- Refund-check processing (see attached)

- Unauthorized customer-deduction processing

- Addition of new customers

- Credit-card orders and payments

- Dealing with overpayments

You need to determine what will be done in Order Entry and what will be done in Receivables, especially relative to corrections to data being passed from one to the other.


You have lots of work ahead, and with proper planning and by knowing the things to watch for, you can succeed. You need to start now on those tasks that could have the biggest impact on your schedule - data conversion, custom invoice printing, layout and programming, and working with the bank on Automatic Lockbox.

Source: www.cathycakebread.com

Category: Bank

Similar articles: