[Home] [Help]
PACKAGE BODY: APPS.BIX_DM_EMAIL_SUMMARY_PKG
Source
1 PACKAGE BODY BIX_DM_EMAIL_SUMMARY_PKG AS
2 /*$Header: bixxemcb.plb 120.1 2005/10/12 22:01:57 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_program_start_date DATE;
9 g_collect_start_date DATE;
10 g_collect_end_date DATE;
11 g_rounded_collect_start_date DATE;
12 g_rounded_collect_end_date DATE;
13 g_error_mesg VARCHAR2(4000);
14 g_status VARCHAR2(4000);
15 g_proc_name VARCHAR2(4000);
16 g_commit_chunk_size NUMBER;
17 g_debug_flag VARCHAR2(1) := 'N';
18
19 G_DATE_MISMATCH EXCEPTION;
20
21 /*===================================================================================================+
22 | INSERT_LOG procedure inserts collection concurrent program status into BIX_DM_COLLECT_LOG table |
23 | It inserts a row with the following details : |
24 | |
25 | COLLECT_STATUS column equals to FAILED if the program failed otherwise SUCCESS |
26 | COLLECT_EXCEP_MESG as error message if the program failed otherwise NULL |
27 | RUN_START_DATE equals to start date time when the collection program started runnning |
28 | RUN_END_DATE equals end date time of the collection program finished |
29 | COLLECT_START_DATE Collection start date specified by the user in the cuncurrent program parameter |
30 | COLLECT_END_DATE Collection end date specified by the user in the cuncurrent program parameter |
31 ====================================================================================================+*/
32
33 PROCEDURE Write_Log (p_msg IN VARCHAR2) IS
34 BEGIN
35 IF (g_debug_flag = 'Y') THEN
36 fnd_file.put_line(fnd_file.log, p_msg);
37 END IF;
38 EXCEPTION
39 WHEN OTHERS THEN
40 RAISE;
41 END Write_Log;
42
43 PROCEDURE INSERT_LOG
44 AS
45 BEGIN
46 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
47 ' BIX_DM_EMAIL_SUMMARY_PKG.INSERT_LOG:' ||
48 'Start inserting collection status into BIX_DM_COLLECT_LOG table');
49
50 /* Insert status into log table */
51
52 INSERT INTO BIX_DM_COLLECT_LOG
53 (
54 COLLECT_ID,
55 COLLECT_CONCUR_ID,
56 OBJECT_NAME,
57 OBJECT_TYPE,
58 RUN_START_DATE,
59 RUN_END_DATE,
60 COLLECT_START_DATE,
61 COLLECT_END_DATE,
62 COLLECT_STATUS,
63 COLLECT_EXCEP_MESG,
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_proc_name,
79 'PL SQL PACKAGE',
80 g_program_start_date,
81 SYSDATE,
82 g_collect_start_date,
83 g_collect_end_date,
84 g_status,
85 g_error_mesg,
86 SYSDATE,
87 g_user_id,
88 SYSDATE,
89 g_user_id,
90 g_user_id,
91 g_request_id,
92 g_program_appl_id,
93 g_program_id,
94 SYSDATE
95 );
96 COMMIT;
97
98 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
99 ' BIX_DM_EMAIL_SUMMARY_PKG.INSERT_LOG:'||
100 'Finished inserting collection status into BIX_DM_COLLECT_LOG table');
101 EXCEPTION
102 WHEN OTHERS THEN
103 write_log( TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
104 'BIX_DM_EMAIL_SUMMARY_PKG.INSERT_LOG: ' ||
105 'Failed to insert rows into BIX_DM_COLLECT_LOG table: '||sqlerrm);
106 RAISE;
107 END INSERT_LOG;
108
109 /*===================================================================================================+
110 | CLEAN_UP procedure writes error message into FND log file,Rollback the data written into Email |
111 | summary tables and also calls INSERT_LOG procedure to log error messge in BIX_DM_COLLECT_LOG table |
112 +===================================================================================================*/
113
114 PROCEDURE clean_up IS
115 l_delete_count NUMBER := 0;
116
117 BEGIN
118 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||g_error_mesg);
119
120 /* Delete from Email summary table */
121
122 LOOP
123 DELETE bix_dm_email_sum
124 WHERE last_update_date > g_program_start_date
125 AND rownum <= g_commit_chunk_size ;
126
127 IF(SQL%ROWCOUNT < g_commit_chunk_size) THEN
128 COMMIT;
129 EXIT;
130 ELSE
131 COMMIT;
132 END IF;
133
134 END LOOP;
135
136 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
137 ' BIX_DM_EMAIL_SUMMARY_PKG.CLEAN_UP: '||
138 ' Finished Deleting rows in BIM_DM_EMAIL_SUM table: ' ||
139 'Row Count:' || l_delete_count);
140
141 l_delete_count := 0;
142
143 /* Delete Email Agent Summary Table */
144
145 LOOP
146 DELETE bix_dm_email_agent_sum
147 WHERE last_update_date > g_program_start_date
148 AND rownum <= g_commit_chunk_size ;
149
150 IF(SQL%ROWCOUNT < g_commit_chunk_size) THEN
151 COMMIT;
152 EXIT;
153 ELSE
154 COMMIT;
155 END IF;
156
157 END LOOP;
158
159 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
160 ' BIX_DM_EMAIL_SUMMARY_PKG.CLEAN_UP: '||
161 ' Finished Deleting rows in BIM_DM_EMAIL_AGENT_SUM table: ' ||
162 'Row Count:' || l_delete_count);
163 l_delete_count := 0;
164
165 /* Delete from Email Group Summary tables */
166
167 LOOP
168 DELETE bix_dm_email_group_sum
169 WHERE last_update_date > g_program_start_date
170 AND rownum <= g_commit_chunk_size ;
171
172 IF(SQL%ROWCOUNT < g_commit_chunk_size) THEN
173 COMMIT;
174 EXIT;
175 ELSE
176 COMMIT;
177 END IF;
178
179 END LOOP;
180
181 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
182 ' BIX_DM_EMAIL_SUMMARY_PKG.CLEAN_UP: '||
183 ' Finished Deleting rows in BIM_DM_EMAIL_GROUP_SUM table: ' ||
184 'Row Count:' || l_delete_count);
185 EXCEPTION
186 WHEN OTHERS THEN
187 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
188 'CLEAN_UP: ERROR: '|| SQLERRM);
189 RAISE;
190 END CLEAN_UP;
191
192 /*==========================================================================================+
193 | This procedure returns weather Email Center application is installed or not |
194 ===========================================================================================*/
195
196 FUNCTION is_emc_installed RETURN VARCHAR2 IS
197 l_emc_installed VARCHAR2(1);
198 BEGIN
199 SELECT UPPER(application_installed) INTO l_emc_installed
200 FROM bix_dm_apps_dependency
201 WHERE application_short_name = 'BIX_DM_EMC_INSTALLED';
202
203 return l_emc_installed;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
208 ' BIX_DM_EMAIL_SUMMARY_PKG.IS_EMC_INSTALLED: ERROR ' || sqlerrm);
209 return 'N';
210 END;
211
212 /*===========================================================================================+
213 | This procedure collects additive email reporting measures summarised by the following |
214 | dimensions |
215 | 1. Email account id |
216 | 2. Classification |
217 | 3. Resource ID |
218 | 4. Time bucket ( half hour). All the measures are summarized by half hour |
219 ============================================================================================*/
220
221 PROCEDURE collect_emails
222 AS
223
224 l_email_sum_delete_count NUMBER := 0 ;
225 l_email_agent_sum_delete_count NUMBER := 0;
226 l_email_sum_insert_count NUMBER := 0;
227 l_email_agent_sum_insert_count NUMBER := 0;
228 l_email_service_level NUMBER := 24 * 60 * 60;
229
230 CURSOR all_emails IS
231 SELECT NVL(ih_mitem.source_id,-1) EMAIL_ACCOUNT_ID,
232 NVL(iem_r_c.ROUTE_CLASSIFICATION_ID,-1) CLASSIFICATION_ID,
233 RESOURCE_ID,
234 NVL(TRUNC(ih_lc_segs.start_date_time),TO_DATE('4012/01/01','YYYY/MM/DD')) PERIOD_START_DATE,
235 NVL(LPAD(TO_CHAR(ih_lc_segs.start_date_time,'HH24:'),3,'0')||
236 DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_lc_segs.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
237 PERIOD_START_TIME,
238 TO_DATE(NVL(TO_CHAR(ih_lc_segs.start_date_time,'YYYY/MM/DD '),'4012/01/01 ')||
239 NVL(LPAD(TO_CHAR(ih_lc_segs.start_date_time,'HH24:'),3,'0')||
240 DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_lc_segs.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00'),
241 'YYYY/MM/DD HH24:MI') PERIOD_START_DATE_TIME,
242 --
243 --Make sure the media start date falls within date range
244 --as where condition is based on segment start time
245 --
246 COUNT(DISTINCT(DECODE(SIGN(ih_mitem.start_date_time - g_rounded_collect_start_date), -1, null,
247 DECODE(SIGN(ih_mitem.start_date_time - g_rounded_collect_end_date), -1, ih_mitem.media_id, null))))
248 EMAILS_OFFERED,
249 SUM(DECODE(ih_lc_seg_typs.MILCS_CODE,'EMAIL_FETCH',1)) EMAILS_FETCHED,
250 SUM(DECODE(ih_lc_seg_typs.MILCS_CODE,'EMAIL_REPLY',1)) EMAILS_REPLIED,
251 SUM(DECODE(ih_lc_seg_typs.MILCS_CODE,'EMAIL_REPLY',
252 (ih_lc_segs.start_date_time - email_fetch_time.start_date_time) * 24 * 60 * 60))
253 COMPLETE_AGENT_RESPONSE_TIME,
254 SUM(DECODE(ih_lc_seg_typs.MILCS_CODE,'EMAIL_REPLY',
255 DECODE(SIGN((NVL(goals.sl_for_replied_emails * 60 * 60,l_email_service_level)) -
256 (ih_lc_segs.start_date_time - email_fetch_time.start_date_time)
257 * 24 * 60 * 60),-1,0,1))) EMAILS_REPLIED_WITHIN_GOAL,
258 SUM(DECODE(ih_lc_seg_typs.MILCS_CODE,'EMAIL_TRANSFERRED',1)) EMAILS_TRANSFERRED,
259 SUM(DECODE(ih_lc_seg_typs.MILCS_CODE,'EMAIL_DELETED',1)) EMAILS_DELETED,
260 SUM(DECODE(ih_lc_seg_typs.MILCS_CODE,'EMAIL_REPLY',
261 (ih_lc_segs.start_date_time - ih_mitem.start_date_time) * 24 * 60 * 60)) EMC_RESPONSE_TIME
262 FROM JTF_IH_MEDIA_ITEMS ih_mitem,
263 JTF_IH_MEDIA_ITEM_LC_SEGS ih_lc_segs,
264 (
265 --
266 --To calculate agent response time
267 --
268 SELECT a.media_id media_id,
269 MAX(c.start_date_time) start_date_time
270 FROM jtf_ih_media_item_lc_segs a,
271 jtf_ih_media_itm_lc_seg_tys b,
272 jtf_ih_media_item_lc_segs c,
273 jtf_ih_media_itm_lc_seg_tys d
274 WHERE a.start_date_time BETWEEN g_rounded_collect_start_date AND g_rounded_collect_end_date
275 AND a.milcs_type_id = b.milcs_type_id
276 AND b.milcs_code = 'EMAIL_REPLY'
277 AND a.media_id = c.media_id
278 AND c.milcs_type_id = d.milcs_type_id
279 AND d.milcs_code IN ('EMAIL_FETCH','EMAIL_TRANSFER')
280 GROUP BY a.media_id
281 ) email_fetch_time,
282 JTF_IH_MEDIA_ITM_LC_SEG_TYS ih_lc_seg_typs,
283 --iem_route_classifications iem_r_c,
284 --
285 --Changes for R12
286 --
287 (
288 select name, max(route_classification_id) route_classification_id
289 from iem_route_classifications
290 group by name
291 ) iem_r_c,
292 ( SELECT * FROM bix_dm_goals_emc
293 WHERE end_date_active IS NULL) goals
294 WHERE ih_mitem.MEDIA_ITEM_TYPE = 'EMAIL'
295 AND ih_mitem.DIRECTION = 'INBOUND'
296 AND ih_mitem.classification = iem_r_c.name(+)
297 AND iem_r_c.route_classification_id = goals.classification_id(+)
298 AND ih_mitem.media_id = email_fetch_time.media_id(+)
299 AND ih_mitem.MEDIA_ID = ih_lc_segs.MEDIA_ID
300 AND ih_lc_segs.MILCS_TYPE_ID = ih_lc_seg_typs.MILCS_TYPE_ID
301 AND ih_lc_segs.START_DATE_TIME BETWEEN g_rounded_collect_start_date and g_rounded_collect_end_date
302 GROUP BY NVL(ih_mitem.SOURCE_ID,-1),
303 NVL(iem_r_c.ROUTE_CLASSIFICATION_ID,-1),
304 RESOURCE_ID,
305 NVL(TRUNC(ih_lc_segs.start_date_time),TO_DATE('4012/01/01','YYYY/MM/DD')),
306 NVL(LPAD(TO_CHAR(ih_lc_segs.start_date_time,'HH24:'),3,'0')||
307 DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_lc_segs.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00'),
308 TO_DATE(NVL(TO_CHAR(ih_lc_segs.start_date_time,'YYYY/MM/DD '),'4012/01/01 ')||
309 NVL(LPAD(TO_CHAR(ih_lc_segs.start_date_time,'HH24:'),3,'0')||
310 DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_lc_segs.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),
311 '00:00'),'YYYY/MM/DD HH24:MI');
312 BEGIN
313
314 /* Get the Service level for the whole email center . Use this service level if the service
315 level is not defined for indivisual classification */
316
317 BEGIN
318 --
319 --This is for cases where the goal is not defined and the default is used
320 --
321 SELECT sl_for_replied_emails * 60 * 60 INTO l_email_service_level
322 FROM bix_dm_goals_emc
323 WHERE classification_id = -999;
324 EXCEPTION
325 WHEN OTHERS THEN
326 l_email_service_level := 24 * 60 * 60;
327 END;
328
329 /* Delete from the Email summary table for the data range that the data need to be collected */
330
331 --dbms_output.put_line('service level emc: '|| l_email_service_level );
332
333 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
334 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAIL :'||
335 'Start Deleting rows from Email summary tables');
336
337 /* Delete from BIX_DM_EMAIL_SUM table where last_update_date
338 between g_rounded_collect_start_date and g_rounded_collect_end_date */
339
340 l_email_sum_delete_count := 0;
341
342 LOOP
343 DELETE bix_dm_email_sum
344 WHERE period_start_date_time BETWEEN g_rounded_collect_start_date AND
345 g_rounded_collect_end_date
346 AND rownum <= g_commit_chunk_size ;
347
348 l_email_sum_delete_count := l_email_sum_delete_count + SQL%ROWCOUNT;
349
350 IF(SQL%ROWCOUNT < g_commit_chunk_size) THEN
351 COMMIT;
352 EXIT;
353 ELSE
354 COMMIT;
355 END IF;
356 END LOOP;
357
358 LOOP
359 DELETE bix_dm_email_agent_sum
360 WHERE period_start_date_time BETWEEN g_rounded_collect_start_date AND
361 g_rounded_collect_end_date
362 AND rownum <= g_commit_chunk_size ;
363
364 l_email_agent_sum_delete_count := l_email_agent_sum_delete_count + SQL%ROWCOUNT;
365
369 ELSE
366 IF(SQL%ROWCOUNT < g_commit_chunk_size) THEN
367 COMMIT;
368 EXIT;
370 COMMIT;
371 END IF;
372 END LOOP;
373
374 --dbms_output.put_line('BIX_DM_EMAIL_SUM Delete Count : '|| l_email_sum_delete_count);
375
376 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
377 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
378 ' Finished Deleting rows in BIM_DM_EMAIL_SUM table: ' ||
379 'Row Count:' || l_email_sum_delete_count);
380
381 --dbms_output.put_line('BIX_DM_EMAIL_AGENT_SUM delete count :'|| l_email_agent_sum_delete_count);
382
383 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
384 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
385 ' Finished Deleting rows in BIM_DM_EMAIL_AGENT_SUM table: ' ||
386 'Row Count:' || l_email_agent_sum_delete_count);
387
388 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
389 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
390 ' Start collecting information into Email summary tables');
391
392 /*
393 dbms_output.put_line('before for loop');
394 dbms_output.put_line('Start Date: ' ||
395 to_char(g_rounded_collect_start_date,'YYYY/MM/DD HH24:MI:SS'));
396 dbms_output.put_line('End Date: ' ||
397 to_char(g_rounded_collect_end_date,'YYYY/MM/DD HH24:MI:SS'));
398 */
399
400 FOR emails IN all_emails LOOP
401
402 -- dbms_output.put_line('inside for loop');
403 IF( emails.resource_id IS NULL) THEN
404 --dbms_output.put_line('inside email IF');
405 /* insert the email summary information into the BIX_DM_EMAIL_SUM table */
406 --
407 --This might be a QUEUE rows OR a EMAILS_PROCESSING row or OFFERED.
408 --
409 INSERT INTO bix_dm_email_sum
410 (
411 email_summary_id,
412 email_account_id,
413 classification_id,
414 period_start_date,
415 period_start_time,
416 period_start_date_time,
417 last_update_date,
418 last_updated_by,
419 creation_date,
420 created_by,
421 emails_offered,
422 request_id,
423 program_application_id,
424 program_id,
425 program_update_date
426 )
427 VALUES
428 (
429 bix_dm_email_sum_s.nextval,
430 emails.email_account_id,
431 emails.classification_id,
432 emails.period_start_date,
433 emails.period_start_time,
434 emails.period_start_date_time,
435 SYSDATE,
436 g_user_id,
437 SYSDATE,
438 g_user_id,
439 emails.emails_offered,
440 g_request_id,
441 g_program_appl_id,
442 g_program_id,
443 SYSDATE
444 );
445 l_email_sum_insert_count := l_email_sum_insert_count + 1;
446 ELSE
447 --dbms_output.put_line('inside email Agent IF');
448 --
449 --This might be any of the agent events like REPLIED, DELETED etc
450 --
451 INSERT INTO bix_dm_email_agent_sum
452 (
453 email_agent_summary_id,
454 email_account_id,
455 classification_id,
456 resource_id,
457 period_start_date,
458 period_start_time,
459 period_start_date_time,
460 last_update_date,
461 last_updated_by,
462 creation_date,
463 created_by,
464 emails_fetched,
465 emails_replied,
466 emails_replied_within_goal,
467 emails_deleted,
468 emails_transferred,
469 emc_response_time,
470 complete_agent_response_time,
471 request_id,
472 program_application_id,
473 program_id,
474 program_update_date
475 )
476 VALUES
477 (
478 bix_dm_email_agent_sum_s.nextval,
479 emails.email_account_id,
480 emails.classification_id,
481 emails.resource_id,
482 emails.period_start_date,
483 emails.period_start_time,
484 emails.period_start_date_time,
485 SYSDATE,
486 g_user_id,
487 SYSDATE,
488 g_user_id,
489 emails.emails_fetched,
490 emails.emails_replied,
491 emails.emails_replied_within_goal,
492 emails.emails_deleted,
493 emails.emails_transferred,
494 emails.emc_response_time,
495 emails.complete_agent_response_time,
496 g_request_id,
497 g_program_appl_id,
498 g_program_id,
499 SYSDATE
500 );
501 l_email_agent_sum_insert_count := l_email_agent_sum_insert_count + 1;
502 END IF;
503
504 /* commit the rows after every commit chunk size which is defined as profile */
505
506 IF(MOD((l_email_sum_insert_count + l_email_agent_sum_insert_count),g_commit_chunk_size)=0) THEN
507 COMMIT;
508 END IF;
509
510 END LOOP;
511
512 COMMIT;
513
514 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
515 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
516 'Finished Inserting rows into BIM_DM_EMAIL_SUM table ' );
517
518 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
519 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
520 'Finished Inserting rows into BIM_DM_EMAIL_AGENT_SUM table ');
521
522 select count(*) into l_email_agent_sum_insert_count
523 from bix_dm_email_agent_sum;
524 --dbms_output.put_line('Inserted Count : ' || l_email_agent_sum_insert_count);
525
529 EXCEPTION
526 /* If any error occurs delete all the rows inserted in this procedure
527 and raise an exception to outer calling procedure */
528
530 WHEN OTHERS THEN
531 --dbms_output.put_line('Error'|| sqlerrm);
532 g_error_mesg := 'COLLECT_CALLS: ERROR: ' || SQLERRM;
533 clean_up;
534 RAISE;
535 END COLLECT_EMAILS;
536
537
538
539 /*===========================================================================================+
540 | This procedure collects Queue measures. The queue cursor has two parts with union. |
541 | The first part collects all the emails which are still in queue now. |
542 | The second part of SQL collects all the emails which was in queue for some time and |
543 | now they are no longer in queue . |
544 | |
545 | Each eamil can be in queue for several days. Each email translates to multiple rows |
546 | in summary table. for example if one emaiil is in queue for 2 days then two rows will |
547 | be inserted one for each day with accumulated_emails_in_queue as 1 for both days. |
548 | |
549 | If there is no emails in queue for particular combination dimensions |
550 | this procedure populates null. |
551 ============================================================================================*/
552
553 PROCEDURE COLLECT_QUEUE_MEASURES
554 AS
555 CURSOR queue_measures IS
556 SELECT ih_mitem.media_id,
557 NVL(ih_mitem.source_id,-1) EMAIL_ACCOUNT_ID,
558 NVL(iem_r_c.ROUTE_CLASSIFICATION_ID,-1) CLASSIFICATION_ID,
559 ih_lc_segs_fetch.START_DATE_TIME start_date_time,
560 g_rounded_collect_end_date end_date_time
561 FROM JTF_IH_MEDIA_ITEMS ih_mitem,
562 JTF_IH_MEDIA_ITEM_LC_SEGS ih_lc_segs_fetch,
563 JTF_IH_MEDIA_ITM_LC_SEG_TYS ih_lc_seg_typs,
564 --IEM_ROUTE_CLASSIFICATIONS iem_r_c
565 --
566 --Changes for R12
567 --
568 (
569 select name, max(route_classification_id) route_classification_id
570 from iem_route_classifications
571 group by name
572 ) iem_r_c
573 WHERE ih_mitem.MEDIA_ITEM_TYPE = 'EMAIL'
574 AND ih_mitem.DIRECTION = 'INBOUND'
575 AND ih_mitem.classification = iem_r_c.name(+)
576 AND ih_mitem.MEDIA_ID = ih_lc_segs_fetch.MEDIA_ID
577 AND ih_lc_segs_fetch.START_DATE_TIME < g_rounded_collect_end_date
578 AND ih_lc_segs_fetch.MILCS_TYPE_ID = ih_lc_seg_typs.MILCS_TYPE_ID
579 AND ih_lc_seg_typs.MILCS_CODE IN ('EMAIL_PROCESSING', 'EMAIL_REQUEUED')
580 AND NOT EXISTS
581 (
582 SELECT 'DUMMY'
583 FROM JTF_IH_MEDIA_ITEM_LC_SEGS B,
584 JTF_IH_MEDIA_ITM_LC_SEG_TYS C
585 WHERE ih_lc_segs_fetch.MEDIA_ID = B.MEDIA_ID
586 AND B.MILCS_TYPE_ID = C.MILCS_TYPE_ID
587 AND C.MILCS_CODE IN
588 ('EMAIL_FETCH', 'EMAIL_RESOLVED', 'EMAIL_AUTO_REDIRECTED', 'EMAIL_AUTO_DELETED',
589 'EMAIL_AUTO_REPLY', 'EMAIL_OPEN', 'EMAIL_AUTO_ROUTED', 'EMAIL_AUTO_UPDATED_SR',
590 'EMAIL_ASSIGN','EMAIL_AUTO_REDIRECTED_INTERNAL','EMAIL_AUTO_REDIRECTED_EXTERNAL','EMAIL_DELETED')
591 AND B.START_DATE_TIME >= ih_lc_segs_fetch.START_DATE_TIME
592 AND B.START_DATE_TIME < g_rounded_collect_end_date
593 )
594 UNION
595 SELECT a.media_id,
596 NVL(a.source_id,-1) EMAIL_ACCOUNT_ID,
597 NVL(iem_r_c.ROUTE_CLASSIFICATION_ID,-1) CLASSIFICATION_ID,
598 Y.START_DATE_TIME start_date_time,
599 MIN(X.START_DATE_TIME) end_date_time
600 FROM JTF_IH_MEDIA_ITEMS A,
601 (
602 SELECT MEDIA_ID,
603 RESOURCE_ID,
604 START_DATE_TIME
605 FROM JTF_IH_MEDIA_ITEM_LC_SEGS B,
606 JTF_IH_MEDIA_ITM_LC_SEG_TYS C
607 WHERE B.MILCS_TYPE_ID = C.MILCS_TYPE_ID
608 AND C.MILCS_CODE IN
609 ('EMAIL_FETCH', 'EMAIL_RESOLVED', 'EMAIL_AUTO_REDIRECTED', 'EMAIL_AUTO_DELETED',
610 'EMAIL_AUTO_REPLY', 'EMAIL_OPEN', 'EMAIL_AUTO_ROUTED', 'EMAIL_AUTO_UPDATED_SR',
611 'EMAIL_ASSIGN','EMAIL_AUTO_REDIRECTED_INTERNAL','EMAIL_AUTO_REDIRECTED_EXTERNAL','EMAIL_DELETED')
612 AND B.START_DATE_TIME BETWEEN g_rounded_collect_start_date AND g_rounded_collect_end_date
613 --
614 --it should have been closed within the collection date range
615 --
616 ) X,
617 JTF_IH_MEDIA_ITEM_LC_SEGS Y,
618 JTF_IH_MEDIA_ITM_LC_SEG_TYS Z,
619 --IEM_ROUTE_CLASSIFICATIONS iem_r_c
620 --
621 --Changes for R12
622 --
623 (
624 select name, max(route_classification_id) route_classification_id
625 from iem_route_classifications
626 group by name
627 ) iem_r_c
628 WHERE a.MEDIA_ITEM_TYPE = 'EMAIL'
629 AND a.DIRECTION = 'INBOUND'
630 AND a.classification = iem_r_c.name(+)
631 AND A.MEDIA_ID = X.MEDIA_ID
632 AND X.MEDIA_ID = Y.MEDIA_ID
633 AND Y.START_DATE_TIME < g_rounded_collect_end_date --email arrival time should be less than collection end date
634 AND Y.MILCS_TYPE_ID = Z.MILCS_TYPE_ID
635 AND X.START_DATE_TIME >= Y.START_DATE_TIME --the delete/reply/resolved should have happened after the email arrival time
636 AND Z.MILCS_CODE IN ('EMAIL_PROCESSING', 'EMAIL_REQUEUED')
637 GROUP BY
641 Y.START_DATE_TIME
638 a.media_id,
639 NVL(a.source_id,-1),
640 NVL(iem_r_c.ROUTE_CLASSIFICATION_ID,-1),
642 ;
643
644 --
645 --the above takes the minimum of date in order to take care of multiple cycles.
646 --for each cycle, the corresponding queu start and corresponding queue end will be taken
647 --
648 l_temp_date DATE;
649 l_begin_bucket_date DATE;
650 l_end_bucket_date DATE;
651 l_counter NUMBER :=0;
652 l_emails_in_queue NUMBER;
653 l_total_queue_time NUMBER;
654 l_emails_queued NUMBER;
655 l_oldest_message_in_queue DATE;
656
657 BEGIN
658
659
660 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
661 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_QUEUE_MEASURES: '||
662 ' Start collecting Queue measures ');
663
664 FOR q_measures IN queue_measures LOOP
665 -- dbms_output.put_line('FOR LOOP media id:' || q_measures.media_id);
666 l_begin_bucket_date := NULL;
667 l_end_bucket_date := NULL;
668 l_temp_date := NULL;
669
670 IF(q_measures.start_date_time >= g_rounded_collect_start_date + 1/48) THEN
671 SELECT TO_DATE(TO_CHAR(q_measures.start_date_time ,'YYYY/MM/DD')||
672 LPAD(TO_CHAR(q_measures.start_date_time ,'HH24:'),3,'0')||
673 DECODE(SIGN(TO_NUMBER(TO_CHAR(q_measures.start_date_time ,'MI'))-29),
674 0,'00',1,'30',-1,'00'),'YYYY/MM/DDHH24:MI')
675 INTO l_begin_bucket_date FROM DUAL;
676 ELSE
677 l_begin_bucket_date := g_rounded_collect_start_date;
678 END IF;
679
680 l_temp_date := l_begin_bucket_date + 1/48;
681
682 IF ( q_measures.end_date_time < g_rounded_collect_end_date - (( 29 * 60 + 59 )/(24*60*60)) ) THEN
683 SELECT TO_DATE(TO_CHAR(q_measures.end_date_time ,'YYYY/MM/DD')||
684 LPAD(TO_CHAR(q_measures.end_date_time ,'HH24:'),3,'0')||
685 DECODE(SIGN(TO_NUMBER(TO_CHAR(q_measures.end_date_time ,'MI'))-29),
686 0,'00',1,'30',-1,'00'),'YYYY/MM/DDHH24:MI')
687 INTO l_end_bucket_date FROM DUAL;
688 ELSE
689 l_end_bucket_date := g_rounded_collect_end_date - (( 29 * 60 + 59 )/(24*60*60));
690 END IF;
691
692 --dbms_output.put_line('STart date:'|| l_begin_bucket_date);
693 --dbms_output.put_line('End date:'|| l_end_bucket_date);
694
695 WHILE(l_temp_date <= l_end_bucket_date + 1/48) LOOP
696 --dbms_output.put_line('media id:' || q_measures.media_id);
697 l_emails_in_queue := 0;
698 l_total_queue_time := 0;
699 l_emails_queued := 0;
700 l_oldest_message_in_queue := NULL;
701
702
703 IF(q_measures.end_date_time BETWEEN l_begin_bucket_date AND l_temp_date
704 AND q_measures.end_date_time <> g_rounded_collect_end_date ) THEN
705 l_total_queue_time := (q_measures.end_date_time - q_measures.start_date_time ) * 24 * 60 * 60;
706 l_emails_queued := 1;
707 ELSE
708 l_total_queue_time := (l_temp_date - q_measures.start_date_time) * 24 * 60 * 60;
709 l_emails_queued := 1;
710 l_emails_in_queue := 1;
711 l_oldest_message_in_queue := q_measures.start_date_time;
712 END IF;
713
714 l_counter := l_counter + 1;
715
716 UPDATE bix_dm_email_sum
717 SET emails_remaining_in_queue = NVL(emails_remaining_in_queue,0) + l_emails_in_queue,
718 queue_time = NVL(queue_time,0) + l_total_queue_time,
719 max_queue_time = DECODE(l_total_queue_time, NULL,max_queue_time,
720 DECODE(max_queue_time,NULL,l_total_queue_time,
721 DECODE(SIGN(NVL(l_total_queue_time,0) - NVL(max_queue_time,0)),
722 1,l_total_queue_time,max_queue_time))),
723 emails_in_q_during_time_period = NVL(emails_in_q_during_time_period,0) + l_emails_queued,
724 oldest_message_in_queue = DECODE(l_oldest_message_in_queue,NULL,oldest_message_in_queue,
725 DECODE(oldest_message_in_queue,NULL,l_oldest_message_in_queue,
726 DECODE(SIGN(oldest_message_in_queue - l_oldest_message_in_queue),
727 0,oldest_message_in_queue,-1,oldest_message_in_queue,1,
728 l_oldest_message_in_queue))),
729 last_update_date = SYSDATE,
730 last_updated_by = g_user_id
731 WHERE period_start_date_time = l_begin_bucket_date
732 AND email_account_id = q_measures.email_account_id
733 AND classification_id = q_measures.classification_id;
734
735 IF ( SQL%ROWCOUNT = 0) THEN
736 INSERT INTO bix_dm_email_sum
737 (
738 email_summary_id,
739 email_account_id,
740 classification_id,
741 period_start_date,
742 period_start_time,
743 period_start_date_time,
744 last_update_date,
745 last_updated_by,
746 creation_date,
747 created_by,
748 emails_remaining_in_queue,
749 queue_time,
750 max_queue_time,
751 emails_in_q_during_time_period,
752 oldest_message_in_queue,
753 request_id,
754 program_application_id,
755 program_id,
756 program_update_date
757 )
758 VALUES
759 (
760 bix_dm_email_sum_s.nextval,
761 q_measures.email_account_id,
762 q_measures.classification_id,
763 TRUNC(l_begin_bucket_date),
764 TO_CHAR(l_begin_bucket_date,'HH24:MI'),
765 l_begin_bucket_date,
766 SYSDATE,
767 g_user_id,
768 SYSDATE,
769 g_user_id,
770 l_emails_in_queue,
771 l_total_queue_time,
772 l_total_queue_time,
773 l_emails_queued,
777 g_program_id,
774 l_oldest_message_in_queue,
775 g_request_id,
776 g_program_appl_id,
778 SYSDATE
779 );
780 END IF;
781
782 IF(MOD(l_counter,g_commit_chunk_size)=0) THEN
783 COMMIT;
784 END IF;
785
786 l_begin_bucket_date := l_begin_bucket_date + 1/48;
787 l_temp_date := l_temp_date + 1/48;
788 END LOOP; -- End of inner While loop
789 END LOOP; -- End of Cursor FOR LOOP
790 COMMIT;
791
792
793 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
794 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_QUEUE_MEASURES: '||
795 ' Finished collecting Queue Measures');
796
797 EXCEPTION
798 WHEN OTHERS THEN
799 g_error_mesg := 'COLLECT_QUEUE_MEASURES: ERROR: '||sqlerrm;
800 clean_up;
801 RAISE;
802 END COLLECT_QUEUE_MEASURES;
803
804 /*=====================================================================================================+
805 | This procedure collects all the open related measures. The queue cursor has two parts with union. |
806 | The first part collects all the emails whIch are still Open (in Agent inbox) now. |
807 | The second part of SQL collects all the emails which was open for some time and |
808 | now they are no longer open. |
809 | |
810 | Each eamil can be sitting in agent inbox for several days. Each email translates to multiple rows |
811 | in summary table. for example if one emaiil is in agent inbox for 2 days then two rows will |
812 | be inserted one for each day with accumulated_open_emails as 1 for both days. |
813 ======================================================================================================*/
814
815 PROCEDURE collect_open_measures
816 AS
817 CURSOR open_measures IS
818 SELECT ih_mitem.media_id,
819 NVL(ih_mitem.source_id,-1) email_account_id,
820 NVL(iem_r_c.route_classification_id,-1) classification_id,
821 ih_lc_seg_typs.MILCS_CODE LC_SEGMENT,
822 NVL(ih_lc_segs_fetch.RESOURCE_ID,-999) RESOURCE_ID,
823 ih_lc_segs_fetch.START_DATE_TIME start_date_time, --open start time for this specific agent
824 g_rounded_collect_end_date end_date_time, --since it is still open at g_collect_end_time
825 min(A.start_date_time) email_start_date_time --very first time that the email was open in ANY of the agents
826 FROM JTF_IH_MEDIA_ITEMS ih_mitem,
827 JTF_IH_MEDIA_ITEM_LC_SEGS ih_lc_segs_fetch,
828 jtf_ih_media_item_lc_segs A,
829 JTF_IH_MEDIA_ITM_LC_SEG_TYS ih_lc_seg_typs,
830 jtf_ih_media_itm_lc_seg_tys B,
831 --IEM_ROUTE_CLASSIFICATIONS iem_r_c
832 --
833 --Changes for R12
834 --
835 (
836 select name, max(route_classification_id) route_classification_id
837 from iem_route_classifications
838 group by name
839 ) iem_r_c
840 WHERE ih_mitem.MEDIA_ITEM_TYPE = 'EMAIL'
841 AND ih_mitem.DIRECTION = 'INBOUND'
842 AND ih_mitem.classification = iem_r_c.name(+)
843 AND ih_mitem.MEDIA_ID = ih_lc_segs_fetch.MEDIA_ID
844 AND ih_mitem.media_id = A.media_id
845 AND ih_lc_segs_fetch.START_DATE_TIME < g_rounded_collect_end_date
846 AND A.START_DATE_TIME < g_rounded_collect_end_date
847 AND ih_lc_segs_fetch.MILCS_TYPE_ID = ih_lc_seg_typs.MILCS_TYPE_ID
848 AND A.milcs_type_id = B.milcs_type_id
849 --
850 --milcs codes by which an email ended up in agents inbox
851 --auto route goes directly to agents inbox
852 --
853 AND ih_lc_seg_typs.MILCS_CODE IN ('EMAIL_FETCH','EMAIL_TRANSFER', 'EMAIL_ASSIGNED','EMAIL_AUTO_ROUTED')
854 AND B.MILCS_CODE IN ('EMAIL_FETCH','EMAIL_TRANSFER', 'EMAIL_ASSIGNED','EMAIL_AUTO_ROUTED')
855 AND NOT EXISTS
856 (
857 SELECT 'DUMMY'
858 FROM JTF_IH_MEDIA_ITEM_LC_SEGS B,
859 JTF_IH_MEDIA_ITM_LC_SEG_TYS C
860 WHERE ih_lc_segs_fetch.MEDIA_ID = B.MEDIA_ID
861 --AND ih_lc_segs_fetch.RESOURCE_ID = B.RESOURCE_ID -- comment out for 11.5.10 features
862 AND B.MILCS_TYPE_ID = C.MILCS_TYPE_ID
863 AND C.MILCS_CODE IN ('EMAIL_REPLY','EMAIL_DELETED','EMAIL_TRANSFERRED', 'EMAIL_ESCALATED', 'EMAIL_REQUEUED',
864 'EMAIL_ASSIGN','EMAIL_REROUTED_DIFF_ACCT', 'EMAIL_REROUTED_DIFF_CLASS')
865 AND B.START_DATE_TIME > ih_lc_segs_fetch.START_DATE_TIME
866 AND B.START_DATE_TIME < g_rounded_collect_end_date
867 )
868 group by ih_mitem.media_id,
869 NVL(ih_mitem.source_id,-1),
870 NVL(iem_r_c.route_classification_id,-1),
871 ih_lc_seg_typs.MILCS_CODE,
872 NVL(ih_lc_segs_fetch.RESOURCE_ID,-999),
873 ih_lc_segs_fetch.START_DATE_TIME,
874 g_rounded_collect_end_date
875 UNION
876 SELECT a.media_id,
877 NVL(a.source_id,-1) email_account_id,
878 NVL(iem_r_c.route_classification_id,-1) classification_id,
879 Z.MILCS_CODE LC_SEGMENT,
880 NVL(Y.RESOURCE_ID,-999) RESOURCE_ID,
881 Y.START_DATE_TIME start_date_time,
882 MIN(X.START_DATE_TIME) end_date_time, --to calculate open times
883 min(Y1.start_date_time) email_start_date_time
884 FROM JTF_IH_MEDIA_ITEMS A,
885 (
886 SELECT MEDIA_ID,
887 RESOURCE_ID,
888 START_DATE_TIME
889 FROM JTF_IH_MEDIA_ITEM_LC_SEGS B,
893 'EMAIL_ASSIGN','EMAIL_REROUTED_DIFF_ACCT', 'EMAIL_REROUTED_DIFF_CLASS')
890 JTF_IH_MEDIA_ITM_LC_SEG_TYS C
891 WHERE B.MILCS_TYPE_ID = C.MILCS_TYPE_ID
892 AND C.MILCS_CODE IN ('EMAIL_REPLY','EMAIL_DELETED','EMAIL_TRANSFERRED', 'EMAIL_ESCALATED', 'EMAIL_REQUEUED',
894 AND B.START_DATE_TIME BETWEEN g_rounded_collect_start_date AND g_rounded_collect_end_date
895 ) X,
896 JTF_IH_MEDIA_ITEM_LC_SEGS Y,
897 jtf_ih_media_item_lc_segs Y1,
898 JTF_IH_MEDIA_ITM_LC_SEG_TYS Z,
899 JTF_IH_MEDIA_ITM_LC_SEG_TYS Z1,
900 --IEM_ROUTE_CLASSIFICATIONS iem_r_c
901 --
902 --Changes for R12
903 --
904 (
905 select name, max(route_classification_id) route_classification_id
906 from iem_route_classifications
907 group by name
908 ) iem_r_c
909 WHERE A.MEDIA_ID = X.MEDIA_ID
910 AND X.MEDIA_ID = Y.MEDIA_ID
911 AND X.media_id = Y1.media_id
912 --AND X.RESOURCE_ID = Y.RESOURCE_ID -- comment this out for 11.5.10
913 AND a.MEDIA_ITEM_TYPE = 'EMAIL'
914 AND a.DIRECTION = 'INBOUND'
915 AND a.classification = iem_r_c.name(+)
916 AND Y.START_DATE_TIME < g_rounded_collect_end_date
917 AND Y1.START_DATE_TIME < g_rounded_collect_end_date
918 AND Y.MILCS_TYPE_ID = Z.MILCS_TYPE_ID
919 AND Y1.MILCS_TYPE_ID = Z1.MILCS_TYPE_ID
920 AND X.START_DATE_TIME >= Y.START_DATE_TIME
921 AND X.START_DATE_TIME >= Y1.START_DATE_TIME
922 AND Z.MILCS_CODE IN ('EMAIL_FETCH','EMAIL_TRANSFER', 'EMAIL_ASSIGNED','EMAIL_ASSIGN', 'EMAIL_AUTO_ROUTED')
923 AND Z1.MILCS_CODE IN ('EMAIL_FETCH','EMAIL_TRANSFER', 'EMAIL_ASSIGNED', 'EMAIL_ASSIGN','EMAIL_AUTO_ROUTED')
924 GROUP BY
925 a.media_id,
926 NVL(a.source_id,-1),
927 NVL(iem_r_c.route_classification_id,-1),
928 Z.MILCS_CODE,
929 NVL(Y.RESOURCE_ID,-999),
930 Y.START_DATE_TIME
931 ;
932 l_temp_date DATE;
933 l_begin_bucket_date DATE;
934 l_end_bucket_date DATE;
935 l_counter NUMBER :=0;
936 l_start_date DATE;
937 l_media_id NUMBER;
938 l_next_seg_start_date DATE;
939
940 l_emails_open NUMBER;
941 l_oldest_open_message DATE;
942 l_oldest_open_age DATE;
943 l_total_open_age NUMBER;
944 l_emails_opend NUMBER;
945 l_resource_id NUMBER;
946
947 BEGIN
948
949
950 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
951 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_OPEN_MEASURES: '||
952 ' Start collecting Open Measures ');
953
954 FOR o_measures IN open_measures LOOP
955 --dbms_output.put_line('FOR LOOP media id:' || o_measures.media_id);
956
957 l_begin_bucket_date := NULL;
958 l_end_bucket_date := NULL;
959 l_temp_date := NULL;
960
961 IF(o_measures.start_date_time >= g_rounded_collect_start_date + 1/48) THEN
962 SELECT TO_DATE(TO_CHAR(o_measures.start_date_time ,'YYYY/MM/DD')||
963 LPAD(TO_CHAR(o_measures.start_date_time ,'HH24:'),3,'0')||
964 DECODE(SIGN(TO_NUMBER(TO_CHAR(o_measures.start_date_time ,'MI'))-29),
965 0,'00',1,'30',-1,'00'),'YYYY/MM/DDHH24:MI')
966 INTO l_begin_bucket_date FROM DUAL;
967 ELSE
968 l_begin_bucket_date := g_rounded_collect_start_date;
969 END IF;
970
971 --dbms_output.put_line('Begin date :'|| l_begin_bucket_date);
972
973 l_temp_date := l_begin_bucket_date + 1/48;
974
975 l_next_seg_start_date := o_measures.end_date_time;
976 l_resource_id := o_measures.resource_id;
977 l_start_date := o_measures.start_date_time;
978
979 /*
980 l_start_date := o_measures.start_date_time;
981 l_media_id := o_measures.media_id;
982 l_resource_id := o_measures.resource_id;
983
984 BEGIN
985 SELECT start_date_time INTO l_next_seg_start_date
986 FROM
987 (
988 SELECT lc_segs.start_date_time start_date_time
989 FROM jtf_ih_media_item_lc_segs lc_segs,
990 jtf_ih_media_itm_lc_seg_tys lc_seg_typs
991 WHERE lc_segs.media_id = l_media_id
992 AND lc_segs.resource_id = l_resource_id
993 AND lc_segs.start_date_time > l_start_date
994 AND lc_segs.milcs_type_id = lc_seg_typs.milcs_type_id
995 AND lc_seg_typs.milcs_code IN ('EMAIL_REPLY','EMAIL_TRANSFERRED','EMAIL_DELETED')
996 ORDER BY lc_segs.start_date_time ASC
997 )
998 WHERE ROWNUM = 1;
999 EXCEPTION
1000 WHEN NO_DATA_FOUND THEN
1001 l_next_seg_start_date := NULL;
1002 END;
1003
1004 IF ( o_measures.lc_segment = 'EMAIL_FETCH' ) THEN
1005 l_resource_id := o_measures.resource_id;
1006 END IF;
1007 */
1008
1009 /*
1010 dbms_output.put_line('Resource ID: '||
1011 l_resource_id || ' '||'
1012 end_date_time: '||
1013 l_next_seg_start_date);
1014 */
1015
1016 IF ( l_next_seg_start_date < g_rounded_collect_end_date - (( 29 * 60 + 59 )/(24*60*60)) ) THEN
1017 SELECT TO_DATE(TO_CHAR(l_next_seg_start_date ,'YYYY/MM/DD')||
1018 LPAD(TO_CHAR(l_next_seg_start_date ,'HH24:'),3,'0')||
1019 DECODE(SIGN(TO_NUMBER(TO_CHAR(l_next_seg_start_date ,'MI'))-29),
1020 0,'00',1,'30',-1,'00'),'YYYY/MM/DDHH24:MI')
1021 INTO l_end_bucket_date FROM DUAL;
1022 ELSE
1023 l_end_bucket_date := g_rounded_collect_end_date - (( 29 * 60 + 59 )/(24*60*60));
1024 END IF;
1025
1029 l_emails_open := 0;
1026 --dbms_output.put_line('End date:'|| l_end_bucket_date);
1027
1028 WHILE(l_temp_date <= l_end_bucket_date + 1/48) LOOP
1030 l_oldest_open_message := NULL;
1031 l_oldest_open_age := NULL;
1032 l_total_open_age := 0;
1033 l_emails_opend := 0;
1034
1035 IF(l_next_seg_start_date BETWEEN l_begin_bucket_date AND l_temp_date
1036 AND l_next_seg_start_date <> g_rounded_collect_end_date ) THEN
1037 l_total_open_age := (l_next_seg_start_date - o_measures.start_date_time ) * 24 * 60 * 60;
1038 l_emails_opend := 1;
1039 ELSE
1040 l_total_open_age := (l_temp_date - o_measures.start_date_time) * 24 * 60 * 60;
1041 l_emails_opend := 1;
1042 l_emails_open := 1;
1043 l_oldest_open_message := o_measures.start_date_time;
1044 l_oldest_open_age := o_measures.email_start_date_time;
1045 END IF;
1046
1047 /*
1048 dbms_output.put_line('Open Age:'|| l_total_open_age/60 ||
1049 'Oldest Open Message :'|| l_oldest_open_message);
1050
1051 dbms_output.put_line('Total: '||l_resource_id||' '||l_begin_bucket_date||' '||
1052 o_measures.email_account_id||' '|| o_measures.classification_id||' '
1053 ||l_emails_open||' '||round(l_total_open_age/60)|| ' '||
1054 l_emails_opend || ' '|| l_oldest_open_message);
1055 */
1056
1057 UPDATE bix_dm_email_agent_sum
1058 SET emails_open = NVL(emails_open,0) + l_emails_open,
1059 open_age = NVL(open_age,0) + l_total_open_age,
1060 emails_open_during_time_period = NVL(emails_open_during_time_period,0) + l_emails_opend,
1061 oldest_open_message = DECODE(l_oldest_open_message,NULL,oldest_open_message,
1062 DECODE(oldest_open_message,NULL,l_oldest_open_message,
1063 DECODE(SIGN(oldest_open_message - l_oldest_open_message),0,
1064 oldest_open_message,-1,oldest_open_message,1,
1065 l_oldest_open_message))),
1066 oldest_open_age = DECODE(l_oldest_open_age,NULL,oldest_open_age,
1067 DECODE(oldest_open_age,NULL,l_oldest_open_age,
1068 DECODE(SIGN(oldest_open_age - l_oldest_open_age),0,
1069 oldest_open_age,-1,oldest_open_age,1,
1070 l_oldest_open_age))),
1071 last_update_date = SYSDATE,
1072 last_updated_by = g_user_id
1073 WHERE period_start_date_time = l_begin_bucket_date
1074 AND email_account_id = o_measures.email_account_id
1075 AND classification_id = o_measures.classification_id
1076 AND resource_id = l_resource_id;
1077
1078 IF ( SQL%ROWCOUNT = 0) THEN
1079 INSERT INTO bix_dm_email_agent_sum
1080 (
1081 email_agent_summary_id,
1082 email_account_id,
1083 classification_id,
1084 resource_id,
1085 period_start_date,
1086 period_start_time,
1087 period_start_date_time,
1088 last_update_date,
1089 last_updated_by,
1090 creation_date,
1091 created_by,
1092 emails_open,
1093 oldest_open_message,
1094 oldest_open_age,
1095 open_age,
1096 emails_open_during_time_period,
1097 request_id,
1098 program_application_id,
1099 program_id,
1100 program_update_date
1101 )
1102 VALUES
1103 (
1104 bix_dm_email_sum_s.nextval,
1105 o_measures.email_account_id,
1106 o_measures.classification_id,
1107 NVL(l_resource_id,-999),
1108 TRUNC(l_begin_bucket_date),
1109 TO_CHAR(l_begin_bucket_date,'HH24:MI'),
1110 l_begin_bucket_date,
1111 SYSDATE,
1112 g_user_id,
1113 SYSDATE,
1114 g_user_id,
1115 l_emails_open,
1116 l_oldest_open_message,
1117 l_oldest_open_age,
1118 l_total_open_age,
1119 l_emails_opend,
1120 g_request_id,
1121 g_program_appl_id,
1122 g_program_id,
1123 SYSDATE
1124 );
1125 END IF;
1126
1127 l_counter := l_counter + 1;
1128
1129 IF(MOD(l_counter,g_commit_chunk_size)=0) THEN
1130 COMMIT;
1131 END IF;
1132
1133 l_begin_bucket_date := l_begin_bucket_date + 1/48;
1134 l_temp_date := l_temp_date + 1/48;
1135 END LOOP; -- End of inner While loop
1136 END LOOP; -- End of Cursor for loop
1137 COMMIT;
1138
1139 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1140 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_OPEN_MEASURES: '||
1141 'Finished Collecting Open Measures ');
1142
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 g_error_mesg := 'COLLECT_OPEN_MEASURES: ERROR: '||sqlerrm;
1146 clean_up;
1147 RAISE;
1148 END collect_open_measures;
1149
1150
1151 /*====================================================================+
1152 | This procedure summarizes all the agent information to the groups. |
1153 | This procedure summarizes the agent information |
1154 | to all the groups in the heirarchy till the root group. |
1155 ======================================================================*/
1156
1157 PROCEDURE collect_group_summary
1158 AS
1159 CURSOR group_email_sum
1160 IS
1161 SELECT
1162 EMAIL_ACCOUNT_ID,
1163 CLASSIFICATION_ID,
1164 group_denorm.parent_group_id GROUP_ID,
1165 PERIOD_START_DATE,
1166 PERIOD_START_TIME,
1170 SUM(EMAILS_REPLIED_WITHIN_GOAL) emails_replied_within_goal,
1167 PERIOD_START_DATE_TIME,
1168 SUM(EMAILS_FETCHED) emails_fetched,
1169 SUM(EMAILS_REPLIED) emails_replied,
1171 SUM(EMAILS_DELETED) emails_deleted,
1172 SUM(EMAILS_TRANSFERRED) emails_transferred,
1173 SUM(EMAILS_OPEN) emails_open,
1174 MIN(OLDEST_OPEN_MESSAGE) oldest_open_message,
1175 MIN(OLDEST_OPEN_AGE) oldest_open_age,
1176 SUM(OPEN_AGE) open_age,
1177 SUM(EMAILS_OPEN_DURING_TIME_PERIOD) emails_open_during_time_period,
1178 SUM(EMC_RESPONSE_TIME) emc_response_time,
1179 SUM(COMPLETE_AGENT_RESPONSE_TIME) complete_agent_response_time
1180 FROM bix_dm_email_agent_sum agt_sum,
1181 jtf_rs_group_members groups,
1182 jtf_rs_groups_denorm group_denorm
1183 WHERE agt_sum.period_start_date_time BETWEEN g_rounded_collect_start_date AND g_rounded_collect_end_date
1184 AND agt_sum.resource_id = groups.resource_id
1185 AND groups.group_id = group_denorm.group_id
1186 AND NVL(groups.delete_flag,'N') <> 'Y'
1187 AND agt_sum.period_start_date_time BETWEEN NVL(group_denorm.start_date_active,agt_sum.period_start_date_time)
1188 AND NVL(group_denorm.end_date_active,SYSDATE)
1189 AND groups.group_member_id =
1190 (select max(mem1.group_member_id)
1191 from jtf_rs_group_members mem1
1192 where mem1.group_id in
1193 (select den1.group_id
1194 from jtf_rs_groups_denorm den1
1195 where den1.parent_group_id = group_denorm.parent_group_id
1196 AND agt_sum.period_start_date_time BETWEEN
1197 NVL(den1.start_date_active,agt_sum.period_start_date_time)
1198 AND NVL(den1.end_date_active,SYSDATE))
1199 and mem1.resource_id = groups.resource_id
1200 and nvl(mem1.delete_flag,'N') <> 'Y')
1201 GROUP BY EMAIL_ACCOUNT_ID,
1202 CLASSIFICATION_ID,
1203 group_denorm.parent_group_id,
1204 PERIOD_START_DATE,
1205 PERIOD_START_TIME,
1206 PERIOD_START_DATE_TIME;
1207
1208 l_counter NUMBER := 0;
1209
1210 BEGIN
1211 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1212 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||
1213 ' Start collecting information into Agent Group Email summary table');
1214
1215 /* Delete the rows from Group summary table for the given date range and re collect the rows from
1216 Agent summary table.
1217 */
1218
1219
1220 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1221 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||
1222 ' Start Deleting the rows from Agent Group Email summary table ');
1223
1224 LOOP
1225 DELETE bix_dm_email_group_sum
1226 WHERE period_start_date_time BETWEEN g_rounded_collect_start_date
1227 AND g_rounded_collect_end_date
1228 AND rownum <= g_commit_chunk_size ;
1229
1230 l_counter:= l_counter + SQL%ROWCOUNT;
1231
1232 IF(SQL%ROWCOUNT < g_commit_chunk_size) THEN
1233 COMMIT;
1234 EXIT;
1235 ELSE
1236 COMMIT;
1237 END IF;
1238 END LOOP;
1239
1240 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1241 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||
1242 ' finished Deleting the rows from Agent Group Email summary table '||
1243 'Row Count: '|| l_counter);
1244
1245 l_counter := 0;
1246
1247 FOR group_emails IN group_email_sum LOOP
1248
1249 INSERT INTO bix_dm_email_group_sum
1250 (
1251 email_group_summary_id,
1252 email_account_id,
1253 classification_id,
1254 group_id,
1255 period_start_date,
1256 period_start_time,
1257 period_start_date_time,
1258 last_update_date,
1259 last_updated_by,
1260 creation_date,
1261 created_by,
1262 emails_fetched,
1263 emails_replied,
1264 emails_replied_within_goal,
1265 emails_deleted,
1266 emails_transferred,
1267 emails_open,
1268 oldest_open_message,
1269 oldest_open_age,
1270 open_age,
1271 emails_open_during_time_period,
1272 emc_response_time,
1273 complete_agent_response_time,
1274 request_id,
1275 program_application_id,
1276 program_id,
1277 program_update_date
1278 )
1279 VALUES
1280 (
1281 bix_dm_email_group_sum_s.nextval,
1282 group_emails.email_account_id,
1283 group_emails.classification_id,
1284 group_emails.group_id,
1285 group_emails.period_start_date,
1286 group_emails.period_start_time,
1287 group_emails.period_start_date_time,
1288 SYSDATE,
1289 g_user_id,
1290 SYSDATE,
1291 g_user_id,
1292 group_emails.emails_fetched,
1293 group_emails.emails_replied,
1294 group_emails.emails_replied_within_goal,
1295 group_emails.emails_deleted,
1296 group_emails.emails_transferred,
1297 group_emails.emails_open,
1298 group_emails.oldest_open_message,
1299 group_emails.oldest_open_age,
1300 group_emails.open_age,
1301 group_emails.emails_open_during_time_period,
1302 group_emails.emc_response_time,
1303 group_emails.complete_agent_response_time,
1304 g_request_id,
1305 g_program_appl_id,
1306 g_program_id,
1307 SYSDATE
1308 );
1312 COMMIT;
1309 l_counter := l_counter + 1;
1310
1311 IF(MOD(l_counter,g_commit_chunk_size)=0) THEN
1313 END IF;
1314
1315 END LOOP; -- End of cursor for loop
1316 COMMIT;
1317
1318 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1319 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||
1320 ' finished collecting information into Agent Group Email summary table ' ||
1321 'Row Count: '|| l_counter );
1322
1323 EXCEPTION
1324 WHEN OTHERS THEN
1325 g_error_mesg := 'COLLECT_GROUP_SUMMRY: ERROR: '||SQLERRM;
1326 clean_up;
1327 RAISE;
1328 END collect_group_summary;
1329
1330
1331 /*====================================================================+
1332 | In this procedure BIX_DM_EMAIL_AGENT_SUM is summarized to all the |
1333 | dimensions other than resource and update the BIX_DM_EMAIL_SUM table|
1334 ======================================================================*/
1335
1336 PROCEDURE collect_agent_summary
1337 AS
1338 CURSOR agent_email_sum
1339 IS
1340 SELECT
1341 --
1342 --Just summarize everthing by dimensions EXCEPT resource_id
1343 --
1344 EMAIL_ACCOUNT_ID,
1345 CLASSIFICATION_ID,
1346 PERIOD_START_DATE,
1347 PERIOD_START_TIME,
1348 PERIOD_START_DATE_TIME,
1349 SUM(EMAILS_FETCHED) emails_fetched,
1350 SUM(EMAILS_REPLIED) emails_replied,
1351 SUM(EMAILS_REPLIED_WITHIN_GOAL) emails_replied_within_goal,
1352 SUM(EMAILS_DELETED) emails_deleted,
1353 SUM(EMAILS_TRANSFERRED) emails_transferred,
1354 SUM(EMAILS_OPEN) emails_open,
1355 MIN(OLDEST_OPEN_MESSAGE) oldest_open_message,
1356 MIN(OLDEST_OPEN_AGE) oldest_open_age,
1357 SUM(OPEN_AGE) open_age,
1358 SUM(EMAILS_OPEN_DURING_TIME_PERIOD) emails_open_during_time_period,
1359 SUM(EMC_RESPONSE_TIME) emc_response_time,
1360 SUM(COMPLETE_AGENT_RESPONSE_TIME) complete_agent_response_time
1361 FROM bix_dm_email_agent_sum agt_sum
1362 WHERE agt_sum.period_start_date_time BETWEEN g_rounded_collect_start_date AND g_rounded_collect_end_date
1363 GROUP BY EMAIL_ACCOUNT_ID,
1364 CLASSIFICATION_ID,
1365 PERIOD_START_DATE,
1366 PERIOD_START_TIME,
1367 PERIOD_START_DATE_TIME;
1368
1369 l_counter NUMBER := 0;
1370
1371 BEGIN
1372 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1373 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_AGENT_SUMMARY: '||
1374 ' Start updating agent information into Email summary table');
1375
1376 l_counter := l_counter + 1;
1377
1378 FOR agent_sum IN agent_email_sum LOOP
1379
1380
1381 UPDATE bix_dm_email_sum
1382 SET emails_fetched = agent_sum.emails_fetched,
1383 emails_replied = agent_sum.emails_replied,
1384 emails_replied_within_goal = agent_sum.emails_replied_within_goal,
1385 emails_deleted = agent_sum.emails_deleted,
1386 emails_transferred = agent_sum.emails_transferred,
1387 emails_open = agent_sum.emails_open,
1388 oldest_open_message = agent_sum.oldest_open_message,
1389 oldest_open_age = agent_sum.oldest_open_age,
1390 open_age = agent_sum.open_age,
1391 emails_open_during_time_period = agent_sum.emails_open_during_time_period,
1392 emc_response_time = agent_sum.emc_response_time,
1393 complete_agent_response_time = agent_sum.complete_agent_response_time
1394 WHERE email_account_id = agent_sum.email_account_id
1395 AND classification_id = agent_sum.classification_id
1396 AND period_start_date_time = agent_sum.period_start_date_time;
1397
1398 IF(SQL%ROWCOUNT = 0) THEN
1399
1400 INSERT INTO bix_dm_email_sum
1401 (
1402 email_summary_id,
1403 email_account_id,
1404 classification_id,
1405 period_start_date,
1406 period_start_time,
1407 period_start_date_time,
1408 last_update_date,
1409 last_updated_by,
1410 creation_date,
1411 created_by,
1412 emails_fetched,
1413 emails_replied,
1414 emails_replied_within_goal,
1415 emails_deleted,
1416 emails_transferred,
1417 emails_open,
1418 oldest_open_message,
1419 oldest_open_age,
1420 open_age,
1421 emails_open_during_time_period,
1422 emc_response_time,
1423 complete_agent_response_time,
1424 request_id,
1425 program_application_id,
1426 program_id,
1427 program_update_date
1428 )
1429 VALUES
1430 (
1431 bix_dm_email_sum_s.nextval,
1432 agent_sum.email_account_id,
1433 agent_sum.classification_id,
1434 agent_sum.period_start_date,
1435 agent_sum.period_start_time,
1436 agent_sum.period_start_date_time,
1437 SYSDATE,
1438 g_user_id,
1439 SYSDATE,
1440 g_user_id,
1441 agent_sum.emails_fetched,
1442 agent_sum.emails_replied,
1443 agent_sum.emails_replied_within_goal,
1444 agent_sum.emails_deleted,
1445 agent_sum.emails_transferred,
1446 agent_sum.emails_open,
1447 agent_sum.oldest_open_message,
1448 agent_sum.oldest_open_age,
1449 agent_sum.open_age,
1450 agent_sum.emails_open_during_time_period,
1451 agent_sum.emc_response_time,
1452 agent_sum.complete_agent_response_time,
1453 g_request_id,
1454 g_program_appl_id,
1455 g_program_id,
1456 SYSDATE
1457 );
1458 l_counter := l_counter + 1;
1459 END IF;
1463
1460 IF(MOD(l_counter,g_commit_chunk_size)=0) THEN
1461 COMMIT;
1462 END IF;
1464 END LOOP; -- End of cursor for loop
1465 COMMIT;
1466
1467 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1468 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||
1469 ' finished updating agent information into Email summary table ' );
1470
1471 EXCEPTION
1472 WHEN OTHERS THEN
1473 g_error_mesg := 'COLLECT_AGENT_SUMMRY: ERROR: '||SQLERRM;
1474 clean_up;
1475 RAISE;
1476 END collect_agent_summary;
1477
1478 /*==============================================================================================+
1479 | COLLECT_EMAILS_SUMMARY procedure is main procedure calls other procedures to collect the data |
1480 | The following procedures are invoked from this procedure to collect the data. |
1481 | 1. COLLECT_EMAILS : Which collects all the emails related information |
1482 | 2. COLLECT_QUEUE_MEASURES : Collects Queue measures |
1483 | 3. COLLECT_OPEN_MEASURES : Collects Open measures |
1484 | 4. COLLECT_GROUP_SUMMARY : summarizes all the agent information to group |
1485 | 5. COLLECT_AGENT_SUMMARY : summarizes all the agent inforation to all the dimension other |
1486 | than resource and update the email table itself |
1487 | 5. INSERT_LOG : Insert the status into BIX_DM_COLLECT_LOG table. |
1488 ==============================================================================================+*/
1489
1490 PROCEDURE COLLECT_EMAILS_SUMMARY(p_start_date IN VARCHAR2, p_end_date IN VARCHAR2)
1491 AS
1492 l_emc_installed VARCHAR2(1);
1493
1494 BEGIN
1495
1496 l_emc_installed := is_emc_installed();
1497
1498 IF( l_emc_installed = 'Y') THEN
1499
1500 /* intialize all global variables */
1501
1502 g_request_id := FND_GLOBAL.CONC_REQUEST_ID();
1503 g_program_appl_id := FND_GLOBAL.PROG_APPL_ID();
1504 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
1505 g_user_id := FND_GLOBAL.USER_ID();
1506 g_program_start_date := SYSDATE;
1507 g_error_mesg := NULL;
1508 g_status := 'FAILED';
1509 g_proc_name := 'BIX_DM_EMAIL_SUMMARY_PKG';
1510 g_commit_chunk_size := 5;
1511
1512 g_collect_start_date := TO_DATE(p_start_date,'YYYY/MM/DD HH24:MI:SS');
1513 g_collect_end_date := TO_DATE(p_end_date,'YYYY/MM/DD HH24:MI:SS');
1514
1515 IF (g_collect_start_date > SYSDATE) THEN
1516 g_collect_start_date := SYSDATE;
1517 END IF;
1518
1519 IF (g_collect_end_date > SYSDATE) THEN
1520 g_collect_end_date := SYSDATE;
1521 END IF;
1522
1523 IF (g_collect_start_date > g_collect_end_date) THEN
1524 RAISE G_DATE_MISMATCH;
1525 END IF;
1526
1527 /*
1528 Round the Collection start date nearest lower time bucket.
1529 ex: if time is between 10:00 and 10:29 round it to 10:00.
1530 */
1531
1532 SELECT TO_DATE(
1533 TO_CHAR(g_collect_start_date,'YYYY/MM/DD')||
1534 LPAD(TO_CHAR(g_collect_start_date,'HH24:'),3,'0')||
1535 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_start_date,'MI'))-29),0,'00:00',1,'30:00',-1,'00:00'),
1536 'YYYY/MM/DDHH24:MI:SS')
1537 INTO g_rounded_collect_start_date
1538 FROM DUAL;
1539
1540 /*
1541 Round the Collection end date to nearest higher time bucket.
1542 ex: if time is between 10:00 and 10:29 round it to 10:29:59
1543 */
1544
1545 SELECT TO_DATE(
1546 TO_CHAR(g_collect_end_date,'YYYY/MM/DD')||
1547 LPAD(TO_CHAR(g_collect_end_date,'HH24:'),3,'0')||
1548 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_end_date,'MI'))-29),0,'29:59',1,'59:59',-1,'29:59'),
1549 'YYYY/MM/DDHH24:MI:SS')
1550 INTO g_rounded_collect_end_date
1551 FROM DUAL;
1552
1553
1554 /*
1555 dbms_output.put_line('Collection End Date: ' ||
1556 to_char(g_rounded_collect_end_date,'YYYY/MM/DD HH24:MI:SS'));
1557 */
1558
1559 /*
1560 Get the commit size from the profile value.
1561 if the profile is not defined assume commit size as 100
1562 */
1563
1564 /*
1565 IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
1566 g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
1567 ELSE
1568 g_commit_chunk_size := 100;
1569 END IF;
1570 */
1571
1572 IF (FND_PROFILE.DEFINED('BIX_DBI_DEBUG')) THEN
1573 g_debug_flag := nvl(FND_PROFILE.VALUE('BIX_DBI_DEBUG'), 'N');
1574 END IF;
1575
1576 --dbms_output.put_line('Commit SIZE: '|| g_commit_chunk_size);
1577
1578 /* Procedure collects all the Email measures information from JTF_IH tables */
1579
1580 --dbms_output.put_line('before COLLECT_EMAILS');
1581
1582 collect_emails;
1583
1584 --dbms_output.put_line('After COLLECT_EMAILS');
1585
1586 /* Collect Queue measures */
1587
1588 collect_queue_measures;
1589
1590 /* Collect Open Email measures */
1591
1592 --dbms_output.put_line('Before COLLECT_OPEN_MEASURES');
1593
1594 collect_open_measures;
1595
1596 --dbms_output.put_line('After COLLECT_OPEN_MEASURES');
1597
1598 /* this procedure summarises agent related measures by resource group which they belong to */
1599
1600 collect_group_summary;
1601
1605 collect_agent_summary;
1602 /* Summarise all the measures in the agent table by all the dimensions
1603 except agent and update in email summary table */
1604
1606
1607 /* Insert the status into BIX_DM_COLLECT_LOG table */
1608
1609 g_status := 'SUCCESS';
1610 insert_log;
1611 ELSE
1612 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1613 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS_SUMMARY : ' ||
1614 'Email Application is not installed. ');
1615 END IF;
1616 EXCEPTION
1617 WHEN G_DATE_MISMATCH THEN
1618 write_log('Collect Start Date cannot be greater than collection end date');
1619 WHEN OTHERS THEN
1620 IF ( g_error_mesg IS NULL) THEN
1621 g_error_mesg := 'COLLECT_EMAILS_SUMMRY: ERROR: '||SQLERRM;
1622 clean_up;
1623 RAISE;
1624 ELSE
1625 RAISE;
1626 END IF;
1627 END COLLECT_EMAILS_SUMMARY;
1628
1629 /*==============================================================================================+
1630 | COLLECT_EMAILS_SUMMARY procedure is main procedure calls other procedures to collect the data |
1631 | The following procedures are invoked from this procedure to collect the data. |
1632 | 1. COLLECT_EMAILS : Which collects all the emails related information |
1633 | 2. COLLECT_QUEUE_MEASURES : Collects Queue measures |
1634 | 3. COLLECT_OPEN_MEASURES : Collects Open measures |
1635 | 4. COLLECT_GROUP_SUMMARY : summarizes all the agent information to group |
1636 | 5. COLLECT_AGENT_SUMMARY : summarizes all the agent inforation to all the dimension other |
1637 | than resource and update the email table itself |
1638 | 5. INSERT_LOG : Insert the status into BIX_DM_COLLECT_LOG table. |
1642 PROCEDURE COLLECT_EMAILS_SUMMARY(errbuf out nocopy varchar2,
1639 ==============================================================================================+*/
1640
1641
1643 retcode out nocopy varchar2,
1644 p_start_date IN VARCHAR2,
1645 p_end_date IN VARCHAR2)
1646 AS
1647 l_emc_installed VARCHAR2(1);
1648
1649 BEGIN
1650
1651 /* Check weather EMC Application is installed or not */
1652
1653 l_emc_installed := is_emc_installed();
1654
1655 IF( l_emc_installed = 'Y') THEN
1656
1657 /* intialize all global variables */
1658
1659 g_request_id := FND_GLOBAL.CONC_REQUEST_ID();
1660 g_program_appl_id := FND_GLOBAL.PROG_APPL_ID();
1661 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
1662 g_user_id := FND_GLOBAL.USER_ID();
1663 g_program_start_date := SYSDATE;
1664 g_error_mesg := NULL;
1665 g_status := 'FAILED';
1666 g_proc_name := 'BIX_DM_EMAIL_SUMMARY_PKG';
1667 g_commit_chunk_size := 100;
1668
1669 g_collect_start_date := TO_DATE(p_start_date,'YYYY/MM/DD HH24:MI:SS');
1670 g_collect_end_date := TO_DATE(p_end_date,'YYYY/MM/DD HH24:MI:SS');
1671
1672 IF (g_collect_start_date > SYSDATE) THEN
1673 g_collect_start_date := SYSDATE;
1674 END IF;
1675
1676 IF (g_collect_end_date > SYSDATE) THEN
1677 g_collect_end_date := SYSDATE;
1678 END IF;
1679
1680 IF (g_collect_start_date > g_collect_end_date) THEN
1681 RAISE G_DATE_MISMATCH;
1682 END IF;
1683
1684 /*
1685 Round the Collection start date nearest lower time bucket.
1686 ex: if time is between 10:00 and 10:29 round it to 10:00.
1687 */
1688
1689 SELECT TO_DATE(
1690 TO_CHAR(g_collect_start_date,'YYYY/MM/DD')||
1691 LPAD(TO_CHAR(g_collect_start_date,'HH24:'),3,'0')||
1692 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_start_date,'MI'))-29),0,'00:00',1,'30:00',-1,'00:00'),
1693 'YYYY/MM/DDHH24:MI:SS')
1694 INTO g_rounded_collect_start_date
1695 FROM DUAL;
1696
1697 /*
1698 Round the Collection end date to nearest higher time bucket.
1699 ex: if time is between 10:00 and 10:29 round it to 10:29:59
1700 */
1701
1702 SELECT TO_DATE(
1703 TO_CHAR(g_collect_end_date,'YYYY/MM/DD')||
1704 LPAD(TO_CHAR(g_collect_end_date,'HH24:'),3,'0')||
1705 DECODE(SIGN(TO_NUMBER(TO_CHAR(g_collect_end_date,'MI'))-29),0,'29:59',1,'59:59',-1,'29:59'),
1706 'YYYY/MM/DDHH24:MI:SS')
1707 INTO g_rounded_collect_end_date
1708 FROM DUAL;
1709
1710 /*
1711 dbms_output.put_line('Collection End Date: ' ||
1712 to_char(g_rounded_collect_end_date,'YYYY/MM/DD HH24:MI:SS'));
1713 */
1714
1715 /*
1716 Get the commit size from the profile value.
1717 if the profile is not defined assume commit size as 100
1718 */
1719
1720
1721 IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
1722 g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
1723 ELSE
1724 g_commit_chunk_size := 100;
1725 END IF;
1726
1727
1728 IF (FND_PROFILE.DEFINED('BIX_DBI_DEBUG')) THEN
1729 g_debug_flag := nvl(FND_PROFILE.VALUE('BIX_DBI_DEBUG'), 'N');
1730 END IF;
1731
1732 --dbms_output.put_line('Commit SIZE: '|| g_commit_chunk_size);
1733
1734 /* Procedure collects all the Email measures information from JTF_IH tables */
1735
1736 --dbms_output.put_line('before COLLECT_EMAILS');
1737
1738 collect_emails;
1739
1740 --dbms_output.put_line('After COLLECT_EMAILS');
1741
1742 /* Collect Queue measures */
1743
1744 collect_queue_measures;
1745
1746 /* Collect Open Email measures */
1747
1748 --dbms_output.put_line('Before COLLECT_OPEN_MEASURES');
1749
1750 collect_open_measures;
1751
1752 --dbms_output.put_line('After COLLECT_OPEN_MEASURES');
1753
1754 /* this procedure summarises agent related measures by resource group which they belong to */
1755
1756 collect_group_summary;
1757
1758 /* Summarise all the measures in the agent table by all the dimensions
1759 except agent and update in email summary table */
1760
1761 collect_agent_summary;
1762
1763 /* Insert the status into BIX_DM_COLLECT_LOG table */
1764
1765 g_status := 'SUCCESS';
1766 insert_log;
1767 retcode := NULL;
1768 errbuf := NULL;
1769 ELSE
1770 write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1771 ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS_SUMMARY : ' ||
1772 'Email Application is not installed. ');
1773 END IF;
1774
1775 EXCEPTION
1776 WHEN G_DATE_MISMATCH THEN
1777 retcode := -1;
1778 errbuf := 'Collect Start Date cannot be greater than collection end date';
1779 write_log('Collect Start Date cannot be greater than collection end date');
1780 WHEN OTHERS THEN
1781 IF ( g_error_mesg IS NULL) THEN
1782 retcode := SQLCODE;
1783 errbuf := SQLERRM;
1784 g_error_mesg := 'COLLECT_EMAILS_SUMMARY: ERROR: '||SQLERRM;
1785 clean_up;
1786 RAISE;
1787 ELSE
1788 RAISE;
1789 END IF;
1790 END COLLECT_EMAILS_SUMMARY;
1791
1792 END BIX_DM_EMAIL_SUMMARY_PKG;