Thursday, August 06, 2015

Query for Actual vs Budget Data

SELECT bal.LEDGER_ID,
  bal.code_combination_id,
  gcc.segment1 Company,
  gcc.segment2 Account,
  gcc.segment5 Cost_Center,
 -- gcc.segment2,
               (SELECT v.description
                 --INTO  v_description1
                FROM  Fnd_Flex_Values_Vl v
                   , fnd_flex_value_sets S
                   , FND_ID_FLEX_SEGMENTS FIFS
                   , GL_LEDGERS  L
                WHERE v.FLEX_VALUE               = gcc.segment1
                AND v.FLEX_VALUE_SET_ID          = s.flex_value_set_id
                AND FIFS.APPLICATION_ID          = 101
                AND FIFS.FLEX_VALUE_SET_ID       = S.FLEX_VALUE_SET_ID
                AND FIFS.APPLICATION_COLUMN_NAME = 'SEGMENT1'
                AND FIFS.ID_FLEX_CODE            = 'GL#'
                AND L.CHART_OF_ACCOUNTS_ID       = FIFS.ID_FLEX_NUM
                AND L.LEDGER_ID                  = bal.LEDGER_ID) Company_desc,
                  (SELECT v.description
                 --INTO  v_description1
                FROM  Fnd_Flex_Values_Vl v
                   , fnd_flex_value_sets S
                   , FND_ID_FLEX_SEGMENTS FIFS
                   , GL_LEDGERS  L
                WHERE v.FLEX_VALUE               = gcc.segment2
                AND v.FLEX_VALUE_SET_ID          = s.flex_value_set_id
                AND FIFS.APPLICATION_ID          = 101
                AND FIFS.FLEX_VALUE_SET_ID       = S.FLEX_VALUE_SET_ID
                AND FIFS.APPLICATION_COLUMN_NAME = 'SEGMENT2'
                AND FIFS.ID_FLEX_CODE            = 'GL#'
                AND L.CHART_OF_ACCOUNTS_ID       = FIFS.ID_FLEX_NUM
                AND L.LEDGER_ID                  = bal.LEDGER_ID) Account_Head,
                  (SELECT v.description
                 --INTO  v_description1
                FROM  Fnd_Flex_Values_Vl v
                   , fnd_flex_value_sets S
                   , FND_ID_FLEX_SEGMENTS FIFS
                   , GL_LEDGERS  L
                WHERE v.FLEX_VALUE               = gcc.segment5
                AND v.FLEX_VALUE_SET_ID          = s.flex_value_set_id
                AND FIFS.APPLICATION_ID          = 101
                AND FIFS.FLEX_VALUE_SET_ID       = S.FLEX_VALUE_SET_ID
                AND FIFS.APPLICATION_COLUMN_NAME = 'SEGMENT5'
                AND FIFS.ID_FLEX_CODE            = 'GL#'
                AND L.CHART_OF_ACCOUNTS_ID       = FIFS.ID_FLEX_NUM
                AND L.LEDGER_ID                  = bal.LEDGER_ID) Cost_center_desc,
 -- actual_flag,
/*  decode( account_type,         'A','Asset',
          'C','Budgetary (CR)', 'D','Budgetary (DR)',
          'E','Expense',        'L','Liability',
          'O','Owners equity',  'R','Revenue',
          account_type) ACCOUNT_TYPE,   -- */
  bal.period_name PERIOD_ABBR,
  gl.period_num,
  --last_day( to_date( period_name, 'MON-RR')) PERIOD_DATE,
  sum( decode( actual_flag, 'B', nvl( period_net_dr, 0) - nvl( period_net_cr, 0),0)) PERIOD_BUD_BAL ,
  sum( decode( actual_flag, 'A', nvl( period_net_dr, 0) - nvl( period_net_cr, 0),0)) PERIOD_ACTUAL_BAL,
  sum( decode( actual_flag, 'B', nvl( period_net_dr, 0) + nvl( project_to_date_dr, 0) - nvl( period_net_cr, 0),0)) YTD_BUD_BAL,
  NVL(SUM(decode( actual_flag, 'B',DECODE(BAL.PERIOD_NAME, :P_PERIOD,NVL(period_net_dr,0)))) +
  SUM(decode( actual_flag, 'B',DECODE(BAL.PERIOD_NAME, :P_PERIOD,NVL(project_to_date_dr,0)))) -
  SUM(decode( actual_flag, 'B',DECODE(BAL.PERIOD_NAME,  :P_PERIOD,NVL(period_net_cr,0) ))),0) YTD_BUDBAL,
 -- sum( decode( actual_flag, 'A', nvl( period_net_dr, 0) + nvl( project_to_date_dr, 0) - nvl( period_net_cr, 0),0)) YTD_ACTUAL_BAL,
  NVL(SUM(decode( actual_flag, 'A',DECODE(BAL.PERIOD_NAME, :FIRST_PERIOD,NVL(BEGIN_BALANCE_DR,0) - NVL(BEGIN_BALANCE_CR,0),0))) +
  SUM(decode( actual_flag, 'A',DECODE(BAL.PERIOD_NAME, :P_PERIOD,NVL(BEGIN_BALANCE_DR,0) + NVL(PERIOD_NET_DR,0),0))) -
  SUM(decode( actual_flag, 'A',DECODE(BAL.PERIOD_NAME,  :P_PERIOD,NVL(BEGIN_BALANCE_CR,0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME, :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR,0), 0))),0) YTD_ACT_BAL ,
 -- sysdate LOAD_DATE,
  bal.PERIOD_YEAR
FROM
   gl_balances bal,
   gl_encumbrance_types enc,
   gl_code_combinations gcc ,
--fnd_lookup_values_vl fnl,
 gl_periods gl
WHERE
   bal.encumbrance_type_id = enc.encumbrance_type_id(+) AND
   bal.code_combination_id = gcc.code_combination_id
 --AND fnl.LOOKUP_TYPE='DK_CCID_USER_ID_COMBINATION'
  -- AND  gcc.SEGMENT5=FNL.DESCRIPTION
  -- AND to_number(FNL.TAG) =:P_USER
  --  and gcc.code_combination_id =721845
    and bal.period_name not like 'ADJ%'
  -- AND bal.Period_Name = 'NOV-14-15'
  -- AND bal.Period_Name = :P_PERIOD
   and bal.CURRENCY_CODE='USD'
   and  bal.period_name=gl.period_name
 --  and gl.period_year='2015'
   and gl.period_type='21'
--  and segment1='11'
  AND gcc.segment2 like '6%'
  AND bal.LEDGER_ID = :GL_LEDGER_ID--1014172
 --and bal.period_name between :FIRST_PERIOD AND :P_PERIOD
  and gl.end_date <= (select end_date from gl_periods where period_name = :p_period)
  and gl.period_year = (select period_year from gl_periods where start_date = (select start_date from gl_periods where period_name = :first_period)
                    and period_type='21')
  AND gl.period_type='21'
  AND gcc.segment1 BETWEEN :p_frm_seg1 AND :p_to_seg1
  AND gcc.segment2 BETWEEN :p_frm_acc  AND :p_to_acc
  AND gcc.segment3 BETWEEN :p_frm_seg3 AND :p_to_seg3
  AND gcc.segment4 BETWEEN :p_frm_seg4 AND :p_to_seg4
  AND gcc.segment5 BETWEEN :p_frm_seg5 AND :p_to_seg5
  AND gcc.segment6  BETWEEN  :p_frm_seg6 AND :p_to_seg6
  AND gcc.segment7 BETWEEN :p_frm_seg7 AND :p_to_seg7
  AND gcc.segment8 BETWEEN :p_frm_seg8 AND :p_to_seg8
  AND gcc.segment9 BETWEEN :p_frm_seg9 AND :p_to_seg9
GROUP BY
   bal.code_combination_id,
   gcc.segment1,
   gcc.segment2,
   gcc.segment5,
   account_type,
   bal.LEDGER_ID,
   gl.period_num,
   bal.period_name,
   bal.period_year
order by gcc.segment1,gcc.segment5,gcc.segment2--,gcc.segment2

No comments:

Post a Comment

Clear BNE Cache for WebADI Changes

It Sometime happens that WebAdi Changes doesn't reflect once migrated in controlled instances. Here are the quick steps(Generally perfor...