[Home] [Help]
PACKAGE BODY: APPS.BIX_DM_AGENT_CALL_SUMMARY_PKG
Source
1 PACKAGE BODY BIX_DM_AGENT_CALL_SUMMARY_PKG AS
2 /*$Header: bixxcalb.pls 115.109 2004/04/13 11:36:01 suray ship $ */
3
4 g_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID();
5 g_program_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID();
6 g_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID();
7 g_user_id NUMBER := FND_GLOBAL.USER_ID();
8 g_insert_count NUMBER := 0;
9 g_delete_count NUMBER := 0;
10 g_program_start_date DATE := SYSDATE;
11 g_collect_start_date DATE;
12 g_collect_end_date DATE;
13 g_error_mesg VARCHAR2(4000) := NULL;
14 g_status VARCHAR2(4000) := 'FAILED';
15 g_table_name VARCHAR2(100) := 'BIX_DM_INTERFACE';
16 g_proc_name VARCHAR2(4000);
17 g_min_call_begin_date DATE;
18 g_max_call_begin_date DATE;
19 g_rounded_collect_start_date DATE;
20 g_rounded_collect_end_date DATE;
21 g_commit_chunk_size number;
22 g_preferred_currency VARCHAR2(15);
23 g_conversion_type VARCHAR2(30);
24 g_bix_schema VARCHAR2(30) := 'BIX';
25
26 g_dial_count NUMBER :=0;
27 g_contact_count NUMBER :=0;
28 g_noncontact_count NUMBER :=0;
29 g_abandon_count NUMBER :=0;
30 g_busy_count NUMBER :=0;
31 g_rna_count NUMBER :=0;
32 g_ansmc_count NUMBER :=0;
33 g_sit_count NUMBER :=0;
34 g_pr_count NUMBER :=0;
35 g_connect_count NUMBER :=0;
36 g_nonconnect_count NUMBER :=0;
37 g_other_count NUMBER :=0;
38 g_debug_flag VARCHAR2(1) := 'N';
39
40 G_DATE_MISMATCH EXCEPTION;
41
42 g_interaction_resource NUMBER;
43 g_ao_dummy_resource NUMBER;
44
45 /*======================================================================================================+
46 | WRITE_LOG procedure writes error message into FND log file and also calls INSERT_LOG procedure |
47 | for writing error details into BIX_DM_COLLECT_LOG table when ever any procedure fails in this Package.|
48 ======================================================================================================+*/
49
50 -- GET_CALLS collects calls from OLTP to the temporary
51 -- Deletes in chunks, thus easing the rollback segments problem.
52 -- The chunk size (in rows) can be defined by the user by setting the
53 -- BIX_DM_DELETE_SIZE profile. If this is not set, then the default
54 -- chunk size is 100 rows.
55
56 /*===================================================================================================+
57 | INSERT_LOG procedure inserts collection concurrent program status into BIX_DM_COLLECT_LOG table |
58 | It inserts a row with the following details : |
59 | |
60 | COLLECT_STATUS column equals to FAILED if the program failed otherwise SUCCESS |
61 | COLLECT_EXCEP_MESG as error message if the program failed otherwise NULL |
62 | RUN_START_DATE equals to start date time when the collection program started runnning |
63 | RUN_END_DATE equals end date time of the collection program finished |
64 | COLLECT_START_DATE Collection start date specified by the user in the cuncurrent program parameter |
65 | COLLECT_END_DATE Collection end date specified by the user in the cuncurrent program parameter |
66 ====================================================================================================+*/
67
68 PROCEDURE INSERT_LOG
69 AS
70 l_bix_collect_log_seq NUMBER;
71 BEGIN
72
73 SELECT BIX_DM_COLLECT_LOG_S.NEXTVAL INTO l_bix_collect_log_seq FROM DUAL;
74
75 /* Insert status into log table */
76
77 INSERT INTO BIX_DM_COLLECT_LOG
78 (
79 COLLECT_ID,
80 COLLECT_CONCUR_ID,
81 OBJECT_NAME,
82 OBJECT_TYPE,
83 RUN_START_DATE,
84 RUN_END_DATE,
85 COLLECT_START_DATE,
86 COLLECT_END_DATE,
87 COLLECT_STATUS,
88 COLLECT_EXCEP_MESG,
89 ROWS_INSERTED,
90 ROWS_DELETED,
91 LAST_UPDATE_DATE,
92 LAST_UPDATED_BY,
93 CREATION_DATE,
94 CREATED_BY,
95 LAST_UPDATE_LOGIN,
96 REQUEST_ID,
97 PROGRAM_APPLICATION_ID,
98 PROGRAM_ID,
99 PROGRAM_UPDATE_DATE
100 )
101 VALUES
102 (
103 l_bix_collect_log_seq,
104 NULL,
105 g_table_name,
106 'TABLE',
107 g_program_start_date,
108 SYSDATE,
109 g_collect_start_date,
110 g_collect_end_date,
111 g_status,
112 g_error_mesg,
113 g_insert_count,
114 g_delete_count,
115 SYSDATE,
116 g_user_id,
117 SYSDATE,
118 g_user_id,
119 g_user_id,
120 g_request_id,
121 g_program_appl_id,
122 g_program_id,
123 SYSDATE
124 );
125 COMMIT;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 raise;
130 END INSERT_LOG;
131
132 PROCEDURE WRITE_LOG(p_msg VARCHAR2, p_proc_name VARCHAR2) IS
133 BEGIN
134
135 fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')|| p_proc_name || ' : '||p_msg);
136
137 END WRITE_LOG;
138
139 PROCEDURE DELETE_IN_CHUNKS(table_name in varchar2,
140 where_condition in varchar2 ,
141 rows_deleted out nocopy number)
142 is
143 l_delete_statement varchar2(4000);
144 l_rows_deleted number;
145 e_statement_too_long EXCEPTION;
146 e_invalid_condition EXCEPTION;
147
148 begin
149
150 --
151 --Changed on 08/21/2003 - SQL statement is not allowed to
152 --have variables concatenated to it. Change to actual statement
153 --
154
155 l_rows_deleted := 0;
156
157 loop
158
159 if upper(table_name) = 'BIX_DM_CALL_SUM'
160 then
161 DELETE BIX_DM_CALL_SUM
162 WHERE period_start_date_time BETWEEN g_min_call_begin_date
163 AND g_max_call_begin_date
164 AND rownum <= g_commit_chunk_size;
165 elsif upper(table_name) = 'BIX_DM_AGENT_SUM'
166 then
167 DELETE BIX_DM_AGENT_SUM
168 WHERE period_start_date_time BETWEEN g_min_call_begin_date
169 AND g_max_call_begin_date
170 AND rownum <= g_commit_chunk_size;
171 elsif upper(table_name) = 'BIX_DM_GROUP_SUM'
172 then
173 DELETE BIX_DM_GROUP_SUM
174 WHERE period_start_date_time BETWEEN g_min_call_begin_date
175 AND g_max_call_begin_date
176 AND rownum <= g_commit_chunk_size;
177 elsif upper(table_name) = 'BIX_DM_AGENT_OUTCOME_SUM'
178 then
179 DELETE BIX_DM_AGENT_OUTCOME_SUM
180 WHERE period_start_date_time BETWEEN g_min_call_begin_date
181 AND g_max_call_begin_date
182 AND rownum <= g_commit_chunk_size;
183 elsif upper(table_name) = 'BIX_DM_GROUP_OUTCOME_SUM'
184 then
185 DELETE BIX_DM_GROUP_OUTCOME_SUM
186 WHERE period_start_date_time BETWEEN g_min_call_begin_date
187 AND g_max_call_begin_date
188 AND rownum <= g_commit_chunk_size;
189 elsif upper(table_name) = 'BIX_DM_EXCEL'
190 then
191 DELETE BIX_DM_EXCEL
192 WHERE creation_date < SYSDATE-2/24
193 AND rownum <= g_commit_chunk_size;
194 else
195 RAISE e_invalid_condition;
196 end if;
197
198 --execute immediate l_delete_statement;
199
200 l_rows_deleted := l_rows_deleted + SQL%ROWCOUNT;
201
202 -- dbms_output.put_line('Rows deleted: '||to_char(l_rows_deleted));
203
204 if SQL%ROWCOUNT < g_commit_chunk_size then
205 commit;
206 exit;
207 else
208 commit;
209 end if;
210
211 end loop;
212
213 rows_deleted := l_rows_deleted;
214
215 EXCEPTION
216 when e_invalid_condition then
217 g_proc_name := 'BIX_DM_AGENT_CALL_SUMMARY_PKG.DELETE_IN_CHUNKS';
218 g_error_mesg := 'Invalid IF condition in delete ';
219 raise;
220 when e_statement_too_long then
221 g_error_mesg := ' SQL Statement too long (4000 char or more). ';
222 when others then
223 g_proc_name := 'BIX_DM_AGENT_CALL_SUMMARY_PKG.DELETE_IN_CHUNKS';
224 g_error_mesg := g_proc_name || g_error_mesg ||' : '|| sqlerrm;
225 raise;
226 end;
227
228 -- campaign list and sublist info for the call (only if the call has a campaign
229 -- schedule id associated with it)
230
231 PROCEDURE GET_LIST_INFO ( p_source_item_id in NUMBER,
232 p_campaign_id OUT NOCOPY NUMBER,
233 p_campaign_schedule_id OUT NOCOPY NUMBER,
234 p_source_list_id OUT NOCOPY NUMBER,
235 p_sublist_id OUT NOCOPY NUMBER,
236 p_dialing_method OUT NOCOPY VARCHAR2) IS
237
238 BEGIN
239
240 select a.list_subset_id,b.list_header_id,c.campaign_id,c.schedule_id,d.dialing_method
241 INTO p_sublist_id,p_source_list_id,p_campaign_id,p_campaign_schedule_id,p_dialing_method
242 from
243 iec_g_list_subsets a,
244 ams_act_lists b,
245 AMS_CAMPAIGN_SCHEDULES_VL c,
246 ams_list_headers_all d
247 where
248 a.list_header_id = b.list_header_id AND
249 b.list_used_by_id = c.schedule_id AND
250 b.list_act_type = 'TARGET' AND
251 b.list_used_by = 'CSCH' AND
252 b.list_header_id = d.list_header_id AND
253 a.list_subset_id = p_source_item_id ;
254 EXCEPTION
255 WHEN OTHERS THEN
256 p_source_list_id := NULL;
257 p_sublist_id := NULL;
258 p_campaign_id := NULL;
259 p_campaign_schedule_id := NULL;
260 p_dialing_method := NULL;
261
262 END;
263
264 FUNCTION GET_DNIS(p_dnis in varchar2) return NUMBER is
265
266 l_dnis number;
267
268 BEGIN
269 if p_dnis is not null then
270 select DNIS_ID into l_dnis from bix_dm_dnis where dnis = p_dnis;
271 return l_dnis;
272 else
273 return null;
274 end if;
275
276 EXCEPTION
277 WHEN NO_DATA_FOUND THEN
278 select bix.bix_dm_dnis_s.nextval into l_dnis from dual;
279 insert into bix_dm_dnis (dnis_id,dnis,last_update_date,last_updated_by,
280 creation_date) values (l_dnis,p_dnis,sysdate,g_user_id,
281 sysdate);
282 commit;
283 return l_dnis;
284 WHEN OTHERS THEN
285 return null;
286 END;
287
288
289
290
291 -- Leads Amount:
292
293 procedure GET_LEAD_AMOUNT ( p_lead_id in number,
294 p_resource_id in number,
295 p_act_start_time in DATE,
296 p_leads_amount out NOCOPY NUMBER,
297 p_currency_code out NOCOPY varchar2
298 ) is
299
300 BEGIN
301
302
303 select budget_amount,currency_code into p_leads_amount, p_currency_code
304 from as_sales_leads asl, jtf_rs_resource_extns res
305 where asl.sales_lead_id = p_lead_id
306 and asl.created_by = res.user_id
307 --and asl.creation_date >= p_act_start_time
308 and res.resource_id = p_resource_id;
309
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 p_leads_amount := 0;
314 p_currency_code := NULL;
315 END;
316 -- Opportunity amount:
317
318 procedure GET_OPPORTUNITY_AMOUNT ( p_opp_id in NUMBER,
319 p_resource_id in number,
320 p_act_start_time in date,
321 p_opp_won out NOCOPY NUMBER,
322 p_opp_amount out NOCOPY NUMBER,
323 p_currency_code out NOCOPY varchar2) is
324
325 BEGIN
326
327 select total_amount,currency_code,1
328 into p_opp_amount, p_currency_code,p_opp_won
329 from as_leads_all a,
330 as_statuses_vl asv,
331 jtf_rs_resource_extns res
332 where a.lead_id = p_opp_id
333 and a.status = asv.status_code
334 and asv.win_loss_indicator = 'W'
335 and res.resource_id = p_resource_id
336 and a.created_by = res.user_id
337 --and a.creation_date >= p_act_start_time;
338 ;
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 p_opp_amount := 0;
343 p_currency_code := NULL;
344 p_opp_won := 0;
345 END;
346
347 -- SR Status:
348
349 procedure GET_SR_STATUS (p_sr_id in number,
350 p_resource_id in number,
351 p_act_start_time in date,
352 p_sr_status out NOCOPY number) is
353
354 v_max_date DATE;
355
356 BEGIN
357
358 --select incident_status_id into p_sr_status from cs_incidents_all_b
359 --where INCIDENT_ID = p_sr_id;
360
361 --
362 --Find out if the agent changed the status from OPEN to CLOSED.
363 --We have to compare it with the activity start time and make sure
364 --the creation_date is greater than the activity start time. Also, there
365 --might be multiple records which meet this criteria, in case the agent
366 --updated the same SR multiple times. In order to take care of the case
367 --where the agnet has updated the same SR multiple times, we use a ORDER BY clause
368 --and ROWNUM = 1.
369 --
370
371 select incident_status_id
372 into p_sr_status
373 from
374 (
375 select incident_status_id
376 from CS_INCIDENTS_AUDIT_B AUD, JTF_RS_RESOURCE_EXTNS RES
377 where AUD.INCIDENT_ID = p_sr_id
378 and AUD.last_update_date >= p_act_start_time
379 and RES.resource_id = p_resource_id
380 and AUD.last_updated_by = RES.user_id
381 --and AUD.old_incident_status_id IN (1,3) -- open status
382 --and AUD.incident_status_id IN (2,4) -- closed status
383 and AUD.change_incident_status_flag = 'Y'
384 order by AUD.last_update_date
385 )
386 where rownum = 1;
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 p_sr_status := 0;
391
392 END;
393
394
395
396 -- Retrievs source_code from the interactions table. If multiple records in
397 -- interactions with different source_codes (should not happen), retrieve
398 -- the first non null.
399
400 FUNCTION GET_SOURCE_CODE (p_media_id in varchar2) return VARCHAR2 is
401
402 v_source_code varchar2(30);
403
404
405 BEGIN
406
407 BEGIN
408
409 select SOURCE_CODE
410 into v_source_code
411 from jtf_ih_interactions
412 where productive_time_amount = p_media_id
413 and SOURCE_CODE is not null
414 and rownum = 1;
415
416 EXCEPTION
417 WHEN NO_DATA_FOUND
418 THEN
419 select int.SOURCE_CODE
420 into v_source_code
421 from jtf_ih_interactions int, jtf_ih_activities act
422 where int.interaction_id = act.interaction_id
423 AND act.media_id = p_media_id
424 and int.SOURCE_CODE is not null
425 and rownum = 1;
426
427 END;
428
429 return v_source_code;
430
431 EXCEPTION
432 WHEN OTHERS THEN
433 return NULL;
434
435 END;
436
437
438 -- Retrievs entity type - if campaign retrns 'CAMP', if campaign schedule,
439 -- returns 'CSCH'.
440
441 FUNCTION GET_CAMPAIGN_CODE (p_source_code in varchar2) return VARCHAR2 is
442
443 sc_code varchar2(10);
444
445
446 BEGIN
447
448 select ARC_SOURCE_CODE_FOR into sc_code from ams_source_codes
449 where source_code = p_source_code;
450
451 return sc_code;
452
453 EXCEPTION
454 WHEN OTHERS THEN
455 return NULL;
456 END;
457
458
459 procedure GET_CAMPAIGN_INFO (p_source_code in varchar2,
460 p_campaign_id out NOCOPY number,
461 p_campaign_schedule_id out NOCOPY number)
462 is
463
464 v_sc_code varchar2(10);
465 begin
466
467 select ARC_SOURCE_CODE_FOR into v_sc_code from ams_source_codes
468 where source_code = p_source_code;
469
470 if v_sc_code = 'CAMP' then
471 select campaign_id into p_campaign_id
472 from
473 ams_campaigns_all_b
474 where
475 source_code = p_source_code;
476
477 p_campaign_schedule_id := NULL;
478
479 elsif v_sc_code = 'CSCH' then
480 select campaign_schedule_id into p_campaign_schedule_id
481 from AMS_CAMPAIGN_SCHEDULES
482 where
483 source_code = p_source_code;
484
485 p_campaign_id := NULL;
486 else
487 p_campaign_id := NULL;
488 p_campaign_schedule_id := NULL;
489 end if;
490
491 EXCEPTION
492 WHEN OTHERS THEN
493 p_campaign_id := NULL;
494 p_campaign_schedule_id := NULL;
495 end;
496
497
498 -- Retrieves interaction end time to calculate wrap time:
499
500 FUNCTION GET_INTERACTION_END_TIME (p_resource_id in number,
501 p_media_id in number) return date is
502
503 int_end_date_time date;
504
505
506 BEGIN
507
508 select max(end_date_time)
509 into int_end_date_time
510 from jtf_ih_interactions
511 where resource_id = p_resource_id
512 and productive_time_amount = p_media_id;
513
514 IF int_end_date_time IS NULL
515 THEN
516
517 select max(int.end_date_time)
518 into int_end_date_time
519 from jtf_ih_interactions int, jtf_ih_activities act
520 where int.resource_id = p_resource_id
521 and int.interaction_id = act.interaction_id
522 AND act.media_id = p_media_id;
523
524 END IF;
525
526 return int_end_date_time;
527
528 EXCEPTION
529 WHEN OTHERS THEN
530 return NULL;
531
532 END;
533
534 PROCEDURE get_outcome_counts (P_MEDIA_ID IN NUMBER,
535 P_RESOURCE_ID IN NUMBER)
536 IS
537
538 v_resource_id NUMBER;
539
540 BEGIN
541
542 g_dial_count :=0;
543 g_contact_count :=0;
544 g_noncontact_count :=0;
545 g_abandon_count :=0;
546 g_busy_count :=0;
547 g_rna_count :=0;
548 g_ansmc_count :=0;
549 g_sit_count :=0;
550 g_pr_count :=0;
551 g_connect_count :=0;
552 g_nonconnect_count :=0;
553 g_other_count :=0;
554
555 BEGIN
556
557 IF (g_debug_flag = 'Y') THEN
558 write_log('Entered get_outcome counts', g_proc_name);
559 END IF;
560
561 g_interaction_resource := NULL;
562
563 --select earliest agent who made/took the call
564 select resource_id
565 into v_resource_id
566 from jtf_ih_interactions int1, jtf_ih_activities act1
567 where int1.start_date_time =
568 (select min(int2.start_date_time)
569 from jtf_ih_interactions int2, jtf_ih_activities act2
570 where act2.media_id = p_media_id
571 and act2.interaction_id = int2.interaction_id
572 and int2.resource_id <> 0 --avoid PREVIEW calls have resourceid of 0
573 and int2.resource_id <> g_ao_dummy_resource
574 )
575 and act1.interaction_id = int1.interaction_id
576 and act1.media_id = p_media_id
577 and int1.resource_id <> 0 --avoid PREVIEW calls with resourceid of 0
578 and int1.resource_id <> g_ao_dummy_resource
579 and rownum = 1
580 ;
581
582 IF (g_debug_flag = 'Y') THEN
583 write_log('p_resource id is ' || p_resource_id, g_proc_name);
584 write_log('v_resource id is ' || v_resource_id, g_proc_name);
585 END IF;
586
587 g_interaction_resource := v_resource_id;
588
589 EXCEPTION
590 WHEN NO_DATA_FOUND
591 THEN
592 IF (g_debug_flag = 'Y') THEN
593 write_log('Exception - no with agent segs', g_proc_name);
594 END IF;
595 --no with_agent segments --- credit 1 call at call row type
596 v_resource_id := NULL;
597 g_dial_count := 1; --credit 1 call at call row type
598 WHEN OTHERS
599 THEN
600 IF (g_debug_flag = 'Y') THEN
601 write_log('Some other exception ' ||sqlerrm, g_proc_name);
602 END IF;
603 v_resource_id := NULL;
604 END;
605
606 IF p_resource_id IS NOT NULL
607 THEN
608
609 IF (g_debug_flag = 'Y') THEN
610 write_log('p_resource_id is not null', g_proc_name);
611 END IF;
612
613 IF p_resource_id = v_resource_id --earliest agent
614 THEN
615
616 IF (g_debug_flag = 'Y') THEN
617 write_log('p = v hence dial count is 1', g_proc_name);
618 END IF;
619
620 g_dial_count := 1;
621 END IF;
622
623 SELECT MAX(DECODE(clook.contact_flag,'Y',1,0)) contacts,
624 MAX(DECODE(clook.contact_flag,'N',1,0)) noncontacts,
625 MAX(DECODE(int.outcome_id,11,1,0)) abandoned,
626 MAX(DECODE(int.outcome_id,2,1,0)) busy,
627 MAX(DECODE(int.outcome_id,1,1,0)) ring_no_ansewr,
628 MAX(DECODE(int.outcome_id,6,1,0)) answering_machine,
629 MAX(DECODE(int.outcome_id,22,1,23,1,24,1,25,1,0)) sit,
630 MAX(DECODE(rlook.positive_response_flag,'Y',1,0)) presp,
631 MAX(DECODE(clook.connect_flag,'Y',1,0)) connects,
632 MAX(DECODE(clook.connect_flag,'N',1,0)) nonconnects,
633 MAX(DECODE(int.outcome_id,7,0,11,0,2,0,1,0,22,0,23,0,24,0,25,0,26,0,
634 decode(clook.connect_flag,'Y',0,'N',0,1))) others
635 INTO g_contact_count,
636 g_noncontact_count,
637 g_abandon_count,
638 g_busy_count,
639 g_rna_count,
640 g_ansmc_count,
641 g_sit_count,
642 g_pr_count,
643 g_connect_count,
644 g_nonconnect_count,
645 g_other_count
646 from jtf_ih_interactions int,
647 jtf_ih_activities act,
648 bix_dm_connect_lookups clook,
649 bix_dm_response_lookups rlook
650 where int.interaction_id = act.interaction_id
651 and int.resource_id = p_resource_id
652 and act.media_id = p_media_id
653 AND int.outcome_id = clook.outcome_id (+)
654 --AND int.outcome_id = rlook.outcome_id (+)
655 AND int.result_id = rlook.result_id (+);
656
657 ELSIF p_resource_id IS NULL
658 THEN
659
660 --Sometimes the resourceid might be valid at interaction level but
661 --not at the lifecycle segment. In this case we need to set dial count
662 --to 1 here as it will not be trapped in the EXCEPTION condition in the
663 --beginning of this procedure.
664
665 g_dial_count := 1;
666
667 IF (g_debug_flag = 'Y') THEN
668 write_log('p_resourceid is null', g_proc_name);
669 END IF;
670
671 SELECT MAX(DECODE(clook.contact_flag,'Y',1,0)) contacts,
672 MAX(DECODE(clook.contact_flag,'N',1,0)) noncontacts,
673 MAX(DECODE(int.outcome_id,11,1,0)) abandoned,
674 MAX(DECODE(int.outcome_id,2,1,0)) busy,
675 MAX(DECODE(int.outcome_id,1,1,0)) ring_no_ansewr,
676 MAX(DECODE(int.outcome_id,6,1,0)) answering_machine,
677 MAX(DECODE(int.outcome_id,22,1,23,1,24,1,25,1,0)) sit,
678 MAX(DECODE(rlook.positive_response_flag,'Y',1,0)) presp,
679 MAX(DECODE(clook.connect_flag,'Y',1,0)) connects,
680 MAX(DECODE(clook.connect_flag,'N',1,0)) nonconnects,
681 MAX(DECODE(int.outcome_id,7,0,11,0,2,0,1,0,22,0,23,0,24,0,25,0,26,0,
682 decode(clook.connect_flag,'Y',0,'N',0,1))) others
683 INTO g_contact_count,
684 g_noncontact_count,
685 g_abandon_count,
686 g_busy_count,
687 g_rna_count,
688 g_ansmc_count,
689 g_sit_count,
690 g_pr_count,
691 g_connect_count,
692 g_nonconnect_count,
693 g_other_count
694 from jtf_ih_interactions int,
695 jtf_ih_activities act,
696 bix_dm_connect_lookups clook,
697 bix_dm_response_lookups rlook
698 where int.interaction_id = act.interaction_id
699 and act.media_id = p_media_id
700 AND int.outcome_id = clook.outcome_id (+)
701 --AND int.outcome_id = rlook.outcome_id (+)
702 AND int.result_id = rlook.result_id (+);
703 END IF;
704
705 EXCEPTION
706 WHEN OTHERS
707 THEN
708
709 IF (g_debug_flag = 'Y') THEN
710 write_log('Exception occured '||sqlerrm, g_proc_name );
711 END IF;
712
713 g_dial_count :=0;
714 g_contact_count :=0;
715 g_noncontact_count :=0;
716 g_abandon_count :=0;
717 g_busy_count :=0;
718 g_rna_count :=0;
719 g_ansmc_count :=0;
720 g_sit_count :=0;
721 g_pr_count :=0;
722 g_connect_count :=0;
723 g_nonconnect_count :=0;
724 g_other_count :=0;
725
726 END get_outcome_counts;
727
728 --
729 --Function to calculate the preview time
730 --
731
732 FUNCTION GET_PREVIEW_TIME (p_media_id in number,
733 p_resource_id in number)
734 RETURN NUMBER IS
735
736 l_talk_start_time DATE;
737 l_int_start_time DATE;
738 l_int_end_time DATE;
739 l_preview_time NUMBER := 0;
740
741 BEGIN
742
743 SELECT min(start_date_time)
744 INTO l_talk_start_time
745 FROM jtf_ih_media_item_lc_segs
746 WHERE media_id = p_media_id
747 AND resource_id = p_resource_id;
748
749 SELECT min(start_date_time), max(end_date_time)
750 INTO l_int_start_time, l_int_end_time
751 FROM jtf_ih_interactions int
752 WHERE resource_id = p_resource_id
753 AND
754 (
755 productive_time_amount = p_media_id
756 OR EXISTS (SELECT act.interaction_id from jtf_ih_activities act
757 WHERE act.media_id = p_media_id
758 AND act.interaction_id = int.interaction_id
759 )
760 );
761
762 l_preview_time := (least(l_talk_start_time, l_int_end_time) - l_int_start_time)*24*60*60;
763
764 return nvl(l_preview_time, 0);
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 return 0;
769
770 END GET_PREVIEW_TIME;
771
772
773 -- Calculates wrap time:
774
775 PROCEDURE GET_WRAP_TIME ( p_resource_id in number,
776 p_media_id in number,
777 p_ag_end_date_time in date,
778 p_direction in varchar2,
779 p_in_wrap_time out NOCOPY number,
780 p_out_wrap_time out NOCOPY number) is
781
782 l_wrap_time date;
783
784 BEGIN
785
786 l_wrap_time := GET_INTERACTION_END_TIME(p_resource_id, p_media_id);
787
788 if l_wrap_time is NULL or
789 l_wrap_time <= p_ag_end_date_time then
790 p_in_wrap_time := 0;
791 p_out_wrap_time := 0;
792 else
793 if p_direction = 'INBOUND' then
794 p_in_wrap_time := (l_wrap_time - p_ag_end_date_time) * 86400;
795 p_out_wrap_time := 0;
796 else
797 p_in_wrap_time := 0;
798 p_out_wrap_time := (l_wrap_time - p_ag_end_date_time) * 86400;
799 end if;
800 end if;
801
802 END;
803
804
805
806
807 FUNCTION GET_CLASSIFICATION (
808 p_classification in varchar2,
809 p_date in date
810 ) return NUMBER is
811
812 v_classification_value_id number;
813
814 BEGIN
815
816 BEGIN
817
818 --
819 --Classification value cannot be duplicated
820 --for non-deleted classifications
821 --
822 select classification_value_id
823 into v_classification_value_id
824 from cct_classification_values
825 where classification_value = p_classification
826 and ( f_deletedflag <> 'D'
827 or f_deletedflag IS NULL
828 )
829 and creation_date < p_date;
830
831 return v_classification_value_id;
832
833 EXCEPTION
834 WHEN NO_DATA_FOUND
835 THEN
836 select max(classification_value_id)
837 into v_classification_value_id
838 from cct_classification_values
839 where classification_value = p_classification
840 and f_deletedflag = 'D'
841 and creation_date < p_date
842 and last_update_date > p_date;
843
844 return v_classification_value_id;
845
846 END;
847
848 EXCEPTION
849 WHEN OTHERS THEN
850 return NULL;
851
852 END;
853
854
855 PROCEDURE GET_GOALS (p_classification_value_id in number,
856 p_min_call_treshold_goal out NOCOPY number,
857 p_sl_seconds_goal out NOCOPY number) is
858
859 v_num_rows_returned number;
860
861
862 BEGIN
863 select count(*)
864 into v_num_rows_returned
865 from bix_dm_goals
866 WHERE classification_value_id = p_classification_value_id
867 and end_date_active IS NULL;
868
869 if v_num_rows_returned = 1 then
870 SELECT min_call_treshold_goal,sl_seconds_goal into
871 p_min_call_treshold_goal, p_sl_seconds_goal
872 FROM bix_dm_goals
873 WHERE classification_value_id = p_classification_value_id
874 and end_date_active IS NULL;
875 elsif v_num_rows_returned = 0 then
876 SELECT min_call_treshold_goal,sl_seconds_goal into
877 p_min_call_treshold_goal, p_sl_seconds_goal
878 FROM bix_dm_goals
879 WHERE classification_value_id = -999
880 and end_date_active IS NULL;
881 end if;
882
883 EXCEPTION
884 WHEN OTHERS THEN
885 p_min_call_treshold_goal := 0;
886 p_sl_seconds_goal := 0;
887 END;
888
889 FUNCTION IS_OTS_INSTALLED return VARCHAR2 is
890
891 v_is_installed varchar2(1);
892
893 BEGIN
894
895 select application_installed into v_is_installed
896 from bix_dm_apps_dependency
897 where application_short_name = 'BIX_DM_OTS_INSTALLED';
898
899 return v_is_installed;
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 return 'N';
904 END;
905
906 FUNCTION IS_OAO_INSTALLED return VARCHAR2 is
907
908 v_is_installed varchar2(1);
909
910 BEGIN
911
912 select application_installed into v_is_installed
913 from bix_dm_apps_dependency
914 where application_short_name = 'BIX_DM_OAO_INSTALLED';
915
916 return v_is_installed;
917
918 EXCEPTION
919 WHEN OTHERS THEN
920 return 'N';
921 END;
922
923 FUNCTION IS_OSR_INSTALLED return VARCHAR2 is
924
925 v_is_installed varchar2(1);
926
927 BEGIN
928
929 select application_installed into v_is_installed
930 from bix_dm_apps_dependency
931 where application_short_name = 'BIX_DM_OSR_INSTALLED';
932
933 return v_is_installed;
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 return 'N';
938 END;
939
940
941
942 -- table BIX_DM_CALL_INTERFACE:
943
944 PROCEDURE GET_CALLS
945 AS
946
947 v_classification_value_id number;
948 v_calls_in_queue number;
949 v_in_calls_handled number;
950 v_calls_transferred number;
951 v_out_calls_handled number;
952 v_in_talk_time number;
953 v_in_wrap_time number;
954 v_out_talk_time number;
955 v_out_wrap_time number;
956 v_campaign_id number;
957 v_campaign_schedule_id number;
958 v_campaign_schedule varchar2(30);
959 v_total_in_talk_time number;
960 v_total_out_talk_time number;
961 v_ivr_time number;
962 v_route_time number;
963 v_queue_time number;
964 v_abandon_time number;
965 v_resource_id number;
966 l_current_start_date_time date;
967 l_current_end_date_time date;
968 l_call_answered_by_r_id number;
969 l_campaign_code varchar2(10);
970 l_counter number;
971 l_current_resource_id number;
972 l_max_end_date date;
973 l_wrap_time number;
974 l_with_agent_segs number;
975 l_delete_size NUMBER := 0;
976 l_source_code varchar2(30);
977 l_source_list_id number;
978 l_sublist_id number;
979 l_dialing_method varchar2(100);
980 l_leads_amount number;
981 l_opp_amount number;
982 v_leads_amount number;
983 v_opp_amount number;
984 l_leads_amount_txn number;
985 l_opp_amount_txn number;
986 v_leads_amount_txn number;
987 v_opp_amount_txn number;
988 l_leads_created number;
989 l_leads_updated number;
990 l_sr_created number;
991 l_sr_opened number;
992 l_sr_closed number;
993 l_sr_info_req number;
994 l_opp_created number;
995 l_opp_updated number;
996 l_opp_cross_sold number;
997 l_opp_up_sold number;
998 l_opp_declined number;
999 l_opp_won number;
1000 v_opp_won number;
1001 l_currency_code varchar2(15);
1002 l_sr_status number := 0;
1003 l_activity_counter number := 0;
1004 l_dnis varchar(30) := NULL;
1005 l_first_outcome_id number := NULL;
1006 l_prev_outcome_id number := NULL;
1007 l_in_cls_hdld_gt_thn_x_tm number := 0;
1008 l_ou_cls_hdld_gt_thn_x_tm number := 0;
1009 l_calls_answrd_within_x_time number := 0;
1010 l_min_call_treshold_goal number := 0;
1011 l_sl_seconds_goal number := 30;
1012 l_queue_time_for_calls_handled number := 0;
1013 l_number_of_rerouts number := 0;
1014
1015 l_outcome_id NUMBER := NULL;
1016 l_result_id NUMBER := NULL;
1017 l_reason_id NUMBER := NULL;
1018 l_has_agent_segs VARCHAR2(1) := 'N';
1019
1020 l_agent_preview_time NUMBER := 0;
1021 l_call_preview_time NUMBER := 0;
1022
1023 CURSOR call_info IS
1024 SELECT ih_mitem.media_id MEDIA_ID,
1025 nvl(ih_mitem.server_group_id, -1) SERVER_GROUP_ID,
1026 CLASSIFICATION CLASSIFICATION,
1027 ih_mitem.dnis DNIS,
1028 ih_mitem.direction DIRECTION,
1029 TRUNC(ih_mitem.start_date_time) PERIOD_START_DATE,
1030 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,
1031 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,
1032 DECODE(UPPER(ih_mitem.direction),'INBOUND',1,0) CALLS_OFFERED,
1033 DECODE(UPPER(ih_mitem.direction),'INBOUND',DECODE(UPPER(ih_mitem.media_abandon_flag),'Y',1,0),0) CALLS_ABANDONED ,
1034 ih_mitem.media_abandon_flag MEDIA_ABANDON_FLAG ,
1035 ih_mitem.source_item_id SOURCE_ITEM_ID,
1036 ih_mitem.start_date_time CALL_START_TIME
1037 FROM JTF_IH_MEDIA_ITEMS ih_mitem
1038 WHERE ih_mitem.start_date_time BETWEEN g_min_call_begin_date AND g_max_call_begin_date
1039 AND
1040 (
1041 ih_mitem.media_item_type = 'TELE_INB' or
1042 ih_mitem.media_item_type = 'TELE_DIRECT' or
1043 ih_mitem.media_item_type = 'TELEPHONE' or
1044 ih_mitem.media_item_type = 'CALL' or
1045 ih_mitem.media_item_type = 'TELE_MANUAL' or
1046 ih_mitem.media_item_type = 'TELE_WEB'
1047 )
1048 AND ih_mitem.active = 'N' ;
1049
1050 CURSOR agent_info (p_media_id NUMBER) is
1051 select msegs.resource_id RESOURCE_ID,
1052 mtyps.milcs_code MEDIA_TYPE,
1053 msegs.duration DURATION,
1054 msegs.start_date_time START_DATE_TIME,
1055 msegs.end_date_time END_DATE_TIME
1056 from
1057 JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
1058 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps
1059 where
1060 msegs.media_id = p_media_id and
1061 msegs.MILCS_TYPE_ID = mtyps.MILCS_TYPE_ID and
1062 msegs.resource_id is not null
1063 order by
1064 msegs.resource_id,
1065 msegs.start_date_time;
1066
1067 /**Change on 20-Mar-2002:
1068 Outcomes will be retrieved at interaction level not activities.
1069 Also, depending on whether it is a service request or lead etc
1070 change the join conditions. This is because right now, leads and
1071 opportunities are using the mdeia_id on jtf_ih_activities table.
1072 So we can join with this.
1073 However for service requests, the media_id of jtf_ih_activities
1074 is populated only for the "CALL ANSWERED" activity. For other actvities
1075 media_id is null on activites table. The productive_time_amount
1076 column of jtf_ih_interactions is populated with the media_id.
1077
1078 ***/
1079
1080 CURSOR activity_info (p_media_id NUMBER, p_resource_id NUMBER) is
1081 select a.outcome_id OUTCOME_ID,
1082 a.action_id ACTION_ID,
1083 a.action_item_id ACTION_ITEM_ID,
1084 a.doc_ref DOC_REF,
1085 a.doc_id DOC_ID,
1086 a.start_date_time ACT_START_TIME
1087 from
1088 jtf_ih_activities a,
1089 jtf_ih_interactions b
1090 where
1091 (
1092 a.media_id = p_media_id
1093 OR b.productive_time_amount = p_media_id
1094 ) and
1095 b.resource_id = p_resource_id and
1096 a.interaction_id = b.interaction_id
1097 order by b.outcome_id;
1098
1099 /*
1100 *Right now the outcome_id from above will not be used for
1101 *outbounbd calls.
1102 *Outcome_info has the outcome result reason combinations
1103 *for a media_id-resource_id combination.
1104 *Since we are going for the outcome result reason at the interaction
1105 *level we will always insert these fields in the first activity row.
1106 *IF there are one or more WITH_AGENT segments.
1107 *If there are no WITH_AGENT segments then write it at the CALL
1108 *row. this can happen for example for outbound calls which were
1109 *predictive dialed but never reached an agent. We still need to
1110 *track the outcome for these calls.
1111 */
1112
1113 BEGIN
1114
1115 v_classification_value_id := NULL;
1116 v_calls_in_queue := 0;
1117 v_in_calls_handled := 0;
1118 v_calls_transferred := 0;
1119 v_out_calls_handled := 0;
1120 v_in_talk_time := 0;
1121 v_in_wrap_time := 0;
1122 v_out_talk_time := 0;
1123 v_out_wrap_time := 0;
1124 v_campaign_id := NULL;
1125 v_campaign_schedule_id := NULL;
1126 v_campaign_schedule := NULL;
1127 v_total_in_talk_time := 0;
1128 v_total_out_talk_time := 0;
1129 v_ivr_time := 0;
1130 v_route_time := 0;
1131 v_queue_time := 0;
1132 v_abandon_time := 0;
1133 v_resource_id := NULL;
1134 l_current_start_date_time := NULL;
1135 l_current_end_date_time := NULL;
1136 l_call_answered_by_r_id := 0;
1137 l_campaign_code := 0;
1138 l_counter := 0;
1139 l_current_resource_id := 0;
1140 l_max_end_date := NULL;
1141 l_wrap_time := 0;
1142 l_source_list_id := NULL;
1143 l_sublist_id := NULL;
1144 l_leads_amount := 0;
1145 l_opp_amount := 0;
1146 v_leads_amount := 0;
1147 v_opp_amount := 0;
1148 l_leads_amount_txn := 0;
1149 l_opp_amount_txn := 0;
1150 v_leads_amount_txn := 0;
1151 v_opp_amount_txn := 0;
1152 l_leads_created := 0;
1153 l_leads_updated := 0;
1154 l_sr_created := 0;
1155 l_sr_opened := 0;
1156 l_sr_closed := 0;
1157 l_opp_created := 0;
1158 l_opp_updated := 0;
1159 l_opp_won := 0;
1160 v_opp_won := 0;
1161 l_currency_code := NULL;
1162 l_activity_counter := 0;
1163 l_sr_info_req := 0;
1164 l_opp_cross_sold := 0;
1165 l_opp_up_sold := 0;
1166 l_opp_cross_sold := 0;
1167 g_insert_count := 0;
1168 g_delete_count := 0;
1169 l_agent_preview_time := 0;
1170 l_call_preview_time := 0;
1171
1172
1173 -- IGOR : initialize and check for 'no rows returned'
1174
1175 --DELETE_IN_CHUNKS('BIX_DM_INTERFACE',NULL,g_delete_count);
1176
1177 --
1178 --Change delete to truncate to avoid performance issue
1179 --with high water mark.
1180 --
1181
1182 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||g_bix_schema||'.BIX_DM_INTERFACE';
1183
1184 -- DEBUG: dbms_output.put_line('started collecting calls');
1185 --Added filter to include only call medias in following query
1186 --
1187
1188 SELECT MIN(start_date_time),MAX(start_date_time)
1189 INTO g_min_call_begin_date,g_max_call_begin_date
1190 FROM jtf_ih_media_items
1191 WHERE last_update_date BETWEEN g_collect_start_date AND g_collect_end_date
1192 AND
1193 (
1194 media_item_type = 'TELE_INB' or
1195 media_item_type = 'TELE_DIRECT' or
1196 media_item_type = 'TELEPHONE' or
1197 media_item_type = 'CALL' or
1198 media_item_type = 'TELE_MANUAL' or
1199 media_item_type = 'TELE_WEB'
1200 );
1201
1202
1203 /* Round the Min begin date to nearest lower time bucket. ex: if time is between 10:00 and 10:29
1204 round it to 10:00.
1205 */
1206
1207 SELECT TO_DATE(
1208 TO_CHAR(g_min_call_begin_date,'YYYY/MM/DD')||
1209 LPAD(TO_CHAR(g_min_call_begin_date,'HH24:'),3,'0')||
1210 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_min_call_begin_date,'MI'))-29),0,'00:00',1,'30:00',-1,'00:00'),
1211 'YYYY/MM/DDHH24:MI:SS')
1212 INTO g_min_call_begin_date
1213 FROM DUAL;
1214
1215
1216 /* Round the Max begin date to nearest higher time bucket. ex: if time is between 10:00 and 10:29
1217 round it to 10:29:59
1218 */
1219
1220 SELECT TO_DATE(
1221 TO_CHAR(g_max_call_begin_date,'YYYY/MM/DD')||
1222 LPAD(TO_CHAR(g_max_call_begin_date,'HH24:'),3,'0')||
1223 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_max_call_begin_date,'MI'))-29),0,'29:59',1,'59:59',-1,'29:59'),
1224 'YYYY/MM/DDHH24:MI:SS')
1225 INTO g_max_call_begin_date
1226 FROM DUAL;
1227
1228
1229 BEGIN
1230
1231 select resource_id
1232 into g_ao_dummy_resource
1233 from jtf_rs_resource_extns
1234 where user_name = 'IECAOUSER';
1235
1236 EXCEPTION
1237 WHEN OTHERS THEN
1238 g_ao_dummy_resource := NULL;
1239
1240 END;
1241
1242
1243 -- DEBUG: dbms_output.put_line('before the call loop');
1244
1245 FOR call in call_info LOOP
1246
1247 if call.dnis is not null then
1248 l_dnis := get_dnis(call.dnis);
1249 else
1250 l_dnis := NULL;
1251 end if;
1252
1253 -- DEBUG: dbms_output.put_line('in the call loop');
1254
1255 -- get the ivr_time, route_time, queue_time and abandon_time from the call
1256 -- segments with the appropriate media_id
1257
1258 select
1259 SUM(DECODE(UPPER(call.direction),'INBOUND',DECODE(ih_milcs_ty.milcs_code,'IVR',NVL(ih_milcs.duration,0),0),0)) IVR_TIME,
1260 SUM(DECODE(UPPER(call.direction),'INBOUND',DECODE(ih_milcs_ty.milcs_code,'ROUTING',NVL(ih_milcs.duration,0),0),0)) ROUTE_TIME,
1261 SUM(DECODE(UPPER(call.direction),'INBOUND',DECODE(ih_milcs_ty.milcs_code,'IN_QUEUE',NVL(ih_milcs.duration,0),0),0)) QUEUE_TIME,
1262 SUM(DECODE(UPPER(call.direction),'INBOUND',DECODE(UPPER(call.media_abandon_flag),'Y',DECODE(ih_milcs_ty.milcs_code,'IN_QUEUE',NVL(ih_milcs.duration,0),0),0),0)) ABANDON_TIME,
1263 SUM(DECODE(ih_milcs_ty.milcs_code,'WITH_AGENT',1,0)),
1264 SUM(DECODE(ih_milcs_ty.milcs_code,'ROUTING',1,0))
1265 into
1266 v_ivr_time ,
1267 v_route_time ,
1268 v_queue_time ,
1269 v_abandon_time ,
1270 l_with_agent_segs,
1271 l_number_of_rerouts
1272 from
1273 JTF_IH_MEDIA_ITEM_LC_SEGS ih_milcs,
1274 JTF_IH_MEDIA_ITM_LC_SEG_TYS ih_milcs_ty
1275 where
1276 ih_milcs.media_id = call.media_id and
1277 ih_milcs.MILCS_TYPE_ID = ih_milcs_ty.MILCS_TYPE_ID ;
1278
1279 --
1280 --Fix for bug 2611727. Reduce it by 1 so that it will be
1281 --considered as a re-route only if it has more than one ROUTING
1282 --segments
1283 --
1284
1285 IF l_number_of_rerouts > 0
1286 THEN
1287 l_number_of_rerouts := l_number_of_rerouts -1;
1288 END IF;
1289
1290 -- if there are 'WITH_AGENT' segments then:
1291
1292 -- DEBUG: dbms_output.put_line('in outer loop after first query');
1293
1294
1295
1296
1297 if v_queue_time > 0 then
1298 v_calls_in_queue := 1;
1299 else
1300 v_calls_in_queue := 0;
1301 end if;
1302
1303 if call.source_item_id is not NULL AND call.direction = 'OUTBOUND' THEN
1304 GET_LIST_INFO
1305 (call.source_item_id,
1306 v_campaign_id,
1307 v_campaign_schedule_id,
1308 l_source_list_id,
1309 l_sublist_id,
1310 l_dialing_method);
1311 else
1312 l_source_code := get_source_code(call.media_id);
1313 GET_CAMPAIGN_INFO(l_source_code,
1314 v_campaign_id,
1315 v_campaign_schedule_id);
1316 end if;
1317
1318 if call.classification is not NULL then
1319 v_classification_value_id := GET_CLASSIFICATION(
1320 call.classification,
1321 call.call_start_time
1322 );
1323
1324 end if;
1325
1326 GET_GOALS(v_classification_value_id,
1327 l_min_call_treshold_goal,
1328 l_sl_seconds_goal);
1329
1330
1331 -- start with agent stuff
1332 -- get agent info only if there are agent segments:
1333 if l_with_agent_segs > 0 then
1334
1335 select min(resource_id) into l_call_answered_by_r_id
1336 from JTF_IH_MEDIA_ITEM_LC_SEGS
1337 where JTF_IH_MEDIA_ITEM_LC_SEGS.media_id =
1338 call.media_id
1339 and resource_id is not null
1340 and start_date_time =
1341 (select min(msegs.start_date_time) from
1342 JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
1343 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps
1344 where
1345 msegs.media_id = call.media_id and
1346 msegs.MILCS_TYPE_ID = mtyps.MILCS_TYPE_ID and
1347 mtyps.milcs_code = 'WITH_AGENT'
1348 );
1349
1350
1351 l_counter := 1;
1352
1353 FOR agent in agent_info(call.media_id) LOOP
1354 -- DEBUG: dbms_output.put_line('in agent loop');
1355
1356 --
1357 --Assign l_has_agent_segs to Y
1358 --This is used while calculating the outcome/result/reason values
1359 --This will also be used to find out if we need to fill in the
1360 --call type row in the interface table with the measures.
1361 --If there are with agent segments then these will be added up
1362 --to form the values in BIX_DM_CALL_SUM else these will have to
1363 --be populated manually at the call type row.
1364 --ALso, if there are with agent segments then do not fill in the
1365 --measures at the call level as otherwise it will result in
1366 --double counting.
1367 --
1368
1369 l_has_agent_segs := 'Y';
1370
1371
1372 if l_counter = 1 then
1373 -- initialize:
1374 l_current_resource_id := agent.resource_id;
1375 v_resource_id := agent.resource_id;
1376 l_current_end_date_time := agent.end_date_time;
1377 if call.direction = 'INBOUND' then
1378 v_in_talk_time := (agent.end_date_time -
1379 agent.start_date_time) * 24 * 3600;
1380 v_total_in_talk_time := (agent.end_date_time
1381 - agent.start_date_time) * 24 * 3600;
1382 elsif call.direction = 'OUTBOUND' then
1383 v_out_talk_time := (agent.end_date_time -
1384 agent.start_date_time) * 24 * 3600;
1385 v_total_out_talk_time :=
1386 (agent.end_date_time -
1387 agent.start_date_time) * 24 * 3600;
1388 end if;
1389 end if;
1390
1391 if agent.resource_id <> l_current_resource_id then
1392
1393 -- DEBUG: dbms_output.put_line('in agent loop, inserting agent record');
1394 -- if we have completed calculations for one agent (i.e. retrieved
1395 -- agent is different from current agent then first calculate the wrap time and
1396 -- then complete with INSERT and CLEANUP for the previous agent:
1397
1398 --
1399 --Get all the outcome counts
1400 --
1401 IF call.direction = 'OUTBOUND'
1402 THEN
1403 get_outcome_counts(call.media_id, l_current_resource_id);
1404 IF g_interaction_resource IS NULL
1405 THEN
1406 --
1407 --Probably a TELE_MANUAL call with no interaction
1408 --Fix for bug 3062185
1409 --
1410 IF l_current_resource_id = l_call_answered_by_r_id
1411 THEN
1412 g_dial_count := 1;
1413 ELSE
1414 g_dial_count :=0;
1415 END IF;
1416 END IF;
1417 END IF;
1418
1419 --
1420 --Preview time
1421 --
1422 IF l_dialing_method = 'PREV'
1423 THEN
1424 l_agent_preview_time := GET_PREVIEW_TIME(call.media_id, l_current_resource_id);
1425 l_call_preview_time := l_call_preview_time + l_agent_preview_time;
1426 ELSE
1427 l_agent_preview_time :=0;
1428 l_call_preview_time:=0;
1429 END IF;
1430
1431 --
1432 -- Wrap Time:
1433 --
1434
1435 GET_WRAP_TIME ( l_current_resource_id,
1436 call.media_id,
1437 l_current_end_date_time,
1438 call.direction,
1439 v_in_wrap_time,
1440 v_out_wrap_time);
1441
1442
1443
1444 l_activity_counter := 1;
1445 l_outcome_id := NULL;
1446 l_result_id := NULL;
1447 l_reason_id := NULL;
1448
1449 IF (g_debug_flag = 'Y') THEN
1450 write_log('About to enter the activity cursor ', g_proc_name);
1451 write_log('Media id is '||call.media_id || ' l_activitycounter is ' || l_activity_counter || ' l_resource id is ' || l_current_resource_id, g_proc_name);
1452 END IF;
1453
1454 FOR activity in activity_info(call.media_id,l_current_resource_id) LOOP
1455
1456 -- IGOR: continue from here:
1457
1458 /*
1459 * Retrieve the outcome, result and reason here.
1460 *Right now applies only to outbound calls.
1461 */
1462
1463 IF (g_debug_flag = 'Y') THEN
1464 write_log('Inside activity cursor loop ', g_proc_name);
1465 write_log('Media id is '||call.media_id || ' l_activitycounter is ' || l_activity_counter || ' l_resource id is ' || l_current_resource_id, g_proc_name);
1466 END IF;
1467
1468 IF call.direction = 'OUTBOUND' AND l_activity_counter = 1
1469 THEN
1470 BEGIN
1471 SELECT DISTINCT int.outcome_id,
1472 int.result_id,
1473 int.reason_id
1474 INTO l_outcome_id, l_result_id, l_reason_id
1475 from jtf_ih_interactions int,
1476 jtf_ih_activities act
1477 where int.interaction_id = act.interaction_id
1478 and int.resource_id = l_current_resource_id
1479 and act.media_id = call.media_id;
1480
1481 IF (g_debug_flag = 'Y') THEN
1482 write_log('Retrieved outcome id '|| l_outcome_id ||' resultid ' ||l_result_id || ' reason id ' || l_reason_id, g_proc_name);
1483 END IF;
1484
1485 EXCEPTION
1486 WHEN OTHERS
1487 THEN
1488 l_outcome_id := NULL;
1489 l_result_id := NULL;
1490 l_reason_id := NULL;
1491
1492 IF (g_debug_flag = 'Y') THEN
1493 write_log('Exception while getting outcome id '|| l_outcome_id ||' resultid ' ||l_result_id || ' reason id ' || l_reason_id, g_proc_name);
1494 END IF;
1495
1496 END;
1497 ELSE
1498
1499 IF (g_debug_flag = 'Y') THEN
1500 write_log('Else condition while getting outcome id '|| l_outcome_id ||' resultid ' ||l_result_id || ' reason id ' || l_reason_id, g_proc_name);
1501 END IF;
1502
1503 END IF;
1504
1505 /***
1506 Change 20-Mar-2002:
1507 Action id values are:
1508 1 = Add, 6=Update, 7=Upsell, 8=Xsell, 13=SR Created
1509 14= SR Updated, 27=Close opportunity
1510
1511 Action Item Id values are:
1512 8=Lead, 17=SR, 21=Opportunity, 22=Sales Lead
1513 ***/
1514
1515 /****Action id of 1 means added/created ****/
1516 if activity.action_id = 1 then -- item added/created
1517 if activity.action_item_id = 22 -- Sales lead
1518 OR activity.action_item_id = 8 -- Lead
1519 then
1520 l_leads_created := l_leads_created + 1;
1521 if activity.doc_ref = 'LEAD' or
1522 activity.doc_ref = 'ASTSC_LEAD' then
1523 get_lead_amount( activity.doc_id,
1524 l_current_resource_id,
1525 activity.act_start_time,
1526 l_leads_amount,
1527 l_currency_code);
1528 v_leads_amount := v_leads_amount + l_leads_amount;
1529 end if;
1530 elsif activity.action_item_id = 21 then -- Opportunity
1531 l_opp_created := l_opp_created + 1;
1532 if activity.doc_ref = 'OPPORTUNITY' or
1533 activity.doc_ref = 'ASTSC_OPP' then
1534 get_opportunity_amount( activity.doc_id,
1535 l_current_resource_id,
1536 activity.act_start_time,
1537 l_opp_won,
1538 l_opp_amount,
1539 l_currency_code);
1540 v_opp_amount := v_opp_amount + l_opp_amount;
1541 v_opp_won := v_opp_won + l_opp_won;
1542 end if;
1543 elsif activity.action_item_id = 17 then -- Service Request
1544 l_sr_created := l_sr_created + 1;
1545 l_sr_opened := l_sr_opened + 1;
1546 end if;
1547 end if;
1548
1549 /****Action id of 6 means updated ****/
1550 --
1551 --Change for bug 2298527: Amounts will be not be calculated
1552 --if the agent updates the lead or opportunity. Amounts are given
1553 --to the agent who created the lead or opportunity. For cross-sold
1554 --and up-sold etc, the amounts are calculated
1555 --
1556
1557 if activity.action_id = 6 then -- item updated
1558 if activity.action_item_id = 22 -- Sales lead
1559 OR activity.action_item_id = 8 -- Lead
1560 then
1561 l_leads_updated := l_leads_updated + 1;
1562 --if activity.doc_ref = 'LEAD' or
1563 --activity.doc_ref = 'ASTSC_LEAD' then
1564 --get_lead_amount( activity.doc_id,
1565 --l_current_resource_id,
1566 --activity.act_start_time,
1567 --l_leads_amount,
1568 --l_currency_code);
1569 --v_leads_amount := v_leads_amount + l_leads_amount;
1570 --end if;
1571 elsif activity.action_item_id = 21 -- Opportunity
1572 then
1573 l_opp_updated := l_opp_updated + 1;
1574 --if activity.doc_ref = 'OPPORTUNITY' or
1575 --activity.doc_ref = 'ASTSC_OPP' then
1576 --get_opportunity_amount( activity.doc_id,
1577 --l_current_resource_id,
1578 --activity.act_start_time,
1579 --l_opp_won,
1580 --l_opp_amount,
1581 --l_currency_code);
1582 --v_opp_amount := v_opp_amount + l_opp_amount;
1583 --v_opp_won := v_opp_won + l_opp_won;
1584 --end if;
1585 elsif activity.action_item_id = 17 -- Service request
1586 then
1587 get_sr_status (activity.doc_id,l_current_resource_id,activity.act_start_time,l_sr_status);
1588 if l_sr_status = 1 or l_sr_status = 3 then
1589 l_sr_opened := l_sr_opened + 1;
1590 elsif l_sr_status = 2 or l_sr_status = 4 then
1591 l_sr_closed := l_sr_closed + 1;
1592 end if;
1593 end if;
1594 end if;
1595
1596 /**** Service request specific action id value ****/
1597 if activity.action_id = 13 then -- sr created specific code
1598 l_sr_created := l_sr_created + 1;
1599 l_sr_opened := l_sr_opened + 1;
1600 end if;
1601
1602 if activity.action_id = 14 then -- sr updated, specific code
1603 get_sr_status (activity.doc_id,l_current_resource_id,activity.act_start_time,l_sr_status);
1604 if l_sr_status = 1 or l_sr_status = 3 then
1605 l_sr_opened := l_sr_opened + 1;
1606 elsif l_sr_status = 2 or l_sr_status = 4 then
1607 l_sr_closed := l_sr_closed + 1;
1608 end if;
1609 end if;
1610
1611 /*** Other action id values ***/
1612 if activity.action_id = 3 then -- info requested
1613 if activity.action_item_id = 17 then -- service request
1614 l_sr_info_req := l_sr_info_req + 1;
1615 end if;
1616 elsif activity.action_id = 8 then -- cross sold
1617 if activity.action_item_id = 21 then
1618 get_opportunity_amount( activity.doc_id,
1619 l_current_resource_id,
1620 activity.act_start_time,
1621 l_opp_won,
1622 l_opp_amount,
1623 l_currency_code);
1624 v_opp_amount := v_opp_amount + l_opp_amount;
1625 l_opp_cross_sold := l_opp_cross_sold + 1;
1626 end if;
1627 elsif activity.action_id = 7 then -- up sold
1628 if activity.action_item_id = 21 then
1629 get_opportunity_amount( activity.doc_id,
1630 l_current_resource_id,
1631 activity.act_start_time,
1632 l_opp_won,
1633 l_opp_amount,
1634 l_currency_code);
1635 v_opp_amount := v_opp_amount + l_opp_amount;
1636 l_opp_up_sold := l_opp_up_sold + 1;
1637 end if;
1638 elsif activity.action_id = 26 then -- declined
1639 if activity.action_item_id = 21 then
1640 l_opp_declined := l_opp_declined + 1;
1641 end if;
1642 end if;
1643
1644
1645 if l_activity_counter > 1 then
1646 -- insert activity row - continue here
1647 insert into bix_dm_interface
1648 (
1649 MEDIA_ID,
1650 RESOURCE_ID,
1651 --CLASSIFICATION_ID,
1652 CLASSIFICATION_VALUE_ID,
1653 SERVER_GROUP_ID,
1654 DNIS,
1655 --OUTCOME_ID,
1656 CURRENCY_CODE,
1657 PERIOD_START_DATE,
1658 PERIOD_START_TIME,
1659 PERIOD_START_DATE_TIME,
1660 CALLS_OFFERED,
1661 CALLS_IN_QUEUE,
1662 IN_CALLS_HANDLED,
1663 CALLS_TRANSFERED,
1664 CALLS_ABANDONED,
1665 OUT_CALLS_HANDLED,
1666 IVR_TIME,
1667 ROUTE_TIME,
1668 QUEUE_TIME,
1669 IN_TALK_TIME,
1670 IN_WRAP_TIME,
1671 ABANDON_TIME,
1672 OUT_TALK_TIME,
1673 OUT_WRAP_TIME,
1674 CAMPAIGN_ID,
1675 CAMPAIGN_SCHEDULE_ID,
1676 SOURCE_LIST_ID,
1677 SUBLIST_ID,
1678 DIRECTION,
1679 SERVICE_REQUESTS_CREATED,
1680 SERVICE_REQUESTS_OPENED,
1681 SERVICE_REQUESTS_CLOSED,
1682 LEADS_CREATED,
1683 LEADS_UPDATED,
1684 OPPORTUNITIES_CREATED,
1685 OPPORTUNITIES_UPDATED,
1686 OPPORTUNITIES_WON,
1687 LEADS_AMOUNT,
1688 OPPORTUNITIES_WON_AMOUNT,
1689 LEADS_AMOUNT_TXN,
1690 OPPORTUNITIES_WON_AMOUNT_TXN,
1691 OUT_CALLS_HANDLD_GT_THN_X_TIME,
1692 IN_CALLS_HANDLD_GT_THN_X_TIME,
1693 CALLS_ANSWRD_WITHIN_X_TIME,
1694 ROW_TYPE,
1695 QUEUE_TIME_FOR_CALLS_HANDLED,
1696 OUTCOME_ID,
1697 RESULT_ID,
1698 REASON_ID
1699 )
1700 values
1701 (
1702 call.media_id,
1703 l_current_resource_id,
1704 v_classification_value_id,
1705 call.server_group_id,
1706 l_dnis,
1707 --decode(activity.outcome_id,l_prev_outcome_id,NULL,activity.outcome_id),
1708 NULL,
1709 call.PERIOD_START_DATE,
1710 call.PERIOD_START_TIME,
1711 call.PERIOD_START_DATE_TIME,
1712 0,
1713 0,
1714 0,
1715 0,
1716 0,
1717 0,
1718 0,
1719 0,
1720 0,
1721 0,
1722 0,
1723 0,
1724 0,
1725 0,
1726 v_campaign_id,
1727 v_campaign_schedule_id,
1728 l_source_list_id,
1729 l_sublist_id,
1730 decode(call.direction,'OUTBOUND',1,2),
1731 0,
1732 0,
1733 0,
1734 0,
1735 0,
1736 0,
1737 0,
1738 0,
1739 0,
1740 0,
1741 0,
1742 0,
1743 0,
1744 0,
1745 0,
1746 'T',
1747 0,
1748 l_outcome_id,
1749 l_result_id,
1750 l_reason_id
1751 );
1752
1753 --IF activity.outcome_id IS NOT NULL
1754 --THEN
1755 --l_prev_outcome_id := activity.outcome_id;
1756 --END IF;
1757
1758 commit;
1759 g_insert_count := g_insert_count + 1;
1760
1761 elsif l_activity_counter = 1 then
1762 l_first_outcome_id := activity.outcome_id;
1763 end if;
1764
1765 l_activity_counter := l_activity_counter + 1;
1766
1767 END LOOP; -- end activity loop 1
1768
1769 -- IGOR: INSERT... agent row:
1770
1771 insert into bix_dm_interface
1772 (
1773 MEDIA_ID,
1774 RESOURCE_ID,
1775 --CLASSIFICATION_ID,
1776 CLASSIFICATION_VALUE_ID,
1777 SERVER_GROUP_ID,
1778 DNIS,
1779 CURRENCY_CODE,
1780 PERIOD_START_DATE,
1781 PERIOD_START_TIME,
1782 PERIOD_START_DATE_TIME,
1783 CALLS_OFFERED,
1784 CALLS_IN_QUEUE,
1785 IN_CALLS_HANDLED,
1786 CALLS_TRANSFERED,
1787 CALLS_ABANDONED,
1788 OUT_CALLS_HANDLED,
1789 IVR_TIME,
1790 ROUTE_TIME,
1791 QUEUE_TIME,
1792 IN_TALK_TIME,
1793 IN_WRAP_TIME,
1794 ABANDON_TIME,
1795 OUT_TALK_TIME,
1796 OUT_WRAP_TIME,
1797 CAMPAIGN_ID,
1798 CAMPAIGN_SCHEDULE_ID,
1799 --OUTCOME_ID,
1800 SOURCE_LIST_ID,
1801 SUBLIST_ID,
1802 DIRECTION,
1803 SERVICE_REQUESTS_CREATED,
1804 SERVICE_REQUESTS_OPENED,
1805 SERVICE_REQUESTS_CLOSED,
1806 LEADS_CREATED,
1807 LEADS_UPDATED,
1808 OPPORTUNITIES_CREATED,
1809 OPPORTUNITIES_UPDATED,
1810 OPPORTUNITIES_WON,
1811 LEADS_AMOUNT,
1812 OPPORTUNITIES_WON_AMOUNT,
1813 LEADS_AMOUNT_TXN,
1814 OPPORTUNITIES_WON_AMOUNT_TXN,
1815 OUT_CALLS_HANDLD_GT_THN_X_TIME,
1816 IN_CALLS_HANDLD_GT_THN_X_TIME,
1817 CALLS_ANSWRD_WITHIN_X_TIME,
1818 ROW_TYPE,
1819 QUEUE_TIME_FOR_CALLS_HANDLED ,
1820 OUT_CALLS_DIALED,
1821 OUT_CONTACT_COUNT,
1822 OUT_NON_CONTACT_COUNT,
1823 OUT_ABANDON_COUNT,
1824 OUT_BUSY_COUNT ,
1825 OUT_RING_NOANSWER_COUNT,
1826 OUT_ANS_MC_COUNT ,
1827 OUT_SIT_COUNT,
1828 OUT_POSITIVE_RESPONSE_COUNT,
1829 OUT_CONNECT_COUNT ,
1830 OUT_NON_CONNECT_COUNT ,
1831 OUT_OTHER_OUTCOME_COUNT,
1832 OUT_PREVIEW_TIME,
1833 OUT_CONTACT_HANDLE_TIME,
1834 OUTCOME_ID,
1835 RESULT_ID,
1836 REASON_ID
1837 )
1838 values
1839 (
1840 call.media_id,
1841 l_current_resource_id,
1842 --v_classification_id,
1843 v_classification_value_id,
1844 call.server_group_id,
1845 l_dnis,
1846 l_currency_code,
1847 call.PERIOD_START_DATE,
1848 call.PERIOD_START_TIME,
1849 call.PERIOD_START_DATE_TIME,
1850 0,
1851 0,
1852 v_in_calls_handled,
1853 v_calls_transferred,
1854 0,
1855 v_out_calls_handled,
1856 0,
1857 0,
1858 0,
1859 v_in_talk_time,
1860 v_in_wrap_time,
1861 0,
1862 v_out_talk_time,
1863 v_out_wrap_time,
1864 v_campaign_id,
1865 v_campaign_schedule_id,
1866 --l_first_outcome_id,
1867 l_source_list_id,
1868 l_sublist_id,
1869 decode(call.direction,'OUTBOUND',1,'INBOUND',2),
1870 l_sr_created,
1871 l_sr_opened,
1872 l_sr_closed,
1873 l_leads_created,
1874 l_leads_updated,
1875 l_opp_created,
1876 l_opp_updated,
1877 v_opp_won,
1878 decode(l_currency_code,g_preferred_currency,v_leads_amount,null,v_leads_amount,gl_currency_api.convert_amount_sql(l_currency_code,g_preferred_currency,call.PERIOD_START_DATE_TIME,g_conversion_type,v_leads_amount)),
1879 decode(l_currency_code,g_preferred_currency,v_opp_amount,null,v_opp_amount,gl_currency_api.convert_amount_sql(l_currency_code,g_preferred_currency,call.PERIOD_START_DATE_TIME,g_conversion_type,v_opp_amount)),
1880 v_leads_amount,
1881 v_opp_amount,
1882 l_ou_cls_hdld_gt_thn_x_tm,
1883 l_in_cls_hdld_gt_thn_x_tm,
1884 l_calls_answrd_within_x_time,
1885 'A',
1886 l_queue_time_for_calls_handled ,
1887 g_dial_count,
1888 g_contact_count,
1889 g_noncontact_count,
1890 g_abandon_count,
1891 g_busy_count,
1892 g_rna_count,
1893 g_ansmc_count,
1894 g_sit_count,
1895 g_pr_count,
1896 g_connect_count,
1897 g_nonconnect_count,
1898 g_other_count,
1899 nvl(l_agent_preview_time,0),
1900 nvl
1901 (decode(g_contact_count,0,0,NULL,0,
1902 nvl(v_out_talk_time,0) +
1903 nvl(v_out_wrap_time,0) +
1904 nvl(l_agent_preview_time,0)
1905 ),0
1906 ),
1907 l_outcome_id,
1908 l_result_id,
1909 l_reason_id
1910 );
1911
1912 --IF l_first_outcome_id IS NOT NULL
1913 --THEN
1914 --l_prev_outcome_id := l_first_outcome_id;
1915 --END IF;
1916
1917 commit;
1918 g_insert_count := g_insert_count + 1;
1919
1920 -- IGOR: agent CLEANUP ...
1921 v_in_talk_time := 0;
1922 v_out_talk_time := 0;
1923 l_current_resource_id := agent.resource_id;
1924 l_current_end_date_time := agent.start_date_time;
1925
1926 -- IGOR: agent cleanup
1927
1928 l_leads_amount := 0;
1929 l_opp_amount := 0;
1930 l_leads_amount_txn := 0;
1931 l_opp_amount_txn := 0;
1932 v_leads_amount := 0;
1933 v_opp_amount := 0;
1934 v_leads_amount_txn := 0;
1935 v_opp_amount_txn := 0;
1936 l_leads_created := 0;
1937 l_leads_updated := 0;
1938 l_sr_created := 0;
1939 l_sr_opened := 0;
1940 l_sr_closed := 0;
1941 l_opp_created := 0;
1942 l_opp_updated := 0;
1943 l_opp_won := 0;
1944 v_opp_won := 0;
1945 l_activity_counter := 0;
1946 l_currency_code := NULL;
1947 l_first_outcome_id := NULL;
1948 l_sr_info_req := 0;
1949 l_opp_cross_sold := 0;
1950 l_opp_up_sold := 0;
1951 l_opp_cross_sold := 0;
1952 l_ou_cls_hdld_gt_thn_x_tm := 0;
1953 l_in_cls_hdld_gt_thn_x_tm := 0;
1954 l_calls_answrd_within_x_time := 0;
1955 l_queue_time_for_calls_handled := 0;
1956 l_agent_preview_time := 0;
1957
1958 end if;
1959
1960
1961
1962
1963 if agent.start_date_time >= l_current_end_date_time then
1964 -- DEBUG: dbms_output.put_line('in agent loop, no overlapping '||'agent '||to_char(l_current_resource_id)||' in_talk_time: '||to_char(v_in_talk_time)||'total talk time '||to_char(v_total_in_talk_time) );
1965
1966 if call.direction = 'INBOUND' then
1967 v_in_talk_time := v_in_talk_time +
1968 (agent.end_date_time -
1969 agent.start_date_time) * 24 * 3600;
1970 v_total_in_talk_time := v_total_in_talk_time
1971 + (agent.end_date_time
1972 - agent.start_date_time) * 24 * 3600;
1973 elsif call.direction = 'OUTBOUND' then
1974 v_out_talk_time := v_out_talk_time +
1975 (agent.end_date_time -
1976 agent.start_date_time) * 24 * 3600;
1977 v_total_out_talk_time :=
1978 v_total_out_talk_time +
1979 (agent.end_date_time -
1980 agent.start_date_time) * 24 * 3600;
1981 end if;
1982 else
1983 -- DEBUG: dbms_output.put_line('in agent loop, overlapping '||'agent '||to_char(l_current_resource_id)||' in_talk_time: '||to_char(v_in_talk_time)||'total talk time '||to_char(v_total_in_talk_time) );
1984
1985 if call.direction = 'INBOUND' then
1986 v_in_talk_time := v_in_talk_time +
1987 (agent.end_date_time -
1988 l_current_end_date_time) * 24 * 3600;
1989 v_total_in_talk_time := v_total_in_talk_time
1990 + (agent.end_date_time
1991 - l_current_end_date_time) * 24 * 3600;
1992 elsif call.direction = 'OUTBOUND' then
1993 v_out_talk_time := v_out_talk_time +
1994 (agent.end_date_time -
1995 l_current_end_date_time) * 24 * 3600;
1996 v_total_out_talk_time :=
1997 v_total_out_talk_time +
1998 (agent.end_date_time -
1999 l_current_end_date_time) * 24 * 3600;
2000 end if;
2001
2002 end if;
2003
2004 l_current_end_date_time := agent.end_date_time;
2005
2006 if agent.resource_id = l_call_answered_by_r_id
2007 and call.direction = 'INBOUND'
2008 then
2009 v_in_calls_handled := 1;
2010 v_out_calls_handled := 0;
2011 v_calls_transferred := 0;
2012 if v_in_talk_time >= l_min_call_treshold_goal then
2013 l_in_cls_hdld_gt_thn_x_tm := 1;
2014 else
2015 l_in_cls_hdld_gt_thn_x_tm := 0;
2016 end if;
2017 --if (v_ivr_time + v_route_time + v_queue_time) <=
2018 if v_queue_time <=
2019 l_sl_seconds_goal then
2020 -- dbms_output.put_line('answ time:'||to_char(v_ivr_time + v_route_time + v_queue_time)||' goal:'||to_char(l_sl_seconds_goal));
2021 l_calls_answrd_within_x_time := 1;
2022 else
2023 l_calls_answrd_within_x_time := 0;
2024 end if;
2025 l_queue_time_for_calls_handled := v_queue_time;
2026 -- dbms_output.put_line('call handled. ivr_time='||to_char(v_ivr_time)||' route_time='||to_char(v_route_time)||' queue_time='||to_char(v_queue_time)||' target='||to_char(l_sl_seconds_goal));
2027 elsif agent.resource_id = l_call_answered_by_r_id
2028 and call.direction = 'OUTBOUND' then
2029 v_in_calls_handled := 0;
2030 v_out_calls_handled := 1;
2031 v_calls_transferred := 0;
2032 if v_out_talk_time >= l_min_call_treshold_goal then
2033 l_ou_cls_hdld_gt_thn_x_tm := 1;
2034 else
2035 l_ou_cls_hdld_gt_thn_x_tm := 0;
2036 end if;
2037 else
2038 v_in_calls_handled := 0;
2039 v_out_calls_handled := 0;
2040 v_calls_transferred := 1;
2041 end if;
2042
2043
2044
2045
2046
2047 l_counter := l_counter +1;
2048 END LOOP;
2049 -- end agent loop
2050
2051 -- wrap time for last agent row:
2052
2053 GET_WRAP_TIME ( l_current_resource_id,
2054 call.media_id,
2055 l_current_end_date_time,
2056 call.direction,
2057 v_in_wrap_time,
2058 v_out_wrap_time);
2059
2060
2061 --
2062 --Preview time for the last agent row
2063 --
2064 IF l_dialing_method = 'PREV'
2065 THEN
2066 l_agent_preview_time := GET_PREVIEW_TIME(call.media_id, l_current_resource_id);
2067 l_call_preview_time := l_call_preview_time + l_agent_preview_time;
2068 ELSE
2069 l_agent_preview_time := 0;
2070 l_call_preview_time:=0;
2071 END IF;
2072
2073 --
2074 --Get all the outcome counts for the last agent row
2075 --
2076 IF call.direction = 'OUTBOUND'
2077 THEN
2078 get_outcome_counts(call.media_id, l_current_resource_id);
2079 IF g_interaction_resource IS NULL
2080 THEN
2081 --
2082 --Probably a TELE_MANUAL call with no interaction
2083 --Fix for bug 3062185
2084 --
2085 IF l_current_resource_id = l_call_answered_by_r_id
2086 THEN
2087 g_dial_count := 1;
2088 ELSE
2089 g_dial_count :=0;
2090 END IF;
2091 END IF;
2092 END IF;
2093
2094 --
2095 --Assign l_has_agent_segs to Y
2096 --This is used while calculating the outcome/result/reason values
2097 --
2098 l_has_agent_segs := 'Y';
2099
2100 -- IGOR: INSERT...
2101 -- IGOR: CLEANUP ... for call row
2102
2103 -- activity loop for the last agent row:
2104
2105
2106 l_activity_counter := 1;
2107 l_outcome_id := NULL;
2108 l_result_id := NULL;
2109 l_reason_id := NULL;
2110
2111 IF (g_debug_flag = 'Y') THEN
2112 write_log('ABout to enter the activity cursor for last agent', g_proc_name);
2113 write_log('Media id is '||call.media_id || ' l_activitycounter is ' || l_activity_counter || ' l_resource id is ' || l_current_resource_id, g_proc_name);
2114 END IF;
2115
2116 FOR activity in activity_info(call.media_id,l_current_resource_id) LOOP
2117
2118 /*
2119 * Retrieve the outcome, result and reason here.
2120 *Right now applies only to outbound calls.
2121 */
2122 IF (g_debug_flag = 'Y') THEN
2123 write_log('Inside the activity cursor for last agent ', g_proc_name);
2124 write_log('Media id is '||call.media_id || ' l_activitycounter is ' || l_activity_counter || ' l_resource id is ' || l_current_resource_id, g_proc_name);
2125 END IF;
2126
2127 IF call.direction = 'OUTBOUND' AND l_activity_counter = 1
2128 THEN
2129 BEGIN
2130 SELECT DISTINCT int.outcome_id,
2131 int.result_id,
2132 int.reason_id
2133 INTO l_outcome_id, l_result_id, l_reason_id
2134 from jtf_ih_interactions int,
2135 jtf_ih_activities act
2136 where int.interaction_id = act.interaction_id
2137 and int.resource_id = l_current_resource_id
2138 and act.media_id = call.media_id;
2139 IF (g_debug_flag = 'Y') THEN
2140 write_log('Retrieved outcome id '|| l_outcome_id ||' resultid ' ||l_result_id || ' reason id ' || l_reason_id, g_proc_name);
2141 END IF;
2142 EXCEPTION
2143 WHEN OTHERS
2144 THEN
2145 IF (g_debug_flag = 'Y') THEN
2146 write_log('Exception while getting outcome id '|| l_outcome_id ||' resultid ' ||l_result_id || ' reason id ' || l_reason_id, g_proc_name);
2147 END IF;
2148 l_outcome_id := NULL;
2149 l_result_id := NULL;
2150 l_reason_id := NULL;
2151 END;
2152 ELSE
2153
2154 IF (g_debug_flag = 'Y') THEN
2155 write_log('Else section while getting outcome id '|| l_outcome_id ||' resultid ' ||l_result_id || ' reason id ' || l_reason_id, g_proc_name);
2156 END IF;
2157
2158 END IF;
2159
2160
2161 if activity.action_id = 1 then -- item created
2162 if activity.action_item_id = 22
2163 OR activity.action_item_id = 8 THEN
2164 l_leads_created := l_leads_created + 1;
2165 if activity.doc_ref = 'LEAD' or
2166 activity.doc_ref = 'ASTSC_LEAD' then
2167 get_lead_amount( activity.doc_id,
2168 l_current_resource_id,
2169 activity.act_start_time,
2170 l_leads_amount,
2171 l_currency_code);
2172 v_leads_amount := v_leads_amount + l_leads_amount;
2173 end if;
2174 elsif activity.action_item_id = 21 then
2175 l_opp_created := l_opp_created + 1;
2176 if activity.doc_ref = 'OPPORTUNITY' or
2177 activity.doc_ref = 'ASTSC_OPP' then
2178 get_opportunity_amount( activity.doc_id,
2179 l_current_resource_id,
2180 activity.act_start_time,
2181 l_opp_won,
2182 l_opp_amount,
2183 l_currency_code);
2184 v_opp_amount := v_opp_amount + l_opp_amount;
2185 v_opp_won := v_opp_won + l_opp_won;
2186 end if;
2187 elsif activity.action_item_id = 17 then
2188 l_sr_created := l_sr_created + 1;
2189 l_sr_opened := l_sr_opened + 1;
2190 end if;
2191 end if;
2192
2193 if activity.action_id = 13 then -- sr created specific code
2194 l_sr_created := l_sr_created + 1;
2195 l_sr_opened := l_sr_opened + 1;
2196 end if;
2197
2198
2199
2200
2201 if activity.action_id = 6 then -- item updated
2202 if activity.action_item_id = 22
2203 OR activity.action_item_id = 8 THEN
2204 l_leads_updated := l_leads_updated + 1;
2205 elsif activity.action_item_id = 21 then
2206 l_opp_updated := l_opp_updated + 1;
2207 if activity.doc_ref = 'OPPORTUNITY' or
2208 activity.doc_ref = 'ASTSC_OPP' then
2209 get_opportunity_amount( activity.doc_id,
2210 l_current_resource_id,
2211 activity.act_start_time,
2212 l_opp_won,
2213 l_opp_amount,
2214 l_currency_code);
2215 v_opp_amount := v_opp_amount + l_opp_amount;
2216 v_opp_won := v_opp_won + l_opp_won;
2217 end if;
2218 elsif activity.action_item_id = 17 then
2219 get_sr_status (activity.doc_id,l_current_resource_id,activity.act_start_time,l_sr_status);
2220 if l_sr_status = 1 or l_sr_status = 3 then
2221 l_sr_opened := l_sr_opened + 1;
2222 elsif l_sr_status = 2 or l_sr_status = 4 then
2223 l_sr_closed := l_sr_closed + 1;
2224 end if;
2225 end if;
2226 end if;
2227
2228 if activity.action_id = 14 then -- sr updated, specific code
2229 get_sr_status (activity.doc_id,l_current_resource_id,activity.act_start_time,l_sr_status);
2230 if l_sr_status = 1 or l_sr_status = 3 then
2231 l_sr_opened := l_sr_opened + 1;
2232 elsif l_sr_status = 2 or l_sr_status = 4 then
2233 l_sr_closed := l_sr_closed + 1;
2234 end if;
2235 end if;
2236
2237 if activity.action_id = 3 then -- info requested
2238 if activity.action_item_id = 17 then -- service request
2239 l_sr_info_req := l_sr_info_req + 1;
2240 end if;
2241 elsif activity.action_id = 8 then -- cross sold
2242 if activity.action_item_id = 21 then
2243 l_opp_cross_sold := l_opp_cross_sold + 1;
2244 end if;
2245 elsif activity.action_id = 7 then -- up sold
2246 if activity.action_item_id = 21 then
2247 l_opp_up_sold := l_opp_up_sold + 1;
2248 end if;
2249 elsif activity.action_id = 26 then -- declined
2250 if activity.action_item_id = 21 then
2251 l_opp_declined := l_opp_declined + 1;
2252 end if;
2253 end if;
2254
2255
2256
2257 if l_activity_counter > 1 then
2258 -- insert last activity row - continue here
2259 insert into bix_dm_interface
2260 (
2261 MEDIA_ID,
2262 RESOURCE_ID,
2263 --CLASSIFICATION_ID,
2264 CLASSIFICATION_VALUE_ID,
2265 SERVER_GROUP_ID,
2266 DNIS,
2267 --OUTCOME_ID,
2268 CURRENCY_CODE,
2269 PERIOD_START_DATE,
2270 PERIOD_START_TIME,
2271 PERIOD_START_DATE_TIME,
2272 CALLS_OFFERED,
2273 CALLS_IN_QUEUE,
2274 IN_CALLS_HANDLED,
2275 CALLS_TRANSFERED,
2276 CALLS_ABANDONED,
2277 OUT_CALLS_HANDLED,
2278 IVR_TIME,
2279 ROUTE_TIME,
2280 QUEUE_TIME,
2281 IN_TALK_TIME,
2282 IN_WRAP_TIME,
2283 ABANDON_TIME,
2284 OUT_TALK_TIME,
2285 OUT_WRAP_TIME,
2286 CAMPAIGN_ID,
2287 CAMPAIGN_SCHEDULE_ID,
2288 SOURCE_LIST_ID,
2289 SUBLIST_ID,
2290 DIRECTION,
2291 SERVICE_REQUESTS_CREATED,
2292 SERVICE_REQUESTS_OPENED,
2293 SERVICE_REQUESTS_CLOSED,
2294 LEADS_CREATED,
2295 LEADS_UPDATED,
2296 OPPORTUNITIES_CREATED,
2297 OPPORTUNITIES_UPDATED,
2298 OPPORTUNITIES_WON,
2299 LEADS_AMOUNT,
2300 OPPORTUNITIES_WON_AMOUNT,
2301 LEADS_AMOUNT_TXN,
2302 OPPORTUNITIES_WON_AMOUNT_TXN,
2303 OUT_CALLS_HANDLD_GT_THN_X_TIME,
2304 IN_CALLS_HANDLD_GT_THN_X_TIME,
2305 CALLS_ANSWRD_WITHIN_X_TIME,
2306 ROW_TYPE,
2307 QUEUE_TIME_FOR_CALLS_HANDLED ,
2308 OUTCOME_ID,
2309 RESULT_ID,
2310 REASON_ID
2311 )
2312 values
2313 (
2314 call.media_id,
2315 l_current_resource_id,
2316 --v_classification_id,
2317 v_classification_value_id,
2318 call.server_group_id,
2319 l_dnis,
2320 --decode(activity.outcome_id,l_prev_outcome_id,NULL,activity.outcome_id),
2321 NULL,
2322 call.PERIOD_START_DATE,
2323 call.PERIOD_START_TIME,
2324 call.PERIOD_START_DATE_TIME,
2325 0,
2326 0,
2327 0,
2328 0,
2329 0,
2330 0,
2331 0,
2332 0,
2333 0,
2334 0,
2335 0,
2336 0,
2337 0,
2338 0,
2339 v_campaign_id,
2340 v_campaign_schedule_id,
2341 l_source_list_id,
2342 l_sublist_id,
2343 decode(call.direction,'OUTBOUND',1,2),
2344 0,
2345 0,
2346 0,
2347 0,
2348 0,
2349 0,
2350 0,
2351 0,
2352 0,
2353 0,
2354 0,
2355 0,
2356 0,
2357 0,
2358 0,
2359 'T',
2360 0,
2361 l_outcome_id,
2362 l_result_id,
2363 l_reason_id
2364 );
2365
2366 --IF activity.outcome_id IS NOT NULL
2367 --THEN
2368 --l_prev_outcome_id := activity.outcome_id;
2369 --END IF;
2370
2371 commit;
2372 g_insert_count := g_insert_count + 1;
2373
2374 elsif l_activity_counter = 1 then
2375 l_first_outcome_id := activity.outcome_id;
2376 end if;
2377
2378 l_activity_counter := l_activity_counter + 1;
2379
2380 END LOOP; -- end activity loop 2
2381
2382
2383
2384
2385
2386 -- DEBUG: dbms_output.put_line('inserting agent record');
2387 -- inserting last agent record:
2388
2389 -- IGOR: INSERT...
2390 insert into bix_dm_interface
2391 (
2392 MEDIA_ID,
2393 RESOURCE_ID,
2394 --CLASSIFICATION_ID,
2395 CLASSIFICATION_VALUE_ID,
2396 SERVER_GROUP_ID,
2397 DNIS,
2398 CURRENCY_CODE,
2399 PERIOD_START_DATE,
2400 PERIOD_START_TIME,
2401 PERIOD_START_DATE_TIME,
2402 CALLS_OFFERED,
2403 CALLS_IN_QUEUE,
2404 IN_CALLS_HANDLED,
2405 CALLS_TRANSFERED,
2406 CALLS_ABANDONED,
2407 OUT_CALLS_HANDLED,
2408 IVR_TIME,
2409 ROUTE_TIME,
2410 QUEUE_TIME,
2411 IN_TALK_TIME,
2412 IN_WRAP_TIME,
2413 ABANDON_TIME,
2414 OUT_TALK_TIME,
2415 OUT_WRAP_TIME,
2416 CAMPAIGN_ID,
2417 CAMPAIGN_SCHEDULE_ID,
2418 --OUTCOME_ID,
2419 SOURCE_LIST_ID,
2420 SUBLIST_ID,
2421 DIRECTION,
2422 SERVICE_REQUESTS_CREATED,
2423 SERVICE_REQUESTS_OPENED,
2424 SERVICE_REQUESTS_CLOSED,
2425 SERVICE_REQUESTS_INFO_REQ,
2426 LEADS_CREATED,
2427 LEADS_UPDATED,
2428 OPPORTUNITIES_CREATED,
2429 OPPORTUNITIES_UPDATED,
2430 OPPORTUNITIES_WON,
2431 LEADS_AMOUNT,
2432 OPPORTUNITIES_WON_AMOUNT,
2433 OPPORTUNITIES_CROSS_SOLD,
2434 OPPORTUNITIES_UP_SOLD,
2435 OPPORTUNITIES_DECLINED,
2436 LEADS_AMOUNT_TXN,
2437 OPPORTUNITIES_WON_AMOUNT_TXN,
2438 OUT_CALLS_HANDLD_GT_THN_X_TIME,
2439 IN_CALLS_HANDLD_GT_THN_X_TIME,
2440 CALLS_ANSWRD_WITHIN_X_TIME,
2441 ROW_TYPE,
2442 QUEUE_TIME_FOR_CALLS_HANDLED ,
2443 OUT_CALLS_DIALED,
2444 OUT_CONTACT_COUNT,
2445 OUT_NON_CONTACT_COUNT,
2446 OUT_ABANDON_COUNT,
2447 OUT_BUSY_COUNT ,
2448 OUT_RING_NOANSWER_COUNT,
2449 OUT_ANS_MC_COUNT ,
2450 OUT_SIT_COUNT,
2451 OUT_POSITIVE_RESPONSE_COUNT,
2452 OUT_CONNECT_COUNT ,
2453 OUT_NON_CONNECT_COUNT ,
2454 OUT_OTHER_OUTCOME_COUNT,
2455 OUT_PREVIEW_TIME,
2456 OUT_CONTACT_HANDLE_TIME,
2457 OUTCOME_ID,
2458 RESULT_ID,
2459 REASON_ID
2460 )
2461 values
2462 (
2463 call.media_id,
2464 l_current_resource_id,
2465 --v_classification_id,
2466 v_classification_value_id,
2467 call.server_group_id,
2468 l_dnis,
2469 l_currency_code,
2470 call.PERIOD_START_DATE,
2471 call.PERIOD_START_TIME,
2472 call.PERIOD_START_DATE_TIME,
2473 0,
2474 0,
2475 v_in_calls_handled,
2476 v_calls_transferred,
2477 0,
2478 v_out_calls_handled,
2479 0,
2480 0,
2481 0,
2482 v_in_talk_time,
2483 v_in_wrap_time,
2484 0,
2485 v_out_talk_time,
2486 v_out_wrap_time,
2487 v_campaign_id,
2488 v_campaign_schedule_id,
2489 --l_first_outcome_id,
2490 l_source_list_id,
2491 l_sublist_id,
2492 decode(call.direction,'OUTBOUND',1,'INBOUND',2),
2493 l_sr_created,
2494 l_sr_opened,
2495 l_sr_closed,
2496 l_sr_info_req,
2497 l_leads_created,
2498 l_leads_updated,
2499 l_opp_created,
2500 l_opp_updated,
2501 v_opp_won,
2502 decode(l_currency_code,g_preferred_currency,v_leads_amount,null,v_leads_amount,gl_currency_api.convert_amount_sql(l_currency_code,g_preferred_currency,call.PERIOD_START_DATE_TIME,g_conversion_type,v_leads_amount)),
2503 decode(l_currency_code,g_preferred_currency,v_opp_amount,null,v_opp_amount,gl_currency_api.convert_amount_sql(l_currency_code,g_preferred_currency,call.PERIOD_START_DATE_TIME,g_conversion_type,v_opp_amount)),
2504 l_opp_cross_sold,
2505 l_opp_up_sold,
2506 l_opp_declined,
2507 v_leads_amount,
2508 v_opp_amount,
2509 l_ou_cls_hdld_gt_thn_x_tm,
2510 l_in_cls_hdld_gt_thn_x_tm,
2511 l_calls_answrd_within_x_time,
2512 'A',
2513 l_queue_time_for_calls_handled ,
2514 g_dial_count,
2515 g_contact_count,
2516 g_noncontact_count,
2517 g_abandon_count,
2518 g_busy_count,
2519 g_rna_count,
2520 g_ansmc_count,
2521 g_sit_count,
2522 g_pr_count,
2523 g_connect_count,
2524 g_nonconnect_count,
2525 g_other_count,
2526 nvl(l_agent_preview_time,0),
2527 nvl
2528 (decode(g_contact_count,0,0,NULL,0,
2529 nvl(v_out_talk_time,0) +
2530 nvl(v_out_wrap_time,0) +
2531 nvl(l_agent_preview_time,0)
2532 ),0
2533 ),
2534 l_outcome_id,
2535 l_result_id,
2536 l_reason_id
2537 );
2538
2539 --IF l_first_outcome_id IS NOT NULL
2540 --THEN
2541 --l_prev_outcome_id := l_first_outcome_id;
2542 --END IF;
2543
2544 commit;
2545 g_insert_count := g_insert_count + 1;
2546
2547 end if;
2548 -- end with agent stuff
2549 -- IGOR - end if for 'with agent' segments
2550 -- insert call row:
2551 -- DEBUG: dbms_output.put_line('inserting call record');
2552
2553 --
2554 --Get all the outcome counts at call level
2555 --
2556
2557 IF l_has_agent_segs = 'N' and call.direction = 'OUTBOUND'
2558 THEN
2559 get_outcome_counts(call.media_id, NULL);
2560 /*
2561 *This means we have not inserted the outcome/result/reason
2562 *as there are no with agent segments.
2563 *This will happen only for predictive calls
2564 *which did not connect to an agent. Assume
2565 *only one interaction row (rownum = 1) - confirmed
2566 *from AO - OLTP team.
2567 * Retrieve the outcome, result and reason here.
2568 *Right now applies only to outbound calls.
2569 */
2570
2571 BEGIN
2572 SELECT DISTINCT int.outcome_id,
2573 int.result_id,
2574 int.reason_id
2575 INTO l_outcome_id, l_result_id, l_reason_id
2576 from jtf_ih_interactions int,
2577 jtf_ih_activities act
2578 where int.interaction_id = act.interaction_id
2579 and act.media_id = call.media_id
2580 and rownum = 1;
2581 EXCEPTION
2582 WHEN OTHERS
2583 THEN
2584 l_outcome_id := NULL;
2585 l_result_id := NULL;
2586 l_reason_id := NULL;
2587 END;
2588
2589 --
2590 --Change on 10-JUL-2003:
2591 --Add check to see if interaction level resource is valid.
2592 --If so, then insert the agent level row here.
2593 --Then, set the AO counts to NULL so that they
2594 --do not get repeated at call level.
2595 --If no interaction resource, do the other steps as usual.
2596 --
2597
2598 IF g_interaction_resource IS NOT NULL
2599 THEN
2600 insert into bix_dm_interface
2601 (
2602 MEDIA_ID,
2603 RESOURCE_ID,
2604 --CLASSIFICATION_ID,
2605 CLASSIFICATION_VALUE_ID,
2606 SERVER_GROUP_ID,
2607 DNIS,
2608 CURRENCY_CODE,
2609 PERIOD_START_DATE,
2610 PERIOD_START_TIME,
2611 PERIOD_START_DATE_TIME,
2612 CALLS_OFFERED,
2613 CALLS_IN_QUEUE,
2614 IN_CALLS_HANDLED,
2615 CALLS_TRANSFERED,
2616 CALLS_ABANDONED,
2617 OUT_CALLS_HANDLED,
2618 IVR_TIME,
2619 ROUTE_TIME,
2620 QUEUE_TIME,
2621 IN_TALK_TIME,
2622 IN_WRAP_TIME,
2623 ABANDON_TIME,
2624 OUT_TALK_TIME,
2625 OUT_WRAP_TIME,
2626 CAMPAIGN_ID,
2627 CAMPAIGN_SCHEDULE_ID,
2628 --OUTCOME_ID,
2629 SOURCE_LIST_ID,
2630 SUBLIST_ID,
2631 DIRECTION,
2632 SERVICE_REQUESTS_CREATED,
2633 SERVICE_REQUESTS_OPENED,
2634 SERVICE_REQUESTS_CLOSED,
2635 LEADS_CREATED,
2636 LEADS_UPDATED,
2637 OPPORTUNITIES_CREATED,
2638 OPPORTUNITIES_UPDATED,
2639 OPPORTUNITIES_WON,
2640 LEADS_AMOUNT,
2641 OPPORTUNITIES_WON_AMOUNT,
2642 LEADS_AMOUNT_TXN,
2643 OPPORTUNITIES_WON_AMOUNT_TXN,
2644 OUT_CALLS_HANDLD_GT_THN_X_TIME,
2645 IN_CALLS_HANDLD_GT_THN_X_TIME,
2646 CALLS_ANSWRD_WITHIN_X_TIME,
2647 ROW_TYPE,
2648 QUEUE_TIME_FOR_CALLS_HANDLED ,
2649 OUT_CALLS_DIALED,
2650 OUT_CONTACT_COUNT,
2651 OUT_NON_CONTACT_COUNT,
2652 OUT_ABANDON_COUNT,
2653 OUT_BUSY_COUNT ,
2654 OUT_RING_NOANSWER_COUNT,
2655 OUT_ANS_MC_COUNT ,
2656 OUT_SIT_COUNT,
2657 OUT_POSITIVE_RESPONSE_COUNT,
2658 OUT_CONNECT_COUNT ,
2659 OUT_NON_CONNECT_COUNT ,
2660 OUT_OTHER_OUTCOME_COUNT,
2661 OUT_PREVIEW_TIME,
2662 OUT_CONTACT_HANDLE_TIME,
2663 OUTCOME_ID,
2664 RESULT_ID,
2665 REASON_ID
2666 )
2667 values
2668 (
2669 call.media_id,
2670 g_interaction_resource, --l_current_resource_id,
2671 --v_classification_id,
2672 v_classification_value_id,
2673 call.server_group_id,
2674 l_dnis,
2675 l_currency_code,
2676 call.PERIOD_START_DATE,
2677 call.PERIOD_START_TIME,
2678 call.PERIOD_START_DATE_TIME,
2679 0,
2680 0,
2681 0, --v_in_calls_handled,
2682 0, --v_calls_transferred,
2683 0,
2684 1, --v_out_calls_handled, make this 1 to indicate agent handled one call
2685 0,
2686 0,
2687 0,
2688 0, --v_in_talk_time,
2689 0, --v_in_wrap_time,
2690 0,
2691 0, --v_out_talk_time,
2692 0, --v_out_wrap_time,
2693 v_campaign_id,
2694 v_campaign_schedule_id,
2695 --l_first_outcome_id,
2696 l_source_list_id,
2697 l_sublist_id,
2698 decode(call.direction,'OUTBOUND',1,'INBOUND',2),
2699 0,--l_sr_created,
2700 0,--l_sr_opened,
2701 0,--l_sr_closed,
2702 0,--l_leads_created,
2703 0,--l_leads_updated,
2704 0,--l_opp_created,
2705 0,--l_opp_updated,
2706 0,--v_opp_won,
2707 0,
2708 0,
2709 0, --v_leads_amount,
2710 0, --v_opp_amount,
2711 0, --l_ou_cls_hdld_gt_thn_x_tm,
2712 0, --l_in_cls_hdld_gt_thn_x_tm,
2713 0, --l_calls_answrd_within_x_time,
2714 'A',
2715 0, --l_queue_time_for_calls_handled ,
2716 g_dial_count,
2717 g_contact_count,
2718 g_noncontact_count,
2719 g_abandon_count,
2720 g_busy_count,
2721 g_rna_count,
2722 g_ansmc_count,
2723 g_sit_count,
2724 g_pr_count,
2725 g_connect_count,
2726 g_nonconnect_count,
2727 g_other_count,
2728 0, --nvl(l_agent_preview_time,0),
2729 0, --nvl(decode(g_contact_count,0,0,NULL,0,v_out_talk_time+v_out_wrap_time),0),
2730 l_outcome_id,
2731 l_result_id,
2732 l_reason_id
2733 );
2734
2735 g_dial_count :=0;
2736 g_contact_count :=0;
2737 g_noncontact_count :=0;
2738 g_abandon_count :=0;
2739 g_busy_count :=0;
2740 g_rna_count :=0;
2741 g_ansmc_count :=0;
2742 g_sit_count :=0;
2743 g_pr_count :=0;
2744 g_connect_count :=0;
2745 g_nonconnect_count :=0;
2746 g_other_count :=0;
2747 l_outcome_id := NULL;
2748 l_result_id := NULL;
2749 l_reason_id := NULL;
2750
2751 g_interaction_resource := NULL;
2752
2753 END IF;
2754
2755 ELSE
2756 l_outcome_id := NULL;
2757 l_result_id := NULL;
2758 l_reason_id := NULL;
2759 g_dial_count :=0;
2760 g_contact_count :=0;
2761 g_noncontact_count :=0;
2762 g_abandon_count :=0;
2763 g_busy_count:=0;
2764 g_rna_count :=0;
2765 g_ansmc_count :=0;
2766 g_sit_count :=0;
2767 g_pr_count :=0;
2768 g_connect_count :=0;
2769 g_nonconnect_count :=0;
2770 g_other_count :=0;
2771 END IF;
2772
2773 insert into bix_dm_interface
2774 (
2775 MEDIA_ID,
2776 RESOURCE_ID,
2777 --CLASSIFICATION_ID,
2778 CLASSIFICATION_VALUE_ID,
2779 SERVER_GROUP_ID,
2780 DNIS,
2781 CURRENCY_CODE,
2782 PERIOD_START_DATE,
2783 PERIOD_START_TIME,
2784 PERIOD_START_DATE_TIME,
2785 CALLS_OFFERED,
2786 CALLS_IN_QUEUE,
2787 IN_CALLS_HANDLED,
2788 CALLS_TRANSFERED,
2789 CALLS_ABANDONED,
2790 OUT_CALLS_HANDLED,
2791 IVR_TIME,
2792 ROUTE_TIME,
2793 QUEUE_TIME,
2794 IN_TALK_TIME,
2795 IN_WRAP_TIME,
2796 ABANDON_TIME,
2797 OUT_TALK_TIME,
2798 OUT_WRAP_TIME,
2799 CAMPAIGN_ID,
2800 CAMPAIGN_SCHEDULE_ID,
2801 SOURCE_LIST_ID,
2802 SUBLIST_ID,
2803 DIRECTION,
2804 ROW_TYPE,
2805 OUT_CALLS_HANDLD_GT_THN_X_TIME,
2806 IN_CALLS_HANDLD_GT_THN_X_TIME,
2807 CALLS_ANSWRD_WITHIN_X_TIME,
2808 QUEUE_TIME_FOR_CALLS_HANDLED ,
2809 NUMBER_OF_REROUTS,
2810 OUTCOME_ID,
2811 RESULT_ID,
2812 REASON_ID,
2813 OUT_CALLS_DIALED,
2814 OUT_CONTACT_COUNT,
2815 OUT_NON_CONTACT_COUNT,
2816 OUT_ABANDON_COUNT,
2817 OUT_BUSY_COUNT ,
2818 OUT_RING_NOANSWER_COUNT,
2819 OUT_ANS_MC_COUNT ,
2820 OUT_SIT_COUNT,
2821 OUT_POSITIVE_RESPONSE_COUNT,
2822 OUT_CONNECT_COUNT ,
2823 OUT_NON_CONNECT_COUNT ,
2824 OUT_OTHER_OUTCOME_COUNT,
2825 OUT_PREVIEW_TIME
2826 )
2827 values
2828 (
2829 call.media_id,
2830 NULL,
2831 --v_classification_id,
2832 v_classification_value_id,
2833 call.server_group_id,
2834 l_dnis,
2835 l_currency_code,
2836 call.PERIOD_START_DATE,
2837 call.PERIOD_START_TIME,
2838 call.PERIOD_START_DATE_TIME,
2839 call.CALLS_OFFERED,
2840 v_calls_in_queue,
2841 0,
2842 0,
2843 call.CALLS_ABANDONED,
2844 0,
2845 v_ivr_time ,
2846 v_route_time ,
2847 v_queue_time ,
2848 0,
2849 0,
2850 v_abandon_time,
2851 0,
2852 0,
2853 v_campaign_id,
2854 v_campaign_schedule_id,
2855 l_source_list_id,
2856 l_sublist_id,
2857 decode(call.direction,'OUTBOUND',1,'INBOUND',2),
2858 'C',
2859 0,
2860 0,
2861 0,
2862 0,
2863 l_number_of_rerouts,
2864 l_outcome_id,
2865 l_result_id,
2866 l_reason_id,
2867 g_dial_count,
2868 g_contact_count,
2869 g_noncontact_count,
2870 g_abandon_count,
2871 g_busy_count,
2872 g_rna_count,
2873 g_ansmc_count,
2874 g_sit_count,
2875 g_pr_count,
2876 g_connect_count,
2877 g_nonconnect_count,
2878 g_other_count,
2879 nvl(l_call_preview_time,0)
2880 );
2881
2882 commit;
2883 g_insert_count := g_insert_count + 1;
2884
2885 -- IGOR: CLEANUP ...
2886
2887 v_classification_value_id := NULL;
2888 v_calls_in_queue := 0;
2889 v_in_calls_handled := 0;
2890 v_calls_transferred := 0;
2891 v_out_calls_handled := 0;
2892 v_in_talk_time := 0;
2893 v_in_wrap_time := 0;
2894 v_out_talk_time := 0;
2895 v_out_wrap_time := 0;
2896 v_campaign_id := NULL;
2897 v_campaign_schedule_id := NULL;
2898 v_campaign_schedule := NULL;
2899 v_total_in_talk_time := 0;
2900 v_total_out_talk_time := 0;
2901 v_ivr_time := 0;
2902 v_route_time := 0;
2903 v_queue_time := 0;
2904 v_abandon_time := 0;
2905 v_resource_id := NULL;
2906 l_current_start_date_time := NULL;
2907 l_current_end_date_time := NULL;
2908 l_call_answered_by_r_id := 0;
2909 l_campaign_code := 0;
2910 l_counter := 0;
2911 l_current_resource_id := 0;
2912 l_max_end_date := NULL;
2913 l_wrap_time := 0;
2914 l_source_list_id := NULL;
2915 l_sublist_id := NULL;
2916
2917 l_leads_amount := 0;
2918 l_opp_amount := 0;
2919 l_leads_amount_txn := 0;
2920 l_opp_amount_txn := 0;
2921 v_leads_amount := 0;
2922 v_opp_amount := 0;
2923 v_leads_amount_txn := 0;
2924 v_opp_amount_txn := 0;
2925 l_leads_created := 0;
2926 l_leads_updated := 0;
2927 l_sr_created := 0;
2928 l_sr_opened := 0;
2929 l_sr_closed := 0;
2930 l_opp_created := 0;
2931 l_opp_updated := 0;
2932 l_opp_won := 0;
2933 v_opp_won := 0;
2934 l_activity_counter := 0;
2935 l_currency_code := NULL;
2936 l_dnis := NULL;
2937 l_first_outcome_id := NULL;
2938 l_prev_outcome_id := NULL;
2939 l_sr_info_req := 0;
2940 l_opp_cross_sold := 0;
2941 l_opp_up_sold := 0;
2942 l_opp_cross_sold := 0;
2943 l_ou_cls_hdld_gt_thn_x_tm := 0;
2944 l_in_cls_hdld_gt_thn_x_tm := 0;
2945 l_calls_answrd_within_x_time := 0;
2946 l_min_call_treshold_goal := 0;
2947 l_sl_seconds_goal := 30;
2948 l_queue_time_for_calls_handled := 0;
2949 l_number_of_rerouts := 0;
2950
2951 l_has_agent_segs := 'N';
2952
2953 l_agent_preview_time := 0;
2954 l_call_preview_time := 0;
2955
2956 END LOOP;
2957
2958
2959 EXCEPTION
2960 WHEN OTHERS THEN
2961 RAISE;
2962
2963 END GET_CALLS;
2964
2965
2966 -- Summarizing the interface table by agent and inserting rows into
2967 -- the BIX_DM_AGENT_SUM table
2968
2969 PROCEDURE SUM_AGENT AS
2970
2971 CURSOR agent_data IS
2972 SELECT call_stage.SERVER_GROUP_ID SERVER_GROUP_ID,
2973 call_stage.CLASSIFICATION_VALUE_ID CLASSIFICATION_VALUE_ID,
2974 call_stage.CAMPAIGN_ID CAMPAIGN_ID,
2975 call_stage.CAMPAIGN_SCHEDULE_ID CAMPAIGN_SCHEDULE_ID,
2976 call_stage.resource_id RESOURCE_ID,
2977 call_stage.PERIOD_START_DATE PERIOD_START_DATE,
2978 call_stage.PERIOD_START_TIME PERIOD_START_TIME,
2979 call_stage.PERIOD_START_DATE_TIME PERIOD_START_DATE_TIME,
2980 NVL(SUM(call_stage.IN_CALLS_HANDLED),0) IN_CALLS_HANDLED,
2981 NVL(SUM(call_stage.IN_CALLS_HANDLD_GT_THN_X_TIME),0) IN_CALLS_HANDLD_GT_THN_X_TIME,
2982 NVL(SUM(call_stage.CALLS_TRANSFERED),0) CALLS_TRANSFERED,
2983 NVL(SUM(call_stage.OUT_CALLS_HANDLED),0) OUT_CALLS_HANDLED,
2984 NVL(SUM(call_stage.OUT_CALLS_HANDLD_GT_THN_X_TIME),0) OUT_CALLS_HANDLD_GT_THN_X_TIME,
2985 NVL(SUM(NVL(call_stage.IN_TALK_TIME,0)),0) IN_TALK_TIME,
2986 NVL(SUM(NVL(call_stage.IN_WRAP_TIME,0)),0) IN_WRAP_TIME,
2987 NVL(SUM(NVL(call_stage.OUT_TALK_TIME,0)),0) OUT_TALK_TIME,
2988 NVL(SUM(NVL(call_stage.OUT_WRAP_TIME,0)),0) OUT_WRAP_TIME,
2989 NVL(MIN(call_stage.IN_TALK_TIME),0) IN_MIN_TALK_TIME,
2990 NVL(MAX(call_stage.IN_TALK_TIME),0) IN_MAX_TALK_TIME,
2991 NVL(MIN(call_stage.OUT_TALK_TIME),0) OUT_MIN_TALK_TIME,
2992 NVL(MAX(call_stage.OUT_TALK_TIME),0) OUT_MAX_TALK_TIME,
2993 NVL(MIN(call_stage.IN_WRAP_TIME),0) IN_MIN_WRAP_TIME,
2994 NVL(MAX(call_stage.IN_WRAP_TIME),0) IN_MAX_WRAP_TIME,
2995 NVL(MIN(call_stage.OUT_WRAP_TIME),0) OUT_MIN_WRAP_TIME,
2996 NVL(MAX(call_stage.OUT_WRAP_TIME),0) OUT_MAX_WRAP_TIME,
2997 NVL(SUM(call_stage.SERVICE_REQUESTS_CREATED),0) SR_CREATED,
2998 NVL(SUM(call_stage.SERVICE_REQUESTS_OPENED),0) SR_OPENED,
2999 NVL(SUM(call_stage.SERVICE_REQUESTS_CLOSED),0) SR_CLOSED,
3000 NVL(SUM(call_stage.SERVICE_REQUESTS_CONTACT_CL),0) SR_FIRST_CONTACT_CLOSE,
3001 NVL(SUM(call_stage.SERVICE_REQUESTS_INFO_REQ),0) SR_ADDITIONAL_INFO_REQUESTED,
3002 NVL(SUM(call_stage.SERVICE_REQUESTS_KB_UPDATES),0) SR_KB_UPDATES,
3003 NVL(SUM(call_stage.LEADS_CREATED),0) LEADS_CREATED,
3004 NVL(SUM(call_stage.LEADS_UPDATED),0) LEADS_UPDATED,
3005 NVL(SUM(call_stage.LEADS_AMOUNT),0) LEADS_AMOUNT,
3006 NVL(SUM(call_stage.LEADS_CONV_TO_OPP),0) LEADS_CONVERTED_TO_OPP,
3007 NVL(SUM(call_stage.LEADS_AMOUNT_TXN),0) LEADS_AMOUNT_TXN,
3008 NVL(SUM(call_stage.OPPORTUNITIES_CREATED),0) OPPORTUNITIES_CREATED,
3009 NVL(SUM(call_stage.OPPORTUNITIES_UPDATED),0) OPPORTUNITIES_UPDATED,
3010 NVL(SUM(call_stage.OPPORTUNITIES_WON),0) OPPORTUNITIES_WON,
3011 NVL(SUM(call_stage.OPPORTUNITIES_WON_AMOUNT),0) OPPORTUNITIES_WON_AMOUNT,
3012 NVL(SUM(call_stage.OPPORTUNITIES_WON_AMOUNT_TXN),0) OPPORTUNITIES_WON_AMOUNT_TXN,
3013 NVL(SUM(call_stage.OPPORTUNITIES_CROSS_SOLD),0) OPPORTUNITIES_CROSS_SOLD,
3014 NVL(SUM(call_stage.OPPORTUNITIES_UP_SOLD),0) OPPORTUNITIES_UP_SOLD,
3015 NVL(SUM(call_stage.OPPORTUNITIES_DECLINED),0) OPPORTUNITIES_DECLINED,
3016 NVL(SUM(call_stage.OPPORTUNITIES_LOST),0) OPPORTUNITIES_LOST,
3017 SUM(NVL(OUT_CALLS_DIALED,0)) OUT_CALLS_DIALED,
3018 SUM(NVL( OUT_CONTACT_COUNT,0)) OUT_CONTACT_COUNT,
3019 SUM(NVL( OUT_NON_CONTACT_COUNT,0)) OUT_NON_CONTACT_COUNT,
3020 SUM(NVL(OUT_ABANDON_COUNT,0)) OUT_ABANDON_COUNT,
3021 SUM(NVL(OUT_BUSY_COUNT ,0)) OUT_BUSY_COUNT,
3022 SUM(NVL(OUT_RING_NOANSWER_COUNT,0)) OUT_RING_NOANSWER_COUNT,
3023 SUM(NVL(OUT_ANS_MC_COUNT ,0)) OUT_ANS_MC_COUNT,
3024 SUM(NVL(OUT_SIT_COUNT,0)) OUT_SIT_COUNT,
3025 SUM(NVL(OUT_POSITIVE_RESPONSE_COUNT,0)) OUT_POSITIVE_RESPONSE_COUNT,
3026 SUM(NVL(OUT_CONNECT_COUNT ,0)) OUT_CONNECT_COUNT,
3027 SUM(NVL(OUT_NON_CONNECT_COUNT ,0)) OUT_NON_CONNECT_COUNT,
3028 SUM(NVL(OUT_OTHER_OUTCOME_COUNT,0)) OUT_OTHER_OUTCOME_COUNT,
3029 SUM(NVL(OUT_PREVIEW_TIME,0)) OUT_PREVIEW_TIME,
3030 SUM(NVL(OUT_CONTACT_HANDLE_TIME,0)) OUT_CONTACT_HANDLE_TIME
3031 FROM bix_dm_interface call_stage
3032 where call_stage.RESOURCE_ID is not null
3033 GROUP BY call_stage.SERVER_GROUP_ID,
3034 call_stage.CLASSIFICATION_VALUE_ID,
3035 call_stage.CAMPAIGN_ID,
3036 call_stage.CAMPAIGN_SCHEDULE_ID,
3037 call_stage.resource_id,
3038 call_stage.period_start_date,
3039 call_stage.period_start_time,
3040 call_stage.PERIOD_START_DATE_TIME;
3041
3042 l_bix_agent_seq number;
3043 l_num_calls number;
3044
3045 BEGIN
3046
3047 g_insert_count := 0;
3048 g_delete_count := 0;
3049
3050 -- IGOR : initialize and check for 'no rows returned'
3051
3052 /* Get the count of rows from the */
3053
3054 SELECT count(*) INTO l_num_calls
3055 FROM bix_dm_interface;
3056
3057 if (l_num_calls > 0) THEN
3058
3059 IF (g_debug_flag = 'Y') THEN
3060 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.AGENT_SUM: '||' Start Deleting rows in BIM_DM_AGENT_SUM Table', g_proc_name);
3061 END IF;
3062
3063 -- Delete the rows from summary table for the date range specified
3064
3065 DELETE_IN_CHUNKS('BIX_DM_AGENT_SUM',
3066 'period_start_date_time BETWEEN '||' to_date('||
3067 ''''||
3068 to_char(g_min_call_begin_date,'YYYY/MM/DDHH24:MI:SS')||
3069 ''''||
3070 ',''YYYY/MM/DDHH24:MI:SS'') AND '||'to_date('||
3071 ''''||
3072 to_char(g_max_call_begin_date,'YYYY/MM/DDHH24:MI:SS')||
3073 ''''||
3074 ',''YYYY/MM/DDHH24:MI:SS'')',
3075 g_delete_count);
3076
3077 --dbms_output.put_line('Deleted count:'||g_delete_count);
3078
3079 COMMIT;
3080
3081 IF (g_debug_flag = 'Y') THEN
3082
3083 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.COLLECT_CALLS: '||' Finished Deleting rows in BIX_DM_AGENT_SUM table: ' || 'Row Count:' || g_delete_count, g_proc_name);
3084
3085 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_AGENT: '||' Start inserting rows into BIX_DM_AGENT_SUM table: ', g_proc_name);
3086
3087 END IF;
3088
3089
3090 for ag_row in agent_data LOOP
3091
3092
3093 SELECT BIX_DM_AGENT_SUM_S.NEXTVAL INTO l_bix_agent_seq FROM DUAL;
3094
3095 INSERT INTO BIX_DM_AGENT_SUM
3096 (
3097 AGENT_SUMMARY_ID,
3098 LAST_UPDATE_DATE,
3099 LAST_UPDATED_BY,
3100 CREATION_DATE,
3101 CREATED_BY,
3102 LAST_UPDATE_LOGIN,
3103 SERVER_GROUP_ID,
3104 --CLASSIFICATION_ID,
3105 CLASSIFICATION_VALUE_ID,
3106 CAMPAIGN_ID,
3107 CAMPAIGN_SCHEDULE_ID,
3108 RESOURCE_ID,
3109 PERIOD_START_DATE,
3110 PERIOD_START_TIME,
3111 PERIOD_START_DATE_TIME,
3112 IN_CALLS_HANDLED,
3113 IN_CALLS_HANDLD_GT_THN_X_TIME,
3114 CALLS_TRANSFERED,
3115 OUT_CALLS_HANDLED,
3116 OUT_CALLS_HANDLD_GT_THN_X_TIME,
3117 IN_TALK_TIME,
3118 IN_WRAP_TIME,
3119 OUT_TALK_TIME,
3120 OUT_WRAP_TIME,
3121 IN_MIN_TALK_TIME,
3122 IN_MAX_TALK_TIME,
3123 OUT_MIN_TALK_TIME,
3124 OUT_MAX_TALK_TIME,
3125 IN_MIN_WRAP_TIME,
3126 IN_MAX_WRAP_TIME,
3127 OUT_MIN_WRAP_TIME,
3128 OUT_MAX_WRAP_TIME,
3129 SR_CREATED,
3130 SR_OPENED,
3131 SR_CLOSED,
3132 SR_FIRST_CONTACT_CLOSE,
3133 SR_ADDITIONAL_INFO_REQUESTED,
3134 SR_KB_UPDATES,
3135 LEADS_CREATED,
3136 LEADS_UPDATED,
3137 LEADS_AMOUNT,
3138 LEADS_CONVERTED_TO_OPP,
3139 OPPORTUNITIES_CREATED,
3140 OPPORTUNITIES_UPDATED,
3141 OPPORTUNITIES_WON,
3142 OPPORTUNITIES_WON_AMOUNT,
3143 OPPORTUNITIES_CROSS_SOLD,
3144 OPPORTUNITIES_UP_SOLD,
3145 OPPORTUNITIES_DECLINED,
3146 OPPORTUNITIES_LOST,
3147 LEADS_AMOUNT_TXN,
3148 OPPORTUNITIES_WON_AMOUNT_TXN,
3149 OUT_CALLS_DIALED,
3150 OUT_CONTACT_COUNT,
3151 OUT_NON_CONTACT_COUNT,
3152 OUT_ABANDON_COUNT,
3153 OUT_BUSY_COUNT ,
3154 OUT_RING_NOANSWER_COUNT,
3155 OUT_ANS_MC_COUNT ,
3156 OUT_SIT_COUNT,
3157 OUT_POSITIVE_RESPONSE_COUNT,
3158 OUT_CONNECT_COUNT ,
3159 OUT_NON_CONNECT_COUNT ,
3160 OUT_OTHER_OUTCOME_COUNT,
3161 OUT_PREVIEW_TIME,
3162 OUT_CONTACT_HANDLE_TIME
3163 )
3164 VALUES
3165 (
3166 l_bix_agent_seq,
3167 SYSDATE,
3168 g_user_id,
3169 SYSDATE,
3170 g_user_id,
3171 SYSDATE,
3172 ag_row.SERVER_GROUP_ID,
3173 ag_row.CLASSIFICATION_VALUE_ID,
3174 ag_row.CAMPAIGN_ID,
3175 ag_row.CAMPAIGN_SCHEDULE_ID,
3176 ag_row.RESOURCE_ID,
3177 ag_row.PERIOD_START_DATE,
3178 ag_row.PERIOD_START_TIME,
3179 ag_row.PERIOD_START_DATE_TIME,
3180 ag_row.IN_CALLS_HANDLED,
3181 ag_row.IN_CALLS_HANDLD_GT_THN_X_TIME,
3182 ag_row.CALLS_TRANSFERED,
3183 ag_row.OUT_CALLS_HANDLED,
3184 ag_row.OUT_CALLS_HANDLD_GT_THN_X_TIME,
3185 ag_row.IN_TALK_TIME,
3186 ag_row.IN_WRAP_TIME,
3187 ag_row.OUT_TALK_TIME,
3188 ag_row.OUT_WRAP_TIME,
3189 ag_row.IN_MIN_TALK_TIME,
3190 ag_row.IN_MAX_TALK_TIME,
3191 ag_row.OUT_MIN_TALK_TIME,
3192 ag_row.OUT_MAX_TALK_TIME,
3193 ag_row.IN_MIN_WRAP_TIME,
3194 ag_row.IN_MAX_WRAP_TIME,
3195 ag_row.OUT_MIN_WRAP_TIME,
3196 ag_row.OUT_MAX_WRAP_TIME,
3197 ag_row.SR_CREATED,
3198 ag_row.SR_OPENED,
3199 ag_row.SR_CLOSED,
3200 ag_row.SR_FIRST_CONTACT_CLOSE,
3201 ag_row.SR_ADDITIONAL_INFO_REQUESTED,
3202 ag_row.SR_KB_UPDATES,
3203 ag_row.LEADS_CREATED,
3204 ag_row.LEADS_UPDATED,
3205 ag_row.LEADS_AMOUNT,
3206 ag_row.LEADS_CONVERTED_TO_OPP,
3207 ag_row.OPPORTUNITIES_CREATED,
3208 ag_row.OPPORTUNITIES_UPDATED,
3209 ag_row.OPPORTUNITIES_WON,
3210 ag_row.OPPORTUNITIES_WON_AMOUNT,
3211 ag_row.OPPORTUNITIES_CROSS_SOLD,
3212 ag_row.OPPORTUNITIES_UP_SOLD,
3213 ag_row.OPPORTUNITIES_DECLINED,
3214 ag_row.OPPORTUNITIES_LOST,
3215 ag_row.LEADS_AMOUNT_TXN,
3216 ag_row.OPPORTUNITIES_WON_AMOUNT_TXN,
3217 ag_row.OUT_CALLS_DIALED,
3218 ag_row.OUT_CONTACT_COUNT,
3219 ag_row.OUT_NON_CONTACT_COUNT,
3220 ag_row.OUT_ABANDON_COUNT,
3221 ag_row.OUT_BUSY_COUNT ,
3222 ag_row.OUT_RING_NOANSWER_COUNT,
3223 ag_row.OUT_ANS_MC_COUNT ,
3224 ag_row.OUT_SIT_COUNT,
3225 ag_row.OUT_POSITIVE_RESPONSE_COUNT,
3226 ag_row.OUT_CONNECT_COUNT ,
3227 ag_row.OUT_NON_CONNECT_COUNT ,
3228 ag_row.OUT_OTHER_OUTCOME_COUNT,
3229 ag_row.OUT_PREVIEW_TIME,
3230 ag_row.OUT_CONTACT_HANDLE_TIME
3231 );
3232
3233 commit;
3234
3235 g_insert_count := g_insert_count+1;
3236
3237 END LOOP;
3238 end if; -- if there are rows in the interface table
3239
3240 EXCEPTION
3241 WHEN OTHERS THEN
3242
3243 IF (g_debug_flag = 'Y') THEN
3244 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' BIX_DM_AGENT_SUMMARY_PKG.SUM_AGENT: '|| ' Failed to Rolled back data in BIX_DM_AGENT_SUM table ', g_proc_name);
3245 END IF;
3246
3247 g_proc_name := 'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_AGENT';
3248 g_error_mesg := g_proc_name ||' : '|| sqlerrm;
3249 RAISE;
3250
3251 END SUM_AGENT;
3252
3253
3254
3255
3256 -- Summarizing the BIX_DM_AGENT_SUM table by group and inserting rows into
3257 -- the BIX_DM_GROUP_SUM table. If an agent is in two or more groups the
3258 -- numbers will be double-counted. Therefore this table is not additive.
3259
3260 PROCEDURE SUM_GROUP AS
3261
3262 CURSOR group_data IS
3263 SELECT group_denorm.parent_group_id GROUP_ID,
3264 agent_sum.SERVER_GROUP_ID SERVER_GROUP_ID,
3265 --agent_sum.CLASSIFICATION_ID CLASSIFICATION_ID,
3266 agent_sum.CLASSIFICATION_VALUE_ID CLASSIFICATION_VALUE_ID,
3267 agent_sum.CAMPAIGN_ID CAMPAIGN_ID,
3268 agent_sum.CAMPAIGN_SCHEDULE_ID CAMPAIGN_SCHEDULE_ID,
3269 agent_sum.PERIOD_START_DATE PERIOD_START_DATE,
3270 agent_sum.PERIOD_START_TIME PERIOD_START_TIME,
3271 agent_sum.PERIOD_START_DATE_TIME PERIOD_START_DATE_TIME,
3272 sum(agent_sum.IN_CALLS_HANDLED) IN_CALLS_HANDLED,
3273 sum(agent_sum.IN_CALLS_HANDLD_GT_THN_X_TIME) IN_CALLS_HANDLD_GT_THN_X_TIME,
3274 sum(agent_sum.OUT_CALLS_HANDLED) OUT_CALLS_HANDLED,
3275 sum(agent_sum.OUT_CALLS_HANDLD_GT_THN_X_TIME) OUT_CALLS_HANDLD_GT_THN_X_TIME,
3276 sum(agent_sum.CALLS_TRANSFERED) CALLS_TRANSFERED,
3277 sum(agent_sum.IN_TALK_TIME) IN_TALK_TIME,
3278 sum(agent_sum.OUT_TALK_TIME) OUT_TALK_TIME,
3279 sum(agent_sum.IN_WRAP_TIME) IN_WRAP_TIME,
3280 sum(agent_sum.OUT_WRAP_TIME) OUT_WRAP_TIME,
3281 sum(agent_sum.IN_MIN_TALK_TIME) IN_MIN_TALK_TIME,
3282 sum(agent_sum.IN_MAX_TALK_TIME) IN_MAX_TALK_TIME,
3283 sum(agent_sum.OUT_MIN_TALK_TIME) OUT_MIN_TALK_TIME,
3284 sum(agent_sum.OUT_MAX_TALK_TIME) OUT_MAX_TALK_TIME,
3285 sum(agent_sum.IN_MIN_WRAP_TIME) IN_MIN_WRAP_TIME,
3286 sum(agent_sum.IN_MAX_WRAP_TIME) IN_MAX_WRAP_TIME,
3287 sum(agent_sum.OUT_MIN_WRAP_TIME) OUT_MIN_WRAP_TIME,
3288 sum(agent_sum.OUT_MAX_WRAP_TIME) OUT_MAX_WRAP_TIME,
3289 sum(agent_sum.SR_CREATED) SR_CREATED,
3290 sum(agent_sum.SR_OPENED) SR_OPENED,
3291 sum(agent_sum.SR_CLOSED) SR_CLOSED,
3292 sum(agent_sum.SR_FIRST_CONTACT_CLOSE) SR_FIRST_CONTACT_CLOSE,
3293 sum(agent_sum.SR_ADDITIONAL_INFO_REQUESTED) SR_ADDITIONAL_INFO_REQUESTED,
3294 sum(agent_sum.SR_KB_UPDATES) SR_KB_UPDATES,
3295 sum(agent_sum.LEADS_CREATED) LEADS_CREATED,
3296 sum(agent_sum.LEADS_UPDATED) LEADS_UPDATED,
3297 sum(agent_sum.LEADS_AMOUNT) LEADS_AMOUNT,
3298 sum(agent_sum.LEADS_AMOUNT_TXN) LEADS_AMOUNT_TXN,
3299 sum(agent_sum.LEADS_CONVERTED_TO_OPP) LEADS_CONVERTED_TO_OPP,
3300 sum(agent_sum.OPPORTUNITIES_CREATED) OPPORTUNITIES_CREATED,
3301 sum(agent_sum.OPPORTUNITIES_UPDATED) OPPORTUNITIES_UPDATED,
3302 sum(agent_sum.OPPORTUNITIES_WON) OPPORTUNITIES_WON,
3303 sum(agent_sum.OPPORTUNITIES_WON_AMOUNT) OPPORTUNITIES_WON_AMOUNT,
3304 sum(agent_sum.OPPORTUNITIES_WON_AMOUNT_TXN) OPPORTUNITIES_WON_AMOUNT_TXN,
3305 sum(agent_sum.OPPORTUNITIES_CROSS_SOLD) OPPORTUNITIES_CROSS_SOLD,
3306 sum(agent_sum.OPPORTUNITIES_UP_SOLD) OPPORTUNITIES_UP_SOLD,
3307 sum(agent_sum.OPPORTUNITIES_DECLINED) OPPORTUNITIES_DECLINED,
3308 sum(agent_sum.OPPORTUNITIES_LOST) OPPORTUNITIES_LOST,
3309 SUM(agent_sum.OUT_CALLS_DIALED) OUT_CALLS_DIALED,
3310 SUM(agent_sum.OUT_CONTACT_COUNT) OUT_CONTACT_COUNT,
3311 SUM(agent_sum.OUT_NON_CONTACT_COUNT) OUT_NON_CONTACT_COUNT,
3312 SUM(agent_sum.OUT_ABANDON_COUNT) OUT_ABANDON_COUNT,
3313 SUM(agent_sum.OUT_BUSY_COUNT) OUT_BUSY_COUNT,
3314 SUM(agent_sum.OUT_RING_NOANSWER_COUNT) OUT_RING_NOANSWER_COUNT,
3315 SUM(agent_sum.OUT_ANS_MC_COUNT) OUT_ANS_MC_COUNT,
3316 SUM(agent_sum.OUT_SIT_COUNT) OUT_SIT_COUNT,
3317 SUM(agent_sum.OUT_POSITIVE_RESPONSE_COUNT) OUT_POSITIVE_RESPONSE_COUNT,
3318 SUM(agent_sum.OUT_CONNECT_COUNT) OUT_CONNECT_COUNT,
3319 SUM(agent_sum.OUT_NON_CONNECT_COUNT) OUT_NON_CONNECT_COUNT,
3320 SUM(agent_sum.OUT_OTHER_OUTCOME_COUNT) OUT_OTHER_OUTCOME_COUNT,
3321 SUM(agent_sum.OUT_PREVIEW_TIME) OUT_PREVIEW_TIME,
3322 SUM(agent_sum.OUT_CONTACT_HANDLE_TIME) OUT_CONTACT_HANDLE_TIME
3323 FROM bix_dm_agent_sum agent_sum,
3324 jtf_rs_group_members groups,
3325 jtf_rs_groups_denorm group_denorm
3326 WHERE agent_sum.period_start_date_time BETWEEN g_min_call_begin_date AND g_max_call_begin_date
3327 AND agent_sum.resource_id = groups.resource_id
3328 AND groups.group_id = group_denorm.group_id
3329 --
3330 --add the following to take care of cases where
3331 --agent belongs to two groups which roll up to the
3332 --same parent group to avoid duplicating the values
3333 --for the parent group
3334 --
3335 AND NVL(groups.delete_flag,'N') <> 'Y'
3336 AND agent_sum.period_start_date_time BETWEEN
3337 NVL(group_denorm.start_date_active,agent_sum.period_start_date_time)
3338 AND NVL(group_denorm.end_date_active,SYSDATE)
3339 AND groups.group_member_id =
3340 (select max(mem1.group_member_id)
3341 from jtf_rs_group_members mem1
3342 where mem1.group_id in
3343 (select den1.group_id
3344 from jtf_rs_groups_denorm den1
3345 where den1.parent_group_id = group_denorm.parent_group_id
3346 AND agent_sum.period_start_date_time BETWEEN
3347 NVL(den1.start_date_active,agent_sum.period_start_date_time)
3348 AND NVL(den1.end_date_active,SYSDATE)
3349 )
3350 AND mem1.resource_id = groups.resource_id
3351 AND nvl(mem1.delete_flag,'N') <> 'Y'
3352 )
3353 GROUP BY group_denorm.parent_group_id,
3354 agent_sum.SERVER_GROUP_ID,
3355 agent_sum.CLASSIFICATION_VALUE_ID,
3356 agent_sum.CAMPAIGN_ID,
3357 agent_sum.CAMPAIGN_SCHEDULE_ID,
3358 agent_sum.period_start_date,
3359 agent_sum.period_start_time,
3360 agent_sum.PERIOD_START_DATE_TIME;
3361
3362 l_bix_group_seq number;
3363 l_num_calls number;
3364
3365 BEGIN
3366
3367 g_insert_count := 0;
3368 g_delete_count := 0;
3369
3370 -- IGOR : initialize and check for 'no rows returned'
3371
3372 /* Get the count of rows from the */
3373
3374 SELECT count(*) INTO l_num_calls
3375 FROM bix_dm_agent_sum where period_start_date_time BETWEEN
3376 g_min_call_begin_date and g_max_call_begin_date;
3377
3378 if (l_num_calls > 0) THEN
3379
3380 IF (g_debug_flag = 'Y') THEN
3381 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.GROUP_SUM: '||' Start Deleting rows in BIM_DM_GROUP_SUM Table', g_proc_name);
3382 END IF;
3383
3384 -- Delete the rows from summary table for the date range specified
3385
3386 DELETE_IN_CHUNKS('BIX_DM_GROUP_SUM',
3387 'period_start_date_time BETWEEN '||' to_date('||
3388 ''''||
3389 to_char(g_min_call_begin_date,'YYYY/MM/DDHH24:MI:SS')||
3390 ''''||
3391 ',''YYYY/MM/DDHH24:MI:SS'') AND '||'to_date('||
3392 ''''||
3393 to_char(g_max_call_begin_date,'YYYY/MM/DDHH24:MI:SS')||
3394 ''''||
3395 ',''YYYY/MM/DDHH24:MI:SS'')',
3396 g_delete_count);
3397
3398 --dbms_output.put_line('Deleted count:'||g_delete_count);
3399
3400 COMMIT;
3401
3402 IF (g_debug_flag = 'Y') THEN
3403
3404 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_GROUP: '||' Finished Deleting rows in BIX_DM_GROUP_SUM table: ' || 'Row Count:' || g_delete_count, g_proc_name);
3405
3406 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.AGENT_SUM: '||' Start inserting rows into BIX_DM_AGENT_SUM table: ', g_proc_name);
3407
3408 END IF;
3409
3410
3411 for gr_row in group_data LOOP
3412
3413
3414 SELECT BIX_DM_GROUP_SUM_S.NEXTVAL INTO l_bix_group_seq FROM DUAL;
3415
3416 INSERT INTO BIX_DM_GROUP_SUM
3417 (
3418 GROUP_SUMMARY_ID,
3419 LAST_UPDATE_DATE,
3420 LAST_UPDATED_BY,
3421 CREATION_DATE,
3422 CREATED_BY,
3423 LAST_UPDATE_LOGIN,
3424 SERVER_GROUP_ID,
3425 --CLASSIFICATION_ID,
3426 CLASSIFICATION_VALUE_ID,
3427 CAMPAIGN_ID,
3428 CAMPAIGN_SCHEDULE_ID,
3429 GROUP_ID,
3430 PERIOD_START_DATE,
3431 PERIOD_START_TIME,
3432 PERIOD_START_DATE_TIME,
3433 IN_CALLS_HANDLED,
3434 IN_CALLS_HANDLD_GT_THN_X_TIME,
3435 CALLS_TRANSFERED,
3436 OUT_CALLS_HANDLED,
3437 OUT_CALLS_HANDLD_GT_THN_X_TIME,
3438 IN_TALK_TIME,
3439 IN_WRAP_TIME,
3440 OUT_TALK_TIME,
3441 OUT_WRAP_TIME,
3442 IN_MIN_TALK_TIME,
3443 IN_MAX_TALK_TIME,
3444 OUT_MIN_TALK_TIME,
3445 OUT_MAX_TALK_TIME,
3446 IN_MIN_WRAP_TIME,
3447 IN_MAX_WRAP_TIME,
3448 OUT_MIN_WRAP_TIME,
3449 OUT_MAX_WRAP_TIME,
3450 SR_CREATED,
3451 SR_OPENED,
3452 SR_CLOSED,
3453 SR_FIRST_CONTACT_CLOSE,
3454 SR_ADDITIONAL_INFO_REQUESTED,
3455 SR_KB_UPDATES,
3456 LEADS_CREATED,
3457 LEADS_UPDATED,
3458 LEADS_AMOUNT,
3459 LEADS_CONVERTED_TO_OPP,
3460 LEADS_AMOUNT_TXN,
3461 OPPORTUNITIES_CREATED,
3462 OPPORTUNITIES_UPDATED,
3463 OPPORTUNITIES_WON,
3464 OPPORTUNITIES_WON_AMOUNT,
3465 OPPORTUNITIES_WON_AMOUNT_TXN,
3466 OPPORTUNITIES_CROSS_SOLD,
3467 OPPORTUNITIES_UP_SOLD,
3468 OPPORTUNITIES_DECLINED,
3469 OPPORTUNITIES_LOST,
3470 OUT_CALLS_DIALED,
3471 OUT_CONTACT_COUNT,
3472 OUT_NON_CONTACT_COUNT,
3473 OUT_ABANDON_COUNT,
3474 OUT_BUSY_COUNT ,
3475 OUT_RING_NOANSWER_COUNT,
3476 OUT_ANS_MC_COUNT ,
3477 OUT_SIT_COUNT,
3478 OUT_POSITIVE_RESPONSE_COUNT,
3479 OUT_CONNECT_COUNT ,
3480 OUT_NON_CONNECT_COUNT ,
3481 OUT_OTHER_OUTCOME_COUNT,
3482 OUT_PREVIEW_TIME,
3483 OUT_CONTACT_HANDLE_TIME
3484 )
3485 VALUES
3486 (
3487 l_bix_group_seq,
3488 SYSDATE,
3489 g_user_id,
3490 SYSDATE,
3491 g_user_id,
3492 SYSDATE,
3493 gr_row.SERVER_GROUP_ID,
3494 gr_row.CLASSIFICATION_VALUE_ID,
3495 gr_row.CAMPAIGN_ID,
3496 gr_row.CAMPAIGN_SCHEDULE_ID,
3497 gr_row.GROUP_ID,
3498 gr_row.PERIOD_START_DATE,
3499 gr_row.PERIOD_START_TIME,
3500 gr_row.PERIOD_START_DATE_TIME,
3501 gr_row.IN_CALLS_HANDLED,
3502 gr_row.IN_CALLS_HANDLD_GT_THN_X_TIME,
3503 gr_row.CALLS_TRANSFERED,
3504 gr_row.OUT_CALLS_HANDLED,
3505 gr_row.OUT_CALLS_HANDLD_GT_THN_X_TIME,
3506 gr_row.IN_TALK_TIME,
3507 gr_row.IN_WRAP_TIME,
3508 gr_row.OUT_TALK_TIME,
3509 gr_row.OUT_WRAP_TIME,
3510 gr_row.IN_MIN_TALK_TIME,
3511 gr_row.IN_MAX_TALK_TIME,
3512 gr_row.OUT_MIN_TALK_TIME,
3513 gr_row.OUT_MAX_TALK_TIME,
3514 gr_row.IN_MIN_WRAP_TIME,
3515 gr_row.IN_MAX_WRAP_TIME,
3516 gr_row.OUT_MIN_WRAP_TIME,
3517 gr_row.OUT_MAX_WRAP_TIME,
3518 gr_row.SR_CREATED,
3519 gr_row.SR_OPENED,
3520 gr_row.SR_CLOSED,
3521 gr_row.SR_FIRST_CONTACT_CLOSE,
3522 gr_row.SR_ADDITIONAL_INFO_REQUESTED,
3523 gr_row.SR_KB_UPDATES,
3524 gr_row.LEADS_CREATED,
3525 gr_row.LEADS_UPDATED,
3526 gr_row.LEADS_AMOUNT,
3527 gr_row.LEADS_CONVERTED_TO_OPP,
3528 gr_row.LEADS_AMOUNT_TXN,
3529 gr_row.OPPORTUNITIES_CREATED,
3530 gr_row.OPPORTUNITIES_UPDATED,
3531 gr_row.OPPORTUNITIES_WON,
3532 gr_row.OPPORTUNITIES_WON_AMOUNT,
3533 gr_row.OPPORTUNITIES_WON_AMOUNT_TXN,
3534 gr_row.OPPORTUNITIES_CROSS_SOLD,
3535 gr_row.OPPORTUNITIES_UP_SOLD,
3536 gr_row.OPPORTUNITIES_DECLINED,
3537 gr_row.OPPORTUNITIES_LOST,
3538 gr_row.OUT_CALLS_DIALED,
3539 gr_row.OUT_CONTACT_COUNT,
3540 gr_row.OUT_NON_CONTACT_COUNT,
3541 gr_row.OUT_ABANDON_COUNT,
3542 gr_row.OUT_BUSY_COUNT ,
3543 gr_row.OUT_RING_NOANSWER_COUNT,
3544 gr_row.OUT_ANS_MC_COUNT ,
3545 gr_row.OUT_SIT_COUNT,
3546 gr_row.OUT_POSITIVE_RESPONSE_COUNT,
3547 gr_row.OUT_CONNECT_COUNT ,
3548 gr_row.OUT_NON_CONNECT_COUNT ,
3549 gr_row.OUT_OTHER_OUTCOME_COUNT,
3550 gr_row.OUT_PREVIEW_TIME,
3551 gr_row.OUT_CONTACT_HANDLE_TIME
3552 );
3553
3554 commit;
3555
3556 g_insert_count := g_insert_count+1;
3557
3558 END LOOP;
3559 end if; -- if there are rows in the interface table
3560
3561 EXCEPTION
3562 WHEN OTHERS THEN
3563
3564 IF (g_debug_flag = 'Y') THEN
3565
3566 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' BIX_DM_AGENT_SUMMARY_PKG.SUM_GROUP: '|| ' Failed to Rolled back data in BIX_DM_GROUP_SUM table ', g_proc_name);
3567
3568 END IF;
3569
3570 g_proc_name := 'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_GROUP';
3571 g_error_mesg := g_proc_name ||' : '|| sqlerrm;
3572 RAISE;
3573
3574 END SUM_GROUP;
3575
3576
3577
3578 -- Summarizing the interface table by call and inserting rows into
3579 -- the BIX_DM_CALL_SUM table
3580
3581 PROCEDURE SUM_CALL AS
3582
3583 CURSOR call_data IS
3584 SELECT call_stage.SERVER_GROUP_ID SERVER_GROUP_ID,
3585 --call_stage.CLASSIFICATION_ID CLASSIFICATION_ID,
3586 call_stage.CLASSIFICATION_VALUE_ID CLASSIFICATION_VALUE_ID,
3587 call_stage.DNIS DNIS,
3588 call_stage.campaign_id CAMPAIGN_ID,
3589 call_stage.campaign_schedule_id CAMPAIGN_SCHEDULE_ID,
3590 call_stage.PERIOD_START_DATE PERIOD_START_DATE,
3591 call_stage.PERIOD_START_TIME PERIOD_START_TIME,
3592 call_stage.PERIOD_START_DATE_TIME PERIOD_START_DATE_TIME,
3593 NVL(SUM(call_stage.IN_CALLS_HANDLED),0) IN_CALLS_HANDLED,
3594 NVL(SUM(call_stage.IN_CALLS_HANDLD_GT_THN_X_TIME),0) IN_CALLS_HANDLD_GT_THN_X_TIME,
3595 NVL(SUM(call_stage.CALLS_TRANSFERED),0) CALLS_TRANSFERED,
3596 NVL(SUM(call_stage.OUT_CALLS_HANDLED),0) OUT_CALLS_HANDLED,
3597 NVL(SUM(call_stage.OUT_CALLS_HANDLD_GT_THN_X_TIME),0) OUT_CALLS_HANDLD_GT_THN_X_TIME,
3598 NVL(SUM(NVL(call_stage.IN_TALK_TIME,0)),0) IN_TALK_TIME,
3599 NVL(SUM(NVL(call_stage.IN_WRAP_TIME,0)),0) IN_WRAP_TIME,
3600 NVL(SUM(NVL(call_stage.OUT_TALK_TIME,0)),0) OUT_TALK_TIME,
3601 NVL(SUM(NVL(call_stage.OUT_WRAP_TIME,0)),0) OUT_WRAP_TIME,
3602 NVL(SUM(call_stage.CALLS_OFFERED),0) CALLS_OFFERED,
3603 NVL(SUM(call_stage.CALLS_IN_QUEUE),0) CALLS_IN_QUEUE,
3604 NVL(SUM(call_stage.CALLS_ABANDONED),0) CALLS_ABANDONED,
3605 NVL(SUM(call_stage.CALLS_ANSWRD_WITHIN_X_TIME),0) CALLS_ANSWRD_WITHIN_X_TIME,
3606 NVL(SUM(call_stage.IVR_TIME),0) IVR_TIME,
3607 NVL(SUM(call_stage.ROUTE_TIME),0) ROUTE_TIME,
3608 NVL(SUM(call_stage.QUEUE_TIME),0) QUEUE_TIME,
3609 NVL(SUM(call_stage.ABANDON_TIME),0) ABANDON_TIME,
3610 NVL(MIN(call_stage.IVR_TIME),0) MIN_IVR_TIME,
3611 NVL(MIN(call_stage.ROUTE_TIME),0) MIN_ROUTE_TIME,
3612 NVL(MIN(call_stage.QUEUE_TIME),0) MIN_QUEUE_TIME,
3613 NVL(MIN(call_stage.ABANDON_TIME),0) MIN_ABANDON_TIME,
3614 NVL(MAX(call_stage.IVR_TIME),0) MAX_IVR_TIME,
3615 NVL(MAX(call_stage.ROUTE_TIME),0) MAX_ROUTE_TIME,
3616 NVL(MAX(call_stage.QUEUE_TIME),0) MAX_QUEUE_TIME,
3617 NVL(MAX(call_stage.ABANDON_TIME),0) MAX_ABANDON_TIME,
3618 NVL(SUM(call_stage.SERVICE_REQUESTS_CREATED),0) SR_CREATED,
3619 NVL(SUM(call_stage.SERVICE_REQUESTS_OPENED),0) SR_OPENED,
3620 NVL(SUM(call_stage.SERVICE_REQUESTS_CLOSED),0) SR_CLOSED,
3621 NVL(SUM(call_stage.SERVICE_REQUESTS_CONTACT_CL),0) SR_FIRST_CONTACT_CLOSE,
3622 NVL(SUM(call_stage.SERVICE_REQUESTS_INFO_REQ),0) SR_ADDITIONAL_INFO_REQUESTED,
3623 NVL(SUM(call_stage.SERVICE_REQUESTS_KB_UPDATES),0) SR_KB_UPDATES,
3624 NVL(SUM(call_stage.LEADS_CREATED),0) LEADS_CREATED,
3625 NVL(SUM(call_stage.LEADS_UPDATED),0) LEADS_UPDATED,
3626 NVL(SUM(call_stage.LEADS_AMOUNT),0) LEADS_AMOUNT,
3627 NVL(SUM(call_stage.LEADS_AMOUNT_TXN),0) LEADS_AMOUNT_TXN,
3628 NVL(SUM(call_stage.LEADS_CONV_TO_OPP),0) LEADS_CONVERTED_TO_OPP,
3629 NVL(SUM(call_stage.OPPORTUNITIES_CREATED),0) OPPORTUNITIES_CREATED,
3630 NVL(SUM(call_stage.OPPORTUNITIES_UPDATED),0) OPPORTUNITIES_UPDATED,
3631 NVL(SUM(call_stage.OPPORTUNITIES_WON),0) OPPORTUNITIES_WON,
3632 NVL(SUM(call_stage.OPPORTUNITIES_WON_AMOUNT),0) OPPORTUNITIES_WON_AMOUNT,
3633 NVL(SUM(call_stage.OPPORTUNITIES_WON_AMOUNT_TXN),0) OPPORTUNITIES_WON_AMOUNT_TXN,
3634 NVL(SUM(call_stage.OPPORTUNITIES_CROSS_SOLD),0) OPPORTUNITIES_CROSS_SOLD,
3635 NVL(SUM(call_stage.OPPORTUNITIES_UP_SOLD),0) OPPORTUNITIES_UP_SOLD,
3636 NVL(SUM(call_stage.OPPORTUNITIES_DECLINED),0) OPPORTUNITIES_DECLINED,
3637 NVL(SUM(call_stage.OPPORTUNITIES_LOST),0) OPPORTUNITIES_LOST,
3638 NVL(SUM(decode(call_stage.OPPORTUNITIES_WON,0,0,NULL,0,1)),0) NO_OF_OPP_WON_CALLS,
3639 SUM(decode(NVL(call_stage.OPPORTUNITIES_WON,0)+NVL(call_stage.OPPORTUNITIES_CROSS_SOLD,0)+NVL(call_stage.OPPORTUNITIES_UP_SOLD,0),0,0,1)) NO_OF_OPP_SOLD_CALLS,
3640 NVL(SUM(call_stage.QUEUE_TIME_FOR_CALLS_HANDLED),0) QUEUE_TIME_FOR_CALLS_HANDLED,
3641 NVL(MAX(call_stage.QUEUE_TIME_FOR_CALLS_HANDLED),0) MAX_QUEUE_TIME_CALLS_HANDLD,
3642 NVL(SUM(call_stage.NUMBER_OF_REROUTS),0) NUMBER_OF_REROUTS,
3643 SUM(NVL(OUT_CALLS_DIALED,0)) OUT_CALLS_DIALED,
3644 SUM(NVL(OUT_CONTACT_COUNT,0)) OUT_CONTACT_COUNT,
3645 SUM(NVL(OUT_NON_CONTACT_COUNT,0)) OUT_NON_CONTACT_COUNT,
3646 SUM(NVL(OUT_ABANDON_COUNT,0)) OUT_ABANDON_COUNT,
3647 SUM(NVL(OUT_BUSY_COUNT,0)) OUT_BUSY_COUNT,
3648 SUM(NVL(OUT_RING_NOANSWER_COUNT,0)) OUT_RING_NOANSWER_COUNT,
3649 SUM(NVL(OUT_ANS_MC_COUNT,0)) OUT_ANS_MC_COUNT,
3650 SUM(NVL(OUT_SIT_COUNT,0)) OUT_SIT_COUNT,
3651 SUM(NVL(OUT_POSITIVE_RESPONSE_COUNT,0)) OUT_POSITIVE_RESPONSE_COUNT,
3652 SUM(NVL(OUT_CONNECT_COUNT,0)) OUT_CONNECT_COUNT,
3653 SUM(NVL(OUT_NON_CONNECT_COUNT,0)) OUT_NON_CONNECT_COUNT,
3654 SUM(NVL(OUT_OTHER_OUTCOME_COUNT,0)) OUT_OTHER_OUTCOME_COUNT,
3655 SUM(NVL(OUT_CONTACT_HANDLE_TIME,0)) OUT_CONTACT_HANDLE_TIME,
3656 SUM(NVL(DECODE(ROW_TYPE,'C',OUT_PREVIEW_TIME,0),0)) OUT_PREVIEW_TIME --do only "C" rowtype
3657 FROM bix_dm_interface call_stage
3658 GROUP BY call_stage.SERVER_GROUP_ID,
3659 call_stage.CLASSIFICATION_VALUE_ID,
3660 call_stage.DNIS,
3661 call_stage.campaign_id,
3662 call_stage.campaign_schedule_id,
3663 call_stage.period_start_date,
3664 call_stage.period_start_time,
3665 call_stage.PERIOD_START_DATE_TIME;
3666
3667 l_bix_call_seq number;
3668 l_num_calls number;
3669
3670 BEGIN
3671
3672 g_insert_count := 0;
3673 g_delete_count := 0;
3674
3675 -- IGOR : initialize and check for 'no rows returned'
3676
3677 /* Get the count of rows from the */
3678
3679 SELECT count(*) INTO l_num_calls
3680 FROM bix_dm_interface;
3681
3682 if (l_num_calls > 0) THEN
3683
3684 IF (g_debug_flag = 'Y') THEN
3685 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.CALL_SUM: '||' Start Deleting rows in BIM_DM_CALL_SUM Table', g_proc_name);
3686 END IF;
3687
3688 -- Delete the rows from summary table for the date range specified
3689
3690 DELETE_IN_CHUNKS('BIX_DM_CALL_SUM',
3691 'period_start_date_time BETWEEN '||' to_date('||
3692 ''''||
3693 to_char(g_min_call_begin_date,'YYYY/MM/DDHH24:MI:SS')||
3694 ''''||
3695 ',''YYYY/MM/DDHH24:MI:SS'') AND '||'to_date('||
3696 ''''||
3697 to_char(g_max_call_begin_date,'YYYY/MM/DDHH24:MI:SS')||
3698 ''''||
3699 ',''YYYY/MM/DDHH24:MI:SS'')',
3700 g_delete_count);
3701
3702 --dbms_output.put_line('Deleted count:'||g_delete_count);
3703
3704 COMMIT;
3705
3706 IF (g_debug_flag = 'Y') THEN
3707 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.CALL_SUM: '||' Finished Deleting rows in BIX_DM_CALL_SUM table: ' || 'Row Count:' || g_delete_count, g_proc_name);
3708
3709 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_CALL: '||' Start inserting rows into BIX_DM_CALL_SUM table: ', g_proc_name);
3710 END IF;
3711
3712
3713 for call_row in call_data LOOP
3714
3715
3716 SELECT BIX_DM_CALL_SUM_S.NEXTVAL INTO l_bix_call_seq FROM DUAL;
3717
3718 INSERT INTO BIX_DM_CALL_SUM
3719 (
3720 CALL_SUMMARY_ID,
3721 LAST_UPDATE_DATE,
3722 LAST_UPDATED_BY,
3723 CREATION_DATE,
3724 CREATED_BY,
3725 LAST_UPDATE_LOGIN,
3726 SERVER_GROUP_ID,
3727 --CLASSIFICATION_ID,
3728 CLASSIFICATION_VALUE_ID,
3729 DNIS_ID,
3730 CAMPAIGN_ID,
3731 CAMPAIGN_SCHEDULE_ID,
3732 PERIOD_START_DATE,
3733 PERIOD_START_TIME,
3734 PERIOD_START_DATE_TIME,
3735 IN_CALLS_HANDLED,
3736 IN_CALLS_HANDLD_GT_THN_X_TIME,
3737 CALLS_TRANSFERED,
3738 OUT_CALLS_HANDLED,
3739 OUT_CALLS_HANDLD_GT_THN_X_TIME,
3740 IN_TALK_TIME,
3741 IN_WRAP_TIME,
3742 OUT_TALK_TIME,
3743 OUT_WRAP_TIME,
3744 CALLS_OFFERED,
3745 CALLS_IN_QUEUE,
3746 CALLS_ABANDONED,
3747 CALLS_ANSWRD_WITHIN_X_TIME,
3748 IVR_TIME,
3749 ROUTE_TIME,
3750 QUEUE_TIME,
3751 ABANDON_TIME,
3752 MIN_IVR_TIME,
3753 MIN_ROUTE_TIME,
3754 MIN_QUEUE_TIME,
3755 MIN_ABANDON_TIME,
3756 MAX_IVR_TIME,
3757 MAX_ROUTE_TIME,
3758 MAX_QUEUE_TIME,
3759 MAX_ABANDON_TIME,
3760 SR_CREATED,
3761 SR_OPENED,
3762 SR_CLOSED,
3763 SR_FIRST_CONTACT_CLOSE,
3764 SR_ADDITIONAL_INFO_REQUESTED,
3765 SR_KB_UPDATES,
3766 LEADS_CREATED,
3767 LEADS_UPDATED,
3768 LEADS_AMOUNT,
3769 LEADS_CONVERTED_TO_OPP,
3770 OPPORTUNITIES_CREATED,
3771 OPPORTUNITIES_UPDATED,
3772 OPPORTUNITIES_WON,
3773 OPPORTUNITIES_WON_AMOUNT,
3774 OPPORTUNITIES_CROSS_SOLD,
3775 OPPORTUNITIES_UP_SOLD,
3776 OPPORTUNITIES_DECLINED,
3777 OPPORTUNITIES_LOST,
3778 NO_OF_OPP_WON_CALLS,
3779 NO_OF_OPP_SOLD_CALLS,
3780 CALLS_OFFRD_GT_THN_X_TIME,
3781 QUEUE_TIME_FOR_CALLS_HANDLED,
3782 MAX_QUEUE_TIME_CALLS_HANDLD,
3783 NUMBER_OF_REROUTES,
3784 LEADS_AMOUNT_TXN,
3785 OPPORTUNITIES_WON_AMOUNT_TXN,
3786 OUT_CALLS_DIALED,
3787 OUT_CONTACT_COUNT,
3788 OUT_NON_CONTACT_COUNT,
3789 OUT_ABANDON_COUNT,
3790 OUT_BUSY_COUNT ,
3791 OUT_RING_NOANSWER_COUNT,
3792 OUT_ANS_MC_COUNT ,
3793 OUT_SIT_COUNT,
3794 OUT_POSITIVE_RESPONSE_COUNT,
3795 OUT_CONNECT_COUNT ,
3796 OUT_NON_CONNECT_COUNT ,
3797 OUT_OTHER_OUTCOME_COUNT,
3798 OUT_CONTACT_HANDLE_TIME,
3799 OUT_PREVIEW_TIME
3800 )
3801 VALUES
3802 (
3803 l_bix_call_seq,
3804 SYSDATE,
3805 g_user_id,
3806 SYSDATE,
3807 g_user_id,
3808 SYSDATE,
3809 call_row.SERVER_GROUP_ID,
3810 call_row.CLASSIFICATION_VALUE_ID,
3811 call_row.DNIS,
3812 call_row.CAMPAIGN_ID,
3813 call_row.CAMPAIGN_SCHEDULE_ID,
3814 call_row.PERIOD_START_DATE,
3815 call_row.PERIOD_START_TIME,
3816 call_row.PERIOD_START_DATE_TIME,
3817 call_row.IN_CALLS_HANDLED,
3818 call_row.IN_CALLS_HANDLD_GT_THN_X_TIME,
3819 call_row.CALLS_TRANSFERED,
3820 call_row.OUT_CALLS_HANDLED,
3821 call_row.OUT_CALLS_HANDLD_GT_THN_X_TIME,
3822 call_row.IN_TALK_TIME,
3823 call_row.IN_WRAP_TIME,
3824 call_row.OUT_TALK_TIME,
3825 call_row.OUT_WRAP_TIME,
3826 call_row.CALLS_OFFERED,
3827 call_row.CALLS_IN_QUEUE,
3828 call_row.CALLS_ABANDONED,
3829 call_row.CALLS_ANSWRD_WITHIN_X_TIME,
3830 call_row.IVR_TIME,
3831 call_row.ROUTE_TIME,
3832 call_row.QUEUE_TIME,
3833 call_row.ABANDON_TIME,
3834 call_row.MIN_IVR_TIME,
3835 call_row.MIN_ROUTE_TIME,
3836 call_row.MIN_QUEUE_TIME,
3837 call_row.MIN_ABANDON_TIME,
3838 call_row.MAX_IVR_TIME,
3839 call_row.MAX_ROUTE_TIME,
3840 call_row.MAX_QUEUE_TIME,
3841 call_row.MAX_ABANDON_TIME,
3842 call_row.SR_CREATED,
3843 call_row.SR_OPENED,
3844 call_row.SR_CLOSED,
3845 call_row.SR_FIRST_CONTACT_CLOSE,
3846 call_row.SR_ADDITIONAL_INFO_REQUESTED,
3847 call_row.SR_KB_UPDATES,
3848 call_row.LEADS_CREATED,
3849 call_row.LEADS_UPDATED,
3850 call_row.LEADS_AMOUNT,
3851 call_row.LEADS_CONVERTED_TO_OPP,
3852 call_row.OPPORTUNITIES_CREATED,
3853 call_row.OPPORTUNITIES_UPDATED,
3854 call_row.OPPORTUNITIES_WON,
3855 call_row.OPPORTUNITIES_WON_AMOUNT,
3856 call_row.OPPORTUNITIES_CROSS_SOLD,
3857 call_row.OPPORTUNITIES_UP_SOLD,
3858 call_row.OPPORTUNITIES_DECLINED,
3859 call_row.OPPORTUNITIES_LOST,
3860 call_row.NO_OF_OPP_WON_CALLS,
3861 call_row.NO_OF_OPP_SOLD_CALLS,
3862 call_row.IN_CALLS_HANDLD_GT_THN_X_TIME + call_row.CALLS_ABANDONED,
3863 call_row.QUEUE_TIME_FOR_CALLS_HANDLED,
3864 call_row.MAX_QUEUE_TIME_CALLS_HANDLD,
3865 call_row.NUMBER_OF_REROUTS,
3866 call_row.LEADS_AMOUNT_TXN,
3867 call_row.OPPORTUNITIES_WON_AMOUNT_TXN,
3868 call_row.OUT_CALLS_DIALED,
3869 call_row.OUT_CONTACT_COUNT,
3870 call_row.OUT_NON_CONTACT_COUNT,
3871 call_row.OUT_ABANDON_COUNT,
3872 call_row.OUT_BUSY_COUNT ,
3873 call_row.OUT_RING_NOANSWER_COUNT,
3874 call_row.OUT_ANS_MC_COUNT ,
3875 call_row.OUT_SIT_COUNT,
3876 call_row.OUT_POSITIVE_RESPONSE_COUNT,
3877 call_row.OUT_CONNECT_COUNT ,
3878 call_row.OUT_NON_CONNECT_COUNT ,
3879 call_row.OUT_OTHER_OUTCOME_COUNT,
3880 call_row.OUT_CONTACT_HANDLE_TIME,
3881 call_row.OUT_PREVIEW_TIME
3882 );
3883
3884 commit;
3885
3886 g_insert_count := g_insert_count+1;
3887
3888 END LOOP;
3889 end if; -- if there are rows in the interface table
3890
3891 EXCEPTION
3892 WHEN OTHERS THEN
3893 IF (g_debug_flag = 'Y') THEN
3894 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' BIX_DM_AGENT_SUMMARY_PKG.SUM_CALL: '|| ' Failed to Rolled back data in BIX_DM_CALL_SUM table ', g_proc_name);
3895 END IF;
3896
3897 g_proc_name := 'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_CALL';
3898 g_error_mesg := g_proc_name ||' : '|| sqlerrm;
3899 RAISE;
3900
3901 END SUM_CALL;
3902
3903 /******************************************************************************************
3904 *
3905 *SUM_AGENT_OUTCOME will populate the BIX_DM_AGENT_OUTCOME_SUM table.
3906 *
3907 *****************************************************************************************/
3908
3909 -- Summarizing the interface table by and inserting into the
3910 -- the BIX_DM_AGENT_OUTCOME_SUM table
3911
3912 PROCEDURE SUM_AGENT_OUTCOME AS
3913
3914 CURSOR outcome_data IS
3915 SELECT call_stage.campaign_id CAMPAIGN_ID,
3916 call_stage.campaign_schedule_id CAMPAIGN_SCHEDULE_ID,
3917 call_stage.SERVER_GROUP_ID SERVER_GROUP_ID,
3918 nvl(call_stage.resource_id,-999) RESOURCE_ID,
3919 call_stage.period_start_date PERIOD_START_DATE,
3920 call_stage.period_start_time PERIOD_START_TIME,
3921 call_stage.period_start_date_time PERIOD_START_DATE_TIME,
3922 'OUTBOUND' DIRECTION,
3923 call_stage.OUTCOME_ID OUTCOME_ID,
3924 call_stage.RESULT_ID RESULT_ID,
3925 call_stage.REASON_ID REASON_ID,
3926 clook.connect_flag CONNECT_FLAG,
3927 clook.contact_flag CONTACT_FLAG,
3928 rlook.positive_response_flag POSITIVE_RESPONSE_FLAG,
3929 sum(call_stage.OUT_CALLS_DIALED) NUMBER_OF_CALLS
3930 FROM bix_dm_interface call_stage,
3931 bix_dm_connect_lookups clook,
3932 bix_dm_response_lookups rlook
3933 WHERE clook.outcome_id (+) = call_stage.outcome_id
3934 --AND rlook.outcome_id (+) = call_stage.outcome_id
3935 AND rlook.result_id (+) = call_stage.result_id
3936 AND call_stage.row_type IN ('C', 'A') --ignore activity type rows
3937 AND call_stage.direction = '1' --only OUTBOUND calls
3938 GROUP BY call_stage.campaign_id,
3939 call_stage.campaign_schedule_id,
3940 call_stage.SERVER_GROUP_ID,
3941 nvl(call_stage.resource_id,-999),
3942 call_stage.period_start_date,
3943 call_stage.period_start_time,
3944 call_stage.period_start_date_time,
3945 'OUTBOUND',
3946 call_stage.OUTCOME_ID,
3947 call_stage.RESULT_ID,
3948 call_stage.REASON_ID,
3949 clook.connect_flag,
3950 clook.contact_flag,
3951 rlook.positive_response_flag;
3952
3953 l_bix_call_seq number;
3954 l_num_calls number;
3955
3956 BEGIN
3957
3958 g_insert_count := 0;
3959 g_delete_count := 0;
3960
3961 IF (g_debug_flag = 'Y') THEN
3962 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_AGENT_OUTCOME: Entered procedure', g_proc_name);
3963 END IF;
3964
3965
3966 /* Get the count of rows from the */
3967
3968 SELECT count(*) INTO l_num_calls
3969 FROM bix_dm_interface;
3970
3971 IF (g_debug_flag = 'Y') THEN
3972 write_log('No of rows in injterface table is ' ||l_num_calls, g_proc_name);
3973 END IF;
3974
3975 if (l_num_calls > 0) THEN
3976
3977 IF (g_debug_flag = 'Y') THEN
3978 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_AGENT_OUTCOME: '||' Start Deleting rows in BIM_DM_AGENT_OUTCOME_SUM Table', g_proc_name);
3979 END IF;
3980
3981 -- Delete the rows from summary table for the date range specified
3982
3983 DELETE_IN_CHUNKS('BIX_DM_AGENT_OUTCOME_SUM',
3984 'period_start_date_time BETWEEN '||' to_date('||
3985 ''''||
3986 to_char(g_min_call_begin_date,'YYYY/MM/DDHH24:MI:SS')||
3987 ''''||
3988 ',''YYYY/MM/DDHH24:MI:SS'') AND '||'to_date('||
3989 ''''||
3990 to_char(g_max_call_begin_date,'YYYY/MM/DDHH24:MI:SS')||
3991 ''''||
3992 ',''YYYY/MM/DDHH24:MI:SS'')',
3993 g_delete_count);
3994
3995 --dbms_output.put_line('Deleted count:'||g_delete_count);
3996
3997 COMMIT;
3998
3999 IF (g_debug_flag = 'Y') THEN
4000
4001 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_AGENT_OUTCOME: '||' Finished Deleting rows in BIX_DM_AGENT_OUTCOME_SUM table: ' || 'Row Count:' || g_delete_count, g_proc_name);
4002
4003 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_AGENT_OUTCOME: '||' Start inserting rows into BIX_DM_AGENT_OUTCOME_SUM table: ', g_proc_name);
4004
4005 END IF;
4006
4007
4008 for outcome_row in outcome_data LOOP
4009
4010 INSERT INTO BIX_DM_AGENT_OUTCOME_SUM
4011 (
4012 AGENT_OUTCOME_SUM_ID,
4013 LAST_UPDATE_DATE,
4014 LAST_UPDATED_BY,
4015 CREATION_DATE,
4016 CREATED_BY,
4017 LAST_UPDATE_LOGIN,
4018 CAMPAIGN_ID,
4019 CAMPAIGN_SCHEDULE_ID,
4020 SERVER_GROUP_ID,
4021 DIRECTION,
4022 RESOURCE_ID,
4023 PERIOD_START_DATE,
4024 PERIOD_START_TIME,
4025 PERIOD_START_DATE_TIME,
4026 OUTCOME_ID,
4027 RESULT_ID,
4028 REASON_ID,
4029 CONNECT_FLAG,
4030 CONTACT_FLAG,
4031 POSITIVE_RESPONSE_FLAG,
4032 NUMBER_OF_CALLS
4033 )
4034 VALUES
4035 (
4036 BIX_DM_AGENT_OUTCOME_SUM_S.NEXTVAL,
4037 SYSDATE,
4038 g_user_id,
4039 SYSDATE,
4040 g_user_id,
4041 g_user_id,
4042 outcome_row.CAMPAIGN_ID,
4043 outcome_row.CAMPAIGN_SCHEDULE_ID,
4044 outcome_row.SERVER_GROUP_ID,
4045 outcome_row.DIRECTION,
4046 outcome_row.RESOURCE_ID,
4047 outcome_row.PERIOD_START_DATE,
4048 outcome_row.PERIOD_START_TIME,
4049 outcome_row.PERIOD_START_DATE_TIME,
4050 outcome_row.OUTCOME_ID,
4051 outcome_row.RESULT_ID,
4052 outcome_row.REASON_ID,
4053 outcome_row.CONNECT_FLAG,
4054 outcome_row.CONTACT_FLAG,
4055 outcome_row.POSITIVE_RESPONSE_FLAG,
4056 outcome_row.NUMBER_OF_CALLS
4057 );
4058
4059 commit;
4060
4061 g_insert_count := g_insert_count+1;
4062
4063 END LOOP;
4064 end if; -- if there are rows in the interface table
4065
4066 IF (g_debug_flag = 'Y') THEN
4067
4068 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_AGENT_OUTCOME: Ended procedure', g_proc_name);
4069
4070 END IF;
4071
4072 EXCEPTION
4073 WHEN OTHERS THEN
4074
4075 IF (g_debug_flag = 'Y') THEN
4076 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_AGENT_OUTCOME: Exception ', g_proc_name);
4077 END IF;
4078
4079 g_proc_name := 'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_AGENT_OUTCOME';
4080 g_error_mesg := g_proc_name ||' : '|| sqlerrm;
4081 RAISE;
4082
4083 END SUM_AGENT_OUTCOME;
4084
4085
4086
4087 /******************End SUM_AGENT_OUTCOME **************************************************/
4088
4089 /******************************************************************************************
4090 *
4091 *SUM_GROUP_OUTCOME will populate the BIX_DM_GROUP_OUTCOME_SUM table.
4092 *
4093 *****************************************************************************************/
4094
4095
4096 -- Summarizing the interface table by and inserting into the
4097 -- the BIX_DM_GROUP_OUTCOME_SUM table
4098
4099 PROCEDURE SUM_GROUP_OUTCOME AS
4100
4101 CURSOR outcome_data IS
4102 SELECT call_stage.campaign_id CAMPAIGN_ID,
4103 call_stage.campaign_schedule_id CAMPAIGN_SCHEDULE_ID,
4104 call_stage.SERVER_GROUP_ID SERVER_GROUP_ID,
4105 group_denorm.parent_group_id GROUP_ID,
4106 call_stage.period_start_date PERIOD_START_DATE,
4107 call_stage.period_start_time PERIOD_START_TIME,
4108 call_stage.period_start_date_time PERIOD_START_DATE_TIME,
4109 call_stage.direction DIRECTION,
4110 call_stage.OUTCOME_ID OUTCOME_ID,
4111 call_stage.RESULT_ID RESULT_ID,
4112 call_stage.REASON_ID REASON_ID,
4113 call_stage.connect_flag CONNECT_FLAG,
4114 call_stage.contact_flag CONTACT_FLAG,
4115 call_stage.positive_response_flag POSITIVE_RESPONSE_FLAG,
4116 sum(call_stage.number_of_calls) NUMBER_OF_CALLS
4117 FROM bix_dm_agent_outcome_sum call_stage,
4118 jtf_rs_group_members groups,
4119 jtf_rs_groups_denorm group_denorm
4120 WHERE call_stage.resource_id = groups.resource_id
4121 AND call_stage.period_start_date_time BETWEEN g_min_call_begin_date AND g_max_call_begin_date
4122 AND groups.group_id = group_denorm.group_id
4123 AND call_stage.resource_id IS NOT NULL
4124 --
4125 --add the following to take care of cases where
4126 --agent belongs to two groups which roll up to the
4127 --same parent group to avoid duplicating the values
4128 --for the parent group
4129 --
4130 AND NVL(groups.delete_flag,'N') <> 'Y'
4131 AND call_stage.period_start_date_time BETWEEN
4132 NVL(group_denorm.start_date_active,call_stage.period_start_date_time)
4133 AND NVL(group_denorm.end_date_active,SYSDATE)
4134 AND groups.group_member_id =
4135 (select max(mem1.group_member_id)
4136 from jtf_rs_group_members mem1
4137 where mem1.group_id in
4138 (select den1.group_id
4139 from jtf_rs_groups_denorm den1
4140 where den1.parent_group_id = group_denorm.parent_group_id
4141 AND call_stage.period_start_date_time BETWEEN
4142 NVL(den1.start_date_active,call_stage.period_start_date_time)
4143 AND NVL(den1.end_date_active,SYSDATE)
4144 )
4145 AND mem1.resource_id = groups.resource_id
4146 AND nvl(mem1.delete_flag,'N') <> 'Y'
4147 )
4148 GROUP BY call_stage.campaign_id,
4149 call_stage.campaign_schedule_id,
4150 call_stage.SERVER_GROUP_ID,
4151 group_denorm.parent_group_id,
4152 call_stage.period_start_date,
4153 call_stage.period_start_time,
4154 call_stage.period_start_date_time,
4155 call_stage.direction,
4156 call_stage.OUTCOME_ID,
4157 call_stage.RESULT_ID,
4158 call_stage.REASON_ID,
4159 call_stage.connect_flag,
4160 call_stage.contact_flag,
4161 call_stage.positive_response_flag
4162 UNION
4163 SELECT call_stage.campaign_id CAMPAIGN_ID,
4164 call_stage.campaign_schedule_id CAMPAIGN_SCHEDULE_ID,
4165 call_stage.SERVER_GROUP_ID SERVER_GROUP_ID,
4166 -999, --calls with no agents
4167 call_stage.period_start_date PERIOD_START_DATE,
4168 call_stage.period_start_time PERIOD_START_TIME,
4169 call_stage.period_start_date_time PERIOD_START_DATE_TIME,
4170 call_stage.direction DIRECTION,
4171 call_stage.OUTCOME_ID OUTCOME_ID,
4172 call_stage.RESULT_ID RESULT_ID,
4173 call_stage.REASON_ID REASON_ID,
4174 call_stage.connect_flag CONNECT_FLAG,
4175 call_stage.contact_flag CONTACT_FLAG,
4176 call_stage.positive_response_flag POSITIVE_RESPONSE_FLAG,
4177 sum(call_stage.number_of_calls) NUMBER_OF_CALLS
4178 FROM bix_dm_agent_outcome_sum call_stage
4179 WHERE call_stage.resource_id IS NULL --calls with no agents
4180 AND call_stage.period_start_date_time BETWEEN g_min_call_begin_date AND g_max_call_begin_date
4181 GROUP BY call_stage.campaign_id,
4182 call_stage.campaign_schedule_id,
4183 call_stage.SERVER_GROUP_ID,
4184 -999,
4185 call_stage.period_start_date,
4186 call_stage.period_start_time,
4187 call_stage.period_start_date_time,
4188 call_stage.direction,
4189 call_stage.OUTCOME_ID,
4190 call_stage.RESULT_ID,
4191 call_stage.REASON_ID,
4192 call_stage.connect_flag,
4193 call_stage.contact_flag,
4194 call_stage.positive_response_flag
4195 ;
4196
4197
4198 l_bix_call_seq number;
4199 l_num_calls number;
4200
4201 BEGIN
4202
4203 g_insert_count := 0;
4204 g_delete_count := 0;
4205
4206 /* Get the count of rows from the */
4207
4208 SELECT count(*) INTO l_num_calls
4209 FROM bix_dm_interface;
4210
4211 if (l_num_calls > 0) THEN
4212
4213 IF (g_debug_flag = 'Y') THEN
4214 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_GROUP_OUTCOME: '||' Start Deleting rows in BIM_DM_GROUP_OUTCOME_SUM Table', g_proc_name);
4215 END IF;
4216
4217 -- Delete the rows from summary table for the date range specified
4218
4219 DELETE_IN_CHUNKS('BIX_DM_GROUP_OUTCOME_SUM',
4220 'period_start_date_time BETWEEN '||' to_date('||
4221 ''''||
4222 to_char(g_min_call_begin_date,'YYYY/MM/DDHH24:MI:SS')||
4223 ''''||
4224 ',''YYYY/MM/DDHH24:MI:SS'') AND '||'to_date('||
4225 ''''||
4226 to_char(g_max_call_begin_date,'YYYY/MM/DDHH24:MI:SS')||
4227 ''''||
4228 ',''YYYY/MM/DDHH24:MI:SS'')',
4229 g_delete_count);
4230
4231 --dbms_output.put_line('Deleted count:'||g_delete_count);
4232
4233 COMMIT;
4234
4235 IF (g_debug_flag = 'Y') THEN
4236 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_GROUP_OUTCOME: '||' Finished Deleting rows in BIX_DM_GROUP_OUTCOME_SUM table: ' || 'Row Count:' || g_delete_count, g_proc_name);
4237
4238 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_GROUP_OUTCOME: '||' Start inserting rows into BIX_DM_GROUP_OUTCOME_SUM table: ', g_proc_name);
4239 END IF;
4240
4241
4242 for outcome_row in outcome_data LOOP
4243
4244 INSERT INTO BIX_DM_GROUP_OUTCOME_SUM
4245 (
4246 GROUP_OUTCOME_SUM_ID,
4247 LAST_UPDATE_DATE,
4248 LAST_UPDATED_BY,
4249 CREATION_DATE,
4250 CREATED_BY,
4251 LAST_UPDATE_LOGIN,
4252 CAMPAIGN_ID,
4253 CAMPAIGN_SCHEDULE_ID,
4254 SERVER_GROUP_ID,
4255 DIRECTION,
4256 GROUP_ID,
4257 PERIOD_START_DATE,
4258 PERIOD_START_TIME,
4259 PERIOD_START_DATE_TIME,
4260 OUTCOME_ID,
4261 RESULT_ID,
4262 REASON_ID,
4263 CONNECT_FLAG,
4264 CONTACT_FLAG,
4265 POSITIVE_RESPONSE_FLAG,
4266 NUMBER_OF_CALLS
4267 )
4268 VALUES
4269 (
4270 BIX_DM_GROUP_OUTCOME_SUM_S.NEXTVAL,
4271 SYSDATE,
4272 g_user_id,
4273 SYSDATE,
4274 g_user_id,
4275 g_user_id,
4276 outcome_row.CAMPAIGN_ID,
4277 outcome_row.CAMPAIGN_SCHEDULE_ID,
4278 outcome_row.SERVER_GROUP_ID,
4279 outcome_row.DIRECTION,
4280 outcome_row.GROUP_ID,
4281 outcome_row.PERIOD_START_DATE,
4282 outcome_row.PERIOD_START_TIME,
4283 outcome_row.PERIOD_START_DATE_TIME,
4284 outcome_row.OUTCOME_ID,
4285 outcome_row.RESULT_ID,
4286 outcome_row.REASON_ID,
4287 outcome_row.CONNECT_FLAG,
4288 outcome_row.CONTACT_FLAG,
4289 outcome_row.POSITIVE_RESPONSE_FLAG,
4290 outcome_row.NUMBER_OF_CALLS
4291 );
4292
4293 commit;
4294
4295 g_insert_count := g_insert_count+1;
4296
4297 END LOOP;
4298 end if; -- if there are rows in the interface table
4299
4300 EXCEPTION
4301 WHEN OTHERS THEN
4302
4303 IF (g_debug_flag = 'Y') THEN
4304 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' BIX_DM_AGENT_SUMMARY_PKG.SUM_GROUP_OUTCOME: '|| ' Failed to Roll back data in BIX_DM_GROUP_OUTCOME_SUM table ', g_proc_name);
4305 END IF;
4306
4307 g_proc_name := 'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_GROUP_OUTCOME';
4308 g_error_mesg := g_proc_name ||' : '|| sqlerrm;
4309 RAISE;
4310
4311 END SUM_GROUP_OUTCOME;
4312
4313
4314
4315 /******************End SUM_GROUP_OUTCOME **************************************************/
4316
4317
4318
4319
4320
4321
4322
4323
4324
4325 /*========================================================================================================+
4326 | COLLECT_CALLS_SUMMARY procedure is main procedure calls other procedures to collect the data |
4327 | The following procedures are invoked from this procedure to collect the data. |
4328 | 1. GET_CALLS : Which populates the BIX_DM_INTERFACE staging table |
4329 | 2. AGENT_SUM : Summarizes agent data and inserts it into the BIX_DM_AGENT_SUM table |
4330 | 3. GROUP_SUM : Summarizes agent group data and inserts it into the BIX_DM_GROUP_SUM table|
4331 | 4. BIX_POP_AO_SUM_PKG.populate: Summarizes adv. outbound data and inserts it into the BIX_DM_AO_SUM tbl |
4332 | 5. CALL_SUM : Summarizes call data and inserts it into the BIX_DM_CALL_SUM table |
4333 | |
4334 | The procedure 1 populates BIX_DM_INTERFACE staging table, the others use the staging table to populate |
4335 | the other tables. After each procedure the insert_log procedure is called to insert the table data into |
4336 | the log table BIX_DM_COLLECT_LOG. |
4337 | There are two versions of this procedure, one called from the concurrent manager and the other callable |
4338 | from sqlplus for debugging purposses. The version below is the one callable from sqlplus. |
4339 ========================================================================================================+*/
4340
4341 PROCEDURE COLLECT_CALLS_SUMMARY(p_start_date IN VARCHAR2,p_end_date IN VARCHAR2)
4342 AS
4343 l_collect_end_date date;
4344 l_collect_start_date date;
4345 BEGIN
4346 /* intialize all global variables */
4347
4348 g_request_id := FND_GLOBAL.CONC_REQUEST_ID();
4349 g_program_appl_id := FND_GLOBAL.PROG_APPL_ID();
4350 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
4351 g_user_id := FND_GLOBAL.USER_ID();
4352 g_insert_count := 0;
4353 g_delete_count := 0;
4354 g_program_start_date := SYSDATE;
4355 g_error_mesg := NULL;
4356 g_status := 'FAILED';
4357 g_table_name := 'BIX_DM_INTERFACE';
4358 g_proc_name := 'BIX_DM_AGENT_CALL_SUMMARY_PKG.COLLECT_CALLS_SUMMARY';
4359
4360 IF (FND_PROFILE.DEFINED('BIX_DM_PREFERRED_CURRENCY')) THEN
4361 g_preferred_currency := FND_PROFILE.VALUE('BIX_DM_PREFERRED_CURRENCY');
4362 ELSE
4363 g_preferred_currency := 'USD';
4364 END IF;
4365
4366 IF (FND_PROFILE.DEFINED('BIX_DM_CURR_CONVERSION_TYPE')) THEN
4367 g_conversion_type := FND_PROFILE.VALUE('BIX_DM_CURR_CONVERSION_TYPE');
4368 ELSE
4369 g_preferred_currency := 'Corporate';
4370 END IF;
4371
4372 g_collect_start_date := TO_DATE(p_start_date,'YYYY/MM/DD HH24:MI:SS');
4373 g_collect_end_date := TO_DATE(p_end_date,'YYYY/MM/DD HH24:MI:SS');
4374
4375 IF (g_collect_start_date > SYSDATE) THEN
4376 g_collect_start_date := SYSDATE;
4377 END IF;
4378
4379 IF (g_collect_end_date > SYSDATE) THEN
4380 g_collect_end_date := SYSDATE;
4381 END IF;
4382
4383 IF (g_collect_start_date > g_collect_end_date) THEN
4384 RAISE G_DATE_MISMATCH;
4385 END IF;
4386
4387 l_collect_start_date := g_collect_start_date;
4388 l_collect_end_date := g_collect_end_date;
4389
4390
4391
4392 IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
4393 g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
4394 ELSE
4395 g_commit_chunk_size := 100;
4396 END IF;
4397
4398 IF (FND_PROFILE.DEFINED('BIX_DBI_DEBUG')) THEN
4399 g_debug_flag := nvl(FND_PROFILE.VALUE('BIX_DBI_DEBUG'), 'N');
4400 ELSE
4401 g_debug_flag := 'N';
4402 END IF;
4403
4404 /* Delete the rows from BIX_DM_EXCEL table which are older than 2 hours */
4405
4406 IF (g_debug_flag = 'Y') THEN
4407 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' Start delete of BIX_DM_EXCEL table', g_proc_name);
4408 END IF;
4409
4410 DELETE_IN_CHUNKS('BIX_DM_EXCEL',' creation_date < SYSDATE-2/24',g_delete_count);
4411 COMMIT;
4412
4413 IF (g_debug_flag = 'Y') THEN
4414 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' End delete of BIX_DM_EXCEL table ' || g_delete_count, g_proc_name);
4415 END IF;
4416
4417 loop
4418 if (g_collect_end_date - g_collect_start_date) > 1 then
4419 g_collect_end_date := g_collect_start_date + 1;
4420 end if;
4421
4422 -- do the thing with g_collect_end_date and g_collect_start_date:
4423
4424
4425 /* Round the Collection start date nearest lower time bucket. ex: if time is between 10:00 and 10:29
4426 round it to 10:00.
4427 */
4428
4429 SELECT TO_DATE(
4430 TO_CHAR(g_collect_start_date,'YYYY/MM/DD')||
4431 LPAD(TO_CHAR(g_collect_start_date,'HH24:'),3,'0')||
4432 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_start_date,'MI'))-29),0,'00:00',1,'30:00',-1,'00:00'),
4433 'YYYY/MM/DDHH24:MI:SS')
4434 INTO g_rounded_collect_start_date
4435 FROM DUAL;
4436
4437
4438 /* Round the Collection end date to nearest higher time bucket. ex: if time is between 10:00 and 10:29
4439 round it to 10:29:59
4440 */
4441
4442 SELECT TO_DATE(
4443 TO_CHAR(g_collect_end_date,'YYYY/MM/DD')||
4444 LPAD(TO_CHAR(g_collect_end_date,'HH24:'),3,'0')||
4445 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_end_date,'MI'))-29),0,'29:59',1,'59:59',-1,'29:59'),
4446 'YYYY/MM/DDHH24:MI:SS')
4447 INTO g_rounded_collect_end_date
4448 FROM DUAL;
4449
4450
4451 /* Procedure collect calls from OLTP to the temporary table BIX_DM_INTERFACE: */
4452 GET_CALLS;
4453 g_status := 'SUCCESS';
4454 g_table_name := 'BIX_DM_INTERFACE';
4455 insert_log;
4456
4457 /* Procedure collects all the calls information for the AGENT data */
4458
4459 --dbms_output.put_line('calling SUM_AGENT');
4460 SUM_AGENT;
4461
4462 g_status := 'SUCCESS';
4463 g_table_name := 'BIX_DM_AGENT_SUM';
4464 insert_log;
4465
4466 /* Procedure collects all the calls information for the GROUP data */
4467
4468 SUM_GROUP;
4469 g_status := 'SUCCESS';
4470 g_table_name := 'BIX_DM_GROUP_SUM';
4471 insert_log;
4472
4473 /* Procedure collects all the calls information for the GROUP data */
4474
4475 SUM_CALL;
4476 g_status := 'SUCCESS';
4477 g_table_name := 'BIX_DM_CALL_SUM';
4478 insert_log;
4479
4480 /* Advanced Outbound Data Population */
4481
4482 --if upper(is_oao_installed) = 'Y' then
4483 --BIX_POP_AO_SUM_PKG.populate(g_min_call_begin_date, g_max_call_begin_date);
4484 --end if;
4485
4486 /* Procedure collects all the outcomes information for the AGENT data */
4487
4488 IF (g_debug_flag = 'Y') THEN
4489 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' Calling procedure SUM_AGENT_OUTCOME', g_proc_name);
4490 END IF;
4491
4492 SUM_AGENT_OUTCOME;
4493
4494 g_status := 'SUCCESS';
4495 g_table_name := 'BIX_DM_AGENT_OUTCOME_SUM';
4496 insert_log;
4497
4498 /* Procedure collects all the outcomes information for the GROUP data */
4499
4500 IF (g_debug_flag = 'Y') THEN
4501 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' Calling procedure SUM_GROUP_OUTCOME', g_proc_name);
4502 END IF;
4503
4504 SUM_GROUP_OUTCOME;
4505
4506 g_status := 'SUCCESS';
4507 g_table_name := 'BIX_DM_GROUP_OUTCOME_SUM';
4508 insert_log;
4509
4510 /* Cleanup: Delete all from the BIX_DM_INTERFACE table */
4511
4512 -- DELETE_IN_CHUNKS('BIX_DM_INTERFACE',NULL,g_delete_count);
4513 -- g_insert_count := 0;
4514 -- g_status := 'SUCCESS';
4515 -- g_table_name := 'BIX_DM_INTERFACE';
4516 -- insert_log;
4517
4518 g_collect_start_date := g_collect_end_date;
4519
4520 if (l_collect_end_date - g_collect_end_date) > 1 then
4521 g_collect_end_date := g_collect_end_date + 1;
4522 elsif ((l_collect_end_date - g_collect_end_date) > 0) and
4523 ((l_collect_end_date - g_collect_end_date) <= 1) then
4524 g_collect_end_date := l_collect_end_date;
4525 else
4526 exit;
4527 end if;
4528
4529
4530 end loop;
4531
4532 EXCEPTION
4533 WHEN G_DATE_MISMATCH THEN
4534 IF (g_debug_flag = 'Y') THEN
4535 write_log('Collect Start Date cannot be greater than collection end date', g_proc_name);
4536 END IF;
4537 WHEN OTHERS THEN
4538 g_status := 'FAILED';
4539 IF (g_debug_flag = 'Y') THEN
4540 write_log(g_error_mesg, g_proc_name);
4541 END IF;
4542 insert_log;
4543 END COLLECT_CALLS_SUMMARY;
4544
4545 /*========================================================================================================+
4546 | COLLECT_CALLS_SUMMARY procedure is main procedure calls other procedures to collect the data |
4547 | The following procedures are invoked from this procedure to collect the data. |
4548 | 1. GET_CALLS : Which populates the BIX_DM_INTERFACE staging table |
4549 | 2. AGENT_SUM : Summarizes agent data and inserts it into the BIX_DM_AGENT_SUM table |
4550 | 3. GROUP_SUM : Summarizes agent group data and inserts it into the BIX_DM_GROUP_SUM table|
4551 | 4. BIX_POP_AO_SUM_PKG.populate: Summarizes adv. outbound data and inserts it into the BIX_DM_AO_SUM tbl |
4552 | 5. CALL_SUM : Summarizes call data and inserts it into the BIX_DM_CALL_SUM table |
4553 | |
4554 | The procedure 1 populates BIX_DM_INTERFACE staging table, the others use the staging table to populate |
4555 | the other tables. After each procedure the insert_log procedure is called to insert the table data into |
4556 | the log table BIX_DM_COLLECT_LOG. |
4557 | There are two versions of this procedure, one called from the concurrent manager and the other callable |
4558 | from sqlplus for debugging purposses. The version below is the one called from the concurrent manager. |
4559 ========================================================================================================+*/
4560
4561 PROCEDURE COLLECT_CALLS_SUMMARY(errbuf out nocopy varchar2, retcode out nocopy varchar2, p_start_date IN VARCHAR2, p_end_date IN VARCHAR2)
4562 AS
4563 l_collect_start_date date;
4564 l_collect_end_date date;
4565 BEGIN
4566
4567 /* intialize all global variables */
4568
4569 g_request_id := FND_GLOBAL.CONC_REQUEST_ID();
4570 g_program_appl_id := FND_GLOBAL.PROG_APPL_ID();
4571 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
4572 g_user_id := FND_GLOBAL.USER_ID();
4573 g_insert_count := 0;
4574 g_delete_count := 0;
4575 g_program_start_date := SYSDATE;
4576 g_error_mesg := NULL;
4577 g_status := 'FAILED';
4578 g_table_name := 'BIX_DM_INTERFACE';
4579 g_proc_name := 'BIX_DM_AGENT_CALL_SUMMARY_PKG.COLLECT_CALLS_SUMMARY';
4580
4581 IF (FND_PROFILE.DEFINED('BIX_DM_PREFERRED_CURRENCY')) THEN
4582 g_preferred_currency := FND_PROFILE.VALUE('BIX_DM_PREFERRED_CURRENCY');
4583 ELSE
4584 g_preferred_currency := 'USD';
4585 END IF;
4586
4587 IF (FND_PROFILE.DEFINED('BIX_DM_CURR_CONVERSION_TYPE')) THEN
4588 g_conversion_type := FND_PROFILE.VALUE('BIX_DM_CURR_CONVERSION_TYPE');
4589 ELSE
4590 g_preferred_currency := 'Corporate';
4591 END IF;
4592
4593 g_collect_start_date := TO_DATE(p_start_date,'YYYY/MM/DD HH24:MI:SS');
4594 g_collect_end_date := TO_DATE(p_end_date,'YYYY/MM/DD HH24:MI:SS');
4595
4596 IF (g_collect_start_date > SYSDATE) THEN
4597 g_collect_start_date := SYSDATE;
4598 END IF;
4599
4600 IF (g_collect_end_date > SYSDATE) THEN
4601 g_collect_end_date := SYSDATE;
4602 END IF;
4603
4604 IF (g_collect_start_date > g_collect_end_date) THEN
4605 RAISE G_DATE_MISMATCH;
4606 END IF;
4607
4608 l_collect_start_date := g_collect_start_date;
4609 l_collect_end_date := g_collect_end_date;
4610
4611
4612 IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
4613 g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
4614 ELSE
4615 g_commit_chunk_size := 100;
4616 END IF;
4617
4618 IF (FND_PROFILE.DEFINED('BIX_DBI_DEBUG')) THEN
4619 g_debug_flag := nvl(FND_PROFILE.VALUE('BIX_DBI_DEBUG'), 'N');
4620 ELSE
4621 g_debug_flag := 'N';
4622 END IF;
4623
4624 /* Delete the rows from BIX_DM_EXCEL table which are older than 2 hours */
4625
4626 IF (g_debug_flag = 'Y') THEN
4627 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' Start delete of BIX_DM_EXCEL table', g_proc_name);
4628 END IF;
4629
4630 DELETE_IN_CHUNKS('BIX_DM_EXCEL',' creation_date < SYSDATE-2/24',g_delete_count);
4631 COMMIT;
4632
4633 IF (g_debug_flag = 'Y') THEN
4634 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' End delete of BIX_DM_EXCEL table ' || g_delete_count, g_proc_name);
4635 END IF;
4636
4637 loop
4638 if (g_collect_end_date - g_collect_start_date) > 1 then
4639 g_collect_end_date := g_collect_start_date + 1;
4640 end if;
4641
4642 -- do the thing with g_collect_end_date and g_collect_start_date:
4643
4644 /* Round the Collection start date nearest lower time bucket. ex: if time is between 10:00 and 10:29
4645 round it to 10:00.
4646 */
4647
4648 SELECT TO_DATE(
4649 TO_CHAR(g_collect_start_date,'YYYY/MM/DD')||
4650 LPAD(TO_CHAR(g_collect_start_date,'HH24:'),3,'0')||
4651 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_start_date,'MI'))-29),0,'00:00',1,'30:00',-1,'00:00'),
4652 'YYYY/MM/DDHH24:MI:SS')
4653 INTO g_rounded_collect_start_date
4654 FROM DUAL;
4655
4656
4657 /* Round the Collection end date to nearest higher time bucket. ex: if time is between 10:00 and 10:29
4658 round it to 10:29:59
4659 */
4660
4661 SELECT TO_DATE(
4662 TO_CHAR(g_collect_end_date,'YYYY/MM/DD')||
4663 LPAD(TO_CHAR(g_collect_end_date,'HH24:'),3,'0')||
4664 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_end_date,'MI'))-29),0,'29:59',1,'59:59',-1,'29:59'),
4665 'YYYY/MM/DDHH24:MI:SS')
4666 INTO g_rounded_collect_end_date
4667 FROM DUAL;
4668
4669 /* Get the commit size from the profile value. if the profile is not defined assume commit size as 100 */
4670
4671 IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
4672 g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
4673 ELSE
4674 g_commit_chunk_size := 100;
4675 END IF;
4676
4677 /* Procedure collect calls from OLTP to the temporary table BIX_DM_INTERFACE: */
4678 GET_CALLS;
4679 g_status := 'SUCCESS';
4680 g_table_name := 'BIX_DM_INTERFACE';
4681 insert_log;
4682
4683 /* Procedure collects all the calls information for the AGENT data */
4684 SUM_AGENT;
4685
4686 g_status := 'SUCCESS';
4687 g_table_name := 'BIX_DM_AGENT_SUM';
4688 insert_log;
4689
4690 /* Procedure collects all the calls information for the GROUP data */
4691
4692 SUM_GROUP;
4693 g_status := 'SUCCESS';
4694 g_table_name := 'BIX_DM_GROUP_SUM';
4695 insert_log;
4696
4697 /* Procedure collects all the calls information for the CALL data */
4698
4699 SUM_CALL;
4700 g_status := 'SUCCESS';
4701 g_table_name := 'BIX_DM_CALL_SUM';
4702 insert_log;
4703
4704
4705 /* Advanced Outbound Data Population */
4706
4707 --if upper(is_oao_installed) = 'Y' then
4708 --BIX_POP_AO_SUM_PKG.populate(g_min_call_begin_date, g_max_call_begin_date);
4709 --end if;
4710
4711 /* Procedure collects all the outcomes information for the AGENT data */
4712
4713 IF (g_debug_flag = 'Y') THEN
4714 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' Calling procedure SUM_AGENT_OUTCOME', g_proc_name);
4715 END IF;
4716
4717 SUM_AGENT_OUTCOME;
4718
4719 g_status := 'SUCCESS';
4720 g_table_name := 'BIX_DM_AGENT_OUTCOME_SUM';
4721 insert_log;
4722
4723 /* Procedure collects all the outcomes information for the GROUP data */
4724
4725 IF (g_debug_flag = 'Y') THEN
4726 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' Calling procedure SUM_GROUP_OUTCOME', g_proc_name);
4727 END IF;
4728
4729 SUM_GROUP_OUTCOME;
4730
4731 g_status := 'SUCCESS';
4732 g_table_name := 'BIX_DM_GROUP_OUTCOME_SUM';
4733 insert_log;
4734
4735 /* Cleanup: Delete all from the BIX_DM_INTERFACE table */
4736
4737 --DELETE_IN_CHUNKS('BIX_DM_INTERFACE',NULL,g_delete_count);
4738 g_insert_count := 0;
4739 g_status := 'SUCCESS';
4740 g_table_name := 'BIX_DM_INTERFACE';
4741 insert_log;
4742
4743
4744
4745
4746 g_collect_start_date := g_collect_end_date;
4747
4748 if (l_collect_end_date - g_collect_end_date) > 1 then
4749 g_collect_end_date := g_collect_end_date + 1;
4750 elsif ((l_collect_end_date - g_collect_end_date) > 0) and
4751 ((l_collect_end_date - g_collect_end_date) <= 1) then
4752 g_collect_end_date := l_collect_end_date;
4753 else
4754 exit;
4755 end if;
4756
4757
4758 end loop;
4759
4760 EXCEPTION
4761 WHEN G_DATE_MISMATCH THEN
4762 retcode := -1;
4763 errbuf := 'Collect Start Date cannot be greater than collection end date';
4764
4765 IF (g_debug_flag = 'Y') THEN
4766 write_log('Collect Start Date cannot be greater than collection end date', g_proc_name);
4767 END IF;
4768
4769 WHEN OTHERS THEN
4770 retcode := SQLCODE;
4771 errbuf := SQLERRM;
4772 g_status := 'FAILED';
4773
4774 IF (g_debug_flag = 'Y') THEN
4775 write_log(g_error_mesg, g_proc_name);
4776 END IF;
4777
4778 insert_log;
4779 END COLLECT_CALLS_SUMMARY;
4780
4781 END BIX_DM_AGENT_CALL_SUMMARY_PKG;