Group Team
PREREQUISITE
-----------------
Following are prerequisite before updating category for an Item.

  1. Organization must be defined in Oracle.
  2. Item must be defined.
  3. Category set name and Category Combination Should already exists for the Item.
  4. New Category that needs to be assigned to the Item Should be the part of already existing category set name.

In Below example Item 'NewTestItem' is considered. Its inventory item id is 83456 and belong to 'Vision operation' Organization with Org id 106.
Category Set name 'Inventory' with category Set id '1' and Category 'NONE' with category id '5' is already assigned to the Item.
New Category 'TestCategory' with category id 1198 needs to be Updated to the Item.
New Category is part of the Category Set 'Inventory'.
Below Code can be used for Updating the Category set and Category Combination for the item 'NewTestItem'.
Code is tested in 11.5.10


DECLARE

X_RETURN_STATUS  VARCHAR2(1000);
X_ERRORCODE         NUMBER;
X_MSG_COUNT        NUMBER;
X_MSG_DATA          VARCHAR2(1000);
l_error_message       VARCHAR2 (1000) := NULL;
x_msg_index_out      NUMBER;
BEGIN
  --Apps Initialization is available in another section. Use the below link to know in detail
        Apps_Initialize;


   INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT
                                               P_API_VERSION            => 1.0, 
                                                   P_INIT_MSG_LIST         => FND_API.G_FALSE, 
                                                   P_COMMIT                   => FND_API.G_FALSE,
                                                   P_CATEGORY_ID           => 5,
                                                   P_OLD_CATEGORY_ID    => 1198
                                                   P_CATEGORY_SET_ID    => 1
                                                   P_INVENTORY_ITEM_ID  => 83456
                                                   P_ORGANIZATION_ID     => 106,
                                                   X_RETURN_STATUS       => X_RETURN_STATUS, 
                                                   X_ERRORCODE              => X_ERRORCODE, 
                                                   X_MSG_COUNT             => X_MSG_COUNT, 
                                                   X_MSG_DATA               => X_MSG_DATA);

    IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line ('Item Category Assignment using API is Successful');
   ELSE
      BEGIN
         IF (fnd_msg_pub.count_msg > 1)
         THEN
            FOR k IN 1 .. fnd_msg_pub.count_msg
            LOOP
               fnd_msg_pub.get (p_msg_index            => k,
                                             p_encoded            => 'F',
                                             p_data                 => x_msg_data,
                                             p_msg_index_out   => x_msg_index_out
                                            );

               DBMS_OUTPUT.PUT_LINE('x_msg_data:= ' || x_msg_data);
               IF x_msg_data IS NOT NULL
               THEN
                  l_error_message := l_error_message || '-' || x_msg_data;
               END IF;
            END LOOP;
         ELSE
            --Only one error
            fnd_msg_pub.get (p_msg_index             => 1,
                                           p_encoded            => 'F',
                                           p_data                 => x_msg_data,
                                           p_msg_index_out   => x_msg_index_out
                                         );
            l_error_message := x_msg_data;
         END IF;

         DBMS_OUTPUT.put_line (   'Error encountered by the API is '
                               || l_error_message
                              );
         ROLLBACK;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error_message := SQLERRM;
            DBMS_OUTPUT.put_line (   'Error encountered by the API is '
                                  || l_error_message
                                 );
      END;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
                   (   'Error in Assigning Category to an Item and error is '
                    || SUBSTR (SQLERRM, 1, 200)
                   );
END;
/
Group Team
PREREQUISITE
-------------------
Following are prerequisite before Assigning category to an Item.

  1. Organization must be defined in Oracle.
  2. Item must be defined.
  3. Category set name and Category Combination Should be defined.

In Below example Item 'NewTestItem' is considered. Its inventory item id is 83456 and belong to 'Vision operation' Organization with Org id 106.
Category Set name 'Inventory' with category Set id '1' and Category 'NONE' with category id '5' is needs to be assigned to the Item.
Below Code can be used for Assigning the Category set and Category Combination for the item 'NewTestItem'.
Code is tested in Oracel11i (11.5.10).

DECLARE

X_RETURN_STATUS  VARCHAR2(1000);
X_ERRORCODE         NUMBER;
X_MSG_COUNT        NUMBER;
X_MSG_DATA          VARCHAR2(1000);

BEGIN

  --Apps Initialization is available in another section. Use the below link to know in detail
        Apps_Initialize;

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT
                                               P_API_VERSION            => 1.0, 
                                                   P_INIT_MSG_LIST         => FND_API.G_FALSE, 
                                                   P_COMMIT                   => FND_API.G_FALSE, 
                                                   X_RETURN_STATUS       => X_RETURN_STATUS, 
                                                   X_ERRORCODE              => X_ERRORCODE, 
                                                   X_MSG_COUNT             => X_MSG_COUNT, 
                                                   X_MSG_DATA               => X_MSG_DATA, 
                                                   P_CATEGORY_ID           => 5
                                                   P_CATEGORY_SET_ID    => 1
                                                   P_INVENTORY_ITEM_ID  => 83456
                                                   P_ORGANIZATION_ID     => 106);

   IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line ('Item Category Assignment using API is Successful');
   ELSE
      BEGIN
         IF (fnd_msg_pub.count_msg > 1)
         THEN
            FOR k IN 1 .. fnd_msg_pub.count_msg
            LOOP
               fnd_msg_pub.get (p_msg_index            => k,
                                             p_encoded            => 'F',
                                             p_data                 => x_msg_data,
                                             p_msg_index_out   => x_msg_index_out
                                            );

               DBMS_OUTPUT.PUT_LINE('x_msg_data:= ' || x_msg_data);
               IF x_msg_data IS NOT NULL
               THEN
                  l_error_message := l_error_message || '-' || x_msg_data;
               END IF;
            END LOOP;
         ELSE
            --Only one error
            fnd_msg_pub.get (p_msg_index             => 1,
                                           p_encoded            => 'F',
                                           p_data                 => x_msg_data,
                                           p_msg_index_out   => x_msg_index_out
                                         );
            l_error_message := x_msg_data;
         END IF;

         DBMS_OUTPUT.put_line (   'Error encountered by the API is '
                               || l_error_message
                              );
         ROLLBACK;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error_message := SQLERRM;
            DBMS_OUTPUT.put_line (   'Error encountered by the API is '
                                  || l_error_message
                                 );
      END;
   END IF;

EXCEPTION
  WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('Error in Assigning Category to an Item and error is '||SUBSTR(SQLERRM,1,200));
END;
/
Group Team
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;
/
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;
/
Group Team
 
We can use the below code to Submit the Item Import Program using API.

Code is tested in 11i.


 CREATE OR REPLACE PROCEDURE Item_import
 IS

 v_phase               VARCHAR2(240);
 v_status              VARCHAR2(240);
 v_request_phase   VARCHAR2(240);
 v_request_status  VARCHAR2(240);
 v_finished            BOOLEAN;
 v_message          VARCHAR2(240);
 l_request_id         NUMBER;

 BEGIN
 
   --We need to apps initialize before calling the import program
   --Apps Initialize is explained in another section. Check below link to know in detail
          Apps Initialize;

      l_request_id := Fnd_Request.submit_request (
                                application   => 'INV',
                               program        => 'INCOIN',
                              description     => NULL,
                              start_time      => SYSDATE,
                              sub_request   => FALSE,
                              argument1     => 1,     --Mode to run this request(Insert new cost information only)
                              argument2     => 1,     --Group ID option (All)
                              argument3     => 1,     -- Group ID Dummy
                             argument4     => 1,
                             argument5     => 1,
                             argument6     => <Group ID>,     -- Group ID passed in Interface table
                             argument7     => <1 or 2>          -- 1 for Create and 2 for Update
                       );                  
      COMMIT;
    
      IF  ( l_request_id = 0 ) THEN
         dbms_output.put_line( 'Submission of Import failed ');
      END IF;
      -- Wait for request to run the import Program to Finish
      v_finished := fnd_concurrent.wait_for_request (request_id      => l_request_id,
                                                     interval        => 60,
                                                     max_wait      => 0,
                                                     phase          => v_phase,
                                                     status          => v_status,
                                                     dev_phase    => v_request_phase,
                                                     dev_status   => v_request_status,
                                                     message      => v_message);

      dbms_output.put_line('Request Phase : '|| v_request_phase );
      dbms_output.put_line('Request Status : ' || v_request_status );

      --Testing end status
      IF ( UPPER(v_request_status) = 'NORMAL') THEN
          dbms_output.put_line( 'Submission of Item Import is Success ');
      ELSE
          dbms_output.put_line( 'Submission of Item Import failed ');
      END IF;
    
 EXCEPTION
    WHEN OTHERS THEN
         dbms_output.put_line( 'Submission of Import failed ');
 END ;
 /
Group Team
Procedure apps_initialize can be used to apps initialize from backend.

It is tested in 11i.

CREATE OR REPLACE PROCEDURE apps_initialize
IS
v_responsibility_name VARCHAR2 (100) := 'Payables';
v_user_name            VARCHAR2 (100) := 'Oracle';
v_application_id         NUMBER := 0;
v_responsibility_id      NUMBER := 0;
v_user_id                 NUMBER := 0;
v_flag                      NUMBER := 0;
BEGIN
      --Getting Responsbility and Application Id
      BEGIN
           SELECT responsibility_id,
                      application_id
           INTO     v_responsibility_id,
                      v_application_id
           FROM   fnd_responsibility_tl
          WHERE  responsibility_name = v_responsibility_name
          AND LANGUAGE = USERENV ('LANG');
       EXCEPTION
          WHEN OTHERS
          THEN
                   DBMS_OUTPUT.put_line
                                ( 'Error in getting Responsibility information and error is '
                                 || SUBSTR (SQLERRM, 1, 200)
                                );
             v_flag := 1;
         END;
        ----Getting User Id
        BEGIN
             SELECT user_id
             INTO v_user_id
             FROM fnd_user
             WHERE user_name = v_user_name;
        EXCEPTION
            WHEN OTHERS
           THEN
                   DBMS_OUTPUT.put_line
                         ( 'Error in getting User information and error is '
                            || SUBSTR (SQLERRM, 1, 200)
                          );
                   v_flag := 1;
         END;

        IF v_flag = 0
        THEN
                fnd_global.apps_initialize (v_user_id,
                                                   v_responsibility_id,
                                                   v_application_id
                                                    );
        END IF;
EXCEPTION
    WHEN OTHERS
     THEN
            DBMS_OUTPUT.put_line
                   ( 'Error in procedure apps initialize and error is '
                     || SUBSTR (SQLERRM, 1, 200)
                    );
END;
/
Labels: 1 comments | edit post