1 PACKAGE BODY bix_uwq_summary_pkg AS
2 /*$Header: bixxuwsb.pls 115.21 2003/08/19 21:34:18 djambula ship $ */
3
4 v_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID();
5 v_program_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID();
6 v_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID();
7 v_user_id NUMBER := FND_GLOBAL.USER_ID();
8 v_insert_count NUMBER := 0;
9 v_delete_count NUMBER := 0;
10 v_message VARCHAR2(4000); --used for storing log file messages that need to be inserted
11 v_status VARCHAR2(10); --used for storing the status that needs to be inserted
12 v_proc_name VARCHAR2(100); --global variable to store the procedure being processed
13 v_table_name VARCHAR2(100); --global variable to store the table being processed
14 v_collect_start_date DATE; --used for storing the start date parameter the user gave
15 v_collect_end_date DATE; --used for storing the end date parameter the suer gave
16 v_run_start_date DATE; --used for recording run statistics for each procedure
17 v_run_end_date DATE; --used for recording run statistics for each procedure
18 v_sysdate DATE := sysdate; --used for deleting in case of errors
19 v_delete_size NUMBER; --based on profile value
20 g_debug_flag VARCHAR2(1) := 'N';
21
22 G_DATE_MISMATCH EXCEPTION;
23
24 PROCEDURE populate_all
25 (
26 errbuf OUT NOCOPY VARCHAR2,
27 retcode OUT NOCOPY VARCHAR2,
28 p_start_date IN VARCHAR2,
29 p_end_date IN VARCHAR2
30 )
31
32 --
33 --This procedure is a "wrapper" procedure which sequentially calls two other procedures to
34 --populate the two tables. First, we do a select to rab the max and min of begin_date_time
35 --between the date range specified by the user. Then we loop through and populate the two tables
36 --for the dates between the max and min dates.
37 --
38 IS
39
40 v_min_date DATE;
41 v_max_date DATE;
42
43 BEGIN
44
45 v_proc_name := 'POPULATE_ALL';
46
47 --
48 --Determine value of commit size, if profile not defined, assume 100 rows.
49 --
50
51 IF FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')
52 THEN
53 v_delete_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
54 ELSE
55 v_delete_size := 100;
56 END IF;
57
58 IF (FND_PROFILE.DEFINED('BIX_DBI_DEBUG')) THEN
59 g_debug_flag := nvl(FND_PROFILE.VALUE('BIX_DBI_DEBUG'), 'N');
60 END IF;
61
62 --
63 --Concurrent program is passing date as YYYY/MM/DD HH24:MI:SS.
64 --
65 v_collect_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
66 v_collect_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
67
68 IF (v_collect_start_date > SYSDATE) THEN
69 v_collect_start_date := SYSDATE;
70 END IF;
71
72 IF (v_collect_end_date > SYSDATE) THEN
73 v_collect_end_date := SYSDATE;
74 END IF;
75
76 IF (v_collect_start_date > v_collect_end_date) THEN
77 RAISE G_DATE_MISMATCH;
78 END IF;
79
80 --
81 --Select the max and min dates
82 --
83 SELECT trunc(min(begin_date_time)), trunc(max(begin_date_time))
84 INTO v_min_date, v_max_date
85 FROM ieu_sh_sessions
86 WHERE (
87 last_update_date BETWEEN v_collect_start_date AND v_collect_end_date
88 OR ACTIVE_FLAG = 'T'
89 )
90 AND application_id = 696;
91
92
93 /* Even if there is no activity for specific day still record need to be inserted for each resource
94 if they have logged into UWQ in the past 60 days.
95 */
96
97 IF (v_min_date > TRUNC(v_collect_start_date) ) THEN
98 v_min_date := TRUNC(v_collect_start_date);
99 END IF;
100
101
102 IF ( v_max_date < TRUNC(v_collect_end_date) ) THEN
103 v_max_date := TRUNC(v_collect_end_date) ;
104 END IF;
105
106 v_message := 'Started insert agents table on ';
107 write_log(v_proc_name, v_message);
108
109
110 populate_agents(v_min_date, v_max_date);
111
112 v_message := 'Completed insert agents table on ';
113 write_log(v_proc_name, v_message);
114
115 v_message := 'Started insert groups table on ';
116 write_log(v_proc_name, v_message);
117
118
119 populate_groups(v_min_date, v_max_date);
120
121
122 v_message := 'Completed insert groups table on ';
123 write_log(v_proc_name, v_message);
124
125 --Success log tables were already inserted, so just commit.
126
127 COMMIT;
128
129 EXCEPTION
130 WHEN G_DATE_MISMATCH THEN
131 retcode := -1;
132 errbuf := 'Collect Start Date cannot be greater than collection end date';
133 write_log(v_proc_name, 'Collect Start Date cannot be greater than collection end date');
134 WHEN OTHERS
135 THEN
136
137 --
138 --Exception occured: Delete all data which was inserted in this run
139 --
140 LOOP
141
142 DELETE FROM bix_dm_uwq_agent_sum
143 WHERE last_update_date >= v_sysdate
144 AND rownum <= v_delete_size;
145
146 IF SQL%ROWCOUNT >= v_delete_size --this means we need to loop again
147 THEN
148 COMMIT;
149 ELSE --this means all rows deleted, exit loop
150 COMMIT;
151 EXIT;
152 END IF;
153
154 END LOOP;
155
156 LOOP
157
158 DELETE FROM bix_dm_uwq_group_sum
159 WHERE last_update_date >= v_sysdate
160 AND rownum <= v_delete_size;
161
162 IF SQL%ROWCOUNT >= v_delete_size --this means we need to loop again
163 THEN
164 COMMIT;
165 ELSE --this means all rows deleted, exit loop
166 COMMIT;
167 EXIT;
168 END IF;
169
170 END LOOP;
171
172 DELETE FROM bix_dm_collect_log
173 WHERE last_update_date >= v_sysdate;
174
175 COMMIT;
176
177 retcode := sqlcode;
178 errbuf := sqlerrm;
179 v_status := 'FAILURE';
180 v_message := 'Failed while executing ' || v_proc_name || ':' || sqlcode ||':'|| sqlerrm;
181
182 write_log(v_proc_name, v_message);
183
184 --Create error log table entry for agent summary
185 v_table_name := 'BIX_DM_UWQ_AGENT_SUM';
186 v_insert_count := 0;
187 v_delete_count := 0;
188 insert_log_table;
189
190 --Create error log table entry for group summary
191 v_table_name := 'BIX_DM_UWQ_GROUP_SUM';
192 v_insert_count := 0;
193 v_delete_count := 0;
194 insert_log_table;
195
196 COMMIT;
197
198 END populate_all;
199
200 PROCEDURE populate_agents
201 (
202 p_start_date IN DATE,
203 p_end_date IN DATE
204 )
205
206 --
207 --This procedure populates the BIX_DM_UWQ_AGENT_SUM table.
208 --
209 IS
210
211 v_insert_date DATE;
212 v_week_start_date DATE;
213 v_day_login NUMBER;
214 v_day_duration NUMBER;
215 v_prior_week_login NUMBER;
216 v_prior_week_duration NUMBER;
217 v_current_week_login NUMBER;
218 v_current_week_duration NUMBER;
219 v_prior_month_login NUMBER;
220 v_prior_month_duration NUMBER;
221 v_current_month_login NUMBER;
222 v_current_month_duration NUMBER;
223
224 --
225 --This cursor will select the resource_id, and using decode statements, calculate
226 --the login information for the required days.
227 --
228 CURSOR c_days(p_insert_date DATE)
229 IS
230 SELECT ses.resource_id RESOURCE_ID,
231 p_insert_date DAY,
232 max(decode(trunc(begin_date_time), p_insert_date,1,0)) DAY_LOGIN,
233 round((sum(decode(trunc(begin_date_time), p_insert_date,
234 (decode(end_date_time,NULL,sysdate,end_date_time)- begin_date_time)
235 ,0)))*24*3600) DAY_DURATION,
236 max(decode(trunc(begin_date_time), (p_insert_date-1),1,0)) DAY1_LOGIN,
237 round((sum(decode(trunc(begin_date_time), trunc(p_insert_date-1),
238 (decode(end_date_time,NULL,sysdate,end_date_time)-begin_date_time)
239 ,0)))*24*3600) DAY1_DURATION,
240 max(decode(trunc(begin_date_time), (p_insert_date-2),1,0)) DAY2_LOGIN,
241 round((sum(decode(trunc(begin_date_time), (p_insert_date-2),
242 (decode(end_date_time,NULL,sysdate,end_date_time)-begin_date_time)
243 ,0)))*24*3600) DAY2_DURATION,
244 max(decode(trunc(begin_date_time), (p_insert_date-3),1,0)) DAY3_LOGIN,
245 round((sum(decode(trunc(begin_date_time), (p_insert_date-3),
246 (decode(end_date_time,NULL,sysdate,end_date_time)-begin_date_time)
247 ,0)))*24*3600) DAY3_DURATION,
248 max(decode(trunc(begin_date_time), (p_insert_date-4),1,0)) DAY4_LOGIN,
249 round((sum(decode(trunc(begin_date_time), (p_insert_date-4),
250 (decode(end_date_time,NULL,sysdate,end_date_time)-begin_date_time)
251 ,0)))*24*3600) DAY4_DURATION,
252 max(decode(trunc(begin_date_time), (p_insert_date-5),1,0)) DAY5_LOGIN,
253 round((sum(decode(trunc(begin_date_time), (p_insert_date-5),
254 (decode(end_date_time,NULL,sysdate,end_date_time)-begin_date_time)
255 ,0)))*24*3600) DAY5_DURATION,
256 max(decode(trunc(begin_date_time), (p_insert_date-6),1,0)) DAY6_LOGIN,
257 round((sum(decode(trunc(begin_date_time), (p_insert_date-6),
258 (decode(end_date_time,NULL,sysdate,end_date_time)-begin_date_time)
259 ,0)))*24*3600) DAY6_DURATION
260 from ieu_sh_sessions ses
261 where begin_date_time > p_insert_date-62
262 and begin_date_time < p_insert_date+1
263 and application_id = 696
264 group by ses.resource_id, p_insert_date;
265
266 BEGIN
267
268 v_insert_date := p_start_date;
269 v_insert_count := 0;
270 v_delete_count := 0;
271 v_proc_name := 'POPULATE_AGENTS';
272 v_table_name := 'BIX_DM_UWQ_AGENT_SUM';
273 v_run_start_date := sysdate;
274
275 --
276 --Delete data between these dates and re-compute
277 --
278
279 LOOP
280
281 DELETE FROM bix_dm_uwq_agent_sum
282 WHERE day BETWEEN p_start_date AND p_end_date
283 AND rownum <= v_delete_size;
284
285 IF SQL%ROWCOUNT >= v_delete_size --this means we need to loop again
286 THEN
287 COMMIT;
288 v_delete_count := v_delete_count + sql%rowcount;
289 ELSE --this means all rows deleted, exit loop
290 COMMIT;
291 v_delete_count := v_delete_count + sql%rowcount;
292 EXIT;
293 END IF;
294
295 END LOOP;
296
297 v_message := 'Deleted ' || v_delete_count ||' rows from bix_dm_uwq_agent on ';
298 write_log(v_proc_name, v_message);
299
300 WHILE v_insert_date <= p_end_date
301 LOOP
302
303
304 --
305 --Determine the date of the Monday for the current week.
306 --An alternative is to use the "IW" date format, which is based on the ISO standard.
307 --The ISO standard is that a week is from Monday through Sunday. This is adopted here.
308 --
309
310 /*
311 SELECT NEXT_DAY(v_insert_date-7, 'MONDAY')
312 INTO v_week_start_date
313 from dual;
314
315 */
316
317 --
318 -- Using NEXT_DAY(v_insert_date-7, 'MONDAY') causes translation issues in other languages.
319
320 SELECT TRUNC(v_insert_date,'IW')
321 INTO v_week_start_date
322 from dual;
323
324
325 FOR rec_days IN c_days(v_insert_date)
326 LOOP
327
328
329 INSERT INTO BIX_DM_UWQ_AGENT_SUM
330 (
331 RESOURCE_ID, DAY, DAY_LOGIN, DAY_DURATION, DAY1_LOGIN,
332 DAY1_DURATION, DAY2_LOGIN, DAY2_DURATION, DAY3_LOGIN, DAY3_DURATION,
333 DAY4_LOGIN, DAY4_DURATION, DAY5_LOGIN, DAY5_DURATION, DAY6_LOGIN,
334 DAY6_DURATION, PRIOR_WEEK_LOGIN, PRIOR_WEEK_DURATION, CURRENT_WEEK_LOGIN,
335 CURRENT_WEEK_DURATION, PRIOR_MONTH_LOGIN, PRIOR_MONTH_DURATION,
336 CURRENT_MONTH_LOGIN, CURRENT_MONTH_DURATION,CREATED_BY, CREATION_DATE,
337 LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
338 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE
339 )
340 VALUES
341 (
342 rec_days.resource_id, rec_days.day,rec_days.day_login,rec_days.day_duration,rec_days.day1_login,
343 rec_days.day1_duration,rec_days.day2_login,rec_days.day2_duration,rec_days.day3_login,
344 rec_days.day3_duration,rec_days.day4_login,rec_days.day4_duration,rec_days.day5_login,
345 rec_days.day5_duration,rec_days.day6_login,rec_days.day6_duration,0,0,0,0,0,0,0,0,
346 v_user_id, sysdate, v_user_id, sysdate, v_user_id, v_request_id, v_program_appl_id, v_program_id, sysdate
347 );
348
349 v_insert_count := v_insert_count + sql%rowcount;
350 commit;
351
352 --
353 --Prior week: If a record exists for the last day of the previous week then use that week's information
354 --
355
356 BEGIN
357
358 --
359 --Select information for last week from summary table. If it doesnt exist, re-compute.
360 --
361 SELECT current_week_login, current_week_duration
362 INTO v_prior_week_login, v_prior_week_duration
363 FROM bix_dm_uwq_agent_sum
364 WHERE resource_id = rec_days.resource_id
365 AND day = v_week_start_date-1;
366
367 EXCEPTION
368 WHEN NO_DATA_FOUND
369 THEN
370 v_message := 'Prior weeks info not found: Need to re-compute for ' || v_insert_date ||' on ';
371 write_log(v_proc_name, v_message);
372
373 SELECT count(*)
374 INTO v_prior_week_login
375 FROM
376 (
377 SELECT DISTINCT resource_id, trunc(begin_date_time)
378 FROM ieu_sh_sessions
379 WHERE resource_id = rec_days.resource_id
380 AND begin_date_time BETWEEN v_week_start_date-7 AND v_week_start_date-1+.99998843
381 AND application_id = 696
382 );
383
384 SELECT round(sum(decode(end_date_time,NULL,sysdate,end_date_time)-begin_date_time)*24*3600)
385 INTO v_prior_week_duration
386 FROM ieu_sh_sessions
387 WHERE resource_id = rec_days.resource_id
388 AND begin_date_time BETWEEN v_week_start_date-7 AND v_week_start_date-1+.99998843
389 AND application_id = 696;
390
391 END;
392
393 --
394 --Current week: Take the current days data and add to the previous values of the current week.
395 --This cannot be done if it is the beginning day of the week.
396 --
397
398 IF v_insert_date <> v_week_start_date
399 THEN
400
401 BEGIN
402
403 SELECT (rec_days.day_login+current_week_login), (rec_days.day_duration+current_week_duration)
404 INTO v_current_week_login, v_current_week_duration
405 FROM bix_dm_uwq_agent_sum
406 WHERE resource_id = rec_days.resource_id
407 AND day = trunc(rec_days.day)-1;
408
409 EXCEPTION
410 WHEN NO_DATA_FOUND
411 THEN
412 --
413 --This means current week information was not found in summary table.
414 --Need to re-compute current week's information.
415 --
416 v_message := 'Current weeks info not found: Need to re-compute for ' || v_insert_date ||' on ';
420 INTO v_current_week_login
417 write_log(v_proc_name, v_message);
418
419 SELECT count(*)
421 FROM
422 (
423 SELECT DISTINCT resource_id, trunc(begin_date_time)
424 FROM ieu_sh_sessions
425 WHERE resource_id = rec_days.resource_id
426 AND begin_date_time BETWEEN v_week_start_date AND v_insert_date+.99998843
427 AND application_id = 696
428 );
429
430 SELECT round(sum(decode(end_date_time,NULL,sysdate,end_date_time)-begin_date_time)*24*3600)
431 INTO v_current_week_duration
432 FROM ieu_sh_sessions
433 WHERE resource_id = rec_days.resource_id
434 AND begin_date_time BETWEEN v_week_start_date AND v_insert_date+.99998843
435 AND application_id = 696
436 ;
437
438 END;
439
440 ELSIF v_insert_date = v_week_start_date
441 THEN
442
443 --
444 --First day of week, so just use information from cursor as we dont need to go back
445 --to previous days of the week.
446 --
447 v_current_week_login := rec_days.day_login;
448 v_current_week_duration := rec_days.day_duration;
449
450 END IF;
451
452 --
453 --Calculate prior month information
454 --
455 BEGIN
456
457 --
458 --If data exists for the last day of the previous month in the summary table, use that
459 --
460 SELECT current_month_login, current_month_duration
461 INTO v_prior_month_login, v_prior_month_duration
462 FROM bix_dm_uwq_agent_sum
463 WHERE resource_id = rec_days.resource_id
464 AND day = last_day(add_months(v_insert_date,-1));
465
466 EXCEPTION
467 WHEN NO_DATA_FOUND
468 THEN
469
470 --
471 --This means last month information did not exist in the summary table.
472 --Re-compute.
473 --
474 v_message := 'Prior month info not found: Need to re-compute for ' || v_insert_date ||' on ';
475 write_log(v_proc_name, v_message);
476
477 SELECT count(*)
478 INTO v_prior_month_login
479 FROM
480 (
481 SELECT DISTINCT resource_id, trunc(begin_date_time)
482 FROM ieu_sh_sessions
483 WHERE resource_id = rec_days.resource_id
484 AND to_char(begin_date_time,'MM/YYYY') = to_char(add_months(v_insert_date,-1), 'MM/YYYY')
485 AND application_id = 696
486 );
487
488 SELECT round(sum(decode(end_date_time,NULL,sysdate,end_date_time)-begin_date_time)*24*3600)
489 INTO v_prior_month_duration
490 FROM ieu_sh_sessions
491 WHERE resource_id = rec_days.resource_id
492 AND to_char(begin_date_time,'MM/YYYY') = to_char(add_months(v_insert_date,-1), 'MM/YYYY')
493 AND application_id = 696;
494
495 END;
496 --
497 --Current month to date: Check if it is the first day of the month. If it is not, then
498 --add current days data to previous days information for the month. If it is the
499 --first day of the month, then assign the values directly.
500 --
501
502 IF v_insert_date <> last_day(add_months(v_insert_date,-1))+1
503 THEN
504
505 BEGIN
506
507 SELECT rec_days.day_login+current_month_login, rec_days.day_duration+current_month_duration
508 INTO v_current_month_login, v_current_month_duration
509 FROM bix_dm_uwq_agent_sum
510 WHERE resource_id = rec_days.resource_id
511 AND day = v_insert_date-1;
512
513 EXCEPTION
514 WHEN NO_DATA_FOUND
515 THEN
516
517 --
518 --Current month info was not found in the summary table.
519 --Re-compute.
520 --
521 v_message := 'Current month info not found: Need to re-compute for ' || v_insert_date ||' on ';
522 write_log(v_proc_name, v_message);
523
524 SELECT count(*)
525 INTO v_current_month_login
526 FROM
527 (
528 SELECT DISTINCT resource_id, trunc(begin_date_time)
529 FROM ieu_sh_sessions
530 WHERE resource_id = rec_days.resource_id
531 AND to_char(begin_date_time,'MM/YYYY') = to_char(v_insert_date, 'MM/YYYY')
532 AND begin_date_time <= v_insert_date+.99998843
533 AND application_id = 696
534 )
535 ;
536
537 SELECT round(sum(decode(end_date_time,NULL,sysdate,end_date_time)-begin_date_time)*24*3600)
538 INTO v_current_month_duration
539 FROM ieu_sh_sessions
540 WHERE resource_id = rec_days.resource_id
541 AND to_char(begin_date_time,'MM/YYYY') = to_char(v_insert_date, 'MM/YYYY')
542 AND begin_date_time <= v_insert_date+.99998843
543 AND application_id = 696;
544
545 END;
546
547 ELSIF v_insert_date = last_day(add_months(v_insert_date,-1))+1
548 THEN
549 --
550 --This means it is the first day of the current month
551 --
552 v_current_month_login := rec_days.day_login;
553 v_current_month_duration := rec_days.day_duration;
554
555 END IF;
556
557 --
558 --Update week and month information
559 --
560
561 UPDATE bix_dm_uwq_agent_sum
562 SET prior_week_login = v_prior_week_login,
563 prior_week_duration = v_prior_week_duration,
564 current_week_login = v_current_week_login,
568 current_month_login = v_current_month_login,
565 current_week_duration = v_current_week_duration,
566 prior_month_login = v_prior_month_login,
567 prior_month_duration = v_prior_month_duration,
569 current_month_duration = v_current_month_duration,
570 LAST_UPDATED_BY = v_user_id,
571 LAST_UPDATE_DATE = sysdate,
572 LAST_UPDATE_LOGIN = v_user_id
573 WHERE resource_id = rec_days.resource_id
574 AND day = rec_days.day;
575
576 v_message := 'Completed update of agents prior,current info for ' || v_insert_date ||' on ';
577 write_log(v_proc_name, v_message);
578
579
580 END LOOP; -- for cursor
581
582
583 v_message := 'Completed loop for insert_date ' || v_insert_date ||' on ';
584 write_log(v_proc_name, v_message);
585
586 v_insert_date := v_insert_date + 1;
587
588 COMMIT; -- commit is performed after rows are inserted for all resources for 1 day
589
590 END LOOP; -- for date
591
592 v_run_end_date := sysdate;
593 v_status := 'SUCCESS';
594 v_message := 'Successfully populated bix_dm_uwq_agent_sum';
595 write_log(v_proc_name, v_message);
596 insert_log_table;
597
598 COMMIT; --commit after all rows are inserted in bix_dm_uwq_agent_sum
599
600 EXCEPTION
601 WHEN OTHERS
602 THEN
603 --
604 --Exception occurred.
605 --Raise exception and pass ccontrol to the calling procedure where we will perform the deletes.
606 --
607 v_status := 'FAILURE';
608 v_message := 'Failed while populating bix_dm_uwq_agent_sum '|| sqlerrm;
609 v_run_end_date := sysdate;
610 write_log(v_proc_name, v_message);
611 RAISE;
612
613 END populate_agents;
614
615 PROCEDURE populate_groups
616 (
617 p_start_date IN DATE,
618 p_end_date IN DATE
619 )
620
621 --
622 --This procedure populates the BIX_DM_UWQ_GROUP_SUM table.
623 --There is no need to go after the OLTP tables. Instead, the
624 --calculations are based off the BIX_DM_UWQ_AGENT_SUM table.
625 --
626 IS
627
628 v_insert_date DATE;
629 v_day_login NUMBER;
630 v_day_duration NUMBER;
631 v_day1_login NUMBER;
632 v_day1_duration NUMBER;
633 v_day2_login NUMBER;
634 v_day2_duration NUMBER;
635 v_day3_login NUMBER;
636 v_day3_duration NUMBER;
637 v_day4_login NUMBER;
638 v_day4_duration NUMBER;
639 v_day5_login NUMBER;
640 v_day5_duration NUMBER;
641 v_day6_login NUMBER;
642 v_day6_duration NUMBER;
643 v_prior_week_login NUMBER;
644 v_prior_week_duration NUMBER;
645 v_current_week_login NUMBER;
646 v_current_week_duration NUMBER;
647 v_prior_month_login NUMBER;
648 v_prior_month_duration NUMBER;
649 v_current_month_login NUMBER;
650 v_current_month_duration NUMBER;
651
652 --
653 --Cursor of all the groups that we need to capture in the summary table
654 --
655
656 CURSOR c_all_groups
657 IS
658 select DISTINCT denorm.parent_group_id group_id
659 from jtf_rs_group_members mem, jtf_rs_groups_denorm denorm,
660 bix_dm_uwq_agent_sum summ
661 where mem.group_id = denorm.group_id
662 and summ.resource_id = mem.resource_id;
663
664 BEGIN
665
666 v_insert_count := 0;
667 v_delete_count := 0;
668 v_proc_name := 'POPULATE_GROUPS';
669 v_table_name := 'BIX_DM_UWQ_GROUP_SUM';
670 v_run_start_date := sysdate;
671
672 --
673 --Delete information for the date range and re-compute.
674 --
675 LOOP
676
677 DELETE FROM bix_dm_uwq_group_sum
678 WHERE day BETWEEN trunc(p_start_date) AND trunc(p_end_date)
679 AND rownum <= v_delete_size;
680
681 IF SQL%ROWCOUNT >= v_delete_size --this means we need to loop again
682 THEN
683 COMMIT;
684 v_delete_count := v_delete_count + sql%rowcount;
685 ELSE --this means all rows deleted, exit loop
686 COMMIT;
687 v_delete_count := v_delete_count + sql%rowcount;
688 EXIT;
689 END IF;
690
691 END LOOP;
692
693 v_delete_count := sql%rowcount;
694
695 FOR rec_groups IN c_all_groups
696 LOOP
697
698 v_insert_date := trunc(p_start_date);
699
700 WHILE v_insert_date <= p_end_date
701 LOOP
702
703 --
704 --Insert zero rows
705 --
706 INSERT INTO bix_dm_uwq_group_sum
707 (group_id, day,
708 DAY_LOGIN, DAY_DURATION, DAY1_LOGIN,
709 DAY1_DURATION, DAY2_LOGIN, DAY2_DURATION, DAY3_LOGIN, DAY3_DURATION,
710 DAY4_LOGIN, DAY4_DURATION, DAY5_LOGIN, DAY5_DURATION, DAY6_LOGIN,
711 DAY6_DURATION, PRIOR_WEEK_LOGIN, PRIOR_WEEK_DURATION, CURRENT_WEEK_LOGIN,
712 CURRENT_WEEK_DURATION, PRIOR_MONTH_LOGIN, PRIOR_MONTH_DURATION,
713 CURRENT_MONTH_LOGIN, CURRENT_MONTH_DURATION,
717 VALUES (rec_groups.group_id, trunc(v_insert_date),
714 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
715 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE
716 )
718 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
719 v_user_id, sysdate, v_user_id, sysdate, v_user_id,
720 v_request_id, v_program_appl_id, v_program_id, sysdate
721 );
722
723 COMMIT;
724
725 v_insert_count := v_insert_count + sql%rowcount;
726
727 --
728 --Compute information using BIX_DM_UWQ_AGENT_SUM and store in variables.
729 --
730 SELECT sum(day_login), sum(day_duration), sum(day1_login), sum(day1_duration),
731 sum(day2_login), sum(day2_duration), sum(day3_login), sum(day3_duration),
732 sum(day4_login), sum(day4_duration), sum(day5_login), sum(day5_duration),
733 sum(day6_login), sum(day6_duration),
734 sum(prior_week_login), sum(prior_week_duration),
735 sum(current_week_login), sum(current_week_duration),
736 sum(prior_month_login), sum(prior_month_duration),
737 sum(current_month_login), sum(current_month_duration)
738 INTO v_day_login, v_day_duration, v_day1_login, v_day1_duration,
739 v_day2_login, v_day2_duration, v_day3_login, v_day3_duration,
740 v_day4_login, v_day4_duration, v_day5_login, v_day5_duration,
741 v_day6_login, v_day6_duration,
742 v_prior_week_login, v_prior_week_duration,
743 v_current_week_login, v_current_week_duration,
744 v_prior_month_login, v_prior_month_duration,
745 v_current_month_login, v_current_month_duration
746 FROM bix_dm_uwq_agent_sum agent
747 WHERE agent.resource_id IN (
748 select grp.resource_id
749 from jtf_rs_groups_denorm denorm, jtf_rs_group_members grp
750 where denorm.parent_group_id = rec_groups.group_id
751 and denorm.group_id = grp.group_id
752 )
753 AND agent.day = v_insert_date;
754
755 --
756 --Update BIX_DM_UWQ_GROUP_SUM table using the above values.
757 --
758 UPDATE bix_dm_uwq_group_sum
759 SET day_login = v_day_login,
760 day_duration = v_day_duration,
761 day1_login = v_day1_login,
762 day1_duration = v_day1_duration,
763 day2_login = v_day2_login,
764 day2_duration = v_day2_duration,
765 day3_login = v_day3_login,
766 day3_duration = v_day3_duration,
767 day4_login = v_day4_login,
768 day4_duration = v_day4_duration,
769 day5_login = v_day5_login,
770 day5_duration = v_day5_duration,
771 day6_login = v_day6_login,
772 day6_duration = v_day6_duration,
773 prior_week_login = v_prior_week_login,
774 prior_week_duration = v_prior_week_duration,
775 current_week_login = v_current_week_login,
776 current_week_duration = v_current_week_duration,
777 prior_month_login = v_prior_month_login,
778 prior_month_duration = v_prior_month_duration,
779 current_month_login = v_current_month_login,
780 current_month_duration = v_current_month_duration,
781 LAST_UPDATED_BY = v_user_id,
782 LAST_UPDATE_DATE = sysdate,
783 LAST_UPDATE_LOGIN = v_user_id
784 WHERE group_id = rec_groups.group_id
785 AND day = v_insert_date;
786
787 --
788 --Increment the date and loop through again
789 --
790 v_insert_date := v_insert_date + 1;
791
792 COMMIT;
793
794 END LOOP; -- date loop
795
796 END LOOP; --groups cursor loop
797
798 v_status := 'SUCCESS';
799 v_message := 'Successfully populated bix_dm_uwq_group_sum';
800 v_run_end_date := sysdate;
801 write_log(v_proc_name, v_message);
802 insert_log_table;
803 COMMIT;
804
805 EXCEPTION
806 WHEN OTHERS
807 THEN
808
809 --
810 --Exception occurred.
811 --Raise the exception to the calling procedure where we will perform the deletes.
812 --
813
814 v_status := 'FAILURE';
815 v_message := 'Failed while populating bix_dm_uwq_agent_sum '|| sqlerrm;
816 v_run_end_date := sysdate;
817 write_log(v_proc_name, v_message);
818 RAISE;
819
820 END populate_groups;
821
822 PROCEDURE write_log (p_proc_name IN VARCHAR2, p_message IN VARCHAR2)
823 --
824 --This procedure is used to write to the log file used by the concurrent program.
825 --
826 IS
827 BEGIN
828
829 IF (g_debug_flag = 'Y') THEN
830 FND_FILE.PUT_LINE(fnd_file.log,'BIX_UWQ_SUMMARY_PKG.' || v_proc_name || ':' || v_message || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
831 END IF;
832
833 END write_log;
834
835 PROCEDURE insert_log_table
836 --
837 --This procedure performs inserts into the BIX_DM_COLLECT_LOG table
838 --so that statistics about the collection run can be stored.
839 --
840 IS
841 BEGIN
842
843 v_proc_name := 'insert_log_table';
844
845 INSERT INTO BIX_DM_COLLECT_LOG
846 (
847 COLLECT_ID,
848 COLLECT_CONCUR_ID,
849 OBJECT_NAME,
850 OBJECT_TYPE,
851 RUN_START_DATE,
852 RUN_END_DATE,
853 COLLECT_START_DATE,
854 COLLECT_END_DATE,
855 COLLECT_STATUS,
856 COLLECT_EXCEP_MESG,
857 ROWS_DELETED,
858 ROWS_INSERTED,
859 LAST_UPDATE_DATE,
860 LAST_UPDATED_BY,
861 CREATION_DATE,
862 CREATED_BY,
863 LAST_UPDATE_LOGIN,
864 REQUEST_ID,
865 PROGRAM_APPLICATION_ID,
866 PROGRAM_ID,
867 PROGRAM_UPDATE_DATE
868 )
869 VALUES
870 (
871 BIX_DM_COLLECT_LOG_S.NEXTVAL,
872 NULL,
873 v_table_name,
874 'TABLE',
875 v_run_start_date,
876 v_run_end_date,
877 v_collect_start_date,
878 v_collect_end_date,
879 v_status,
880 v_message,
881 v_delete_count,
882 v_insert_count,
883 sysdate,
884 v_user_id,
885 sysdate,
886 v_user_id,
887 v_user_id,
888 v_request_id,
889 v_program_appl_id,
890 v_program_id,
891 sysdate
892 );
893
894 EXCEPTION
895 WHEN OTHERS
896 THEN
897 v_message := 'Error inserting log table on ';
898 write_log(v_proc_name, v_message);
899 RAISE;
900
901 END insert_log_table;
902
903 END bix_uwq_summary_pkg;
[Home] [Help]