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 related information's in oracle apps from external sources. We can import the informations about GL Batches , GL Journals , GL Ledgers Balances through these GL interface tables in oracle apps r12. We can directly import the Manual Journals from these GL interface tables into the oracle application. There are some columns in the GL interface tables which we need to enter or cannot leave blank to import the External journals into the oracle cloud application. Here below , I will try to share these GL interface tables and the complete GL interface sample script which do helps to import the  Journals from external system into the Oracle apps r12.

GL Interface Table in Oracle apps

This is the only GL interface table which we do use to import the External GL journals into the Oracle apps r12.

1. GL_INTERFACE

Important Columns of GL Interface Table 

These are the below important columns of GL interface table which we have to pass informations for importing External Journals.

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

Sample 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;

GL interface tables in oracle apps r12