Introduction
This Post illustrates steps required to Extracting InterCompany batch details in Oracle EBS R12.
Extracting InterCompany batch details Query
SELECT DISTINCT xep.NAME provider, ftb.status batch_status,
ftb.batch_number batch_number,
ftb.description batch_description, ftb.batch_date batch_date,
ftb.gl_date accounting_date,
fttb.trx_type_code transaction_type,
(SELECT xep2.NAME
FROM xle_entity_profiles xep2
WHERE 1 = 1
AND xep2.legal_entity_id = fth.to_le_id) receiver,
fth.trx_number transaction_number,
DECODE (party_type_flag,
‘I’, ‘Provider’,
‘R’, ‘Receiver’
) distribution,
fdl.dist_number distribution_number,
ftb.currency_code currency,
NVL (fdl.amount_dr, -1 * fdl.amount_cr) entered_amount,
(SELECT gll.currency_code
FROM gl_ledgers gll,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir
WHERE gll.ledger_id = gjh.ledger_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gir.reference_2 = ftb.batch_id
AND gir.reference_3 = fth.trx_id
AND gir.reference_4 = ftl.line_id
AND gir.reference_5 = fdl.dist_id
AND gir.je_batch_id = gjb.je_batch_id
AND gir.je_header_id = gjh.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND ( gjh.ledger_id = fth.to_ledger_id
OR gjh.ledger_id = ftb.from_ledger_id
)
AND ROWNUM = 1) primary_currency,
(SELECT NVL (gjl.accounted_dr,
-1 * gjl.accounted_cr
)
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gir.reference_2 = ftb.batch_id
AND gir.reference_3 = fth.trx_id
AND gir.reference_4 = ftl.line_id
AND gir.reference_5 = fdl.dist_id
AND gir.je_batch_id = gjb.je_batch_id
AND gir.je_header_id = gjh.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND ( gjh.ledger_id = fth.to_ledger_id
OR gjh.ledger_id = ftb.from_ledger_id
)
AND ROWNUM = 1) primary_amount,
fdl.description
FROM xle_entity_profiles xep,
fun_trx_batches ftb,
fun_trx_types_b fttb,
fun_trx_headers fth,
fun_trx_lines ftl,
fun_dist_lines fdl
WHERE 1 = 1
AND xep.legal_entity_id = ftb.from_le_id
AND fttb.trx_type_id = ftb.trx_type_id
AND ftb.batch_id = fth.batch_id
AND fdl.trx_id = fth.trx_id
AND ftl.trx_id = fth.trx_id
AND ftl.line_id = fdl.line_id
AND ftb.trx_type_code = ‘IC_GL’
What we expect in the script.
This script helps us to comprehend how Extracting InterCompany batch details Query.A couple of tables which is being used in the scripts are gl_je_batches,gl_je_headers,gl_je_lines,gl_import_references etc.
Summary
This Post described the script extracting InterCompany batch details Query in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at venkatesh.b@doyensys.com or use the comment section below to ask your questions.
