API - Inventory Item Creation

DECLARE
   X_INVENTORY_ITEM_ID   NUMBER;
   X_ORGANIZATION_ID     NUMBER;
   X_RETURN_STATUS       VARCHAR2 (4000);
   X_MSG_DATA            VARCHAR2 (4000);
   X_MSG_COUNT           NUMBER;
   LX_MSG_INDEX_OUT      NUMBER;
BEGIN
   FND_GLOBAL.
   APPS_INITIALIZE (USER_ID => 1832, RESP_ID => NULL, RESP_APPL_ID => NULL);

   ego_item_pub.
   process_item (p_api_version                  => 1.0,
                 p_init_msg_list                => 'T',
                 p_commit                       => 'T',
                 p_Transaction_Type             => 'CREATE' -- UPDATE FOR Updating item
                                                           ,
                 p_segment1                     => 'XYZABC'      --  ITEM CODE
                                                           ,
                 p_description                  => 'CREATED BY API' --  ITEM DESCRIPTION
                                                                   ,
                 p_long_description             => 'CREATED FOR LONG' --  ITEM LONG DESCRIPTION
                                                                     ,
                 p_organization_id              => 755 --  WAREHOUSE ORGANIZATION ID
                                                      --, P_TEMPLATE_ID                => 2
                 ,
                 p_Inventory_Item_Status_Code   => 'Active',
                 p_approval_status              => 'A',
                 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);

   DBMS_OUTPUT.put_line (x_msg_data);

   FOR j IN 1 .. x_msg_count
   LOOP
      FND_MSG_PUB.Get (p_msg_index       => j,
                       p_encoded         => 'F',
                       p_data            => x_msg_data,
                       p_msg_index_out   => lx_msg_index_out);
      DBMS_OUTPUT.put_line (x_msg_data);
   END LOOP;

   IF x_return_status = 'S'
   THEN
      DBMS_OUTPUT.put_line ('ITEM CREATION SUCCESSFUL');
   END IF;
END;

---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------

 OR

 --------------------------------------------------------------------------------------------
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.

/* Formatted on 3/24/2014 10:23:27 PM (QP5 v5.139.911.3011) */
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';