Procedure to Update Item Number in Oracle Inventory

ego_item_pub API to update Item Number.
--------------------------------------------
CREATE OR REPLACE
PACKAGE xx_item_num_update
AS
PROCEDURE item_update (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_old_item_name IN VARCHAR2,
p_new_item_name IN VARCHAR2
);

END xx_item_num_update;
/
SHOW ERRORS PACKAGE xx_item_num_update;

CREATE OR REPLACE
PACKAGE BODY xx_item_num_update
AS

/******************************************************************************
Procedure: put_log()
Description: Writes a line to the log file.
+- Required/Optional
| Name Description
- ----------------------------- -----------------------------------------------
R p_string String to write to the log file
******************************************************************************/
PROCEDURE put_log (p_string IN VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_string);
END put_log;

/******************************************************************************
Procedure: item_update()
Description: Procedure to update Item Number.
+- Required/Optional
| Name Description
- ----------------------------------------------------------------------------
R p_old_item_name Old Item Number
R p_new_item_name New Item Number
******************************************************************************/
PROCEDURE item_update (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_old_item_name IN VARCHAR2,
p_new_item_name IN VARCHAR2
)
IS

CURSOR c_items (p_item_id NUMBER) IS
SELECT organization_id
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id;

e_abort EXCEPTION;
e_skip EXCEPTION;
l_inventory_item_id NUMBER;
l_organization_id NUMBER;
x_inventory_item_id NUMBER;
x_organization_id NUMBER;
x_return_status VARCHAR2(300);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
x_message_list Error_Handler.Error_Tbl_Type;

BEGIN

BEGIN
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_kfv
WHERE organization_id = 82
AND concatenated_segments = p_old_item_name;
EXCEPTION
WHEN OTHERS THEN
put_log ('Error While fetching inventory item id'||SQLERRM);
RAISE e_abort;
END;

put_log ('Inventory Item Id: '||l_inventory_item_id);
put_log ('Old Item Name: '||p_old_item_name);
put_log ('New Item Name: '||p_new_item_name);

FOR rec_items IN c_items(l_inventory_item_id)
LOOP

apps.ego_item_pub.process_item
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => FND_API.G_FALSE,--'F',--'T',
p_transaction_type => 'UPDATE',
p_inventory_item_id => l_inventory_item_id,
p_organization_id => rec_items.organization_id,
p_Item_Number => p_new_item_name,
x_inventory_item_id => x_inventory_item_id,
x_organization_id => x_organization_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);

IF (x_return_status <> apps.fnd_api.g_ret_sts_success) THEN
ROLLBACK;
put_log( 'Item Number Update API Error '|| x_return_status );
put_log('Error Messages :');
Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
FOR i IN 1..x_message_list.COUNT LOOP
put_log(x_message_list(i).message_text);
END LOOP;
RAISE e_abort;
ELSE
COMMIT;
put_log('Item Number Update API Success: ' || x_return_status || ' Org Id: '||rec_items.organization_id);
END IF;
END LOOP;

EXCEPTION
WHEN e_abort THEN
put_log ('Unexpected Error Occured in item_update(). Aborting the Program');
retcode := 2;
WHEN OTHERS THEN
put_log ('Error in item_update(): ' || SQLERRM);
errbuf := SQLERRM;
retcode := 2;
END item_update;
END xx_item_num_update;
/
SHOW ERRORS PACKAGE BODY xx_item_num_update;