YTD actual and YTD budget

[nhsa]

Hi...
How can I calculate YTD actual and budget from gl_balances table?

I found this formula when I searched through metalink :
YTD = begin_balance_dr + period_net_dr - begin_balance_cr - period_net_cr

but when I use it and compare the report output with the system data , I get some figures fine and some figures are not , I'm not sure what is the problem?
maybe on the calculation way??

Any help?
Thanks in advance

 

[mdwyer]

The formula is correct. Are you taking the Actual_Flag into consideration (A/ctual, B/udget, or E/ncumbrance)?

Here is the extract view I use for my data warehouse:

CREATE OR REPLACE VIEW XXLCW_VIEWS.FIN_GL_BALANCES_V
(CODE_COMBINATION_ID, FUND, ORG, PROJECT, ACCOUNT, 
 LOCATION, ACCOUNT_TYPE, PERIOD_ABBR, PERIOD_DATE, REPORT_SIGN, 
 PERIOD_NET_BUDGET_BALANCE, PERIOD_NET_ACTUAL_BALANCE, PERIOD_NET_INV_ENCUMBRANCE, PERIOD_NET_OBL_ENCUMBRANCE, CURRENT_GL_BALANCE, 
 INVOICE_ENCUMBRANCE, OBLIGATION_ENCUMBRANCE, ADOPTED_BUDGET, LOAD_DATE, PERIOD_YEAR)
AS 
SELECT 
  bal.code_combination_id, 
  segment1 FUND, 
  segment2 ORG, 
  segment3 PROJECT, 
  segment4 ACCOUNT, 
  segment5 LOCATION, 
  -- 
  decode( account_type,         'A','Asset', 
          'C','Budgetary (CR)', 'D','Budgetary (DR)', 
          'E','Expense',        'L','Liability', 
          'O','Owners equity',  'R','Revenue', 
          account_type) ACCOUNT_TYPE, 
  -- 
  period_name PERIOD_ABBR, 
  last_day( to_date( period_name, 'MON-RR')) PERIOD_DATE, 
  -- 
  decode( account_type, 'R', -1, -- or 
  decode( segment4, '31000', -1, -- or 
  decode( segment4, '31800', -1, -- or 
  decode( segment4, '39901', -1, -- or 
  decode( segment4, '39999', -1, -- else 
    +1))))) REPORT_SIGN, -- use to reverse signs where appropriate, when requested
  -- 
  sum( decode( actual_flag, 'B', 
    nvl( period_net_dr, 0) - nvl( period_net_cr, 0), 
    0)) PERIOD_NET_BUDGET_BALANCE 
, 
  sum( decode( actual_flag, 'A', 
    nvl( period_net_dr, 0) - nvl( period_net_cr, 0), 
    0)) PERIOD_NET_ACTUAL_BALANCE 
, 
  sum( decode( actual_flag, 'E', decode( encumbrance_type, 'Invoice', 
    nvl( period_net_dr, 0) - nvl( period_net_cr, 0), 
    0), 0)) PERIOD_NET_INV_ENCUMBRANCE 
, 
  sum( decode( actual_flag, 'E', decode( encumbrance_type, 'Obligation', 
    nvl( period_net_dr, 0) - nvl( period_net_cr, 0), 
    0), 0)) PERIOD_NET_OBL_ENCUMBRANCE 
, 
  sum( decode( actual_flag, 'A', 
    nvl( begin_balance_dr, 0) + nvl( period_net_dr, 0) 
  - nvl( begin_balance_cr, 0) - nvl( period_net_cr, 0), 
    0)) CURRENT_GL_BALANCE 
, 
  sum( decode( actual_flag, 'E', decode( encumbrance_type, 'Invoice', 
    nvl( begin_balance_dr, 0) + nvl( period_net_dr, 0) 
  - nvl( begin_balance_cr, 0) - nvl( period_net_cr, 0), 
    0), 0)) INVOICE_ENCUMBRANCE 
, 
  sum( decode( actual_flag, 'E', decode( encumbrance_type, 'Obligation', 
    nvl( begin_balance_dr, 0) + nvl( period_net_dr, 0) 
  - nvl( begin_balance_cr, 0) - nvl( period_net_cr, 0), 
    0), 0)) OBLIGATION_ENCUMBRANCE 
, 
  sum( decode( actual_flag, 'B', 
    nvl( begin_balance_dr, 0) + nvl( period_net_dr, 0) 
  - nvl( begin_balance_cr, 0) - nvl( period_net_cr, 0), 
    0)) ADOPTED_BUDGET, 
  -- 
  sysdate LOAD_DATE, 
  PERIOD_YEAR
FROM 
   gl_balances bal, 
   gl_encumbrance_types enc, 
   gl_code_combinations gcc 
WHERE 
   bal.encumbrance_type_id = enc.encumbrance_type_id(+) AND 
   bal.code_combination_id = gcc.code_combination_id 
GROUP BY 
   bal.code_combination_id, 
   segment1, 
   segment2, 
   segment3, 
   segment4, 
   segment5, 
   account_type, 
   period_name, 
   period_year;

 

[nhsa]

Thanks a lot...............