Importing journals Oracle Apps R12
To prepare for importing journals:
1. Define all
account segment values used in your feeder systems.
2. Define your
ledger using Accounting Setup Manager. The accounting setup must have a
Complete status to use its ledgers.
3. Define or
enable all currencies used in your feeder systems.
4. Define the
journal entry sources used in your feeder systems. You can also specify whether
you want General Ledger to store journal reference information from your feeder
systems for a particular source. 5. Define journal entry categories used in
your feeder systems.
6. If you want
Journal Import to assign sequential numbers to your journal entries, enable
sequential numbering, specifying Automatic as both your numbering and document
generation method.
7. Open periods
used in your feeder system. You can only import journals into Open or
Future-Enterable periods in General Ledger.
8. Run the
Optimizer program to create indexes on your account segments.
9. Define the
concurrent program controls to improve the performance of Journal Import by
setting the amount of disk space and memory it uses. The Journal Import program
requires approximately 1.4 megabytes of memory to run. You can also specify
whether to save your Journal Import data each time you run Journal Import.
Journal Import runs faster if you do not archive your data.
10. Disable
dynamic insertion. Journal Import runs much faster when it does not have to
create new account combinations dynamically.
11. Define any
accounts used in your feeder systems that you have not yet defined in General
Ledger.
Exporting Data
From Your Feeder System, Journal Import receives accounting data from the
GL_INTERFACE table. For non-Oracle applications, you must import data from your
feeder systems to this table. Use an import utility, or have your on-site MIS
personnel or Oracle consultant develop an import program for you. Your import program
should convert data from your feeder system into a standard data format that
Journal Import can read from the GL_INTERFACE table. Journal Import can then
convert your import data into your General Ledger application journal entries.
You can write an import program to import data from a non-Oracle system, or you
can write an import program to import historical data from your previous
accounting system.
The GL_INTERFACE
Table The GL_INTERFACE table is where Journal Import receives accounting data
that you import from other systems. When Journal Import receives this data, it
validates and converts your import data into journal entries within your
General Ledger application. The GL_INTERFACE table is organized by columns in
which your General Ledger application categorizes and stores specific
accounting data. For example, journal entry source information is stored in the
column called USER_JE_SOURCE_NAME. GL_INTERFACE contains the columns shown in
the following table.
Column Name
|
Type
|
Nullable
|
status
|
varchar2(50)
|
|
ledger_id
|
number(15)
|
y
|
accounting_date
|
date
|
|
currency_code
|
varchar2(15)
|
|
date_created
|
date
|
|
created_by
|
number(15)
|
|
actual_flag
|
varchar2(1)
|
|
user_je_category_name
|
varchar2(25)
|
|
user_je_source_name
|
varchar2(25)
|
|
currency_conversion_date
|
date
|
y
|
encumbrance_type_id
|
number
|
y
|
budget_version_id
|
number
|
y
|
user_currency_conversion_type
|
varchar2(30)
|
y
|
currency_conversion_rate
|
number
|
y
|
average_journal_flag
|
varchar2(1)
|
y
|
originating_bal_seg_value
|
varchar2(25)
|
y
|
segment1
|
varchar2(25)
|
y
|
segment2
|
varchar2(25)
|
y
|
segment3
|
varchar2(25)
|
y
|
segment4
|
varchar2(25)
|
y
|
segment5
|
varchar2(25)
|
y
|
segment6
|
varchar2(25)
|
y
|
segment7
|
varchar2(25)
|
y
|
segment8
|
varchar2(25)
|
y
|
segment9
|
varchar2(25)
|
y
|
segment10
|
varchar2(25)
|
y
|
segment11
|
varchar2(25)
|
y
|
segment12
|
varchar2(25)
|
y
|
segment13
|
varchar2(25)
|
y
|
segment14
|
varchar2(25)
|
y
|
segment15
|
varchar2(25)
|
y
|
segment16
|
varchar2(25)
|
y
|
segment17
|
varchar2(25)
|
y
|
segment18
|
varchar2(25)
|
y
|
segment19
|
varchar2(25)
|
y
|
segment20
|
varchar2(25)
|
y
|
segment21
|
varchar2(25)
|
y
|
segment22
|
varchar2(25)
|
y
|
segment23
|
varchar2(25)
|
y
|
segment24
|
varchar2(25)
|
y
|
segment25
|
varchar2(25)
|
y
|
segment26
|
varchar2(25)
|
y
|
segment27
|
varchar2(25)
|
y
|
segment28
|
varchar2(25)
|
y
|
segment29
|
varchar2(25)
|
y
|
segment30
|
varchar2(25)
|
y
|
entered_dr
|
number
|
y
|
entered_cr
|
number
|
y
|
accounted_dr
|
number
|
y
|
accounted_cr
|
number
|
y
|
transaction_date
|
date
|
y
|
reference1
|
varchar2(100)
|
y
|
reference2
|
varchar2(240)
|
y
|
reference3
|
varchar2(100)
|
y
|
reference4
|
varchar2(100)
|
y
|
reference5
|
varchar2(240)
|
y
|
reference6
|
varchar2(100)
|
y
|
reference7
|
varchar2(100)
|
y
|
reference8
|
varchar2(100)
|
y
|
reference9
|
varchar2(100)
|
y
|
reference10
|
varchar2(240)
|
y
|
reference11
|
varchar2(240)
|
y
|
reference12
|
varchar2(100)
|
y
|
reference13
|
varchar2(100)
|
y
|
reference14
|
varchar2(100)
|
y
|
reference15
|
varchar2(100)
|
y
|
reference16
|
varchar2(100)
|
y
|
reference17
|
varchar2(100)
|
y
|
reference18
|
varchar2(100)
|
y
|
reference19
|
varchar2(100)
|
y
|
reference20
|
varchar2(100)
|
y
|
reference21
|
varchar2(240)
|
y
|
reference22
|
varchar2(240)
|
y
|
reference23
|
varchar2(240)
|
y
|
reference24
|
varchar2(240)
|
y
|
reference25
|
varchar2(240)
|
y
|
reference26
|
varchar2(240)
|
y
|
reference27
|
varchar2(240)
|
y
|
reference28
|
varchar2(240)
|
y
|
reference29
|
varchar2(240)
|
y
|
reference30
|
varchar2(240)
|
y
|
je_batch_id
|
number(15)
|
y
|
period_name
|
varchar2(15)
|
y
|
je_header_id
|
number(15)
|
y
|
je_line_num
|
number(15)
|
y
|
chart_of_accounts_id
|
number(15)
|
y
|
functional_currency_code
|
varchar2(15)
|
y
|
code_combination_id
|
number(15)
|
y
|
date_created_in_gl
|
date
|
y
|
warning_code
|
varchar2(4)
|
y
|
status_description
|
varchar2(240)
|
y
|
stat_amount
|
number
|
y
|
group_id
|
number(15)
|
y
|
request_id
|
number(15)
|
y
|
subledger_doc_sequence_id
|
number
|
y
|
subledger_doc_sequence_value
|
number
|
y
|
attribute1
|
varchar2(150)
|
y
|
attribute2
|
varchar2(150)
|
y
|
gl_sl_link_id
|
number
|
y
|
gl_sl_link_table
|
varchar2(30)
|
y
|
attribute3
|
varchar2(150)
|
y
|
attribute4
|
varchar2(150)
|
y
|
attribute5
|
varchar2(150)
|
y
|
attribute6
|
varchar2(150)
|
y
|
attribute7
|
varchar2(150)
|
y
|
attribute8
|
varchar2(150)
|
y
|
attribute9
|
varchar2(150)
|
y
|
attribute10
|
varchar2(150)
|
y
|
attribute11
|
varchar2(150)
|
y
|
attribute12
|
varchar2(150)
|
y
|
attribute13
|
varchar2(150)
|
y
|
attribute14
|
varchar2(150)
|
y
|
attribute15
|
varchar2(150)
|
y
|
attribute16
|
varchar2(150)
|
y
|
attribute17
|
varchar2(150)
|
y
|
attribute18
|
varchar2(150)
|
y
|
attribute19
|
varchar2(150)
|
y
|
attribute20
|
varchar2(150)
|
y
|
context
|
varchar2(150)
|
y
|
context2
|
varchar2(150)
|
y
|
invoice_date
|
date
|
y
|
tax_code
|
varchar2(15)
|
y
|
invoice_identifier
|
varchar2(20)
|
y
|
invoice_amount
|
number
|
y
|
context3
|
varchar2(150)
|
y
|
ussgl_transaction_code
|
varchar2(30)
|
y
|
descr_flex_error_message
|
varchar2(240)
|
y
|
jgzz_recon_ref
|
varchar2(240)
|
y
|
reference_date
|
date
|
y
|
set_of_books_id
|
number(15)
|
y
|
balancing_segment_value
|
varchar2(25)
|
y
|
management_segment_value
|
varchar2(25)
|
y
|
funds_reserved_flag
|
varchar2(1)
|
y
|
code_combination_id_interim
|
number(15)
|
y
|
1.Submit GL
Journal Import From PLSQL or from SRS window :GL Journal
Import
GL Journal
Import is used to import journals from sub ledgers and external systems into
Oracle GL. We can do it manually or programatically. This post describes how to
call the GL Journal Import from plsql procedure. We will be looking at
importing AR (Receivable) Journal Entries.
Also one of Pre
Requisite step is to insert the journal records to be imported into
GL_INTERFACE Table. The status of the records in the GL_INTERFACE table for new
records should be “NEW”.
DECLARE
l_conc_id NUMBER;
l_int_run_id NUMBER;
l_access_set_id NUMBER;
l_org_id NUMBER := 81;
l_sob_id NUMBER := 101;
l_user_id NUMBER := FND_GLOBAL.USER_ID;
l_resp_id NUMBER := FND_GLOBAL.RESP_ID;
l_resp_app_id NUMBER := FND_GLOBAL.RESP_APPL_ID;
BEGIN
fnd_global.apps_initialize
(
user_id => l_user_id --User Id
,resp_id => l_resp_id --Responsibility Id
,resp_appl_id => l_resp_app_id --Responsibility Application Id
);
{To get these Id's directly run query mentioned in below link :
mo_global.set_policy_context('S',l_org_id);
SELECT
gl_journal_import_s.NEXTVAL
INTO
l_int_run_id
FROM
dual;
SELECT
access_set_id
INTO
l_access_set_id
FROM
gl_access_sets
WHERE
name = ‘Ledger_Name’ ;
INSERT INTO gl_interface_control
(
je_source_name
,interface_run_id
,status
,set_of_books_id
)
VALUES
(
'Receivables'
,l_int_run_id
,'S'
,l_sob_id
);
l_conc_id := fnd_request.submit_request
( application => 'SQLGL'
,program => 'GLLEZL'
,description => NULL
,start_time => SYSDATE
,sub_request => FALSE
,argument1 => l_int_run_id --interface run id
,argument2 => l_access_set_id --data access set_id
,argument3 => 'N' --post to suspense
,argument4 => NULL --from date
,argument5 => NULL --to date
,argument6 => 'N' --summary mode
,argument7 => 'N' --import DFF
,argument8 => 'Y' --backward mode
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('GL Import Submitted.
Request Id : '||l_conc_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while
submitting the GL Import Program.');
DBMS_OUTPUT.PUT_LINE('Error :
'||SQLCODE||'-'||SUBSTR(SQLERRM,1,200));
END;
Hope this
helps!! Let me know your comments and feedback. If you have any questions you
can leave a comment or email me @Conflatetogether@gmail.com
Thanks for
reading.
