[Home] [Help]
MATERIALIZED VIEW: APPS.BIL_OBI_PIPE_G_MV
Source
SELECT
'PIPEPER' UMARKER,
fact.parent_sales_group_id,
fact.sales_group_id sales_group_id,
NVL(fact.salesrep_id,0) salesrep_id,
time.ent_period_id effective_time_id,
product_category_id,
item_id,
DECODE(NVL(fact.salesrep_id,0),0,'Y','N') ROLLUP_FLAG,
SUM(DECODE(snap_date, time.ent_period_end_date, fact.pipeline_amt_period)) pipeline_amt,
SUM(DECODE(snap_date, time.ent_period_end_date,fact.wtd_pipeline_amt_period)) wtd_pipeline_amt,
SUM(DECODE(snap_date, time.ent_period_end_date,fact.open_amt_period)) open_amt,
SUM(DECODE(snap_date, DECODE(time1.report_date, time1.ent_period_end_date, null, time1.week_end_date),fact.open_amt_period)) period_open_amt,
SUM(DECODE(snap_date, time.ent_period_end_date,fact.pipeline_amt_period_s)) pipeline_amt_s,
SUM(DECODE(snap_date, time.ent_period_end_date,fact.wtd_pipeline_amt_period_s)) wtd_pipeline_amt_s,
SUM(DECODE(snap_date, time.ent_period_end_date,fact.open_amt_period_s)) open_amt_s,
SUM(DECODE(snap_date, DECODE(time1.report_date, time1.ent_period_end_date, null, time1.week_end_date),fact.open_amt_period_s)) period_open_amt_s,
COUNT(DECODE(snap_date, time.ent_period_end_date, fact.pipeline_amt_period)) PIPELINE_AMT_XCNT ,
COUNT(DECODE(snap_date, time.ent_period_end_date,fact.wtd_pipeline_amt_period)) WTD_PIPELINE_AMT_XCNT ,
COUNT(DECODE(snap_date, time.ent_period_end_date,fact.open_amt_period)) OPEN_AMT_XCNT ,
COUNT(DECODE(snap_date, DECODE(time1.report_date, time1.ent_period_end_date, null, time1.week_end_date),fact.open_amt_period)) PERIOD_OPEN_AMT_XCNT,
COUNT(DECODE(snap_date, time.ent_period_end_date,fact.pipeline_amt_period_s)) PIPELINE_AMT_XCNT_S,
COUNT(DECODE(snap_date, time.ent_period_end_date,fact.wtd_pipeline_amt_period_s)) WTD_PIPELINE_AMT_XCNT_S ,
COUNT(DECODE(snap_date, time.ent_period_end_date,fact.open_amt_period_s)) OPEN_AMT_XCNT_S ,
COUNT(DECODE(snap_date, DECODE(time1.report_date, time1.ent_period_end_date, null, time1.week_end_date),fact.open_amt_period_s)) PERIOD_OPEN_AMT_XCNT_S ,
COUNT(*) xcnt
FROM APPS.BIL_BI_PIPE_MV fact, FII.FII_TIME_DAY time, FII.FII_TIME_DAY time1
WHERE (time1.report_date = time1.ent_period_end_date or time1.report_date = time1.ent_period_start_date)
AND snap_date = DECODE(time1.report_date, time1.ent_period_end_date, time1.ent_period_end_date, time1.week_end_date)
AND time.report_date = DECODE(time1.report_date, time1.ent_period_end_date, time1.ent_period_end_date, time1.week_end_date)
AND grp_total_flag = 0
GROUP BY fact.sales_group_id,NVL(fact.salesrep_id,0), fact.parent_sales_group_id,
time.ent_period_id, product_category_id,item_id
UNION ALL
SELECT
'PIPEQTR' UMARKER,
fact.parent_sales_group_id,
fact.sales_group_id sales_group_id,
NVL(fact.salesrep_id,0) salesrep_id,
time.ent_qtr_id effective_time_id,
product_category_id,
item_id,
DECODE(NVL(fact.salesrep_id,0),0,'Y','N') ROLLUP_FLAG,
SUM(DECODE(snap_date, time.ent_qtr_end_date, fact.pipeline_amt_quarter)) pipeline_amt,
SUM(DECODE(snap_date, time.ent_qtr_end_date,fact.wtd_pipeline_amt_quarter)) wtd_pipeline_amt,
SUM(DECODE(snap_date, time.ent_qtr_end_date,fact.open_amt_quarter)) open_amt,
SUM(DECODE(snap_date, DECODE(time1.report_date, time1.ent_qtr_end_date, null, time1.week_end_date),fact.open_amt_quarter)) period_open_amt,
SUM(DECODE(snap_date, time.ent_qtr_end_date,fact.pipeline_amt_quarter_s)) pipeline_amt_s,
SUM(DECODE(snap_date, time.ent_qtr_end_date,fact.wtd_pipeline_amt_quarter_s)) wtd_pipeline_amt_s,
SUM(DECODE(snap_date, time.ent_qtr_end_date,fact.open_amt_quarter_s)) open_amt_s,
SUM(DECODE(snap_date, DECODE(time1.report_date, time1.ent_qtr_end_date, null, time1.week_end_date),fact.open_amt_quarter_s)) period_open_amt_s,
COUNT(DECODE(snap_date, time.ent_qtr_end_date, fact.pipeline_amt_quarter)) PIPELINE_AMT_XCNT ,
COUNT(DECODE(snap_date, time.ent_qtr_end_date,fact.wtd_pipeline_amt_quarter)) WTD_PIPELINE_AMT_XCNT ,
COUNT(DECODE(snap_date, time.ent_qtr_end_date,fact.open_amt_quarter)) OPEN_AMT_XCNT ,
COUNT(DECODE(snap_date, DECODE(time1.report_date, time1.ent_qtr_end_date, null, time1.week_end_date),fact.open_amt_quarter)) PERIOD_OPEN_AMT_XCNT,
COUNT(DECODE(snap_date, time.ent_qtr_end_date,fact.pipeline_amt_quarter_s)) PIPELINE_AMT_XCNT_S,
COUNT(DECODE(snap_date, time.ent_qtr_end_date,fact.wtd_pipeline_amt_quarter_s)) WTD_PIPELINE_AMT_XCNT_S ,
COUNT(DECODE(snap_date, time.ent_qtr_end_date,fact.open_amt_quarter_s)) OPEN_AMT_XCNT_S ,
COUNT(DECODE(snap_date, DECODE(time1.report_date, time1.ent_qtr_end_date, null, time1.week_end_date),fact.open_amt_quarter_s)) PERIOD_OPEN_AMT_XCNT_S ,
COUNT(*) xcnt
FROM APPS.BIL_BI_PIPE_MV fact, FII.FII_TIME_DAY time, FII.FII_TIME_DAY time1
WHERE (time1.report_date = time1.ent_qtr_end_date or time1.report_date = time1.ent_qtr_start_date)
AND snap_date = DECODE(time1.report_date, time1.ent_qtr_end_date, time1.ent_qtr_end_date, time1.week_end_date)
AND time.report_date = DECODE(time1.report_date, time1.ent_qtr_end_date, time1.ent_qtr_end_date, time1.week_end_date)
AND grp_total_flag = 0
GROUP BY fact.sales_group_id,NVL(fact.salesrep_id,0),fact.parent_sales_group_id,
time.ent_qtr_id, product_category_id,item_id
UNION ALL
SELECT
'PIPEYR' UMARKER,
fact.parent_sales_group_id,
fact.sales_group_id sales_group_id,
NVL(fact.salesrep_id,0) salesrep_id,
time.ent_year_id effective_time_id,
product_category_id,
item_id,
DECODE(NVL(fact.salesrep_id,0),0,'Y','N') ROLLUP_FLAG,
SUM(DECODE(snap_date, time.ent_year_end_date, fact.pipeline_amt_year)) pipeline_amt,
SUM(DECODE(snap_date, time.ent_year_end_date,fact.wtd_pipeline_amt_year)) wtd_pipeline_amt,
SUM(DECODE(snap_date, time.ent_year_end_date,fact.open_amt_year)) open_amt,
SUM(DECODE(snap_date, DECODE(time1.report_date, time1.ent_year_end_date, null, time1.week_end_date),fact.open_amt_year)) period_open_amt,
SUM(DECODE(snap_date, time.ent_year_end_date,fact.pipeline_amt_year_s)) pipeline_amt_s,
SUM(DECODE(snap_date, time.ent_year_end_date,fact.wtd_pipeline_amt_year_s)) wtd_pipeline_amt_s,
SUM(DECODE(snap_date, time.ent_year_end_date,fact.open_amt_year_s)) open_amt_s,
SUM(DECODE(snap_date, DECODE(time1.report_date, time1.ent_year_end_date, null, time1.week_end_date),fact.open_amt_year_s)) period_open_amt_s,
COUNT(DECODE(snap_date, time.ent_year_end_date, fact.pipeline_amt_year)) PIPELINE_AMT_XCNT ,
COUNT(DECODE(snap_date, time.ent_year_end_date,fact.wtd_pipeline_amt_year)) WTD_PIPELINE_AMT_XCNT ,
COUNT(DECODE(snap_date, time.ent_year_end_date,fact.open_amt_year)) OPEN_AMT_XCNT ,
COUNT(DECODE(snap_date, DECODE(time1.report_date, time1.ent_year_end_date, null, time1.week_end_date),fact.open_amt_year)) PERIOD_OPEN_AMT_XCNT,
COUNT(DECODE(snap_date, time.ent_year_end_date,fact.pipeline_amt_year_s)) PIPELINE_AMT_XCNT_S,
COUNT(DECODE(snap_date, time.ent_year_end_date,fact.wtd_pipeline_amt_year_s)) WTD_PIPELINE_AMT_XCNT_S,
COUNT(DECODE(snap_date, time.ent_year_end_date,fact.open_amt_year_s)) OPEN_AMT_XCNT_S ,
COUNT(DECODE(snap_date, DECODE(time1.report_date, time1.ent_year_end_date, null, time1.week_end_date),fact.open_amt_year_s)) PERIOD_OPEN_AMT_XCNT_S,
COUNT(*) xcnt
FROM APPS.BIL_BI_PIPE_MV fact, FII.FII_TIME_DAY time, FII.FII_TIME_DAY time1
WHERE (time1.report_date = time1.ent_year_end_date or time1.report_date = time1.ent_year_start_date)
AND snap_date = DECODE(time1.report_date, time1.ent_year_end_date, time1.ent_year_end_date, time1.week_end_date)
AND time.report_date = DECODE(time1.report_date, time1.ent_year_end_date, time1.ent_year_end_date, time1.week_end_date)
AND grp_total_flag = 0
GROUP BY fact.sales_group_id,NVL(fact.salesrep_id,0),fact.parent_sales_group_id,
time.ent_year_id, product_category_id,item_id