API EGO_ITEM_PUB.Process_Items can be used to create or Update Items.

In this we will see how to create a new Item 'NEWITEM' using an API.

Below code can be used to create it.


SET SERVEROUTPUT ON
DECLARE
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

--Apps Initialization

FND_GLOBAL.APPS_INITIALIZE(USER_ID=>&userid,RESP_ID=>&RESP_ID,RESP_APPL_ID=>&RESP_APPL_ID);

--FIRST Item definition
l_item_table(1).Transaction_Type := 'CREATE'; -- Replace this with 'UPDATE' for update transaction.
l_item_table(1).Segment1 := 'NEWITEM';
l_item_table(1).Description := 'NEWITEM';
l_item_table(1).Organization_Code := '&masterorg';
l_item_table(1).Template_Name := '&template';

DBMS_OUTPUT.PUT_LINE('Calling API to Create Item');

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);

IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
FOR i IN 1..x_item_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Inventory Item Id Created:'to_char(x_item_table(i).Inventory_Item_Id));
DBMS_OUTPUT.PUT_LINE('Organization Id :'to_char(x_item_table(i).Organization_Id));
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('Error Messages :');
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;
END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error has Occured and error is 'SUBSTR(SQLERRM,1,200));
END;

Debugging Errors
---------------------------

Suppose if any error has occured during the process, we can check the details of the error using Mtl_system_items_interface and Mtl_interace_errors table.

If any error occured, then Process_flag value of the record in the Mtl_system_items_interface will be 3. Also for this record transaction_id will be populated.

Using the transaction_id we can check the error in Mtl_interface_error table and get the exact error_mesage on which the record is failing.

Use the below script to debug error in case item 'NEWITEM' is not created.


SELECT msii.segment1,msii.description,msii.process_flag,msii.transaction_id,mie.error_message
FROM mtl_system_items_interface msii,
mtl_interface_errors mie
WHERE msii.transaction_id = mie.transaction_id
AND msii.process_flag = 3
AND msii.segment1 = 'NEWITEM';