[Home] [Help]
PACKAGE BODY: APPS.BIX_DM_SESSIONINFO_SUMMARY_PKG
Source
1 PACKAGE BODY BIX_DM_SESSIONINFO_SUMMARY_PKG AS
2 /*$Header: bixxsagb.plb 120.0 2005/05/25 17:21:00 appldev noship $ */
3
4 g_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID();
5 g_program_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID();
6 g_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID();
7 g_user_id NUMBER := FND_GLOBAL.USER_ID();
8 g_login_id NUMBER := FND_GLOBAL.LOGIN_ID();
9 g_insert_count NUMBER := 0;
10 g_delete_count NUMBER := 0;
11 g_update_count NUMBER := 0;
12 g_message VARCHAR2(4000); --used to store log file messages
13 g_error_msg VARCHAR2(4000);
14 g_status VARCHAR2(10); --used to store collection status
15 g_proc_name VARCHAR2(100); --used to store procedure being processed
16 g_table_name VARCHAR2(100); --used to store table being processed
17 g_collect_start_date DATE; --used to store start date parameter user gave
18 g_collect_end_date DATE; --used to store end date parameter user gave
19 g_rounded_collect_start_date DATE; --used to store rounded start date parameter
20 g_rounded_collect_end_date DATE; --used to store rounded end date parameter
21 g_run_start_date DATE; --used to store start time when program is run
22 g_run_end_date DATE; --used to store end time of the program run
23 g_commit_chunk_size NUMBER; --based on profile value
24 g_pkg_name VARCHAR2(50) := 'BIX_DM_SESSIONINFO_SUMMARY_PKG' ;
25 g_debug_flag VARCHAR2(1) := 'N';
26
27 G_DATE_MISMATCH EXCEPTION;
28
29 PROCEDURE write_log(p_pkg_name VARCHAR2,
30 p_proc_name VARCHAR2,
31 p_msg VARCHAR2 )
32 IS
33 l_proc_name VARCHAR2(20) := 'WRITE_LOG';
34 BEGIN
35 IF (g_debug_flag = 'Y') THEN
36 fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
37 ' : ' || p_proc_name || ' : '|| p_msg);
38 END IF;
39 EXCEPTION
40 WHEN OTHERS THEN
41 RAISE;
42 END write_log;
43
44 PROCEDURE insert_log_table
45 IS
46 l_proc_name VARCHAR2(20) := 'INSERT_LOG_TABLE';
47 BEGIN
48
49 /* Insert status into log table */
50 INSERT INTO BIX_DM_COLLECT_LOG
51 (
52 collect_id,
53 collect_concur_id,
54 object_name,
55 object_type,
56 run_start_date,
57 run_end_date,
58 collect_start_date,
59 collect_end_date,
60 collect_status,
61 collect_excep_mesg,
62 rows_deleted,
63 rows_inserted,
64 rows_updated,
65 last_update_date,
66 last_updated_by,
67 creation_date,
68 created_by,
69 last_update_login,
70 request_id,
71 program_application_id,
72 program_id,
73 program_update_date
74 )
75 VALUES
76 (
77 BIX_DM_COLLECT_LOG_S.NEXTVAL,
78 null,
79 g_table_name,
80 'TABLE',
81 g_run_start_date,
82 g_run_end_date,
83 g_collect_start_date,
84 g_collect_end_date,
85 g_status,
86 g_error_msg,
87 g_delete_count,
88 g_insert_count,
89 g_update_count,
90 sysdate,
91 g_user_id,
92 sysdate,
93 g_user_id,
94 g_login_id,
95 g_request_id,
96 g_program_appl_id,
97 g_program_id,
98 sysdate
99 );
100 EXCEPTION
101 WHEN OTHERS THEN
102 RAISE;
103 END insert_log_table;
104
105
106 PROCEDURE delete_in_chunks(p_table_name IN VARCHAR2,
107 p_type IN NUMBER,
108 p_rows_deleted OUT nocopy NUMBER)
109 IS
110 l_rows_deleted NUMBER;
111 l_proc_name VARCHAR2(20) := 'DELETE_IN_CHUNKS';
112
113 BEGIN
114
115 l_rows_deleted := 0;
116
117 LOOP
118
119 IF(p_type = 1 AND p_table_name = 'BIX_DM_AGENT_SESSION_SUM') THEN
120 DELETE FROM BIX_DM_AGENT_SESSION_SUM
121 WHERE period_start_date_time between g_rounded_collect_start_date AND g_rounded_collect_end_date
122 AND rownum <= g_commit_chunk_size;
123 ELSIF(p_type = 1 AND p_table_name = 'BIX_DM_GROUP_SESSION_SUM') THEN
124 DELETE FROM BIX_DM_GROUP_SESSION_SUM
125 WHERE period_start_date_time between g_rounded_collect_start_date AND g_rounded_collect_end_date
126 AND rownum <= g_commit_chunk_size;
127 ELSIF(p_type = 2 AND p_table_name = 'BIX_DM_AGENT_SESSION_SUM') THEN
128 DELETE FROM BIX_DM_AGENT_SESSION_SUM
129 WHERE request_id = g_request_id
130 AND rownum <= g_commit_chunk_size;
131 ELSIF(p_type = 2 AND p_table_name = 'BIX_DM_GROUP_SESSION_SUM') THEN
132 DELETE FROM BIX_DM_GROUP_SESSION_SUM
133 WHERE request_id = g_request_id
134 AND rownum <= g_commit_chunk_size;
135 END IF;
136
137 l_rows_deleted := l_rows_deleted + SQL%ROWCOUNT;
138
139 IF SQL%ROWCOUNT < g_commit_chunk_size
140 THEN
141 COMMIT;
142 EXIT;
143 ELSE
144 COMMIT;
145 END IF;
146 END LOOP;
147 g_message := 'Deleted ' || l_rows_deleted || ' rows from table '
148 || p_table_name ;
149 write_log(g_pkg_name, l_proc_name, g_message );
150 p_rows_deleted := l_rows_deleted;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 RAISE;
155 END delete_in_chunks;
156
157
158 PROCEDURE insert_login_row(p_resource_id IN NUMBER,
159 p_start_date IN DATE,
160 p_secs IN NUMBER,
161 p_agent_cost IN NUMBER,
162 p_ddl_type OUT nocopy VARCHAR2)
163 IS
164 l_proc_name VARCHAR2(20) := 'INSERT_LOGIN_ROW';
165 l_exists VARCHAR2(1) := 'N' ;
166 BEGIN
167 g_proc_name := l_proc_name;
168
169 BEGIN
170 SELECT 'Y'
171 INTO l_exists
172 FROM BIX_DM_AGENT_SESSION_SUM
173 WHERE resource_id = p_resource_id
174 AND period_start_date_time = p_start_date;
175 EXCEPTION
176 WHEN NO_DATA_FOUND THEN
177 l_exists := 'N';
178 WHEN OTHERS THEN
179 raise;
180 END;
181
182 IF l_exists = 'N'
183 THEN
184 INSERT INTO BIX_DM_AGENT_SESSION_SUM
185 ( agent_session_summary_id
186 ,resource_id
187 ,period_start_date
188 ,period_start_time
189 ,period_start_date_time
190 ,last_update_date
191 ,last_updated_by
192 ,creation_date
193 ,created_by
194 ,last_update_login
195 ,login_time
196 ,available_time
197 ,idle_time
198 ,agent_cost
199 ,request_id
200 ,program_application_id
201 ,program_id
202 ,program_update_date
203 ) VALUES
204 ( BIX_DM_AGENT_SESSION_SUM_S.NEXTVAL
205 ,p_resource_id
206 ,TRUNC(p_start_date)
207 ,TO_CHAR(p_start_date,'HH24:MI')
208 ,p_start_date
209 ,SYSDATE
210 ,g_user_id
211 ,SYSDATE
212 ,g_user_id
213 ,g_user_id
214 ,p_secs
215 ,0
216 ,0
217 ,(p_secs/3600)* p_agent_cost
218 ,g_request_id
219 ,g_program_appl_id
220 ,g_program_id
221 ,SYSDATE );
222 p_ddl_type := 'I' ;
223 ELSE
224 UPDATE BIX_DM_AGENT_SESSION_SUM
225 SET login_time = login_time + p_secs,
226 agent_cost = agent_cost + ((p_secs/3600) * p_agent_cost ),
227 last_update_date = SYSDATE,
228 last_updated_by = g_user_id,
229 program_update_date = SYSDATE
230 WHERE resource_id = p_resource_id
231 AND period_start_date_time = p_start_date;
232 p_ddl_type := 'U' ;
233 END IF;
234 EXCEPTION
235 WHEN OTHERS THEN
236 RAISE;
237 END insert_login_row;
238
239 PROCEDURE collect_agent_idle_time(l_session_id IN NUMBER,
240 l_resource_id IN NUMBER,
241 l_ses_begin_Date IN DATE,
242 l_ses_end_date IN DATE)
243 --
244 --Idle time is calculated as the time between each media cycle activity.
245 --For the very first meda cycle activity, it is cycle start time - login time.
246 --
247 IS
248 l_end_Date DATE;
249 l_begin_date DATE;
250 l_period_start DATE;
251 l_idle_start DATE;
252 l_idle_end DATE;
253 l_prev_mc_start_date DATE;
254 l_prev_mc_end_date DATE;
255 l_secs number;
256 l_row_count NUMBER := 0;
257 l_row_counter NUMBER := 0;
258 l_temp VARCHAR2(100);
259 l_proc_name VARCHAR2(25) := 'COLLECT_AGENT_IDLE_TIME';
260 l_count NUMBER := 0;
261
262 CURSOR get_media_cycle_time IS
263 SELECT begin_date_time,end_date_time
264 FROM ieu_sh_activities
265 WHERE session_id = l_session_id
266 AND activity_type_code = 'MEDIA_CYCLE'
267 ORDER BY begin_date_time;
268
269 BEGIN
270
271 g_proc_name := l_proc_name ;
272 g_message := 'Start collecting agent idle time';
273 write_log( g_pkg_name, l_proc_name, g_message);
274
275 FOR media_cycle_time IN get_media_cycle_time
276 LOOP
277
278 /* If the MEDIA_CYCLE activity has been started before collection start date time
279 mark the begin date as collection start date time of the time window */
280
281 /* if the current row is the first media_cycle row, then we need to calculate the time agent was idle before
282 he pressed get work.
283 */
284
285 IF l_count = 0 THEN
286 l_begin_date := l_ses_begin_date;
287 ELSE
288 l_begin_date := l_prev_mc_end_date;
289 END IF;
290
291 l_count := l_count + 1;
292
293 /* If the MEDIA_CYCLE activity is continue to be open after the collection
294 end date, mark the end date as collection end date */
295
296 IF (media_cycle_time.begin_date_time > g_rounded_collect_end_date) THEN
297 l_end_date := g_rounded_collect_end_date + (1/(24*3600));
298 ELSE
299 l_end_date := media_cycle_time.begin_date_time;
300 END IF;
301
302 SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
303 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0') ||
304 DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
305 0,'00',1,'30',-1,'00')
306 INTO l_temp FROM DUAL;
307
308 l_period_start := TO_DATE(l_temp,'YYYY/MM/DDHH24:MI');
309 l_row_counter := 0;
310 WHILE ( l_period_start < l_end_date )
311 LOOP
312 IF (l_row_counter = 0 )
313 THEN
314 l_idle_start := l_begin_date;
315 ELSE
316 l_idle_start := l_period_start;
317 END IF;
318
319 l_idle_end := l_period_start + 1/48;
320 IF ( l_idle_end > l_end_date )
321 THEN
322 l_idle_end := l_end_date ;
323 END IF;
324 l_secs := round((l_idle_end - l_idle_start) * 24 * 3600);
325 IF (l_secs > 0 )
326 THEN
327 UPDATE BIX_DM_AGENT_SESSION_SUM
328 SET idle_time = nvl(idle_time,0)+l_secs,
329 last_update_date = SYSDATE,
330 last_updated_by = g_user_id,
331 program_update_date = SYSDATE
332 WHERE resource_id = l_resource_id
333 AND period_start_date_time = l_period_start;
334 l_row_count := l_row_count + 1;
335 END IF;
336
337 l_row_counter := l_row_counter + 1;
338 l_period_start := l_period_start + 1/48;
339
340 IF(MOD(l_row_count,g_commit_chunk_size)=0) THEN
341 COMMIT;
342 END IF;
343
344 END LOOP; -- end of WHILE loop
345
346 l_prev_mc_start_date := media_cycle_time.begin_date_time;
347 l_prev_mc_end_date := media_cycle_time.end_date_time;
348
349 IF ( l_prev_mc_end_date IS NULL) THEN
350 EXIT;
351 END IF;
352 END LOOP; -- End of FOR loop.
353
354
355 /* Calculate the time the agent is logged into UWQ after he press stop work
356 This is the time agent is idle before he log out and after he pressed stop media
357 */
358
359 IF(l_count = 0) THEN -- if agent logged in and never pressed getwork.No media cycle rows present in activity table.
360 l_begin_date := l_ses_begin_date;
361 l_end_date := l_ses_end_date;
362 ELSIF(l_prev_mc_end_date IS NOT NULL) THEN -- do not calculate idle time , if the last media_cycle is not ended.
363
364 l_begin_date := l_prev_mc_end_date;
365 l_end_date := l_ses_end_date;
366 END IF;
367
368 IF(l_count = 0 OR l_prev_mc_end_date IS NOT NULL ) THEN
369 SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
370 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0') ||
371 DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
372 0,'00',1,'30',-1,'00')
373 INTO l_temp FROM DUAL;
374
375 l_period_start := TO_DATE(l_temp,'YYYY/MM/DDHH24:MI');
376 l_row_counter := 0;
377 WHILE ( l_period_start < l_end_date )
378 LOOP
379 IF (l_row_counter = 0 )
380 THEN
381 l_idle_start := l_begin_date;
382 ELSE
383 l_idle_start := l_period_start;
384 END IF;
385
386 l_idle_end := l_period_start + 1/48;
387 IF ( l_idle_end > l_end_date )
388 THEN
389 l_idle_end := l_end_date ;
390 END IF;
391 l_secs := round((l_idle_end - l_idle_start) * 24 * 3600);
392 IF (l_secs > 0 )
393 THEN
394 UPDATE BIX_DM_AGENT_SESSION_SUM
395 SET idle_time = nvl(idle_time,0)+l_secs,
396 last_update_date = SYSDATE,
397 last_updated_by = g_user_id,
398 program_update_date = SYSDATE
399 WHERE resource_id = l_resource_id
400 AND period_start_date_time = l_period_start;
401 l_row_count := l_row_count + 1;
402 END IF;
403
404 l_row_counter := l_row_counter + 1;
405 l_period_start := l_period_start + 1/48;
406
407 IF(MOD(l_row_count,g_commit_chunk_size)=0) THEN
408 COMMIT;
409 END IF;
410
411 END LOOP; -- end of WHILE loop
412 END IF;
413
414 COMMIT;
415 g_update_count := l_row_count;
416 g_message := 'Finished collecting agent idle time : Updated ' ||
417 l_row_count || ' rows in BIM_DM_AGENT_SESSION_SUM' ;
418 write_log(g_pkg_name, l_proc_name, g_message );
419 EXCEPTION
420 WHEN OTHERS THEN
421 RAISE;
422 END collect_agent_idle_time;
423
424 PROCEDURE collect_agent_login_time
425 IS
426 l_end_Date DATE;
427 l_begin_date DATE;
428 l_period_start DATE;
429 l_login_start DATE;
430 l_login_end DATE;
431 l_secs NUMBER;
432 l_row_count NUMBER :=0;
433 l_row_counter NUMBER := 0;
434 l_agent_cost NUMBER := 0;
435 l_temp VARCHAR2(100);
436 l_ddl_type VARCHAR2(1);
437 l_proc_name VARCHAR2(25) := 'COLLECT_AGENT_LOGIN_TIME';
438 l_counter NUMBER := 0;
439
440 CURSOR get_login_time IS
441 SELECT iss.session_id session_id,
442 iss.resource_id resource_id,
443 iss.begin_date_time begin_date_time,
444 iss.end_date_time end_date_time
445 FROM ieu_sh_sessions iss
446 WHERE iss.application_id = 696
447 AND iss.begin_date_time <= g_rounded_collect_end_date
448 AND (iss.end_date_time >= g_rounded_collect_start_date
449 OR iss.end_date_time is NULL ) ;
450 BEGIN
451
452 g_proc_name := l_proc_name;
453
454 BEGIN
455 IF (FND_PROFILE.DEFINED('BIX_DM_AGENT_COST')) THEN
456 l_agent_cost := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_AGENT_COST'));
457 ELSE
458 g_message := 'Agent cost is not defined. Agent Cost set to zero.';
459 write_log( g_pkg_name, l_proc_name, g_message );
460 END IF;
461 EXCEPTION
462 WHEN OTHERS THEN
463 l_agent_cost := 0;
464 g_message := 'Failed to get the Agent cost.';
465 write_log( g_pkg_name, l_proc_name, g_message );
466 END;
467
468 g_message := 'Start collecting agent login time into BIX_DM_AGENT_SESSION_SUM';
469 write_log( g_pkg_name, l_proc_name, g_message );
470
471 FOR login_time IN get_login_time
472 LOOP
473 /* If the session started before the time window that the concurrent
474 program is running mark the begin date as collection start date
475 time of the time window */
476 IF ( login_time.begin_date_time < g_rounded_collect_start_date )
477 THEN
478 l_begin_date := g_rounded_collect_start_date;
479 ELSE
480 l_begin_date := login_time.begin_date_time;
481 END IF;
482
483 /* If the session is ended after the collection end date , mark the
484 end date as collection end date */
485 IF( login_time.end_date_time IS NULL OR
486 login_time.end_date_time > g_rounded_collect_end_date)
487 THEN
488 l_end_date := g_rounded_collect_end_date + (1/(24*3600));
489 ELSE
490 l_end_date := login_time.end_date_time;
491 END IF;
492
493 /* get the nearest lowest time bucket for the begin date
494 to populate appropriate bucket */
495 SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
496 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')||
497 DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
498 0,'00',1,'30',-1,'00')
499 INTO l_temp FROM DUAL;
500
501 l_period_start := TO_DATE(l_temp,'YYYY/MM/DDHH24:MI');
502 l_row_counter := 0;
503 WHILE ( l_period_start < l_end_date )
504 LOOP
505 IF (l_row_counter = 0 )
506 THEN
507 l_login_start := l_begin_date;
508 ELSE
509 l_login_start := l_period_start;
510 END IF;
511
512 l_login_end := l_period_start + 1/48;
513 IF ( l_login_end > l_end_date )
514 THEN
515 l_login_end := l_end_date ;
516 END IF;
517
518 l_secs := round((l_login_end - l_login_start) * 24 * 3600);
519 INSERT_LOGIN_ROW(login_time.resource_id,l_period_start,l_secs,
520 l_agent_cost, l_ddl_type);
521
522 IF l_ddl_type = 'I'
523 THEN
524 l_row_count := l_row_count + 1;
525 END IF;
526 l_row_counter := l_row_counter + 1;
527 l_period_start := l_period_start + 1/48;
528
529 IF(MOD(l_row_count,g_commit_chunk_size)=0)
530 THEN
531 COMMIT;
532 END IF;
533 END LOOP; -- end of WHILE loop
534
535 -- call the collect_agent_idle_time to collect agent idle time for this session.
536
537 COLLECT_AGENT_IDLE_TIME(login_time.session_id,login_time.resource_id,l_begin_date,l_end_date);
538
539 END LOOP; -- End of FOR loop.
540
541 COMMIT;
542 g_message := 'Finished collecting agent login time : Inserted ' ||
543 l_row_count || ' rows into BIM_DM_AGENT_SESSION_SUM' ;
544 write_log(g_pkg_name, l_proc_name, g_message );
545
546 g_insert_count := l_row_count;
547
548 EXCEPTION
549 WHEN OTHERS THEN
550 RAISE;
551 END collect_agent_login_time;
552
553 PROCEDURE collect_agent_avail_time
554 AS
555 l_end_Date DATE;
556 l_begin_date DATE;
557 l_period_start DATE;
558 l_login_start DATE;
559 l_login_end DATE;
560 l_secs number;
561 l_row_count NUMBER := 0;
562 l_row_counter NUMBER := 0;
563 l_temp VARCHAR2(100);
564 l_proc_name VARCHAR2(25) := 'COLLECT_AGENT_AVAIL_TIME';
565
566 CURSOR get_avail_time IS
567 SELECT iss.resource_id resource_id,
568 isa.begin_date_time begin_date_time,
569 isa.deliver_date_time end_date_time
570 FROM ieu_sh_sessions iss,
571 ieu_sh_activities isa
572 WHERE isa.begin_date_time <= g_rounded_collect_end_date
573 AND (isa.deliver_date_time >= g_rounded_collect_start_date
574 OR (isa.deliver_date_time is NULL AND isa.end_date_time IS NULL)
575 )
576 AND iss.application_id = 696
577 AND iss.session_id = isa.session_id
578 AND isa.activity_type_code = 'MEDIA'
579 UNION ALL
580 SELECT iss.resource_id resource_id,
581 isa.begin_date_time begin_date_time,
582 isa.end_date_time end_date_time
583 FROM ieu_sh_sessions iss,
584 ieu_sh_activities isa
585 WHERE isa.begin_date_time <= g_rounded_collect_end_date
586 AND isa.end_date_time >= g_rounded_collect_start_date
587 AND isa.deliver_date_time IS NULL
588 AND iss.application_id = 696
589 AND iss.session_id = isa.session_id
590 AND isa.activity_type_code = 'MEDIA';
591
592 BEGIN
593
594 g_proc_name := l_proc_name ;
595 g_message := 'Start collecting agent available time';
596 write_log( g_pkg_name, l_proc_name, g_message);
597
598 FOR avail_time IN get_avail_time
599 LOOP
600
601 /* If the Resource has been avilable before collection start date time
602 mark the begin date as collection start date time of the time window */
603 IF ( avail_time.begin_date_time < g_rounded_collect_start_date ) THEN
604 l_begin_date := g_rounded_collect_start_date;
605 ELSE
606 l_begin_date := avail_time.begin_date_time;
607 END IF;
608
609 /* If the Resource is continue to be avilable after the collection
610 end date, mark the end date as collection end date */
611 IF( avail_time.end_date_time IS NULL OR
612 avail_time.end_date_time > g_rounded_collect_end_date) THEN
613 l_end_date := g_rounded_collect_end_date + (1/(24*3600));
614 ELSE
615 l_end_date := avail_time.end_date_time;
616 END IF;
617
618 SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
619 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0') ||
620 DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
621 0,'00',1,'30',-1,'00')
622 INTO l_temp FROM DUAL;
623
624 l_period_start := TO_DATE(l_temp,'YYYY/MM/DDHH24:MI');
625 l_row_counter := 0;
626 WHILE ( l_period_start < l_end_date )
627 LOOP
628 IF (l_row_counter = 0 )
629 THEN
630 l_login_start := l_begin_date;
631 ELSE
632 l_login_start := l_period_start;
633 END IF;
634
635 l_login_end := l_period_start + 1/48;
636 IF ( l_login_end > l_end_date )
637 THEN
638 l_login_end := l_end_date ;
639 END IF;
640 l_secs := round((l_login_end - l_login_start) * 24 * 3600);
641 IF (l_secs > 0 )
642 THEN
643 UPDATE BIX_DM_AGENT_SESSION_SUM
644 SET available_time = nvl(available_time,0)+l_secs,
645 last_update_date = SYSDATE,
646 last_updated_by = g_user_id,
647 program_update_date = SYSDATE
648 WHERE resource_id = avail_time.resource_id
649 AND period_start_date_time = l_period_start;
650 l_row_count := l_row_count + 1;
651 END IF;
652
653 l_row_counter := l_row_counter + 1;
654 l_period_start := l_period_start + 1/48;
655
656 IF(MOD(l_row_count,g_commit_chunk_size)=0) THEN
657 COMMIT;
658 END IF;
659
660 END LOOP; -- end of WHILE loop
661 END LOOP; -- End of FOR loop.
662
663 COMMIT;
664 g_update_count := l_row_count;
665 g_message := 'Finished collecting agent available time : Updated ' ||
666 l_row_count || ' rows in BIM_DM_AGENT_SESSION_SUM' ;
667 write_log(g_pkg_name, l_proc_name, g_message );
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 RAISE;
672 END collect_agent_avail_time;
673
674
675 PROCEDURE populate_groups
676 IS
677
678 l_row_count NUMBER := 0;
679 l_proc_name VARCHAR2(20) := 'POPULATE_GROUPS';
680
681 CURSOR group_agents IS
682 SELECT group_denorm.parent_group_id group_id,
683 agt_sum.period_start_date,
684 agt_sum.period_start_time,
685 agt_sum.period_start_date_time,
686 SUM(agt_sum.available_time) available_time,
687 SUM(agt_sum.login_time) login_time,
688 SUM(agt_sum.idle_time) idle_time,
689 SUM(agt_sum.agent_cost) group_cost
690 FROM bix_dm_agent_session_sum agt_sum,
691 jtf_rs_group_members groups,
692 jtf_rs_groups_denorm group_denorm
693 WHERE agt_sum.period_start_date_time BETWEEN g_rounded_collect_start_date
694 AND g_rounded_collect_end_date
695 AND agt_sum.resource_id = groups.resource_id
696 AND groups.group_id = group_denorm.group_id
697 AND NVL(groups.delete_flag,'N') <> 'Y'
698 AND agt_sum.period_start_date_time BETWEEN
699 NVL(group_denorm.start_date_active,agt_sum.period_start_date_time)
700 AND NVL(group_denorm.end_date_active,SYSDATE)
701 AND groups.group_member_id =
702 (select max(mem1.group_member_id)
703 from jtf_rs_group_members mem1
704 where mem1.group_id in
705 (select den1.group_id
706 from jtf_rs_groups_denorm den1
707 where den1.parent_group_id = group_denorm.parent_group_id
708 AND agt_sum.period_start_date_time BETWEEN
709 NVL(den1.start_date_active,agt_sum.period_start_date_time)
710 AND NVL(den1.end_date_active,SYSDATE)
711 )
712 AND mem1.resource_id = groups.resource_id
713 AND nvl(mem1.delete_flag,'N') <> 'Y'
714 )
715 GROUP BY group_denorm.parent_group_id,
716 agt_sum.period_start_date_time,
717 agt_sum.period_start_date,
718 agt_sum.period_start_time;
719
720 BEGIN
721 g_insert_count := 0;
722 g_delete_count := 0;
723 g_update_count := 0;
724 g_proc_name := l_proc_name ;
725 g_table_name := 'BIX_DM_GROUP_SESSION_SUM';
726
727 /* Delete data between these dates and re-compute */
728 delete_in_chunks( 'BIX_DM_GROUP_SESSION_SUM', 1, g_delete_count);
729
730 g_message := 'Start Inserting rows into BIX_DM_GROUP_SESSION_SUM table';
731 write_log(g_pkg_name, l_proc_name, g_message );
732
733 FOR groupinfo IN group_agents
734 LOOP
735 INSERT INTO BIX_DM_GROUP_SESSION_SUM
736 ( group_session_summary_id
737 ,group_id
738 ,period_start_date
739 ,period_start_time
740 ,period_start_date_time
741 ,last_update_date
742 ,last_updated_by
743 ,creation_date
744 ,created_by
745 ,last_update_login
746 ,available_time
747 ,login_time
748 ,idle_time
749 ,group_cost
750 ,request_id
751 ,program_application_id
752 ,program_id
753 ,program_update_date )
754 VALUES (
755 BIX_DM_GROUP_CALL_SUM_S.NEXTVAL
756 ,groupinfo.group_id
757 ,groupinfo.period_start_date
758 ,groupinfo.period_start_time
759 ,groupinfo.period_start_date_time
760 ,SYSDATE
761 ,g_user_id
762 ,SYSDATE
763 ,g_user_id
764 ,g_user_id
765 ,groupinfo.available_time
766 ,groupinfo.login_time
767 ,groupinfo.idle_time
768 ,groupinfo.group_cost
769 ,g_request_id
770 ,g_program_appl_id
771 ,g_program_id
772 ,SYSDATE
773 );
774
775 l_row_count := l_row_count + 1;
776 IF(MOD(l_row_count,g_commit_chunk_size)=0) THEN
777 COMMIT;
778 END IF;
779
780 END LOOP;
781
782 g_message := 'Finished inserting rows into BIX_DM_GROUP_SESSION_SUM. Inserted ' || l_row_count || ' rows ';
783 write_log(g_pkg_name, l_proc_name, g_message );
784
785 g_insert_count := l_row_count;
786
787 g_run_end_date := sysdate;
788 g_status := 'SUCCESS';
789 insert_log_table;
790 COMMIT;
791
792 EXCEPTION
793 WHEN OTHERS THEN
794 g_message := 'ERROR : '|| sqlerrm;
795 g_error_msg := 'Failed while executing ' || g_proc_name || ':'
796 || sqlcode ||':'|| sqlerrm;
797 write_log(g_pkg_name, l_proc_name, g_message);
798
799 g_message :='Start rolling back data in BIX_DM_GROUP_SESSION_SUM table';
800 write_log(g_pkg_name, l_proc_name, g_message );
801
802 BEGIN
803 delete_in_chunks('BIX_DM_GROUP_SESSION_SUM', 2, l_row_count);
804 g_message := 'Finished rolling back data in BIX_DM_GROUP_SESSION_SUM table';
805 write_log(g_pkg_name, l_proc_name, g_message );
806 EXCEPTION
807 WHEN OTHERS THEN
808 g_message := 'Failed to roll back data in BIX_DM_GROUP_SESSION_SUM table ';
809 write_log(g_pkg_name, l_proc_name, g_message );
810 END;
811
812 g_message := 'Start rolling back data in BIX_DM_AGENT_SESSION_SUM table';
813 write_log(g_pkg_name, l_proc_name, g_message );
814
815 BEGIN
816 delete_in_chunks('BIX_DM_AGENT_SESSION_SUM', 2, l_row_count);
817 g_message := 'Finished Rollling back data in BIX_DM_AGENT_SESSION_SUM table ' ;
818 write_log(g_pkg_name, l_proc_name, g_message );
819 EXCEPTION
820 WHEN OTHERS THEN
821 g_message := 'Failed to roll back data in BIX_DM_AGENT_SESSION_SUM table ';
822 write_log(g_pkg_name, l_proc_name, g_message );
823 END;
824
825 UPDATE BIX_DM_COLLECT_LOG
826 SET collect_status = 'FAILURE',
827 rows_inserted = 0,
828 rows_updated = 0,
829 collect_excep_mesg = g_error_msg
830 WHERE request_id = g_request_id
831 AND object_name = 'BIX_DM_AGENT_SESSION_SUM';
832
833 g_run_end_date := sysdate;
834 g_status := 'FAILURE';
835 insert_log_table;
836
837 RAISE;
838 END populate_groups;
839
840
841 PROCEDURE populate_agents
842 IS
843 l_proc_name VARCHAR2(20) := 'POPULATE_AGENTS';
844 l_delete_count NUMBER := 0;
845 BEGIN
846 g_insert_count := 0;
847 g_delete_count := 0;
848 g_update_count := 0;
849 g_proc_name := l_proc_name;
850 g_table_name := 'BIX_DM_AGENT_SESSION_SUM';
851
852 /* Delete data between these dates and re-compute */
853 delete_in_chunks( 'BIX_DM_AGENT_SESSION_SUM', 1, g_delete_count);
854
855 /* Procedure collects Agent login time from IEU_SH_SESSIONS table */
856 collect_agent_login_time;
857
858 /* Procedure collects Agent avialable time from IEU_SH_SESSIONS
859 ,IEU_SH_ACTIVITIES tables */
860 collect_agent_avail_time;
861
862 /* Insert the status into BIX_DM_COLLECT_LOG table */
863 g_run_end_date := sysdate;
864 g_status := 'SUCCESS';
865 insert_log_table;
866
867 COMMIT; --commit after all rows are inserted in bix_dm_agent_session_sum
868
869 EXCEPTION
870 WHEN OTHERS THEN
871 g_message := 'Failed while populating bix_dm_agent_session_sum : '||
872 sqlerrm;
873 write_log(g_pkg_name, l_proc_name, g_message);
874
875 g_message :='Start rolling back data in BIX_DM_AGENT_SESSION_SUM table';
876 write_log(g_pkg_name, l_proc_name, g_message );
877
878 BEGIN
879 delete_in_chunks('BIX_DM_AGENT_SESSION_SUM', 2, l_delete_count);
880 g_message := 'Finished rolling back data in BIX_DM_AGENT_SESSION_SUM table' ;
881 write_log(g_pkg_name, l_proc_name, g_message );
882 EXCEPTION
883 WHEN OTHERS THEN
884 g_message := 'Failed to roll back data in BIX_DM_AGENT_SESSION_SUM table ';
885 write_log(g_pkg_name, l_proc_name, g_message );
886 END;
887
888 g_run_end_date := sysdate;
889 g_status := 'FAILURE';
890 g_insert_count := 0;
891 g_update_count := 0;
892 g_error_msg := 'Failed while executing ' || g_proc_name
893 || ':' || sqlcode ||':'|| sqlerrm;
894 insert_log_table;
895 RAISE;
896
897 END populate_agents;
898
899 /*
900 This procedure is the main procedure which is called from the concurrent
901 program. It calls two other procedures to get the agent and group
902 session information.
903 */
904 PROCEDURE populate_session_sum_tables( errbuf OUT nocopy VARCHAR2,
905 retcode OUT nocopy VARCHAR2,
906 p_start_date IN VARCHAR2,
907 p_end_date IN VARCHAR2 )
908 IS
909 l_proc_name VARCHAR2(35) := 'POPULATE_SESSION_SUMMARY_TABLES';
910
911 BEGIN
912
913 g_run_start_date := sysdate;
914 g_proc_name := l_proc_name;
915
916 /* Determine value of commit size. If the profile is not defined,
917 assume 100 rows. */
918 IF FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')
919 THEN
920 g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
921 ELSE
922 g_commit_chunk_size := 100;
923 END IF;
924
925 IF (FND_PROFILE.DEFINED('BIX_DBI_DEBUG')) THEN
926 g_debug_flag := nvl(FND_PROFILE.VALUE('BIX_DBI_DEBUG'), 'N');
927 END IF;
928
929 /* Concurrent program is passing date as YYYY/MM/DD HH24:MI:SS. */
930 g_collect_start_date := TO_DATE(p_start_date,'YYYY/MM/DD HH24:MI:SS');
931 g_collect_end_date := TO_DATE(p_end_date,'YYYY/MM/DD HH24:MI:SS');
932
933 IF (g_collect_start_date > SYSDATE) THEN
934 g_collect_start_date := SYSDATE;
935 END IF;
936
937 IF (g_collect_end_date > SYSDATE) THEN
938 g_collect_end_date := SYSDATE;
939 END IF;
940
941 IF (g_collect_start_date > g_collect_end_date) THEN
942 RAISE G_DATE_MISMATCH;
943 END IF;
944
945 /* Round the collection start date to the nearest lower time bucket.
946 eg: if time is between 10:00 and 10:29 round it to 10:00.
947 */
948 SELECT TO_DATE(TO_CHAR(g_collect_start_date,'YYYY/MM/DD')||
949 LPAD(TO_CHAR(g_collect_start_date,'HH24:'),3,'0')||
950 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_start_date,'MI'))-29),
951 0,'00:00',1,'30:00',-1,'00:00'), 'YYYY/MM/DDHH24:MI:SS')
952 INTO g_rounded_collect_start_date
953 FROM DUAL;
954
955 /* Round the collection end date to nearest higher time bucket.
956 eg: if time is between 10:00 and 10:29 round it to 10:29:59
957 */
958 SELECT TO_DATE(
959 TO_CHAR(g_collect_end_date,'YYYY/MM/DD')||
960 LPAD(TO_CHAR(g_collect_end_date,'HH24:'),3,'0')||
961 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_end_date,'MI'))-29),
962 0,'29:59',1,'59:59',-1,'29:59'), 'YYYY/MM/DDHH24:MI:SS')
963 INTO g_rounded_collect_end_date
964 FROM DUAL;
965
966 g_message := 'Collection Period : '||
967 to_char(g_rounded_collect_start_date,'DD-MON-YYYY HH24:MI:SS') || ' to '
968 || to_char(g_rounded_collect_end_date, 'DD-MON-YYYY HH24:MI:SS');
969 write_log(g_pkg_name, l_proc_name, g_message);
970
971 g_message := '-----------------------------------------------------------';
972 write_log(g_pkg_name, l_proc_name, g_message);
973
974 g_message := 'Start collecting agent session information';
975 write_log(g_pkg_name, l_proc_name, g_message);
976
977 populate_agents;
978
979 g_message := 'Finished collecting agent session information';
980 write_log(g_pkg_name, l_proc_name, g_message);
981
982 g_message := '----------------------------------------------------------';
983 write_log(g_pkg_name, l_proc_name, g_message);
984
985 g_message := 'Start processing group session information';
986 write_log(g_pkg_name, l_proc_name, g_message);
987
988 populate_groups;
989
990 g_message := 'Finished processing group session information';
991 write_log(g_pkg_name, l_proc_name, g_message);
992
993 g_message := '--------------------------------------------------------';
994 write_log(g_pkg_name, l_proc_name, g_message);
995
996 /*
997 Success log tables were already inserted, so just commit
998 */
999 COMMIT;
1000
1001 /*
1002 *CALL the procedure to populate the session by campaign tables
1003 */
1004
1005 g_message := 'Calling bix_dm_sessbycamp_pkg.populate_all ';
1006 write_log(g_pkg_name, l_proc_name, g_message);
1007
1008 g_message := '--------------------------------------------------------';
1009 write_log(g_pkg_name, l_proc_name, g_message);
1010
1011 BIX_DM_SESSBYCAMP_PKG.populate_all(errbuf,retcode, to_char(g_collect_start_date, 'YYYY/MM/DD HH24:MI:SS') ,
1012 to_char(g_collect_end_date, 'YYYY/MM/DD HH24:MI:SS') );
1013
1014 g_message := 'Returned to BIX_DM_SESSIONINFO:Completed BIX_DM_SESSBYCAMP';
1015 write_log(g_pkg_name, l_proc_name, g_message);
1016
1017 g_message := '--------------------------------------------------------';
1018 write_log(g_pkg_name, l_proc_name, g_message);
1019
1020 EXCEPTION
1021 WHEN G_DATE_MISMATCH THEN
1022 retcode := -1;
1023 errbuf := 'Collect Start Date cannot be greater than collection end date';
1024 write_log(g_pkg_name, l_proc_name, 'Collect Start Date cannot be greater than collection end date');
1025 WHEN OTHERS THEN
1026 retcode := sqlcode;
1027 errbuf := sqlerrm;
1028 g_status := 'FAILURE';
1029 g_error_msg := 'Failed while executing ' || g_proc_name || ':' ||
1030 sqlcode ||':'|| sqlerrm;
1031 g_message := 'ERROR : ' || sqlerrm;
1032 write_log(g_pkg_name, g_proc_name, g_message);
1033
1034 COMMIT;
1035 END populate_session_sum_tables;
1036
1037 END BIX_DM_SESSIONINFO_SUMMARY_PKG;