GL interface tables in oracle apps r12

GL interface tables in oracle apps r12

In this post , We will be discuss about the gl interface tables in oracle apps r12. GL interface tables helps to import or upload the GL journals from external application into the oracle apps. GL interface play the roles of mediator or bride in which we do insert the GL journal data from external system and then these GL interface tables migrates these data from the GL interface into Oracle apps application. We just need to prepare the GL data in the format of GL interface tables in oracle. Here below is the detail information about gl interface tables in oracle apps r12.

Most Important gl interface tables in oracle apps r12

GL_INTERFACE

Important Points about gl interface tables in oracle apps r12

1.      We need to put the Data in the GL Interface table to import in Oracle Application.

2.      We need to prepare and transform the data in the format of gl Interface table. We need to do the GL data conversion for GL Interface tables if we are importing the journals from non-oracle environment.

3.      Once the data Uploaded into the GL Interface tables , need to run the Oracle Standard program ‘Run the Journal Import program’which do transfers the data from GL Interface tables to Oracle GL Base tables in oracle apps r12.

GL Interface Script Using GL interface tables in oracle apps r12

      Declare

cursor xx_gl_stag
IS
SELECT set_of_books_id,JE_Source_name,
je_category_name,currency,actual_flag,
ACCOUNTING_DATE,DATE_CREATED,CREATED_BY,entered_dr,
entered_cr,accounted_dr,accounted_cr,
segment1, segment2, segment3, segment4, segment5,segment6
FROM XX_GL_STAG;

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 v_gl_stag in xx_gl_stag

LOOP

lv_flag := 'A';

lv_err_flag := 'A';

BEGIN

SELECT user_je_source_name into lv_user_je_source_name FROM GL_JE_SOURCES

WHERE user_je_source_name=v_gl_stag.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');

END;

BEGIN

SELECT set_of_books_id into lv_sob_id from gl_sets_of_books

where set_of_books_id=v_gl_stag.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 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=v_gl_stag.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');

END;

BEGIN

SELECT currency_code into lv_cur_code from FND_CURRENCIES

where currency_code=v_gl_stag.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_STAG

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,segment6)

VALUES (

lv_Status, lv_sob_id, lv_User_JE_Source_name, lv_user_je_category_name,

lv_cur_code,lv_actual_flag,v_gl_stag.ACCOUNTING_DATE, v_gl_stag.DATE_CREATED,

12423,v_gl_stag.entered_dr, v_gl_stag.entered_cr, v_gl_stag.accounted_dr,v_gl_stag.accounted_cr,

v_gl_stag.segment1, v_gl_stag.segment2, v_gl_stag.segment3, v_gl_stag.segment4, v_gl_stag.segment5,v_gl_stag.segment6);

END IF;

lv_flag :=null;

lv_err_flag:=null;

END LOOP;

COMMIT;

End;

Click To Know More