GL Daily Rates Open Interface By Avinash and Rahul

OPEN INTERFACE FOR DAILY RATES

    Oracle General Ledger ever since Release 11 provides a mechanism to load automatically the GL_DAILY_RATES table.It is possible to insert, update or delete records
 in GL_DAILY_RATES using interface table GL_DAILY_RATES_INTERFACE.

 There is a trigger over GL_DAILY_RATES_INTERFACE table;
this trigger provides the mechanism to load data in GL_DAILY_RATES.
 The structure of table GL_DAILY_RATES_INTERFACE can be found in the User?s Guide Manual for General Ledger module (See references).
It is not supported to insert records directly in GL_DAILY_RATES; this can cause corruption in the daily rates data.
Each row defined in the interface table defines the daily currency rate from one currency to another,
for a specific date and period and the operation (Insert or Update) to be done.
For example, define a Corporate conversion rate from USD to CLP of 555 during January 2000 requires to insert in GL_DAILY_RATES_INTERFACE the following values:

•From_currency : USD
•To_currency : CLP
•From_conversion_date : 01-JAN-2000
•To_conversion_date : 31-JAN-2000
•User_conversion_type : Corporate
•User_conversion_rate : 555
•Mode_flag : I

   The formula used to find the amount converted is:

To_Currency_Amount = Conversion_Rate * From_Currency_Amount
   This means, you have to define a direct rate in gl_daily_rates or in gl_daily_rates_interface; do not use an inverse rate.

    If the profile Daily Rates Window: Enforce Inverse Relationship During Entry is defined to Yes,
when you enter a daily rate to convert from Currency I to Currency II,
General Ledger automatically calculates the inverse rate (Currency II to Currency I) and enters it in the adjacent column.



Trigger GL_DAILY_RATES_INTERFACE_AI
   Trigger GL_DAILY_RATES_INTERFACE_AI is defined over GL_DAILY_RATES_INTERFACE table.
 It creates and deletes the data in GL_DAILY_RATES after an INSERT statement in GL_DAILY_RATES table.
Trigger inserts or deletes records in GL_DAILY_RATES depending on the value of column Mode_Flag.
 If you define D value, the trigger deletes the corresponding rates in GL_DAILY_RATES, and if you define I value it inserts rates in GL_DAILY_RATES.

   This trigger makes the following verifications:

•Conversion_type exists
•Conversion_rate is not a negative number
•Inverse_conversion_rate is not a negative number
•Range of Dates specified does not exceed 366 days.
•From_Currency and To_Currency:

a. Currency exists in the FND_CURRENCIES table
b. Currency is enabled
c. Currency is not a statistical currency
d. Currency is not out of date
e. Currency is not an EMU currency
   Whenever the trigger does not detect an error condition, the corresponding records are
   automatically deleted in GL_DAILY_RATES_INTERFACE and inserted in GL_DAILY_RATES.
If an error condition is found, the trigger updates the column Mode_flag with an X value and
  Error_code is defined with one of the following message codes:



Message Code Description
Nonexistant_Conversion_Type Specified conversion type does not exist in table GL_DAILY_CONVERSION_TYPES.
Date_Range_Too_Large The number of days between to_conversion_date and from__conversion_date is greater than 367 or one year
Negative_Conversion_Rate Conversion rate in GL_DAILY_RATES_INTERFACE is negative
Negative_Inverse_Rate Inverse conversion rate in GL_DAILY_RATES_INTERFACE is negative
Nonexistant_From_Currency Specified from currency does not exist in table FND_CURRENCIES
Disabled_From_Currency The currency is disabled in FND_CURRENCIES
Statistical_From_Currency Statistical currency does not need conversion rate
Out_Of_Date_From_Currency The active period of the from currency is not included the conversion rate period
Emu_From_Currency  
Nonexistant_To_Currency There is not a valid currency code
Disabled_To_Currency The currency is disabled or non active in FND_CURRENCIES
Out_Of_Date_To_Currency The to_currency effective period does not match with conversion rate period
Emu_To_Currency Others



   How To Enter The Data Into Interface Table
   Use SQL*Plus, PL/SQL, SQL*Loader or other tool in order to manipulate information in GL_DAILY_INTERFACE table.

   Here is an example of control, data and command using sql loader.

   Control File, rates.ctl:



LOAD DATA
APPEND
INTO TABLE GL_DAILY_RATES_INTERFACE
FIELDS TERMINATED BY ','
(FROM_CURRENCY CHAR
, TO_CURRENCY CHAR
, FROM_CONVERSION_DATE DATE
, TO_CONVERSION_DATE DATE
, USER_CONVERSION_TYPE CHAR
, CONVERSION_RATE DECIMAL EXTERNAL
, MODE_FLAG CHAR)

    Data File, rates.txt:



CLP,USD,01-SEP-00,30-SEP-00,Corporate,501.99,I
CLP,USD,01-OCT-00,31-OCT-00,Corporate,503.99,I
CLP,USD,01-NOV-00,30-NOV-00,Corporate,507.99,I

    Command Line:



sqlldr gl/gl control=rates.ctl log=rates.log bad=rates.bad data=rates.txt
    Some Useful Queries To Check The Interface Table
Select count(*)
from gl_daily_rates_interface;

Select count(*)
from
gl_daily_rates;

Select from_currency
, to_currency
, min(conversion_date)
, max(conversion_date)
from gl_daily_rates
group by from_currency, to_currency;

Select from_currency
, to_currency
, from_conversion_date
, to_conversion_date
, user_conversion_type
, conversion_rate
, mode_flag
, error_code
from gl_daily_rates_interface;