API scripts to Create/Update/delete the Category Set/Category Assignment for a Item
/*****************************************************************************
Script to Create the Category for an item
*****************************************************************************/
DECLARE
l_msg_index_out NUMBER;
l_error_message VARCHAR2 (2000);
x_return_status VARCHAR2 (80);
x_error_code NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (250);
l_category_id NUMBER;
l_category_set_id NUMBER;
l_inventory_item_id NUMBER;
l_organization_id NUMBER;
BEGIN
SELECT mcs_tl.category_set_id
INTO l_category_set_id
FROM mtl_category_sets_tl mcs_tl
WHERE mcs_tl.category_set_name = '<category_set_name>';
SELECT mcb.category_id
INTO l_category_id
FROM mtl_categories_b mcb
WHERE mcb.segment1 = '<category_name>'
AND mcb.structure_id = (SELECT mcs.structure_id
FROM mtl_category_sets_b mcs
WHERE mcs.category_set_id = l_category_set_id);
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE organization_code = '<organization_name>';
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = '<Item_name>'
AND organization_id = l_organization_id;
inv_item_category_pub.create_category_assignment (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_true
, x_return_status => x_return_status
, x_errorcode => x_error_code
, 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 => l_inventory_item_id
, p_organization_id => l_organization_id
);
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. x_msg_count
LOOP
apps.fnd_msg_pub.get (p_msg_index => i
, p_encoded => fnd_api.g_false
, p_data => x_msg_data
, p_msg_index_out => l_msg_index_out
);
IF l_error_message IS NULL
THEN
l_error_message := SUBSTR (x_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
DBMS_OUTPUT.put_line ('*****************************************');
ELSE
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
DBMS_OUTPUT.put_line ('*****************************************');
END IF;
END;
/
/*****************************************************************************
Script to Update the Category for an item
*****************************************************************************/
DECLARE
l_msg_index_out NUMBER;
l_error_message VARCHAR2 (2000);
x_return_status VARCHAR2 (80);
x_error_code NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (250);
l_category_id NUMBER;
l_category_set_id NUMBER;
l_inventory_item_id NUMBER;
l_organization_id NUMBER;
l_old_category_id NUMBER;
BEGIN
SELECT mcs_tl.category_set_id
INTO l_category_set_id
FROM mtl_category_sets_tl mcs_tl
WHERE mcs_tl.category_set_name = '<category_set_name>';
SELECT mcb.category_id
INTO l_category_id
FROM mtl_categories_b mcb
WHERE mcb.segment1 = '<category_name>'
AND mcb.structure_id = (SELECT mcs_b.structure_id
FROM mtl_category_sets_b mcs_b
WHERE mcs_b.category_set_id = l_category_set_id);
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE organization_code = '<organization_id>';
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = '<Item_name>'
AND organization_id = l_organization_id;
SELECT mcb.category_id
INTO l_old_category_id
FROM mtl_system_items_b msi
, mtl_item_categories mic
, mtl_categories_b mcb
, mtl_category_sets mcs
WHERE 1 = 1
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mic.category_id = mcb.category_id
AND mic.category_set_id = mcs.category_set_id
AND mcb.structure_id = mcs.structure_id
AND msi.inventory_item_id = c_inventory_item_id
AND msi.organization_id = c_orgnaization_id
AND mcs.category_set_id = c_category_set_id;
inv_item_category_pub.update_category_assignment (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_true
, x_return_status => l_return_status
, x_errorcode => l_error_code
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_category_id => l_category_id
, p_category_set_id => l_category_set_id
, p_inventory_item_id => l_inventory_item_id
, p_organization_id => l_organization_id
, p_old_category_id => l_old_category_id
);
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. x_msg_count
LOOP
apps.fnd_msg_pub.get (p_msg_index => i
, p_encoded => fnd_api.g_false
, p_data => x_msg_data
, p_msg_index_out => l_msg_index_out
);
IF l_error_message IS NULL
THEN
l_error_message := SUBSTR (x_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
DBMS_OUTPUT.put_line ('*****************************************');
ELSE
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
DBMS_OUTPUT.put_line ('*****************************************');
END IF;
END;
/
/*****************************************************************************
Script to delete the categroy assignment for a item
*****************************************************************************/
DECLARE
l_msg_index_out NUMBER;
l_error_message VARCHAR2 (2000);
x_return_status VARCHAR2 (80);
x_error_code NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (250);
CURSOR c_get_item_categories
IS
SELECT msi.segment1
, msi.inventory_item_id
, mcs.category_set_id
, mcb.category_id
, msi.organization_id
FROM mtl_system_items_b msi
, mtl_item_categories mic
, mtl_categories_b mcb
, mtl_category_sets mcs
WHERE 1 = 1
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mic.category_id = mcb.category_id
AND mic.category_set_id = mcs.category_set_id
AND mcb.structure_id = mcs.structure_id
AND msi.inventory_item_id = c_inventory_item_id
AND msi.organization_id = c_orgnaization_id;
BEGIN
FOR c_get_item_categories_rec IN c_get_item_categories
LOOP
inv_item_category_pub.delete_category_assignment (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_true
, x_return_status => x_return_status
, x_errorcode => x_error_code
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_category_id => c_get_item_categories_rec.category_id
, p_category_set_id => c_get_item_categories_rec.category_set_id
, p_inventory_item_id => c_get_item_categories_rec.inventory_item_id
, p_organization_id => c_get_item_categories_rec.organization_id
);
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. x_msg_count
LOOP
apps.fnd_msg_pub.get (p_msg_index => i
, p_encoded => fnd_api.g_false
, p_data => x_msg_data
, p_msg_index_out => l_msg_index_out
);
IF l_error_message IS NULL
THEN
l_error_message := SUBSTR (x_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
DBMS_OUTPUT.put_line ('*****************************************');
ELSE
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ( 'Removed Category Assiginment from Item : '
|| c_get_item_categories_rec.segment1
|| ' Successfully'
);
DBMS_OUTPUT.put_line ('*****************************************');
END IF;
END LOOP;
END;
/
********************************************************************
Bulk Update script to Update the Category on Item
********************************************************************
DECLARE
CURSOR c_category
IS
SELECT mcb.category_id
, mcs.category_set_id
, msi.organization_id
, msi.inventory_item_id
, msi.segment1
FROM mtl_system_items_b msi
, mtl_item_categories mic
, mtl_categories_b mcb
, mtl_category_sets mcs
WHERE 1 = 1
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mic.category_id = mcb.category_id
AND mic.category_set_id = mcs.category_set_id
AND mcb.structure_id = mcs.structure_id
AND msi.segment1 = :p_segment1
OR msi.inventory_item_id = :p_inventory_item_id
AND msi.organization_id = :p_organization_id
AND mcs.category_set_id = :p_category_set_id
AND mcb.category_id = :p_category_id;
l_msg_index_out NUMBER;
l_error_message VARCHAR2(2000);
x_return_status VARCHAR2(80);
x_error_code NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2(250);
l_category_id NUMBER;
l_category_set_id NUMBER;
l_inventory_item_id NUMBER;
l_organization_id NUMBER;
l_old_category_id NUMBER;
BEGIN
fnd_global.apps_initialize( 12247, 20634, 401);
COMMIT;
FOR z IN c_category
LOOP
inv_item_category_pub.update_category_assignment(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_true
, x_return_status => x_return_status
, x_errorcode => x_error_code
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_category_id => :p_new_category_id --3372
, p_category_set_id => z.category_set_id
, p_inventory_item_id => z.inventory_item_id
, p_organization_id => z.organization_id
, p_old_category_id => z.category_id);
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. x_msg_count
LOOP
apps.fnd_msg_pub.get(
p_msg_index => i
, p_encoded => fnd_api.g_false
, p_data => x_msg_data
, p_msg_index_out => l_msg_index_out);
IF l_error_message IS NULL
THEN
l_error_message := SUBSTR( x_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message
|| ' /'
|| SUBSTR( x_msg_data, 1, 250);
END IF;
END LOOP;
DBMS_OUTPUT.put_line( '*****************************************');
DBMS_OUTPUT.put_line(
'API Error : '
|| l_error_message);
DBMS_OUTPUT.put_line( '*****************************************');
ELSE
DBMS_OUTPUT.put_line( '*****************************************');
DBMS_OUTPUT.put_line(
'Created Category Assiginment from Item id : '
|| l_inventory_item_id
|| ' Successfully');
DBMS_OUTPUT.put_line( '*****************************************');
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(
'UNEXP_ERROR IN MAIN : '
|| SUBSTR( SQLERRM, 1, 250));
END;
/
/*****************************************************************************
Script to Create the Category for an item
*****************************************************************************/
DECLARE
l_msg_index_out NUMBER;
l_error_message VARCHAR2 (2000);
x_return_status VARCHAR2 (80);
x_error_code NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (250);
l_category_id NUMBER;
l_category_set_id NUMBER;
l_inventory_item_id NUMBER;
l_organization_id NUMBER;
BEGIN
SELECT mcs_tl.category_set_id
INTO l_category_set_id
FROM mtl_category_sets_tl mcs_tl
WHERE mcs_tl.category_set_name = '<category_set_name>';
SELECT mcb.category_id
INTO l_category_id
FROM mtl_categories_b mcb
WHERE mcb.segment1 = '<category_name>'
AND mcb.structure_id = (SELECT mcs.structure_id
FROM mtl_category_sets_b mcs
WHERE mcs.category_set_id = l_category_set_id);
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE organization_code = '<organization_name>';
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = '<Item_name>'
AND organization_id = l_organization_id;
inv_item_category_pub.create_category_assignment (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_true
, x_return_status => x_return_status
, x_errorcode => x_error_code
, 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 => l_inventory_item_id
, p_organization_id => l_organization_id
);
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. x_msg_count
LOOP
apps.fnd_msg_pub.get (p_msg_index => i
, p_encoded => fnd_api.g_false
, p_data => x_msg_data
, p_msg_index_out => l_msg_index_out
);
IF l_error_message IS NULL
THEN
l_error_message := SUBSTR (x_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
DBMS_OUTPUT.put_line ('*****************************************');
ELSE
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
DBMS_OUTPUT.put_line ('*****************************************');
END IF;
END;
/
/*****************************************************************************
Script to Update the Category for an item
*****************************************************************************/
DECLARE
l_msg_index_out NUMBER;
l_error_message VARCHAR2 (2000);
x_return_status VARCHAR2 (80);
x_error_code NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (250);
l_category_id NUMBER;
l_category_set_id NUMBER;
l_inventory_item_id NUMBER;
l_organization_id NUMBER;
l_old_category_id NUMBER;
BEGIN
SELECT mcs_tl.category_set_id
INTO l_category_set_id
FROM mtl_category_sets_tl mcs_tl
WHERE mcs_tl.category_set_name = '<category_set_name>';
SELECT mcb.category_id
INTO l_category_id
FROM mtl_categories_b mcb
WHERE mcb.segment1 = '<category_name>'
AND mcb.structure_id = (SELECT mcs_b.structure_id
FROM mtl_category_sets_b mcs_b
WHERE mcs_b.category_set_id = l_category_set_id);
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE organization_code = '<organization_id>';
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = '<Item_name>'
AND organization_id = l_organization_id;
SELECT mcb.category_id
INTO l_old_category_id
FROM mtl_system_items_b msi
, mtl_item_categories mic
, mtl_categories_b mcb
, mtl_category_sets mcs
WHERE 1 = 1
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mic.category_id = mcb.category_id
AND mic.category_set_id = mcs.category_set_id
AND mcb.structure_id = mcs.structure_id
AND msi.inventory_item_id = c_inventory_item_id
AND msi.organization_id = c_orgnaization_id
AND mcs.category_set_id = c_category_set_id;
inv_item_category_pub.update_category_assignment (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_true
, x_return_status => l_return_status
, x_errorcode => l_error_code
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_category_id => l_category_id
, p_category_set_id => l_category_set_id
, p_inventory_item_id => l_inventory_item_id
, p_organization_id => l_organization_id
, p_old_category_id => l_old_category_id
);
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. x_msg_count
LOOP
apps.fnd_msg_pub.get (p_msg_index => i
, p_encoded => fnd_api.g_false
, p_data => x_msg_data
, p_msg_index_out => l_msg_index_out
);
IF l_error_message IS NULL
THEN
l_error_message := SUBSTR (x_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
DBMS_OUTPUT.put_line ('*****************************************');
ELSE
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
DBMS_OUTPUT.put_line ('*****************************************');
END IF;
END;
/
/*****************************************************************************
Script to delete the categroy assignment for a item
*****************************************************************************/
DECLARE
l_msg_index_out NUMBER;
l_error_message VARCHAR2 (2000);
x_return_status VARCHAR2 (80);
x_error_code NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (250);
CURSOR c_get_item_categories
IS
SELECT msi.segment1
, msi.inventory_item_id
, mcs.category_set_id
, mcb.category_id
, msi.organization_id
FROM mtl_system_items_b msi
, mtl_item_categories mic
, mtl_categories_b mcb
, mtl_category_sets mcs
WHERE 1 = 1
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mic.category_id = mcb.category_id
AND mic.category_set_id = mcs.category_set_id
AND mcb.structure_id = mcs.structure_id
AND msi.inventory_item_id = c_inventory_item_id
AND msi.organization_id = c_orgnaization_id;
BEGIN
FOR c_get_item_categories_rec IN c_get_item_categories
LOOP
inv_item_category_pub.delete_category_assignment (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_true
, x_return_status => x_return_status
, x_errorcode => x_error_code
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_category_id => c_get_item_categories_rec.category_id
, p_category_set_id => c_get_item_categories_rec.category_set_id
, p_inventory_item_id => c_get_item_categories_rec.inventory_item_id
, p_organization_id => c_get_item_categories_rec.organization_id
);
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. x_msg_count
LOOP
apps.fnd_msg_pub.get (p_msg_index => i
, p_encoded => fnd_api.g_false
, p_data => x_msg_data
, p_msg_index_out => l_msg_index_out
);
IF l_error_message IS NULL
THEN
l_error_message := SUBSTR (x_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
DBMS_OUTPUT.put_line ('*****************************************');
ELSE
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ( 'Removed Category Assiginment from Item : '
|| c_get_item_categories_rec.segment1
|| ' Successfully'
);
DBMS_OUTPUT.put_line ('*****************************************');
END IF;
END LOOP;
END;
/
********************************************************************
Bulk Update script to Update the Category on Item
********************************************************************
DECLARE
CURSOR c_category
IS
SELECT mcb.category_id
, mcs.category_set_id
, msi.organization_id
, msi.inventory_item_id
, msi.segment1
FROM mtl_system_items_b msi
, mtl_item_categories mic
, mtl_categories_b mcb
, mtl_category_sets mcs
WHERE 1 = 1
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mic.category_id = mcb.category_id
AND mic.category_set_id = mcs.category_set_id
AND mcb.structure_id = mcs.structure_id
AND msi.segment1 = :p_segment1
OR msi.inventory_item_id = :p_inventory_item_id
AND msi.organization_id = :p_organization_id
AND mcs.category_set_id = :p_category_set_id
AND mcb.category_id = :p_category_id;
l_msg_index_out NUMBER;
l_error_message VARCHAR2(2000);
x_return_status VARCHAR2(80);
x_error_code NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2(250);
l_category_id NUMBER;
l_category_set_id NUMBER;
l_inventory_item_id NUMBER;
l_organization_id NUMBER;
l_old_category_id NUMBER;
BEGIN
fnd_global.apps_initialize( 12247, 20634, 401);
COMMIT;
FOR z IN c_category
LOOP
inv_item_category_pub.update_category_assignment(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_true
, x_return_status => x_return_status
, x_errorcode => x_error_code
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_category_id => :p_new_category_id --3372
, p_category_set_id => z.category_set_id
, p_inventory_item_id => z.inventory_item_id
, p_organization_id => z.organization_id
, p_old_category_id => z.category_id);
IF x_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. x_msg_count
LOOP
apps.fnd_msg_pub.get(
p_msg_index => i
, p_encoded => fnd_api.g_false
, p_data => x_msg_data
, p_msg_index_out => l_msg_index_out);
IF l_error_message IS NULL
THEN
l_error_message := SUBSTR( x_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message
|| ' /'
|| SUBSTR( x_msg_data, 1, 250);
END IF;
END LOOP;
DBMS_OUTPUT.put_line( '*****************************************');
DBMS_OUTPUT.put_line(
'API Error : '
|| l_error_message);
DBMS_OUTPUT.put_line( '*****************************************');
ELSE
DBMS_OUTPUT.put_line( '*****************************************');
DBMS_OUTPUT.put_line(
'Created Category Assiginment from Item id : '
|| l_inventory_item_id
|| ' Successfully');
DBMS_OUTPUT.put_line( '*****************************************');
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(
'UNEXP_ERROR IN MAIN : '
|| SUBSTR( SQLERRM, 1, 250));
END;
/
No comments:
Post a Comment