For Daily Currency
conversion interface can be entered by 2 ways, either
1.)
to GL_INTERFACE
table To import multi-currency data
(Currency conversion) or
2.)
to Gl_DAILY_RATES_INTERFACE
to
import multi-currency data (Currency conversion)
1.) The following
process is explained from GL_INTERFACE table ,
Other sample codes will get from blog spot GL Interface and Its process
Load multi-currency
data into the GL_INTERFACE table the same way you load
Regular data.
If you want your
General Ledger application to calculate your conversion,
you must enter a
value in the
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE and USER_CURRENCY_CONVERSION_TYPE
columns of the
GL_INTERFACE table.
If the conversion
type is User, you must also enter a value in the
CURRENCY_CONVERSION_RATE
column of the GL_INTERFACE table. Or, you can
directly specify the
converted amounts by entering values in the ACCOUNTED_DR
and ACCOUNTED_CR
columns. If you choose to enter your converted amounts, do not
specify the
conversion rate, type and date.
2.) The following
process is explained from Gl_DAILY_RATES_INTERFACE
table
Procedure:
1) We populate the Staging Table
2) Performed Validation on Staging Table.
3) Populate Gl_DAILY_RATES_INTERFACE
4) Used Standard Concurrent Program : Program - Daily Rates Import and Calculation.
Interface table for GL
Daily Rates are—
Gl_DAILY_RATES_INTERFACE
Base tables for GL
Daily Rates are—
Gl_DAILY_RATES
Create a Staging
table based on the requirement
CREATE TABLE XXGL_DRATES_STG
(
FROM_CURRENCY VARCHAR2 (15),
TO_CURRENCY VARCHAR2 (15),
FROM_CONVERSION_DATE DATE,
TO_CONVERSION_DATE DATE,
USER_CONVERSION_TYPE VARCHAR2 (30),
CONVERSION_RATE NUMBER,
MODE_FLAG CHAR (1)
);
Inserting Data into
Staging Table:
Insert into XXGL_DRATES_STG Values (
'USD','INR','29-Jan-2009','31-Jan-2009','Corporate','50','I');
Moving the Data from
Flat File to Base Table using SQL * LOADER:
options(skip=1)
load data
errors=5
infile '/ebs/oracle/apps/apps_st/appl/gl/12.0.0/bin/gl_daily_rates.csv'
Insert into table
GL_daily_rates_stg
fields terminated by
','
optionally enclosed
by '"'
Trailing nullcols
(From_currency
,To_currency, From_conversion_date, To_conversion_date,
User_conversion_type, conversion_rate,
Mode_flag)
Moving the data from
Staging tables to Base Tables using
Standard Interface
Programs:
Create a Package with
validations to move the data into Interface Tables
CREATE OR REPLACE PACKAGE XXGL_DRATES_PKG
IS
PROCEDURE DAILY_RATES_PRC (retcode OUT NUMBER, errbuff OUT VARCHAR2);
END;
------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY XXGL_DRATES_PKG
IS
PROCEDURE DAILY_RATES_PRC (retcode OUT NUMBER, errbuff OUT VARCHAR2)
IS
CURSOR cur_drates
IS
SELECT FROM_CURRENCY,
TO_CURRENCY,
FROM_CONVERSION_DATE,
TO_CONVERSION_DATE,
USER_CONVERSION_TYPE,
CONVERSION_RATE,
MODE_FLAG
FROM XXGL_DRATES_STG;
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) := 'A';
BEGIN
FOR i IN CUR_DRATES
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,
'The Currency Code is
not defined
/not enabled if not enabled
enable it.');
END;
FND_FILE.PUT_line (
FND_FILE.LOG,
'The Currency Code inserting IS--' || LV_FROM_CURRENCY);
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,
'The Currency Code is
not defined
/not enabled if not enabled
enable it.');
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.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 is not defined.');
END;
FND_FILE.PUT_line (
FND_FILE.LOG,
'The USER_CONVERSION_TYPE
inserting IS--'
|| LV_USER_CONVERSION_TYPE);
BEGIN
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 is not defined.');
END;
FND_FILE.PUT_line (
FND_FILE.LOG,
'The USER_CONVERSION_TYPE
inserting IS--'
|| LV_USER_CONVERSION_TYPE);
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)
VALUES (LV_FROM_CURRENCY,
LV_TO_CURRENCY,
I.FROM_CONVERSION_DATE,
I.TO_CONVERSION_DATE,
LV_USER_CONVERSION_TYPE,
I.CONVERSION_RATE,
I.MODE_FLAG);
END IF;
END LOOP;
COMMIT;
END;
END XXGL_DRATES_PKG;
Run the Standard Concurrent Program = Program
- Daily Rates Import and Calculation
No comments:
Post a Comment