Monday, October 31, 2016

Oracle apps GL Trial Balance Detail Report query

Trial Balance in Oracle Apps will give you the Credits, Debits and Net balances for a specific account in a given period. Below query does the same.
Same query in Oracle Apps can be used to find out the Journals posted in a specific Account, Company for a given 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'        

--Actual balances
AND gjh.actual_flag='A' 
AND gjh.period_name='AUG-14'
AND GL.name='US Vision Operation'  --Ledger
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 ;

2 comments:

sivanesan said...

Nice blog. Thank you for sharing. The information you shared is very effective for learners I have got some important suggestions from it. erp software in chennai.

Anonymous said...

copied from http://oracleappstechguide.blogspot.com/2015/06/gl-trail-balance-query-detail.html