1 PACKAGE BODY BIX_REAL_TIME_RPTS_PKG AS
2 /* $Header: bixxrtsb.pls 115.43 2003/01/10 18:39:55 achanda noship $ */
3
4 /* populate table for report: Queue Status Report */
5 /* component code: BIX_QUEUE_STATUS_RPT */
6 /* component type: BIN */
7
8 PROCEDURE pop_q_st_rpt(p_context VARCHAR2)
9 AS
10 l_session_id number;
11 l_date_low date;
12 l_date_high date;
13 loggedin number;
14 callsWaiting number;
15 longestCallWaiting number;
16 l_string varchar2(50);
17 l_string2 varchar2(50);
18 pstring varchar(50);
19 callsOffered number;
20 abandonCalls number;
21 paraX number := 20;
22 avgAbandonTime number;
23 avgSpeedToAnswer number;
24 avgTalkTime number;
25 avgQtime number;
26 totalWaitingTime number;
27 callsSrvLevel number;
28 talk number;
29 wrap number;
30 talk_graph number;
31 wrap_graph number;
32 activeAgent number;
33 v_group_id number;
34 v_classification_id NUMBER;
35 v_classification VARCHAR2(64);
36 v_drill_class_id NUMBER;
37 v_site_id number;
38 l_className varchar2(100);
39 numRowRTmeasure number;
40 i number;
41 j number;
42 calls_range1 number;
43 calls_range2 number;
44 calls_range3 number;
45 calls_range4 number;
46 calls_range5 number;
47 calls_range6 number;
48 calls_range7 number;
49 Cursor getClassList is
50 select classification className
51 from cct_classifications
52 where
53 classification_id = v_classification_id;
54
55
56 /* calls in queue */
57 Cursor getCallsWaitingForClass is
58 Select count(distinct(C.MEDIA_ITEM_ID)) calls,
59 C.classification class
60 from CCT_MEDIA_ITEMS C
61 where C.status = 1
62 and media_type = 0
63 and C.creation_date between l_date_low and l_date_high
64 and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
65 group by C.classification
66 order by count(C.MEDIA_ITEM_ID);
67
68
69 BEGIN
70
71 l_date_low := trunc(sysdate);
72 l_date_high := sysdate;
73
74 l_session_id:= bix_util_pkg.get_icx_session_id;
75
76 delete from BIX_DM_REPORT
77 where session_id = l_session_id
78 and report_code = 'BIX_QUEUE_STATUS_RPT';
79
80 /* drill down from queue status bin, queue status report itself.
81 to queue status report itself, agent status report, queue detail */
82
83 /* try to get incoming parameter(s) */
84 l_string := bix_util_pkg.get_parameter_value(p_context,'pContext');
85
86 if l_string is not null /*parameter got*/
87 then
88 l_string2 := substr(l_string,2,1);
89 if l_string2 = 'G' then /* drill from Q Status Rpt */
90 i := instr(l_string,'C',2) ;
91 v_group_id := to_number( substr(l_string,3,i-3));
92 j := instr(l_string,'S',i+1);
93 v_classification_id :=to_number(substr(l_string,i+1,j-i-1));
94 v_site_id :=to_number(substr(l_string,j+1));
95 else v_drill_class_id := to_number(l_string);/* from queue st bin */
96 v_classification_id := v_drill_class_id;
97 v_site_id := -999;
98 v_group_id := -999;
99 end if;
100 else
101 v_classification_id := to_number(bix_util_pkg.get_parameter_value(p_context,'P_CLASSIFICATION_ID'));
102 v_site_id := to_number(bix_util_pkg.get_parameter_value(p_context,'P_SITE_ID'));
103 v_group_id := to_number(bix_util_pkg.get_parameter_value(p_context,'P_GROUP_ID'));
104 end if;
105
106
107 /* If the user has selected "All" for agent group paramter , display the default group
108 of the user */
109 IF (v_group_id = -999) THEN
110 SELECT fnd_profile.value('BIX_DM_DEFAULT_GROUP')
111 INTO v_group_id
112 FROM dual;
113 END IF;
114
115 /* or the user has selected "all" as agent group paramter and (s)he is not assigned to
116 any default group */
117 IF (v_group_id IS NULL) THEN
118 RETURN;
119 END IF;
120
121 v_classification := NULL;
122 for rec in getClassList
123 loop
124 l_className :=rec.className;
125 v_classification := l_className;
126 end loop;
127
128
129 /* calls in queue by time ranges for graph */
130 Select count(distinct(C.MEDIA_ITEM_ID))
131 into calls_range1
132 from CCT_MEDIA_ITEMS C
133 where C.status = 1
134 and C.media_type = 0
135 and C.creation_date between l_date_low and l_date_high
136 and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
137 and (C.classification = v_classification or (v_classification is null))
138 and (sysdate - C.last_update_date) * 24 * 60 <= 10;
139
140 /* calls in queue by time ranges for graph */
141 Select count(distinct(C.MEDIA_ITEM_ID))
142 into calls_range2
143 from CCT_MEDIA_ITEMS C
144 where C.status = 1
145 and C.media_type = 0
146 and C.creation_date between l_date_low and l_date_high
147 and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
148 and (C.classification = v_classification or (v_classification is null))
149 and (sysdate - C.last_update_date) * 24 * 60 <= 20
150 and (sysdate - C.last_update_date) * 24 * 60 > 10;
151
152 /* calls in queue by time ranges for graph */
153 Select count(distinct(C.MEDIA_ITEM_ID))
154 into calls_range3
155 from CCT_MEDIA_ITEMS C
156 where C.status = 1
157 and media_type = 0
158 and C.creation_date between l_date_low and l_date_high
159 and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
160 and (C.classification = v_classification or (v_classification is null))
161 and (sysdate - last_update_date) * 24 * 60 <= 30
162 and (sysdate - last_update_date) * 24 * 60 > 20;
163
164
165 /* calls in queue by time ranges for graph */
166 Select count(distinct(C.MEDIA_ITEM_ID))
167 into calls_range4
168 from CCT_MEDIA_ITEMS C
169 where C.status = 1
170 and media_type = 0
171 and C.creation_date between l_date_low and l_date_high
172 and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
173 and (C.classification = v_classification or (v_classification is null))
174 and (sysdate - last_update_date) * 24 * 60 <= 40
175 and (sysdate - last_update_date) * 24 * 60 > 30;
176
177 /* calls in queue by time ranges for graph */
178 Select count(distinct(C.MEDIA_ITEM_ID))
179 into calls_range5
180 from CCT_MEDIA_ITEMS C
181 where C.status = 1
182 and media_type = 0
183 and C.creation_date between l_date_low and l_date_high
184 and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
185 and (C.classification = v_classification or (v_classification is null))
186 and (sysdate - last_update_date) * 24 * 60 <= 50
187 and (sysdate - last_update_date) * 24 * 60 > 40;
188
189 /* calls in queue by time ranges for graph */
190 Select count(distinct(C.MEDIA_ITEM_ID))
191 into calls_range6
192 from CCT_MEDIA_ITEMS C
193 where C.status = 1
194 and media_type = 0
195 and C.creation_date between l_date_low and l_date_high
196 and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
197 and (C.classification = v_classification or (v_classification is null))
198 and (sysdate - last_update_date) * 24 * 60 <= 60
199 and (sysdate - last_update_date) * 24 * 60 > 50;
200
201 /* calls in queue by time ranges for graph */
202 Select count(distinct(C.MEDIA_ITEM_ID))
203 into calls_range7
204 from CCT_MEDIA_ITEMS C
205 where C.status = 1
206 and media_type = 0
207 and C.creation_date between l_date_low and l_date_high
208 and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
209 and (C.classification = v_classification or (v_classification is null))
210 and (sysdate - last_update_date) * 24 * 60 > 60;
211
212 /* get talking agents for graph */
213 select count(distinct I1.resource_id) into talk_graph
214 from IEU_SH_SESSIONS I1,
215 IEU_SH_ACTIVITIES I2,
216 CCT_media_items M,
217 JTF_RS_GROUP_MEMBERS J4,
218 CCT_AGENT_RT_STATS C1
219 where I1.session_id = I2.session_id
220 and I1.application_id = 696
221 and I1.active_flag ='T'
222 and I1.end_date_time is NULL
223 and I2.activity_type_code = 'MEDIA'
224 and I2.active_flag = 'T'
225 and I2.end_date_time is NULL
226 and I2.deliver_date_time is not null
227 and C1.agent_id = I1.resource_id
228 and C1.has_call = 'T'
229 and I1.begin_date_time between l_date_low and l_date_high
230 and M.media_item_id = I2.media_id
231 and I1.resource_id= J4.resource_id
232 and (J4.group_id = v_group_id or v_group_id=-999)
233 and (M.server_group_id= v_site_id or v_site_id=-999)
234 and (M.classification = l_className or v_classification_id=-999);
235
236
237 /* get wrapping agents for graph */
238 /* removed
239 Select count(distinct I1.resource_id) into wrap_graph
240 from IEU_SH_SESSIONS I1,
241 IEU_SH_ACTIVITIES I2,
242 CCT_AGENT_RT_STATS C1,
243 CCT_media_items M,
244 JTF_RS_GROUP_MEMBERS J4
245 where I1.session_id = I2.session_id
246 and I1.application_id = 696
247 and I2.active_flag ='T'
248 and I2.activity_type_code = 'MEDIA'
249 and I2.end_date_time is null
250 and I2.deliver_date_time is not null
251 and I1.resource_id = C1.agent_id
252 and C1.has_call = 'F'
253 and I1.begin_date_time between l_date_low and l_date_high
254 and M.media_item_id = I2.media_id
255 and C1.agent_id= J4.resource_id
256 and (J4.group_id = v_group_id or v_group_id=-999)
257 and (M.server_group_id= v_site_id or v_site_id=-999)
258 and (M.classification = l_className or v_classification_id=-999);
259 */
260
261
262 /* get active agents */
263 /* removed
264 activeAgent := talk_graph +wrap_graph;
265 */
266
267
268 /* calls in queue */
269 Select count(distinct(C.MEDIA_ITEM_ID)) into callsWaiting
270 from CCT_MEDIA_ITEMS C
271 where C.status = 1
272 and media_type = 0
273 and C.creation_date between l_date_low and l_date_high
274 and (C.server_group_id= v_site_id or v_site_id=-999)
275 and (C.classification = l_className or v_classification_id=-999);
276
277 /* CCT_MEDIA_ITEMS.status
278 0 Media item received, not yet routed ( active mode only )
279 1 Media item routed, waiting in queue
280 2 Email reserved by an agent ( email only )
281 3 Media item dequeued and served by an agent
282 4 Media item received in passive mode
283 5 Media item served in passive mode
284 6 Media item abandoned */
285
286
287 /* longest call in queue */
288 Select max(l_date_high- C.last_update_date)*24*3600 into longestCallWaiting
289 from CCT_MEDIA_ITEMS C
290 where C.status = 1
291 and media_type = 0
292 and C.creation_date between l_date_low and l_date_high
293 and (C.server_group_id= v_site_id or v_site_id=-999)
294 and (C.classification = l_className or v_classification_id=-999);
295
296 /* total queue time */
297 select sum(l_date_high- C.last_update_date)*24*3600 into totalWaitingTime
298 from CCT_MEDIA_ITEMS C
299 where C.status = 1
300 and media_type = 0
301 and C.creation_date between l_date_low and l_date_high
302 and (C.server_group_id= v_site_id or v_site_id=-999)
303 and (C.classification = l_className or v_classification_id=-999);
304
305 if totalWaitingTime is null then totalWaitingTime :=0;
306 end if;
307 if callsWaiting is null then callsWaiting:=0; end if;
308 if longestCallWaiting is null then longestCallWaiting :=0;
309 end if;
310
311 /* get average queue time for calls */
312 if callsWaiting = 0 then avgQtime :=0;
313 else
314 avgQtime := trunc(totalWaitingTime / callsWaiting);
315 end if;
316
317
318 /****************** Daily measures : ***************************/
319
320 SELECT sum(DECODE(UPPER(ih_mitem.direction),'INBOUND',1,0)) ,
321 sum(DECODE(UPPER(ih_mitem.direction),'INBOUND',DECODE(UPPER(ih_mitem.media_abandon_flag),'Y',1,0),0))
322 into callsOffered, abandonCalls
323 FROM JTF_IH_MEDIA_ITEMS ih_mitem
324 WHERE ih_mitem.start_date_time BETWEEN l_date_low and l_date_high
325 AND
326 (
327 ih_mitem.media_item_type = 'TELE_INB' or
328 ih_mitem.media_item_type = 'TELE_DIRECT' or
329 ih_mitem.media_item_type = 'TELE_MANUAL' or
330 ih_mitem.media_item_type = 'TELE_WEB'
331 )
332 AND ih_mitem.active = 'N'
333 AND (ih_mitem.classification = v_classification or v_classification is null)
334 AND (ih_mitem.server_group_id = v_site_id or (v_site_id=-999 or v_site_id is null));
335
336 SELECT SUM(DECODE(SIGN(msegs.duration - goals.SL_SECONDS_GOAL),0,1,-1,1,0))
337 into callsSrvLevel
338 FROM jtf_ih_media_items ih_mitem,
339 JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
340 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps,
341 bix_dm_goals goals
342 where (ih_mitem.server_group_id = v_site_id or (v_site_id is null or v_site_id = -999))
343 and goals.call_type_id = v_classification_id
344 and (ih_mitem.classification = v_classification or (v_classification is null))
345 and ih_mitem.start_date_time BETWEEN l_date_low and l_date_high
346 and ih_mitem.media_id = msegs.media_id
347 and mtyps.milcs_type_id = msegs.milcs_type_id
348 and mtyps.milcs_code = 'IN_QUEUE'
349 and ih_mitem.direction = 'INBOUND'
350 and goals.end_date_active is null;
351
352 SELECT SUM(msegs.duration)/count(distinct(msegs.media_id))
353 INTO avgAbandonTime
354 FROM jtf_ih_media_items ih_mitem,
355 JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
356 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps
357 where ih_mitem.start_date_time BETWEEN l_date_low and l_date_high
358 and ih_mitem.media_id = msegs.media_id
359 and mtyps.milcs_type_id = msegs.milcs_type_id
360 and mtyps.milcs_code = 'IN_QUEUE'
361 and ih_mitem.direction = 'INBOUND'
362 and ih_mitem.media_abandon_flag = 'Y'
363 and ih_mitem.active = 'N'
364 and (ih_mitem.classification = v_classification or (v_classification is null))
365 and (ih_mitem.server_group_id = v_site_id or (v_site_id is null or v_site_id = -999));
366
367 SELECT sum(msegs.duration)/count(distinct(msegs.media_id))
368 INTO avgTalkTime
369 FROM jtf_ih_media_items ih_mitem,
370 JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
371 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps,
372 JTF_RS_GROUP_MEMBERS gp
373 where ih_mitem.start_date_time BETWEEN l_date_low and l_date_high
374 and ih_mitem.media_id = msegs.media_id
375 and mtyps.milcs_type_id = msegs.milcs_type_id
376 and ih_mitem.active = 'N'
377 and (ih_mitem.classification = v_classification or (v_classification is null ))
378 and (ih_mitem.server_group_id = v_site_id or (v_site_id is null or v_site_id = -999))
379 and mtyps.milcs_code = 'WITH_AGENT'
380 and gp.resource_id = msegs.resource_id
381 and (gp.group_id = v_group_id or (v_group_id = -999 or v_group_id is null));
382
383 SELECT sum(msegs1.duration)/count(distinct(msegs1.media_id))
384 INTO avgSpeedToAnswer
385 FROM jtf_ih_media_items ih_mitem,
386 JTF_IH_MEDIA_ITEM_LC_SEGS msegs1,
387 JTF_IH_MEDIA_ITEM_LC_SEGS msegs2,
388 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps1,
389 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps2,
390 JTF_RS_GROUP_MEMBERS gp
391 where ih_mitem.start_date_time BETWEEN l_date_low and l_date_high
392 and ih_mitem.media_id = msegs1.media_id
393 and ih_mitem.media_id = msegs2.media_id
394 and mtyps1.milcs_type_id = msegs1.milcs_type_id
395 and mtyps2.milcs_type_id = msegs2.milcs_type_id
396 and ih_mitem.active = 'N'
397 and (ih_mitem.classification = v_classification or (v_classification is null ))
398 and (ih_mitem.server_group_id = v_site_id or (v_site_id is null or v_site_id = -999))
399 and mtyps1.milcs_code = 'IN_QUEUE'
400 and mtyps2.milcs_code = 'WITH_AGENT'
401 and gp.resource_id = msegs2.resource_id
402 and (gp.group_id = v_group_id or (v_group_id = -999 or v_group_id is null));
403
404
405 if callsOffered is null then callsOffered:=0;
406 end if;
407 if abandonCalls is null then abandonCalls :=0;
408 end if;
409 if callsSrvLevel is null then callsSrvLevel :=0;
410 end if;
411 if avgAbandonTime is null then avgAbandonTime :=0;
412 end if;
413 if avgSpeedToAnswer is null then avgSpeedToAnswer :=0;
414 end if;
415 if avgTalkTime is null then avgTalkTime:=0;
416 end if;
417
418 /* insert data into table for reporting */
419
420 /* for parameter passing */
421 pstring := 'G' || to_char(v_group_id) || 'C' ||to_char(v_classification_id) || 'S' || to_char(v_site_id) ;
422
423 /* Make sure check col1 for bixxqstr.jsp seeding */
424 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8, col10, col12, col14, col16, col18, col20, col22, col24)
425 values (l_session_id,'BIX_QUEUE_STATUS_RPT','1' || pstring , 'Calls Waiting', callsWaiting,
426 'Calls Offered', callsOffered,'', calls_range1, calls_range2, calls_range3, calls_range4, calls_range5, calls_range6, calls_range7);
427
428 l_string2:= bix_util_pkg.get_hrmiss_frmt(longestCallWaiting);
429
430 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
431 values (l_session_id,'BIX_QUEUE_STATUS_RPT','2' || pstring, 'Longest Call Waiting', l_string2,
432 'Abandon Calls', abandonCalls, null, null);
433
434 l_string2:= bix_util_pkg.get_hrmiss_frmt(avgQtime);
435
436 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
437 values (l_session_id,'BIX_QUEUE_STATUS_RPT','3' || pstring, 'Average Queue Time', l_string2,
438 'Calls within Service Level', callsSrvLevel,null, null);
439
440
441 l_string:= bix_util_pkg.get_hrmiss_frmt(avgAbandonTime);
442 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
443 values (l_session_id,'BIX_QUEUE_STATUS_RPT','4'|| pstring, 'Talking Agents', talk_graph,
444 'Average abandon Time', l_string, null, null);
445
446 l_string := bix_util_pkg.get_hrmiss_frmt(avgSpeedToAnswer);
447
448
449 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
450 values (l_session_id,'BIX_QUEUE_STATUS_RPT','5', '', null,
451 'Average Speed To Answer', l_string,null, null);
452
453 l_string := bix_util_pkg.get_hrmiss_frmt(avgTalkTime);
454
455 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
456 values (l_session_id,'BIX_QUEUE_STATUS_RPT','6', '', null,
457 'Average Talk Time', l_string,null, null);
458
459 /*
460 numRowRTmeasure := 6;
461 i :=0;
462 select bix_util_pkg.get_null_lookup into l_string2 from dual;
463 for rec in getCallsWaitingForClass loop
464 if rec.class is null then l_string:= l_string2;
465 else l_string :=rec.class;
466 end if;
467 i :=i+1;
468 if i<=numRowRTmeasure then
469 update BIX_DM_REPORT set col10=l_string, col12=calls_range1
470 where col1 like to_char(i) || '%';
471 else insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4, col6, col8,col10, col12)
472 values (l_session_id,'BIX_QUEUE_STATUS_RPT', i ,null,null,null,null,l_string,rec.calls);
473 end if;
474 end loop;
475 */
476 END pop_q_st_rpt;
477
478
479
480 /* utility procedure to get agents in idle, available, wrap, talk status */
481 PROCEDURE getTimeOfStatus (v_agent_id in number,
482 available out nocopy number,
483 talk out nocopy number,
484 wrap out nocopy number,
485 idle out nocopy number,
486 p_out out nocopy number,
487 loggedin out nocopy number)
488
489 AS
490 l_date_low date;
491 l_date_high date;
492 num_agents number;
493 g_session_id NUMBER;
494 talk_now number;
495 talk_past number;
496 wrap_now number;
497 wrap_past number;
498
499 BEGIN
500 l_date_low := trunc(sysdate);
501 l_date_high := sysdate;
502
503 /*loggined in time
504 ------------------*/
505
506 select sum(decode(end_date_time, null, l_date_high-begin_date_time,
507 end_date_time-begin_date_time))*3600*24 into loggedin
508 from ieu_sh_sessions
509 where resource_id = v_agent_id
510 and begin_date_time between l_date_low and l_date_high
511 and application_id = 696;
512
513 if loggedin is null then loggedin :=0;
514 end if;
515
516 /*
517 available:
518 ----------*/
519 select sum(decode(I1.deliver_date_time,null,l_date_high-I1.begin_date_time,
520 I1.deliver_date_time-I1.begin_date_time))*3600*24 into available
521 from ieu_sh_activities I1,
522 ieu_sh_sessions I2
523 where I1.session_id = I2.session_id
524 and I2.resource_id = v_agent_id
525 and I2.begin_date_time between l_date_low and l_date_high
526 and I2.application_id = 696
527 /* and I1.deliver_date_time is not null */
528 and I1.activity_type_code = 'MEDIA';
529
530 if available is null then available :=0;
531 end if;
532
533
534 /*
535 talk time
536 ----------*/
537 select sum(J1.duration) *3600*24 into talk_past
538 from jtf_ih_media_item_lc_segs J1,
539 jtf_ih_media_itm_lc_seg_tys J2,
540 jtf_ih_media_items J3
541 where J1.resource_id = v_agent_id
542 and J1.milcs_type_id = J2.milcs_type_id
543 and J2.milcs_code = 'WITH_AGENT'
544 and J1.start_date_time between l_date_low and l_date_high
545 and J3.media_id = J1.media_id
546 and J3.media_item_type = 'TELEPHONE';
547
548 select sum(l_date_high-I2.deliver_date_time)*3600*24 into talk_now
549 from ieu_sh_activities I2,
550 ieu_sh_sessions I1
551 where I1.session_id = I2.session_id
552 and I1.resource_id = v_agent_id
553 and I1.application_id = 696
554 and I1.active_flag = 'T'
555 and I1.end_Date_time is null
556 and I2.activity_type_code = 'MEDIA'
557 and I2.active_flag = 'T'
558 and I2.end_date_time is null
559 and I2.deliver_date_time is not null
560 and I1.begin_date_time between l_date_low and l_date_high;
561
562 talk := nvl(talk_past,0)+ nvl(talk_now,0);
563
564 /*
565 wrap: wrap_now + wrap_past
566 --------------------------*/
567
568 Select sum(l_date_high - C1.last_update_date)*24*3600 into wrap_now
569 from IEU_SH_SESSIONS I1,
570 IEU_SH_ACTIVITIES I2,
571 CCT_AGENT_RT_STATS C1
572 where I1.session_id = I2.session_id
573 and I1.application_id = 696
574 and I2.active_flag ='T'
575 and I2.activity_type_code = 'MEDIA'
576 and I2.end_date_time is null
577 and I2.deliver_date_time is not null
578 and I1.resource_id = C1.agent_id
579 and I1.resource_id = v_agent_id
580 and C1.has_call = 'F'
581 and I1.begin_date_time between l_date_low and l_date_high;
582
583
584
585 select sum(J1.end_date_time - J2.end_date_time)*24*3600 into wrap_past
586 from jtf_ih_media_item_lc_segs J2,
587 jtf_ih_interactions J1,
588 jtf_ih_media_itm_lc_seg_tys J3
589 where J1.resource_id = v_agent_id
590 and J1.resource_id = J2.resource_id
591 and J2.media_id = J1.productive_time_amount
592 and J2.milcs_type_id = J3.milcs_type_id
593 and J3.milcs_code = 'WITH_AGENT'
594 and J1.start_date_time between l_date_low and l_date_high;
595
596 wrap := nvl(wrap_now,0) + nvl(wrap_past,0);
597
598
599 idle := loggedin - available - talk - wrap;
600
601 /*
602 loggoutTime
603 -----------*/
604 p_out := (l_date_high-l_date_low)*3600*24 -loggedin;
605
606 END getTimeOfStatus;
607
608
609 /* utility procedure to insert data for agent time spent graph in agent */
610 /* detail report */
611
612 PROCEDURE insertRowsForGraph( i in number,
613 l_session_id in number,
614 available in number,
615 talk in number,
616 wrap in number,
617 idle in number,
618 p_out in number)
619
620 AS
621 j number;
622
623 BEGIN
624
625 if i=3 then
626 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
627 values (l_session_id,'BIX_AGENT_DETAIL_RPT',5,null, null,
628 'Out', p_out);
629 elsif i=2 then
630 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
631 values (l_session_id,'BIX_AGENT_DETAIL_RPT',4,null, null,
632 'Idle', idle);
633 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
634 values (l_session_id,'BIX_AGENT_DETAIL_RPT',5,null, null,
635 'Out', p_out);
636 elsif i=1 then
637 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
638 values (l_session_id,'BIX_AGENT_DETAIL_RPT',3,null, null,
639 'Available', available);
640 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
641 values (l_session_id,'BIX_AGENT_DETAIL_RPT',4,null, null,
642 'Idle', idle);
643 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
644 values (l_session_id,'BIX_AGENT_DETAIL_RPT',5,null, null,
645 'Out', p_out);
646 elsif i=0 then
647 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
648 values (l_session_id,'BIX_AGENT_DETAIL_RPT',2,null, null,
649 'Wrap', wrap);
650 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
651 values (l_session_id,'BIX_AGENT_DETAIL_RPT',3,null, null,
652 'Available', available);
653 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
654 values (l_session_id,'BIX_AGENT_DETAIL_RPT',4,null, null,
655 'Idle', idle);
656 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
657 values (l_session_id,'BIX_AGENT_DETAIL_RPT',5,null, null,
658 'Out', p_out);
659 end if;
660
661 end insertRowsForGraph;
662
663
664
665 /* populate table for report: Agent Detail Report */
666 /* component code: BIX_AGENT_DETAIL_RPT */
667 /* component type: REPORT */
668 PROCEDURE pop_agt_dtl_rpt(p_context VARCHAR2)
669 AS
670
671 l_session_id number;
672 v_resource_id number;
673 l_date_low date;
674 l_date_high date;
675 l_string varchar2(100);
676 l_agent_name varchar2(100);
677 v_status varchar2(100);
678 v_duration number;
679 v_display_id number;
680 v_found number;
681 CallsHandled number;
682 LoginTime number;
683 TodayLoginTime number;
684 LogoutTime number;
685 LogoutDate Date;
686 LogoutReason varchar2(100);
687 l_string2 varchar2(100);
688 l_string3 varchar2(100);
689 i number;
690 j number;
691
692 /* for graph */
693
694 available number;
695 talk number;
696 wrap number;
697 idle number;
698 l_out number;
699 loggedin number;
700
701
702 Cursor getAvailableAgentList is
703 Select
704 (l_date_high - I2.begin_date_time)*24*3600 duration
705 from IEU_SH_SESSIONS I1,
706 IEU_SH_ACTIVITIES I2
707 where I1.session_id = I2.session_id
708 and I1.application_id = 696
709 and I1.active_flag ='T'
710 and I1.end_date_time is NULL
711 and I2.activity_type_code = 'MEDIA'
712 and I2.deliver_date_time is null
713 and I2.completion_code is null
714 and I2.active_flag ='T'
715 and I2.end_date_time is NULL
716 and I1.begin_date_time between l_date_low and l_date_high
717 and I1.resource_id = v_resource_id;
718
719 Cursor getTalkAgentList is
720 Select
721 (l_date_high - C1.last_update_date)*24*3600 duration
722 from CCT_AGENT_RT_STATS C1
723 where C1.has_call = 'T'
724 and C1.agent_id = v_resource_id
725 and C1.last_update_date between l_date_low and l_date_high;
726
727
728 Cursor getWrapAgentList is
729 Select
730 (l_date_high - C1.last_update_date)*24*3600 duration
731 from CCT_AGENT_RT_STATS C1,
732 /* CCT_MEDIA_ITEMS C2,*/
733 IEU_SH_SESSIONS I1,
734 IEU_SH_ACTIVITIES I2
735 where C1.has_call = 'F'
736 and C1.agent_id = v_resource_id
737 /*and C2.media_item_id = I2.media_id*/
738 and I1.session_id = I2.session_id
739 and I1.application_id = 696
740 and I1.resource_id = C1.agent_id
741 and I2.active_flag = 'T'
742 and I2.completion_code is null
743 and I2.deliver_date_time is not null
744 and C1.last_update_date between l_date_low and l_date_high;
745
746
747 Cursor getLoggedinAgentList is /* for idle*/
748 Select
749 (l_date_high-I1.begin_date_time)*24*3600 duration
750 from IEU_SH_SESSIONS I1
751 where
752 I1.active_flag = 'T'
753 and I1.application_id = 696
754 and I1.end_date_time is NULL
755 and I1.begin_date_time between l_date_low and l_date_high
756 and I1.resource_id = v_resource_id;
757
758 Cursor getLogoutList is
759 select
760 (l_date_high-I1.end_date_time)*24*3600 duration
761 from IEU_SH_SESSIONS I1
762 where (I1.active_flag is null or I1.active_flag = 'F')
763 /* I1.begin_date_time between l_date_low and l_date_high */
764 /* the agent logged not necessary in today */
765 and I1.resource_id = v_resource_id
766 and I1.application_id = 696
767 and I1.resource_id not in /* make sure no new sessoin logged in by this agent */
768 (
769 select distinct(resource_id)
770 from IEU_SH_SESSIONS
771 where end_date_time is null
772 );
773
774 /* there should be only one current login for an agent */
775 /* today: does not make sense
776 Cursor getTodayLoginTime is
777 select l_date_high-I1.begin_date_time loginTime
778 from IEU_SH_SESSIONS I1,
779 IEU_SH_ACTIVITIES I2
780 where I1.session_id = I2.session_id
781 and I1.application_id = 696
782 and I1.begin_date_time between l_date_low and l_date_high
783 and I1.resource_id = v_resource_id
784 and I1.active_flag = 'T';
785 */
786
787 Cursor getLoginTime is
788 select min(l_date_high-I1.begin_date_time)*24*3600 loginTime
789 from IEU_SH_SESSIONS I1,
790 IEU_SH_ACTIVITIES I2
791 where I1.session_id = I2.session_id
792 and I1.application_id = 696
793 and I1.resource_id = v_resource_id
794 and I1.active_flag = 'T';
795
796 Cursor getLogoutTime is
797 select
798 (I1.begin_date_time-l_date_low)*3600*24 loginTime,
799 (I1.end_date_time-l_date_low)*3600*24 logoutTime,
800 I1.end_reason_code reason
801 from IEU_SH_SESSIONS I1
802 where
803 I1.begin_date_time between l_date_low and l_date_high
804 and I1.resource_id = v_resource_id
805 and I1.application_id = 696
806 and (I1.active_flag is null or I1.active_flag='F')
807 order by I1.begin_date_time;
808
809 cursor getAgentSkills IS
810 select p1.name skillname,p3.NAME skilllevel
811 from per_competences p1, per_competence_elements p2, jtf_rs_resource_extns j1,
812 PER_COMPETENCE_LEVELS_V p3
813 where p1.competence_id = p2.competence_id
814 and j1.resource_id = v_resource_id
815 and p2.person_id = j1.source_id
816 and (p2.EFFECTIVE_DATE_TO is null or p2.EFFECTIVE_DATE_TO >= sysdate)
817 and p3.competence_id = p1.competence_id
818 and p3.rating_level_id = p2.PROFICIENCY_LEVEL_ID;
819
820
821 BEGIN
822
823 l_date_low := trunc(sysdate);
824 l_date_high := sysdate;
825
826 l_session_id:= bix_util_pkg.get_icx_session_id;
827
828 delete from BIX_DM_REPORT
829 where session_id = l_session_id
830 and report_code = 'BIX_AGENT_DETAIL_RPT';
831
832 /* check if drilled down from q status or agent status report */
833 l_string := bix_util_pkg.get_parameter_value(p_context,'pContext');
834 if l_string is null then /* not drilled down */
835 v_display_id := to_number(bix_util_pkg.get_parameter_value(p_context,'P_DISPLAY_ID'));
836 v_resource_id := to_number(bix_util_pkg.get_parameter_value(p_context,'P_RESOURCE_ID'));
837 else
838 if l_string= 'BIX_AGENT_DETAIL_RPT' then v_resource_id := NULL;
839 else v_resource_id := to_number(l_string);
840 v_display_id := 1;
841 end if;
842 end if;
843
844
845 /****************** for graph *************************************/
846 getTimeOfStatus( v_resource_id, available, talk ,
847 wrap, idle, l_out, loggedin);
848
849 /********************** Measure Value ****************************/
850 if v_display_id = 1 then
851
852 v_found:=0;
853 for rec in getAvailableAgentList
854 loop
855 v_found :=1;
856 v_status := 'AVAILABLE';
857 v_duration := rec.duration;
858 end loop;
859
860 if v_found=0 then
861 for rec in getTalkAgentList
862 loop
863 v_found :=1;
864 v_status := 'TALK';
865 v_duration := rec.duration;
866 end loop;
867 end if;
868
869 if v_found=0 then
870 for rec in getWrapAgentList
871 loop
872 v_found :=1;
873 v_status := 'TALK';
874 v_duration := rec.duration;
875 end loop;
876 end if;
877
878 if v_found=0 then
879 for rec in getLoggedinAgentList
880 loop
881 v_found :=1;
882 v_status := 'IDLE';
883 v_duration := rec.duration;
884 end loop;
885 end if;
886
887
888 if v_found=0 then
889 for rec in getLogoutList
890 loop
891 v_found :=1;
892 v_status := 'OUT';
893 v_duration := rec.duration;
894 end loop;
895 end if;
896
897 if v_found=0 then
898 v_status := 'None';
899 v_duration := null;
900 end if;
901
902 if (v_duration>60*60*24) then l_string := '>24 Hours';
903 else
904 l_string := bix_util_pkg.get_hrmiss_frmt(v_duration);
905 end if;
906
907 select count(I2.media_id) into CallsHandled
908 from IEU_SH_SESSIONS I1,
909 IEU_SH_ACTIVITIES I2
910 where I1.session_id = I2.session_id
911 and I1.application_id = 696
912 and I1.begin_date_time between l_date_low and l_date_high
913 and I1.resource_id = v_resource_id;
914
915 if v_status = 'None' then LoginTime :=null;
916 else
917 for rec in getLoginTime loop
918
919 LoginTime := rec.loginTime;
920 end loop;
921
922 if LoginTime is null then
923 LoginTime :=0;
924 end if;
925 end if;
926
927 select agent_name into l_agent_name
928 from BIX_DM_AGENT_PARAM_V
929 where agent_id = v_resource_id;
930
931 l_string := bix_util_pkg.get_hrmiss_frmt(v_duration);
932
933 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
934 values (l_session_id,'BIX_AGENT_DETAIL_RPT',1, 'Agent Name', l_agent_name,'Talk',
935 talk);
936
937 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
938 values (l_session_id,'BIX_AGENT_DETAIL_RPT',2, 'Current Status', v_status,'Wrap',
939 wrap);
940 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
941 values (l_session_id,'BIX_AGENT_DETAIL_RPT',3, 'Duration in Status', l_string,'Available',available);
942
943 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
944 values (l_session_id,'BIX_AGENT_DETAIL_RPT',4,'Calls Handled',CallsHandled,'Idle',
945 idle);
946
947 if LoginTime>24*3600 then l_string := '>24 Hours';
948 else
949 l_string := bix_util_pkg.get_hrmiss_frmt(LoginTime);
950 end if;
951
952 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
953 values (l_session_id,'BIX_AGENT_DETAIL_RPT',5,'Login Time', l_string,'Out',l_out);
954
955 end if; /* of display 1 */
956
957 /******************time Card *********************************************/
958
959 if v_display_id= 2 then
960
961 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
962 values (l_session_id,'BIX_AGENT_DETAIL_RPT',1,'Login/Logout Time', 'Reason',
963 'Talk',talk);
964
965 i:=0;
966 for rec in getLogoutTime
967 loop
968
969 l_string := bix_util_pkg.get_hrmiss_frmt(rec.LoginTime);
970 l_string2 := bix_util_pkg.get_hrmiss_frmt(rec.LogoutTime);
971
972 select F.meaning into LogoutReason
973 from FND_LOOKUP_VALUES F
974 where F.lookup_code = rec.reason
975 and F.lookup_type = 'IEU_CTRL_BREAK_REASON';
976 i := i+1;
977
978 if i=1 then l_string3 := 'Wrap'; j:=wrap;
979 elsif i=2 then l_string3 := 'Available'; j:=available;
980 elsif i=3 then l_string3 := 'Idle'; j:=idle;
981 else l_string3 := 'Out'; j:=l_out;
982 end if;
983
984 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
985 values (l_session_id,'BIX_AGENT_DETAIL_RPT',i+1,l_string || '/' || l_string2, LogoutReason,
986 l_string3, j);
987
988 end loop;
989
990 insertRowsForGraph(i, l_session_id, available, talk, wrap, idle, l_out);
991
992 end if; /*display 2*/
993
994 /******************Checkout duration**************************************/
995 if v_display_id=3 then
996
997 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
998 values (l_session_id,'BIX_AGENT_DETAIL_RPT',1,'Check Out Reason', 'Duration',
999 'Talk',talk);
1000 i:=0;
1001 for rec in getLogoutTime
1002 loop
1003
1004 l_string := bix_util_pkg.get_hrmiss_frmt(rec.LogoutTime-rec.LoginTime);
1005
1006 select F.meaning into LogoutReason
1007 from FND_LOOKUP_VALUES F
1008 where F.lookup_code = rec.reason
1009 and F.lookup_type = 'IEU_CTRL_BREAK_REASON';
1010 i := i+1;
1011
1012 if i=1 then l_string3 := 'Wrap'; j:=wrap;
1013 elsif i=2 then l_string3 := 'Available'; j:=available;
1014 elsif i=3 then l_string3 := 'Idle'; j:=idle;
1015 else l_string3 := 'Out'; j:=l_out;
1016 end if;
1017
1018 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
1019 values (l_session_id,'BIX_AGENT_DETAIL_RPT', i+1, LogoutReason, l_string ,l_string3,j);
1020 end loop;
1021
1022 insertRowsForGraph(i, l_session_id, available, talk, wrap, idle, l_out);
1023
1024 end if; /*display 3*/
1025
1026 /**********************Skill*************************************/
1027 if v_display_id =4 then
1028
1029 i:=0;
1030
1031 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
1032 values (l_session_id,'BIX_AGENT_DETAIL_RPT',1,'Skill', 'Level',
1033 null,null);
1034 for skilldata in getAgentSkills LOOP
1035 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
1036 values (l_session_id,'BIX_AGENT_DETAIL_RPT',i,skilldata.skillname,skilldata.skilllevel, null, null);
1037 i := i + 1;
1038 end loop;
1039
1040 insert into BIX_DM_REPORT(session_id,report_code, col1, col2, col4,col6,col8)
1041 values (l_session_id,'BIX_AGENT_DETAIL_RPT',1,'', '',
1042 'Talk',talk);
1043 i:=0;
1044 /* for ... */
1045 insertRowsForGraph(i, l_session_id, available, talk, wrap, idle, l_out);
1046
1047
1048 end if; /*display 4 */
1049
1050 END pop_agt_dtl_rpt;
1051
1052
1053 /* populate table for report: Agent status Report */
1054 /* component code: BIX_AGENT_STATUS_REPORT */
1055 /* component type: REPORT
1056 */
1057 PROCEDURE pop_agt_st_rpt(p_context VARCHAR2)
1058 AS
1059 reportcode number;
1060 l_date_low date;
1061 l_date_high date;
1062 l_timestring varchar2(10);
1063 v_group_id number;
1064 v_status_id number;
1065 v_classification number;
1066 l_className varchar2(100);
1067 logoutreason varchar2(100);
1068 l_session_id number;
1069 l_groupName varchar2(100);
1070 l_agentId number;
1071 l_unknown varchar2(100);
1072 v_show number;
1073 l_string varchar2(100);
1074 l_string2 varchar2(100);
1075 i number;
1076 j number;
1077 v_site_id number;
1078
1079 /* get available agents */
1080 Cursor getAvailableAgentList is
1081 Select distinct(I1.resource_id) agentID,
1082 J.resource_name agentName,
1083 (l_date_high - I2.begin_date_time)*24*3600 availTime,
1084 /* J1.group_name groupName,*/
1085 I1.extension extension
1086 from IEU_SH_SESSIONS I1,
1087 IEU_SH_ACTIVITIES I2,
1088 JTF_RS_RESOURCE_EXTNS_VL J,
1089 JTF_RS_GROUPS_VL J1,
1090 JTF_RS_GROUP_MEMBERS J2
1091 where I1.session_id = I2.session_id
1092 and I1.application_id = 696
1093 and I1.active_flag ='T'
1094 and I1.end_date_time is NULL
1095 and I2.activity_type_code = 'MEDIA'
1096 and I2.deliver_date_time is null
1097 and I2.completion_code is null
1098 and I2.active_flag ='T'
1099 and I2.end_date_time is NULL
1100 and I1.begin_date_time between l_date_low and l_date_high
1101 and I1.resource_id = J.resource_id
1102 and (J.resource_id = J2.resource_id)
1103 and (J1.group_id = J2.group_id)
1104 and (J1.group_id = v_group_id)
1105 and (J.server_group_id = v_site_id or (v_site_id = -999 or v_site_id is null)) /* new */
1106 order by J.resource_name;
1107
1108 /* get talking agents */
1109 Cursor getTalkAgentList is
1110 Select distinct(C1.agent_id) agentID,
1111 J.resource_name agentName,
1112 (l_date_high - C1.last_update_date)*24*3600 talkTime,
1113 /* J3.group_name groupName,*/
1114 I1.extension extension,
1115 C2.classification class
1116 from CCT_AGENT_RT_STATS C1,
1117 CCT_MEDIA_ITEMS C2,
1118 JTF_RS_RESOURCE_EXTNS_VL J,
1119 JTF_RS_GROUPS_VL J3,
1120 JTF_RS_GROUP_MEMBERS J4,
1121 IEU_SH_SESSIONS I1,
1122 IEU_SH_ACTIVITIES I2
1123 where C1.has_call = 'T'
1124 and C1.agent_id = J.resource_id
1125 and C2.media_item_id = I2.media_id
1126 and I1.session_id = I2.session_id
1127 and I1.application_id = 696
1128 and I1.resource_id = C1.agent_id
1129 and (J.resource_id = J4.resource_id)
1130 and (J3.group_id = J4.group_id)
1131 and (J3.group_id = v_group_id)
1132 and (C2.server_group_id = v_site_id or (v_site_id =-999 or v_site_id is null))
1133 and C1.last_update_date between l_date_low and l_date_high
1134 order by J.resource_name;
1135
1136 /* get wrapping agents */
1137 Cursor getWrapAgentList is
1138 Select distinct(C1.agent_id) agentID,
1139 J.resource_name agentName,
1140 (l_date_high - C1.last_update_date)*24*3600 wrapTime,
1141 /* J3.group_name groupName,*/
1142 I1.extension extension
1143 from CCT_AGENT_RT_STATS C1,
1144 JTF_RS_RESOURCE_EXTNS_VL J,
1145 JTF_RS_GROUPS_VL J3,
1146 JTF_RS_GROUP_MEMBERS J4,
1147 IEU_SH_SESSIONS I1,
1148 IEU_SH_ACTIVITIES I2
1149 where C1.has_call = 'F'
1150 and C1.agent_id = J.resource_id
1151 /* and C2.media_item_id = I2.media_id */
1152 and I1.session_id = I2.session_id
1153 and I1.application_id = 696
1154 and I1.resource_id = C1.agent_id
1155 and (J.resource_id = J4.resource_id)
1156 and (J3.group_id = J4.group_id)
1157 and (J3.group_id = v_group_id)
1158 and (J.server_group_id = v_site_id or (v_site_id =-999 or v_site_id is null))
1159 and I2.active_flag = 'T'
1160 and I2.completion_code is null
1161 and I2.deliver_date_time is not null
1162 and C1.last_update_date between l_date_low and l_date_high
1163 order by J.resource_name;
1164
1165 /* get idle agents ??? need calculation*/
1166 Cursor getIdleAgentList is
1167 Select distinct(I1.resource_id) agentID,
1168 J5.resource_name agentName,
1169 (l_date_high-I1.begin_date_time)*24*3600 idleTime,
1170 /* J3.group_name groupName,*/
1171 I1.extension extension
1172 from IEU_SH_SESSIONS I1,
1173 JTF_RS_GROUP_MEMBERS J4,
1174 JTF_RS_RESOURCE_EXTNS_VL J5,
1175 JTF_RS_GROUPS_VL J3
1176 where I1.active_flag = 'T'
1177 and I1.application_id = 696
1178 and I1.end_date_time is NULL
1179 and I1.begin_date_time between l_date_low and l_date_high
1180 and I1.resource_id = J5.resource_id
1181 and (J5.resource_id = J4.resource_id)
1182 and (J3.group_id = J4.group_id)
1183 and (J3.group_id = v_group_id)
1184 and (J5.server_group_id = v_site_id or (v_site_id =-999 or v_site_id is null))
1185 and I1.resource_id not in
1186 ( select col3 from BIX_DM_REPORT
1187 where report_code = 'BIX_AGENT_STATUS_REPORT'
1188 and session_id = l_session_id
1189 and (col8 = 'TALK' or col8='WRAP' or col8='AVAILABLE')
1190 )
1191 order by J5.resource_name;
1192
1193
1194 /* get out agents */
1195 Cursor getOutAgentList is
1196 Select distinct(I1.resource_id) agentID,
1197 J5.resource_name agentName,
1198 (l_date_high-I1.end_date_time)*24*3600 loggedoutTime,
1199 /* J3.group_name groupName,*/
1200 I1.extension extension,
1201 I1.end_reason_code reasoncode
1202 from IEU_SH_SESSIONS I1,
1203 JTF_RS_GROUP_MEMBERS J4,
1204 JTF_RS_RESOURCE_EXTNS_VL J5,
1205 JTF_RS_GROUPS_VL J3
1206 /* , FND_LOOKUPS F */
1207 where
1208 (I1.active_flag is null or I1.active_flag='F') /* 'N' */
1209 and I1.application_id = 696
1210 and I1.resource_id = J5.resource_id
1211 and (J5.resource_id = J4.resource_id)
1212 and (J3.group_id = J4.group_id)
1213 and (J3.group_id = v_group_id)
1214 and (J5.server_group_id = v_site_id or (v_site_id =-999 or v_site_id is null)) /* no classification assigned */
1215 and I1.resource_id not in /* make sure no new sessoin logged in by this agent */
1216 (
1217 select distinct(resource_id)
1218 from IEU_SH_SESSIONS
1219 where end_date_time is null
1220 )
1221 order by J5.resource_name;
1222
1223
1224 Cursor getNoneStatusAgentList is
1225 select
1226 J5.resource_name agentName,
1227 J4.resource_id agentID
1228 from JTF_RS_GROUP_MEMBERS J4,
1229 JTF_RS_RESOURCE_EXTNS_VL J5
1230 where J4.group_id = v_group_id
1231 and J5.resource_id = J4.resource_id
1232 and J5.resource_id not in
1233 (select col3
1234 from BIX_DM_REPORT
1235 where report_code = 'BIX_AGENT_STATUS_REPORT'
1236 and session_id = l_session_id
1237 )
1238 order by J5.resource_name;
1239
1240 /*************** begin ************************/
1241 BEGIN
1242
1243 l_session_id:= bix_util_pkg.get_icx_session_id;
1244
1245 delete from BIX_DM_REPORT
1246 where session_id = l_session_id
1247 and report_code = 'BIX_AGENT_STATUS_REPORT';
1248
1249 l_date_low := trunc(sysdate);
1250 l_date_high := sysdate;
1251
1252
1253
1254 /* drill down from agent status bin, q status report */
1255 /* try to get incoming parameter(s) */
1256 l_string := bix_util_pkg.get_parameter_value(p_context,'pContext');
1257
1258 if l_string is not null /*parameter got*/
1259 then
1260 l_string2 := substr(l_string,2,1);
1261 if l_string2 = 'G' then /* drill from Q Status Rpt */
1262 i := instr(l_string,'C',2) ;
1263 v_group_id := to_number( substr(l_string,3,i-3));
1264 j := instr(l_string,'S',i+1);
1265 v_classification :=to_number(substr(l_string,i+1,j-i-1));
1266 v_site_id :=to_number(substr(l_string,j+1));
1267 else /* drill from bin */
1268 v_classification := to_number(l_string);
1269 v_site_id := -999;
1270 v_group_id := -999;
1271 end if;
1272 else
1273 /*
1274 v_classification:= to_number(bix_util_pkg.get_parameter_value(p_context,'P_CLASSIFICATION_ID'));
1275 */
1276 v_site_id := to_number(bix_util_pkg.get_parameter_value(p_context,'P_SITE_ID'));
1277 v_status_id := to_number(bix_util_pkg.get_parameter_value(p_context,'P_STATUS_ID'));
1278 v_group_id := to_number(bix_util_pkg.get_parameter_value(p_context,'P_GROUP_ID'));
1279 end if;
1280
1281
1282 select bix_util_pkg.get_null_lookup into l_unknown
1283 from dual;
1284
1285 /* If the user has selected "All" for agent group paramter , display the default group of the user */
1286 IF (v_group_id = -999 or v_group_id is null) THEN
1287 SELECT fnd_profile.value('BIX_DM_DEFAULT_GROUP')
1288 INTO v_group_id
1289 FROM dual;
1290 END IF;
1291
1292 /* or the user has selected "all" as agent group paramter and (s)he is not assigned to any default group */
1293 IF (v_group_id IS NULL) THEN
1294 RETURN;
1295 END IF;
1296
1297 Select group_name into l_groupName
1298 from JTF_RS_GROUPS_VL
1299 where group_id = v_group_id;
1300
1301 if l_groupName is null
1302 then l_groupName:= l_unknown;
1303 end if;
1304
1305 /* get class name */
1306 /*
1307 if v_classification=-999 then l_className := null;
1308 else
1309 select classification into l_className
1310 from cct_classifications
1311 where
1312 classification_id = v_classification;
1313 end if;
1314 */
1315
1316
1317 /******* start check each status *************/
1318 reportcode:=0;
1319 v_show :=0;
1320
1321
1322 if (v_status_id=3 or v_status_id=-999) then
1323 for rec in getTalkAgentList
1324 loop
1325 reportcode := reportcode +1;
1326 l_timestring := bix_util_pkg.get_hrmiss_frmt(rec.talkTime);
1327 /* for null group name or an agent is not assigned to any group */
1328 /*
1329 l_groupName := l_unknown;
1330 l_agentId := rec.agentID;
1331 for rcd in getGroupName
1332 loop
1333 l_groupName := rcd.groupName;
1334 end loop;
1335 */
1336 if v_show=1 then l_groupName :=null;
1337 end if; /* only one group name is showing */
1338
1339 insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7, col8, col10, col12,col14)
1340 values (l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName, rec.agentID,
1341 rec.agentName, rec.extension,'TALK','TALK',l_timestring,' ', rec.class);
1342 v_show:=1;
1343 end loop;
1344 end if;
1345
1346
1347 if (v_status_id=4 or v_status_id=-999) then
1348
1349 for rec in getWrapAgentList
1350 loop
1351 reportcode := reportcode +1;
1352 l_timestring := bix_util_pkg.get_hrmiss_frmt(rec.wrapTime);
1353 if v_show=1 then l_groupName :=null;
1354 end if;
1355 insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7, col8, col10,col12,col14)
1356 values (l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName, rec.agentID,rec.agentName,
1357 rec.extension,'WRAP','WRAP',l_timestring,'', '');
1358 v_show :=1;
1359 end loop;
1360 end if;
1361
1362
1363 if (v_status_id=2 or v_status_id=-999) then
1364 for rec in getAvailableAgentList
1365 loop
1366 reportcode := reportcode +1;
1367 l_timestring := bix_util_pkg.get_hrmiss_frmt(rec.availTime);
1368 if v_show=1 then l_groupName :=null;
1369 end if;
1370 insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7, col8,
1371 col10,col12, col14)
1372 values (l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName, rec.agentID,
1373 rec.agentName, rec.extension,'AVAIL','AVAILABLE',l_timestring,' ',' ');
1374 v_show :=1;
1375 end loop;
1376 end if;
1377
1378
1379 if (v_status_id=1 or v_status_id=-999) then
1380 for rec in getIdleAgentList
1381 loop
1382 reportcode := reportcode +1;
1383 if v_show=1 then l_groupName :=null;
1384 end if;
1385 l_timestring := bix_util_pkg.get_hrmiss_frmt(trunc(rec.idleTime));
1386 insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7, col8, col10,col12,col14)
1387 values (l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName, rec.agentID,
1388 rec.agentName, rec.extension,'IDLE','IDLE',
1389 l_timestring,' ',' ');
1390
1391 v_show :=1;
1392 end loop;
1393 end if;
1394
1395
1396
1397 if (v_status_id=5 or v_status_id=-999) then
1398
1399 for rec in getOutAgentList
1400 loop
1401 reportcode := reportcode +1;
1402
1403 select F.meaning into logoutreason
1404 from FND_LOOKUP_VALUES F
1405 where F.lookup_code = rec.reasoncode
1406 and F.lookup_type = 'IEU_CTRL_BREAK_REASON';
1407
1408 if v_show=1 then l_groupName :=null;
1409 end if;
1410
1411 insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7, col8, col10,col12,col14)
1412 values (l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName, rec.agentID,rec.agentName, rec.extension,'Out','Out',
1413 bix_util_pkg.get_hrmiss_frmt(rec.loggedoutTime),' ',logoutreason);
1414 v_show :=1;
1415 end loop;
1416 end if;
1417
1418
1419
1420 /* list all agent without status */
1421 /* if 'ALL' is selected for status */
1422 if (v_status_id=-999) then
1423 for rec in getNoneStatusAgentList
1424 loop
1425 reportcode := reportcode +1;
1426
1427 if v_show=1 then l_groupName :=null;
1428 end if;
1429
1430 insert into BIX_DM_REPORT(session_id,report_code, col1, col2,col3, col4,col6, col7,
1431 col8, col10,col12,col14)
1432 values(l_session_id,'BIX_AGENT_STATUS_REPORT',reportcode,l_groupName,rec.agentID,rec.agentName,
1433 ' ','None','None',' ',' ',' ');
1434 v_show :=1;
1435 end loop;
1436 end if;
1437
1438
1439 END pop_agt_st_rpt;
1440
1441
1442 END BIX_REAL_TIME_RPTS_PKG;
[Home] [Help]