In this post, I will share GL query which provide details of journal entries in General Ledger.
Tables
gl_je_headers
gl_je_lines
gl_je_batches
gl_code_combinations
xle_entity_profiles
gl_periods
gl_je_lines
gl_je_batches
gl_code_combinations
xle_entity_profiles
gl_periods
GL Detailed Query
SELECT xep.name legal_entity_name,
gjh.legal_entity_id,
gjh.je_header_id,
TRUNC(gjh.creation_date) journal_date,
gjh.default_effective_date accounting_date,
gjh.posted_date,
gjh.je_source,
gjh.je_category,
gjh.name journal_name,
gjh.period_name,
gjb.name batch_name,
gjh.POSTING_ACCT_SEQ_VALUE doc_sequence_value,
gjl.je_line_num,
gcc.segment1 company,
gcc.segment2 cost_center,
gcc.segment3 natural_account,
gcc.segment4 product,
gcc.segment5 intercompany,
gcc.segment6 future,
gjl.description,
gjl.accounted_cr,
gjl.accounted_dr
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_je_batches gjb,
gl_code_combinations gcc,
xle_entity_profiles xep,
gl_periods gp
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.legal_entity_id = xep.legal_entity_id
AND gjh.status = 'P' -- only posted journals
AND gjh.period_name = gp.period_name
AND gp.period_set_name IN (SELECT period_set_name
FROM gl_ledgers
WHERE ledger_id IN (SELECT ledger_id
FROM xle_le_ou_ledger_v
WHERE legal_entity_id = :p_entity_id
)
)
AND xep.legal_entity_id = :p_entity_id
AND TO_NUMBER(gp.period_year||lpad(gp.period_num,2,'0')) >= :p_from_period
AND TO_NUMBER(gp.period_year||lpad(gp.period_num,2,'0')) <= :p_to_period
ORDER BY gjh.je_header_id, gjl.je_line_num
gjh.legal_entity_id,
gjh.je_header_id,
TRUNC(gjh.creation_date) journal_date,
gjh.default_effective_date accounting_date,
gjh.posted_date,
gjh.je_source,
gjh.je_category,
gjh.name journal_name,
gjh.period_name,
gjb.name batch_name,
gjh.POSTING_ACCT_SEQ_VALUE doc_sequence_value,
gjl.je_line_num,
gcc.segment1 company,
gcc.segment2 cost_center,
gcc.segment3 natural_account,
gcc.segment4 product,
gcc.segment5 intercompany,
gcc.segment6 future,
gjl.description,
gjl.accounted_cr,
gjl.accounted_dr
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_je_batches gjb,
gl_code_combinations gcc,
xle_entity_profiles xep,
gl_periods gp
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.legal_entity_id = xep.legal_entity_id
AND gjh.status = 'P' -- only posted journals
AND gjh.period_name = gp.period_name
AND gp.period_set_name IN (SELECT period_set_name
FROM gl_ledgers
WHERE ledger_id IN (SELECT ledger_id
FROM xle_le_ou_ledger_v
WHERE legal_entity_id = :p_entity_id
)
)
AND xep.legal_entity_id = :p_entity_id
AND TO_NUMBER(gp.period_year||lpad(gp.period_num,2,'0')) >= :p_from_period
AND TO_NUMBER(gp.period_year||lpad(gp.period_num,2,'0')) <= :p_to_period
ORDER BY gjh.je_header_id, gjl.je_line_num