Through Concurrent Program create Category and Assign Category to Category Sets

Concurrent Program create Category and Assign Category to Category Sets.
In this concurrent program you need to pass the Category sets name and category.
First category is created in mtl_categories through API.
Second category is assigned category sets through API.
set echo on
set feedback on
CREATE OR REPLACE PACKAGE ajay_inv_util_pkg IS
PROCEDURE create_category ( errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
i_category_set IN VARCHAR2,
i_category_name IN VARCHAR2 );
END ajay_inv_util_pkg;
/
CREATE OR REPLACE PACKAGE BODY ajay_inv_util_pkg IS
PROCEDURE create_category ( errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
i_category_set IN VARCHAR2,
i_category_name IN VARCHAR2 )
IS
l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
o_return_status VARCHAR2(100);
o_errorcode VARCHAR2(500);
o_msg_count VARCHAR2(100);
o_msg_data VARCHAR2(1000);
xcategory_set_id NUMBER;
xcategory_id NUMBER;
xparent_category_id NUMBER;
v_count NUMBER;
BEGIN
fnd_file.put_line(fnd_file.log,'******************************************');
fnd_file.put_line(fnd_file.log,'Create Category Procedure Begins ');
fnd_file.put_line(fnd_file.log,'Parameter Entered !!!! ');
fnd_file.put_line(fnd_file.log,'Category Set Name: - 'i_category_set);
fnd_file.put_line(fnd_file.log,'Category Name: - 'i_category_name);
fnd_file.put_line(fnd_file.log,'******************************************');
-- Intialize the Parameters.
l_category_rec := NULL;
l_category_rec.segment1 := i_category_name;
l_category_rec.description := i_category_name; -- Get the Parameters Values to pass into API. BEGIN
SELECT category_set_id, structure_id, structure_name, 'Y', 'N'
INTO xcategory_set_id, l_category_rec.structure_id, l_category_rec.structure_code, l_category_rec.enabled_flag, l_category_rec.summary_flag
FROM mtl_category_sets_v
WHERE category_set_name = i_category_set;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xcategory_set_id := null;
l_category_rec.structure_id := null;
l_category_rec.structure_code := null;
l_category_rec.enabled_flag := null;
l_category_rec.summary_flag := null;
fnd_file.put_line(fnd_file.log, 'Category Set Name Already Not Defined: 'i_category_set); retcode :=1;
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'*****************'); -- Complete the program with error. retcode :=1;
errbuf := SUBSTR(SQLERRM,1,50)SQLCODE;
fnd_file.put_line(fnd_file.log,'Problem finding Catg_set: 'errbuf);
END;---- After the category record is loaded, then call the create_category api to-- create the new mtl_categories record. -----------------------------------------------------------------------------
-- 1. Create_Category ----------------------------------------------------------------------------/*# * Use this API to create a category. */
BEGIN
select count(segment1)
INTO v_count
from mtl_categories
where structure_id = l_category_rec.structure_id
and segment1 = l_category_rec.segment1;END;
IF v_count = 0 THEN-- Print the Parameters entered by User for this Report.
fnd_file.put_line(fnd_file.log,'******************************************');
fnd_file.put_line(fnd_file.log,'Passing Parameters Values into API Inv_Item_Category_Pub.Create_Category Begins ');
fnd_file.put_line(fnd_file.log,'Structure ID: - 'l_category_rec.structure_id);
fnd_file.put_line(fnd_file.log,'Structure Code: - 'l_category_rec.structure_code);
fnd_file.put_line(fnd_file.log,'Description: - 'l_category_rec.description);
fnd_file.put_line(fnd_file.log,'Summary Flag: - 'l_category_rec.summary_flag);
fnd_file.put_line(fnd_file.log,'Enabled Flag: - 'l_category_rec.enabled_flag);
fnd_file.put_line(fnd_file.log,'******************************************');
Inv_Item_Category_Pub.Create_Category ( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => o_return_status,
x_errorcode => o_errorcode,
x_msg_count => o_msg_count,
x_msg_data => o_msg_data,
p_category_rec => l_category_rec,
x_category_id => xcategory_id );
IF o_return_Status = 'S' THEN
fnd_file.put_line(fnd_file.log,'*****************');
fnd_file.put_line(fnd_file.log, 'Category successfully created. New Category Id = 'xcategory_id); fnd_file.put_line(fnd_file.log, 'Segment Combination = 'l_category_rec.segment1'.'); fnd_file.put_line(fnd_file.log,'*****************'); retcode :=0; -- API to create a valid Category in Category Sets ----------------------------------------------------------------------------- /*# * Use this API to for assigning a category to a category set. A category will be available * in the list of valid categoies for a category set only if it is assigned to the category set. * @param p_category_id contains the category id of the category being created. * @param p_category_set_id identifies the category set in which the category is to be created. * @param p_parent_category_id if NULL then this category becomes a first level child in the category set. * @rep:displayname Assign a Category to a Category Set. * @rep:scope public */
-- Print the Parameters entered by User for this Report.fnd_file.put_line(fnd_file.log,'******************************************');
fnd_file.put_line(fnd_file.log,'Passing Parameters Values into API Inv_Item_Category_Pub.Create_Valid_Category ');
fnd_file.put_line(fnd_file.log,'Category Set ID: - 'xcategory_set_id);
fnd_file.put_line(fnd_file.log,'Category ID: - 'xcategory_id);
fnd_file.put_line(fnd_file.log,'Parent Category ID: - ''null');fnd_file.put_line(fnd_file.log,'******************************************');
Inv_Item_Category_Pub.Create_Valid_Category ( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
p_category_set_id => xcategory_set_id,
p_category_id => xcategory_id,
p_parent_category_id => xparent_category_id,
x_return_status => o_return_status,
x_errorcode => o_errorcode,
x_msg_count => o_msg_count,
x_msg_data => o_msg_data );
IF o_return_Status = 'S' THEN
fnd_file.put_line(fnd_file.log,'*****************');
fnd_file.put_line(fnd_file.log, 'Category successfully added to Category Set.');
fnd_file.put_line(fnd_file.log, 'Category Set Name : 'i_category_set'.');
fnd_file.put_line(fnd_file.log, 'Category Name : 'i_category_name'.');
fnd_file.put_line(fnd_file.log,'*****************'); ELSE fnd_file.put_line(fnd_file.log,'*****************');
fnd_file.put_line(fnd_file.log,'#API Error while adding Category to Category Set'); fnd_file.put_line(fnd_file.log, 'Category Set Name = 'i_category_set'.');
fnd_file.put_line(fnd_file.log, 'Category Name = 'i_category_name'.');
IF o_msg_count > 0 THEN FOR i IN 1 .. o_msg_count
LOOP
fnd_file.put_line(fnd_file.log, i '.' SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
END LOOP;
END IF;
fnd_file.put_line(fnd_file.log,'*****************');
fnd_file.put_line(fnd_file.log,'*****************'); -- Complete the program with error. retcode :=1;
END IF;
ELSE fnd_file.put_line(fnd_file.log,'*****************');
fnd_file.put_line(fnd_file.log,'#API Error while creating Category');
fnd_file.put_line(fnd_file.log, 'Segment Combination = 'l_category_rec.segment1'.');
IF o_msg_count > 0 THEN
FOR i IN 1 .. o_msg_count
LOOP
fnd_file.put_line(fnd_file.log, i '.' SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255));
END LOOP;
END IF;
fnd_file.put_line(fnd_file.log,'*****************'); -- Complete the program with error. retcode :=1;
END IF;
ELSE
fnd_file.put_line(fnd_file.log,'******************************************');
fnd_file.put_line(fnd_file.log, 'Do not call API Inv_Item_Category_Pub.Create_Category Begins ');
fnd_file.put_line(fnd_file.log, 'The category already exist for structure ');fnd_file.put_line(fnd_file.log,'******************************************');
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'*****************');
fnd_file.put_line(fnd_file.log, 'Issue while in ajay_inv_util_pkg.Create_Category');
fnd_file.put_line(fnd_file.log,'*****************'); -- Complete the program with error. retcode :=SQLCODE; errbuf := SUBSTR(SQLERRM,1,50); FND_FILE.PUT_LINE(FND_FILE.LOG,retcode': 'errbuf);
END create_category;
END ajay_inv_util_pkg;
/
EXIT