GL Moudle:
PROCESS:
-------------
1)we have received flat file from client
2)We have created Staging table as per flat file structre
3)developed Control file and uploaded data using Sql* loader .
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
10) Interface tables:
GL_INTERFACE
Base tables:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES
Pre-Requisitions:
==================
Before Going to develop the GL interface we are supposed
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
Foreign Currency : Other than 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
GL Control 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
Or
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)
);
Moving the Data from Flat File to stg Table using SQL *
LOADER:
Options (Skip =0)
Load data
infile '/ebs/oracle/apps/apps_st/appl/gl/12.0.0/bin/gl_daily_rates.csv'
Insert into table GL_daily_rates_stg
fields terminated by ','
optionally enclosed by '"'
Trailing nullcols
(From_currency ,To_currency, From_conversion_date, To_conversion_date,
User_conversion_type, conversion_rate, Mode_flag)
FROM STG TABLE TO INTERFACE TABLE:
2. 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 waht 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
Difference between Interface and
API
An API (Application Programming Interface) is inbuilt program through which data’s can be transferred to Oracle base tables directly without writing the program for validating or inserting in Interface tables.
But through User Interface, we have to write codes for validation and insertion of data’s In Interface tables and then in Oracle base tables
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
1)Status Column will accept any Data. but we will insert
always standard string called
"NEW". It means that we are bringing new data into Oracle
Applications.
2)ACCOUNTING_DATE
: Column will accept valid acc_date as per the accounting Periods
3)CURRENCY_CODE
: from FND_CURRENCIES table we can find out wether CUrrency Code is
valid or Not if Currecny code is
available in the Table and enabled it is
valid.otherwise Invalid
4)CREATED_BY : IS
nothing but UserID we have to find wether
USerID is valid or not
By
using FND_USER table we can find out wether it is valid or not.
5)CREATION_DATE : Should be valid date
date should be <= SYSDATE
6)USER_JE_SOURCE_NAME: Valid Source name
By
using GL_JE_SOURCES table we can find
wether valid source or not.
7)USER_JE_CATEGORY_NAME : Will accept valid cvategory
name
By using
GL_JE_CATEGORIES table we can find out wether valid category or Not.
8)ACTUL_FLAG :
This Column will accept single Character
Either 'A' 'B' 'E'
A=Actuval Amounts
B=Budeget Amounts
E=Encumbrance Amounts
9)ENTERED_DR
10)ENTERED_CR : Both Columns we accept Numbers Only but
same number we have to insert
into both the columns.
Always ENTERED_CR
= ENTERED_DR . Otherwise Suspense Account will be
Created.
11)GROUP_ID :
Will accept any Number
12)period_name : Will Accept valid period and that period
should be in the Open status
By
using GL_PERIODS we can find wether Period is there or not
GL_PERIOD_STATUSES table we can find wether it is in "Open"
status or Not.
13)SET_OF_BOOKS_ID
: This column will accept valid set of Books ID. By using
GL_SETS_OF_BOOKS table we can fnd out wether valuid set_of_books_id or
not.
PROCEDURE FOR GL:
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;
/
Create a Package with validations to move the data into
Interface Tables
CREATE OR REPLACE PACKAGE XXGL_DRATES_PKG
is
PROCEDURE DAILY_RATES_PRC(retcode out number,errbuff out varchar2);
END;
CREATE OR REPLACE PACKAGE BODY XXGL_DRATES_PKG
is
PROCEDURE DAILY_RATES_PRC(retcode out number, errbuff out varchar2)
Is
Cursor cur_drates is
Select FROM_CURRENCY, TO_CURRENCY, FROM_CONVERSION_DATE , TO_CONVERSION_DATE ,
USER_CONVERSION_TYPE, CONVERSION_RATE , MODE_FLAG FROM XXGL_DRATES_STG;
LV_FROM_CURRENCY VARCHAR2(15);
LV_TO_CURRENCY VARCHAR2(15);
LV_USER_CONVERSION_TYPE VARCHAR2(30);
LV_CONVERSION_RATE NUMBER;
LV_ERR_FLAG VARCHAR2(1):= 'A';
BEGIN
FOR i IN CUR_DRATES
LOOP
BEGIN
Select CURRENCY_CODE into LV_FROM_CURRENCY FROM
FND_CURRENCIES where CURRENCY_CODE=i.FROM_CURRENCY;
Exception
When NO_DATA_FOUND Then
lv_from_currency := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code is not defined
/not enabled if not enabled enable it.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code inserting IS--'
|| LV_FROM_CURRENCY );
BEGIN
Select CURRENCY_CODE into LV_TO_CURRENCY
FROM FND_CURRENCIES where ENABLED_FLAG='Y'
AND CURRENCY_CODE=i.To_CURRENCY;
Exception
When NO_DATA_FOUND Then
lv_from_currency := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code is not defined
/not enabled if not enabled enable it.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code inserting IS--'
|| LV_TO_CURRENCY );
BEGIN
Select USER_CONVERSION_TYPE into LV_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES where
USER_CONVERSION_TYPE=i.USER_CONVERSION_TYPE;
Exception
When NO_DATA_FOUND Then
LV_USER_CONVERSION_TYPE := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE is not defined.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE inserting IS--'
||LV_USER_CONVERSION_TYPE );
BEGIN
Select USER_CONVERSION_TYPE into LV_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES where
USER_CONVERSION_TYPE=i.USER_CONVERSION_TYPE;
Exception
When NO_DATA_FOUND Then
LV_USER_CONVERSION_TYPE := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE is not defined.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE inserting IS--'
||LV_USER_CONVERSION_TYPE );
IF LV_ERR_FLAG='A' THEN
INSERT INTO GL_DAILY_RATES_INTERFACE (
FROM_CURRENCY, TO_CURRENCY,
FROM_CONVERSION_DATE, TO_CONVERSION_DATE,
USER_CONVERSION_TYPE, CONVERSION_RATE,
MODE_FLAG)
VALUES (
LV_FROM_CURRENCY,LV_TO_CURRENCY,
I.FROM_CONVERSION_DATE, I.TO_CONVERSION_DATE
, LV_USER_CONVERSION_TYPE, I.CONVERSION_RATE
, I.MODE_FLAG);
END IF;
END LOOP;
COMMIT;
END;
END XXGL_DRATES_PKG;
Base tables for GL Daily Rates are—
Gl_DAILY_RATES
Interface table for GL Daily Rates are—
Gl_DAILY_RATES_INTERFACE
Moving the Data from Flat File to Base Table using SQL *
LOADER:
Options (Skip =0)
Load data
infile '/ebs/oracle/apps/apps_st/appl/gl/12.0.0/bin/gl_daily_rates.csv'
Insert into table GL_daily_rates_stg
fields terminated by ','
optionally enclosed by '"'
Trailing nullcols
(From_currency ,To_currency, From_conversion_date, To_conversion_date,
User_conversion_type, conversion_rate, Mode_flag)
Moving the data from Staging tables to Base Tables using
Standard Interface Programs:
Create a Staging table based on the requirement
CREATE TABLE XXGL_DRATES_STG (
FROM_CURRENCY VARCHAR2(15),
TO_CURRENCY VARCHAR2(15),
FROM_CONVERSION_DATE DATE,
TO_CONVERSION_DATE DATE,
USER_CONVERSION_TYPE VARCHAR2(30),
CONVERSION_RATE NUMBER,
MODE_FLAG CHAR(1));
Inserting Data into Staging Table:
Insert into XXGL_DRATES_STG Values (
'USD','INR','29-Jan-2009','31-Jan-2009','Corporate','50','I');
Create a Package with validations to move the data into
Interface Tables
CREATE OR REPLACE PACKAGE XXGL_DRATES_PKG
is
PROCEDURE DAILY_RATES_PRC(retcode out number,errbuff out varchar2);
END;
CREATE OR REPLACE PACKAGE BODY XXGL_DRATES_PKG
is
PROCEDURE DAILY_RATES_PRC(retcode out number, errbuff out varchar2)
Is
Cursor cur_drates is
Select FROM_CURRENCY, TO_CURRENCY, FROM_CONVERSION_DATE , TO_CONVERSION_DATE ,
USER_CONVERSION_TYPE, CONVERSION_RATE , MODE_FLAG FROM XXGL_DRATES_STG;
LV_FROM_CURRENCY VARCHAR2(15);
LV_TO_CURRENCY VARCHAR2(15);
LV_USER_CONVERSION_TYPE VARCHAR2(30);
LV_CONVERSION_RATE NUMBER;
LV_ERR_FLAG VARCHAR2(1):= 'A';
BEGIN
FOR i IN CUR_DRATES
LOOP
BEGIN
Select CURRENCY_CODE into LV_FROM_CURRENCY FROM
FND_CURRENCIES where CURRENCY_CODE=i.FROM_CURRENCY;
Exception
When NO_DATA_FOUND Then
lv_from_currency := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code is not defined
/not enabled if not enabled enable it.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code inserting IS--'
|| LV_FROM_CURRENCY );
BEGIN
Select CURRENCY_CODE into LV_TO_CURRENCY
FROM FND_CURRENCIES where ENABLED_FLAG='Y'
AND CURRENCY_CODE=i.To_CURRENCY;
Exception
When NO_DATA_FOUND Then
lv_from_currency := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code is not defined
/not enabled if not enabled enable it.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code inserting IS--'
|| LV_TO_CURRENCY );
BEGIN
Select USER_CONVERSION_TYPE into LV_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES where
USER_CONVERSION_TYPE=i.USER_CONVERSION_TYPE;
Exception
When NO_DATA_FOUND Then
LV_USER_CONVERSION_TYPE := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE is not defined.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE inserting IS--'
||LV_USER_CONVERSION_TYPE );
BEGIN
Select USER_CONVERSION_TYPE into LV_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES where
USER_CONVERSION_TYPE=i.USER_CONVERSION_TYPE;
Exception
When NO_DATA_FOUND Then
LV_USER_CONVERSION_TYPE := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE is not defined.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE inserting IS--'
||LV_USER_CONVERSION_TYPE );
IF LV_ERR_FLAG='A' THEN
INSERT INTO GL_DAILY_RATES_INTERFACE (
FROM_CURRENCY, TO_CURRENCY,
FROM_CONVERSION_DATE, TO_CONVERSION_DATE,
USER_CONVERSION_TYPE, CONVERSION_RATE,
MODE_FLAG)
VALUES (
LV_FROM_CURRENCY,LV_TO_CURRENCY,
I.FROM_CONVERSION_DATE, I.TO_CONVERSION_DATE
, LV_USER_CONVERSION_TYPE, I.CONVERSION_RATE
, I.MODE_FLAG);
END IF;
END LOOP;
COMMIT;
END;
END XXGL_DRATES_PKG;
Create an Executable – XXGL_DRATES_PKG_EXEC
Execution File
Create a Concurrent program – XXGL_DRATES_PKG_EXEC IFace Conc
prg
Add the Conc program to the Request group
In custom module, Run the Conc
Program thro’ SRS Window.
In GL MODULE Run the Standard
Concurrent Program –
Program - Daily Rates Import and Calculation
No comments:
Post a Comment