[Home] [Help]
MATERIALIZED VIEW: APPS.BIL_BI_OPTY_PG_MV
Source
SELECT
DECODE(GROUPING_ID(time.ent_year_id,time.ent_qtr_id,time.ent_period_id,time.week_id,time.report_date_julian),
15,time.ent_year_id,23,time.ent_qtr_id,27,time.ent_period_id,29,time.week_id,30,time.report_date_julian)
effective_time_id
,DECODE(GROUPING_ID(time.ent_year_id,time.ent_qtr_id,time.ent_period_id,time.week_id,time.report_date_julian),
15,128,23,64,27,32,29,16,30,1) effective_period_type_id
,op12.parent_sales_group_id
,op12.sales_group_id sales_group_id
,op12.salesrep_id salesrep_id
,op12.product_category_id
,op12.item_id
,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
,time.report_date_julian
,time.week_id
,GROUPING_ID(parent_sales_group_id,sales_group_id,salesrep_id,product_category_id,item_id,
time.ent_year_id,time.ent_qtr_id,time.ent_period_id,time.week_id,time.report_date_julian) xgrp
FROM
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
,op12.salesrep_id
,op12.product_category_id
,op12.item_id
,GROUPING SETS(time.ent_year_id,time.ent_qtr_id,time.ent_period_id,time.week_id,time.report_date_julian)