API to create subinventory in oracle apps r12

API to create sub inventory in oracle apps r12

In this post , we will be discuss about API to create sub inventory in oracle apps r12. As per standard we did not have any API and Interfaces to Create subinventory and to maintain the Item Information's in the subinventory . But We have one method using table MTL_ITEM_SUB_INVENTORIES. We can do directly insert in this Table to create and maintain the Item Information's in the subinventory. Please find below detail code API to create subinventory in oracle apps r12

 

PLSQL Script of API to create subinventory in oracle apps r12

 

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;