GL Daily rates interface in oracle apps r12

GL Daily rates interface in oracle apps r12

In this post , We will be discuss about GL Daily rates interface in oracle apps r12. GL daily rates means , We need to enter the foreign currency amount for the dates in oracle system so that we can create transactions in that currency. For Example , My Ledger currency is 'INR' and I can easily enter the AP invoice for the currency 'INR' without entering the GL daily rates but if I want to enter the AP Invoice for the currency 'USD' then first I need to enter the GL Daily rates/Exchange rate for USD to INR in the system before entering the AP invoice for USD currency. Here below is the source code of GL Daily rates interface in oracle apps r12.

 

 

PLSQL Sample code of GL Daily rates interface in oracle apps r12 

CREATE TABLE XX_STAG_DAILY_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 C1 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 C1

LOOP

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;

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;

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;