The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
''[email protected]'' VIEWBY,
10 BIX_EMC_BACKLOG,
20 BIX_EMC_BACKCHANGE,
30 BIX_EMC_AGT_HR_EQ,
40 BIX_EMC_COST_EQ,
50 BIX_EMC_0_1_DAY,
60 BIX_EMC_0_1_DAY_CHANGE,
70 BIX_EMC_2_3_DAY,
80 BIX_EMC_2_3_DAY_CHANGE,
90 BIX_EMC_4_7_DAY,
100 BIX_EMC_4_7_DAY_CHANGE,
110 BIX_EMC_OVER_7_DAY,
120 BIX_EMC_OVER_7_DAY_CHANGE,
130 BIX_PMV_TOTAL1,
140 BIX_PMV_TOTAL2,
150 BIX_PMV_TOTAL3,
160 BIX_PMV_TOTAL4,
170 BIX_PMV_TOTAL5,
180 BIX_PMV_TOTAL6,
190 BIX_PMV_TOTAL7,
200 BIX_PMV_TOTAL8,
210 BIX_PMV_TOTAL9,
220 BIX_PMV_TOTAL10,
230 BIX_PMV_TOTAL11,
240 BIX_PMV_TOTAL12
FROM DUAL';
SELECT * FROM (
SELECT
lookup_table.value VIEWBY,
lookup_table.id VIEWBYID,
SUM(curr_backlog) BIX_EMC_BACKLOG_CP,
(NVL(SUM(curr_backlog),0) -SUM(prev_backlog))/
DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog)) * 100 BIX_EMC_BACKCHANGE,
SUM(prev_backlog) BIX_EMC_PREVBACKLOG,
SUM(curr_backlog)/(SUM(total_emails_replied)/SUM(total_login_hours)) BIX_EMC_AGT_HR_EQ,
NVL(SUM(curr_one_day),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog))*100 BIX_EMC_0_1_DAY_CP,
(NVL(SUM(curr_one_day),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog)) -
NVL(SUM(prev_one_day),0)/DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog))) * 100 BIX_EMC_0_1_DAY_CHANGE,
NVL(SUM(curr_three_day),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog))*100 BIX_EMC_2_3_DAY_CP,
(NVL(SUM(curr_three_day),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog)) -
NVL(SUM(prev_three_day),0)/DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog))) * 100 BIX_EMC_2_3_DAY_CHANGE,
NVL(SUM(curr_week),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog))*100 BIX_EMC_4_7_DAY_CP,
(NVL(SUM(curr_week),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog)) -
NVL(SUM(prev_week),0)/DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog))) * 100 BIX_EMC_4_7_DAY_CHANGE,
NVL(SUM(curr_week_plus),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog))*100 BIX_EMC_OVER_7_DAY_CP,
(NVL(SUM(curr_week_plus),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog)) -
NVL(SUM(prev_week_plus),0)/DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog))) * 100 BIX_EMC_OVER_7_DAY_CHANGE,
SUM(SUM(curr_backlog)) OVER() BIX_PMV_TOTAL1,
(NVL(SUM(SUM(curr_backlog)) OVER(),0) - SUM(SUM(prev_backlog)) OVER())/
DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER()) * 100 BIX_PMV_TOTAL2,
SUM(SUM(curr_backlog)) OVER()/(SUM(SUM(total_emails_replied)) OVER()/SUM(SUM(total_login_hours)) OVER()) BIX_PMV_TOTAL3,
NVL(SUM(SUM(curr_one_day)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER())*100 BIX_PMV_TOTAL5,
(NVL(SUM(SUM(curr_one_day)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER()) -
NVL(SUM(SUM(prev_one_day)) OVER(),0)/DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER())) * 100 BIX_PMV_TOTAL6,
NVL(SUM(SUM(curr_three_day)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER())*100 BIX_PMV_TOTAL7,
(NVL(SUM(SUM(curr_three_day)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER()) -
NVL(SUM(SUM(prev_three_day)) OVER(),0)/DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER())) * 100 BIX_PMV_TOTAL8,
NVL(SUM(SUM(curr_week)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER())*100 BIX_PMV_TOTAL9,
(NVL(SUM(SUM(curr_week)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER()) -
NVL(SUM(SUM(prev_week)) OVER(),0)/DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER())) * 100 BIX_PMV_TOTAL10,
NVL(SUM(SUM(curr_week_plus)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER())*100 BIX_PMV_TOTAL11,
(NVL(SUM(SUM(curr_week_plus)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER()) -
NVL(SUM(SUM(prev_week_plus)) OVER(),0)/DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER())) * 100 BIX_PMV_TOTAL12
FROM
( ';
l_sqltext := l_sqltext || ' SELECT
email_account_id id,
SUM(DECODE(period_start_date,:l_max_collect_date,
NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) curr_backlog,
SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,
NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) prev_backlog,
NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_ONE_DAY)),0) curr_one_day,
SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_ONE_DAY)) prev_one_day,
NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_THREE_DAYS)),0) curr_three_day,
SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_THREE_DAYS)) prev_three_day,
NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_WEEK)),0) curr_week,
SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_WEEK)) prev_week,
NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_WEEK_PLUS)),0) curr_week_plus,
SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_WEEK_PLUS)) prev_week_plus
FROM bix_email_details_mv
WHERE time_id IN (TO_CHAR(:l_max_collect_date,''J''),TO_CHAR(&BIS_PREVIOUS_ASOF_DATE,''J''))
AND row_type = :l_row_type
AND period_type_id = :l_period_type_id ' || l_where_clause || '
GROUP BY email_account_id
) fact, ';
l_sqltext := l_sqltext || ' SELECT
email_classification_id id,
SUM(DECODE(period_start_date,:l_max_collect_date,
NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) curr_backlog,
SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,
NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) prev_backlog,
NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_ONE_DAY)),0) curr_one_day,
SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_ONE_DAY)) prev_one_day,
NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_THREE_DAYS)),0) curr_three_day,
SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_THREE_DAYS)) prev_three_day,
NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_WEEK)),0) curr_week,
SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_WEEK)) prev_week,
NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_WEEK_PLUS)),0) curr_week_plus,
SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_WEEK_PLUS)) prev_week_plus
FROM bix_email_details_mv
WHERE time_id IN (TO_CHAR(:l_max_collect_date,''J''),TO_CHAR(&BIS_PREVIOUS_ASOF_DATE,''J''))
AND row_type = :l_row_type
AND period_type_id = :l_period_type_id ' || l_where_clause || '
GROUP BY email_classification_id
) fact, ';
SELECT SUM(EMAILS_REPLIED_IN_PERIOD) total_emails_replied
FROM bix_email_details_mv fact,
fii_time_rpt_struct calendar
WHERE fact.time_id = calendar.time_id
AND fact.period_type_id = calendar.period_type_id
AND calendar.report_date = &BIS_CURRENT_ASOF_DATE
AND fact.row_type = :l_row_type
AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id
),
(
SELECT SUM(LOGIN_TIME)/3600 total_login_hours
FROM bix_agent_session_f fact,
fii_time_rpt_struct calendar
WHERE fact.application_id = :l_application_id
AND fact.time_id = calendar.time_id
AND fact.period_type_id = calendar.period_type_id
AND calendar.report_date = &BIS_CURRENT_ASOF_DATE
AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id
), ';