GL Daily rates conversion in oracle apps

GL Daily rates conversion in oracle apps

In this post , We will be discuss about GL Daily rates conversion in oracle apps. GL daily rates conversion is the process to entering the Exchange rate between the Currencies in Oracle apps. We Enter GL daily rates in the General Ledger in oracle apps but we are working with many foreign currencies then its quite tuff to enter the GL daily manually in oracle apps r12 so for this , we can use the Oracle Standard Interface to do the GL Daily rates conversion in oracle apps. Here below I am sharing the complete code of the GL Daily rates conversion in oracle apps.

 

 

Script to do the GL Daily rates conversion in oracle apps

 

CREATE TABLE XX_STAG_RATES (

FROM_CURRENCY VARCHAR2(15),
TO_CURRENCY VARCHAR2(15),
FROM_DATE DATE,
END_DATE DATE,
CONV_TYPE VARCHAR2(30),
EXCHABGE_RATE NUMBER,
STATUS VARCHAR2(30));

 

DECLARE

Cursor C4 is

Select FROM_CURRENCY, TO_CURRENCY, FROM_DATE , END_DATE ,

CONV_TYPE, EXCHANGE_RATE , STATUS FROM XX_STAG_DAILY_RATES;

LV_FROM_CURRENCY VARCHAR2(15);

LV_TO_CURRENCY VARCHAR2(15);

LV_USER_CONVERSION_TYPE VARCHAR2(30);

LV_CONVERSION_RATE NUMBER;

LV_ERR_FLAG VARCHAR2(1):= 'Y';

BEGIN

FOR i IN C4

LOOP

BEGIN

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_from_currency := null;

lv_err_flag := 'E';

FND_FILE.PUT_line(FND_FILE.LOG,'CURRENCY IS INVALID');

end;

BEGIN

Select CURRENCY_CODE into LV_FROM_CURRENCY FROM

FND_CURRENCIES where CURRENCY_CODE=i.FROM_CURRENCY;

Exception

When NO_DATA_FOUND Then

lv_from_currency := null;

lv_err_flag := 'E';

FND_FILE.PUT_line(FND_FILE.LOG,'CURRENCY IS INVALID');

end;

FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code inserting IS--'

|| LV_TO_CURRENCY );

 

BEGIN

Select USER_CONVERSION_TYPE into LV_USER_CONVERSION_TYPE

FROM GL_DAILY_CONVERSION_TYPES where

USER_CONVERSION_TYPE=i.CONV_TYPE;

Exception

When NO_DATA_FOUND Then

LV_USER_CONVERSION_TYPE := null;

lv_err_flag := 'E';

FND_FILE.PUT_line(FND_FILE.LOG,'The CURRENCT CONVERSION TYPE is not defined.');
end;

 

IF LV_ERR_FLAG='Y' THEN

 

INSERT INTO GL_DAILY_RATES_INTERFACE (

FROM_CURRENCY, TO_CURRENCY,

FROM_CONVERSION_DATE, TO_CONVERSION_DATE,

USER_CONVERSION_TYPE, CONVERSION_RATE,

MODE_FLAG)

VALUES (

LV_FROM_CURRENCY,LV_TO_CURRENCY,

I.FROM_DATE, I.END_DATE

, LV_USER_CONVERSION_TYPE, I.EXCHANGE_RATE

,'I');

 

END IF;

 

END LOOP;

 

COMMIT;

END;