DBA Data[Home] [Help]

APPS.BIX_PMV_EMC_BACK_ACT_RPT_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 100

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';
Line: 133

  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
  ( ';
Line: 174

  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, ';
Line: 195

  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, ';
Line: 218

      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
     ), ';