[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_source_code_where_clause := ' AND mv.campaign_id in (select source_code_for_id from
ams_source_codes where source_code_id IN (:l_source_code_id) and arc_source_code_for=''CAMP'' and active_flag=''Y'') ';
SELECT DECODE(greatest(RANKING,10),10,group_name,
decode(group_name,:l_unknown,
''''|| :l_unknown ||'' '',
''''|| :l_other_group ||'' ''
)
) VIEWBY,
sum(HAND) BIX_PMV_AO_OUTCALLHAND,
sum(sum(HAND)) over() BIX_PMV_TOTAL2,
round(NVL(sum(ABAND)*100/
decode(sum(OFFRD),0,NULL,sum(OFFRD)),0),1) BIX_PMV_AO_ABANRATE,
round(sum (sum(ABAND)) over()*100/
decode(sum(sum(OFFRD)) over(),0,NULL,sum(sum(OFFRD)) over()),1) BIX_PMV_TOTAL4,
round(nvl(sum(USABAND)*100/
decode(sum(OFFRD),0,NULL,sum(OFFRD)),0),1) BIX_PMV_AO_US_ABANRATE,
round(sum(nvl(sum(USABAND),0)) over()*100/
decode(sum(sum(OFFRD)) over(),0,NULL,sum(sum(OFFRD)) over()),1) BIX_PMV_TOTAL11,
sum(sr) BIX_PMV_AO_SRCR,
sum(sum(sr)) over() BIX_PMV_TOTAL6,
sum(lead) BIX_PMV_AO_LECR,
sum(sum(lead)) over() BIX_PMV_TOTAL7 ,
sum(opp) BIX_PMV_AO_OPCR,
sum(sum(opp)) over() BIX_PMV_TOTAL8,
round(nvl(3600* sum(contacts) /decode(sum(login),0,null,sum(login)),0),1)
BIX_PMV_AO_CONTPERHR,
round(sum(sum(contacts)) over() /(decode(sum(sum(login)) over(),0,null,sum(sum(login)) over())/3600),1) BIX_PMV_TOTAL9,
nvl(sum(pr),0) BIX_PMV_AO_PORESP,
sum(nvl(sum(pr),0)) over() BIX_PMV_TOTAL10
FROM
(
/*
--Additional inline view needed to compute RANK due to continued measures
*/
SELECT nvl(bixcent.value,:l_unknown) group_name,
nvl(sum(OFFRD),0) OFFRD, nvl(sum(hand),0) HAND,
nvl(sum(ABAND),0) ABAND,
nvl(sum(USABAND),0) USABAND,
nvl(sum(LOGIN),0) LOGIN,
nvl(sum(CONTACTS),0) CONTACTS,
nvl(sum(PR),0) PR,
nvl(sum(talk),0) TALK, nvl(sum(sr),0) SR, nvl(sum(lead),0) LEAD, nvl(sum(opp),0) OPP,
decode(bixcent.value,NULL,11,
RANK() OVER (ORDER BY nvl(sum(HAND),0) DESC, bixcent.value)
) RANKING
FROM
(
SELECT server_group_id server_group_id,
sum(decode(dialing_method,''PRED'',CALL_CALLS_OFFERED_TOTAL,0) ) OFFRD,
sum(AGENT_CALLS_HANDLED_TOTAL) HAND,
sum(decode(dialing_method,''PRED'',CALL_CALLS_ABANDONED,0) ) ABAND,
sum(decode(dialing_method,''PRED'',CALL_CALLS_ABANDONED_US,0) ) USABAND,
sum(CALL_TALK_TIME) TALK,
sum(AGENT_SR_CREATED) SR,
sum(AGENT_LEADS_CREATED) LEAD,
sum(AGENT_OPPORTUNITIES_CREATED) OPP,
sum(AGENTCALL_CONTACT_COUNT) CONTACTS,
sum(AGENTCALL_PR_COUNT) PR,
0 LOGIN
FROM bix_ao_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,
sum(decode(dialing_method,''PRED'',CALL_CONT_CALLS_HANDLED_TOT_NA,0) ) OFFRD,
nvl(sum(AGENT_CONT_CALLS_HAND_NA),0) HAND,
0 ABAND,
0 USBAND,
0 TALK,
0 SR,
0 LEAD,
0 OPP,
0 CONTACTS,
0 PR,
0 LOGIN
FROM bix_ao_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
';
SELECT mv.server_group_id ,
0 OFFRD,
0 HAND,
0 ABAND,
0 USABAND,
0 TALK,
0 SR,
0 LEAD,
0 OPP,
0 CONTACTS,
0 PR,
SUM(LOGIN_TIME) LOGIN
FROM
bix_agent_session_f mv,
fii_time_rpt_struct cal
WHERE mv.time_id = cal.time_id
AND application_id = :l_application_id
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
';