MATERIALIZED VIEW: APPS.BIL_BI_PIPE_G_MV

Source

(
SELECT
  		 grp_total_flag,
        		fact.parent_sales_group_id,
  		fact.sales_group_id sales_group_id,
  		fact.salesrep_id,
  		fact.snap_date,
  		fact.product_category_id,
  		fact.item_id,
  		SUM(fact.pipeline_amt_day) pipeline_amt_day,
  		SUM(fact.pipeline_amt_week) pipeline_amt_week,
  		SUM(fact.pipeline_amt_period) pipeline_amt_period,
  		SUM(fact.pipeline_amt_quarter) pipeline_amt_quarter,
  		SUM(fact.pipeline_amt_year) pipeline_amt_year,
		
		SUM(fact.wtd_pipeline_amt_day) wtd_pipeline_amt_day,
  		SUM(fact.wtd_pipeline_amt_week) wtd_pipeline_amt_week,
  		SUM(fact.wtd_pipeline_amt_period) wtd_pipeline_amt_period,
  		SUM(fact.wtd_pipeline_amt_quarter) wtd_pipeline_amt_quarter,
  		SUM(fact.wtd_pipeline_amt_year) wtd_pipeline_amt_year,

  		SUM(fact.open_amt_day) open_amt_day,
  		SUM(fact.open_amt_week) open_amt_week,
  		SUM(fact.open_amt_period) open_amt_period,
  		SUM(fact.open_amt_quarter) open_amt_quarter,
  		SUM(fact.open_amt_year) open_amt_year,
  		SUM(fact.pipeline_amt_day_s) pipeline_amt_day_s,
  		SUM(fact.pipeline_amt_week_s) pipeline_amt_week_s,
  		SUM(fact.pipeline_amt_period_s) pipeline_amt_period_s,
  		SUM(fact.pipeline_amt_quarter_s) pipeline_amt_quarter_s,
  		SUM(fact.pipeline_amt_year_s) pipeline_amt_year_s,
		
		SUM(fact.wtd_pipeline_amt_day_s) wtd_pipeline_amt_day_s,
  		SUM(fact.wtd_pipeline_amt_week_s) wtd_pipeline_amt_week_s,
  		SUM(fact.wtd_pipeline_amt_period_s) wtd_pipeline_amt_period_s,
  		SUM(fact.wtd_pipeline_amt_quarter_s) wtd_pipeline_amt_quarter_s,
  		SUM(fact.wtd_pipeline_amt_year_s) wtd_pipeline_amt_year_s,
  		
  		SUM(fact.open_amt_day_s) open_amt_day_s,
  		SUM(fact.open_amt_week_s) open_amt_week_s,
  		SUM(fact.open_amt_period_s) open_amt_period_s,
  		SUM(fact.open_amt_quarter_s) open_amt_quarter_s,
  		SUM(fact.open_amt_year_s) open_amt_year_s,
  		COUNT(PIPELINE_AMT_DAY) PIPELINE_AMT_DAY_XCNT,
  		COUNT(PIPELINE_AMT_WEEK) PIPELINE_AMT_WEEK_XCNT,
  		COUNT(PIPELINE_AMT_PERIOD) PIPELINE_AMT_PERIOD_XCNT ,
  		COUNT(PIPELINE_AMT_Quarter) PIPELINE_AMT_Quarter_XCNT,
  		COUNT(PIPELINE_AMT_YEAR) PIPELINE_AMT_YEAR_XCNT,
		COUNT(WTD_PIPELINE_AMT_DAY) WTD_PIPELINE_AMT_DAY_XCNT,
  		COUNT(WTD_PIPELINE_AMT_WEEK) WTD_PIPELINE_AMT_WEEK_XCNT,
  		COUNT(WTD_PIPELINE_AMT_PERIOD) WTD_PIPELINE_AMT_PERIOD_XCNT ,
  		COUNT(WTD_PIPELINE_AMT_Quarter) WTD_PIPELINE_AMT_Quarter_XCNT,
  		COUNT(WTD_PIPELINE_AMT_YEAR) WTD_PIPELINE_AMT_YEAR_XCNT,
  		
  		COUNT(OPEN_AMT_DAY) OPEN_AMT_DAY_XCNT,
  		COUNT(OPEN_AMT_WEEK) OPEN_AMT_WEEK_XCNT ,
  		COUNT(OPEN_AMT_PERIOD) OPEN_AMT_PERIOD_XCNt ,
  		COUNT(OPEN_AMT_Quarter) OPEN_AMT_Quarter_XCNT,
  		COUNT(OPEN_AMT_YEAR) OPEN_AMT_YEAR_XCNT,
  		COUNT(PIPELINE_AMT_DAY_S) PIPELINE_AMT_DAY_XCNT_S,
  		COUNT(PIPELINE_AMT_WEEK_S) PIPELINE_AMT_WEEK_XCNT_S,
  		COUNT(PIPELINE_AMT_PERIOD_S) PIPELINE_AMT_PERIOD_XCNT_S ,
  		COUNT(PIPELINE_AMT_Quarter_S) PIPELINE_AMT_Quarter_XCNT_S,
  		COUNT(PIPELINE_AMT_YEAR_S) PIPELINE_AMT_YEAR_XCNT_S,
		
		COUNT(WTD_PIPELINE_AMT_DAY_S) WTD_PIPELINE_AMT_DAY_XCNT_S,
  		COUNT(WTD_PIPELINE_AMT_WEEK_S) WTD_PIPELINE_AMT_WEEK_XCNT_S,
  		COUNT(WTD_PIPELINE_AMT_PERIOD_S) WTD_PIPELINE_AMT_PERIOD_XCNT_S ,
  		COUNT(WTD_PIPELINE_AMT_Quarter_S) WTD_PIPELINE_AMT_QTR_XCNT_S,
  		COUNT(WTD_PIPELINE_AMT_YEAR_S) WTD_PIPELINE_AMT_YEAR_XCNT_S,
  		
  		COUNT(OPEN_AMT_DAY_S) OPEN_AMT_DAY_XCNT_S,
  		COUNT(OPEN_AMT_WEEK_S) OPEN_AMT_WEEK_XCNT_S ,
  		COUNT(OPEN_AMT_PERIOD_S) OPEN_AMT_PERIOD_XCNt_S ,
  		COUNT(OPEN_AMT_Quarter_S) OPEN_AMT_Quarter_XCNT_S,
  		COUNT(OPEN_AMT_YEAR_S) OPEN_AMT_YEAR_XCNT_S,
    		grouping_id( fact.sales_group_id, fact.parent_sales_group_id, fact.salesrep_id, 
  	     	 SNAP_DATE, product_category_id,item_id) groupid,
  		COUNT(*)                        xcnt
  	FROM bil_bi_pipe_mv fact
   	group by
             fact.sales_group_id,fact.salesrep_id, fact.parent_sales_group_id,
  	      	SNAP_DATE, product_category_id,item_id
          		, grp_total_flag

)