[Home] [Help]
MATERIALIZED VIEW: APPS.BIL_BI_OPLDC_G_MV
Source
SELECT
ent_year_id,
ent_qtr_id,
ent_period_id,
week_id,
report_date_julian,
sales_group_id sales_group_id,
salesrep_id,
denorm.parent_source_code_id source_code_id,
denorm.prior_id parent_source_code_id,
denorm.top_node_flag,
denorm.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
FROM
bil_bi_opldc_mv fact,
BIM.BIM_I_SOURCE_DENORM denorm,
FII.FII_TIME_DAY time
WHERE
fact.source_code_id = denorm.source_code_id
AND time.report_date_julian = fact.effective_time_id
GROUP BY
sales_group_id,
salesrep_id,
denorm.parent_source_code_id,
denorm.prior_id,
denorm.top_node_flag,
denorm.leaf_node_flag,
ent_year_id,
ent_qtr_id,
ent_period_id,
week_id,
report_date_julian