Group Team
Prerequistics
-------------
Following needs to be defined before assigning Item to a Category

  1. Organization must be defined.
  2. Item must be defined.
  3. Category set must exists.
  4. Category must be defined and it must be the part of category set name defined.

Below Code is tested in Oracle 11i (11.5.10).


DECLARE

v_item                       VARCHAR2(100)  := 'NewItem';
v_cat_set_name          VARCHAR2(100)    := 'Inventory';
v_category_segment1  VARCHAR2(50)      := 'NONE';
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;

BEGIN

       dbms_output.put_line('  ------Checking whether Item exists in Oracl 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 ITD 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 Oracle 11i----------------');
       BEGIN
          SELECT category_id
          INTO   v_category_id
          FROM   mtl_categories mc,
                     mtl_category_sets mcs
          WHERE  mc.segment1 = v_category_segment1  -- 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_category_segment1||' does not exists in Oracle 11i.';
           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
                ,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
               ,NUll
               ,NUll
               ,NUll
               ,NUll
               ,SYSDATE
               ,-1
               ,SYSDATE
               ,-1
               ,-1
               ,1              --Process_flag(always 1 - pending)
               ,'CREATE'
               ,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;
/
1 Response
  1. Sridevi K Says:

    Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.


Post a Comment