GL interface in oracle apps r12 : GL interface sample code

GL interface in oracle apps r12 : GL interface sample code

In this post , we will discuss about GL interface in oracle apps r12. I will share the GL interface sample code in PLSQL which helps to Import GL Journals from backend in oracle apps r12. We can upload Mass GL journals wit the help on this Sample code. This is the Tested GL interface in oracle apps r12. In this GL interface , Oracle has only Provided the Single Interface table to Create the GL journals in Oracle app. Here below is the complete PLSQL code of GL Interface in oracle apps r12.

 

 

 

Complete PLSQL GL interface 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;