Item Interface

1.Create a Stagging Tables

2.Create a Data File.

DATA FILE:

AVK,204,VIMAL,CREATE,1,1001,@Finished Good,FG,V1

BVK,204,VINOT,CREATE,1,1001,@Finished Good,FG,V1

MRB,204,RAJES,CREATE,1,1001,@Finished Good,FG,V1

RAK,204,ARAVI,CREATE,1,1001,@Finished Good,FG,V1

BVP,204,VIVIN,CREATE,1,1001,@Finished Good,FG,V1

ARK,204,RAJES,CREATE,1,1001,@Finished Good,FG,V1

MBM,204,BALAS,CREATE,1,1001,@Finished Good,FG,V1

MMS,204,SAMIS,CREATE,1,1001,@Finished Good,FG,V1

SJM,204,JOTHI,CREATE,1,1001,@Finished Good,FG,V1

RKN,204,KANNA,CREATE,1,1001,@Finished Good,FG,V1

3.Create a Control File

CONTROL FILE:

LOAD DATA
INFILE '/oracle2/VIS1211/apps/apps_st/appl/inv/12.0.0/bin/oit.txt'
INSERT INTO TABLE iteminterface
FIELDS TERMINATED BY ','
(
SEGMENT1 ,
ORGANIZATION_ID,
DESCRIPTION,
TRANSACTION_TYPE,
PROCESS_FLAG,
SET_PROCESS_ID,
TEMPLATE_NAME,
ITEM_TYPE,
ORGANIZATION_CODE
)

save it.

4.Move Data&Control File in gftp in This directory
    /oracle2/VIS1211/apps/apps_st/appl/inv/12.0.0/bin

5.In Apps

Switch Responsibility->Application Developer->Concurrent->Executable

Executable                     :oit865
Short Name                   :oit865   
Application                    :Inventory
Execution Method         :SQL Loader
Execution File Name     :oit865

Switch Responsibility->Application Developer->Concurrent->Program

Program             :oit865
Short Name        :oit865   
Application        :Inventory
Name                 :oit865



Switch Responsibility->System Administrator

Security->Responsibility->Request

Group             :All Inclusive GUI
Application     :Inventory

Give the Control File Name and save it.


Switch Responsibility->inventory,vision operations(USA)

View->Request->submit a new request


Name        :oit865

click submit

Then the Program Will Compile and Run Normal.

Then the Data's are loaded into stagging table

Table Name:iteminterface

After Finishing the Stagging table move the table to interface table using PL/SQL Procedure.

Before that run the Procedure

 Procedure Program:

CREATE OR REPLACE PROCEDURE APPS.vimal_interface
(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2) IS
    LN_REC_CNT      NUMBER := 0;
      v_template_name NUMBER;
      v_organization  NUMBER;
      l_error_details VARCHAR2 (4000) := NULL;
      l_error_flag    VARCHAR2 (2000) := NULL;
      l_failure_count NUMBER          := 0;
      l_success_count NUMBER          := 0;
      l_user_id       NUMBER          := fnd_global.user_id;
      l_resp_id       NUMBER          := fnd_global.resp_id;
      l_appl_id       NUMBER          := fnd_global.resp_appl_id;
      CURSOR DATA_LOAD
      IS
         SELECT * FROM iteminterface;
BEGIN
      fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
      FOR I IN DATA_LOAD
      LOOP
            l_error_flag    := NULL;
            l_error_details := NULL;
            LN_REC_CNT      := LN_REC_CNT + 1;
            BEGIN
               SELECT COUNT (*)
             INTO v_organization
             FROM org_organization_definitions
            WHERE organization_code = i.organization_code;
              IF v_organization         = 0 THEN
            l_error_flag           := 'Y';
            l_error_details        := l_error_details || '\' || 'Organization Error' || '\' ||             SUBSTR (SQLERRM, 1, 100);
      END IF;
END;
BEGIN
    SELECT COUNT (*)
        INTO v_template_name
        FROM mtl_item_templates
        WHERE template_name = i.template_name;
          IF v_template_name    = 0 THEN
        l_error_flag       := 'Y';
        l_error_details    := l_error_details || '\' || 'Template_Name Error' || '\' || SUBSTR          (SQLERRM, 1, 100);
      END IF;
END;
        IF (l_error_flag   = 'Y') THEN
          l_failure_count := l_failure_count + 1;
        ELSE
           INSERT
         INTO MTL_SYSTEM_ITEMS_INTERFACE
         (
              SEGMENT1        ,
              ORGANIZATION_ID ,
              DESCRIPTION     ,
              TRANSACTION_TYPE,
              PROCESS_FLAG    ,
              SET_PROCESS_ID  ,
              TEMPLATE_NAME   ,
               ITEM_TYPE       ,
              ORGANIZATION_CODE
    )
        VALUES
        (
              i.SEGMENT1        ,
              i.ORGANIZATION_ID ,
              i.DESCRIPTION     ,
              i.TRANSACTION_TYPE,
              i.PROCESS_FLAG    ,
              i.SET_PROCESS_ID  ,
              i.TEMPLATE_NAME   ,
              i.ITEM_TYPE       ,
              i.ORGANIZATION_CODE
    );
    
          l_success_count := l_success_count + 1;
        END IF;
      END LOOP;
      COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      FND_FILE.PUT_LINE
      (
            FND_FILE.LOG,'Records not loaded into the interface table'
      );
END;
Switch Responsibility->Application Developer->Concurrent->Executable

Executable                    :vimal_interface
Short Name                  :vimal_interface
Application                   :Inventory
Execution Method        :PL/SQL Stored Procedure
Execution File Name    :vimal_interface


Switch Responsibility->Application Developer->Concurrent->Program

Program             :vimal_interface
Short Name        :vimal_interface
Application        :Inventory
Name                 :vimal_interface


Switch Responsibility->System Administrator

Security->Responsibility->Request

Group            :All Inclusive GUI
Application    :Inventory

Give the interface table name and save it.

Switch Responsibility->inventory,vision operations(USA)

view->request->submit a new request


Name        :vimal_interface

click submit

Then the Program Will Compile and Run Normal.

Then the Data's are moved from stagging table to interface table.

Table Name:mtl_system_items_interface

Then we want to move from interface table to base table

Inventory Vision Operations->View->Request


Name        :Import Items

click submit

Then the Program Will Compile and Run Normal.

Then the Data's are moved from  interface table to base table.

Table Name:select * from mtl_system_items_b order by creation_date desc;

Error Table:mtl_interface_errors