Installed Base integration with Inventory

Refrence http://www.oraclebusinessapps.com/2008/06/12/installed-base-and-its-integration-with-inventory-in-depth/

Installed Base and its integration with Inventory – In Depth

12th June 2008, 02:48 pm by Nagamohan Uppara

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.

Category: Installed Base  |  Comment (RSS)  |  Trackback