YTD Balance query in oracle apps

YTD Balance query in oracle apps

Hi Friends, We are going to discuss about YTD Balance query in oracle apps. We will be share the query which helps to extract the YTD GL balances from Oracle Apps system. This sql Query will share the summary level GL balances for each GL account. We will be able to get the YTD and PTD GL balances from this query in oracle apps. This is one of the most important sql query related to GL which we do need in year end and month end or period close activities in oracle apps. You can refer this sql query to develop the custom GL reports which will help to extract the GL balances in both YTD and PTD formats. Please find below the detail about YTD Balance query in oracle apps.

YTD Balance query in oracle apps

Important Tables used by YTD Balance query

1.gl_balances
2.gl_ledgers
3.gl_code_combinations_kfv
4.FND_FLEX_VALUES_VL
5.gl_ledgers

Detail YTD Balance query in oracle apps

SELECT 
        gl.name,
        gb.LEDGER_ID,
gcc.segment1|| '-'|| gcc.segment2|| '-'|| gcc.segment3|| '-'|| gcc.segment4|| '-'|| gcc.segment5 "GL ACCOUNT",
        gcc.SEGMENT3 "Natural Account",
         FFV.DESCRIPTION "Natural Account Desc",
         gb.PERIOD_NAME,
         NVL (gb.PERIOD_NET_DR, 0) - NVL (gb.PERIOD_NET_CR, 0) PTD,
         (NVL (gb.BEGIN_BALANCE_DR, 0) - NVL (gb.BEGIN_BALANCE_CR, 0))
         + (NVL (gb.PERIOD_NET_DR, 0) - NVL (gb.PERIOD_NET_CR, 0)) YTD
    FROM  gl_balances gb,
          gl_ledgers gl,
          gl_code_combinations_kfv gcc,
          APPS.FND_FLEX_VALUES_VL FFV,
          gl_ledgers gl
   WHERE  gb.code_combination_id = gcc.code_combination_id
      AND gb.LEDGER_ID = gl.ledger_id
      AND gcc.SEGMENT3 = FFV.FLEX_VALUE
      AND gl.name ='LEDGER NAME'
      AND gb.period_name ='OCT-18-19'
ORDER BY  gb.LEDGER_ID,
gcc.segment1|| '-'|| gcc.segment2|| '-'|| gcc.segment3|| '-'|| gcc.segment4|| '-'|| gcc.segment5

YTD Balance query in oracle apps