There are at least 40 search hits on this blog for this error:

“Please define all periods in which revenue is to be recognized or credited. Note that revenue cannot be recognized or credited in closed and close pending periods.”

Just thought throw some light on why this error occurs. This error is defined in Receivables (AR) and here is the background.

We all know about accounting rules accounting rule duration in AR. Accounting rule tells the system how to recognize the revenue (Variable vs. Fixed) and whether it is deferred or not. On the other hand accounting rule duration (in fixed case we define this in Rule and in case of variable we enter this in transaction) decides how many periods into the future the revenue recognition should happen starting from the rule start date (which usually is transaction date).

The key word here is “future”. For example, if you enter 36 as duration (either fixed or variable), Revenue Recognition process tries to create that many distributions with “future” GL date (36 periods, assuming that the accounting rule is not defined as Deferred). But what if that many number of periods are not even defined in the accounting calendar associated with Ledger or Set of Books? That is where this error comes up.

When you get this error, this simply means that all the periods (number of periods in duration) from the accounting rule start date should be defined in accounting calendar (not required to open or future status). Just define that many number of periods in calendar and this error goes away.

Prepayments in business are not unusual. In Payables, we prepay employees for their expenses ahead of their trip; we prepay suppliers in advance for the goods to be delivered in future. The same can happen with your customers as well. You customers would like to prepay (commitments) in advance for the goods to be delivered in future. These commitments can be against

·         Pricing agreements that they have contractually agreed for a specific product

·         Only for a specific product without any pricing agreement

·         Or just a commitment in general which can be used against any purchase without any agreement

This article focuses on how these commitments and pricing agreements are used in sales orders and AR transactions. Also it discusses what happens to the customer balances and accounting when this sales order line is invoiced.

We all know the order to cash process. We

·         Enter an Order

·         Ship that order

·         Invoice that order

·         Collect and apply cash against that invoice

But just reverse the cycle where you will see

·         Receive cash from customer in advance

·         Create deposit transaction and apply that cash receipt against the deposit

·         Enter and ship an order. While doing so you specify which deposit transaction (commitment) you are shipping goods against.

·         Invoice the order

·         Invoice balance gets adjusted and applying the prior commitment (invoice adjustment).

Commitments in are created in AR in the form of transactions. Commitments can be of two types: Deposits and Guarantees. User guide defines these two transactions as follows:

Deposits: Customer’s prepayment for goods or services that you will provide in the future.

Guarantees: Contractual agreement with your customer to conduct business over a specified period of time.

Technically there is no difference, but business process wise these two transaction types help identify the transaction in a different perspective. While creating these commitments, you can refer to a pricing agreement created for this customer in OM. This will help qualify when you are referring to commitment in order line.

If a customer pays $10,000 in advance, you get a check without any invoice reference. It can come through the lock box as well. Based on this you create a deposit and apply this check to it making the balance in deposit to zero. Now you have commitment.

Next step is to enter a sales order. While entering the sales order line, you can choose the commitment you just created in AR in the LOV in the commitment field (available in order lines).You ship the goods and invoice the customer for the same. When you run auto-invoice import for the same you will see that Prepayment Matching program automatically kicks off. What this program does is to apply an adjustment to this invoice to the extent of the deposited amount. By doing so this invoice balance is reduced. So now you have

·         Deposit

·         Cash receipt

·         Invoice for sales order

·         Adjustment to reduce the balance on this invoice

Each one of them has accounting as follows. You can follow serial number logic to identify which account is getting cleared in which transaction.

When cash receipt is created:

When Deposit created:

When cash receipt is applied against this deposit:

When order line is invoiced:

At the time invoicing since customer has already paid for these goods, an adjustment is created making this invoice balance to zero

The net entry at the end of all these transactions is:

There are a lot business process steps that need to be filled in (communication of cash receipt and creation of commitment so on) and some automation is possible to create transactions.

 Let me know your views on this process.

A reader asked a question on how to convert the invoices from the legacy system that are already paid by the customer but have deferred (unearned revenue) to be recognized in Oracle after conversion. Thought it makes sense write a post on this topic as the topic has enough to cover.

Basically here is the scenario. Say you have sold service or subscription for entire year in advance. Example is the yearly subscription price I pay in advance for my HBR copy. I pay about 144 USD in advance. What HBR does with this advance payment I make? Basically they had an invoice created on my name for that amount. But since the magazine needs to be delivered 12 months into the future, they have to recognize the revenue appropriately every month for each issue that is delivered. They cannot recognize the revenue in one month for all the144 USD. But I have already paid for it so they apply cash to this invoice thus bringing down the receivables to zero.

Imagine if HBSP was using a legacy system when I was a subscriber and would like to move to Oracle in the middle of my subscription. My subscription is from Jan-09 to Jan-10 and if they are migrating in Aug-09, how will this invoice converted?

In general this invoice is not considered as open because I have already paid for it (usually we convert open invoices only). So the invoice that is converted should have zero outstanding balance but outstanding revenue or in other words deferred or unearned revenue.

Why is this hard? The reason is accounting. Let us take the same example and create accounting entries for the same. In the first month when the invoice is created here is the entry.

Since the one issue of the magazine will be delivered in this month they need to recognize 12 USD in the month of Jan-09. Also mind that I am paying this invoice. Accounting entries are:

When cash is applied against the invoice:

When revenue is recognized:

And this recognition continues till August in the legacy system. In August when we convert the invoice into Oracle, how to convert this? Should we convert the entire balance of receivables and unearned revenue or only the remaining unearned revenue about should be the amount in the invoice to convert?

In general cases are:

1.     Invoice is completely paid, and unearned amount is greater than receivables amount (receivables is zero)

2.     Invoice is partially paid and remaining receivable balance is less than the remaining unearned amount.

3.     Invoice is partially paid and the open balance on the invoice is more than the unearned amount.

Two important things to keep in mind are: how much should be the invoice value in all these cases? This question is relevant because receivables balance is not the same as the original invoice amount in the legacy system. And the second question is which GL date to use? And we need an accounting rule to make this happen. As invoices in the legacy can have different number of periods to recognize revenue, we define this rule as Variable type? As we schedule the revenue as soon the revenue recognition is run, we do not check the deferred flag in the accounting rule.

You can convert the invoice amount as is from the legacy and then use adjustments to reduce the receivables balance to appropriate number(zero or partial).  As for the revenue use the rule start date as the equal to the transaction date (as it is in the source). If you keep only current period open gl date will be current for all the past revenue (when revenue recognition is run) and future date for the future periods as per the schedule till the end of it. The future revenue will be recognized on that appropriate date.

But again this falls into bigger picture of your migration strategy and depends on how you convert GL balances for these accounts.

We all know Order to Cash process. We are also aware of RMA to credit process. But now you can extend that process further to refund the customer.

• The process steps are:
• Customer orders a product
• Product is shipped and invoice is sent.
• This invoice is paid by the customer
• Now customer finds an issue with the product and would like to return.
• RMA is created and product is returned (order or invoice can be referenced in RMA).
• Now customer can ask for Refund for the return or he can tell us to keep the money as on-account credit.

Up until recently the only option we had was creating on-account credit for RMAs and manage the process from there. But isn’t it nice to have an option where you can decide to keep it on-account or refund based on customer’s request? Yes the wish is granted. Now you can manage this process completely with standard functionality starting R12 (probably in 11.5.10 as well with a patch or it is already there in that version). Please take a look at the presentation (you can view in full screen is the screens are not visible).

You can either automate this process by with setup at batch source level in AR setup or you can manually perform refund transaction this using applications screen using Refund receivables activity. Refund is a sedeed recevables activity.

In continuation with the Revenue Management discussion (earlier article is here), here I am discussing the role of revenue contingencies in importing invoices using Auto-Invoice or ar_invoice_api_pub from external sources or partner systems.

Just to recap, if you have revenue policy enabled (as seen in the screen shot here, if one of the options is enabled, it means you have enabled revenue policy) revenue of all transactions that violate revenue policy will be automatically deferred. This logic applies to transactions coming from legacy or partner systems also (using either ar_invoice_api_pub or ra_interface tables). Additional qualification for this is that the revenue management should be installed (in R12 it is always installed, though, I predict this will be a separate product in coming releases).

Revenue policy can be violated in AR by using non-standard attributes in a transaction or creating a transaction for a customer whose profile has credit classification which is part of the revenue policy. There are two standard attributes: Refund or Payment Terms. If you specify 30 for Standard Payment term it means your revenue policy is to recognize the revenue (do not defer) as long as the payment terms are 30 or less number of days. If we use any other payment term (non-standard), it is violating the Revenue Policy hence revenue recognition should be deferred till the removal event occurs. On the other hand if you specify 14 days in Standard Refund, that means all the transactions that qualify for this contingency, can have revenue recognized only after 14 days (since in 14 days customer can reject the goods).

In addition to the revenue policy, Oracle has seeded some contingencies. Each contingency has a deferral reason removal event attached to it. This means, only when that removal event occurs, deferred revenue will be recognized. One example is payment of invoice is deferred removal reason for all the invoices that have non-standard payment terms (revenue will be recognized only when the receipt is applied). These removal events are seeded and there is no room for defining custom removal events. But you can create your own contingency rules and assign these seeded removal events.

So what this means for AR conversion? If the converted invoice violates the revenue policy will the revenue be deferred? Are there any options available not to defer the revenue though the revenue policy is violated? See this screen shot of interface lines in AR. This is what we are concentrating on.

These following simple cases are taken to test. In each case a SQL is used to insert into interface tables and ran Autoinvoice Import program.

Case1: A non-standard payment term is used to convert invoice. Just two tables are populated : ra_interface_lines_all and ra_interface_salescredits_all. As I have used non-standard payment term, revenue was automatically deferred which can only be recognized when the invoice is paid. Your can find the sql to test this case here.

Case2: A non-standard payment term is used to convert invoice. But three tables are populated: ra_interface_lines_all, ra_interface_salescredits_all and ra_interface_distributions_all. The last table was populated for Account class ‘REV’ which is for revenue for a specific account. Result was revenue got recognized instantly ignoring the non-standard payment term. The reason for this is this simple: if we are passing revenue line in the distribution, we are saying that the revenue is already recognized, hence do not defer again. Your can find the sql to test this case here.

Case3: Objective of this case was to not to defer the revenue though a non-standard payment term is used. A non-standard payment term is used with the following tables: ra_interface_lines_all and ra_interface_salescredits_all. In order to achieve the objective, we need to populate another column in the ra_interface_lines_all table: deferral_exclusion_flag. Once this column is populated as Y, revenue was not deferred. Your can find the sql to test this case here.

Case4: Finally used a contingency rule. In this case apart from populating ra_interface_lines_all, ra_interface_salescredits_all, I populated another table ar_interface_conts_all with contingency_id 4 (which is Doubtful Collectibility), taken from ar_deferral_reasons. Revenue was deferred as expected. Your can find the sql to test this case here.

Apart from the standard destination tables which are populated by auto invoice program (ra_customer_trx_all, ra_customer_trx_lines_all ra_cust_trx_line_gl_dist_all and ra_cust_trx_line_salesreps_all), if there is contingency that defers the revenue, these two additional tables are populated : ar_deferred_lines_all and ar_cont_lines_all. Both these tables can be joined to the ra_customer_trx_lines_all using customer_trx_line_id.

So to conclude, starting R12, we have to remember contingencies also while converting AR invoices. If ignored, this will have some bad consequences. If you did not want to recognize the revenue because it is already recognized in the legacy, but only receivables, we usually use dummy revenue account while converting AR invoices. But if this gets deferred, (no revenue accounting is generated, but only unearned revenue ) upon the removal of this contingency, revenue will be posted to the account per the auto-accounting setup automatically which was not required.

If you do not want any revenue to be deferred when converting using autoinvoice, then you have to ensure that:

1. There are no revenue policy violations for the invoice or do not create any revenue policy.

2. Use deferral_exclusion_flag in ra_interface_lines_all.

3. Do not use contingencies table (ar_interface_conts_all).

Someone was frenetically searching for an API to create a credit memo in AR. Certainly there are APIs available to create a credit memo, but the issue is that all of them allow creating a credit memo only against an existing invoice and not on account credit. If you have read the Receivables reference guide it clearly tells you to that the AR_CREDIT_MEMO_API_PUB.create_request cannot be used to create on-account credit memos.

But you can always use AR Interface tables to create on-account credit memo. While there is no recommendation on which one is better, personally my choice is always based on the volume. If it is batch processing, I always prefer using ra_interface_lines_all, ra_interface_sales_credits_all and ra_interface_distributions_all. If it is unit transaction, I prefer API approach as the I can reduce unnecessary coding required to insert and then call the auto-invoice interface and then monitor it whether it is completed or not.

The API in focus here is ar_transaction_pub.create_transaction. Using this you can create all types of transactions that can be created in AR. 

Bad news is this can only be used till 11.5.10. In R12, this API has been obsoleted. From R12, the only way you can create on-account credit memos is using Auto Invoice Interface. 

Revenuerecognition.com reports that “One of the primary goals of Sarbanes-Oxley (Sarbox) is to ensure that companies are reporting accurate revenue numbers”. A new survey of 400 public, as reported, and private companies found that more than half (55%) of all public companies,have changed revenue recognition policies as a result of Sarbox and that many of these changes were “moderate”; to “significant”.

None of us can under estimate the importance of accurate revenue recognition in light of Enron episode.

Yet revenue recognition remains largely misunderstood and mismanaged in business world in general and oracle business world in particular. Until R12 we did not have a complete revenue recognizing solution that can accommodate a broad spectrum of industries. But in R12 (and in some releases of R11i) we have some great features supporting revenue recognition practices of companies.

When to recognize the revenue is a very big and complicated question. Different industries have different practices and different countries have different laws governing the same.

What Oracle EBS Offers?

In Oracle EBS we have basically three approaches to recognize the revenue for a transaction.

• Using Accounting Rules. We can define accounting rules with specified percentages of revenue to be recognized over the period of time as deferred revenue.
• Using Revenue Recognition policies and Contingency events: We can specifiy revenue recognition policies as global rules and default them in transactions to defer the revenue to be recognized later.
• Using public APIs we can extend revenue recognition approach as per the needs of an organization.

Revenue Management using Accounting Rules

Prior to the introduction of revenue management functionality, the only way to defer the revenue (to be recognized at an appropriate point of time) is using Accounting Rules. Once defined, these accounting rules can be used in Order Management, Service Contracts and other applications which create their invoices using auto invoice interface. Once the invoice is created for these sources, revenue is deferred based on the accounitng rule setup. Here I am taking a simple scenario of create manual invoice.

Revenue Management using Revenue Policies

Revenue policy simply dictates when the revenue should be recongnized at an operating unit level. Revenue policies are setup with the following options:

• Credit worthiness of the customer
• Non-standard payment terms
• Refund Policy

Creditworthiness

If customer’s credit worthiness is in doubt, we cannot recognize the revenue till the customer pays. In Revenue Management Super User we can setup three (max) possible credit classification profiles. If any of these three classifications are assigned to the customer’s profile classes (as shown in the screen shot), which in turn are assigned to customer accounts, revenue of the invoices created for this customer will get deferred using a contingency rule. Revenue will be recognized only when the customer pays up for this invoice.

Refund Policy

Also a number of companies do have refund policies. Customers can get refund with in some number of days if the product is returned. When we assign the refund contingency to the one of the criteria and if one or more criteria matches to invoice attributes, revenue will be automatically deferred.

Standard Payment Terms

If the company’s standard payment term policy is 30 days and if any customer is given extended payment terms (credit for more than standard number days), all invoices that have payment terms that are non-standard will be deferred. Revenue will not be recognized until the customer pays up.
On top of these three criteria, we can set up contigency rules and create assignment rules. Contingency rules decide under what circumstances invoice’s revenue should be deferred. Contingency rules also use revenue policies defined earler. Assignment Rules match the criteria to consider the invoice for deferral. They work in conjunction with each other.

Custom Contingecy Rules

We can create custom contingency rules with specific parameters or criteria. If the al or one of transaction attributes match this criteria, revenue will be deferred and recognized later at an appropriate time.

API approach

This is most flexible approach. You can find sample script of the ar_revenueadjust_pub API here and here. In this example I am deferring revenue for all the amount of the invoice for 12 months equally. If you are creating invoices all through the day (not in batch mode), you can create a subscription to a standard oracle business event oracle.apps.ar.transaction.Invoice.complete (as shown in this API). If it is in the batch mode, you can create a concurrent program and run that after invoices are created or imported but before accounting is created in Subledger Accounting to transfer to GL.