SELECT /* bug4654692 */
fact.opty_creation_time_id,
fact.opty_close_time_id,
fact.opty_ld_conversion_time_id,
fact.sales_group_id,
fact.salesrep_id,
(CASE WHEN fact.item_id=-1 THEN fact.product_category_id ELSE star.vbh_category_id END) product_category_id,
(CASE WHEN fact.item_id=-1 THEN -1 ELSE NULL END) item_id,
fact.campaign_object_id,
fact.campaign_object_type,
fact.child_campaign_object_id,
fact.child_campaign_object_type,
fact.opty_id,
fact.competitor_id,
fact.hdr_source_promotion_id,
fact.open_status_flag,
fact.win_loss_indicator,
fact.forecast_rollup_flag,
SUM(sales_credit_amt) sales_credit_amt,
COUNT(sales_credit_amt) sales_credit_amt_xcnt,
SUM(sales_credit_amt_s) sales_credit_amt_s,
COUNT(sales_credit_amt_s) sales_credit_amt_xcnt_s,
SUM(sales_credit_amt) new_opty_amt,
SUM(sales_credit_amt_s) new_opty_amt_s,
SUM((CASE WHEN forecast_rollup_flag||open_status_flag='YY'
THEN (sales_credit_amt) ELSE NULL END)) latest_open_opty_amt,
SUM((CASE WHEN forecast_rollup_flag||open_status_flag='YY'
THEN sales_credit_amt_s ELSE NULL END)) latest_open_opty_amt_s,
SUM((CASE WHEN win_loss_indicator||open_status_flag='WN'
THEN sales_credit_amt ELSE NULL END)) won_opty_amt,
SUM((CASE WHEN win_loss_indicator||open_status_flag='WN'
THEN sales_credit_amt_s ELSE NULL END)) won_opty_amt_s,
SUM((CASE WHEN win_loss_indicator||open_status_flag='LN'
THEN sales_credit_amt ELSE NULL END)) lost_opty_amt,
SUM((CASE WHEN win_loss_indicator||open_status_flag='LN'
THEN sales_credit_amt_s ELSE NULL END)) lost_opty_amt_s,
SUM((CASE WHEN win_loss_indicator||open_status_flag='NN'
THEN sales_credit_amt ELSE NULL END)) no_opty_amt,
SUM((CASE WHEN win_loss_indicator||open_status_flag='NN'
THEN sales_credit_amt_s ELSE NULL END)) no_opty_amt_s,
SUM((CASE WHEN opty_ld_conversion_time_id IS NULL
THEN NULL ELSE sales_credit_amt END)) cnv_opty_amt,
SUM((CASE WHEN opty_ld_conversion_time_id IS NULL
THEN NULL ELSE sales_credit_amt_s END)) cnv_opty_amt_s,
SUM(CASE WHEN fact.opty_rank =1 THEN 1 ELSE NULL END) new_opty_cnt,
SUM((CASE WHEN forecast_rollup_flag||open_status_flag='YY' THEN
CASE WHEN fact.opty_rank = 1 THEN 1 ELSE NULL END
ELSE NULL END)) latest_open_opty_cnt,
SUM((CASE WHEN win_loss_indicator||open_status_flag='WN' THEN
CASE WHEN fact.opty_rank = 1 THEN 1 ELSE NULL END
ELSE NULL END)) won_opty_cnt,
SUM((CASE WHEN win_loss_indicator||open_status_flag='LN' THEN 1 ELSE NULL END)) lost_opty_cnt,
SUM((CASE WHEN win_loss_indicator||open_status_flag='NN' THEN 1 ELSE NULL END)) no_opty_cnt,
SUM((CASE WHEN opty_ld_conversion_time_id IS NULL THEN
NULL
ELSE
CASE WHEN fact.opty_rank = 1 THEN 1 ELSE NULL END
END)) cnv_opty_cnt,
COUNT(sales_credit_amt) new_opty_amt_xcnt,
COUNT(sales_credit_amt_s) new_opty_amt_xcnt_s,
COUNT((CASE WHEN forecast_rollup_flag||open_status_flag='YY'
THEN sales_credit_amt ELSE NULL END)) latest_open_opty_xcnt,
COUNT((CASE WHEN forecast_rollup_flag||open_status_flag='YY'
THEN sales_credit_amt_s ELSE NULL END)) latest_open_opty_xcnt_s,
COUNT((CASE WHEN win_loss_indicator||open_status_flag='WN'
THEN sales_credit_amt ELSE NULL END)) won_opty_amt_xcnt,
COUNT((CASE WHEN win_loss_indicator||open_status_flag='WN'
THEN sales_credit_amt_s ELSE NULL END)) won_opty_amt_xcnt_s,
COUNT((CASE WHEN win_loss_indicator||open_status_flag='LN'
THEN sales_credit_amt ELSE NULL END)) lost_opty_amt_xcnt,
COUNT((CASE WHEN win_loss_indicator||open_status_flag='LN'
THEN sales_credit_amt_s ELSE NULL END)) lost_opty_amt_xcnt_s,
COUNT((CASE WHEN win_loss_indicator||open_status_flag='NN'
THEN sales_credit_amt ELSE NULL END)) no_opty_amt_xcnt,
COUNT((CASE WHEN win_loss_indicator||open_status_flag='NN'
THEN sales_credit_amt_s ELSE NULL END)) no_opty_amt_xcnt_s,
COUNT((CASE WHEN opty_ld_conversion_time_id IS NULL
THEN NULL ELSE sales_credit_amt END)) cnv_opty_amt_xcnt,
COUNT((CASE WHEN opty_ld_conversion_time_id IS NULL
THEN NULL ELSE sales_credit_amt_s END)) cnv_opty_amt_xcnt_s,
COUNT(CASE WHEN fact.opty_rank =1 THEN 1 ELSE NULL END) new_opty_cnt_xcnt,
COUNT((CASE WHEN forecast_rollup_flag||open_status_flag='YY' THEN
CASE WHEN fact.opty_rank = 1 THEN 1 ELSE NULL END
ELSE NULL END)) latest_open_opty_cnt_xcnt,
COUNT((CASE WHEN win_loss_indicator||open_status_flag='WN' THEN
CASE WHEN fact.opty_rank = 1 THEN 1 ELSE NULL END
ELSE NULL END)) won_opty_cnt_xcnt,
COUNT((CASE WHEN win_loss_indicator||open_status_flag='LN' THEN 1 ELSE NULL END)) lost_opty_cnt_xcnt,
COUNT((CASE WHEN win_loss_indicator||open_status_flag='NN' THEN 1 ELSE NULL END)) no_opty_cnt_xcnt,
COUNT((CASE WHEN opty_ld_conversion_time_id IS NULL THEN
NULL
ELSE
CASE WHEN fact.opty_rank = 1 THEN 1 ELSE NULL END
END)) cnv_opty_cnt_xcnt,
SUM((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_year_start_date
AND day.ent_year_end_date THEN sales_credit_amt ELSE NULL END)) nfp_yr,
SUM((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_year_start_date
AND day.ent_year_end_date THEN sales_credit_amt_s ELSE NULL END)) nfp_yr_s,
SUM((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_qtr_start_date
AND day.ent_qtr_end_date THEN sales_credit_amt ELSE NULL END)) nfp_qtr,
SUM((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_qtr_start_date
AND day.ent_qtr_end_date THEN sales_credit_amt_s ELSE NULL END)) nfp_qtr_s,
SUM((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_period_start_date
AND day.ent_period_end_date THEN sales_credit_amt ELSE NULL END)) nfp_per,
SUM((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_period_start_date
AND day.ent_period_end_date THEN sales_credit_amt_s ELSE NULL END)) nfp_per_s,
SUM((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.week_start_date
AND day.week_end_date THEN sales_credit_amt ELSE NULL END)) nfp_wk,
SUM((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.week_start_date
AND day.week_end_date THEN sales_credit_amt_s ELSE NULL END)) nfp_wk_s,
COUNT((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_year_start_date
AND day.ent_year_end_date THEN sales_credit_amt ELSE NULL END)) nfp_yr_cnt,
COUNT((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_year_start_date
AND day.ent_year_end_date THEN sales_credit_amt_s ELSE NULL END)) nfp_yr_cnt_s,
COUNT((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_qtr_start_date
AND day.ent_qtr_end_date THEN sales_credit_amt ELSE NULL END)) nfp_qtr_cnt,
COUNT((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_qtr_start_date
AND day.ent_qtr_end_date THEN sales_credit_amt_s ELSE NULL END)) nfp_qtr_cnt_s,
COUNT((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_period_start_date
AND day.ent_period_end_date THEN sales_credit_amt ELSE NULL END)) nfp_per_cnt,
COUNT((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.ent_period_start_date
AND day.ent_period_end_date THEN sales_credit_amt_s ELSE NULL END)) nfp_per_cnt_s,
COUNT((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.week_start_date
AND day.week_end_date THEN sales_credit_amt ELSE NULL END)) nfp_wk_cnt,
COUNT((CASE WHEN to_date(opty_effective_time_id,'J') BETWEEN day.week_start_date
AND day.week_end_date THEN sales_credit_amt_s ELSE NULL END)) nfp_wk_cnt_s,
COUNT(*) xcnt,
fact.lead_number,
fact.win_probability,
fact.sales_stage_id,
fact.customer_id,
fact.status,
fact.owner_sales_group_id,
fact.owner_salesrep_id,
fact.opty_amt,
fact.opty_amt_s,
fact.opty_effective_time_id
FROM
ENI.ENI_OLTP_ITEM_STAR star,
BIL.BIL_BI_OPDTL_F fact,
FII.FII_TIME_DAY day,
JTF.JTF_RS_GROUP_USAGES jgu
WHERE
fact.item_id = star.inventory_item_id
AND fact.item_organization_id = star.organization_id
AND jgu.group_id = fact.sales_group_id
AND jgu.usage= 'SALES'
AND opty_creation_time_id = day.report_date_julian
GROUP BY
fact.opty_creation_time_id,
fact.opty_close_time_id,
fact.opty_ld_conversion_time_id,
fact.sales_group_id,
fact.salesrep_id,
(CASE WHEN fact.item_id=-1 THEN fact.product_category_id ELSE star.vbh_category_id END),
(CASE WHEN fact.item_id=-1 THEN -1 ELSE NULL END),
fact.campaign_object_id,
fact.campaign_object_type,
fact.child_campaign_object_id,
fact.child_campaign_object_type,
fact.opty_id,
fact.competitor_id,
fact.hdr_source_promotion_id,
fact.open_status_flag,
fact.win_loss_indicator,
fact.forecast_rollup_flag,
fact.lead_number,
fact.win_probability,
fact.sales_stage_id,
fact.customer_id,
fact.status,
fact.owner_sales_group_id,
fact.owner_salesrep_id,
fact.opty_amt,
fact.opty_amt_s,
fact.opty_effective_time_id
[Home] [Help]