[Home] [Help]
MATERIALIZED VIEW: APPS.BIL_BI_OPLPC_GC_MV
Source
SELECT /* 12.0: bug#4526784 */
'SLSGRP' umarker,
DECODE(GROUPING_ID(
ent_year_id, ent_qtr_id,
ent_period_id, week_id,
report_date_julian),
15, 128,
23, 64,
27, 32,
29, 16,
30, 1) effective_period_type_id,
DECODE(GROUPING_ID(
ent_year_id, ent_qtr_id,
ent_period_id, week_id,
report_date_julian),
15, ent_year_id,
23, ent_qtr_id,
27, ent_period_id,
29, week_id,
30, report_date_julian) effective_time_id,
ent_year_id,
ent_qtr_id,
ent_period_id,
week_id,
report_date_julian,
denorm.parent_group_id sales_group_id,
NULL salesrep_id,
product_category_id,
source_code_id,
parent_source_code_id,
top_node_flag,
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,
GROUPING_ID(denorm.parent_group_id,source_code_id,parent_source_code_id,top_node_flag,leaf_node_flag,
ent_year_id,ent_qtr_id, ent_period_id,week_id, report_date_julian,product_category_id) grouping_ident
FROM
BIL_BI_OPLPC_G_MV fact,
BIL_BI_RS_GRP_MV denorm
WHERE
denorm.sales_group_id = fact.sales_group_id
GROUP BY
denorm.parent_group_id,
product_category_id,
source_code_id,
parent_source_code_id,
top_node_flag,
leaf_node_flag,
GROUPING SETS ( ent_year_id, ent_qtr_id, ent_period_id,week_id, report_date_julian)
UNION ALL
SELECT
'SLSREP' umarker,
DECODE(GROUPING_ID(
ent_year_id, ent_qtr_id,
ent_period_id, week_id,
report_date_julian),
15, 128,
23, 64,
27, 32,
29, 16,
30, 1) effective_period_type_id,
DECODE(GROUPING_ID(
ent_year_id, ent_qtr_id,
ent_period_id, week_id,
report_date_julian),
15, ent_year_id,
23, ent_qtr_id,
27, ent_period_id,
29, week_id,
30, report_date_julian) effective_time_id,
ent_year_id,
ent_qtr_id,
ent_period_id,
week_id,
report_date_julian,
fact.sales_group_id,
fact.salesrep_id,
product_category_id,
source_code_id,
parent_source_code_id,
top_node_flag,
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,
GROUPING_ID(fact.sales_group_id,fact.salesrep_id,source_code_id,parent_source_code_id,top_node_flag,
leaf_node_flag,ent_year_id,ent_qtr_id, ent_period_id,week_id,report_date_julian,
product_category_id) grouping_ident
FROM
BIL_BI_OPLPC_G_MV fact
GROUP BY
fact.sales_group_id,
fact.salesrep_id,
product_category_id,
source_code_id,
parent_source_code_id,
top_node_flag,
leaf_node_flag,
GROUPING SETS ( ent_year_id, ent_qtr_id, ent_period_id,week_id, report_date_julian)