GL Interface

Interface tables:

GL_INTERFACE

Base tables:

GL_JE_HEADERS

GL_JE_LINES

GL_JE_BACTHES

Concurrent Program: Journal Import

Journal Posting  --- populates GL_BALANCES

Validations: check SOB, journal source name, journal category name, actual flag

A – actual amounts

B – budget amounts

E – encumbrance amount

If u enter E in the interface table, then enter appropriate encumbrance ID.

B – budget id.

Check if accounting date or GL date based period name is valid (i.e., not closed).

Check if accounting date falls in open or future open period status.

Check chart of accounts id based on Sob id.

Check if valid code combination.

Check if ccid is enabled.

Check if record already exists in GL interface table.

Check if already journal exists in GL application.

Validations for the staging table:

Check if the input data file is already uploaded into staging table.

Check if the record already exists in the interface table.

Check if the journal already exists in the GL application.

Staging Table:

Create table XX_GL_RY_STG

(status varchar2(50),

set_of_books_id number(15),

User_JE_Source_name varchar2(25),

user_je_category_name varchar2(25),

currency_code varchar2(15),

actual_flag char(1),

ACCOUNTING_DATE date,

DATE_CREATED date,

CREATED_BY number(15),

entered_dr number,

entered_cr number,

accounted_dr number,

accounted_cr number,

segment1 varchar2(25),

segment2 varchar2(25),

segment3 varchar2(25),

segment4 varchar2(25),

segment5 varchar2(25)

);

insert into XX_GL_RY_STG values(

'NEW',1,'Manual' ,'Adjustment','USD','A','20-MAR-2009' ,'20-MAR-2009', 2000,2000,2000,2000,

'01','000','9950','2080','000','0')

Package:

CREATE OR REPLACE PACKAGE XX_GL_INT_RY_PKG

IS

PROCEDURE xx_gl_int_prc(errbuf out varchar2,Retcode out varchar2);

END;

CREATE OR REPLACE Package body XX_GL_INT_RY_PKG

is

Procedure xx_gl_int_prc(errbuf out varchar2,Retcode out varchar2)

is

cursor GL_CUR

IS

SELECT Status,set_of_books_id,User_JE_Source_name,

user_je_category_name,currency_code,actual_flag,

ACCOUNTING_DATE,DATE_CREATED,CREATED_BY,entered_dr,

entered_cr,accounted_dr,accounted_cr,

segment1, segment2, segment3, segment4, segment5

FROM XX_GL_RY_STG;

lv_status varchar2(50);

lv_sob_id Number(15);

lv_user_je_source_name varchar2(25);

lv_user_je_category_name varchar2(25);

lv_cur_code varchar2(15);

lv_actual_flag varchar2(1);

lv_err_flag varchar2(2);

lv_flag varchar2(2);

BEGIN

FOR rec in GL_CUR

LOOP

lv_flag := 'A';

lv_err_flag := 'A';

BEGIN

SELECT distinct Status into lv_status from XX_GL_RY_STG Where status = 'NEW';

EXCEPTION

When no_data_found Then

lv_status := null;

lv_flag := 'E';

lv_err_flag := 'E';

FND_FILE.PUT_line(FND_FILE.LOG,'The status is not correct so change the status');

FND_FILE.PUT_line(FND_FILE.LOG,'The data is inserting'|| lv_status );

END;

BEGIN

SELECT set_of_books_id into lv_sob_id from gl_sets_of_books

where set_of_books_id=rec.set_of_books_id;

Exception

When no_data_found Then

lv_sob_id:=null;

lv_flag := 'E';

lv_err_flag := 'E';

FND_FILE.PUT_line(FND_FILE.LOG,'The SOB is not correct change SOB ID');

End;

FND_FILE.PUT_line(FND_FILE.LOG,'The data is inserting'|| lv_sob_id );

BEGIN

SELECT user_je_source_name into lv_user_je_source_name FROM GL_JE_SOURCES

WHERE user_je_source_name=rec.user_je_source_name;

EXCEPTION

WHEN no_data_found THEN

lv_user_je_source_name := NULL;

lv_flag := 'E';

lv_err_flag := 'E';

FND_FILE.PUT_line(FND_FILE.LOG,'The SOURCE NAME is not correct change It');

END;

FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_user_je_source_name );

BEGIN

SELECT user_je_category_name INTO lv_user_je_category_name FROM GL_JE_CATEGORIES

where user_je_category_name=rec.user_je_category_name;

EXCEPTION

When no_data_found Then

lv_user_je_category_name:=NULL;

lv_flag := 'E';

lv_err_flag := 'E';

FND_FILE.PUT_line(FND_FILE.LOG,'The Category name is not correct Change it');

FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_user_je_category_name );

END;

BEGIN

SELECT currency_code into lv_cur_code from FND_CURRENCIES

where currency_code=rec.currency_code;

Exception

When no_data_found Then

lv_cur_code:=null;

lv_flag := 'E';

lv_err_flag := 'E';

FND_FILE.PUT_line(FND_FILE.LOG,'The Currency code is not correct ');

End;

FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_cur_code);

BEGIN

SELECT ACTUAL_FLAG into lv_actual_flag from XX_GL_RY_STG

where actual_flag in ('A','B','E');

Exception

When no_data_found then

lv_actual_flag := null;

lv_flag := 'E';

lv_err_flag := 'E';

FND_FILE.PUT_line(FND_FILE.LOG,'The Flag is not correct');

END;

FND_FILE.PUT_line(FND_FILE.LOG,'The dat inserting is... '|| lv_actual_flag);

IF lv_flag = 'A' THEN

INSERT into GL_INTERFACE (

STATUS, SET_OF_BOOKS_ID, USER_JE_SOURCE_NAME ,USER_JE_CATEGORY_NAME,

CURRENCY_CODE,ACTUAL_FLAG,

ACCOUNTING_DATE, DATE_CREATED,CREATED_BY, ENTERED_DR,ENTERED_CR,

ACCOUNTED_DR,ACCOUNTED_CR,segment1, segment2, segment3, segment4, segment5)

VALUES (

lv_Status, lv_sob_id, lv_User_JE_Source_name, lv_user_je_category_name,

lv_cur_code,lv_actual_flag,rec.ACCOUNTING_DATE, rec.DATE_CREATED,

1318,rec.entered_dr, rec.entered_cr, rec.accounted_dr,rec.accounted_cr,

rec.segment1, rec.segment2, rec.segment3, rec.segment4, rec.segment5);

END IF;

lv_flag :=null;

lv_err_flag:=null;

END LOOP;

COMMIT;

End;

END XX_GL_INT_RY_PKG;

/

Base tables for GL Daily Rates are—

Gl_DAILY_RATES

Interface table for GL Daily Rates are—

Gl_DAILY_RATES_INTERFACE

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;

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;

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