API to Update Item Status in Oracle E-Business Suite
Introduction
In Oracle E-Business Suite (EBS), Item Status controls key aspects of how an item behaves across different modules such as Inventory, Purchasing, and Order Management.
For example, an item status may define whether an item can be purchased, transacted, costed, or orderable.
Many organizations face the need to update item statuses in bulk — for example, moving items from Active to Inactive, or setting items to a Hold status. Doing this manually can be time-consuming and error-prone.
To simplify this, Oracle provides the EGO Item API (EGO_ITEM_PUB.PROCESS_ITEMS) that can be used to programmatically update item attributes, including status.
Use Case
The customer’s requirement was to update the existing item status for a list of items. Instead of using the Item form, the process was automated using a staging table (xxxx_item_master_upd_temp) and an API call.
This PL/SQL script:
- Fetches items from the staging table.
- Reads the new status value from the setup table (MTL_ITEM_STATUS).
- Calls the EGO_ITEM_PUB.PROCESS_ITEMS API to update the status.
- Updates the staging table with success/failure results.
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;
— Changing Values
l_item_table ego_item_pub.item_tbl_type;
x_item_table ego_item_pub.item_tbl_type;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER (10);
x_msg_data VARCHAR2 (1000);
x_message_list error_handler.error_tbl_type;
BEGIN
DBMS_OUTPUT.put_line (‘Starting …’);
— Initialize Apps Session
fnd_global.apps_initialize (32645, 20634, 401);
— Debug profile check
DBMS_OUTPUT.put_line (‘INV Debug Profile: ‘
|| fnd_profile.VALUE (‘INV_DEBUG_TRACE’));
— Loop through staging data
FOR rec_upd IN
(SELECT *
FROM (SELECT tmp_tbl.*,
msib.inventory_item_id,
msib.organization_id,
msib.inventory_item_status_code,
(SELECT inventory_item_status_code
FROM mtl_item_status mis
WHERE mis.inventory_item_status_code_tl = tmp_tbl.status)
upd_status
FROM xxxx_item_master_upd_temp tmp_tbl,
mtl_system_items_b msib
WHERE tmp_tbl.item = msib.segment1
ORDER BY tmp_tbl.item))
LOOP
BEGIN
— Prepare API Input
l_item_table (1).transaction_type := ‘UPDATE’;
l_item_table (1).inventory_item_id := rec_upd.inventory_item_id;
l_item_table (1).organization_id := rec_upd.organization_id;
l_item_table (1).inventory_item_status_code := rec_upd.upd_status;
DBMS_OUTPUT.put_line (‘Calling API to Update Item Status’);
— Call EGO API
ego_item_pub.process_items (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_item_tbl => l_item_table,
x_item_tbl => x_item_table,
x_return_status => x_return_status,
x_msg_count => x_msg_count);
DBMS_OUTPUT.put_line (‘Return Status ==> ‘ || x_return_status);
— Success
IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
COMMIT;
DBMS_OUTPUT.put_line (
‘Updation of Item Status is Successful: ‘
|| rec_upd.item || ‘~’ || rec_upd.inventory_item_id
|| ‘~’ || rec_upd.organization_id);
UPDATE xxxx_item_master_upd_temp
SET status_process_flag = x_return_status
WHERE record_id = rec_upd.record_id;
— Failure
ELSE
ROLLBACK;
DBMS_OUTPUT.put_line (‘Error Messages:’);
— Get error details
error_handler.get_message_list (x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
DBMS_OUTPUT.put_line (x_message_list (i).MESSAGE_TEXT);
END LOOP;
UPDATE xxxx_item_master_upd_temp
SET status_process_flag = NVL (x_return_status, ‘E’)
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);
DBMS_OUTPUT.put_line (‘=====================================’);
RAISE;
END;
Key Highlights
- API Used: EGO_ITEM_PUB.PROCESS_ITEMS
- Transaction Type: ‘UPDATE’ is used to change the item status.
- Input Source: A staging table holds the items and new status values.
- Validation: New status is fetched from MTL_ITEM_STATUS.
- Error Handling: Captures error messages using ERROR_HANDLER.GET_MESSAGE_LIST.
- Audit Trail: Updates staging table with success or error flags for tracking.
Conclusion
This approach allows organizations to bulk update item statuses in Oracle EBS quickly and reliably using the standard EGO Item API.
It helps by:
- Eliminating manual updates.
- Reducing errors during mass changes.
- Providing a repeatable and auditable process.
This method is particularly useful during product lifecycle changes, item cleanups, and business transitions where many items must be moved from one status to another.
