APPS.BIX_REAL_TIME_BINS_PKG SQL Statements

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

Line: 22

 Select count(C1.MEDIA_ITEM_ID)  calls_waiting,
        nvl(sum(sysdate - C1.last_update_date)*24*3600/decode(count(C1.MEDIA_ITEM_ID),0,1,null,1,count(C1.media_item_id)),0)
	   avg_queue_time,
	   C1.classification classification
 from CCT_MEDIA_ITEMS C1
 where C1.status = 1
 and   media_type = 0
 and C1.creation_date between l_date_low and l_date_high
 group by classification;

Line: 40

/* delete data from previous runs */
delete from bix_dm_bin
where session_id = g_session_id
and bin_code = 'BIX_QUEUE_STATUS';

Line: 50

select count(classification_id)
into   l_count
from   cct_classifications
where  classification = rec.classification;

Line: 56

 select classification_id
 into   l_classification_id
 from   cct_classifications
 where  classification = rec.classification;

Line: 66

/* insert the calls in queue in table for the reporting */
insert into bix_dm_bin (bin_code, session_id, col1, col2, col4, col6)
Values ('BIX_QUEUE_STATUS',g_session_id, l_classification_id, rec.classification,
         rec.calls_waiting,l_timestring);

Line: 101

/* delete data from pervious runs */
delete from bix_dm_bin
where session_id = g_session_id
and bin_code = 'BIX_AGENT_STATUS';

Line: 107

select count(distinct I1.resource_id) into available
from IEU_SH_SESSIONS I1,
     IEU_SH_ACTIVITIES I2
where I1.session_id = I2.session_id
	 and I1.application_id = 696
	 and I1.active_flag ='T'
      and I1.end_date_time is NULL
      and I2.activity_type_code = 'MEDIA'
      and I2.active_flag = 'T'
      and I2.end_date_time is NULL
	 and I2.deliver_date_time is NULL
      and I1.begin_date_time between l_date_low and l_date_high;

Line: 121

Select count(distinct C1.agent_id) into talk
from CCT_AGENT_RT_STATS C1
where C1.has_call = 'T'
      and C1.last_update_date between  l_date_low and l_date_high;

Line: 127

Select count(distinct I1.resource_id) into wrap
from IEU_SH_SESSIONS I1,
     IEU_SH_ACTIVITIES I2,
     CCT_AGENT_RT_STATS C1
where I1.session_id  = I2.session_id
	 and I1.application_id = 696
      and I2.active_flag ='T'
	 and I2.activity_type_code = 'MEDIA'
	 and I2.end_date_time is null
	 and I2.deliver_date_time is not null
      and I1.resource_id = C1.agent_id
      and C1.has_call = 'F'
      and I1.begin_date_time between l_date_low and l_date_high;

Line: 142

Select count(distinct I1.resource_id) into loggedin
from IEU_SH_SESSIONS I1
where I1.active_flag = 'T'
	 and I1.application_id = 696
      and I1.end_date_time is NULL
      and I1.begin_date_time between l_date_low and l_date_high;

Line: 150

Select count(distinct I1.resource_id) into out
from IEU_SH_SESSIONS I1
where I1.active_flag <> 'T'
	 and I1.application_id = 696
      and I1.end_date_time is not NULL
      and I1.begin_date_time between l_date_low and l_date_high
      and I1.resource_id not in(
		select distinct(resource_id) from IEU_SH_SESSIONS
		where  active_flag = 'T'
		and    application_id = 696
		and    end_date_time is null
		and    begin_date_time between l_date_low and l_date_high
   	);

Line: 167

/* insert the agent data into table for reporting */
insert into bix_dm_bin (bin_code, session_id, col1, col2, col4)
values ('BIX_AGENT_STATUS', g_session_id, '2', 'Available', available);

Line: 171

insert into bix_dm_bin (bin_code,  session_id, col1, col2, col4)
values ('BIX_AGENT_STATUS', g_session_id, '3', 'Talk', talk);

Line: 174

insert into bix_dm_bin (bin_code,  session_id, col1, col2, col4)
values ('BIX_AGENT_STATUS', g_session_id, '4', 'Wrap', wrap);

Line: 177

insert into bix_dm_bin (bin_code,  session_id, col1, col2, col4)
values ('BIX_AGENT_STATUS', g_session_id, '5', 'Out', out);

Line: 180

insert into bix_dm_bin (bin_code, session_id, col1, col2, col4)
values ('BIX_AGENT_STATUS', g_session_id, '1', 'Idle', idle);