Quarterly Trial Balance Report Query (QTD)

Quarterly Trail Balance report used to get TB for particular Quarter. Some of major companies need to publish  there Profit and loss number. For this one can compared it using QTD report.

Quarter may contain 3 or 4(Including Adjacent Period)  periods e.g. Jan-16, Feb-16, Mar-16 & Mar-Adj-16. One can easily get details about period structured from back end by querying GL_PERIODS table.

In this table you will get column like Period Name, Quarter_Num, Period_Year etc. by using this column we can have condition in our query to get detail for Quarter period. Period_set_name also important column in GL_PERIODS table. Functional people defined period set name in GL. e.g. Fiscal, Calendar, or any custom XXCust_Set.

Query to retrieve Quarterly Trial balance:

SELECT gcc.segment3 account,
  gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3) Acc_Description,
  SUM(NVL(GJL.ACCOUNTED_DR,0))Q_ACC_DR,
  SUM(NVL(GJL.ACCOUNTED_CR,0))Q_ACC_CR,
  SUM(NVL(GJL.ACCOUNTED_DR,0)-NVL(GJL.ACCOUNTED_CR,0))QTD_BALANCE
FROM gl_je_headers gjh,
  gl_je_lines gjl,
  gl_ledgers gl,
  gl_code_combinations_kfv gcc
WHERE gjl.je_header_id     = gjh.je_header_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'                                      ---- 'A' for Actuals
AND gjh.period_name       IN
  (
--Query to retrieve period name for first quarter----
 SELECT Period_name
  FROM gl_periods;
  WHERE period_set_name='XXCust_Set'
  AND quarter_num=1                                          ---- 1 will consider first quarter Jan-Mar
 AND period_year=2016
--
  )
  AND gl.name               = 'US Vision Operation' --Ledger Name
  GROUP BY gcc.segement3,
  gcc.chart_of_accounts_id
order by 1;