Usual wisdom till R11i is that install base gets updated or created as customer product when we ship the product. This shipping must go through the inventory transaction (remember that we can ship no inventory items as well) in order for the IB to be updated or created. But to achieve the same for the order lines that do not go through shipping and inventory (like Bill Only lines with fulfillment), we used to extend Order line workflow (supported way) to plug in a function that sends a message to update or create instance. This used to be upon fulfillment.

Stating R12 this is a seeded functionality. The same function that is given to customize the workflow is added to the order line fulfillment code. Meaning all bill only lines (for that matter any line workflow that has fulfill node and item is IB trackable) automatically create or update the install base upon fulfillment. But again this is controlled by a profile option (OM: Automatically Interface Lines to IB on Fulfillment). You can control this behavior by setting this profile option value as Yes or No.

As we all know till recently we cannot change the item in IB once it is created (neither from UI nor from API) from any source (from IB UI or from Inventory). The reason is plain simple: too many dependencies. Serial number from inventory travels across from Inventory to IB when it is used in a transaction. Also if it is created in IB, it goes and sits in Inventory as well. Also counters are associated with inventory item and they get instantiated when IB is created for that item. This list is partial and goes on and on.

As you can see changing item associated with IB instance has some consequences. Nevertheless, people do make mistakes using wrong serial number when item is transacted and IB gets created or updated with that and subsequent issues ensue. Genuinely sometimes there is a need to change the item, if everything else seems OK.

Till R11i it is not possible and hence people have to do some creative work like issuing it out in inventory and receiving correct serial number or other methods like updating the serial number and expire it in IB and recreating it. Without question, this is a bit painful.

Now this is possible in R12. There is a new transaction type introduced for the application Complex Overhaul and Repair called Item Serial Change. If you use this transaction type in item instance update API, you can change item. But if you use CMRO product this transaction type can be used to execute a transaction to change the item. Of course, if there is a depot repair order open on this serial number, CMRO also cannot change the item. I am not fully clear on the business process that demands item change in that application, but if you have used it, please share. You can see a small presentation here and the script used is here.

 

We know that if the item is serial number controlled, until R11i, we can only create one instance at a time in the installed base UI. This is because the UI automatically makes the quantity field read only with the quantity 1 placed in there. Take a look at this screen shot for example.

 

Take a look at this R12 screen shot. Though the item is serial number controlled, now you can create multiple serial numbers at the the same time. But the condition is that all these serial numbers share the same attributes like ownership, location and so on.

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.

 

Installed Base is one of the products I have seen evolving with very little functionality from form based (11.5.3 and 4) to JSP based 11.5.6 with a lot of functionality. From mere tracking customer products (products that are shipped to customer only), it is redesigned to track life cycle of an instance. With that, integration of this product is expanded to a number of products with in the EBS.

This integration as you see here, tells you the importance this product in the enterprise applications foot print.

In this article, specifically I will be covering integration of this product with inventory transactions. The fact that this product tracks “life cycle” of an instance, we need to understand how an instance takes birth in the first place. An instance gets life in IB, when we receive a product into inventory from supplier (buying), customer (returning), making (WIP) or simply migrating from legacy applications to Oracle. Also an instance can be created in Installed Base directly (if the instance has a serial number, it gets created in Inventory into mtl_serial_numbers table immediately). As you might have guessed by now, you cannot create an instance that is in inventory manually in IB as what is in Inventory always comes from Inventory to IB and not from IB to Inventory. So what is created in IB manually is always out of Inventory.

An instance is always owned by an owner. If it is in inventory or deployed for internal use it is always owned by internal party and if it is deployed outside then the owner is the outside customer who owns it. When we say internally owned, an instance can be not only in Inventory but also can be deployed in Project or is sitting in WIP or even In-transit. If the instance is in any of these locations, owner of the instance is internal party (as you see in this screen shot highlighting Party Name).

Not all products are required to be tracked in Installed base. Companies usually track products that require service or need tracking because of financial value. Spare Parts and Supplies usually do not need any tracking. This process of tracking is controlled at the item creation process level using an item attribute called Installed Base Trackable. Only transactions of IB trackable items are published for processing. Every transaction performed in Inventory for these items, either creates or updates the IB depending on the nature of transaction.

These items can be serial and or lot controlled. IB captures all these attributes as well. An inventory transaction can have multiple quantities and hence can have multiple serial numbers. If a serialized item is received into inventory with a quantity of 10, against one inventory transaction, there will be 10 separated instances created for each of these serial numbers.

Once the item is received into Inventory, this can be sold to a customer or issued for a specific purpose within the organization. Transactions are performed in Inventory to move material. These transactions are used to synchronize the inventory with IB. In essence every transaction in inventory has to be synchronized with IB for IB trackable items.

Likewise as shown in integration diagram, lot of products send messages to IB to either create or update. If the message cannot be processed successfully, error is created in the csi_txn_errors table for that message. And all subsequent transactions are not processed until the first error is cleared.

Messaging architecture and SFM

SFM, an acronym for Service Fulfillment Manager, is simply a concurrent manager. The role of this manager is to dequeue the messages published by source application.

If you refer back to the Install Parameters screen, there is an option called SFM Bypass. This check box decides whether the message (a transaction coming from Inventory) needs to be queued for further processing or process it online. If you check this box (bypass SFM) you are going into online mode where a transaction from Inventory will directly call the IB code to update or create IB. It is a little expensive operation as there can be thousands of transactions in a day. On the other had if you choose to use SFM (check box unchecked), inventory transaction is queued in the form of a message to processed by SFM at a later stage. SFM processes these queues in the order it receives them so that no transactions are missed.

Technical Details

Here are some useful SQLs you can use to understand the relationship between inventory and IB.

---

SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_id = &your_transaction_id;

SELECT serial_number
FROM mtl_unit_transactions a, mtl_material_transactions b
WHERE a.transaction_id = b.transaction_id AND b.transaction_id = &trx_id_from_above
UNION
SELECT serial_number
FROM mtl_unit_transactions a, mtl_transaction_lot_numbers b, mtl_material_transactions c
WHERE a.transaction_id = b.serial_transaction_id AND b.transaction_id = c.transaction_id
AND c.transaction_id = &trx_id_from_above;

SELECT transaction_id
FROM csi_transactions
WHERE inv_material_transaction_id = &trx_id_from_above;

SELECT instance_id
FROM csi_item_instances_h
WHERE transaction_id = &csi_trx_id_from_above;–this is from csi_transactions

SELECT serial_number
FROM csi_item_instances
WHERE instance_id IN (SELECT instance_id
FROM csi_item_instances_h
WHERE transaction_id = &csi_trx_id_from_above);

SELECT instance_party_id
FROM csi_i_parties
WHERE isntance_id = &instance_id_from_above;

SELECT ip_account_id
FROM csi_ip_accounts
WHERE instance_party_id = &instance_party_id_from_above;

---

 

Here is the process flow and technical details of transaction. I am taking a simple case of miscellaneous transaction from inventory.

You will love this story. Footnoted.org reported today about Circuit City’s SEC filings. Simple summary is that as part of the officer evaluation program the company’s executive officers get to try and become familiar with the products that the company sells and can get to evaluate them over the period of 18 to 24 months. Which, in simple terms, means that they can keep the most expensive TV or any other gadget for that period for free in their homes. Of course there is a limit of $8,000 per officer. At the end of the term, they either must buy the product at the current value, or return it.

The company operates in the Speciality Retailers industry where the products’ life is short because of innovation in product technology. Products that the store carries depreciate so fast that at the end of this time period, these officers can get this product for free or if they return it, you or me probably end up buying it in a sale for about half the original price.

There are 26 officers as reported by Wall Street Journal (wsj.com) in the company. If everyone of them is “evaluating” this TV shown here (priced incidentlly at $7,999 a piece) company can potentially lock up about $208,000 in assets. How I wish I was one of those!

Coming to the point,when I saw this article , it occured to me that I found a good example to write about a feature of one of the Oracle’s products : Asset Tracking. As you might have guessed by now there are some unique characterstics about this process. It is the very same TV that Circuit City sells to the customer that needs to be in asset when given to executives. We should be able to track these assets but not have it in inventory. Also this asset should be depreciating over that period. Finally we should be able to achieve this without extending Oracle Applications.

Without the functionality of Asset Tracking we may have to extend the applications. I will leave that upto your imagination on how you can do that.

I first wrote an introductory article on Asset Tracking here. As explained in that article Asset Tracking is about two kinds of items : Depreciable and Normal Items. Depreciable items get created as assets as soon they are received into inventory where as Normal items will flow into Assets when they are deployed in the field or projects.

So, how will Circuit City (CC) get these products to the executive houses to be evaluated and track and depreciate them and, at the same time, can sell the same product to customers?. If there are only 26 officers, probably a spreadsheet can be used to manage these assets. But if this program is extended to director level, it will get a little out of control. This is where Asset Tracking helps you.

Basic high level process is as shown here. Entire flow uses the following products: Purchasing, Inventory, Installed Base, Asset Tracking, Order Management, Subledger Accounting and Fixed Assets. In this example I am sparing the executives by considering a cheaper product: a laptop not that expensive as TV. Assume it costs about $2,000 for CC.

As you understood by now, our aim is to create an asset for the products we are issuing to the officer’s house for evaluation.

So the first step is to get the house address and create a HZ location for that address. Also we know that Fixed Asset has a flexfield called Location Flexfield which is used to capture the location of the asset. We need to map these two so that Fixed Asset knows to keep that asset in that location where asset tracking is keeping it. This is done in the Asset Tracking or Installed Base responsibility.

Second step is to create an asset book and asset category and link between these two. These are created in the Fixed Assets. In this example I am using an existing asset book and asset category.

In third step we create a laptop Item in Inventory and associate the asset category that we just created above. Make sure to check installed base trackable and uncheck Create Asset attributes, under the service group. We give it a cost of $2,000 and have it created in the price list so that we can create a sales order.

As part of the fourth step either we create a PO or use miscellaneous receipt to get the inventory. I used Miscellaneous inventory in this example. Asset Tracking uses Installed base as the source. As inventory transactions pump the changes into installed base (Installed Base trackable flag), this transaction will creates or updates installed base for this transaction.

Now we have to send this product to our executive’s house. To achieve this I use a custom transaction called eIB issue to field location, where location is required. Officer’s house is our location. This transaction updates the installed base changing the location from warehouse to the officer’s house address. Optionally we can perform a transaction in Asset tracking application called In Service. This transaction updates the Operational status in the csi_item_instances table (installed base owned. Just recall the integration diagram in the asset tracking article). This is only for reporting purpose and has no bearing on asset creation.

As Intalled base’s owner is still Circuit City but the current address changed from store or warehouse’s address to is officer’s house address. That will help Circuit City to track it.

Now we need to create accounting for these inventory transactions. After cost manager costs these transactions in inventory, Create Accounting program can be run for these transaction in Cost Management subledger.

Finally Run the program Create Assets : Interface Inventory transactions to Oracle Assets. This creates records in mass additions interface table. Post mass additions and you should be able to see that asset tied back to the installed base.

This asset will be created in the asset book that is associated to the item’s asset category, uses inventory transaction date as Date Placed in Service, asset location is the location that we have mapped to officer’s house address.

This completes the asset creation. Running Create Accounting program for Assets Subledger to create accounting entires for the asset addition. From this day asset will start depreciating.

Now our officer “evaluates” (read “enjoys”) this asset for coming two years and at the end of that term either must buy or return it. I bet he will not buy it. Why would he buy when he can get another new one for free! Also the value asset depends on how we depreciated it. If it is returned, my guess is, there will be no value left in this product after two years.

When this product is returned after two years, we will do a receipt into inventory using miscellaneous receipt again. This transaction changes the location of installed base back the warehouse or the store where it is received into. Running Interface Move Transactions to Oracle Assets in Asset Tracking sends a retirement message to the Fixed Assets. When you run the Post Mass Retirements Program it will retire the asset.

This completes the asset life cycle.

Now you know why you get a 65″ that is originally priced at $8000 for $4000 after a year (for one year we never know that the product existed). I wish I knew this before. I would have never bought anything from that store.

BTW, as of 2/29/08 Circuit City’s balance sheet shows Property and Equipment assets worth $2,485,600 and other assets worth upto $132,458. But I do not know where officer’s products are sitting between these two buckets! Stock was lagging at $4.91.

Presentation of this process is here. You can find complete accounting entries for this transaction in the presentation. There seems to be a bug in functionality in accounting entires, which, I expect to have been fixed by now.

What is Asset Tracking?

Asset Tracking in R12 (formerly known as Enterprise Installed Base), provides the functionality of operational and financial tracking of assets that are deployed in the feild as well as that are in inventory.

Traditionally we  have been creating assets in Fixed Assets using the Procure to Pay business flow. These POs have destination type as expense and not as inventory. Once the PO is matched to Invoice, Mass Additions Create Program sends assets to Mass Additions interface.

On the other hand Asset Tracking can create assets that are purchased into Inventory. While still being in Inventory, assets are created in fixed assets and depreciated. Operationally these assets are tracked in this application using the Installed Base’s current location. It uses Installed Base functionality as backbone to track the location of the assets.

Features of Asset Tracking

Asset Tracking is all about two kinds of items : Depreciable Items and Non-depreciable items. Depreciable items are those that depreciate while still being in Inventory. Non-Depreciable items are those that are still tracked as assets and depreciated while being “deployed” in the field (issued to field location or a project).

Foremost requirement for tracking an asset in this application is that the item is Intalled Base trackable. This is an attribute maintained in the item master. Using the integration between Inventory and Installed Base, all depreciable items, are created as assets as soon they are received into inventory.

All transactions coming from inventory and deployment transactions of Asset Tracking are tracked in Installed Base against the instance of this asset. Also Instance is linked to the financial asset in the installed base.

This application tracks the asset life cycle. When a depreciable item is received into inventory, asset gets created, but when it is sold or issued out the asset gets retired. On the otherside non-depreciable items are created as assets only when they are deployed not when they are in Inventory.

Alternatively assets can be created in the Fixed Assets first and then created in Asset Tracking, which automatically ties the asset and instance in Installed Base.

Integration of Asset Tracking

Asset Tracking inherits all the integration that is out there for the Installed Base with other products. Asset Tracking integrates the operational aspects of the installed base with financial aspects of assets.