Item Instance Expire API

DECLARE

 l_record_status   varchar2(1);

 l_sv_status VARCHAR2(2000);

 l_msg_data VARCHAR2(2000);

 l_mesg                              varchar2(4000);

 l_mesg_len                          number;

 l_mesg_count   number;

 SUBTYPE instance_rec        IS     csi_datastructures_pub.instance_rec;

   SUBTYPE transaction_rec   IS     csi_datastructures_pub.transaction_rec;

   SUBTYPE id_tbl         IS     csi_datastructures_pub.id_tbl;

   SUBTYPE instance_query_rec      IS     csi_datastructures_pub.instance_query_rec;

   SUBTYPE party_query_rec      IS     csi_datastructures_pub.party_query_rec;

   SUBTYPE party_account_query_rec IS     csi_datastructures_pub.party_account_query_rec;

   SUBTYPE instance_header_tbl     IS     csi_datastructures_pub.instance_header_tbl;

   SUBTYPE extend_attrib_values_tbl   IS     csi_datastructures_pub.extend_attrib_values_tbl;

   SUBTYPE party_tbl         IS     csi_datastructures_pub.party_tbl;

   SUBTYPE party_account_tbl    IS     csi_datastructures_pub.party_account_tbl;

   SUBTYPE pricing_attribs_tbl     IS     csi_datastructures_pub.pricing_attribs_tbl;

   SUBTYPE organization_units_tbl  IS     csi_datastructures_pub.organization_units_tbl;

   SUBTYPE instance_asset_tbl      IS     csi_datastructures_pub.instance_asset_tbl;

  -- Get Item Instance parameters

  l_instance_query_rec instance_query_rec;

  l_party_query_rec     party_query_rec;

  l_account_query_rec     party_account_query_rec;

  l_instance_header_tbl   instance_header_tbl;

  -- Expire Item Instance parameters

  l_instance_rec        instance_rec;

  l_txn_rec              transaction_rec;

  l_instance_id_lst     id_tbl;

  l_return_status      VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;

  l_overall_status  VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;

  l_api_name         CONSTANT VARCHAR2(30)  := 'expire_item';

  l_api_version      CONSTANT NUMBER  := 1;

  l_msg_count          NUMBER      := FND_API.G_MISS_NUM;

  

  l_mtl_transaction_id  NUMBER;

  l_expire_date     DATE;

  l_max_trx_date    DATE;

BEGIN

 l_instance_query_rec.instance_id  := &p_instance_id;

 l_mtl_transaction_id := &p_mtl_transaction_id; -- can be null if no mtl transaction is available

 IF  l_mtl_transaction_id = 0 THEN

    l_mtl_transaction_id := NULL;

 END IF;

  csi_item_instance_pub.get_item_instances (

     p_api_version            => l_api_version,

     p_commit               => FND_API.G_FALSE,

     p_init_msg_list              => FND_API.G_FALSE,

     p_validation_level          => FND_API.G_VALID_LEVEL_FULL,

     p_instance_query_rec    => l_instance_query_rec,

     p_party_query_rec        => l_party_query_rec,

     p_account_query_rec         => l_account_query_rec,

     p_transaction_id         => NULL,

     p_resolve_id_columns    => FND_API.G_FALSE,

     p_active_instance_only     => FND_API.G_TRUE,

     x_instance_header_tbl      => l_instance_header_tbl,

     x_return_status              => l_return_status,

     x_msg_count               => l_msg_count,

     x_msg_data                => l_msg_data);

-- *************************************

-- Initialize parameters to be passed in

-- *************************************

  IF l_mtl_transaction_id is not null THEN

     select max(transaction_date)

     into l_expire_date

     from   mtl_material_transactions

     where transaction_id = l_mtl_transaction_id;

  ELSE

     l_expire_date := sysdate;

  END IF;

  

  select max(transaction_date)

  into l_max_trx_date

  from csi_inst_transactions_v

  where instance_id =  l_instance_query_rec.instance_id;

  

  IF l_expire_date < l_max_trx_date THEN

     l_expire_date := l_max_trx_date;

  END IF;

  

  l_instance_rec.instance_id    :=  l_instance_header_tbl(1).instance_id;

  l_instance_rec.object_version_number   :=

  l_instance_header_tbl(1).object_version_number;

  l_instance_rec.active_end_date   := l_expire_date;

  l_txn_rec.transaction_date := sysdate;

  l_txn_rec.source_transaction_date := l_expire_date;

  l_txn_rec.transaction_id := NULL;

  l_txn_rec.transaction_type_id := 1;

  if l_mtl_transaction_id is not null then

    l_txn_rec.inv_material_transaction_id := l_mtl_transaction_id;

  end if;

-- **************************************

-- Call Installed Base API to expire item

-- **************************************

  csi_item_instance_pub.expire_item_instance (

     p_api_version     => l_api_version,

     p_commit        => FND_API.G_FALSE,

     p_init_msg_list      => FND_API.G_FALSE,

     p_validation_level   => FND_API.G_VALID_LEVEL_FULL,

     p_instance_rec    => l_instance_rec,

     p_expire_children => FND_API.G_TRUE,

     p_txn_rec          => l_txn_rec,

     x_instance_id_lst => l_instance_id_lst,

     x_return_status      => l_return_status,

     x_msg_count        => l_msg_count,

     x_msg_data         => l_msg_data);

-- dbms_output.put_line('ret sts 2.. '||l_return_status);

  IF l_return_status = 'S' THEN

    dbms_output.put_line('instance '|| l_instance_rec.instance_id||' expired successfully');

-- **************************************

-- updating the IB error if there is one

-- **************************************

    IF l_mtl_transaction_id is not NULL THEN

      update csi_txn_errors

      set processed_flag = 'P',

          error_text = 'updated for datafix'||processed_flag

      where inv_material_transaction_id = l_mtl_transaction_id;

    END IF;

-- **************************************

-- Display errors encounted for the expiration

-- **************************************

  ELSE

    l_mesg_count := fnd_msg_pub.count_msg;

       if l_mesg_count > 0 then

          l_mesg := chr(10) || substr(fnd_msg_pub.get

                                      (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),

                                       1, 512);

          for i in 1..2 loop -- (l_mesg_count - 1) loop

             l_mesg := l_mesg || chr(10) ||

                         substr(fnd_msg_pub.get

                                (fnd_msg_pub.G_NEXT,

                                 fnd_api.G_FALSE), 1, 512);

          end loop;

          fnd_msg_pub.delete_msg();

          l_mesg_len := length(l_mesg);

          for i in 1..ceil(l_mesg_len/255) loop

                 dbms_output.put_line(substr(l_mesg, ((i*255)-254), 255));

          end loop;

       end if;

   END IF;

 end;

 /

 commit;

 /

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