GL Interface
Interface tables:
GL_INTERFACE
GL_INTERFACE_CONTROL
GL_INTERFACE_HISTORY
GL_IMPORT_REFERENCES
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;
Package
Body:
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;
/
