[Home] [Help]
PACKAGE BODY: APPS.BIX_SESSION_LOAD_PKG
Source
1 PACKAGE BODY BIX_SESSION_LOAD_PKG AS
2 /*$Header: bixagtlb.plb 120.1 2006/03/28 22:34:53 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_commit_chunk_size NUMBER;
10 g_rows_ins_upd NUMBER;
11 g_collect_start_date DATE;
12 g_collect_end_date DATE;
13 g_sysdate DATE;
14 g_debug_flag VARCHAR2(1) := 'N';
15 g_agent_cost NUMBER;
16
17 g_errbuf VARCHAR2(1000);
18 g_retcode VARCHAR2(10) := 'S';
19
20 G_OLTP_CLEANUP_ISSUE EXCEPTION;
21 G_TIME_DIM_MISSING EXCEPTION;
22
23 TYPE g_session_id_tab IS TABLE OF ieu_sh_sessions.session_id%TYPE;
24 TYPE g_activity_id_tab IS TABLE OF ieu_sh_activities.activity_id%TYPE;
25 TYPE g_resource_id_tab IS TABLE OF ieu_sh_sessions.resource_id%TYPE;
26 TYPE g_begin_date_time_tab IS TABLE OF ieu_sh_sessions.begin_date_time%TYPE;
27 TYPE g_end_date_time_tab IS TABLE OF ieu_sh_sessions.end_date_time%TYPE;
28 TYPE g_server_group_id_tab IS TABLE OF jtf_rs_resource_extns.server_group_id%TYPE;
29 TYPE g_application_id_tab IS TABLE OF ieu_sh_sessions.application_id%TYPE;
30 TYPE g_schedule_id_tab IS TABLE OF ams_campaign_schedules_b.schedule_id%TYPE;
31 TYPE g_campaign_id_tab IS TABLE OF ams_campaign_schedules_b.campaign_id%TYPE;
32
33 PROCEDURE Write_Log (p_msg IN VARCHAR2) IS
34 BEGIN
35 IF (g_debug_flag = 'Y') THEN
36 BIS_COLLECTION_UTILITIES.log(p_msg);
37 END IF;
38 EXCEPTION
39 WHEN OTHERS THEN
40 RAISE;
41 END Write_Log;
42
43 PROCEDURE truncate_table (p_table_name in varchar2) is
44
45 l_stmt varchar2(400);
46 BEGIN
47 write_log('Start of the procedure truncate_table at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
48
49 l_stmt:='truncate table '||g_bix_schema||'.'|| p_table_name;
50 execute immediate l_stmt;
51
52 write_log('Table ' || p_table_name || ' has been truncated');
53 write_log('Finished procedure truncate_table at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
54 EXCEPTION
55 WHEN OTHERS THEN
56 write_log('Error in truncate_table : Error : ' || sqlerrm);
57 RAISE;
58 END truncate_table;
59
60 PROCEDURE init
61 IS
62
63 l_status VARCHAR2(30);
64 l_industry VARCHAR2(30);
65 BEGIN
66
67 IF (BIS_COLLECTION_UTILITIES.SETUP('BIX_AGENT_SESSION_F') = FALSE) THEN
68 RAISE_APPLICATION_ERROR(-20000, 'BIS_COLLECTION_UTILITIES.setup has failed');
69 END IF;
70
71 write_log('Start of the procedure init at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
72
73 write_log('Initializing global variables');
74 g_request_id := FND_GLOBAL.CONC_REQUEST_ID();
75 g_program_appl_id := FND_GLOBAL.PROG_APPL_ID();
76 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
77 g_user_id := FND_GLOBAL.USER_ID();
78 g_sysdate := SYSDATE;
79 g_commit_chunk_size := 10000;
80 g_rows_ins_upd := 0;
81 g_agent_cost := 0;
82
83 write_log('Getting Commit Size');
84 IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
85 g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
86 END IF;
87 write_log('Commit SIZE : ' || g_commit_chunk_size);
88
89 write_log('Getting Debug Information');
90 IF (FND_PROFILE.DEFINED('BIX_DBI_DEBUG')) THEN
91 g_debug_flag := nvl(FND_PROFILE.VALUE('BIX_DBI_DEBUG'), 'N');
92 END IF;
93 write_log('Debug Flag : ' || g_debug_flag);
94
95 --
96 --Comment this out since it is not used - sometimes it causes character to number conversion error if it has decimals
97 --
98 --write_log('Getting Agent Cost');
99 --IF (FND_PROFILE.DEFINED('BIX_DM_AGENT_COST')) THEN
100 --g_agent_cost := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_AGENT_COST')) / 3600;
101 --END IF;
102
103 write_log('Agent Cost : ' || g_agent_cost);
104
105 write_log('Getting schema information');
106 IF(FND_INSTALLATION.GET_APP_INFO('BIX', l_status, l_industry, g_bix_schema)) THEN
107 NULL;
108 END IF;
109 write_log('BIX Schema : ' || g_bix_schema);
110
111 write_log('Truncating tables');
112 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIX_AGENT_SESSION_F');
113 Truncate_Table('BIX_AGENT_SESSION_F');
114 Truncate_Table('BIX_SESSIONS');
115 Truncate_Table('BIX_AGENT_SESSION_STG');
116
117
118 write_log('Setting the sort and hash are size');
119 execute immediate 'alter session set sort_area_size=1048576000';
120 execute immediate 'alter session set hash_area_size=1048576000';
121
122 write_log('Finished procedure init at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
123 EXCEPTION
124 WHEN OTHERS THEN
125 write_log('Error in init : Error : ' || sqlerrm);
126 RAISE;
127 END init;
128
129 PROCEDURE clean_up IS
130
131 BEGIN
132 write_log('Start of the procedure clean_up at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
133
134 write_log('Truncating the tables');
135 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIX_AGENT_SESSION_F');
136 Truncate_Table('BIX_AGENT_SESSION_F');
137 Truncate_Table('BIX_SESSIONS');
138 Truncate_Table('BIX_AGENT_SESSION_STG');
139
140 write_log('Finished procedure clean_up at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
141 EXCEPTION
142 WHEN OTHERS THEN
143 write_log('Error in cleaning up the tables : Error : ' || sqlerrm);
144 RAISE;
145 END CLEAN_UP;
146
147 PROCEDURE collect_idle_time IS
148 BEGIN
149
150 write_log('Start of the procedure collect_idle_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
151
152 INSERT /*+ append parallel(bss) */ INTO bix_agent_session_stg bss
153 (bss.agent_id,
154 bss.server_group_id,
155 bss.schedule_id,
156 bss.campaign_id,
157 bss.application_id,
158 bss.time_id,
159 bss.period_type_id,
160 bss.period_start_date,
161 bss.period_start_time,
162 bss.day_of_week,
163 bss.created_by,
164 bss.creation_date,
165 bss.last_updated_by,
166 bss.last_update_date,
167 bss.idle_time,
168 bss.request_id,
169 bss.program_application_id,
170 bss.program_id,
171 bss.program_update_date )
172 (SELECT /*+ parallel(bas) */
173 bas.agent_id,
174 bas.server_group_id,
175 -1,
176 -1,
177 bas.application_id,
178 bas.time_id,
179 bas.period_type_id,
180 bas.period_start_date,
181 bas.period_start_time,
182 bas.day_of_week,
183 g_user_id,
184 g_sysdate,
185 g_user_id,
186 g_sysdate,
187 decode(nvl(sum(bas.login_time),0) - nvl(sum(bas.work_time), 0), 0, to_number(null),
188 nvl(sum(bas.login_time),0) - nvl(sum(bas.work_time), 0)),
189 g_request_id,
190 g_program_appl_id,
191 g_program_id,
192 g_sysdate
193 FROM bix_agent_session_stg bas
194 WHERE bas.application_id = 696
195 GROUP BY
196 bas.agent_id,
197 bas.server_group_id,
198 bas.application_id,
199 bas.time_id,
200 bas.period_type_id,
201 bas.period_start_date,
202 bas.period_start_time,
203 bas.day_of_week);
204
205 COMMIT;
206
207 write_log('Finished procedure collect_idle_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
208 EXCEPTION
209 WHEN OTHERS THEN
210 write_log('Error in procedure collect_idle_time : Error : ' || sqlerrm);
211 RAISE;
212 END collect_idle_time;
213
214 PROCEDURE insert_available_row(p_activity_id in g_activity_id_tab,
215 p_agent_id in g_resource_id_tab,
216 p_activity_begin_date in g_begin_date_time_tab,
217 p_activity_end_date in g_end_date_time_tab,
218 p_server_group_id in g_server_group_id_tab,
219 p_application_id in g_application_id_tab,
220 p_schedule_id in g_schedule_id_tab,
221 p_campaign_id in g_campaign_id_tab)
222 IS
223 TYPE available_time_tab is TABLE OF bix_agent_session_f.available_time%TYPE;
224
225 l_agent_id g_resource_id_tab;
226 l_period_start_date g_begin_date_time_tab;
227 l_available_time available_time_tab;
228 l_server_group_id g_server_group_id_tab;
229 l_application_id g_application_id_tab;
230 l_schedule_id g_schedule_id_tab;
231 l_campaign_id g_campaign_id_tab;
232
233 l_begin_date DATE;
234 l_end_date DATE;
235 l_period_start DATE;
236 l_row_counter NUMBER;
237 l_work_start DATE;
238 l_work_end DATE;
239 l_secs NUMBER;
240 j NUMBER;
241 BEGIN
242 write_log('Start of the procedure insert_available_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
243
244 /* Initialize all the variables */
245 j := 0;
246 l_agent_id := g_resource_id_tab();
247 l_period_start_date := g_begin_date_time_tab();
248 l_server_group_id := g_server_group_id_tab();
249 l_application_id := g_application_id_tab();
250 l_available_time := available_time_tab();
251 l_schedule_id := g_schedule_id_tab();
252 l_campaign_id := g_campaign_id_tab();
253
254 /* Loop through all the activities rows returned by the cursor */
255 FOR i in p_activity_id.FIRST .. p_activity_id.LAST LOOP
256 l_begin_date := p_activity_begin_date(i);
257
258 l_end_date := p_activity_end_date(i);
259
260 IF (l_begin_date < l_end_date) THEN
261 /* Get the half hour bucket of the session begin date time */
262 SELECT trunc(l_begin_date)
263 INTO l_period_start
264 FROM DUAL;
265
266 l_row_counter := 0; /* Variable to identify the first row of the session in the while loop */
267
268 /* Loop through the session record and insert a record for each half hour bucket */
269 WHILE ( l_period_start < l_end_date )
270 LOOP
271 j := j + 1;
272 IF (l_row_counter = 0 )
273 THEN
274 l_work_start := l_begin_date;
275 ELSE
276 l_work_start := l_period_start;
277 END IF;
278
279 l_work_end := l_period_start + 1;
280 IF ( l_work_end > l_end_date )
281 THEN
282 l_work_end := l_end_date ;
283 END IF;
284
285 l_secs := round((l_work_end - l_work_start) * 24 * 3600);
286
287 l_agent_id.extend(1);
288 l_period_start_date.extend(1);
289 l_server_group_id.extend(1);
290 l_application_id.extend(1);
291 l_schedule_id.extend(1);
292 l_campaign_id.extend(1);
293 l_available_time.extend(1);
294
295 l_agent_id(j) := p_agent_id(i);
296 l_period_start_date(j) := l_period_start;
297 l_server_group_id(j) := p_server_group_id(i);
298 l_application_id(j) := p_application_id(i);
299 l_schedule_id(j) := p_schedule_id(i);
300 l_campaign_id(j) := p_campaign_id(i);
301 l_available_time(j) := l_secs;
302
303 l_row_counter := l_row_counter + 1;
304 l_period_start := l_period_start + 1;
305
306 END LOOP; -- end of WHILE loop
307 END IF; /* end if (l_begin_date > l_end_date) */
308 END LOOP;
309
310 /* Bulk insert all the rows in the staging area */
311 IF (l_agent_id.COUNT > 0) THEN
312 FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
313 INSERT /*+ append */ INTO bix_agent_session_stg bas (
314 bas.agent_id
315 ,bas.server_group_id
316 ,bas.schedule_id
317 ,bas.campaign_id
318 ,bas.application_id
319 ,bas.time_id
320 ,bas.period_type_id
321 ,bas.period_start_date
322 ,bas.period_start_time
323 ,bas.day_of_week
324 ,bas.last_update_date
325 ,bas.last_updated_by
326 ,bas.creation_date
327 ,bas.created_by
328 ,bas.last_update_login
329 ,bas.available_time
330 ,bas.request_id
331 ,bas.program_application_id
332 ,bas.program_id
333 ,bas.program_update_date)
334 VALUES (
335 l_agent_id(i)
336 ,l_server_group_id(i)
337 ,l_schedule_id(i)
338 ,l_campaign_id(i)
339 ,l_application_id(i)
340 ,to_number(to_char(l_period_start_date(i), 'J'))
341 ,1
342 ,TRUNC(l_period_start_date(i))
343 ,'00:00'
344 ,TO_CHAR(l_period_start_date(i),'D')
345 ,g_sysdate
346 ,g_user_id
347 ,g_sysdate
348 ,g_user_id
349 ,g_user_id
350 ,decode(l_available_time(i), 0, to_number(null), l_available_time(i))
351 ,g_request_id
352 ,g_program_appl_id
353 ,g_program_id
354 ,g_sysdate);
355 END IF;
356
357 write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
358
359 write_log('Finished procedure insert_available_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
360 EXCEPTION
361 WHEN OTHERS THEN
362 write_log('Error in insert_available_row : Error : ' || sqlerrm);
363 RAISE;
364 END insert_available_row;
365
366 PROCEDURE collect_available_time IS
367 CURSOR get_available_time IS
368 SELECT /*+ parallel(iss) parallel(isa1) parallel(isa2) parallel(bis1) parallel(res) parallel(csh)
369 pq_distribute(iss hash,hash) pq_distribute(isa1 hash,hash)
370 pq_distribute(isa2 hash,hash) pq_distribute(bis1 hash,hash)
371 pq_distribute(res hash,hash) pq_distribute(csh hash,hash)
372 use_hash(iss,isa1,isa2,bis1,res,csh) */
373 isa1.activity_id activity_id
374 ,iss.resource_id resource_id
375 ,isa1.begin_date_time begin_date_time
376 ,nvl(isa1.deliver_date_time, nvl(isa1.end_date_time, isa1.begin_date_time))
377 end_date_time
378 ,nvl(res.server_group_id,-1) server_group_id
379 ,iss.application_id application_id
380 ,decode(isa2.category_type, 'CSCH', nvl(csh.schedule_id, -1), -1)
381 schedule_id
382 ,decode(isa2.category_type, 'CSCH', nvl(csh.campaign_id, -1), -1)
383 campaign_id
384 FROM
385 ieu_sh_sessions iss
386 ,ieu_sh_activities isa1
387 ,ieu_sh_activities isa2
388 ,jtf_rs_resource_extns res
389 ,ams_campaign_schedules_b csh
390 WHERE isa1.last_update_date > g_collect_start_date
391 AND isa1.last_update_date <= g_collect_end_date
392 AND iss.session_id = isa1.session_id
393 AND iss.application_id = 696
394 AND isa1.activity_type_code = 'MEDIA'
395 AND isa1.parent_cycle_id = isa2.activity_id
396 AND isa2.activity_type_code = 'MEDIA_CYCLE'
397 AND iss.resource_id = res.resource_id
398 AND decode(isa2.category_type, 'CSCH', to_number(nvl(isa2.category_value, -1)), -1) = csh.schedule_id(+);
399
400 l_activity_id g_activity_id_tab;
401 l_resource_id g_resource_id_tab;
402 l_begin_date_time g_begin_date_time_tab;
403 l_end_date_time g_end_date_time_tab;
404 l_server_group_id g_server_group_id_tab;
405 l_application_id g_application_id_tab;
406 l_schedule_id g_schedule_id_tab;
407 l_campaign_id g_campaign_id_tab;
408
409 l_no_of_records NUMBER;
410 BEGIN
411
412 write_log('Start of the procedure collect_available_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
413
414 OPEN get_available_time;
415
416 LOOP
417 /* Fetch the activity rows in bulk and process them row by row */
418 FETCH get_available_time BULK COLLECT INTO
419 l_activity_id,
420 l_resource_id,
421 l_begin_date_time,
422 l_end_date_time,
423 l_server_group_id,
424 l_application_id,
425 l_schedule_id,
426 l_campaign_id
427 LIMIT g_commit_chunk_size;
428
429 l_no_of_records := l_activity_id.COUNT;
430
431 IF (l_no_of_records > 0) THEN
432 insert_available_row(
433 l_activity_id,
434 l_resource_id,
435 l_begin_date_time,
436 l_end_date_time,
437 l_server_group_id,
438 l_application_id,
439 l_schedule_id,
440 l_campaign_id);
441
442 l_activity_id.TRIM(l_no_of_records);
443 l_resource_id.TRIM(l_no_of_records);
444 l_begin_date_time.TRIM(l_no_of_records);
445 l_end_date_time.TRIM(l_no_of_records);
446 l_server_group_id.TRIM(l_no_of_records);
447 l_application_id.TRIM(l_no_of_records);
448 l_schedule_id.TRIM(l_no_of_records);
449 l_campaign_id.TRIM(l_no_of_records);
450 END IF;
451
452 EXIT WHEN get_available_time%NOTFOUND;
453
454 END LOOP;
455
456 CLOSE get_available_time;
457
458 COMMIT;
459
460 write_log('Finished procedure collect_available_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
461 EXCEPTION
462 WHEN OTHERS THEN
463 write_log('Error in procedure collect_available_time : Error : ' || sqlerrm);
464 IF (get_available_time%ISOPEN) THEN
465 CLOSE get_available_time;
466 END IF;
467 RAISE;
468 END collect_available_time;
469
470 PROCEDURE insert_work_row(p_activity_id in g_activity_id_tab,
471 p_agent_id in g_resource_id_tab,
472 p_activity_begin_date in g_begin_date_time_tab,
473 p_activity_end_date in g_end_date_time_tab,
474 p_server_group_id in g_server_group_id_tab,
475 p_application_id in g_application_id_tab,
476 p_schedule_id in g_schedule_id_tab,
477 p_campaign_id in g_campaign_id_tab)
478 IS
479 TYPE work_time_tab is TABLE OF bix_agent_session_f.work_time%TYPE;
480
481 l_agent_id g_resource_id_tab;
482 l_period_start_date g_begin_date_time_tab;
483 l_work_time work_time_tab;
484 l_server_group_id g_server_group_id_tab;
485 l_application_id g_application_id_tab;
486 l_schedule_id g_schedule_id_tab;
487 l_campaign_id g_campaign_id_tab;
488
489 l_begin_date DATE;
490 l_end_date DATE;
491 l_period_start DATE;
492 l_row_counter NUMBER;
493 l_work_start DATE;
494 l_work_end DATE;
495 l_secs NUMBER;
496 j NUMBER;
497 BEGIN
498 write_log('Start of the procedure insert_work_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
499
500 /* Initialize all the variables */
501 j := 0;
502 l_agent_id := g_resource_id_tab();
503 l_period_start_date := g_begin_date_time_tab();
504 l_server_group_id := g_server_group_id_tab();
505 l_application_id := g_application_id_tab();
506 l_work_time := work_time_tab();
507 l_schedule_id := g_schedule_id_tab();
508 l_campaign_id := g_campaign_id_tab();
509
510 /* Loop through all the activities rows returned by the cursor */
511 FOR i in p_activity_id.FIRST .. p_activity_id.LAST LOOP
512 l_begin_date := p_activity_begin_date(i);
513
514 l_end_date := p_activity_end_date(i);
515
516 IF (l_begin_date < l_end_date) THEN
517 /* Get the half hour bucket of the session begin date time */
518 SELECT trunc(l_begin_date)
519 INTO l_period_start
520 FROM DUAL;
521
522 l_row_counter := 0; /* Variable to identify the first row of the session in the while loop */
523
524 /* Loop through the session record and insert a record for each half hour bucket */
525 WHILE ( l_period_start < l_end_date )
526 LOOP
527 j := j + 1;
528 IF (l_row_counter = 0 )
529 THEN
530 l_work_start := l_begin_date;
531 ELSE
532 l_work_start := l_period_start;
533 END IF;
534
535 l_work_end := l_period_start + 1;
536 IF ( l_work_end > l_end_date )
537 THEN
538 l_work_end := l_end_date ;
539 END IF;
540
541 l_secs := round((l_work_end - l_work_start) * 24 * 3600);
542
543 l_agent_id.extend(1);
544 l_period_start_date.extend(1);
545 l_server_group_id.extend(1);
546 l_application_id.extend(1);
547 l_schedule_id.extend(1);
548 l_campaign_id.extend(1);
549 l_work_time.extend(1);
550
551 l_agent_id(j) := p_agent_id(i);
552 l_period_start_date(j) := l_period_start;
553 l_server_group_id(j) := p_server_group_id(i);
554 l_application_id(j) := p_application_id(i);
555 l_schedule_id(j) := p_schedule_id(i);
556 l_campaign_id(j) := p_campaign_id(i);
557 l_work_time(j) := l_secs;
558
559 l_row_counter := l_row_counter + 1;
560 l_period_start := l_period_start + 1;
561
562 END LOOP; -- end of WHILE loop
563 END IF; /* end if (l_begin_date > l_end_date) */
564 END LOOP;
565
566 /* Bulk insert all the rows in the staging area */
567 IF (l_agent_id.COUNT > 0) THEN
568 FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
569 INSERT /*+ append */ INTO bix_agent_session_stg bas (
570 bas.agent_id
571 ,bas.server_group_id
572 ,bas.schedule_id
573 ,bas.campaign_id
574 ,bas.application_id
575 ,bas.time_id
576 ,bas.period_type_id
577 ,bas.period_start_date
578 ,bas.period_start_time
579 ,bas.day_of_week
580 ,bas.last_update_date
581 ,bas.last_updated_by
582 ,bas.creation_date
583 ,bas.created_by
584 ,bas.last_update_login
585 ,bas.work_time
586 ,bas.request_id
587 ,bas.program_application_id
588 ,bas.program_id
589 ,bas.program_update_date)
590 VALUES (
591 l_agent_id(i)
592 ,l_server_group_id(i)
593 ,l_schedule_id(i)
594 ,l_campaign_id(i)
595 ,l_application_id(i)
596 ,to_number(to_char(l_period_start_date(i), 'J'))
597 ,1
598 ,TRUNC(l_period_start_date(i))
599 ,'00:00'
600 ,TO_CHAR(l_period_start_date(i),'D')
601 ,g_sysdate
602 ,g_user_id
603 ,g_sysdate
604 ,g_user_id
605 ,g_user_id
606 ,decode(l_work_time(i), 0, to_number(null), l_work_time(i))
607 ,g_request_id
608 ,g_program_appl_id
609 ,g_program_id
610 ,g_sysdate);
611 END IF;
612
613 write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
614
615 write_log('Finished procedure insert_work_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
616 EXCEPTION
617 WHEN OTHERS THEN
618 write_log('Error in insert_work_row : Error : ' || sqlerrm);
619 RAISE;
620 END insert_work_row;
621
622 PROCEDURE collect_work_time IS
623 CURSOR get_work_time IS
624 SELECT /*+ parallel(iss) parallel(isa) parallel(isamed) parallel(res) parallel(csh)
625 pq_distribute(iss hash,hash) pq_distribute(isa hash,hash)
626 pq_distribute(isamed hash,hash) pq_distribute(res hash,hash)
627 pq_distribute(csh hash,hash)
628 use_hash(iss,isa,isamed,res,csh)*/
629 isa.activity_id activity_id
630 ,iss.resource_id resource_id
631 ,isa.begin_date_time begin_date_time
632 ,nvl(isa.end_date_time, nvl(isamed.end_Date_time,isa.begin_Date_time)) end_date_time
633 ,nvl(res.server_group_id,-1) server_group_id
634 ,iss.application_id application_id
635 ,decode(isa.category_type, 'CSCH', nvl(csh.schedule_id, -1), -1)
636 schedule_id
637 ,decode(isa.category_type, 'CSCH', nvl(csh.campaign_id, -1), -1)
638 campaign_id
639 FROM
640 ieu_sh_sessions iss
641 ,ieu_sh_activities isa
642 ,jtf_rs_resource_extns res
643 ,ams_campaign_schedules_b csh
644 ,(select parent_cycle_id,max(isamed.end_Date_time) end_date_time from ieu_sh_Activities isamed,ieu_sh_sessions isamedsess
645 where activity_type_code='MEDIA'
646 and isamedsess.session_id=isamed.session_id
647 and isamedsess.application_id=696
648 group by parent_cycle_id) isamed
649 WHERE isa.last_update_date > g_collect_start_date
650 AND isa.last_update_date <= g_collect_end_date
651 AND iss.session_id = isa.session_id
652 AND iss.application_id = 696
653 AND isa.activity_type_code = 'MEDIA_CYCLE'
654 AND isa.activity_id=isamed.parent_cycle_id(+)
655 AND iss.resource_id = res.resource_id
656 AND decode(isa.category_type, 'CSCH', to_number(nvl(isa.category_value, -1)), -1) = csh.schedule_id(+);
657
658 l_activity_id g_activity_id_tab;
659 l_resource_id g_resource_id_tab;
660 l_begin_date_time g_begin_date_time_tab;
661 l_end_date_time g_end_date_time_tab;
662 l_server_group_id g_server_group_id_tab;
663 l_application_id g_application_id_tab;
664 l_schedule_id g_schedule_id_tab;
665 l_campaign_id g_campaign_id_tab;
666
667 l_no_of_records NUMBER;
668 BEGIN
669
670 write_log('Start of the procedure collect_work_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
671
672 OPEN get_work_time;
673
674 LOOP
675 /* Fetch the activity rows in bulk and process them row by row */
676 FETCH get_work_time BULK COLLECT INTO
677 l_activity_id,
678 l_resource_id,
679 l_begin_date_time,
680 l_end_date_time,
681 l_server_group_id,
682 l_application_id,
683 l_schedule_id,
684 l_campaign_id
685 LIMIT g_commit_chunk_size;
686
687 l_no_of_records := l_activity_id.COUNT;
688
689 IF (l_no_of_records > 0) THEN
690 insert_work_row(
691 l_activity_id,
692 l_resource_id,
693 l_begin_date_time,
694 l_end_date_time,
695 l_server_group_id,
696 l_application_id,
697 l_schedule_id,
698 l_campaign_id);
699
700 l_activity_id.TRIM(l_no_of_records);
701 l_resource_id.TRIM(l_no_of_records);
702 l_begin_date_time.TRIM(l_no_of_records);
703 l_end_date_time.TRIM(l_no_of_records);
704 l_server_group_id.TRIM(l_no_of_records);
705 l_application_id.TRIM(l_no_of_records);
706 l_schedule_id.TRIM(l_no_of_records);
707 l_campaign_id.TRIM(l_no_of_records);
708 END IF;
709
710 EXIT WHEN get_work_time%NOTFOUND;
711
712 END LOOP;
713
714 CLOSE get_work_time;
715
716 COMMIT;
717
718 write_log('Finished procedure collect_work_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
719 EXCEPTION
720 WHEN OTHERS THEN
721 write_log('Error in procedure collect_work_time : Error : ' || sqlerrm);
722 IF (get_work_time%ISOPEN) THEN
723 CLOSE get_work_time;
724 END IF;
725 RAISE;
726 END collect_work_time;
727
728 PROCEDURE insert_login_row(p_session_id in g_session_id_tab,
729 p_agent_id in g_resource_id_tab,
730 p_session_begin_date in g_begin_date_time_tab,
731 p_session_end_date in g_end_date_time_tab,
732 p_server_group_id in g_server_group_id_tab,
733 p_application_id in g_application_id_tab)
734 IS
735 TYPE login_time_tab is TABLE OF bix_agent_session_f.login_time%TYPE;
736 TYPE session_id_tab is TABLE OF ieu_sh_sessions.session_id%TYPE;
737 TYPE collect_date_tab is TABLE OF ieu_sh_sessions.end_date_time%TYPE;
738
739 l_agent_id g_resource_id_tab;
740 l_period_start_date g_begin_date_time_tab;
741 l_login_time login_time_tab;
742 l_server_group_id g_server_group_id_tab;
743 l_application_id g_application_id_tab;
744
745 l_session_id session_id_tab;
746 l_collect_date collect_date_tab;
747
748 l_begin_date DATE;
749 l_end_date DATE;
750 l_period_start DATE;
751 l_row_counter NUMBER;
752 l_login_start DATE;
753 l_login_end DATE;
754 l_secs NUMBER;
755 j NUMBER;
756 k NUMBER;
757 BEGIN
758 write_log('Start of the procedure insert_login_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
759
760 /* Initialize all the variables */
761 j := 0;
762 k := 0;
763 l_agent_id := g_resource_id_tab();
764 l_period_start_date := g_begin_date_time_tab();
765 l_server_group_id := g_server_group_id_tab();
766 l_application_id := g_application_id_tab();
767 l_login_time := login_time_tab();
768 l_session_id := session_id_tab();
769 l_collect_date := collect_date_tab();
770
771 /* Loop through all the session rows returned by the cursor */
772 FOR i in p_session_id.FIRST .. p_session_id.LAST LOOP
773 l_begin_date := p_session_begin_date(i);
774 l_end_date := p_session_end_date(i);
775
776 IF (l_begin_date < l_end_date) THEN
777 k := k + 1;
778 l_session_id.extend(1);
779 l_collect_date.extend(1);
780
781 l_session_id(k) := p_session_id(i);
782 l_collect_date(k) := l_end_date;
783
784 /* Get the half hour bucket of the session begin date time */
785 SELECT trunc(l_begin_date)
786 INTO l_period_start
787 FROM DUAL;
788
789 l_row_counter := 0; /* Variable to identify the first row of the session in the while loop */
790
791 /* Loop through the session record and insert a record for each half hour bucket */
792 WHILE ( l_period_start < l_end_date )
793 LOOP
794 j := j + 1;
795 IF (l_row_counter = 0 )
796 THEN
797 l_login_start := l_begin_date;
798 ELSE
799 l_login_start := l_period_start;
800 END IF;
801
802 l_login_end := l_period_start + 1;
803 IF ( l_login_end > l_end_date )
804 THEN
805 l_login_end := l_end_date ;
806 END IF;
807
808 l_secs := round((l_login_end - l_login_start) * 24 * 3600);
809
810 l_agent_id.extend(1);
811 l_period_start_date.extend(1);
812 l_login_time.extend(1);
813 l_server_group_id.extend(1);
814 l_application_id.extend(1);
815
816 l_agent_id(j) := p_agent_id(i);
817 l_period_start_date(j) := l_period_start;
818 l_login_time(j) := l_secs;
819 l_server_group_id(j) := p_server_group_id(i);
820 l_application_id(j) := p_application_id(i);
821
822 l_row_counter := l_row_counter + 1;
823 l_period_start := l_period_start + 1;
824
825 END LOOP; -- end of WHILE loop
826 END IF; /* end if (l_begin_date > l_end_date) */
827 END LOOP;
828
829 /* Bulk insert all the rows in the staging area */
830 IF (l_agent_id.COUNT > 0) THEN
831 FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
832 INSERT /*+ append */ INTO bix_agent_session_stg bas (
833 bas.agent_id
834 ,bas.server_group_id
835 ,bas.schedule_id
836 ,bas.campaign_id
837 ,bas.application_id
838 ,bas.time_id
839 ,bas.period_type_id
840 ,bas.period_start_date
841 ,bas.period_start_time
842 ,bas.day_of_week
843 ,bas.last_update_date
844 ,bas.last_updated_by
845 ,bas.creation_date
846 ,bas.created_by
847 ,bas.last_update_login
848 ,bas.login_time
849 ,bas.request_id
850 ,bas.program_application_id
851 ,bas.program_id
852 ,bas.program_update_date)
853 VALUES (
854 l_agent_id(i)
855 ,l_server_group_id(i)
856 ,-1
857 ,-1
858 ,l_application_id(i)
859 ,to_number(to_char(l_period_start_date(i), 'J'))
860 ,1
861 ,TRUNC(l_period_start_date(i))
862 ,'00:00'
863 ,TO_CHAR(l_period_start_date(i),'D')
864 ,g_sysdate
865 ,g_user_id
866 ,g_sysdate
867 ,g_user_id
868 ,g_user_id
869 ,decode(l_login_time(i), 0, to_number(null), l_login_time(i))
870 ,g_request_id
871 ,g_program_appl_id
872 ,g_program_id
873 ,g_sysdate);
874 END IF;
875
876 write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
877
878 IF (l_session_id.COUNT > 0) THEN
879 FORALL i IN l_session_id.FIRST .. l_session_id.LAST
880 INSERT /*+ append */ INTO bix_sessions bis1 (
881 bis1.session_id,
882 bis1.created_by,
883 bis1.creation_date,
884 bis1.last_updated_by,
885 bis1.last_update_date,
886 bis1.curr_collect_date,
887 bis1.last_collect_date,
888 bis1.request_id,
889 bis1.program_application_id,
890 bis1.program_id,
891 bis1.program_update_date )
892 VALUES (
893 l_session_id(i),
894 g_user_id,
895 g_sysdate,
896 g_user_id,
897 g_sysdate,
898 l_collect_date(i),
899 l_collect_date(i),
900 g_request_id,
901 g_program_appl_id,
902 g_program_id,
903 g_sysdate);
904 END IF;
905 write_log('Finished procedure insert_login_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
906 EXCEPTION
907 WHEN OTHERS THEN
908 write_log('Error in insert_login_row : Error : ' || sqlerrm);
909 RAISE;
910 END insert_login_row;
911
912 PROCEDURE collect_login_time IS
913 CURSOR get_login_time IS
914 select /*+ use_hash(res) parallel(res) parallel(inv) */ inv.session_id, inv.resource_id, inv.begin_date_time,
915 nvl (inv.end_date_time, lead (inv.prevsd, 1, inv.maxval) over
916 (partition by inv.resource_id order by begin_date_time)) end_date_time,
917 nvl (res.server_group_id, -1) server_group_id,
918 decode (inv.application_id, 696, 696, 680, 680, 0) application_id
919 from jtf_rs_resource_extns res,
920 (select /*+ parallel(x) */ type, resource_id, begin_date_time,
921 end_date_time, session_id, application_id, lag (begin_date_time)
922 over (partition by resource_id order by begin_date_time) prevsd,
923 max (begin_date_time)
924 over (partition by resource_id order by begin_date_time) maxval
925 from (
926 select /*+ parallel(sess1) */ 1 type, resource_id, begin_date_time, end_date_time,
927 session_id, application_id
928 from ieu_sh_sessions sess1
929 WHERE last_update_date > g_collect_start_date
930 AND last_update_date <= g_collect_end_date
931 union all
932 select /*+ parallel(msegs) */ 2 type, resource_id,
933 start_date_time begin_date_time, null end_date_time,
934 null session_id, null application_id
935 from jtf_ih_media_item_lc_segs msegs,
936 jtf_ih_media_itm_lc_seg_tys segs
937 where msegs.milcs_type_id = segs.milcs_type_id
938 and segs.milcs_code in ('EMAIL_FETCH', 'EMAIL_REPLY',
939 'EMAIL_DELETED', 'EMAIL_OPEN', 'EMAIL_REQUEUED',
940 'EMAIL_REROUTED_DIFF_CLASS', 'EMAIL_REROUTED_DIFF_ACCT',
941 'EMAIL_SENT', 'EMAIL_TRANSFERRED', 'EMAIL_ASSIGN',
942 'EMAIL_COMPOSE', 'WITH_AGENT', 'EMAIL_ESCALATED')) x) inv
943 where inv.resource_id = res.resource_id
944 and type = 1;
945
946 /**************************
947 ,inv1.begin_date_time begin_date_time
948 ,decode(inv1.end_date_time, to_date(null), decode(max(mseg.start_date_time), to_date(null), inv1.begin_date_time,
949 max(mseg.start_date_time)), inv1.end_date_time)
950 end_date_time
951 ,nvl(res.server_group_id,-1) server_group_id
952 ,decode(inv1.application_id, 696, 696, 680, 680, 0)
953 application_id
954 FROM
955 ( SELECT + full(msegs)
956 resource_id, start_date_time FROM jtf_ih_media_item_lc_segs msegs
957 ,jtf_ih_media_itm_lc_seg_tys segs
958 WHERE msegs.milcs_type_id = segs.milcs_type_id
959 AND segs.milcs_code IN
960 ('EMAIL_FETCH'
961 ,'EMAIL_REPLY'
962 ,'EMAIL_DELETED'
963 ,'EMAIL_OPEN'
964 ,'EMAIL_REQUEUED'
965 ,'EMAIL_REROUTED_DIFF_CLASS'
966 ,'EMAIL_REROUTED_DIFF_ACCT'
967 ,'EMAIL_SENT'
968 ,'EMAIL_TRANSFERRED'
969 ,'EMAIL_ASSIGN'
970 ,'EMAIL_COMPOSE'
971 ,'WITH_AGENT'
972 ,'EMAIL_ESCALATED'
973 )
974 ) mseg
975 ,jtf_rs_resource_extns res
976 ,(
977 SELECT + full(sess1) session_id, resource_id, application_id, begin_date_time, end_date_time,
978 lead(begin_date_time, 1)
979 over (partition by resource_id order by begin_date_time) next_sess_begin_date_time
980 FROM ieu_sh_sessions sess1
981 WHERE last_update_date > g_collect_start_date
982 AND last_update_date <= g_collect_end_date
983 ) inv1
984 WHERE inv1.resource_id = res.resource_id
985 AND mseg.resource_id(+) = inv1.resource_id
986 AND mseg.start_date_time(+) >= inv1.begin_date_time
987 AND mseg.start_date_time(+) < nvl(inv1.next_sess_begin_date_time, g_sysdate)
988 GROUP BY inv1.session_id, inv1.resource_id, inv1.begin_date_time, inv1.end_date_time, res.server_group_id, inv1.application_id;
989 **************************************************/
990
991 l_session_id g_session_id_tab;
992 l_resource_id g_resource_id_tab;
993 l_begin_date_time g_begin_date_time_tab;
994 l_end_date_time g_end_date_time_tab;
995 l_server_group_id g_server_group_id_tab;
996 l_application_id g_application_id_tab;
997
998 l_no_of_records NUMBER;
999 BEGIN
1000
1001 write_log('Start of the procedure collect_login_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1002
1003 OPEN get_login_time;
1004
1005 LOOP
1006
1007 /* Fetch the login rows in bulk and process them row by row */
1008 FETCH get_login_time BULK COLLECT INTO
1009 l_session_id,
1010 l_resource_id,
1011 l_begin_date_time,
1012 l_end_date_time,
1013 l_server_group_id,
1014 l_application_id
1015 LIMIT g_commit_chunk_size;
1016
1017 l_no_of_records := l_session_id.COUNT;
1018
1019 IF (l_no_of_records > 0) THEN
1020 insert_login_row(
1021 l_session_id,
1022 l_resource_id,
1023 l_begin_date_time,
1024 l_end_date_time,
1025 l_server_group_id,
1026 l_application_id);
1027
1028 l_session_id.TRIM(l_no_of_records);
1029 l_resource_id.TRIM(l_no_of_records);
1030 l_begin_date_time.TRIM(l_no_of_records);
1031 l_end_date_time.TRIM(l_no_of_records);
1032 l_server_group_id.TRIM(l_no_of_records);
1033 l_application_id.TRIM(l_no_of_records);
1034 END IF;
1035
1036 COMMIT;
1037
1038 EXIT WHEN get_login_time%NOTFOUND;
1039
1040 END LOOP;
1041
1042 CLOSE get_login_time;
1043
1044 COMMIT;
1045
1046 write_log('Finished procedure collect_login_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 write_log('Error in procedure collect_login_time : Error : ' || sqlerrm);
1050 IF (get_login_time%ISOPEN) THEN
1051 CLOSE get_login_time;
1052 END IF;
1053 RAISE;
1054 END collect_login_time;
1055
1056 PROCEDURE merge_data IS
1057
1058 BEGIN
1059
1060 write_log('Start of the procedure merge_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1061
1062 /* Move the data from the staging area to the summary table bix_agent_session_f */
1063 INSERT /*+ append parallel(bas) */ INTO bix_agent_session_f bas
1064 (bas.agent_id,
1065 bas.server_group_id,
1066 bas.schedule_id,
1067 bas.campaign_id,
1068 bas.application_id,
1069 bas.time_id,
1070 bas.period_type_id,
1071 bas.period_start_date,
1072 bas.period_start_time,
1073 bas.day_of_week,
1074 bas.created_by,
1075 bas.creation_date,
1076 bas.last_updated_by,
1077 bas.last_update_date,
1078 bas.login_time,
1079 bas.work_time,
1080 bas.available_time,
1081 bas.idle_time,
1082 bas.agent_cost,
1083 bas.request_id,
1084 bas.program_application_id,
1085 bas.program_id,
1086 bas.program_update_date )
1087 (SELECT /*+ parallel(bstg) */
1088 bstg.agent_id agent_id,
1089 bstg.server_group_id server_group_id,
1090 bstg.schedule_id schedule_id,
1091 bstg.campaign_id campaign_id,
1092 bstg.application_id application_id,
1093 bstg.time_id time_id,
1094 bstg.period_type_id period_type_id,
1095 bstg.period_start_date period_start_date,
1096 bstg.period_start_time period_start_time,
1097 bstg.day_of_week day_of_week,
1098 g_user_id,
1099 g_sysdate,
1100 g_user_id,
1101 g_sysdate,
1102 sum(bstg.login_time) login_time,
1103 sum(bstg.work_time) work_time,
1104 sum(bstg.available_time) available_time,
1105 sum(bstg.idle_time) idle_time,
1106 decode(nvl(sum(bstg.login_time),0) * g_agent_cost, 0, to_number(null),
1107 nvl(sum(bstg.login_time),0) * g_agent_cost) agent_cost,
1108 g_request_id,
1109 g_program_appl_id,
1110 g_program_id,
1111 g_sysdate
1112 FROM bix_agent_session_stg bstg
1113 GROUP BY
1114 bstg.agent_id,
1115 bstg.server_group_id,
1116 bstg.schedule_id,
1117 bstg.campaign_id,
1118 bstg.application_id,
1119 bstg.time_id,
1120 bstg.period_type_id,
1121 bstg.period_start_date,
1122 bstg.period_start_time,
1123 bstg.day_of_week);
1124
1125 g_rows_ins_upd := g_rows_ins_upd + SQL%ROWCOUNT;
1126 write_log('Total rows merged in bix_agent_session_f : ' || to_char(g_rows_ins_upd));
1127
1128 COMMIT;
1129
1130 write_log('Finished procedure merge_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1131
1132 EXCEPTION
1133 WHEN OTHERS THEN
1134 write_log('Error in procedure merge_data : Error : ' || sqlerrm);
1135 RAISE;
1136 END merge_data;
1137
1138
1139 PROCEDURE collect_day IS
1140 BEGIN
1141 write_log('Start of the procedure collect_day at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1142
1143 write_log('Calling procedure collect_login_time');
1144 collect_login_time;
1145 write_log('End procedure collect_login_time');
1146
1147 write_log('Calling procedure collect_work_time');
1148 collect_work_time;
1149 write_log('End procedure collect_work_time');
1150
1151 write_log('Calling procedure collect_available_time');
1152 collect_available_time;
1153 write_log('End procedure collect_available_time');
1154
1155 write_log('Calling procedure collect_idle_time');
1156 collect_idle_time;
1157 write_log('End procedure collect_idle_time');
1158
1159 write_log('Calling procedure merge_data');
1160 merge_data;
1161 write_log('End procedure merge_data');
1162
1163 write_log('Finished procedure collect_day at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1164 EXCEPTION
1165 WHEN OTHERS THEN
1166 write_log('Error in procedure collect_day : Error : ' || sqlerrm);
1167 RAISE;
1168 END collect_day;
1169
1170 PROCEDURE summarize_data IS
1171 BEGIN
1172
1173 write_log('Start of the procedure summarize_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1174
1175 /* Rollup the half-hour information to day, week, month, quarter and year time bucket */
1176 /* An outer query is necessary after rolling up to day, week, month, quarter and year */
1177 /* as rollup function produces two rows for weeks spanning two months */
1178 INSERT /*+ APPEND PARALLEL(bea) */ INTO BIX_AGENT_SESSION_F bea
1179 (bea.agent_id,
1180 bea.server_group_id,
1181 bea.schedule_id,
1182 bea.campaign_id,
1183 bea.application_id,
1184 bea.time_id,
1185 bea.period_type_id,
1186 bea.period_start_date,
1187 bea.period_start_time,
1188 bea.day_of_week,
1189 bea.created_by,
1190 bea.creation_date,
1191 bea.last_updated_by,
1192 bea.last_update_date,
1193 bea.login_time,
1194 bea.work_time,
1195 bea.available_time,
1196 bea.idle_time,
1197 bea.agent_cost,
1198 bea.request_id,
1199 bea.program_application_id,
1200 bea.program_id,
1201 bea.program_update_date )
1202 (SELECT /*+ PARALLEL(inv1) */
1203 inv1.agent_id,
1204 inv1.server_group_id,
1205 inv1.schedule_id,
1206 inv1.campaign_id,
1207 inv1.application_id,
1208 inv1.time_id,
1209 inv1.period_type_id,
1210 inv1.period_start_date,
1211 '00:00',
1212 inv1.day_of_week,
1213 g_user_id,
1214 g_sysdate,
1215 g_user_id,
1216 g_sysdate,
1217 sum(inv1.login_time),
1218 sum(inv1.work_time),
1219 sum(inv1.available_time),
1220 sum(inv1.idle_time),
1221 sum(inv1.agent_cost),
1222 g_request_id,
1223 g_program_appl_id,
1224 g_program_id,
1225 g_sysdate
1226 FROM
1227 (SELECT /*+ parallel(bes) parallel(ftd)
1228 pq_distribute(bes hash,hash) pq_distribute(ftd hash,hash)
1229 use_hash(bes,ftd) */
1230 bes.agent_id agent_id,
1231 bes.server_group_id,
1232 bes.schedule_id schedule_id,
1233 bes.campaign_id campaign_id,
1234 bes.application_id application_id,
1235 bes.day_of_week day_of_week,
1236 decode(ftd.week_id, null, decode(ftd.ent_period_id, null, decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null,
1237 to_number(null), ftd.ent_year_id), ftd.ent_qtr_id), ftd.ent_period_id), ftd.week_id) time_id,
1238 decode(ftd.week_id, null, decode(ftd.ent_period_id, null, decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null,
1239 to_number(null), 128), 64), 32), 16) period_type_id,
1240 decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
1241 decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_date(null), min(ftd.ent_year_start_date)),
1242 min(ftd.ent_qtr_start_date)), min(ftd.ent_period_start_date)), min(ftd.week_start_date)) period_start_date,
1243 sum(bes.login_time) login_time,
1244 sum(bes.work_time) work_time,
1245 sum(bes.available_time) available_time,
1246 sum(bes.idle_time) idle_time,
1247 sum(bes.agent_cost) agent_cost
1248 FROM BIX_AGENT_SESSION_F bes,
1249 fii_time_day ftd
1250 WHERE bes.time_id = ftd.report_date_julian
1251 AND bes.period_type_id = 1
1252 GROUP BY
1253 bes.agent_id,
1254 bes.server_group_id,
1255 bes.schedule_id,
1256 bes.campaign_id,
1257 bes.application_id,
1258 bes.day_of_week,
1259 ROLLUP (
1260 ftd.ent_year_id,
1261 ftd.ent_qtr_id,
1262 ftd.ent_period_id,
1263 ftd.week_id)
1264 HAVING
1265 decode(ftd.week_id, null, decode(ftd.ent_period_id, null, decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null,
1266 to_number(null), 128), 64), 32), 16) IS NOT NULL) inv1
1267 GROUP BY
1268 inv1.agent_id,
1269 inv1.server_group_id,
1270 inv1.schedule_id,
1271 inv1.campaign_id,
1272 inv1.application_id,
1273 inv1.time_id,
1274 inv1.period_type_id,
1275 inv1.period_start_date,
1276 inv1.day_of_week);
1277
1278 g_rows_ins_upd := g_rows_ins_upd + SQL%ROWCOUNT;
1279
1280 COMMIT;
1281
1282 write_log('Total rows inserted after rolling up in BIX_AGENT_SESSION_F : ' || to_char(g_rows_ins_upd));
1283
1284 write_log('Finished procedure summarize_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1285 EXCEPTION
1286 WHEN OTHERS THEN
1287 write_log('Error in procedure summarize_data : Error : ' || sqlerrm);
1288 RAISE;
1289 END summarize_data;
1290
1291 PROCEDURE check_missing_date(p_start_date IN DATE,
1292 p_end_date IN DATE,
1293 p_has_missing_date OUT NOCOPY BOOLEAN) IS
1294 l_count1 NUMBER;
1295 l_count2 NUMBER;
1296 BEGIN
1297
1298 p_has_missing_date := FALSE;
1299
1300 SELECT count(*)
1301 INTO l_count1
1302 FROM fii_time_day
1303 WHERE report_date between trunc(p_start_date) and trunc(p_end_date);
1304
1305 SELECT (trunc(p_end_date) - trunc(p_start_date)) + 1
1306 INTO l_count2
1307 FROM dual;
1308
1309 IF (l_count1 < l_count2) THEN
1310 p_has_missing_date := TRUE;
1311 END IF;
1312
1313 write_log('Finished procedure check_missing_date at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1314 EXCEPTION
1315 WHEN OTHERS THEN
1316 write_log('Error in procedure check_missing_date : Error : ' || sqlerrm);
1317 RAISE;
1318 END check_missing_date;
1319
1320
1321 ---Cleanup the media/sessions
1322
1323 PROCEDURE cleanup_oltp
1324 IS
1325
1326 BEGIN
1327
1328 --
1329 --Close media items
1330 --
1331 BEGIN
1332 g_errbuf := NULL;
1333 g_retcode := 'S';
1334 CCT_CONCURRENT_PUB.CLOSE_MEDIA_ITEMS(g_errbuf, g_retcode);
1335
1336 IF g_retcode <> 'S'
1337 THEN
1338 RAISE G_OLTP_CLEANUP_ISSUE;
1339 END IF;
1340
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 write_log('Close Media Items exited with: ' ||g_retcode || ' error buffer is: ' || g_errbuf);
1344 RAISE G_OLTP_CLEANUP_ISSUE;
1345 END;
1346
1347 --
1348 --Time out media items - interval hardcoded to 24 hours for now
1349 --
1350 BEGIN
1351 g_errbuf := NULL;
1352 g_retcode := 'S';
1353 CCT_CONCURRENT_PUB.TIMEOUT_MEDIA_ITEMS_RS(g_errbuf, g_retcode,24);
1354
1355 IF g_retcode <> 'S'
1356 THEN
1357 RAISE G_OLTP_CLEANUP_ISSUE;
1358 END IF;
1359
1360 EXCEPTION
1361 WHEN OTHERS THEN
1362 write_log('Timeout Media Items exited with: ' ||g_retcode || ' error buffer is: ' || g_errbuf);
1363 RAISE G_OLTP_CLEANUP_ISSUE;
1364 END;
1365
1366
1367 --IEU Session History Cleanup
1368
1369 BEGIN
1370 g_errbuf := NULL;
1371 g_retcode := 'S';
1372 IEU_SH_CON_PVT.IEU_SH_END_IDLE_TRANS(g_errbuf, g_retcode,NULL,'3',8);
1373
1374 IF g_retcode <> 'S'
1375 THEN
1376 RAISE G_OLTP_CLEANUP_ISSUE;
1377 END IF;
1378
1379 EXCEPTION
1380 WHEN OTHERS THEN
1381 write_log('Timeout Media Items exited with: ' ||g_retcode || ' error buffer is: ' || g_errbuf);
1382 RAISE G_OLTP_CLEANUP_ISSUE;
1383 END;
1384
1385 END cleanup_oltp;
1386
1387 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
1388 retcode OUT NOCOPY VARCHAR2,
1389 p_start_date IN VARCHAR2,
1390 p_end_date IN VARCHAR2)
1391 IS
1392
1393 l_has_missing_date BOOLEAN := FALSE;
1394
1395 BEGIN
1396 errbuf := null;
1397 retcode := 0;
1398
1399 write_log('Collection start date : ' || p_start_date);
1400 write_log('Collection end date : ' || p_end_date);
1401
1402
1403 cleanup_oltp;
1404
1405 g_collect_start_date := TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
1406 g_collect_end_date := TO_DATE(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
1407
1408 /* Check if the time dimension is populated for the collection date range ; if not exit */
1409 check_missing_date(g_collect_start_date, g_collect_end_date, l_has_missing_date);
1410 IF (l_has_missing_date) THEN
1411 write_log('Time dimension is not populated for the entire collection date range');
1412 RAISE G_TIME_DIM_MISSING;
1413 END IF;
1414
1415 /* collect the half hour data for the entire date range */
1416 write_log('Calling procedure collect_day');
1417 collect_day;
1418 write_log('End procedure collect_day');
1419
1420 /* Summarize data to day, week, month, quater and year time buckets */
1421 write_log('Calling procedure summarize_data');
1422 summarize_data;
1423 write_log('End procedure summarize_data');
1424
1425 write_log('Truncating the table bix_agent_session_stg');
1426 Truncate_Table('BIX_AGENT_SESSION_STG');
1427 write_log('Done truncating the table bix_agent_session_stg');
1428
1429 write_log('Finished Procedure BIX_SESSION_LOAD_PKG with success at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1430
1431 write_log('Calling procedure WRAPUP');
1432 bis_collection_utilities.wrapup(
1433 p_status => TRUE,
1434 p_count => g_rows_ins_upd,
1435 p_message => NULL,
1436 p_period_from => g_collect_start_date,
1437 p_period_to => g_collect_end_date);
1438
1439 EXCEPTION
1440 WHEN G_TIME_DIM_MISSING THEN
1441 retcode := -1;
1442 errbuf := 'Time Dimension is not populated for the entire collection range';
1443 bis_collection_utilities.wrapup(
1444 p_status => FALSE,
1445 p_count => 0,
1446 p_message => 'eMail summary package failed : Error : Time dimension is not populated',
1447 p_period_from => g_collect_start_date,
1448 p_period_to => g_collect_end_date);
1449 WHEN OTHERS THEN
1450 clean_up;
1451 retcode := SQLCODE;
1452 errbuf := SQLERRM;
1453 bis_collection_utilities.wrapup(
1454 p_status => FALSE,
1455 p_count => 0,
1456 p_message => 'eMail summary package failed : error : ' || sqlerrm,
1457 p_period_from => g_collect_start_date,
1458 p_period_to => g_collect_end_date);
1459 END main;
1460
1461 PROCEDURE load (errbuf OUT NOCOPY VARCHAR2,
1462 retcode OUT NOCOPY VARCHAR2)
1463 IS
1464 l_start_date DATE;
1465 BEGIN
1466 init;
1467 write_log('End procedure init');
1468
1469 /* Get the global start date */
1470 l_start_date := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
1471
1472
1473 Main(errbuf,
1474 retcode,
1475 TO_CHAR(l_start_date, 'YYYY/MM/DD HH24:MI:SS'),
1476 TO_CHAR(g_sysdate,'YYYY/MM/DD HH24:MI:SS'));
1477
1478 EXCEPTION
1479 WHEN OTHERS THEN
1480 RAISE;
1481 END load;
1482
1483 END BIX_SESSION_LOAD_PKG;