GL Trial Balance Query -Summary

In this query segment3 is gl account against which credit , debit and balance value show. To get Gl segment3 i.e account description i used gl_flexfield_pkg.get_description_sql API.



select
GCC.SEGMENT3 account,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3)description,
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_kfv 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'
AND gjh.actual_flag='A'
AND gjh.period_name='DEC-2014'
AND GL.name= 'US Vision Operation'
GROUP BY
GCC.SEGMENT3,
gcc.chart_of_accounts_id
order by 1;


Output
-------------------------------------------------------------------------------------------------
Account     Description                                  accounted_dr   accounted_cr    Balance(Total)
12536        Sales account                                             3000                1000                    2000
.
.
.
.
.
.

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 ;