[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(greatest(RANKING,10),10,account.value,:l_other_account) BIX_EMC_ACCOUNT,
round(nvl(SUM(MSGSGOAL),0)*100/decode(SUM(TOT_REPLD),0,NULL,SUM(TOT_REPLD)),1) BIX_EMC_MSGSGOAL,
nvl(SUM(SUM(MSGSGOAL)) over(),0)*100/
decode(SUM(SUM(TOT_REPLD)) over(),0,NULL,sum(SUM(TOT_REPLD)) over()) BIX_PMV_TOTAL1,
round(nvl(sum(AUTO_REPLD),0)*100/
decode(SUM(TOT_REPLD),0,NULL,SUM(TOT_REPLD)),1) BIX_EMC_AUTO_RPLD_RATE,
round(nvl(sum(sum(AUTO_REPLD)) over(),0)*100/
decode(SUM(SUM(TOT_REPLD)) OVER(),0,NULL,SUM(SUM(TOT_REPLD)) OVER()),1) BIX_PMV_TOTAL6,
ROUND(NVL(SUM(TRFD),0)*100/DECODE(SUM(RESOLVED),0,NULL,SUM(RESOLVED)),1) BIX_EMC_TRANRATIO,
ROUND(NVL(SUM(SUM(TRFD)) OVER(),0)*100/
DECODE(SUM(SUM(RESOLVED)) OVER(),0,NULL,SUM(SUM(RESOLVED)) OVER()),1) BIX_PMV_TOTAL7,
round(nvl(sum(CRTIME),0)/(3600*decode(SUM(TOT_REPLD),0,NULL,SUM(TOT_REPLD))),1) BIX_EMC_CRTIME,
round(nvl(sum(sum(CRTIME)) over(),0)/
(3600*decode(SUM(SUM(TOT_REPLD)) over(),0,NULL,SUM(SUM(TOT_REPLD)) over())),1) BIX_PMV_TOTAL3,
round(nvl(sum(ARTIME),0)/(3600*decode(SUM(REPLD),0,NULL,SUM(REPLD))),1) BIX_EMC_ARTIME,
round(nvl(SUM(SUM(ARTIME)) over(),0)/(3600*decode(
SUM(SUM(REPLD)) over(),0,NULL,SUM(SUM(REPLD)) over())),1) BIX_PMV_TOTAL4,
nvl(SUM(ONEDONE),0)*100/decode(SUM(THREADS),0,NULL,SUM(THREADS)) BIX_EMC_ONE_DONE,
nvl(SUM(SUM(ONEDONE)) over(),0)*100/
decode(SUM(SUM(THREADS)) over(),0,NULL,SUM(SUM(THREADS)) over()) BIX_PMV_TOTAL5,
--Start 001. Changes for adding columns for delete rate and its Grand Total
(NVL(SUM(curr_deleted),0)+ NVL(SUM(curr_auto_deleted),0))/ DECODE(SUM(curr_completed),0,NULL,SUM(curr_completed))*100 BIX_EMC_DELETE_RATE,
(NVL(SUM(SUM(curr_deleted)) OVER(),0) + NVL(SUM(SUM(curr_auto_deleted)) OVER(),0) )/
DECODE(SUM(SUM(curr_completed)) OVER(),0,NULL,SUM(SUM(curr_completed)) OVER())*100 BIX_EMC_DELETE_TOTAL
--End 001. Changes for adding columns for delete rate and its Grand Total
FROM (
SELECT email_account_id ACCOUNT_ID,
sum(EMAILS_RPLD_BY_GOAL_IN_PERIOD) MSGSGOAL,
sum(EMAILS_REPLIED_IN_PERIOD) REPLD,
sum(EMAILS_AUTO_REPLIED_IN_PERIOD) AUTO_REPLD,
NVL(SUM(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0) TOT_REPLD,
sum(EMAILS_RSL_AND_TRFD_IN_PERIOD) TRFD,
NVL(SUM(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_DELETED_IN_PERIOD),0) RESOLVED,
sum(EMAIL_RESP_TIME_IN_PERIOD) CRTIME,
sum(AGENT_RESP_TIME_IN_PERIOD) ARTIME,
sum(ONE_RSLN_IN_PERIOD) ONEDONE,
sum(INTERACTION_THREADS_IN_PERIOD) THREADS,
sum(sum(EMAILS_REPLIED_IN_PERIOD)) over() TOTALREPLD,
RANK() OVER (ORDER BY nvl(sum(EMAILS_RPLD_BY_GOAL_IN_PERIOD)/
DECODE(NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0),0,
NULL,
NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0)),0) DESC,
account.value
) RANKING,
--Start 001. Changes for adding columns for delete rate and its Grand Total
SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) CURR_DELETED,
SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) CURR_AUTO_DELETED,
NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_DELETED_IN_PERIOD,0) + NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) + NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) curr_completed
--End 001. Changes for adding columns for delete rate and its Grand Total
FROM bix_email_details_mv fact,
fii_time_rpt_struct cal, bix_email_accounts_v account
WHERE fact.time_id = cal.time_id
AND fact.row_type = :l_row_type
AND fact.email_account_id = account.id
AND fact.period_type_id = cal.period_type_id
AND cal.report_date = &BIS_CURRENT_ASOF_DATE
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id ';