Oracle Item Disabling Script

For Multiple Items:

-------------------

DECLARE

   --v_inventory_item_id          NUMBER;

   --v_organization_id            NUMBER;

   --v_segment1                   VARCHAR2(100);

   v_inventory_item_status_code VARCHAR2(100);

   x_inventory_item_id          NUMBER;

   x_organization_id            NUMBER;

   x_return_status              VARCHAR2 (300);

   x_msg_count                  NUMBER;

   x_msg_data                   VARCHAR2 (4000);

   

          cursor c1 is select organization_id,inventory_item_id,segment1 from mtl_system_items_b where inventory_item_id=45;

BEGIN

         

          v_inventory_item_status_code  :='Inactive';  -- Inactive for disabling all functions

for i in c1

loop

         apps.ego_item_pub.process_item

                      (p_api_version                 => 1.0,

                       p_init_msg_list               => 'T',

                       p_commit                      => 'T',

                       p_transaction_type            => 'UPDATE',

                       p_inventory_item_id           => i.inventory_item_id,

                       p_organization_id             => i.organization_id,

                       p_segment1                    => i.segment1  ,

                       p_Inventory_Item_Status_Code  => v_inventory_item_status_code,

                       x_inventory_item_id           => x_inventory_item_id,

                       x_organization_id             => x_organization_id,

                       x_return_status               => x_return_status,

                       x_msg_count                   => x_msg_count,

                       x_msg_data                    => x_msg_data

                      );

end loop;

          dbms_output.put_line(x_organization_id);

          dbms_output.put_line(x_return_status );

          dbms_output.put_line(x_inventory_item_id );

                     

                     

        if (x_return_status <> 's')

        then

               dbms_output.put_line( 'item attribute update api error'|| x_return_status );

        else

               dbms_output.put_line('item attribute update api success' || x_return_status);

        end if;

end;

Oracle API For Assign the Item to catalog

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE AssignItmToCat(p_segment1 IN VARCHAR2, p_category_set_name IN VARCHAR2, p_category_name IN VARCHAR2)

AS

        l_api_version    NUMBER := 1.0;

        l_init_msg_list     VARCHAR2(2) := FND_API.G_TRUE;

        l_commit     VARCHAR2(2) := FND_API.G_FALSE;

       

        l_category_id            NUMBER;

        l_category_set_id        NUMBER;

        l_transaction_type       VARCHAR2(20) := EGO_ITEM_PUB.G_TTYPE_CREATE;     

   

        x_message_list           Error_Handler.Error_Tbl_Type;

        x_return_status     VARCHAR2(2);

        x_msg_count      NUMBER := 0;

        x_msg_data               VARCHAR2(255);

        x_error_code             NUMBER;

   

    l_user_id     NUMBER := -1;

    l_resp_id     NUMBER := -1;

    l_application_id  NUMBER := -1;

        l_rowcnt     NUMBER := 1;

        l_user_name      VARCHAR2(30) := 'MGRPLM';

        l_resp_name      VARCHAR2(30) := 'EGO_DEVELOPMENT_MANAGER';   

        CURSOR csr_org_items IS

        SELECT inventory_item_id, organization_id

        FROM mtl_system_items_b

        WHERE segment1 like p_segment1;

BEGIN

    -- Get the user_id

    SELECT user_id

    INTO l_user_id

    FROM fnd_user

    WHERE user_name = l_user_name;

    -- Get the application_id and responsibility_id

    SELECT application_id, responsibility_id

    INTO l_application_id, l_resp_id

    FROM fnd_responsibility

    WHERE responsibility_key = l_resp_name;

    FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);  -- MGRPLM / Development Manager / EGO

    dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );

 

        SELECT category_set_id INTO l_category_set_id FROM mtl_category_sets

        WHERE category_set_name = p_category_set_name;  -- 'Product Family'

       

        SELECT category_id INTO l_category_id FROM mtl_categories_b

        WHERE segment1 = p_category_name; -- 'Consumer Goods'     

       

        -- call API to load Items

       DBMS_OUTPUT.PUT_LINE('====================================================');

       DBMS_OUTPUT.PUT_LINE('Calling EGO_ITEM_PUB.Process_Item_Cat_Assignment API');       

 

      FOR itm IN csr_org_items

      LOOP

              EGO_ITEM_PUB.Process_Item_Cat_Assignment

             (

                    l_api_version     

                  , l_init_msg_list   

                  , l_commit          

                  , l_category_id     

                  , l_category_set_id 

                  , itm.inventory_item_id

                  , itm.organization_id 

                  , l_transaction_type

                  , x_return_status   

                  , x_error_code       

                  , x_msg_count       

                  , x_msg_data        

             );

    

      END LOOP;

     

       DBMS_OUTPUT.PUT_LINE('==================================================');

       DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);

       IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN

          DBMS_OUTPUT.PUT_LINE('Error Message Count :'||x_msg_count);

          DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);

       END IF;

       DBMS_OUTPUT.PUT_LINE('=========================================');      

       

EXCEPTION

        WHEN OTHERS THEN

          DBMS_OUTPUT.PUT_LINE('Exception Occured :');

          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);

          DBMS_OUTPUT.PUT_LINE('========================================');

END;