Oracle Business Applications

Here is the summary of all the articles that talk about service contracts integration with OM. Click on each link to see individual arcticles.

Problem: How to bill a service contract that is created from Order Management?

OK, you have seen how to create a service contract from order management and some technical details so far. What we have done so far is, understanding the standard functionality. Knowing this basic functionality is the key to understanding what we are going to discuss in this article.

When a service contract is created from order management service line is always billed from order management. Let us take an example. Assume that you are on a shopping binge this weekend. On the list say you have 65” TV, one Black Berry and internet connection at home.

When you walk into a store to buy a TV, you might end up buying a service contract apart from warranty that comes along with the product. We all do this to protect ourselves from the risks that we might encounter with the product. When we buy the service contract, we get billed for the service right there. We pay for it and get out with the TV. To enter the same transaction in the system, this is what we do: we enter the TV item in the first line and enter the service line in the next line and assign service reference information in the service tab of order lines. Once we fulfill the transaction this line goes to the receivables interface (ra_interface_lines_all) for it to be imported into the AR. The companies can recognize the revenue over the period of service contract by using the revenue contingencies or accounting rules. Good for the retailer in this industry that the out of the box functionality works here.

Say you next move on to buy a cell phone. As the deal says, you get the instrument for about $100 and there is a two year service contract. There is a base charge for about $50 per month and if you exceed the minimum number minutes “usage” you will be charged 10 cents for each minute of usage. I will discuss the usage at a later point of time. Let us talk about the base charge. The agreement is that the base charge invoice for $50 should be generated and sent to you every month. This is where the problem starts. Unlike service agreement for TV retailer cannot charge one time invoice for entire service contract amount in this case. A $50 dollar invoice needs to be generated every month. So we cannot create a service contract from order management in this case as this is expected to bill from Order Management and have to create this manually in Service Contracts or you have to extend either workflow in OM or by other means to create service contract.

This disjointed process hurts productivity. Think about it, we create an order for the sale and service contract has to be authored manually in order to bill from the service contract for 2 years (we call this as bill schedule).

What can be done?

With the change in the way the service contracts are created from OM starting R12, there is a flexibility to automate this process. Please see the part II of this series to understand the technical details behind this process.

The code behind the program (package oks_ocint_pub) has some extendable custom package. The good thing about this package (OKS_OMIB_INT_EXTNS_PUB) is that if the contract is successfully created, it gets contract ID, topline_id and subline_id as parameters to this procedure post_integration. That is it. If you understand the service contract module’s data model you can manipulate the contract to make it eligible for billing again. So, when you are creating a service line in the order, you do not invoice it there, but you will only fulfill that line so that the contract can be billed from here.

What exactly happens behind the scenes to prevent the contract from billing into AR?

• Contract is created
• Line is created with a covered line that has item instance which was shipped in the order.
• Bill schedules are created with date_completed pre-populated in the oks_level_elements.
• oks_bill_cont_lines is pre-populated with the with btn_id (id from oks_bill_transactions) as -44 for contract line ID (topline_id). This is the same when we suppress the credits for a termination of contract line.
• And finally there is a row created in the okc_k_rel_objs for the contract line id that is created. Billing program ignores these contract lines from being billed.

So we need to do this:

• Take care of the tables oks_bill_cont_lines and oks_bill_sub_lines.
• Take care of the table oks_level_elements (since date_completed is already populated).
• Take care of the table where related objects are populated (okc_k_rel_objs).
• And finally recreate the schedule so that the billing can happen every month for the period of the contract.

Created a simple package that takes the out values from the post_integration procedure of the API OKS_OMIB_INT_EXTNS_PUB and did all the above. Contract got billed!

That is it folks. You can download the code here. There is table script to capture the out variables of the API. Please note that this article provides you a guideline and not a complete solution!

This is the third one in the series of articles that I have been discussing on this topic. First we created Warranty Contract from Order Management, next was on  creating a Service Contract from OM. In this article, I will just walk you through technical details of the process of creating service contracts from order management (not warranties).

Proir to R12, ASO_ORDER_FEEDBACK_T (you can find an article here in this blog on the same) queue table was used to communicate order line information to CRM applications. Starting R12 the architecture is changed (from service contract creation perspective). Obviously ASO queue approach had issues. Once the order is booked, order lines were queued to be processed later by the respective subscribers. But once the queue is dequeued, and if there are any issues, the queue was lost and contract was never created. Sometimes IB was not updated as the customer product thought the order line is shipped or fulfilled and was stuck in the IB interface errors table due to some issues. Hence this IB is not visible for the program to create as covered product.

In R12 this queue is not used any more. Though the queue is still built when the order is booked, the queue is not used anymore for this process. But if you are using this process in 11i, all the existing queue details will be migrated to the new architecture when the upgrade happens from old to new architecture.

Going into the details, a new table is introduced to capture the eligible lines for service contract creation program: oks_reprocessing. When the order is shipped and fulfilled or just fulfilled, a record is created in this table for further processing. The record is created by fulfillement logic of the order management. So without fulfillment node in the order line workflow service contract cannot be created. From code perspective, oe_fulfill_wf.start_fulfillement called in the order line workflow will call oe_line_fullfill.process_fulfillement. This in turn calls oe_line_fulfill.fulfill_line. This is exactly where the row gets inserted into oks_reprocessing. Fulfilling process calls oks_omint_pub.interface_service_order_lines which inserts row into this table.

The row is inserted for all the service items in the order line. These lines are identified by the item_category_code in order lines which is stored as SERVICE. From implementation perspective, fulfillment is a must in the order line workflow.

Once the row is available, we run the program Service Contracts Order Processing in Service contracts to create service contract. The code behind this program oks_ocint_pub.order_reprocess. This uses this table as the base and creates contract if everything is good. If the contract cannot be created for some reason, the record is left in this table for processing at a later point of time.  A new screeen is provided to see the errored records for selecting and processing.

In an earlier article I discussed how to create a warranty contract from Order Management. In this article I am discussing how to sell service and how to create service contracts from order management. Presentation is here. This presentation lasts for about 7 mins.

We all buy different kinds of stuff all the time. Especially when we buy high ticketed items like a computer, we expect that the vendor cover us for free for some time so that we can get any issue fixed free of cost. This is called warranty. Warranty is always free for a customer. Of course vendor covers this cost of risk in product pricing, which is not explicitly told. This quick article tells you what you need to create a warranty contract in Oracle.

What we need to create a warranty?
You basically need one coverage, two items, one Bill of Material and a sales order to ship.

Required Item Attributes Setup

The process is pretty simple. Once these setups are done just create a sales order and ship it. You will instance is either updated (if that was in inventory before which you shipped it) or you just fulfill the order (without shipping) and IB is created and warranty is created.

Some FAQ
Where is the warranty item taken from to create a warranty contract?
We do not enter warranty item in the order. It automatically is taken from the BOM for warranty creation.
If the product is fulfilled in different organizations, can they have different warranty periods?
Yes, you probably need a patch. Log a service request with support.
Do we have to ship the order line in order to create instance?
No. You can just fulfill the order which creates the IB instance (you need to use a workflow that has creation of instance as part of the workflow).
If this is a serial controlled item without shipping how is IB updated?
Upon fulfillment, instance is created without serial number. Instance can be updated later with a serial number later. This is a practice in software industry where we have nothing to ship as the orders can be fulfilled online. If that is the case, once the license key is registered, IB can be updated.

This is second article in the series of articles on revenue-COGS matching. As discussed in the first article, with the introduction of deferred COGS accounting, COGS accounting does not take place unless revenue is recognized so that the revenue and COGS recognition happen in the same period. In this article let us discuss customer acceptance process in relation to the revenue COGS matching. In fact  introduction of deferred COGS functionality gave room for this feature.

What is Customer Acceptance and how it is related to Revenue – COGS matching?

Customer acceptance is a process where you cannot recognize revenue and COGS until customer has accepted the delivery. As we have discussed in the earlier article, starting R12, when goods are shipped always Deferred COGS account is debited (instead of actual COGS) irrespective of customer acceptance. As we could delay this recognition, now we can add this extra step of customer acceptance. The following is the table of revenue contingencies that are used in customer acceptance process. Of course we can create custom contingecies, but the removal events are always seeded. So these contigencies are more identified with the removal events rather than contingency name itself.

These contingencies are of three types:

1. Prevent creation of invoice if the customer does not accept the goods. But as soon the customer accepts the goods, order is interfaced to AR and invoice is created and revenue is immediately recognized. This is called pre-billing acceptance. The line status is placed in Pre-Billing Acceptance status and removal event is customer acceptance and invoice creation.
2. Create invoice but do not recognize revenue until customer accepts the goods. This is called post billing acceptance. Contingencies Explicit acceptance and Installation required, fall into this category. The line status is placed in Post-Billing Acceptance status. Once the customer accepts the goods, revenue is recognized immediately (invoice is already created) and line is closed.
3. The last one is called proof of delivery. The difference between this one and other two is that, this neither can be defaulted into the order line nor can be picked up manually in the LOV when the order line is created. If the order line attributes (like bill to customer or site, AR transaction type associated with order type or line type so on) match the assignment rules set up in for the revenue contingency, delviery contingency is applied when order line is invoiced. Unfortunately at this point of time there is no user interface to record the proof of delivery. This, I am sure on the way in the coming releases.  But there is a group API that can be used to remove this contigency and the sample code for the same is here.

Setup and Behavior

These contingencies can be defaulted into order line based on the setup. As shown in the associated table, value for the OM system parameter, Fulfillment Acceptace Required can be set as Yes. This allows acceptance name to be chosen from the LOV in the order lines when an order line is being created. These are hidden fields in the Others tab and folders can be used to open them up. But if contingency assignment rules are setup, say with bill to customer name parameter and value as Business World, and if the order is entered for the same customer as bill to, acceptance contingency automatically gets defaulted once the order line is saved ( this is delayed request in the order line creation and not part of the defaulting rules). The following the list of parameters that can be used to setup the assignment rules.

 In Revenue Management Super User, when these contingencies are assigned to a rule with any of the parameters as shown in the table, order creation process matches the values in the order with the setup rules. If the matching is found, then the contingency is defaulted into the order line. Even if there is no setup done, contingency can be manually assigned to the order line.

 Finally coming to the transactions, if acceptance exists, when the order line shipped line status is placed in one of the statuses mentioned above based on the type of contingency.

Acceptance can be performed by customers themselves or internally by the users. For external users to perform this step, these users should be registered against the customers (as person parties) and should be assigned with a responsibility called Order Information Super User. Once the goods have reached them, they can log in to this customer portal (iStore or iSupport) and accept or reject the goods. Also if they reject them, they can request for RMA. Rest of the process follows as discussed earlier from revenue accounting perspective. For the COGS recognition, the steps mentioned in the part I of this article should be followed.

Move Orders in Oracle provide good functionality for approval based movement of material with in an inventory organization. Move Order Approvals use a workflow (INV: Move Order Approval) for approvals and hence provide flexibility to extend it for approvals.

Move Orders can be created from different sources. These move orders are identified by Move order type at the header level. Different types of move orders are:

Move Order Requisitions: These are created from the move order form. Based on the setup at the organization and item level, move orders require approval before they can be allocated and transacted. These requisitions can be created to charge an account or move the material from one subinventory to another.

Replenishment Move Orders: These are auto-created with pre approved status. Depending on the planning type you can replenish the quantity in a subinventory for an item. Three types of replenishment plans (in relation to move orders) are available:

1. Min-max Replenishment Planning
2. Kanban Replenishment Planning
3. Replenishing Count planning

Based on these sources, when appropriate programs are run, move orders are automatically created.

Pick Wave Move Order Requisitions: These move orders are specifically for the sales order picking process. When Pick Releasing program is run move orders are created as preapproved and allocated. Pick Confirm process transacts these pick wave move orders.

Move orders contain headers and lines. All types of approved move orders have to be allocated (basically reserved) before they can be transacted. Depending on the item transaction controls (Subinventory, Locator, Revision, Lot or Serial number) move order creation and allocation gets complex.

The process is simple. Create and approve Move orders, allocate move order lines and transact them.

In this article let us review creating, allocating, and transacting a move order requisition of first type (requisitions) using APIs. These move orders will be created in pre approved status and hence do not use workflow. Move Order Issue transaction type is used in this example. An account is chosen while creating this move order. When successfully transacted, this account gets debited, crediting inventory account of the subinventory. From the user interface, to create Move orders, Move Orders window is used.

The lines are allocated when allocation step is performed. In this example the logic for allocation and transaction is borrowed from pick confirmation code. To allocate and transact same form is used: Transact Move Orders.

If the item is lot and serial number controlled (as in this example), you have to pass the lot number and serial number information into the move order lines table parameter. And also make sure to pass another parameter (p_suggess_serial) as true. This will automatically take care of allocating the lot and serial numbers into the appropriate table as mentioned in the process flow. Also when we allocate the move order from the user interface, the records in the form are created in the mtl_material_transactions_temp table with transaction_status as 2, which allows the user to change the values from user interface. But in order to perform transaction from SQL, you have to change this value to 3 so that the transaction manager can pick these records up. You find that logic in the allocating code.

The tables affected in each process step are mentioned in the process flow. Code for each step as well as complete code to create, allocate and transact can be downloaded as seen here.

As discussed in the earlier article Asset Tracking is all about two kinds of items: Depreciable and Normal Items. I already discussed normal items with simple inventory transactions earlier. Let us discuss depreciable items in this article. This is very peculiar situation where you will be receiving items into inventory and at the same time depreciating them by creating assets while it is sitting in your inventory.

Sounds odd but this is reality in a number of asset intensive industries. Take construction industry for example. All the equipment and material that is required to construct a plant or a building is typically stored on site. Here we should be able to keep track of inventory from planning perspective and at the same time some of the items while sitting in inventory on site. Asset tracking for depreciable items comes handy here.

The flow is very simple. You receive items against a purchase order and run one program which creates an asset for you in Fixed Assets. From then on the asset starts getting depreciated. But where it gets tricky is in accounting. Since we are receiving this into inventory as well as creating an asset in fixed asset, potentially we are counting the asset value twice. An asset value can be in inventory or assets not both. So how Oracle handles this? Also what happens if we dispose of or sell this asset? Let us examine the accounting entries for each transaction to understand this.

Take a case. Donald Drumps Construction Company is constructing a huge building in the suburbs of New York. A forklift was required to be purchased for the construction needs. This forklift is treated as an asset hence required to be created as asset. Hence a purchase order was raised and sent to the supplier to be delivered at the construction site. It costs $150,000 and the company uses standard costing.

Each construction site can be assigned a different organization or a Subinventory within an organization. A location should be created for this construction site in New York and assigned to this Subinventory. Important attributes in the item master to be enabled are shown here.

When the supplier sends forklift against this PO, someone on site receives into inventory. As soon it is received into inventory, IB gets created, marking the transaction (csi_transactions) ready to be processed for asset creation.

Accounting for PO Receipt transaction into Inventory is as shown here.

 Now as per the flow running the program Create Assets: Interface Inventory Transactions to Assets will interface the asset to the fixed assets taking this information from IB and inventory. Creation of assets also involves in an accounting entry as the asset value is increasing. So the accounting in this transaction is:

As you can see here, inventory account is used to credit, debiting the asset account taken from the asset category assigned to the item in the item master. So the inventory value goes down in balance sheet increasing the asset value from fixed asset. All this is happening at cost of the item. And the asset starts depreciating for the period of the project.

What happens after the building construction is done?

Out of a number of possibilities, two things can happen. This forklift can be moved to another site where construction is going on or simply after the project is successfully completed, this can be sold right from the site to another smaller construction company. If it is moved to another site (considered as Asset Move in Asset Tracking) depending on how the other site is configured in the system (different organization mapped to different asset book or same asset book), treatment in assets is different. But let us take the case where Mr. Drump is tired of this asset and would like to sell it off. Since quantity exists in inventory we can create a sales order and ship it. When you ship accounting is (assuming that he is selling at 60% of the cost as the equipment is already used):

 This is not good. As you can see we credited Inventory account twice. Once when we are creating asset and once shipping this forklift after usage. Also since we have shipped the asset to someone else, Mr. Drump should retire this asset from asset books by retiring the asset.

Asset Tracking handles this well. Inventory sends this shipping transaction message to IB which identifies the transaction to be eligible for asset retirement. Another program, called Interface Move Transactions to Assets is run to send the retirement message to Fixed Assets from IB. Asset retirement ensues.

But what about crediting the inventory account twice? To solve this issue there is another program we need to run to reverse the shipping accounting: Create Reversal GL entries For Inventory FA Items. This program identifies transactions that are eligible (rows in csi_transactions table with gl_interface_status_code flag set to 1) and inserts rows into gl_interface for importing into GL. So when you run this program, accounting is:

You can see that COGS account is not touched as revenue is involved and only Deferred COGS is touched. This might surprise you because, you thought, with the advent of Subledger Accounting, all transactions from sub-ledgers to GL flow through the Subledger Accounting application. Here is one accounting entry flowing into GL without Subledger accounting, making reconciliation harder for inventory.

Moreover this left credit balance in my deferred COGS account as you figured it out!
Points to consider:

• Don’t be surprised to see inventory account having the source of Assets when reconciling inventory to GL. Also accounting flows from Asset Tracking but with the source of Inventory (reversals).
• What happens to all those reports that are used to match the inventory value by quantity and inventory value in inventory account? The will not match here because quantity very well exists but the value in the account got credited elsewhere in assets and not in the inventory in of form of an issue.

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.