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