oracle application 11i


    Example : ITEM UPLOAD INTERFACE

    Share
    avatar
    shrikantgarud
    Admin

    Posts : 109
    Join date : 2007-12-02
    Age : 31
    Location : Clover Infotech

    Example : ITEM UPLOAD INTERFACE

    Post  shrikantgarud on Sun Jan 27, 2008 8:28 am

    STEP1.

    GO TO APPS AND CREATE STAGING TABLE

    create table SG_ITEM_STAGE
    (segment1 varchar2(25),
    DESCRIPTION VARCHAR2(25),
    TEMPLATE_NAME VARCHAR2(25),
    MATERIAL_COST NUMBER,
    PROCESS_FLAG NUMBER,
    ENABLED_FLAG VARCHAR2(1),
    TRANSACTION_TYPE VARCHAR2(25),
    ORGANIZATION_CODE VARCHAR2(25))

    STEP 2.

    Create a SG_ITEM_DATA.dat File

    SGTest Item51, SGTest Item11,Finished Good,100,
    SGTest Item52, SGTest Item12, Finished Good, 500,
    SGTest Item53, SGTest item13, Finished Good, 600,
    SGtest Item54, SGTest Item14, Finished Good,300,

    STEP 3.
    Create a shri.ctl file (control file which contains the logic of uploading data from flat file to the stagging table)

    LOAD DATA
    INFILE '/apps/visappl/cust/11.5.0/bin/SG_ITEM_DATA.dat'
    INTO TABLE SG_ITEM_STAGE
    APPEND
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    (SEGMENT1,
    DESCRIPTION,
    TEMPLATE_NAME,
    MATERIAL_COST,
    PROCESS_FLAG CONSTANT 1,
    ENABLED_FLAG CONSTANT 'Y',
    TRANSACTION_TYPE CONSTANT 'Create',
    ORGANIZATION_CODE CONSTANT "V1")

    STEP 4.

    UP LOAD CTL AND DAT FILE
    FTP
    TELNET

    STEP 5.

    CHECK DATA IN STAGING TABLE
    select count(*) from
    SG_STAGE_TABLE;

    STEP 6.

    CREATE PRE_INTERFACE TABLE AS INTERFACE TABLE

    create table SG_ITEM_PRE_INTERFACE AS
    select * from mtl_system_items_interface ;

    STEP 7.


    RUN THIS PROCEDURE TO UPLOAD DATA FROM STAGING TABLE TO PREINTERFACE
    TABLE

    STAGING TABLE---------------------------->PRE-INTERFACE TABLE

    CREATE OR REPLACE Procedure SG_ITEM_PRE_INTERFACE Is
    m_Segment1 varchar2(40);
    m_Description varchar2(40);
    M_TEMPLATE_NAME VARCHAR2(40);
    M_MATERIAL_COST NUMBER;
    M_PROCESS_FLAG NUMBER;
    m_Transaction_Type varchar2(10);
    m_Organization_CODE VARCHAR2(10);
    M_ENABLED_FLAG VARCHAR2(1);
    m_set_process_id number;
    Cursor C1 is Select * from SG_STAGE_TABLE;
    Begin
    For rec in C1
    loop
    m_Segment1 := REC.SEGMENT1;
    m_Description := REC.DESCRIPTION;
    M_TEMPLATE_NAME := REC.TEMPLATE_NAME;
    IF M_MATERIAL_COST>500 THEN
    M_MaTERIAL_COST:=500;
    END IF;
    IF M_PROCESS_FLAG IN(2,3,4,5) THEN
    M_PROCESS_FLAG:=1;
    ELSE
    M_PROCESS_FLAG:=REC.PROCESS_FLAG;
    END IF;
    M_MATERIAL_COST := abs(REC.MATERIAL_COST);
    M_PROCESS_FLAG := REC.PROCESS_FLAG;
    m_Transaction_Type := REC.TRANSACTION_TYPE;
    m_Organization_CODE:= REC.ORGANIZATION_CODE;
    M_ENABLED_FLAG := REC.ENABLED_FLAG;
    m_set_process_id := 25101974;
    Insert into
    SG1_ITEM_PRE_INTERFACE
    (
    SEGMENT1,
    DESCRIPTION,
    TEMPLATE_NAME,
    MATERIAL_COST,
    PROCESS_FLAG,
    TRANSACTION_TYPE,
    ORGANIZATION_CODE,
    ENABLED_FLAG,
    SET_PROCESS_ID
    )
    values
    (
    m_Segment1,
    m_Description,
    M_TEMPLATE_NAME,
    M_MATERIAL_COST,
    M_PROCESS_FLAG,
    m_Transaction_Type,
    m_Organization_CODE,
    M_ENABLED_FLAG,
    M_SET_PROCESS_ID
    );
    End Loop;
    End SG_ITEM_PRE_INTERFACE;
    /

    Compile & execute this procedure. After this check data is uploaded in the pre-interface
    table.
    EXEC SG_ITEM_PRE_INTERFACE;
    SELECT COUNT(*) FROM SG1_ITEM_PRE_INTERFACE;

    STEP 7.

    TRANSFER DATA FROM
    PRE-INTERFACE TABLE-------------------------->INTERFACE TABLE

    CREATE OR REPLACE Procedure SG2_ITEM_INTERFACE Is
    m_Segment1 varchar2(40);
    m_Description varchar2(40);
    M_TEMPLATE_NAME VARCHAR2(40);
    M_MATERIAL_COST NUMBER;
    M_PROCESS_FLAG NUMBER;
    m_Transaction_Type varchar2(10);
    m_Organization_CODE VARCHAR2(10);
    M_ENABLED_FLAG VARCHAR2(1);
    m_set_process_id number;
    Cursor C1 is Select * from SG1_ITEM_PRE_INTERFACE;
    Begin
    For rec in C1
    loop
    m_Segment1 := REC.SEGMENT1;
    m_Description := REC.DESCRIPTION;
    M_TEMPLATE_NAME := REC.TEMPLATE_NAME;
    M_MATERIAL_COST := REC.MATERIAL_COST;
    M_PROCESS_FLAG := REC.PROCESS_FLAG;
    m_Transaction_Type := REC.TRANSACTION_TYPE;
    m_Organization_CODE:= REC.ORGANIZATION_CODE;
    M_ENABLED_FLAG := REC.ENABLED_FLAG;
    m_set_process_id := 25101974;
    Insert into MTL_SYSTEM_ITEMS_INTERFACE
    (
    SEGMENT1,
    DESCRIPTION,
    TEMPLATE_NAME,
    MATERIAL_COST,
    PROCESS_FLAG,
    TRANSACTION_TYPE,
    ORGANIZATION_CODE,
    ENABLED_FLAG,
    SET_PROCESS_ID
    )
    values
    (
    m_Segment1,
    m_Description,
    M_TEMPLATE_NAME,
    M_MATERIAL_COST,
    M_PROCESS_FLAG,
    m_Transaction_Type,
    m_Organization_CODE,
    M_ENABLED_FLAG,
    M_SET_PROCESS_ID
    );
    End Loop;
    End SG2_ITEM_INTERFACE;
    EXEC SG_ITEM_PRE_INTERFACE;

    SELECT COUNT(*) FROM SG1_ITEM_PRE_INTERFACE;

    STEP 8.
    Check the data
    SELECT COUNT(*) FROM MTL_SYSTEM_ITEMS_INTERFACE;

    STEP 9.
    Log on to apps(mfg/welcome).
    select ==>Inventory.
    In Navigator ==> Items==>Import Items ==> Select master organisation

      Current date/time is Sun Nov 19, 2017 7:02 pm