Process Steps:
1)we have received flat file from client2)We have created Staging table as per flat file structre
3)developed Control file and uploaded data
4)Developed PL/SQL Program to upload the data from stage into interface table
1)declare Cursor
2)open cursor
3)Validate each record
4)If no invalid record then insert into interface table.
5)Run the journal import from GL => Journal => Import => Run
Give the two parameters 1)Source
2)Group ID
6) Open the Output if status is 'SUCEESS' then take Request ID.
7)open Journal Enter screen Query the records based on the %requestid% As batch Name
Select Review journal button we can see the journal detailed transaction
8) If we want correct the journals we can correct from Journal=>Import=>Correct
9) If we want delete the journals we can delete from Journal=>Import=>Delete
Pre-Requisitions:
==================Before Going to develop the GL interface we are asuppose to check the following
functional setups has completed or not
1)Source and category Creation (Setup=>Journal=>Sources)
2)Period are defined and Open status(Setup=>open\Close)
3)Accounting structuere should be validated
4)Set of Books should be defined 1)currency 2)Calendar3)Chart of Accounts
5)Currency Conversion Rates should be defined.
1 USD = 45 INR Corporate
INR
Funcational Currency : Local Currency
Forein Currency : Other thatn the local currency whatever is there it is foregn currency.
Ex:
For India users INR is functional currency and USD is foreign currency
For US users USD is functional currency and INR is foreign currency
Entered_Dr
Entered_Cr : These columns contains the amount (Dr/Cr) what ever we have entered.
Accounted_Dr
Accounted_Cr : These columns contains the amount which is transfered into Foreirn currency
CODE :
========== Creation of Control file based upon the structure of the received flat file===========
LOAD DATA
INFILE *
TRUNCATE INTO TABLE XX_GL_TEMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(STATUS,
SET_OF_BOOKS_ID,
ACCOUNTING_DATE,
CURRENCY,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
CATEGORY,
SOURCE,
CURR_CONVERSION,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR,
GROUP_ID)
BEGINDATA
"NEW",1,"11-AUG-2002","USD","11-AUG-2002",1318,"A","Inventory","JETFORMS","Corporate","01","110","7730","0000","000",555,555,555,555,66
"NEW",1,"11-AUG-2002","USD","11-AUG-2002",1318,"A","Inventory","JETFORMS","Corporate","01","110","7730","0000","000",554,554,554,554,66
"NEW",99,"11-AUG-2002","USD","11-AUG-2002",1318,"A","Inventory","JETFORMS","Corporate","01","110","7730","0000","000",321,321,321,321,66
"NEW",1,"11-AUG-2002","USD","11-AUG-2002",1318,"A","Inventory","JETFORMS","Corporate","01","110","7730","0000","000",431,431,431,431,66
"NEW",1,"11-AUG-2002","SGD","11-AUG-2002",1318,"K","Inventory","JETFORMS","Corporate","01","110","7730","0000","000",1500,1500,1500,1500,66
"NEW",1,"11-AUG-2002","EUR","11-AUG-2002",1318,"A","Inventory","JETFORMS","Corporate","01","110","7730","0000","000",1600,1600,1600,1600,66
================ END OF CONTROL FILE===================================
GL_INTERFACE
1)Once we get the data into the stage table we have to transfer from stage table into
Interface table by using PL/SQL Program
Inside this program we write
1)Cursor (to select data from stage table)
2)Validate Data
3)Insert Statement(To Insert Into Interface table.
Note:1) If Record is valid record then we will insert into interface table other wise
we will insert into Error tables.
2)Before Inserting the Data into Interface tables first we have to understand the
Interface table structure and we should know what data is valid and what data
is not valid for the all Mandatroy columns.
2)Once the data is Inserted into the Interface table . Then we will submit concurrent
program to transfer the data from interface table to Base Tables.
In this Interface Interface table is GL_INTERFACE
Mandatroy Columns:
==================
1)STATUS
2)ACCOUNTING_DATE
3)CURRENCY_CODE
4)CREATED_BY
5)CREATION_DATE
6)USER_JE_SOURCE_NAME
7)USER_JE_CATEGORY_NAME
8)ACTUAL_FLAG
9)ENTERED_DR
10)ENTERED_CR
11)GROUP_ID
12)PERIOD_NAME
13)SET_OF_BOOKS_ID
CODE:
===============Stating of the procedure================================
CREATE OR REPLACE PROCEDURE GE_Pro30(Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2) IS
-- cursor declaration
CURSOR gl_cur IS
SELECT
status,
set_of_books_id,
accounting_date,
currency,
date_created,
created_by,
actual_flag,
category,
source,
curr_conversion,
segment1,
segment2,
segment3,
segment4,
segment5,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
group_id
FROM XX_TEMP;
l_currencycode VARCHAR2(25);
l_set_of_books_id NUMBER(3);
l_flag VARCHAR2(2);
l_error_msg VARCHAR2(100);
l_err_flag VARCHAR2(10);
l_category VARCHAR2(100);
l_USERID NUMBER(10);
l_count NUMBER(9) default 0;
BEGIN
DELETE FROM gl_interface;
COMMIT;
FOR rec_cur IN gl_cur LOOP
l_count:=l_count+1;
l_flag :='A';
--Category Column Validation
BEGIN
SELECT USER_JE_CATEGORY_NAME
INTO l_CATEGORY
FROM GL_JE_CATEGORIES
WHERE USER_JE_CATEGORY_NAME = REC_CUR.Category;
EXCEPTION
WHEN OTHERS THEN
l_flag:='E';
l_error_msg:='Category does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
END;
--End Category Column Validation
--User ID column validation
BEGIN
SELECT USER_ID
INTO L_USERID
FROM FND_USER
WHERE USER_ID = REC_CUR.created_by;
EXCEPTION
WHEN OTHERS THEN
l_flag:='E';
l_error_msg:='User ID does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
END;
--End of Created_by OR UserID column Validation
--Set of books Validation
BEGIN
SELECT set_of_books_id
INTO l_set_of_books_id
FROM GL_SETS_OF_BOOKS
WHERE set_of_books_id=rec_cur.set_of_books_id;
EXCEPTION
WHEN OTHERS THEN
l_flag:='E';
l_error_msg:='set of Books ID does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
END;
--Cuurency Code Validation
BEGIN
SELECT currency_code
INTO l_currencycode
FROM fnd_currencies
WHERE currency_code=rec_cur.currency
AND currency_code='USD';
EXCEPTION
WHEN OTHERS THEN
l_flag:='E';
l_error_msg:='currency code does not exists';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
END;
IF l_flag!='E' THEN
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE');
INSERT INTO gl_interface(status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
user_currency_conversion_type,
segment1,
segment2,
segment3,
segment4,
segment5,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
group_id)
VALUES
(rec_cur.status,
rec_cur.set_of_books_id,
rec_cur.accounting_date ,
rec_cur.currency,
rec_cur.date_created,
rec_cur.created_by ,
rec_cur.actual_flag,
rec_cur.category,
rec_cur.source,
rec_cur.curr_conversion,
rec_cur.segment1 ,
rec_cur.segment2 ,
rec_cur.segment3 ,
rec_cur.segment4 ,
rec_cur.segment5 ,
rec_cur.entered_dr,
rec_cur.entered_cr,
rec_cur.accounted_dr,
rec_cur.accounted_cr,
rec_cur.group_id);
END IF;
l_flag:=NULL;
l_error_msg:=NULL;
END LOOP;
COMMIT;
END GE_Pro30;
/
==========END OF THE PROCEDURE=========================================
No comments:
Post a Comment