GL Trial Balance Query -Detail

This is Trial balance detail query which will gives you detail for balance against each code combination with journal, category , batch name and source detail. Trial balance total should be always zero for any period.

select
gjh.name Journal_name,
gjh.je_category category_name,
gjh.je_source source_name,
gjb.name batch_name,
GCC.SEGMENT1,   -- comapny
GCC.SEGMENT2,  --cost center
GCC.SEGMENT3,  -- account
GCC.SEGMENT4,  ---Project
GCC.SEGMENT5, --Product
GCC.SEGMENT6, --Others
SUM(NVL(GJL.ACCOUNTED_DR,0))ACCOUNTED_DR,
SUM(NVL(GJL.ACCOUNTED_CR,0))ACCOUNTED_CR,
SUM(NVL(GJL.ACCOUNTED_DR,0)-NVL(GJL.ACCOUNTED_CR,0))BALANCE
from gl_je_headers gjh,
gl_je_lines gjl,
gl_ledgers gl,
gl_code_combinations gcc,
GL_JE_BATCHES GJB
where gjl.je_header_id = gjh.je_header_id
AND gjh.je_batch_id=gjb.je_batch_id
and gjl.code_combination_id=gcc.code_combination_id
and gjh.ledger_id=gl.ledger_id
and gjh.status='P'         --This will pick all posted journal entry
AND gjh.actual_flag='A'  -- This is for Actual entry A-Actual, B-Budget
AND gjh.period_name='MAY-2015'
AND GL.name='US Vision Operation'   --Ledger Name for organization
GROUP BY
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
gjh.je_category ,
gjh.je_source ,
gjb.name,
gjh.name ;