[Home] [Help]
PACKAGE BODY: APPS.BIX_CALL_UPDATE_PKG
Source
1 PACKAGE BODY BIX_CALL_UPDATE_PKG AS
2 /*$Header: bixcaupd.plb 120.2 2005/09/09 15:22:28 anasubra noship $ */
3
4 g_request_id NUMBER;
5 g_program_appl_id NUMBER;
6 g_program_id NUMBER;
7 g_user_id NUMBER;
8 g_bix_schema VARCHAR2(30) := 'BIX';
9 g_rows_ins_upd NUMBER;
10 g_commit_chunk_size NUMBER;
11 g_no_of_jobs NUMBER := 0;
12 g_required_workers NUMBER := 0;
13 g_collect_start_date DATE;
14 g_collect_end_date DATE;
15 g_sysdate DATE;
16 g_debug_flag VARCHAR2(1) := 'N';
17
18 g_errbuf VARCHAR2(1000);
19 g_retcode VARCHAR2(10) := 'S';
20
21 MAX_LOOP CONSTANT NUMBER := 180;
22
23 G_TIME_DIM_MISSING EXCEPTION;
24 G_CHILD_PROCESS_ISSUE EXCEPTION;
25 G_OLTP_CLEANUP_ISSUE EXCEPTION;
26
27 TYPE WorkerList is table of NUMBER index by binary_integer;
28 g_worker WorkerList;
29
30 TYPE ProcRec IS RECORD
31 (
32 media_id NUMBER,
33 name VARCHAR2(100),
34 value1 NUMBER,
35 value2 VARCHAR2(10)
36 );
37
38 TYPE ProcTable IS TABLE OF ProcRec;
39
40 l_proc_table ProcTable;
41
42 PROCEDURE write_Log (p_msg IN VARCHAR2
43 ) IS
44 BEGIN
45
46 --IF (g_debug_flag = 'Y') THEN
47 BIS_COLLECTION_UTILITIES.log(p_msg);
48 --BIS_COLLECTION_UTILITIES.log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
49 --p_msg);
50
51 --fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')|| ' : '||p_msg);
52
53 --insert into bixtest
54 --values(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' : '||p_msg);
55 --commit;
56
57 --END IF;
58
59 --g_test:=g_test+1;
60 --insert into bixtest values(to_char(sysdate,'HH:MI:SS')||':'||p_msg);
61 --commit;
62
63 EXCEPTION
64 WHEN OTHERS THEN
65 RAISE;
66 END Write_Log;
67
68 PROCEDURE cleanup_oltp
69 IS
70
71 BEGIN
72
73 --
74 --Close media items
75 --
76 BEGIN
77 g_errbuf := NULL;
78 g_retcode := 'S';
79 CCT_CONCURRENT_PUB.CLOSE_MEDIA_ITEMS(g_errbuf, g_retcode);
80
81 IF g_retcode <> 'S'
82 THEN
83 RAISE G_OLTP_CLEANUP_ISSUE;
84 END IF;
85
86 EXCEPTION
87 WHEN OTHERS THEN
88 write_log('Close Media Items exited with: ' ||g_retcode || ' error buffer is: ' || g_errbuf);
89 RAISE G_OLTP_CLEANUP_ISSUE;
90 END;
91
92 --
93 --Time out media items - interval hardcoded to 24 hours for now
94 --
95 BEGIN
96 g_errbuf := NULL;
97 g_retcode := 'S';
98 CCT_CONCURRENT_PUB.TIMEOUT_MEDIA_ITEMS_RS(g_errbuf, g_retcode,24);
99
100 IF g_retcode <> 'S'
101 THEN
102 RAISE G_OLTP_CLEANUP_ISSUE;
103 END IF;
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 write_log('Timeout Media Items exited with: ' ||g_retcode || ' error buffer is: ' || g_errbuf);
108 RAISE G_OLTP_CLEANUP_ISSUE;
109 END;
110
111 --
112 --IEU Session History Cleanup
113 --
114 --BEGIN
115 --g_errbuf := NULL;
116 --g_retcode := 'S';
117 --IEU_SH_CON_PVT.IEU_SH_END_IDLE_TRANS(g_errbuf, g_retcode);
118 --
119 --IF g_retcode <> 'S'
120 --THEN
121 --RAISE G_OLTP_CLEANUP_ISSUE;
122 --END IF;
123 --
124 --EXCEPTION
125 --WHEN OTHERS THEN
126 --write_log('Timeout Media Items exited with: ' ||g_retcode || ' error buffer is: ' || g_errbuf);
127 --RAISE G_OLTP_CLEANUP_ISSUE;
128 --END;
129
130 END cleanup_oltp;
131
132 PROCEDURE INSERT_DNIS
133 IS
134
135 BEGIN
136
137 INSERT /*+ APPEND */ INTO BIX_DM_DNIS
138 (
139 dnis_id, dnis, last_update_date, last_updated_by, creation_date
140 )
141 SELECT BIX_DM_DNIS_S.NEXTVAL, DNIS, g_sysdate, g_user_id, g_sysdate
142 FROM
143 (
144 SELECT DISTINCT decode(DIRECTION, 'OUTBOUND', 'OUTBOUND', DNIS) DNIS
145 FROM BIX_MEDIAS_FOR_WORKER WORK
146 WHERE NOT EXISTS
147 (SELECT 1
148 FROM BIX_DM_DNIS DNIS
149 WHERE WORK.DNIS = DNIS.DNIS
150 )
151 AND DNIS IS NOT NULL
152 );
153
154 COMMIT;
155
156 EXCEPTION
157 WHEN OTHERS THEN
158 NULL;
159
160 END INSERT_DNIS;
161
162 --Start 002
163 --Commenting out these two procedures as the
164 --values returned by these are not used anywhere in ICI.
165 /*********************
166 PROCEDURE GET_LEAD_AMOUNT ( p_lead_id in number,
167 p_leads_amount out NOCOPY NUMBER,
168 p_currency_code out NOCOPY varchar2
169 ) is
170
171 BEGIN
172
173
174 select budget_amount,currency_code into p_leads_amount, p_currency_code
175 from as_sales_leads where SALES_LEAD_ID = p_lead_id;
176
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 p_leads_amount := 0;
181 p_currency_code := NULL;
182 END;
183 -- Opportunity amount:
184
185 procedure GET_OPPORTUNITY_AMOUNT ( p_opp_id in NUMBER,
186 p_opp_won out NOCOPY NUMBER,
187 p_opp_amount out NOCOPY NUMBER,
188 p_currency_code out NOCOPY varchar2) is
189
190 BEGIN
191
192 select total_amount,currency_code,1 into
193 p_opp_amount, p_currency_code,p_opp_won
194 from
195 as_leads_all a,
196 as_statuses_vl asv
197 where a.lead_id = p_opp_id and
198 a.status = asv.status_code and
199 asv.win_loss_indicator = 'W';
200
201
202 EXCEPTION
203 WHEN OTHERS THEN
204 p_opp_amount := 0;
205 p_currency_code := NULL;
206 p_opp_won := 0;
207 END;
208 **********/
209 --End 002
210 --Commenting out these two procedures as the
211 --values returned by these are not used anywhere in ICI.
212
213 PROCEDURE mark_as_processed (
214 p_media_id NUMBER,
215 p_name VARCHAR2,
216 p_value1 NUMBER,
217 p_value2 VARCHAR2
218 )
219 IS
220
221 BEGIN
222
223 NULL;
224 --INSERT INTO BIX_CALL_PROCESSED_RECS
225 --(media_id, name, value1, value2)
226 --VALUES (p_media_id, p_name, p_value1, p_value2);
227
228 l_proc_table.extend();
229 l_proc_table(l_proc_table.count).media_id := p_media_id;
230 l_proc_table(l_proc_table.count).name := p_name;
231 l_proc_table(l_proc_table.count).value1 := p_value1;
232 l_proc_table(l_proc_table.count).value2 := p_value2;
233
234 --IF g_debug_flag = 'Y'
235 --THEN
236 --write_log('Values in l_proc_table are: ' ||
237 --'Media id: ' ||l_proc_table(l_proc_table.count).media_id ||
238 --'Name : ' ||l_proc_table(l_proc_table.count).name ||
239 --'Value 1 : ' ||l_proc_table(l_proc_table.count).value1 ||
240 --'Value 2 : ' ||l_proc_table(l_proc_table.count).value2);
241 --END IF;
242 --COMMIT;
243
244 END;
245
246 FUNCTION check_if_processed (
247 p_media_id NUMBER,
248 p_name VARCHAR2,
249 p_value1 NUMBER,
250 p_value2 VARCHAR2
251 )
252 RETURN VARCHAR2
253 IS
254
255 l_count NUMBER;
256 l_agent_flag VARCHAR2(1);
257 l_both_flag VARCHAR2(1);
258
259 BEGIN
260
261 l_count := 0;
262
263 IF p_value1 IS NULL AND p_value2 IS NULL
264 THEN
265 RETURN 'Y';
266 END IF;
267
268 IF p_name <> 'BUCKET' AND p_name <> 'AGENT_BUCKET'
269 THEN
270
271 FOR x IN l_proc_table.FIRST .. l_proc_table.LAST
272 LOOP
273 IF l_proc_table(x).media_id = p_media_id
274 AND l_proc_table(x).name = p_name
275 AND l_proc_table(x).value1 = p_value1
276 THEN
277 RETURN 'Y';
278 END IF;
279 END LOOP;
280
281 ELSIF p_name = 'AGENT_BUCKET'
282 THEN
283
284 FOR x IN l_proc_table.FIRST .. l_proc_table.LAST
285 LOOP
286 IF l_proc_table(x).media_id = p_media_id
287 AND l_proc_table(x).name = p_name
288 AND l_proc_table(x).value1 = p_value1
289 AND l_proc_table(x).value2 = p_value2
290 THEN
291 l_both_flag := 'B'; -- this means both agent and bucket matched
292 END IF;
293
294 IF l_proc_table(x).media_id = p_media_id
295 AND l_proc_table(x).name = p_name
296 AND l_proc_table(x).value1 = p_value1
297 --AND l_proc_table(x).value2 = p_value2 -- dont check for bucket here
298 THEN
299 l_agent_flag := 'A'; -- this means only agent matched
300 END IF;
301 END LOOP;
302
303 IF l_both_flag = 'B'
304 THEN
305 RETURN 'B';
306 ELSIF l_agent_flag = 'A'
307 THEN
308 RETURN 'A';
309 END IF;
310
311 ELSE
312
313 --SELECT count(*)
314 --INTO l_count
315 --FROM BIX_CALL_PROCESSED_RECS
316 --WHERE name = p_name
317 --AND value2 = p_value2
318 --AND media_id = p_media_id;
319
320 FOR x IN l_proc_table.FIRST .. l_proc_table.LAST
321 LOOP
322 IF l_proc_table(x).media_id = p_media_id
323 AND l_proc_table(x).name = p_name
324 AND l_proc_table(x).value2 = p_value2
325 THEN
326 RETURN 'Y';
327 END IF;
328 END LOOP;
329
330 END IF;
331
332 --IF l_count > 0
333 --THEN
334 --IF (g_debug_flag = 'Y') THEN
335 --write_log ('Count > 0');
336 --END IF;
337 --RETURN 'Y';
338 --ELSE
339 --IF (g_debug_flag = 'Y') THEN
340 --write_log ('Count = 0');
341 --END IF;
342 --RETURN 'N';
343 --END IF;
344
345 RETURN 'N';
346
347 EXCEPTION
348 WHEN OTHERS
349 THEN
350 --IF (g_debug_flag = 'Y') THEN
351 --write_log('Exception in check_if_processed');
352 --END IF;
353 RETURN 'N';
354
355 END check_if_processed;
356
357 PROCEDURE get_campaign_details (
358 p_media_id IN NUMBER,
359 p_direction IN VARCHAR2,
360 p_source_item_id IN NUMBER,
361 p_source_code IN VARCHAR2,
362 p_campaign_id OUT NOCOPY NUMBER,
363 p_schedule_id OUT NOCOPY NUMBER,
364 p_source_code_id OUT NOCOPY NUMBER,
365 p_dialing_method OUT NOCOPY VARCHAR2
366 )
367 IS
368
369 l_source_code_for VARCHAR2(50);
370
371 BEGIN
372
373 --IF (g_debug_flag = 'Y') THEN
374 --write_log('Called get_campaign_details with source_code ' || p_source_code ||
375 --'media id ' || p_media_id ||
376 --'source item id ' || p_source_item_id ||
377 --'direction ' || p_direction
378 --);
379 --END IF;
380
381
382 --002 , Commenting out the IF...else Block processing as
383 -- outbound is not needed and so no point in processing
384 -- an "IF" statement
385
386 --IF p_direction = 'INBOUND' 002
387 --THEN 002
388
389 p_dialing_method := 'N/A';
390
391 --
392 --For AI calls use the source_code from interactions table
393 --
394 SELECT source_code_id, ARC_SOURCE_CODE_FOR
395 INTO p_source_code_id, l_source_code_for
396 FROM ams_source_codes
397 WHERE source_code = p_source_code;
398
399 if l_source_code_for = 'CAMP' then
400 select campaign_id
401 into p_campaign_id
402 from ams_campaigns_all_b
403 where source_code = p_source_code;
404
405 p_schedule_id := NULL;
406
407 elsif l_source_code_for = 'CSCH'
408 then
409 select schedule_id, campaign_id
410 into p_schedule_id, p_campaign_id
411 from AMS_CAMPAIGN_SCHEDULES_B
412 where source_code = p_source_code;
413
414 else
415 p_campaign_id := NULL;
416 p_schedule_id := NULL;
417 end if;
418
419 --Start 002
420 /******
421 ELSIF p_direction = 'OUTBOUND'
422 THEN
423 --
424 --For AO calls use a different path
425 --
426
427 select c.campaign_id,c.schedule_id, code.source_code_id, d.dialing_method
428 INTO p_campaign_id,p_schedule_id,p_source_code_id,p_dialing_method
429 from iec_g_list_subsets a,
430 ams_act_lists b,
431 AMS_CAMPAIGN_SCHEDULES_B c,
432 ams_list_headers_all d,
433 ams_source_codes code
434 where
435 a.list_header_id = b.list_header_id AND
436 b.list_used_by_id = c.schedule_id AND
437 b.list_act_type = 'TARGET' AND
438 b.list_used_by = 'CSCH' AND
439 b.list_header_id = d.list_header_id AND
440 a.list_subset_id = p_source_item_id AND
441 code.source_code = c.source_code;
442
443 END IF;
444
445 *********/
446 --End 002
447
448 EXCEPTION
449 WHEN OTHERS
450 THEN
451 p_campaign_id := NULL;
452 p_schedule_id := NULL;
453 p_source_code_id := NULL;
454 p_dialing_method := 'N/A';
455
456 END get_campaign_details;
457
458 PROCEDURE get_segment_details (p_media_id IN NUMBER,
459 p_resource_id IN NUMBER,
460 p_milcs_id IN NUMBER,
461 p_final_segment OUT NOCOPY VARCHAR2,
462 p_max_talk_end_date_time OUT NOCOPY DATE
463 )
464 IS
465
466 l_max_milcs_id NUMBER;
467
468 BEGIN
469
470 SELECT max(milcs_id), max(end_date_time)
471 INTO l_max_milcs_id, p_max_talk_end_date_time
472 FROM JTF_IH_MEDIA_ITEM_LC_SEGS SEGS, JTF_IH_MEDIA_ITM_LC_SEG_TYS TYPES
473 WHERE media_id = p_media_id
474 AND resource_id = p_resource_id
475 AND SEGS.milcs_type_id = TYPES.milcs_type_id
476 AND milcs_code = 'WITH_AGENT';
477
478 IF l_max_milcs_id = p_milcs_id
479 THEN
480 p_final_segment := 'Y';
481 ELSE
482 p_final_segment := 'N';
483 END IF;
484
485 EXCEPTION
486 WHEN OTHERS
487 THEN
488 RAISE;
489
490 END get_segment_details;
491
492 PROCEDURE get_media_details (p_media_id IN NUMBER,
493 p_has_agent_segs OUT NOCOPY VARCHAR2,
494 p_earliest_agent OUT NOCOPY NUMBER,
495 p_min_talk_start OUT NOCOPY DATE,
496 p_max_talk_end OUT NOCOPY DATE
497 )
498 IS
499
500 l_agent_segments NUMBER := 0;
501
502 BEGIN
503
504 SELECT min(resource_id)
505 INTO p_earliest_agent
506 FROM JTF_IH_MEDIA_ITEM_LC_SEGS SEGS, JTF_IH_MEDIA_ITM_LC_SEG_TYS TYPES
507 WHERE SEGS.media_id = p_media_id
508 AND SEGS.milcs_type_id = TYPES.milcs_type_id
509 AND TYPES.milcs_code = 'WITH_AGENT'
510 AND SEGS.start_date_time = (select min(start_date_time)
511 FROM JTF_IH_MEDIA_ITEM_LC_SEGS SEGS,
512 JTF_IH_MEDIA_ITM_LC_SEG_TYS TYPES
513 WHERE SEGS.media_id = p_media_id
514 AND SEGS.milcs_type_id = TYPES.milcs_type_id
515 AND TYPES.milcs_code = 'WITH_AGENT'
516 );
517
518 IF p_earliest_agent IS NOT NULL
519 THEN
520 p_has_agent_segs := 'Y';
521 ELSE
522 p_has_agent_segs := 'N';
523 END IF;
524
525 SELECT min(start_date_time), max(end_date_time)
526 INTO p_min_talk_start, p_max_talk_end
527 FROM JTF_IH_MEDIA_ITEM_LC_SEGS SEGS,
528 JTF_IH_MEDIA_ITM_LC_SEG_TYS TYPES
529 WHERE SEGS.media_id = p_media_id
530 AND SEGS.milcs_type_id = TYPES.milcs_type_id
531 AND TYPES.milcs_code = 'WITH_AGENT';
532
533 EXCEPTION
534 WHEN NO_DATA_FOUND
535 THEN
536 p_earliest_agent := NULL;
537 p_has_agent_segs := 'N';
538 p_min_talk_start := NULL;
539 p_max_talk_end := NULL;
540 WHEN OTHERS
541 THEN
542 RAISE;
543
544 END get_media_details;
545
546
547 PROCEDURE truncate_table (p_tle_name in varchar2) is
548
549 l_stmt varchar2(400);
550 BEGIN
551 --IF (g_debug_flag = 'Y') THEN
552 --write_log('Start of the procedure truncate_table at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
553 --
554 --END IF;
555 l_stmt:='truncate table '||g_bix_schema||'.'|| p_tle_name;
556 execute immediate l_stmt;
557
558 --IF (g_debug_flag = 'Y') THEN
559 --write_log('Table ' || p_tle_name || ' has been truncated');
560 --write_log('Finished procedure truncate_table at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
561 --END IF;
562 EXCEPTION
563 WHEN OTHERS THEN
564 --IF (g_debug_flag = 'Y') THEN
565 --write_log('Error in truncate_table : Error : ' || sqlerrm);
566 --END IF;
567 RAISE;
568 END truncate_table;
569
570 PROCEDURE init IS
571
572 l_status VARCHAR2(30);
573 l_industry VARCHAR2(30);
574
575 BEGIN
576
577 --IF (g_debug_flag = 'Y') THEN
578 --write_log('Start of the procedure init at : ' ||
579 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
580 --END IF;
581
582 IF (BIS_COLLECTION_UTILITIES.SETUP('BIX_CALL_DETAILS_F') = FALSE) THEN
583 RAISE_APPLICATION_ERROR(-20000,
584 'BIS_COLLECTION_UTILITIES.setup has failed');
585 END IF;
586
587
588 --IF (g_debug_flag = 'Y') THEN
589 --write_log('Initializing global variables');
590 --END IF;
591
592 g_request_id := FND_GLOBAL.CONC_REQUEST_ID();
593 g_program_appl_id := FND_GLOBAL.PROG_APPL_ID();
594 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
595 g_user_id := FND_GLOBAL.USER_ID();
596 g_sysdate := SYSDATE;
597 g_commit_chunk_size := 1500;
598 g_rows_ins_upd := 0;
599
600
601 IF(FND_INSTALLATION.GET_APP_INFO('BIX', l_status, l_industry, g_bix_schema)) THEN
602 NULL;
603 END IF;
604 --IF (g_debug_flag = 'Y') THEN
605 --write_log('BIX Schema : ' || g_bix_schema);
606 --END IF;
607
608 --SETUP will do this - no need to call this
609 --write_log('Setting the sort and hash area size');
610 execute immediate 'alter session set sort_area_size=524288000';
611 execute immediate 'alter session set hash_area_size=524288000';
612
613 --IF (g_debug_flag = 'Y') THEN
614 --write_log('Finished procedure init at : ' ||
615 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
616 --END IF;
617
618 EXCEPTION
619 WHEN OTHERS THEN
620 --IF (g_debug_flag = 'Y') THEN
621 --write_log('Error in init : Error : ' || sqlerrm);
622 --END IF;
623 RAISE;
624 END init;
625
626 FUNCTION launch_worker(p_worker_no in NUMBER) RETURN NUMBER IS
627
628 l_request_id NUMBER;
629 l_message VARCHAR2(1000);
630
631 test1 varchar2(100);
632 test2 varchar2(100);
633
634 BEGIN
635
636 IF (g_debug_flag = 'Y') THEN
637 write_log('Start of the procedure launch_worker for worker ' ||p_worker_no||' at : ' ||
638 to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
639 END IF;
640
641 -- Submit the parallel concurrent request
642 BEGIN
643
644 l_request_id := FND_REQUEST.SUBMIT_REQUEST('BIX',
645 'BIX_CALL_UPDATE_SUBWORKER',
646 NULL,
647 NULL,
648 FALSE,
649 p_worker_no);
650 /*
651 l_request_id := 123456;
652 worker (test1,test2,p_worker_no);
653 */
654
655
656
657 EXCEPTION
658 WHEN OTHERS THEN
659 l_message:=SQLERRM;
660 --IF (g_debug_flag = 'Y') THEN
661 write_log('Worker exception is ' || l_message);
662 --END IF;
663 END;
664
665 --IF (g_debug_flag = 'Y') THEN
666 write_log('Request ID of the concurrent request launched : ' || to_char(l_request_id));
667 --END IF;
668
669 -- if the submission of the request fails , abort the program
670 IF (l_request_id = 0) THEN
671 rollback;
672 --IF (g_debug_flag = 'Y') THEN
673 write_log('Error in launching child workers');
674 --END IF;
675 RAISE G_CHILD_PROCESS_ISSUE;
676 END IF;
677
678 --IF (g_debug_flag = 'Y') THEN
679 write_log('Finished procedure launch_worker at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
680 --END IF;
681 RETURN l_request_id;
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 --IF (g_debug_flag = 'Y') THEN
686 write_log('Error in launch_worker : Error : ' || sqlerrm);
687 --END IF;
688 RAISE;
689 END LAUNCH_WORKER;
690
691 PROCEDURE register_jobs IS
692
693 --l_start_date_range DATE;
694 --l_end_date_range DATE;
695 l_count NUMBER := 0;
696
697 BEGIN
698 --IF (g_debug_flag = 'Y') THEN
699 --write_log('Start of the procedure register_jobs at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
700 --END IF;
701
702 -- No of jobs to be submitted = No of days for which we need to collect data
703 --SELECT ceil(g_collect_end_date - g_collect_start_date)
704 --INTO l_count
705 --FROM dual;
706
707 g_no_of_jobs := l_count;
708
709 --IF (g_debug_flag = 'Y') THEN
710 --write_log('Number of workers that need to ne instantiated : ' || to_char(l_count));
711 --END IF;
712
713 Delete BIX_WORKER_JOBS WHERE OBJECT_NAME = 'BIX_CALL_DETAILS_F';
714
715 --IF (l_count > 0) THEN
716 --l_start_date_range := g_collect_start_date;
717
718 -- Register a job for each day of the collection date range
719 --FOR i IN 1..l_count
720 --LOOP
721 -- End date range is end of day of l_start_date_range
722 --l_end_date_range := trunc(l_start_date_range) + 86399/86400;
723
724 --IF (l_start_date_range > g_collect_end_date) THEN
725 --EXIT;
726 --END IF;
727
728 --IF (l_end_date_range > g_collect_end_date) THEN
729 --l_end_date_range := g_collect_end_date;
730 --END IF;
731
732 --INSERT INTO BIX_WORKER_JOBS(OBJECT_NAME,
733 --START_DATE_RANGE,
734 --END_DATE_RANGE,
735 --WORKER_NUMBER,
736 --STATUS)
737 --VALUES (
738 --'BIX_CALL_DETAILS_F',
739 --l_start_date_range,
740 --l_end_date_range,
741 --l_count,
742 --'UNASSIGNED');
743
744 --l_start_date_range := l_end_date_range + 1/86400;
745 --END LOOP;
746 --END IF;
747
748 --
749 --Insert the medias to be processed into the worker table
750 --
751
752 INSERT /* APPEND */
753 INTO bix_medias_for_worker
754 ( MEDIA_ID
755 ,DIRECTION
756 ,START_DATE_TIME
757 ,END_DATE_TIME
758 ,SOURCE_ITEM_ID
759 ,MEDIA_ITEM_TYPE
760 ,MEDIA_ABANDON_FLAG
761 ,MEDIA_TRANSFERRED_FLAG
762 ,ANI
763 ,DNIS
764 ,SERVER_GROUP_ID
765 ,CLASSIFICATION
766 ,WORKER_NUMBER
767 ,STATUS
768 )
769 SELECT
770 MEDIA_ID
771 --, nvl(DIRECTION, 'INBOUND')
772 ,decode(DIRECTION,NULL,'INBOUND','N/A','INBOUND',DIRECTION)
773 , START_DATE_TIME
774 , END_DATE_TIME
775 , SOURCE_ITEM_ID
776 , decode(MEDIA_ITEM_TYPE,'TELE_WEB','TELE_WEB_CALLBACK',MEDIA_ITEM_TYPE)
777 , MEDIA_ABANDON_FLAG
778 , MEDIA_TRANSFERRED_FLAG
779 , ANI
780 ,decode(DIRECTION,'OUTBOUND','OUTBOUND', DNIS) DNIS
781 , SERVER_GROUP_ID
782 , CLASSIFICATION
783 , 1
784 , 'NO WORKER DEFINED'
785 --med.media_id, 1, 'NO WORKER DEFINED'
786 FROM JTF_IH_MEDIA_ITEMS MED
787 WHERE med.last_update_date BETWEEN g_collect_start_date
788 AND g_collect_end_date
789 AND med.active = 'N'
790 AND (
791 med.direction IN ('INBOUND','OUTBOUND') OR
792 med.media_item_type = 'UNSOLICITED' -- if unsolicited it may have NULL direction
793 )
794 AND
795 (
796 med.media_item_type = 'TELE_INB' or
797 med.media_item_type = 'TELE_DIRECT' or
798 --002 commenting out "Telephone" media type as it is meant for
799 --"Outbound Telphony" which has been obsoleted.
800 --med.media_item_type = 'TELEPHONE' or
801 med.media_item_type = 'CALL' or
802 med.media_item_type = 'TELE_MANUAL' or
803 med.media_item_type = 'TELE_WEB' or
804 med.media_item_type = 'TELE_WEB_CALLBACK' or
805 med.media_item_type = 'UNSOLICITED'
806 );
807
808 g_no_of_jobs := SQL%ROWCOUNT;
809 write_log('SQLrowcount = ' || g_no_of_jobs);
810
811 COMMIT;
812
813 DBMS_STATS.gather_table_stats(ownname => g_bix_schema,
814 tabName => 'bix_medias_for_worker',
815 cascade => TRUE,
816 degree => bis_common_parameters.get_degree_of_parallelism,
817 estimate_percent => 10,
818 granularity => 'GLOBAL');
819
820 --
821 --Insert the missing DNIS values
822 --
823 insert_dnis;
824
825 --execute immediate 'analyze index bix.bix_medias_for_worker_n1 compute statistics';
826
827 /*
828 SELECT max(ranking)
829 INTO g_no_of_jobs
830 FROM bix_medias_for_worker;
831 */
832
833 --
834 --Set the worker number correctly
835 --
836 IF g_no_of_jobs > 0
837 THEN
838
839 --dbms_output.put_line('outside loop');
840 --dbms_output.put_line('g_no_of_jobs='||g_no_of_jobs);
841 --dbms_output.put_line('g_required_workers='||g_required_workers);
842
843 FOR x IN 1 .. g_required_workers LOOP
844
845 --dbms_output.put_line('Inside loop with x = ' || x );
846
847 --UPDATE bix_medias_for_worker
848 UPDATE bix_medias_for_worker
849 SET worker_number = x,
850 status = 'UNASSIGNED'
851 WHERE worker_number = 1
852 AND status = 'NO WORKER DEFINED'
853 --AND rownum < 10
854 AND rownum <= ceil(g_no_of_jobs/g_required_workers)
855 --AND ranking > ceil(g_no_of_jobs/g_required_workers)*(x-1)
856 --AND ranking <= ceil(g_no_of_jobs/g_required_workers)*x
857 ;
858
859 IF SQL%ROWCOUNT > 0
860 THEN
861
862 INSERT INTO BIX_WORKER_JOBS
863 (
864 OBJECT_NAME,
865 START_DATE_RANGE,
866 END_DATE_RANGE,
867 WORKER_NUMBER,
868 STATUS
869 )
870 VALUES
871 (
872 'BIX_CALL_DETAILS_F',
873 sysdate,
874 sysdate,
875 x,
876 'UNASSIGNED'
877 );
878
879 END IF;
880
881 END LOOP;
882
883 COMMIT;
884
885 --insert into bixtest
886 --select 'worker number is ' || worker_number
887 --from bix_medias_for_worker;
888 --commit;
889
890 END IF;
891
892 --IF (g_debug_flag = 'Y') THEN
893 --write_log('Finished procedure register_jobs at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
894 --END IF;
895
896 EXCEPTION
897 WHEN OTHERS THEN
898 --IF (g_debug_flag = 'Y') THEN
899 --write_log('Error in register_jobs : Error : ' || sqlerrm);
900 --END IF;
901 RAISE;
902 END REGISTER_JOBS;
903
904 PROCEDURE clean_up IS
905
906 l_total_rows_deleted NUMBER := 0;
907 l_rows_deleted NUMBER := 0;
908
909 BEGIN
910 --IF (g_debug_flag = 'Y') THEN
911 --write_log('Start of the procedure clean_up at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
912 --END IF;
913
914 rollback;
915
916 --IF (g_debug_flag = 'Y') THEN
917 --write_log('Deleting data from bix_call_details_f');
918 --END IF;
919
920 -- Delete all the rows inserted from subworkers
921 IF (g_worker.COUNT > 0) THEN
922 FOR i IN g_worker.FIRST .. g_worker.LAST
923 LOOP
924 LOOP
925 DELETE BIX_CALL_DETAILS_F
926 WHERE request_id = g_worker(i)
927 AND last_update_date >= g_sysdate
928 AND rownum <= g_commit_chunk_size ;
929
930 l_rows_deleted := SQL%ROWCOUNT;
931 l_total_rows_deleted := l_total_rows_deleted + l_rows_deleted;
932
933 COMMIT;
934
935 IF (l_rows_deleted < g_commit_chunk_size) THEN
936 EXIT;
937 END IF;
938 END LOOP;
939 END LOOP;
940 END IF;
941
942 -- Deleting all rows inserted by this main program
943 LOOP
944
945 DELETE BIX_CALL_DETAILS_F
946 WHERE request_id = g_request_id
947 AND last_update_date >= g_sysdate
948 AND rownum <= g_commit_chunk_size ;
949
950 l_rows_deleted := SQL%ROWCOUNT;
951 l_total_rows_deleted := l_total_rows_deleted + l_rows_deleted;
952
953 COMMIT;
954
955 IF (l_rows_deleted < g_commit_chunk_size) THEN
956 EXIT;
957 END IF;
958 END LOOP;
959
960 --IF (g_debug_flag = 'Y') THEN
961 --write_log('Number of rows deleted from BIX_CALL_DETAILS_F : ' || to_char(l_total_rows_deleted));
962 --END IF;
963
964 truncate_table('BIX_CALL_DETAILS_STG');
965 --truncate_table('BIX_CALL_PROCESSED_RECS');
966 --IF (g_debug_flag = 'Y') THEN
967 --write_log('Done truncating bix_call_details_stg
968 --and bix_call_processed_recs');
969 --
970 --write_log('Finished procedure clean_up at ' ||
971 --to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
972 --END IF;
973
974 EXCEPTION
975 WHEN OTHERS THEN
976 --IF (g_debug_flag = 'Y') THEN
977 --write_log('Error in cleaning up the tables : Error : ' || sqlerrm);
978 --END IF;
979 RAISE;
980 END CLEAN_UP;
981
982 PROCEDURE insert_half_hour_rows (
983 p_collect_start_date IN DATE,
984 p_collect_end_date IN DATE,
985 p_worker_no IN NUMBER
986 )
987 IS
988
989 CURSOR get_call_info IS
990 SELECT
991 MEDIA.media_id MEDIA_ID,
992 MEDIA.server_group_id SERVER_GROUP_ID,
993 MEDIA.CLASSIFICATION CLASSIFICATION,
994 MEDIA.dnis DNIS,
995 MEDIA.direction DIRECTION,
996 MEDIA.media_item_type MEDIA_ITEM_TYPE,
997 MEDIA.media_start_time MEDIA_START_TIME,
998 MEDIA.media_end_time MEDIA_END_TIME,
999 MEDIA.abandon_flag ABANDON_FLAG ,
1000 MEDIA.transfer_flag TRANSFER_FLAG,
1001 MEDIA.source_item_id SOURCE_ITEM_ID,
1002 --MEDIA.MILCS_ID MILCS_ID,
1003 MEDIA.resource_id RESOURCE_ID,
1004 --MEDIA.segment_type SEGMENT_TYPE,
1005 --MEDIA.segment_start_time SEGMENT_START_TIME,
1006 --MEDIA.segment_end_time SEGMENT_END_TIME,
1007 MEDIA.MAX_AGENT_TALK_END MAX_TALK_END_TIME,
1008 MEDIA.FIRST_AGENT FIRST_AGENT,
1009 MEDIA.CALL_TALK_START CALL_TALK_START,
1010 MEDIA.CALL_TALK_END CALL_TALK_END,
1011 INTACT.INTERACTION_ID INTERACTION_ID,
1012 INTACT.OUTCOME_ID OUTCOME_ID,
1013 INTACT.RESULT_ID RESULT_ID,
1014 INTACT.REASON_ID REASON_ID,
1015 first_value(INTACT.PARTY_ID)
1016 over(partition by nvl(intact.media_id,media.media_id)
1017 order by intact.int_end_time DESC NULLS LAST
1018 ) PARTY_ID,
1019 first_value(INTACT.SOURCE_CODE)
1020 over(partition by nvl(intact.media_id,media.media_id)
1021 order by intact.int_end_time DESC NULLS LAST
1022 ) SOURCE_CODE,
1023 INTACT.INT_START_TIME INT_START_TIME,
1024 INTACT.INT_END_TIME INT_END_TIME,
1025 INTACT.MAX_AGENT_INT_END MAX_INT_END_TIME,
1026 INTACT.ACTIVITY_ID ACTIVITY_ID,
1027 INTACT.ACT_START_TIME ACT_START_TIME,
1028 INTACT.ACTION_ID ACTION_ID,
1029 INTACT.ACTION_ITEM_ID ACTION_ITEM_ID,
1030 INTACT.DOC_REF DOC_REF,
1031 INTACT.DOC_ID DOC_ID,
1032 MEDIA.ROUTE_MILCS_ID,
1033 MEDIA.ROUTE_SEGS_START_TIME,
1034 MEDIA.ROUTE_SEGS_END_TIME,
1035 MEDIA.IVR_MILCS_ID,
1036 MEDIA.IVR_SEGS_START_TIME,
1037 MEDIA.IVR_SEGS_END_TIME,
1038 MEDIA.FIRST_QUEUE,
1039 MEDIA.LAST_QUEUE,
1040 MEDIA.QUEUE_MILCS_ID,
1041 MEDIA.QUEUE_SEGS_START_TIME,
1042 MEDIA.QUEUE_SEGS_END_TIME,
1043 MEDIA.AGENT_MILCS_ID,
1044 MEDIA.AGENT_SEGS_START_TIME,
1045 MEDIA.AGENT_SEGS_END_TIME
1046 FROM
1047 (
1048 SELECT /*+ use_nl(MED,IVR_SEGS,ROUTE_SEGS,QUEUE_SEGS,AGENT_SEGS) */
1049 MED.media_id MEDIA_ID,
1050 MED.server_group_id SERVER_GROUP_ID,
1051 MED.CLASSIFICATION CLASSIFICATION,
1052 MED.dnis DNIS,
1053 MED.direction DIRECTION,
1054 MED.media_item_type MEDIA_ITEM_TYPE,
1055 MED.start_date_time MEDIA_START_TIME,
1056 MED.end_date_time MEDIA_END_TIME,
1057 MED.media_abandon_flag ABANDON_FLAG ,
1058 MED.MEDIA_TRANSFERRED_FLAG TRANSFER_FLAG,
1059 MED.source_item_id SOURCE_ITEM_ID,
1060 --SEGS.MILCS_ID MILCS_ID,
1061 ROUTE_SEGS.MILCS_ID ROUTE_MILCS_ID,
1062 ROUTE_SEGS.START_DATE_TIME ROUTE_SEGS_START_TIME,
1063 ROUTE_SEGS.END_DATE_TIME ROUTE_SEGS_END_TIME,
1064 IVR_SEGS.MILCS_ID IVR_MILCS_ID,
1065 IVR_SEGS.START_DATE_TIME IVR_SEGS_START_TIME,
1066 IVR_SEGS.END_DATE_TIME IVR_SEGS_END_TIME,
1067 FIRST_VALUE(QUEUE_SEGS.MILCS_ID)
1068 OVER(PARTITION BY QUEUE_SEGS.MEDIA_ID
1069 ORDER BY QUEUE_SEGS.START_DATE_TIME ASC NULLS LAST
1070 ) FIRST_QUEUE,
1071 FIRST_VALUE(QUEUE_SEGS.MILCS_ID)
1072 OVER(PARTITION BY QUEUE_SEGS.MEDIA_ID
1073 ORDER BY QUEUE_SEGS.START_DATE_TIME DESC NULLS LAST
1074 ) LAST_QUEUE,
1075 QUEUE_SEGS.MILCS_ID QUEUE_MILCS_ID,
1076 QUEUE_SEGS.START_DATE_TIME QUEUE_SEGS_START_TIME,
1077 QUEUE_SEGS.END_DATE_TIME QUEUE_SEGS_END_TIME,
1078 AGENT_SEGS.MILCS_ID AGENT_MILCS_ID,
1079 AGENT_SEGS.START_DATE_TIME AGENT_SEGS_START_TIME,
1080 AGENT_SEGS.END_DATE_TIME AGENT_SEGS_END_TIME,
1081 AGENT_SEGS.resource_id RESOURCE_ID,
1082 --SEGTYPES.milcs_code SEGMENT_TYPE,
1083 --SEGS.start_date_time SEGMENT_START_TIME,
1084 --SEGS.end_date_time SEGMENT_END_TIME,
1085 --
1086 --use decode to make sure segment id of 5 (with_agent) comes first
1087 --
1088 FIRST_VALUE(AGENT_SEGS.RESOURCE_ID)
1089 OVER(PARTITION BY AGENT_SEGS.MEDIA_ID
1090 ORDER BY decode(AGENT_SEGS.MILCS_TYPE_ID,5,1000,AGENT_SEGS.MILCS_TYPE_ID) DESC NULLS LAST,
1091 AGENT_SEGS.START_DATE_TIME
1092 ) FIRST_AGENT,
1093 FIRST_VALUE(AGENT_SEGS.START_DATE_TIME)
1094 OVER(PARTITION BY AGENT_SEGS.MEDIA_ID
1095 ORDER BY decode(AGENT_SEGS.MILCS_TYPE_ID,5,1000,AGENT_SEGS.MILCS_TYPE_ID) DESC NULLS LAST,
1096 AGENT_SEGS.START_DATE_TIME
1097 ) CALL_TALK_START,
1098 FIRST_VALUE(AGENT_SEGS.END_DATE_TIME)
1099 OVER(PARTITION BY AGENT_SEGS.MEDIA_ID
1100 ORDER BY decode(AGENT_SEGS.MILCS_TYPE_ID,5,1000,AGENT_SEGS.MILCS_TYPE_ID) DESC NULLS LAST,
1101 AGENT_SEGS.END_DATE_TIME DESC NULLS LAST
1102 ) CALL_TALK_END,
1103 FIRST_VALUE(AGENT_SEGS.END_DATE_TIME)
1104 OVER(PARTITION BY AGENT_SEGS.MEDIA_ID, AGENT_SEGS.RESOURCE_ID
1105 ORDER BY decode(AGENT_SEGS.MILCS_TYPE_ID,5,1000,AGENT_SEGS.MILCS_TYPE_ID) DESC NULLS LAST,
1106 AGENT_SEGS.END_DATE_TIME DESC NULLS LAST
1107 ) MAX_AGENT_TALK_END
1108 --FROM (JTF_IH_MEDIA_ITEMS MED LEFT OUTER JOIN
1109 --JTF_IH_MEDIA_ITEM_LC_SEGS AGENT_SEGS
1110 --ON MED.media_id = AGENT_SEGS.media_id
1111 --)
1112 --LEFT OUTER JOIN
1113 --JTF_IH_MEDIA_ITM_LC_SEG_TYS SEGTYPES
1114 --ON SEGS.MILCS_TYPE_ID = SEGTYPES.MILCS_TYPE_ID
1115 FROM
1116 bix_medias_for_worker MED,
1117 (select *
1118 from jtf_ih_media_item_lc_segs
1119 where milcs_type_id = 1) IVR_SEGS,
1120 (select *
1121 from jtf_ih_media_item_lc_segs
1122 where milcs_type_id = 4) ROUTE_SEGS,
1123 (select *
1124 from jtf_ih_media_item_lc_segs
1125 where milcs_type_id = 3) QUEUE_SEGS,
1126 (select *
1127 from jtf_ih_media_item_lc_segs
1128 where milcs_type_id = 5) AGENT_SEGS
1129 WHERE MED.worker_number = p_worker_no
1130 AND MED.status = 'IN PROCESS'
1131 --WHERE MED.last_update_date BETWEEN g_collect_start_date
1132 -- AND g_collect_end_date
1133 AND med.media_id = ROUTE_SEGS.media_id (+)
1134 and med.media_id = IVR_SEGS.media_id (+)
1135 and med.media_id = QUEUE_SEGS.media_id (+)
1136 and med.media_id = AGENT_SEGS.media_id (+)
1137 --AND MED.active = 'N'
1138 --AND MED.direction IN ('INBOUND','OUTBOUND')
1139 --AND
1140 --(
1141 --MED.media_item_type = 'TELE_INB' or
1142 --MED.media_item_type = 'TELE_DIRECT' or
1143 --MED.media_item_type = 'TELEPHONE' or
1144 --MED.media_item_type = 'CALL' or
1145 --MED.media_item_type = 'TELE_MANUAL' or
1146 --MED.media_item_type = 'TELE_WEB'
1147 --)
1148 ) MEDIA LEFT OUTER JOIN
1149 (
1150 select distinct INTERACTION_ID,
1151 MEDIA_ID,
1152 RESOURCE_ID,
1153 OUTCOME_ID,
1154 RESULT_ID,
1155 REASON_ID,
1156 INT_START_TIME,
1157 INT_END_TIME,
1158 ACTIVITY_ID,
1159 ACT_START_TIME,
1160 ACTION_ID,
1161 ACTION_ITEM_ID,
1162 DOC_REF,
1163 DOC_ID,
1164 PARTY_ID,
1165 SOURCE_CODE,
1166 MAX_AGENT_INT_END
1167 from (
1168 SELECT /*+ FIRST_ROWS */
1169 INT.INTERACTION_ID INTERACTION_ID,
1170 --MED.MEDIA_ID MEDIA_ID,
1171 WORK.MEDIA_ID MEDIA_ID,
1172 INT.RESOURCE_ID RESOURCE_ID,
1173 INT.OUTCOME_ID OUTCOME_ID,
1174 INT.RESULT_ID RESULT_ID,
1175 INT.REASON_ID REASON_ID,
1176 INT.START_DATE_TIME INT_START_TIME,
1177 INT.END_DATE_TIME INT_END_TIME,
1178 ACT.ACTIVITY_ID ACTIVITY_ID,
1179 ACT.START_DATE_TIME ACT_START_TIME,
1180 ACT.ACTION_ID ACTION_ID,
1181 ACT.ACTION_ITEM_ID ACTION_ITEM_ID,
1182 ACT.DOC_REF DOC_REF,
1183 ACT.DOC_ID DOC_ID,
1184 INT.PARTY_ID PARTY_ID,
1185 INT.SOURCE_CODE SOURCE_CODE,
1186 first_value(INT.END_DATE_TIME)
1187 over(partition by
1188 --med.media_id,
1189 work.media_id,
1190 int.resource_id
1191 order by int.end_date_time DESC NULLS LAST
1192 ) MAX_AGENT_INT_END
1193 FROM
1194 --JTF_IH_MEDIA_ITEMS MED,
1195 BIX_MEDIAS_FOR_WORKER WORK,
1196 JTF_IH_INTERACTIONS INT LEFT OUTER JOIN JTF_IH_ACTIVITIES ACT
1197 ON INT.interaction_id = ACT.interaction_id
1198 --WHERE MED.last_update_date BETWEEN p_collect_start_date
1199 --AND p_collect_end_date
1200 --AND INT.start_date_time BETWEEN p_collect_start_date-1
1201 --AND p_collect_end_date+1
1202 --AND MED.active = 'N'
1203 --AND MED.direction IN ('INBOUND','OUTBOUND')
1204 --AND MED.media_id = int.productive_time_amount
1205 WHERE int.productive_time_amount = work.media_id
1206 AND work.status = 'IN PROCESS'
1207 AND work.worker_number = p_worker_no
1208 --AND
1209 --(
1210 --MED.media_item_type = 'TELE_INB' or
1211 --MED.media_item_type = 'TELE_DIRECT' or
1212 --MED.media_item_type = 'TELEPHONE' or
1213 --MED.media_item_type = 'CALL' or
1214 --MED.media_item_type = 'TELE_MANUAL' or
1215 --MED.media_item_type = 'TELE_WEB'
1216 --)
1217 UNION ALL
1218 SELECT /*+ FIRST_ROWS */
1219 INT.INTERACTION_ID INTERACTION_ID,
1220 ACT.MEDIA_ID MEDIA_ID,
1221 INT.RESOURCE_ID RESOURCE_ID,
1222 INT.OUTCOME_ID OUTCOME_ID,
1223 INT.RESULT_ID RESULT_ID,
1224 INT.REASON_ID REASON_ID,
1225 INT.START_DATE_TIME INT_START_TIME,
1226 INT.END_DATE_TIME INT_END_TIME,
1227 ACT.ACTIVITY_ID ACTIVITY_ID,
1228 ACT.START_DATE_TIME ACT_START_TIME,
1229 ACT.ACTION_ID ACTION_ID,
1230 ACT.ACTION_ITEM_ID ACTION_ITEM_ID,
1231 ACT.DOC_REF DOC_REF,
1232 ACT.DOC_ID DOC_ID,
1233 INT.PARTY_ID PARTY_ID,
1234 INT.SOURCE_CODE SOURCE_CODE ,
1235 first_value(INT.END_DATE_TIME)
1236 over(partition by act.media_id,int.resource_id
1237 order by int.end_date_time DESC NULLS LAST
1238 ) MAX_AGENT_INT_END
1239 FROM
1240 --JTF_IH_MEDIA_ITEMS MED,
1241 BIX_MEDIAS_FOR_WORKER WORK,
1242 JTF_IH_INTERACTIONS INT, JTF_IH_ACTIVITIES ACT
1243 --WHERE MED.last_update_date BETWEEN p_collect_start_date
1244 --AND p_collect_end_date
1245 --AND MED.active = 'N'
1246 WHERE INT.interaction_id = ACT.interaction_id
1247 AND ACT.media_id = WORK.media_id
1248 AND work.status = 'IN PROCESS'
1249 AND work.worker_number = p_worker_no
1250 --AND MED.direction IN ('INBOUND','OUTBOUND')
1251 --AND MED.media_id = ACT.media_id
1252 --AND
1253 --(
1254 --MED.media_item_type = 'TELE_INB' or
1255 --MED.media_item_type = 'TELE_DIRECT' or
1256 --MED.media_item_type = 'TELEPHONE' or
1257 --MED.media_item_type = 'CALL' or
1258 --MED.media_item_type = 'TELE_MANUAL' or
1259 --MED.media_item_type = 'TELE_WEB'
1260 --)
1261 )
1262 ) INTACT
1263 ON (MEDIA.MEDIA_ID = INTACT.MEDIA_ID
1264 AND INTACT.RESOURCE_ID = decode(media.agent_milcs_id,NULL,INTACT.resource_id,
1265 MEDIA.resource_id)
1266 )
1267 GROUP BY
1268 MEDIA.media_id,
1269 MEDIA.server_group_id,
1270 MEDIA.CLASSIFICATION,
1271 MEDIA.dnis,
1272 MEDIA.direction,
1273 MEDIA.media_item_type,
1274 MEDIA.media_start_time,
1275 MEDIA.media_end_time,
1276 MEDIA.abandon_flag,
1277 MEDIA.transfer_flag,
1278 MEDIA.source_item_id,
1279 --MEDIA.MILCS_ID,
1280 MEDIA.resource_id,
1281 --MEDIA.segment_type,
1282 --MEDIA.segment_start_time,
1283 --MEDIA.segment_end_time,
1284 INTACT.INTERACTION_ID,
1285 INTACT.OUTCOME_ID,
1286 INTACT.RESULT_ID,
1287 INTACT.REASON_ID,
1288 INTACT.INT_START_TIME,
1289 INTACT.INT_END_TIME,
1290 INTACT.ACTIVITY_ID,
1291 INTACT.ACT_START_TIME,
1292 INTACT.ACTION_ID,
1293 INTACT.ACTION_ITEM_ID,
1294 INTACT.DOC_REF,
1295 INTACT.DOC_ID,
1296 INTACT.MEDIA_ID,
1297 INTACT.RESOURCE_ID,
1298 MEDIA.MAX_AGENT_TALK_END,
1299 MEDIA.FIRST_AGENT,
1300 MEDIA.CALL_TALK_START,
1301 MEDIA.CALL_TALK_END,
1302 INTACT.MAX_AGENT_INT_END,
1303 INTACT.PARTY_ID,
1304 INTACT.SOURCE_CODE,
1305 MEDIA.ROUTE_MILCS_ID,
1306 MEDIA.ROUTE_SEGS_START_TIME,
1307 MEDIA.ROUTE_SEGS_END_TIME,
1308 MEDIA.IVR_MILCS_ID,
1309 MEDIA.IVR_SEGS_START_TIME,
1310 MEDIA.IVR_SEGS_END_TIME,
1311 MEDIA.FIRST_QUEUE,
1312 MEDIA.LAST_QUEUE,
1313 MEDIA.QUEUE_MILCS_ID,
1314 MEDIA.QUEUE_SEGS_START_TIME,
1315 MEDIA.QUEUE_SEGS_END_TIME,
1316 MEDIA.AGENT_MILCS_ID,
1317 MEDIA.AGENT_SEGS_START_TIME,
1318 MEDIA.AGENT_SEGS_END_TIME
1319 ORDER BY MEDIA.media_id,
1320 --MEDIA.MILCS_ID,
1321 --MEDIA.media_start_time, MEDIA.resource_id, MEDIA.SEGMENT_TYPE,
1322 INTACT.INTERACTION_ID, INTACT.ACTIVITY_ID
1323 ;
1324
1325 --
1326 --Source Table Datatype Declarations
1327 --
1328
1329 TYPE L_MEDIA_ID_t IS TABLE OF jtf_ih_media_items.media_id%TYPE INDEX BY BINARY_INTEGER;
1330 TYPE L_MEDIA_ITEM_TYPE_t IS TABLE OF jtf_ih_media_items.media_item_type%TYPE INDEX BY BINARY_INTEGER;
1331 TYPE L_SERVER_GROUP_ID_t IS TABLE OF jtf_ih_media_items.SERVER_GROUP_ID%TYPE INDEX BY BINARY_INTEGER;
1332 TYPE L_CLASSIFICATION_VALUE_t IS TABLE OF jtf_ih_media_items.CLASSIFICATION%TYPE INDEX BY BINARY_INTEGER;
1333 TYPE L_DNIS_NAME_t IS TABLE OF jtf_ih_media_items.DNIS%TYPE INDEX BY BINARY_INTEGER;
1334 TYPE L_DIRECTION_t IS TABLE OF jtf_ih_media_items.DIRECTION%TYPE INDEX BY BINARY_INTEGER;
1335 TYPE L_MEDIA_START_TIME_t IS TABLE OF jtf_ih_media_items.START_DATE_TIME%TYPE INDEX BY BINARY_INTEGER;
1336 TYPE L_MEDIA_END_TIME_t IS TABLE OF jtf_ih_media_items.END_DATE_TIME%TYPE INDEX BY BINARY_INTEGER;
1337 TYPE L_ABANDON_FLAG_t IS TABLE OF jtf_ih_media_items.MEDIA_ABANDON_FLAG%TYPE INDEX BY BINARY_INTEGER;
1338 TYPE L_TRANSFER_FLAG_t IS TABLE OF jtf_ih_media_items.MEDIA_TRANSFERRED_FLAG%TYPE INDEX BY BINARY_INTEGER;
1339 TYPE L_SOURCE_ITEM_ID_t IS TABLE OF jtf_ih_media_items.SOURCE_ITEM_ID%TYPE INDEX BY BINARY_INTEGER;
1340 TYPE L_MILCS_ID_t IS TABLE OF jtf_ih_media_item_lc_segs.MILCS_ID%TYPE INDEX BY BINARY_INTEGER;
1341 TYPE L_RESOURCE_ID_t IS TABLE OF jtf_ih_media_item_lc_segs.RESOURCE_ID%TYPE INDEX BY BINARY_INTEGER;
1342 TYPE L_SEGMENT_TYPE_t IS TABLE OF jtf_ih_media_itm_lc_seg_tys.MILCS_CODE%TYPE INDEX BY BINARY_INTEGER;
1343 TYPE L_SEGMENT_START_TIME_t IS TABLE OF jtf_ih_media_item_lc_segs.START_DATE_TIME%TYPE INDEX BY BINARY_INTEGER;
1344 TYPE L_SEGMENT_END_TIME_t IS TABLE OF jtf_ih_media_item_lc_segs.END_DATE_TIME%TYPE INDEX BY BINARY_INTEGER;
1345 --
1346 --Target Table Datatype Declarations
1347 --
1348 TYPE L_TIME_ID_T IS TABLE OF BIX_CALL_DETAILS_F.TIME_ID%TYPE INDEX BY BINARY_INTEGER;
1349 TYPE L_PERIOD_TYPE_ID_T IS TABLE OF BIX_CALL_DETAILS_F.PERIOD_TYPE_ID%TYPE INDEX BY BINARY_INTEGER;
1350 TYPE L_PERIOD_START_DATE_t IS TABLE OF BIX_CALL_DETAILS_F.PERIOD_START_DATE%TYPE INDEX BY BINARY_INTEGER;
1351 TYPE L_PERIOD_START_TIME_t IS TABLE OF BIX_CALL_DETAILS_F.PERIOD_START_TIME%TYPE INDEX BY BINARY_INTEGER;
1352 TYPE L_DAY_OF_WEEK_t IS TABLE OF BIX_CALL_DETAILS_F.DAY_OF_WEEK%TYPE INDEX BY BINARY_INTEGER;
1353 TYPE L_PARTY_ID_t IS TABLE OF BIX_CALL_DETAILS_F.PARTY_ID%TYPE INDEX BY BINARY_INTEGER;
1354 TYPE L_CAMPAIGN_ID_t IS TABLE OF BIX_CALL_DETAILS_F.CAMPAIGN_ID%TYPE INDEX BY BINARY_INTEGER;
1355 TYPE L_SCHEDULE_ID_t IS TABLE OF BIX_CALL_DETAILS_F.SCHEDULE_ID%TYPE INDEX BY BINARY_INTEGER;
1356 TYPE L_SOURCE_CODE_ID_t IS TABLE OF BIX_CALL_DETAILS_F.SOURCE_CODE_ID%TYPE INDEX BY BINARY_INTEGER;
1357 TYPE L_DIALING_METHOD_t IS TABLE OF BIX_CALL_DETAILS_F.DIALING_METHOD%TYPE INDEX BY BINARY_INTEGER;
1358 TYPE L_OUTCOME_ID_t IS TABLE OF BIX_CALL_DETAILS_F.OUTCOME_ID%TYPE INDEX BY BINARY_INTEGER;
1359 TYPE L_RESULT_ID_t IS TABLE OF BIX_CALL_DETAILS_F.RESULT_ID%TYPE INDEX BY BINARY_INTEGER;
1360 TYPE L_REASON_ID_t IS TABLE OF BIX_CALL_DETAILS_F.REASON_ID%TYPE INDEX BY BINARY_INTEGER;
1361 TYPE L_PARTITION_KEY_t IS TABLE OF BIX_CALL_DETAILS_F.PARTITION_KEY%TYPE INDEX BY BINARY_INTEGER;
1362 TYPE L_CALL_CALLS_off_TOTAL_t IS TABLE OF BIX_CALL_DETAILS_F.CALL_CALLS_OFFERED_TOTAL%TYPE INDEX BY BINARY_INTEGER;
1363 TYPE L_CALL_CALLS_off_ABOVE_TH_t IS TABLE OF BIX_CALL_DETAILS_F.CALL_CALLS_OFFERED_ABOVE_TH%TYPE INDEX BY BINARY_INTEGER;
1364 TYPE L_CALL_CALLS_aband_t IS TABLE OF BIX_CALL_DETAILS_F.CALL_CALLS_ABANDONED%TYPE INDEX BY BINARY_INTEGER;
1365 TYPE L_CALL_CALLS_aband_us_t IS TABLE OF BIX_CALL_DETAILS_F.CALL_CALLS_ABANDONED_US%TYPE INDEX BY BINARY_INTEGER;
1366 TYPE L_CALL_CALLS_TRANSFERRED_t IS TABLE OF BIX_CALL_DETAILS_F.CALL_CALLS_TRANSFERRED%TYPE INDEX BY BINARY_INTEGER;
1367 TYPE L_CALL_IVR_TIME_t IS TABLE OF BIX_CALL_DETAILS_F.CALL_IVR_TIME%TYPE INDEX BY BINARY_INTEGER;
1368 TYPE L_CALL_ROUTE_TIME_t IS TABLE OF BIX_CALL_DETAILS_F.CALL_ROUTE_TIME%TYPE INDEX BY BINARY_INTEGER;
1369 TYPE l_call_queue_time_t IS TABLE OF BIX_CALL_DETAILS_F.call_queue_time%TYPE INDEX BY BINARY_INTEGER;
1370 TYPE L_CALL_TALK_TIME_t IS TABLE OF BIX_CALL_DETAILS_F.CALL_TALK_TIME%TYPE INDEX BY BINARY_INTEGER;
1371 TYPE L_AGENT_TALK_TIME_NAC_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_TALK_TIME_NAC%TYPE INDEX BY BINARY_INTEGER;
1372 TYPE L_AGENT_WRAP_TIME_NAC_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_WRAP_TIME_NAC%TYPE INDEX BY BINARY_INTEGER;
1373 TYPE L_AGENT_PREVIEW_TIME_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_PREVIEW_TIME%TYPE INDEX BY BINARY_INTEGER;
1374 TYPE L_AGENT_CALLS_TRAN_CONF_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_CALLS_TRAN_CONF_TO_NAC%TYPE INDEX BY BINARY_INTEGER;
1375 TYPE L_AGENT_CONT_CALLS_HAND_NA_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_CONT_CALLS_HAND_NA%TYPE INDEX BY BINARY_INTEGER;
1376 TYPE L_AGENT_CALLS_hand_TOTAL_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_CALLS_HANDLED_TOTAL%TYPE INDEX BY BINARY_INTEGER;
1377 TYPE L_AGENT_CALLS_hand_ABOVE_TH_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_CALLS_HANDLED_ABOVE_TH%TYPE INDEX BY BINARY_INTEGER;
1378 TYPE L_AGENT_CALLS_ans_BY_GOAL_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_CALLS_ANSWERED_BY_GOAL%TYPE INDEX BY BINARY_INTEGER;
1379 TYPE L_AGENT_LEADS_CREATED_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_LEADS_CREATED%TYPE INDEX BY BINARY_INTEGER;
1380 TYPE L_AGENT_SR_CREATED_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_SR_CREATED%TYPE INDEX BY BINARY_INTEGER;
1381 TYPE L_AGENT_LEADS_AMOUNT_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_LEADS_AMOUNT%TYPE INDEX BY BINARY_INTEGER;
1382 TYPE L_AGENT_LEADS_conv_TO_OPP_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_LEADS_CONVERTED_TO_OPP%TYPE INDEX BY BINARY_INTEGER;
1383 TYPE L_AGENT_opps_CREATED_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_OPPORTUNITIES_CREATED%TYPE INDEX BY BINARY_INTEGER;
1384 TYPE L_AGENT_opps_WON_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_OPPORTUNITIES_WON%TYPE INDEX BY BINARY_INTEGER;
1385 TYPE L_AGENT_opps_WON_AMOUNT_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_OPPORTUNITIES_WON_AMOUNT%TYPE INDEX BY BINARY_INTEGER;
1386 TYPE L_AGENT_opps_CROSS_SOLD_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_OPPORTUNITIES_CROSS_SOLD%TYPE INDEX BY BINARY_INTEGER;
1387 TYPE L_AGENT_opps_UP_SOLD_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_OPPORTUNITIES_UP_SOLD%TYPE INDEX BY BINARY_INTEGER;
1388 TYPE L_AGENT_opps_DECLINED_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_OPPORTUNITIES_DECLINED%TYPE INDEX BY BINARY_INTEGER;
1389 TYPE L_AGENT_opps_LOST_t IS TABLE OF BIX_CALL_DETAILS_F.AGENT_OPPORTUNITIES_LOST%TYPE INDEX BY BINARY_INTEGER;
1390 TYPE L_AGENTCALL_ORR_COUNT_t IS TABLE OF BIX_CALL_DETAILS_F.AGENTCALL_ORR_COUNT%TYPE INDEX BY BINARY_INTEGER;
1391 TYPE l_agentcall_pr_count_t IS TABLE OF BIX_CALL_DETAILS_F.AGENTCALL_PR_COUNT%TYPE INDEX BY BINARY_INTEGER;
1392 TYPE l_agentcall_contact_count_t IS TABLE OF BIX_CALL_DETAILS_F.AGENTCALL_CONTACT_COUNT%TYPE INDEX BY BINARY_INTEGER;
1393 TYPE l_int_id_t IS TABLE OF JTF_IH_INTERACTIONS.interaction_id%TYPE INDEX BY BINARY_INTEGER;
1394 TYPE l_source_code_t IS TABLE OF JTF_IH_INTERACTIONS.source_code%TYPE INDEX BY BINARY_INTEGER;
1395 TYPE l_int_start_time_t IS TABLE OF JTF_IH_INTERACTIONS.start_date_time%TYPE INDEX BY BINARY_INTEGER;
1396 TYPE l_int_end_time_t IS TABLE OF JTF_IH_INTERACTIONS.end_date_time%TYPE INDEX BY BINARY_INTEGER;
1397 TYPE l_act_id_t IS TABLE OF JTF_IH_ACTIVITIES.activity_id%TYPE INDEX BY BINARY_INTEGER;
1398 TYPE l_act_start_time_t IS TABLE OF JTF_IH_ACTIVITIES.start_date_time%TYPE INDEX BY BINARY_INTEGER;
1399 TYPE l_action_id_t IS TABLE OF JTF_IH_ACTIVITIES.action_id%TYPE INDEX BY BINARY_INTEGER;
1400 TYPE l_action_item_id_t IS TABLE OF JTF_IH_ACTIVITIES.action_item_id%TYPE INDEX BY BINARY_INTEGER;
1401 TYPE l_doc_ref_t IS TABLE OF JTF_IH_ACTIVITIES.doc_ref%TYPE INDEX BY BINARY_INTEGER;
1402 TYPE l_doc_id_t IS TABLE OF JTF_IH_ACTIVITIES.doc_id%TYPE INDEX BY BINARY_INTEGER;
1403
1404 TYPE SourceRecordType IS RECORD
1405 (
1406 media_id L_MEDIA_ID_t,
1407 media_item_type L_MEDIA_ITEM_TYPE_t,
1408 server_group_id L_SERVER_GROUP_ID_t,
1409 classification_value L_CLASSIFICATION_VALUE_t,
1410 dnis_name L_DNIS_NAME_t,
1411 direction L_DIRECTION_t,
1412 media_start_time L_MEDIA_START_TIME_t,
1413 media_end_time L_MEDIA_END_TIME_t,
1414 abandon_flag L_ABANDON_FLAG_t,
1415 transfer_flag L_TRANSFER_FLAG_t,
1416 source_item_id L_SOURCE_ITEM_ID_t,
1417 --milcs_id L_MILCS_ID_t,
1418 resource_id L_RESOURCE_ID_t,
1419 --segment_type L_SEGMENT_TYPE_t,
1420 --segment_start_time L_SEGMENT_START_TIME_t,
1421 --segment_end_time L_SEGMENT_END_TIME_t,
1422 int_id l_int_id_t,
1423 act_id l_act_id_t,
1424 outcome_id l_outcome_id_t,
1425 result_id l_result_id_t,
1426 reason_id l_reason_id_t,
1427 party_id l_party_id_t,
1428 source_code l_source_code_t,
1429 int_start_time l_int_start_time_t,
1430 int_end_time l_int_end_time_t,
1431 action_id l_action_id_t,
1432 action_item_id l_action_item_id_t,
1433 doc_ref l_doc_ref_t,
1434 doc_id l_doc_id_t,
1435 first_agent l_resource_id_t,
1436 max_agent_talk_end l_segment_start_time_t,
1437 call_talk_start l_segment_start_time_t,
1438 call_talk_end l_segment_start_time_t,
1439 max_agent_int_end l_segment_start_time_t,
1440 act_start_time l_segment_start_time_t,
1441 ROUTE_MILCS_ID l_milcs_id_t,
1442 ROUTE_SEGS_START_TIME l_segment_start_time_t,
1443 ROUTE_SEGS_END_TIME l_segment_start_time_t,
1444 IVR_MILCS_ID l_milcs_id_t,
1445 IVR_SEGS_START_TIME l_segment_start_time_t,
1446 IVR_SEGS_END_TIME l_segment_start_time_t,
1447 FIRST_QUEUE l_milcs_id_t,
1448 LAST_QUEUE l_milcs_id_t,
1449 QUEUE_MILCS_ID l_milcs_id_t,
1450 QUEUE_SEGS_START_TIME l_segment_start_time_t,
1451 QUEUE_SEGS_END_TIME l_segment_start_time_t,
1452 AGENT_MILCS_ID l_milcs_id_t,
1453 AGENT_SEGS_START_TIME l_segment_start_time_t,
1454 AGENT_SEGS_END_TIME l_segment_start_time_t
1455 );
1456
1457
1458 l_source_record SourceRecordType;
1459 l_source_null_record SourceRecordType;
1460
1461 TYPE TargetRecordType IS RECORD
1462 (
1463 media_id L_MEDIA_ID_T,
1464 time_id L_TIME_ID_T,
1465 period_type_id L_PERIOD_TYPE_ID_T,
1466 period_start_date L_PERIOD_START_DATE_t,
1467 period_start_time L_PERIOD_START_TIME_t,
1468 day_of_week L_DAY_OF_WEEK_t,
1469 direction L_DIRECTION_T,
1470 media_item_type L_MEDIA_ITEM_TYPE_T,
1471 classification_value L_CLASSIFICATION_VALUE_T,
1472 dnis_name L_DNIS_NAME_T,
1473 server_group_id L_SERVER_GROUP_ID_T,
1474 resource_id L_RESOURCE_ID_T,
1475 party_id L_PARTY_ID_t,
1476 campaign_id L_CAMPAIGN_ID_t,
1477 schedule_id L_SCHEDULE_ID_t,
1478 source_code_id L_SOURCE_CODE_ID_t,
1479 dialing_method L_DIALING_METHOD_t,
1480 outcome_id L_OUTCOME_ID_t,
1481 result_id L_RESULT_ID_t,
1482 reason_id L_REASON_ID_t,
1483 partition_key L_PARTITION_KEY_t,
1484 call_calls_offered_total l_call_CALLS_off_TOTAL_t,
1485 call_calls_offered_above_th l_call_CALLS_off_ABOVE_TH_t,
1486 call_calls_abandoned l_call_CALLS_aband_t,
1487 call_calls_abandoned_us L_CALL_CALLS_aband_us_t,
1488 call_calls_transferred l_call_CALLS_TRANSFERRED_t,
1489 call_ivr_time l_call_IVR_TIME_t,
1490 call_route_time l_call_ROUTE_TIME_t,
1491 call_queue_time l_call_queue_time_t,
1492 call_talk_time l_call_TALK_TIME_t,
1493 call_calls_hand_tot l_call_calls_off_total_t,
1494 call_calls_hand_above_th l_call_calls_off_total_t,
1495 CALL_CONT_CALLS_OFFERED_NA l_call_calls_off_total_t,
1496 CALL_CONT_CALLS_HANDLED_TOT_NA l_call_calls_off_total_t,
1497 agent_talk_time_nac l_agent_TALK_TIME_NAC_t,
1498 agent_wrap_time_nac l_agent_WRAP_TIME_NAC_t,
1499 agent_preview_time l_agent_PREVIEW_TIME_t,
1500 agent_calls_tran_conf_to_nac l_agent_CALLS_TRAN_CONF_t,
1501 agent_cont_calls_hand_na l_agent_CONT_CALLS_HAND_NA_t,
1502 agent_cont_calls_tc_na l_agent_CONT_CALLS_HAND_NA_t,
1503 agent_calls_handled_total l_agent_CALLS_hand_TOTAL_t,
1504 agent_calls_handled_above_th l_agent_CALLS_hand_ABOVE_TH_t,
1505 agent_calls_answered_by_goal l_agent_CALLS_ans_BY_GOAL_t,
1506 agent_leads_created l_agent_LEADS_CREATED_t,
1507 agent_sr_created l_agent_SR_CREATED_t,
1508 agent_leads_amount l_agent_LEADS_AMOUNT_t,
1509 agent_leads_converted_to_opp l_agent_LEADS_conv_TO_OPP_t,
1510 agent_opportunities_created l_agent_opps_CREATED_t,
1511 agent_opportunities_won l_agent_opps_WON_t,
1512 agent_opportunities_won_amount l_agent_opps_WON_AMOUNT_t,
1513 agent_opportunities_cross_sold l_agent_opps_CROSS_SOLD_t,
1514 agent_opportunities_up_sold l_agent_opps_UP_SOLD_t,
1515 agent_opportunities_declined l_agent_opps_DECLINED_t,
1516 agent_opportunities_lost l_agent_opps_LOST_t,
1517 agentcall_orr_count l_agentcall_ORR_COUNT_t,
1518 agentcall_pr_count l_agentcall_pr_count_t,
1519 agentcall_contact_count l_agentcall_contact_count_t,
1520 CALL_TOT_QUEUE_TO_ABANDON l_call_queue_time_t,
1521 call_tot_queue_to_answer l_call_queue_time_t
1522 );
1523
1524 l_target_record TargetRecordType;
1525 l_target_null_record TargetRecordType;
1526
1527 --
1528 --Miscellaneous Variables
1529 --
1530 l_segment_start DATE;
1531 l_segment_end DATE;
1532 l_secs NUMBER;
1533 l_row_counter NUMBER;
1534 counter NUMBER;
1535 l_period_start DATE;
1536 l_final_segment VARCHAR2(1) := NULL;
1537 l_max_talk_end_date_time DATE := NULL;
1538 --l_has_agent_segs VARCHAR2(1) := NULL;
1539 --l_earliest_agent NUMBER := NULL;
1540 l_met_goal VARCHAR2(1) := NULL;
1541 l_cumulative_queue NUMBER := 0;
1542 l_sl_goal NUMBER;
1543 l_threshold NUMBER;
1544 l_prev_media_id NUMBER;
1545
1546 l_prev_campaign_id NUMBER;
1547 l_prev_schedule_id NUMBER;
1548 l_prev_outcome_id NUMBER;
1549 l_prev_result_id NUMBER;
1550 l_prev_reason_id NUMBER;
1551 l_prev_source_code_id NUMBER;
1552 l_prev_dialing_method VARCHAR2(30);
1553
1554 l_begin_date DATE;
1555 l_end_date DATE;
1556
1557 i NUMBER;
1558 j NUMBER;
1559
1560 l_call_start_bucket VARCHAR2(5);
1561 l_call_start_date DATE;
1562
1563 l_temp_flag VARCHAR2(1) := NULL;
1564 l_transferred_agent VARCHAR2(1) := NULL;
1565
1566 l_currency_code varchar2(15);
1567
1568
1569 l_test varchar2(1000);
1570
1571 --
1572 --Define some variables to hold the bucket name and
1573 --counter number of records already processed and if they are
1574 --already processed then re-write to the same counter number
1575 --
1576 l_cs_bucket DATE := NULL; --call start time bucket
1577 l_ts_bucket DATE := NULL; --talk start time bucket
1578 l_te_bucket DATE := NULL; --talk end bucket
1579 l_cs_counter NUMBER := NULL; --call start counter
1580 l_ts_counter NUMBER := NULL; --talk start counter
1581 l_te_counter NUMBER := NULL; --talk end counter
1582
1583 BEGIN
1584
1585 --g_debug_flag := 'Y';
1586
1587 --DBMS_PROFILER.START_PROFILER('ICI Test ');
1588 --DBMS_PROFILER.FLUSH_DATA;
1589
1590 l_proc_table := ProcTable();
1591
1592 --IF (g_debug_flag = 'Y') THEN
1593 write_log('Start of the procedure insert_half_hour_rows at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1594 --END IF;
1595
1596 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
1597 --'Start insert_half_hour_rows ');
1598
1599 --insert into bixtest
1600 --values ('Start insert_half_hour at ' ||
1601 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
1602 --);
1603 --commit;
1604
1605 --
1606 --Initialize source collections
1607 --
1608 l_source_record := l_source_null_record;
1609
1610
1611 --IF (g_debug_flag = 'Y') THEN
1612 --write_log('Initialized source collections');
1613 --END IF;
1614 --
1615 --Initialize target collections
1616 --
1617 l_target_record := l_target_null_record;
1618
1619 --IF (g_debug_flag = 'Y') THEN
1620 --write_log('Initialized target collections');
1621 --END IF;
1622 --
1623 --Retrieve any required profile values
1624 --
1625 IF (FND_PROFILE.DEFINED('BIX_CALL_THRESHOLD_SECS')) THEN
1626 begin
1627 l_threshold := to_number(FND_PROFILE.VALUE('BIX_CALL_THRESHOLD_SECS'));
1628 exception
1629 when others then
1630 l_threshold:=0;
1631 end;
1632 ELSE
1633 l_threshold := 0;
1634 END IF;
1635
1636 IF (FND_PROFILE.DEFINED('BIX_CALL_SLGOAL_SECS')) THEN
1637 begin
1638 l_sl_goal := to_number(FND_PROFILE.VALUE('BIX_CALL_SLGOAL_SECS'));
1639 exception
1640 when others then
1641 l_sl_goal:=0;
1642 end;
1643 ELSE
1644 l_sl_goal := 0;
1645 END IF;
1646
1647 --IF (g_debug_flag = 'Y') THEN
1648 write_log('Opening call_info cursor at '||
1649 to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
1650 );
1651 --END IF;
1652
1653 --DBMS_PROFILER.START_PROFILER('ICI Test ');
1654 --DBMS_PROFILER.FLUSH_DATA;
1655
1656 IF get_call_info%ISOPEN
1657 THEN
1658 CLOSE get_call_info;
1659 END IF;
1660
1661 OPEN get_call_info;
1662
1663 LOOP -- loop till all the records are bulk fetched and processed
1664
1665 --IF (g_debug_flag = 'Y') THEN
1666 --write_log('Before bulk fetch cursor at '||
1667 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
1668 --);
1669
1670 --
1671 --END IF;
1672
1673 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
1674 --'Start fetch bulk collect ');
1675
1676 --insert into bixtest values('before fetch');
1677 --commit;
1678 /* Fetch the rows in bulk and process them row by row */
1679 FETCH get_call_info BULK COLLECT INTO
1680 l_source_record.media_id,
1681 l_source_record.server_group_id,
1682 l_source_record.classification_value,
1683 l_source_record.dnis_name,
1684 l_source_record.direction,
1685 l_source_record.media_item_type,
1686 l_source_record.media_start_time,
1687 l_source_record.media_end_time,
1688 l_source_record.abandon_flag ,
1689 l_source_record.transfer_flag,
1690 l_source_record.source_item_id,
1691 --l_source_record.milcs_id,
1692 l_source_record.resource_id,
1693 --l_source_record.segment_type,
1694 --l_source_record.segment_start_time,
1695 --l_source_record.segment_end_time,
1696 l_source_record.max_agent_talk_end,
1697 l_source_record.first_agent,
1698 l_source_record.call_talk_start,
1699 l_source_record.call_talk_end,
1700 l_source_record.int_id,
1701 l_source_record.outcome_id,
1702 l_source_record.result_id,
1703 l_source_record.reason_id,
1704 l_source_record.party_id,
1705 l_source_record.source_code,
1706 l_source_record.int_start_time,
1707 l_source_record.int_end_time,
1708 l_source_record.max_agent_int_end,
1709 l_source_record.act_id,
1710 l_source_record.act_start_time,
1711 l_source_record.action_id,
1712 l_source_record.action_item_id,
1713 l_source_record.doc_ref,
1714 l_source_record.doc_id,
1715 l_source_record.ROUTE_MILCS_ID,
1716 l_source_record.ROUTE_SEGS_START_TIME,
1717 l_source_record.ROUTE_SEGS_END_TIME,
1718 l_source_record.IVR_MILCS_ID,
1719 l_source_record.IVR_SEGS_START_TIME,
1720 l_source_record.IVR_SEGS_END_TIME,
1721 l_source_record.FIRST_QUEUE,
1722 l_source_record.LAST_QUEUE,
1723 l_source_record.QUEUE_MILCS_ID,
1724 l_source_record.QUEUE_SEGS_START_TIME,
1725 l_source_record.QUEUE_SEGS_END_TIME,
1726 l_source_record.AGENT_MILCS_ID,
1727 l_source_record.AGENT_SEGS_START_TIME,
1728 l_source_record.AGENT_SEGS_END_TIME
1729 LIMIT 5000;
1730 --LIMIT g_commit_chunk_size;
1731
1732 --insert into bixtest values('After fetch');
1733 --commit;
1734
1735 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
1736 --'Completed bulk fetch ');
1737
1738 --IF (g_debug_flag = 'Y') THEN
1739 --write_log('Completed bulk fetch cursor at '||
1740 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
1741 --);
1742 --END IF;
1743
1744 counter := 0;
1745
1746
1747 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
1748 --'Start looping thru medias fetched ' );
1749
1750 IF l_source_record.media_id.COUNT > 0
1751 THEN
1752
1753 /* Loop through all the media rows returned by the cursor */
1754 FOR i in l_source_record.media_id.FIRST .. l_source_record.media_id.LAST LOOP
1755
1756 --
1757 --Check if it is a new media - if so need to set call counts
1758 --
1759 IF l_source_record.media_id(i) <> l_prev_media_id
1760 OR l_prev_media_id IS NULL
1761 THEN
1762 --
1763 --Set the values for the CALL row
1764 --
1765 counter := counter + 1;
1766
1767 l_met_goal := NULL;
1768 l_cumulative_queue := 0;
1769
1770 /*
1771 IF to_number(to_char(l_source_record.media_start_time(i),'MI')) >= 30
1772 THEN
1773 l_target_record.period_start_time(counter) := lpad(to_char(l_source_record.media_start_time(i),
1774 'HH24:'),3,'0')
1775 || '30';
1776 ELSE
1777 l_target_record.period_start_time(counter) := lpad(to_char(l_source_record.media_start_time(i),
1778 'HH24:'),3,'0')
1779 || '00';
1780 END IF;
1781 */
1782 l_target_record.period_start_time(counter) := '00:00';
1783
1784 l_target_record.time_id(counter) := to_char(l_source_record.media_start_time(i),'J');
1785 l_target_record.period_type_id(counter) := 1;
1786 l_target_record.period_start_date(counter) := trunc(l_source_record.media_start_time(i));
1787 l_target_record.day_of_week(counter) := to_char(l_source_record.media_start_time(i),'D');
1788 l_target_record.direction(counter) := nvl(l_source_record.direction(i),'N/A');
1789 l_target_record.media_item_type(counter) := nvl(l_source_record.media_item_type(i),-1);
1790 l_target_record.resource_id(counter) := -1; -- since it is a CALL row
1791 l_target_record.party_id(counter) := nvl(l_source_record.party_id(i),-1); -- need to set this down the line
1792 l_target_record.classification_value(counter) := nvl(l_source_record.classification_value(i),'N/A');
1793 l_target_record.dnis_name(counter) := nvl(l_source_record.dnis_name(i),'N/A');
1794 l_target_record.server_group_id(counter) := nvl(l_source_record.server_group_id(i),-1);
1795 l_target_record.outcome_id(counter) := -1; -- need to set this down the line
1796 l_target_record.result_id(counter) := -1; -- need to set this down the line
1797 l_target_record.reason_id(counter) := -1; -- need to set this down the line
1798
1799 IF l_source_record.direction(i) = 'INBOUND'
1800 THEN
1801 l_target_record.partition_key(counter) := 'IC';
1802 ELSIF l_source_record.direction(i) = 'OUTBOUND'
1803 THEN
1804 l_target_record.partition_key(counter) := 'OC';
1805 END IF;
1806
1807 l_target_record.call_calls_offered_total(counter) := 0;
1808 l_target_record.call_calls_offered_above_th(counter) := 0;
1809 l_target_record.call_calls_abandoned(counter) := 0;
1810 l_target_record.call_calls_abandoned_us(counter) := 0;
1811 l_target_record.call_calls_transferred(counter) := 0;
1812 l_target_record.call_ivr_time(counter) := 0;
1813 l_target_record.call_route_time(counter) := 0;
1814 l_target_record.call_queue_time(counter) := 0;
1815 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := 0;
1816 l_target_record.call_tot_queue_to_answer(counter) := 0;
1817 l_target_record.call_talk_time(counter) := 0;
1818 l_target_record.agent_talk_time_nac(counter) := 0;
1819 l_target_record.agent_wrap_time_nac(counter) := 0;
1820 l_target_record.agent_calls_tran_conf_to_nac(counter) := 0;
1821 l_target_record.agent_cont_calls_hand_na(counter) := 0; --needs to be NULL
1822 l_target_record.agent_cont_calls_tc_na(counter) := 0; --needs to be NULL
1823 l_target_record.agent_calls_handled_total(counter) := 0;
1824 l_target_record.agent_calls_handled_above_th(counter) := 0;
1825 l_target_record.agent_calls_answered_by_goal(counter) := 0;
1826 l_target_record.agent_sr_created(counter) := 0;
1827 l_target_record.agent_leads_created(counter) := 0;
1828 l_target_record.agent_leads_amount(counter) := 0;
1829 l_target_record.agent_leads_converted_to_opp(counter) := 0;
1830 l_target_record.agent_opportunities_created(counter) := 0;
1831 l_target_record.agent_opportunities_won(counter) := 0;
1832 l_target_record.agent_opportunities_won_amount(counter) := 0;
1833 l_target_record.agent_opportunities_cross_sold(counter) := 0;
1834 l_target_record.agent_opportunities_up_sold(counter) := 0;
1835 l_target_record.agent_opportunities_declined(counter) := 0;
1836 l_target_record.agent_opportunities_lost(counter) := 0;
1837 l_target_record.agent_preview_time(counter) := 0;
1838 l_target_record.agentcall_orr_count(counter) := 0;
1839 l_target_record.agentcall_pr_count(counter) := 0;
1840 l_target_record.agentcall_contact_count(counter) := 0;
1841 l_target_record.call_cont_calls_offered_na(counter) := 0;
1842 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 0;
1843 l_target_record.call_calls_hand_tot(counter) := 0;
1844 l_target_record.call_calls_hand_above_th(counter) := 0;
1845
1846 --IF g_debug_flag = 'Y'
1847 --THEN
1848 --write_log('New media, current size of l_proc_table is '||l_proc_table.COUNT);
1849 --END IF;
1850
1851 l_proc_table.TRIM(l_proc_table.COUNT);
1852
1853 --
1854 --Store the call start bucket into a local variable
1855 --so that we can compare it to the bucket for the continued
1856 --call measures.
1857 --
1858 l_call_start_bucket := l_target_record.period_start_time(counter);
1859 l_call_start_date := l_target_record.period_start_date(counter);
1860
1861 --
1862 --Get the campaign details - a call can be tied to only one
1863 --campaign schedule/campaign - hence we will do this at call level
1864 --
1865 --IF (g_debug_flag = 'Y') THEN
1866 --write_log('Before get_campaign_details at '||
1867 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
1868 --);
1869 --END IF;
1870
1871 --
1872 --This call should be enhanced to not go back to interactions
1873 --table as we have that info for AI already
1874 --
1875 get_campaign_details(l_source_record.media_id(i),
1876 l_source_record.direction(i),
1877 l_source_record.source_item_id(i),
1878 l_source_record.source_code(i),
1879 l_target_record.campaign_id(counter),
1880 l_target_record.schedule_id(counter),
1881 l_target_record.source_code_id(counter) ,
1882 l_target_record.dialing_method(counter)
1883 );
1884
1885 --IF (g_debug_flag = 'Y') THEN
1886 --write_log ('Source code id retrieved is ' || l_target_record.source_code_id(counter) ||
1887 --'campaign id ' || l_target_record.campaign_id(counter) ||
1888 --'schedule_id ' || l_target_record.schedule_id(counter)
1889 --);
1890 --END IF;
1891
1892 --
1893 --l_source_record.first_agent will indicate the l_earliest_agent
1894 --l_source_record.call_talk_start will indicate call level talk start time.
1895 --l_source_record.call_talk_end will indicate call level talk end time.
1896 --
1897
1898 IF l_target_record.campaign_id(counter) IS NULL
1899 THEN
1900 l_target_record.campaign_id(counter) := -1;
1901 END IF;
1902
1903 IF l_target_record.schedule_id(counter) IS NULL
1904 THEN
1905 l_target_record.schedule_id(counter) := -1;
1906 END IF;
1907
1908 IF l_target_record.source_code_id(counter) IS NULL
1909 THEN
1910 l_target_record.source_code_id(counter) := -1;
1911 END IF;
1912
1913 IF l_target_record.dialing_method(counter) IS NULL
1914 THEN
1915 l_target_record.dialing_method(counter) := 'N/A';
1916 END IF;
1917
1918 --
1919 --After the above call, campaign_id, schedule_id, source_code_id are set
1920 --
1921 --IF (g_debug_flag = 'Y') THEN
1922 --write_log('Setting previous values ');
1923 --END IF;
1924 l_prev_media_id := l_source_record.media_id(i);
1925 l_prev_campaign_id := l_target_record.campaign_id(counter);
1926 l_prev_schedule_id := l_target_record.schedule_id(counter);
1927 l_prev_source_code_id := l_target_record.source_code_id(counter);
1928 l_prev_dialing_method := l_target_record.dialing_method(counter);
1929
1930 l_target_record.call_calls_offered_total(counter) := 1;
1931
1932 IF (l_source_record.media_end_time(i)-
1933 l_source_record.media_start_time(i))*24*60*60 > l_threshold
1934 THEN
1935 l_target_record.call_calls_offered_above_th(counter) := 1;
1936 END IF;
1937
1938 IF l_source_record.transfer_flag(i) = 'T' -- transferred
1939 OR l_source_record.transfer_flag(i) = 'B' -- both transferred and conferenced
1940 THEN
1941 l_target_record.call_calls_transferred(counter) := 1;
1942 END IF;
1943
1944 --
1945 --At this point, for the new call, all dimensions have been set
1946 --for the call start bucket. Store the counter and bucket for
1947 --later re-use.
1948 --
1949 write_log('Storing cs bucket in counter : ' || counter );
1950 l_cs_bucket := to_date(
1951 to_char(l_target_record.period_start_date(counter),'DD-MM-YYYY')||
1952 ' ' || l_target_record.period_start_time(counter)
1953 ,'DD-MM-YYYY HH24:MI');
1954 l_cs_counter := counter;
1955 write_log('Storing cs bucket: ' || l_cs_bucket ||
1956 ' l_cs_counter: ' || l_cs_counter);
1957
1958 --
1959 --Now loop through and stripe the call level talk time
1960 --
1961
1962 IF l_source_record.call_talk_start(i) IS NOT NULL
1963 AND l_source_record.call_talk_end(i) IS NOT NULL
1964 THEN
1965
1966 l_begin_date := l_source_record.call_talk_start(i);
1967 l_end_date := l_source_record.call_talk_end(i);
1968
1969 l_period_start := trunc(l_begin_date);
1970
1971 /**
1972 IF TO_NUMBER(TO_CHAR(l_begin_date,'MI')) >= 30
1973 THEN
1974 l_period_start :=
1975 TO_DATE(
1976 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
1977 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '30',
1978 'YYYY/MM/DDHH24:MI'
1979 );
1980 ELSE
1981 l_period_start :=
1982 TO_DATE(
1983 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
1984 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '00',
1985 'YYYY/MM/DDHH24:MI'
1986 );
1987 END IF;
1988 **/
1989
1990 -- Variable to identify the first row of the talk time in the while loop
1991 l_row_counter := 0;
1992
1993 WHILE ( l_period_start < l_end_date )
1994 LOOP
1995
1996 IF l_cs_bucket = l_period_start
1997 THEN
1998 write_log('Buckets matched in call talk time');
1999 --
2000 --Just set the measures for counter of l_cs_counter
2001 --other dimensions are already set. Also no need to
2002 --increase counter if this is the case
2003 --
2004 IF (l_row_counter = 0 )
2005 THEN
2006 l_segment_start := l_source_record.call_talk_start(i);
2007 --
2008 --Give calls handled to beginning of talk segment
2009 --
2010 l_target_record.call_calls_hand_tot(l_cs_counter) := 1;
2011
2012 IF (l_source_record.media_end_time(i)-
2013 l_source_record.media_start_time(i))*24*60*60 > l_threshold
2014 THEN
2015 l_target_record.call_calls_hand_above_th(l_cs_counter) := 1;
2016 END IF;
2017 ELSE
2018 l_segment_start := l_period_start;
2019 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(l_cs_counter) := 1;
2020 END IF;
2021
2022 --l_segment_end := l_period_start + 1/48;
2023 l_segment_end := l_period_start + 1;
2024
2025 IF ( l_segment_end > l_end_date )
2026 THEN
2027 l_segment_end := l_end_date ;
2028 END IF;
2029
2030 l_secs := round((l_segment_end - l_segment_start) * 24 * 3600);
2031 l_target_record.call_talk_time(l_cs_counter) := l_secs;
2032
2033 l_row_counter := l_row_counter + 1;
2034 --l_period_start := l_period_start + 1/48;
2035 l_period_start := l_period_start + 1;
2036
2037 ELSE
2038
2039 counter := counter + 1;
2040 --
2041 --These are dimensions which need to be set every time a row is inserted:
2042 --
2043 l_target_record.period_type_id(counter) := 1;
2044 l_target_record.direction(counter) := nvl(l_source_record.direction(i),'N/A');
2045 l_target_record.media_item_type(counter) := nvl(l_source_record.media_item_type(i),'N/A');
2046 l_target_record.classification_value(counter) := nvl(l_source_record.classification_value(i),'N/A');
2047 l_target_record.dnis_name(counter) := nvl(l_source_record.dnis_name(i),'N/A');
2048 l_target_record.server_group_id(counter) := nvl(l_source_record.server_group_id(i),-1);
2049 l_target_record.resource_id(counter) := -1; --CALL ROW
2050
2051 l_target_record.campaign_id(counter) := nvl(l_prev_campaign_id,-1);
2052 l_target_record.schedule_id(counter) := nvl(l_prev_schedule_id,-1);
2053 l_target_record.source_code_id(counter) := nvl(l_prev_source_code_id,-1);
2054 l_target_record.dialing_method(counter) := nvl(l_prev_dialing_method,'N/A');
2055
2056 l_target_record.outcome_id(counter) := -1;
2057 l_target_record.result_id(counter) := -1;
2058 l_target_record.reason_id(counter) := -1;
2059
2060 l_target_record.party_id(counter) := nvl(l_source_record.party_id(i),-1);
2061
2062 IF l_source_record.direction(i) = 'INBOUND'
2063 THEN
2064 l_target_record.partition_key(counter) := 'IC';
2065 ELSIF l_source_record.direction(i) = 'OUTBOUND'
2066 THEN
2067 l_target_record.partition_key(counter) := 'OC';
2068 END IF;
2069
2070 l_target_record.call_calls_offered_total(counter) := 0;
2071 l_target_record.call_calls_offered_above_th(counter) := 0;
2072 l_target_record.call_calls_abandoned(counter) := 0;
2073 l_target_record.call_calls_abandoned_us(counter) := 0;
2074 l_target_record.call_calls_transferred(counter) := 0;
2075 l_target_record.call_ivr_time(counter) := 0;
2076 l_target_record.call_route_time(counter) := 0;
2077 l_target_record.call_queue_time(counter) := 0;
2078 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := 0;
2079 l_target_record.call_tot_queue_to_answer(counter) := 0;
2080 l_target_record.call_talk_time(counter) := 0;
2081 l_target_record.agent_talk_time_nac(counter) := 0;
2082 l_target_record.agent_wrap_time_nac(counter) := 0;
2083 l_target_record.agent_calls_tran_conf_to_nac(counter) := 0;
2084 l_target_record.agent_cont_calls_hand_na(counter) := 0; --needs to be NULL
2085 l_target_record.agent_cont_calls_tc_na(counter) := 0; --needs to be NULL
2086 l_target_record.agent_calls_handled_total(counter) := 0;
2087 l_target_record.agent_calls_handled_above_th(counter) := 0;
2088 l_target_record.agent_calls_answered_by_goal(counter) := 0;
2089 l_target_record.agent_sr_created(counter) := 0;
2090 l_target_record.agent_leads_created(counter) := 0;
2091 l_target_record.agent_leads_amount(counter) := 0;
2092 l_target_record.agent_leads_converted_to_opp(counter) := 0;
2093 l_target_record.agent_opportunities_created(counter) := 0;
2094 l_target_record.agent_opportunities_won(counter) := 0;
2095 l_target_record.agent_opportunities_won_amount(counter) := 0;
2096 l_target_record.agent_opportunities_cross_sold(counter) := 0;
2097 l_target_record.agent_opportunities_up_sold(counter) := 0;
2098 l_target_record.agent_opportunities_declined(counter) := 0;
2099 l_target_record.agent_opportunities_lost(counter) := 0;
2100 l_target_record.agent_preview_time(counter) := 0;
2101 l_target_record.agentcall_orr_count(counter) := 0;
2102 l_target_record.agentcall_pr_count(counter) := 0;
2103 l_target_record.agentcall_contact_count(counter) := 0;
2104 l_target_record.call_cont_calls_offered_na(counter) := 0;
2105 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 0;
2106 l_target_record.call_calls_hand_tot(counter) := 0;
2107 l_target_record.call_calls_hand_above_th(counter) := 0;
2108 IF (l_row_counter = 0 )
2109 THEN
2110 l_segment_start := l_source_record.call_talk_start(i);
2111 --
2112 --Give calls handled to beginning of talk segment
2113 --
2114 l_target_record.call_calls_hand_tot(counter) := 1;
2115
2116 IF (l_source_record.media_end_time(i)-
2117 l_source_record.media_start_time(i))*24*60*60 > l_threshold
2118 THEN
2119 l_target_record.call_calls_hand_above_th(counter) := 1;
2120 END IF;
2121 ELSE
2122 l_segment_start := l_period_start;
2123 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 1;
2124 END IF;
2125
2126 --l_segment_end := l_period_start + 1/48;
2127 l_segment_end := l_period_start + 1;
2128 IF ( l_segment_end > l_end_date )
2129 THEN
2130 l_segment_end := l_end_date ;
2131 END IF;
2132
2133 l_target_record.time_id(counter) := to_char(l_period_start,'J');
2134 l_target_record.period_start_date(counter) := trunc(l_period_start);
2135 --l_target_record.period_start_time(counter) := to_char(l_period_start,'HH24:MI');
2136 l_target_record.period_start_time(counter) := '00:00';
2137 l_target_record.day_of_week(counter) := to_char(l_period_start,'D');
2138
2139 l_secs := round((l_segment_end - l_segment_start) * 24 * 3600);
2140 l_target_record.call_talk_time(counter) := l_secs;
2141
2142 --
2143 --Set the continued call offered measures here
2144 --
2145 IF l_target_record.period_start_time(counter) <> l_call_start_bucket
2146 THEN
2147 IF check_if_processed(l_prev_media_id,'BUCKET',NULL,l_target_record.period_start_time(counter))='N'
2148 THEN
2149 l_target_record.call_cont_calls_offered_na(counter) :=1;
2150 mark_as_processed(l_prev_media_id,'BUCKET',NULL,l_target_record.period_start_time(counter));
2151 END IF;
2152 END IF;
2153
2154 l_row_counter := l_row_counter + 1;
2155 --l_period_start := l_period_start + 1/48;
2156 l_period_start := l_period_start + 1;
2157
2158 END IF; -- for if which checks if bucket = call start bucket
2159
2160 END LOOP; -- end of WHILE loop for striping CALL TALK time across buckets
2161
2162 END IF; -- if to make sure l_begin is not null
2163
2164 --IF (g_debug_flag = 'Y') THEN
2165 --write_log('Completed CALL level TALK time at '||
2166 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
2167 --);
2168 --END IF;
2169
2170 ELSE -- for if which checks if this is a new media_id
2171 --
2172 --Its the same media as before - set the dimensions which need to be set
2173 --down the line
2174 --
2175 /***********************
2176 counter := counter+1;
2177 l_target_record.campaign_id(counter) := l_prev_campaign_id;
2178 l_target_record.schedule_id(counter) := l_prev_schedule_id;
2179 l_target_record.source_code_id(counter) := l_prev_source_code_id;
2180 --Cannot set ORR at call level as one call might have multiple ORRs
2181 l_target_record.outcome_id(counter) := -1;
2182 l_target_record.result_id(counter) := -1;
2183 l_target_record.reason_id(counter) := -1;
2184 ************************/
2185 NULL;
2186
2187 END IF; --for if which checks if this is a new media
2188
2189 --
2190 --IN_QUEUE processing
2191 --
2192 IF check_if_processed(l_prev_media_id,'MILCS_ID',l_source_record.queue_milcs_id(i),NULL) = 'N'
2193 THEN
2194
2195 mark_as_processed(l_prev_media_id,'MILCS_ID', l_source_record.queue_milcs_id(i),NULL);
2196
2197 --
2198 --If this is the first IN_QUEUE segment for the media
2199 --then see if the call was ans within goal- this logic will
2200 --only work if CURSOR is sorted on MILCS_ID, as it is.
2201 --
2202 IF l_met_goal IS NULL
2203 THEN
2204 IF (l_source_record.queue_segs_end_time(i)-
2205 l_source_record.queue_segs_start_time(i)
2206 )*24*60*60 <= l_sl_goal
2207 THEN
2208 l_met_goal := 'Y';
2209 ELSE
2210 l_met_goal := 'N';
2211 END IF;
2212 END IF;
2213
2214 --
2215 --Stripe queue time by half hour buckets.
2216 --
2217
2218 l_begin_date := l_source_record.queue_segs_start_time(i);
2219 l_end_date := l_source_record.queue_segs_end_time(i);
2220
2221 l_period_start := trunc(l_begin_date);
2222
2223 /***
2224 IF TO_NUMBER(TO_CHAR(l_begin_date,'MI')) >= 30
2225 THEN
2226 l_period_start :=
2227 TO_DATE(
2228 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
2229 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '30',
2230 'YYYY/MM/DDHH24:MI'
2231 );
2232 ELSE
2233 l_period_start :=
2234 TO_DATE(
2235 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
2236 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '00',
2237 'YYYY/MM/DDHH24:MI'
2238 );
2239 END IF;
2240 ***/
2241
2242 -- Variable to identify the first row of the talk time in the while loop
2243 l_row_counter := 0;
2244
2245 WHILE ( l_period_start < l_end_date )
2246 LOOP
2247
2248 --
2249 --Check if bucket is same as call start bucket
2250 --
2251 IF l_cs_bucket = l_period_start
2252 THEN
2253 --
2254 --Just set the measures for counter of l_cs_counter
2255 --other dimensions are already set. Also no need to
2256 --increase counter if this is the case
2257 --
2258 IF (l_row_counter = 0 )
2259 THEN
2260 l_segment_start := l_source_record.queue_segs_start_time(i);
2261 ELSE
2262 l_segment_start := l_period_start;
2263 END IF;
2264
2265 --l_segment_end := l_period_start + 1/48;
2266 l_segment_end := l_period_start + 1;
2267
2268 IF ( l_segment_end >= l_end_date )
2269 THEN
2270
2271 --This is the last queue bucket - set the abandon count
2272 --and other measures which go to the last queue bucket
2273 --This is the last queue bucket. We need to check if this is the
2274 --last queue segment for this media_id since a call might have
2275 --multiple QUEUE segments. Set the abandon count, total queue to
2276 --answer, total queue to abandon etc only for the last bucket of
2277 --the last queue segment.
2278
2279 l_segment_end := l_end_date ;
2280
2281 --
2282 --l_cumulative_queue is used to keep track of the cumulative queue times
2283 --of all the QUEUE segments for a given media.
2284 --
2285 l_cumulative_queue := l_cumulative_queue +
2286 (l_source_record.queue_segs_end_time(i)-
2287 l_source_record.queue_segs_start_time(i)
2288 )*24*60*60;
2289
2290 IF l_source_record.abandon_flag(i) = 'Y'
2291 AND l_source_record.direction(i) = 'INBOUND' -- for AO calls abandone count goes to ORR row
2292 AND l_source_record.queue_milcs_id(i) =
2293 l_source_record.last_queue(i)
2294 THEN
2295 l_target_record.call_calls_abandoned(l_cs_counter) := 1;
2296 --l_target_record.CALL_TOT_QUEUE_TO_ABANDON(l_cs_counter) :=
2297 --(l_source_record.queue_segs_end_time(i)-
2298 --l_source_record.queue_segs_start_time(i))*24*60*60;
2299 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(l_cs_counter) :=
2300 nvl(l_target_record.CALL_TOT_QUEUE_TO_ABANDON(l_cs_counter),0) +
2301 l_cumulative_queue;
2302 l_cumulative_queue := 0;
2303 END IF;
2304
2305 IF l_source_record.first_agent(i) IS NOT NULL
2306 AND l_source_record.queue_milcs_id(i) =
2307 l_source_record.last_queue(i)
2308 THEN
2309 --l_target_record.call_tot_queue_to_answer(l_cs_counter) :=
2310 --(l_source_record.queue_segs_end_time(i)-
2311 --l_source_record.queue_segs_start_time(i))*24*60*60;
2312 l_target_record.call_tot_queue_to_answer(l_cs_counter) :=
2313 nvl(l_target_record.call_tot_queue_to_answer(l_cs_counter),0) +
2314 l_cumulative_queue;
2315
2316 IF l_cumulative_queue <= l_sl_goal
2317 THEN
2318 l_met_goal := 'Y';
2319 ELSE
2320 l_met_goal := 'N';
2321 END IF;
2322
2323 l_cumulative_queue := 0;
2324
2325 END IF;
2326
2327 END IF;
2328
2329 l_secs := round((l_segment_end - l_segment_start) * 24 * 3600);
2330 l_target_record.call_queue_time(l_cs_counter) :=
2331 nvl(l_target_record.call_queue_time(l_cs_counter),0) + l_secs;
2332
2333 l_row_counter := l_row_counter + 1;
2334 --l_period_start := l_period_start + 1/48;
2335 l_period_start := l_period_start + 1;
2336
2337 ELSE
2338
2339 counter := counter + 1;
2340 l_target_record.period_type_id(counter) := 1;
2341 l_target_record.direction(counter) := nvl(l_source_record.direction(i),'N/A');
2342 l_target_record.media_item_type(counter) := nvl(l_source_record.media_item_type(i),'N/A');
2343 l_target_record.classification_value(counter) := nvl(l_source_record.classification_value(i),'N/A');
2344 l_target_record.dnis_name(counter) := nvl(l_source_record.dnis_name(i),'N/A');
2345 l_target_record.server_group_id(counter) := nvl(l_source_record.server_group_id(i),-1);
2346 l_target_record.resource_id(counter) := -1; --CALL ROW
2347
2348 l_target_record.campaign_id(counter) := nvl(l_prev_campaign_id,-1);
2349 l_target_record.schedule_id(counter) := nvl(l_prev_schedule_id,-1);
2350 l_target_record.source_code_id(counter) := nvl(l_prev_source_code_id,-1);
2351 l_target_record.dialing_method(counter) := nvl(l_prev_dialing_method,'N/A');
2352
2353 l_target_record.outcome_id(counter) := -1;
2354 l_target_record.result_id(counter) := -1;
2355 l_target_record.reason_id(counter) := -1;
2356
2357 l_target_record.party_id(counter) := nvl(l_source_record.party_id(i),-1);
2358
2359 IF l_source_record.direction(i) = 'INBOUND'
2360 THEN
2361 l_target_record.partition_key(counter) := 'IC';
2362 ELSIF l_source_record.direction(i) = 'OUTBOUND'
2363 THEN
2364 l_target_record.partition_key(counter) := 'OC';
2365 END IF;
2366 l_target_record.call_calls_offered_total(counter) := 0;
2367 l_target_record.call_calls_offered_above_th(counter) := 0;
2368 l_target_record.call_calls_abandoned(counter) := 0;
2369 l_target_record.call_calls_abandoned_us(counter) := 0;
2370 l_target_record.call_calls_transferred(counter) := 0;
2371 l_target_record.call_ivr_time(counter) := 0;
2372 l_target_record.call_route_time(counter) := 0;
2373 l_target_record.call_queue_time(counter) := 0;
2374 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := 0;
2375 l_target_record.call_tot_queue_to_answer(counter) := 0;
2376 l_target_record.call_talk_time(counter) := 0;
2377 l_target_record.agent_talk_time_nac(counter) := 0;
2378 l_target_record.agent_wrap_time_nac(counter) := 0;
2379 l_target_record.agent_calls_tran_conf_to_nac(counter) := 0;
2380 l_target_record.agent_cont_calls_hand_na(counter) := 0; --needs to be NULL
2381 l_target_record.agent_cont_calls_tc_na(counter) := 0; --needs to be NULL
2382 l_target_record.agent_calls_handled_total(counter) := 0;
2383 l_target_record.agent_calls_handled_above_th(counter) := 0;
2384 l_target_record.agent_calls_answered_by_goal(counter) := 0;
2385 l_target_record.agent_sr_created(counter) := 0;
2386 l_target_record.agent_leads_created(counter) := 0;
2387 l_target_record.agent_leads_amount(counter) := 0;
2388 l_target_record.agent_leads_converted_to_opp(counter) := 0;
2389 l_target_record.agent_opportunities_created(counter) := 0;
2390 l_target_record.agent_opportunities_won(counter) := 0;
2391 l_target_record.agent_opportunities_won_amount(counter) := 0;
2392 l_target_record.agent_opportunities_cross_sold(counter) := 0;
2393 l_target_record.agent_opportunities_up_sold(counter) := 0;
2394 l_target_record.agent_opportunities_declined(counter) := 0;
2395 l_target_record.agent_opportunities_lost(counter) := 0;
2396 l_target_record.agent_preview_time(counter) := 0;
2397 l_target_record.agentcall_orr_count(counter) := 0;
2398 l_target_record.agentcall_pr_count(counter) := 0;
2399 l_target_record.agentcall_contact_count(counter) := 0;
2400 l_target_record.call_cont_calls_offered_na(counter) := 0;
2401 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 0;
2402 l_target_record.call_calls_hand_tot(counter) := 0;
2403 l_target_record.call_calls_hand_above_th(counter) := 0;
2404
2405 IF (l_row_counter = 0 )
2406 THEN
2407 l_segment_start := l_source_record.queue_segs_start_time(i);
2408 ELSE
2409 l_segment_start := l_period_start;
2410 END IF;
2411
2412 --l_segment_end := l_period_start + 1/48;
2413 l_segment_end := l_period_start + 1;
2414
2415 IF ( l_segment_end >= l_end_date )
2416 THEN
2417
2418 --This is the last queue bucket - set the abandon count
2419 --and other measures which go to the last queue bucket
2420 --This is the last queue bucket. We need to check if this is the
2421 --last queue segment for this media_id since a call might have
2422 --multiple QUEUE segments. Set the abandon count, total queue to
2423 --answer, total queue to abandon etc only for the last bucket of
2424 --the last queue segment.
2425
2426 l_segment_end := l_end_date ;
2427 --
2428 --l_cumulative_queue is used to keep track of the cumulative queue times
2429 --of all the QUEUE segments for a given media.
2430 --
2431 l_cumulative_queue := l_cumulative_queue +
2432 (l_source_record.queue_segs_end_time(i)-
2433 l_source_record.queue_segs_start_time(i)
2434 )*24*60*60;
2435
2436 IF l_source_record.abandon_flag(i) = 'Y'
2437 AND l_source_record.direction(i) = 'INBOUND' -- for AO calls abandone count goes to ORR row
2438 AND l_source_record.queue_milcs_id(i) =
2439 l_source_record.last_queue(i)
2440 THEN
2441 l_target_record.call_calls_abandoned(counter) := 1;
2442 --l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) :=
2443 --(l_source_record.queue_segs_end_time(i)-
2444 --l_source_record.queue_segs_start_time(i))*24*60*60;
2445 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := l_cumulative_queue;
2446 l_cumulative_queue := 0;
2447 END IF;
2448
2449 IF l_source_record.first_agent(i) IS NOT NULL
2450 AND l_source_record.queue_milcs_id(i) =
2451 l_source_record.last_queue(i)
2452 THEN
2453 --l_target_record.call_tot_queue_to_answer(counter) :=
2454 --(l_source_record.queue_segs_end_time(i)-
2455 --l_source_record.queue_segs_start_time(i))*24*60*60;
2456 l_target_record.call_tot_queue_to_answer(counter) := l_cumulative_queue;
2457
2458 IF l_cumulative_queue <= l_sl_goal
2459 THEN
2460 l_met_goal := 'Y';
2461 ELSE
2462 l_met_goal := 'N';
2463 END IF;
2464
2465 l_cumulative_queue := 0;
2466
2467 END IF;
2468
2469 END IF;
2470
2471 l_target_record.time_id(counter) := to_char(l_period_start,'J');
2472 l_target_record.period_start_date(counter) := trunc(l_period_start);
2473 --l_target_record.period_start_time(counter) := to_char(l_period_start,'HH24:MI');
2474 l_target_record.period_start_time(counter) := '00:00';
2475 l_target_record.day_of_week(counter) := to_char(l_period_start,'D');
2476
2477 l_secs := round((l_segment_end - l_segment_start) * 24 * 3600);
2478 l_target_record.call_queue_time(counter) := l_secs;
2479
2480 --
2481 --Set the continued call measures here
2482 --
2483 IF l_target_record.period_start_time(counter) <> l_call_start_bucket
2484 THEN
2485 IF check_if_processed(l_prev_media_id,'BUCKET',NULL,l_target_record.period_start_time(counter))='N'
2486 THEN
2487 --IF (g_debug_flag = 'Y') THEN
2488 --write_log('Setting continued measures');
2489 --END IF;
2490 l_target_record.call_cont_calls_offered_na(counter) :=1;
2491 mark_as_processed(l_prev_media_id,'BUCKET',NULL,l_target_record.period_start_time(counter));
2492 END IF;
2493 END IF;
2494
2495 l_row_counter := l_row_counter + 1;
2496 --l_period_start := l_period_start + 1/48;
2497 l_period_start := l_period_start + 1;
2498
2499 END IF; -- for if which checks if same as call start bucket
2500
2501 END LOOP; -- end of WHILE loop for striping queue time across half hour buckets
2502
2503 --IF (g_debug_flag = 'Y') THEN
2504 --write_log('Completed queue time at '||
2505 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
2506 --);
2507 --END IF;
2508
2509
2510 -------------------------------------------------------------------
2511
2512
2513 END IF; -- for if which check if it is IN_QUEUE
2514
2515 --
2516 --IVR Processing
2517 --
2518
2519 IF check_if_processed(l_prev_media_id,'MILCS_ID',l_source_record.ivr_milcs_id(i),NULL) = 'N'
2520 THEN
2521 mark_as_processed(l_prev_media_id,'MILCS_ID', l_source_record.ivr_milcs_id(i),NULL);
2522
2523 --
2524 --Stripe IVR time by half hour buckets.
2525 --
2526 l_begin_date := l_source_record.ivr_segs_start_time(i);
2527 l_end_date := l_source_record.ivr_segs_end_time(i);
2528
2529 l_period_start := trunc(l_begin_date);
2530
2531 /****
2532 IF TO_NUMBER(TO_CHAR(l_begin_date,'MI')) >= 30
2533 THEN
2534 l_period_start :=
2535 TO_DATE(
2536 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
2537 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '30',
2538 'YYYY/MM/DDHH24:MI'
2539 );
2540 ELSE
2541 l_period_start :=
2542 TO_DATE(
2543 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
2544 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '00',
2545 'YYYY/MM/DDHH24:MI'
2546 );
2547 END IF;
2548 ***/
2549
2550 -- Variable to identify the first row of the talk time in the while loop
2551 l_row_counter := 0;
2552
2553 WHILE ( l_period_start < l_end_date )
2554 LOOP
2555
2556 --
2557 --Check if bucket is same as call start bucket
2558 --
2559 IF l_cs_bucket = l_period_start
2560 THEN
2561 --
2562 --Just set the measures for counter of l_cs_counter
2563 --other dimensions are already set. Also no need to
2564 --increase counter if this is the case
2565 --
2566 IF (l_row_counter = 0 )
2567 THEN
2568 l_segment_start := l_source_record.ivr_segs_start_time(i);
2569 ELSE
2570 l_segment_start := l_period_start;
2571 END IF;
2572
2573 --l_segment_end := l_period_start + 1/48;
2574 l_segment_end := l_period_start + 1;
2575 IF ( l_segment_end > l_end_date )
2576 THEN
2577 l_segment_end := l_end_date ;
2578 END IF;
2579
2580 l_secs := round((l_segment_end - l_segment_start) * 24 * 3600);
2581 l_target_record.call_ivr_time(l_cs_counter) := l_secs;
2582
2583 l_row_counter := l_row_counter + 1;
2584 --l_period_start := l_period_start + 1/48;
2585 l_period_start := l_period_start + 1;
2586
2587 ELSE
2588
2589 counter := counter + 1;
2590 l_target_record.period_type_id(counter) := 1;
2591 l_target_record.direction(counter) := nvl(l_source_record.direction(i),'N/A');
2592 l_target_record.media_item_type(counter) := nvl(l_source_record.media_item_type(i),'N/A');
2593 l_target_record.classification_value(counter) := nvl(l_source_record.classification_value(i),'N/A');
2594 l_target_record.dnis_name(counter) := nvl(l_source_record.dnis_name(i),'N/A');
2595 l_target_record.server_group_id(counter) := nvl(l_source_record.server_group_id(i),-1);
2596 l_target_record.resource_id(counter) := -1; --CALL ROW
2597
2598 l_target_record.campaign_id(counter) := nvl(l_prev_campaign_id,-1);
2599 l_target_record.schedule_id(counter) := nvl(l_prev_schedule_id,-1);
2600 l_target_record.source_code_id(counter) := nvl(l_prev_source_code_id,-1);
2601 l_target_record.dialing_method(counter) := nvl(l_prev_dialing_method,'N/A');
2602
2603 l_target_record.outcome_id(counter) := -1;
2604 l_target_record.result_id(counter) := -1;
2605 l_target_record.reason_id(counter) := -1;
2606
2607 l_target_record.party_id(counter) := nvl(l_source_record.party_id(i),-1);
2608
2609 IF l_source_record.direction(i) = 'INBOUND'
2610 THEN
2611 l_target_record.partition_key(counter) := 'IC';
2612 ELSIF l_source_record.direction(i) = 'OUTBOUND'
2613 THEN
2614 l_target_record.partition_key(counter) := 'OC';
2615 END IF;
2616 l_target_record.call_calls_offered_total(counter) := 0;
2617 l_target_record.call_calls_offered_above_th(counter) := 0;
2618 l_target_record.call_calls_abandoned(counter) := 0;
2619 l_target_record.call_calls_abandoned_us(counter) := 0;
2620 l_target_record.call_calls_transferred(counter) := 0;
2621 l_target_record.call_ivr_time(counter) := 0;
2622 l_target_record.call_route_time(counter) := 0;
2623 l_target_record.call_queue_time(counter) := 0;
2624 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := 0;
2625 l_target_record.call_tot_queue_to_answer(counter) := 0;
2626 l_target_record.call_talk_time(counter) := 0;
2627 l_target_record.agent_talk_time_nac(counter) := 0;
2628 l_target_record.agent_wrap_time_nac(counter) := 0;
2629 l_target_record.agent_calls_tran_conf_to_nac(counter) := 0;
2630 l_target_record.agent_cont_calls_hand_na(counter) := 0; --needs to be NULL
2631 l_target_record.agent_cont_calls_tc_na(counter) := 0; --needs to be NULL
2632 l_target_record.agent_calls_handled_total(counter) := 0;
2633 l_target_record.agent_calls_handled_above_th(counter) := 0;
2634 l_target_record.agent_calls_answered_by_goal(counter) := 0;
2635 l_target_record.agent_sr_created(counter) := 0;
2636 l_target_record.agent_leads_created(counter) := 0;
2637 l_target_record.agent_leads_amount(counter) := 0;
2638 l_target_record.agent_leads_converted_to_opp(counter) := 0;
2639 l_target_record.agent_opportunities_created(counter) := 0;
2640 l_target_record.agent_opportunities_won(counter) := 0;
2641 l_target_record.agent_opportunities_won_amount(counter) := 0;
2642 l_target_record.agent_opportunities_cross_sold(counter) := 0;
2643 l_target_record.agent_opportunities_up_sold(counter) := 0;
2644 l_target_record.agent_opportunities_declined(counter) := 0;
2645 l_target_record.agent_opportunities_lost(counter) := 0;
2646 l_target_record.agent_preview_time(counter) := 0;
2647 l_target_record.agentcall_orr_count(counter) := 0;
2648 l_target_record.agentcall_pr_count(counter) := 0;
2649 l_target_record.agentcall_contact_count(counter) := 0;
2650 l_target_record.call_cont_calls_offered_na(counter) := 0;
2651 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 0;
2652 l_target_record.call_calls_hand_tot(counter) := 0;
2653 l_target_record.call_calls_hand_above_th(counter) := 0;
2654
2655 IF (l_row_counter = 0 )
2656 THEN
2657 l_segment_start := l_source_record.ivr_segs_start_time(i);
2658 ELSE
2659 l_segment_start := l_period_start;
2660 END IF;
2661
2662 --l_segment_end := l_period_start + 1/48;
2663 l_segment_end := l_period_start + 1;
2664 IF ( l_segment_end > l_end_date )
2665 THEN
2666 l_segment_end := l_end_date ;
2667 END IF;
2668
2669 l_target_record.time_id(counter) := to_char(l_period_start,'J');
2670 l_target_record.period_start_date(counter) := trunc(l_period_start);
2671 --l_target_record.period_start_time(counter) := to_char(l_period_start,'HH24:MI');
2672 l_target_record.period_start_time(counter) := '00:00';
2673 l_target_record.day_of_week(counter) := to_char(l_period_start,'D');
2674
2675 l_secs := round((l_segment_end - l_segment_start) * 24 * 3600);
2676 l_target_record.call_ivr_time(counter) := l_secs;
2677
2678 --
2679 --Set the continued call measures here
2680 --
2681 IF l_target_record.period_start_time(counter) <> l_call_start_bucket
2682 THEN
2683 IF check_if_processed(l_prev_media_id,'BUCKET',NULL,l_target_record.period_start_time(counter))='N'
2684 THEN
2685 --IF (g_debug_flag = 'Y') THEN
2686 --write_log('Setting continued measures');
2687 --END IF;
2688 l_target_record.call_cont_calls_offered_na(counter) :=1;
2689 mark_as_processed(l_prev_media_id,'BUCKET',NULL,l_target_record.period_start_time(counter));
2690 END IF;
2691 END IF;
2692
2693 l_row_counter := l_row_counter + 1;
2694 --l_period_start := l_period_start + 1/48;
2695 l_period_start := l_period_start + 1;
2696
2697 END IF; --for if which checks if = call start bucket
2698
2699 END LOOP; -- end of WHILE loop for striping IVR time across buckets
2700
2701 END IF; --check if IVR processed
2702
2703 --
2704 --ROUTE processing
2705 --
2706
2707 IF check_if_processed(l_prev_media_id,'MILCS_ID',l_source_record.route_milcs_id(i),NULL) = 'N'
2708 THEN
2709 mark_as_processed(l_prev_media_id,'MILCS_ID', l_source_record.route_milcs_id(i),NULL);
2710 --
2711 --Stripe ROUTE time by half hour buckets.
2712 --
2713 l_begin_date := l_source_record.route_segs_start_time(i);
2714 l_end_date := l_source_record.route_segs_end_time(i);
2715
2716 l_period_start := trunc(l_begin_date);
2717 /***
2718 IF TO_NUMBER(TO_CHAR(l_begin_date,'MI')) >= 30
2719 THEN
2720 l_period_start :=
2721 TO_DATE(
2722 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
2723 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '30',
2724 'YYYY/MM/DDHH24:MI'
2725 );
2726 ELSE
2727 l_period_start :=
2728 TO_DATE(
2729 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
2730 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '00',
2731 'YYYY/MM/DDHH24:MI'
2732 );
2733 END IF;
2734 ***/
2735
2736 -- Variable to identify the first row of the talk time in the while loop
2737 l_row_counter := 0;
2738
2739 WHILE ( l_period_start < l_end_date )
2740 LOOP
2741
2742 --
2743 --Check if bucket is same as call start bucket
2744 --
2745 IF l_cs_bucket = l_period_start
2746 THEN
2747 --
2748 --Just set the measures for counter of l_cs_counter
2749 --other dimensions are already set. Also no need to
2750 --increase counter if this is the case
2751 --
2752 IF (l_row_counter = 0 )
2753 THEN
2754 l_segment_start := l_source_record.route_segs_start_time(i);
2755 ELSE
2756 l_segment_start := l_period_start;
2757 END IF;
2758
2759 --l_segment_end := l_period_start + 1/48;
2760 l_segment_end := l_period_start + 1;
2761 IF ( l_segment_end > l_end_date )
2762 THEN
2763 l_segment_end := l_end_date ;
2764 END IF;
2765
2766 l_secs := round((l_segment_end - l_segment_start) * 24 * 3600);
2767 l_target_record.call_route_time(l_cs_counter) := l_secs;
2768
2769 l_row_counter := l_row_counter + 1;
2770 --l_period_start := l_period_start + 1/48;
2771 l_period_start := l_period_start + 1;
2772
2773 ELSE
2774
2775 counter := counter + 1;
2776
2777 l_target_record.period_type_id(counter) := 1;
2778 l_target_record.direction(counter) := nvl(l_source_record.direction(i),'N/A');
2779 l_target_record.media_item_type(counter) := nvl(l_source_record.media_item_type(i),'N/A');
2780 l_target_record.classification_value(counter) := nvl(l_source_record.classification_value(i),'N/A');
2781 l_target_record.dnis_name(counter) := nvl(l_source_record.dnis_name(i),'N/A');
2782 l_target_record.server_group_id(counter) := nvl(l_source_record.server_group_id(i),-1);
2783 l_target_record.resource_id(counter) := -1; --CALL ROW
2784
2785 l_target_record.campaign_id(counter) := nvl(l_prev_campaign_id,-1);
2786 l_target_record.schedule_id(counter) := nvl(l_prev_schedule_id,-1);
2787 l_target_record.source_code_id(counter) := nvl(l_prev_source_code_id,-1);
2788 l_target_record.dialing_method(counter) := nvl(l_prev_dialing_method,'N/A');
2789
2790 l_target_record.outcome_id(counter) := -1;
2791 l_target_record.result_id(counter) := -1;
2792 l_target_record.reason_id(counter) := -1;
2793
2794 l_target_record.party_id(counter) := nvl(l_source_record.party_id(i),-1);
2795
2796 IF l_source_record.direction(i) = 'INBOUND'
2797 THEN
2798 l_target_record.partition_key(counter) := 'IC';
2799 ELSIF l_source_record.direction(i) = 'OUTBOUND'
2800 THEN
2801 l_target_record.partition_key(counter) := 'OC';
2802 END IF;
2803 l_target_record.call_calls_offered_total(counter) := 0;
2804 l_target_record.call_calls_offered_above_th(counter) := 0;
2805 l_target_record.call_calls_abandoned(counter) := 0;
2806 l_target_record.call_calls_abandoned_us(counter) := 0;
2807 l_target_record.call_calls_transferred(counter) := 0;
2808 l_target_record.call_ivr_time(counter) := 0;
2809 l_target_record.call_route_time(counter) := 0;
2810 l_target_record.call_queue_time(counter) := 0;
2811 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := 0;
2812 l_target_record.call_tot_queue_to_answer(counter) := 0;
2813 l_target_record.call_talk_time(counter) := 0;
2814 l_target_record.agent_talk_time_nac(counter) := 0;
2815 l_target_record.agent_wrap_time_nac(counter) := 0;
2816 l_target_record.agent_calls_tran_conf_to_nac(counter) := 0;
2817 l_target_record.agent_cont_calls_hand_na(counter) := 0; --needs to be NULL
2818 l_target_record.agent_cont_calls_tc_na(counter) := 0; --needs to be NULL
2819 l_target_record.agent_calls_handled_total(counter) := 0;
2820 l_target_record.agent_calls_handled_above_th(counter) := 0;
2821 l_target_record.agent_calls_answered_by_goal(counter) := 0;
2822 l_target_record.agent_sr_created(counter) := 0;
2823 l_target_record.agent_leads_created(counter) := 0;
2824 l_target_record.agent_leads_amount(counter) := 0;
2825 l_target_record.agent_leads_converted_to_opp(counter) := 0;
2826 l_target_record.agent_opportunities_created(counter) := 0;
2827 l_target_record.agent_opportunities_won(counter) := 0;
2828 l_target_record.agent_opportunities_won_amount(counter) := 0;
2829 l_target_record.agent_opportunities_cross_sold(counter) := 0;
2830 l_target_record.agent_opportunities_up_sold(counter) := 0;
2831 l_target_record.agent_opportunities_declined(counter) := 0;
2832 l_target_record.agent_opportunities_lost(counter) := 0;
2833 l_target_record.agent_preview_time(counter) := 0;
2834 l_target_record.agentcall_orr_count(counter) := 0;
2835 l_target_record.agentcall_pr_count(counter) := 0;
2836 l_target_record.agentcall_contact_count(counter) := 0;
2837 l_target_record.call_cont_calls_offered_na(counter) := 0;
2838 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 0;
2839 l_target_record.call_calls_hand_tot(counter) := 0;
2840 l_target_record.call_calls_hand_above_th(counter) := 0;
2841
2842 IF (l_row_counter = 0 )
2843 THEN
2844 l_segment_start := l_source_record.route_segs_start_time(i);
2845 ELSE
2846 l_segment_start := l_period_start;
2847 END IF;
2848
2849 --l_segment_end := l_period_start + 1/48;
2850 l_segment_end := l_period_start + 1;
2851 IF ( l_segment_end > l_end_date )
2852 THEN
2853 l_segment_end := l_end_date ;
2854 END IF;
2855
2856 l_target_record.time_id(counter) := to_char(l_period_start,'J');
2857 l_target_record.period_start_date(counter) := trunc(l_period_start);
2858 --l_target_record.period_start_time(counter) := to_char(l_period_start,'HH24:MI');
2859 l_target_record.period_start_time(counter) := '00:00';
2860 l_target_record.day_of_week(counter) := to_char(l_period_start,'D');
2861
2862 l_secs := round((l_segment_end - l_segment_start) * 24 * 3600);
2863 l_target_record.call_route_time(counter) := l_secs;
2864
2865 --
2866 --Set the continued call measures here
2867 --
2868 IF l_target_record.period_start_time(counter) <> l_call_start_bucket
2869 THEN
2870 IF check_if_processed(l_prev_media_id,'BUCKET',NULL,l_target_record.period_start_time(counter))='N'
2871 THEN
2872 l_target_record.call_cont_calls_offered_na(counter) := 1;
2873 mark_as_processed(l_prev_media_id,'BUCKET',NULL,l_target_record.period_start_time(counter));
2874 END IF;
2875 END IF;
2876
2877 l_row_counter := l_row_counter + 1;
2878 --l_period_start := l_period_start + 1/48;
2879 l_period_start := l_period_start + 1;
2880
2881 END IF; --to check if same as call start bucket
2882
2883 END LOOP; -- end of WHILE loop for striping queue time across half hour buckets
2884
2885 END IF; --if ROUTE not processed
2886
2887 --
2888 --This is the end of processing for the call level row.
2889 --Now start the agent level row after incrementing counter.
2890 --
2891
2892 IF l_source_record.agent_milcs_id(i) IS NOT NULL --means with_agent segment is there
2893 THEN
2894
2895 IF check_if_processed(l_prev_media_id,'MILCS_ID',l_source_record.agent_milcs_id(i),NULL) = 'N'
2896 THEN
2897 mark_as_processed(l_prev_media_id,'MILCS_ID', l_source_record.agent_milcs_id(i),NULL);
2898
2899 --IF (g_debug_flag = 'Y') THEN
2900 --write_log('Starting agent processing');
2901 --END IF;
2902 --
2903 --Agent level processing
2904 --
2905 counter := counter+1;
2906
2907 --IF (g_debug_flag = 'Y') THEN
2908 --write_log('Counter value agent proc is '||counter);
2909 --END IF;
2910
2911 --
2912 --These are the dimensions which need to be set every time a row is inserted:
2913 --
2914
2915 --IF (g_debug_flag = 'Y') THEN
2916 --write_log('Completed Extending agent level dimensions ');
2917 --END IF;
2918 --
2919 --Figure out what the bucket start time is at the call level
2920 --This will be used for period start time
2921 --
2922 --select lpad(to_char(l_source_record.segment_start_time(i),'HH24:'),3,'0') ||
2923 --decode(sign(to_number(to_char(l_source_record.segment_start_time(i)
2924 --,'MI'))-29),0,'00',1,'30',-1,'00')
2925 --into l_target_record.period_start_time(counter)
2926 --from dual;
2927
2928 l_target_record.period_start_time(counter) := '00:00';
2929
2930 /***
2931 IF to_number(to_char(l_source_record.agent_segs_start_time(i),'MI')) >= 30
2932 THEN
2933 l_target_record.period_start_time(counter):=
2934 lpad(to_char(l_source_record.agent_segs_start_time(i),'HH24:'),3,'0')||'30';
2935 ELSE
2936 l_target_record.period_start_time(counter) :=
2937 lpad(to_char(l_source_record.agent_segs_start_time(i),'HH24:'),3,'0') ||
2938 '00';
2939 END IF;
2940 ****/
2941
2942 l_target_record.time_id(counter) := to_char(l_source_record.agent_segs_start_time(i),'J');
2943 l_target_record.period_type_id(counter) := 1;
2944 l_target_record.period_start_date(counter) := trunc(l_source_record.agent_segs_start_time(i));
2945 l_target_record.day_of_week(counter) := to_char(l_source_record.agent_segs_start_time(i),'D');
2946 l_target_record.media_id(counter) := l_source_record.media_id(i);
2947 l_target_record.direction(counter) := l_source_record.direction(i);
2948 l_target_record.media_item_type(counter) := nvl(l_source_record.media_item_type(i),'N/A');
2949 l_target_record.resource_id(counter) := nvl(l_source_record.resource_id(i),-1);
2950 l_target_record.party_id(counter) := nvl(l_source_record.party_id(i),-1);
2951 l_target_record.classification_value(counter) := nvl(l_source_record.classification_value(i),'N/A');
2952 l_target_record.dnis_name(counter) := nvl(l_source_record.dnis_name(i),'N/A');
2953 l_target_record.server_group_id(counter) := nvl(l_source_record.server_group_id(i),-1);
2954
2955 l_target_record.campaign_id(counter) := nvl(l_prev_campaign_id,-1);
2956 l_target_record.schedule_id(counter) := nvl(l_prev_schedule_id,-1);
2957 l_target_record.source_code_id(counter) := nvl(l_prev_source_code_id,-1);
2958 l_target_record.dialing_method(counter) := nvl(l_prev_dialing_method,'N/A');
2959
2960 l_target_record.outcome_id(counter) := -1; -- need to set this down the line
2961 l_target_record.result_id(counter) := -1; -- need to set this down the line
2962 l_target_record.reason_id(counter) := -1; -- need to set this down the line
2963
2964 IF l_source_record.direction(i) = 'INBOUND'
2965 THEN
2966 l_target_record.partition_key(counter) := 'IA';
2967 ELSIF l_source_record.direction(i) = 'OUTBOUND'
2968 THEN
2969 l_target_record.partition_key(counter) := 'OA';
2970 END IF;
2971
2972 l_target_record.call_calls_offered_total(counter) := 0;
2973 l_target_record.call_calls_offered_above_th(counter) := 0;
2974 l_target_record.call_calls_abandoned(counter) := 0;
2975 l_target_record.call_calls_abandoned_us(counter) := 0;
2976 l_target_record.call_calls_transferred(counter) := 0;
2977 l_target_record.call_ivr_time(counter) := 0;
2978 l_target_record.call_route_time(counter) := 0;
2979 l_target_record.call_queue_time(counter) := 0;
2980 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := 0;
2981 l_target_record.call_tot_queue_to_answer(counter) := 0;
2982 l_target_record.call_talk_time(counter) := 0;
2983 l_target_record.agent_talk_time_nac(counter) := 0;
2984 l_target_record.agent_wrap_time_nac(counter) := 0;
2985 l_target_record.agent_calls_tran_conf_to_nac(counter) := 0;
2986 l_target_record.agent_cont_calls_hand_na(counter) := 0; --needs to be NULL
2987 l_target_record.agent_cont_calls_tc_na(counter) := 0; --needs to be NULL
2988 l_target_record.agent_calls_handled_total(counter) := 0;
2989 l_target_record.agent_calls_handled_above_th(counter) := 0;
2990 l_target_record.agent_calls_answered_by_goal(counter) := 0;
2991 l_target_record.agent_sr_created(counter) := 0;
2992 l_target_record.agent_leads_created(counter) := 0;
2993 l_target_record.agent_leads_amount(counter) := 0;
2994 l_target_record.agent_leads_converted_to_opp(counter) := 0;
2995 l_target_record.agent_opportunities_created(counter) := 0;
2996 l_target_record.agent_opportunities_won(counter) := 0;
2997 l_target_record.agent_opportunities_won_amount(counter) := 0;
2998 l_target_record.agent_opportunities_cross_sold(counter) := 0;
2999 l_target_record.agent_opportunities_up_sold(counter) := 0;
3000 l_target_record.agent_opportunities_declined(counter) := 0;
3001 l_target_record.agent_opportunities_lost(counter) := 0;
3002 l_target_record.agent_preview_time(counter) := 0;
3003 l_target_record.agentcall_orr_count(counter) := 0;
3004 l_target_record.agentcall_pr_count(counter) := 0;
3005 l_target_record.agentcall_contact_count(counter) := 0;
3006 l_target_record.call_cont_calls_offered_na(counter) := 0;
3007 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 0;
3008 l_target_record.call_calls_hand_tot(counter) := 0;
3009 l_target_record.call_calls_hand_above_th(counter) := 0;
3010 --
3011 --These are the measures which need to be set every time a row is inserted
3012 --
3013
3014 --IF (g_debug_flag = 'Y') THEN
3015 --write_log('Completed Extending agent level measures ');
3016 --END IF;
3017
3018 --IF (g_debug_flag = 'Y') THEN
3019 --write_log('Completed initializing agent level measures ');
3020 --END IF;
3021
3022 --IF l_source_record.resource_id(i) = l_earliest_agent
3023
3024 l_temp_flag := NULL;
3025 l_transferred_agent := NULL;
3026
3027 IF l_source_record.resource_id(i) = l_source_record.first_agent(i)
3028 THEN
3029 l_transferred_agent := 'N';
3030 --
3031 --This means this agent was the first agent to answer the call and hence
3032 --gets credit for the handled count.
3033 --However, in some cases the same agent might have records in the segments
3034 --multiple times - example in case of transfers. For such cases we need
3035 --to make sure the agent has not already been given credit for this call.
3036 --If we dont do this then we will double count the calls.
3037 --
3038
3039 l_temp_flag := check_if_processed(l_prev_media_id,'AGENT_BUCKET',
3040 l_source_record.resource_id(i),
3041 l_target_record.period_start_time(counter));
3042
3043 IF l_temp_flag = 'N'
3044 THEN
3045
3046 mark_as_processed(l_prev_media_id,'AGENT_BUCKET',
3047 l_source_record.resource_id(i),
3048 l_target_record.period_start_time(counter));
3049
3050 --
3051 --Set the calls hand and other measures which go only to first agent
3052 --
3053 l_target_record.agent_calls_handled_total(counter) := 1;
3054
3055 IF (l_source_record.media_end_time(i)-
3056 l_source_record.media_start_time(i)
3057 )*24*60*60 > l_threshold
3058 THEN
3059 l_target_record.agent_calls_handled_above_th(counter) := 1;
3060 ELSE
3061 l_target_record.agent_calls_handled_above_th(counter) := 0;
3062 END IF;
3063
3064 IF l_met_goal = 'Y'
3065 THEN
3066 l_target_record.agent_calls_answered_by_goal(counter) := 1;
3067 ELSE
3068 l_target_record.agent_calls_answered_by_goal(counter) := 0;
3069 END IF;
3070
3071 ELSIF l_temp_flag = 'A' --this means agent was processed but not bucket
3072 --set the continued calls measure
3073 THEN
3074
3075 l_target_record.agent_cont_calls_hand_na(counter) := 1;
3076
3077 ELSIF l_temp_flag = 'N'
3078 THEN
3079 NULL; --this means the agents bucket was already processed so do not
3080 --assign any counts as it will double count
3081
3082 END IF; -- for if which checks if agent has been processed already for media
3083
3084 ELSE -- this means not the earliest agent to handle call
3085 l_transferred_agent := 'Y';
3086 l_target_record.agent_calls_tran_conf_to_nac(counter) := 1;
3087 END IF; -- for check for earliest agent to handle call
3088
3089 --
3090 --First set the AGENT level talk time and wrap time which need
3091 --splitting across multiple half hour buckets
3092 --
3093
3094 --
3095 --Talk time
3096 --
3097 l_begin_date := l_source_record.agent_segs_start_time(i);
3098 l_end_date := l_source_record.agent_segs_end_time(i);
3099
3100 --IF (g_debug_flag = 'Y') THEN
3101 --write_log('Starting talk time at '||
3102 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') ||
3103 --'with begin of '||to_char(l_begin_date,'mm/dd/yyyy hh24:mi:ss') ||
3104 --'end of ' ||to_char(l_end_date,'mm/dd/yyyy hh24:mi:ss')
3105 --);
3106 --END IF;
3107
3108 l_period_start := trunc(l_begin_date);
3109 /***
3110 IF TO_NUMBER(TO_CHAR(l_begin_date,'MI')) >= 30
3111 THEN
3112 l_period_start :=
3113 TO_DATE(
3114 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
3115 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '30',
3116 'YYYY/MM/DDHH24:MI'
3117 );
3118 ELSE
3119 l_period_start :=
3120 TO_DATE(
3121 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
3122 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '00',
3123 'YYYY/MM/DDHH24:MI'
3124 );
3125 END IF;
3126 **/
3127
3128 -- Variable to identify the first row of the talk time in the while loop
3129 l_row_counter := 0;
3130
3131 WHILE ( l_period_start < l_end_date )
3132 LOOP
3133
3134 counter := counter + 1;
3135 --IF (g_debug_flag = 'Y') THEN
3136 --write_log('Counter value talk time is '||counter);
3137 --END IF;
3138 --
3139 --These are dimensions which need to be set every time a row is inserted:
3140 --
3141
3142 l_target_record.period_type_id(counter) := 1;
3143
3144 l_target_record.media_id(counter) := l_source_record.media_id(i);
3145 l_target_record.direction(counter) := nvl(l_source_record.direction(i),'N/A');
3146 l_target_record.media_item_type(counter) := nvl(l_source_record.media_item_type(i),'N/A');
3147 l_target_record.classification_value(counter) := nvl(l_source_record.classification_value(i),'N/A');
3148 l_target_record.dnis_name(counter) := nvl(l_source_record.dnis_name(i),'N/A');
3149 l_target_record.server_group_id(counter) := nvl(l_source_record.server_group_id(i),-1);
3150 l_target_record.resource_id(counter) := nvl(l_source_record.resource_id(i),-1);
3151
3152 l_target_record.campaign_id(counter) := nvl(l_prev_campaign_id,-1);
3153 l_target_record.schedule_id(counter) := nvl(l_prev_schedule_id,-1);
3154 l_target_record.source_code_id(counter) := nvl(l_prev_source_code_id,-1);
3155 l_target_record.dialing_method(counter) := nvl(l_prev_dialing_method,'N/A');
3156
3157 l_target_record.outcome_id(counter) := -1;
3158 l_target_record.result_id(counter) := -1;
3159 l_target_record.reason_id(counter) := -1;
3160
3161 l_target_record.party_id(counter) := nvl(l_source_record.party_id(i),-1);
3162
3163 IF l_source_record.direction(i) = 'INBOUND'
3164 THEN
3165 l_target_record.partition_key(counter) := 'IA';
3166 ELSIF l_source_record.direction(i) = 'OUTBOUND'
3167 THEN
3168 l_target_record.partition_key(counter) := 'OA';
3169 END IF;
3170
3171 l_target_record.call_calls_offered_total(counter) := 0;
3172 l_target_record.call_calls_offered_above_th(counter) := 0;
3173 l_target_record.call_calls_abandoned(counter) := 0;
3174 l_target_record.call_calls_abandoned_us(counter) := 0;
3175 l_target_record.call_calls_transferred(counter) := 0;
3176 l_target_record.call_ivr_time(counter) := 0;
3177 l_target_record.call_route_time(counter) := 0;
3178 l_target_record.call_queue_time(counter) := 0;
3179 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := 0;
3180 l_target_record.call_tot_queue_to_answer(counter) := 0;
3181 l_target_record.call_talk_time(counter) := 0;
3182 l_target_record.agent_talk_time_nac(counter) := 0;
3183 l_target_record.agent_wrap_time_nac(counter) := 0;
3184 l_target_record.agent_calls_tran_conf_to_nac(counter) := 0;
3185 l_target_record.agent_cont_calls_hand_na(counter) := 0; --needs to be NULL
3186 l_target_record.agent_cont_calls_tc_na(counter) := 0; --needs to be NULL
3187 l_target_record.agent_calls_handled_total(counter) := 0;
3188 l_target_record.agent_calls_handled_above_th(counter) := 0;
3189 l_target_record.agent_calls_answered_by_goal(counter) := 0;
3190 l_target_record.agent_sr_created(counter) := 0;
3191 l_target_record.agent_leads_created(counter) := 0;
3192 l_target_record.agent_leads_amount(counter) := 0;
3193 l_target_record.agent_leads_converted_to_opp(counter) := 0;
3194 l_target_record.agent_opportunities_created(counter) := 0;
3195 l_target_record.agent_opportunities_won(counter) := 0;
3196 l_target_record.agent_opportunities_won_amount(counter) := 0;
3197 l_target_record.agent_opportunities_cross_sold(counter) := 0;
3198 l_target_record.agent_opportunities_up_sold(counter) := 0;
3199 l_target_record.agent_opportunities_declined(counter) := 0;
3200 l_target_record.agent_opportunities_lost(counter) := 0;
3201 l_target_record.agent_preview_time(counter) := 0;
3202 l_target_record.agentcall_orr_count(counter) := 0;
3203 l_target_record.agentcall_pr_count(counter) := 0;
3204 l_target_record.agentcall_contact_count(counter) := 0;
3205 l_target_record.call_cont_calls_offered_na(counter) := 0;
3206 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 0;
3207 l_target_record.call_calls_hand_tot(counter) := 0;
3208 l_target_record.call_calls_hand_above_th(counter) := 0;
3209 --
3210 --These are the measures which need to be set every time a row is inserted
3211 --
3212
3213 IF (l_row_counter = 0 )
3214 THEN
3215 l_segment_start := l_source_record.agent_segs_start_time(i);
3216 ELSE
3217 l_segment_start := l_period_start;
3218 IF l_transferred_agent = 'N'
3219 THEN
3220 l_target_record.agent_cont_calls_hand_na(counter) := 1;
3221 ELSIF l_transferred_agent = 'Y'
3222 THEN
3223 l_target_record.agent_cont_calls_tc_na(counter) := 1;
3224 END IF;
3225 END IF;
3226
3227 --l_segment_end := l_period_start + 1/48;
3228 l_segment_end := l_period_start + 1;
3229 IF ( l_segment_end > l_end_date )
3230 THEN
3231 l_segment_end := l_end_date ;
3232 END IF;
3233
3234 l_secs := round((l_segment_end - l_segment_start) * 24 * 3600);
3235
3236 --IF (g_debug_flag = 'Y') THEN
3237 --write_log('Completed calculating l_secs value ');
3238 --write_log('Setting agent talk time period start date and time ');
3239 --write_log('l_period_start is ' || l_period_start);
3240 --END IF;
3241
3242 l_target_record.time_id(counter) := to_char(l_period_start,'J');
3243 l_target_record.period_start_date(counter) := trunc(l_period_start);
3244 --l_target_record.period_start_time(counter) := to_char(l_period_start,'HH24:MI');
3245 l_target_record.period_start_time(counter) := '00:00';
3246 l_target_record.day_of_week(counter) := to_char(l_period_start,'D');
3247
3248 --IF (g_debug_flag = 'Y') THEN
3249 --write_log('Setting agent l_agent_talk_time');
3250 --END IF;
3251 l_target_record.agent_talk_time_nac(counter) := l_secs;
3252
3253 l_row_counter := l_row_counter + 1;
3254 --l_period_start := l_period_start + 1/48;
3255 l_period_start := l_period_start + 1;
3256
3257 END LOOP; -- end of WHILE loop for striping talk time across half hour buckets
3258
3259 --IF (g_debug_flag = 'Y') THEN
3260 --write_log('Completed talk time at '||
3261 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
3262 --);
3263 --END IF;
3264
3265 mark_as_processed(l_prev_media_id,'MILCS_ID',l_source_record.agent_milcs_id(i),NULL);
3266
3267 END IF; -- for if does check_if_processed for the segment
3268
3269 IF check_if_processed(l_prev_media_id,'WRAP',l_source_record.resource_id(i),NULL) = 'N'
3270 THEN
3271
3272 --IF (g_debug_flag = 'Y') THEN
3273 --write_log('Started wrap time at '||
3274 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
3275 --);
3276 --END IF;
3277 --
3278 --Calculate wrap time here
3279 --Wrap time will be equal to the max(interaction end date) - max(talk end date)
3280 --for that agent and media.
3281 --
3282
3283 l_begin_date := l_source_record.max_agent_talk_end(i);
3284 l_end_date := l_source_record.max_agent_int_end(i);
3285
3286 /*
3287 --The agent may not perform any wrap up.
3288 --Or he may complete the wrap up before releasing the call.
3289 --So in some cases the interaction end time might be lesser than talk end time.
3290 */
3291
3292 if l_begin_date > l_end_date then
3293 l_begin_date := l_end_date;
3294 end if;
3295
3296 l_period_start := trunc(l_begin_date);
3297 /**
3298 IF TO_NUMBER(TO_CHAR(l_begin_date,'MI')) >= 30
3299 THEN
3300 l_period_start :=
3301 TO_DATE(
3302 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
3303 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '30',
3304 'YYYY/MM/DDHH24:MI'
3305 );
3306 ELSE
3307 l_period_start :=
3308 TO_DATE(
3309 TO_CHAR(l_begin_date,'YYYY/MM/DD')||
3310 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')|| '00',
3311 'YYYY/MM/DDHH24:MI'
3312 );
3313 END IF;
3314 **/
3315
3316 l_row_counter := 0;
3317
3318 WHILE ( l_period_start < l_end_date )
3319 LOOP
3320 IF (l_row_counter = 0 )
3321 THEN
3322 --l_segment_start := l_begin_date;
3323 --l_segment_start := l_max_talk_end_date_time; --CHECK THIS!!
3324 l_segment_start := l_begin_date;
3325 ELSE
3326 --
3327 --Wrap time is being striped to the next half hour bucket
3328 --
3329 l_segment_start := l_period_start;
3330 END IF;
3331
3332 --l_segment_end := l_period_start + 1/48;
3333 l_segment_end := l_period_start + 1;
3334 IF ( l_segment_end > l_end_date )
3335 THEN
3336 l_segment_end := l_end_date ;
3337 END IF;
3338
3339 l_secs := round((l_segment_end - l_segment_start) * 24 * 3600);
3340
3341 counter := counter + 1;
3342
3343 --
3344 --These are the dimensions which need to be set every time a row is inserted:
3345 --
3346
3347 l_target_record.period_type_id(counter) := 1;
3348 l_target_record.media_id(counter) := l_source_record.media_id(i);
3349 l_target_record.direction(counter) := nvl(l_source_record.direction(i),'N/A');
3350 l_target_record.media_item_type(counter) := nvl(l_source_record.media_item_type(i),'N/A');
3351 l_target_record.classification_value(counter) := nvl(l_source_record.classification_value(i),'N/A');
3352 l_target_record.dnis_name(counter) := nvl(l_source_record.dnis_name(i),'N/A');
3353 l_target_record.server_group_id(counter) := nvl(l_source_record.server_group_id(i),-1);
3354 l_target_record.resource_id(counter) := nvl(l_source_record.resource_id(i),-1);
3355
3356 l_target_record.campaign_id(counter) := nvl(l_prev_campaign_id,-1);
3357 l_target_record.schedule_id(counter) := nvl(l_prev_schedule_id,-1);
3358 l_target_record.source_code_id(counter) := nvl(l_prev_source_code_id,-1);
3359 l_target_record.dialing_method(counter) := nvl(l_prev_dialing_method,'N/A');
3360
3361 l_target_record.outcome_id(counter) := -1;
3362 l_target_record.result_id(counter) := -1;
3363 l_target_record.reason_id(counter) := -1;
3364
3365 l_target_record.party_id(counter) := nvl(l_source_record.party_id(i),-1);
3366
3367 IF l_source_record.direction(i) = 'INBOUND'
3368 THEN
3369 l_target_record.partition_key(counter) := 'IA';
3370 ELSIF l_source_record.direction(i) = 'OUTBOUND'
3371 THEN
3372 l_target_record.partition_key(counter) := 'OA';
3373 END IF;
3374
3375 --
3376 --These are the measures which need to be set every time a row is inserted
3377 --
3378 l_target_record.call_calls_offered_total(counter) := 0;
3379 l_target_record.call_calls_offered_above_th(counter) := 0;
3380 l_target_record.call_calls_abandoned(counter) := 0;
3381 l_target_record.call_calls_abandoned_us(counter) := 0;
3382 l_target_record.call_calls_transferred(counter) := 0;
3383 l_target_record.call_ivr_time(counter) := 0;
3384 l_target_record.call_route_time(counter) := 0;
3385 l_target_record.call_queue_time(counter) := 0;
3386 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := 0;
3387 l_target_record.call_tot_queue_to_answer(counter) := 0;
3388 l_target_record.call_talk_time(counter) := 0;
3389 l_target_record.agent_talk_time_nac(counter) := 0;
3390 l_target_record.agent_wrap_time_nac(counter) := 0;
3391 l_target_record.agent_calls_tran_conf_to_nac(counter) := 0;
3392 l_target_record.agent_cont_calls_hand_na(counter) := 0; --needs to be NULL
3393 l_target_record.agent_cont_calls_tc_na(counter) := 0; --needs to be NULL
3394 l_target_record.agent_calls_handled_total(counter) := 0;
3395 l_target_record.agent_calls_handled_above_th(counter) := 0;
3396 l_target_record.agent_calls_answered_by_goal(counter) := 0;
3397 l_target_record.agent_sr_created(counter) := 0;
3398 l_target_record.agent_leads_created(counter) := 0;
3399 l_target_record.agent_leads_amount(counter) := 0;
3400 l_target_record.agent_leads_converted_to_opp(counter) := 0;
3401 l_target_record.agent_opportunities_created(counter) := 0;
3402 l_target_record.agent_opportunities_won(counter) := 0;
3403 l_target_record.agent_opportunities_won_amount(counter) := 0;
3404 l_target_record.agent_opportunities_cross_sold(counter) := 0;
3405 l_target_record.agent_opportunities_up_sold(counter) := 0;
3406 l_target_record.agent_opportunities_declined(counter) := 0;
3407 l_target_record.agent_opportunities_lost(counter) := 0;
3408 l_target_record.agent_preview_time(counter) := 0;
3409 l_target_record.agentcall_orr_count(counter) := 0;
3410 l_target_record.agentcall_pr_count(counter) := 0;
3411 l_target_record.agentcall_contact_count(counter) := 0;
3412 l_target_record.call_cont_calls_offered_na(counter) := 0;
3413 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 0;
3414 l_target_record.call_calls_hand_tot(counter) := 0;
3415 l_target_record.call_calls_hand_above_th(counter) := 0;
3416
3417 --IF (g_debug_flag = 'Y') THEN
3418 --write_log('Setting wrap time period start date and time ');
3419 --write_log('l_period_start is ' || l_period_start);
3420 --END IF;
3421
3422 l_target_record.time_id(counter) := to_char(l_period_start,'J');
3423 l_target_record.period_start_date(counter) := trunc(l_period_start);
3424 --l_target_record.period_start_time(counter) := to_char(l_period_start,'HH24:MI');
3425 l_target_record.period_start_time(counter) := '00:00';
3426 l_target_record.day_of_week(counter) := to_char(l_period_start,'D');
3427
3428 --IF (g_debug_flag = 'Y') THEN
3429 --write_log('Setting l_wrap_time to l_secs ');
3430 --END IF;
3431
3432 l_target_record.agent_wrap_time_nac(counter) := l_secs;
3433
3434 l_row_counter := l_row_counter + 1;
3435 --l_period_start := l_period_start + 1/48;
3436 l_period_start := l_period_start + 1;
3437
3438 END LOOP; -- end of WHILE loop
3439
3440 mark_as_processed(l_prev_media_id,'WRAP', l_source_record.resource_id(i),NULL);
3441
3442 END IF; -- for if which checks if wrap has been calculated for agent
3443
3444 --IF (g_debug_flag = 'Y') THEN
3445 --write_log('Completed wrap time at '||
3446 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
3447 --);
3448 --END IF;
3449
3450 END IF; -- for if which chekcs if this a WITH_AGENT segment
3451 --WRAP HAS to be calculated only if it is a WITH AGENT segment
3452
3453 --IF (g_debug_flag = 'Y') THEN
3454 --write_log('Exited loop which sets wrap time ');
3455 --END IF;
3456
3457 --
3458 --Now set the AGENT level measures which do not require
3459 --splitting across multiple half hour buckets
3460 --
3461
3462 --IF (g_debug_flag = 'Y') THEN
3463 --write_log('Started interaction processing at '||
3464 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
3465 --);
3466 --END IF;
3467
3468 IF l_source_record.int_id(i) IS NOT NULL
3469 AND check_if_processed(l_prev_media_id,'INTERACTION_ID',l_source_record.int_id(i),NULL) = 'N'
3470 THEN
3471
3472 counter := counter+1;
3473 --IF (g_debug_flag = 'Y') THEN
3474 --write_log('Int_id: ' || l_source_record.int_id(i) ||' counter is '||counter);
3475 --END IF;
3476
3477 --
3478 --Within this loop calculate the outcome, result, reason,
3479 --
3480
3481 --
3482 --These are the dimensions which need to be set every time a row is inserted:
3483 --
3484
3485 l_target_record.period_start_time(counter) := '00:00';
3486 /***
3487 IF to_number(to_char(l_source_record.int_start_time(i),'MI')) >= 30
3488 THEN
3489 l_target_record.period_start_time(counter) :=
3490 lpad(to_char(l_source_record.int_start_time(i),'HH24:'),3,'0') ||
3491 '30';
3492 ELSE
3493 l_target_record.period_start_time(counter) :=
3494 lpad(to_char(l_source_record.int_start_time(i),'HH24:'),3,'0') ||
3495 '00';
3496 END IF;
3497 ****/
3498
3499 --IF (g_debug_flag = 'Y') THEN
3500 --write_log('Period start time after '||l_target_record.period_start_time(counter));
3501 --write_log('Int start time is ' || l_source_record.int_start_time(i));
3502 --END IF;
3503
3504 l_target_record.time_id(counter) := to_char(l_source_record.int_start_time(i),'J');
3505 l_target_record.period_type_id(counter) := 1;
3506 l_target_record.period_start_date(counter) := trunc(l_source_record.int_start_time(i));
3507 l_target_record.day_of_week(counter) := to_char(l_source_record.int_start_time(i),'D');
3508 l_target_record.media_id(counter) := l_source_record.media_id(i);
3509 l_target_record.direction(counter) := nvl(l_source_record.direction(i),'N/A');
3510 l_target_record.media_item_type(counter) := nvl(l_source_record.media_item_type(i),'N/A');
3511 l_target_record.resource_id(counter) := nvl(l_source_record.resource_id(i),-1);
3512 l_target_record.classification_value(counter) := nvl(l_source_record.classification_value(i),'N/A');
3513 l_target_record.dnis_name(counter) := nvl(l_source_record.dnis_name(i),'N/A');
3514 l_target_record.server_group_id(counter) := nvl(l_source_record.server_group_id(i),-1);
3515
3516 l_target_record.campaign_id(counter) := nvl(l_prev_campaign_id,-1);
3517 l_target_record.schedule_id(counter) := nvl(l_prev_schedule_id,-1);
3518 l_target_record.source_code_id(counter) := nvl(l_prev_source_code_id,-1);
3519 l_target_record.dialing_method(counter) := nvl(l_prev_dialing_method,'N/A');
3520
3521 l_target_record.party_id(counter) := nvl(l_source_record.party_id(i),-1);
3522
3523 --
3524 --Only for interaction records, it is possible that the
3525 --resource_id at the segment level may be NULL. In this case
3526 --we assign the ORRs to the call level. This is the way it was
3527 --done in DCF. One thing we might miss with this approach is if
3528 --the interaction had a valid resource_id - example for WITHDRAWN
3529 --DURING RINGING then we miss the fact that this particular agent
3530 --initiated the call. This is because this agent does not have a
3531 --WITH_AGENT segment. Might need to look into enhancing this.
3532 --
3533 --Also note that it is not possible to get a report which gives
3534 --call measures such as TALK time by ORR. This is becasue a call
3535 --can have multiple ORR values. There is no way to say which ORR
3536 --combination had which talk times. This is the same in DCF version.
3537 --
3538 IF l_source_record.direction(i) = 'INBOUND'
3539 AND l_source_record.resource_id(i) IS NOT NULL
3540 THEN
3541 l_target_record.partition_key(counter) := 'IA';
3542 ELSIF l_source_record.direction(i) = 'INBOUND'
3543 AND l_source_record.resource_id(i) IS NULL
3544 THEN
3545 l_target_record.partition_key(counter) := 'IC';
3546 ELSIF l_source_record.direction(i) = 'OUTBOUND'
3547 AND l_source_record.resource_id(i) IS NOT NULL
3548 THEN
3549 l_target_record.partition_key(counter) := 'OA';
3550 ELSIF l_source_record.direction(i) = 'OUTBOUND'
3551 AND l_source_record.resource_id(i) IS NULL
3552 THEN
3553 l_target_record.partition_key(counter) := 'OC';
3554 END IF;
3555 l_target_record.call_calls_offered_total(counter) := 0;
3556 l_target_record.call_calls_offered_above_th(counter) := 0;
3557 l_target_record.call_calls_abandoned(counter) := 0;
3558 l_target_record.call_calls_abandoned_us(counter) := 0;
3559 l_target_record.call_calls_transferred(counter) := 0;
3560 l_target_record.call_ivr_time(counter) := 0;
3561 l_target_record.call_route_time(counter) := 0;
3562 l_target_record.call_queue_time(counter) := 0;
3563 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := 0;
3564 l_target_record.call_tot_queue_to_answer(counter) := 0;
3565 l_target_record.call_talk_time(counter) := 0;
3566 l_target_record.agent_talk_time_nac(counter) := 0;
3567 l_target_record.agent_wrap_time_nac(counter) := 0;
3568 l_target_record.agent_calls_tran_conf_to_nac(counter) := 0;
3569 l_target_record.agent_cont_calls_hand_na(counter) := 0; --needs to be NULL
3570 l_target_record.agent_cont_calls_tc_na(counter) := 0; --needs to be NULL
3571 l_target_record.agent_calls_handled_total(counter) := 0;
3572 l_target_record.agent_calls_handled_above_th(counter) := 0;
3573 l_target_record.agent_calls_answered_by_goal(counter) := 0;
3574 l_target_record.agent_sr_created(counter) := 0;
3575 l_target_record.agent_leads_created(counter) := 0;
3576 l_target_record.agent_leads_amount(counter) := 0;
3577 l_target_record.agent_leads_converted_to_opp(counter) := 0;
3578 l_target_record.agent_opportunities_created(counter) := 0;
3579 l_target_record.agent_opportunities_won(counter) := 0;
3580 l_target_record.agent_opportunities_won_amount(counter) := 0;
3581 l_target_record.agent_opportunities_cross_sold(counter) := 0;
3582 l_target_record.agent_opportunities_up_sold(counter) := 0;
3583 l_target_record.agent_opportunities_declined(counter) := 0;
3584 l_target_record.agent_opportunities_lost(counter) := 0;
3585 l_target_record.agent_preview_time(counter) := 0;
3586 l_target_record.agentcall_orr_count(counter) := 0;
3587 l_target_record.agentcall_pr_count(counter) := 0;
3588 l_target_record.agentcall_contact_count(counter) := 0;
3589 l_target_record.call_cont_calls_offered_na(counter) := 0;
3590 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 0;
3591 l_target_record.call_calls_hand_tot(counter) := 0;
3592 l_target_record.call_calls_hand_above_th(counter) := 0;
3593
3594 --IF (g_debug_flag = 'Y') THEN
3595 --write_log('Setting outcome result reason ');
3596 --write_log('Period start time is ' || l_target_record.period_start_time(counter));
3597 --END IF;
3598
3599 l_target_record.outcome_id(counter) := nvl(l_source_record.outcome_id(i),-1);
3600 l_target_record.result_id(counter) := nvl(l_source_record.result_id(i),-1);
3601 l_target_record.reason_id(counter) := nvl(l_source_record.reason_id(i),-1);
3602
3603 l_target_record.agentcall_orr_count(counter) := 1;
3604 --IF (g_debug_flag = 'Y') THEN
3605 --write_log('Set orr count to 1 for counter ' || counter);
3606 --END IF;
3607
3608 IF l_target_record.outcome_id(counter) = 11 --ABANDON
3609 THEN
3610 l_target_record.call_calls_abandoned(counter) := 1;
3611 END IF;
3612
3613 IF l_target_record.outcome_id(counter) = 7
3614 THEN
3615 l_target_record.agentcall_contact_count(counter) := 1;
3616 ELSE
3617 l_target_record.agentcall_contact_count(counter) := 0;
3618 END IF;
3619
3620 SELECT nvl(max(decode(positive_response_flag,'Y',1,0)),0)
3621 INTO l_target_record.agentcall_pr_count(counter)
3622 FROM jtf_ih_results_b
3623 WHERE result_id = l_target_record.result_id(counter);
3624
3625 /* Add count for US Predictive Abandoned rate */
3626 IF l_source_record.abandon_flag(i) = 'U' and l_source_record.direction(i) = 'OUTBOUND'
3627 THEN
3628 l_target_record.call_calls_abandoned_us(counter):= 1;
3629 ELSE
3630 l_target_record.call_calls_abandoned_us(counter) := 0;
3631 END IF;
3632
3633 --IF (g_debug_flag = 'Y') THEN
3634 --write_log('Set pr count to '|| l_target_record.agentcall_pr_count(counter) ||
3635 --' for counter ' || counter);
3636 --END IF;
3637
3638 mark_as_processed(l_prev_media_id,'INTERACTION_ID',l_source_record.int_id(i),NULL);
3639
3640 END IF; -- for if which checks if int_id has been processed
3641
3642 --
3643 --Process business measures
3644 --
3645 IF l_source_record.act_id(i) IS NOT NULL
3646 AND check_if_processed(l_prev_media_id,'ACTIVITY_ID',l_source_record.act_id(i),NULL)='N'
3647 THEN
3648
3649 --
3650 --Take care of case where it might be a fresh set of records from cursor
3651 --in which case you cannot depend on counter value
3652 --
3653 IF counter = 0
3654 THEN
3655 write_log ('Entered counter = 0 if ');
3656
3657 counter := counter+1;
3658 --
3659 --These are the dimensions which need to be set every time a row is inserted:
3660 --
3661 l_target_record.period_start_time(counter) := '00:00';
3662
3663 /***
3664 IF to_number(to_char(l_source_record.int_start_time(i),'MI')) >= 30
3665 THEN
3666 l_target_record.period_start_time(counter) :=
3667 lpad(to_char(l_source_record.int_start_time(i),'HH24:'),3,'0') ||
3668 '30';
3669 ELSE
3670 l_target_record.period_start_time(counter) :=
3671 lpad(to_char(l_source_record.int_start_time(i),'HH24:'),3,'0') ||
3672 '00';
3673 END IF;
3674 ***/
3675
3676 l_target_record.time_id(counter) := to_char(l_source_record.int_start_time(i),'J');
3677 l_target_record.period_type_id(counter) := 1;
3678 l_target_record.period_start_date(counter) := trunc(l_source_record.int_start_time(i));
3679 l_target_record.day_of_week(counter) := to_char(l_source_record.int_start_time(i),'D');
3680 l_target_record.media_id(counter) := l_source_record.media_id(i);
3681 l_target_record.direction(counter) := nvl(l_source_record.direction(i),'N/A');
3682 l_target_record.media_item_type(counter) := nvl(l_source_record.media_item_type(i),'N/A');
3683 l_target_record.resource_id(counter) := nvl(l_source_record.resource_id(i),-1);
3684 l_target_record.classification_value(counter) := nvl(l_source_record.classification_value(i),'N/A');
3685 l_target_record.dnis_name(counter) := nvl(l_source_record.dnis_name(i),'N/A');
3686 l_target_record.server_group_id(counter) := nvl(l_source_record.server_group_id(i),-1);
3687
3688 l_target_record.campaign_id(counter) := nvl(l_prev_campaign_id,-1);
3689 l_target_record.schedule_id(counter) := nvl(l_prev_schedule_id,-1);
3690 l_target_record.source_code_id(counter) := nvl(l_prev_source_code_id,-1);
3691 l_target_record.dialing_method(counter) := nvl(l_prev_dialing_method,'N/A');
3692
3693 l_target_record.party_id(counter) := nvl(l_source_record.party_id(i),-1);
3694
3695 IF l_source_record.direction(i) = 'INBOUND'
3696 AND l_source_record.resource_id(i) IS NOT NULL
3697 THEN
3698 l_target_record.partition_key(counter) := 'IA';
3699 ELSIF l_source_record.direction(i) = 'INBOUND'
3700 AND l_source_record.resource_id(i) IS NULL
3701 THEN
3702 l_target_record.partition_key(counter) := 'IC';
3703 ELSIF l_source_record.direction(i) = 'OUTBOUND'
3704 AND l_source_record.resource_id(i) IS NOT NULL
3705 THEN
3706 l_target_record.partition_key(counter) := 'OA';
3707 ELSIF l_source_record.direction(i) = 'OUTBOUND'
3708 AND l_source_record.resource_id(i) IS NULL
3709 THEN
3710 l_target_record.partition_key(counter) := 'OC';
3711 END IF;
3712 l_target_record.call_calls_offered_total(counter) := 0;
3713 l_target_record.call_calls_offered_above_th(counter) := 0;
3714 l_target_record.call_calls_abandoned(counter) := 0;
3715 l_target_record.call_calls_abandoned_us(counter) := 0;
3716 l_target_record.call_calls_transferred(counter) := 0;
3717 l_target_record.call_ivr_time(counter) := 0;
3718 l_target_record.call_route_time(counter) := 0;
3719 l_target_record.call_queue_time(counter) := 0;
3720 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := 0;
3721 l_target_record.call_tot_queue_to_answer(counter) := 0;
3722 l_target_record.call_talk_time(counter) := 0;
3723 l_target_record.agent_talk_time_nac(counter) := 0;
3724 l_target_record.agent_wrap_time_nac(counter) := 0;
3725 l_target_record.agent_calls_tran_conf_to_nac(counter) := 0;
3726 l_target_record.agent_cont_calls_hand_na(counter) := 0; --needs to be NULL
3727 l_target_record.agent_cont_calls_tc_na(counter) := 0; --needs to be NULL
3728 l_target_record.agent_calls_handled_total(counter) := 0;
3729 l_target_record.agent_calls_handled_above_th(counter) := 0;
3730 l_target_record.agent_calls_answered_by_goal(counter) := 0;
3731 l_target_record.agent_sr_created(counter) := 0;
3732 l_target_record.agent_leads_created(counter) := 0;
3733 l_target_record.agent_leads_amount(counter) := 0;
3734 l_target_record.agent_leads_converted_to_opp(counter) := 0;
3735 l_target_record.agent_opportunities_created(counter) := 0;
3736 l_target_record.agent_opportunities_won(counter) := 0;
3737 l_target_record.agent_opportunities_won_amount(counter) := 0;
3738 l_target_record.agent_opportunities_cross_sold(counter) := 0;
3739 l_target_record.agent_opportunities_up_sold(counter) := 0;
3740 l_target_record.agent_opportunities_declined(counter) := 0;
3741 l_target_record.agent_opportunities_lost(counter) := 0;
3742 l_target_record.agent_preview_time(counter) := 0;
3743 l_target_record.agentcall_orr_count(counter) := 0;
3744 l_target_record.agentcall_pr_count(counter) := 0;
3745 l_target_record.agentcall_contact_count(counter) := 0;
3746 l_target_record.call_cont_calls_offered_na(counter) := 0;
3747 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 0;
3748 l_target_record.call_calls_hand_tot(counter) := 0;
3749 l_target_record.call_calls_hand_above_th(counter) := 0;
3750
3751 l_target_record.outcome_id(counter) := nvl(l_source_record.outcome_id(i),-1);
3752 l_target_record.result_id(counter) := nvl(l_source_record.result_id(i),-1);
3753 l_target_record.reason_id(counter) := nvl(l_source_record.reason_id(i),-1);
3754
3755 END IF; --for counter if
3756 -----------------------------------------------------------------
3757 --
3758 -- Action id values are:
3759 -- 1 = Add, 6=Update, 7=Upsell, 8=Xsell, 13=SR Created
3760 -- 14= SR Updated, 27=Close opportunity
3761 --
3762 -- Action Item Id values are:
3763 -- 8=Lead, 17=SR, 21=Opportunity, 22=Sales Lead
3764 --
3765
3766 if l_source_record.action_id(i) = 1
3767 then -- item added/created
3768 if l_source_record.action_item_id(i) = 22 -- Sales lead
3769 OR l_source_record.action_item_id(i) = 8 -- Lead
3770 then
3771 l_target_record.agent_leads_created(counter) := l_target_record.agent_leads_created(counter)+ 1;
3772 --Start 002 . Comment out call to get_lead_amount
3773 /********
3774 if l_source_record.doc_ref(i) = 'LEAD' or
3775 l_source_record.doc_ref(i) = 'ASTSC_LEAD'
3776 then
3777 get_lead_amount( l_source_record.doc_id(i),
3778 l_target_record.agent_leads_amount(i),
3779 l_currency_code);
3780 end if;
3781 *******/
3782 --End 002 . Comment out call to get_lead_amount
3783 elsif l_source_record.action_item_id(i) = 21
3784 then -- Opportunity
3785 l_target_record.agent_opportunities_created(counter) := 1;
3786 --Start 002 . Comment out call to get_opportunity_amount
3787 /********
3788 if l_source_record.doc_ref(i) = 'OPPORTUNITY' or
3789 l_source_record.doc_ref(i) = 'ASTSC_OPP'
3790 then
3791 get_opportunity_amount( l_source_record.doc_id(i),
3792 l_target_record.agent_opportunities_won(counter),
3793 l_target_record.agent_opportunities_won_amount(counter),
3794 l_currency_code);
3795 end if;
3796 *******/
3797 --End 002 . Comment out call to get_opportunity_amount
3798 elsif l_source_record.action_item_id(i) = 17
3799 then -- Service Request
3800 l_target_record.agent_sr_created(counter) := l_target_record.agent_sr_created(counter)+ 1;
3801 end if;
3802 end if;
3803
3804 -- Service request specific action id value
3805 if l_source_record.action_id(i) = 13
3806 then -- sr created specific code
3807 l_target_record.agent_sr_created(counter) := l_target_record.agent_sr_created(counter)+ 1;
3808 end if;
3809
3810 if l_source_record.action_id(i) = 8
3811 then -- cross sold
3812 if l_source_record.action_item_id(i) = 21
3813 then
3814 l_target_record.agent_opportunities_cross_sold(counter) := l_target_record.agent_opportunities_cross_sold(counter)+1;
3815 end if;
3816 elsif l_source_record.action_id(i) = 7
3817 then -- up sold
3818 if l_source_record.action_item_id(i) = 21
3819 then
3820 l_target_record.agent_opportunities_up_sold(counter) := l_target_record.agent_opportunities_up_sold(counter)+1;
3821 end if;
3822 elsif l_source_record.action_id(i) = 26
3823 then -- declined
3824 if l_source_record.action_item_id(i) = 21
3825 then
3826 l_target_record.agent_opportunities_declined(counter) := l_target_record.agent_opportunities_declined(counter)+1;
3827 end if;
3828 end if;
3829
3830 --IF (g_debug_flag = 'Y') THEN
3831 --write_log('Completed business measure at '||
3832 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
3833 --);
3834 --END IF;
3835 mark_as_processed(l_prev_media_id,'ACTIVITY_ID',l_source_record.act_id(i),NULL);
3836
3837 END IF; -- for if which checks if ACT_ID has been processed
3838 --this is the end of the agent measures
3839 /*****
3840 --
3841 --Process business measures
3842 --
3843 --IF (g_debug_flag = 'Y') THEN
3844 --write_log('Starting business measure at '||
3845 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
3846 --);
3847 --END IF;
3848
3849 IF l_source_record.act_id(i) IS NOT NULL
3850 AND check_if_processed(l_prev_media_id,'ACTIVITY_ID',l_source_record.act_id(i),NULL)='N'
3851 THEN
3852
3853 counter := counter+1;
3854 --
3855 --These are the dimensions which need to be set every time a row is inserted:
3856 --
3857
3858 l_target_record.period_start_time(counter) := '00:00';
3859
3860 l_target_record.time_id(counter) := to_char(l_source_record.int_start_time(i),'J');
3861 l_target_record.period_type_id(counter) := 1;
3862 l_target_record.period_start_date(counter) := trunc(l_source_record.int_start_time(i));
3863 l_target_record.day_of_week(counter) := to_char(l_source_record.int_start_time(i),'D');
3864 l_target_record.media_id(counter) := l_source_record.media_id(i);
3865 l_target_record.direction(counter) := nvl(l_source_record.direction(i),'N/A');
3866 l_target_record.media_item_type(counter) := nvl(l_source_record.media_item_type(i),'N/A');
3867 l_target_record.resource_id(counter) := nvl(l_source_record.resource_id(i),-1);
3868 l_target_record.classification_value(counter) := nvl(l_source_record.classification_value(i),'N/A');
3869 l_target_record.dnis_name(counter) := nvl(l_source_record.dnis_name(i),'N/A');
3870 l_target_record.server_group_id(counter) := nvl(l_source_record.server_group_id(i),-1);
3871
3872 l_target_record.campaign_id(counter) := nvl(l_prev_campaign_id,-1);
3873 l_target_record.schedule_id(counter) := nvl(l_prev_schedule_id,-1);
3874 l_target_record.source_code_id(counter) := nvl(l_prev_source_code_id,-1);
3875 l_target_record.dialing_method(counter) := nvl(l_prev_dialing_method,'N/A');
3876
3877 l_target_record.outcome_id(counter) := nvl(l_source_record.outcome_id(i),-1);
3878 l_target_record.result_id(counter) := nvl(l_source_record.result_id(i),-1);
3879 l_target_record.reason_id(counter) := nvl(l_source_record.reason_id(i),-1);
3880
3881 l_target_record.party_id(counter) := nvl(l_source_record.party_id(i),-1);
3882
3883 IF l_source_record.direction(i) = 'INBOUND'
3884 THEN
3885 l_target_record.partition_key(counter) := 'IA';
3886 ELSIF l_source_record.direction(i) = 'OUTBOUND'
3887 THEN
3888 l_target_record.partition_key(counter) := 'OA';
3889 END IF;
3890 l_target_record.call_calls_offered_total(counter) := 0;
3891 l_target_record.call_calls_offered_above_th(counter) := 0;
3892 l_target_record.call_calls_abandoned(counter) := 0;
3893 l_target_record.call_calls_transferred(counter) := 0;
3894 l_target_record.call_ivr_time(counter) := 0;
3895 l_target_record.call_route_time(counter) := 0;
3896 l_target_record.call_queue_time(counter) := 0;
3897 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(counter) := 0;
3898 l_target_record.call_tot_queue_to_answer(counter) := 0;
3899 l_target_record.call_talk_time(counter) := 0;
3900 l_target_record.agent_talk_time_nac(counter) := 0;
3901 l_target_record.agent_wrap_time_nac(counter) := 0;
3902 l_target_record.agent_calls_tran_conf_to_nac(counter) := 0;
3903 l_target_record.agent_cont_calls_hand_na(counter) := 0; --needs to be NULL
3904 l_target_record.agent_cont_calls_tc_na(counter) := 0; --needs to be NULL
3905 l_target_record.agent_calls_handled_total(counter) := 0;
3906 l_target_record.agent_calls_handled_above_th(counter) := 0;
3907 l_target_record.agent_calls_answered_by_goal(counter) := 0;
3908 l_target_record.agent_sr_created(counter) := 0;
3909 l_target_record.agent_leads_created(counter) := 0;
3910 l_target_record.agent_leads_amount(counter) := 0;
3911 l_target_record.agent_leads_converted_to_opp(counter) := 0;
3912 l_target_record.agent_opportunities_created(counter) := 0;
3913 l_target_record.agent_opportunities_won(counter) := 0;
3914 l_target_record.agent_opportunities_won_amount(counter) := 0;
3915 l_target_record.agent_opportunities_cross_sold(counter) := 0;
3916 l_target_record.agent_opportunities_up_sold(counter) := 0;
3917 l_target_record.agent_opportunities_declined(counter) := 0;
3918 l_target_record.agent_opportunities_lost(counter) := 0;
3919 l_target_record.agent_preview_time(counter) := 0;
3920 l_target_record.agentcall_orr_count(counter) := 0;
3921 l_target_record.agentcall_pr_count(counter) := 0;
3922 l_target_record.agentcall_contact_count(counter) := 0;
3923 l_target_record.call_cont_calls_offered_na(counter) := 0;
3924 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(counter) := 0;
3925 l_target_record.call_calls_hand_tot(counter) := 0;
3926 l_target_record.call_calls_hand_above_th(counter) := 0;
3927
3928 -----------------------------------------------------------------
3929 --
3930 -- Action id values are:
3931 -- 1 = Add, 6=Update, 7=Upsell, 8=Xsell, 13=SR Created
3932 -- 14= SR Updated, 27=Close opportunity
3933 --
3934 -- Action Item Id values are:
3935 -- 8=Lead, 17=SR, 21=Opportunity, 22=Sales Lead
3936 --
3937
3938 if l_source_record.action_id(i) = 1
3939 then -- item added/created
3940 if l_source_record.action_item_id(i) = 22 -- Sales lead
3941 OR l_source_record.action_item_id(i) = 8 -- Lead
3942 then
3943 l_target_record.agent_leads_created(counter) := 1;
3944 if l_source_record.doc_ref(i) = 'LEAD' or
3945 l_source_record.doc_ref(i) = 'ASTSC_LEAD'
3946 then
3947 get_lead_amount( l_source_record.doc_id(i),
3948 l_target_record.agent_leads_amount(i),
3949 l_currency_code);
3950 end if;
3951 elsif l_source_record.action_item_id(i) = 21
3952 then -- Opportunity
3953 l_target_record.agent_opportunities_created(counter) := 1;
3954 if l_source_record.doc_ref(i) = 'OPPORTUNITY' or
3955 l_source_record.doc_ref(i) = 'ASTSC_OPP'
3956 then
3957 get_opportunity_amount( l_source_record.doc_id(i),
3958 l_target_record.agent_opportunities_won(counter),
3959 l_target_record.agent_opportunities_won_amount(counter),
3960 l_currency_code);
3961 end if;
3962 elsif l_source_record.action_item_id(i) = 17
3963 then -- Service Request
3964 l_target_record.agent_sr_created(counter) := 1;
3965 end if;
3966 end if;
3967
3968 -- Service request specific action id value
3969 if l_source_record.action_id(i) = 13
3970 then -- sr created specific code
3971 l_target_record.agent_sr_created(counter) := 1;
3972 end if;
3973
3974 if l_source_record.action_id(i) = 8
3975 then -- cross sold
3976 if l_source_record.action_item_id(i) = 21
3977 then
3978 l_target_record.agent_opportunities_cross_sold(counter) := 1;
3979 end if;
3980 elsif l_source_record.action_id(i) = 7
3981 then -- up sold
3982 if l_source_record.action_item_id(i) = 21
3983 then
3984 l_target_record.agent_opportunities_up_sold(counter) := 1;
3985 end if;
3986 elsif l_source_record.action_id(i) = 26
3987 then -- declined
3988 if l_source_record.action_item_id(i) = 21
3989 then
3990 l_target_record.agent_opportunities_declined(counter) := 1;
3991 end if;
3992 end if;
3993
3994 --IF (g_debug_flag = 'Y') THEN
3995 --write_log('Completed business measure at '||
3996 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
3997 --);
3998 --END IF;
3999 mark_as_processed(l_prev_media_id,'ACTIVITY_ID',l_source_record.act_id(i),NULL);
4000
4001 END IF; -- for if which checks if ACT_ID has been processed
4002 --this is the end of the agent measures
4003
4004 ******/
4005
4006 END LOOP; -- media id cursor loop ends when LIMIT clause is reached
4007
4008 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
4009 --'End loop for PL/SQL processing 5000 medias in FETCH ');
4010
4011 -- Bulk merge all the rows into the staging area
4012
4013 --IF (g_debug_flag = 'Y') THEN
4014 write_log('Starting bulk merge into staging table at '||
4015 to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') ||
4016 'for ' || counter || ' number of rows '
4017 );
4018 --END IF;
4019
4020 --insert into bixtest
4021 --values ('Start merge of '|| counter || 'at ' ||
4022 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
4023 --);
4024
4025 --insert into bixtest
4026 --values ('Merging at ' ||
4027 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
4028 --);
4029
4030 commit;
4031
4032 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
4033 --'Before merge of staging table ');
4034
4035 write_log('Max counter is ' || counter);
4036 write_log('Max value of the collection is '||l_target_record.time_id.COUNT);
4037
4038 --for i in 1 .. counter
4039 --loop
4040 --l_target_record.media_id(i) := 0;
4041 --end loop;
4042
4043 --for i in 1 .. counter
4044 --loop
4045 --write_log('l_target.media_id is ' || nvl(l_target_record.media_id(i),-1));
4046 --write_log('l_target.time_id is ' || l_target_record.time_id(i));
4047 --write_log('l_target.direction is ' || nvl(l_target_record.direction(i),-1));
4048 --end loop;
4049
4050 FORALL k IN 1 .. counter
4051 INSERT /*+ APPEND */
4052 INTO bix_call_details_stg STG
4053 (
4054 time_id ,
4055 period_type_id ,
4056 period_start_date ,
4057 period_start_time ,
4058 day_of_week ,
4059 direction ,
4060 media_item_type ,
4061 resource_id ,
4062 party_id ,
4063 classification_value ,
4064 dnis_name ,
4065 server_group_id ,
4066 campaign_id ,
4067 schedule_id ,
4068 outcome_id ,
4069 result_id ,
4070 reason_id ,
4071 source_code_id ,
4072 dialing_method ,
4073 partition_key ,
4074 call_calls_offered_total ,
4075 call_calls_offered_above_th ,
4076 call_calls_handled_total ,
4077 call_calls_handled_above_th ,
4078 call_calls_abandoned ,
4079 call_calls_abandoned_us ,
4080 call_calls_transferred ,
4081 call_ivr_time ,
4082 call_route_time ,
4083 call_queue_time ,
4084 CALL_TOT_QUEUE_TO_ABANDON ,
4085 call_tot_queue_to_answer ,
4086 call_talk_time ,
4087 CALL_CONT_CALLS_OFFERED_NA ,
4088 CALL_CONT_CALLS_HANDLED_TOT_NA ,
4089 agent_talk_time_nac ,
4090 agent_wrap_time_nac ,
4091 agent_calls_tran_conf_to_nac ,
4092 AGENT_CONT_CALLS_HAND_NA ,
4093 AGENT_CONT_CALLS_TC_NA ,
4094 agent_calls_handled_total ,
4095 agent_calls_handled_above_th ,
4096 agent_calls_answered_by_goal ,
4097 agent_sr_created ,
4098 agent_leads_created ,
4099 agent_leads_amount ,
4100 agent_leads_converted_to_opp ,
4101 agent_opportunities_created ,
4102 agent_opportunities_won ,
4103 agent_opportunities_won_amount ,
4104 agent_opportunities_cross_sold ,
4105 agent_opportunities_up_sold ,
4106 agent_opportunities_declined ,
4107 agent_opportunities_lost ,
4108 agent_preview_time ,
4109 agentcall_orr_count ,
4110 agentcall_pr_count ,
4111 agentcall_contact_count
4112 )
4113 VALUES
4114 (
4115 to_char(l_target_record.period_start_date(k),'J'),
4116 1,
4117 l_target_record.period_start_date(k),
4118 l_target_record.period_start_time(k),
4119 l_target_record.day_of_week(k),
4120 l_target_record.direction(k),
4121 l_target_record.media_item_type(k),
4122 l_target_record.resource_id(k),
4123 l_target_record.party_id(k),
4124 l_target_record.classification_value(k),
4125 l_target_record.dnis_name(k),
4126 l_target_record.server_group_id(k),
4127 l_target_record.campaign_id(k),
4128 l_target_record.schedule_id(k),
4129 l_target_record.outcome_id(k),
4130 l_target_record.result_id(k),
4131 l_target_record.reason_id(k),
4132 l_target_record.source_code_id(k),
4133 l_target_record.dialing_method(k),
4134 l_target_record.partition_key(k),
4135 l_target_record.call_calls_offered_total(k),
4136 l_target_record.call_calls_offered_above_th(k),
4137 l_target_record.call_calls_hand_tot(k),
4138 l_target_record.call_calls_hand_above_th(k),
4139 l_target_record.call_calls_abandoned(k),
4140 l_target_record.call_calls_abandoned_us(k),
4141 l_target_record.call_calls_transferred(k),
4142 l_target_record.call_ivr_time(k),
4143 l_target_record.call_route_time(k),
4144 l_target_record.call_queue_time(k),
4145 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(k),
4146 l_target_record.call_tot_queue_to_answer(k),
4147 l_target_record.call_talk_time(k),
4148 l_target_record.call_cont_calls_offered_na(k),
4149 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(k),
4150 l_target_record.agent_talk_time_nac(k),
4151 l_target_record.agent_wrap_time_nac(k),
4152 l_target_record.agent_calls_tran_conf_to_nac(k),
4153 l_target_record.agent_cont_calls_hand_na(k),
4154 l_target_record.agent_cont_calls_tc_na(k),
4155 l_target_record.agent_calls_handled_total(k),
4156 l_target_record.agent_calls_handled_above_th(k),
4157 l_target_record.agent_calls_answered_by_goal(k),
4158 l_target_record.agent_sr_created(k),
4159 l_target_record.agent_leads_created(k),
4160 l_target_record.agent_leads_amount(k),
4161 l_target_record.agent_leads_converted_to_opp(k),
4162 l_target_record.agent_opportunities_created(k),
4163 l_target_record.agent_opportunities_won(k),
4164 l_target_record.agent_opportunities_won_amount(k),
4165 l_target_record.agent_opportunities_cross_sold(k),
4166 l_target_record.agent_opportunities_up_sold(k),
4167 l_target_record.agent_opportunities_declined(k),
4168 l_target_record.agent_opportunities_lost(k),
4169 l_target_record.agent_preview_time(k),
4170 l_target_record.agentcall_orr_count(k),
4171 l_target_record.agentcall_pr_count(k),
4172 l_target_record.agentcall_contact_count(k)
4173 )
4174 ;
4175 /****
4176 MERGE INTO BIX_CALL_DETAILS_STG STG
4177 USING (
4178 SELECT
4179 --l_target_record.media_id(k) media_id,
4180 to_char(l_target_record.period_start_date(k),'J') time_id,
4181 -1 period_type_id,
4182 l_target_record.period_start_date(k) period_start_date,
4183 l_target_record.period_start_time(k) period_start_time,
4184 l_target_record.day_of_week(k) day_of_week,
4185 l_target_record.direction(k) direction,
4186 l_target_record.media_item_type(k) media_item_type,
4187 l_target_record.resource_id(k) resource_id,
4188 l_target_record.party_id(k) party_id,
4189 l_target_record.classification_value(k) classification_value,
4190 l_target_record.dnis_name(k) dnis_name,
4191 l_target_record.server_group_id(k) server_group_id,
4192 l_target_record.campaign_id(k) campaign_id,
4193 l_target_record.schedule_id(k) schedule_id,
4194 l_target_record.outcome_id(k) outcome_id,
4195 l_target_record.result_id(k) result_id,
4196 l_target_record.reason_id(k) reason_id,
4197 l_target_record.source_code_id(k) source_code_id,
4198 l_target_record.dialing_method(k) dialing_method,
4199 l_target_record.partition_key(k) partition_key,
4200 l_target_record.call_calls_offered_total(k) call_calls_offered_total,
4201 l_target_record.call_calls_offered_above_th(k) call_calls_offered_above_th,
4202 l_target_record.call_calls_hand_tot(k) call_calls_handled_total,
4203 l_target_record.call_calls_hand_above_th(k) call_calls_handled_above_th,
4204 l_target_record.call_calls_abandoned(k) call_calls_abandoned,
4205 l_target_record.call_calls_transferred(k) call_calls_transferred,
4206 l_target_record.call_ivr_time(k) call_ivr_time,
4207 l_target_record.call_route_time(k) call_route_time,
4208 l_target_record.call_queue_time(k) call_queue_time,
4209 l_target_record.CALL_TOT_QUEUE_TO_ABANDON(k) CALL_TOT_QUEUE_TO_ABANDON,
4210 l_target_record.call_tot_queue_to_answer(k) call_tot_queue_to_answer,
4211 l_target_record.call_talk_time(k) call_talk_time,
4212 l_target_record.call_cont_calls_offered_na(k) call_cont_calls_offered_na,
4213 l_target_record.CALL_CONT_CALLS_HANDLED_TOT_NA(k) CALL_CONT_CALLS_HANDLED_TOT_NA,
4214 l_target_record.agent_talk_time_nac(k) agent_talk_time_nac,
4215 l_target_record.agent_wrap_time_nac(k) agent_wrap_time_nac,
4216 l_target_record.agent_calls_tran_conf_to_nac(k) agent_calls_tran_conf_to_nac,
4217 l_target_record.agent_cont_calls_hand_na(k) AGENT_CONT_CALLS_HAND_NA,
4218 l_target_record.agent_cont_calls_tc_na(k) AGENT_CONT_CALLS_TC_NA,
4219 l_target_record.agent_calls_handled_total(k) agent_calls_handled_total,
4220 l_target_record.agent_calls_handled_above_th(k) agent_calls_handled_above_th,
4221 l_target_record.agent_calls_answered_by_goal(k) agent_calls_answered_by_goal,
4222 l_target_record.agent_sr_created(k) agent_sr_created,
4223 l_target_record.agent_leads_created(k) agent_leads_created,
4224 l_target_record.agent_leads_amount(k) agent_leads_amount,
4225 l_target_record.agent_leads_converted_to_opp(k) agent_leads_converted_to_opp,
4226 l_target_record.agent_opportunities_created(k) agent_opportunities_created,
4227 l_target_record.agent_opportunities_won(k) agent_opportunities_won,
4228 l_target_record.agent_opportunities_won_amount(k) agent_opportunities_won_amount,
4229 l_target_record.agent_opportunities_cross_sold(k) agent_opportunities_cross_sold,
4230 l_target_record.agent_opportunities_up_sold(k) agent_opportunities_up_sold,
4231 l_target_record.agent_opportunities_declined(k) agent_opportunities_declined,
4232 l_target_record.agent_opportunities_lost(k) agent_opportunities_lost,
4233 l_target_record.agent_preview_time(k) agent_preview_time,
4234 l_target_record.agentcall_orr_count(k) agentcall_orr_count,
4235 l_target_record.agentcall_pr_count(k) agentcall_pr_count,
4236 l_target_record.agentcall_contact_count(k) agentcall_contact_count
4237 FROM DUAL
4238 ) SUMM
4239 ON
4240 (
4241 stg.time_id = summ.time_id
4242 --AND stg.media_id = summ.media_id
4243 AND stg.period_type_id = summ.period_type_id
4244 AND stg.period_start_date = summ.period_start_date
4245 AND stg.period_start_time = summ.period_start_time
4246 AND stg.day_of_week = summ.day_of_week
4247 AND stg.direction = summ.direction
4248 AND stg.media_item_type = summ.media_item_type
4249 AND stg.resource_id = summ.resource_id
4250 AND stg.party_id = summ.party_id
4251 AND stg.classification_value = summ.classification_value
4252 AND stg.dnis_name = summ.dnis_name
4253 AND stg.server_group_id = summ.server_group_id
4254 AND stg.campaign_id = summ.campaign_id
4255 AND stg.schedule_id = summ.schedule_id
4256 AND stg.outcome_id = summ.outcome_id
4257 AND stg.result_id = summ.result_id
4258 AND stg.reason_id = summ.reason_id
4259 AND stg.source_code_id = summ.source_code_id
4260 AND stg.dialing_method = summ.dialing_method
4261 AND stg.partition_key = summ.partition_key
4262 )
4263 WHEN MATCHED
4264 THEN
4265 UPDATE
4266 SET
4267 stg.call_calls_offered_total = nvl(stg.call_calls_offered_total,0) +
4268 nvl(summ.call_calls_offered_total,0),
4269 stg.call_calls_offered_above_th = nvl(stg.call_calls_offered_above_th,0) +
4270 nvl(summ.call_calls_offered_above_th,0),
4271 stg.call_calls_handled_total = nvl(stg.call_calls_handled_total,0) +
4272 nvl(summ.call_calls_handled_total,0),
4273 stg.call_calls_handled_above_th = nvl(stg.call_calls_handled_above_th,0) +
4274 nvl(summ.call_calls_handled_above_th,0),
4275 stg.call_calls_abandoned = nvl(stg.call_calls_abandoned,0) +
4276 nvl(summ.call_calls_abandoned,0),
4277 stg.call_calls_transferred = nvl(stg.call_calls_transferred,0) +
4278 nvl(summ.call_calls_transferred,0),
4279 stg.call_ivr_time = nvl(stg.call_ivr_time,0) +
4280 nvl(summ.call_ivr_time,0),
4281 stg.call_route_time = nvl(stg.call_route_time,0) +
4282 nvl(summ.call_route_time,0),
4283 stg.call_queue_time = nvl(stg.call_queue_time,0) +
4284 nvl(summ.call_queue_time,0),
4285 stg.CALL_TOT_QUEUE_TO_ABANDON = nvl(stg.CALL_TOT_QUEUE_TO_ABANDON,0) +
4286 nvl(summ.CALL_TOT_QUEUE_TO_ABANDON,0),
4287 stg.call_tot_queue_to_answer = nvl(stg.call_tot_queue_to_answer,0) +
4288 nvl(summ.call_tot_queue_to_answer,0),
4289 stg.call_talk_time = nvl(stg.call_talk_time,0) +
4290 nvl(summ.call_talk_time,0),
4291 stg.call_cont_calls_offered_na = nvl(stg.call_cont_calls_offered_na,0) +
4292 nvl(summ.call_cont_calls_offered_na,0),
4293 stg.call_cont_calls_handled_tot_na = nvl(stg.call_cont_calls_handled_tot_na,0) +
4294 nvl(summ.call_cont_calls_handled_tot_na,0),
4295 stg.agent_talk_time_nac = nvl(stg.agent_talk_time_nac,0) +
4296 nvl(summ.agent_talk_time_nac,0),
4297 stg.agent_wrap_time_nac = nvl(stg.agent_wrap_time_nac,0) +
4298 nvl(summ.agent_wrap_time_nac,0),
4299 stg.agent_calls_tran_conf_to_nac = nvl(stg.agent_calls_tran_conf_to_nac,0) +
4300 nvl(summ.agent_calls_tran_conf_to_nac,0),
4301 stg.AGENT_CONT_CALLS_HAND_NA = nvl(stg.AGENT_CONT_CALLS_HAND_NA,0) +
4302 nvl(summ.AGENT_CONT_CALLS_HAND_NA,0),
4303 stg.AGENT_CONT_CALLS_TC_NA = nvl(stg.AGENT_CONT_CALLS_TC_NA,0) +
4304 nvl(summ.AGENT_CONT_CALLS_TC_NA,0),
4305 stg.agent_calls_handled_total = nvl(stg.agent_calls_handled_total,0) +
4306 nvl(summ.agent_calls_handled_total,0),
4307 stg.agent_calls_handled_above_th = nvl(stg.agent_calls_handled_above_th,0) +
4308 nvl(summ.agent_calls_handled_above_th,0),
4309 stg.agent_calls_answered_by_goal = nvl(stg.agent_calls_answered_by_goal,0) +
4310 nvl(summ.agent_calls_answered_by_goal,0),
4311 stg.agent_sr_created = nvl(stg.agent_sr_created,0) +
4312 nvl(summ.agent_sr_created,0),
4313 stg.agent_leads_created = nvl(stg.agent_leads_created,0) +
4314 nvl(summ.agent_leads_created,0),
4315 stg.agent_leads_amount = nvl(stg.agent_leads_amount,0) +
4316 nvl(summ.agent_leads_amount,0),
4317 stg.agent_leads_converted_to_opp = nvl(stg.agent_leads_converted_to_opp,0) +
4318 nvl(summ.agent_leads_converted_to_opp,0),
4319 stg.agent_opportunities_created = nvl(stg.agent_opportunities_created,0) +
4320 nvl(summ.agent_opportunities_created,0),
4321 stg.agent_opportunities_won = nvl(stg.agent_opportunities_won,0) +
4322 nvl(summ.agent_opportunities_won,0),
4323 stg.agent_opportunities_won_amount = nvl(stg.agent_opportunities_won_amount,0)+
4324 nvl(summ.agent_opportunities_won_amount,0),
4325 stg.agent_opportunities_cross_sold = nvl(stg.agent_opportunities_cross_sold,0) +
4326 nvl(summ.agent_opportunities_cross_sold,0),
4327 stg.agent_opportunities_up_sold = nvl(stg.agent_opportunities_up_sold,0) +
4328 nvl(summ.agent_opportunities_up_sold,0),
4329 stg.agent_opportunities_declined = nvl(stg.agent_opportunities_declined,0) +
4330 nvl(summ.agent_opportunities_declined,0),
4331 stg.agent_opportunities_lost = nvl(stg.agent_opportunities_lost,0) +
4332 nvl(summ.agent_opportunities_lost,0),
4333 stg.agent_preview_time = nvl(stg.agent_preview_time,0) +
4334 nvl(summ.agent_preview_time,0),
4335 stg.agentcall_orr_count = nvl(stg.agentcall_orr_count,0) +
4336 nvl(summ.agentcall_orr_count,0),
4337 stg.agentcall_pr_count = nvl(stg.agentcall_pr_count,0) +
4338 nvl(summ.agentcall_pr_count,0),
4339 stg.agentcall_contact_count = nvl(stg.agentcall_contact_count,0) +
4340 nvl(summ.agentcall_contact_count,0),
4341 stg.last_update_date = g_sysdate,
4342 stg.last_updated_by = g_user_id
4343 WHEN NOT MATCHED
4344 THEN
4345 INSERT
4346 (
4347 --media_id ,
4348 time_id ,
4349 period_type_id ,
4350 period_start_date ,
4351 period_start_time ,
4352 day_of_week ,
4353 direction ,
4354 media_item_type ,
4355 resource_id ,
4356 party_id ,
4357 classification_value ,
4358 dnis_name ,
4359 server_group_id ,
4360 campaign_id ,
4361 schedule_id ,
4362 outcome_id ,
4363 result_id ,
4364 reason_id ,
4365 source_code_id ,
4366 dialing_method ,
4367 partition_key ,
4368 call_calls_offered_total ,
4369 call_calls_offered_above_th ,
4370 call_calls_handled_total ,
4371 call_calls_handled_above_th ,
4372 call_calls_abandoned ,
4373 call_calls_transferred ,
4374 call_ivr_time ,
4375 call_route_time ,
4376 call_queue_time ,
4377 CALL_TOT_QUEUE_TO_ABANDON ,
4378 call_tot_queue_to_answer ,
4379 call_talk_time ,
4380 CALL_CONT_CALLS_OFFERED_NA ,
4381 call_cont_calls_handled_tot_na ,
4382 agent_talk_time_nac ,
4383 agent_wrap_time_nac ,
4384 agent_calls_tran_conf_to_nac ,
4385 AGENT_CONT_CALLS_HAND_NA ,
4386 AGENT_CONT_CALLS_TC_NA ,
4387 agent_calls_handled_total ,
4388 agent_calls_handled_above_th ,
4389 agent_calls_answered_by_goal ,
4390 agent_sr_created ,
4391 agent_leads_created ,
4392 agent_leads_amount ,
4393 agent_leads_converted_to_opp ,
4394 agent_opportunities_created ,
4395 agent_opportunities_won ,
4396 agent_opportunities_won_amount ,
4397 agent_opportunities_cross_sold ,
4398 agent_opportunities_up_sold ,
4399 agent_opportunities_declined ,
4400 agent_opportunities_lost ,
4401 agent_preview_time ,
4402 agentcall_orr_count ,
4403 agentcall_pr_count ,
4404 agentcall_contact_count ,
4405 created_by ,
4406 creation_date ,
4407 last_updated_by ,
4408 last_update_date ,
4409 last_update_login ,
4410 request_id ,
4411 program_application_id ,
4412 program_id ,
4413 program_update_date
4414 )
4415 values
4416 (
4417 --summ.media_id ,
4418 summ.time_id ,
4419 summ.period_type_id ,
4420 summ.period_start_date ,
4421 summ.period_start_time ,
4422 summ.day_of_week ,
4423 summ.direction ,
4424 summ.media_item_type ,
4425 summ.resource_id ,
4426 summ.party_id ,
4427 summ.classification_value ,
4428 summ.dnis_name ,
4429 summ.server_group_id ,
4430 summ.campaign_id ,
4431 summ.schedule_id ,
4432 summ.outcome_id ,
4433 summ.result_id ,
4434 summ.reason_id ,
4435 summ.source_code_id ,
4436 summ.dialing_method ,
4437 nvl(summ.partition_key,'NA') ,
4438 summ.call_calls_offered_total ,
4439 summ.call_calls_offered_above_th ,
4440 summ.CALL_CALLS_HANDLED_TOTAL ,
4441 summ.CALL_CALLS_HANDLED_ABOVE_TH ,
4442 summ.call_calls_abandoned ,
4443 summ.call_calls_transferred ,
4444 summ.call_ivr_time ,
4445 summ.call_route_time ,
4446 summ.call_queue_time ,
4447 summ.CALL_TOT_QUEUE_TO_ABANDON ,
4448 summ.call_tot_queue_to_answer ,
4449 summ.call_talk_time ,
4450 summ.CALL_CONT_CALLS_OFFERED_NA ,
4451 summ.CALL_CONT_CALLS_HANDLED_TOT_NA ,
4452 summ.agent_talk_time_nac ,
4453 summ.agent_wrap_time_nac ,
4454 summ.agent_calls_tran_conf_to_nac ,
4455 summ.AGENT_CONT_CALLS_HAND_NA ,
4456 summ.AGENT_CONT_CALLS_TC_NA ,
4457 summ.agent_calls_handled_total ,
4458 summ.agent_calls_handled_above_th ,
4459 summ.agent_calls_answered_by_goal ,
4460 summ.agent_sr_created ,
4461 summ.agent_leads_created ,
4462 summ.agent_leads_amount ,
4463 summ.agent_leads_converted_to_opp ,
4464 summ.agent_opportunities_created ,
4465 summ.agent_opportunities_won ,
4466 summ.agent_opportunities_won_amount ,
4467 summ.agent_opportunities_cross_sold ,
4468 summ.agent_opportunities_up_sold ,
4469 summ.agent_opportunities_declined ,
4470 summ.agent_opportunities_lost ,
4471 summ.agent_preview_time ,
4472 summ.agentcall_orr_count ,
4473 summ.agentcall_pr_count ,
4474 summ.agentcall_contact_count ,
4475 g_user_id ,
4476 g_sysdate ,
4477 g_user_id ,
4478 g_sysdate ,
4479 g_user_id ,
4480 g_request_id ,
4481 g_program_appl_id ,
4482 g_program_id ,
4483 g_sysdate
4484 );
4485 ****/
4486
4487 --insert into bixtest
4488 --values ('Completed merge of '|| counter || 'at ' ||
4489 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
4490 --);
4491
4492 COMMIT;
4493
4494 write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
4495 'Completed merge of staging table ');
4496
4497 --IF (g_debug_flag = 'Y') THEN
4498 --write_log('Completed bulk merge into staging table at '||
4499 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') ||
4500 --'for ' || counter || ' number of rows '
4501 --);
4502 --END IF;
4503
4504 END IF; -- for if which checks if l_source_record.media_id.COUNT > 0
4505
4506 --Trim all table collections
4507
4508 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
4509 --'Before initializing record of tables ');
4510
4511 l_source_record := l_source_null_record;
4512 l_target_record := l_target_null_record;
4513
4514 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
4515 --'After initializing record of tables ');
4516 --
4517
4518 -----
4519
4520 EXIT WHEN get_call_info%NOTFOUND;
4521
4522 END LOOP; -- end loop for fetching all the cursor records.
4523
4524 write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
4525 'Ended loop of all medias in cursor ');
4526
4527 CLOSE get_call_info;
4528
4529 --DBMS_PROFILER.STOP_PROFILER;
4530
4531 --IF (g_debug_flag = 'Y') THEN
4532 --write_log('Finished procedure insert_half_hour_rows at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
4533 --END IF;
4534 EXCEPTION
4535 WHEN OTHERS THEN
4536 --IF (g_debug_flag = 'Y') THEN
4537 write_log('Error in insert_half_hour_rows : Error : ' || sqlerrm);
4538 --l_test := SQLERRM;
4539 --insert into bixtest values ('Error: ' || l_test);
4540 --commit;
4541 --END IF;
4542 IF (get_call_info%ISOPEN) THEN
4543 CLOSE get_call_info;
4544 END IF;
4545 RAISE;
4546
4547 --DBMS_PROFILER.STOP_PROFILER;
4548
4549 --insert into bixtest
4550 --values ('Ended insert_half_hour at ' ||
4551 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss')
4552 --);
4553
4554 commit;
4555
4556 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
4557 --'End insert_half_hour_rows ');
4558
4559 END insert_half_hour_rows;
4560
4561 PROCEDURE move_stg_to_fact IS
4562
4563 BEGIN
4564
4565 --IF (g_debug_flag = 'Y') THEN
4566 --write_log('Start of the procedure move_stg_to_fact at : ' ||
4567 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
4568 --END IF;
4569
4570 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
4571 --'Start move_stg_to_fact ');
4572
4573 --
4574 --Move the data from the staging area to the summary table BIX_CALL_DETAILS_F
4575 --
4576
4577 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
4578 --'Before analyze ');
4579
4580 --ANalyze fact before merge
4581 --DBMS_STATS.gather_table_stats(ownname => g_bix_schema,
4582 --tabName => 'BIX_CALL_DETAILS_F',
4583 --cascade => TRUE,
4584 --degree => bis_common_parameters.get_degree_of_parallelism,
4585 --estimate_percent => 10,
4586 --granularity => 'GLOBAL');
4587
4588 --execute immediate 'analyze index bix.bix_sum_n1 compute statistics';
4589 --execute immediate 'analyze index bixbitmap compute statistics';
4590
4591 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
4592 --'After analyze complete ');
4593
4594 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
4595 --'Starting merge to fact from staging ');
4596 --
4597 --This can be a INSERT APPEND only in initial load, not in incremental.
4598 --
4599 /*
4600 INSERT
4601 INTO BIX_CALL_DETAILS_F SUMM
4602 (
4603 TIME_ID,
4604 PERIOD_TYPE_ID,
4605 PERIOD_START_DATE,
4606 PERIOD_START_TIME,
4607 DAY_OF_WEEK,
4608 DIRECTION,
4609 MEDIA_ITEM_TYPE,
4610 CLASSIFICATION_VALUE,
4611 DNIS_NAME,
4612 SERVER_GROUP_ID,
4613 RESOURCE_ID,
4614 CAMPAIGN_ID,
4615 SCHEDULE_ID,
4616 SOURCE_CODE_ID,
4617 DIALING_METHOD,
4618 OUTCOME_ID,
4619 RESULT_ID,
4620 REASON_ID,
4621 PARTY_ID,
4622 PARTITION_KEY,
4623 CALL_CALLS_OFFERED_TOTAL,
4624 CALL_CALLS_OFFERED_ABOVE_TH,
4625 CALL_CALLS_HANDLED_TOTAL,
4626 CALL_CALLS_HANDLED_ABOVE_TH,
4627 CALL_CALLS_ABANDONED,
4628 CALL_CALLS_TRANSFERRED,
4629 CALL_IVR_TIME,
4630 CALL_ROUTE_TIME,
4631 CALL_QUEUE_TIME,
4632 CALL_TOT_QUEUE_TO_ABANDON,
4633 CALL_TOT_QUEUE_TO_ANSWER,
4634 CALL_TALK_TIME,
4635 CALL_CONT_CALLS_OFFERED_NA,
4636 CALL_CONT_CALLS_HANDLED_TOT_NA,
4637 AGENT_TALK_TIME_NAC,
4638 AGENT_WRAP_TIME_NAC,
4639 AGENT_CALLS_TRAN_CONF_TO_NAC,
4640 AGENT_CALLS_HANDLED_TOTAL,
4641 AGENT_CALLS_HANDLED_ABOVE_TH,
4642 AGENT_CALLS_ANSWERED_BY_GOAL,
4643 AGENT_SR_CREATED,
4644 AGENT_LEADS_CREATED,
4645 AGENT_LEADS_AMOUNT,
4646 AGENT_LEADS_CONVERTED_TO_OPP,
4647 AGENT_OPPORTUNITIES_CREATED,
4648 AGENT_OPPORTUNITIES_WON,
4649 AGENT_OPPORTUNITIES_WON_AMOUNT,
4650 AGENT_OPPORTUNITIES_CROSS_SOLD,
4651 AGENT_OPPORTUNITIES_UP_SOLD,
4652 AGENT_OPPORTUNITIES_DECLINED,
4653 AGENT_OPPORTUNITIES_LOST,
4654 AGENT_PREVIEW_TIME,
4655 AGENTCALL_ORR_COUNT,
4656 AGENTCALL_PR_COUNT,
4657 AGENTCALL_CONTACT_COUNT,
4658 AGENT_CONT_CALLS_HAND_NA,
4659 AGENT_CONT_CALLS_TC_NA
4660 )
4661 SELECT
4662 TIME_ID,
4663 PERIOD_TYPE_ID,
4664 PERIOD_START_DATE,
4665 PERIOD_START_TIME,
4666 DAY_OF_WEEK,
4667 DIRECTION,
4668 MEDIA_ITEM_TYPE,
4669 CLASSIFICATION_VALUE,
4670 DNIS_NAME,
4671 SERVER_GROUP_ID,
4672 RESOURCE_ID,
4673 CAMPAIGN_ID,
4674 SCHEDULE_ID,
4675 SOURCE_CODE_ID,
4676 DIALING_METHOD,
4677 OUTCOME_ID,
4678 RESULT_ID,
4679 REASON_ID,
4680 PARTY_ID,
4681 PARTITION_KEY,
4682 SUM(CALL_CALLS_OFFERED_TOTAL) CALL_CALLS_OFFERED_TOTAL,
4683 SUM(CALL_CALLS_OFFERED_ABOVE_TH) CALL_CALLS_OFFERED_ABOVE_TH,
4684 SUM(CALL_CALLS_HANDLED_TOTAL) CALL_CALLS_HANDLED_TOTAL,
4685 SUM(CALL_CALLS_HANDLED_ABOVE_TH) CALL_CALLS_HANDLED_ABOVE_TH,
4686 SUM(CALL_CALLS_ABANDONED) CALL_CALLS_ABANDONED,
4687 SUM(CALL_CALLS_TRANSFERRED) CALL_CALLS_TRANSFERRED,
4688 SUM(CALL_IVR_TIME) CALL_IVR_TIME,
4689 SUM(CALL_ROUTE_TIME) CALL_ROUTE_TIME,
4690 SUM(CALL_QUEUE_TIME) CALL_QUEUE_TIME,
4691 SUM(CALL_TOT_QUEUE_TO_ABANDON) CALL_TOT_QUEUE_TO_ABANDON,
4692 SUM(CALL_TOT_QUEUE_TO_ANSWER) CALL_TOT_QUEUE_TO_ANSWER,
4693 SUM(CALL_TALK_TIME) CALL_TALK_TIME,
4694 SUM(CALL_CONT_CALLS_OFFERED_NA) CALL_CONT_CALLS_OFFERED_NA,
4695 SUM(CALL_CONT_CALLS_HANDLED_TOT_NA) CALL_CONT_CALLS_HANDLED_TOT_NA,
4696 SUM(AGENT_TALK_TIME_NAC) AGENT_TALK_TIME_NAC,
4697 SUM(AGENT_WRAP_TIME_NAC) AGENT_WRAP_TIME_NAC,
4698 SUM(AGENT_CALLS_TRAN_CONF_TO_NAC) AGENT_CALLS_TRAN_CONF_TO_NAC,
4699 SUM(AGENT_CALLS_HANDLED_TOTAL) AGENT_CALLS_HANDLED_TOTAL,
4700 SUM(AGENT_CALLS_HANDLED_ABOVE_TH) AGENT_CALLS_HANDLED_ABOVE_TH,
4701 SUM(AGENT_CALLS_ANSWERED_BY_GOAL) AGENT_CALLS_ANSWERED_BY_GOAL,
4702 SUM(AGENT_SR_CREATED) AGENT_SR_CREATED,
4703 SUM(AGENT_LEADS_CREATED) AGENT_LEADS_CREATED,
4704 SUM(AGENT_LEADS_AMOUNT) AGENT_LEADS_AMOUNT,
4705 SUM(AGENT_LEADS_CONVERTED_TO_OPP) AGENT_LEADS_CONVERTED_TO_OPP,
4706 SUM(AGENT_OPPORTUNITIES_CREATED) AGENT_OPPORTUNITIES_CREATED,
4707 SUM(AGENT_OPPORTUNITIES_WON) AGENT_OPPORTUNITIES_WON,
4708 SUM(AGENT_OPPORTUNITIES_WON_AMOUNT) AGENT_OPPORTUNITIES_WON_AMOUNT,
4709 SUM(AGENT_OPPORTUNITIES_CROSS_SOLD) AGENT_OPPORTUNITIES_CROSS_SOLD,
4710 SUM(AGENT_OPPORTUNITIES_UP_SOLD) AGENT_OPPORTUNITIES_UP_SOLD,
4711 SUM(AGENT_OPPORTUNITIES_DECLINED) AGENT_OPPORTUNITIES_DECLINED,
4712 SUM(AGENT_OPPORTUNITIES_LOST) AGENT_OPPORTUNITIES_LOST,
4713 SUM(AGENT_PREVIEW_TIME) AGENT_PREVIEW_TIME,
4714 SUM(AGENTCALL_ORR_COUNT) AGENTCALL_ORR_COUNT,
4715 SUM(AGENTCALL_PR_COUNT) AGENTCALL_PR_COUNT,
4716 SUM(AGENTCALL_CONTACT_COUNT) AGENTCALL_CONTACT_COUNT,
4717 NVL(SUM(AGENT_CONT_CALLS_HAND_NA),-1) AGENT_CONT_CALLS_HAND_NA
4718 FROM bix_call_details_stg stg
4719 GROUP BY
4720 TIME_ID,
4721 PERIOD_TYPE_ID,
4722 PERIOD_START_DATE,
4723 PERIOD_START_TIME,
4724 DAY_OF_WEEK,
4725 DIRECTION,
4726 MEDIA_ITEM_TYPE,
4727 CLASSIFICATION_VALUE,
4728 DNIS_NAME,
4729 SERVER_GROUP_ID,
4730 RESOURCE_ID,
4731 CAMPAIGN_ID,
4732 SCHEDULE_ID,
4733 SOURCE_CODE_ID,
4734 DIALING_METHOD,
4735 OUTCOME_ID,
4736 RESULT_ID,
4737 REASON_ID,
4738 PARTY_ID,
4739 PARTITION_KEY
4740 ;
4741 */
4742 MERGE INTO BIX_CALL_DETAILS_F SUMM
4743 USING
4744 (
4745 SELECT
4746 TIME_ID,
4747 PERIOD_TYPE_ID,
4748 PERIOD_START_DATE,
4749 PERIOD_START_TIME,
4750 DAY_OF_WEEK,
4751 DIRECTION,
4752 MEDIA_ITEM_TYPE,
4753 CLASSIFICATION_VALUE,
4754 DNIS_NAME,
4755 SERVER_GROUP_ID,
4756 RESOURCE_ID,
4757 CAMPAIGN_ID,
4758 SCHEDULE_ID,
4759 SOURCE_CODE_ID,
4760 DIALING_METHOD,
4761 OUTCOME_ID,
4762 RESULT_ID,
4763 REASON_ID,
4764 PARTY_ID,
4765 PARTITION_KEY,
4766 SUM(CALL_CALLS_OFFERED_TOTAL) CALL_CALLS_OFFERED_TOTAL,
4767 SUM(CALL_CALLS_OFFERED_ABOVE_TH) CALL_CALLS_OFFERED_ABOVE_TH,
4768 SUM(CALL_CALLS_HANDLED_TOTAL) CALL_CALLS_HANDLED_TOTAL,
4769 SUM(CALL_CALLS_HANDLED_ABOVE_TH) CALL_CALLS_HANDLED_ABOVE_TH,
4770 SUM(CALL_CALLS_ABANDONED) CALL_CALLS_ABANDONED,
4771 SUM(CALL_CALLS_ABANDONED_US) CALL_CALLS_ABANDONED_US,
4772 SUM(CALL_CALLS_TRANSFERRED) CALL_CALLS_TRANSFERRED,
4773 SUM(CALL_IVR_TIME) CALL_IVR_TIME,
4774 SUM(CALL_ROUTE_TIME) CALL_ROUTE_TIME,
4775 SUM(CALL_QUEUE_TIME) CALL_QUEUE_TIME,
4776 SUM(CALL_TOT_QUEUE_TO_ABANDON) CALL_TOT_QUEUE_TO_ABANDON,
4777 SUM(CALL_TOT_QUEUE_TO_ANSWER) CALL_TOT_QUEUE_TO_ANSWER,
4778 SUM(CALL_TALK_TIME) CALL_TALK_TIME,
4779 --decode(PERIOD_TYPE_ID,-1,SUM(CALL_CONT_CALLS_OFFERED_NA),0) CALL_CONT_CALLS_OFFERED_NA,
4780 --decode(period_type_id,-1,SUM(CALL_CONT_CALLS_HANDLED_TOT_NA),0) CALL_CONT_CALLS_HANDLED_TOT_NA,
4781 decode(PERIOD_TYPE_ID,1,SUM(CALL_CONT_CALLS_OFFERED_NA),0) CALL_CONT_CALLS_OFFERED_NA,
4782 decode(period_type_id,1,SUM(CALL_CONT_CALLS_HANDLED_TOT_NA),0) CALL_CONT_CALLS_HANDLED_TOT_NA,
4783 SUM(AGENT_TALK_TIME_NAC) AGENT_TALK_TIME_NAC,
4784 SUM(AGENT_WRAP_TIME_NAC) AGENT_WRAP_TIME_NAC,
4785 SUM(AGENT_CALLS_TRAN_CONF_TO_NAC) AGENT_CALLS_TRAN_CONF_TO_NAC,
4786 SUM(AGENT_CALLS_HANDLED_TOTAL) AGENT_CALLS_HANDLED_TOTAL,
4787 SUM(AGENT_CALLS_HANDLED_ABOVE_TH) AGENT_CALLS_HANDLED_ABOVE_TH,
4788 SUM(AGENT_CALLS_ANSWERED_BY_GOAL) AGENT_CALLS_ANSWERED_BY_GOAL,
4789 SUM(AGENT_SR_CREATED) AGENT_SR_CREATED,
4790 SUM(AGENT_LEADS_CREATED) AGENT_LEADS_CREATED,
4791 SUM(AGENT_LEADS_AMOUNT) AGENT_LEADS_AMOUNT,
4792 SUM(AGENT_LEADS_CONVERTED_TO_OPP) AGENT_LEADS_CONVERTED_TO_OPP,
4793 SUM(AGENT_OPPORTUNITIES_CREATED) AGENT_OPPORTUNITIES_CREATED,
4794 SUM(AGENT_OPPORTUNITIES_WON) AGENT_OPPORTUNITIES_WON,
4795 SUM(AGENT_OPPORTUNITIES_WON_AMOUNT) AGENT_OPPORTUNITIES_WON_AMOUNT,
4796 SUM(AGENT_OPPORTUNITIES_CROSS_SOLD) AGENT_OPPORTUNITIES_CROSS_SOLD,
4797 SUM(AGENT_OPPORTUNITIES_UP_SOLD) AGENT_OPPORTUNITIES_UP_SOLD,
4798 SUM(AGENT_OPPORTUNITIES_DECLINED) AGENT_OPPORTUNITIES_DECLINED,
4799 SUM(AGENT_OPPORTUNITIES_LOST) AGENT_OPPORTUNITIES_LOST,
4800 SUM(AGENT_PREVIEW_TIME) AGENT_PREVIEW_TIME,
4801 SUM(AGENTCALL_ORR_COUNT) AGENTCALL_ORR_COUNT,
4802 SUM(AGENTCALL_PR_COUNT) AGENTCALL_PR_COUNT,
4803 SUM(AGENTCALL_CONTACT_COUNT) AGENTCALL_CONTACT_COUNT,
4804 --decode(period_type_id,-1,SUM(AGENT_CONT_CALLS_HAND_NA),-1) AGENT_CONT_CALLS_HAND_NA,
4805 --decode(period_type_id,-1,SUM(AGENT_CONT_CALLS_TC_NA),0) AGENT_CONT_CALLS_TC_NA
4806 decode(period_type_id,1,SUM(AGENT_CONT_CALLS_HAND_NA),-1) AGENT_CONT_CALLS_HAND_NA,
4807 decode(period_type_id,1,SUM(AGENT_CONT_CALLS_TC_NA),0) AGENT_CONT_CALLS_TC_NA
4808 FROM bix_call_details_stg stg
4809 GROUP BY
4810 TIME_ID,
4811 PERIOD_TYPE_ID,
4812 PERIOD_START_DATE,
4813 PERIOD_START_TIME,
4814 DAY_OF_WEEK,
4815 DIRECTION,
4816 MEDIA_ITEM_TYPE,
4817 CLASSIFICATION_VALUE,
4818 DNIS_NAME,
4819 SERVER_GROUP_ID,
4820 RESOURCE_ID,
4821 CAMPAIGN_ID,
4822 SCHEDULE_ID,
4823 SOURCE_CODE_ID,
4824 DIALING_METHOD,
4825 OUTCOME_ID,
4826 RESULT_ID,
4827 REASON_ID,
4828 PARTY_ID,
4829 PARTITION_KEY
4830 ) STG
4831 ON
4832 (
4833 summ.PERIOD_TYPE_ID = stg.PERIOD_TYPE_ID
4834 AND summ.PERIOD_START_DATE = stg.PERIOD_START_DATE
4835 AND summ.PERIOD_START_TIME = stg.PERIOD_START_TIME
4836 AND summ.DAY_OF_WEEK =stg.DAY_OF_WEEK
4837 AND summ.PARTITION_KEY = stg.PARTITION_KEY
4838 AND summ.DIRECTION = stg.DIRECTION
4839 AND summ.MEDIA_ITEM_TYPE=stg.MEDIA_ITEM_TYPE
4840 AND summ.RESOURCE_ID = stg.RESOURCE_ID
4841 AND summ.PARTY_ID = stg.PARTY_ID
4842 AND summ.CLASSIFICATION_VALUE = stg.CLASSIFICATION_VALUE
4843 AND summ.DNIS_NAME = stg.DNIS_NAME
4844 AND summ.SERVER_GROUP_ID = stg.SERVER_GROUP_ID
4845 AND summ.CAMPAIGN_ID = stg.CAMPAIGN_ID
4846 AND summ.SCHEDULE_ID = stg.SCHEDULE_ID
4847 AND summ.OUTCOME_ID = stg.OUTCOME_ID
4848 AND summ.RESULT_ID = stg.RESULT_ID
4849 AND summ.REASON_ID = stg.REASON_ID
4850 AND summ.SOURCE_CODE_ID = stg.SOURCE_CODE_ID
4851 AND summ.DIALING_METHOD = stg.DIALING_METHOD
4852 AND summ.TIME_ID = stg.TIME_ID
4853 )
4854 WHEN MATCHED
4855 THEN
4856 UPDATE
4857 SET
4858 summ.call_calls_offered_total = nvl(summ.call_calls_offered_total,0) + nvl(stg.call_calls_offered_total,0),
4859 summ.call_calls_offered_above_th = nvl(summ.call_calls_offered_above_th,0) +nvl(stg.call_calls_offered_above_th,0),
4860 summ.call_calls_handled_total = nvl(summ.call_calls_handled_total,0) +
4861 nvl(stg.call_calls_handled_total,0),
4862 summ.call_calls_handled_above_th = nvl(summ.call_calls_handled_above_th,0) +
4863 nvl(stg.call_calls_handled_above_th,0),
4864 summ.call_calls_abandoned = nvl(summ.call_calls_abandoned,0) + nvl(stg.call_calls_abandoned,0),
4865 summ.call_calls_abandoned_us = nvl(summ.call_calls_abandoned_us,0) + nvl(stg.call_calls_abandoned_us,0),
4866 summ.call_calls_transferred = nvl(summ.call_calls_transferred,0) + nvl(stg.call_calls_transferred,0),
4867 summ.call_ivr_time = nvl(summ.call_ivr_time,0) + nvl(stg.call_ivr_time,0),
4868 summ.call_route_time = nvl(summ.call_route_time,0) + nvl(stg.call_route_time,0),
4869 summ.call_queue_time = nvl(summ.call_queue_time,0) + nvl(stg.call_queue_time,0),
4870 summ.CALL_TOT_QUEUE_TO_ABANDON = nvl(summ.CALL_TOT_QUEUE_TO_ABANDON,0) + nvl(stg.CALL_TOT_QUEUE_TO_ABANDON,0),
4871 summ.call_tot_queue_to_answer = nvl(summ.call_tot_queue_to_answer,0) + nvl(stg.call_tot_queue_to_answer,0),
4872 summ.call_talk_time = nvl(summ.call_talk_time,0) + nvl(stg.call_talk_time,0),
4873 summ.call_cont_calls_offered_na = nvl(summ.call_cont_calls_offered_na,0) +
4874 nvl(stg.call_cont_calls_offered_na,0),
4875 summ.CALL_CONT_CALLS_HANDLED_TOT_NA = nvl(summ.CALL_CONT_CALLS_HANDLED_TOT_NA,0) +
4876 nvl(stg.CALL_CONT_CALLS_HANDLED_TOT_NA,0),
4877 summ.agent_talk_time_nac = nvl(summ.agent_talk_time_nac,0) + nvl(stg.agent_talk_time_nac,0),
4878 summ.agent_wrap_time_nac = nvl(summ.agent_wrap_time_nac,0) + nvl(stg.agent_wrap_time_nac,0),
4879 summ.agent_calls_tran_conf_to_nac = nvl(summ.agent_calls_tran_conf_to_nac,0) + nvl(stg.agent_calls_tran_conf_to_nac,0),
4880 summ.AGENT_CONT_CALLS_HAND_NA = nvl(summ.AGENT_CONT_CALLS_HAND_NA,0) + nvl(stg.AGENT_CONT_CALLS_HAND_NA,0),
4881 summ.AGENT_CONT_CALLS_TC_NA = nvl(summ.AGENT_CONT_CALLS_TC_NA,0) + nvl(stg.AGENT_CONT_CALLS_TC_NA,0),
4882 summ.agent_calls_handled_total = nvl(summ.agent_calls_handled_total,0) + nvl(stg.agent_calls_handled_total,0),
4883 summ.agent_calls_handled_above_th = nvl(summ.agent_calls_handled_above_th,0) + nvl(stg.agent_calls_handled_above_th,0),
4884 summ.agent_calls_answered_by_goal = nvl(summ.agent_calls_answered_by_goal,0) + nvl(stg.agent_calls_answered_by_goal,0),
4885 summ.agent_sr_created = nvl(summ.agent_sr_created,0) + nvl(stg.agent_sr_created,0),
4886 summ.agent_leads_created = nvl(summ.agent_leads_created,0) + nvl(stg.agent_leads_created,0),
4887 summ.agent_leads_amount = nvl(summ.agent_leads_amount,0) + nvl(stg.agent_leads_amount,0),
4888 summ.agent_leads_converted_to_opp = nvl(summ.agent_leads_converted_to_opp,0) + nvl(stg.agent_leads_converted_to_opp,0),
4889 summ.agent_opportunities_created = nvl(summ.agent_opportunities_created,0) + nvl(stg.agent_opportunities_created,0),
4890 summ.agent_opportunities_won = nvl(summ.agent_opportunities_won,0) + nvl(stg.agent_OPPORTUNITIES_won,0),
4891 summ.agent_opportunities_won_amount = nvl(summ.agent_opportunities_won_amount,0)+nvl(stg.agent_opportunities_won_amount,0),
4892 summ.agent_opportunities_cross_sold = nvl(summ.agent_opportunities_cross_sold,0) + nvl(stg.agent_opportunities_cross_sold,0),
4893 summ.agent_opportunities_up_sold = nvl(summ.agent_opportunities_up_sold,0) + nvl(stg.agent_opportunities_up_sold,0),
4894 summ.agent_opportunities_declined = nvl(summ.agent_opportunities_declined,0) + nvl(stg.agent_opportunities_declined,0),
4895 summ.agent_opportunities_lost = nvl(summ.agent_opportunities_lost,0) + nvl(stg.agent_opportunities_lost,0),
4896 summ.agent_preview_time = nvl(summ.agent_preview_time,0) + nvl(stg.agent_preview_time,0),
4897 summ.agentcall_orr_count = nvl(summ.agentcall_orr_count,0) + nvl(stg.agentcall_orr_count,0),
4898 summ.agentcall_pr_count = nvl(summ.agentcall_pr_count,0) + nvl(stg.agentcall_pr_count,0),
4899 summ.agentcall_contact_count = nvl(summ.agentcall_contact_count,0) + nvl(stg.agentcall_contact_count,0),
4900 summ.last_update_date = g_sysdate,
4901 summ.last_updated_by = g_user_id,
4902 summ.request_id = g_request_id
4903 WHEN NOT MATCHED
4904 THEN
4905 INSERT
4906 (
4907 summ.time_id ,
4908 summ.period_type_id ,
4909 summ.period_start_date ,
4910 summ.period_start_time ,
4911 summ.day_of_week ,
4912 summ.direction ,
4913 summ.media_item_type ,
4914 summ.resource_id ,
4915 summ.party_id ,
4916 summ.classification_value ,
4917 summ.dnis_name ,
4918 summ.server_group_id ,
4919 summ.campaign_id ,
4920 summ.schedule_id ,
4921 summ.outcome_id ,
4922 summ.result_id ,
4923 summ.reason_id ,
4924 summ.source_code_id ,
4925 summ.DIALING_METHOD ,
4926 summ.partition_key ,
4927 summ.call_calls_offered_total ,
4928 summ.call_calls_offered_above_th ,
4929 summ.call_calls_handled_total ,
4930 summ.call_calls_handled_above_th ,
4931 summ.call_calls_abandoned ,
4932 summ.call_calls_abandoned_us ,
4933 summ.call_calls_transferred ,
4934 summ.call_ivr_time ,
4935 summ.call_route_time ,
4936 summ.call_queue_time ,
4937 summ.CALL_TOT_QUEUE_TO_ABANDON ,
4938 summ.call_tot_queue_to_answer ,
4939 summ.call_talk_time ,
4940 summ.CALL_CONT_CALLS_OFFERED_NA ,
4941 summ.CALL_CONT_CALLS_HANDLED_TOT_NA ,
4942 summ.agent_talk_time_nac ,
4943 summ.agent_wrap_time_nac ,
4944 summ.agent_calls_tran_conf_to_nac ,
4945 summ.AGENT_CONT_CALLS_HAND_NA ,
4946 summ.AGENT_CONT_CALLS_TC_NA ,
4947 summ.agent_calls_handled_total ,
4948 summ.agent_calls_handled_above_th ,
4949 summ.agent_calls_answered_by_goal ,
4950 summ.agent_sr_created ,
4951 summ.agent_leads_created ,
4952 summ.agent_leads_amount ,
4953 summ.agent_leads_converted_to_opp ,
4954 summ.agent_opportunities_created ,
4955 summ.agent_opportunities_won ,
4956 summ.agent_opportunities_won_amount ,
4957 summ.agent_opportunities_cross_sold ,
4958 summ.agent_opportunities_up_sold ,
4959 summ.agent_opportunities_declined ,
4960 summ.agent_opportunities_lost ,
4961 summ.agent_preview_time ,
4962 summ.agentcall_orr_count ,
4963 summ.agentcall_pr_count ,
4964 summ.agentcall_contact_count ,
4965 summ.created_by ,
4966 summ.creation_date ,
4967 summ.last_updated_by ,
4968 summ.last_update_date ,
4969 summ.last_update_login ,
4970 summ.request_id ,
4971 summ.program_application_id ,
4972 summ.program_id ,
4973 summ.program_update_date
4974 )
4975 values
4976 (
4977 stg.time_id ,
4978 stg.period_type_id ,
4979 stg.period_start_date ,
4980 stg.period_start_time ,
4981 stg.day_of_week ,
4982 stg.direction ,
4983 stg.media_item_type ,
4984 stg.resource_id ,
4985 stg.party_id ,
4986 stg.classification_value ,
4987 stg.dnis_name ,
4988 stg.server_group_id ,
4989 stg.campaign_id ,
4990 stg.schedule_id ,
4991 stg.outcome_id ,
4992 stg.result_id ,
4993 stg.reason_id ,
4994 stg.source_code_id ,
4995 stg.dialing_method ,
4996 stg.partition_key ,
4997 stg.call_calls_offered_total ,
4998 stg.call_calls_offered_above_th ,
4999 stg.CALL_CALLS_HANDLED_TOTAL ,
5000 stg.CALL_CALLS_HANDLED_ABOVE_TH ,
5001 stg.call_calls_abandoned ,
5002 stg.call_calls_abandoned_us ,
5003 stg.call_calls_transferred ,
5004 stg.call_ivr_time ,
5005 stg.call_route_time ,
5006 stg.call_queue_time ,
5007 stg.CALL_TOT_QUEUE_TO_ABANDON ,
5008 stg.call_tot_queue_to_answer ,
5009 stg.call_talk_time ,
5010 stg.CALL_CONT_CALLS_OFFERED_NA ,
5011 stg.CALL_CONT_CALLS_HANDLED_TOT_NA ,
5012 stg.agent_talk_time_nac ,
5013 stg.agent_wrap_time_nac ,
5014 stg.agent_calls_tran_conf_to_nac ,
5015 stg.AGENT_CONT_CALLS_HAND_NA ,
5016 stg.AGENT_CONT_CALLS_TC_NA ,
5017 stg.agent_calls_handled_total ,
5018 stg.agent_calls_handled_above_th ,
5019 stg.agent_calls_answered_by_goal ,
5020 stg.agent_sr_created ,
5021 stg.agent_leads_created ,
5022 stg.agent_leads_amount ,
5023 stg.agent_leads_converted_to_opp ,
5024 stg.agent_opportunities_created ,
5025 stg.agent_opportunities_won ,
5026 stg.agent_opportunities_won_amount ,
5027 stg.agent_opportunities_cross_sold ,
5028 stg.agent_opportunities_up_sold ,
5029 stg.agent_opportunities_declined ,
5030 stg.agent_opportunities_lost ,
5031 stg.agent_preview_time ,
5032 stg.agentcall_orr_count ,
5033 stg.agentcall_pr_count ,
5034 stg.agentcall_contact_count ,
5035 g_user_id ,
5036 g_sysdate ,
5037 g_user_id ,
5038 g_sysdate ,
5039 g_user_id ,
5040 g_request_id ,
5041 g_program_appl_id ,
5042 g_program_id ,
5043 g_sysdate
5044 );
5045
5046 g_rows_ins_upd := g_rows_ins_upd + SQL%ROWCOUNT;
5047 --IF (g_debug_flag = 'Y') THEN
5048 --write_log('Total rows merged in BIX_CALL_DETAILS_F : ' ||
5049 --g_rows_ins_upd);
5050 --END IF;
5051
5052 COMMIT;
5053
5054 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
5055 --'Completed merge to fact from staging ');
5056
5057 LOOP
5058
5059 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
5060 --'Before UPDATE of FACT table ');
5061
5062 UPDATE BIX_CALL_DETAILS_F FACT1
5063 SET
5064 (
5065 AGENT_CONT_CALLS_HAND_NA,
5066 AGENT_CONT_CALLS_TC_NA,
5067 CALL_CONT_CALLS_OFFERED_NA,
5068 CALL_CONT_CALLS_HANDLED_TOT_NA,
5069 LAST_UPDATE_DATE,
5070 LAST_UPDATED_BY
5071 ) =
5072 (
5073 SELECT
5074 NVL(MAX(AGENT_CONT_CALLS_HAND_NA),0),
5075 NVL(MAX(AGENT_CONT_CALLS_TC_NA),0),
5076 NVL(MAX(CALL_CONT_CALLS_OFFERED_NA),0),
5077 NVL(MAX(CALL_CONT_CALLS_HANDLED_TOT_NA),0),
5078 g_sysdate,
5079 g_user_id
5080 FROM BIX_CALL_DETAILS_F FACT2
5081 WHERE FACT1.TIME_ID = FACT2.TIME_ID
5082 AND FACT2.PERIOD_TYPE_ID = 1
5083 AND FACT2.PERIOD_START_TIME = '00:00'
5084 AND FACT1.DAY_OF_WEEK = FACT2.DAY_OF_WEEK
5085 AND FACT1.DIRECTION = FACT2.DIRECTION
5086 AND FACT1.MEDIA_ITEM_TYPE = FACT2.MEDIA_ITEM_TYPE
5087 AND FACT1.RESOURCE_ID = FACT2.RESOURCE_ID
5088 AND FACT1.PARTY_ID = FACT2.PARTY_ID
5089 AND FACT1.CLASSIFICATION_VALUE = FACT2.CLASSIFICATION_VALUE
5090 AND FACT1.DNIS_NAME = FACT2.DNIS_NAME
5091 AND FACT1.SERVER_GROUP_ID = FACT2.SERVER_GROUP_ID
5092 AND FACT1.CAMPAIGN_ID = FACT2.CAMPAIGN_ID
5093 AND FACT1.SCHEDULE_ID = FACT2.SCHEDULE_ID
5094 AND FACT1.OUTCOME_ID = FACT2.OUTCOME_ID
5095 AND FACT1.RESULT_ID = FACT2.RESULT_ID
5096 AND FACT1.REASON_ID = FACT2.REASON_ID
5097 AND FACT1.SOURCE_CODE_ID = FACT2.SOURCE_CODE_ID
5098 AND FACT1.DIALING_METHOD = FACT2.DIALING_METHOD
5099 AND FACT1.PARTITION_KEY = FACT2.PARTITION_KEY
5100 )
5101 WHERE REQUEST_ID = G_REQUEST_ID
5102 AND AGENT_CONT_CALLS_HAND_NA < 0
5103 AND LAST_UPDATE_DATE >= G_SYSDATE
5104 AND FACT1.PERIOD_TYPE_ID > 1
5105 AND ROWNUM <= 50000;
5106 --AND ROWNUM <= G_COMMIT_CHUNK_SIZE;
5107
5108 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
5109 --'AFter UPDATE of fact table ');
5110
5111 IF SQL%ROWCOUNT < G_COMMIT_CHUNK_SIZE
5112 THEN
5113 COMMIT;
5114 --IF (g_debug_flag = 'Y') THEN
5115 --write_log('Commiting and exiting g_commit_size is ' || g_commit_chunk_size);
5116 --END IF;
5117 EXIT;
5118 ELSE
5119 COMMIT;
5120 --IF (g_debug_flag = 'Y') THEN
5121 --write_log('Commiting g_commit_size is ' || g_commit_chunk_size);
5122 --END IF;
5123 END IF;
5124
5125 END LOOP;
5126
5127 COMMIT;
5128 --IF (g_debug_flag = 'Y') THEN
5129 --write_log('Finished procedure move_stg_to_fact at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5130 --END IF;
5131
5132 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
5133 --'Completed move_stg_to_fact ');
5134
5135 EXCEPTION
5136 WHEN OTHERS THEN
5137 --IF (g_debug_flag = 'Y') THEN
5138 --write_log('Error in procedure move_stg_to_fact : Error : ' || sqlerrm);
5139 --END IF;
5140 RAISE;
5141 END move_stg_to_fact;
5142
5143 PROCEDURE rollup_data IS
5144
5145 BEGIN
5146
5147 --IF (g_debug_flag = 'Y') THEN
5148 --write_log('Start of the procedure rollup_data at : ' ||
5149 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5150 --END IF;
5151
5152 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
5153 --'Before rollup_data ');
5154
5155 --Rollup half-hour information to day, month, quarter and year time bucket
5156
5157 --
5158 --This can be a INSERT and not a MERGE if we assume that all the
5159 --half hour rows would be inserted at this time into the STAGING table,
5160 --which would be the case, provided we wait for all the workers to complete.
5161 --
5162 INSERT /*+ APPEND */ INTO bix_call_details_stg STG
5163 (
5164 STG.TIME_ID,
5165 STG.PERIOD_TYPE_ID,
5166 STG.PERIOD_START_DATE,
5167 STG.PERIOD_START_TIME,
5168 STG.DAY_OF_WEEK,
5169 STG.DIRECTION,
5170 STG.MEDIA_ITEM_TYPE,
5171 STG.RESOURCE_ID,
5172 STG.PARTY_ID,
5173 STG.CLASSIFICATION_VALUE,
5174 STG.DNIS_NAME,
5175 STG.SERVER_GROUP_ID,
5176 STG.CAMPAIGN_ID,
5177 STG.SCHEDULE_ID,
5178 STG.OUTCOME_ID,
5179 STG.RESULT_ID,
5180 STG.REASON_ID,
5181 STG.SOURCE_CODE_ID,
5182 STG.DIALING_METHOD,
5183 STG.PARTITION_KEY,
5184 STG.CALL_CALLS_OFFERED_TOTAL,
5185 STG.CALL_CALLS_OFFERED_ABOVE_TH,
5186 STG.CALL_CALLS_HANDLED_TOTAL,
5187 STG.CALL_CALLS_HANDLED_ABOVE_TH,
5188 STG.CALL_CALLS_ABANDONED,
5189 STG.CALL_CALLS_ABANDONED_US,
5190 STG.CALL_CALLS_TRANSFERRED,
5191 STG.CALL_IVR_TIME,
5192 STG.CALL_ROUTE_TIME,
5193 STG.call_queue_time,
5194 STG.CALL_TOT_QUEUE_TO_ABANDON,
5195 STG.call_tot_queue_to_answer,
5196 STG.CALL_TALK_TIME,
5197 STG.AGENT_TALK_TIME_NAC,
5198 STG.AGENT_WRAP_TIME_NAC,
5199 STG.AGENT_CALLS_TRAN_CONF_TO_NAC,
5200 STG.AGENT_CONT_CALLS_HAND_NA, --FORCE THIS TO NULL SO THAT THE UPDATE WORKS
5201 STG.AGENT_CALLS_HANDLED_TOTAL,
5202 STG.AGENT_CALLS_HANDLED_ABOVE_TH,
5203 STG.AGENT_CALLS_ANSWERED_BY_GOAL,
5204 STG.AGENT_SR_CREATED,
5205 STG.AGENT_LEADS_CREATED,
5206 STG.AGENT_LEADS_AMOUNT,
5207 STG.AGENT_LEADS_CONVERTED_TO_OPP,
5208 STG.AGENT_OPPORTUNITIES_CREATED,
5209 STG.AGENT_OPPORTUNITIES_WON,
5210 STG.AGENT_OPPORTUNITIES_WON_AMOUNT,
5211 STG.AGENT_OPPORTUNITIES_CROSS_SOLD,
5212 STG.AGENT_OPPORTUNITIES_UP_SOLD,
5213 STG.AGENT_OPPORTUNITIES_DECLINED,
5214 STG.AGENT_OPPORTUNITIES_LOST,
5215 STG.AGENT_PREVIEW_TIME,
5216 STG.AGENTCALL_ORR_COUNT,
5217 STG.AGENTCALL_PR_COUNT,
5218 STG.AGENTCALL_CONTACT_COUNT,
5219 STG.CREATED_BY,
5220 STG.CREATION_DATE,
5221 STG.LAST_UPDATED_BY,
5222 STG.LAST_UPDATE_DATE,
5223 STG.LAST_UPDATE_LOGIN,
5224 STG.REQUEST_ID,
5225 STG.PROGRAM_APPLICATION_ID,
5226 STG.PROGRAM_ID,
5227 STG.PROGRAM_UPDATE_DATE
5228 )
5229 (
5230 SELECT
5231 --decode(ftd.report_date_julian, null, decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
5232 --decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null), ftd.ent_year_id),
5233 --ftd.ent_qtr_id), ftd.ent_period_id), ftd.week_id), ftd.report_date_julian),
5234 --decode(ftd.report_date_julian, null, decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
5235 --decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null),
5236 --128), 64), 32), 16), 1),
5237 --decode(ftd.report_date_julian, null, decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
5238 --decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_date(null),
5239 --min(ftd.ent_year_start_date)),min(ftd.ent_qtr_start_date)),
5240 --min(ftd.ent_period_start_date)), min(ftd.week_start_date)),
5241 --min(stg.period_start_date)),
5242 decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
5243 decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null), ftd.ent_year_id),
5244 ftd.ent_qtr_id), ftd.ent_period_id), ftd.week_id),
5245 decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
5246 decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null),
5247 128), 64), 32), 16),
5248 decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
5249 decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_date(null),
5250 min(ftd.ent_year_start_date)),min(ftd.ent_qtr_start_date)),
5251 min(ftd.ent_period_start_date)), min(ftd.week_start_date)),
5252 '00:00',
5253 DAY_OF_WEEK,
5254 DIRECTION,
5255 MEDIA_ITEM_TYPE,
5256 RESOURCE_ID,
5257 PARTY_ID,
5258 CLASSIFICATION_VALUE,
5259 DNIS_NAME,
5260 SERVER_GROUP_ID,
5261 CAMPAIGN_ID,
5262 SCHEDULE_ID,
5263 OUTCOME_ID,
5264 RESULT_ID,
5265 REASON_ID,
5266 SOURCE_CODE_ID,
5267 DIALING_METHOD,
5268 PARTITION_KEY,
5269 SUM(CALL_CALLS_OFFERED_TOTAL),
5270 SUM(CALL_CALLS_OFFERED_ABOVE_TH),
5271 SUM(CALL_CALLS_HANDLED_TOTAL),
5272 SUM(CALL_CALLS_HANDLED_ABOVE_TH),
5273 SUM(CALL_CALLS_ABANDONED),
5274 SUM(CALL_CALLS_ABANDONED_US),
5275 SUM(CALL_CALLS_TRANSFERRED),
5276 SUM(CALL_IVR_TIME),
5277 SUM(CALL_ROUTE_TIME),
5278 SUM(call_queue_time),
5279 SUM(CALL_TOT_QUEUE_TO_ABANDON),
5280 SUM(call_tot_queue_to_answer),
5281 SUM(CALL_TALK_TIME),
5282 SUM(AGENT_TALK_TIME_NAC),
5283 SUM(AGENT_WRAP_TIME_NAC),
5284 SUM(AGENT_CALLS_TRAN_CONF_TO_NAC),
5285 --NULL, SUM(AGENT_CONT_CALLS_HAND_NA),
5286 -1,
5287 SUM(AGENT_CALLS_HANDLED_TOTAL),
5288 SUM(AGENT_CALLS_HANDLED_ABOVE_TH),
5289 SUM(AGENT_CALLS_ANSWERED_BY_GOAL),
5290 SUM(AGENT_SR_CREATED),
5291 SUM(AGENT_LEADS_CREATED),
5292 SUM(AGENT_LEADS_AMOUNT),
5293 SUM(AGENT_LEADS_CONVERTED_TO_OPP),
5294 SUM(AGENT_OPPORTUNITIES_CREATED),
5295 SUM(AGENT_OPPORTUNITIES_WON),
5296 SUM(AGENT_OPPORTUNITIES_WON_AMOUNT),
5297 SUM(AGENT_OPPORTUNITIES_CROSS_SOLD),
5298 SUM(AGENT_OPPORTUNITIES_UP_SOLD),
5299 SUM(AGENT_OPPORTUNITIES_DECLINED),
5300 SUM(AGENT_OPPORTUNITIES_LOST),
5301 SUM(AGENT_PREVIEW_TIME),
5302 SUM(AGENTCALL_ORR_COUNT),
5303 SUM(AGENTCALL_PR_COUNT),
5304 SUM(AGENTCALL_CONTACT_COUNT),
5305 g_user_id,
5306 g_sysdate,
5307 g_user_id,
5308 g_sysdate,
5309 g_user_id,
5310 g_request_id,
5311 g_program_appl_id,
5312 g_program_id,
5313 g_sysdate
5314 FROM bix_call_details_stg stg,
5315 fii_time_day ftd
5316 WHERE stg.time_id = ftd.report_date_julian
5317 AND stg.period_type_id = 1
5318 GROUP BY
5319 DAY_OF_WEEK,
5320 DIRECTION,
5321 MEDIA_ITEM_TYPE,
5322 RESOURCE_ID,
5323 PARTY_ID,
5324 CLASSIFICATION_VALUE,
5325 DNIS_NAME,
5326 SERVER_GROUP_ID,
5327 CAMPAIGN_ID,
5328 SCHEDULE_ID,
5329 OUTCOME_ID,
5330 RESULT_ID,
5331 REASON_ID,
5332 SOURCE_CODE_ID,
5333 DIALING_METHOD,
5334 PARTITION_KEY,
5335 ROLLUP (
5336 ftd.ent_year_id,
5337 ftd.ent_qtr_id,
5338 ftd.ent_period_id,
5339 ftd.week_id
5340 --ftd.report_date_julian)
5341 )
5342 HAVING
5343 --decode(ftd.report_date_julian, null, decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
5344 --decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null), 128), 64), 32), 16), 1) IS NOT NULL
5345 --)
5346 decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
5347 decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null)
5348 ,128), 64), 32), 16) IS NOT NULL
5349 )
5350 ;
5351
5352 --IF (g_debug_flag = 'Y') THEN
5353 --write_log('Total rows inserted in the staging area for day, month and year : ' || to_char(SQL%ROWCOUNT));
5354 --END IF;
5355
5356 COMMIT;
5357
5358 --IF (g_debug_flag = 'Y') THEN
5359 --write_log('Finished procedure rollup_data at : ' ||
5360 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5361 --END IF;
5362
5363 --write_log(to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss: ')||
5364 --'After rollup_data ');
5365
5366 EXCEPTION
5367 WHEN OTHERS THEN
5368 --IF (g_debug_flag = 'Y') THEN
5369 --write_log('Error in procedure rollup_data : Error : ' || sqlerrm);
5370 --END IF;
5371 RAISE;
5372 END rollup_data;
5373
5374 PROCEDURE worker(errbuf OUT NOCOPY VARCHAR2,
5375 retcode OUT NOCOPY VARCHAR2,
5376 p_worker_no IN NUMBER) IS
5377
5378 l_unassigned_cnt NUMBER := 0;
5379 l_failed_cnt NUMBER := 0;
5380 l_wip_cnt NUMBER := 0;
5381 l_completed_cnt NUMBER := 0;
5382 l_total_cnt NUMBER := 0;
5383 l_count NUMBER := 0;
5384 l_start_date_range DATE;
5385 l_end_date_range DATE;
5386
5387 BEGIN
5388
5389 --IF (g_debug_flag = 'Y') THEN
5390 --write_log('Start of the procedure worker at : ' ||
5391 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5392 --END IF;
5393
5394 --dbms_output.put_line('Called worker with p_worker_no ' || p_worker_no);
5395
5396 errbuf := NULL;
5397 retcode := 0;
5398
5399 --IF (g_debug_flag = 'Y') THEN
5400 --write_log('Calling procedure init');
5401 --END IF;
5402 init;
5403
5404 l_count:= 0;
5405
5406 LOOP
5407
5408 /* Get the status of all the jobs in BIX_WORKER_JOBS */
5409 --SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
5410 --NVL(sum(decode(status,'FAILED', 1, 0)),0),
5411 --NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
5412 --NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
5413 --count(*)
5414 --INTO l_unassigned_cnt,
5415 --l_failed_cnt,
5416 --l_wip_cnt,
5417 --l_completed_cnt,
5418 --l_total_cnt
5419 --FROM BIX_WORKER_JOBS
5420 --WHERE object_name = 'BIX_CALL_DETAILS_F';
5421
5422 SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
5423 NVL(sum(decode(status,'FAILED', 1, 0)),0),
5424 NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
5425 NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
5426 count(*)
5427 INTO l_unassigned_cnt,
5428 l_failed_cnt,
5429 l_wip_cnt,
5430 l_completed_cnt,
5431 l_total_cnt
5432 FROM BIX_WORKER_JOBS
5433 WHERE worker_number = p_worker_no
5434 AND object_name = 'BIX_CALL_DETAILS_F';
5435
5436 --dbms_output.put_line(
5437 --'Unassigned: '||l_unassigned_cnt||
5438 --' In Process: '||l_wip_cnt||
5439 --' Completed: '||l_completed_cnt||
5440 --' Failed: '||l_failed_cnt||
5441 --' Total: '|| l_total_cnt);
5442
5443 --IF (g_debug_flag = 'Y') THEN
5444 --write_log('Job status - Unassigned: '||l_unassigned_cnt||
5445 --' In Process: '||l_wip_cnt||
5446 --' Completed: '||l_completed_cnt||
5447 --' Failed: '||l_failed_cnt||
5448 --' Total: '|| l_total_cnt);
5449 --END IF;
5450
5451 IF (l_failed_cnt > 0) THEN
5452 --IF (g_debug_flag = 'Y') THEN
5453 --write_log('Another worker have errored out. Stop processing.');
5454 --END IF;
5455 EXIT;
5456 ELSIF (l_unassigned_cnt = 0) THEN
5457 --IF (g_debug_flag = 'Y') THEN
5458 --write_log('No more jobs left. Terminating.');
5459 --END IF;
5460 EXIT;
5461 ELSIF (l_completed_cnt = l_total_cnt) THEN
5462 --IF (g_debug_flag = 'Y') THEN
5463 --write_log('All jobs completed, no more job. Terminating');
5464 --END IF;
5465 EXIT;
5466 ELSIF (l_unassigned_cnt > 0) THEN
5467 --dbms_output.put_line('setting to in process for worker ' || p_worker_no);
5468
5469 /* Pickup any one unassigned job to process */
5470 --UPDATE BIX_WORKER_JOBS
5471 --SET status = 'IN PROCESS',
5472 --worker_number = p_worker_no
5473 --WHERE status = 'UNASSIGNED'
5474 --AND rownum < 2
5475 --AND object_name = 'BIX_CALL_DETAILS_F';
5476
5477 UPDATE BIX_WORKER_JOBS
5478 SET status = 'IN PROCESS'
5479 WHERE object_name = 'BIX_CALL_DETAILS_F'
5480 AND worker_number = p_worker_no;
5481
5482 UPDATE bix_medias_for_worker
5483 SET status = 'IN PROCESS'
5484 --worker_number = p_worker_no
5485 WHERE status = 'UNASSIGNED'
5486 AND worker_number = p_worker_no;
5487
5488 l_count := sql%rowcount;
5489 COMMIT;
5490 END IF;
5491
5492 -- -----------------------------------
5493 -- There could be rare situations where
5494 -- the unassigned job gets taken by
5495 -- another worker. So, if unassigned
5496 -- job no longer exist. Do nothing.
5497 -- -----------------------------------
5498
5499 IF (l_count > 0) THEN
5500
5501 DECLARE
5502 BEGIN
5503
5504 --
5505 --Collect data for half hour time buckets for the date range of the job
5506 --
5507 --SELECT start_date_range, end_date_range
5508 --INTO l_start_date_range, l_end_date_range
5509 --FROM BIX_WORKER_JOBS
5510 --WHERE worker_number = p_worker_no
5511 --AND status = 'IN PROCESS'
5512 --AND object_name = 'BIX_CALL_DETAILS_F';
5513
5514 --IF (g_debug_flag = 'Y') THEN
5515 --write_log('Calling procedure insert_half_hour_rows');
5516 --END IF;
5517
5518 --insert_half_hour_rows (
5519 --l_start_date_range,
5520 --l_end_date_range
5521 --);
5522
5523 insert_half_hour_rows (
5524 g_collect_start_date,
5525 g_collect_end_date,
5526 p_worker_no
5527 );
5528
5529 --IF (g_debug_flag = 'Y') THEN
5530 --write_log('End procedure insert_half_hour_rows');
5531 --END IF;
5532
5533 --
5534 -- Update the status of job to 'COMPLETED'
5535 --
5536 --UPDATE BIX_WORKER_JOBS
5537 --SET status = 'COMPLETED'
5538 --WHERE status = 'IN PROCESS'
5539 --AND worker_number = p_worker_no
5540 --AND object_name = 'BIX_CALL_DETAILS_F';
5541
5542 UPDATE bix_medias_for_worker
5543 SET status = 'COMPLETED'
5544 WHERE status = 'IN PROCESS'
5545 AND worker_number = p_worker_no;
5546
5547 UPDATE BIX_WORKER_JOBS
5548 SET status = 'COMPLETED'
5549 WHERE object_name = 'BIX_CALL_DETAILS_F'
5550 AND status = 'IN PROCESS'
5551 AND worker_number = p_worker_no;
5552
5553 COMMIT;
5554
5555 EXCEPTION
5556 WHEN OTHERS THEN
5557 retcode := -1;
5558
5559 --UPDATE BIX_WORKER_JOBS
5560 --SET status = 'FAILED'
5561 --WHERE worker_number = p_worker_no
5562 --AND status = 'IN PROCESS'
5563 --AND object_name = 'BIX_CALL_DETAILS_F';
5564
5565 UPDATE BIX_WORKER_JOBS
5566 SET status = 'FAILED'
5567 WHERE object_name = 'BIX_CALL_DETAILS_F'
5568 AND status = 'IN PROCESS'
5569 AND worker_number = p_worker_no;
5570
5571 UPDATE bix_medias_for_worker
5572 SET status = 'FAILED'
5573 WHERE worker_number = p_worker_no
5574 AND status = 'IN PROCESS';
5575
5576 COMMIT;
5577 --IF (g_debug_flag = 'Y') THEN
5578 write_log('Error in worker');
5579 --END IF;
5580 RAISE G_CHILD_PROCESS_ISSUE;
5581 END;
5582
5583 END IF; -- for if which checks count > 0
5584
5585 END LOOP;
5586
5587 --IF (g_debug_flag = 'Y') THEN
5588 --write_log('Finished procedure worker at : ' ||
5589 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5590 --END IF;
5591
5592 EXCEPTION
5593 WHEN OTHERS THEN
5594 --IF (g_debug_flag = 'Y') THEN
5595 --write_log('Error in procedure worker : Error : ' || sqlerrm);
5596 --END IF;
5597 RAISE;
5598 END WORKER;
5599
5600 PROCEDURE main (errbuf OUT NOCOPY VARCHAR2,
5601 retcode OUT NOCOPY VARCHAR2,
5602 p_number_of_processes IN NUMBER
5603 )
5604 IS
5605
5606 l_has_missing_date BOOLEAN := FALSE;
5607 l_no_of_workers NUMBER;
5608 l_last_start_date DATE;
5609 l_last_end_date DATE;
5610 l_last_period_from DATE;
5611 l_last_period_to DATE;
5612 l_start_date DATE;
5613
5614 BEGIN
5615
5616 IF (FND_PROFILE.DEFINED('BIX_DBI_DEBUG')) THEN
5617 g_debug_flag := nvl(FND_PROFILE.VALUE('BIX_DBI_DEBUG'), 'N');
5618 END IF;
5619
5620 --g_debug_flag := 'Y';
5621
5622
5623 g_required_workers := p_number_of_processes;
5624
5625 --IF (g_debug_flag = 'Y') THEN
5626 --write_log('Debug Flag : ' || g_debug_flag);
5627 --END IF;
5628
5629 --IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
5630 --g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
5631 --END IF;
5632
5633 g_commit_chunk_size := 1500;
5634
5635 --IF (g_debug_flag = 'Y') THEN
5636 --write_log('Commit SIZE : ' || g_commit_chunk_size);
5637 --
5638 --write_log('Start of the procedure main at : ' ||
5639 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5640 --END IF;
5641
5642 errbuf := null;
5643 retcode := 0;
5644
5645 truncate_table('bix_medias_for_worker');
5646
5647 truncate_table('BIX_CALL_DETAILS_STG');
5648 --truncate_table('BIX_CALL_PROCESSED_RECS');
5649
5650 cleanup_oltp; --001
5651
5652 --IF (g_debug_flag = 'Y') THEN
5653 --write_log('Done truncating bix_call_details_stg and bix_call_procesed_recs');
5654 --END IF;
5655
5656 --Analyze with zero rows to enable MERGE on FACT to perform with index
5657 DBMS_STATS.gather_table_stats(ownname => g_bix_schema,
5658 tabName => 'BIX_CALL_DETAILS_STG',
5659 cascade => TRUE,
5660 degree => bis_common_parameters.get_degree_of_parallelism,
5661 estimate_percent => 10,
5662 granularity => 'GLOBAL');
5663
5664
5665 init;
5666
5667 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('BIX_CALL_DETAILS_F',
5668 l_last_start_date,
5669 l_last_end_date,
5670 l_last_period_from,
5671 l_last_period_to);
5672
5673 --IF (g_debug_flag = 'Y') THEN
5674 --write_log('After get_last_refresh '||
5675 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5676 --END IF;
5677
5678 -- cleanup_oltp; --001 shifted the call just above,after the truncates
5679
5680 IF l_last_period_to IS NULL THEN
5681 l_last_period_to := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),
5682 'MM/DD/YYYY');
5683 END IF;
5684
5685 l_last_period_to := l_last_period_to + 1/86400;
5686
5687 l_start_date := l_last_period_to;
5688
5689 g_collect_start_date := l_start_date;
5690 g_collect_end_date := sysdate;
5691
5692 --g_collect_start_date:=to_date('10-OCT-2003 00:00:00','DD-MON-YYYY HH24:MI:SS');
5693 --g_collect_end_date:=to_date('11-OCT-2003 00:00:00','DD-MON-YYYY HH24:MI:SS');
5694
5695 --
5696 -- Check if time dimension is populated for the collection date range
5697 --
5698 --IF (g_debug_flag = 'Y') THEN
5699 --write_log('Checking if time dimension is populated between ' ||
5700 --g_collect_start_date || ' and ' ||
5701 --g_collect_end_date
5702 --);
5703 --END IF;
5704
5705 fii_time_api.check_missing_date(g_collect_start_date,
5706 g_collect_end_date,
5707 l_has_missing_date
5708 );
5709
5710 IF (l_has_missing_date)
5711 THEN
5712 --IF (g_debug_flag = 'Y') THEN
5713 --write_log('Time dimension is not populated for the entire
5714 --collection date range');
5715 --END IF;
5716 RAISE G_TIME_DIM_MISSING;
5717 END IF;
5718
5719 --
5720 --If the collection date range is more than 1 day and user has
5721 --specified to launch more than 1 worker
5722 -- then launch parallel workers to do the half hour collection of each day
5723 --
5724
5725 --IF (((g_collect_end_date - g_collect_start_date) > 1) AND
5726 --(p_number_of_processes > 1)) THEN
5727 --IF (g_debug_flag = 'Y') THEN
5728 --write_log('Calling register_jobs at '||
5729 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5730 --END IF;
5731
5732 --
5733 --We will call register_jobs every time
5734 --Do not check date range
5735 --
5736
5737 register_jobs;
5738
5739 /* Inserting PR count only into bix_call_details for the purpose of marketing mv */
5740
5741 INSERT /*+ APPEND */
5742 INTO bix_call_details_stg STG
5743 (
5744 time_id ,
5745 period_type_id ,
5746 period_start_date ,
5747 period_start_time ,
5748 source_code_id ,
5749 party_id ,
5750 partition_key ,
5751 day_of_week ,
5752 Direction ,
5753 agentcall_pr_count ,
5754 call_calls_offered_total ,
5755 call_calls_offered_above_th ,
5756 call_calls_handled_total ,
5757 call_calls_handled_above_th ,
5758 call_calls_abandoned ,
5759 call_calls_abandoned_us ,
5760 call_calls_transferred ,
5761 call_ivr_time ,
5762 call_route_time ,
5763 call_queue_time ,
5764 CALL_TOT_QUEUE_TO_ABANDON ,
5765 call_tot_queue_to_answer ,
5766 call_talk_time ,
5767 CALL_CONT_CALLS_OFFERED_NA ,
5768 CALL_CONT_CALLS_HANDLED_TOT_NA ,
5769 agent_talk_time_nac ,
5770 agent_wrap_time_nac ,
5771 agent_calls_tran_conf_to_nac ,
5772 AGENT_CONT_CALLS_HAND_NA ,
5773 AGENT_CONT_CALLS_TC_NA ,
5774 agent_calls_handled_total ,
5775 agent_calls_handled_above_th ,
5776 agent_calls_answered_by_goal ,
5777 agent_sr_created ,
5778 agent_leads_created ,
5779 agent_leads_amount ,
5780 agent_leads_converted_to_opp ,
5781 agent_opportunities_created ,
5782 agent_opportunities_won ,
5783 agent_opportunities_won_amount ,
5784 agent_opportunities_cross_sold ,
5785 agent_opportunities_up_sold ,
5786 agent_opportunities_declined ,
5787 agent_opportunities_lost ,
5788 agent_preview_time ,
5789 agentcall_orr_count
5790
5791 )
5792 (
5793 select to_char(trunc(start_date_time),'J')
5794 ,1,trunc(start_date_time),'00:00',source_code_id ,
5795 a.party_id,
5796 'PR',
5797 to_char(trunc(start_date_time),'D') ,
5798 'N/A',
5799 count(*) ,
5800 0,0,0,0,0,
5801 0,0,0,0,0,
5802 0,0,0,0,0,
5803 0,0,0,0,0,
5804 0,0,0,0,0,
5805 0,0,0,0,0,
5806 0,0,0,0,0,0
5807 from jtf_ih_interactions a, jtf_ih_results_b b
5808 where a.result_id=b.result_id
5809 and a.end_date_time between g_collect_start_date and g_collect_end_date
5810 and b.positive_response_flag='Y'
5811 and a.active='N'
5812 group by trunc(start_date_time),source_code_id,a.party_id
5813 );
5814
5815
5816
5817
5818
5819
5820
5821
5822
5823 --IF (g_debug_flag = 'Y') THEN
5824 --write_log('Ended register_jobs at '||
5825 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5826 --END IF;
5827
5828 --
5829 --Launch a parallel worker for each day of the collection date
5830 --range or number of processes user has requested for , whichever is less
5831 --
5832 l_no_of_workers := least(g_no_of_jobs, p_number_of_processes);
5833
5834 IF (g_debug_flag = 'Y') THEN
5835 write_log ('g_no_of_jobs='||g_no_of_jobs||' p_num_proc='||p_number_of_processes);
5836 write_log('Launching workers at '||
5837 to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5838 END IF;
5839 FOR i IN 1 .. l_no_of_workers
5840 LOOP
5841 g_worker(i) := LAUNCH_WORKER(i);
5842 NULL;
5843 END LOOP;
5844
5845 --IF (g_debug_flag = 'Y') THEN
5846 --write_log('Completed Launching workers at '||
5847 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5848 --write_log('Number of Workers launched : ' || to_char(l_no_of_workers));
5849 --END IF;
5850
5851 COMMIT;
5852
5853 --
5854 -- Monitor child processes after launching them
5855 --
5856
5857 DECLARE
5858
5859 l_unassigned_cnt NUMBER := 0;
5860 l_completed_cnt NUMBER := 0;
5861 l_wip_cnt NUMBER := 0;
5862 l_failed_cnt NUMBER := 0;
5863 l_tot_cnt NUMBER := 0;
5864 l_last_unassigned_cnt NUMBER := 0;
5865 l_last_completed_cnt NUMBER := 0;
5866 l_last_wip_cnt NUMBER := 0;
5867 l_cycle NUMBER := 0;
5868
5869 BEGIN
5870 LOOP
5871
5872 --IF (g_debug_flag = 'Y') THEN
5873 --write_log('Before worker status select '||
5874 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5875 --END IF;
5876
5877 --SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
5878 --NVL(sum(decode(status,'COMPLETED',1,0)),0),
5879 --NVL(sum(decode(status,'IN PROCESS',1,0)),0),
5880 --NVL(sum(decode(status,'FAILED',1,0)),0),
5881 --count(*)
5882 --INTO l_unassigned_cnt,
5883 --l_completed_cnt,
5884 --l_wip_cnt,
5885 --l_failed_cnt,
5886 --l_tot_cnt
5887 --FROM BIX_WORKER_JOBS
5888 --WHERE OBJECT_NAME = 'BIX_CALL_DETAILS_F';
5889 SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
5890 NVL(sum(decode(status,'COMPLETED',1,0)),0),
5891 NVL(sum(decode(status,'IN PROCESS',1,0)),0),
5892 NVL(sum(decode(status,'FAILED',1,0)),0),
5893 count(*)
5894 INTO l_unassigned_cnt,
5895 l_completed_cnt,
5896 l_wip_cnt,
5897 l_failed_cnt,
5898 l_tot_cnt
5899 FROM BIX_WORKER_JOBS
5900 WHERE object_name = 'BIX_CALL_DETAILS_F';
5901
5902 --IF (g_debug_flag = 'Y') THEN
5903 --write_log('After worker status select '||
5904 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5905 --END IF;
5906
5907 IF (l_failed_cnt > 0) THEN
5908 RAISE G_CHILD_PROCESS_ISSUE;
5909 END IF;
5910
5911 IF (l_tot_cnt = l_completed_cnt) THEN
5912 --IF (g_debug_flag = 'Y') THEN
5913 --write_log('Total count ' ||l_tot_cnt ||
5914 --'Completed count ' || l_completed_cnt ||
5915 --'- can exit loop now');
5916 --END IF;
5917 EXIT;
5918 END IF;
5919
5920 IF (l_unassigned_cnt = l_last_unassigned_cnt AND
5921 l_completed_cnt = l_last_completed_cnt AND
5922 l_wip_cnt = l_last_wip_cnt) THEN
5923 l_cycle := l_cycle + 1;
5924 ELSE
5925 l_cycle := 1;
5926 END IF;
5927
5928 --IF (l_cycle > MAX_LOOP) THEN
5929 --write_log('Infinite loop');
5930 --dbms_output.put_line('Infinite loop');
5931 --RAISE G_CHILD_PROCESS_ISSUE;
5932 --END IF;
5933
5934 dbms_lock.sleep(5);
5935
5936 l_last_unassigned_cnt := l_unassigned_cnt;
5937 l_last_completed_cnt := l_completed_cnt;
5938 l_last_wip_cnt := l_wip_cnt;
5939
5940 END LOOP;
5941
5942 END; -- Monitor child process Ends here.
5943
5944 --
5945 --At this point bix_medias_for_worker is no longer needed. Truncate this
5946 --so that we can save some space.
5947 --
5948 truncate_table('bix_medias_for_worker');
5949
5950 --ELSE
5951 --
5952 --Collecting only one day - so call insert_half_hour_rows
5953 --
5954 --IF (g_debug_flag = 'Y') THEN
5955 --write_log('Before calling insert_half_hour_rows (no workers) '||
5956 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5957 --END IF;
5958
5959 --insert_half_hour_rows(
5960 --g_collect_start_date,
5961 --g_collect_end_date
5962 --);
5963
5964 --IF (g_debug_flag = 'Y') THEN
5965 --write_log('Completed insert_half_hour_rows '||
5966 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5967 --END IF;
5968
5969 --END IF;
5970
5971 --DBMS_STATS.gather_table_stats(ownname => g_bix_schema,
5972 --tabName => 'BIX_CALL_DETAILS_STG',
5973 --cascade => TRUE,
5974 --degree => bis_common_parameters.get_degree_of_parallelism,
5975 --estimate_percent => 10,
5976 --granularity => 'GLOBAL');
5977
5978
5979
5980
5981
5982
5983 --
5984 -- Summarize data to day, week, month, quater and year time buckets
5985 --
5986 --IF (g_debug_flag = 'Y') THEN
5987 --write_log('Calling rollup_data at '||
5988 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5989 --END IF;
5990 rollup_data;
5991 --IF (g_debug_flag = 'Y') THEN
5992 --write_log('Ended rollup_data at '||
5993 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
5994 --END IF;
5995
5996 --
5997 -- Merge the data to the main summary table from staging area
5998 --
5999 move_stg_to_fact;
6000 --IF (g_debug_flag = 'Y') THEN
6001 --write_log('Ended move_stg_to_fact at '||
6002 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
6003
6004 --write_log('Total Rows Inserted/Updated : ' || to_char(g_rows_ins_upd));
6005
6006 --write_log('Finished Procedure BIX_CALL_LOAD with success at : ' ||
6007 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
6008
6009 --END IF;
6010 truncate_table('BIX_CALL_DETAILS_STG');
6011 --truncate_table('BIX_CALL_PROCESSED_RECS');
6012 --IF (g_debug_flag = 'Y') THEN
6013 --write_log('Done truncating bix_call_details_stg and bix_call_procesed_recs');
6014 --
6015 --write_log('Calling procedure WRAPUP');
6016 --END IF;
6017 bis_collection_utilities.wrapup(
6018 p_status => TRUE,
6019 p_count => g_rows_ins_upd,
6020 p_message => NULL,
6021 p_period_from => g_collect_start_date,
6022 p_period_to => g_collect_end_date);
6023
6024 --IF (g_debug_flag = 'Y') THEN
6025 --write_log('End of the procedure main at : ' ||
6026 --to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
6027 --END IF;
6028
6029 EXCEPTION
6030 WHEN G_TIME_DIM_MISSING THEN
6031 retcode := -1;
6032 errbuf := 'Time Dimension is not populated for the entire collection range';
6033 bis_collection_utilities.wrapup(
6034 p_status => FALSE,
6035 p_count => 0,
6036 p_message => 'Load Calls summary package failed : Error : Time dimension is not populated',
6037 p_period_from => g_collect_start_date,
6038 p_period_to => g_collect_end_date);
6039 WHEN G_CHILD_PROCESS_ISSUE THEN
6040 clean_up;
6041 retcode := SQLCODE;
6042 errbuf := SQLERRM;
6043 bis_collection_utilities.wrapup(
6044 p_status => FALSE,
6045 p_count => 0,
6046 p_message => 'Load Calls summary package failed : error : ' || sqlerrm,
6047 p_period_from => g_collect_start_date,
6048 p_period_to => g_collect_end_date);
6049 WHEN G_OLTP_CLEANUP_ISSUE THEN
6050 clean_up;
6051 --retcode := SQLCODE;
6052 --errbuf := SQLERRM;
6053 bis_collection_utilities.wrapup(
6054 p_status => FALSE,
6055 p_count => 0,
6056 p_message => 'Update Calls summary package failed in OLTP cleanup : error : ' || g_errbuf,
6057 p_period_from => g_collect_start_date,
6058 p_period_to => g_collect_end_date);
6059 WHEN OTHERS THEN
6060 clean_up;
6061 retcode := SQLCODE;
6062 errbuf := SQLERRM;
6063 bis_collection_utilities.wrapup(
6064 p_status => FALSE,
6065 p_count => 0,
6066 p_message => 'Load Calls summary package failed : error : ' || sqlerrm,
6067 p_period_from => g_collect_start_date,
6068 p_period_to => g_collect_end_date);
6069 END main;
6070
6071 END BIX_CALL_UPDATE_PKG;