Monday, October 31, 2016

Oracle Apps GL Period to Date(PTD) and Year to Date(YTD) query

Below query will give YTD and PTD balances for specific Account posted in a specific Period.

SELECT   gb.LEDGER_ID,
         glcc.SEGMENT1,
         glcc.segment2,
         glcc.SEGMENT3,
         glcc.segment4,
         glcc.segment5,
         glcc.segment6
         FFV.DESCRIPTION,
         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 glcc,
          APPS.FND_FLEX_VALUES_VL FFV,
          gl_ledgers gl
   WHERE  gb.code_combination_id = glcc.code_combination_id
      AND gb.LEDGER_ID = gl.ledger_id
      AND GLCC.SEGMENT3 = FFV.FLEX_VALUE
      AND gl.name = :ledger_name
      AND gb.period_name = :PERIOD_NAME
ORDER BY  gb.LEDGER_ID,
          glcc.SEGMENT1,
          glcc.segment2,
          glcc.SEGMENT3,
          glcc.segment4,
          glcc.segment5,
          glcc.segment6

Oracle Apps, General Ledger, YTD query, PTD query

No comments: