SELECT /* bug4654692 */ * FROM
(
SELECT
'SLSREP' umarker,
RANK() OVER(PARTITION BY base.sales_group_id,base.salesrep_id,yr,opty_status ORDER BY opty_amt DESC)
year_rank,
RANK() OVER(PARTITION BY base.sales_group_id,base.salesrep_id,quarter,opty_status ORDER BY opty_amt DESC)
quarter_rank,
RANK() OVER(PARTITION BY base.sales_group_id,base.salesrep_id,period,opty_status ORDER BY opty_amt DESC)
period_rank,
RANK() OVER(PARTITION BY base.sales_group_id,base.salesrep_id,week,opty_status ORDER BY opty_amt DESC)
week_rank,
base.sales_group_id parent_sales_group_id,
base.sales_group_id,
base.salesrep_id,
base.opty_id,
base.opty_number,
base.customer_id,
base.sales_stage_id,
base.win_probability,
base.opty_close_date,
base.opty_status,
base.opty_status_code,
base.yr,
base.quarter,
base.period,
base.week,
base.opty_amt,
base.opty_amt_s
FROM
(
SELECT /*+ USE_HASH(sumry) USE_HASH(time) USE_HASH(jgu)*/
sumry.opty_id,
sumry.lead_number opty_number,
sumry.owner_sales_group_id sales_group_id,
sumry.owner_salesrep_id salesrep_id,
TO_DATE(sumry.opty_effective_time_id,'J') opty_close_date,
sumry.customer_id,
sumry.win_probability,
(CASE
WHEN forecast_rollup_flag||open_status_flag='YY' THEN 'OPEN'
WHEN open_status_flag||win_loss_indicator='NW' THEN 'WON'
WHEN open_status_flag||win_loss_indicator='NL' THEN 'LOST'
WHEN open_status_flag||win_loss_indicator='NN' THEN 'NO_OPPORTUNITY'
ELSE '-1' END
) opty_status,
(CASE WHEN opty_effective_time_id BETWEEN time.yr_start_date_id AND time.yr_end_date_id
THEN 1 ELSE 0 END) yr,
(CASE WHEN opty_effective_time_id BETWEEN time.qtr_start_date_id AND time.qtr_end_date_id
THEN 1 ELSE 0 END) quarter,
(CASE WHEN opty_effective_time_id BETWEEN time.per_start_date_id AND time.per_end_date_id
THEN 1 ELSE 0 END) period,
(CASE WHEN opty_effective_time_id BETWEEN time.wk_start_date_id AND time.wk_end_date_id
THEN 1 ELSE 0 END) week,
sumry.sales_stage_id,
sumry.status opty_status_code,
sumry.opty_amt,
sumry.opty_amt_s
FROM
bil_bi_opdtl_mv sumry,
JTF.JTF_RS_GROUP_USAGES jgu,
(SELECT
TO_NUMBER(TO_CHAR(LEAST(ent_year_start_date,week_start_date),'J')) start_date_id
, TO_NUMBER(TO_CHAR(GREATEST(ent_year_end_date, week_end_date),'J')) end_date_id
, TO_NUMBER(TO_CHAR(ent_year_start_date,'J')) yr_start_date_id
, TO_NUMBER(TO_CHAR(ent_year_end_date,'J')) yr_end_date_id
, TO_NUMBER(TO_CHAR(ent_qtr_start_date,'J')) qtr_start_date_id
, TO_NUMBER(TO_CHAR(ent_qtr_end_date,'J')) qtr_end_date_id
, TO_NUMBER(TO_CHAR(ent_period_start_date,'J')) per_start_date_id
, TO_NUMBER(TO_CHAR(ent_period_end_date,'J')) per_end_date_id
, TO_NUMBER(TO_CHAR(week_start_date,'J')) wk_start_date_id
, TO_NUMBER(TO_CHAR(week_end_date,'J')) wk_end_date_id
FROM
FII.FII_TIME_DAY day
WHERE
TRUNC(SYSDATE) = day.report_date
) time
WHERE
sumry.opty_effective_time_id BETWEEN time.start_date_id AND time.end_date_id
AND sumry.owner_sales_group_id = jgu.group_id
AND jgu.usage='SALES'
AND NVL(sumry.opty_amt,0) > 0
GROUP BY
sumry.opty_id,
sumry.lead_number,
sumry.owner_sales_group_id,
sumry.owner_salesrep_id,
sumry.opty_effective_time_id,
sumry.customer_id,
sumry.win_probability,
sumry.win_loss_indicator,
sumry.open_status_flag,
sumry.forecast_rollup_flag,
sumry.sales_stage_id,
sumry.status,
sumry.opty_amt,
sumry.opty_amt_s,
time.start_date_id,
time.end_date_id,
time.yr_start_date_id,
time.yr_end_date_id,
time.qtr_start_date_id,
time.qtr_end_date_id,
time.per_start_date_id,
time.per_end_date_id,
time.wk_start_date_id,
time.wk_end_date_id
) base
WHERE base.opty_status <> '-1'
UNION ALL
SELECT
'SLSGRP' umarker,
RANK() OVER(PARTITION BY jgd.parent_group_id,yr,opty_status ORDER BY opty_amt DESC) year_rank,
RANK() OVER(PARTITION BY jgd.parent_group_id,quarter,opty_status ORDER BY opty_amt DESC) quarter_rank,
RANK() OVER(PARTITION BY jgd.parent_group_id,period,opty_status ORDER BY opty_amt DESC) period_rank,
RANK() OVER(PARTITION BY jgd.parent_group_id,week,opty_status ORDER BY opty_amt DESC) week_rank,
jgd.parent_group_id parent_sales_group_id,
base.sales_group_id,
base.salesrep_id,
base.opty_id,
base.opty_number,
base.customer_id,
base.sales_stage_id,
base.win_probability,
base.opty_close_date,
base.opty_status,
base.opty_status_code,
base.yr,
base.quarter,
base.period,
base.week,
base.opty_amt,
base.opty_amt_s
FROM
JTF.JTF_RS_GROUPS_DENORM jgd,
JTF.JTF_RS_GROUP_USAGES pjgu,
(
SELECT /*+ USE_HASH(sumry) USE_HASH(time) USE_HASH(jgu)*/
sumry.opty_id,
sumry.lead_number opty_number,
sumry.owner_sales_group_id sales_group_id,
sumry.owner_salesrep_id salesrep_id,
TO_DATE(sumry.opty_effective_time_id,'J') opty_close_date,
sumry.customer_id,
sumry.win_probability,
(CASE
WHEN forecast_rollup_flag||open_status_flag='YY' THEN 'OPEN'
WHEN open_status_flag||win_loss_indicator='NW' THEN 'WON'
WHEN open_status_flag||win_loss_indicator='NL' THEN 'LOST'
WHEN open_status_flag||win_loss_indicator='NN' THEN 'NO_OPPORTUNITY'
ELSE '-1' END
) opty_status,
(CASE WHEN opty_effective_time_id BETWEEN time.yr_start_date_id AND time.yr_end_date_id
THEN 1 ELSE 0 END) yr,
(CASE WHEN opty_effective_time_id BETWEEN time.qtr_start_date_id AND time.qtr_end_date_id
THEN 1 ELSE 0 END) quarter,
(CASE WHEN opty_effective_time_id BETWEEN time.per_start_date_id AND time.per_end_date_id
THEN 1 ELSE 0 END) period,
(CASE WHEN opty_effective_time_id BETWEEN time.wk_start_date_id AND time.wk_end_date_id
THEN 1 ELSE 0 END) week,
sumry.sales_stage_id,
sumry.status opty_status_code,
sumry.opty_amt,
sumry.opty_amt_s
FROM
bil_bi_opdtl_mv sumry,
JTF.JTF_RS_GROUP_USAGES jgu,
(SELECT
TO_NUMBER(TO_CHAR(LEAST(ent_year_start_date,week_start_date),'J')) start_date_id
, TO_NUMBER(TO_CHAR(GREATEST(ent_year_end_date, week_end_date),'J')) end_date_id
, TO_NUMBER(TO_CHAR(ent_year_start_date,'J')) yr_start_date_id
, TO_NUMBER(TO_CHAR(ent_year_end_date,'J')) yr_end_date_id
, TO_NUMBER(TO_CHAR(ent_qtr_start_date,'J')) qtr_start_date_id
, TO_NUMBER(TO_CHAR(ent_qtr_end_date,'J')) qtr_end_date_id
, TO_NUMBER(TO_CHAR(ent_period_start_date,'J')) per_start_date_id
, TO_NUMBER(TO_CHAR(ent_period_end_date,'J')) per_end_date_id
, TO_NUMBER(TO_CHAR(week_start_date,'J')) wk_start_date_id
, TO_NUMBER(TO_CHAR(week_end_date,'J')) wk_end_date_id
FROM
FII.FII_TIME_DAY day
WHERE
TRUNC(SYSDATE) = day.report_date
) time
WHERE
sumry.opty_effective_time_id BETWEEN time.start_date_id AND time.end_date_id
AND sumry.owner_sales_group_id = jgu.group_id
AND jgu.usage='SALES'
AND NVL(sumry.opty_amt,0) > 0
GROUP BY
sumry.opty_id,
sumry.lead_number,
sumry.owner_sales_group_id,
sumry.owner_salesrep_id,
sumry.opty_effective_time_id,
sumry.customer_id,
sumry.win_probability,
sumry.win_loss_indicator,
sumry.open_status_flag,
sumry.forecast_rollup_flag,
sumry.competitor_id,
sumry.sales_stage_id,
sumry.status,
sumry.opty_amt,
sumry.opty_amt_s,
time.start_date_id,
time.end_date_id,
time.yr_start_date_id,
time.yr_end_date_id,
time.qtr_start_date_id,
time.qtr_end_date_id,
time.per_start_date_id,
time.per_end_date_id,
time.wk_start_date_id,
time.wk_end_date_id
) base
WHERE base.opty_status <> '-1'
AND base.sales_group_id = jgd.group_id
AND jgd.parent_group_id = pjgu.group_id
AND jgd.latest_relationship_flag = 'Y'
AND pjgu.usage = 'SALES'
)
WHERE
(yr = 1 and year_rank < 26)
OR (quarter = 1 and quarter_rank < 26)
OR (period = 1 and period_rank < 26)
OR (week = 1 and week_rank < 26)
[Home] [Help]