[Home] [Help]
PACKAGE BODY: APPS.BIX_DM_SESSBYCAMP_PKG
Source
1 PACKAGE BODY BIX_DM_SESSBYCAMP_PKG AS
2 /*$Header: bixxsecb.pls 115.12 2003/08/21 23:47:18 anasubra 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_SESSBYCAMP_PKG' ;
25 g_debug_flag VARCHAR2(1) := 'N';
26
27
28 PROCEDURE write_log(p_pkg_name in VARCHAR2,
29 p_proc_name in VARCHAR2,
30 p_msg in VARCHAR2 )
31 IS
32 l_proc_name VARCHAR2(20) := 'WRITE_LOG';
33 BEGIN
34 IF (g_debug_flag = 'Y') THEN
35 fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
36 ' : ' || p_proc_name || ' : '|| p_msg);
37 END IF;
38 EXCEPTION
39 WHEN OTHERS THEN
40 RAISE;
41 END write_log;
42
43 PROCEDURE insert_log_table
44 IS
45 l_proc_name VARCHAR2(20) := 'INSERT_LOG_TABLE';
46 BEGIN
47
48 /* Insert status into log table */
49 INSERT INTO BIX_DM_COLLECT_LOG
50 (
51 collect_id,
52 collect_concur_id,
53 object_name,
54 object_type,
55 run_start_date,
56 run_end_date,
57 collect_start_date,
58 collect_end_date,
59 collect_status,
60 collect_excep_mesg,
61 rows_deleted,
62 rows_inserted,
63 rows_updated,
64 last_update_date,
65 last_updated_by,
66 creation_date,
67 created_by,
68 last_update_login,
69 request_id,
70 program_application_id,
71 program_id,
72 program_update_date
73 )
74 VALUES
75 (
76 BIX_DM_COLLECT_LOG_S.NEXTVAL,
77 null,
78 g_table_name,
79 'TABLE',
80 g_run_start_date,
81 g_run_end_date,
82 g_collect_start_date,
83 g_collect_end_date,
84 g_status,
85 g_error_msg,
86 g_delete_count,
87 g_insert_count,
88 g_update_count,
89 sysdate,
90 g_user_id,
91 sysdate,
92 g_user_id,
93 g_login_id,
94 g_request_id,
95 g_program_appl_id,
96 g_program_id,
97 sysdate
98 );
99 EXCEPTION
100 WHEN OTHERS THEN
101 RAISE;
102 END insert_log_table;
103
104
105 PROCEDURE delete_in_chunks(p_table_name IN VARCHAR2,
106 p_type IN NUMBER,
107 p_rows_deleted OUT nocopy NUMBER)
108 IS
109 l_delete_statement VARCHAR2(4000);
110 l_where_clause VARCHAR2(1000);
111 l_rows_deleted NUMBER;
112 l_proc_name VARCHAR2(20) := 'DELETE_IN_CHUNKS';
113
114 e_invalid_condition EXCEPTION;
115
116 BEGIN
117
118 --IF p_type = 1
119 --THEN
120 --l_where_clause := ' period_start_date_time between to_date(''' ||
121 --to_char(g_rounded_collect_start_date, 'YYYY/MM/DDHH24:MI:SS') ||
122 --''', ''YYYY/MM/DDHH24:MI:SS'') and to_date(''' ||
123 --to_char(g_rounded_collect_end_date, 'YYYY/MM/DDHH24:MI:SS') ||
124 --''', ''YYYY/MM/DDHH24:MI:SS'')' ;
125 --ELSE
126 --l_where_clause := ' request_id = ' || g_request_id ;
127 --END IF;
128
129 --l_delete_statement := 'DELETE FROM '|| p_table_name ||
130 --' WHERE ' || l_where_clause ||
131 --' AND rownum <= ' || g_commit_chunk_size ;
132 l_rows_deleted := 0;
133
134 -- dbms_output.put_line('SQL Statement: '||l_delete_statement);
135
136 LOOP
137 --EXECUTE IMMEDIATE l_delete_statement;
138
139 IF(p_type = 1 AND p_table_name = 'BIX_DM_AGENT_SESSBYCAMP_SUM') THEN
140 DELETE FROM BIX_DM_AGENT_SESSBYCAMP_SUM
141 WHERE period_start_date_time between g_rounded_collect_start_date
142 AND g_rounded_collect_end_date
143 AND rownum <= g_commit_chunk_size;
144 ELSIF(p_type = 1 AND p_table_name = 'BIX_DM_GROUP_SESSBYCAMP_SUM') THEN
145 DELETE FROM BIX_DM_GROUP_SESSBYCAMP_SUM
146 WHERE period_start_date_time between g_rounded_collect_start_date
147 AND g_rounded_collect_end_date
148 AND rownum <= g_commit_chunk_size;
149 ELSIF(p_type = 2 AND p_table_name = 'BIX_DM_AGENT_SESSBYCAMP_SUM') THEN
150 DELETE FROM BIX_DM_AGENT_SESSBYCAMP_SUM
151 WHERE request_id = g_request_id
152 AND rownum <= g_commit_chunk_size;
153 ELSIF(p_type = 2 AND p_table_name = 'BIX_DM_GROUP_SESSBYCAMP_SUM') THEN
154 DELETE FROM BIX_DM_GROUP_SESSBYCAMP_SUM
155 WHERE request_id = g_request_id
156 AND rownum <= g_commit_chunk_size;
157 ELSE
158 RAISE e_invalid_condition;
159 END IF;
160
161 l_rows_deleted := l_rows_deleted + SQL%ROWCOUNT;
162
163 IF SQL%ROWCOUNT < g_commit_chunk_size
164 THEN
165 COMMIT;
166 EXIT;
167 ELSE
168 COMMIT;
169 END IF;
170 END LOOP;
171 g_message := 'Deleted ' || l_rows_deleted || ' rows from table '
172 || p_table_name ;
173 write_log(g_pkg_name, l_proc_name, g_message );
174 p_rows_deleted := l_rows_deleted;
175
176 EXCEPTION
177 WHEN e_invalid_condition then
178 g_proc_name := 'BIX_DM_SESSBYCAMP_PKG.DELETE_IN_CHUNKS';
179 g_error_msg := 'Invalid IF condition in delete ';
180 raise;
181 WHEN OTHERS THEN
182 RAISE;
183 END delete_in_chunks;
184
185
186 PROCEDURE INSERT_WORKTIME_ROW(p_resource_id in NUMBER,
187 p_server_group_id in NUMBER,
188 p_campaign_id in NUMBER,
189 p_campaign_schedule_id in NUMBER,
190 p_start_date in DATE,
191 p_secs in NUMBER,
192 p_ddl_type OUT nocopy VARCHAR2)
193 IS
194 l_proc_name VARCHAR2(20) := 'INSERT_WORKTIME_ROW';
195 l_exists VARCHAR2(1) := 'N' ;
196 BEGIN
197 g_proc_name := l_proc_name;
198
199 BEGIN
200 SELECT 'Y'
201 INTO l_exists
202 FROM BIX_DM_AGENT_SESSBYCAMP_SUM
203 WHERE resource_id = p_resource_id
204 AND period_start_date_time = p_start_date
205 AND server_group_id = p_server_group_id
206 AND campaign_id = p_campaign_id
207 AND campaign_schedule_id = p_campaign_schedule_id;
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 l_exists := 'N';
211 WHEN OTHERS THEN
212 raise;
213 END;
214
215 IF l_exists = 'N'
216 THEN
217 INSERT INTO BIX_DM_AGENT_SESSBYCAMP_SUM
218 ( agent_sessbycamp_sum_id
219 ,resource_id
220 ,server_group_id
221 ,campaign_id
222 ,campaign_schedule_id
223 ,period_start_date
224 ,period_start_time
225 ,period_start_date_time
226 ,last_update_date
227 ,last_updated_by
228 ,creation_date
229 ,created_by
230 ,last_update_login
231 ,work_time
232 ,available_time
233 ,request_id
234 ,program_application_id
235 ,program_id
236 ,program_update_date
237 ) VALUES
238 ( BIX_DM_AGENT_SESSBYCAMP_SUM_S.NEXTVAL
239 ,p_resource_id
240 ,p_server_group_id
241 ,p_campaign_id
242 ,p_campaign_schedule_id
243 ,TRUNC(p_start_date)
244 ,TO_CHAR(p_start_date,'HH24:MI')
245 ,p_start_date
246 ,SYSDATE
247 ,g_user_id
248 ,SYSDATE
249 ,g_user_id
250 ,g_user_id
251 ,p_secs
252 ,0
253 ,g_request_id
254 ,g_program_appl_id
255 ,g_program_id
256 ,SYSDATE );
257 p_ddl_type := 'I' ;
258 ELSE
259 UPDATE BIX_DM_AGENT_SESSBYCAMP_SUM
260 SET work_time = work_time + p_secs,
261 last_update_date = SYSDATE,
262 last_updated_by = g_user_id,
263 program_update_date = SYSDATE
264 WHERE resource_id = p_resource_id
265 AND period_start_date_time = p_start_date
266 AND server_group_id = p_server_group_id
267 AND campaign_schedule_id = p_campaign_schedule_id;
268
269 p_ddl_type := 'U' ;
270 END IF;
271 EXCEPTION
272 WHEN OTHERS THEN
273 RAISE;
274 END INSERT_WORKTIME_ROW;
275
276 PROCEDURE collect_agent_work_time
277 IS
278 l_end_Date DATE;
279 l_begin_date DATE;
280 l_period_start DATE;
281 l_work_start DATE;
282 l_work_end DATE;
283 l_secs NUMBER;
284 l_row_count NUMBER :=0;
285 l_row_counter NUMBER := 0;
286 l_agent_cost NUMBER := 0;
287 l_temp VARCHAR2(100);
288 l_ddl_type VARCHAR2(1);
289 l_proc_name VARCHAR2(25) := 'COLLECT_AGENT_WORK_TIME';
290
291 CURSOR get_work_time IS
292 SELECT sess.resource_id resource_id,
293 campsch.campaign_id campaign_id,
294 campsch.schedule_id campaign_schedule_id,
295 res.server_group_id server_group_id,
296 act.begin_date_time begin_date_time,
297 act.end_date_time end_date_time
298 FROM ieu_sh_sessions sess,
299 ieu_sh_activities act,
300 --ieu_uwq_media_types_tl med,
301 jtf_rs_resource_extns res,
302 ams_campaign_schedules_b campsch
303 WHERE sess.session_id = act.session_id
304 AND sess.resource_id = res.resource_id
305 AND sess.application_id = 696
306 AND act.activity_type_code = 'MEDIA_CYCLE'
307 AND act.category_type = 'CSCH' --campaign schedule
308 --AND act.category_value = to_char(campsch.schedule_id)
309 AND decode(act.category_type,'CSCH',to_number(nvl(act.category_value,-1)),-1) = campsch.schedule_id
310 --AND med.media_type_id = 10009
311 --AND med.media_type_id = act.media_type_id
312 AND act.media_type_id = 10009 -- outbound calls
313 AND sess.begin_date_time <= g_rounded_collect_end_date
314 AND (sess.end_date_time >= g_rounded_collect_start_date
315 OR sess.end_date_time is NULL ) ;
316 BEGIN
317
318 g_proc_name := l_proc_name;
319 g_message := 'Start collecting work time into BIX_DM_AGENT_SESSBYCAMP_SUM';
320 write_log( g_pkg_name, l_proc_name, g_message );
321
322 FOR work_time IN get_work_time
323 LOOP
324 /* If the session started before the time window that the concurrent
325 program is running mark the begin date as collection start date
326 time of the time window */
327 IF ( work_time.begin_date_time < g_rounded_collect_start_date )
328 THEN
329 l_begin_date := g_rounded_collect_start_date;
330 ELSE
331 l_begin_date := work_time.begin_date_time;
332 END IF;
333
334 /* If the activity ended after the collection end date , mark the
335 end date as collection end date */
336 IF( work_time.end_date_time IS NULL OR
337 work_time.end_date_time > g_rounded_collect_end_date)
338 THEN
339 l_end_date := g_rounded_collect_end_date + (1/(24*3600));
340 ELSE
341 l_end_date := work_time.end_date_time;
342 END IF;
343
344 /* get the nearest lowest time bucket for the begin date
345 to populate appropriate bucket */
346 SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
347 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0')||
348 DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
349 0,'00',1,'30',-1,'00')
350 INTO l_temp FROM DUAL;
351
352 l_period_start := TO_DATE(l_temp,'YYYY/MM/DDHH24:MI');
353 l_row_counter := 0;
354 WHILE ( l_period_start < l_end_date )
355 LOOP
356 IF (l_row_counter = 0 )
357 THEN
358 l_work_start := l_begin_date;
359 ELSE
360 l_work_start := l_period_start;
361 END IF;
362
363 l_work_end := l_period_start + 1/48;
364 IF ( l_work_end > l_end_date )
365 THEN
366 l_work_end := l_end_date ;
367 END IF;
368
369 l_secs := round((l_work_end - l_work_start) * 24 * 3600);
370
371 INSERT_WORKTIME_ROW(work_time.resource_id,
372 work_time.server_group_id,
373 work_time.campaign_id,
374 work_time.campaign_schedule_id,
375 l_period_start,
376 l_secs,
377 l_ddl_type);
378
379 IF l_ddl_type = 'I'
380 THEN
381 l_row_count := l_row_count + 1;
382 END IF;
386 IF(MOD(l_row_count,g_commit_chunk_size)=0)
383 l_row_counter := l_row_counter + 1;
384 l_period_start := l_period_start + 1/48;
385
387 THEN
388 COMMIT;
389 END IF;
390 END LOOP; -- end of WHILE loop
391
392 END LOOP; -- End of FOR loop.
393
394 COMMIT;
395 g_message := 'Finished collecting agent work time : Inserted ' ||
396 l_row_count || ' rows into BIM_DM_AGENT_SESSBYCAMP_SUM' ;
397 write_log(g_pkg_name, l_proc_name, g_message );
398
399 g_insert_count := l_row_count;
400
401 EXCEPTION
402 WHEN OTHERS THEN
403 RAISE;
404
405 END collect_agent_work_time;
406
407
408 PROCEDURE collect_agent_avail_time
409 AS
410 l_end_Date DATE;
411 l_begin_date DATE;
412 l_period_start DATE;
413 l_work_start DATE;
414 l_work_end DATE;
415 l_secs number;
416 l_row_count NUMBER := 0;
417 l_row_counter NUMBER := 0;
418 l_temp VARCHAR2(100);
419 l_proc_name VARCHAR2(25) := 'COLLECT_AGENT_AVAIL_TIME';
420
421 CURSOR get_avail_time IS
422 SELECT sess.resource_id resource_id,
423 res.server_group_id server_group_id,
424 campsch.campaign_id campaign_id,
425 campsch.schedule_id campaign_schedule_id,
426 act1.begin_date_time begin_date_time,
427 act1.deliver_date_time end_date_time
428 FROM ieu_sh_sessions sess,
429 ieu_sh_activities act1,
430 ieu_sh_activities act2,
431 --ieu_uwq_media_types_tl med,
432 jtf_rs_resource_extns res,
433 ams_campaign_schedules_b campsch
434 WHERE act1.begin_date_time <= g_rounded_collect_end_date
435 AND (act1.deliver_date_time >= g_rounded_collect_start_date
436 OR (act1.deliver_date_time is NULL AND act1.end_date_time IS NULL)
437 )
438 AND sess.application_id = 696
439 AND sess.session_id = act1.session_id
440 AND sess.resource_id = res.resource_id
441 AND act1.activity_type_code = 'MEDIA'
442 AND act1.parent_cycle_id = act2.activity_id
443 AND act2.activity_type_code = 'MEDIA_CYCLE'
444 AND act2.category_type = 'CSCH' --campaign schedule
445 --AND act2.category_value = to_char(campsch.schedule_id)
446 AND decode(act2.category_type,'CSCH',to_number(nvl(act2.category_value,-1)),-1) = campsch.schedule_id
447 --AND med.media_type_id = 10009 --outbound calls
448 --AND med.media_type_id = act2.media_type_id
449 AND act2.media_type_id = 10009 --outbound calls
450 UNION ALL
451 SELECT sess.resource_id resource_id,
452 res.server_group_id server_group_id,
453 campsch.campaign_id campaign_id,
454 campsch.schedule_id campaign_schedule_id,
455 act1.begin_date_time begin_date_time,
456 act1.end_date_time end_date_time
457 FROM ieu_sh_sessions sess,
458 ieu_sh_activities act1,
459 ieu_sh_activities act2,
460 jtf_rs_resource_extns res,
461 ams_campaign_schedules_b campsch
462 WHERE act1.begin_date_time <= g_rounded_collect_end_date
463 AND act1.end_date_time >= g_rounded_collect_start_date
464 AND act1.deliver_date_time IS NULL
465 AND sess.application_id = 696
466 AND sess.session_id = act1.session_id
467 AND sess.resource_id = res.resource_id
468 AND act1.activity_type_code = 'MEDIA'
469 AND act1.parent_cycle_id = act2.activity_id
470 AND act2.activity_type_code = 'MEDIA_CYCLE'
471 AND act2.category_type = 'CSCH' --campaign schedule
472 --AND act2.category_value = to_char(campsch.schedule_id)
473 AND decode(act2.category_type,'CSCH',to_number(nvl(act2.category_value,-1)),-1) = campsch.schedule_id
474 AND act2.media_type_id = 10009 --outbound calls
475 ;
476
477 BEGIN
478
479 g_proc_name := l_proc_name ;
480 g_message := 'Start collecting agent available time';
481 write_log( g_pkg_name, l_proc_name, g_message);
482
483 FOR avail_time IN get_avail_time
484 LOOP
485
486 /* If the Resource has been avilable before collection start date time
487 mark the begin date as collection start date time of the time window */
488 IF ( avail_time.begin_date_time < g_rounded_collect_start_date ) THEN
489 l_begin_date := g_rounded_collect_start_date;
490 ELSE
491 l_begin_date := avail_time.begin_date_time;
492 END IF;
493
494 /* If the Resource is continue to be avilable after the collection
495 end date, mark the end date as collection end date */
496 IF( avail_time.end_date_time IS NULL OR
497 avail_time.end_date_time > g_rounded_collect_end_date) THEN
498 l_end_date := g_rounded_collect_end_date + (1/(24*3600));
499 ELSE
500 l_end_date := avail_time.end_date_time;
501 END IF;
502
503 SELECT TO_CHAR(l_begin_date,'YYYY/MM/DD')||
504 LPAD(TO_CHAR(l_begin_date,'HH24:'),3,'0') ||
505 DECODE(SIGN(TO_NUMBER(TO_CHAR(l_begin_date,'MI'))-29),
506 0,'00',1,'30',-1,'00')
507 INTO l_temp FROM DUAL;
508
509 l_period_start := TO_DATE(l_temp,'YYYY/MM/DDHH24:MI');
510 l_row_counter := 0;
511 WHILE ( l_period_start < l_end_date )
512 LOOP
516 ELSE
513 IF (l_row_counter = 0 )
514 THEN
515 l_work_start := l_begin_date;
517 l_work_start := l_period_start;
518 END IF;
519
520 l_work_end := l_period_start + 1/48;
521 IF ( l_work_end > l_end_date )
522 THEN
523 l_work_end := l_end_date ;
524 END IF;
525 l_secs := round((l_work_end - l_work_start) * 24 * 3600);
526 IF (l_secs > 0 )
527 THEN
528
529 UPDATE BIX_DM_AGENT_SESSBYCAMP_SUM
530 SET available_time = nvl(available_time,0)+l_secs,
531 last_update_date = SYSDATE,
532 last_updated_by = g_user_id,
533 program_update_date = SYSDATE
534 WHERE resource_id = avail_time.resource_id
535 AND server_group_id = avail_time.server_group_id
536 AND campaign_id = avail_time.campaign_id
537 AND campaign_schedule_id = avail_time.campaign_schedule_id
538 AND period_start_date_time = l_period_start;
539
540 l_row_count := l_row_count + 1;
541 END IF;
542
543 l_row_counter := l_row_counter + 1;
544 l_period_start := l_period_start + 1/48;
545
546 IF(MOD(l_row_count,g_commit_chunk_size)=0) THEN
547 COMMIT;
548 END IF;
549
550 END LOOP; -- end of WHILE loop
551 END LOOP; -- End of FOR loop.
552
553 COMMIT;
554 g_update_count := l_row_count;
555 g_message := 'Finished collecting agent available time : Updated ' ||
556 l_row_count || ' rows in BIM_DM_AGENT_SESSBYCAMP_SUM' ;
557 write_log(g_pkg_name, l_proc_name, g_message );
558
559 EXCEPTION
560 WHEN OTHERS THEN
561 RAISE;
562 END collect_agent_avail_time;
563
564
565 PROCEDURE populate_groups
566 IS
567
568 l_row_count NUMBER := 0;
569 l_proc_name VARCHAR2(20) := 'POPULATE_GROUPS';
570
571 CURSOR group_agents IS
572 SELECT grp_denorm.parent_group_id group_id,
573 agt_sum.server_group_id server_group_id,
574 agt_sum.campaign_id campaign_id,
575 agt_sum.campaign_schedule_id campaign_schedule_id,
576 agt_sum.period_start_date,
577 agt_sum.period_start_time,
578 agt_sum.period_start_date_time,
579 SUM(agt_sum.available_time) available_time,
580 SUM(agt_sum.work_time) work_time
581 FROM BIX_DM_AGENT_SESSBYCAMP_sum agt_sum,
582 jtf_rs_group_members grp_mem,
583 jtf_rs_groups_denorm grp_denorm
584 WHERE agt_sum.period_start_date_time BETWEEN g_rounded_collect_start_date
585 AND g_rounded_collect_end_date
586 AND agt_sum.resource_id = grp_mem.resource_id
587 AND grp_mem.group_id = grp_denorm.group_id
588 --
589 --add the following to take care of cases where
590 --agent belongs to two groups which roll up to the
591 --same parent group to avoid duplicating the values
592 --for the parent group
593 --
594 AND NVL(grp_mem.delete_flag,'N') <> 'Y'
595 AND agt_sum.period_start_date_time BETWEEN
596 NVL(grp_denorm.start_date_active,agt_sum.period_start_date_time)
597 AND NVL(grp_denorm.end_date_active,SYSDATE)
598 AND grp_mem.group_member_id =
599 (select max(mem1.group_member_id)
600 from jtf_rs_group_members mem1
601 where mem1.group_id in
602 (select den1.group_id
603 from jtf_rs_groups_denorm den1
604 where den1.parent_group_id = grp_denorm.parent_group_id
605 AND agt_sum.period_start_date_time BETWEEN
606 NVL(den1.start_date_active,agt_sum.period_start_date_time)
607 AND NVL(den1.end_date_active,SYSDATE)
608 )
609 AND mem1.resource_id = grp_mem.resource_id
610 AND nvl(mem1.delete_flag,'N') <> 'Y'
611 )
612 GROUP BY grp_denorm.parent_group_id,
613 agt_sum.server_group_id,
614 agt_sum.campaign_id,
615 agt_sum.campaign_schedule_id,
616 agt_sum.period_start_date_time,
617 agt_sum.period_start_date,
618 agt_sum.period_start_time;
619
620 BEGIN
621 g_insert_count := 0;
622 g_delete_count := 0;
623 g_update_count := 0;
624 g_proc_name := l_proc_name ;
625 g_table_name := 'BIX_DM_GROUP_SESSBYCAMP_SUM';
626
627 /* Delete data between these dates and re-compute */
628 delete_in_chunks( 'BIX_DM_GROUP_SESSBYCAMP_SUM', 1, g_delete_count);
629
630 g_message := 'Start Inserting rows into BIX_DM_GROUP_SESSION_SUM table';
631 write_log(g_pkg_name, l_proc_name, g_message );
632
633 FOR groupinfo IN group_agents
634 LOOP
635 INSERT INTO BIX_DM_GROUP_SESSBYCAMP_SUM
636 ( group_sessbycamp_sum_id
637 ,group_id
638 ,server_group_id
639 ,campaign_id
640 ,campaign_schedule_id
641 ,period_start_date
642 ,period_start_time
643 ,period_start_date_time
644 ,last_update_date
648 ,last_update_login
645 ,last_updated_by
646 ,creation_date
647 ,created_by
649 ,available_time
650 ,work_time
651 ,request_id
652 ,program_application_id
653 ,program_id
654 ,program_update_date )
655 VALUES (
656 BIX_DM_GROUP_CALL_SUM_S.NEXTVAL
657 ,groupinfo.group_id
658 ,groupinfo.server_group_id
659 ,groupinfo.campaign_id
660 ,groupinfo.campaign_schedule_id
661 ,groupinfo.period_start_date
662 ,groupinfo.period_start_time
663 ,groupinfo.period_start_date_time
664 ,SYSDATE
665 ,g_user_id
666 ,SYSDATE
667 ,g_user_id
668 ,g_user_id
669 ,groupinfo.available_time
670 ,groupinfo.work_time
671 ,g_request_id
672 ,g_program_appl_id
673 ,g_program_id
674 ,SYSDATE
675 );
676
677 l_row_count := l_row_count + 1;
678 IF(MOD(l_row_count,g_commit_chunk_size)=0) THEN
679 COMMIT;
680 END IF;
681
682 END LOOP;
683
684 g_message := 'Finished inserting rows into BIX_DM_GROUP_SESSBYCAMP_SUM. Inserted ' || l_row_count || ' rows ';
685 write_log(g_pkg_name, l_proc_name, g_message );
686
687 g_insert_count := l_row_count;
688
689 g_run_end_date := sysdate;
690 g_status := 'SUCCESS';
691 insert_log_table;
692 COMMIT;
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 g_message := 'ERROR : '|| sqlerrm;
697 g_error_msg := 'Failed while executing ' || g_proc_name || ':'
698 || sqlcode ||':'|| sqlerrm;
699 write_log(g_pkg_name, l_proc_name, g_message);
700
701 g_message :='Start rolling back data in BIX_DM_GROUP_SESSBYCAMP_SUM table';
702 write_log(g_pkg_name, l_proc_name, g_message );
703
704 BEGIN
705 delete_in_chunks('BIX_DM_GROUP_SESSBYCAMP_SUM', 2, l_row_count);
706 g_message := 'Finished rolling back data in BIX_DM_GROUP_SESSBYCAMP_SUM table';
707 write_log(g_pkg_name, l_proc_name, g_message );
708 EXCEPTION
709 WHEN OTHERS THEN
710 g_message := 'Failed to roll back data in BIX_DM_GROUP_SESSBYCAMP_SUM table ';
711 write_log(g_pkg_name, l_proc_name, g_message );
712 END;
713
714 g_message := 'Start rolling back data in BIX_DM_AGENT_SESSBYCAMP_SUM table';
715 write_log(g_pkg_name, l_proc_name, g_message );
716
717 BEGIN
718 delete_in_chunks('BIX_DM_AGENT_SESSBYCAMP_SUM', 2, l_row_count);
719 g_message := 'Finished Rollling back data in BIX_DM_AGENT_SESSBYCAMP_SUM table ' ;
720 write_log(g_pkg_name, l_proc_name, g_message );
721 EXCEPTION
722 WHEN OTHERS THEN
723 g_message := 'Failed to roll back data in BIX_DM_AGENT_SESSBYCAMP_SUM table ';
724 write_log(g_pkg_name, l_proc_name, g_message );
725 END;
726
727 UPDATE BIX_DM_COLLECT_LOG
728 SET collect_status = 'FAILURE',
729 rows_inserted = 0,
730 rows_updated = 0,
731 collect_excep_mesg = g_error_msg
732 WHERE request_id = g_request_id
733 AND object_name = 'BIX_DM_GROUP_SESSBYCAMP_SUM';
734
735 g_run_end_date := sysdate;
736 g_status := 'FAILURE';
737 insert_log_table;
738
739 RAISE;
740 END populate_groups;
741
742
743 PROCEDURE populate_agents
744 IS
745 l_proc_name VARCHAR2(20) := 'POPULATE_AGENTS';
746 l_delete_count NUMBER := 0;
747 BEGIN
748 g_insert_count := 0;
749 g_delete_count := 0;
750 g_update_count := 0;
751 g_proc_name := l_proc_name;
752 g_table_name := 'BIX_DM_AGENT_SESSBYCAMP_SUM';
753
754 /* Delete data between these dates and re-compute */
755 delete_in_chunks( 'BIX_DM_AGENT_SESSBYCAMP_SUM', 1, g_delete_count);
756
757 /* Procedure collects Agent work time from IEU_SH_SESSIONS table */
758 collect_agent_work_time;
759
760 /* Procedure collects Agent avialable time from IEU_SH_SESSIONS
761 ,IEU_SH_ACTIVITIES tables */
762 collect_agent_avail_time;
763
764 /* Insert the status into BIX_DM_COLLECT_LOG table */
765 g_run_end_date := sysdate;
766 g_status := 'SUCCESS';
767 insert_log_table;
768
769 COMMIT; --commit after all rows are inserted in BIX_DM_AGENT_SESSBYCAMP_sum
770
771 EXCEPTION
772 WHEN OTHERS THEN
773 g_message := 'Failed while populating BIX_DM_AGENT_SESSBYCAMP_sum : '||
774 sqlerrm;
775 write_log(g_pkg_name, l_proc_name, g_message);
776
777 g_message :='Start rolling back data in BIX_DM_AGENT_SESSBYCAMP_SUM table';
778 write_log(g_pkg_name, l_proc_name, g_message );
779
780 BEGIN
781 delete_in_chunks('BIX_DM_AGENT_SESSBYCAMP_SUM', 2, l_delete_count);
782 g_message := 'Finished rolling back data in BIX_DM_AGENT_SESSBYCAMP_SUM table' ;
783 write_log(g_pkg_name, l_proc_name, g_message );
784 EXCEPTION
785 WHEN OTHERS THEN
789
786 g_message := 'Failed to roll back data in BIX_DM_AGENT_SESSBYCAMP_SUM table ';
787 write_log(g_pkg_name, l_proc_name, g_message );
788 END;
790 g_run_end_date := sysdate;
791 g_status := 'FAILURE';
792 g_insert_count := 0;
793 g_update_count := 0;
794 g_error_msg := 'Failed while executing ' || g_proc_name
795 || ':' || sqlcode ||':'|| sqlerrm;
796 insert_log_table;
797 RAISE;
798
799 END populate_agents;
800
801 /*
802 This procedure is the main procedure which is called from the concurrent
803 program. It calls two other procedures to get the agent and group
804 session information.
805 */
806 PROCEDURE populate_all ( errbuf OUT nocopy VARCHAR2,
807 retcode OUT nocopy VARCHAR2,
808 p_start_date IN VARCHAR2,
809 p_end_date IN VARCHAR2 )
810 IS
811 l_proc_name VARCHAR2(35) := 'POPULATE_SESSBYCAMP_SUM_TABLES';
812
813 BEGIN
814
815 g_run_start_date := sysdate;
816 g_proc_name := l_proc_name;
817
821 THEN
818 /* Determine value of commit size. If the profile is not defined,
819 assume 100 rows. */
820 IF FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')
822 g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
823 ELSE
824 g_commit_chunk_size := 100;
825 END IF;
826
827 IF (FND_PROFILE.DEFINED('BIX_DBI_DEBUG')) THEN
828 g_debug_flag := nvl(FND_PROFILE.VALUE('BIX_DBI_DEBUG'), 'N');
829 END IF;
830
831 /* Concurrent program is passing date as YYYY/MM/DD HH24:MI:SS. */
832 g_collect_start_date := TO_DATE(p_start_date,'YYYY/MM/DD HH24:MI:SS');
833 g_collect_end_date := TO_DATE(p_end_date,'YYYY/MM/DD HH24:MI:SS');
834
835 /* Round the collection start date to the nearest lower time bucket.
836 eg: if time is between 10:00 and 10:29 round it to 10:00.
837 */
838 SELECT TO_DATE(TO_CHAR(g_collect_start_date,'YYYY/MM/DD')||
839 LPAD(TO_CHAR(g_collect_start_date,'HH24:'),3,'0')||
840 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_start_date,'MI'))-29),
841 0,'00:00',1,'30:00',-1,'00:00'), 'YYYY/MM/DDHH24:MI:SS')
842 INTO g_rounded_collect_start_date
843 FROM DUAL;
844
845 /* Round the collection end date to nearest higher time bucket.
846 eg: if time is between 10:00 and 10:29 round it to 10:29:59
847 */
848 SELECT TO_DATE(
849 TO_CHAR(g_collect_end_date,'YYYY/MM/DD')||
850 LPAD(TO_CHAR(g_collect_end_date,'HH24:'),3,'0')||
851 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_end_date,'MI'))-29),
852 0,'29:59',1,'59:59',-1,'29:59'), 'YYYY/MM/DDHH24:MI:SS')
853 INTO g_rounded_collect_end_date
854 FROM DUAL;
855
856 g_message := 'Collection Period : '||
857 to_char(g_rounded_collect_start_date,'DD-MON-YYYY HH24:MI:SS') || ' to '
858 || to_char(g_rounded_collect_end_date, 'DD-MON-YYYY HH24:MI:SS');
859 write_log(g_pkg_name, l_proc_name, g_message);
860
861 g_message := '-----------------------------------------------------------';
862 write_log(g_pkg_name, l_proc_name, g_message);
863
864 g_message := 'Start collecting agent session information';
865 write_log(g_pkg_name, l_proc_name, g_message);
866
867 populate_agents;
868
869 g_message := 'Finished collecting agent session information';
870 write_log(g_pkg_name, l_proc_name, g_message);
871
872 g_message := '----------------------------------------------------------';
873 write_log(g_pkg_name, l_proc_name, g_message);
874
875 g_message := 'Start processing group session information';
876 write_log(g_pkg_name, l_proc_name, g_message);
877
878 populate_groups;
879
880 g_message := 'Finished processing group session information';
881 write_log(g_pkg_name, l_proc_name, g_message);
882
883 g_message := '--------------------------------------------------------';
884 write_log(g_pkg_name, l_proc_name, g_message);
885
886 /*
887 Success log tables were already inserted, so just commit
888 */
889 COMMIT;
890
891 EXCEPTION
892 WHEN OTHERS THEN
893 retcode := sqlcode;
894 errbuf := sqlerrm;
895 g_status := 'FAILURE';
896 g_error_msg := 'Failed while executing ' || g_proc_name || ':' ||
897 sqlcode ||':'|| sqlerrm;
898 g_message := 'ERROR : ' || sqlerrm;
899 write_log(g_pkg_name, g_proc_name, g_message);
900
901 COMMIT;
902 END populate_all;
903
904 END BIX_DM_SESSBYCAMP_PKG;