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 the GL Journals from External source into the Oracle apps r12. We need to prepare our External GL journals in the form of GL interface tables and then Import into the Oracle application. GL Interface table works like a Mediator between the External Source and with Oracle Apps. Once we have inserted the data in the GL Interface Tables , then we only need to run the Standard GL import program to import the GL journals from GL interface table in to the Oracle apps base tables. After Import completed usefully then we can see the Journals in the Oracle apps application and in Oracle apps base tables too. Here below I will share the GL Interface table and the sample script using these Interface table in oracle apps r12.

Important Table of GL Interface in Oracle Apps r12.

GL_INTERFACE

This is the Only table for the GL interface , we just need to insert the External Journals data in this Single table and oracle application automatically migrate this data into the Oracle apps standard two base tables GL_JE_HEADERS and GL_JEs_LINES

Detail GL Interface Script using the GL Interface table 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;