[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,
nvl(sum(CURR_RCVD),0) BIX_EMC_RCVD,
nvl(SUM(SUM(CURR_RCVD)) over(),0) BIX_PMV_TOTAL1,
nvl(sum(curr_composed),0) BIX_EMC_COMPOSED,
nvl(SUM(sum(curr_composed)) OVER(),0) BIX_PMV_TOTAL2,
sum(CURR_REPLD) BIX_EMC_REPLD,
SUM(sum(CURR_REPLD)) over() BIX_PMV_TOTAL3,
sum(CURR_DEL) BIX_EMC_DELETED,
SUM(sum(CURR_DEL)) over() BIX_PMV_TOTAL4,
nvl(sum(curr_trfd) ,0) BIX_EMC_TRANOUT,
nvl(SUM(sum(curr_trfd)) OVER(),0) BIX_PMV_TOTAL6,
nvl(sum(CURR_BACKLOG),0) BIX_EMC_BACKLOG,
nvl(SUM(sum(CURR_BACKLOG)) over(),0) BIX_PMV_TOTAL5,
nvl(sum(CURR_SR),0) BIX_EMC_SR,
nvl(SUM(sum(CURR_SR)) over(),0) BIX_PMV_TOTAL8,
nvl(sum(curr_leads),0) BIX_EMC_LEADS,
nvl(SUM(sum(curr_leads)) OVER(),0) BIX_PMV_TOTAL9
FROM (
SELECT email_account_id ACCOUNT_ID,
sum(EMAILS_OFFERED_IN_PERIOD) CURR_RCVD,
sum(EMAILS_COMPOSED_IN_PERIOD) CURR_COMPOSED,
NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0) CURR_REPLD,
NVL(sum(EMAILS_DELETED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_DELETED_IN_PERIOD),0) CURR_DEL,
sum(EMAILS_RSL_AND_TRFD_IN_PERIOD) CURR_TRFD,
sum(SR_CREATED_IN_PERIOD) CURR_SR,
sum(LEADS_CREATED_IN_PERIOD) CURR_LEADS,
RANK() OVER (ORDER BY sum(nvl(EMAILS_OFFERED_IN_PERIOD,0)) DESC, vl.value) RANKING
FROM bix_email_details_mv fact,
fii_time_rpt_struct cal, bix_email_accounts_v vl
WHERE fact.time_id = cal.time_id
AND fact.row_type = :l_row_type
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
AND vl.id = fact.email_account_id ';
SELECT email_account_id ACCOUNT_ID,
SUM(NVL(ACCUMULATED_OPEN_EMAILS,0) +
NVL(ACCUMULATED_EMAILS_IN_QUEUE,0)
) CURR_BACKLOG
FROM bix_email_details_mv
WHERE time_id = to_char(:l_max_collect_date,''J'')
AND row_type = :l_row_type
AND period_type_id = :l_period_type_id ';