Introduction:
This API will create Item categories in EBS.
SET SERVEROUTPUT ON;
DECLARE
v_return_status VARCHAR2(1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2(2000);
v_errorcode VARCHAR2(1000);
v_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
v_category_id NUMBER;
v_context VARCHAR2 (2);
l_STRUCTURE_ID number;
cursor c1 is
select distinct a.sourcing_category
from XXEIPL_ITEM_CROSS_REF_UPD_STG a
where 1=1
and not exists
( select mck.* from mtl_categories_kfv mck,MTL_CATEGORY_SETS mcs where
mck.STRUCTURE_ID=mcs.STRUCTURE_ID
and mcs.CATEGORY_SET_NAME=’XXX SOURCING CATEGORY SET’
and mck.CONCATENATED_SEGMENTS=sourcing_category
) ;
BEGIN
select STRUCTURE_ID into l_STRUCTURE_ID from MTL_CATEGORY_SETS where CATEGORY_SET_NAME=’XXX SOURCING CATEGORY SET’;
for i in c1
loop
v_category_rec := NULL;
v_category_rec.structure_id := l_STRUCTURE_ID;
v_category_rec.summary_flag := ‘N’;
v_category_rec.enabled_flag := ‘Y’;
v_category_rec.segment1 := i.sourcing_category; –‘XXX’;
v_category_rec.description := i.sourcing_category; –‘XXX’;
— 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_false,
x_return_status => v_return_status,
x_errorcode => v_errorcode,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_category_rec => v_category_rec,
x_category_id => v_category_id –returns category id
);
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line (‘Creation of Item Category is Successful : ‘||v_CATEGORY_ID);
ELSE
DBMS_OUTPUT.put_line (‘Creation of Item Category Failed with the error :’||v_ERRORCODE);
ROLLBACK;
FOR i IN 1 .. v_msg_count
LOOP
v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => ‘F’);
dbms_output.put_line( i|| ‘) ‘|| v_msg_data);
END LOOP;
END IF;
end loop;
END;
