Oracle Apps Knowledge Sharing

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;