GL query in oracle fusion

GL query in oracle fusion

In this post , We will be discuss about GL query in oracle fusion. This query will help to extract the GL Journal details in oracle fusion. We will use this GL query to develop the custom BIP reports in oracle fusion. This GL query will be using most of the important GL tables of Oracle fusion for extracting the GL details in oracle fusion. Here below is the detail detail list of GL query in oracle fusion.

Most Important tables used by GL query in oracle fusion

1.gl_je_batches
2.gl_je_headers
3.gl_je_lines
4.gl_code_combinations
5.gl_je_categories
6.gl_je_sources

Detail GL query in oracle fusion

Here below is the complete SQL GL Query in Oracle Fusion.

select   
  gjb.name GL_BATCH_NAME,
  gjb.description GL_BATCH_DESCRIPTION,
  gjh.period_name,
  gjc.user_je_category_name "JOURNAL_CATEGORY_NAME",
  gjl.PERIOD_NAME,
 gjh.actual_flag "ACTUAL_FLAG",
 gcc.CODE_COMBINATION_ID ,
 gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 gl_code,
gjl.currency_code,
  gjl.entered_dr ,
  gjl.entered_cr ,
  gjl.accounted_dr,
  gjl.accounted_cr,
  gjl.stat_amount,
  to_char(gjl.currency_conversion_date,'DD/MM/YYYY') currency_conversion_date,
  gjl.currency_conversion_type,
  gjl.currency_conversion_rate,
  gjl.status
from gl_je_batches gjb,
  gl_je_headers gjh,
  gl_je_lines gjl ,
  gl_code_combinations gcc,
 gl_je_categories gjc,
 gl_je_sources gjs
where 1=1
and gjb.je_batch_id       =gjh.je_batch_id
AND gjh.je_header_id        = gjl.je_header_id
AND gcc.code_combination_id = gjl.code_combination_id
and gjc.je_category_name=gjh.je_category
and gjs.je_source_name=gjh.je_source
and gjh.period_name=:PERIOD