PTD and YTD balances query in Oracle R12

PTD and YTD balances query in Oracle R12

select 
  * 
from 
  (
    SELECT 
      gb.currency_code, 
      gcc.concatenated_segments "Chart_Of_Accounts", 
      SUM(
        nvl(gb.begin_balance_dr, 0) - nvl(gb.begin_balance_cr, 0)
      ) opening_balance, 
      sum(gb.period_net_dr) dr, 
      sum(gb.period_net_cr) cr, 
      sum(
        nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0)
      ) ptd, 
      SUM(
        nvl(gb.begin_balance_dr, 0) - nvl(gb.begin_balance_cr, 0) +(
          nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0)
        )
      ) closing_bal 
    FROM 
      gl_balances gb, 
      gl_code_combinations_kfv gcc 
    WHERE 
      gb.code_combination_id = gcc.code_combination_id -- AND gcc.concatenated_segments = '001.0000.120120.000.000000.0000.000.0000'
      and segment1 = '001' 
      and segment3 = '120120' 
      AND gb.ledger_id = 1000
      AND gb.actual_flag = 'A' 
      AND gb.period_name = 'DEC-22' -- AND gb.currency_code = (
      -- SELECT currency_code FROM gl_ledgers
      -- WHERE ledger_id = gb.ledger_id )
    GROUP BY 
      gb.currency_code, 
      gcc.concatenated_segments
  ) 
where 
  OPENING_BALANCE<>0 
  or dr<>0 
  or cr<>0  
  or ptd<>0 
  or CLOSING_BAL<>0 ;

Related posts:

• PTD and YTD balances query in Oracle R12
• Accounting entries not transferred to the General Ledger
• R12 AR cash receipt SLA and GL details query
• Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets
• Query to get description of GL Code Combination

Upload your own post and refer it anywhere anytime: