[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 active_flag=''Y'' and arc_source_code_for=''CAMP'') ';
SELECT fii1.name VIEWBY,
NVL(sum(PREV_ABAND)*100/
sum(DECODE(PREV_OFFRD,0,NULL,PREV_OFFRD)),0) BIX_PMV_AO_ABANRATE_PP,
NVL(sum(CURR_ABAND)*100/
sum(DECODE(CURR_OFFRD,0,NULL,CURR_OFFRD)),0) BIX_PMV_AO_ABANRATE_CP,
' || l_goal ||' BIX_PMV_AO_ABANRATE_GL
FROM
(
/*start inline view
select current and previous calls offered and abandoned
*/
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_CALLS_OFFERED_TOTAL
else
0
end
) CURR_OFFRD,
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_OFFERED_TOTAL
else
0
end
) PREV_OFFRD,
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_PREVIOUS_ASOF_DATE)
)
then
CALL_CALLS_ABANDONED
else
0
end
) CURR_ABAND,
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_ABANDONED
else
0
end
) PREV_ABAND
FROM ';
/*start inline view -select continued offer measures */
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_CONT_CALLS_HANDLED_TOT_NA
else
0
end
) CURR_OFFRD,
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_CONT_CALLS_HANDLED_TOT_NA
else
0
end
) PREV_OFFRD,
0 CURR_ABAND,
0 PREV_ABAND
FROM ';
SELECT fii1.name VIEWBY,
0 BIX_PMV_AO_ABANRATE_PP,
NVL(sum(CURR_ABAND)*100/
sum(DECODE(CURR_OFFRD,0,NULL,CURR_OFFRD)),0) BIX_PMV_AO_ABANRATE_CP,
' || l_goal ||' BIX_PMV_AO_ABANRATE_GL
FROM
(
/*start of inline view
select current contacts from mv */
SELECT fii1.name NAME,
sum(CALL_CALLS_ABANDONED ) CURR_ABAND,
sum(CALL_CALLS_OFFERED_TOTAL) CURR_OFFRD
FROM '||l_period_type||' fii1,
bix_ao_call_details_mv mv,
fii_time_rpt_struct cal
WHERE mv.time_id = cal.time_id
AND dialing_method =''PRED''
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
&BIS_CURRENT_ASOF_DATE
AND cal.report_date = least(&BIS_CURRENT_ASOF_DATE,fii1.end_date)
AND cal.period_type_id = mv.period_type_id
AND mv.row_type = :l_row_type ';
/*start of inline view -select continued measures*/
SELECT fii1.name NAME,
0 CURR_ABAND,
sum(CALL_CONT_CALLS_HANDLED_TOT_NA) CURR_OFFRD
FROM '||l_period_type||' fii1,
bix_ao_call_details_mv mv,
fii_time_rpt_struct cal
WHERE mv.time_id = cal.time_id
AND dialing_method =''PRED''
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
&BIS_CURRENT_ASOF_DATE
AND cal.report_date = least(&BIS_CURRENT_ASOF_DATE,fii1.end_date)
AND cal.period_type_id = mv.period_type_id
AND mv.row_type = :l_row_type ';