Musings by Dr Aarti

Source

Consignment Inventory – A different approach to handle

“Consignment Inventory is inventory that is in the possession of the customer, but is still owned by the supplier. In other words, the supplier places some of his inventory in his customer’s possession (in their store or warehouse) and allows them to sell or consume directly from his stock. The customer purchases the inventory only after he has resold or consumed it. The key benefit to the customer should be obvious; he does not have to tie up his capital in inventory. This does not mean that there are no inventory carrying costs for the customer; he does still incur costs related to storing and managing the inventory.”

So the real question is how to handle this in Oracle Applications. Here in this article, imagine you are tasked to implement this solution for you customer who plays role of “supplier” in the above example. The basic requirements usually are:

1. Ability to capture a sales order so that the goods can be shipped to customer site with documentation
2. Inventory value of these products should still be seen in the supplier’s accounting books.
3. We should not be invoicing the customer at the time of shipping as it is still supplier’s inventory.
4. Optionally ability to track these assets (location details). If there is an issue with the product, we should be able to send a technician to fix it and capture the service history.
5. If the cutomer either buys it or resells it, we should be able to generate COGS and create an invoice for the customer thus generating revenue.
6. Also if this product that is in “display” and needs to be depreciated, we ahould be able to create this as a financial asset.

To achieve this, we used start the process flow using internal requisition. The reason why we use internal requisition is that we can autocreate internal sales order which can be shipped with documentation like any other shipment. This makes life of logistics easy, because, there is no difference between this order and any other sales order as far they are concerned.

In order to be able to capture this value in accounting books, usually we either create a subinventory or one inventory organization (or sometime even a locator under a common subinventory called Consignment) to represent a customer’s location. I think subinventory approach wins over organization. The reason for this is, cost of the item. If organization approach is used, we have to make sure to keep the cost current in that organization as well.

And when this internal sales order is shipped, material only moves from supplier’s warehouse to logical warehouse or subinventory (which represents customer’s location) depending the way it is setup. Since the material is in that subinventory or organization material value can be seen in the accounting books and quantity also can be tracked.

Earlier Installed Base lacked asset life tracking features. In the absence of such features we could never provide asset tracking (we know it is in subinventory but where is not known (address details)) functionality. Hence sending a field service technician is not easy when a service call is logged. For that matter, replacing that product takes significant amount of time to research. Regarding depreciation, it is even more difficult in those days.

So far all sounds good. But one issue here is creation of internal requisition to start with. A number of times I have seen that users were not happy with that. Think of it: it is the same department that enters normal sales orders, manages these consignment orders as well. Now when you tell them to enter an internal requisition for the consignment orders and not sales order, they tend to be put off. If there is no choice they have to gulp it.

But are there any choices to make the users life easy? So I thought what if users always create sales orders only and we take care of converting them into internal sales orders rather than other way around. With the goal of finding a way to convert any sales order into internal sales order, I started exploring. In no time,to my surprise,I found a way right under my nose.

There is a procedure called auto_create_internal_req in a database package called oe_delayed_requests_util (available even in 11.5.9). It takes order header id as a parameter and converts into internal sales by creating internal requisition and updating the order with source information. That is it. Kicked off writing small piece of code to test. After sometime code was ready (just assembled the code taking snippets from here and there). Guess what, the solution turned out to be a good one. I have written only pl/sql code here. This can be used to plug into workflow.

This is how the new process flow looks like after the code. As you can see in the absence such flow, picking and shipping processes may have to be extended to move the material into a subinventory rather than debiting the COGS at the time of shipping consignment material.

Sometimes there is a need to create an asset when the material is in possession of customer. This helps to capture depreciation till the customer actually buys it. Asset Tracking has capabilities to create assets for the items that are in inventory and also for normal items that are issued to either field locations or projects. This product features can be exploited to achieve this requirement (for this some extension may be required to be written).

 This piece of code does the following:

1. Creates a location with customer’s ship to address (taking it from salesorder header)
2. Creates a subinventory and assigns this location to this subinventory
3. Updates the customer’s ship to site with this location (so that we can create internal reqs for this address).
4. Creates a row in the po_location_associations_all (this is where the link between customer’s ship to site and HR location is established. Done automatically by the code which updates in step 3)
5. Creates an Approved Internal requisition and updates the order with the source details.

You can download the code here. I tested this in 11.5.9 as well as 12.0.4 and works pretty well.