Wednesday, March 23, 2016

Inventory Item Upload API

Salam Alaikum,

   I share with you here a very simple API that can be used to upload inventory items to the interface "MTL_SYSTEM_ITEMS_INTERFACE". And please note that after populating the interface you have to run the request "Import Items".

DECLARE
   CURSOR cur
   IS
      SELECT ITEM_CODE,
             ITEM_DESCRIPTION,
             UOM_CODE,
             TEMPLATE_ID
        FROM XXX_ITEMS_TEMP;
BEGIN
   fnd_global.apps_initialize (1150, 20634, 401);

   FOR rec IN cur
   LOOP
      INSERT INTO APPS.MTL_SYSTEM_ITEMS_INTERFACE (SEGMENT1,
                                                   DESCRIPTION,
                                                   ORGANIZATION_ID,
                                                   PRIMARY_UOM_CODE,
                                                   TEMPLATE_ID,
                                                   CREATED_BY,
                                                   PROCESS_FLAG,
                                                   TRANSACTION_TYPE)
           VALUES (rec.ITEM_CODE,
                   rec.ITEM_DESCRIPTION,
                   102,
                   rec.UOM_CODE,
                   rec.TEMPLATE_ID,
                   1150,
                   1,
                   'CREATE');

      INSERT INTO APPS.MTL_SYSTEM_ITEMS_INTERFACE (SEGMENT1,
                                                   DESCRIPTION,
                                                   ORGANIZATION_ID,
                                                   PRIMARY_UOM_CODE,
                                                   TEMPLATE_ID,
                                                   CREATED_BY,
                                                   PROCESS_FLAG,
                                                   TRANSACTION_TYPE)
           VALUES (rec.ITEM_CODE,
                   rec.ITEM_DESCRIPTION,
                   101,
                   rec.UOM_CODE,
                   rec.TEMPLATE_ID,
                   1150,
                   1,
                   'CREATE');

      UPDATE XXX_ITEMS_TEMP2 t
         SET t.data_upload = 'Y'
       WHERE t.ITEM_CODE = rec.ITEM_CODE;
   END LOOP;
END;

Some notes regarding the Concurrent "Import Items":
  1. Indicate whether to run the interface for all organizations in the item interface table. If you choose No, the interface runs only for the current organization and interface table rows for other organizations are ignored.
  2. Indicate whether to validate all items and their data residing in the interface table that have not yet been validated. If items are not validated, they are not processed into Oracle Inventory. (You would choose No if you had previously run the item interface and responded Yes for Validate Items and No for Process Items, and now want to process your items).
  3. Indicate whether to process items. If you choose Yes, all qualifying items in the interface table are inserted into Inventory. (You would choose No, along with Yes for Delete Processed Rows, to remove successfully processed rows from the interface table without performing any other processing).
  4. Indicate whether to delete successfully processed items from the item interface tables
    (Choose No to leave all rows in the item interface tables for successfully processed items).


  Hope you found it useful, feel free to leave a comment if have any note on the subject.

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete