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;
/
