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

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

Line: 48


' SELECT v.resource_name BIX_PMV_AI_AGENT, '
||bix_pmv_dbi_utl_pkg.get_simple_measure(
					p_curr=>'to_number(NVL(c_lg,0))'
					,p_measurecol=>'BIX_PMV_AI_LOGIN_CP'
					,p_totalcol=>'BIX_PMV_TOTAL1'
					,p_convertunit=>'/3600'
					)
||','
||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
					p_num =>'to_number(NVL(c_lg,0))'
					,p_measurecol=>'BIX_PMV_AI_LOGIN_PT'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'Y'
					,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_measurecol=>'BIX_PMV_AI_AVAILRATE_CP'
					,p_totalcol=>'BIX_PMV_TOTAL2'
					)
||','
||bix_pmv_dbi_utl_pkg.get_devavg_measure(
					p_percentage=>'Y'
					,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_col=>'BIX_PMV_AI_AVAILRATE_DEV'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'Y'
					,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)-NVL(c_av,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_measurecol=>'BIX_PMV_AI_UTILRATE_CP'
					,p_totalcol=>'BIX_PMV_TOTAL3'
					)
||','
||bix_pmv_dbi_utl_pkg.get_devavg_measure(
					p_percentage=>'Y'
					,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)-NVL(c_av,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_col=>'BIX_PMV_AI_UTILRATE_DEV'
					)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
					p_curr=>'NVL(c_ic,0)'
					,p_measurecol=>'BIX_PMV_AI_INCALLHAND_CP'
					,p_totalcol=>'BIX_PMV_TOTAL4'
					)
||','
||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
					p_num =>'to_number(NVL(c_ic,0))'
					,p_measurecol=>'BIX_PMV_AI_INCALLHAND_PT'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'N'
					,p_num=>'NVL(c_ic,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_measurecol=>'BIX_PMV_AI_INCALLHAND_PAH'
					,p_totalcol=>'BIX_PMV_TOTAL5'
					,p_convunitfordenom=>'/3600'
					)
||','
||bix_pmv_dbi_utl_pkg.get_devavg_measure(
					p_percentage=>'N'
					,p_num=>'NVL(c_ic,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_col=>'BIX_PMV_AI_INCALLHAND_PAH_DEV'
				    ,p_convunitfordenom=>'/3600'
					)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
					p_curr=>'NVL(c_mc,0)'
					,p_measurecol=>'BIX_PMV_AI_DIALED_CP'
					,p_totalcol=>'BIX_PMV_TOTAL8'
					)
||','
||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
					p_num =>'to_number(NVL(c_mc,0))'
					,p_measurecol=>'BIX_PMV_AI_DIALED_PT'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'N'
					,p_num=>'NVL(c_mc,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_measurecol=>'BIX_PMV_AI_DIALED_PAH'
					,p_totalcol=>'BIX_PMV_TOTAL9'
					,p_convunitfordenom=>'/3600'
					)
||','
||bix_pmv_dbi_utl_pkg.get_devavg_measure(
					p_percentage=>'N'
					,p_num=>'NVL(c_mc,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_col=>'BIX_PMV_AI_DIALED_PAH_DEV'
					,p_convunitfordenom=>'/3600'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'N'
					,p_num=>'NVL(c_t, 0)'
					,p_denom=>'NVL(c_tc,0)+NVL(c_cch,0)+
					           NVL(c_ctc,0)+NVL(c_cct,0)'
					,p_measurecol=>'BIX_PMV_AI_AVGTALK_CP'
					,p_totalcol=>'BIX_PMV_TOTAL10'
					)
||','
||bix_pmv_dbi_utl_pkg.get_devavg_measure(
					p_percentage=>'N'
					,p_num=>'NVL(c_t, 0)'
					,p_denom=>'NVL(c_tc,0)+NVL(c_cch,0)+
					           NVL(c_ctc,0)+NVL(c_cct,0)'
					,p_col=>'BIX_PMV_AI_AVGTALK_DEV'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'N'
					,p_num=>'NVL(c_w, 0)'
					,p_denom=>'NVL(c_tc,0)+NVL(c_cch,0)+
					           NVL(c_ctc,0)+NVL(c_cct,0)'
					,p_measurecol=>'BIX_PMV_AI_AVGWRAP_CP'
					,p_totalcol=>'BIX_PMV_TOTAL11'
					)
||','
||bix_pmv_dbi_utl_pkg.get_devavg_measure(
					p_percentage=>'N'
					,p_num=>'NVL(c_w, 0)'
					,p_denom=>'NVL(c_tc,0)+NVL(c_cch,0)+
					           NVL(c_ctc,0)+NVL(c_cct,0)'
					,p_col=>'BIX_PMV_AI_AVGWRAP_DEV'
					)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
					p_curr=>'NVL(c_wc,0)'
					,p_measurecol=>'BIX_PMV_AI_WEBCALL_CP'
					,p_totalcol=>'BIX_PMV_TOTAL6'
					)
||','
||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
					p_num =>'to_number(NVL(c_wc,0))'
					,p_measurecol=>'BIX_PMV_AI_WEBCALL_PT'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'N'
					,p_num=>'NVL(c_wc,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_measurecol=>'BIX_PMV_AI_WEBCALL_PAH'
					,p_totalcol=>'BIX_PMV_TOTAL7'
					,p_convunitfordenom=>'/3600'
					)
||','
||bix_pmv_dbi_utl_pkg.get_devavg_measure(
					p_percentage=>'N'
					,p_num=>'NVL(c_wc,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_col=>'BIX_PMV_AI_WEBCALL_PAH_DEV'
	   			    ,p_convunitfordenom=>'/3600'
					)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
					p_curr=>'NVL(c_s,0)'
					,p_measurecol=>'BIX_PMV_AI_SRCR_CP'
					,p_totalcol=>'BIX_PMV_TOTAL13'
					)
||','
||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
					p_num =>'to_number(NVL(c_s,0))'
					,p_measurecol=>'BIX_PMV_AI_SRCR_PT'
					)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
					p_curr=>'NVL(c_l,0)'
					,p_measurecol=>'BIX_PMV_AI_LECR_CP'
					,p_totalcol=>'BIX_PMV_TOTAL14'
					)
||','
||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
					p_num =>'to_number(NVL(c_l,0))'
					,p_measurecol=>'BIX_PMV_AI_LECR_PT'
					)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
					p_curr=>'NVL(c_o,0)'
					,p_measurecol=>'BIX_PMV_AI_OPCR_CP'
					,p_totalcol=>'BIX_PMV_TOTAL15'
					)
||','
||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
					p_num =>'to_number(NVL(c_o,0))'
					,p_measurecol=>'BIX_PMV_AI_OPCR_PT'
					)
||','

||bix_pmv_dbi_utl_pkg.get_simple_measure(
					p_curr=>'NVL(c_mc,0)'
					,p_measurecol=>'BIX_CALC_ITEM1'
					,p_totalcol=>'BIX_CALC_ITEM2'
					)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
					p_curr=>'NVL(p_mc,0)'
					,p_measurecol=>'BIX_CALC_ITEM3'
					,p_totalcol=>'BIX_CALC_ITEM4'
					)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
					p_curr=>'NVL(c_wc,0)'
					,p_measurecol=>'BIX_CALC_ITEM5'
					,p_totalcol=>'BIX_CALC_ITEM6'
					)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
					p_curr=>'NVL(p_wc,0)'
					,p_measurecol=>'BIX_CALC_ITEM7'
					,p_totalcol=>'BIX_CALC_ITEM8'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'Y'
					,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_measurecol=>'BIX_CALC_ITEM9'
					,p_totalcol=>'BIX_CALC_ITEM10'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'Y'
					,p_num=>'NVL(p_lg, 0)-NVL(p_id,0)'
					,p_denom=>'NVL(p_lg,0)'
					,p_measurecol=>'BIX_CALC_ITEM11'
					,p_totalcol=>'BIX_CALC_ITEM12'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'Y'
					,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)-NVL(c_av,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_measurecol=>'BIX_CALC_ITEM13'
					,p_totalcol=>'BIX_CALC_ITEM14'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'Y'
					,p_num=>'NVL(p_lg, 0)-NVL(p_id,0)-NVL(p_av,0)'
					,p_denom=>'NVL(p_lg,0)'
					,p_measurecol=>'BIX_CALC_ITEM15'
					,p_totalcol=>'BIX_CALC_ITEM16'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'N'
					,p_num=>'NVL(c_ic,0)'
					,p_denom=>'NVL(c_lg,0)'
					,p_measurecol=>'BIX_CALC_ITEM17'
					,p_totalcol=>'BIX_CALC_ITEM18'
					,p_convunitfordenom=>'/3600'
					)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
					p_percentage=>'N'
					,p_num=>'NVL(p_ic,0)'
					,p_denom=>'NVL(p_lg,0)'
					,p_measurecol=>'BIX_CALC_ITEM19'
					,p_totalcol=>'BIX_CALC_ITEM20'
					)
;

Line: 375


  l_view_by_select                VARCHAR2 (500);

Line: 376


  l_view_by_select_sess                VARCHAR2 (500);

Line: 409


, p_view_by_select      => l_view_by_select
, p_view_by             => l_view_by
);

Line: 543


, p_view_by_select      => l_view_by_select_sess
, p_view_by             => l_view_by
);

Line: 577


l_sqltext_sess :='(Select
sess.agent_id resource_id,
null c_t,null p_t,
null c_w,null p_w,
null c_ic,null p_ic,
null c_wc,null p_wc,
null c_mc,null p_mc,
null c_tc,null p_tc,
null c_cch,null p_cch,
null c_ctc,null p_ctc,
null c_cct,null p_cct,
null c_l,null p_l,
null c_o,null p_o,
null c_s,null p_s,
c_lg,p_lg,c_id,p_id,c_av,p_av
from ';

Line: 738


l_sqltext :='select * from ('||get_measures|| ' from (('||l_sqltext_cont||l_sqltext_sess||') sess) UNION ALL '||l_sqltext||')'||GET_ZERONULL_CLAUSE;

Line: 759


    'SELECT
        res.resource_name
                 BIX_PMV_AI_AGENT
       ,ROUND(SUM(NVL(fact.login,0))/3600, 1)
                      BIX_PMV_AI_LOGIN_CP
       ,ROUND((SUM(SUM(NVL(fact.login,0))) OVER())/3600, 1)
                      BIX_PMV_TOTAL1
       ,ROUND(SUM(nvl(fact.login,0))*100/
        decode(SUM(SUM(nvl(fact.login,0))) OVER(), 0, NULL,
	          SUM(SUM(nvl(fact.login,0))) OVER()),1) BIX_PMV_AI_LOGIN_PT
       ,ROUND((SUM(NVL(fact.login,0) - NVL(fact.idle, 0)) /
            DECODE(SUM(NVL(fact.login,0)), 0, NULL, SUM(NVL(fact.login,0)))) * 100, 1)
                      BIX_PMV_AI_AVAILRATE_CP
	   ,ROUND(
		  (
			SUM(SUM(NVL(fact.login,0) - NVL(fact.idle, 0))) OVER() /
               DECODE(SUM(SUM(NVL(fact.login,0))) OVER(), 0, NULL,
			    SUM(SUM(NVL(fact.login,0))) OVER()
			      )
            ) * 100
              ,1)
                      BIX_PMV_TOTAL2
       ,ROUND(((SUM(NVL(fact.login,0) - NVL(fact.idle, 0)) /
            DECODE(SUM(NVL(fact.login,0)), 0, NULL, SUM(NVL(fact.login,0)))) * 100)
                      -
       ((SUM(SUM(NVL(fact.login,0) - NVL(fact.idle, 0))) OVER() /
            DECODE(SUM(SUM(NVL(fact.login,0))) OVER(), 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER())) * 100) ,1)  BIX_PMV_AI_AVAILRATE_DEV
       ,ROUND(
		    (
			SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) ) /
               DECODE(SUM(fact.login), 0, NULL, SUM(fact.login))
			) * 100
	      , 1)
                      BIX_PMV_AI_UTILRATE_CP
       ,ROUND(
		    (
		SUM(SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) )) over() /
          DECODE(SUM(SUM(fact.login)) over(), 0, NULL, SUM(SUM(fact.login)) over())
			) * 100
	      , 1) BIX_PMV_TOTAL3
       ,ROUND(
		    (
			SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) ) /
               DECODE(SUM(fact.login), 0, NULL, SUM(fact.login))
			) * 100
       -
		    (
		SUM(SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) )) over() /
          DECODE(SUM(SUM(fact.login)) over(), 0, NULL, SUM(SUM(fact.login)) over())
			) * 100
             ,1) BIX_PMV_AI_UTILRATE_DEV
       ,SUM(NVL(fact.inb_calls,0))
                      BIX_PMV_AI_INCALLHAND_CP
       ,SUM(SUM(NVL(fact.inb_calls,0))) OVER()
                      BIX_PMV_TOTAL4
,ROUND(SUM(NVL(fact.inb_calls,0))*100/
decode(SUM(SUM(NVL(fact.inb_calls,0))) OVER(),0,NULL,
       SUM(SUM(NVL(fact.inb_calls,0))) OVER()),1) BIX_PMV_AI_INCALLHAND_PT
       ,ROUND(SUM(NVL(fact.inb_calls,0)) /
            DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1)
                      BIX_PMV_AI_INCALLHAND_PAH
       ,ROUND(SUM(SUM(NVL(fact.inb_calls,0))) OVER() /
            DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
                      BIX_PMV_TOTAL5
       ,ROUND(SUM(NVL(fact.inb_calls,0)) /
            DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1) -
          ROUND(SUM(SUM(NVL(fact.inb_calls,0))) OVER() /
            DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
                      BIX_PMV_AI_INCALLHAND_PAH_DEV
       ,SUM(NVL(fact.web_calls,0))
                      BIX_PMV_AI_WEBCALL_CP
       ,SUM(SUM(NVL(fact.web_calls,0))) OVER()
                      BIX_PMV_TOTAL6
,ROUND(SUM(NVL(fact.web_calls,0))*100/
DECODE(SUM(SUM(NVL(fact.web_calls,0))) OVER(),0,NULL,
       SUM(SUM(NVL(fact.web_calls,0))) OVER()),1) BIX_PMV_AI_WEBCALL_PT
       ,ROUND(SUM(NVL(fact.web_calls,0)) /
            DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1)
                      BIX_PMV_AI_WEBCALL_PAH
       ,ROUND(SUM(SUM(NVL(fact.web_calls,0))) OVER() /
            DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
                      BIX_PMV_TOTAL7
       ,ROUND(SUM(NVL(fact.web_calls,0)) /
            DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1) -
          ROUND(SUM(SUM(NVL(fact.web_calls,0))) OVER() /
            DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
                      BIX_PMV_AI_WEBCALL_PAH_DEV
       ,SUM(NVL(fact.man_calls,0))
                      BIX_PMV_AI_DIALED_CP
       ,SUM(SUM(NVL(fact.man_calls,0))) OVER()
                      BIX_PMV_TOTAL8
,ROUND(SUM(NVL(fact.man_calls,0))*100/
DECODE(SUM(SUM(NVL(fact.man_calls,0))) OVER(),0,NULL,
       SUM(SUM(NVL(fact.man_calls,0))) OVER()),1) BIX_PMV_AI_DIALED_PT
       ,ROUND(SUM(NVL(fact.man_calls,0)) /
            DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1)
                      BIX_PMV_AI_DIALED_PAH
       ,ROUND(SUM(SUM(NVL(fact.man_calls,0))) OVER() /
            DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
                      BIX_PMV_TOTAL9
       ,ROUND(SUM(NVL(fact.man_calls,0)) /
            DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1) -
          ROUND(SUM(SUM(NVL(fact.man_calls,0))) OVER() /
            DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
                      BIX_PMV_AI_DIALED_PAH_DEV
       ,ROUND(SUM(NVL(fact.talk,0)) /
            DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
              + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
              NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))), 1)
                      BIX_PMV_AI_AVGTALK_CP
       ,ROUND(SUM(SUM(NVL(fact.talk,0))) OVER() /
            DECODE(SUM(SUM(NVL(tot_calls,0) +
					  NVL(fact.cont_calls_hand,0)
                   )) OVER(), 0, NULL,
			    SUM(SUM(NVL(tot_calls,0) +
					  NVL(fact.cont_calls_hand,0)
			    )) OVER()), 1)
                      BIX_PMV_TOTAL10
       ,ROUND(SUM(NVL(fact.talk,0)) /
            DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
              + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
              NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))), 1) -
          ROUND(SUM(SUM(NVL(fact.talk,0))) OVER() /
            DECODE(SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0)
            )) OVER(), 0, NULL, SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) )) OVER()), 1)
                      BIX_PMV_AI_AVGTALK_DEV
       ,ROUND(SUM(NVL(fact.wrap,0)) /
            DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
            + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
            NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))), 1)
                      BIX_PMV_AI_AVGWRAP_CP
       ,ROUND(SUM(SUM(NVL(fact.wrap,0))) OVER() /
            DECODE(SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0)
            )) OVER(), 0, NULL, SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) )) OVER()), 1)
                      BIX_PMV_TOTAL11
       ,ROUND(SUM(NVL(fact.wrap,0)) /
            DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
            + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
            NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))), 1) -
          ROUND(SUM(SUM(NVL(fact.wrap,0))) OVER() /
            DECODE(SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0)
            )) OVER(), 0, NULL, SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) )) OVER()), 1)
                      BIX_PMV_AI_AVGWRAP_DEV
       ,SUM(NVL(fact.sr,0))
                      BIX_PMV_AI_SRCR_CP
       ,SUM(SUM(NVL(fact.sr,0))) OVER()
                      BIX_PMV_TOTAL13
,ROUND(SUM(NVL(fact.sr,0))*100/
DECODE(SUM(SUM(NVL(fact.sr,0))) OVER(),0,NULL,
       SUM(SUM(NVL(fact.sr,0))) OVER()),1) BIX_PMV_AI_SRCR_PT
       ,SUM(NVL(fact.leads,0))
                      BIX_PMV_AI_LECR_CP
       ,SUM(SUM(NVL(fact.leads,0))) OVER()
                      BIX_PMV_TOTAL14
,ROUND(SUM(NVL(fact.leads,0))*100/
DECODE(SUM(SUM(NVL(fact.leads,0))) OVER(),0,NULL,
       SUM(SUM(NVL(fact.leads,0))) OVER()),1) BIX_PMV_AI_LECR_PT
       ,SUM(NVL(fact.oppr,0))
                      BIX_PMV_AI_OPCR_CP
       ,SUM(SUM(NVL(fact.oppr,0))) OVER()
                      BIX_PMV_TOTAL15
,ROUND(SUM(NVL(fact.oppr,0))*100/
DECODE(SUM(SUM(NVL(fact.oppr,0))) OVER(),0,NULL,
       SUM(SUM(NVL(fact.oppr,0))) OVER()),1) BIX_PMV_AI_OPCR_PT
     FROM (
      SELECT
         fact.resource_id
                 agent_id
        ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_talk_time_nac))
                 talk
        ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_wrap_time_nac))
                 wrap
        ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(fact.media_item_type, ''TELE_INB'', agent_calls_handled_total,
               ''TELE_DIRECT'', agent_calls_handled_total, 0)))
                 inb_calls
        ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(fact.media_item_type, ''TELE_WEB_CALLBACK'',
          agent_calls_handled_total, 0)))
                 web_calls
        ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(fact.media_item_type, ''TELE_MANUAL'', agent_calls_handled_total, 0)))
                 man_calls
        ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_calls_handled_total))
                 tot_calls
        ,NULL    cont_calls_hand
        ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_calls_tran_conf_to_nac))
                 calls_tran_conf
        ,NULL    cont_calls_tc
        ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_leads_created))
                 leads
        ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_opportunities_created))
                 oppr
        ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_sr_created))
                 sr
        ,NULL    login
	   ,NULL    idle
        ,NULL    available
      FROM
        bix_ai_call_details_mv fact,
        fii_time_rpt_struct calendar
      WHERE fact.row_type = ''CDR''
	 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 ';

Line: 968


                       SELECT 1
                       FROM   jtf_rs_group_members mem
                       WHERE  fact.resource_id = mem.resource_id
                       AND    mem.group_id IN (:l_agent_group)
                       AND    nvl(mem.delete_flag, ''N'') <> ''Y'' )';

Line: 978


    SELECT
        fact.agent_id
              agent_id
      , NULL  talk
      , NULL  wrap
      , NULL  inb_calls
      , NULL  web_calls
      , NULL  man_calls
      , NULL  tot_calls
      , NULL  cont_calls_hand
      , NULL  calls_tran_conf
      , NULL  cont_calls_tc
      , NULL  leads
      , NULL  oppr
      , NULL  sr
      , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.login_time))
              login
      , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.idle_time))
              idle
      , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.available_time))
              available
    FROM
      bix_agent_session_f fact,
      fii_time_rpt_struct calendar
    WHERE fact.time_id = calendar.time_id
    AND   fact.application_id = 696
    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 ';

Line: 1013


                       SELECT 1
                       FROM   jtf_rs_group_members mem
                       WHERE  fact.agent_id = mem.resource_id
                       AND    mem.group_id IN (:l_agent_group)
                       AND    nvl(mem.delete_flag, ''N'') <> ''Y'' )';

Line: 1023


    SELECT
        fact.resource_id
              agent_id
      , NULL  talk
      , NULL  wrap
      , NULL  inb_calls
      , NULL  web_calls
      , NULL  man_calls
      , NULL  tot_calls
      , SUM(fact.agent_cont_calls_hand_na)
              cont_calls_hand
      , NULL  calls_tran_conf
      , SUM(fact.agent_cont_calls_tc_na)
              cont_calls_tc
      , NULL  leads
      , NULL  oppr
      , NULL  sr
      , NULL  login
	 , NULL  idle
      , NULL  available
    FROM
      bix_ai_call_details_mv fact
    WHERE fact.row_type = ''CDR''
    AND   fact.time_id = TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J''))
    AND   fact.period_type_id = 1 ';

Line: 1054


                       SELECT 1
                       FROM   jtf_rs_group_members mem
                       WHERE  fact.resource_id = mem.resource_id
                       AND    mem.group_id IN (:l_agent_group)
                       AND    nvl(mem.delete_flag, ''N'') <> ''Y'' )';