Following are prerequistics before updating category to an Item.

  1. Organization must be defined in Oracle.
  2. Item must be defined.
  3. Category set name and Category Combination is Unique.
  4. Already Category set name and Category Combination Should be assigned to the Item. Already existing Item Category is to be replaced by New Category.
  4. New Category must be defined and it must be the part of category set name defined.

Below Code is tested in Oracle11i (11.5.10).


DECLARE

v_item                   VARCHAR2(100)  := 'NewItem';
v_cat_set_name      VARCHAR2(100)  := 'Inventory';
v_Update_category  VARCHAR2(50)   := 'NewCategory';
v_organization_id     NUMBER            := 101;  --Vision Operation is the Org Name
v_inv_item_id          NUMBER           := 0;
l_error                   VARCHAR2(1000):= NULL;
v_flag                   NUMBER            := 0;
v_category_id        NUMBER            := 0;
v_cat_set_id          NUMBER            := 0;
v_11i_cat_id          NUMBER            := 0;

BEGIN

       dbms_output.put_line('  ------Checking whether Item exists in Oracle 11i----------------');
       BEGIN
          SELECT inventory_item_id
          INTO   v_inv_item_id
          FROM   mtl_system_items_b msi
          WHERE  msi.organization_id   = v_organization_id
          AND    msi.segment1          = v_item;
         
       EXCEPTION
       WHEN OTHERS THEN
         l_error := 'Item : '||v_item||' -- not defined for the organization.';    
         dbms_output.put_line( l_error);
         v_flag := 1;
       END;
     
       dbms_output.put_line('  ------Checking Category Set exists in Oracle 11i----------------');
       BEGIN
          SELECT category_set_id
          INTO   v_cat_set_id
          FROM   mtl_category_sets
          WHERE  category_set_name = v_cat_set_name;
         
       EXCEPTION
          WHEN OTHERS THEN
         l_error := v_cat_set_name||' -- '||' does not exist in Oracle 11i.';
           dbms_output.put_line( l_error);
         v_flag := 1;          
       END;
      
        dbms_output.put_line('------Checking Category exists in Oracl 11i----------------');
       BEGIN
          SELECT category_id
          INTO   v_category_id
          FROM   mtl_categories mc,
                      mtl_category_sets mcs
          WHERE  mc.segment1 = v_Update_category  -- Incase category is combination of different segment then needs to check for all segment values
          AND    mc.structure_id =mcs.structure_id
          AND    mcs.category_set_name= v_cat_set_name;
         
       EXCEPTION
         WHEN OTHERS THEN
           l_error := 'Category '||v_Update_category||' does not exists in Oracle 11i.';
           dbms_output.put_line( l_error);
         v_flag := 1;
       END;    
      
         dbms_output.put_line('------Checking whether any Category is assigned to the Item----------');
       BEGIN
             SELECT category_id
             INTO   v_11i_cat_id
             FROM   mtl_item_categories
             WHERE  category_set_id   = v_cat_set_id
             AND    organization_id   = v_organization_id
             AND    inventory_item_id = v_inv_item_id;
         
       EXCEPTION
         WHEN OTHERS THEN
           l_error := 'There is no Category Set already existing for the Item. Hence cannot update the new Category. Need to Create new Category Set and Category Combination for the Item';
           dbms_output.put_line( l_error);
         v_flag := 1;
       END;           
      
       IF v_flag = 0 THEN
      
         INSERT INTO  MTL_ITEM_CATEGORIES_INTERFACE
                 ( INVENTORY_ITEM_ID
                  ,ORGANIZATION_ID
                  ,CATEGORY_SET_ID
                  ,CATEGORY_ID
                  ,OLD_CATEGORY_ID
                  ,REQUEST_ID
                  ,PROGRAM_APPLICATION_ID
                  ,PROGRAM_ID
                  ,PROGRAM_UPDATE_DATE
                  ,LAST_UPDATE_DATE
                  ,LAST_UPDATED_BY
                  ,CREATION_DATE
                  ,CREATED_BY
                  ,LAST_UPDATE_LOGIN
                  ,PROCESS_FLAG
                  ,TRANSACTION_TYPE
                  ,SET_PROCESS_ID
                 )
            VALUES
                 ( v_inv_item_id
                  ,v_organization_id
                  ,v_cat_set_id
                  ,v_category_id
                  ,v_11i_cat_id
                  ,NUll
                  ,NUll
                  ,NUll
                  ,NUll
                  ,SYSDATE
                  ,-1
                  ,SYSDATE
                  ,-1
                  ,-1
                  ,1              --Process_flag(always 1 - pending)
                  ,'UPDATE'
                  ,1             --set_process_id
                 );
             COMMIT;
                     
       END IF;
      
       BEGIN
         -- Calling Item Import Procedure to load data into base table. Click on the below Item_Import link to know about API used to Import Item
         Item_Import;        
        
       EXCEPTION
         WHEN OTHERS THEN
            dbms_output.put_line('Error in calling Import Program and error is '||SUBSTR(SQLERRM,1,200));
       END;
      
EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line('Error in assinging new category to Item and error is '||SUBSTR(SQLERRM,1,200));
END;
/