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

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

Line: 10


/* inserts data into bix_dm_report table used for reporting */
/* called by populate(p_context) procedure                  */
PROCEDURE insert_temp_table(p_classification_id IN NUMBER,
                            p_site_id     IN NUMBER,
                            p_start_period IN DATE,
                            p_end_period   IN DATE)
IS
  l_index NUMBER;

Line: 24


  select distinct c.classification_id, c.classification
  from   cct_classifications c, bix_dm_real_queue_sum b
  where   ((c.classification_id = p_classification_id) or (p_classification_id is null or p_classification_id = -999))
  and b.classification_id = c.classification_id;

Line: 34


/* delete data from the previous runs */
delete bix_dm_report
where  session_id = g_session_id
and report_code = 'BIX_QUEUE_DETAIL_REPORT';

Line: 40


select count(*)
into   l_unclassified_count
from   bix_dm_real_queue_sum
where  (classification_id is null or
         classification_id not in
	 (select distinct classification_id
	  from cct_classifications));

Line: 49


  /* insert data for all classified calls for the given parameters */
  for classifications in get_classifications LOOP
	v_classification_id := classifications.classification_id;

Line: 53


	/* insert summary level data */
      INSERT INTO bix_dm_report(
			 report_code
                , session_id
			 ,  col1
			 ,  col2
			 ,  col3
			 ,  col4
			 ,  col6
			 ,  col8
			 ,  col10
			 ,  col12
			 ,  col14
			 ,  col16)
      (SELECT
			 'BIX_QUEUE_DETAIL_REPORT'
                         , g_session_id
			 , 'c' || to_char(v_classification_id) || 'd' || '0'
			 , v_classification
			 , l_index
                ,  NULL
			 , decode(SUM(CALLS_OFFERED), NULL, 0, SUM(CALLS_OFFERED))
			 , decode(SUM(CALLS_ABANDONED), NULL, 0, SUM(CALLS_ABANDONED))
			 , decode(SUM(CALLS_ANSWRD_WITHIN_X_TIME), NULL, 0, SUM(CALLS_ANSWRD_WITHIN_X_TIME))
			 , bix_util_pkg.get_hrmiss_frmt(SUM(ABANDON_TIME)/SUM(CALLS_ABANDONED))
			 ,bix_util_pkg.get_hrmiss_frmt(SUM(QUEUE_TIME_ANSWERED)/decode(SUM(calls_answered), 0, 1, SUM(calls_answered)))
			 ,bix_util_pkg.get_hrmiss_frmt(SUM(talk_time)/ DECODE(SUM(calls_handled),0,1,SUM(calls_handled)))
        from bix_dm_real_queue_sum
	   where classification_id = v_classification_id
	   and   session_id = g_session_id
	   and  period_start_date_time between p_start_period and p_end_period
  and ((server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999)));

Line: 85


  /* insert detailed rows into the bix_dm_report if drilldown clicked */
  if (g_classification_id <> -999 and g_drilldown = 0) THEN
      l_index := l_index + 1;

Line: 88


      INSERT INTO bix_dm_report(
			 report_code
                         , session_id
			 , col1
			 ,  col2
			 ,  col3
			 ,  col4
			 ,  col6
			 ,  col8
			 ,  col10
			 ,  col12
			 ,  col14
			 ,  col16)
      (SELECT
			 'BIX_QUEUE_DETAIL_REPORT'
                         , g_session_id
			 , l_index
			 , null
			 , l_index
                , decode(g_time_range,1,to_char(to_date(period_start_time,'hh24:mi'), 'hh24:miAM'),
 2,to_char(to_date(substr(period_start_time,1,2),'hh24:mi'), 'hh24:miAM'),
    3,to_char(to_date(floor(substr(period_start_time,1,2) / 2) * 2, 'hh24:mi' ), 'hh24:miAM') ,
 4,to_char(to_date(floor(substr(period_start_time,1,2) / 4) * 4, 'hh24:mi'), 'hh24:miAM'), period_start_date_time)
			 , decode(sum(CALLS_OFFERED), NULL, 0, sum(CALLS_OFFERED))
			 , decode(sum(CALLS_ABANDONED), NULL, 0,sum(CALLS_ABANDONED))
			 , decode(sum(CALLS_ANSWRD_WITHIN_X_TIME), NULL, 0,sum(CALLS_ANSWRD_WITHIN_X_TIME))
			 , bix_util_pkg.get_hrmiss_frmt(SUM(ABANDON_TIME)/SUM(CALLS_ABANDONED))
			 ,bix_util_pkg.get_hrmiss_frmt(SUM(QUEUE_TIME_ANSWERED)/decode(SUM(calls_answered), 0, 1, SUM(calls_answered)))
			 ,bix_util_pkg.get_hrmiss_frmt(SUM(talk_time)/ DECODE(SUM(calls_handled),0,1,SUM(calls_handled)))
        from bix_dm_real_queue_sum
	   where classification_id = v_classification_id
	   and   session_id = g_session_id
	   and   classification_id = g_classification_id
	   and  period_start_date_time between p_start_period and p_end_period
  and ((server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999))
             GROUP BY decode(g_time_range,1,to_char(to_date(period_start_time,'hh24:mi'), 'hh24:miAM'),
 2,to_char(to_date(substr(period_start_time,1,2),'hh24:mi'), 'hh24:miAM'),
    3,to_char(to_date(floor(substr(period_start_time,1,2) / 2) * 2, 'hh24:mi' ), 'hh24:miAM') ,
 4,to_char(to_date(floor(substr(period_start_time,1,2) / 4) * 4, 'hh24:mi'), 'hh24:miAM'), period_start_date_time));

Line: 127


		 /* update the drilldown to contract on click */
		 /* g_drilldown  = 1 implies contract         */
		 update bix_dm_report
		 set  col1 =  'c' || to_char(v_classification_id) || 'd' || '1'
		 where report_code  = 'BIX_QUEUE_DETAIL_REPORT'
		 and session_id = g_session_id
		 and  col2 = v_classification
		 and  v_classification = g_classification;

Line: 142


  /* insert summary level data into table only if no classification */
  /* is selected 									       */
  IF (p_classification_id = -999 or p_classification_id is NULL) then
      INSERT INTO bix_dm_report(
			 report_code
                , session_id
			 ,  col1
			 ,  col2
			 ,  col3
			 ,  col4
			 ,  col6
			 ,  col8
			 ,  col10
			 ,  col12
			 ,  col14
			 ,  col16)
      (SELECT
			 'BIX_QUEUE_DETAIL_REPORT'
                , g_session_id
			 , 'c' || to_char(-9999) || 'd' || '0'
	--		 ,g_null_desc
			 ,'unClassified'
			 , l_index
                ,  NULL
			 , decode(SUM(CALLS_OFFERED), NULL, 0, SUM(CALLS_OFFERED))
			 , decode(SUM(CALLS_ABANDONED), NULL, 0, SUM(CALLS_ABANDONED))
			 , decode(SUM(CALLS_ANSWRD_WITHIN_X_TIME), NULL, 0, SUM(CALLS_ANSWRD_WITHIN_X_TIME))
			 , bix_util_pkg.get_hrmiss_frmt(SUM(ABANDON_TIME)/SUM(CALLS_ABANDONED))
			 ,bix_util_pkg.get_hrmiss_frmt(SUM(QUEUE_TIME_ANSWERED)/decode(SUM(calls_answered), 0, 1, SUM(calls_answered)))
			 ,bix_util_pkg.get_hrmiss_frmt(SUM(talk_time)/ DECODE(SUM(calls_handled),0,1,SUM(calls_handled)))
        from bix_dm_real_queue_sum
        where ( classification_id is null or classification_id not in
		   (select distinct classification_id
		    from cct_classifications))
	   and  period_start_date_time between p_start_period and p_end_period
	   and   session_id = g_session_id
  and ((server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999)));

Line: 180


  /* insert detailed rows for unclassified calls only if drilldown clicked */
  if ((g_classification_id = -9999 and g_drilldown = 0) and (p_classification_id = -999 or p_classification_id is null)) THEN
      l_index := l_index + 1;

Line: 183


      INSERT INTO bix_dm_report(
			 report_code
                , session_id
			 , col1
			 ,  col2
			 ,  col3
			 ,  col4
			 ,  col6
			 ,  col8
			 ,  col10
			 ,  col12
			 ,  col14
			 ,  col16)
      (SELECT
			 'BIX_QUEUE_DETAIL_REPORT'
                , g_session_id
			 , l_index
			 , null
			 , l_index
                , decode(g_time_range,1,to_char(to_date(period_start_time,'hh24:mi'), 'hh24:miAM'),
 2,to_char(to_date(substr(period_start_time,1,2),'hh24:mi'), 'hh24:miAM'),
    3,to_char(to_date(floor(substr(period_start_time,1,2) / 2) * 2, 'hh24:mi' ), 'hh24:miAM') ,
 4,to_char(to_date(floor(substr(period_start_time,1,2) / 4) * 4, 'hh24:mi'), 'hh24:miAM'), period_start_date_time)
			 , decode(sum(CALLS_OFFERED), NULL, 0, sum(CALLS_OFFERED))
			 , decode(sum(CALLS_ABANDONED), NULL, 0,sum(CALLS_ABANDONED))
			 , decode(sum(CALLS_ANSWRD_WITHIN_X_TIME), NULL, 0,sum(CALLS_ANSWRD_WITHIN_X_TIME))
			 , bix_util_pkg.get_hrmiss_frmt(SUM(ABANDON_TIME)/SUM(CALLS_ABANDONED))
			 ,bix_util_pkg.get_hrmiss_frmt(SUM(QUEUE_TIME_ANSWERED)/decode(SUM(calls_answered), 0, 1, SUM(calls_answered)))
			 ,bix_util_pkg.get_hrmiss_frmt(SUM(talk_time)/ DECODE(SUM(calls_handled),0,1,SUM(calls_handled)))
        from bix_dm_real_queue_sum
	   where (classification_id is null or classification_id not in
	       (select distinct classification_id
		   from cct_classifications))
        and session_id = g_session_id
	   and  period_start_date_time between p_start_period and p_end_period
  and ((server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999))
             GROUP BY decode(g_time_range,1,to_char(to_date(period_start_time,'hh24:mi'), 'hh24:miAM'),
 2,to_char(to_date(substr(period_start_time,1,2),'hh24:mi'), 'hh24:miAM'),
    3,to_char(to_date(floor(substr(period_start_time,1,2) / 2) * 2, 'hh24:mi' ), 'hh24:miAM') ,
 4,to_char(to_date(floor(substr(period_start_time,1,2) / 4) * 4, 'hh24:mi'), 'hh24:miAM'), period_start_date_time));

Line: 223


		 /* update the drilldown to contract on click */
		 /* g_drilldown  = 1 implies contract         */
		 update bix_dm_report
		 set  col1 =  'c' || to_char(-9999) || 'd' || '1'
		 where report_code  = 'BIX_QUEUE_DETAIL_REPORT'
		 and session_id = g_session_id
		 and  col2 = g_null_desc;

Line: 233


delete from bix_dm_real_queue_sum
where session_id = g_session_id;

Line: 237


END insert_temp_table;

Line: 241


/* insert_temp_table procedure                         */
PROCEDURE populate(p_context IN VARCHAR2)
IS
v_classification_id NUMBER;

Line: 257


  SELECT fnd_profile.value('BIX_DM_RPT_TIME_RANGE')
  INTO   g_time_range
  FROM   dual;

Line: 261


  select count(meaning)
  into   l_null_desc_count
  FROM   fnd_lookups
  WHERE  lookup_type = 'BIX_DM_NULL_DESC'
  AND    lookup_code = 'NULL';

Line: 268


   SELECT meaning
   INTO   g_null_desc
   FROM   fnd_lookups
   WHERE  lookup_type = 'BIX_DM_NULL_DESC'
   AND    lookup_code = 'NULL';

Line: 296


	select classification_name
	into   g_classification
	from   bix_dm_classification_param_v
	where  to_number(classification_id) = g_classification_id;

Line: 321


  /* insert data into bix_dm_report */
  insert_temp_table(v_classification_id, v_site_id, v_start_period, v_end_period);

Line: 336


   select max(period_start_date_time)
   into l_date
   from bix_dm_real_queue_sum;