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_ERPT_RPT_PKG SQL Statements

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

Line: 101


SELECT * from
(
SELECT lookup_table.value   VIEWBY,
        lookup_table.id      VIEWBYID,
nvl(sum(CURR_MSGSGOAL),0)*100/DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD)) BIX_EMC_MSGSGOAL_CP
,
nvl(sum(CURR_MSGSGOAL),0)*100/DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD)) BIX_CALC_ITEM1,
nvl(sum(sum(CURR_MSGSGOAL)) over(),0)*100/
      DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over()) BIX_PMV_TOTAL1,
	 nvl(sum(sum(CURR_MSGSGOAL)) over(),0)*100/
	 DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over()) BIX_CALC_ITEM2,
	 100*
	 (
	 (nvl(sum(CURR_MSGSGOAL),0)/DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD))) -
      (nvl(sum(PREV_MSGSGOAL),0)/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD)))
	 )
	  BIX_EMC_MGCHANGE,
	 100*
	 (
	 (nvl(sum(sum(CURR_MSGSGOAL)) over(),0)/DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over()))-
      (nvl(sum(sum(PREV_MSGSGOAL)) over(),0)/DECODE(sum(sum(PREV_TOT_REPLD)) over(),0,NULL,sum(sum(PREV_TOT_REPLD)) over()))
	 )
	  BIX_PMV_TOTAL2,
nvl(sum(PREV_MSGSGOAL),0)*100/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD)) BIX_EMC_PREVMSGSGOAL,
nvl(sum(PREV_MSGSGOAL),0)*100/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD)) BIX_CALC_ITEM3,
nvl(sum(sum(PREV_MSGSGOAL)) over(),0)*100/DECODE(sum(sum(PREV_TOT_REPLD)) over(),0,NULL,sum(sum(PREV_TOT_REPLD))
over()) BIX_CALC_ITEM4,
nvl(sum(CURR_TOT_REPLD),0) BIX_EMC_REPLD_CP,
nvl(sum(sum(CURR_TOT_REPLD)) over(),0) BIX_PMV_TOTAL3,
(nvl(sum(CURR_TOT_REPLD),0) - sum(PREV_TOT_REPLD))*100/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))  BIX_EMC_REPCHANGE,
(nvl(sum(sum(CURR_TOT_REPLD)) over(),0)-sum(sum(PREV_TOT_REPLD)) over())*100/
   DECODE(sum(sum(PREV_TOT_REPLD)) over(),0,NULL,sum(sum(PREV_TOT_REPLD)) over()) BIX_PMV_TOTAL4,
nvl(sum(prev_tot_repld),0) BIX_EMC_PRREPLD,
nvl(sum(CURR_AUTO_REPLD),0)*100/
 decode(SUM(CURR_TOT_REPLD),0,NULL,SUM(CURR_TOT_REPLD))       BIX_EMC_AUTO_RPLD_RATE_CP,
nvl(sum(sum(CURR_AUTO_REPLD)) over(),0)*100/
 decode(SUM(SUM(CURR_TOT_REPLD)) OVER(),0,NULL,SUM(SUM(CURR_TOT_REPLD)) OVER())   BIX_PMV_TOTAL11,
((nvl(sum(CURR_AUTO_REPLD),0)/ decode(SUM(CURR_TOT_REPLD),0,NULL,SUM(CURR_TOT_REPLD))) -
(sum(PREV_AUTO_REPLD)/ decode(SUM(PREV_TOT_REPLD),0,NULL,SUM(PREV_TOT_REPLD))))*100 BIX_EMC_AUTO_PRLD_CHANGE,
((nvl(SUM(sum(CURR_AUTO_REPLD)) OVER(),0)/ decode(SUM(SUM(CURR_TOT_REPLD)) OVER(),0,NULL,SUM(SUM(CURR_TOT_REPLD)) OVER())) -
(SUM(sum(PREV_AUTO_REPLD))OVER()/ decode(SUM(SUM(PREV_TOT_REPLD)) OVER(),0,NULL,SUM(SUM(PREV_TOT_REPLD)) OVER())))*100 BIX_PMV_TOTAL13,
NVL(SUM(CURR_TRFD),0)*100/DECODE(SUM(CURR_RESOLVED),0,NULL,SUM(CURR_RESOLVED)) BIX_EMC_TRANRATIO_CP,
NVL(SUM(SUM(CURR_TRFD)) OVER(),0)*100/
  DECODE(SUM(SUM(CURR_RESOLVED)) OVER(),0,NULL,SUM(SUM(CURR_RESOLVED)) OVER())   BIX_PMV_TOTAL12,

((NVL(SUM(CURR_TRFD),0)/DECODE(SUM(CURR_RESOLVED),0,NULL,SUM(CURR_RESOLVED))) -
(SUM(PREV_TRFD)/DECODE(SUM(PREV_RESOLVED),0,NULL,SUM(PREV_RESOLVED)))) * 100
 BIX_EMC_TRATIOCHANGE,

((NVL(SUM(SUM(CURR_TRFD)) OVER(),0)/DECODE(SUM(SUM(CURR_RESOLVED)) OVER(),0,NULL,SUM(SUM(CURR_RESOLVED)) OVER())) -
(SUM(SUM(PREV_TRFD)) OVER()/DECODE(SUM(SUM(PREV_RESOLVED)) OVER(),0,NULL,SUM(SUM(PREV_RESOLVED)) OVER()))) * 100
 BIX_PMV_TOTAL14,
nvl(sum(CURR_CRTIME),0)/(3600*DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD))) BIX_EMC_CRTIME_CP,
nvl(sum(CURR_CRTIME),0)/(3600*DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD))) BIX_CALC_ITEM5,
nvl(sum(sum(CURR_CRTIME)) over(),0)/
    (3600*DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over())) BIX_PMV_TOTAL5,
    nvl(sum(sum(CURR_CRTIME)) over(),0)/
	   (3600*DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over())) BIX_CALC_ITEM6,
((nvl(sum(CURR_CRTIME),0)/DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD)))
   - (sum(PREV_CRTIME)/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))))*100/
(sum(PREV_CRTIME)/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))) BIX_EMC_CRCHANGE,
((nvl(sum(sum(CURR_CRTIME)) OVER(),0)/DECODE(sum(sum(CURR_TOT_REPLD)) OVER(),0,NULL,sum(sum(CURR_TOT_REPLD)) OVER()))
   - (sum(sum(PREV_CRTIME)) OVER()/DECODE(sum(sum(PREV_TOT_REPLD)) OVER(),0,NULL,sum(sum(PREV_TOT_REPLD)) OVER())))*100/
(sum(sum(PREV_CRTIME)) OVER()/DECODE(sum(sum(PREV_TOT_REPLD)) OVER(),0,NULL,sum(sum(PREV_TOT_REPLD)) OVER())) BIX_PMV_TOTAL6,
nvl(sum(PREV_CRTIME),0)/(3600*DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))) BIX_EMC_PRCRTIME,
nvl(sum(PREV_CRTIME),0)/(3600*DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))) BIX_CALC_ITEM7,
sum(sum(PREV_CRTIME)) over()/
	   (3600*DECODE(sum(sum(PREV_TOT_REPLD)) over(),0,NULL,sum(sum(PREV_TOT_REPLD)) over())) BIX_CALC_ITEM8,
nvl(sum(CURR_ARTIME),0)/(3600*sum(CURR_REPLD)) BIX_EMC_ARTIME_CP,
nvl(sum(sum(CURR_ARTIME)) over(),0)/(3600*sum(sum(CURR_REPLD)) over()) BIX_PMV_TOTAL7,
((nvl(sum(CURR_ARTIME),0)/(3600*sum(CURR_REPLD))) - (nvl(sum(PREV_ARTIME),0)/(3600*sum(PREV_REPLD))))*100 /
       (nvl(sum(PREV_ARTIME),0)/(3600*sum(PREV_REPLD))) BIX_EMC_ARCHANGE,
((nvl(sum(sum(CURR_ARTIME)) over(),0)/(3600*sum(sum(CURR_REPLD)) over())) - (nvl(sum(sum(PREV_ARTIME)) over(),0)/(3600*sum(sum(PREV_REPLD)) over())))*100 /
       (nvl(sum(sum(PREV_ARTIME)) over(),0)/(3600*sum(sum(PREV_REPLD)) over())) BIX_PMV_TOTAL8,
nvl(sum(CURR_ONEDONE),0)*100/sum(CURR_THREADS) BIX_EMC_ONE_DONE_CP,
nvl(sum(CURR_ONEDONE),0)*100/sum(CURR_THREADS) BIX_CALC_ITEM9,
nvl(sum(sum(CURR_ONEDONE)) over(),0)*100/sum(sum(CURR_THREADS)) over() BIX_PMV_TOTAL9,
nvl(sum(sum(CURR_ONEDONE)) over(),0)*100/sum(sum(CURR_THREADS)) over() BIX_CALC_ITEM10,
 nvl(sum(PREV_ONEDONE),0)*100/sum(PREV_THREADS) BIX_CALC_ITEM11,
 nvl(sum(sum(PREV_ONEDONE)) over(),0)*100/sum(sum(PREV_THREADS)) over() BIX_CALC_ITEM12,
nvl(sum(CURR_ONEDONE),0)*100/sum(CURR_THREADS)- nvl(sum(PREV_ONEDONE),0)*100/sum(PREV_THREADS) BIX_EMC_ODCHANGE,
nvl(sum(sum(CURR_ONEDONE)) over(),0)*100/sum(sum(CURR_THREADS)) over()-
		nvl(sum(sum(PREV_ONEDONE)) over(),0)*100/sum(sum(PREV_THREADS)) over() BIX_PMV_TOTAL10  ,
(NVL(SUM(curr_deleted),0)+ NVL(SUM(curr_auto_deleted),0))/ DECODE(SUM(curr_completed),0,NULL,SUM(curr_completed))*100 BIX_EMC_DELRATIO_CP,
(NVL(SUM(curr_deleted),0)+ NVL(SUM(curr_auto_deleted),0))/ DECODE(SUM(curr_completed),0,NULL,SUM(curr_completed))*100 BIX_CALC_ITEM13,
(((NVL(SUM(curr_deleted),0)+ NVL(SUM(curr_auto_deleted),0))/ DECODE(SUM(curr_completed),0,NULL,SUM(curr_completed))) -
          ((NVL(SUM(prev_deleted),0)+ NVL(SUM(prev_auto_deleted),0))/ DECODE(SUM(prev_completed),0,NULL,SUM(prev_completed))))* 100 BIX_EMC_DRATIOCHANGE,
(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_PMV_TOTAL15,
(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_CALC_ITEM14,
(((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())) -
          ((NVL(SUM(SUM(prev_deleted)) OVER(),0)+ NVL(SUM(SUM(prev_auto_deleted)) OVER(),0))/ DECODE(SUM(SUM(prev_completed)) OVER(),0,NULL,SUM(SUM(prev_completed)) OVER())))* 100 BIX_PMV_TOTAL16,
(NVL(SUM(prev_deleted),0)+ NVL(SUM(prev_auto_deleted),0))/ DECODE(SUM(prev_completed),0,NULL,SUM(prev_completed))*100 BIX_CALC_ITEM15,
(NVL(SUM(SUM(prev_deleted)) OVER(),0) + NVL(SUM(SUM(prev_auto_deleted)) OVER(),0) )/
          DECODE(SUM(SUM(prev_completed)) OVER(),0,NULL,SUM(SUM(prev_completed)) OVER())*100 BIX_CALC_ITEM16,
NVL(SUM(CURR_TRFD),0)*100/DECODE(SUM(CURR_RESOLVED),0,NULL,SUM(CURR_RESOLVED)) BIX_CALC_ITEM17,
NVL(SUM(SUM(CURR_TRFD)) OVER(),0)*100/
DECODE(SUM(SUM(CURR_RESOLVED)) OVER(),0,NULL,SUM(SUM(CURR_RESOLVED)) OVER())   BIX_CALC_ITEM18,
NVL(SUM(PREV_TRFD),0)*100/DECODE(SUM(PREV_RESOLVED),0,NULL,SUM(PREV_RESOLVED)) BIX_CALC_ITEM19,
SUM(SUM(PREV_TRFD)) OVER()*100/
DECODE(SUM(SUM(PREV_RESOLVED)) OVER(),0,NULL,SUM(SUM(PREV_RESOLVED)) OVER())   BIX_CALC_ITEM20
FROM ( ';

Line: 206


       l_sqltext := l_sqltext || ' SELECT  email_account_id                                ID,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
                     EMAILS_RPLD_BY_GOAL_IN_PERIOD,NULL))         CURR_MSGSGOAL,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
                     EMAILS_RPLD_BY_GOAL_IN_PERIOD,NULL))         PREV_MSGSGOAL,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     EMAILS_REPLIED_IN_PERIOD,NULL))                    CURR_REPLD,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     EMAILS_REPLIED_IN_PERIOD,NULL))                    PREV_REPLD,
     sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD,NULL))  CURR_AUTO_REPLD,
     sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD,NULL))  PREV_AUTO_REPLD,
	SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
              NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),NULL)) CURR_TOT_REPLD,
	SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
              NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),NULL)) PREV_TOT_REPLD,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD,NULL)) CURR_TRFD,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD,NULL)) PREV_TRFD,
        sum( decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
               NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_DELETED_IN_PERIOD,0),NULL)) CURR_RESOLVED,
        sum( decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
               NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_DELETED_IN_PERIOD,0),NULL)) PREV_RESOLVED,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     EMAIL_RESP_TIME_IN_PERIOD,NULL))                    CURR_CRTIME,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     EMAIL_RESP_TIME_IN_PERIOD,NULL))                    PREV_CRTIME,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     AGENT_RESP_TIME_IN_PERIOD,NULL))                    CURR_ARTIME,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     AGENT_RESP_TIME_IN_PERIOD,NULL))                    PREV_ARTIME,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     ONE_RSLN_IN_PERIOD,NULL))                    CURR_ONEDONE,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     ONE_RSLN_IN_PERIOD,NULL))                    PREV_ONEDONE,
 	sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
 		     EMAILS_DELETED_IN_PERIOD)) 		CURR_DELETED,
        sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
                     EMAILS_DELETED_IN_PERIOD)) 		PREV_DELETED,
	sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     EMAILS_AUTO_DELETED_IN_PERIOD)) 		CURR_AUTO_DELETED,
     	sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
     		     EMAILS_AUTO_DELETED_IN_PERIOD)) 		PREV_AUTO_DELETED,
	sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		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,
     	sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
     		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))) 	PREV_COMPLETED,
        sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     INTERACTION_THREADS_IN_PERIOD,NULL))                    CURR_THREADS,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     INTERACTION_THREADS_IN_PERIOD,NULL))                    PREV_THREADS,
	sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     EMAILS_REPLIED_IN_PERIOD,NULL))) over()                 CURR_TOTALREPLD,
	sum(sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     EMAILS_REPLIED_IN_PERIOD,NULL))) over()                 PREV_TOTALREPLD
       FROM bix_email_details_mv fact,
  	   fii_time_rpt_struct cal
      WHERE fact.time_id = cal.time_id
      AND fact.period_type_id = cal.period_type_id
	 AND fact.row_type = :l_row_type
      AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
      AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id '
	  || l_where_clause ||
      ' GROUP BY email_account_id
	 ) summ, ';

Line: 280


       l_sqltext := l_sqltext || ' SELECT  email_classification_id                                ID,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
                     EMAILS_RPLD_BY_GOAL_IN_PERIOD,NULL))         CURR_MSGSGOAL,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
                     EMAILS_RPLD_BY_GOAL_IN_PERIOD,NULL))         PREV_MSGSGOAL,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     EMAILS_REPLIED_IN_PERIOD,NULL))                    CURR_REPLD,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     EMAILS_REPLIED_IN_PERIOD,NULL))                    PREV_REPLD,
        sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD,NULL))  CURR_AUTO_REPLD,
        sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD,NULL))  PREV_AUTO_REPLD,
	SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
              NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),NULL)) CURR_TOT_REPLD,
	SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
              NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),NULL)) PREV_TOT_REPLD,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD,NULL)) CURR_TRFD,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD,NULL)) PREV_TRFD,
        sum( decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
               NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_DELETED_IN_PERIOD,0),NULL)) CURR_RESOLVED,
        sum( decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
               NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_DELETED_IN_PERIOD,0),NULL)) PREV_RESOLVED,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     EMAIL_RESP_TIME_IN_PERIOD,NULL))                    CURR_CRTIME,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     EMAIL_RESP_TIME_IN_PERIOD,NULL))                    PREV_CRTIME,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     AGENT_RESP_TIME_IN_PERIOD,NULL))                    CURR_ARTIME,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     AGENT_RESP_TIME_IN_PERIOD,NULL))                    PREV_ARTIME,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     ONE_RSLN_IN_PERIOD,NULL))                    CURR_ONEDONE,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     ONE_RSLN_IN_PERIOD,NULL))                    PREV_ONEDONE,
	sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
 		     EMAILS_DELETED_IN_PERIOD)) 		CURR_DELETED,
        sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
                     EMAILS_DELETED_IN_PERIOD)) 		PREV_DELETED,
        sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     EMAILS_AUTO_DELETED_IN_PERIOD)) 		CURR_AUTO_DELETED,
	sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
     		     EMAILS_AUTO_DELETED_IN_PERIOD)) 		PREV_AUTO_DELETED,
	sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		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,
     	sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
     		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))) 	PREV_COMPLETED,
	sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     INTERACTION_THREADS_IN_PERIOD,NULL))                    CURR_THREADS,
	sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     INTERACTION_THREADS_IN_PERIOD,NULL))                    PREV_THREADS,
	sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
		     EMAILS_REPLIED_IN_PERIOD,NULL))) over()                 CURR_TOTALREPLD,
	sum(sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
		     EMAILS_REPLIED_IN_PERIOD,NULL))) over()                 PREV_TOTALREPLD
	FROM bix_email_details_mv fact,
  	   fii_time_rpt_struct cal
      WHERE fact.time_id = cal.time_id
      AND fact.period_type_id = cal.period_type_id
	 AND fact.row_type = :l_row_type
      AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
      AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id '
	  || l_where_clause ||
      ' GROUP BY email_classification_id
	 ) summ, ';