GL Daily Rates Interface Steps

GL Daily Rates Interface Steps

Interface table for GL Daily Rates are—

Gl_DAILY_RATES_INTERFACE

Base tables for GL Daily Rates are—

Gl_DAILY_RATES

Moving the Data from Flat File to Base Table using SQL * LOADER:

Options (Skip =0)

Load data

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 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');

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;

 Package Body

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;

Register the Procedure as a Concurrent Programe

Create an Executable – XXGL_DRATES_PKG_EXEC

Execution File

Create a Concurrent program – XXGL_DRATES_PKG_EXEC IFace Conc prg

Add the Conc program to the Request group

In custom module, Run the Conc Program thro’ SRS Window.

In GL MODULE Run the Standard Concurrent Program –

Program - Daily Rates Import and Calculation