MATERIALIZED VIEW: APPS.BIM_I_BGT_LVL_MV

Source

SELECT /* Dummy Comment */ (CASE
      WHEN GROUPING(b.report_date_julian)=0 Then b.report_date_julian
      WHEN GROUPING(b.week_id)=0 Then b.week_id
      WHEN GROUPING(b.ent_period_id)=0 Then b.ent_period_id
      WHEN GROUPING(b.ent_qtr_id)=0    Then b.ent_qtr_id
      ELSE b.ent_year_id
      END
     ) time_id
     ,(CASE
      WHEN GROUPING(b.report_date_julian)=0 Then 1
      WHEN GROUPING(b.week_id)=0 Then 16
      WHEN GROUPING(b.ent_period_id)=0 Then 32
      WHEN GROUPING(b.ent_qtr_id)=0    Then 64
      ELSE 128
      END
     ) period_type_id
     ,a.fund_id fund_id
     ,a.fund_type fund_type
     ,a.parent_fund_id parent_fund_id
     ,a.category_id category_id
     ,a.leaf_node_flag leaf_node_flag
     ,sum(a.original_budget) original_budget
     ,count(a.original_budget) original_budget_c
     ,sum(a.holdback) holdback
     ,count(a.holdback) holdback_c
     ,sum(a.transfer_in) transfer_in
     ,count(a.transfer_in) transfer_in_c
     ,sum(a.transfer_out) transfer_out
     ,count(a.transfer_out) transfer_out_c
     ,sum(a.accrual) accrual
     ,count(a.accrual) accrual_c
     ,sum(a.committed) committed
     ,count(a.committed) committed_c
     ,sum(a.planned) planned
     ,count(a.planned) planned_c
     ,sum(a.utilized) utilized
     ,count(a.utilized) utilized_c
     ,sum(a.original_budget_s) original_budget_s
      ,count(a.original_budget_s) original_budget_s_c
      ,sum(holdback_s) holdback_s
      ,count(holdback_s) holdback_s_c
      ,sum(a.transfer_in_s) transfer_in_s
      ,count(a.transfer_in_s) transfer_in_s_c
      ,sum(a.transfer_out_s) transfer_out_s
      ,count(a.transfer_out_s) transfer_out_s_c
      ,sum(a.accrual_s) accrual_s
      ,count(a.accrual_s) accrual_s_c
      ,sum(a.committed_s) committed_s
      ,count(a.committed_s) committed_s_c
      ,sum(a.planned_s) planned_s
      ,count(a.planned_s) planned_s_c
      ,sum(a.utilized_s) utilized_s
      ,count(a.utilized_s) utilized_s_c
     ,count(*) count_all
     ,b.report_date_julian 	dummy1_fr
     ,b.week_id 		dummy2_fr
     ,b.ent_period_id 		dummy3_fr
     ,b.ent_qtr_id 		dummy4_fr
     ,b.ent_year_id 		dummy5_fr
     ,GROUPING_ID(b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id,
                 a.fund_id,a.fund_type,a.parent_fund_id,a.category_id,a.leaf_node_flag ) dummy6_fr
FROM BIM_I_BGT_FCT_MV a,
     FII.FII_TIME_DAY b
WHERE b.report_Date=a.transaction_create_date
GROUP BY GROUPING SETS (b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id),
     a.fund_id,
     a.fund_type,
     a.parent_fund_id,a.category_id,a.leaf_node_flag