GL
Interface Process
Procedure:
1) We populate the
Staging Table
2) Performed
Validation on Staging Table.
3) Populate GL
Interface
4) Used Concurrent
Program : Journal IMPORT.
5) Report Used is :
Journal Import Execution Report – That gives Info on failed import.
6) If we get few
errors, then we fix the errors in GL_Interface.
7) If we get many
Errors: Then we delete IMPORT Journals using
Concurrent Procss :
Delete Journal Import Data.
- we
clean the GL_Interface
- Fix
the data from the Source and start over.
8) Post Journal
Entries after successful Import.
GL Interface used to follow the following process
1. To
import multi-currency data (Currency conversion)
2. To
import intercompany data (Intercompany):
3. To
import statistical data(Statistical)
4. To
import encumbrance data (Encumbrance)
5. To
import budget data (Budget)
Interface tables:
GL_INTERFACE
Error Tables
GL_INTERFACE_CONTROL
GL_INTERFACE_HISTORY
GL_IMPORT_REFERENCES
Base tables:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES
Concurrent Program: (1.)
Journal Import , (2.) 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')
--------------------===============
SQL * LOADER
options(skip=1)
load data
errors=5
infile '/ebs12/oracle/apps/apps_st/appl/ar/12.0.0/bin/customer
flat file.csv'
insert into table xxcustomersstg
---append into table
xxcustomersstg
---replace into table
xxcustomersstg
---truncate into
table xxcustomersstg
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
---------,Validation_Record_Flag CONSTANT "N" ----Optional
---------,error_flag CONSTANT "N"
----Optional
)
--------------------===============
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;
/
2nd Approach
-------Staging Table
Create table XX_TEMP(
Status varchar2(50),
set_of_books_id number,
accounting_date date,
currency varchar2(15),
date_created date,
created_by number,
actual_flag varchar2(50),
category varchar2(50),
source varchar2(50),
curr_conversion varchar2(50),
segment1 varchar2(50),
segment2 varchar2(50),
segment3 varchar2(50),
segment4 varchar2(50),
segment5 varchar2(50),
entered_dr number,
entered_cr number,
accounted_dr number,
accounted_cr number,
group_id number,
batch_name varchar2(50),
batch_description varchar2(150),
journal_name varchar2(50),
journal_description varchar2(150),
journal_line_description varchar2(250))
-------2ND Approach
Package----================
CREATE OR REPLACE PROCEDURE XX_GL_INTERFACE__TRFANS (Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2)
/*######################################################################################
Author :
Santosh Kumar A
File Name :
XX_GL_INTERFACE_TABLES_DATA
Created Date :
AUG 2011
Modified By :
Modified Date :
Procedure Name :
XX_GL_INTERFACE__TRFANS
Description :
This package is used to Validate and transfer data from staging to
gl_interface
Comments :
----------------------------------
#######################################################################################
Total Validation columns are
-- Journal Category Column
Validation
-- Journal Source Column
Validation
--User ID column validation OR -1
In case of Special Report for GL_Interface
--Set of books / ledgers
Validation
--Currency Code Validation
-- GL Period Name Validation
------------
------------
TABLES ASSOSIATED IN GL_INTERFACE
PROCESS IS
GL_INTERFACE
GL_INTERFACE_CONTROL
GL_INTERFACE_HISTORY
GL_IMPORT_REFERENCES
----------------------------------
GL_INTERFACE Columns Explanations
----------------------------------
--GL INTERFACE ADDITIONAL AND
CONDITIONALLY REQUIED COLUMNS
----------------------------------
STATUS (value = NEW) = STATUS: Enter the value NEW to indicate
that you are bringing new data into your General Ledger application.
LEDGER_ID (value = LEDGER_ID)
-----------
LEDGER_ID: Enter the appropriate
ledger ID for your transaction. You define your
ledger using Accounting Setup
Manager. You can find a list of valid values in the
LEDGER_ID column of the Ledgers
table (GL_LEDGERS. LEDGER_ID)
-----------SELECT LEDGER_ID, NAME
FROM GL_LEDGERS;
-----------
USER_JE_SOURCE_NAME
-------------
Enter the journal entry source
name for your transaction.
You define journal sources in the
Journal Sources form of your General Ledger
application. You can find a list
of valid values in the USER_JE_SOURCE_NAME
column of the Journal Entry
Sources table
(GL_JE_SOURCES.USER_JE_SOURCE_NAME).
If you have the Import Using Key
option enabled for the journal source, enter the
journal source key instead of the
journal source name. You can find a list of valid values
in the JE_SOURCE_KEY column of the
Journal Entry Sources table
(GL_JE_SOURCES.JE_SOURCE_KEY).
------------select
USER_JE_SOURCE_NAME,JE_SOURCE_KEY from GL_JE_SOURCES
-----------------------
USER_JE_CATEGORY_NAME
-------------------
Enter the journal category name
for your transaction.
You define journal categories in
the Journal Categories form of your General Ledger
application. You can find a list
of valid values in the USER_JE_CATEGORY_ NAME
column of the Journal Entry
Categories table (GL_JE_CATEGORIES.USER_JE_
CATEGORY_NAME).
If you have the Import Using Key
option enabled for the journal source, enter the
journal category key instead of the
journal category name. You can find a list of valid
values in the JE_CATEGORY_KEY
column of the Journal Entry Categories table
(GL_JE_CATEGORIES.JE_CATEGORY_KEY).
---------select
USER_JE_CATEGORY_NAME,JE_CATEGORY_KEY from GL_JE_CATEGORIES
-------------------
ACCOUNTING_DATE
---------------
Enter the accounting date on which
your transaction occurred.
Your General Ledger application
automatically assigns your journal batch to the
non-adjusting accounting period
that includes your accounting date. If you have
average balance processing
enabled, General Ledger uses your defined Effective Date
Rules to validate the accounting
date against your transaction calendar to determine the
transaction's effective date.
----------------------
CURRENCY_CODE
-----------------
Enter the currency code for your
transaction. You define new
currency codes in the Currencies
form of your General Ledger application. You can find
a list of valid values in the
CURRENCY_CODE column of the Currencies table
(FND_CURRENCIES.CURRENCY_CODE).
----------select CURRENCY_CODE from
FND_CURRENCIES
-----------------------
DATE_CREATED
-------------
Enter the date your import journal
entry line was created. The
information you enter here is for
your own records, and does not appear in your
General Ledger application.
------------
CREATED_BY
---------------
Enter an ID that you can use to
identify the data coming from your
feeder system. The ID you enter
provides you with an audit trail from Journal Import
data to your feeder system.
However, your Journal Import data will be removed from
the GL_INTERFACE table after it is
successfully imported, and this ID will not appear
in your General Ledger
application.
------------------------
ACTUAL_FLAG
------------
Enter the value A for actual amounts,
B for Budget amounts, or E for
encumbrance amounts.
---------------
ENCUMBRANCE_TYPE_ID
--------------------
If you entered the value E in the
ACTUAL_FLAG
column of the GL_INTERFACE table,
you must enter the appropriate encumbrance ID.
You define new encumbrance types
in the Encumbrance Types form of your General
Ledger application. You can find a
list of valid values in the
ENCUMBRANCE_TYPE_ID column of the
Encumbrance Types table (GL_
ENCUMBRANCE_TYPES. ENCUMBRANCE_
TYPE_ID).
SELECT ENCUMBRANCE_TYPE_ID,
ENCUMBRANCE_TYPE FROM GL_ENCUMBRANCE_TYPES WHERE ENABLED_FLAG = 'Y'
--------------------------
BUDGET_VERSION_ID
------------------------
If you entered the value B in the
ACTUAL_FLAG column of
the GL_INTERFACE table, you must
enter the appropriate budget ID. You define new
budget versions in the Define
Budget form of your General Ledger application. You can
find a list of valid values in the
BUDGET_ VERSION_ID column of the Budget Versions
table
(GL_BUDGET_VERSIONS.BUDGET_VERSION_ ID).
SELECT BUDGET_VERSION_ID,
BUDGET_NAME FROM GL_BUDGET_VERSIONS WHERE STATUS IN ('C','O');
-----------
PERIOD_NAME
------------------
To import actual and encumbrance
journals, specify the period name
and an accounting date within that
period. Journal Import will import journals into
adjusting and non-adjusting
periods.
To import budget transactions,
enter a period name for your budget transactions
(ACTUAL_FLAG = B) and an accounting date.
The accounting date is the effective date
of the journal. The accounting
date for budget amounts will be ignored by the Journal
Import process, which uses the
accounting period instead. The period name is required
when you are importing budget data
using Journal Import and it must be associated
with an open budget fiscal year.
Journal Import will import budget journals into
adjusting and non-adjusting
periods.
--------------------------
ENTERED_DR
-----------------
Enter the debit amount for each
line of your transaction. You can enter
a value for the ENTERED_DR and the
ENTERED_CR in one row.
-----------------
ENTERED_CR
-----------------
Enter the credit amount for each
line of your transaction. You can enter
a value for the ENTERED_DR and the
ENTERED_CR in one row.
-----------------
CURRENCY_CONVERSION_DATE
-------------------
Enter a currency conversion date
for your actual
entered currency transactions. If
you enter a conversion type other than User in the
USER_CURRENCY_CONVERSION_TYPE
column, you must enter a value in this
column. If your conversion type is
User, the default value for this column is the
accounting date.
-----------------
CURRENCY_CONVERSION_RATE
--------------------------
Enter a currency conversion rate
for your actual
entered currency transactions. If
you enter a conversion type of User in the
USER_CURRENCY_ CONVERSION_TYPE
column, you must enter a value in this
column. If you enter a conversion
type other than USER, do not enter anything in this
column.
-------------------------
REFERENCE1 (Batch Name)
REFERENCE2 (Batch Description)
REFERENCE4 (Journal entry name)
REFERENCE5 (Journal entry
description)
REFERENCE6 (Journal entry
reference)
REFERENCE7 (Journal entry reversal
flag)
REFERENCE8 (Journal entry reversal
period)
REFERENCE9 (Journal reversal
method)
REFERENCE10 (Journal entry line
description)
CONTEXT, CONTEXT1,
CONTEXT2,ATTRIBUTE1
through ATTRIBUTE 10:Enter values
for your
descriptive flexfield
"Journals - Journal
Entry Line".-ATTRIBUTE11
through ATTRIBUTE 20
:Enter values for your descriptive
flexfield"
Journals - x
Information"."Journals
- Captured Information".
/*--REFERENCE21 through
REFERENCE30
are Mapped to REFERENCE1 to
REFERENCE10
of GL_JE_LINES Tables
GROUP_ID
STAT_AMOUNT
-----------------------------
USSGL_TRANSACTION_CODE
-----------------------------
---#####################################################
----Required NULL Columns in the
GL_INTERFACE Table
---#####################################################
REFERENCE3: Do not enter a value
in this column.
-----------------------------
REFERENCE11 through REFERENCE20:
Do not enter a value in this column
-----------------------------
TRANSACTION_DATE: Do not enter a
value in this column
-----------------------------
JE_BATCH_ID: Do not enter a value
in this column
-----------------------------
JE_HEADER_ID: Do not enter a value
in this column
-----------------------------
JE_LINE_NUM: Do not enter a value
in this column
-----------------------------
CHART_OF_ACCOUNTS_ID: Do not enter
a value in this column
-----------------------------
FUNCTIONAL_CURRENCY_CODE: Do not
enter a value in this column
-----------------------------
DATE_CREATED_IN_GL: Do not enter a
value in this column
-----------------------------
WARNING_CODE
-----------------------------
STATUS_DESCRIPTION
-----------------------------
DESC_FLEX_ERROR_MESSAGE
-----------------------------
REQUEST_ID
-----------------------------
SUBLEDGER_DOC_SEQUENCE_ID
-----------------------------
SUBLEDGER_DOC_SEQUENCE_VALUE
-----------------------------
GL_SL_LINK_ID
-----------------------------
GL_SL_LINK_TABLE
-----------------------------
BALANCING_SEGMENT_VALUE
-----------------------------
MANAGEMENT_SEGMENT_VALUE
-----------------------------
FUNDS_RESERVED_FLAG
-----------------------------
--Correcting Journal Import Data
When you correct your data through
the Correct Journal Import window, use
the Find
-----------------------------
Journal Import window to locate
your data.
##########################################################################
Enter values in the following
required columns of the GL_INTERFACE table:
------------------------
Mandatory Columns
------------------------
1. STATUS: Enter the value NEW to indicate
that you are bringing new data into your
General Ledger application.
------------------------
2. LEDGER_ID: Enter the appropriate ledger ID
for your transaction. You define your
ledger using Accounting Setup
Manager. You can find a list of valid values in the
LEDGER_ID column of the Ledgers table
(GL_LEDGERS. LEDGER_ID).
3. USER_JE_SOURCE_NAME: Enter the journal
entry source name for your transaction.
You define journal sources in the
Journal Sources form of your General Ledger
application. You can find a list
of valid values in the USER_JE_SOURCE_NAME
column of the Journal Entry
Sources table
(GL_JE_SOURCES.USER_JE_SOURCE_NAME).
If you have the Import Using Key
option enabled for the journal source, enter the
journal source key instead of the
journal source name. You can find a list of valid values
in the JE_SOURCE_KEY column of the
Journal Entry Sources table
(GL_JE_SOURCES.JE_SOURCE_KEY).
------------------------
4. USER_JE_CATEGORY_NAME: Enter the journal
category name for your transaction.
You define journal categories in
the Journal Categories form of your General Ledger
application. You can find a list
of valid values in the USER_JE_CATEGORY_ NAME
column of the Journal Entry
Categories table (GL_JE_CATEGORIES.USER_JE_
CATEGORY_NAME).
If you have the Import Using Key
option enabled for the journal source, enter the
journal category key instead of
the journal category name. You can find a list of valid
values in the JE_CATEGORY_KEY
column of the Journal Entry Categories table
(GL_JE_CATEGORIES.JE_CATEGORY_KEY).
------------------------
5. ACCOUNTING_DATE: Enter the accounting date
on which your transaction occurred.
Your General Ledger application
automatically assigns your journal batch to the
non-adjusting accounting period
that includes your accounting date. If you have
average balance processing
enabled, General Ledger uses your defined Effective Date
Rules to validate the accounting
date against your transaction calendar to determine the
transaction's effective date.
------------------------
6. CURRENCY_CODE: Enter the currency code for
your transaction. You define new
currency codes in the Currencies
form of your General Ledger application. You can find
a list of valid values in the
CURRENCY_CODE column of the Currencies table
(FND_CURRENCIES.CURRENCY_CODE).
------------------------
7. DATE_CREATED: Enter the date your import
journal entry line was created. The
information you enter here is for
your own records, and does not appear in your General Ledger application.
------------------
8. CREATED_BY: Enter an ID that you can use to
identify the data coming from your
feeder system. The ID you enter
provides you with an audit trail from Journal Import
data to your feeder system.
However, your Journal Import data will be removed from
the GL_INTERFACE table after it is
successfully imported, and this ID will not appear
in your General Ledger
application.
------------------------
9. ACTUAL_FLAG: Enter the value A for actual
amounts, B for Budget amounts, or E for
encumbrance amounts.
------------------------
10. ENCUMBRANCE_TYPE_ID: If you entered the
value E in the ACTUAL_FLAG
column of the GL_INTERFACE table,
you must enter the appropriate encumbrance ID.
------------------------
You define new encumbrance types
in the Encumbrance Types form of your General
Ledger application. You can find a
list of valid values in the
ENCUMBRANCE_TYPE_ID column of the
Encumbrance Types table (
GL_ENCUMBRANCE_TYPES. ENCUMBRANCE_
TYPE_ID).
------------------------
11. BUDGET_VERSION_ID: If you entered the value
B in the ACTUAL_FLAG column of
the GL_INTERFACE table, you must
enter the appropriate budget ID. You define new
budget versions in the Define
Budget form of your General Ledger application. You can
find a list of valid values in the
BUDGET_ VERSION_ID column of the Budget Versions
table
(GL_BUDGET_VERSIONS.BUDGET_VERSION_ ID).
12. PERIOD_NAME: To import actual and
encumbrance journals, specify the period name
and an accounting date within that
period. Journal Import will import journals into
adjusting and non-adjusting
periods.
To import budget transactions,
enter a period name for your budget transactions
(ACTUAL_FLAG = B) and an
accounting date. The accounting date is the effective date
of the journal. The accounting
date for budget amounts will be ignored by the Journal
Import process, which uses the
accounting period instead.
The period name is required when
you are importing budget data using Journal Import and it must be associated
with an open budget fiscal year. Journal Import will import budget journals
into
adjusting and non-adjusting
periods.
13. ENTERED_DR: Enter the debit amount for each
line of your transaction. You can enter
a value for the ENTERED_DR and the
ENTERED_CR in one row.
------------------------
14. ENTERED_CR: Enter the credit amount for
each line of your transaction. You can enter
a value for the ENTERED_DR and the
ENTERED_CR in one row.
15. USER_CURRENCY_CONVERSION_TYPE: Enter a
currency conversion type for your
actual entered currency
transactions. Acceptable values are User, Spot, Corporate, or
any other type you define in the
Conversion Rate Types form. If you enter
a rate type of
User, then you must also enter a
conversion rate in the CURRENCY_CONVERSION_
RATE column. For all other conversion types you
must enter a conversion date in the CURRENCY_ CONVERSION_DATE column.
You can find a list of valid
values in the USER_CURRENCY_CONVERSION_TYPE
column of the Conversion Types
table
(GL_DAILY_CONVERSION_TYPES.USER_CURRENCY_ CONVERSION_TYPE).
------------------------
16. CURRENCY_CONVERSION_DATE: Enter a currency
conversion date for your actual
entered currency transactions. If
you enter a conversion type other than User in the
USER_CURRENCY_CONVERSION_TYPE
column, you must enter a value in this
column. If your conversion type is
User, the default value for this column is the
accounting date.
------------------------
17. CURRENCY_CONVERSION_RATE: Enter a currency
conversion rate for your actual
entered currency transactions. If
you enter a conversion type of User in the
USER_CURRENCY_ CONVERSION_TYPE
column, you must enter a value in this
column. If you enter a conversion
type other than USER, do not enter anything in this
column.
------------------------
18. ACCOUNTED_DR: Enter a converted debit
amount for your actual entered currency
transactions. You can enter a
value for the ACCOUNTED_DR and ACCOUNTED_CR
in one row. You must enter a value
for ENTERED_DR if you entered a value for
ACCOUNTED_DR.
------------------------
19. ACCOUNTED_CR: Enter a converted credit
amount for your actual entered currency
transactions. You can enter a
value for the ACCOUNTED_DR and ACCOUNTED_CR
in one row. You must enter a value for
ENTERED_CR if you entered a value for
ACCOUNTED_CR.
Optional Columns
Assigning Values to Optional
Columns
Enter values in the following
optional columns of the GL_INTERFACE table:
------------------------
1. REFERENCE1 (Batch Name): Enter a batch name
for your import batch. Journal Import
creates a default batch name using
the following format:
(Optional User-Entered
REFERENCE1)+ (Source) (Request ID) +(Actual Flag)+ (Group ID).
If you enter a batch name,
Journal Import prefixes the first
50 characters of your batch name to the above
format.
------------------------
The batch name must be unique for
each combination of charts of accounts, accounting
calendar, and period type.
------------------------
2. REFERENCE2 (Batch Description): Enter a
description for your batch.
If you do not enter a batch
description, Journal Import automatically gives your batch a description using
the format: Journal Import (Source) + (Request Id).
------------------------
3. REFERENCE4 (Journal entry name): Enter a
journal entry name for your journal entry.
Journal Import creates a default
journal entry name using the following format:
(Category Name) +(Currency)
+(Currency Conversion Type, if applicable)+ (Currency
Conversion Rate, if applicable)+
(Currency Conversion Date, if applicable)+ (Encumbrance
Type ID, if applicable) + (Budget
Version ID, if applicable).
If you enter a journal entry name,
Journal Import prepends the first 25 characters of your journal entry name to
the above format.
------------------------
4. REFERENCE5 (Journal entry description):
Enter a description for your journal entry. If
you do not enter a journal entry
description, Journal Import automatically gives your
journal entry a description using
the format: Journal Import - Concurrent Request ID.
------------------------
5. REFERENCE6 (Journal entry reference): Enter
a reference name or number for your
journal entry. If you do not enter
a journal entry reference, Journal Import automatically
creates a journal entry reference
called Journal Import Created.
------------------------
6. REFERENCE7 (Journal entry reversal flag):
Enter Yes to mark your journal entry for
reversal. If you do not enter Yes,
Journal Import automatically defaults to No.
------------------------
7. REFERENCE8 (Journal entry reversal period):
Enter the name of the period to which
you want to reverse your journal
entry. If you enter Yes in the REFERENCE7 column,
you must enter a value in this column.
If you have enabled average
balance processing, enter the effective date for the reversal.
General Ledger will determine the
appropriate reversal period based on the date you
supply.
------------------------
8. REFERENCE9 (Journal reversal method): Enter
Yes to use the change sign method, No
to use the switch debit/credit
method.
------------------------
9.
REFERENCE10 (Journal entry line
description): Enter a description for your journal
entry line. If you do not enter a
journal entry line description, Journal Import uses the
subledger document sequence
value. If there is no document sequence
value,
Journal Import creates a journal
entry description called = Journal
Import Created.
------------------------
10. REFERENCE21 through REFERENCE30: Enter a
reference name or number to further
identify your import journal entry
lines.
Columns REFERENCE21 through
REFERENCE30 map into columns REFERENCE_1 through REFERENCE_10,respectively, of
the GL_JE_LINES table.
------------------------
Once in the GL_JE_LINES table,
your General Ledger application prints the value
stored in REFERENCE_1 in standard
reports run with Line detail, and prints the value
stored in REFERENCE_4 in standard
reports run with Source detail. The other reference
columns are for descriptive or
tracking purposes only. The values in these columns are
not used in your General Ledger
application.
------------------------
11. GROUP_ID: Enter a unique group number to
distinguish import data within a source.
You can run Journal Import in
parallel for the same source if you specify a unique
group number for each request.
------------------------
12. STAT_AMOUNT: Enter the statistical amount
associated with your journal entry line
data. You define statistical units
of measure in the Statistical Units of Measure form of
your General Ledger application.
You must use this column when you want to see
statistical and monetary amounts
in the same journal entry line.
------------------------
13. USSGL_TRANSACTION_CODE: Enter a valid USSGL
transaction code for your
journal entry line. Journal Import
validates and imports the USSGL transaction codes
when you have the profile option
Enable Transaction Code set to Yes, and you have
defined your USSGL transaction
codes using the Public Sector Transaction Codes
window.
------------------------
Note: This column is ignored for
commercial installations of General
Ledger.
------------------------
14. ATTRIBUTE1 through ATTRIBUTE 10: Enter
values for your descriptive flexfield
"Journals - Journal Entry
Line" DFF Title Name =Enter Journals: Lines . The values you enter depend on how you
defined your
descriptive flexfield in the
Descriptive Flexfield Segments form.
------------------------
15. ATTRIBUTE11 through ATTRIBUTE 20: Enter
values for your descriptive flexfield
"Journals - Captured
Information" DFF Title Name = Enter Journals: Captured Information . The
values you enter depend on how you defined
your descriptive flexfield in the
Descriptive Flexfield Segments form.
The context for Journals -
Captured Information is the natural account value of the account used on
each line.
------------------------
16. CONTEXT: Enter the context field value for
the descriptive flexfield "Journals – Journal Entry Line" ,
DFF Title Name =Enter Journals:
Lines that identifies the structure of your descriptive flexfield.
--------DFF Title Name =Enter
Journals: Lines
------------------------------
If you enter a value, you can also
enter some combination of values in the columns ATTRIBUTE1
through ATTRIBUTE10.
------------------------
------------------------
17. CONTEXT2: Enter Yes to identify your Value
Added Tax Descriptive Flexfield structure.
You must use this column if you
import data for the Value Added Tax Descriptive Flexfield.
Enter No to indicate that your
journal entry line is not a tax item.
If you enter No, the four Value
Added Tax Descriptive Flexfield related columns must be null.
-----------DFF Title Name =Enter
Journals: Value Added Tax
------------------------
18. CONTEXT3: Enter the context field value
(natural account) for the descriptive flexfield
"Journals - Captured
Information" that identifies the structure of your descriptive
flexfield. Enter a value only if you are importing the
descriptive flexfield "Journals -
Captured Information" without validation. If you enter a value, you
can also enter some
combination of values in the
columns ATTRIBUTE11 through ATTRIBUTE20.
-----------DFF Title Name = Enter
Journals: Captured Information
------------------------
19. INVOICE_DATE: Enter the date on which you
paid or collected tax on your tax journal
entry line. Enter the date in the
format DD-MON-YY or the default date format for your
language. Your invoice date should
correspond to the date when tax amounts were
paid or received for this invoice.
You must use this column if you import data for the
Value Added Tax Descriptive
Flexfield.
20. INVOICE_AMOUNT: Enter an invoice amount.
Enter the net invoice amount that
relates to your tax journal entry
line amount. You must use this column if you import
data for the Value Added Tax
Descriptive Flexfield.
------------------------
21. TAX_CODE: Enter a valid tax code that
identifies the type of tax paid for this invoice.
You define a list of valid tax
codes for this field when you define your descriptive
flexfield values. You must use
this column if you import data for the Value Added Tax
Descriptive Flexfield.
22. REFERENCE_DATE: Enter a date to capture
additional date information about your
journal. The Reference Date column
satisfies Libro Giornale requirements in Italy, but
can be used to capture any other
date information you want to store at the journal
header level. This column is not
used to group journal lines. If multiple rows in the
GL_INTERFACE table use different reference
dates, they can be grouped into the same
journal entry which will choose a
reference date from one of the rows to use as a
reference date for the journal
entry.
23. JGZZ_RECON_REF: Enter a reconciliation
reference to identify a group of journal lines
that need to net to zero.
24. AVERAGE_JOURNAL_FLAG: Set to Y (Yes) when
importing average balance journals
into an average balances
consolidation ledger. The flag indicates whether this journal
affects the average balances and
not the standard balances.
------------------------
25. ORIGINATING_BAL_SEG_VAL: Enter a clearing
balancing segment value. You can
use this column if you are
entering an intercompany transaction and want to override
the default clearing balancing
segment.
26.
INVOICE_IDENTIFIER: Enter an invoice identifier. Enter reference
information about
the source document or invoice
upon which you paid or collected tax. You must use
this column if you import data for
the Value Added Tax Descriptive Flexfield.
27. LEDGER_ID: Enter the appropriate ledger ID
for your transaction. Use this column if
you need to import data from
applications on Release 11i or earlier that use the set of
books ID.
------------------------
Required NULL Columns in the
GL_INTERFACE Table
Some columns in the GL_INTERFACE
table must be NULL as Journal Import uses
them for internal processing or
does not use them in the current release. The following
-------##############################################
-------columns must be NULL in
your General Ledger application:
-------##############################################
------------------------
1. REFERENCE3: Do not enter a value in this
column.
2. REFERENCE11 through REFERENCE20: Do not
enter a value in this column.
3. TRANSACTION_DATE: Do not enter a value in
this column.
4.
JE_BATCH_ID: Do not enter a value in this column.
5. JE_HEADER_ID: Do not enter a value in this
column.
6. JE_LINE_NUM: Do not enter a value in this
column.
7. CHART_OF_ACCOUNTS_ID: Do not enter a value
in this column.
8. FUNCTIONAL_CURRENCY_CODE: Do not enter a
value in this column.
9. DATE_CREATED_IN_GL: Do not enter a value in
this column.
10. WARNING_CODE: Do not enter a value in this
column.
11. STATUS_DESCRIPTION: Do not enter a value in
this column.
12. DESC_FLEX_ERROR_MESSAGE: Do not enter a
value in this column.
13. REQUEST_ID: Do not enter a value in this
column.
14. SUBLEDGER_DOC_SEQUENCE_ID: Do not enter a
value in this column.
15. SUBLEDGER_DOC_SEQUENCE_VALUE: Used for
communication between
General Ledger and the subledgers.
Do not populate with your own data.
16. GL_SL_LINK_ID: Populated by Oracle
Subledger Accounting (SLA) to indicate the
link to the subledger transaction.
17. GL_SL_LINK_TABLE: Populated by Oracle
Subledger Accounting (SLA) to
indicate the subledger table that
contains the originating transaction.
18. BALANCING_SEGMENT_VALUE: Used internally
for Journal Import. Do not
enter a value in this column.
19. MANAGEMENT_SEGMENT_VALUE: Used internally
for Journal Import. Do not
enter a value in this column.
20. FUNDS_RESERVED_FLAG: Used internally for
Journal Import. Do not enter a
value in this column.
------------------------
Importing Specialized Data
(Various type of use for gl_interface process ) by various process through
gl_interface import
------------------------
1. To import multi-currency data (Currency
conversion):
------------------------
Load multi-currency data into the
GL_INTERFACE table the same way you load
regular data. If you want your
General Ledger application to calculate your conversion,
you must enter a value in the
CURRENCY_CODE, CURRENCY_CONVERSION_DATE
and USER_CURRENCY_CONVERSION_TYPE
columns of the GL_INTERFACE table.
If the conversion type is User,
you must also enter a value in the
CURRENCY_CONVERSION_RATE column of
the GL_INTERFACE table. Or, you can
directly specify the converted
amounts by entering values in the ACCOUNTED_DR
and ACCOUNTED_CR columns. If you
choose to enter your converted amounts, do not
specify the conversion rate, type
and date.
------------------------
2. To import intercompany data (Intercompany):
------------------------
Load intercompany data into the
GL_INTERFACE table the same way you load regular
data. Journal Import creates
intercompany journal entries from the data you import.
And, if you want, your General
Ledger application automatically balances your
intercompany journal entries
during posting to an intercompany account you specify
when you define your ledger.
-----------------------------------------------
3. To import statistical data(Statistical):
------------------------------------------------
Load statistical data into the
GL_INTERFACE table the same way you load regular
data. The only difference is that
you enter the value STAT in the CURRENCY_CODE
column of the GL_INTERFACE table.
Do not enter values in the STAT_AMOUNT
column.
Alternatively, if you choose to use
units of measure, you can enter a positive amount for
a debit or a negative amount for a
credit in the STAT_AMOUNT column of the
GL_INTERFACE table for each
monetary journal entry line amount. In this case, enter a
monetary currency, not STAT, in
the CURRENCY_CODE column.
-----------------------------------------------
4. To import encumbrance data (Encumbrance):
-----------------------------------------------
Load encumbrance data into the
GL_INTERFACE table the same way you load regular
data. The only difference is that
you must enter the value E in the ACTUAL_FLAG
column and the appropriate encumbrance
type ID in the ENCUMBRANCE_TYPE_ID
column of the GL_INTERFACE table.
Note: All encumbrance journals in
a batch must have the same ledger.
-----------------------------------------------
5. To import budget data (Budget):
-----------------------------------------------
Load budget data into the
GL_INTERFACE table the same way you load regular data.
The only difference is that you
must enter the value B in the ACTUAL_FLAG column
and the appropriate budget version
ID in the BUDGET_VERSION_ID column of the
GL_INTERFACE table.
• You must enter a valid period
name for budget journal batches created by Journal
Import. Use the PERIOD_NAME column
to enter a valid batch period whenever
you specify the value B in the
ACTUAL_FLAG column of the GL_INTERFACE
table.
Note: All budget journals in a
batch must have the same ledger.
#########################################################################
*/
IS
-- cursor declaration
CURSOR gl_cur
IS
SELECT status,
set_of_books_id,
-- ledger_id,
--rec.ENCUMBRANCE_TYPE_ID
--rec.USER_CONVERSION_TYPE
--rec.BUDGET_VERSION_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,
batch_name,
batch_description,
journal_name,
journal_description,
journal_line_description
FROM XX_TEMP123;
-- End of cursor
declaration
--------------------------***********************************************
-- Local Variable
Declarations
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_source VARCHAR2 (100);
l_USERID NUMBER (10);
l_count NUMBER (9) DEFAULT 0;
l_period DATE;
l_actual_flag VARCHAR2 (10);
l_ledger_id NUMBER (3);
L_ENCUMBRANCE_TYPE_ID NUMBER (9);
L_USER_CONVERSION_TYPE VARCHAR2 (10);
---------------------------------------------
BEGIN
FOR rec_cur IN gl_cur
LOOP
l_count := l_count + 1;
l_flag := 'A';
-----------------------------------
--Set of books ID
Validation
--------------------------------------------------
BEGIN
SELECT set_of_books_id
INTO l_set_of_books_id
FROM
GL_SETS_OF_BOOKS --GL_LEDGERS
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;
/*
-----------------------------------
---- ledgers ID Validation
--------------------------------------------------
BEGIN
SELECT LEDGER_ID
INTO L_ledger_id
FROM GL_LEDGERS --GL_LEDGERS
WHERE LEDGER_ID =
rec_cur.LEDGER_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;
---------------------------------------
-- ENCUMBRANCE_TYPE_ID Column Validation
---------------------------------------
BEGIN
SELECT
ENCUMBRANCE_TYPE_ID
--,
ENCUMBRANCE_TYPE
INTO
L_ENCUMBRANCE_TYPE_ID
FROM
GL_ENCUMBRANCE_TYPES
WHERE ENABLED_FLAG = 'Y'
AND
ENCUMBRANCE_TYPE_ID = rec.ENCUMBRANCE_TYPE_ID;
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
l_error_msg :=
'ENCUMBRANCE_TYPE_ID does not exist ';
Fnd_File.put_line
(
Fnd_File.LOG,
'Inserting
data into the Interface TABLE'
|| '-'
|| l_count
|| ' '
||
l_error_msg);
END;
------------------------------------------------
--BUDGET_VERSION_ID
Column Validation
---------------------------------------------------
BEGIN
SELECT
BUDGET_VERSION_ID
INTO L_BUDGET_VERSION_ID
FROM
GL_BUDGET_VERSIONS
WHERE STATUS IN ('C', 'O')
AND
BUDGET_VERSION_ID = REC.L_BUDGET_VERSION_ID;
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
l_error_msg :=
'BUDGET_VERSION_ID having an error ';
Fnd_File.put_line
(
Fnd_File.LOG,
'Inserting
data into the Interface TABLE'
|| '-'
|| l_count
|| ' '
|| l_error_msg);
END;
---------------------------------------------------
-----USER_CONVERSION_TYPE
Column Validation
--------------------------------------------------
BEGIN
SELECT
USER_CONVERSION_TYPE
INTO
L_USER_CONVERSION_TYPE
FROM
GL_DAILY_CONVERSION_TYPES
WHERE
USER_CONVERSION_TYPE = rec.USER_CONVERSION_TYPE;
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
l_error_msg :=
'CONVERSION_TYPE having Error ';
Fnd_File.put_line (
Fnd_File.LOG,
'Inserting data
into the Interface TABLE'
|| '-'
|| l_count
|| ' '
|| l_error_msg);
END;
*/
---------------------------------------
-- Journal Source
Column Validation
----------------------------------------
BEGIN
SELECT USER_JE_SOURCE_NAME
INTO l_source
FROM GL_JE_SOURCES
WHERE USER_JE_SOURCE_NAME = REC_CUR.source;
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
l_error_msg := 'source does not exist
';
Fnd_File.put_line (
Fnd_File.LOG,
'Inserting data into
the Interface TABLE'
|| '-'
|| l_count
|| ' '
|| l_error_msg);
END;
---------------------------------------
-- Journal 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;
---------------------------------------
-- GL Period
Name Validation
---------------------------------------
BEGIN
SELECT PERIOD_NAME
INTO l_period
FROM GL_PERIOD_STATUSES
WHERE closing_status IN ('O', 'F');
EXCEPTION
WHEN OTHERS
THEN
l_flag := 'E';
l_error_msg := 'period does not exists
or not opened';
Fnd_File.put_line (
Fnd_File.LOG,
'Inserting data into
the Interface TABLE'
|| '-'
|| l_count
|| ' '
|| l_error_msg);
END;
---------------------------------------
-- GL
ACTUAL_FLAG Validation
---------------------------------------
BEGIN
SELECT ACTUAL_FLAG
INTO l_actual_flag
FROM XX_TEMP123
WHERE actual_flag IN ('A', 'B', 'E');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_actual_flag := NULL;
l_flag := 'E';
l_err_flag := 'E';
FND_FILE.PUT_line (FND_FILE.LOG, 'The Flag is not
correct');
END;
---------------------------------------
--User ID column
validation OR -1
----------------------------------
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;
--------------------------------------------------
--Currency Code
Validation
--------------------------------------------------
BEGIN
SELECT currency_code
INTO l_currencycode
FROM fnd_currencies
WHERE currency_code = rec_cur.currency AND currency_code = 'BHD';
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');
---------------------------------------
--Create GL ENTRY
---------------------------------------
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 )
--
,REFERENCE1,---- (Batch Name)
--
REFERENCE2,---- (Batch Description)
--
REFERENCE4,---- (Journal entry name)
--
REFERENCE5)---- (Journal entry description)
--
--REFERENCE10)---- (Journal entry line description)
-- ---- REFERENCE7 (Journal entry reversal
flag)
--
---- REFERENCE8 (Journal entry reversal period)
--
---- REFERENCE9 (Journal reversal method)
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
--
,rec.batch_name,
--
rec.batch_description,
--
rec.journal_name,
--
rec.journal_description
--
rec.journal_line_description
);
l_flag := NULL;
l_error_msg := NULL;
END IF;
END LOOP;
COMMIT;
END
XX_GL_INTERFACE__TRFANS;
