Introduction
General Ledger provides the GL_DAILY_RATES_INTERFACE table that you can use to automatically insert, update, or delete daily rates in the GL_DAILY_RATES table. General Ledger validates the rows in the interface table before making changes in the GL_DAILY_RATES table.Warning: Always use the interface table to load your daily rates into General Ledger. Do not load rates directly into the GL_DAILY_RATES table, since this can corrupt your daily rates data.
When General Ledger process the interface table, the system follows the behavior described below:
If you specify a range of conversion dates, the system inserts, updates, or deletes one row in GL_DAILY_RATES for each date in your range. For Example:
From-currency: JPY
To-currency: USD
Conversion date range: 01-OCT-97 to 03-OCT-97
User conversion type: Spot
Conversion rate: .0083
And you are inserting new rates, General Ledger will insert three new rows into GL_DAILY_RATES with the following information:
From Currency To Currency Conversion Date User Conversion Type Conversion Rate
------------------ -------------- -------------------- -------------------------- --------------------
JPY USD 01-OCT-97 Spot .0083
JPY USD 02-OCT-97 Spot .0083
JPY USD 03-OCT-97 Spot .0083
General Ledger automatically inserts, updates, or deletes the corresponding inverse rates row in GL_DAILY_RATES. Using the same example as above, General Ledger will insert three additional rows into GL_DAILY_RATES with the following information.
From Currency To Currency Conversion Date User Conversion Type Conversion Rate
------------------ -------------- -------------------- -------------------------- --------------------
USD JPY 01-OCT-97 Spot 120.482
USD JPY 02-OCT-97 Spot 120.482
USD JPY 03-OCT-97 Spot 120.482
The GL_DAILY_RATES_INTERFACE Table
The insert, update, or deletion of rates in GL_DAILY_RATES is done automatically by database triggers on the GL_DAILY_RATES_INTERFACE table. You do not need to run any import programs. You only need to develop an automated process that populates the interface with your daily rates information.The columns in the GL-DAILY_RATES_INTERFACE are described below.
Column Name Null? Type
FROM_CURRENCY NOT NULL VARCHAR2(15)
TO_CURRENCY NOT NULL VARCHAR2(15)
FROM_CONVERSION_DATE NOT NULL DATE
TO_CONVERSION_DATE NOT NULL DATE
USER_CONVERSION_TYPE NOT NULL VARCHAR2(30)
CONVERSION_RATE NOT NULL NUMBER
MODE_FLAG NOT NULL VARCHAR2(1)
INVERSE_CONVERSION_RATE NUMBER
USER_ID NUMBER(15)
ERROR_CODE VARCHAR2(30)
LAUNCH_RATE_CHANGE VARCHAR2(1)
CONTEXT VARCHAR2(150)
ATTRIBUTE1 VARCHAR2(150)
ATTRIBUTE2 VARCHAR2(150)
ATTRIBUTE3 VARCHAR2(150)
ATTRIBUTE4 VARCHAR2(150)
ATTRIBUTE5 VARCHAR2(150)
ATTRIBUTE6 VARCHAR2(150)
ATTRIBUTE7 VARCHAR2(150)
ATTRIBUTE8 VARCHAR2(150)
ATTRIBUTE9 VARCHAR2(150)
ATTRIBUTE10 VARCHAR2(150)
ATTRIBUTE11 VARCHAR2(150)
ATTRIBUTE12 VARCHAR2(150)
ATTRIBUTE13 VARCHAR2(150)
ATTRIBUTE14 VARCHAR2(150)
ATTRIBUTE15 VARCHAR2(150)
USED_FOR_AB_TRANSLATION VARCHAR2(1)
Required and Conditionally Required Columns The field descriptions below are based on the example below.
Required Columns
FROM_CURRENCY
The source currency applicable to the conversion rate. The amount denominated in the from-currency multiplied by the conversion rate gives the amount denominated in the to-currency.
TO_CURRENCY
The target currency applicable to the conversion rate.
FROM_CONVERSION_DATE
The starting date of the range of dates for which rows will be inserted into GL_DAILY_RATES. General Ledger will insert one row for each date in the range. Each date will have the same conversion rate you specify.
TO_CONVERSION_DATE
The ending date of the range of dates fro which rows will be inserted into GL_ DAILY_RATES. The range of dates specified by FROM_CONVERSION_DATE and TO_CONVERSION_DATE cannot exceed 366 days.
USER_CONVERSION_TYPE
The conversion type that users see displayed in the Daily Rates window. General Ledger automatically converts the user conversion type into the conversion type ID that is stored in the GL_DAILY_RATES table.
CONVERSION_RATE
The currency conversion rate. This is the rate by which the amount denominated in the from-currency is multiplied to arrive at the amount denominated in the to-currency. If the row you are entering in the interface table is to delete rates in GL_DAILEY_RATES, enter a dummy CONVERSION_RATE.
MODE_FLAG
For each row enter a
D - if you want to delete matching rows from the GL_DAILY_RATES table
I - if you want to insert new rows.
If you specify 'I' as the MODE_FLAG and the combination or from-currency, to-currency, conversion date, and user conversion type already exist in GL_DAILY_RATES, the existing rate will be updated with the new rate you specified in the interface table.
If you specify 'D' as the MODE_FLAG, General Ledger will also delete corresponding inverse rates rows in GL_DAILY_RATES.
Any rows you enter in GL_DAILY_RATES_INTERFACE that fail validation will remain in the interface table and will not be moved to GL_DAILY_RATES. Also, the mode flag will change to X and the error code column will be populated. Use a SQL*Plus SELECT Statement to check for any of the rows you loaded into the interface table failed validation.
You cannot reprocess rejected rows that remain in the interface table after failing validation. To process the correct data, you must first delete the rejected rows from the interface table then enter the correct data as new rows in the table. The new data will be processed as usual.
Optional Columns
INVERSE_CONVERSION_RATE
The inverse of the conversion rate. This is the rate by which the amount denominated in the to-currency is multiplied to arrive at the amount denominated in the from-currency.
If you do not provide this value, General Ledger will calculate the inverse rate from the CONVERSION_RATE column and insert the appropriate inverse rate rows into GL_DAILY_RATES.
USER_ID
The user ID of the person who is adding rows to the interface table. To determine the user ID for a specific user name, use the following SQL*Plus statement:
select user_id
from fnd_user
where user.name='<user name>'
from fnd_user
where user.name='<user name>'
LAUNCH_RATE_CHANGE
If you want the rate change program to run automatically, enter a 'Y' in LAUNCH_RATE_CHANGE column for one row of the rates you are loading. Leave this column blank for the remaining rows. Otherwise, multiple concurrent requests will launched when only one is required to load all of your rates.
When a daily rate has changed, the rate change program will outdate average translations in those average balance sets of books that use the changed daily rate.
CONTEXT
The descriptive flexfield context.
ATTRIBUTE1 through ATTRIBUTE15
Any descriptive flexfield information associated with the daily rate.
Other Columns
ERROR_CODE
The text of the error message you receive if the row in the interface table failed validation. This column is used by the system. No user entry is needed.
USED_FOR_AB_TRANSLATION
This column is used internally by General Ledger when coping rates to GL_DAILY_RATES. Do not make any entries in this column.
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;
Loading 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;
Sources:
No comments:
Post a Comment