Item Category Assignment In Oracle Apps R12

 CREATE OR replace PROCEDURE Xx_apply_item_category(p_organization_id   NUMBER,                                                           p_category_set_name VARCHAR2,                                                         p_category_code     VARCHAR2,                                                         p_item_code         VARCHAR2,                                                         po_status OUT VARCHAR2,                                                               po_msg OUT VARCHAR2 )

AS
  l_msg_index_out     NUMBER;
  l_error_message     VARCHAR2(2000);
  l_error_code        VARCHAR2(2000);
  l_return_status     VARCHAR2(80);
  l_msg_count         NUMBER;
  l_msg_data          VARCHAR2(250);
  l_category_id       NUMBER;
  l_category_set_id   NUMBER;
  l_inventory_item_id NUMBER;
  l_organization_id   NUMBER;
  l_old_category_id   NUMBER;
  l_errm              VARCHAR2(4000);
  l_old_category      VARCHAR2(200);
BEGIN
  BEGIN
    SELECT mcs_tl.category_set_id,
           decode(control_level_disp,
                  'Org',p_organization_id,
                  <:MASTER_ORGANIZATION_ID>)organization_id
    INTO   l_category_set_id,
           l_organization_id
    FROM   mtl_category_sets_v mcs_tl
    WHERE  mcs_tl.category_set_name=p_category_set_name;
  
  EXCEPTION
  WHEN OTHERS THEN
    l_errm:=l_errm
    ||','
    ||'InvalidCategorySet';
  END;
  BEGIN
    SELECT mcb.category_id
    INTO   l_category_id
    FROM   mtl_categories_kfv mcb
    WHERE  mcb.concatenated_segments=p_category_code
    AND    mcb.structure_id=
           (
                  SELECT mcs.structure_id
                  FROM   mtl_category_sets_b mcs
                  WHERE  mcs.category_set_id=l_category_set_id);
  
  EXCEPTION
  WHEN OTHERS THEN
    l_errm:=l_errm
    ||','
    ||'InvalidCategoryCode';
  END;
  BEGIN
    SELECT inventory_item_id
    INTO   l_inventory_item_id
    FROM   mtl_system_items_b
    WHERE  segment1=p_item_code
    AND    organization_id=l_organization_id;
  
  EXCEPTION
  WHEN OTHERS THEN
    l_errm:=l_errm
    ||','
    ||'InvalidItemCode';
  END;
  BEGIN
    SELECT mcb.category_id,
           mcb.concatenated_segments
    INTO   l_old_category_id,
           l_old_category
    FROM   mtl_system_items_b msi ,
           mtl_item_categories mic ,
           mtl_categories_kfv mcb ,
           mtl_category_sets mcs
    WHERE  1=1
    AND    mic.inventory_item_id=msi.inventory_item_id
    AND    mic.organization_id=msi.organization_id
    AND    mic.category_id=mcb.category_id
    AND    mic.category_set_id=mcs.category_set_id
    AND    mcb.structure_id=mcs.structure_id
    AND    msi.inventory_item_id=l_inventory_item_id
    AND    msi.organization_id=l_organization_id
    AND    mcs.category_set_id=l_category_set_id;
  
  EXCEPTION
  WHEN OTHERS THEN
    l_old_category_id:=NULL;
  END;
  IF l_old_category=p_category_code THEN
    l_errm:=l_errm
    ||',Item already assigned to the category.';
  END IF;
  IF l_errm IS NULL THEN
    IF l_old_category_id IS NULL THEN
      inv_item_category_pub.Create_category_assignment(p_api_version=>1.0 ,
                                                       p_init_msg_list =>fnd_api.g_true ,
                                                       p_commit=>fnd_api.g_true ,
                                                       x_return_status=>l_return_status ,
                                                       x_errorcode=>l_error_code ,
                                                       x_msg_count=>l_msg_count ,
                                                       x_msg_data=>l_msg_data ,
                                                       p_category_id=>l_category_id ,
                                                       p_category_set_id=>l_category_set_id ,
                                                       p_inventory_item_id=>l_inventory_item_id ,
                                                       p_organization_id=>l_organization_id );
    ELSE
      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_true ,
                                                       x_return_status=>l_return_status ,
                                                       x_errorcode=>l_error_code ,
                                                       x_msg_count=>l_msg_count ,
                                                       x_msg_data=>l_msg_data ,
                                                       p_category_id=>l_category_id ,
                                                       p_category_set_id=>l_category_set_id ,
                                                       p_inventory_item_id=>l_inventory_item_id ,
                                                       p_organization_id=>l_organization_id ,
                                                       p_old_category_id=>l_old_category_id );
    END IF;
    IF l_return_status<>fnd_api.g_ret_sts_success THEN
      FOR i IN 1..l_msg_count
      LOOP
        apps.fnd_msg_pub.Get(p_msg_index=>i ,
                             p_encoded=>fnd_api.g_false ,
                             p_data=>l_msg_data ,
                             p_msg_index_out=>l_msg_index_out );
        IF l_error_message IS NULL THEN
          l_error_message:=Substr(l_msg_data,1,250);
        ELSE
          l_error_message:= l_error_message
          ||'/'
          ||Substr(l_msg_data,1,250);
        END IF;
      END LOOP;
      -- DBMS_OUTPUT.put_line('*****************************************');
      --  DBMS_OUTPUT.put_line('APIError:'||l_error_message);
      --  DBMS_OUTPUT.put_line('*****************************************');
      po_msg:=l_error_message;
      po_status:='E';
    ELSE
      po_status:='S';
      --   DBMS_OUTPUT.put_line('*****************************************');
      --   DBMS_OUTPUT.put_line('Created Category Assiginment from Item id:'||l_inventory_item_id||'Successfully');
      --   DBMS_OUTPUT.put_line('*****************************************');
    END IF;
  ELSE
    po_msg:=l_errm;
    po_status:='E';
  END IF;
EXCEPTION
WHEN OTHERS THEN
  po_msg:=SQLERRM;
  po_status:='E';
END xx_apply_item_category;

CREATE TABLE xx_items_temp
             (
                          item_code        VARCHAR2(50 byte),
                          inv_cat          VARCHAR2(240 byte),
                          category_flag    VARCHAR2(1 byte)
             );

DECLARE
  v_status VARCHAR2(10);
  v_msg    VARCHAR2(2000);
BEGIN
  FOR c1 IN
  (
         SELECT msi.inventory_item_id,
                msi.segment1 item_code,
                1            category_set_id,
                'Inventory'  category_set_name,
                mck.category_id,
                mck.concatenated_segments category_code,
                <organization_id>         organization_id
         FROM   mtl_system_items_b msi,
                mtl_categories_kfv mck ,
                xx_items_temp xi
         WHERE  1=1
         AND    xi.inv_cat=mck.concatenated_segments
         AND    xi.item_code=msi.segment1
         AND    msi.organization_id = <:MASTER_ORGANIZATION>
         AND    mck.structure_id=101
         AND    xi.category_flag='N'
  )
  LOOP
    v_status:=NULL;
    v_msg:=NULL;
    Xx_apply_item_category(c1.organization_id,c1.category_set_name, c1.category_code,c1.item_code, v_status, v_msg );
    IF v_status!='S' THEN
      dbms_output.Put_line('Error in Item Code:'
      ||c1.item_code);
    ELSE
      UPDATE xx_items_temp
      SET    category_flag='Y'
      WHERE  item_code=c1.item_code;
    
    END IF;
    COMMIT;
  END LOOP;
END;