GL Daily rates Interface in Oracle Apps R12

create or replace PACKAGE XXFIN_GL_XE_DRATES_PKG
IS

  PROCEDURE XXFIN_DAILY_RATES_PROC(
      retcode OUT NUMBER,
      errbuff OUT VARCHAR2);
 
END XXFIN_GL_XE_DRATES_PKG;
/


create or replace PACKAGE BODY XXFIN_GL_XE_DRATES_PKG
IS

 
PROCEDURE XXFIN_DAILY_RATES_PROC(
    retcode OUT NUMBER,
    errbuff OUT VARCHAR2)
IS
 
  CURSOR cur_drates
  IS
    SELECT gers.from_currency from_currency ,
      gers.amount amount ,
      'Corporate' user_conversion_type ,
      'I' MODE_FLAG ,
      gers.from_conversion_date conversion_date
      -- ,substr(gers.from_conversion_date,1,10) conversion_date_w
      ,
      SUBSTR(gers.from_conversion_date,1,10)
      ||' '
      ||SUBSTR(gers.from_conversion_date,12,8) Conversion_date_from ,
      SUBSTR(gers.from_conversion_date,1,10)
      ||' '
      ||SUBSTR(gers.from_conversion_date,12,8) Conversion_date_to ,
      gers.to_currency to_currency ,
      gers.conversion_rate conversion_rate
    FROM xxfin_gl_exchange_rates_stg gers
    WHERE SUBSTR(gers.from_conversion_date,1,10)=TO_CHAR(sysdate,'YYYY-MM-DD');
 
  LV_FROM_CURRENCY        VARCHAR2(15);
  LV_FROM_CURRENCY2        VARCHAR2(15);
  LV_TO_CURRENCY          VARCHAR2(15);
  LV_TO_CURRENCY2          VARCHAR2(15);
  LV_USER_CONVERSION_TYPE VARCHAR2(30);
  LV_USER_CONVERSION_TYPE2 VARCHAR2(30);
  LV_CONVERSION_RATE      NUMBER;
  LN_USER_ID              NUMBER;
  LV_DATE_FROM DATE;
  LV_DATE_TO DATE;
  LV_STATUS varchar2(10);
  LN_ACCESS_SET_ID NUMBER(20);
  LN_LEDGER_ID  NUMBER(20);
  LN_APPLID NUMBER(20);
  LV_UC_TYPE              VARCHAR2(100);
  LV_ERR_FLAG             VARCHAR2(1):= 'A';
 
BEGIN
 
  FOR i IN cur_drates
  LOOP
 
  LV_ERR_FLAG:='A';
 
   
    BEGIN
      LN_USER_ID:=FND_GLOBAL.USER_ID;
    END;
   
   
    --start from currency validation
    BEGIN
    LV_FROM_CURRENCY2:=i.from_currency;
      SELECT CURRENCY_CODE
      INTO LV_FROM_CURRENCY
      FROM FND_CURRENCIES
      WHERE  CURRENCY_CODE=i.from_currency
         AND ENABLED_FLAG='Y';
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      lv_from_currency := NULL;
      lv_err_flag      := 'E';
      FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code '||LV_FROM_CURRENCY2||' is not defined or not enabled.');
    END;
   
     --start to currency validation
    BEGIN
    LV_FROM_CURRENCY2:=i.to_currency;
      SELECT CURRENCY_CODE
      INTO LV_TO_CURRENCY
      FROM FND_CURRENCIES
      WHERE ENABLED_FLAG='Y'
      AND CURRENCY_CODE =i.to_currency;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      LV_TO_CURRENCY := NULL;
      lv_err_flag      := 'E';
   
     FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code '||LV_TO_CURRENCY2||' is not defined or not enabled.');
    END;
     --end to currency validation
   
   
    --start user conversion type validation.
    BEGIN
    LV_USER_CONVERSION_TYPE2:=i.user_conversion_type;
      SELECT USER_CONVERSION_TYPE
      INTO LV_USER_CONVERSION_TYPE
      FROM GL_DAILY_CONVERSION_TYPES
      WHERE USER_CONVERSION_TYPE=i.user_conversion_type;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      LV_USER_CONVERSION_TYPE := NULL;
      lv_err_flag             := 'E';
      FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE '||LV_USER_CONVERSION_TYPE2||' is not defined.');
    END;
    --end user conversion type validation
   
    --start validating dates
    begin
    LV_DATE_FROM:=TO_DATE(i.Conversion_date_from,'YYYY-MM-DD HH24:MI:SS');
    LV_DATE_TO:=TO_DATE(i.Conversion_date_to,'YYYY-MM-DD HH24:MI:SS');
    LN_ACCESS_SET_ID:= FND_PROFILE.value('GL_ACCESS_SET_ID');
    LN_LEDGER_ID:=GL_ACCESS_SET_SECURITY_PKG.get_default_ledger_id(ln_access_set_id,'R');
    LN_APPLID:=fnd_profile.value('RESP_APPL_ID');
    SELECT PS.CLOSING_STATUS
    INTO LV_STATUS
    FROM gl_period_statuses PS
    WHERE PERIOD_NAME= TO_CHAR(TO_DATE(LV_DATE_FROM),'MON-YY')
      AND PS.APPLICATION_ID=LN_APPLID
      AND PS.LEDGER_ID=LN_LEDGER_ID;
     
 
  IF LV_STATUS in ('O','F') THEN
     NULL;
  ELSE
     lv_err_flag             := 'E';
     FND_FILE.PUT_line(FND_FILE.LOG,'Date '||LV_DATE_FROM||' is not in an open or future');
  END IF;
 
  exception
  when no_data_found then
  FND_FILE.PUT_line(FND_FILE.LOG,'Date '||LV_DATE_FROM||' is not in an open or future for this combination'||LN_APPLID||','||LN_LEDGER_ID||','||LN_ACCESS_SET_ID);
  when others then
  FND_FILE.PUT_line(FND_FILE.LOG,'Error caused by date issue '||SQLCODE||','||SQLERRM);
end;
    --end validating dates
   
   
    IF LV_ERR_FLAG='A' 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
        (
          LV_FROM_CURRENCY,
          LV_TO_CURRENCY,
          LV_DATE_FROM,
          LV_DATE_TO,
          LV_USER_CONVERSION_TYPE,
          i.conversion_rate,
          i.MODE_FLAG,
          LN_USER_ID);
    END IF;
  END LOOP;
  COMMIT;
END XXFIN_DAILY_RATES_PROC;

END XXFIN_GL_XE_DRATES_PKG;
/