[Home] [Help]
MATERIALIZED VIEW: APPS.BIL_OBI_OPTY_PG_MV
Source
SELECT
DECODE(GROUPING_ID(time.ent_year_id,time.ent_qtr_id,time.ent_period_id),
3,time.ent_year_id,5,time.ent_qtr_id,6,time.ent_period_id) effective_time_id
,op12.parent_sales_group_id parent_sales_group_id
,op12.sales_group_id sales_group_id
,NVL(op12.salesrep_id,0) salesrep_id
,op12.product_category_id
,op12.item_id
,decode(nvl(op12.salesrep_id,0),0,'Y','N') rollup_flag
,SUM(new_opty_amt) new_opty_amt
,SUM(new_opty_amt_s) new_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(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_amt_xcnt
,COUNT(cnv_opty_amt_s) cnv_opty_amt_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
,time.ent_year_id
,time.ent_qtr_id
,time.ent_period_id
,GROUPING_ID(op12.parent_sales_group_id, op12.sales_group_id, NVL(op12.salesrep_id,0), op12.product_category_id, op12.item_id
,time.ent_year_id,time.ent_qtr_id,time.ent_period_id) xgrp
FROM
APPS.BIL_BI_OP12_PG_MV op12,
FII.FII_TIME_DAY time
WHERE
time.report_date_julian = op12.effective_time_id
GROUP BY
op12.parent_sales_group_id, op12.sales_group_id
,NVL(op12.salesrep_id,0)
,op12.product_category_id
,op12.item_id
,GROUPING SETS(time.ent_year_id,time.ent_qtr_id,time.ent_period_id)