[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'') ';
l_sess_source_where_clause := ' AND sess.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
1 BIX_PMV_AO_CONTPERHR,
1 BIX_PMV_AO_PREVCONTPERHR,
1 BIX_PMV_AO_TARCONT,
1 BIX_PMV_AO_PREVTARCONT,
1 BIX_PMV_AO_PORESP,
1 BIX_PMV_AO_PREVPORESP,
1 BIX_PMV_AO_ABANRATE,
1 BIX_PMV_AO_PREVABANRATE,
1 BIX_PMV_AO_ABANRATE_GL,
1 BIX_PMV_AO_PREVARGOAL,
1 BIX_PMV_AO_OUTCALLHAND,
1 BIX_PMV_AO_PREVOUTCALLHAND,
1 BIX_PMV_AO_SCHED,
1 BIX_PMV_AO_PREVSCHED,
1 BIX_PMV_AO_AVAILRATE,
1 BIX_PMV_AO_PREVAVAILRATE,
1 BIX_PMV_AO_UTILRATE,
1 BIX_PMV_AO_PREVUTILRATE,
1 BIX_PMV_AO_AVGTALK,
1 BIX_PMV_AO_PREVAVGTALK,
1 BIX_PMV_AO_AVGWRAP,
1 BIX_PMV_AO_PREVAVGWRAP,
1 BIX_PMV_AO_HANDPERHR,
1 BIX_PMV_AO_PREVHANDPERHR,
1 BIX_PMV_AO_CUST,
1 BIX_PMV_AO_PREVCUST,
1 BIX_PMV_AO_LECR,
1 BIX_PMV_AO_PREVLECR,
1 BIX_PMV_AO_OPCR,
1 BIX_PMV_AO_PREVOPCR,
1 BIX_PMV_AO_SRCR,
1 BIX_PMV_AO_PREVSRCR
FROM DUAL ';
SELECT
SUM(CCONT/(DECODE(CLO,0,NULL,CLO)/3600)) BIX_PMV_AO_CONTPERHR ,
SUM(PCONT/(DECODE(PLO,0,NULL,PLO)/3600)) BIX_PMV_AO_PREVCONTPERHR,
SUM(CPR) BIX_PMV_AO_PORESP,
SUM(PPR) BIX_PMV_AO_PREVPORESP,
'||l_contgoal||' BIX_PMV_AO_TARCONT,
'||l_contgoal||' BIX_PMV_AO_PREVTARCONT,
sum(PAB)*100/
decode(sum(nvl(PIOPRED,0)+nvl(PIOCPRED,0)),0,NULL,
sum(nvl(PIOPRED,0)+nvl(PIOCPRED,0))) BIX_PMV_AO_PREVABANRATE,
sum(CAB)*100/
decode(sum(nvl(CIOPRED,0)+nvl(CIOCPRED,0)),0,NULL,
sum(nvl(CIOPRED,0)+nvl(CIOCPRED,0))) BIX_PMV_AO_ABANRATE,
/* Added for US Abandonment Rate */
sum(PABFTC)*100/
decode(sum(nvl(PIOPRED,0)+nvl(PIOCPRED,0)),0,NULL,
sum(nvl(PIOPRED,0)+nvl(PIOCPRED,0))) BIX_PMV_AO_US_ABANRATE_PP,
sum(CABFTC)*100/
decode(sum(nvl(CIOPRED,0)+nvl(CIOCPRED,0)),0,NULL,
sum(nvl(CIOPRED,0)+nvl(CIOCPRED,0))) BIX_PMV_AO_US_ABANRATE_CP,
/* End of additions */
'||l_abangoal||' BIX_PMV_AO_ABANRATE_GL,
'||l_abangoal||' BIX_PMV_AO_PREVARGOAL,
sum(csched) BIX_PMV_AO_SCHED,
sum(psched) BIX_PMV_AO_PREVSCHED,
sum(CHA) BIX_PMV_AO_OUTCALLHAND,
sum(PHA) BIX_PMV_AO_PREVOUTCALLHAND,
sum(nvl(CLO,0)-nvl(CID,0))*100/
decode(sum(CLO),0,NULL,sum(CLO)) BIX_PMV_AO_AVAILRATE,
sum(nvl(PLO,0)-nvl(PID,0))*100/
decode(sum(PLO),0,NULL,sum(PLO)) BIX_PMV_AO_PREVAVAILRATE,
sum(nvl(CLO,0)-nvl(CAV,0)-nvl(CID,0))*100/
decode(sum(CLO),0,NULL,sum(CLO)) BIX_PMV_AO_UTILRATE,
sum(nvl(PLO,0)-nvl(PAV,0)-nvl(PID,0))*100/
decode(sum(PLO),0,NULL,sum(PLO)) BIX_PMV_AO_PREVUTILRATE,
sum(PTA)/
decode(sum(nvl(PHA,0)+nvl(PHAC,0)),0,NULL,
sum(nvl(PHA,0)+nvl(PHAC,0))) BIX_PMV_AO_PREVAVGTALK,
sum(CTA)/
decode(sum(nvl(CHA,0)+nvl(CHAC,0)),0,NULL,
sum(nvl(CHA,0)+nvl(CHAC,0))) BIX_PMV_AO_AVGTALK,
sum(PWA)/
decode(sum(nvl(PHA,0)+nvl(PHAC,0)),0,NULL,
sum(nvl(PHA,0)+nvl(PHAC,0))) BIX_PMV_AO_PREVAVGWRAP,
sum(CWA)/
decode(sum(nvl(CHA,0)+nvl(CHAC,0)),0,NULL,
sum(nvl(CHA,0)+nvl(CHAC,0))) BIX_PMV_AO_AVGWRAP,
round( sum(CHA)*3600/
decode(sum(CLO),0,NULL,sum(CLO)),1) BIX_PMV_AO_HANDPERHR,
round(sum(PHA)*3600/
decode(sum(PLO),0,NULL,sum(PLO)),1) BIX_PMV_AO_PREVHANDPERHR,
sum(CCU) BIX_PMV_AO_CUST,
sum(PCU) BIX_PMV_AO_PREVCUST,
sum(CSR) BIX_PMV_AO_SRCR,
sum(PSR) BIX_PMV_AO_PREVSRCR,
sum(CLE) BIX_PMV_AO_LECR,
sum(PLE) BIX_PMV_AO_PREVLECR,
sum(COP) BIX_PMV_AO_OPCR,
sum(POP) BIX_PMV_AO_PREVOPCR
FROM
(
SELECT
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
AGENTCALL_CONTACT_COUNT ,0
)
) PCONT,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
AGENTCALL_CONTACT_COUNT ,0
)
) CCONT,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
AGENTCALL_PR_COUNT ,0
)
) PPR,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
AGENTCALL_PR_COUNT ,0
)
) CPR,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
CALL_CALLS_OFFERED_TOTAL,0
)
) PIO,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
CALL_CALLS_OFFERED_TOTAL,0
)
) CIO,
sum(
case when dialing_method=''PRED'' then
decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
CALL_CALLS_OFFERED_TOTAL,0
)
else
0
end
) PIOPRED,
sum(
case when dialing_method=''PRED'' then
decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
CALL_CALLS_OFFERED_TOTAL,0
)
else
0
end
)
CIOPRED,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
CALL_CALLS_HANDLED_TOTAL ,0)) PHA,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
CALL_CALLS_HANDLED_TOTAL,0)) CHA,
sum(
case when dialing_method=''PRED'' then
decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
CALL_CALLS_ABANDONED,0)
else 0
end
) PAB,
sum(
case when dialing_method=''PRED'' then
decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
CALL_CALLS_ABANDONED,0)
else 0
end ) CAB,
/* Added for US Abandonment rate */
sum(
case when dialing_method=''PRED'' then
decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
CALL_CALLS_ABANDONED_US,0)
else 0
end
) PABFTC,
sum(
case when dialing_method=''PRED'' then
decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
CALL_CALLS_ABANDONED_US,0)
else 0
end ) CABFTC,
/* End of additions */
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
CALL_CALLS_TRANSFERRED,0 )) PTR,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
CALL_CALLS_TRANSFERRED ,0)) CTR,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
CALL_TALK_TIME,0)) PTA,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
CALL_TALK_TIME,0)) CTA,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
AGENT_WRAP_TIME_NAC,0)) PWA,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
AGENT_WRAP_TIME_NAC,0)) CWA,
count(DISTINCT(CASE
WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
AND party_id <> -1
THEN
PARTY_ID
END
)
) CCU,
count(DISTINCT(CASE
WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN
schedule_id
END
)
) CSCHED,
count(DISTINCT(CASE
WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN
schedule_id
END
)
) PSCHED,
count(DISTINCT(CASE
WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
AND party_id <> -1
THEN
PARTY_ID
END
)
) PCU,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
AGENT_SR_CREATED,0)) PSR,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
AGENT_SR_CREATED,0)) CSR,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
AGENT_LEADS_CREATED,0)) PLE,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
AGENT_LEADS_CREATED,0)) CLE,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
AGENT_OPPORTUNITIES_CREATED,0)) POP,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
AGENT_OPPORTUNITIES_CREATED,0)) COP
FROM bix_ao_call_details_mv mv,
fii_time_rpt_struct cal
WHERE mv.time_id = cal.time_id
AND mv.row_type = :l_cust_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
nvl(SUM( CASE when
period_start_date = &BIS_PREVIOUS_EFFECTIVE_START_DATE
then
CALL_CONT_CALLS_OFFERED_NA
else
0
end),0) PIOC,
nvl(SUM( CASE when
period_start_date = &BIS_CURRENT_EFFECTIVE_START_DATE
then
CALL_CONT_CALLS_OFFERED_NA
else
0
end),0) CIOC,
nvl(SUM( CASE when
period_start_date = &BIS_PREVIOUS_EFFECTIVE_START_DATE
then
CALL_CONT_CALLS_OFFERED_NA
else
0
end),0) PIOCPRED,
nvl(SUM( CASE when
period_start_date = &BIS_CURRENT_EFFECTIVE_START_DATE
and dialing_method=''PRED''
then
CALL_CONT_CALLS_OFFERED_NA
else
0
end),0) CIOCPRED,
nvl(SUM( CASE when
period_start_date = &BIS_PREVIOUS_EFFECTIVE_START_DATE
then
CALL_CONT_CALLS_HANDLED_TOT_NA
else
0
end),0) PIHC,
nvl(SUM( CASE when
period_start_date = &BIS_CURRENT_EFFECTIVE_START_DATE
then
CALL_CONT_CALLS_HANDLED_TOT_NA
else
0
end),0) CIHC,
nvl(SUM(decode(period_start_date,&BIS_PREVIOUS_EFFECTIVE_START_DATE,
CALL_CONT_CALLS_HANDLED_TOT_NA,NULL)),0) PHAC,
nvl(SUM(decode(period_start_date,&BIS_CURRENT_EFFECTIVE_START_DATE,
CALL_CONT_CALLS_HANDLED_TOT_NA,NULL)),0) CHAC
FROM bix_ao_call_details_mv mv
WHERE time_id IN ( to_char(&BIS_CURRENT_EFFECTIVE_START_DATE,''J''),
to_char(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J''))
AND mv.period_type_id = :l_period_type_id
AND mv.row_type = :l_class_row_type
';
SELECT
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
LOGIN_TIME,0)) PLO,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
LOGIN_TIME,0)) CLO,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
AVAILABLE_TIME,0)) PAV,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
AVAILABLE_TIME,0)) CAV,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
IDLE_TIME,0)) PID,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
IDLE_TIME,0)) CID
FROM bix_agent_session_f sess,
fii_time_rpt_struct cal
WHERE sess.time_id = cal.time_id
AND sess.period_type_id = cal.period_type_id
AND application_id = :l_application_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) ';