Mass Inventory Category Cleanup Using Oracle Inventory API
In large Oracle E-Business Suite (EBS) implementations, item categories often become inconsistent over time due to data migrations, manual corrections, or changes in business classification. In this scenario, the customer wanted to perform a mass Inventory Category cleanup activity to update items with the correct new category values in a controlled and Oracle-supported way.
This blog explains how we achieved this using the standard Oracle Inventory public API instead of direct table updates.
Business Requirement
The customer faced the following challenges:
Inventory items assigned to incorrect or outdated Inventory Categories
Requirement to update category values in bulk
Need for a safe, auditable, and supported approach
Avoiding direct DML on Oracle base tables
To address this, a PL/SQL-based mass update solution using the Oracle Inventory API was implemented.
Solution Overview
We used the standard Oracle API:
INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT
This API allows updating item category assignments while respecting Oracle business logic and validations.
Key Features of the Solution
Mass update driven by a staging table
Dynamic derivation of new category IDs using category segments
API-based update (Oracle-recommended approach)
Detailed success and error logging
Safe rollback handling for failed records
High-Level Process Flow
Load item numbers and updated category segment values into a staging table
Fetch existing Inventory Category assignments for each item
Derive the new category ID based on updated segment values
Invoke the Inventory API to update the category assignment
Log success or failure status back to the staging table
Technical Approach
- Application Context Initialization
Before calling any Oracle API, the application context must be initialized using:
User ID
Responsibility ID
Application ID
This ensures the API runs with proper security and responsibility access.
- Identifying Items and Categories
The program:
Reads item details from a staging table
Fetches the current Inventory Category
Resolves the new category ID from MTL_CATEGORIES_B using updated segment values
Only records with valid new category combinations are processed.
- Updating Inventory Category Using API
The Inventory Category update is performed using the standard API inside a loop to handle mass updates safely.
INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT
The API updates:
Item
Organization
Inventory Category Set
Old Category → New Category
- Success and Error Handling
On success
Commit the transaction
Update the staging table with success status
Log details using DBMS_OUTPUT
On failure
Rollback the transaction
Capture detailed error messages using OE_MSG_PUB.GET
Store error codes and messages for troubleshooting
Sample PL/SQL Code
Below is the complete PL/SQL block used for the mass Inventory Category cleanup:
DECLARE
l_return_status VARCHAR2 (10000);
l_errorcode NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (10000);
l_api_version NUMBER := 1.0;
BEGIN
DBMS_OUTPUT.put_line (‘Starting …’);
fnd_global.apps_initialize (userid, responsbilityid, applicationid);
FOR rec_upd IN
(SELECT *
FROM (SELECT temps.*,
(SELECT category_id
FROM mtl_categories_b mcb
WHERE mcb.structure_id = 101
AND mcb.segment1 = temps.updated_segment_1
AND mcb.segment2 = temps.updated_segment_2
AND mcb.segment3 = temps.updated_segment_3
AND NVL (mcb.segment4, ‘X’) =
NVL (temps.updated_segment_4, ‘X’)
AND NVL (mcb.segment5, ‘X’) =
NVL (temps.updated_segment_5, ‘X’))
new_category_id
FROM xxxx_item_master_cleanup_4 temps)
WHERE new_category_id IS NOT NULL)
LOOP
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;
ELSE
ROLLBACK;
END IF;
END LOOP;
END;
/
Benefits of This Approach
Oracle-supported and upgrade-safe
Suitable for large data volumes
Easy to audit and troubleshoot
Prevents data corruption
Reusable for future cleanup activities
Conclusion
For mass Inventory Category cleanup activities in Oracle EBS, using standard public APIs is the safest and most reliable approach. This solution allowed the customer to correct category assignments efficiently while maintaining data integrity and auditability.
