How to Upload Item in Inventory through Interface

Step1: Create Staging Table.

CREATE TABLE APPS.XXCONA_ITEM_UPLOAD_STG
(
  ITEM_NUMBER        VARCHAR2(40 BYTE),
  DESCRIPTION        VARCHAR2(240 BYTE),
  LONG_DESCRIPTION   VARCHAR2(400 BYTE),
  ENABLED_FLAG       VARCHAR2(1 BYTE),
  PRIMARY_UOM_CODE   VARCHAR2(20 BYTE),
  TEMPLATE_NAME      VARCHAR2(100 BYTE),
  ORGANIZATION_CODE  VARCHAR2(10 BYTE),
  TRANSACTION_TYPE   VARCHAR2(40 BYTE),
  ITEM_TYPE          VARCHAR2(40 BYTE),
  LOT_CONTROL_CODE   VARCHAR2(40 BYTE),
  ITEM_STATUS_CODE   VARCHAR2(40 BYTE),
  VERIFY_FLAG        VARCHAR2(1 BYTE),
  ERROR_MSG          VARCHAR2(200 BYTE),
  ERROR_MESSAGE      VARCHAR2(2000 BYTE)
)

After Create Staging Table

Import data to the Staging Table.

DECLARE
  L_MST_ORG_ID      NUMBER;
  L_VERIFY_FLAG     VARCHAR2(2):='Y';
  L_UOM             VARCHAR2(10);
  L_TEMPLATE_ID     NUMBER;
  L_COUNT           NUMBER;
  L_ITEM_TYPE       VARCHAR2(20);
  L_CATEGORY_ID     NUMBER;
  L_ERROR_MESSAGE   VARCHAR2(200):= NULL;
  L_CATEGORY_SET_ID NUMBER;
  L_ERRM            VARCHAR2(2000);
  ret_num           NUMBER;
  L_User_Id         NUMBER := fnd_profile.VALUE ('USER_ID');
  L_resp_app_id     NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');
  L_resp_id         NUMBER := fnd_profile.VALUE ('RESP_ID');
  L_Organization_id NUMBER := fnd_profile.VALUE ('MFG_ORGANIZATION_ID');

  CURSOR CUR_ITEM
  IS
    SELECT * FROM XXCONA_ITEM_UPLOAD_STG
       WHERE  NVL(VERIFY_FLAG,'N') ='N';
      
 BEGIN

  FOR ITEM IN CUR_ITEM
  LOOP
   /*ORGANIZATION VALIDATION*/
    BEGIN
      SELECT ORGANIZATION_ID
      INTO L_MST_ORG_ID
      FROM ORG_ORGANIZATION_DEFINITIONS
      WHERE UPPER(TRIM(ORGANIZATION_CODE))=UPPER(TRIM(ITEM.ORGANIZATION_CODE));
     dbms_output.PUT_LINE(CUR_ITEM%ROWCOUNT||'.ORGANIZATION ID: '||L_MST_ORG_ID);
    EXCEPTION
    WHEN OTHERS THEN
      L_VERIFY_FLAG  :='N';
      L_ERROR_MESSAGE:='INVALID ORGANIZATION';
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERROR_MESSAGE);
      dbms_output.PUT_LINE('INVALID ORGANIZATION');
    END;
   
     /*TEMPLATE VALIDATION*/
    BEGIN
      SELECT TEMPLATE_ID
      into L_TEMPLATE_ID
      FROM MTL_ITEM_TEMPLATES
      WHERE upper(trim(TEMPLATE_NAME)) = upper(trim(ITEM.TEMPLATE_NAME));
     dbms_output.PUT_LINE('L_TEMPLATE_ID'||L_TEMPLATE_ID);
    EXCEPTION
    WHEN OTHERS THEN
      L_VERIFY_FLAG  :='N';
      L_ERROR_MESSAGE:='INVALID Template Name';
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERROR_MESSAGE);
      dbms_output.PUT_LINE(L_ERROR_MESSAGE);
    END;
   
    /*UOM VALIDATION*/
    BEGIN
      SELECT UOM_CODE
      INTO L_UOM
      FROM MTL_UNITS_OF_MEASURE
      WHERE UPPER(TRIM(UNIT_OF_MEASURE))=UPPER(TRIM(ITEM.PRIMARY_UOM_CODE));
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_UOM);
      --dbms_output.PUT_LINE(L_UOM);
        dbms_output.PUT_LINE('L_UOM: '||L_UOM);
    EXCEPTION
    WHEN OTHERS THEN
      L_VERIFY_FLAG  :='N';
      L_ERROR_MESSAGE:=L_ERROR_MESSAGE||'UOM IS NOT VALID';
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERROR_MESSAGE);
      dbms_output.PUT_LINE('UOM IS NOT VALID');
    END;
   
        /* ITEM IS EXIST OR NOT*/
    BEGIN
      L_COUNT := 0;
      SELECT COUNT(*)
      INTO L_COUNT
      FROM MTL_SYSTEM_ITEMS_B
      WHERE UPPER(TRIM(SEGMENT1))= UPPER(TRIM(ITEM.ITEM_NUMBER))
      AND ORGANIZATION_ID        = L_MST_ORG_ID;
   
     IF L_COUNT                 > 0 THEN
        L_VERIFY_FLAG           := 'N';
        L_ERROR_MESSAGE         :=L_ERROR_MESSAGE || 'ITEM ALREADY EXISTING' ;
     END IF;
  
    EXCEPTION
    WHEN OTHERS THEN
      L_ERROR_MESSAGE:=L_ERROR_MESSAGE|| 'ITEM CHECKING FAILED' ;
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERROR_MESSAGE);
      dbms_output.PUT_LINE('ITEM CHECKING FAILED');
    END;

  /*CATEGORY_SET_ID VALIDATION*/
    BEGIN
      SELECT CATEGORY_SET_ID
      INTO L_CATEGORY_SET_ID
      FROM MTL_CATEGORY_SETS_TL
      WHERE UPPER(TRIM(CATEGORY_SET_NAME)) = UPPER(TRIM('Inventory'));
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_CATEGORY_SET_ID);
      dbms_output.PUT_LINE('CATEGORY_SET_ID: '||L_CATEGORY_SET_ID);
    EXCEPTION
    WHEN OTHERS THEN
      L_VERIFY_FLAG  := 'N';
      L_ERROR_MESSAGE:= L_ERROR_MESSAGE||'INVALID CATEGORY SET';
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERROR_MESSAGE);
      dbms_output.PUT_LINE('INVALID CATEGORY SET');
    END;
   
    /*IF ALL CONDITION SATISFY THEN INSERT INTO TABLE*/
    IF ITEM.VERIFY_FLAG<>'N' or L_VERIFY_FLAG <> 'N' THEN
   
      BEGIN
     
        -----MASTER ORGANIZATION  -----------  
        FND_FILE.put_line (FND_FILE.LOG,'1');
        INSERT
        INTO APPS.MTL_SYSTEM_ITEMS_INTERFACE
          ( INVENTORY_ITEM_ID,
            SEGMENT1,
            DESCRIPTION,
            LONG_DESCRIPTION,
            PRIMARY_UOM_CODE,
            SET_PROCESS_ID,
            TEMPLATE_ID,
            ORGANIZATION_ID,
            TRANSACTION_TYPE,
            PROCESS_FLAG,
            --ITEM_TYPE,
            INVENTORY_ITEM_STATUS_CODE,
         --   LOT_CONTROL_CODE,
         --   DEFAULT_LOT_STATUS_ID,
         --   INVENTORY_PLANNING_CODE,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATED_BY,
            LAST_UPDATE_DATE
          )
          VALUES
          (MTL_SYSTEM_ITEMS_B_S.nextval,
            ITEM.ITEM_NUMBER,
            ITEM.DESCRIPTION,
            ITEM.LONG_DESCRIPTION,
            L_UOM,
            1,   ---SET_PROCESS_ID
            L_TEMPLATE_ID,
            L_MST_ORG_ID,
            'CREATE',   ---TRANSACTION_TYPE, ---CREATE
            1, ---PROCESS_FLAG
            --ITEM.ITEM_TYPE,
            ITEM.ITEM_STATUS_CODE,
            --DECODE(LOWER(ITEM.LOT_CONTROL_CODE),'yes',2,1),
            --ITEM.AUTO_LOT_ALPHA_PREFIX,
            --2,
            --ITEM.SHELF_LIFE_DAYS,
           -- 83,
           -- 6,
            SYSDATE,
            L_USER_ID,
            L_USER_ID,
            SYSDATE
          );
         
          ------------------requered organization---------
        
    
        FND_FILE.put_line (FND_FILE.LOG,'1');
        INSERT
        INTO APPS.MTL_SYSTEM_ITEMS_INTERFACE
          (INVENTORY_ITEM_ID,
            SEGMENT1,
            DESCRIPTION,
            LONG_DESCRIPTION,
            PRIMARY_UOM_CODE,
            SET_PROCESS_ID,
            TEMPLATE_ID,
            ORGANIZATION_ID,
            TRANSACTION_TYPE,
            PROCESS_FLAG,
            --ITEM_TYPE,
            INVENTORY_ITEM_STATUS_CODE,
         --   LOT_CONTROL_CODE,
         --   DEFAULT_LOT_STATUS_ID,
         --   INVENTORY_PLANNING_CODE,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATED_BY,
            LAST_UPDATE_DATE
          )
          VALUES
          ( MTL_SYSTEM_ITEMS_B_S.currval,
            ITEM.ITEM_NUMBER,
            ITEM.DESCRIPTION,
            ITEM.LONG_DESCRIPTION,
            L_UOM,
            1,
            L_TEMPLATE_ID,
            142, --- UNITII_IO 
            'CREATE', --TRANSACTION_TYPE,  ---CREATE 
            1, ---PROCESS_FLAG
            --ITEM.ITEM_TYPE,
            ITEM.ITEM_STATUS_CODE,
            --DECODE(LOWER(ITEM.LOT_CONTROL_CODE),'yes',2,1),
            --ITEM.AUTO_LOT_ALPHA_PREFIX,
            --2,
            --ITEM.SHELF_LIFE_DAYS,
           -- 83,
           -- 6,
            SYSDATE,
            L_USER_ID,
            L_USER_ID,
            SYSDATE
          );
         
        UPDATE XXCONA_ITEM_UPLOAD_STG
        SET VERIFY_FLAG = 'Y'
            ,ERROR_MESSAGE = L_ERROR_MESSAGE
        WHERE ITEM_NUMBER =ITEM.ITEM_NUMBER;
        COMMIT;
       
      EXCEPTION
      WHEN OTHERS THEN
        L_ERRM := SQLERRM;
        FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERRM);
        DBMS_OUTPUT.PUT_LINE(L_ERRM);
        UPDATE XXCONA_ITEM_UPLOAD_STG
        SET VERIFY_FLAG = 'N'
          , ERROR_MSG =SUBSTR(L_ERRM,1,150)
          , ERROR_MESSAGE = L_ERROR_MESSAGE
        WHERE ITEM_NUMBER=ITEM.ITEM_NUMBER;
         COMMIT;
      END;
     
     
    ELSE
      UPDATE XXCONA_ITEM_UPLOAD_STG
      SET VERIFY_FLAG = 'N'
       , ERROR_MSG =SUBSTR(L_ERRM,1,150)
       , ERROR_MESSAGE = L_ERROR_MESSAGE
      WHERE ITEM_NUMBER =ITEM.ITEM_NUMBER;
      COMMIT;
     
     
    END IF;
   
   
  END LOOP;
  COMMIT;
  fnd_global.apps_initialize (L_User_Id,L_resp_id,L_resp_app_id);
  ret_num := fnd_request.submit_request('INV', 'INCOIN', NULL, NULL, False, L_Organization_id, 1, 1, 1, 1, NULL, 1, 1, chr(0));
 COMMIT;
END;