Friday, April 5, 2013

Script to create an Item category and assign it to Item Category Set (inv_item_category_pub)














SET SERVEROUTPUT ON;
DECLARE
  l_structure_id   NUMBER;
  lv_cate_set_name VARCHAR2(50) := 'Test Item Category';
  l_concat_segs    VARCHAR2(40) := 'TEST1';

  l_return_status VARCHAR2(1) := NULL;
  l_msg_count     NUMBER := 0;
  l_msg_data      VARCHAR2(2000);
  l_errorcode     VARCHAR2(1000);
  l_category_rec  inv_item_category_pub.category_rec_type;
  l_category_id   NUMBER;
  l_cate_set_id   NUMBER;
  l_parent_category_id NUMBER;
BEGIN

  BEGIN
    SELECT structure_id,
           category_set_id
      INTO l_structure_id,
           l_cate_set_id
      FROM mtl_category_sets_v mc
     WHERE mc.category_set_name = lv_cate_set_name;
  EXCEPTION
    WHEN OTHERS THEN
      l_structure_id := NULL;
      l_cate_set_id    := NULL;
      dbms_output.put_line('Unable to fetch the category set details :' || SQLERRM);
      RAISE;
  END;

  SELECT category_id
    INTO l_category_id
    FROM mtl_categories_v
   WHERE structure_id = l_structure_id
     AND category_concat_segs = l_concat_segs;

EXCEPTION
  WHEN no_data_found THEN
    l_category_rec              := NULL;
    l_category_rec.structure_id := l_structure_id;
    l_category_rec.summary_flag := 'N';
    l_category_rec.enabled_flag := 'Y';
    l_category_rec.segment1     := l_concat_segs;
    -- Calling the api to create category --
    inv_item_category_pub.create_category
	            (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_errorcode,
                 x_msg_count     => l_msg_count,
                 x_msg_data      => l_msg_data,
                 p_category_rec  => l_category_rec,
                 x_category_id   => l_category_id);
    IF l_return_status <> fnd_api.g_ret_sts_success
    THEN
      tcs_sys_common_pkg.write_log_msg('Creation of Item Category Failed with the error :' || l_errorcode);
      FOR i IN 1 .. l_msg_count
      LOOP
        l_msg_data := oe_msg_pub.get(p_msg_index => i,
                                     p_encoded   => 'F');
        dbms_output.put_line(i || ') ' || l_msg_data);
      END LOOP;
    ELSE
      SELECT category_id
        INTO l_category_id
        FROM mtl_categories_v
       WHERE structure_id = l_structure_id
         AND category_concat_segs = l_concat_segs;
    
      dbms_output.put_line('Category Id: ' || l_category_id);
    END IF;
  
  -- Create Valid Category
  inv_item_category_pub.create_valid_category
                  (p_api_version        => 1.0,
                   p_init_msg_list      => fnd_api.g_true,
                   p_commit             => fnd_api.g_true,
                   p_category_set_id    => l_cate_set_id,
                   p_category_id        => l_category_id,
                   p_parent_category_id => l_parent_category_id,
                   x_return_status      => l_return_status,
                   x_errorcode          => l_errorcode,
                   x_msg_count          => l_msg_count,
                   x_msg_data           => l_msg_data);
      IF l_return_status = fnd_api.g_ret_sts_success
      THEN
             
        dbms_output.put_line('Assignment of category is sucessful');
    
      ELSE
        dbms_output.put_line('Assignment of category ' ||
                                         l_concat_segs ||
                                         ' falied with the error ' ||
                                         l_msg_data);
        ROLLBACK;
        FOR i IN 1 .. l_msg_count
        LOOP
          l_msg_data := oe_msg_pub.get(p_msg_index => i,
                                       p_encoded   => 'F');
          dbms_output.put_line(i || ') ' || l_msg_data);
        END LOOP;  
    END IF;
  
END;

0 Responses to “Script to create an Item category and assign it to Item Category Set (inv_item_category_pub)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.