[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select source_code_for_id from ams_source_codes where arc_source_code_for=''CAMP''
and source_code_id in (:l_source_code_id) and active_flag=''Y'' ) ';
SELECT decode(campmast.value,null,''''|| :l_unknown ||'' '',campmast.value) VIEWBY,
nvl(max(campmast.id),-1) VIEWBYID,
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(nvl(sum(ABAND),0)) 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,
3600*nvl(sum(contacts)/decode(MIN(login),0,null,MIN(login)),0)
BIX_PMV_AO_CONTPERHR,
3600*sum(sum(contacts)) over()/decode(MIN(login),0,null,MIN(login))
BIX_PMV_TOTAL9,
nvl(sum(pr),0) BIX_PMV_AO_PORESP,
sum(nvl(sum(pr),0)) over() BIX_PMV_TOTAL10
FROM
(
/* Add another Inline view */
SELECT camp ,sourcecode sourcecode,OFFRD, HAND,ABAND,USABAND,TALK,SR,LEAD,OPP,CONTACTS,PR, SUM(LOGIN) over() LOGIN
FROM
( SELECT campaign_id camp,
min(source_code_id) sourcecode,
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 mv.campaign_id camp,
min(source_code_id) sourcecode,
sum(decode(dialing_method,''PRED'',CALL_CONT_CALLS_HANDLED_TOT_NA,0) ) OFFRD,
sum(AGENT_CONT_CALLS_HAND_NA) HAND,
0 ABAND,
0 USABAND,
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 -999 camp,
-999 sourcecode,
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 ';
select source_code_for_id,camp.id id,camp.value value from bim_dimv_campaigns camp,ams_source_codes scodes
where scodes.source_code_id=camp.id and
arc_source_code_for=''CAMP'' and active_flag=''Y'' ) campmast
where summ.camp=campmast.source_code_for_id(+)
and camp<>-999
GROUP BY campmast.value
';