Assign item to subinventory api

Assign item to subinventory api

In this post , We will discuss how we can assign the sub inventories to the inventory items in oracle apps. Here is the complete code of assign item to sub inventory api. As such oracle has not provided the api to assign item to subinventory but we can do this by directly insert the record in the base table mtl_item_sub_inventories.

 

Table uses for assign item to subinventory api

 

mtl_item_sub_inventories

 

DECLARE

l_item_id NUMBER;

l_item_count NUMBER;

l_vendor_id NUMBER := 0;

l_err_flag CHAR (1) := 'N';

l_organization_id NUMBER := 0;

m_exists varchar2 (1);

v_count number :=0;

  

CURSOR c IS SELECT SEGMENT1, SEGMENT2, INVENTORY_ITEM_ID, ORGANIZATION_ID

FROM apps.MTL_SYSTEM_ITEMS

where organization_id=:p_organization_id;

  

CURSOR sub_invs (p_organization_id IN NUMBER) IS

SELECT DISTINCT secondary_inventory_name FROM apps.mtl_secondary_inventories

WHERE organization_id=:p_organization_id;

 

 BEGIN

 l_item_count := 0;

 FOR r IN c LOOP

  l_item_id := NULL;

   l_vendor_id := NULL;

    l_err_flag := 'N';

     l_organization_id := NULL;

     IF (l_err_flag <> 'Y') THEN

     FOR r1 IN sub_invs (r.organization_id) LOOP

     BEGIN

     BEGIN

         SELECT count(*) INTO v_count FROM apps.mtl_item_sub_inventories

         WHERE inventory_item_id = r.inventory_item_id

         AND organization_id = r.organization_id

         AND secondary_inventory = r1.secondary_inventory_name;

          EXCEPTION WHEN NO_DATA_FOUND THEN

           INSERT INTO apps.mtl_item_sub_inventories

           ( inventory_item_id,

            organization_id,

            secondary_inventory,

             last_update_date,

             last_updated_by,

              creation_date,

               created_by,

                last_update_login,

                inventory_planning_code )

           VALUES (r.inventory_item_id,

            r.ORGANIZATION_ID,

            r1.secondary_inventory_name,

            SYSDATE, -1,

            SYSDATE,

             -1,

             -1,

             6);

               EXCEPTION WHEN OTHERS THEN

               NULL;

               END;

               END LOOP;

              END IF;

               END LOOP;

               COMMIT;

 END;