DECLARE
x_return_status VARCHAR2(1000);
x_errorcode NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2(1000);
x_msg_index_out NUMBER;
l_error_message VARCHAR2(4000);
l_category_id NUMBER := 7041; --DEFAULT.DEFAULT.DEFAULT
l_category_set_id NUMBER := 1100000061; --TLM_IS_Production_Scheduling
l_inventory_item_id NUMBER := 1008;
l_organization_id NUMBER := 116; --TIS
BEGIN
--Create Table Script--
/*
CREATE TABLE xxtlm_item_category_assign_stag (
item_id NUMBER,
item_code VARCHAR2(500),
organization_id NUMBER,
status VARCHAR2(500),
error_msg VARCHAR2(500)
);
*/
DELETE FROM xxtlm_item_category_assign_stag;
COMMIT;
--START Insert Item in the Stag table--
INSERT INTO xxtlm_item_category_assign_stag (
item_code,
item_id,
organization_id
)
SELECT
a.assembly_item item_code,
a.assembly_item_id item_id,
a.organization_id
FROM
xxtlm_trace_bom_v a
WHERE
a.organization_id = 116
AND EXISTS (
SELECT
item_type
FROM
mtl_system_items_b
WHERE
organization_id = 116
AND item_type <> 'ASM1'
AND inventory_item_id = a.assembly_item_id
)
UNION
SELECT
a.component_item item_code,
a.component_item_id item_id,
a.organization_id
FROM
xxtlm_trace_bom_v a
WHERE
organization_id = 116
AND EXISTS (
SELECT
item_type
FROM
mtl_system_items_b
WHERE
organization_id = 116
AND item_type <> 'ASM1'
AND inventory_item_id = a.component_item_id
);
COMMIT;
--END Insert Item in the Stag table--
--Get Category Set ID--
BEGIN
SELECT
category_set_id
INTO l_category_set_id
FROM
mtl_category_sets_tl
WHERE
category_set_name = 'TLM_IS_Production_Scheduling'; --CATEGORY_SET_ID-1100000061
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error while getting category set details '
|| substr(sqlerrm, 1, 200));
END;
--Get Category ID--
BEGIN
SELECT
category_id
INTO l_category_id
FROM
apps.mtl_categories_kfv
WHERE
concatenated_segments = 'DEFAULT.DEFAULT.DEFAULT'; --CATEGORY_ID=7041
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error while getting category details '
|| substr(sqlerrm, 1, 200));
END;
--Apps Initialization is available in another section. Use the below link to know in detail
fnd_global.apps_initialize(user_id => 3985, resp_id => 20634, resp_appl_id => 401);
FOR i IN (
SELECT
item_id,
organization_id
FROM
xxtlm_item_category_assign_stag
WHERE
item_id = 1008
) LOOP
l_error_message := NULL;
inv_item_category_pub.create_category_assignment(p_api_version => 1.0, p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_errorcode => x_errorcode,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_category_id => l_category_id,
p_category_set_id => l_category_set_id,
p_inventory_item_id => i.item_id,-- l_inventory_item_id,
p_organization_id => i.organization_id --l_organization_id
);
IF x_return_status = fnd_api.g_ret_sts_success THEN
UPDATE xxtlm_item_category_assign_stag
SET
status = 'S'
WHERE
item_id = i.item_id
AND organization_id = i.organization_id;
COMMIT;
dbms_output.put_line('Item Category Assignment using API is Successful');
ELSE
BEGIN
IF ( fnd_msg_pub.count_msg > 1 ) THEN
FOR k IN 1..fnd_msg_pub.count_msg LOOP
fnd_msg_pub.get(p_msg_index => k, p_encoded => 'F', p_data => x_msg_data,
p_msg_index_out => x_msg_index_out);
dbms_output.put_line('x_msg_data:= ' || x_msg_data);
IF x_msg_data IS NOT NULL THEN
l_error_message := l_error_message
|| '-'
|| x_msg_data;
END IF;
END LOOP;
ELSE
--Only one error
fnd_msg_pub.get(p_msg_index => 1, p_encoded => 'F', p_data => x_msg_data,
p_msg_index_out => x_msg_index_out);
l_error_message := x_msg_data;
END IF;
dbms_output.put_line('Error encountered by the API is ' || l_error_message);
ROLLBACK;
EXCEPTION
WHEN OTHERS THEN
l_error_message := sqlerrm;
dbms_output.put_line('Error encountered by the API is ' || l_error_message);
END;
UPDATE xxtlm_item_category_assign_stag
SET
status = 'E',
error_msg = l_error_message
WHERE
item_id = i.item_id
AND organization_id = i.organization_id;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in Assigning Category to an Item and error is '
|| substr(sqlerrm, 1, 200));
END;
/