MATERIALIZED VIEW: APPS.BIL_BI_OP12_PG_MV

Source

SELECT
  'SLSREP' umarker
  ,op11.effective_time_id effective_time_id
  ,op11.sales_group_id parent_sales_group_id
  ,op11.sales_group_id sales_group_id
  ,op11.salesrep_id salesrep_id
  ,op11.product_category_id
  ,op11.item_id
  ,SUM(new_opty_amt) new_opty_amt
  ,SUM(new_opty_amt_s) new_opty_amt_s
  ,SUM(latest_open_opty_amt) latest_open_opty_amt
  ,SUM(latest_open_opty_amt_s) latest_open_opty_amt_s
  ,SUM(won_opty_amt) won_opty_amt
  ,SUM(won_opty_amt_s) won_opty_amt_s
  ,SUM(lost_opty_amt) lost_opty_amt
  ,SUM(lost_opty_amt_s) lost_opty_amt_s
  ,SUM(no_opty_amt) no_opty_amt
  ,SUM(no_opty_amt_s) no_opty_amt_s
  ,SUM(cnv_opty_amt) cnv_opty_amt
  ,SUM(cnv_opty_amt_s) cnv_opty_amt_s
  ,SUM(nfp_yr) nfp_yr
  ,SUM(nfp_yr_s) nfp_yr_s
  ,SUM(nfp_qtr) nfp_qtr
  ,SUM(nfp_qtr_s) nfp_qtr_s
  ,SUM(nfp_per) nfp_per
  ,SUM(nfp_per_s) nfp_per_s
  ,SUM(nfp_wk) nfp_wk
  ,SUM(nfp_wk_s) nfp_wk_s
  ,COUNT(new_opty_amt) new_opty_xcnt
  ,COUNT(new_opty_amt_s) new_opty_xcnt_s
  ,COUNT(latest_open_opty_amt) latest_open_opty_xcnt
  ,COUNT(latest_open_opty_amt_s) latest_open_opty_xcnt_s
  ,COUNT(won_opty_amt) won_opty_xcnt
  ,COUNT(won_opty_amt_s) won_opty_xcnt_s
  ,COUNT(lost_opty_amt) lost_opty_xcnt
  ,COUNT(lost_opty_amt_s) lost_opty_xcnt_s
  ,COUNT(no_opty_amt) no_opty_xcnt
  ,COUNT(no_opty_amt_s) no_opty_xcnt_s
  ,COUNT(cnv_opty_amt) cnv_opty_xcnt
  ,COUNT(cnv_opty_amt_s) cnv_opty_xcnt_s
  ,COUNT(nfp_yr) nfp_yr_xcnt
  ,COUNT(nfp_yr_s) nfp_yr_xcnt_s
  ,COUNT(nfp_qtr) nfp_qtr_xcnt
  ,COUNT(nfp_qtr_s) nfp_qtr_xcnt_s
  ,COUNT(nfp_per) nfp_per_xcnt
  ,COUNT(nfp_per_s) nfp_per_xcnt_s
  ,COUNT(nfp_wk) nfp_wk_xcnt
  ,COUNT(nfp_wk_s) nfp_wk_xcnt_s
  ,COUNT(*) all_xcnt
FROM
  BIL_BI_OP11_PG_MV op11
GROUP BY
  op11.effective_time_id
  ,op11.sales_group_id
  ,op11.salesrep_id
  ,op11.product_category_id
  ,op11.item_id
UNION ALL
SELECT
  'SLSGRP' umarker
  ,op11.effective_time_id effective_time_id
  ,prnt.parent_parent_group_id parent_sales_group_id
  ,prnt.parent_group_id sales_group_id
  ,NULL salesrep_id
  ,op11.product_category_id
  ,op11.item_id
  ,SUM(new_opty_amt) new_opty_amt
  ,SUM(new_opty_amt_s) new_opty_amt_s
  ,SUM(latest_open_opty_amt) latest_open_opty_amt
  ,SUM(latest_open_opty_amt_s) latest_open_opty_amt_s
  ,SUM(won_opty_amt) won_opty_amt
  ,SUM(won_opty_amt_s) won_opty_amt_s
  ,SUM(lost_opty_amt) lost_opty_amt
  ,SUM(lost_opty_amt_s) lost_opty_amt_s
  ,SUM(no_opty_amt) no_opty_amt
  ,SUM(no_opty_amt_s) no_opty_amt_s
  ,SUM(cnv_opty_amt) cnv_opty_amt
  ,SUM(cnv_opty_amt_s) cnv_opty_amt_s
  ,SUM(nfp_yr) nfp_yr
  ,SUM(nfp_yr_s) nfp_yr_s
  ,SUM(nfp_qtr) nfp_qtr
  ,SUM(nfp_qtr_s) nfp_qtr_s
  ,SUM(nfp_per) nfp_per
  ,SUM(nfp_per_s) nfp_per_s
  ,SUM(nfp_wk) nfp_wk
  ,SUM(nfp_wk_s) nfp_wk_s
  ,COUNT(new_opty_amt) new_opty_xcnt
  ,COUNT(new_opty_amt_s) new_opty_xcnt_s
  ,COUNT(latest_open_opty_amt) latest_open_opty_xcnt
  ,COUNT(latest_open_opty_amt_s) latest_open_opty_xcnt_s
  ,COUNT(won_opty_amt) won_opty_xcnt
  ,COUNT(won_opty_amt_s) won_opty_xcnt_s
  ,COUNT(lost_opty_amt) lost_opty_xcnt
  ,COUNT(lost_opty_amt_s) lost_opty_xcnt_s
  ,COUNT(no_opty_amt) no_opty_xcnt
  ,COUNT(no_opty_amt_s) no_opty_xcnt_s
  ,COUNT(cnv_opty_amt) cnv_opty_xcnt
  ,COUNT(cnv_opty_amt_s) cnv_opty_xcnt_s
  ,COUNT(nfp_yr) nfp_yr_xcnt
  ,COUNT(nfp_yr_s) nfp_yr_xcnt_s
  ,COUNT(nfp_qtr) nfp_qtr_xcnt
  ,COUNT(nfp_qtr_s) nfp_qtr_xcnt_s
  ,COUNT(nfp_per) nfp_per_xcnt
  ,COUNT(nfp_per_s) nfp_per_xcnt_s
  ,COUNT(nfp_wk) nfp_wk_xcnt
  ,COUNT(nfp_wk_s) nfp_wk_xcnt_s
  ,COUNT(*) all_xcnt
FROM
  BIL_BI_OP11_PG_MV op11,
  bil_bi_RS_grp_mv prnt
WHERE
  op11.sales_group_id = prnt.sales_group_id
GROUP BY
  op11.effective_time_id
  ,prnt.parent_parent_group_id
  ,prnt.parent_group_id
  ,op11.product_category_id
  ,op11.item_id