[Home] [Help]
PACKAGE BODY: APPS.BIX_SESSION_SUMMARY_PKG
Source
1 PACKAGE BODY BIX_SESSION_SUMMARY_PKG AS
2 /*$Header: bixsessd.plb 120.2 2006/02/13 15:11:53 anasubra noship $ */
3
4 g_request_id NUMBER;
5 g_program_appl_id NUMBER;
6 g_program_id NUMBER;
7 g_user_id NUMBER;
8 g_bix_schema VARCHAR2(30) := 'BIX';
9 g_rows_ins_upd NUMBER;
10 g_commit_chunk_size NUMBER;
11 g_no_of_jobs NUMBER := 0;
12 g_collect_start_date DATE;
13 g_collect_end_date DATE;
14 g_sysdate DATE;
15 g_debug_flag VARCHAR2(1) := 'N';
16 g_agent_cost NUMBER;
17
18 g_errbuf VARCHAR2(1000);
19 g_retcode VARCHAR2(10) := 'S';
20
21 MAX_LOOP CONSTANT NUMBER := 180;
22
23 G_OLTP_CLEANUP_ISSUE EXCEPTION;
24 G_TIME_DIM_MISSING EXCEPTION;
25 G_CHILD_PROCESS_ISSUE EXCEPTION;
26 G_PARAM_MISMATCH EXCEPTION;
27
28 TYPE WorkerList is table of NUMBER index by binary_integer;
29 g_worker WorkerList;
30
31 TYPE g_session_id_tab IS TABLE OF ieu_sh_sessions.session_id%TYPE;
32 TYPE g_activity_id_tab IS TABLE OF ieu_sh_activities.activity_id%TYPE;
33 TYPE g_resource_id_tab IS TABLE OF ieu_sh_sessions.resource_id%TYPE;
34 TYPE g_begin_date_time_tab IS TABLE OF ieu_sh_sessions.begin_date_time%TYPE;
35 TYPE g_end_date_time_tab IS TABLE OF ieu_sh_sessions.end_date_time%TYPE;
36 TYPE g_last_collect_date_tab IS TABLE OF bix_sessions.last_collect_date%TYPE;
37 TYPE g_server_group_id_tab IS TABLE OF jtf_rs_resource_extns.server_group_id%TYPE;
38 TYPE g_application_id_tab IS TABLE OF ieu_sh_sessions.application_id%TYPE;
39 TYPE g_schedule_id_tab IS TABLE OF ams_campaign_schedules_b.schedule_id%TYPE;
40 TYPE g_campaign_id_tab IS TABLE OF ams_campaign_schedules_b.campaign_id%TYPE;
41
42 PROCEDURE Write_Log (p_msg IN VARCHAR2) IS
43 BEGIN
44 IF (g_debug_flag = 'Y') THEN
45 BIS_COLLECTION_UTILITIES.log(p_msg);
46 END IF;
47 EXCEPTION
48 WHEN OTHERS THEN
49 RAISE;
50 END Write_Log;
51
52 PROCEDURE truncate_table (p_table_name in varchar2) is
53
54 l_stmt varchar2(400);
55 BEGIN
56 write_log('Start of the procedure truncate_table at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
57
58 l_stmt:='truncate table '||g_bix_schema||'.'|| p_table_name;
59 execute immediate l_stmt;
60
61 write_log('Table ' || p_table_name || ' has been truncated');
62
63 write_log('Finished procedure truncate_table at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
64 EXCEPTION
65 WHEN OTHERS THEN
66 write_log('Error in truncate_table : Error : ' || sqlerrm);
67 RAISE;
68 END truncate_table;
69
70 PROCEDURE init IS
71
72 l_status VARCHAR2(30);
73 l_industry VARCHAR2(30);
74 BEGIN
75
76 IF (BIS_COLLECTION_UTILITIES.SETUP('BIX_AGENT_SESSION_F') = FALSE) THEN
77 RAISE_APPLICATION_ERROR(-20000, 'BIS_COLLECTION_UTILITIES.setup has failed');
78 END IF;
79
80 write_log('Start of the procedure init at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
81
82 write_log('Initializing global variables');
83
84 g_request_id := FND_GLOBAL.CONC_REQUEST_ID();
85 g_program_appl_id := FND_GLOBAL.PROG_APPL_ID();
86 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
87 g_user_id := FND_GLOBAL.USER_ID();
88 g_sysdate := SYSDATE;
89 g_commit_chunk_size := 10000;
90 g_rows_ins_upd := 0;
91
92 write_log('Getting Commit Size');
93 IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
94 g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
95 END IF;
96 write_log('Commit SIZE : ' || g_commit_chunk_size);
97
98 write_log('Getting Debug Information');
99 IF (FND_PROFILE.DEFINED('BIX_DBI_DEBUG')) THEN
100 g_debug_flag := nvl(FND_PROFILE.VALUE('BIX_DBI_DEBUG'), 'N');
101 END IF;
102 write_log('Debug Flag : ' || g_debug_flag);
103
104 write_log('Getting Agent Cost');
105
106 g_agent_cost := 0;
107 --IF (FND_PROFILE.DEFINED('BIX_DM_AGENT_COST')) THEN
108 --g_agent_cost := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_AGENT_COST')) / 3600;
109 --END IF;
110
111 write_log('Agent Cost : ' || g_agent_cost);
112
113 write_log('Getting schema information');
114 IF(FND_INSTALLATION.GET_APP_INFO('BIX', l_status, l_industry, g_bix_schema)) THEN
115 NULL;
116 END IF;
117 write_log('BIX Schema : ' || g_bix_schema);
118
119 write_log('Finished procedure init at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
120 EXCEPTION
121 WHEN OTHERS THEN
122 write_log('Error in init : Error : ' || sqlerrm);
123 RAISE;
124 END init;
125
126 FUNCTION launch_worker(p_worker_no in NUMBER) RETURN NUMBER IS
127
128 l_request_id NUMBER;
129 BEGIN
130
131 write_log('Start of the procedure launch_worker at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
132
133 /* Submit the parallel concurrent request */
134 l_request_id := FND_REQUEST.SUBMIT_REQUEST('BIX',
135 'BIX_SESSION_SUBWORKER',
136 NULL,
137 NULL,
138 FALSE,
139 p_worker_no);
140
141 write_log('Request ID of the concurrent request launched : ' || to_char(l_request_id));
142
143 /* if the submission of the request fails , abort the program */
144 IF (l_request_id = 0) THEN
145 rollback;
146 write_log('Error in launching child workers');
147 RAISE G_CHILD_PROCESS_ISSUE;
148 END IF;
149
150 write_log('Finished procedure launch_worker at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
151 RETURN l_request_id;
152
153 EXCEPTION
154 WHEN OTHERS THEN
155 write_log('Error in launch_worker : Error : ' || sqlerrm);
156 RAISE;
157 END LAUNCH_WORKER;
158
159 PROCEDURE register_jobs IS
160
161 l_start_date_range DATE;
162 l_end_date_range DATE;
163 l_count NUMBER := 0;
164
165 BEGIN
166 write_log('Start of the procedure register_jobs at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
167
168 /* No of jobs to be submitted = No of days for which we need to collect data */
169 SELECT ceil(g_collect_end_date - g_collect_start_date)
170 INTO l_count
171 FROM dual;
172
173 g_no_of_jobs := l_count;
174
175 write_log('Number of workers that need to ne instantiated : ' || to_char(l_count));
176
177 Delete BIX_WORKER_JOBS WHERE OBJECT_NAME = 'BIX_AGENT_SESSION_F';
178
179 IF (l_count > 0) THEN
180 l_start_date_range := g_collect_start_date;
181
182 /* Register a job for each day of the collection date range */
183 FOR i IN 1..l_count
184 LOOP
185 /* End date range is end of day of l_start_date_range */
186 l_end_date_range := trunc(l_start_date_range) + 1;
187
188 IF (l_start_date_range > g_collect_end_date) THEN
189 EXIT;
190 END IF;
191
192 IF (l_end_date_range > g_collect_end_date) THEN
193 l_end_date_range := g_collect_end_date;
194 END IF;
195
196 INSERT INTO BIX_WORKER_JOBS(OBJECT_NAME
197 , START_DATE_RANGE
198 , END_DATE_RANGE
199 , WORKER_NUMBER
200 , STATUS)
201 VALUES (
202 'BIX_AGENT_SESSION_F'
203 , l_start_date_range
204 , l_end_date_range
205 , l_count
206 , 'UNASSIGNED');
207
208 l_start_date_range := l_end_date_range;
209 END LOOP;
210 END IF;
211
212 COMMIT;
213
214 write_log('Finished procedure register_jobs at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
215 EXCEPTION
216 WHEN OTHERS THEN
217 write_log('Error in register_jobs : Error : ' || sqlerrm);
218 RAISE;
219 END REGISTER_JOBS;
220
221 PROCEDURE clean_up IS
222
223 l_total_rows_deleted NUMBER := 0;
224 l_rows_deleted NUMBER := 0;
225
226 BEGIN
227 write_log('Start of the procedure clean_up at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
228
229 rollback;
230
231 write_log('Deleting data from bix_agent_session_f');
232
233 /* Delete all the rows inserted from subworkers */
234 IF (g_worker.COUNT > 0) THEN
235 FOR i IN g_worker.FIRST .. g_worker.LAST
236 LOOP
237 LOOP
238 DELETE bix_agent_session_f
239 WHERE request_id = g_worker(i)
240 AND rownum <= g_commit_chunk_size ;
241
242 l_rows_deleted := SQL%ROWCOUNT;
243 l_total_rows_deleted := l_total_rows_deleted + l_rows_deleted;
244
245 COMMIT;
246
247 IF (l_rows_deleted < g_commit_chunk_size) THEN
248 EXIT;
249 END IF;
250 END LOOP;
251 END LOOP;
252 END IF;
253
254 /* Deleting all rows inserted by this main program */
255 LOOP
256
257 DELETE bix_agent_session_f
258 WHERE request_id = g_request_id
259 AND rownum <= g_commit_chunk_size ;
260
261 l_rows_deleted := SQL%ROWCOUNT;
262 l_total_rows_deleted := l_total_rows_deleted + l_rows_deleted;
263
264 COMMIT;
265
266 IF (l_rows_deleted < g_commit_chunk_size) THEN
267 EXIT;
268 END IF;
269 END LOOP;
270
271 write_log('Number of rows deleted from bix_agent_session_f : ' || to_char(l_total_rows_deleted));
272
273 write_log('Truncating the table bix_agent_session_stg');
274 Truncate_Table('BIX_AGENT_SESSION_STG');
275 write_log('Done truncating the table bix_agent_session_stg');
276
277 write_log('Finished procedure clean_up at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 write_log('Error in cleaning up the tables : Error : ' || sqlerrm);
282 RAISE;
283 END CLEAN_UP;
284
285 PROCEDURE insert_login_row(p_session_id in g_session_id_tab,
286 p_agent_id in g_resource_id_tab,
287 p_session_begin_date in g_begin_date_time_tab,
288 p_session_end_date in g_end_date_time_tab,
289 p_last_collect_date in g_last_collect_date_tab,
290 p_server_group_id in g_server_group_id_tab,
291 p_application_id in g_application_id_tab)
292 IS
293 TYPE login_time_tab is TABLE OF bix_agent_session_f.login_time%TYPE;
294 TYPE session_id_tab is TABLE OF ieu_sh_sessions.session_id%TYPE;
295 TYPE collect_date_tab is TABLE OF ieu_sh_sessions.end_date_time%TYPE;
296
297 l_agent_id g_resource_id_tab;
298 l_period_start_date g_begin_date_time_tab;
299 l_login_time login_time_tab;
300 l_server_group_id g_server_group_id_tab;
301 l_application_id g_application_id_tab;
302
303 l_session_id session_id_tab;
304 l_collect_date collect_date_tab;
305
306 l_begin_date DATE;
307 l_end_date DATE;
308 l_period_start DATE;
309 l_row_counter NUMBER;
310 l_login_start DATE;
311 l_login_end DATE;
312 l_secs NUMBER;
313 j NUMBER;
314 k NUMBER;
315 BEGIN
316 write_log('Start of the procedure insert_login_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
317
318 /* Initialize all the variables */
319 j := 0;
320 k := 0;
321 l_agent_id := g_resource_id_tab();
322 l_period_start_date := g_begin_date_time_tab();
323 l_server_group_id := g_server_group_id_tab();
324 l_application_id := g_application_id_tab();
325 l_login_time := login_time_tab();
326 l_session_id := session_id_tab();
327 l_collect_date := collect_date_tab();
328
329 /* Loop through all the session rows returned by the cursor */
330 FOR i in p_session_id.FIRST .. p_session_id.LAST LOOP
331 /* Collect from either session begin date or the date till which the session info has alreday been collected */
332 l_begin_date := greatest(p_session_begin_date(i), nvl(p_last_collect_date(i), p_session_begin_date(i)));
333 l_end_date := p_session_end_date(i);
334
335 IF (l_begin_date < l_end_date) THEN
336 k := k + 1;
337 l_session_id.extend(1);
338 l_collect_date.extend(1);
339
340 l_session_id(k) := p_session_id(i);
341 l_collect_date(k) := l_end_date;
342
343 /* Get the half hour bucket of the session begin date time */
344 SELECT trunc(l_begin_date)
345 INTO l_period_start
346 FROM DUAL;
347
348 l_row_counter := 0; /* Variable to identify the first row of the session in the while loop */
349
350 /* Loop through the session record and insert a record for each half hour bucket */
351 WHILE ( l_period_start < l_end_date )
352 LOOP
353 j := j + 1;
354 IF (l_row_counter = 0 )
355 THEN
356 l_login_start := l_begin_date;
357 ELSE
358 l_login_start := l_period_start;
359 END IF;
360
361 l_login_end := l_period_start + 1;
362 IF ( l_login_end > l_end_date )
363 THEN
364 l_login_end := l_end_date ;
365 END IF;
366
367 l_secs := round((l_login_end - l_login_start) * 24 * 3600);
368
369 l_agent_id.extend(1);
370 l_period_start_date.extend(1);
371 l_login_time.extend(1);
372 l_server_group_id.extend(1);
373 l_application_id.extend(1);
374
375 l_agent_id(j) := p_agent_id(i);
376 l_period_start_date(j) := l_period_start;
377 l_login_time(j) := l_secs;
378 l_server_group_id(j) := p_server_group_id(i);
379 l_application_id(j) := p_application_id(i);
380
381 l_row_counter := l_row_counter + 1;
382 l_period_start := l_period_start + 1;
383
384 END LOOP; -- end of WHILE loop
385 END IF; /* end if (l_begin_date > l_end_date) */
386 END LOOP;
387
388 /* Bulk insert all the rows in the staging area */
389 IF (l_agent_id.COUNT > 0) THEN
390 FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
391 INSERT /*+ append */ INTO bix_agent_session_stg (
392 agent_id
393 ,server_group_id
394 ,schedule_id
395 ,campaign_id
396 ,application_id
397 ,time_id
398 ,period_type_id
399 ,period_start_date
400 ,period_start_time
401 ,day_of_week
402 ,last_update_date
403 ,last_updated_by
404 ,creation_date
405 ,created_by
406 ,last_update_login
407 ,login_time
408 ,request_id
412 VALUES (
409 ,program_application_id
410 ,program_id
411 ,program_update_date)
413 l_agent_id(i)
414 ,l_server_group_id(i)
415 ,-1
416 ,-1
417 ,l_application_id(i)
418 ,to_number(to_char(l_period_start_date(i), 'J'))
419 ,1
420 ,TRUNC(l_period_start_date(i))
421 ,'00:00'
422 ,TO_NUMBER(TO_CHAR(l_period_start_date(i),'D'))
423 ,g_sysdate
424 ,g_user_id
425 ,g_sysdate
426 ,g_user_id
427 ,g_user_id
428 ,decode(l_login_time(i), 0, to_number(null), l_login_time(i))
429 ,g_request_id
430 ,g_program_appl_id
431 ,g_program_id
432 ,g_sysdate);
433 END IF;
434
435 write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
436
437 IF (l_session_id.COUNT > 0) THEN
438 FORALL i IN l_session_id.FIRST .. l_session_id.LAST
439 MERGE INTO bix_sessions bis1
440 USING (
441 SELECT
442 l_session_id(i) session_id,
443 l_collect_date(i) curr_collect_date
444 FROM dual ) change
445 ON ( bis1.session_id = change.session_id )
446 WHEN MATCHED THEN
447 UPDATE SET
448 bis1.curr_collect_date = change.curr_collect_date
449 ,bis1.last_update_date = g_sysdate
450 ,bis1.last_updated_by = g_user_id
451 ,bis1.program_update_date = g_sysdate
452 WHEN NOT MATCHED THEN INSERT (
453 bis1.session_id,
454 bis1.created_by,
455 bis1.creation_date,
456 bis1.last_updated_by,
457 bis1.last_update_date,
458 bis1.curr_collect_date,
459 bis1.request_id,
460 bis1.program_application_id,
461 bis1.program_id,
462 bis1.program_update_date )
463 VALUES (
464 change.session_id,
465 g_user_id,
466 g_sysdate,
467 g_user_id,
468 g_sysdate,
469 change.curr_collect_date,
470 g_request_id,
471 g_program_appl_id,
472 g_program_id,
473 g_sysdate);
474 END IF;
475 write_log('Finished procedure insert_login_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
476 EXCEPTION
477 WHEN OTHERS THEN
478 write_log('Error in insert_login_row : Error : ' || sqlerrm);
479 RAISE;
480 END insert_login_row;
481
482 PROCEDURE collect_login_time IS
483 CURSOR get_login_time IS
484 SELECT
485 iss.session_id session_id
486 ,iss.resource_id resource_id
487 ,iss.begin_date_time begin_date_time
488 ,iss.end_date_time end_date_time
489 ,bis1.last_collect_date last_collect_date
490 ,nvl(res.server_group_id,-1) server_group_id
491 ,decode(iss.application_id, 696, 696, 680, 680, 0) application_id
492 FROM
493 ieu_sh_sessions iss
494 ,bix_sessions bis1
495 ,jtf_rs_resource_extns res
496 WHERE iss.last_update_date > g_collect_start_date
497 AND iss.last_update_date <= g_collect_end_date
498 AND iss.session_id = bis1.session_id(+)
499 AND iss.resource_id = res.resource_id
500 AND iss.end_date_time IS NOT NULL
501 UNION ALL
502 SELECT
503 inv1.session_id session_id
504 ,inv1.resource_id resource_id
505 ,inv1.begin_date_time begin_date_time
506 ,decode(max(mseg.start_date_time), to_date(null), inv1.begin_date_time, max(mseg.start_date_time))
507 end_date_time
508 ,bis1.last_collect_date last_collect_date
509 ,nvl(res.server_group_id,-1) server_group_id
510 ,decode(inv1.application_id, 696, 696, 680, 680, 0)
511 application_id
512 FROM
513 ( SELECT msegs.* FROM jtf_ih_media_item_lc_segs msegs
514 ,jtf_ih_media_itm_lc_seg_tys segs
515 WHERE msegs.milcs_type_id = segs.milcs_type_id
516 AND segs.milcs_code IN
517 ('EMAIL_FETCH'
518 ,'EMAIL_REPLY'
519 ,'EMAIL_DELETED'
520 ,'EMAIL_OPEN'
521 ,'EMAIL_REQUEUED'
522 ,'EMAIL_REROUTED_DIFF_CLASS'
523 ,'EMAIL_REROUTED_DIFF_ACCT'
524 ,'EMAIL_SENT'
525 ,'EMAIL_TRANSFERRED'
526 ,'EMAIL_ASSIGN'
527 ,'EMAIL_COMPOSE'
528 ,'WITH_AGENT'
529 ,'EMAIL_ESCALATED'
530 )
531 ) mseg
532 ,bix_sessions bis1
533 ,jtf_rs_resource_extns res
534 ,(
535 SELECT
536 iss1.session_id session_id
537 , iss1.resource_id resource_id
538 , iss1.application_id application_id
539 , iss1.begin_date_time begin_date_time
540 , iss1.end_date_time end_date_time
541 , min(iss2.begin_date_time) next_sess_begin_date_time
542 FROM
543 ieu_sh_sessions iss1
544 ,ieu_sh_sessions iss2
545 WHERE iss1.active_flag = 'T'
546 AND iss1.resource_id = iss2.resource_id(+)
547 AND iss2.begin_date_time(+) > iss1.begin_date_time
551 AND mseg.resource_id(+) = inv1.resource_id
548 GROUP BY iss1.session_id, iss1.resource_id, iss1.application_id, iss1.begin_date_time, iss1.end_date_time
549 ) inv1
550 WHERE inv1.resource_id = res.resource_id
552 AND mseg.start_date_time(+) >= inv1.begin_date_time
553 AND mseg.start_date_time(+) < nvl(inv1.next_sess_begin_date_time, g_sysdate)
554 AND inv1.session_id = bis1.session_id(+)
555 GROUP BY inv1.session_id, inv1.resource_id, inv1.begin_date_time, bis1.last_collect_date, res.server_group_id, inv1.application_id;
556
557 l_session_id g_session_id_tab;
558 l_resource_id g_resource_id_tab;
559 l_begin_date_time g_begin_date_time_tab;
560 l_end_date_time g_end_date_time_tab;
561 l_last_collect_date g_last_collect_date_tab;
562 l_server_group_id g_server_group_id_tab;
563 l_application_id g_application_id_tab;
564
565 l_no_of_records NUMBER;
566 BEGIN
567
568 write_log('Start of the procedure collect_login_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
569
570 OPEN get_login_time;
571
572 LOOP
573
574 /* Fetch the login rows in bulk and process them row by row */
575 FETCH get_login_time BULK COLLECT INTO
576 l_session_id,
577 l_resource_id,
578 l_begin_date_time,
579 l_end_date_time,
580 l_last_collect_date,
581 l_server_group_id,
582 l_application_id
583 LIMIT g_commit_chunk_size;
584
585 l_no_of_records := l_session_id.COUNT;
586
587 IF (l_no_of_records > 0) THEN
588 insert_login_row(
589 l_session_id,
590 l_resource_id,
591 l_begin_date_time,
592 l_end_date_time,
593 l_last_collect_date,
594 l_server_group_id,
595 l_application_id);
596
597 l_session_id.TRIM(l_no_of_records);
598 l_resource_id.TRIM(l_no_of_records);
599 l_begin_date_time.TRIM(l_no_of_records);
600 l_end_date_time.TRIM(l_no_of_records);
601 l_last_collect_date.TRIM(l_no_of_records);
602 l_server_group_id.TRIM(l_no_of_records);
603 l_application_id.TRIM(l_no_of_records);
604 END IF;
605
606 EXIT WHEN get_login_time%NOTFOUND;
607
608 END LOOP;
609
610 CLOSE get_login_time;
611
612 COMMIT;
613
614 write_log('Finished procedure collect_login_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
615 EXCEPTION
616 WHEN OTHERS THEN
617 write_log('Error in procedure collect_login_time : Error : ' || sqlerrm);
618 IF (get_login_time%ISOPEN) THEN
619 CLOSE get_login_time;
620 END IF;
621 RAISE;
622 END collect_login_time;
623
624 PROCEDURE insert_work_row(p_activity_id in g_activity_id_tab,
625 p_agent_id in g_resource_id_tab,
626 p_activity_begin_date in g_begin_date_time_tab,
627 p_activity_end_date in g_end_date_time_tab,
628 p_last_collect_date in g_last_collect_date_tab,
629 p_server_group_id in g_server_group_id_tab,
630 p_application_id in g_application_id_tab,
631 p_schedule_id in g_schedule_id_tab,
632 p_campaign_id in g_campaign_id_tab)
633 IS
634 TYPE work_time_tab is TABLE OF bix_agent_session_f.work_time%TYPE;
635
636 l_agent_id g_resource_id_tab;
637 l_period_start_date g_begin_date_time_tab;
638 l_work_time work_time_tab;
639 l_server_group_id g_server_group_id_tab;
640 l_application_id g_application_id_tab;
641 l_schedule_id g_schedule_id_tab;
642 l_campaign_id g_campaign_id_tab;
643
644 l_begin_date DATE;
645 l_end_date DATE;
646 l_period_start DATE;
647 l_row_counter NUMBER;
648 l_work_start DATE;
649 l_work_end DATE;
650 l_secs NUMBER;
651 j NUMBER;
652 BEGIN
653 write_log('Start of the procedure insert_work_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
654
655 /* Initialize all the variables */
656 j := 0;
657 l_agent_id := g_resource_id_tab();
658 l_period_start_date := g_begin_date_time_tab();
659 l_server_group_id := g_server_group_id_tab();
660 l_application_id := g_application_id_tab();
661 l_work_time := work_time_tab();
662 l_schedule_id := g_schedule_id_tab();
663 l_campaign_id := g_campaign_id_tab();
664
665 /* Loop through all the activities rows returned by the cursor */
666 FOR i in p_activity_id.FIRST .. p_activity_id.LAST LOOP
667 /* Collect from either activity begin date or the date till which the activity info has alreday been collected */
668 l_begin_date := greatest(p_activity_begin_date(i), nvl(p_last_collect_date(i), p_activity_begin_date(i)));
669
670 l_end_date := p_activity_end_date(i);
671
672 IF (l_begin_date < l_end_date) THEN
673 /* Get the half hour bucket of the session begin date time */
674 SELECT trunc(l_begin_date)
675 INTO l_period_start
676 FROM DUAL;
677
678 l_row_counter := 0; /* Variable to identify the first row of the session in the while loop */
682 LOOP
679
680 /* Loop through the session record and insert a record for each half hour bucket */
681 WHILE ( l_period_start < l_end_date )
683 j := j + 1;
684 IF (l_row_counter = 0 )
685 THEN
686 l_work_start := l_begin_date;
687 ELSE
688 l_work_start := l_period_start;
689 END IF;
690
691 l_work_end := l_period_start + 1;
692 IF ( l_work_end > l_end_date )
693 THEN
694 l_work_end := l_end_date ;
695 END IF;
696
697 l_secs := round((l_work_end - l_work_start) * 24 * 3600);
698
699 l_agent_id.extend(1);
700 l_period_start_date.extend(1);
701 l_server_group_id.extend(1);
702 l_application_id.extend(1);
703 l_schedule_id.extend(1);
704 l_campaign_id.extend(1);
705 l_work_time.extend(1);
706
707 l_agent_id(j) := p_agent_id(i);
708 l_period_start_date(j) := l_period_start;
709 l_server_group_id(j) := p_server_group_id(i);
710 l_application_id(j) := p_application_id(i);
711 l_schedule_id(j) := p_schedule_id(i);
712 l_campaign_id(j) := p_campaign_id(i);
713 l_work_time(j) := l_secs;
714
715 l_row_counter := l_row_counter + 1;
716 l_period_start := l_period_start + 1;
717
718 END LOOP; -- end of WHILE loop
719 END IF; /* end if (l_begin_date > l_end_date) */
720 END LOOP;
721
722 /* Bulk insert all the rows in the staging area */
723 IF (l_agent_id.COUNT > 0) THEN
724 FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
725 INSERT /*+ append */ INTO bix_agent_session_stg (
726 agent_id
727 ,server_group_id
728 ,schedule_id
729 ,campaign_id
730 ,application_id
731 ,time_id
732 ,period_type_id
733 ,period_start_date
734 ,period_start_time
735 ,day_of_week
736 ,last_update_date
737 ,last_updated_by
738 ,creation_date
739 ,created_by
740 ,last_update_login
741 ,work_time
742 ,request_id
743 ,program_application_id
744 ,program_id
745 ,program_update_date)
746 VALUES (
747 l_agent_id(i)
748 ,l_server_group_id(i)
749 ,l_schedule_id(i)
750 ,l_campaign_id(i)
751 ,l_application_id(i)
752 ,to_number(to_char(l_period_start_date(i), 'J'))
753 ,1
754 ,TRUNC(l_period_start_date(i))
755 ,'00:00'
756 ,TO_CHAR(l_period_start_date(i),'D')
757 ,g_sysdate
758 ,g_user_id
759 ,g_sysdate
760 ,g_user_id
761 ,g_user_id
762 ,decode(l_work_time(i), 0, to_number(null), l_work_time(i))
763 ,g_request_id
764 ,g_program_appl_id
765 ,g_program_id
766 ,g_sysdate);
767 END IF;
768
769 write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
770
771 write_log('Finished procedure insert_work_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
772 EXCEPTION
773 WHEN OTHERS THEN
774 write_log('Error in insert_work_row : Error : ' || sqlerrm);
775 RAISE;
776 END insert_work_row;
777
778 PROCEDURE collect_work_time IS
779 CURSOR get_work_time IS
780 SELECT
781 isa.activity_id activity_id
782 ,iss.resource_id resource_id
783 ,isa.begin_date_time begin_date_time
784 /* ,nvl(isa.end_date_time, bis1.curr_collect_date) end_date_time*/
785 ,nvl(isa.end_date_time, nvl(isamed.end_Date_time,isa.begin_Date_time)) end_date_time
786 ,bis1.last_collect_date last_collect_date
787 ,nvl(res.server_group_id,-1) server_group_id
788 ,iss.application_id application_id
789 ,decode(isa.category_type, 'CSCH', nvl(csh.schedule_id, -1), -1)
790 schedule_id
791 ,decode(isa.category_type, 'CSCH', nvl(csh.campaign_id, -1), -1)
792 campaign_id
793 FROM
794 ieu_sh_sessions iss
795 ,ieu_sh_activities isa
796 ,bix_sessions bis1
797 ,jtf_rs_resource_extns res
798 ,ams_campaign_schedules_b csh
799 ,(select parent_cycle_id,max(isamed.end_Date_time) end_date_time from ieu_sh_Activities isamed,ieu_sh_sessions isamedsess
800 where activity_type_code='MEDIA'
801 and isamedsess.last_update_date > g_collect_start_date-2 --dummy filter to force index scan
802 and isamed.last_update_date > g_collect_start_date
803 AND isamed.last_update_date <= g_collect_end_date
804 and isamedsess.session_id=isamed.session_id
805 and isamedsess.application_id=696
806 group by parent_cycle_id) isamed
807 WHERE isa.last_update_date > g_collect_start_date
808 AND iss.last_update_date > g_collect_start_date-2
809 AND isa.last_update_date <= g_collect_end_date
810 AND isa.activity_id=isamed.parent_cycle_id(+)
811 AND iss.application_id = 696
812 AND iss.session_id = isa.session_id
813 AND isa.activity_type_code = 'MEDIA_CYCLE'
817
814 AND iss.session_id = bis1.session_id
815 AND iss.resource_id = res.resource_id
816 AND decode(isa.category_type, 'CSCH', to_number(nvl(isa.category_value, -1)), -1) = csh.schedule_id(+);
818 l_activity_id g_activity_id_tab;
819 l_resource_id g_resource_id_tab;
820 l_begin_date_time g_begin_date_time_tab;
821 l_end_date_time g_end_date_time_tab;
822 l_last_collect_date g_last_collect_date_tab;
823 l_server_group_id g_server_group_id_tab;
824 l_application_id g_application_id_tab;
825 l_schedule_id g_schedule_id_tab;
826 l_campaign_id g_campaign_id_tab;
827
828 l_no_of_records NUMBER;
829 BEGIN
830
831 write_log('Start of the procedure collect_work_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
832
833 OPEN get_work_time;
834
835 LOOP
836 /* Fetch the activity rows in bulk and process them row by row */
837 FETCH get_work_time BULK COLLECT INTO
838 l_activity_id,
839 l_resource_id,
840 l_begin_date_time,
841 l_end_date_time,
842 l_last_collect_date,
843 l_server_group_id,
844 l_application_id,
845 l_schedule_id,
846 l_campaign_id
847 LIMIT g_commit_chunk_size;
848
849 l_no_of_records := l_activity_id.COUNT;
850
851 IF (l_no_of_records > 0) THEN
852 insert_work_row(
853 l_activity_id,
854 l_resource_id,
855 l_begin_date_time,
856 l_end_date_time,
857 l_last_collect_date,
858 l_server_group_id,
859 l_application_id,
860 l_schedule_id,
861 l_campaign_id);
862
863 l_activity_id.TRIM(l_no_of_records);
864 l_resource_id.TRIM(l_no_of_records);
865 l_begin_date_time.TRIM(l_no_of_records);
866 l_end_date_time.TRIM(l_no_of_records);
867 l_last_collect_date.TRIM(l_no_of_records);
868 l_server_group_id.TRIM(l_no_of_records);
869 l_application_id.TRIM(l_no_of_records);
870 l_schedule_id.TRIM(l_no_of_records);
871 l_campaign_id.TRIM(l_no_of_records);
872 END IF;
873
874 EXIT WHEN get_work_time%NOTFOUND;
875
876 END LOOP;
877
878 CLOSE get_work_time;
879
880 COMMIT;
881
882 write_log('Finished procedure collect_work_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
883 EXCEPTION
884 WHEN OTHERS THEN
885 write_log('Error in procedure collect_work_time : Error : ' || sqlerrm);
886 IF (get_work_time%ISOPEN) THEN
887 CLOSE get_work_time;
888 END IF;
889 RAISE;
890 END collect_work_time;
891
892 PROCEDURE insert_available_row(p_activity_id in g_activity_id_tab,
893 p_agent_id in g_resource_id_tab,
894 p_activity_begin_date in g_begin_date_time_tab,
895 p_activity_end_date in g_end_date_time_tab,
896 p_last_collect_date in g_last_collect_date_tab,
897 p_server_group_id in g_server_group_id_tab,
898 p_application_id in g_application_id_tab,
899 p_schedule_id in g_schedule_id_tab,
900 p_campaign_id in g_campaign_id_tab)
901 IS
902 TYPE available_time_tab is TABLE OF bix_agent_session_f.available_time%TYPE;
903
904 l_agent_id g_resource_id_tab;
905 l_period_start_date g_begin_date_time_tab;
906 l_available_time available_time_tab;
907 l_server_group_id g_server_group_id_tab;
908 l_application_id g_application_id_tab;
909 l_schedule_id g_schedule_id_tab;
910 l_campaign_id g_campaign_id_tab;
911
912 l_begin_date DATE;
913 l_end_date DATE;
914 l_period_start DATE;
915 l_row_counter NUMBER;
916 l_work_start DATE;
917 l_work_end DATE;
918 l_secs NUMBER;
919 j NUMBER;
920 BEGIN
921 write_log('Start of the procedure insert_available_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
922
923 /* Initialize all the variables */
924 j := 0;
925 l_agent_id := g_resource_id_tab();
926 l_period_start_date := g_begin_date_time_tab();
927 l_server_group_id := g_server_group_id_tab();
928 l_application_id := g_application_id_tab();
929 l_available_time := available_time_tab();
933 /* Loop through all the activities rows returned by the cursor */
930 l_schedule_id := g_schedule_id_tab();
931 l_campaign_id := g_campaign_id_tab();
932
934 FOR i in p_activity_id.FIRST .. p_activity_id.LAST LOOP
935 /* Collect from either activity begin date or the date till which the activity info has alreday been collected */
936 l_begin_date := greatest(p_activity_begin_date(i), nvl(p_last_collect_date(i), p_activity_begin_date(i)));
937
938 l_end_date := p_activity_end_date(i);
939
940 IF (l_begin_date < l_end_date) THEN
941 /* Get the half hour bucket of the session begin date time */
942 SELECT trunc(l_begin_date)
943 INTO l_period_start
944 FROM DUAL;
945
946 l_row_counter := 0; /* Variable to identify the first row of the session in the while loop */
947
948 /* Loop through the session record and insert a record for each half hour bucket */
949 WHILE ( l_period_start < l_end_date )
950 LOOP
951 j := j + 1;
952 IF (l_row_counter = 0 )
953 THEN
954 l_work_start := l_begin_date;
955 ELSE
956 l_work_start := l_period_start;
957 END IF;
958
959 l_work_end := l_period_start + 1;
960 IF ( l_work_end > l_end_date )
961 THEN
962 l_work_end := l_end_date ;
963 END IF;
964
965 l_secs := round((l_work_end - l_work_start) * 24 * 3600);
966
967 l_agent_id.extend(1);
968 l_period_start_date.extend(1);
969 l_server_group_id.extend(1);
970 l_application_id.extend(1);
971 l_schedule_id.extend(1);
972 l_campaign_id.extend(1);
973 l_available_time.extend(1);
974
975 l_agent_id(j) := p_agent_id(i);
976 l_period_start_date(j) := l_period_start;
977 l_server_group_id(j) := p_server_group_id(i);
978 l_application_id(j) := p_application_id(i);
979 l_schedule_id(j) := p_schedule_id(i);
980 l_campaign_id(j) := p_campaign_id(i);
981 l_available_time(j) := l_secs;
982
983 l_row_counter := l_row_counter + 1;
984 l_period_start := l_period_start + 1;
985
986 END LOOP; -- end of WHILE loop
987 END IF; /* end if (l_begin_date > l_end_date) */
988 END LOOP;
989
990 /* Bulk insert all the rows in the staging area */
991 IF (l_agent_id.COUNT > 0) THEN
992 FORALL i IN l_agent_id.FIRST .. l_agent_id.LAST
993 INSERT /*+ append */ INTO bix_agent_session_stg (
994 agent_id
995 ,server_group_id
996 ,schedule_id
997 ,campaign_id
998 ,application_id
999 ,time_id
1000 ,period_type_id
1001 ,period_start_date
1002 ,period_start_time
1003 ,day_of_week
1004 ,last_update_date
1005 ,last_updated_by
1006 ,creation_date
1007 ,created_by
1008 ,last_update_login
1009 ,available_time
1010 ,request_id
1011 ,program_application_id
1012 ,program_id
1013 ,program_update_date)
1014 VALUES (
1015 l_agent_id(i)
1016 ,l_server_group_id(i)
1017 ,l_schedule_id(i)
1018 ,l_campaign_id(i)
1019 ,l_application_id(i)
1020 ,to_number(to_char(l_period_start_date(i), 'J'))
1021 ,1
1022 ,TRUNC(l_period_start_date(i))
1023 ,'00:00'
1024 ,TO_CHAR(l_period_start_date(i),'D')
1025 ,g_sysdate
1026 ,g_user_id
1027 ,g_sysdate
1028 ,g_user_id
1029 ,g_user_id
1030 ,decode(l_available_time(i), 0, to_number(null), l_available_time(i))
1031 ,g_request_id
1032 ,g_program_appl_id
1033 ,g_program_id
1034 ,g_sysdate);
1035 END IF;
1036
1037 write_log('Total rows inserted in the staging area for half hour time bucket : ' || to_char(l_agent_id.COUNT));
1038
1039 write_log('Finished procedure insert_available_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 write_log('Error in insert_available_row : Error : ' || sqlerrm);
1043 RAISE;
1044 END insert_available_row;
1045
1046 PROCEDURE collect_available_time IS
1047 CURSOR get_available_time IS
1048 SELECT
1049 /*+ parallel(iss) parallel(isa1) parallel(isa2) parallel(bis1) parallel(res) parallel(csh)
1050 pq_distribute(iss hash,hash) pq_distribute(isa1 hash,hash)
1051 pq_distribute(isa2 hash,hash) pq_distribute(bis1 hash,hash)
1052 pq_distribute(res hash,hash) pq_distribute(csh hash,hash)
1053 use_hash(iss,isa1,isa2,bis1,res,csh) */
1054 isa1.activity_id activity_id
1055 ,iss.resource_id resource_id
1056 ,isa1.begin_date_time begin_date_time
1057 /* ,nvl(isa1.deliver_date_time, nvl(isa1.end_date_time, bis1.curr_collect_date)) */
1061 ,nvl(res.server_group_id,-1) server_group_id
1058 ,nvl(isa1.deliver_date_time, nvl(isa1.end_date_time, isa1.begin_date_time))
1059 end_date_time
1060 ,bis1.last_collect_date last_collect_date
1062 ,iss.application_id application_id
1063 ,decode(isa2.category_type, 'CSCH', nvl(csh.schedule_id, -1), -1)
1064 schedule_id
1065 ,decode(isa2.category_type, 'CSCH', nvl(csh.campaign_id, -1), -1)
1066 campaign_id
1067 FROM
1068 ieu_sh_sessions iss
1069 ,ieu_sh_activities isa1
1070 ,ieu_sh_activities isa2
1071 ,bix_sessions bis1
1072 ,jtf_rs_resource_extns res
1073 ,ams_campaign_schedules_b csh
1074 WHERE isa1.last_update_date > g_collect_start_date
1075 AND isa1.last_update_date <= g_collect_end_date
1076 AND iss.application_id = 696
1077 AND iss.session_id = isa1.session_id
1078 AND isa1.activity_type_code = 'MEDIA'
1079 AND isa1.parent_cycle_id = isa2.activity_id
1080 AND isa2.activity_type_code = 'MEDIA_CYCLE'
1081 AND iss.session_id = bis1.session_id
1082 AND iss.resource_id = res.resource_id
1083 AND decode(isa2.category_type, 'CSCH', to_number(nvl(isa2.category_value, -1)), -1) = csh.schedule_id(+);
1084
1085 l_activity_id g_activity_id_tab;
1086 l_resource_id g_resource_id_tab;
1087 l_begin_date_time g_begin_date_time_tab;
1088 l_end_date_time g_end_date_time_tab;
1089 l_last_collect_date g_last_collect_date_tab;
1090 l_server_group_id g_server_group_id_tab;
1091 l_application_id g_application_id_tab;
1092 l_schedule_id g_schedule_id_tab;
1093 l_campaign_id g_campaign_id_tab;
1094
1095 l_no_of_records NUMBER;
1096 BEGIN
1097
1098 write_log('Start of the procedure collect_available_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1099
1100 OPEN get_available_time;
1101
1102 LOOP
1103 /* Fetch the activity rows in bulk and process them row by row */
1104 FETCH get_available_time BULK COLLECT INTO
1105 l_activity_id,
1106 l_resource_id,
1107 l_begin_date_time,
1108 l_end_date_time,
1109 l_last_collect_date,
1110 l_server_group_id,
1111 l_application_id,
1112 l_schedule_id,
1113 l_campaign_id
1114 LIMIT g_commit_chunk_size;
1115
1116 l_no_of_records := l_activity_id.COUNT;
1117
1118 IF (l_no_of_records > 0) THEN
1119 insert_available_row(
1120 l_activity_id,
1121 l_resource_id,
1122 l_begin_date_time,
1123 l_end_date_time,
1124 l_last_collect_date,
1125 l_server_group_id,
1126 l_application_id,
1127 l_schedule_id,
1128 l_campaign_id);
1129
1130 l_activity_id.TRIM(l_no_of_records);
1131 l_resource_id.TRIM(l_no_of_records);
1132 l_begin_date_time.TRIM(l_no_of_records);
1133 l_end_date_time.TRIM(l_no_of_records);
1134 l_last_collect_date.TRIM(l_no_of_records);
1135 l_server_group_id.TRIM(l_no_of_records);
1136 l_application_id.TRIM(l_no_of_records);
1137 l_schedule_id.TRIM(l_no_of_records);
1138 l_campaign_id.TRIM(l_no_of_records);
1139 END IF;
1140
1141 EXIT WHEN get_available_time%NOTFOUND;
1142
1143 END LOOP;
1144
1145 CLOSE get_available_time;
1146
1147 COMMIT;
1148
1149 write_log('Finished procedure collect_available_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1150 EXCEPTION
1151 WHEN OTHERS THEN
1152 write_log('Error in procedure collect_available_time : Error : ' || sqlerrm);
1153 IF (get_available_time%ISOPEN) THEN
1154 CLOSE get_available_time;
1155 END IF;
1156 RAISE;
1157 END collect_available_time;
1158
1159 PROCEDURE collect_idle_time IS
1160 BEGIN
1161
1162 write_log('Start of the procedure collect_idle_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1163
1164 INSERT /*+ append */ INTO bix_agent_session_stg
1165 (agent_id,
1166 server_group_id,
1167 schedule_id,
1168 campaign_id,
1169 application_id,
1170 time_id,
1171 period_type_id,
1172 period_start_date,
1173 period_start_time,
1174 day_of_week,
1175 created_by,
1176 creation_date,
1177 last_updated_by,
1178 last_update_date,
1179 idle_time,
1180 request_id,
1181 program_application_id,
1182 program_id,
1183 program_update_date )
1184 (SELECT
1185 bas.agent_id,
1186 bas.server_group_id,
1187 -1,
1188 -1,
1189 bas.application_id,
1190 bas.time_id,
1191 bas.period_type_id,
1192 bas.period_start_date,
1193 bas.period_start_time,
1194 bas.day_of_week,
1195 g_user_id,
1196 g_sysdate,
1197 g_user_id,
1198 g_sysdate,
1199 decode(nvl(sum(bas.login_time),0) - nvl(sum(bas.work_time), 0), 0, to_number(null),
1200 nvl(sum(bas.login_time),0) - nvl(sum(bas.work_time), 0)),
1201 g_request_id,
1202 g_program_appl_id,
1203 g_program_id,
1207 GROUP BY
1204 g_sysdate
1205 FROM bix_agent_session_stg bas
1206 WHERE bas.application_id = 696
1208 bas.agent_id,
1209 bas.server_group_id,
1210 bas.application_id,
1211 bas.time_id,
1212 bas.period_type_id,
1213 bas.period_start_date,
1214 bas.period_start_time,
1215 bas.day_of_week);
1216
1217 COMMIT;
1218
1219 write_log('Finished procedure collect_idle_time at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1220 EXCEPTION
1221 WHEN OTHERS THEN
1222 write_log('Error in procedure collect_idle_time : Error : ' || sqlerrm);
1223 RAISE;
1224 END collect_idle_time;
1225
1226 PROCEDURE collect_day IS
1227 BEGIN
1228 write_log('Start of the procedure collect_day at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1229
1230 write_log('Calling procedure collect_login_time');
1231 collect_login_time;
1232 write_log('End procedure collect_login_time');
1233
1234 write_log('Calling procedure collect_work_time');
1235 collect_work_time;
1236 write_log('End procedure collect_work_time');
1237
1238 write_log('Calling procedure collect_available_time');
1239 collect_available_time;
1240 write_log('End procedure collect_available_time');
1241
1242 write_log('Calling procedure collect_idle_time');
1243 collect_idle_time;
1244 write_log('End procedure collect_idle_time');
1245
1246 write_log('Finished procedure collect_day at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 write_log('Error in procedure collect_day : Error : ' || sqlerrm);
1250 RAISE;
1251 END collect_day;
1252
1253 PROCEDURE merge_data IS
1254
1255 BEGIN
1256
1257 write_log('Start of the procedure merge_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1258
1259 /* Update bix_sessions to set the last collection date with the current collection date */
1260 /* First update the rows inserted/updated from the workers */
1261 IF (g_worker.COUNT > 0) THEN
1262 FOR i IN g_worker.FIRST .. g_worker.LAST
1263 LOOP
1264 UPDATE bix_sessions
1265 SET last_collect_date = curr_collect_date
1266 WHERE request_id = g_worker(i);
1267 END LOOP;
1268 END IF;
1269
1270 /* Update all rows inserted/updated by this main program */
1271 UPDATE bix_sessions
1272 SET last_collect_date = curr_collect_date
1273 WHERE request_id = g_request_id;
1274
1275 /* Move the data from the staging area to the summary table bix_agent_session_f */
1276 MERGE INTO bix_agent_session_f bas
1277 USING (
1278 SELECT
1279 bstg.agent_id agent_id,
1280 bstg.server_group_id server_group_id,
1281 bstg.schedule_id schedule_id,
1282 bstg.campaign_id campaign_id,
1283 bstg.application_id application_id,
1284 bstg.time_id time_id,
1285 bstg.period_type_id period_type_id,
1286 bstg.period_start_date period_start_date,
1287 bstg.period_start_time period_start_time,
1288 bstg.day_of_week day_of_week,
1289 sum(bstg.login_time) login_time,
1290 sum(bstg.work_time) work_time,
1291 sum(bstg.available_time) available_time,
1292 sum(bstg.idle_time) idle_time,
1293 nvl(sum(bstg.login_time), 0) * g_agent_cost agent_cost
1294 FROM bix_agent_session_stg bstg
1295 GROUP BY
1296 bstg.agent_id,
1297 bstg.server_group_id,
1298 bstg.schedule_id,
1299 bstg.campaign_id,
1300 bstg.application_id,
1301 bstg.time_id,
1302 bstg.period_type_id,
1303 bstg.period_start_date,
1304 bstg.period_start_time,
1305 bstg.day_of_week) change
1306 ON ( bas.agent_id = change.agent_id
1307 AND bas.server_group_id = change.server_group_id
1308 AND bas.schedule_id = change.schedule_id
1309 AND bas.campaign_id = change.campaign_id
1310 AND bas.application_id = change.application_id
1311 AND bas.time_id = change.time_id
1312 AND bas.period_type_id = change.period_type_id
1313 AND bas.period_start_date = change.period_start_date
1314 AND bas.period_start_time = change.period_start_time
1315 AND bas.day_of_week = change.day_of_week)
1316 WHEN MATCHED THEN
1317 UPDATE SET
1318 bas.login_time = decode(nvl(change.login_time,0), 0, bas.login_time, nvl(bas.login_time, 0) + change.login_time)
1319 ,bas.work_time = decode(nvl(change.work_time,0), 0, bas.work_time, nvl(bas.work_time, 0) + change.work_time)
1320 ,bas.available_time = decode(nvl(change.available_time,0), 0, bas.available_time, nvl(bas.available_time,0)
1321 + change.available_time)
1322 ,bas.idle_time = decode(nvl(change.idle_time,0), 0, bas.idle_time, nvl(bas.idle_time, 0) + change.idle_time)
1323 ,bas.agent_cost = decode(nvl(change.agent_cost,0), 0, bas.agent_cost, nvl(bas.agent_cost, 0) + change.agent_cost)
1324 ,bas.last_update_date = g_sysdate
1325 ,bas.last_updated_by = g_user_id
1326 ,bas.program_update_date = g_sysdate
1327 WHEN NOT MATCHED THEN INSERT
1328 (bas.agent_id,
1329 bas.server_group_id,
1330 bas.schedule_id,
1331 bas.campaign_id,
1332 bas.application_id,
1333 bas.time_id,
1334 bas.period_type_id,
1335 bas.period_start_date,
1339 bas.creation_date,
1336 bas.period_start_time,
1337 bas.day_of_week,
1338 bas.created_by,
1340 bas.last_updated_by,
1341 bas.last_update_date,
1342 bas.login_time,
1343 bas.work_time,
1344 bas.available_time,
1345 bas.idle_time,
1346 bas.agent_cost,
1347 bas.request_id,
1348 bas.program_application_id,
1349 bas.program_id,
1350 bas.program_update_date )
1351 VALUES (
1352 change.agent_id,
1353 change.server_group_id,
1354 change.schedule_id,
1355 change.campaign_id,
1356 change.application_id,
1357 change.time_id,
1358 change.period_type_id,
1359 change.period_start_date,
1360 change.period_start_time,
1361 change.day_of_week,
1362 g_user_id,
1363 g_sysdate,
1364 g_user_id,
1365 g_sysdate,
1366 decode(change.login_time, 0, to_number(null), change.login_time),
1367 decode(change.work_time, 0, to_number(null), change.work_time),
1368 decode(change.available_time, 0, to_number(null), change.available_time),
1369 decode(change.idle_time, 0, to_number(null), change.idle_time),
1370 decode(change.agent_cost, 0, to_number(null), change.agent_cost),
1371 g_request_id,
1372 g_program_appl_id,
1373 g_program_id,
1374 g_sysdate);
1375
1376 g_rows_ins_upd := g_rows_ins_upd + SQL%ROWCOUNT;
1377 write_log('Total rows merged in bix_agent_session_f : ' || to_char(g_rows_ins_upd));
1378
1379 COMMIT;
1380
1381 write_log('Finished procedure merge_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1382
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 write_log('Error in procedure merge_data : Error : ' || sqlerrm);
1386 RAISE;
1387 END merge_data;
1388
1389 PROCEDURE summarize_data IS
1390
1391 BEGIN
1392
1393 write_log('Start of the procedure summarize_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1394
1395 /* Rollup the half-hour information to day, week, month, quarter and year time bucket */
1396 INSERT /*+ append */ INTO bix_agent_session_stg
1397 (agent_id,
1398 server_group_id,
1399 schedule_id,
1400 campaign_id,
1401 application_id,
1402 time_id,
1403 period_type_id,
1404 period_start_date,
1405 period_start_time,
1406 day_of_week,
1407 created_by,
1408 creation_date,
1409 last_updated_by,
1410 last_update_date,
1411 login_time,
1412 work_time,
1413 available_time,
1414 idle_time,
1415 request_id,
1416 program_application_id,
1417 program_id,
1418 program_update_date )
1419 (SELECT
1420 bas.agent_id,
1421 bas.server_group_id,
1422 bas.schedule_id,
1423 bas.campaign_id,
1424 bas.application_id,
1425 decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
1426 decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null), ftd.ent_year_id),
1427 ftd.ent_qtr_id), ftd.ent_period_id), ftd.week_id),
1428 decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
1429 decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null), 128), 64), 32), 16),
1430 decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
1431 decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_date(null), min(ftd.ent_year_start_date)),
1432 min(ftd.ent_qtr_start_date)), min(ftd.ent_period_start_date)), min(ftd.week_start_date)),
1433 '00:00',
1434 bas.day_of_week,
1435 g_user_id,
1436 g_sysdate,
1437 g_user_id,
1438 g_sysdate,
1439 sum(bas.login_time),
1440 sum(bas.work_time),
1441 sum(bas.available_time),
1442 sum(bas.idle_time),
1443 g_request_id,
1444 g_program_appl_id,
1445 g_program_id,
1446 g_sysdate
1447 FROM bix_agent_session_stg bas,
1448 fii_time_day ftd
1449 WHERE bas.time_id = ftd.report_date_julian
1450 AND bas.period_type_id = 1
1451 GROUP BY
1452 bas.agent_id,
1453 bas.server_group_id,
1454 bas.schedule_id,
1455 bas.campaign_id,
1456 bas.application_id,
1457 bas.day_of_week,
1458 ROLLUP (
1459 ftd.ent_year_id,
1460 ftd.ent_qtr_id,
1461 ftd.ent_period_id,
1462 ftd.week_id)
1463 HAVING
1464 decode(ftd.week_id, null, decode(ftd.ent_period_id, null,
1465 decode(ftd.ent_qtr_id, null, decode(ftd.ent_year_id, null, to_number(null), 128), 64), 32), 16) IS NOT NULL);
1466
1467 write_log('Total rows inserted in the staging area for day, month and year : ' || to_char(SQL%ROWCOUNT));
1468
1469 COMMIT;
1470
1471 write_log('Finished procedure summarize_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1472
1473 EXCEPTION
1474 WHEN OTHERS THEN
1475 write_log('Error in procedure summarize_data : Error : ' || sqlerrm);
1476 RAISE;
1477 END summarize_data;
1478
1479 PROCEDURE worker(errbuf OUT NOCOPY VARCHAR2,
1480 retcode OUT NOCOPY VARCHAR2,
1484 l_failed_cnt NUMBER := 0;
1481 p_worker_no IN NUMBER) IS
1482
1483 l_unassigned_cnt NUMBER := 0;
1485 l_wip_cnt NUMBER := 0;
1486 l_completed_cnt NUMBER := 0;
1487 l_total_cnt NUMBER := 0;
1488 l_count NUMBER := 0;
1489 l_start_date_range DATE;
1490 l_end_date_range DATE;
1491
1492 BEGIN
1493
1494 write_log('Start of the procedure worker at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1495
1496 errbuf := NULL;
1497 retcode := 0;
1498
1499 write_log('Calling procedure init');
1500 init;
1501 write_log('End procedure init');
1502
1503 l_count:= 0;
1504
1505 LOOP
1506
1507 /* Get the status of all the jobs in BIX_WORKER_JOBS */
1508 SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
1509 NVL(sum(decode(status,'FAILED', 1, 0)),0),
1510 NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
1511 NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
1512 count(*)
1513 INTO l_unassigned_cnt,
1514 l_failed_cnt,
1515 l_wip_cnt,
1516 l_completed_cnt,
1517 l_total_cnt
1518 FROM BIX_WORKER_JOBS
1519 WHERE object_name = 'BIX_AGENT_SESSION_F';
1520
1521 write_log('Job status - Unassigned: '||l_unassigned_cnt||
1522 ' In Process: '||l_wip_cnt||
1523 ' Completed: '||l_completed_cnt||
1524 ' Failed: '||l_failed_cnt||
1525 ' Total: '|| l_total_cnt);
1526
1527 IF (l_failed_cnt > 0) THEN
1528 write_log('Another worker have errored out. Stop processing.');
1529 EXIT;
1530 ELSIF (l_unassigned_cnt = 0) THEN
1531 write_log('No more jobs left. Terminating.');
1532 EXIT;
1533 ELSIF (l_completed_cnt = l_total_cnt) THEN
1534 write_log('All jobs completed, no more job. Terminating');
1535 EXIT;
1536 ELSIF (l_unassigned_cnt > 0) THEN
1537 /* Pickup any one unassigned job to process */
1538 UPDATE BIX_WORKER_JOBS
1539 SET status = 'IN PROCESS',
1540 worker_number = p_worker_no
1541 WHERE status = 'UNASSIGNED'
1542 AND rownum < 2
1543 AND object_name = 'BIX_AGENT_SESSION_F';
1544
1545 l_count := sql%rowcount;
1546 COMMIT;
1547 END IF;
1548
1549 -- -----------------------------------
1550 -- There could be rare situations where
1551 -- between Section 30 and Section 50
1552 -- the unassigned job gets taken by
1553 -- another worker. So, if unassigned
1554 -- job no longer exist. Do nothing.
1555 -- -----------------------------------
1556
1557 IF (l_count > 0) THEN
1558
1559 DECLARE
1560 BEGIN
1561
1562 /* Collect data for half hour time buckets for the date range of the job */
1563 SELECT start_date_range, end_date_range
1564 INTO l_start_date_range, l_end_date_range
1565 FROM BIX_WORKER_JOBS
1566 WHERE worker_number = p_worker_no
1567 AND status = 'IN PROCESS'
1568 AND object_name = 'BIX_AGENT_SESSION_F';
1569
1570 write_log('Calling procedure collect_day');
1571 g_collect_start_date := l_start_date_range;
1572 g_collect_end_date := l_end_date_range;
1573 collect_day;
1574 write_log('End procedure collect_day');
1575
1576 /* Update the status of job to 'COMPLETED' */
1577 UPDATE BIX_WORKER_JOBS
1578 SET status = 'COMPLETED'
1579 WHERE status = 'IN PROCESS'
1580 AND worker_number = p_worker_no
1581 AND object_name = 'BIX_AGENT_SESSION_F';
1582
1583 COMMIT;
1584
1585 EXCEPTION
1586 WHEN OTHERS THEN
1587 retcode := -1;
1588
1589 UPDATE BIX_WORKER_JOBS
1590 SET status = 'FAILED'
1591 WHERE worker_number = p_worker_no
1592 AND status = 'IN PROCESS'
1593 AND object_name = 'BIX_AGENT_SESSION_F';
1594
1595 COMMIT;
1596 write_log('Error in worker');
1597 RAISE G_CHILD_PROCESS_ISSUE;
1598 END;
1599
1600 END IF; /* IF (l_count> 0) */
1601
1602 END LOOP;
1603
1604 write_log('Finished procedure worker at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1605 EXCEPTION
1606 WHEN OTHERS THEN
1607 write_log('Error in procedure worker : Error : ' || sqlerrm);
1608 RAISE;
1609 END WORKER;
1610
1611 ---Cleanup the media/sessions
1612
1613 PROCEDURE cleanup_oltp
1614 IS
1615
1616 BEGIN
1617
1618 --
1619 --Close media items
1620 --
1621 BEGIN
1622 g_errbuf := NULL;
1623 g_retcode := 'S';
1624 CCT_CONCURRENT_PUB.CLOSE_MEDIA_ITEMS(g_errbuf, g_retcode);
1625
1626 IF g_retcode <> 'S'
1627 THEN
1628 RAISE G_OLTP_CLEANUP_ISSUE;
1629 END IF;
1630
1631 EXCEPTION
1632 WHEN OTHERS THEN
1633 write_log('Close Media Items exited with: ' ||g_retcode || ' error buffer is: ' || g_errbuf);
1634 RAISE G_OLTP_CLEANUP_ISSUE;
1635 END;
1639 --
1636
1637 --
1638 --Time out media items - interval hardcoded to 24 hours for now
1640 BEGIN
1641 g_errbuf := NULL;
1642 g_retcode := 'S';
1643 CCT_CONCURRENT_PUB.TIMEOUT_MEDIA_ITEMS_RS(g_errbuf, g_retcode,24);
1644
1645 IF g_retcode <> 'S'
1646 THEN
1647 RAISE G_OLTP_CLEANUP_ISSUE;
1648 END IF;
1649
1650 EXCEPTION
1651 WHEN OTHERS THEN
1652 write_log('Timeout Media Items exited with: ' ||g_retcode || ' error buffer is: ' || g_errbuf);
1653 RAISE G_OLTP_CLEANUP_ISSUE;
1654 END;
1655
1656
1657 --IEU Session History Cleanup
1658
1659 BEGIN
1660 g_errbuf := NULL;
1661 g_retcode := 'S';
1662 IEU_SH_CON_PVT.IEU_SH_END_IDLE_TRANS(g_errbuf, g_retcode,NULL,'3',8);
1663
1664 IF g_retcode <> 'S'
1665 THEN
1666 RAISE G_OLTP_CLEANUP_ISSUE;
1667 END IF;
1668
1669 EXCEPTION
1670 WHEN OTHERS THEN
1671 write_log('Timeout Media Items exited with: ' ||g_retcode || ' error buffer is: ' || g_errbuf);
1672 RAISE G_OLTP_CLEANUP_ISSUE;
1673 END;
1674
1675 END cleanup_oltp;
1676
1677 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
1678 retcode OUT NOCOPY VARCHAR2,
1679 p_start_date IN VARCHAR2,
1680 p_end_date IN VARCHAR2,
1681 p_number_of_processes IN NUMBER)
1682 IS
1683
1684 l_has_missing_date BOOLEAN := FALSE;
1685 l_no_of_workers NUMBER;
1686
1687 BEGIN
1688
1689 errbuf := null;
1690 retcode := 0;
1691
1692 write_log('Truncating the table bix_agent_session_stg');
1693 Truncate_Table('BIX_AGENT_SESSION_STG');
1694 write_log('Done truncating the table bix_agent_session_stg');
1695
1696 write_log('Collection start date : ' || p_start_date);
1697 write_log('Collection end date : ' || p_end_date);
1698
1699 cleanup_oltp;
1700
1701 g_collect_start_date := TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
1702 g_collect_end_date := TO_DATE(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
1703
1704
1705
1706
1707 /* Collection start date will be greater than collection end date */
1708 /* if the program is executed more than once in the same half hour */
1709 IF (g_collect_start_date >= g_collect_end_date) THEN
1710 write_log('Collection start date cannot be greater than or equal to collection end date');
1711 RAISE G_PARAM_MISMATCH;
1712 END IF;
1713
1714 /* Check if time dimension is populated for the collection date range */
1715 fii_time_api.check_missing_date(g_collect_start_date, g_collect_end_date, l_has_missing_date);
1716 IF (l_has_missing_date) THEN
1717 write_log('Time dimension is not populated for the entire collection date range');
1718 RAISE G_TIME_DIM_MISSING;
1719 END IF;
1720
1721 /* if the collection date range is more than 1 day and user has specified to launch more than 1 worker , */
1722 /* then launch parallel workers to do the half hour collection of each day */
1723 IF (((g_collect_end_date - g_collect_start_date) > 1) AND
1724 (p_number_of_processes > 1)) THEN
1725 write_log('Calling procedure register_jobs');
1726 register_jobs;
1727 write_log('End procedure register_jobs');
1728
1729 /* Launch a parallel worker for each day of the collection date range or number of processes */
1730 /* user has requested for , whichever is less */
1731 l_no_of_workers := least(g_no_of_jobs, p_number_of_processes);
1732
1733 write_log('Launching Workers');
1734 FOR i IN 1 .. l_no_of_workers
1735 LOOP
1736 g_worker(i) := LAUNCH_WORKER(i);
1737 END LOOP;
1738 write_log('Number of Workers launched : ' || to_char(l_no_of_workers));
1739
1740 COMMIT;
1741
1742 /* Monitor child processes after launching them */
1743 DECLARE
1744
1745 l_unassigned_cnt NUMBER := 0;
1746 l_completed_cnt NUMBER := 0;
1747 l_wip_cnt NUMBER := 0;
1748 l_failed_cnt NUMBER := 0;
1749 l_tot_cnt NUMBER := 0;
1750 l_last_unassigned_cnt NUMBER := 0;
1751 l_last_completed_cnt NUMBER := 0;
1752 l_last_wip_cnt NUMBER := 0;
1753 l_cycle NUMBER := 0;
1754
1755 BEGIN
1756 LOOP
1757
1758 SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
1759 NVL(sum(decode(status,'COMPLETED',1,0)),0),
1760 NVL(sum(decode(status,'IN PROCESS',1,0)),0),
1761 NVL(sum(decode(status,'FAILED',1,0)),0),
1762 count(*)
1763 INTO l_unassigned_cnt,
1764 l_completed_cnt,
1765 l_wip_cnt,
1766 l_failed_cnt,
1767 l_tot_cnt
1768 FROM BIX_WORKER_JOBS
1769 WHERE OBJECT_NAME = 'BIX_AGENT_SESSION_F';
1770
1771 IF (l_failed_cnt > 0) THEN
1772 RAISE G_CHILD_PROCESS_ISSUE;
1773 END IF;
1774
1775 IF (l_tot_cnt = l_completed_cnt) THEN
1776 EXIT;
1777 END IF;
1778
1779 IF (l_unassigned_cnt = l_last_unassigned_cnt AND
1780 l_completed_cnt = l_last_completed_cnt AND
1781 l_wip_cnt = l_last_wip_cnt) THEN
1782 l_cycle := l_cycle + 1;
1783 ELSE
1784 l_cycle := 1;
1785 END IF;
1786
1787 IF (l_cycle > MAX_LOOP) THEN
1788 write_log('Infinite loop');
1789 RAISE G_CHILD_PROCESS_ISSUE;
1790 END IF;
1791
1792 dbms_lock.sleep(60);
1793
1794 l_last_unassigned_cnt := l_unassigned_cnt;
1798 END LOOP;
1795 l_last_completed_cnt := l_completed_cnt;
1796 l_last_wip_cnt := l_wip_cnt;
1797
1799
1800 END; -- Monitor child process Ends here.
1801 ELSE
1805 write_log('End procedure collect_day');
1802 /* if no child process , then collect the half hour data for the entire date range */
1803 write_log('Calling procedure collect_day');
1804 collect_day;
1806 END IF;
1807
1808 /* Summarize data to day, week, month, quater and year time buckets */
1809 write_log('Calling procedure summarize_data');
1810 summarize_data;
1811 write_log('End procedure summarize_data');
1812
1813 /* Merge the data to the main summary table from staging area */
1814 write_log('Calling procedure merge_data');
1815 merge_data;
1816 write_log('End procedure merge_data');
1817
1818 write_log('Total Rows Inserted/Updated : ' || to_char(g_rows_ins_upd));
1819
1820 write_log('Finished Procedure BIX_SESSION_SUMMARY_PKG with success at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1821
1822 write_log('Truncating the table bix_agent_session_stg');
1823 Truncate_Table('BIX_AGENT_SESSION_STG');
1824 write_log('Done truncating the table bix_agent_session_stg');
1825
1826 write_log('Calling procedure WRAPUP');
1827 bis_collection_utilities.wrapup(
1828 p_status => TRUE,
1829 p_count => g_rows_ins_upd,
1830 p_message => NULL,
1831 p_period_from => g_collect_start_date,
1832 p_period_to => g_collect_end_date);
1833
1834 EXCEPTION
1835 WHEN G_PARAM_MISMATCH THEN
1836 bis_collection_utilities.wrapup(
1837 p_status => FALSE,
1838 p_count => 0,
1839 p_message => '0 rows collected : collect start date cannot be greater than collection end date',
1840 p_period_from => g_collect_start_date,
1841 p_period_to => g_collect_end_date);
1842 WHEN G_TIME_DIM_MISSING THEN
1843 retcode := -1;
1844 errbuf := 'Time Dimension is not populated for the entire collection range';
1845 bis_collection_utilities.wrapup(
1846 p_status => FALSE,
1847 p_count => 0,
1848 p_message => 'eMail summary package failed : Error : Time dimension is not populated',
1849 p_period_from => g_collect_start_date,
1850 p_period_to => g_collect_end_date);
1851 WHEN G_CHILD_PROCESS_ISSUE THEN
1852 clean_up;
1853 retcode := SQLCODE;
1854 errbuf := SQLERRM;
1855 bis_collection_utilities.wrapup(
1856 p_status => FALSE,
1857 p_count => 0,
1858 p_message => 'eMail summary package failed : error : ' || sqlerrm,
1859 p_period_from => g_collect_start_date,
1860 p_period_to => g_collect_end_date);
1861 WHEN OTHERS THEN
1862 clean_up;
1863 retcode := SQLCODE;
1864 errbuf := SQLERRM;
1865 bis_collection_utilities.wrapup(
1866 p_status => FALSE,
1867 p_count => 0,
1868 p_message => 'eMail summary package failed : error : ' || sqlerrm,
1869 p_period_from => g_collect_start_date,
1870 p_period_to => g_collect_end_date);
1871 END main;
1872
1873 PROCEDURE load (errbuf OUT NOCOPY VARCHAR2,
1874 retcode OUT NOCOPY VARCHAR2,
1875 p_number_of_processes IN NUMBER )
1876 IS
1877 l_last_start_date DATE;
1878 l_last_end_date DATE;
1879 l_last_period_from DATE;
1880 l_last_period_to DATE;
1881 BEGIN
1882 init;
1883 write_log('End procedure init');
1884
1885 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('BIX_AGENT_SESSION_F',
1886 l_last_start_date,
1887 l_last_end_date,
1888 l_last_period_from,
1889 l_last_period_to);
1890 IF l_last_period_to IS NULL THEN
1891 l_last_period_to := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
1892 END IF;
1893
1894 Main(errbuf,
1895 retcode,
1896 TO_CHAR(l_last_period_to, 'YYYY/MM/DD HH24:MI:SS'),
1897 TO_CHAR(g_sysdate, 'YYYY/MM/DD HH24:MI:SS'),
1898 p_number_of_processes);
1899
1900 EXCEPTION
1901 WHEN OTHERS THEN
1902 RAISE;
1903 END load;
1904
1905 END BIX_SESSION_SUMMARY_PKG;