This Blog
post is written for end-to-end automation of Oracle Applications’ General
Ledger (GL) Journal Interface, and it contains all the best practices followed
in the interface as well. The purpose of this white paper is to serve as a
template, as well as reference material for the interface. This outlines the
entire implementation of GL Interface process in detail. It details the
interface in detail - from the time of getting data from any external source,
till it gets transferred into Oracle Applications’ GL as Journals. It also
contains information on error handling of the interface.
As part
of the main concurrent program, the following steps are handled:
Batch Control Validation
In this
process, the Journal data is grouped into a batch or batches, depending on the
number of Journal records and the grouping rules. One record exists for each
batch to be maintained in the Batch Control table. Each batch should have one
record in the Batch control table. It will have two statuses namely, Batch and
data status. These statuses will be updated to reflect the current status of a
given Batch. This provides better control over the Batch of Journal records for
importing the Journal data into Oracle Applications.
When the
Batch record(s) hit this table, they will carry the Batch and Data status as
‘New’ and ‘Never Validated’. It will then be continually updated to refl ect
the status of the Batch till it reaches the Data Import.
The Batch
control table record maintains the count of Total Journal lines, the Total
Credit and Debit lines and the respective amounts. When the Batch of Journal
hits the Staging table, a procedure in the Interface package, checks to see if
the counts match by performing the Count and Sum on the associated Journal
records. Accordingly, it updates the Batch and Data status as ‘Control
Validated’ and ‘Control Validation Successful’ or ‘Control Validated’ and ‘Control
Validation Failed’.
The Batch
of Journal which gets successfully control-validated alone would be taken through
the remaining steps.
Data Validation
The
Journal data available in the Journal transaction table is validated on the
required columns, as given below:
Local Currency Journals:
1.
Accounting Date – The period of the date needs to
open in GL module
2.
Segment 1 to Segment n – based on the number of
account segments defined
3.
Currency Code should be defined and exists in
FND_CURRENCIES table
4.
User Journal Source Name - should be defined and
exists in GL_JE_SOURCES table
5.
User Journal Category Name - should be defined and
exists in GL_JE_CATEGORIES table
6.
Derive Set of Books Id based on set of Books name
7.
Actual Flag – A-Actual; B- Budget type Journals
Foreign Currency Journals:
1.
Currency Conversion Rate
2.
Currency Conversion Date
3. Currency Conversion Type
At the end of data validation for each Batch, the
Batch Control and Journal transaction data
table has to be updated as given below:
1.
The Batch Control table’s Batch and Data statuses
have to updated as ‘Validated’ and
‘Validation Successful’/’Validation Failed’/Validation Partially
Successful’.
2.
The Journal Transaction table’s Process Flag has to
be updated as ‘Validated’/ ‘Validation Error’.
3.
The error records and the ERROR_MESSAGE columns
have to be updated with the actual error message. Since it is handled as a
multiple-step process, given the situation of error could occur at any step,
the error messages needs to be appended.
Data Transfer
The
Journal Batches which were successfully validated will then be transferred to
the Open
GL
Interface table. At the Batch control table, the Batch and Data status will be
updated to
‘Transferred’
and ‘Transfer Successful’ / ‘Transfer Error’. The Process Flag columns of the
Journal Transaction table have to be updated as ‘Transferred’ or ‘Transfer
Error’ as well.
Data Import
The
Journal records of a Batch which were successfully transferred to the GL
Interface table
will be
taken up for Importing into Oracle EBS. The Oracle standard Journal Import
program would need to be invoked from back-end for importing Journal data.
Note: The Batch Id is mapped to the GROUP_ID in Oracle,
so that the Batch gets imported into GL as Batch and the “One Fail All Fail”
concept is naturally achieved as well.
The
Oracle Base table would need to be inserted with a record for each Batch of
Journals
Import as
given below:
For
instance,
GL_INTERFACE_CONTROL
table population
INSERT
INTO
gl_interface_control
(
je_source_name,
status,
interface_run_id,
group_id,
set_of_books_id
)
VALUES
(
v_je_source_name,
-- Value from gl_je_sources.je_source_name
‘S’, --
status constant value
v_interface_run_id,
-- gl_journal_import_s.NEXTVAL
g_group_id,
g_sob_id
)
Note: In
the insert statement above, to populate the JE_SOURCE_NAME Columns, fetch the
value from the Column JE_SOURCE_NAME from gl_je_sources table, based on
matching of USER_JE_SOURCE_NAME column.
Submitting
a back-end concurrent request for Journal Import program is given in the following
sample code:
--Submitting
the Concurrent request for Journal Import from Back end PLSQL
package/ program
<number variable> :=
fnd_request.submit_request
(‘SQLGL’, -- p_conc_id OUT NUMBER
‘GLLEZL’,-- ‘GLJIMPT’ p_conc_name IN VARCHAR2,
‘Journal Import - ‘ || batch_rec.source,
SYSDATE,
FALSE,
TO_CHAR(v_interface_run_id), --
gl_journal_import_s.NEXTVAL
TO_CHAR(g_sob_id),
‘N’,
NULL,
NULL,
‘N’,
‘O’--DFF Import N-No Import; O- Without
validation;W-With validation
);
-- A Call
to wait and get a concurrent Request Id of the Journal Import
<BOOLEAN variable>:=
fnd_concurrent.wait_for_request
(v_req_id,
10,
99999,
v_rphase,
v_rstatus,
v_dphase,
v_dstatus,
v_message
);
Based on
the call, update the Staging table (Batch Control and Journal tranaction table)
about the status of the Import, as given below:
IF NOT ((v_dphase = ‘COMPLETE’) AND (v_dstatus = ‘NORMAL’))
Update the Staging table status columns about the
Journal Import Failed
ELSE
Check if no record exists for given batch by using
GROUP_ID
IF no records exists in GL_INTERFACE table
Update the Staging table status columns about the
Journal Import Successful
ELSE
Update the Staging table status columns about the
Journal Import Failed
END IF
END IF;
