MATERIALIZED VIEW: APPS.BIL_BI_OPLPC_G_MV

Source

SELECT
      ent_year_id,
      ent_qtr_id,
      ent_period_id,
      week_id,
      report_date_julian,
      sales_group_id sales_group_id,
      salesrep_id,
      product_category_id,
      denorm.parent_source_code_id source_code_id,
      denorm.prior_id parent_source_code_id,
      denorm.top_node_flag,
      denorm.leaf_node_flag,
      SUM(new_leads_cnt) new_leads_cnt,
      SUM(cnv_leads_cnt) cnv_leads_cnt,
      SUM(dead_leads_cnt)  dead_leads_cnt,
      SUM(closed_leads_cnt) closed_leads_cnt,
      SUM(new_opty_amt) new_opty_amt,
      SUM(cnv_opty_amt) cnv_opty_amt,
      SUM(won_opty_amt) won_opty_amt,
      SUM(lost_opty_amt) lost_opty_amt,
      SUM(no_opty_amt) no_opty_amt,
      SUM(new_opty_amt_s) new_opty_amt_s,
      SUM(cnv_opty_amt_s) cnv_opty_amt_s,
      SUM(won_opty_amt_s) won_opty_amt_s,
      SUM(lost_opty_amt_s) lost_opty_amt_s,
      SUM(no_opty_amt_s) no_opty_amt_s,
      COUNT(new_leads_cnt) new_leads_xcnt,
      COUNT(cnv_leads_cnt) cnv_leads_xcnt,
      COUNT(dead_leads_cnt)  dead_leads_xcnt,
      COUNT(closed_leads_cnt) closed_leads_xcnt,
      COUNT(new_opty_amt)  new_opty_xcnt,
      COUNT(cnv_opty_amt)  cnv_opty_xcnt,
      COUNT(won_opty_amt)  won_opty_xcnt,
      COUNT(lost_opty_amt) lost_opty_xcnt,
      COUNT(no_opty_amt)   no_opty_amt_xcnt,
      COUNT(new_opty_amt_s) new_opty_xcnt_s,
      COUNT(cnv_opty_amt_s) cnv_opty_xcnt_s,
      COUNT(won_opty_amt_s) won_opty_xcnt_s,
      COUNT(lost_opty_amt_s) lost_opty_xcnt_s,
      COUNT(no_opty_amt_s) no_opty_amt_xcnt_s,
      COUNT(*) xcnt
    FROM
      bil_bi_oplpc_mv fact,
      BIM.BIM_I_SOURCE_DENORM denorm,
      FII.FII_TIME_DAY time
    WHERE
      fact.source_code_id = denorm.source_code_id
      AND time.report_date_julian  = fact.effective_time_id
    GROUP BY
      sales_group_id,
      salesrep_id,
      product_category_id,
      denorm.parent_source_code_id,
      denorm.prior_id,
      denorm.top_node_flag,
      denorm.leaf_node_flag,
      ent_year_id,
      ent_qtr_id,
      ent_period_id,
      week_id,
      report_date_julian