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
.
.
.
.
.
.