BALANCING ORACLE RECEIVABLES AND WHERE
DID THOSE ACCOUNTS COME FROM ANYWAY?

Cathy Cakebread, Consultant

How to balance Oracle Receivables using the standard reports, including:

- Tying Receivables activity to the Aged Trial Balance

- Balancing to Oracle General Ledger including issues regarding posting to the General Ledger

- Period end close procedures

- Details of the actual accounting that takes place for each type of activity and the related source (set up) for each.

Since most companies are now implementing or upgrading to Release 10, I have used the Release 10 screen and report names in the main text but I have included the Release 9 names in italics (only where different). If there are significant changes in release 10, I have noted them.

There are two aspects of balancing Receivables:

1) Balancing the current Aged Trial Balance against the previous period end Aged Trial Balance -- taking into consideration the current month's activity.

2) Balancing totals from Receivables to the totals in the General Ledger (for specific accounts). You can accomplish both using standard Oracle reports.

The purpose of balancing to the Aged Trial Balance is to:

- Insure that the various activities within the system are in sync

- Feel confident that what you are posting is correct

- Have a clear record of the month's activity.

You can use this procedure for any time frame that you need -- daily, weekly, monthly, month to date or whatever. The keys are: 1) use only GL Dates as the date range you select for ALL reports and 2) using the same date range for ALL reports (with no overlap with the dates in the prior period). Within the period, the safest option is to use the period start date through the current date, since you may enter back dated items within a period. I recommend using a spreadsheet to accumulate and print your totals. See attachments for instructions for running the reports and a sample spreadsheet. Note that you can use just the functional currency amounts or both your functional and local currency amounts -- depending on your reporting requirements.

A) Start with the ending balance for your prior period using your Aged Trial Balance of choice. Since most companies have customized the Aging, you can use your custom Agings for this purpose. However, you should first verify that the final total on the custom report is consistent with the total on the standard Oracle Aging.

Since you already ran this report at the end of last period, it is not necessary to run it again.

Field Used: Total for Outstanding Amount.

B) Run the current Aged Trial Balance to know where you are now.

Use the same program that you used in the prior period. You also need to be consistent in whether or not you include Open Credits and Unapplied Payments.

Crucial Value: - As of Date -- should be the GL period end date

Field Used: Total for Outstanding Amount

C) Run the Transaction Register to see the totals for invoices, credit memos, debit memos, on-account credits, and chargebacks by company and by currency with converted values.

Crucial Values: - GL Date range must be the period start date through the period end date

- Companies - be sure to include ALL companies

Fields Used: The Sum for each Class for each Company for each Currency -- in the functional currency

Since you are using so many different values from this report, add a separate total to your spreadsheet to verify that the totals of what you have entered equals the total for the report.

D) Run the Applied Receipts Register (Applied Payments Register) to show what payments were applied in the period.

Crucial Values: Receipt GL Date range must be the period start date through the period end date

Fields Used: Grand Totals for Functional Currency:

D1 -- Applied Amount

D2 -- Earned Discount + Unearned Discount

Since you are using so many different values from this report, add a separate total to your spreadsheet to verify that the totals of what you have entered equals the total for the report.

E) Run the Unapplied Receipts Register (Unapplied Payment Activity Report) to see the net activity for unapplied, on-account and unidentified receipts.

Crucial Values: Receipt GL Date range must be the period start date through the period end date

Fields Used: Add the Report Total for On-Account Amount to the Report Total for Unapplied Amount and multiply the sum by negative 1 to get the net activity amount.

F) Run the Adjustment Register to see your adjustment activity for the period.

Crucial Values: GL Date range must be the period start date through the period end date

Fields Used:

in 10 -- Total for Report for Postable = Yes for the Functional Amount

in 9 -- Sum for Functional Currency

Enter the applicable amounts from the reports into your spreadsheet. The level of detail you include in your spreadsheet depends on various factors. These include: the number of currencies you use, the number of different companies you use and the level of detail you wish to report.

Accumulate the totals for all of the fields listed above (except the ending Aged Trial Balance amount) and display the result.

Calculate the difference between the calculated total and ending Aged Trial Balance amount. The difference should be zero. If it is not, the following are possible causes:

1) You missed a number along the way. I include non-displayed totals for all the items from the Transaction Register and the Applied Receipts Register since I use multiple fields from these reports. If these totals do not equal the total at the end of the reports -- I know that I missed something and I go back and check what I entered.

2) You have used inconsistent values e.g. converted values mixed with non-converted values.

3) You used the wrong sign. Payments, discounts and credit memos are always negative. Adjustments and net unapplied activity may be either negative or positive.

If you still have differences, try re-running your previous period Aged Trial Balance to see if you get the same total as before. If you get a different value, possible reasons include:

1) Known bug in the Research and Reapply Payments screen in Release 9 where Oracle Receivables does not properly close previously unapplied payments -- call Oracle for the 'discovery' script, fix it scripts and the patch.

2) You closed a period with items that were stuck in AutoInvoice between your billing system and Oracle Receivables. Try to have all items cleared from the interface table before you close the period.

A) Journal Entries Report(s)

This ‘report’ consists of four 4 different reports:

Detail by Account

Summary by Account

Detail by Category

Summary by Category

These reports provide a way to see what your journal entries will look like, either prior to or after your posted to GL. Ideally you should run these reports prior to posting to GL in order to pre-view what will happen and to assure that no activity is found in unexpected accounts. If you find invalid accounts and you have not yet posted, correct and re-run the reports. If you find invalid accounts and you have already posted, create the proper offsetting items and re-run the reports.

TIP -- if you are running this report for Unposted Items -- you must leave the values in the Posted Date range blank or nothing will appear on your report.

To see journal entry details and backup for invoices -- run the Sales Journal by GL Account. You can see details and totals for the primary categories: revenue, tax and freight. In Release 10 you can also run the Sales Journal by Customer Account.

Also new in 10 is the Receipt Journal Report which lists the activity (detail or summary) for each of your cash accounts for the date range that you request.

It is good idea to verify that the what you thought you posted to the General Ledger is actually in the General Ledger, especially for critical accounts such as:

Accounts Receivable

Cash -- for each bank account

Unapplied Cash

The level of difficulty in doing this is directly proportional to the complexity of your Accounting Flexfield structure.

Before balancing to the General Ledger you should be sure that your data was actually posted in Oracle General Ledger. The General Ledger Interface is a multi-step process (steps one to five automatically run when you submit Run General Ledger Interface). The steps are:

1) General Ledger Transfer -- extracts the Receivables data and moves it to the General Ledger Interface tables. Check the report for any unposted items.

2) Revenue Recognition from GL-- runs the process that creates the appropriate distributions for items with deferred revenue. Check the report to see what it did. Note that this is not applicable if you do not have deferred revenue recognition.

3) Update Posting Controls -- updates internal controls within Receivables - no report is produced.

4) Unposted Items From GL Transfer Program -- creates a report to tell you if you have any items that are not yet posted and why. Be sure to check this report to see if you have any items that did not post.

5) Journal Import -- verifies the accuracy of the data, checking to see if the Accounting Flexfield segments and combinations are still valid. ALWAYS check this report to see if you have a status of SUCCESS.

6) Journal Post -- the actual posting within GL, generally done by the GL staff. Note that this is a separate process.

ALL six steps must complete successfully before you have posted items to reconcile.

Problems can occur if you:

- Create an activity in Receivables using a valid Accounting Flexfield

- But, between the time you create it and when you run the General Ledger Interface, the Accounting Flexfield or any of it’s segments were disabled. This causes a status of ERROR in the Journal Import. You can change the segment value or Accounting Flexfield in the General Ledger system but then your General Ledger does not match your Receivables values.

I suggest that you:

- Reactivate the combination or segment in GL

- Re-run the Journal Import (you may need to have someone in GL run this for you)

- Post the Journals

- Re-inactivate the segment or Accounting Flexfield.

You may need to create an offsetting Journal Entry in General Ledger but at least you have an audit trail of what happened.

Since the Journal Import can be automatically triggered to run as part of the General Ledger Interface, is it easy to forget to check the results. Always verify that you have a status of SUCCESS, otherwise you will not have any data to post to the General Ledger.

Note that data flows from Receivables to the General Ledger but not back. Any manual journal entries that you enter in GL will not be reflected in Receivables. I suggest that you only do manual journal entries for the reason I listed above. You should set up your system to handle ALL likely entries in Receivables and NOT in General Ledger.

Run the Account Analysis report in Oracle General Ledger for each account you wish to balance. Note that you can use ranges for the Accounting Flexfield segments such as cost center or division but the account value should be the same.

This report provides you with:

- Details of the journal entries for the period you select (you can clearly see the source and category (origin) of each Journal Entry)

- A beginning balance and an ending balance

- Ideally the difference between the beginning balance and the ending balance for the account should be the activity for the period.

If there are journal entries where the source is not Receivables or Revenue Accounting you must isolate them and recalculate your totals or you will not balance to your Receivables activity reports. Examples of non-Receivables entries include manual journal entries created in General Ledger or items from other systems that are using "your" accounts -- you might ask -- "should this really be happening?" In theory, if you are using your Receivables system to accurately record all activity, there SHOULD BE NO REASON to create manual journal entries.

If you do not isolate activity against these key accounts to their standard usage, you may get confusing results. For example -- if you create receivable adjustments that credit your unapplied cash account you may have unexplained differences. Set up your chart of accounts and your standard Receivables activity to utilize accounts that are specific to what you are doing and it will be much less confusing.

Again you can use spreadsheets to calculate and document these values.

If you use only one account for Accounts Receivable -- then the net amount on the Account Analysis report should equal:

the total amount on the Transaction Register

- less the Applied Receipts Register total

- less the Unapplied Net Activity.

If you use more that one account for Accounts Receivable e.g., for different types of business or for debit memos, you may have to run the Transaction Register by type to get a more accurate breakdown.

In order to see the activity for the cash accounts (bank accounts) it is necessary to run additional reports.

Deposited Cash Report - Applied Detail shows the amounts deposited into each bank account for the period you request including Miscellaneous Receipts. Compare the Sum for Actual Amount to the net debit amount for the period. You should also verify that there is no difference between the actual and control totals on this report -- if there are differences, you have out of balance batches and you should correct them immediately. You can also use this report to compare to your actual bank statement.

For more details on your miscellaneous receipts and their accounting impact, run the Miscellaneous Transactions Report (Other Receipts Report).

Reversed Receipts Report (Reversed Payments Report) shows current and prior period payments that were reversed in the current period (these are the credits to the cash account). Use the amount in Total for Reversed Payments. The Release 10 version is better than in Release 9 since it gives you totals by bank account. In Release 9, you will have to manually determine which reversals applied to which bank account.

Use the totals by bank account along with the totals from the Reversed Receipts Report (Reversed Payments Report) to compare to the General Ledger totals.

Since most companies use the same General Ledger account for all three of these status’s, I will treat them as one for the purpose of this discussion. Do not be alarmed by the large values you see being debited and credited to this account. ALL payments (whether you know the invoice number or not) initially credit the unapplied account, even if you immediately match it to an open invoice. The important figure is the net amount, this should equal the net unapplied activity that you determined when you were balancing to the Aged Trial Balance.

Since there is so much activity in and out of the unapplied accounts, I recommend that you NOT use your Receivables account. With a separate account, you have greater visibility of what your actual Receivables are and what your open receipts are. You can always combine them for external reporting. You do have the option to use the same or different GL account numbers for unapplied, on-account and unidentified. Most companies use the same account for all three, but your usage depends on the level of detail you wish to see.

I suggest that you only keep one GL period open at one time in order to avoid possible problems with data crossing from one period to another. Usually this is a problem if you have a very long close process since it is desirable to keep as current as possible with your receipt applications.

To help to streamline your close process you can use to attached Period Close Worksheet to systematically review your data and to insure that you have not missed anything that will cause you to waste time re-processing. There will be some items that will not be applicable to you but you can simply leave them off of your list.

The Accounting Flexfields may or may not appear on the screen depending on what you are doing but everything that you do has an accounting impact. In order to understand this impact it is necessary to know:

1) what accounts are impacted by each transaction

2) what are the related set ups

3) what can you change and/or override and what is out of your control.

AutoAccounting a very powerful setup feature that tells Oracle Receivables how to determine the individual segment values for certain types of activities using the rules that you specify. You can use this feature when creating invoices, debit memos and credit memos either manually or through AutoInvoice. The types of accounts impacted by AutoAccounting include:

(Accounts) Receivable

Revenue

Tax

Freight

Unearned Revenue

Unbilled Receivable

AutoInvoice Clearing (10 only)

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

Transaction Types

Salesreps

Standard Lines (Items)

Taxes

And/or you can hard code values.

You can get one segment value for one type of account from a different place than for another. See the AutoInvoice Worksheets.

The main difference between Release 9 and Release 10 is that you have more flexibility in the sources of the segment values in 10. You have more tables to choose from and may now derive some accounts where in 9 you could only use hard coded values. In 10 you also have the ability to define AutoAccounting for AutoInvoice Clearing where you could not in 9.

When you create an invoice either through AutoInvoice or manually, you can take advantage of AutoAccounting to provide the default Accounting Flexfield values. However, for manual invoices you can override the default values. Invoices created through the Invoice Interface (in Receivables in Release 9 only) use the values defined in the Define Transaction Type screen or may be hard coded in the interface program.

For a standard Invoice:

DR AR

CR Revenue

Tax

Freight

You can also create invoices with special accounting and invoicing rules that allow you to defer revenue recognition for the amount and number of periods that you specify. The following is an example of an invoice created with deferred revenue recognition for $12000 split evenly over 12 periods:

When first created:

DR AR 12000

CR Unearned Revenue 12000

DR Unearned Revenue

1000

CR Revenue 1000

For each of the next 11 periods:

DR Unearned Revenue

1000

CR Revenue 1000

In Release 9, the subsequent period records are automatically created when you create the invoice. In Release 10, you need to run the revenue recognition program for each period -- Run Revenue Recognition.

Recurring Invoices are treated just like regular invoices, they just have different GL dates. Once you have created a recurring invoice has, it really is just another invoice -- with future dates.

In release 10 -- debit memos work just like standard invoices (you even create them on the same screens) -- taking advantage of AutoAccounting but with overridable segments. You can include tax and freight accounts and amounts in 10 whereas you could not in 9. You can also use standard items (Define Item) or the values you have defined as your memo lines (Define Memo Lines) to default the line information. The accounting works the same as in Release 9.

In Release 9

Debit Memos (created in Receivables only) take advantage of pre-defined Transaction Types (Define Transaction Types) and Standard Lines (Define Standard Lines -- in Receivables only) for the default debit and credit Accounting Flexfields, respectively. You can override the default values at entry time.

DR AR (generally)

CR whatever account you choose

You create chargebacks when you are applying cash to close the original invoice and create a new invoice for the amount that the customer short paid. By definition, there is a one to one relationship between a chargeback and the original invoice. You need to set up values for chargebacks in 3 places: Define Receivables Activity where you specify the ‘wash’ account to be used when creating a chargeback. Define Transaction Types where you specify the default AR account. Define Memo Lines is seeded by Oracle and is just used for the line description when you print the chargeback.

The Accounts Receivable account for the new invoice is based on the Accounts Receivable account for the chargeback but you may override it. Oracle credits the Accounts Receivable account for the original invoice (note that these two accounts may not be identical).

In the Category Chargeback you create:

DR Chargeback AR

account (overridable)

CR chargeback ‘wash’ account

In the Category Adjustment you create:

DR chargeback ‘wash’

account

CR AR account from the original invoice

Manual credit memos work differently in Release 9 than in Release 10. Credit memos and on-account credits that are created using AutoInvoice take advantage of AutoAccounting and/or hard coded values

in both Release 9 and Release 10.

In Release 9:

There are two types of credit memos: Credit Memos that you created in Revenue Accounting that offset an individual invoice are called Credit Memos. Credit memos created in Receivables are called On-Account Credits and impact a customer’s account but are not initially tied to a specific invoice. On-account credits may by tied to invoice(s) using the Enter Payments or Credit Research and Reapply screens. The accounting for Revenue Accounting credit memos is controlled entirely by the system and offsets the applicable accounts from the original invoice. These accounts can include Accounts Receivable, Revenue, Tax and/or Freight.

DR Revenue and/or

Tax and/or

Freight

CR AR

On-account credits take advantage of pre-defined Transaction Types (Define Transaction Types) and Standard Lines (Define Standard Lines -- in Receivables only) for the default credit and debit Accounting Flexfields, respectively. You can override the default values at entry time.

DR the account

you choose

CR the account you choose (generally AR)

When you apply an on-account credit to invoice(s), you debit the credit account you used when you created the on-account credit. The Accounts Receivable account for the invoice being offset is credited.

DR CM credit account

(generally AR)

CR AR

In Release 10

You can create both types of credit memos using the same screen. If you indicate a related invoice - the accounting works as in Release 9 BUT you may override the account. If you set your profile option (AR:Use Invoice Account For Credit Memo) to Yes Oracle uses the invoice accounts as the default accounts for your credit memos. If you do not indicate an invoice, Oracle utilizes AutoAccounting but you may override the segment values. The accounting looks that same as above including for the application of on-account credits using the Reapply Credits screen.

The accounting for payments (receipts), with the exceptions of Miscellaneous Receipts, is totally controlled behind the scenes by Oracle Receivables. The accounting flexfields used are determined by the values you defined in Define Batch Sources in Release 9 and in Define Payment Methods in Release 10. The Define Batch Sources screen uses the values you defined in the Define Receivables Activity screen.

NOTE: This means that you have essentially one unapplied, etc. account for each bank and class, which does not allow you to split the unapplied, etc. accounts for the applicable cost center or division.

You are required to set up a Receivables Activity for the following types of activity. You may set up different values for each bank and class that you use (especially important for the cash account) or you may share the activities for multiple bank accounts (e.g., the unapplied and discount accounts).

- Your cash account (the default debit account for that bank account)

- Your unapplied payments account (the default used until you match the payment to an invoice)

- Your on-account account (used to account for pre-payments until you apply them to invoice(s))

- Your unidentified account (used for

receipts where you do not know what customer the receipt is for)

NOTE: Often companies use the same accounting flexfield for unapplied, on-account and unidentified.

- Your earned and unearned discount accounts (used when a client pays their invoices in accordance with the early payment terms) -- these are also often the same.

When you match a payment to an invoice, the cash account (debit) defaults from the Cash Activity for the Payment Method (Batch Source) indicated for the Receipt (Payment) batch. The Accounts Receivable account (credit) defaults from the invoice that is being paid.

NOTE: Even if you instantly match a payment to an open invoice, Oracle still credits and debits the unapplied cash account.

DR Cash

CR Unapplied

DR Unapplied

CR AR

When you leave a receipt as unapplied the accounting is as follows, using the accounting flexfield you defined for this Payment Method (Batch Source):

DR Cash

CR Unapplied

When you identify a receipt is as a pre-payment or deposit, the accounting is also based on the accounting flexfields you defined in the Payment Method (Batch Source):

DR Cash

CR On-account

For unidentified receipts, you also get the accounts from the Payment Method (Batch Source):

DR Cash

CR Unidentified

When you make changes to existing records, the accounting is determined by what it is you are doing. The accounts are based on the accounts you used when you ORIGINALLY created the records.

If you apply a previously unapplied, on-account, or unidentified receipt, Oracle uses the unapplied, on-account or unidentified account used when you created the original payment record. This explains why you get activity against the old account even though you have since changed it to a new account.

The Accounts Receivable account still comes from the invoice.

DR Unapplied or

On-account or

Unidentified

CR AR

When you unapply a receipt, the accounting is just the opposite of the application accounting. You debit the AR account for the original invoice and credit the unapplied account based on the unapplied account when you entered the original receipt:

DR AR

CR Unapplied

When you reverse a receipt, you have two possible options: re-open the invoices you previously paid or create a debit memo for the amount of the reversed payment. If you re-open the invoices, the system offsets the accounts used when you originally applied the payment (from the invoice and the cash account). Note that this process also impacts the unapplied account.

DR Unapplied

CR Cash

DR AR

CR Unapplied

If you create a debit memo, you credit the original cash account but debit the Accounts Receivable Account for the Debit Memo type you selected. You can override the Accounts Receivable account when you enter the payment reversal.

DR AR (overridable)

CR Cash

Miscellaneous Receipts are any receipts that are not tied to open Receivables. Examples include: Cobra Payments, T-shirt Sales, Utility Refunds, and Returns on Investments. Due to the nature of this activity you may need to credit any account within the chart of accounts. The Enter Miscellaneous Transactions (Enter OtherReceipts) screen allows you to do just that.

You can pre-define the credit accounts that you usually use to speed entry but you also have the flexibility to override the values at entry time. You also have the ability to split the amounts into multiple accounts (you can also pre-define those accounts).

Whether you will be splitting the distributions or not, you need to first set up a distribution set -- Define Distribution Sets. A distribution set is a name and one or more accounting flexfields and percentages that you define. You then need to set up a Receivables Activity for each type of standard activity -- Define Receivables Activity. The Receivables Activity refers to the Distribution Set that you just defined. DO NOT enter an Accounting Flexfield for Miscellaneous Cash type Receivable Activities.

When you enter your Miscellaneous Receipts you refer to the Receivables Activities that you defined above. However, you can override the default Accounting Flexfields, both the individual segments and/or the amounts. The cash account used defaults based on the Payment Method (Batch Source) for the bank you specified on the Batch Screen.

DR Cash

CR Miscellaneous Account(s)

Receivable Adjustments are generally write-offs, changes to the invoice balance due to over or under payment by the customer, or the addition of finance charges. You should pre-define commonly used adjustment types using the Define Receivable Activities screen. Though this speeds entry, you may override the default values as you enter the adjustments.

NOTE: always define an Accounting Flexfield and NOT a Distribution Set when you define Receivable Activities for adjustments.

A Receivables Adjustment is always applied to a specific invoice so it impacts the Accounts Receivable account for that invoice. Receivables adjustments may either be positive (debit AR, and increase the invoice balance) or negative (credit AR and decrease the invoice balance).

Examples include:

Add a finance charge:

DR AR

CR Finance Charges

Reduce the Freight Amount:

DR Freight

CR AR

Write-off the invoice balance:

DR Cost of doing

business

CR AR

You can use Automatic Adjustments to perform mass cleanup of open invoice balances. The Accounts Receivable account credited is based on the Accounts Receivable account you used when you created the invoice. The account debited is based on the Receivables Activity you select when you submit the Automatic Adjustment process. Note that ALL adjusts made during this process will use that exact same ‘write off’ account even if the original invoices are for different companies, or cost centers. This may be a consideration in determining if you can utilize Automatic Adjustments or how you may want to run multiple passes of AutoAdjustment.

By knowing the sources and the accounting impact of each activity and the ways to track and reconcile that activity, you should have greater control over the outcome of your data and greater confidence that it works as you expect it to.

About the Author

Cathy Cakebread -- Consultant

I am an independent consultant specializing in Oracle Financials. I have over fifteen years experience in designing, developing and implementing financial software and I was one of the original designers of Oracle Receivables and Revenue Accounting.