GL_DAILY_RATES Interface

SAMPLE DATA
----------------------------
USD|INR|27-MAR-2013|27-MAR-2013|DALY|54|I|
EUR|INR|27-MAR-2013|27-MAR-2013|DALY|75|I|
EUR|INR|27-MAR-2013|27-MAR-2013|DALYTUI|75|I|
GBP|INR|27-MAR-2013|27-MAR-2013|DALY|70|I|
TUIGBP|TUIINR|27-MAR-2013|27-MAR-2013|DALTTY|70|I|

Control file
-----------------------
LOAD DATA
INFILE "/Dev/devappl/DEV/apps/apps_st/appl/gl/12.0.0/data/TUI_GL_DAILY_DATA.txt"
APPEND INTO TABLE XXTUI_GL_DAILY_STG
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(FROM_CURRENCY          ,
 TO_CURRENCY            ,
 FROM_CONVERSION_DATE   ,
 TO_CONVERSION_DATE     ,
 USER_CONVERSION_TYPE   ,
 CONVERSION_RATE        ,
 MODE_FLAG              ,
 CREATION_DATE       "SYSDATE",
 CREATED_BY          "FND_GLOBAL.USER_ID",
 LAST_UPDATE_DATE    "SYSDATE" ,
 LAST_UPDATED_BY      CONSTANT "-1" ,
 PROCESS_FLAG         CONSTANT "N" )


Package Code
-------------------------------
CREATE OR REPLACE PACKAGE XXTUI_GL_DAILY_RATES_PKG IS
PROCEDURE XXTUI_GL_DAILY_RATES_PROC(ERRBUF OUT VARCHAR2,
                                    RETCODE OUT NUMBER);
END;       
/
CREATE OR REPLACE PACKAGE BODY XXTUI_GL_DAILY_RATES_PKG IS
PROCEDURE XXTUI_GL_DAILY_RATES_PROC(ERRBUF OUT VARCHAR2,
                                    RETCODE OUT NUMBER) IS
                                   
    lb_error_flag               BOOLEAN;   
    lv_from_currency            VARCHAR2(15);    
    lv_to_currency              VARCHAR2(15); 
    lv_user_conversion_type     VARCHAR2(30);                           
BEGIN

        FOR rec_gl_daily IN (SELECT a.rowid row_id,
                                    a.*
                               FROM XXTUI_GL_DAILY_STG a
                              WHERE process_flag = 'N')
        LOOP
       
        lb_error_flag           := FALSE;
       
       
       
--Vaildate from currency       
        BEGIN
            SELECT currency_code
              INTO lv_from_currency
              FROM FND_CURRENCIES
             WHERE currency_code = rec_gl_daily.from_currency
               AND enabled_flag = 'Y';     
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 lb_error_flag := TRUE;
            WHEN OTHERS THEN
                 lb_error_flag := TRUE;
        END;
       
       
--Vaildate to currency       
        BEGIN
            SELECT currency_code
              INTO lv_to_currency
              FROM FND_CURRENCIES
             WHERE currency_code = rec_gl_daily.to_currency
               AND enabled_flag = 'Y';     
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 lb_error_flag := TRUE;
            WHEN OTHERS THEN
                 lb_error_flag := TRUE;
        END;       
       
       
--Vaildate User conversion type       
        BEGIN
            SELECT user_conversion_type
              INTO lv_user_conversion_type
              FROM GL_DAILY_CONVERSION_TYPES
             WHERE user_conversion_type = rec_gl_daily.user_conversion_type;     
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                 lb_error_flag := TRUE;
            WHEN OTHERS THEN
                 lb_error_flag := TRUE;
        END;        
       
        IF lb_error_flag = FALSE THEN
       
            INSERT INTO GL_DAILY_RATES_INTERFACE
                        (from_currency,
                         to_currency,
                         from_conversion_date,
                         to_conversion_date,
                         user_conversion_type,
                         conversion_rate,
                         mode_flag,
                         user_id)
                  VALUES(rec_gl_daily.from_currency,
                         rec_gl_daily.to_currency,
                         rec_gl_daily.from_conversion_date,
                         rec_gl_daily.to_conversion_date,
                         rec_gl_daily.user_conversion_type,
                         rec_gl_daily.conversion_rate,
                         rec_gl_daily.mode_flag,
                         FND_GLOBAL.USER_ID);
                        
           UPDATE XXTUI_GL_DAILY_STG
              SET process_flag = 'S'
            WHERE rowid = rec_gl_daily.row_id;
           
        ELSE
       
           UPDATE XXTUI_GL_DAILY_STG
              SET process_flag = 'E'
            WHERE rowid = rec_gl_daily.row_id;       
       
                        
        END IF;
       
       
        END LOOP;                                                                                                                                                                                        


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