[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_source_code_where_clause := ' AND fact.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(g_id,3,:l_subtotal,outcome.outcome_code) BIX_PMV_AO_OUTCOME,
DECODE(g_id,3,'' '',NVL(result.result_code,:l_unknown)) BIX_PMV_AO_RESULT,
DECODE(g_id,3,'' '',NVL(reason.reason_code,:l_unknown )) BIX_PMV_AO_REASON,
NVL(curr_count,0) BIX_PMV_AO_COUNT,
nvl(sum(decode(g_id,3,curr_count)) over(),0) BIX_PMV_TOTAL1,
ROUND(nvl(curr_count * 100/DECODE(sum(decode(g_id,3,curr_count)) over(),0,NULL,sum(decode(g_id,3,curr_count)) over()),0),1) BIX_PMV_AO_PERTOTAL1,
ROUND(nvl(curr_count * 100/DECODE(sum(decode(g_id,3,curr_count)) over(),0,NULL,sum(decode(g_id,3,curr_count)) over()),0),1) -
ROUND(NVL(prev_count * 100/DECODE(sum(decode(g_id,3,prev_count)) over(),0,NULL,sum(decode(g_id,3,prev_count)) over()),0),1) BIX_PMV_AO_COUNTCHANGE
/*,sum( ROUND(NVL(curr_count,0) * 100/DECODE(SUM(curr_count) OVER(),0,NULL,SUM(curr_count) OVER()),1) -
ROUND(NVL(prev_count,0) * 100/DECODE(SUM(prev_count) OVER(),0,NULL,SUM(prev_count) OVER()),1) ) over() over() BIX_PMV_TOTAL2
*/
FROM
(
SELECT outcome_id,
result_id,
reason_id,
grouping_id(outcome_id,result_id,reason_id) g_id,
SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,AGENTCALL_ORR_COUNT,NULL)) curr_count,
SUM(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,AGENTCALL_ORR_COUNT,NULL)) prev_count
FROM bix_ao_call_details_mv fact,
fii_time_rpt_struct cal
WHERE fact.time_id = cal.time_id
AND fact.row_type = :l_row_type
AND fact.period_type_id = cal.period_type_id
AND fact.outcome_id != :l_outcome_filter
AND cal.report_date IN ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
' || l_where_clause ||
' GROUP BY
GROUPING SETS
(
( outcome_id,result_id,reason_id),
( outcome_id)
)
) fact, jtf_ih_outcomes_vl outcome,jtf_ih_results_vl result,jtf_ih_reasons_vl reason
WHERE fact.outcome_id = outcome.outcome_id(+)
AND fact.result_id = result.result_id(+)
AND fact.reason_id = reason.reason_id(+)
order by outcome.outcome_code,g_id,result.result_code,reason.reason_code ';