The following steps have to be created in fusion before extracting data from EBS instance:
- BU
- Legal Entity
- Assign legal entity with BU
- Ledger
- Assign Legal Entity with Ledger
- COA
- Calendar
- Currency
- Assign Calendar, Currency and COA to Ledger
- Categories
Source
create view
CREATE OR REPLACE VIEW apps.xx_journal_import_v
AS
(SELECT CAST (NULL AS VARCHAR2 (30)) AS LOAD, ‘NEW’ status, jh.ledger_id,
jl.je_line_num,
TO_CHAR (jh.default_effective_date,
‘RRRR/MM/DD’
) default_effective_date,
jh.je_source, jh.je_category, jh.currency_code,
TO_CHAR (jh.date_created, ‘RRRR/MM/DD’) date_created,
jh.actual_flag, cc.segment1, cc.segment2, cc.segment3, cc.segment4,
cc.segment5, cc.segment6, cc.segment7, cc.segment8, cc.segment9,
cc.segment10, cc.segment11, cc.segment12, cc.segment13,
cc.segment14, cc.segment15, cc.segment16, cc.segment17,
cc.segment18, cc.segment19, cc.segment20, cc.segment21,
cc.segment22, cc.segment23, cc.segment24, cc.segment25,
cc.segment26, cc.segment27, cc.segment28, cc.segment29,
cc.segment30, jl.entered_dr, jl.entered_cr, jl.accounted_dr,
jl.accounted_cr, jb.NAME ref1, jb.description ref2,
CAST (NULL AS VARCHAR2 (30)) AS ref3, jh.NAME ref4,
jh.description ref5, jh.external_reference ref6,
jh.accrual_rev_flag ref7, jh.accrual_rev_period_name ref8,
jh.accrual_rev_change_sign_flag ref9, jl.description ref10,
CAST (NULL AS VARCHAR2 (30)) AS ref21,
CAST (NULL AS VARCHAR2 (30)) AS ref22,
CAST (NULL AS VARCHAR2 (30)) AS ref23,
CAST (NULL AS VARCHAR2 (30)) AS ref24,
CAST (NULL AS VARCHAR2 (30)) AS ref25,
CAST (NULL AS VARCHAR2 (30)) AS ref26,
CAST (NULL AS VARCHAR2 (30)) AS ref27,
CAST (NULL AS VARCHAR2 (30)) AS ref28,
CAST (NULL AS VARCHAR2 (30)) AS ref29,
CAST (NULL AS VARCHAR2 (30)) AS ref30, jl.stat_amount,
jh.currency_conversion_type,
TO_CHAR (jh.currency_conversion_date,
‘RRRR/MM/DD’
) currency_conversion_date,
jh.currency_conversion_rate, jb.GROUP_ID, jl.CONTEXT,
jl.attribute1, jl.attribute2, jl.attribute3, jl.attribute4,
jl.attribute5, jl.attribute6, jl.attribute7, jl.attribute8,
jl.attribute9, jl.attribute10, jl.attribute11, jl.attribute12,
jl.attribute13, jl.attribute14, jl.attribute15, jl.attribute16,
jl.attribute17, jl.attribute18, jl.attribute19, jl.attribute20,
jh.CONTEXT AS att,
CAST (NULL AS VARCHAR2 (30)) AS average_journal_flag,
jh.originating_bal_seg_value, gl.NAME ledger_name,
jh.encumbrance_type_id, jh.jgzz_recon_ref, jh.period_name,
jh.created_by, jh.creation_date, jh.last_update_date,
jh.last_update_login, jh.last_updated_by,
CAST (NULL AS VARCHAR2 (30)) AS object_version_number
FROM gl_je_batches jb,
gl_je_headers jh,
gl_je_lines jl,
gl_code_combinations cc,
gl_ledgers gl
WHERE gl.ledger_id = 1
AND jb.je_batch_id = jh.je_batch_id
AND jh.je_header_id = jl.je_header_id
AND jh.ledger_id = jl.ledger_id
AND jl.code_combination_id = cc.code_combination_id
AND jh.ledger_id = gl.ledger_id );
Create UTL Package
CREATE OR REPLACE PACKAGE BODY APPS.xx_fusion_jvi_journ_ext
AS
PROCEDURE xx_journal_import_p
AS
/*========================================================================================================+
x_id UTL_FILE.file_type;
l_count NUMBER;
l_dir VARCHAR2 (100);
l_Ledger_id VARCHAR2 (100);
CURSOR c1
IS
SELECT *
FROM xx_journal_import_v
where rownum<10;
BEGIN
SELECT description
INTO l_dir
FROM fnd_lookup_values_vl
WHERE lookup_type=’XX_FUSION_LOOKUPS’ AND lookup_code = ‘JVI_DIR’;
SELECT description
INTO l_Ledger_id
FROM fnd_lookup_values_vl
WHERE lookup_type=’XX_FUSION_LOOKUPS’ AND lookup_code = ‘LEDGER_ID’;
x_id := UTL_FILE.fopen (l_dir, ‘GlInterface.csv’, ‘W’);
DBMS_OUTPUT.put_line (‘START’);
FOR x1 IN c1
LOOP
UTL_FILE.put_line (x_id,
x1.status
|| ‘,’
|| l_Ledger_id —ledger_id from fusion
|| ‘,’
|| x1.default_effective_date
|| ‘,’
|| x1.je_source
|| ‘,’
|| x1.je_category
|| ‘,’
|| x1.currency_code
|| ‘,’
|| x1.date_created
|| ‘,’
|| x1.actual_flag
|| ‘,’
|| x1.segment1
|| ‘,’
|| x1.segment2
|| ‘,’
|| x1.segment3
|| ‘,’
|| x1.segment4
|| ‘,’
|| x1.segment5
|| ‘,’
|| x1.segment6
|| ‘,’
|| x1.segment7
|| ‘,’
|| x1.segment8
|| ‘,’
|| x1.segment9
|| ‘,’
|| x1.segment10
|| ‘,’
|| x1.segment11
|| ‘,’
|| x1.segment12
|| ‘,’
|| x1.segment13
|| ‘,’
|| x1.segment14
|| ‘,’
|| x1.segment15
|| ‘,’
|| x1.segment16
|| ‘,’
|| x1.segment17
|| ‘,’
|| x1.segment18
|| ‘,’
|| x1.segment19
|| ‘,’
|| x1.segment20
|| ‘,’
|| x1.segment21
|| ‘,’
|| x1.segment22
|| ‘,’
|| x1.segment23
|| ‘,’
|| x1.segment24
|| ‘,’
|| x1.segment25
|| ‘,’
|| x1.segment26
|| ‘,’
|| x1.segment27
|| ‘,’
|| x1.segment28
|| ‘,’
|| x1.segment29
|| ‘,’
|| x1.segment30
|| ‘,’
|| x1.entered_dr
|| ‘,’
|| x1.entered_cr
|| ‘,’
|| x1.accounted_dr
|| ‘,’
|| x1.accounted_cr
|| ‘,’
|| x1.ref1
|| ‘,’
|| x1.ref2
|| ‘,’
|| x1.ref3
|| ‘,’
|| x1.ref4
|| ‘,’
|| x1.ref5
|| ‘,’
|| x1.ref6
|| ‘,’
|| x1.ref7
|| ‘,’
|| x1.ref8
|| ‘,’
|| x1.ref9
|| ‘,’
|| x1.ref10
|| ‘,’
|| x1.ref21
|| ‘,’
|| x1.ref22
|| ‘,’
|| x1.ref23
|| ‘,’
|| x1.ref24
|| ‘,’
|| x1.ref25
|| ‘,’
|| x1.ref26
|| ‘,’
|| x1.ref27
|| ‘,’
|| x1.ref28
|| ‘,’
|| x1.ref29
|| ‘,’
|| x1.ref30
|| ‘,’
|| x1.stat_amount
|| ‘,’
|| x1.currency_conversion_type
|| ‘,’
|| x1.currency_conversion_date
|| ‘,’
|| x1.currency_conversion_rate
|| ‘,’
|| x1.GROUP_ID
|| ‘,’
|| x1.CONTEXT
|| ‘,’
|| x1.attribute1
|| ‘,’
|| x1.attribute2
|| ‘,’
|| x1.attribute3
|| ‘,’
|| x1.attribute4
|| ‘,’
|| x1.attribute5
|| ‘,’
|| x1.attribute6
|| ‘,’
|| x1.attribute7
|| ‘,’
|| x1.attribute8
|| ‘,’
|| x1.attribute9
|| ‘,’
|| x1.attribute10
|| ‘,’
|| x1.attribute11
|| ‘,’
|| x1.attribute12
|| ‘,’
|| x1.attribute13
|| ‘,’
|| x1.attribute14
|| ‘,’
|| x1.attribute15
|| ‘,’
|| x1.attribute16
|| ‘,’
|| x1.attribute17
|| ‘,’
|| x1.attribute18
|| ‘,’
|| x1.attribute19
|| ‘,’
|| x1.attribute20
|| ‘,’
|| x1.att
|| ‘,’
|| x1.AVERAGE_JOURNAL_FLAG
||’,’
|| x1.originating_bal_seg_value
|| ‘,’
|| x1.NAME
|| ‘,’
|| x1.encumbrance_type_id
|| ‘,’
|| x1.jgzz_recon_ref
|| ‘,’
|| x1.period_name
);
l_count := c1%ROWCOUNT;
END LOOP;
DBMS_OUTPUT.put_line (l_count);
fnd_file.put_line (fnd_file.output, ‘TOTAL RECORD:’ || l_count);
UTL_FILE.fclose (x_id);
EXCEPTION
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line (fnd_file.LOG, ‘invalid operation’);
UTL_FILE.fclose_all;
RAISE;
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line (fnd_file.LOG, ‘invalid path’);
UTL_FILE.fclose_all;
RAISE;
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line (fnd_file.LOG, ‘invalid mode’);
UTL_FILE.fclose_all;
RAISE;
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line (fnd_file.LOG, ‘invalid filehandle’);
UTL_FILE.fclose_all;
RAISE;
WHEN UTL_FILE.read_error
THEN
fnd_file.put_line (fnd_file.LOG, ‘read error’);
UTL_FILE.fclose_all;
RAISE;
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line (fnd_file.LOG, ‘internal error’);
UTL_FILE.fclose_all;
RAISE;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, ‘other error’);
UTL_FILE.fclose_all;
RAISE;
END xx_journal_import_p;
PROCEDURE XX_MAIN_JVI(Errbuf OUT VARCHAR2 ,
Retcode OUT VARCHAR2)
AS
BEGIN
xx_journal_import_p;
CREAETZIPPRO(‘/xxxx/TEST/apps/apps_st/appl/XXC/12.0.0/EBS2Fusion/JVI/’,’GlInterface.zip’);
END XX_MAIN_JVI;
END xx_fusion_jvi_journ_ext;
/
Download FBDI from Oracle Enterprise Repository and mapped the UTL file data based on view and run schedule process in Fusion
