[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 fii1.name VIEWBY,
sum(PREV_TALK)/
decode(sum(PREV_HAND),0,NULL,sum(PREV_HAND)) BIX_PMV_AO_AVGTALK_PP,
sum(CURR_TALK)/
decode(sum(CURR_HAND),0,NULL,sum(CURR_HAND)) BIX_PMV_AO_AVGTALK_CP,
sum(PREV_WRAP)/
decode(sum(PREV_HAND),0,NULL,sum(PREV_HAND)) BIX_PMV_AO_AVGWRAP_PP,
sum(CURR_WRAP)/
decode(sum(CURR_HAND),0,NULL,sum(CURR_HAND)) BIX_PMV_AO_AVGWRAP_CP
FROM
( SELECT fii1.sequence SEQUENCE,
SUM( CASE when
(
fii1.start_date between &BIS_CURRENT_REPORT_START_DATE
and &BIS_CURRENT_ASOF_DATE
and cal.report_date = least(fii1.end_date,&BIS_CURRENT_ASOF_DATE)
)
then
CALL_TALK_TIME
else
0
end
) CURR_TALK,
SUM( CASE when
(
fii1.start_date between &BIS_CURRENT_REPORT_START_DATE
and &BIS_CURRENT_ASOF_DATE
and cal.report_date = least(fii1.end_date,&BIS_CURRENT_ASOF_DATE)
)
then
AGENT_WRAP_TIME_NAC
else
0
end
) CURR_WRAP,
SUM( CASE when
(
fii1.start_date between &BIS_CURRENT_REPORT_START_DATE
and &BIS_CURRENT_ASOF_DATE
and cal.report_date = least(fii1.end_date,&BIS_CURRENT_ASOF_DATE)
)
then
CALL_CALLS_HANDLED_TOTAL
else
0
end
) CURR_HAND,
SUM( CASE when
(
fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE
and &BIS_PREVIOUS_ASOF_DATE
and cal.report_date = least(fii1.end_date,&BIS_PREVIOUS_ASOF_DATE)
)
then
CALL_TALK_TIME
else
0
end
) PREV_TALK,
SUM( CASE when
(
fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE
and &BIS_PREVIOUS_ASOF_DATE
and cal.report_date = least(fii1.end_date,&BIS_PREVIOUS_ASOF_DATE)
)
then
AGENT_WRAP_TIME_NAC
else
0
end
) PREV_WRAP,
SUM( CASE when
(
fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE
and &BIS_PREVIOUS_ASOF_DATE
and cal.report_date = least(fii1.end_date,&BIS_PREVIOUS_ASOF_DATE)
)
then
CALL_CALLS_HANDLED_TOTAL
else
0
end
) PREV_HAND
FROM ';
SELECT fii1.sequence,
0 CURR_TALK,
0 CURR_WRAP,
SUM( CASE when
(
fii1.start_date between &BIS_CURRENT_REPORT_START_DATE
and &BIS_CURRENT_ASOF_DATE
)
then
CALL_CONT_CALLS_HANDLED_TOT_NA
else
0
end
) CURR_HAND,
0 PREV_TALK,
0 PREV_WRAP,
SUM( CASE when
(
fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE
and &BIS_PREVIOUS_ASOF_DATE
)
then
CALL_CONT_CALLS_HANDLED_TOT_NA
else
0
end
) PREV_HAND
FROM bix_ao_call_details_mv mv, ';
SELECT fii1.name VIEWBY,
NULL BIX_PMV_AO_AVGTALK_PP,
sum(CURR_TALK)/
decode(sum(CURR_HAND),0,NULL,sum(CURR_HAND)) BIX_PMV_AO_AVGTALK_CP,
NULL BIX_PMV_AO_AVGWRAP_PP,
sum(CURR_WRAP)/
decode(sum(CURR_HAND),0,NULL,sum(CURR_HAND)) BIX_PMV_AO_AVGWRAP_CP
FROM
( SELECT fii1.name NAME,
sum(CALL_TALK_TIME) CURR_TALK,
sum(AGENT_WRAP_TIME_NAC) CURR_WRAP,
sum(CALL_CALLS_HANDLED_TOTAL) CURR_HAND
FROM ';
SELECT fii1.name,
0 CURR_TALK,
0 CURR_WRAP,
SUM(CALL_CONT_CALLS_HANDLED_TOT_NA) CURR_HAND
FROM bix_ao_call_details_mv mv, ';