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

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

Line: 13


select count(classification_id)
into   v_classification_count
from   cct_classifications
where classification = p_classification;

Line: 21


  select classification_id into v_classification_id from cct_classifications where classification = p_classification;

Line: 59


 SELECT sum(DECODE(UPPER(ih_mitem.direction),'INBOUND',1,0)) CALLS_OFFERED,
        sum(DECODE(UPPER(ih_mitem.direction),'INBOUND',DECODE(UPPER(ih_mitem.media_abandon_flag),'Y',1,0),0)) CALLS_ABANDONED,
	ih_mitem.server_group_id SERVER_GROUP_ID,
        ih_mitem.classification CLASSIFICATION,
        TRUNC(ih_mitem.start_date_time) PERIOD_START_DATE,
        LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00') PERIOD_START_TIME,
	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0') || DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI') PERIOD_START_DATE_TIME
   FROM      JTF_IH_MEDIA_ITEMS ih_mitem
 WHERE  ih_mitem.start_date_time BETWEEN l_start_date and l_end_date
 AND
 (
 ih_mitem.media_item_type = 'TELE_INB' or
 ih_mitem.media_item_type = 'TELE_DIRECT' or
 ih_mitem.media_item_type = 'TELE_MANUAL' or
 ih_mitem.media_item_type = 'TELE_WEB'
 )
 AND    ih_mitem.active = 'N'
GROUP BY ih_mitem.server_group_id,
         ih_mitem.classification,
         TRUNC(ih_mitem.start_date_time),
         LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),
	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0') || DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI');

Line: 87


SELECT		SUM(NVL(msegs.duration,0)) ABANDON_TIME
FROM   jtf_ih_media_items ih_mitem,
       JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
       JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps
where ih_mitem.server_group_id =  p_server_group_id
and   ih_mitem.classification = p_classification
and   TRUNC(ih_mitem.start_date_time) = p_period_start_date
and   LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00') = p_period_start_time
and 	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0') ||
DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI') = p_period_start_date_time
and  ih_mitem.media_id = msegs.media_id
and  mtyps.milcs_type_id = msegs.milcs_type_id
and  mtyps.milcs_code = 'IN_QUEUE'
and  ih_mitem.direction = 'INBOUND'
and  ih_mitem.media_abandon_flag = 'Y' ;

Line: 109


SELECT count(distinct msegs.media_id) CALLS_ANSWRD_WITHIN_X_TIME
FROM   jtf_ih_media_items ih_mitem,
       JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
       JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps
where ih_mitem.server_group_id =  p_server_group_id
and   ih_mitem.classification = p_classification
and   TRUNC(ih_mitem.start_date_time) = p_period_start_date
and   LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00') = p_period_start_time
and 	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0') ||
DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI') = p_period_start_date_time
and  ih_mitem.media_id = msegs.media_id
and  mtyps.milcs_type_id = msegs.milcs_type_id
and  mtyps.milcs_code = 'IN_QUEUE'
and  ih_mitem.direction = 'INBOUND'
and  msegs.duration <= v_goal
and (ih_mitem.media_abandon_flag = 'N' or ih_mitem.media_abandon_flag is null);

Line: 131


SELECT		SUM(NVL(msegs.duration,0)) queue_time_answered,
                count(distinct(ih_mitem.media_id)) calls_answered
FROM   jtf_ih_media_items ih_mitem,
       JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
       JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps
where ih_mitem.server_group_id =  p_server_group_id
and   ih_mitem.classification = p_classification
and   ih_mitem.active = 'N'
and   TRUNC(ih_mitem.start_date_time) = p_period_start_date
and   LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00') = p_period_start_time
and 	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0') ||
DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI') = p_period_start_date_time
and  ih_mitem.media_id = msegs.media_id
and  mtyps.milcs_type_id = msegs.milcs_type_id
and  mtyps.milcs_code = 'IN_QUEUE'
and  ih_mitem.direction = 'INBOUND'
and (ih_mitem.media_abandon_flag = 'N' or ih_mitem.media_abandon_flag is null);

Line: 154


SELECT		SUM(NVL(msegs.duration,0)) talk_time,
                count(distinct(msegs.media_id)) calls_handled
FROM   jtf_ih_media_items ih_mitem,
       JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
       JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps
where ih_mitem.server_group_id =  p_server_group_id
and   ih_mitem.classification = p_classification
and   ih_mitem.active = 'N'
and   TRUNC(ih_mitem.start_date_time) = p_period_start_date
and   LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00') = p_period_start_time
and 	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')
|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI') = p_period_start_date_time
and  ih_mitem.media_id = msegs.media_id
and  mtyps.milcs_type_id = msegs.milcs_type_id
and  mtyps.milcs_code = 'WITH_AGENT'
and  ih_mitem.direction = 'INBOUND';

Line: 172


IS  SELECT goals.SL_SECONDS_GOAL goal from bix_dm_goals goals
where goals.call_type_id = -999
and  goals.end_date_active is null;

Line: 177


IS   SELECT goals.SL_SECONDS_GOAL goal from bix_dm_goals goals
where goals.call_type_id = v_classification_id
and  goals.end_date_active is null;

Line: 184


delete from bix_dm_real_queue_sum
where session_id = p_session_id;

Line: 229


    insert into BIX_DM_REAL_QUEUE_SUM
		(
		calls_offered,
		calls_abandoned,
		abandon_time,
		talk_time,
		calls_answrd_within_x_time,
		queue_time_answered,
		calls_answered,
		calls_handled,
		server_group_id,
		classification,
		classification_id,
		period_start_date,
		period_start_time,
		period_start_date_time,
		session_id
		)
		values
		(
		v_calls_offered,
		v_calls_abandoned,
		v_abandon_time,
		decode(v_talk_time, NULL, 0, v_talk_time),
		decode(v_calls_answrd_within_x_time, NULL, 0, v_calls_answrd_within_x_time),
		v_queue_time_answered,
		v_calls_answered,
		v_calls_handled,
		v_server_group_id,
		v_classification,
		v_classification_id,
		v_period_start_date,
		v_period_start_time,
		v_period_start_date_time,
		p_session_id
		);