[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_view_by_select VARCHAR2(500) ;
, p_view_by_select => l_view_by_select
, p_view_by => l_view_by
);
l_sqltext :=' SELECT decode(media_item_type, ''TELE_INB'',:l_inbound,''TELE_DIRECT'', :l_inbound,
''TELE_MANUAL'', :l_dialed,''TELE_WEB_CALLBACK'', :l_webcall,''UNSOLICITED'', :l_unsolicited,
media_item_type) BIX_PMV_AI_MITYPE, nvl(sum(P_HANDLED),0) BIX_PMV_AI_PPER, nvl(sum(C_HANDLED),0) BIX_PMV_AI_CPER
from ((SELECT orderby orderby, name media_item_type,0 c_handled,0 p_handled FROM (
select 1 orderby, ''TELE_INB'' name from dual UNION ALL
select 2 orderby, ''TELE_DIRECT'' name from dual UNION ALL
select 3 orderby, ''TELE_WEB_CALLBACK''name from dual UNION ALL
select 4 orderby, ''TELE_MANUAL'' name from dual UNION ALL
select 5 orderby, ''UNSOLICITED'' name from dual ) types
)
UNION ALL (
select 0 orderby, media_item_type,c_handled, p_handled
from'||l_sqltext||
'GROUP BY decode(media_item_type, ''TELE_INB'',:l_inbound,''TELE_DIRECT'', :l_inbound,
''TELE_MANUAL'', :l_dialed,''TELE_WEB_CALLBACK'', :l_webcall,''UNSOLICITED'', :l_unsolicited,
media_item_type)
ORDER BY SUM(ORDERBY)';
SELECT mediatype BIX_PMV_AI_MITYPE,
nvl(sum(pper),0) BIX_PMV_AI_PPER,
nvl(sum(cper),0) BIX_PMV_AI_CPER
FROM (
SELECT 0 orderby,
decode(mv.media_item_type, ''TELE_INB'',:l_inbound,
''TELE_DIRECT'', :l_direct,
''TELE_MANUAL'', :l_dialed,
''TELE_WEB_CALLBACK'', :l_webcall,
''UNSOLICITED'', :l_unsolicited,
mv.media_item_type) MEDIATYPE,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
CALL_CALLS_OFFERED_TOTAL,0)) PPER,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
CALL_CALLS_OFFERED_TOTAL,0)) CPER
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 IN (&BIS_CURRENT_ASOF_DATE,
&BIS_PREVIOUS_ASOF_DATE) ';
SELECT orderby orderby, name MEDIATYPE,
0 PPER,
0 CPER
FROM (select 1 orderby, :l_inbound name from dual UNION ALL
select 2 orderby, :l_direct name from dual UNION ALL
select 3 orderby, :l_webcall name from dual UNION ALL
select 4 orderby, :l_manual name from dual UNION ALL
select 5 orderby, :l_unsolicited name from dual
) types
) GROUP BY mediatype ORDER BY sum(orderby) ' ;