Item Interface

V1|GE2000|BOLT|BOLT 22" MILD STEEL|Purchased Item|CREATE|
M1|GE2000|BOLT|BOLT 22" MILD STEEL|Purchased Item|CREATE|
V1C|GE2001|NUT|NUT 22" MILD STEEL|Purchased Item|CREATE|
V1|GE2002|STUD|STUD 22" MILD STEEL|Purchased Item|CREATE|
M1|GE2002|STUD|STUD 22"MILD STEEL|Purchased Item|CREATE|
V1B|GE2003|STUD|STUD 22" MILD STEEL|GEXX Purchased Item|CREATE|


LOAD DATA
INFILE "&DIR_PATH"
APPEND INTO TABLE XXGE_ITEM_IMPORT_STG
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(ORGANIZATION_CODE          ,
  SEGMENT1                   ,
  DESCRIPTION                ,
  LONG_DESCRIPTION           ,
  TEMPLATE_NAME              ,
  TRANSACTION_TYPE           ,
  CREATION_DATE            "SYSDATE",
  CREATED_BY                   CONSTANT "-1" ,
  LAST_UPDATE_DATE     "SYSDATE",
  LAST_UPDATED_BY        CONSTANT "-1" ,
  PROCESS_FLAG              CONSTANT "N" )


CREATE OR REPLACE PACKAGE XXGE_ITEM_IMPORT_PKG IS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
               RETCODE OUT NUMBER);
END;   
/
CREATE OR REPLACE PACKAGE BODY XXGE_ITEM_IMPORT_PKG IS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
               RETCODE OUT NUMBER) IS
              
        lv_template_name            VARCHAR2(30);
        lb_error_flag               BOOLEAN;
        lv_error_message            VARCHAR2(4000);   
        ln_organization_id          NUMBER;      
        lv_segment1                 VARCHAR2(40);   
        ln_cnt                      NUMBER := 0;
        ln_s_cnt                    NUMBER := 0;
        ln_e_cnt                    NUMBER := 0;
BEGIN
        FND_FILE.PUT_LINE(FND_FILE.LOG,'*****GE Item Import program started*****  '
                                        ||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));

        FOR rec_item IN (SELECT a.rowid rowid1,
                                a.*
                           FROM XXGE_ITEM_IMPORT_STG a
                          WHERE NVL(process_flag,'N') = 'N')
        LOOP
       
        ln_cnt := ln_cnt + 1;
       
        lb_error_flag       := FALSE;
        lv_error_message    := '';
        ln_organization_id  := NULL;
       
        BEGIN
            SELECT template_name
              INTO lv_template_name
              FROM MTL_ITEM_TEMPLATES
             WHERE template_name = rec_item.template_name;      
       
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 lb_error_flag := TRUE;
                 lv_error_message := 'Template name provided is invalid';
            WHEN OTHERS THEN
                 lb_error_flag := TRUE;
                 lv_error_message := 'Error while validating template name:-'||SQLERRM;           
        END;
       
       
        BEGIN
            SELECT organization_id
              INTO ln_organization_id
              FROM MTL_PARAMETERS
             WHERE organization_code = rec_item.organization_code;      
       
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 lb_error_flag := TRUE;
                 lv_error_message := lv_error_message ||', '|| 'Organization code provided is invalid';
            WHEN OTHERS THEN
                 lb_error_flag := TRUE;
                 lv_error_message := lv_error_message ||', '||'Error while validating organization code:-'||SQLERRM;           
        END;       
       
       
        IF ln_organization_id IS NOT NULL THEN
       
        BEGIN
            SELECT segment1
              INTO lv_segment1
              FROM MTL_SYSTEM_ITEMS_B
             WHERE segment1 = rec_item.segment1
               AND organization_id = ln_organization_id ;  
              
                 lb_error_flag := TRUE;
                 lv_error_message := lv_error_message ||', '|| 'Item alredy defined in inventory';               
                 
       
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 NULL;
            WHEN OTHERS THEN
                 lb_error_flag := TRUE;
                 lv_error_message := lv_error_message ||', '||'Error while validating organization code:-'||SQLERRM;           
        END;     
       
        END IF;  
              
       
       
       
        IF lb_error_flag = FALSE THEN
       
        ln_s_cnt := ln_s_cnt + 1;
       
            INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
                        (template_name,
                         organization_code,
                         segment1,
                         description,
                         long_description,
                         request_id,
                         process_flag,
                         transaction_type,
                         created_by,
                         creation_date,
                         last_updated_by,
                         last_update_date)
                  VALUES(NULL,--rec_item.template_name,
                         rec_item.organization_code,
                         rec_item.segment1,
                         rec_item.description,
                         rec_item.long_description,
                         FND_GLOBAL.CONC_REQUEST_ID,
                         1,
                         rec_item.transaction_type,
                         FND_GLOBAL.USER_ID,
                         SYSDATE,
                         FND_GLOBAL.USER_ID,
                         SYSDATE);
                        
            UPDATE XXGE_ITEM_IMPORT_STG
               SET process_flag = 'S'
             WHERE rowid = rec_item.rowid1;
           
        ELSE
       
        ln_e_cnt := ln_e_cnt + 1;
       
            UPDATE XXGE_ITEM_IMPORT_STG
               SET process_flag = 'E',
                   error_message = lv_error_message
             WHERE rowid = rec_item.rowid1;       
       
       
        END IF;
       
       
        END LOOP;
       
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Records processed   :-'||ln_cnt);
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Total records successed   :-'||ln_s_cnt);
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Total records got rejected:-'||ln_e_cnt);
        FND_FILE.PUT_LINE(FND_FILE.LOG,'*****GE Item Import program ended*****  '
                                        ||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));

EXCEPTION
        WHEN OTHERS THEN
             FND_FILE.PUT_LINE(FND_FILE.LOG,'Errnum:-'||SQLCODE||'-'||
                                            'Errmsg:-'||SQLERRM);
             ROLLBACK;
             RETCODE := 2;              
END;
END;
/