eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BIX_PMV_EMC_RESOLV_RPT_PKG SQL Statements

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

Line: 67


SELECT
''[email protected]'' VIEWBY,
10 BIX_EMC_COMPLETED,
20 BIX_EMC_COMPCHANGE,
30 BIX_EMC_REPLD,
40 BIX_EMC_REPCHANGE,
50 BIX_EMC_DELETED,
60 BIX_EMC_DELCHANGE,
70 BIX_EMC_AUTOREPLD,
80 BIX_EMC_AUTOREPLD_CHANGE,
90  BIX_EMC_AUTODELETED,
100 BIX_EMC_AUTODELETED_CHANGE,
10 BIX_EMC_AUTORESOLV,
20 BIX_EMC_AUTORESOLV_CHANGE,
30 BIX_EMC_AUTOUPDATESR,
40 BIX_EMC_AUTOUPDATESR_CHANGE,
10 BIX_PMV_TOTAL3,
20 BIX_PMV_TOTAL4,
30 BIX_PMV_TOTAL5,
40 BIX_PMV_TOTAL6,
50 BIX_PMV_TOTAL7,
60 BIX_PMV_TOTAL8,
70 BIX_PMV_TOTAL9,
80 BIX_PMV_TOTAL10,
90 BIX_PMV_TOTAL11,
100 BIX_PMV_TOTAL12,
10 BIX_PMV_TOTAL13,
20 BIX_PMV_TOTAL14,
30 BIX_PMV_TOTAL15,
40 BIX_PMV_TOTAL16,
1000 BIX_EMC_AUTORESPONSE,
2000 BIX_EMC_RESPONSE
FROM DUAL';

Line: 106


  SELECT * FROM
  (
  SELECT
    lookup_table.value VIEWBY,
    lookup_table.id    VIEWBYID,
    NVL(SUM(curr_completed),0) BIX_EMC_COMPLETED_CP,
    (NVL(SUM(curr_completed),0) - DECODE(SUM(prev_completed),0,NULL,SUM(prev_completed)))
    / DECODE(SUM(prev_completed),0,NULL,SUM(prev_completed)) * 100 BIX_EMC_COMPCHANGE,
    NVL(SUM(curr_replied),0) BIX_EMC_REPLD,
    (NVL(SUM(curr_replied),0) - SUM(prev_replied)) / SUM(prev_replied) * 100 BIX_EMC_REPCHANGE,
    NVL(SUM(curr_deleted),0) BIX_EMC_DELETED,
    (NVL(SUM(curr_deleted),0) - SUM(prev_deleted)) / SUM(prev_deleted) * 100 BIX_EMC_DELCHANGE,
    NVL(SUM(curr_auto_replied),0) BIX_EMC_AUTOREPLD,
    (NVL(SUM(curr_auto_replied),0) - SUM(prev_auto_replied)) / SUM(prev_auto_replied) * 100 BIX_EMC_AUTOREPLD_CHANGE,
    NVL(SUM(curr_auto_deleted),0) BIX_EMC_AUTODELETED,
    (NVL(SUM(curr_auto_deleted),0) - SUM(prev_auto_deleted)) / SUM(prev_auto_deleted) * 100 BIX_EMC_AUTODELETED_CHANGE,
    NVL(SUM(curr_auto_resolv),0) BIX_EMC_AUTORESOLV,
    (NVL(SUM(curr_auto_resolv),0) - SUM(prev_auto_resolv)) / SUM(prev_auto_resolv) * 100 BIX_EMC_AUTORESOLV_CHANGE,
    NVL(SUM(curr_auto_sr),0) BIX_EMC_AUTOUPDATESR,
    (NVL(SUM(curr_auto_sr),0) - SUM(prev_auto_sr)) / SUM(prev_auto_sr) * 100 BIX_EMC_AUTOUPDATESR_CHANGE,
    NVL(SUM(curr_replied),0) +  NVL(SUM(curr_deleted),0)  BIX_EMC_RESPONSE,
    NVL(SUM(curr_auto_replied),0) +  NVL(SUM(curr_auto_deleted),0) +
    NVL(SUM(curr_auto_sr),0) + NVL(SUM(curr_auto_resolv),0)    BIX_EMC_AUTORESPONSE,
    NVL(SUM(SUM(curr_completed)) OVER(),0) BIX_PMV_TOTAL3,
    (NVL(SUM(SUM(curr_completed)) OVER(),0) - DECODE(SUM(SUM(prev_completed)) OVER(),0,NULL,SUM(SUM(prev_completed)) OVER() ))
    / DECODE(SUM(SUM(prev_completed)) OVER(),0,NULL,SUM(SUM(prev_completed)) OVER()) * 100 BIX_PMV_TOTAL4,
    NVL(SUM(SUM(curr_replied)) OVER(),0) BIX_PMV_TOTAL5,
    (NVL(SUM(SUM(curr_replied)) OVER(),0) - SUM(SUM(prev_replied)) OVER()) / SUM(SUM(prev_replied)) OVER() * 100 BIX_PMV_TOTAL6,
    NVL(SUM(SUM(curr_deleted)) OVER(),0) BIX_PMV_TOTAL7,
    (NVL(SUM(SUM(curr_deleted)) OVER(),0) - SUM(SUM(prev_deleted)) OVER()) / SUM(SUM(prev_deleted)) OVER() * 100 BIX_PMV_TOTAL8,
    NVL(SUM(SUM(curr_auto_replied)) OVER(),0) BIX_PMV_TOTAL9,
    (NVL(SUM(SUM(curr_auto_replied)) OVER(),0) - SUM(SUM(prev_auto_replied)) OVER()) / SUM(SUM(prev_auto_replied)) OVER() * 100 BIX_PMV_TOTAL10,
    NVL(SUM(SUM(curr_auto_deleted)) OVER() ,0) BIX_PMV_TOTAL11,
    (NVL(SUM(SUM(curr_auto_deleted)) OVER(),0) - SUM(SUM(prev_auto_deleted)) OVER()) / SUM(SUM(prev_auto_deleted)) OVER() * 100 BIX_PMV_TOTAL12,
    NVL(SUM(SUM(curr_auto_resolv)) OVER(),0) BIX_PMV_TOTAL13,
    (NVL(SUM(SUM(curr_auto_resolv)) OVER(),0) - SUM(SUM(prev_auto_resolv)) OVER()) / SUM(SUM(prev_auto_resolv)) OVER() * 100 BIX_PMV_TOTAL14,
    NVL(SUM(SUM(curr_auto_sr)) OVER(),0) BIX_PMV_TOTAL15,
    (NVL(SUM(SUM(curr_auto_sr)) OVER(),0) - SUM(SUM(prev_auto_sr)) OVER()) / SUM(SUM(prev_auto_sr)) OVER() * 100 BIX_PMV_TOTAL16,
    NVL(SUM(SUM(curr_replied)) OVER(),0) +  NVL(SUM(SUM(curr_deleted)) OVER(),0)  BIX_CALC_ITEM33,--001
    NVL(SUM(SUM(curr_auto_replied)) OVER(),0) +  NVL(SUM(SUM(curr_auto_deleted)) OVER(),0) +
    NVL(SUM(SUM(curr_auto_sr)) OVER(),0) + NVL(SUM(SUM(curr_auto_resolv)) OVER(),0)    BIX_CALC_ITEM34 --001
  FROM
  ( ';

Line: 151


    l_sqltext := l_sqltext || ' SELECT
     email_account_id id,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,
                NVL(EMAILS_REPLIED_IN_PERIOD,0) +
                NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
                NVL(EMAILS_DELETED_IN_PERIOD,0) +
                NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0)+
                NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0)+
                NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0)
                )) curr_completed,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,
                NVL(EMAILS_REPLIED_IN_PERIOD,0) +
                NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
                NVL(EMAILS_DELETED_IN_PERIOD,0) +
                NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0)+
                NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0)+
                NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0)
                )) prev_completed,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) curr_replied,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) prev_replied,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) curr_deleted,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) prev_deleted,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) curr_auto_replied,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) prev_auto_replied,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) curr_auto_deleted,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) prev_auto_deleted,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_UPTD_SR_IN_PERIOD)) curr_auto_sr,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_UPTD_SR_IN_PERIOD)) prev_auto_sr,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_RESOLVED_IN_PERIOD)) curr_auto_resolv,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_RESOLVED_IN_PERIOD)) prev_auto_resolv
     FROM bix_email_details_mv fact,
          fii_time_rpt_struct calendar
        WHERE fact.time_id = calendar.time_id
	   AND   fact.row_type = :l_row_type
        AND fact.period_type_id = calendar.period_type_id
        AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
        AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) =  calendar.record_type_id '
        || l_where_clause || '
     GROUP BY email_account_id
     ) fact, ';

Line: 192


    l_sqltext := l_sqltext || ' SELECT
     email_classification_id id,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,
                NVL(EMAILS_REPLIED_IN_PERIOD,0) +
                NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
                NVL(EMAILS_DELETED_IN_PERIOD,0) +
                NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0)+
                NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0)+
                NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0)
                )) curr_completed,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,
                NVL(EMAILS_REPLIED_IN_PERIOD,0) +
                NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
                NVL(EMAILS_DELETED_IN_PERIOD,0) +
                NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0)+
                NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0)+
                NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0)
                )) prev_completed,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) curr_replied,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) prev_replied,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) curr_deleted,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) prev_deleted,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) curr_auto_replied,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) prev_auto_replied,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) curr_auto_deleted,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) prev_auto_deleted,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_UPTD_SR_IN_PERIOD)) curr_auto_sr,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_UPTD_SR_IN_PERIOD)) prev_auto_sr,
     SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_RESOLVED_IN_PERIOD)) curr_auto_resolv,
     SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_RESOLVED_IN_PERIOD)) prev_auto_resolv
     FROM bix_email_details_mv fact,
          fii_time_rpt_struct calendar
        WHERE fact.time_id = calendar.time_id
	   AND   fact.row_type = :l_row_type
        AND fact.period_type_id = calendar.period_type_id
        AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
        AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) =  calendar.record_type_id '
        || l_where_clause || '
     GROUP BY email_classification_id
     ) fact, ';

Line: 251


	nvl(BIX_EMC_DELETED,0)+
	abs(nvl(BIX_EMC_DELCHANGE,0))+
	nvl(BIX_EMC_AUTOREPLD,0)+
	abs(nvl(BIX_EMC_AUTOREPLD_CHANGE,0))+
	nvl(BIX_EMC_AUTODELETED,0)+
	abs(nvl(BIX_EMC_AUTODELETED_CHANGE,0))+
	nvl(BIX_EMC_AUTORESOLV,0)+
	abs(nvl(BIX_EMC_AUTORESOLV_CHANGE,0))+
	nvl(BIX_EMC_AUTOUPDATESR,0)+
	abs(nvl(BIX_EMC_AUTOUPDATESR_CHANGE,0))
	)<> 0
	&ORDER_BY_CLAUSE ';