API to Update the Item Categories in Oracle EBS
Introduction
In Oracle E-Business Suite (EBS), item categories play a crucial role in organizing and classifying items for inventory, costing, and reporting purposes.
Many businesses face the requirement to update existing item category assignments when new category values are introduced. Doing this manually through the front end for hundreds or thousands of items can be time-consuming and error-prone.
To address this, we can leverage the Oracle Public API – INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT to update categories programmatically.
This blog explains how to use the API to update item category assignments, along with a sample PL/SQL block.
Use Case
A customer wanted to update new category values to already assigned item categories in bulk. Instead of updating each item through the Item Categories form, we designed a PL/SQL program that:
Reads the new category values from a staging table (xxxx_item_master_upd_temp).
Validates the old and new categories.
Calls the Oracle API to update the assignment.
Logs the success/failure for audit and reprocessing.
Sample Code
DECLARE
l_return_status VARCHAR2 (10000);
l_errorcode NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (10000);
l_api_version NUMBER := 1.0;
l_organization_id NUMBER := 204;
l_inventory_item_id NUMBER := 592008;
l_category_set_id NUMBER := 2;
l_category_id NUMBER := 21; — New Category
l_old_category_id NUMBER := 1; — Old Category
BEGIN
DBMS_OUTPUT.put_line (‘Starting …’);
fnd_global.apps_initialize (32645, 20634, 401); — Initialize session
FOR rec_upd IN
(SELECT *
FROM (SELECT tmp_tbl.*, msib.inventory_item_id,
msib.organization_id, category_id old_category_id,
(SELECT category_id
FROM mtl_categories_b mcb
WHERE mcb.structure_id = 101
AND mcb.segment1 = tmp_tbl.segment1
AND mcb.segment2 = tmp_tbl.segment2
AND mcb.segment3 = tmp_tbl.segment3
AND mcb.segment4 = tmp_tbl.segment4
AND mcb.segment5 = tmp_tbl.segment5)
new_category_id
FROM xxxx_item_master_upd_temp tmp_tbl,
mtl_system_items_b msib,
mtl_item_categories_v micv
WHERE tmp_tbl.item = msib.segment1
AND msib.inventory_item_id = micv.inventory_item_id
AND micv.category_set_name = ‘Inventory’
AND msib.organization_id = micv.organization_id
ORDER BY tmp_tbl.item))
LOOP
BEGIN
inv_item_category_pub.update_category_assignment
(p_api_version => l_api_version,
p_category_id => rec_upd.new_category_id,
p_old_category_id => rec_upd.old_category_id,
p_category_set_id => 1,
p_inventory_item_id => rec_upd.inventory_item_id,
p_organization_id => rec_upd.organization_id,
x_return_status => l_return_status,
x_errorcode => l_errorcode,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
IF l_return_status = fnd_api.g_ret_sts_success
THEN
COMMIT;
DBMS_OUTPUT.put_line(‘Item Category Update Successful: ‘
|| rec_upd.item || ‘~’ || rec_upd.inventory_item_id);
UPDATE xxxx_item_master_upd_temp
SET cat_process_flag = l_return_status
WHERE record_id = rec_upd.record_id;
ELSE
DBMS_OUTPUT.put_line(‘Update Failed: ‘ || l_msg_data);
ROLLBACK;
FOR i IN 1 .. l_msg_count
LOOP
l_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => ‘F’);
DBMS_OUTPUT.put_line (i || ‘) ‘ || l_msg_data);
END LOOP;
UPDATE xxxx_item_master_upd_temp
SET cat_process_flag = NVL (l_return_status, ‘E’),
error_message = l_errorcode || ‘~’ || l_msg_data
WHERE record_id = rec_upd.record_id;
END IF;
END;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Exception Occurred: ‘ || SQLCODE || ‘:’ || SQLERRM);
RAISE;
END;
Key Points
API Used: INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT
Prerequisites:
FND_GLOBAL.APPS_INITIALIZE must be called to set user responsibility context.
Ensure correct CATEGORY_SET_ID (e.g., Inventory, Purchasing).
Audit Trail: Update staging table with success/failure flags for easy tracking.
Conclusion
Using Oracle’s Item Category Update API provides a scalable and reliable way to bulk update item category assignments. This eliminates manual effort, reduces errors, and ensures faster processing, especially in cases where thousands of items need reclassification.
This approach is particularly useful during:
Category restructuring projects.
Mass updates during data migration.
Aligning categories across multiple operating units.
With proper error handling and staging table design, businesses can maintain clean and accurate category assignments efficiently.
