[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(greatest(RANKING,10),10,group_name,
decode(group_name,:l_unknown,
''''|| :l_unknown ||'' '',
''''|| :l_other_group ||'' ''
)
) VIEWBY,
round(sum(in_ansgoal)*100/decode(sum(IN_OFFRD),0,NULL,sum(IN_OFFRD)),1) BIX_PMV_AI_SL,
round(sum(sum(in_ansgoal)) over()*100/
decode(sum(sum(IN_OFFRD)) over(),0,NULL,sum(sum(IN_OFFRD)) over()),1) BIX_PMV_TOTAL1,
sum(in_fresh_hand) BIX_PMV_AI_INCALLHAND,
sum(sum(in_fresh_hand)) over() BIX_PMV_TOTAL2,
round(sum(IN_QTOANS)/
decode(sum(in_hand),0,NULL,sum(in_hand)),1) BIX_PMV_AI_SPANS,
round(sum(sum(IN_QTOANS)) over()/
decode(sum(sum(in_hand)) over(),0,NULL,sum(sum(in_hand)) over()),1) BIX_PMV_TOTAL3,
round(sum(IN_ABAND)*100/
decode(sum(IN_OFFRD),0,NULL,sum(IN_OFFRD)),1) BIX_PMV_AI_ABANRATE,
round(sum(sum(IN_ABAND)) over()*100/
decode(sum(sum(IN_OFFRD)) over(),0,NULL,sum(sum(IN_OFFRD)) over()),1) BIX_PMV_TOTAL4,
round(sum(talk)/
decode(sum(hand),0,NULL,sum(hand)),1) BIX_PMV_AI_AVGTALK,
round(sum(sum(talk)) over()/
decode(sum(sum(hand)) over(),0,NULL,sum(sum(hand)) over()),1) BIX_PMV_TOTAL5,
sum(sr) BIX_PMV_AI_SRCR,
sum(sum(sr)) over() BIX_PMV_TOTAL6,
sum(lead) BIX_PMV_AI_LECR,
sum(sum(lead)) over() BIX_PMV_TOTAL7 ,
sum(opp) BIX_PMV_AI_OPCR,
sum(sum(opp)) over() BIX_PMV_TOTAL8
FROM
(
--
--Additional inline view needed to compute RANK due to continued measures
--
SELECT nvl(group_name,:l_unknown) group_name,
nvl(sum(IN_OFFRD),0) IN_OFFRD, nvl(sum(in_ansgoal),0) in_ansgoal, nvl(sum(in_hand),0) in_hand,
nvl(sum(IN_FRESH_HAND),0) IN_FRESH_HAND,
nvl(sum(hand),0) hand, nvl(sum(IN_QTOANS),0) IN_QTOANS, nvl(sum(IN_ABAND),0) IN_ABAND,
nvl(sum(talk),0) talk, nvl(sum(sr),0) sr, nvl(sum(lead),0) lead, nvl(sum(opp),0) opp,
decode(group_name,NULL,11,
RANK() OVER (ORDER BY nvl(sum(in_fresh_hand),0) DESC, group_name)
) RANKING
FROM
(
SELECT server_group_id server_group_id,
sum(decode(mv.media_item_type,
''TELE_INB'',CALL_CALLS_OFFERED_TOTAL,
''TELE_DIRECT'',CALL_CALLS_OFFERED_TOTAL,
0)
) IN_OFFRD,
sum(decode(mv.media_item_type,
''TELE_INB'',AGENT_CALLS_ANSWERED_BY_GOAL,
''TELE_DIRECT'',AGENT_CALLS_ANSWERED_BY_GOAL,
0)
) IN_ANSGOAL,
sum(decode(mv.media_item_type,
''TELE_INB'',AGENT_CALLS_HANDLED_TOTAL,
''TELE_DIRECT'',AGENT_CALLS_HANDLED_TOTAL,
0)
) IN_HAND,
sum(decode(mv.media_item_type,
''TELE_INB'',AGENT_CALLS_HANDLED_TOTAL,
''TELE_DIRECT'',AGENT_CALLS_HANDLED_TOTAL,
0)
) IN_FRESH_HAND,
sum(AGENT_CALLS_HANDLED_TOTAL) HAND,
sum(decode(mv.media_item_type,
''TELE_INB'',CALL_TOT_QUEUE_TO_ANSWER,
''TELE_DIRECT'',CALL_TOT_QUEUE_TO_ANSWER,
0)
) IN_QTOANS,
sum(decode(mv.media_item_type,
''TELE_INB'',CALL_CALLS_ABANDONED,
''TELE_DIRECT'',CALL_CALLS_ABANDONED,
0)
) IN_ABAND,
sum(CALL_TALK_TIME) TALK,
sum(AGENT_SR_CREATED) SR,
sum(AGENT_LEADS_CREATED) LEAD,
sum(AGENT_OPPORTUNITIES_CREATED) OPP
FROM bix_ai_call_details_mv mv,
fii_time_rpt_struct cal
WHERE mv.time_id = cal.time_id
AND mv.row_type = :l_row_type
AND mv.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
cal.record_type_id
AND cal.report_date = &BIS_CURRENT_ASOF_DATE ';
SELECT server_group_id server_group_id,
nvl(sum(decode(mv.media_item_type,
''TELE_INB'',CALL_CONT_CALLS_OFFERED_NA,
''TELE_DIRECT'', CALL_CONT_CALLS_OFFERED_NA,
0)
),0) IN_OFFRD,
0 IN_ANSGOAL,
nvl(sum(decode(mv.media_item_type,
''TELE_INB'',AGENT_CONT_CALLS_HAND_NA,
''TELE_DIRECT'', AGENT_CONT_CALLS_HAND_NA,
0)
),0) IN_HAND,
0 IN_FRESH_HAND,
nvl(sum(AGENT_CONT_CALLS_HAND_NA),0) HAND,
0 IN_QTOANS,
0 IN_ABAND,
0 TALK,
0 SR,
0 LEAD,
0 OPP
FROM bix_ai_call_details_mv mv
WHERE mv.time_id = to_char(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')
AND mv.row_type = :l_row_type
AND mv.period_type_id = :l_period_type_id
AND period_start_time = :l_period_start_time ';