Trial Balance Query In Oracle Fusion

Trial Balance Query In Oracle Fusion

We are going to discuss about Trial Balance Query In Oracle Fusion. We will be share the detail sql query which helps to extract the GL Trial Balances in Oracle Fusion. We can refer this SQL query to develop the custom BIP report in Oracle fusion. In Oracle Fusion , we need Trial Balance report to match the AP GL and AR GL. We uses GL trial Balance report for the GL and Subledger reconciliation in oracle fusion. This is one of the most important sql query related to GL in Oracle Fusion.

Trial Balance helps to provide the Summary level GL accounts balances. In trial balance , we have the credit and debit amount columns too in the oracle fusion reports. Please find below the detail level of Trial Balance Query In Oracle Fusion.

Trial Balance Query In Oracle Fusion

5 Important Tables of Trial Balance Query

1.gl_je_headers

2.gl_je_lines

3.gl_ledgers gl,

4.gl_code_combinations

5.GL_JE_BATCHES

Detail level Trial Balance Query In Oracle Fusion

Here below , we are sharing the SQL query to extract the Trial Balance from GL in Oracle Fusion.

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

Trial Balance Query In Oracle Fusion

Trial Balance Query In Oracle Fusion