GL Trial Balance Query in oracle apps r12

GL Trial Balance Query in oracle apps r12

Hi Friends, We are going to discuss about one important sql query which we need in the month end process and that is called 'GL Trial Balance Query . We are sharing the complete sql query , which helps to extract the complete GL trial in oracle apps r12. Using this query , we will be able to find out our GL trial balance in oracle apps system. GL trial report is one of the important report , in the month end process in oracle apps. We need this report during month end. We also need  this report , if we are doing the Payables and GL reconciliation. 

GL trial balance is the summary of all GL accounts balances which hit for the particular month and based on that we do out AP GL and AR G L reconciliations. GL trial balances helps to reconcile the ledgers with sub ledgers. Using this GL trial balance sql query , we can develop the custom GL trial balance report in oracle apps. This query helps to extract the data GL trial balance data directly from Data base or we can also develop the custom report. Please find below the details GL Trial Balance Query in oracle apps r12.

GL Trial Balance Query in oracle apps r12

4 Important Tables used by GL Trial Balance Query 

1.gl_je_headers

2.gl_je_lines

3.gl_ledgers

4.gl_code_combinations

Click To Know More

Detail Level of GL Trial Balance Query in oracle apps r12 GL Trial Balance Query in oracle apps r12

Here below is the sql query which helps to GL trial balance in oracle apps.

select

gjb.name "Journal Batch Name",

gjh.name "Journal Name",

gjh.je_source "Journal Source",

gjh.je_category "journal Category Name",

gcc.segment1|| '-'|| gcc.segment2|| '-'|| gcc.segment3|| '-'|| gcc.segment4|| '-'|| gcc.segment5 "GL ACCOUNT",

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.status='P'      -- P FOR POSTER JOURNALS -- U FOR UNOSTED JOURNALS---  

AND gjh.period_name='DEC-19-20'

AND GL.name='LEDGER NAME'

AND gjh.je_batch_id=gjb.je_batch_id

and gjh.ledger_id=gl.ledger_id

and gjl.code_combination_id=gcc.code_combination_id

AND gjh.actual_flag='A' 

GROUP BY

gjh.je_category ,

gjh.je_source ,

gjb.name,

gjh.name,

gcc.segment1|| '-'|| gcc.segment2|| '-'|| gcc.segment3|| '-'|| gcc.segment4|| '-'|| gcc.segment5

GL Trial Balance Query in oracle apps r12