CREATE OR REPLACE PROCEDURE APPS.xx_create_new_item_category
IS
l_category_rec inv_item_category_pub.category_rec_type;
/*
Query to check results
SELECT M1.SEGMENT1||'.'||M1.SEGMENT2||'.'||M1.SEGMENT3||'.'||M1.SEGMENT4||'.'||M1.SEGMENT5||'.'||M1.SEGMENT6
FROM MTL_CATEGORIES_B M1,
MTL_CATEGORIES_TL M2
WHERE M1.CATEGORY_ID = M2.CATEGORY_ID
AND M2.LANGUAGE = 'US'


Table Structure
CREATE TABLE XX_CREATE_NEW_CATEGORY
(
STRUCTURE_ID NUMBER,
SEGMENT1 VARCHAR2(2000 BYTE),
SEGMENT2 VARCHAR2(2000 BYTE),
SEGMENT3 VARCHAR2(2000 BYTE),
SEGMENT4 VARCHAR2(2000 BYTE),
SEGMENT5 VARCHAR2(2000 BYTE),
SEGMENT6 VARCHAR2(2000 BYTE)
)

*/
CURSOR c_cat
IS
SELECT *
FROM xx_create_new_category;

l_api_version NUMBER;
o_return_status VARCHAR2 (2000);
o_msg_count NUMBER;
o_msg_data VARCHAR2 (2000);
o_errorcode VARCHAR2 (2000);
l_error_code VARCHAR2 (2000);
l_error_desc VARCHAR2 (2000);
l_conv_status VARCHAR2 (2000);
v_category_id NUMBER;


BEGIN
fnd_global.apps_initialize (1130, 50634, 401);

FOR i IN c_cat
LOOP
l_category_rec := NULL;
l_category_rec.structure_id := i.structure_id; -- inventory super user > setup > items > categories > Query The Category Set > get the Struture id from Examine
-- l_category_rec.structure_code := g_cat_flex_code;
l_category_rec.summary_flag := 'N';
l_category_rec.enabled_flag := 'Y';
l_category_rec.segment1 := TO_CHAR (i.segment1);
l_category_rec.segment2 := TO_CHAR (i.segment2);
l_category_rec.segment3 := TO_CHAR (i.segment3);
l_category_rec.segment4 := TO_CHAR (i.segment4);
l_category_rec.segment5 := TO_CHAR (i.segment5);
l_category_rec.segment6 := TO_CHAR (i.segment6);
--
-- After the category record is loaded, then call the create_category api to
-- create the new mtl_categories record.
inv_item_category_pub.create_category
(p_api_version => 1.0,
--l_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_true,
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 => v_category_id
); --l_category_rec.category_id);

IF o_return_status = 'S'
THEN
DBMS_OUTPUT.put_line ('*****************');
DBMS_OUTPUT.put_line ( 'Category successfully created. New Category Id = '|| v_category_id);
DBMS_OUTPUT.put_line ( 'Segment Combination = '|| i.segment1|| '.'|| i.segment2|| '.'|| i.segment3|| '.'|| i.segment4|| '.'|| i.segment5|| '.'|| i.segment6);
DBMS_OUTPUT.put_line ('*****************');
ELSE
l_error_code := NVL (l_error_code, '') || 'CATCONV005,';
l_error_desc := NVL (l_error_desc, '') || '#API Error while creating Category';
l_conv_status := 'ERROR';
DBMS_OUTPUT.put_line ('API STATUS : ' || o_return_status);
DBMS_OUTPUT.put_line ( 'Segment Combination = '|| i.segment1|| '.'|| i.segment2|| '.'|| i.segment3|| '.'|| i.segment4|| '.'|| i.segment5
|| '.'|| i.segment6|| SQLERRM);

IF o_msg_count > 0
THEN
DBMS_OUTPUT.put_line (o_errorcode);
DBMS_OUTPUT.put_line (o_msg_data);

FOR i IN 1 .. o_msg_count
LOOP
DBMS_OUTPUT.put_line
( i|| '.'|| SUBSTR(fnd_msg_pub.get (p_encoded => fnd_api.g_false),1,255));
END LOOP;
END IF;
END IF;
END LOOP;
END;
/

begin
xx_create_new_item_category;
end;
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply