APPS.BIX_REAL_TIME_RPTS_PKG SQL Statements

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

Line: 50

select classification className
from cct_classifications
where
       classification_id = v_classification_id;

Line: 58

Select count(distinct(C.MEDIA_ITEM_ID)) calls,
	  C.classification class
from CCT_MEDIA_ITEMS C
where C.status = 1
and   media_type = 0
and   C.creation_date between l_date_low and l_date_high
and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
group by  C.classification
order by  count(C.MEDIA_ITEM_ID);

Line: 76

  delete from BIX_DM_REPORT
  where session_id = l_session_id
  and report_code =  'BIX_QUEUE_STATUS_RPT';

Line: 107

  /* If the user has selected "All" for agent group paramter , display the default group
 of the user */
  IF (v_group_id = -999) THEN
    SELECT fnd_profile.value('BIX_DM_DEFAULT_GROUP')
    INTO   v_group_id
    FROM   dual;

Line: 115

  /* or the user has selected "all" as agent group paramter and (s)he is not assigned to
 any default group */
  IF (v_group_id IS NULL) THEN
    RETURN;

Line: 130

Select count(distinct(C.MEDIA_ITEM_ID))
into calls_range1
from CCT_MEDIA_ITEMS C
where C.status = 1
and   C.media_type = 0
and   C.creation_date between l_date_low and l_date_high
and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
and (C.classification = v_classification or (v_classification is null))
and (sysdate - C.last_update_date) * 24 * 60 <= 10;

Line: 141

Select count(distinct(C.MEDIA_ITEM_ID))
into calls_range2
from CCT_MEDIA_ITEMS C
where C.status = 1
and   C.media_type = 0
and   C.creation_date between l_date_low and l_date_high
and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
and (C.classification = v_classification or (v_classification is null))
and (sysdate - C.last_update_date) * 24 * 60 <= 20
and (sysdate - C.last_update_date) * 24 * 60 > 10;

Line: 153

Select count(distinct(C.MEDIA_ITEM_ID))
into calls_range3
from CCT_MEDIA_ITEMS C
where C.status = 1
and   media_type = 0
and   C.creation_date between l_date_low and l_date_high
and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
and (C.classification = v_classification or (v_classification is null))
and (sysdate - last_update_date) * 24 * 60 <= 30
and (sysdate - last_update_date) * 24 * 60 > 20;

Line: 166

Select count(distinct(C.MEDIA_ITEM_ID))
into calls_range4
from CCT_MEDIA_ITEMS C
where C.status = 1
and   media_type = 0
and   C.creation_date between l_date_low and l_date_high
and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
and (C.classification = v_classification or (v_classification is null))
and (sysdate - last_update_date) * 24 * 60 <= 40
and (sysdate - last_update_date) * 24 * 60 > 30;

Line: 178

Select count(distinct(C.MEDIA_ITEM_ID))
into calls_range5
from CCT_MEDIA_ITEMS C
where C.status = 1
and   media_type = 0
and   C.creation_date between l_date_low and l_date_high
and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
and (C.classification = v_classification or (v_classification is null))
and (sysdate - last_update_date) * 24 * 60 <= 50
and (sysdate - last_update_date) * 24 * 60 > 40;

Line: 190

Select count(distinct(C.MEDIA_ITEM_ID))
into calls_range6
from CCT_MEDIA_ITEMS C
where C.status = 1
and   media_type = 0
and   C.creation_date between l_date_low and l_date_high
and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
and (C.classification = v_classification or (v_classification is null))
and (sysdate - last_update_date) * 24 * 60 <= 60
and (sysdate - last_update_date) * 24 * 60 > 50;

Line: 202

Select count(distinct(C.MEDIA_ITEM_ID))
into calls_range7
from CCT_MEDIA_ITEMS C
where C.status = 1
and   media_type = 0
and   C.creation_date between l_date_low and l_date_high
and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
and (C.classification = v_classification or (v_classification is null))
and (sysdate - last_update_date) * 24 * 60 > 60;

Line: 213

select count(distinct I1.resource_id) into talk_graph
from IEU_SH_SESSIONS I1,
     IEU_SH_ACTIVITIES I2,
     CCT_media_items M,
     JTF_RS_GROUP_MEMBERS J4,
	CCT_AGENT_RT_STATS C1
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 not null
	 and C1.agent_id = I1.resource_id
	 and C1.has_call = 'T'
      and I1.begin_date_time between l_date_low and l_date_high
      and M.media_item_id = I2.media_id
      and I1.resource_id= J4.resource_id
      and (J4.group_id = v_group_id or v_group_id=-999)
      and (M.server_group_id= v_site_id or v_site_id=-999)
      and (M.classification = l_className or v_classification_id=-999);

Line: 239

Select count(distinct I1.resource_id) into wrap_graph
from IEU_SH_SESSIONS I1,
     IEU_SH_ACTIVITIES I2,
     CCT_AGENT_RT_STATS C1,
     CCT_media_items M,
     JTF_RS_GROUP_MEMBERS J4
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
       and M.media_item_id = I2.media_id
       and C1.agent_id= J4.resource_id
       and (J4.group_id = v_group_id or v_group_id=-999)
       and (M.server_group_id= v_site_id or v_site_id=-999)
       and (M.classification = l_className or v_classification_id=-999);

Line: 269

Select count(distinct(C.MEDIA_ITEM_ID)) into callsWaiting
from CCT_MEDIA_ITEMS C
where C.status = 1
and   media_type = 0
and   C.creation_date between l_date_low and l_date_high
and (C.server_group_id= v_site_id or v_site_id=-999)
and (C.classification = l_className or v_classification_id=-999);

Line: 288

Select max(l_date_high- C.last_update_date)*24*3600 into longestCallWaiting
from CCT_MEDIA_ITEMS C
where C.status = 1
and   media_type = 0
and   C.creation_date between l_date_low and l_date_high
and (C.server_group_id= v_site_id or v_site_id=-999)
and (C.classification = l_className or v_classification_id=-999);

Line: 297

select sum(l_date_high- C.last_update_date)*24*3600 into totalWaitingTime
from CCT_MEDIA_ITEMS C
where C.status = 1
and   media_type = 0
and   C.creation_date between l_date_low and l_date_high
and (C.server_group_id= v_site_id or v_site_id=-999)
and (C.classification = l_className or v_classification_id=-999);

Line: 320

SELECT sum(DECODE(UPPER(ih_mitem.direction),'INBOUND',1,0)) ,
	  sum(DECODE(UPPER(ih_mitem.direction),'INBOUND',DECODE(UPPER(ih_mitem.media_abandon_flag),'Y',1,0),0))
into callsOffered, abandonCalls
FROM      JTF_IH_MEDIA_ITEMS ih_mitem
WHERE  ih_mitem.start_date_time BETWEEN l_date_low and l_date_high
 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'
AND    (ih_mitem.classification = v_classification or v_classification is null)
AND    (ih_mitem.server_group_id = v_site_id or (v_site_id=-999 or v_site_id is null));

Line: 336

SELECT  SUM(DECODE(SIGN(msegs.duration -  goals.SL_SECONDS_GOAL),0,1,-1,1,0))
into callsSrvLevel
FROM   jtf_ih_media_items ih_mitem,
	  JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
       JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps,
        bix_dm_goals goals
where (ih_mitem.server_group_id =  v_site_id or (v_site_id is null or v_site_id = -999))
and   goals.call_type_id = v_classification_id
and   (ih_mitem.classification = v_classification or (v_classification is null))
and   ih_mitem.start_date_time BETWEEN l_date_low and l_date_high
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  goals.end_date_active is null;

Line: 352

SELECT SUM(msegs.duration)/count(distinct(msegs.media_id))
INTO   avgAbandonTime
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.start_date_time BETWEEN l_date_low and l_date_high
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'
and  ih_mitem.active = 'N'
and   (ih_mitem.classification = v_classification or (v_classification is null))
and (ih_mitem.server_group_id =  v_site_id or (v_site_id is null or v_site_id = -999));

Line: 367

SELECT   sum(msegs.duration)/count(distinct(msegs.media_id))
INTO   avgTalkTime
FROM   jtf_ih_media_items ih_mitem,
	  JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
       JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps,
       JTF_RS_GROUP_MEMBERS        gp
where   ih_mitem.start_date_time BETWEEN l_date_low and l_date_high
and    ih_mitem.media_id = msegs.media_id
and  mtyps.milcs_type_id = msegs.milcs_type_id
and  ih_mitem.active = 'N'
and   (ih_mitem.classification = v_classification or (v_classification is null ))
and (ih_mitem.server_group_id =  v_site_id or (v_site_id is null or v_site_id = -999))
and  mtyps.milcs_code = 'WITH_AGENT'
and gp.resource_id = msegs.resource_id
and  (gp.group_id = v_group_id  or (v_group_id = -999 or v_group_id is null));

Line: 383

SELECT   sum(msegs1.duration)/count(distinct(msegs1.media_id))
INTO   avgSpeedToAnswer
FROM   jtf_ih_media_items ih_mitem,
	  JTF_IH_MEDIA_ITEM_LC_SEGS msegs1,
	  JTF_IH_MEDIA_ITEM_LC_SEGS msegs2,
       JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps1,
       JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps2,
       JTF_RS_GROUP_MEMBERS        gp
where   ih_mitem.start_date_time BETWEEN l_date_low and l_date_high
and    ih_mitem.media_id = msegs1.media_id
and    ih_mitem.media_id = msegs2.media_id
and  mtyps1.milcs_type_id = msegs1.milcs_type_id
and  mtyps2.milcs_type_id = msegs2.milcs_type_id
and  ih_mitem.active = 'N'
and   (ih_mitem.classification = v_classification or (v_classification is null ))
and (ih_mitem.server_group_id =  v_site_id or (v_site_id is null or v_site_id = -999))
and  mtyps1.milcs_code = 'IN_QUEUE'
and  mtyps2.milcs_code = 'WITH_AGENT'
and  gp.resource_id = msegs2.resource_id
and  (gp.group_id = v_group_id or (v_group_id = -999 or v_group_id is null));

Line: 418

/* insert data into table for reporting */

/* for parameter passing */
pstring := 'G' || to_char(v_group_id) || 'C' ||to_char(v_classification_id) || 'S' || to_char(v_site_id) ;

Line: 424

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8, col10, col12, col14, col16, col18, col20, col22, col24)
values (l_session_id,'BIX_QUEUE_STATUS_RPT','1' || pstring , 'Calls Waiting', callsWaiting,
	   'Calls Offered', callsOffered,'', calls_range1, calls_range2, calls_range3, calls_range4, calls_range5, calls_range6, calls_range7);

Line: 430

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
values (l_session_id,'BIX_QUEUE_STATUS_RPT','2' || pstring, 'Longest Call Waiting', l_string2,
	   'Abandon Calls', abandonCalls, null, null);

Line: 436

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
values (l_session_id,'BIX_QUEUE_STATUS_RPT','3' || pstring, 'Average Queue Time', l_string2,
 'Calls within Service Level', callsSrvLevel,null, null);

Line: 442

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
values (l_session_id,'BIX_QUEUE_STATUS_RPT','4'|| pstring, 'Talking Agents', talk_graph,
'Average abandon Time', l_string, null, null);

Line: 449

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
values (l_session_id,'BIX_QUEUE_STATUS_RPT','5',  '', null,
	   'Average Speed To Answer', l_string,null, null);

Line: 455

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
values (l_session_id,'BIX_QUEUE_STATUS_RPT','6',  '', null,
        'Average Talk Time', l_string,null, null);

Line: 462

 select bix_util_pkg.get_null_lookup into l_string2 from dual;

Line: 469

    update BIX_DM_REPORT set col10=l_string, col12=calls_range1
    where col1 like to_char(i) || '%';

Line: 471

    else insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
	    values (l_session_id,'BIX_QUEUE_STATUS_RPT', i ,null,null,null,null,l_string,rec.calls);

Line: 506

select sum(decode(end_date_time, null, l_date_high-begin_date_time,
end_date_time-begin_date_time))*3600*24 into loggedin
from ieu_sh_sessions
where resource_id = v_agent_id
      and begin_date_time between l_date_low and l_date_high
      and application_id = 696;

Line: 519

select sum(decode(I1.deliver_date_time,null,l_date_high-I1.begin_date_time,
       I1.deliver_date_time-I1.begin_date_time))*3600*24 into available
from ieu_sh_activities I1,
     ieu_sh_sessions I2
where I1.session_id = I2.session_id
      and I2.resource_id = v_agent_id
      and I2.begin_date_time between l_date_low and l_date_high
      and I2.application_id = 696
      /* and I1.deliver_date_time is not null */
      and I1.activity_type_code = 'MEDIA';

Line: 537

select sum(J1.duration) *3600*24 into talk_past
from jtf_ih_media_item_lc_segs J1,
     jtf_ih_media_itm_lc_seg_tys J2,
     jtf_ih_media_items J3
where J1.resource_id = v_agent_id
      and J1.milcs_type_id = J2.milcs_type_id
      and J2.milcs_code = 'WITH_AGENT'
      and J1.start_date_time between l_date_low and l_date_high
      and J3.media_id = J1.media_id
      and J3.media_item_type = 'TELEPHONE';

Line: 548

select sum(l_date_high-I2.deliver_date_time)*3600*24 into talk_now
from ieu_sh_activities I2,
     ieu_sh_sessions I1
where I1.session_id = I2.session_id
      and I1.resource_id = v_agent_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 not null
      and I1.begin_date_time between l_date_low and l_date_high;

Line: 568

Select sum(l_date_high - C1.last_update_date)*24*3600 into wrap_now
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 I1.resource_id = v_agent_id
      and C1.has_call = 'F'
      and I1.begin_date_time between l_date_low and l_date_high;

Line: 585

 select sum(J1.end_date_time - J2.end_date_time)*24*3600 into wrap_past
 from jtf_ih_media_item_lc_segs J2,
      jtf_ih_interactions J1,
      jtf_ih_media_itm_lc_seg_tys J3
 where J1.resource_id        = v_agent_id
       and J1.resource_id    = J2.resource_id
       and J2.media_id       = J1.productive_time_amount
       and J2.milcs_type_id  = J3.milcs_type_id
       and J3.milcs_code     = 'WITH_AGENT'
       and J1.start_date_time  between l_date_low and l_date_high;

Line: 609

/* utility procedure to insert data for agent time spent graph in agent */
/* detail report                                                        */

PROCEDURE insertRowsForGraph( i in number,
                              l_session_id in number,
                              available  in number,
                              talk       in number,
                              wrap       in number,
                              idle       in number,
                              p_out        in number)

AS
 j number;

Line: 626

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
                values (l_session_id,'BIX_AGENT_DETAIL_RPT',5,null, null,
                            'Out', p_out);

Line: 630

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
                values (l_session_id,'BIX_AGENT_DETAIL_RPT',4,null, null,
                            'Idle', idle);

Line: 633

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
                values (l_session_id,'BIX_AGENT_DETAIL_RPT',5,null, null,
                            'Out', p_out);

Line: 637

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
                values (l_session_id,'BIX_AGENT_DETAIL_RPT',3,null, null,
                            'Available', available);

Line: 640

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
                values (l_session_id,'BIX_AGENT_DETAIL_RPT',4,null, null,
                            'Idle', idle);

Line: 643

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
            values (l_session_id,'BIX_AGENT_DETAIL_RPT',5,null, null,
                            'Out', p_out);

Line: 647

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
                values (l_session_id,'BIX_AGENT_DETAIL_RPT',2,null, null,
                            'Wrap', wrap);

Line: 650

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
                values (l_session_id,'BIX_AGENT_DETAIL_RPT',3,null, null,
                            'Available', available);

Line: 653

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
             values (l_session_id,'BIX_AGENT_DETAIL_RPT',4,null, null,
                           'Idle', idle);

Line: 656

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
             values (l_session_id,'BIX_AGENT_DETAIL_RPT',5,null, null,
                           'Out', p_out);

Line: 661

end insertRowsForGraph;

Line: 703

  Select
          (l_date_high - I2.begin_date_time)*24*3600 duration
  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.deliver_date_time is null
      and I2.completion_code is null
      and I2.active_flag ='T'
      and I2.end_date_time is NULL
      and I1.begin_date_time between l_date_low and l_date_high
      and I1.resource_id = v_resource_id;

Line: 720

 Select
	(l_date_high - C1.last_update_date)*24*3600 duration
from CCT_AGENT_RT_STATS C1
where C1.has_call = 'T'
      and C1.agent_id =  v_resource_id
      and C1.last_update_date between l_date_low and l_date_high;

Line: 729

 Select
	 (l_date_high - C1.last_update_date)*24*3600 duration
 from CCT_AGENT_RT_STATS C1,
      /* CCT_MEDIA_ITEMS C2,*/
      IEU_SH_SESSIONS I1,
      IEU_SH_ACTIVITIES I2
 where C1.has_call = 'F'
           and C1.agent_id = v_resource_id
           /*and C2.media_item_id = I2.media_id*/
           and I1.session_id = I2.session_id
	      and I1.application_id = 696
           and I1.resource_id = C1.agent_id
           and I2.active_flag = 'T'
           and I2.completion_code is null
           and I2.deliver_date_time  is not null
           and C1.last_update_date between l_date_low and l_date_high;

Line: 748

  Select
         (l_date_high-I1.begin_date_time)*24*3600 duration
  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
      and I1.resource_id = v_resource_id;

Line: 759

select
     (l_date_high-I1.end_date_time)*24*3600 duration
from IEU_SH_SESSIONS I1
where  (I1.active_flag is null or I1.active_flag = 'F')
       /* I1.begin_date_time between l_date_low and l_date_high */
       /* the agent logged not necessary in today */
       and I1.resource_id = v_resource_id
	  and I1.application_id = 696
       and I1.resource_id not in  /* make sure no new sessoin logged in by this agent */
          (
           select distinct(resource_id)
           from IEU_SH_SESSIONS
           where end_date_time is null
          );

Line: 777

select l_date_high-I1.begin_date_time loginTime
from IEU_SH_SESSIONS I1,
     IEU_SH_ACTIVITIES I2
where I1.session_id = I2.session_id
	 and I1.application_id = 696
       and I1.begin_date_time between l_date_low and l_date_high
       and I1.resource_id = v_resource_id
       and I1.active_flag = 'T';

Line: 788

select min(l_date_high-I1.begin_date_time)*24*3600 loginTime
from IEU_SH_SESSIONS I1,
     IEU_SH_ACTIVITIES I2
where I1.session_id = I2.session_id
	 and I1.application_id = 696
       and I1.resource_id = v_resource_id
       and I1.active_flag = 'T';

Line: 797

select
    (I1.begin_date_time-l_date_low)*3600*24 loginTime,
    (I1.end_date_time-l_date_low)*3600*24 logoutTime,
    I1.end_reason_code reason
from IEU_SH_SESSIONS I1
where
  I1.begin_date_time between l_date_low and l_date_high
  and I1.resource_id = v_resource_id
  and I1.application_id = 696
  and (I1.active_flag is null or I1.active_flag='F')
  order by I1.begin_date_time;

Line: 810

select  p1.name skillname,p3.NAME skilllevel
from per_competences p1, per_competence_elements p2, jtf_rs_resource_extns j1,
 PER_COMPETENCE_LEVELS_V p3
where p1.competence_id = p2.competence_id
and   j1.resource_id = v_resource_id
and   p2.person_id = j1.source_id
and   (p2.EFFECTIVE_DATE_TO is null or p2.EFFECTIVE_DATE_TO >= sysdate)
and   p3.competence_id = p1.competence_id
and   p3.rating_level_id = p2.PROFICIENCY_LEVEL_ID;

Line: 828

  delete from BIX_DM_REPORT
  where session_id = l_session_id
  and report_code =  'BIX_AGENT_DETAIL_RPT';

Line: 907

select count(I2.media_id) into CallsHandled
from IEU_SH_SESSIONS I1,
     IEU_SH_ACTIVITIES I2
where I1.session_id = I2.session_id
      and I1.application_id = 696
      and I1.begin_date_time between l_date_low and l_date_high
      and I1.resource_id = v_resource_id;

Line: 927

select agent_name into l_agent_name
from BIX_DM_AGENT_PARAM_V
where agent_id = v_resource_id;

Line: 933

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
 values (l_session_id,'BIX_AGENT_DETAIL_RPT',1, 'Agent Name', l_agent_name,'Talk',
 talk);

Line: 937

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
 values (l_session_id,'BIX_AGENT_DETAIL_RPT',2, 'Current  Status', v_status,'Wrap',
 wrap);

Line: 940

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
  values (l_session_id,'BIX_AGENT_DETAIL_RPT',3, 'Duration in Status', l_string,'Available',available);

Line: 943

 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
  values (l_session_id,'BIX_AGENT_DETAIL_RPT',4,'Calls Handled',CallsHandled,'Idle',
   idle);

Line: 952

 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
values (l_session_id,'BIX_AGENT_DETAIL_RPT',5,'Login Time', l_string,'Out',l_out);

Line: 961

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
        values (l_session_id,'BIX_AGENT_DETAIL_RPT',1,'Login/Logout Time', 'Reason',
                'Talk',talk);

Line: 972

select    F.meaning into LogoutReason
          from FND_LOOKUP_VALUES F
          where F.lookup_code = rec.reason
		and   F.lookup_type = 'IEU_CTRL_BREAK_REASON';

Line: 984

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
		values (l_session_id,'BIX_AGENT_DETAIL_RPT',i+1,l_string || '/' || l_string2, LogoutReason,
			   l_string3, j);

Line: 990

insertRowsForGraph(i, l_session_id, available, talk, wrap, idle, l_out);

Line: 997

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
                values (l_session_id,'BIX_AGENT_DETAIL_RPT',1,'Check Out Reason', 'Duration',
                                                                'Talk',talk);

Line: 1006

select    F.meaning into LogoutReason
          from FND_LOOKUP_VALUES F
          where F.lookup_code = rec.reason
		and   F.lookup_type = 'IEU_CTRL_BREAK_REASON';

Line: 1018

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
	values (l_session_id,'BIX_AGENT_DETAIL_RPT', i+1, LogoutReason, l_string ,l_string3,j);

Line: 1022

insertRowsForGraph(i, l_session_id, available, talk, wrap, idle, l_out);

Line: 1031

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
	values (l_session_id,'BIX_AGENT_DETAIL_RPT',1,'Skill', 'Level',
		null,null);

Line: 1035

   insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
	values (l_session_id,'BIX_AGENT_DETAIL_RPT',i,skilldata.skillname,skilldata.skilllevel, null, null);

Line: 1040

insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
	values (l_session_id,'BIX_AGENT_DETAIL_RPT',1,'', '',
		'Talk',talk);

Line: 1045

insertRowsForGraph(i, l_session_id, available, talk, wrap, idle, l_out);

Line: 1081

  Select  distinct(I1.resource_id) agentID,
		J.resource_name agentName,
		(l_date_high - I2.begin_date_time)*24*3600 availTime,
		/* J1.group_name groupName,*/
                I1.extension extension
  from IEU_SH_SESSIONS I1,
	  IEU_SH_ACTIVITIES I2,
	  JTF_RS_RESOURCE_EXTNS_VL J,
	  JTF_RS_GROUPS_VL J1,
	  JTF_RS_GROUP_MEMBERS J2
  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.deliver_date_time is null
         and I2.completion_code is null
         and I2.active_flag ='T'
         and I2.end_date_time is NULL
	 and I1.begin_date_time between l_date_low and l_date_high
	 and I1.resource_id = J.resource_id
	 and (J.resource_id = J2.resource_id)
	 and (J1.group_id = J2.group_id)
	 and (J1.group_id = v_group_id)
      and (J.server_group_id = v_site_id or (v_site_id = -999 or v_site_id is null))  /* new */
   order by J.resource_name;

Line: 1110

 Select distinct(C1.agent_id) agentID,
	   J.resource_name agentName,
	   (l_date_high - C1.last_update_date)*24*3600 talkTime,
	   /* J3.group_name groupName,*/
           I1.extension extension,
           C2.classification class
from CCT_AGENT_RT_STATS C1,
     CCT_MEDIA_ITEMS C2,
     JTF_RS_RESOURCE_EXTNS_VL J,
     JTF_RS_GROUPS_VL J3,
     JTF_RS_GROUP_MEMBERS J4,
     IEU_SH_SESSIONS I1,
     IEU_SH_ACTIVITIES I2
	where C1.has_call = 'T'
           and C1.agent_id = J.resource_id
           and C2.media_item_id = I2.media_id
           and I1.session_id = I2.session_id
	      and I1.application_id = 696
           and I1.resource_id = C1.agent_id
           and (J.resource_id = J4.resource_id)
	      and (J3.group_id = J4.group_id)
           and (J3.group_id = v_group_id)
		 and (C2.server_group_id = v_site_id or (v_site_id =-999 or v_site_id is null))
	   and C1.last_update_date between l_date_low and l_date_high
    order by J.resource_name;

Line: 1138

 Select distinct(C1.agent_id) agentID,
	   J.resource_name agentName,
	   (l_date_high - C1.last_update_date)*24*3600 wrapTime,
	   /* J3.group_name groupName,*/
           I1.extension extension
from CCT_AGENT_RT_STATS C1,
     JTF_RS_RESOURCE_EXTNS_VL J,
     JTF_RS_GROUPS_VL J3,
     JTF_RS_GROUP_MEMBERS J4,
     IEU_SH_SESSIONS I1,
     IEU_SH_ACTIVITIES I2
	where C1.has_call = 'F'
           and C1.agent_id = J.resource_id
           /* and C2.media_item_id = I2.media_id */
           and I1.session_id = I2.session_id
	      and I1.application_id = 696
           and I1.resource_id = C1.agent_id
           and (J.resource_id = J4.resource_id)
	      and (J3.group_id = J4.group_id)
           and (J3.group_id = v_group_id)
      and (J.server_group_id = v_site_id  or (v_site_id =-999 or v_site_id is null))
           and I2.active_flag = 'T'
           and I2.completion_code is null
           and I2.deliver_date_time  is not null
	   and C1.last_update_date between l_date_low and l_date_high
    order by J.resource_name;

Line: 1167

  Select distinct(I1.resource_id) agentID,
         J5.resource_name agentName,
         (l_date_high-I1.begin_date_time)*24*3600 idleTime,
         /* J3.group_name groupName,*/
         I1.extension extension
  from IEU_SH_SESSIONS I1,
       JTF_RS_GROUP_MEMBERS J4,
       JTF_RS_RESOURCE_EXTNS_VL J5,
       JTF_RS_GROUPS_VL J3
  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
      and I1.resource_id = J5.resource_id
      and (J5.resource_id = J4.resource_id)
      and (J3.group_id = J4.group_id)
      and (J3.group_id = v_group_id)
      and (J5.server_group_id = v_site_id  or (v_site_id =-999 or v_site_id is null))
      and I1.resource_id not in
       ( select col3 from BIX_DM_REPORT
        where report_code = 'BIX_AGENT_STATUS_REPORT'
        and session_id = l_session_id
        and (col8 = 'TALK' or col8='WRAP' or col8='AVAILABLE')
      )
  order by J5.resource_name;

Line: 1196

  Select distinct(I1.resource_id) agentID,
         J5.resource_name agentName,
         (l_date_high-I1.end_date_time)*24*3600 loggedoutTime,
         /* J3.group_name groupName,*/
         I1.extension extension,
         I1.end_reason_code reasoncode
  from IEU_SH_SESSIONS I1,
       JTF_RS_GROUP_MEMBERS J4,
       JTF_RS_RESOURCE_EXTNS_VL J5,
       JTF_RS_GROUPS_VL J3
       /* , FND_LOOKUPS F */
  where
      (I1.active_flag is null or I1.active_flag='F') /* 'N' */
	 and I1.application_id = 696
      and I1.resource_id = J5.resource_id
      and (J5.resource_id  = J4.resource_id)
      and (J3.group_id = J4.group_id)
      and (J3.group_id = v_group_id)
      and (J5.server_group_id = v_site_id or (v_site_id =-999 or v_site_id is null)) /* no classification assigned */
      and I1.resource_id not in  /* make sure no new sessoin logged in by this agent */
	  (
	   select distinct(resource_id)
	   from IEU_SH_SESSIONS
	   where end_date_time is null
	  )
order by J5.resource_name;

Line: 1225

select
        J5.resource_name agentName,
        J4.resource_id  agentID
from JTF_RS_GROUP_MEMBERS J4,
     JTF_RS_RESOURCE_EXTNS_VL J5
where J4.group_id = v_group_id
      and J5.resource_id  = J4.resource_id
      and J5.resource_id not in
         (select  col3
          from BIX_DM_REPORT
          where report_code = 'BIX_AGENT_STATUS_REPORT'
                and session_id = l_session_id
          )
order by J5.resource_name;

Line: 1245

  delete from BIX_DM_REPORT
  where session_id = l_session_id
  and report_code =  'BIX_AGENT_STATUS_REPORT';

Line: 1282

 select bix_util_pkg.get_null_lookup into l_unknown
 from dual;

Line: 1285

  /* If the user has selected "All" for agent group paramter , display the default group of the user */
  IF (v_group_id = -999 or v_group_id is null) THEN
    SELECT fnd_profile.value('BIX_DM_DEFAULT_GROUP')
    INTO   v_group_id
    FROM   dual;

Line: 1292

  /* or the user has selected "all" as agent group paramter and (s)he is not assigned to any default group */
  IF (v_group_id IS NULL) THEN
    RETURN;

Line: 1297

   Select group_name into l_groupName
   from JTF_RS_GROUPS_VL
   where group_id = v_group_id;

Line: 1309

select classification into l_className
from cct_classifications
where
      classification_id = v_classification;

Line: 1339

insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7,  col8, col10, col12,col14)
          values (l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName, rec.agentID,
                         rec.agentName, rec.extension,'TALK','TALK',l_timestring,' ', rec.class);

Line: 1355

insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7,  col8, col10,col12,col14)
          values (l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName, rec.agentID,rec.agentName,
                   rec.extension,'WRAP','WRAP',l_timestring,'', '');

Line: 1370

 insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7,  col8,
                          col10,col12, col14)
       values (l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName, rec.agentID,
               rec.agentName, rec.extension,'AVAIL','AVAILABLE',l_timestring,' ',' ');

Line: 1386

insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7,  col8, col10,col12,col14)
          values (l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName, rec.agentID,
                         rec.agentName, rec.extension,'IDLE','IDLE',
                         l_timestring,' ',' ');

Line: 1403

    select F.meaning into logoutreason
    from FND_LOOKUP_VALUES F
    where F.lookup_code = rec.reasoncode
    and   F.lookup_type = 'IEU_CTRL_BREAK_REASON';

Line: 1411

    insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7,  col8, col10,col12,col14)
          values (l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName, rec.agentID,rec.agentName,                   rec.extension,'Out','Out',
                  bix_util_pkg.get_hrmiss_frmt(rec.loggedoutTime),' ',logoutreason);

Line: 1421

/* if 'ALL' is selected for status      */
if (v_status_id=-999) then
  for rec in getNoneStatusAgentList
  loop
    reportcode := reportcode +1;

Line: 1430

  insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7,
                            col8, col10,col12,col14)
  values(l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName,rec.agentID,rec.agentName,
         ' ','None','None',' ',' ',' ');