[Home] [Help]
PACKAGE BODY: APPS.BIX_EMAILS_LOAD_PKG
Source
1 PACKAGE BODY BIX_EMAILS_LOAD_PKG AS
2 /*$Header: bixemllb.plb 120.6 2006/08/04 11:09:38 pubalasu 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_collect_start_date DATE;
9 g_collect_end_date DATE;
10 g_commit_chunk_size NUMBER;
11 g_rows_ins_upd NUMBER;
12 g_sysdate DATE;
13 g_bix_schema VARCHAR2(30) := 'BIX';
14 g_debug_flag VARCHAR2(1) := 'N';
15
16 G_TIME_DIM_MISSING EXCEPTION;
17
18 TYPE g_media_id_tab IS TABLE OF jtf_ih_media_items.media_id%TYPE;
19 TYPE g_email_account_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
20 TYPE g_email_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
21 TYPE g_resource_id_tab IS TABLE OF bix_email_details_f.agent_id%TYPE;
22 TYPE g_party_id_tab IS TABLE OF bix_email_details_f.party_id%TYPE;
23 TYPE g_start_date_time_tab IS TABLE OF jtf_ih_media_item_lc_segs.start_date_time%TYPE;
24 TYPE g_end_date_time_tab IS TABLE OF jtf_ih_media_item_lc_segs.end_date_time%TYPE;
25 TYPE g_media_start_date_time_tab IS TABLE OF jtf_ih_media_items.start_date_time%TYPE;
26 TYPE g_period_start_date_tab IS TABLE OF bix_email_details_f.period_start_date%TYPE;
27
28 TYPE g_emails_open_tab IS TABLE OF bix_email_details_f.accumulated_open_emails%TYPE;
29 TYPE g_total_open_age_tab IS TABLE OF bix_email_details_f.accumulated_open_age%TYPE;
30 TYPE g_oldest_open_message_tab IS TABLE OF bix_email_details_f.oldest_email_open_date%TYPE;
31 TYPE g_emails_in_queue_tab IS TABLE OF bix_email_details_f.accumulated_emails_in_queue%TYPE;
32 TYPE g_total_queue_time_tab IS TABLE OF bix_email_details_f.accumulated_queue_time%TYPE;
33 TYPE g_oldest_message_in_queue_tab IS TABLE OF bix_email_details_f.oldest_email_queue_date%TYPE;
34 TYPE g_acc_emails_one_day_tab IS TABLE OF bix_email_details_f.accumulated_emails_one_day%TYPE;
35 TYPE g_acc_emails_three_days_tab IS TABLE OF bix_email_details_f.accumulated_emails_three_days%TYPE;
36 TYPE g_acc_emails_week_tab IS TABLE OF bix_email_details_f.accumulated_emails_week%TYPE;
37 TYPE g_acc_emails_week_plus_tab IS TABLE OF bix_email_details_f.accumulated_emails_week_plus%TYPE;
38
39 PROCEDURE Write_Log (p_msg IN VARCHAR2) IS
40 BEGIN
41
42 IF (g_debug_flag = 'Y') THEN
43 BIS_COLLECTION_UTILITIES.log(p_msg);
44
45 END IF;
46 EXCEPTION
47 WHEN OTHERS THEN
48 RAISE;
49 END Write_Log;
50
51 PROCEDURE truncate_table (p_table_name in varchar2) is
52
53 l_stmt varchar2(400);
54 BEGIN
55
56 write_log('Start of the procedure truncate_table at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
57
58 l_stmt:='truncate table '||g_bix_schema||'.'|| p_table_name;
59 execute immediate l_stmt;
60
61 write_log('Table ' || p_table_name || ' has been truncated');
62
63 write_log('Finished procedure truncate_table at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
64 EXCEPTION
65 WHEN OTHERS THEN
66 write_log('Error in truncate_table : Error : ' || sqlerrm);
67 RAISE;
68 END truncate_table;
69
70 PROCEDURE init
71 IS
72
73 l_status VARCHAR2(30);
74 l_industry VARCHAR2(30);
75 BEGIN
76
77 IF (BIS_COLLECTION_UTILITIES.SETUP('BIX_EMAIL_DETAILS_F') = FALSE) THEN
78 RAISE_APPLICATION_ERROR(-20000, 'BIS_COLLECTION_UTILITIES.setup has failed');
79 END IF;
80
81 write_log('Start of the procedure init at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
82
83 write_log('Initializing global variables');
84
85 g_request_id := FND_GLOBAL.CONC_REQUEST_ID();
86 g_program_appl_id := FND_GLOBAL.PROG_APPL_ID();
87 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
88 g_user_id := FND_GLOBAL.USER_ID();
89 g_sysdate := SYSDATE;
90 g_commit_chunk_size := 10000;
91 g_rows_ins_upd := 0;
92
93 write_log('Getting Commit Size');
94
95 IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
96 g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
97 END IF;
98
99 write_log('Commit SIZE : ' || g_commit_chunk_size);
100
101 write_log('Getting Debug Information');
102
103 IF (FND_PROFILE.DEFINED('BIX_DBI_DEBUG')) THEN
104 g_debug_flag := nvl(FND_PROFILE.VALUE('BIX_DBI_DEBUG'), 'N');
105 END IF;
106
107 write_log('Debug Flag : ' || g_debug_flag);
108
109 write_log('Getting schema information');
110
111 IF(FND_INSTALLATION.GET_APP_INFO('BIX', l_status, l_industry, g_bix_schema)) THEN
112 NULL;
113 END IF;
114
115 write_log('BIX Schema : ' || g_bix_schema);
116
117 write_log('Truncating the table bix_email_details_f and bix_interactions_temp');
118 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIX_EMAIL_DETAILS_F');
119 Truncate_Table('BIX_EMAIL_DETAILS_F');
120 Truncate_Table('BIX_INTERACTIONS_TEMP');
121 write_log('Done truncating the table bix_email_details_f and bix_interactions_temp');
122
123 write_log('Setting the sore and hash are size');
124 execute immediate 'alter session set sort_area_size=104857600';
125 execute immediate 'alter session set hash_area_size=104857600';
126
127 write_log('Finished procedure init at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
128
129 EXCEPTION
130 WHEN OTHERS THEN
131 write_log('Error in init : Error : ' || sqlerrm);
132 RAISE;
133 END init;
134
135
136 /* This procedure collects One, two , three and Four done resolutiions. Email center keeps the threads of email
137 interactions in jtf_ih_interaction_inters table. If the customer reply back to the agent response then the entry will
138 be created in the above table with old and new interaction. This table stores both parent and child interaction id.
139
140 We can find the depth of thread from this table. If the table does not have entry in this
141 table for replied email, then the email interaction is one and done ,
142 if the depth is 1 then it is two and done and so on.
143
144 jtf_ih_interaction_inters:
145 This table gets an entry the moment the customer replies to an email. If it doesnt have an entry it
146 means it is ONE AND DONE provided there is a reply.
147
148 INTERACT_INTERACTION_IDRELATES is the parent interaction
149 INTERACT_INTERACTION_ID is the child interaction
150
151 START WITH and CONNECT BY are used to determine the depth. The depth is needed
152 for 2-done, 3-done, 4-done. It is not needed for 1-done.
153
154 In reports, only ONE_DONE is used - other DONES are for future use.
155
156 */
157
158
159
160 PROCEDURE collect_resolutions IS
161
162 CURSOR all_root_interactions IS
163 SELECT /*+ ordered full(intr) full(actv) full(mitm) use_hash(mitm,actv,intr,) +*/
164 intr.interaction_id interaction_id,
165 max(nvl(intr.resource_id, -1)) resource_id,
166 max(nvl(intr.party_id, -1)) party_id,
167 max(intr.start_date_time) start_date_time,
168 max(nvl(mitm.source_id, -1)) source_id,
169 max(nvl(irc.route_classification_id, -1)) route_classification_id,
170 max(nvl(iview.depth, 0)) depth
171 FROM
172 jtf_ih_media_items mitm,
173 jtf_ih_activities actv,
174 jtf_ih_interactions intr,
175 --
176 --Changes for R12
177 --
178 (
179 select /*+ full(im) +*/ name, max(route_classification_id) route_classification_id
180 from iem_route_classifications im
181 group by name
182 ) irc,
183 (
184 select
185 interaction_id,
186 sum(depth) depth
187 from
188 (
189 /* This returns the parent level interactions and hardcoded depth as 1 for email replies-auto replies */
190 SELECT
191 actv.interaction_id interaction_id,
192 1 depth
193 FROM jtf_ih_activities actv,
194 /* jtf_ih_media_items imtm,*/
195 jtf_ih_media_item_lc_segs mseg,
196 jtf_ih_media_itm_lc_seg_tys mtys
197 WHERE actv.media_id = mseg.media_id
198 AND mtys.milcs_type_id = mseg.milcs_type_id
199 AND mtys.milcs_code IN ('EMAIL_AUTO_REPLY','EMAIL_REPLY')
200 GROUP BY actv.interaction_id
201 UNION ALL
202 /* This will count the no of replies that the interaction tree has. If there is no
203 child interaction that has a reply ,we need to go to the parent interaction level
204 and check if that has a reply, if so increment by 1 or leave it as is*/
205 SELECT root_interaction_id ,
206 /*NVL(max(decode(milcs_code,'EMAIL_REPLY',DEPTH,'EMAIL_AUTO_REPLY',DEPTH,NULL)),0)+1 DEPTH*/
207 count(distinct media_id)
208 FROM
209 (
210 SELECT /*+ ordered */
211 root_interaction_id,
212 parent,
213 child,
214 depth,
215 mseg.media_id
216 /* Added */
217 --,
218 --first_value(milcs_code) over (partition by mseg.media_id order by mseg.start_Date_time desc) milcs_code
219 FROM
220 jtf_ih_media_itm_lc_seg_tys mtys,
221 jtf_ih_media_item_lc_segs mseg,
222 jtf_ih_media_items mitm,
223 jtf_ih_activities actv ,
224 (
225 SELECT to_number(decode(instr(sys_connect_by_path(intr3.interact_interaction_idrelates, ':'), ':', 2), 0,
226 substr(sys_connect_by_path(intr3.interact_interaction_idrelates, ':'), 2),
227 substr(sys_connect_by_path(intr3.interact_interaction_idrelates, ':'), 2,
228 instr(sys_connect_by_path(intr3.interact_interaction_idrelates, ':'), ':', 2)-2))) root_interaction_id,
229 intr3.interact_interaction_idrelates parent,
230 intr3.interact_interaction_id child,
231 level depth
232 FROM jtf_ih_interaction_inters intr3
233 START WITH intr3.interact_interaction_idrelates in
234 (select
235 intr2.interact_interaction_idrelates
236 from jtf_ih_interaction_inters intr2
237 where intr2.interact_interaction_idrelates not in
238 (
239 select
240 intr1.interact_interaction_id
241 from jtf_ih_interaction_inters intr1
242 )
243 )
244 CONNECT BY intr3.interact_interaction_idrelates = PRIOR intr3.interact_interaction_id
245 ) intr
246 WHERE actv.interaction_id =intr.child
247 AND mitm.media_id = actv.media_id
248 AND mitm.media_id = mseg.media_id
249 AND mseg.milcs_type_id = mtys.milcs_type_id
250 AND mitm.direction='INBOUND' AND mitm.media_item_type='EMAIL'
251 /* Added */ AND mtys.milcs_code in ('EMAIL_REPLY','EMAIL_AUTO_REPLY')
252
253 ) GROUP BY ROOT_INTERACTION_ID
254 ) iview/* Added */
255 WHERE NOT EXISTS (
256 select 1 from jtf_ih_interaction_inters inter
257 WHERE iview.interaction_id = inter.interact_interaction_id
258 )
259 group by interaction_id
260 ) iview
261 WHERE intr.start_date_time between g_collect_start_date and g_collect_end_date
262 AND
263 intr.interaction_id = actv.interaction_id
264 AND intr.interaction_id = iview.interaction_id(+)
265 AND mitm.media_id = actv.media_id
266 AND mitm.direction = 'INBOUND'
267 AND mitm.media_item_type = 'EMAIL'
268 AND mitm.classification = irc.name(+)
269 AND intr.interaction_id NOT IN (
270 SELECT
271 inter.interact_interaction_id
272 FROM jtf_ih_interaction_inters inter)
273 GROUP BY intr.interaction_id;
274
275
276
277
278 TYPE root_interaction_id_tab IS TABLE OF jtf_ih_interactions.interaction_id%TYPE;
279 TYPE agent_id_tab IS TABLE OF jtf_ih_interactions.resource_id%TYPE;
280 TYPE party_id_tab IS TABLE OF jtf_ih_interactions.party_id%TYPE;
281 TYPE start_date_time_tab IS TABLE OF jtf_ih_interactions.start_date_time%TYPE;
282 TYPE source_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
283 TYPE route_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
284 TYPE one_done_rsln_tab IS TABLE OF bix_email_details_f.one_rsln_in_period%TYPE;
285 TYPE two_done_rsln_tab IS TABLE OF bix_email_details_f.two_rsln_in_period%TYPE;
286 TYPE three_done_rsln_tab IS TABLE OF bix_email_details_f.three_rsln_in_period%TYPE;
287 TYPE four_done_rsln_tab IS TABLE OF bix_email_details_f.four_rsln_in_period%TYPE;
288 TYPE intr_thread_tab IS TABLE OF bix_email_details_f.interaction_threads_in_period%TYPE;
289 TYPE week_id_tab IS TABLE OF fii_time_day.week_id%TYPE;
290 TYPE ent_period_id_tab IS TABLE OF fii_time_day.ent_period_id%TYPE;
291 TYPE ent_qtr_id_tab IS TABLE OF fii_time_day.ent_qtr_id%TYPE;
292 TYPE ent_year_id_tab IS TABLE OF fii_time_day.ent_year_id%TYPE;
293 TYPE curr_depth_tab IS TABLE OF bix_email_details_f.one_rsln_in_period%TYPE;
294
295 l_root_interaction_id root_interaction_id_tab;
296 l_agent_id agent_id_tab;
297 l_party_id party_id_tab;
298 l_start_date_time start_date_time_tab;
299 l_email_account_id source_id_tab;
300 l_classification_id route_classification_id_tab;
301 l_one_done_rsln one_done_rsln_tab;
302 l_two_done_rsln two_done_rsln_tab;
303 l_three_done_rsln three_done_rsln_tab;
304 l_four_done_rsln four_done_rsln_tab;
305 l_intr_thread intr_thread_tab;
306 l_curr_depth curr_depth_tab;
307
308 l_no_of_records NUMBER;
309
310 BEGIN
311
312 write_log('Start of the procedure collect_resolutions at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
313
314 /* Initialize the variables */
315 l_one_done_rsln := one_done_rsln_tab();
316 l_two_done_rsln := two_done_rsln_tab();
317 l_three_done_rsln := three_done_rsln_tab();
318 l_four_done_rsln := four_done_rsln_tab();
319 l_intr_thread := intr_thread_tab();
320
321 OPEN all_root_interactions;
322
323 LOOP
324
325 /* fetch all root interactions that have been created in the collection date range */
326 /* or a reply has been given within the collection date range for the interaction */
327 FETCH all_root_interactions BULK COLLECT INTO
328 l_root_interaction_id,
329 l_agent_id,
330 l_party_id,
331 l_start_date_time,
332 l_email_account_id,
333 l_classification_id,
334 l_curr_depth
335 LIMIT g_commit_chunk_size;
336
337 IF (l_root_interaction_id.COUNT > 0) THEN
338
339 l_no_of_records := l_root_interaction_id.COUNT;
340
341 /* Make place for all the interactions */
342 l_one_done_rsln.EXTEND(l_no_of_records);
343 l_two_done_rsln.EXTEND(l_no_of_records);
344 l_three_done_rsln.EXTEND(l_no_of_records);
345 l_four_done_rsln.EXTEND(l_no_of_records);
346 l_intr_thread.EXTEND(l_no_of_records);
347
348 FOR i IN l_root_interaction_id.FIRST .. l_root_interaction_id.LAST
349 LOOP
350 l_one_done_rsln(i) := 0;
351 l_two_done_rsln(i) := 0;
352 l_three_done_rsln(i) := 0;
353 l_four_done_rsln(i) := 0;
354 l_intr_thread(i) := 0;
355
356 l_intr_thread(i) := 1;
357
358 IF (l_curr_depth(i) = 1) THEN l_one_done_rsln(i) := 1;
359 ELSIF (l_curr_depth(i) = 2) THEN l_two_done_rsln(i) := 1;
360 ELSIF (l_curr_depth(i) = 3) THEN l_three_done_rsln(i) := 1;
361 ELSIF (l_curr_depth(i) = 4) THEN l_four_done_rsln(i) := 1;
362 END IF;
363
364 END LOOP;
365
366 /* Update the half-hour rows of ICI summary table with the resolution measures */
367 FORALL i IN l_root_interaction_id.FIRST .. l_root_interaction_id.LAST
368 MERGE INTO bix_email_details_f bed
369 USING (
370 SELECT
371 l_agent_id(i) agent_id
372 ,l_email_account_id(i) email_account_id
373 ,l_classification_id(i) email_classification_id
374 ,l_party_id(i) party_id
375 ,to_number(to_char(l_start_date_time(i), 'J')) time_id
376 ,1 period_type_id
377 ,trunc(l_start_date_time(i)) period_start_date
378 ,'00:00' period_start_time
379 ,l_one_done_rsln(i) one_rsln_in_period
380 ,l_two_done_rsln(i) two_rsln_in_period
381 ,l_three_done_rsln(i) three_rsln_in_period
382 ,l_four_done_rsln(i) four_rsln_in_period
383 ,l_intr_thread(i) interaction_threads_in_period
384 FROM DUAL) change
385 ON (
386 bed.agent_id = change.agent_id
387 AND bed.party_id = change.party_id
388 AND bed.email_account_id = change.email_account_id
389 AND bed.email_classification_id = change.email_classification_id
390 AND bed.time_id = change.time_id
391 AND bed.period_start_time = change.period_start_time
392 AND bed.period_start_date = change.period_start_date
393 AND bed.period_type_id = change.period_type_id
394 AND bed.outcome_id = -1 AND bed.result_id = -1 AND bed.reason_id = -1
395 )
396 WHEN MATCHED THEN
397 UPDATE
398 SET bed.one_rsln_in_period = decode(change.one_rsln_in_period, 0, bed.one_rsln_in_period,
399 decode(nvl(bed.one_rsln_in_period, 0) + change.one_rsln_in_period, 0, to_number(null),
400 nvl(bed.one_rsln_in_period, 0) + change.one_rsln_in_period))
401 ,bed.two_rsln_in_period = decode(change.two_rsln_in_period, 0, bed.two_rsln_in_period,
402 decode(nvl(bed.two_rsln_in_period, 0) + change.two_rsln_in_period, 0, to_number(null),
403 nvl(bed.two_rsln_in_period, 0) + change.two_rsln_in_period))
404 ,bed.three_rsln_in_period = decode(change.three_rsln_in_period, 0, bed.three_rsln_in_period,
405 decode(nvl(bed.three_rsln_in_period, 0) + change.three_rsln_in_period, 0, to_number(null),
406 nvl(bed.three_rsln_in_period, 0) + change.three_rsln_in_period))
407 ,bed.four_rsln_in_period = decode(change.four_rsln_in_period, 0, bed.four_rsln_in_period,
408 decode(nvl(bed.four_rsln_in_period, 0) + change.four_rsln_in_period, 0, to_number(null),
409 nvl(bed.four_rsln_in_period, 0) + change.four_rsln_in_period))
410 ,bed.interaction_threads_in_period = decode(change.interaction_threads_in_period, 0,
411 bed.interaction_threads_in_period, decode(nvl(bed.interaction_threads_in_period, 0)
412 + change.interaction_threads_in_period, 0, to_number(null),
413 nvl(bed.interaction_threads_in_period, 0) + change.interaction_threads_in_period))
414 ,bed.last_updated_by = g_user_id
415 ,bed.last_update_date = g_sysdate
416 WHEN NOT MATCHED THEN INSERT (
417 bed.agent_id
418 ,bed.party_id
419 ,bed.email_account_id
420 ,bed.email_classification_id
421 ,bed.time_id
422 ,bed.period_start_time
423 ,bed.period_start_date
424 ,bed.period_type_id
425 ,outcome_id
426 ,result_id
427 ,reason_id
428 ,bed.created_by
429 ,bed.creation_date
430 ,bed.last_updated_by
431 ,bed.last_update_date
432 ,bed.one_rsln_in_period
433 ,bed.two_rsln_in_period
434 ,bed.three_rsln_in_period
435 ,bed.four_rsln_in_period
436 ,bed.interaction_threads_in_period )
437 VALUES (
438 change.agent_id
439 ,change.party_id
440 ,change.email_account_id
441 ,change.email_classification_id
442 ,change.time_id
443 ,change.period_start_time
444 ,change.period_start_date
445 ,change.period_type_id
446 ,-1
447 ,-1
448 ,-1
449 ,g_user_id
450 ,g_sysdate
451 ,g_user_id
452 ,g_sysdate
453 ,decode(change.one_rsln_in_period, 0, to_number(null), change.one_rsln_in_period)
454 ,decode(change.two_rsln_in_period, 0, to_number(null), change.two_rsln_in_period)
455 ,decode(change.three_rsln_in_period, 0, to_number(null), change.three_rsln_in_period)
456 ,decode(change.four_rsln_in_period, 0, to_number(null), change.four_rsln_in_period)
457 ,decode(change.interaction_threads_in_period, 0, to_number(null), change.interaction_threads_in_period));
458
459 COMMIT;
460
461 write_log('Total rows inserted/updated in bix_email_details_f for resolution : ' ||
462 to_char(l_root_interaction_id.COUNT));
463 g_rows_ins_upd := g_rows_ins_upd + (l_root_interaction_id.COUNT);
464
465 /* Update the bix_interactions_temp table to keep track of depth by interaction */
466 --
467 --BIX_INTERACTION_TEMP is used for UPDATE program. This is used to keep track of
468 --whet we need to subtract - example yesterday an email might have been ONE AND DONE.
469 --Today the customer replies to it and it is no longer DONE. So we need to go back and subtract
470 --yesterday's ONE AND DONE
471 --
472 FORALL i IN l_root_interaction_id.FIRST .. l_root_interaction_id.LAST
473 INSERT INTO BIX_INTERACTIONS_TEMP bit (
474 interaction_id,
475 created_by,
476 creation_date,
477 last_updated_by,
478 last_update_date,
479 depth,
480 request_id,
481 program_application_id,
482 program_id,
483 program_update_date )
484 VALUES (
485 l_root_interaction_id(i),
486 g_user_id,
487 g_sysdate,
488 g_user_id,
489 g_sysdate,
490 l_curr_depth(i),
491 g_request_id,
492 g_program_appl_id,
493 g_program_id,
494 g_sysdate);
495
496 COMMIT;
497
498 write_log('Total rows inserted/updated in bix_interactions_temp : ' || to_char(l_root_interaction_id.COUNT));
499 g_rows_ins_upd := g_rows_ins_upd + l_root_interaction_id.COUNT;
500
501 l_one_done_rsln.TRIM(l_no_of_records);
502 l_two_done_rsln.TRIM(l_no_of_records);
503 l_three_done_rsln.TRIM(l_no_of_records);
504 l_four_done_rsln.TRIM(l_no_of_records);
505 l_intr_thread.TRIM(l_no_of_records);
506
507 END IF;
508
509 EXIT WHEN all_root_interactions%NOTFOUND;
510
511 END LOOP;
512
513 write_log('Finished procedure collect_resolutions at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 write_log('Error in procedure collect_resolutions : Error : ' || sqlerrm);
518 RAISE;
519 END collect_resolutions;
520
521 PROCEDURE clean_up IS
522
523 l_total_rows_deleted NUMBER := 0;
524 l_rows_deleted NUMBER := 0;
525
526 BEGIN
527 write_log('Start of the procedure clean_up at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
528
529 write_log('Truncating the table bix_email_details_f and bix_interactions_temp');
530 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIX_EMAIL_DETAILS_F');
531 Truncate_Table('BIX_EMAIL_DETAILS_F');
532 Truncate_Table('BIX_INTERACTIONS_TEMP');
533 write_log('Done truncating the table bix_email_details_f and bix_interactions_temp');
534
535 write_log('Finished procedure clean_up at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
536
537 EXCEPTION
538 WHEN OTHERS THEN
539 write_log('Error in cleaning up the tables : Error : ' || sqlerrm);
540 RAISE;
541 END CLEAN_UP;
542
543
544 /*
545 This procedure collects all the additive measures. In this procedure we collect all the measures except queue, open and resolution measures.
546 */
547
548
549
550 PROCEDURE COLLECT_EMAILS IS
551
552 l_email_service_level NUMBER;
553
554 BEGIN
555
556 write_log('Start of the procedure collect_emails at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
557
558 /* Get the service level for the whole email center : if not defined then 1 day is the default */
559 /* Multiply the profile by 60 * 60 to convert from hour to seconds */
560 IF (FND_PROFILE.DEFINED('BIX_EMAIL_GOAL')) THEN
561 l_email_service_level := TO_NUMBER(FND_PROFILE.VALUE('BIX_EMAIL_GOAL')) * 60 * 60;
562 ELSE
563 l_email_service_level := 24 * 60 * 60;
564 END IF;
565 write_log('The service level for the whole email center : ' || to_char(l_email_service_level) || ' seconds');
566
567 write_log('Merging additive measures into table bix_email_details_f');
568
569 /* Insert / Update additive measures to summary table bix_email_details_f */
570 INSERT /*+ APPEND PARALLEL(bed) */ INTO BIX_EMAIL_DETAILS_F bed
571 (email_account_id,
572 email_classification_id,
573 agent_id,
574 party_id,
575 time_id,
576 period_type_id,
577 period_start_date,
578 period_start_time,
579 outcome_id,
580 result_id,
581 reason_id,
582 created_by,
583 creation_date,
584 last_updated_by,
585 last_update_date,
586 emails_offered_in_period,
587 emails_fetched_in_period,
588 emails_replied_in_period,
589 emails_rpld_by_goal_in_period,
590 AGENT_EMAILS_RPLD_BY_GOAL,
591 emails_deleted_in_period,
592 emails_trnsfrd_out_in_period,
593 emails_trnsfrd_in_in_period,
594 emails_assigned_in_period,
595 emails_auto_routed_in_period,
596 emails_auto_uptd_sr_in_period,
597 email_resp_time_in_period,
598 agent_resp_time_in_period,
599 sr_created_in_period,
600 emails_rsl_and_trfd_in_period,
601 emails_orr_count_in_period,
602 EMAILS_AUTO_REPLIED_IN_PERIOD,
603 EMAILS_AUTO_DELETED_IN_PERIOD,
604 EMAILS_AUTO_RESOLVED_IN_PERIOD,
605 emails_composed_in_period,
606 emails_rerouted_in_period,
607 leads_created_in_period,
608 request_id,
609 program_application_id,
610 program_id,
611 program_update_date)
612 (SELECT /*+ PARALLEL(inv2) */
613 inv2.email_account_id,
614 inv2.email_classification_id,
615 inv2.agent_id,
616 inv2.party_id,
617 inv2.time_id,
618 --1/2hr changed period_type_id to 1 from -1
619 1,
620 inv2.period_start_date,
621 inv2.period_start_time,
622 inv2.outcome_id,
623 inv2.result_id,
624 inv2.reason_id,
625 g_user_id,
626 g_sysdate,
627 g_user_id,
628 g_sysdate,
629 decode(sum(emails_offered_in_period), 0, to_number(null), sum(emails_offered_in_period)),
630 decode(sum(emails_fetched_in_period), 0, to_number(null), sum(emails_fetched_in_period)),
631 decode(sum(emails_replied_in_period), 0, to_number(null), sum(emails_replied_in_period)),
632 decode(sum(emails_rpld_by_goal_in_period), 0, to_number(null), sum(emails_rpld_by_goal_in_period)),
633 decode(sum(AGENT_EMAILS_RPLD_BY_GOAL), 0, to_number(null), sum(AGENT_EMAILS_RPLD_BY_GOAL)),
634 decode(sum(emails_deleted_in_period), 0, to_number(null), sum(emails_deleted_in_period)),
635 decode(sum(emails_trnsfrd_out_in_period), 0, to_number(null), sum(emails_trnsfrd_out_in_period)),
636 decode(sum(emails_trnsfrd_in_in_period), 0, to_number(null), sum(emails_trnsfrd_in_in_period)),
637 decode(sum(emails_assigned_in_period), 0, to_number(null), sum(emails_assigned_in_period)),
638 decode(sum(emails_auto_routed_in_period), 0, to_number(null), sum(emails_auto_routed_in_period)),
639 decode(sum(emails_auto_uptd_sr_in_period), 0, to_number(null), sum(emails_auto_uptd_sr_in_period)),
640 decode(round(sum(email_resp_time_in_period)), 0, to_number(null), round(sum(email_resp_time_in_period))),
641 decode(round(sum(agent_resp_time_in_period)), 0, to_number(null), round(sum(agent_resp_time_in_period))),
642 decode(sum(sr_created_in_period), 0, to_number(null), sum(sr_created_in_period)),
643 decode(sum(emails_rsl_and_trfd_in_period), 0, to_number(null), sum(emails_rsl_and_trfd_in_period)),
644 decode(sum(emails_orr_count_in_period), 0, to_number(null), sum(emails_orr_count_in_period)),
645 decode(sum(EMAILS_AUTO_REPLIED_IN_PERIOD), 0, to_number(null), sum(EMAILS_AUTO_REPLIED_IN_PERIOD)),
646 decode(sum(EMAILS_AUTO_DELETED_IN_PERIOD), 0, to_number(null), sum(EMAILS_AUTO_DELETED_IN_PERIOD)),
647 decode(sum(EMAILS_AUTO_RESOLVED_IN_PERIOD), 0, to_number(null), sum(EMAILS_AUTO_RESOLVED_IN_PERIOD)),
648 decode(sum(emails_composed_in_period), 0, to_number(null), sum(emails_composed_in_period)),
649 decode(sum(EMAILS_REROUTED_IN_PERIOD), 0, to_number(null), sum(EMAILS_REROUTED_IN_PERIOD)),
650 decode(sum(leads_created_in_period), 0, to_number(null), sum(leads_created_in_period)),
651 g_request_id,
652 g_program_appl_id,
653 g_program_id,
654 g_sysdate
655 FROM --This sql fetches the count of a whole bunch of pure email measures like fetched, replied etc
656 (SELECT /*+ use_hash(mitm) use_hash(mseg) use_hash(inv2) use_hash(inv1) use_hash(mtyp) use_hash(irc)
657 PARALLEL(mitm) PARALLEL(mseg) PARALLEL(inv2) PARALLEL(inv1) PARALLEL(mtyp) PARALLEL(irc) */
658 nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
659 nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
660 nvl(mseg.resource_id, -1) AGENT_ID,
661 nvl(inv1.party_id, -1) PARTY_ID,
662 trunc(mseg.start_date_time) PERIOD_START_DATE,
663 to_number(to_char(mseg.start_date_time, 'J')) TIME_ID,
664 --1/2hr
665 --nvl(lpad(to_char(mseg.start_date_time,'HH24:'),3,'0') ||
666 -- decode(sign(to_number(to_char(mseg.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
667 '00:00'
668 PERIOD_START_TIME,
669 -1 OUTCOME_ID,
670 -1 RESULT_ID,
671 -1 REASON_ID,
672 0 EMAILS_OFFERED_IN_PERIOD,
673 sum(decode(mtyp.milcs_code,'EMAIL_FETCH',1)) EMAILS_FETCHED_IN_PERIOD,
674 sum(decode(mtyp.milcs_code,'EMAIL_REPLY',1)) EMAILS_REPLIED_IN_PERIOD,
675
676 sum(decode(mtyp.milcs_code,'EMAIL_REPLY', (mseg.start_date_time - inv2.start_date_time) * 24 * 60 * 60))
677 AGENT_RESP_TIME_IN_PERIOD,
678 sum(decode(mtyp.milcs_code,'EMAIL_REPLY',
679 decode(sign(l_email_service_level - (mseg.start_date_time - mitm.start_date_time) * 24 * 60 * 60),-1,0,1)
680 , 'EMAIL_AUTO_REPLY',
681 decode(sign(l_email_service_level - (mseg.start_date_time - mitm.start_date_time) * 24 * 60 * 60),-1,0,1) ))
682 EMAILS_RPLD_BY_GOAL_IN_PERIOD,
683 sum(decode(mtyp.milcs_code,'EMAIL_REPLY',
684 decode(sign(l_email_service_level - (mseg.start_date_time - mitm.start_date_time) * 24 * 60 * 60),-1,0,1)
685 ))
686 AGENT_EMAILS_RPLD_BY_GOAL,
687 sum(decode(mtyp.milcs_code,'EMAIL_TRANSFERRED',1,'EMAIL_ESCALATED',1)) EMAILS_TRNSFRD_OUT_IN_PERIOD,
688 sum(decode(mtyp.milcs_code,'EMAIL_TRANSFER',1)) EMAILS_TRNSFRD_IN_IN_PERIOD,
689 sum(decode(mtyp.milcs_code,'EMAIL_ASSIGNED',1)) EMAILS_ASSIGNED_IN_PERIOD,
690 sum(decode(mtyp.milcs_code,'EMAIL_AUTO_ROUTED',1)) EMAILS_AUTO_ROUTED_IN_PERIOD,
691 sum(decode(mtyp.milcs_code,'EMAIL_AUTO_UPDATED_SR',1))
692 EMAILS_AUTO_UPTD_SR_IN_PERIOD,
693 sum(decode(mtyp.milcs_code,'EMAIL_DELETED',1)) EMAILS_DELETED_IN_PERIOD,
694 sum(decode(mtyp.milcs_code,'EMAIL_REPLY', (mseg.start_date_time - mitm.start_date_time) * 24 * 60 * 60,
695 'EMAIL_AUTO_REPLY',(mseg.start_date_time - mitm.start_date_time) * 24 * 60 * 60))
696 EMAIL_RESP_TIME_IN_PERIOD,
697 0 SR_CREATED_IN_PERIOD,
698 0 EMAILS_RSL_AND_TRFD_IN_PERIOD,
699 sum(decode(mtyp.milcs_code,'EMAIL_AUTO_REPLY',1)) EMAILS_AUTO_REPLIED_IN_PERIOD,
700 sum(decode(mtyp.milcs_code,'EMAIL_AUTO_DELETED',1)) EMAILS_AUTO_DELETED_IN_PERIOD,
701 sum(decode(mtyp.milcs_code,'EMAIL_RESOLVED',1)) EMAILS_AUTO_RESOLVED_IN_PERIOD,
702 0 emails_composed_in_period,
703 0 emails_orr_count_in_period,
704 sum(decode(mtyp.milcs_code,'EMAIL_REROUTED_DIFF_CLASS',1,
705 'EMAIL_REROUTED_DIFF_ACCT',1,
706 'EMAIL_REQUEUED',1 )) EMAILS_REROUTED_IN_PERIOD,
707 0 LEADS_CREATED_IN_PERIOD
708 FROM
709 JTF_IH_MEDIA_ITEMS mitm,
710 JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
711 (
712 SELECT /*+ use_hash(mseg1) use_hash(mtyp1) use_hash(mseg2) use_hash(mtyp2)
713 PARALLEL(mseg1) PARALLEL(mtyp1) PARALLEL(mseg2) PARALLEL(mtyp2) */
714 mseg1.media_id media_id,
715 MAX(mseg2.start_date_time) start_date_time
716 FROM
717 jtf_ih_media_item_lc_segs mseg1,
718 jtf_ih_media_itm_lc_seg_tys mtyp1,
719 jtf_ih_media_item_lc_segs mseg2,
720 jtf_ih_media_itm_lc_seg_tys mtyp2
721 WHERE mseg1.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
722 AND mseg1.milcs_type_id = mtyp1.milcs_type_id
723 AND mtyp1.milcs_code = 'EMAIL_REPLY'
724 AND mseg1.media_id = mseg2.media_id
725 AND mseg2.milcs_type_id = mtyp2.milcs_type_id
726 AND mtyp2.milcs_code IN ('EMAIL_FETCH','EMAIL_TRANSFER', 'EMAIL_ASSIGN_OPEN', 'EMAIL_AUTO_ROUTED', 'EMAIL_ASSIGNED')
727 GROUP BY mseg1.media_id
728 ) inv2,
729 (
730 SELECT /*+ use_hash(actv) use_hash(intr) PARALLEL(actv) PARALLEL(intr) */
731 actv.media_id media_id,
732 min(intr.party_id) party_id
733 FROM
734 jtf_ih_activities actv,
735 jtf_ih_interactions intr
736 WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
737 AND actv.interaction_id = intr.interaction_id
738 GROUP BY actv.media_id
739 ) inv1,
740 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyp,
741 --
742 --Changes for R12
743 --
744 (
745 select name, max(route_classification_id) route_classification_id
746 from iem_route_classifications
747 group by name
748 ) irc
749 WHERE mitm.media_item_type = 'EMAIL'
750 AND mitm.direction = 'INBOUND'
751 AND mitm.classification = irc.name(+)
752 AND mitm.media_id = inv2.media_id(+)
753 AND mitm.media_id = inv1.media_id(+)
754 AND mitm.MEDIA_ID = mseg.MEDIA_ID
755 AND mseg.MILCS_TYPE_ID = mtyp.MILCS_TYPE_ID
756 AND mseg.START_DATE_TIME BETWEEN g_collect_start_date and g_collect_end_date
757 GROUP BY
758 nvl(mitm.source_id, -1),
759 nvl(irc.route_classification_id, -1),
760 mseg.resource_id,
761 nvl(inv1.party_id, -1),
762 trunc(mseg.start_date_time),
763 to_number(to_char(mseg.start_date_time, 'J'))
764 --1/2hr
765 --nvl(lpad(to_char(mseg.start_date_time,'HH24:'),3,'0') ||
766 -- decode(sign(to_number(to_char(mseg.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
767 UNION ALL --This sql counts the number of emails offered in the given period grouped together by day
768 SELECT /*+ use_hash(mitm) use_hash(inv1) use_hash(irc) PARALLEL(mitm) PARALLEL(inv1) PARALLEL(irc) */
769 nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
770 nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
771 -1 AGENT_ID,
772 nvl(inv1.party_id, -1) PARTY_ID,
773 trunc(mitm.start_date_time) PERIOD_START_DATE,
774 to_number(to_char(mitm.start_date_time, 'J')) TIME_ID,
775 --1/2hr
776 --nvl(lpad(to_char(mitm.start_date_time,'HH24:'),3,'0') ||
777 -- decode(sign(to_number(to_char(mitm.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
778 '00:00'
779 PERIOD_START_TIME,
780 -1 OUTCOME_ID,
781 -1 RESULT_ID,
782 -1 REASON_ID,
783 COUNT(*) EMAILS_OFFERED_IN_PERIOD,
784 0 EMAILS_FETCHED_IN_PERIOD,
785 0 EMAILS_REPLIED_IN_PERIOD,
786 0 AGENT_RESP_TIME_IN_PERIOD,
787 0 EMAILS_RPLD_BY_GOAL_IN_PERIOD,
788 0 AGENT_EMAILS_RPLD_BY_GOAL,
789 0 EMAILS_TRNSFRD_OUT_IN_PERIOD,
790 0 EMAILS_TRNSFRD_IN_IN_PERIOD,
791 0 EMAILS_ASSIGNED_IN_PERIOD,
792 0 EMAILS_AUTO_ROUTED_IN_PERIOD,
793 0 EMAILS_AUTO_UPTD_SR_IN_PERIOD,
794 0 EMAILS_DELETED_IN_PERIOD,
795 0 EMAIL_RESP_TIME_IN_PERIOD,
796 0 SR_CREATED_IN_PERIOD,
797 0 EMAILS_RSL_AND_TRFD_IN_PERIOD,
798 0 EMAILS_AUTO_REPLIED_IN_PERIOD,
799 0 EMAILS_AUTO_DELETED_IN_PERIOD,
800 0 EMAILS_AUTO_RESOLVED_IN_PERIOD,
801 0 emails_composed_in_period,
802 0 emails_orr_count_in_period,
803 0 EMAILS_REROUTED_IN_PERIOD,
804 0 LEADS_CREATED_IN_PERIOD
805 FROM
806 JTF_IH_MEDIA_ITEMS mitm,
807 (
808 SELECT /*+ use_hash(actv) use_hash(intr) PARALLEL(actv) PARALLEL(intr) */
809 actv.media_id media_id,
810 min(intr.party_id) party_id
811 FROM
812 jtf_ih_activities actv,
813 jtf_ih_interactions intr
814 WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
815 AND actv.interaction_id = intr.interaction_id
816 GROUP BY actv.media_id
817 ) inv1,
818 --
819 --Changes for R12
820 --
821 (
822 select name, max(route_classification_id) route_classification_id
823 from iem_route_classifications
824 group by name
825 ) irc
826 WHERE mitm.MEDIA_ITEM_TYPE = 'EMAIL'
827 AND mitm.DIRECTION = 'INBOUND'
828 AND mitm.classification = irc.name(+)
829 AND mitm.media_id = inv1.media_id(+)
830 AND mitm.START_DATE_TIME BETWEEN g_collect_start_date and g_collect_end_date
831 GROUP BY
832 nvl(mitm.source_id, -1),
833 nvl(irc.route_classification_id, -1),
834 nvl(inv1.party_id, -1),
835 trunc(mitm.start_date_time),
836 to_number(to_char(mitm.start_date_time, 'J'))
837 --1/2hr
838 --nvl(lpad(to_char(mitm.start_date_time,'HH24:'),3,'0') ||
839 -- decode(sign(to_number(to_char(mitm.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
840 UNION ALL -- This sql segment counts the number of emails/SR created
841 SELECT /*+ use_hash(actv) use_hash(intr) use_hash(mitm) use_hash(irc)
842 PARALLEL(actv) PARALLEL(intr) PARALLEL(mitm) PARALLEL(irc) */
843 nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
844 nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
845 nvl(intr.resource_id, -1) AGENT_ID,
846 nvl(intr.party_id, -1) PARTY_ID,
847 trunc(actv.start_date_time) PERIOD_START_DATE,
848 to_number(to_char(actv.start_date_time, 'J')) TIME_ID,
849 --1/2hr
850 --nvl(lpad(to_char(actv.start_date_time,'HH24:'),3,'0') ||
851 -- decode(sign(to_number(to_char(actv.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
852 '00:00'
853 PERIOD_START_TIME,
854 -1 OUTCOME_ID,
855 -1 RESULT_ID,
856 -1 REASON_ID,
857 0 EMAILS_OFFERED_IN_PERIOD,
858 0 EMAILS_FETCHED_IN_PERIOD,
859 0 EMAILS_REPLIED_IN_PERIOD,
860 0 AGENT_RESP_TIME_IN_PERIOD,
861 0 EMAILS_RPLD_BY_GOAL_IN_PERIOD,
862 0 AGENT_EMAILS_RPLD_BY_GOAL,
863 0 EMAILS_TRNSFRD_OUT_IN_PERIOD,
864 0 EMAILS_TRNSFRD_IN_IN_PERIOD,
865 0 EMAILS_ASSIGNED_IN_PERIOD,
866 0 EMAILS_AUTO_ROUTED_IN_PERIOD,
867 0 EMAILS_AUTO_UPTD_SR_IN_PERIOD,
868 0 EMAILS_DELETED_IN_PERIOD,
869 0 EMAIL_RESP_TIME_IN_PERIOD,
870 SUM(DECODE(actv.action_id,13,1)) SR_CREATED_IN_PERIOD,
871 0 EMAILS_RSL_AND_TRFD_IN_PERIOD,
872 0 EMAILS_AUTO_REPLIED_IN_PERIOD,
873 0 EMAILS_AUTO_DELETED_IN_PERIOD,
874 0 EMAILS_AUTO_RESOLVED_IN_PERIOD,
875 0 emails_composed_in_period,
876 0 emails_orr_count_in_period,
877 0 EMAILS_REROUTED_IN_PERIOD,
878 SUM(DECODE(actv.action_id,71,1)) LEADS_CREATED_IN_PERIOD
879 FROM
880 JTF_IH_ACTIVITIES actv,
881 JTF_IH_INTERACTIONS intr,
882 JTF_IH_MEDIA_ITEMS mitm,
883 (select
884 actv.interaction_id interaction_id,
885 max(mitm.classification) classification
886 from
887 jtf_ih_activities actv,
888 jtf_ih_activities actv1,
889 jtf_ih_media_items mitm
890 where actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
891 and actv.interaction_id = actv1.interaction_id
892 and actv1.media_id = mitm.media_id
893 and mitm.direction = 'INBOUND'
894 and mitm.media_item_type = 'EMAIL'
895 group by actv.interaction_id ) inv2,
896 --
897 --Changes for R12
898 --
899 (
900 select name, max(route_classification_id) route_classification_id
901 from iem_route_classifications
902 group by name
903 ) irc
904 WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
905 AND ( ( actv.action_id = 13 AND actv.action_item_id = 17 ) OR
906 ( actv.action_id = 71 AND actv.action_item_id = 8 )
907 )
908 AND actv.media_id = mitm.media_id
909 AND mitm.MEDIA_ITEM_TYPE = 'EMAIL'
910 AND inv2.classification = irc.name(+)
911 AND actv.interaction_id = intr.interaction_id
912 AND actv.interaction_id = inv2.interaction_id(+)
913 GROUP BY
914 nvl(mitm.source_id, -1),
915 nvl(irc.route_classification_id, -1),
916 intr.resource_id,
917 nvl(intr.party_id, -1),
918 trunc(actv.start_date_time),
919 to_number(to_char(actv.start_date_time, 'J'))
920 --1/2hr
921 --nvl(lpad(to_char(actv.start_date_time,'HH24:'),3,'0') ||
922 -- decode(sign(to_number(to_char(actv.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
923 UNION ALL -- This sql counts the number of emails resolved and transferred within the period grouped together by a day
924 SELECT /*+ use_hash(inv2) use_hash(mitm) use_hash(mseg) use_hash(mtyp) use_hash(irc)
925 PARALLEL(inv2) PARALLEL(mitm) PARALLEL(mseg) PARALLEL(mtyp) PARALLEL(irc) */
926 nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
927 nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
928 nvl(mseg.resource_id, -1) AGENT_ID,
929 nvl(inv2.party_id, -1) PARTY_ID,
930 trunc(mseg.start_date_time) PERIOD_START_DATE,
931 to_number(to_char(mseg.start_date_time, 'J')) TIME_ID,
932 --1/2hr
933 --nvl(lpad(to_char(mseg.start_date_time,'HH24:'),3,'0') ||
934 -- decode(sign(to_number(to_char(mseg.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
935 '00:00'
936 PERIOD_START_TIME,
937 -1 OUTCOME_ID,
938 -1 RESULT_ID,
939 -1 REASON_ID,
940 0 EMAILS_OFFERED_IN_PERIOD,
941 0 EMAILS_FETCHED_IN_PERIOD,
942 0 EMAILS_REPLIED_IN_PERIOD,
943 0 AGENT_RESP_TIME_IN_PERIOD,
944 0 EMAILS_RPLD_BY_GOAL_IN_PERIOD,
945 0 AGENT_EMAILS_RPLD_BY_GOAL,
946 0 EMAILS_TRNSFRD_OUT_IN_PERIOD,
947 0 EMAILS_TRNSFRD_IN_IN_PERIOD,
948 0 EMAILS_ASSIGNED_IN_PERIOD,
949 0 EMAILS_AUTO_ROUTED_IN_PERIOD,
950 0 EMAILS_AUTO_UPTD_SR_IN_PERIOD,
951 0 EMAILS_DELETED_IN_PERIOD,
952 0 EMAIL_RESP_TIME_IN_PERIOD,
953 0 SR_CREATED_IN_PERIOD,
954 count(*) EMAILS_RSL_AND_TRFD_IN_PERIOD,
955 0 EMAILS_AUTO_REPLIED_IN_PERIOD,
956 0 EMAILS_AUTO_DELETED_IN_PERIOD,
957 0 EMAILS_AUTO_RESOLVED_IN_PERIOD,
958 0 emails_composed_in_period,
959 0 emails_orr_count_in_period,
960 0 EMAILS_REROUTED_IN_PERIOD,
961 0 LEADS_CREATED_IN_PERIOD
962 FROM
963 (
964 SELECT /*+ use_hash(actv) use_hash(intr) PARALLEL(actv) PARALLEL(intr) */
965 actv.media_id media_id,
966 min(intr.party_id) party_id
967 FROM
968 jtf_ih_activities actv,
969 jtf_ih_interactions intr
970 WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
971 AND actv.interaction_id = intr.interaction_id
972 GROUP BY actv.media_id
973 ) inv2,
974 JTF_IH_MEDIA_ITEMS mitm,
975 JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
976 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyp,
977 --
978 --Changes for R12
979 --
980 (
981 select name, max(route_classification_id) route_classification_id
982 from iem_route_classifications
983 group by name
984 ) irc
985 WHERE mitm.MEDIA_ITEM_TYPE = 'EMAIL'
986 AND mitm.DIRECTION = 'INBOUND'
987 AND mitm.classification = irc.name(+)
988 AND mitm.media_id = inv2.media_id(+)
989 AND mitm.MEDIA_ID = mseg.MEDIA_ID
990 AND mseg.MILCS_TYPE_ID = mtyp.MILCS_TYPE_ID
991 AND mtyp.MILCS_CODE IN ('EMAIL_REPLY', 'EMAIL_DELETED')
992 AND mseg.START_DATE_TIME BETWEEN g_collect_start_date and g_collect_end_date
993 AND EXISTS (
994 SELECT /*+ use_hash(mseg1) use_hash(mtys1) PARALLEL(mseg1) PARALLEL(mtys1) */
995 1
996 FROM
997 jtf_ih_media_item_lc_segs mseg1,
998 jtf_ih_media_itm_lc_seg_tys mtys1
999 WHERE mseg1.media_id = mitm.media_id
1000 AND mtys1.milcs_type_id = mseg1.milcs_type_id
1001 AND mtys1.milcs_code IN ( 'EMAIL_TRANSFERRED','EMAIL_ESCALATED') )
1002 GROUP BY
1003 nvl(mitm.source_id, -1),
1004 nvl(irc.route_classification_id, -1),
1005 nvl(mseg.resource_id, -1),
1006 nvl(inv2.party_id, -1),
1007 trunc(mseg.start_date_time),
1008 to_number(to_char(mseg.start_date_time, 'J'))
1009 --1/2hr
1010 --nvl(lpad(to_char(mseg.start_date_time,'HH24:'),3,'0') ||
1011 -- decode(sign(to_number(to_char(mseg.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
1012 UNION ALL -- This sql segment collects inbound email outcome count
1013 SELECT /*+ use_hash(inv2) use_hash(mitm) use_hash(mseg) use_hash(mtyp) use_hash(irc)
1014 PARALLEL(inv2) PARALLEL(mitm) PARALLEL(mseg) PARALLEL(mtyp) PARALLEL(irc)
1015 use_hash(actv) use_hash(intr) PARALLEL(actv) PARALLEL(intr) */
1016 nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
1017 nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
1018 nvl(intr.resource_id, -1) AGENT_ID,
1019 nvl(intr.party_id, -1) PARTY_ID,
1020 trunc(intr.last_update_date) PERIOD_START_DATE,
1021 to_number(to_char(intr.last_update_date, 'J')) TIME_ID,
1022 --1/2hr
1023 --nvl(lpad(to_char(intr.last_update_date,'HH24:'),3,'0') ||
1024 -- decode(sign(to_number(to_char(intr.last_update_date,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
1025 --Replacing with 00:00 since we are going to ignore 1/2 hr segments
1026 '00:00' PERIOD_START_TIME,
1027 NVL(intr.outcome_id,-1) OUTCOME_ID,
1028 NVL(intr.result_id,-1) RESULT_ID,
1029 NVL(intr.reason_id,-1) REASON_ID,
1030 0 EMAILS_OFFERED_IN_PERIOD,
1031 0 EMAILS_FETCHED_IN_PERIOD,
1032 0 EMAILS_REPLIED_IN_PERIOD,
1033 0 AGENT_RESP_TIME_IN_PERIOD,
1034 0 EMAILS_RPLD_BY_GOAL_IN_PERIOD,
1035 0 AGENT_EMAILS_RPLD_BY_GOAL,
1036 0 EMAILS_TRNSFRD_OUT_IN_PERIOD,
1037 0 EMAILS_TRNSFRD_IN_IN_PERIOD,
1038 0 EMAILS_ASSIGNED_IN_PERIOD,
1039 0 EMAILS_AUTO_ROUTED_IN_PERIOD,
1040 0 EMAILS_AUTO_UPTD_SR_IN_PERIOD,
1041 0 EMAILS_DELETED_IN_PERIOD,
1042 0 EMAIL_RESP_TIME_IN_PERIOD,
1043 0 SR_CREATED_IN_PERIOD,
1044 0 EMAILS_RSL_AND_TRFD_IN_PERIOD,
1045 0 EMAILS_AUTO_REPLIED_IN_PERIOD,
1046 0 EMAILS_AUTO_DELETED_IN_PERIOD,
1047 0 EMAILS_AUTO_RESOLVED_IN_PERIOD,
1048 0 emails_composed_in_period,
1049 COUNT(DISTINCT intr.interaction_id) emails_orr_count_in_period,
1050 0 EMAILS_REROUTED_IN_PERIOD,
1051 0 LEADS_CREATED_IN_PERIOD
1052 FROM
1053 JTF_IH_MEDIA_ITEMS mitm,
1054 JTF_IH_ACTIVITIES actv,
1055 JTF_IH_INTERACTIONS intr,
1056 --
1057 --Changes for R12
1058 --
1059 (
1060 select name, max(route_classification_id) route_classification_id
1061 from iem_route_classifications
1062 group by name
1063 ) irc
1064 WHERE mitm.MEDIA_ITEM_TYPE = 'EMAIL'
1065 AND mitm.direction = 'INBOUND'
1066 AND mitm.classification = irc.name(+)
1067 AND mitm.media_id = actv.media_id
1068 AND actv.interaction_id = intr.interaction_id
1069 AND intr.LAST_UPDATE_DATE BETWEEN g_collect_start_date and g_collect_end_date
1070 AND intr.outcome_id IS NOT NULL
1071 GROUP BY
1072 nvl(mitm.source_id, -1),
1073 nvl(irc.route_classification_id, -1),
1074 nvl(intr.resource_id, -1),
1075 nvl(intr.party_id, -1),
1076 trunc(intr.last_update_date),
1077 to_number(to_char(intr.last_update_date, 'J')),
1078 --1/2hr
1079 --nvl(lpad(to_char(intr.last_update_date,'HH24:'),3,'0') ||
1080 --decode(sign(to_number(to_char(intr.last_update_date,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00'),
1081 NVL(intr.outcome_id,-1),
1082 NVL(intr.result_id,-1),
1083 NVL(intr.reason_id,-1)
1084 UNION ALL -- This sql segment collects outbound email outcome count
1085 SELECT /*+ use_hash(inv2) use_hash(mitm) use_hash(mseg) use_hash(mtyp) use_hash(irc)
1086 PARALLEL(inv2) PARALLEL(mitm) PARALLEL(mseg) PARALLEL(mtyp) PARALLEL(irc)
1087 use_hash(actv) use_hash(intr) PARALLEL(actv) PARALLEL(intr) */
1088 nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
1089 nvl(irc.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
1090 nvl(intr.resource_id, -1) AGENT_ID,
1091 nvl(intr.party_id, -1) PARTY_ID,
1092 trunc(intr.last_update_date) PERIOD_START_DATE,
1093 to_number(to_char(intr.last_update_date, 'J')) TIME_ID,
1094 --1/2hr
1095 --nvl(lpad(to_char(intr.last_update_date,'HH24:'),3,'0') ||
1096 -- decode(sign(to_number(to_char(intr.last_update_date,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00')
1097 '00:00' PERIOD_START_TIME,
1098 NVL(intr.outcome_id,-1) OUTCOME_ID,
1099 NVL(intr.result_id,-1) RESULT_ID,
1100 NVL(intr.reason_id,-1) REASON_ID,
1101 0 EMAILS_OFFERED_IN_PERIOD,
1102 0 EMAILS_FETCHED_IN_PERIOD,
1103 0 EMAILS_REPLIED_IN_PERIOD,
1104 0 AGENT_RESP_TIME_IN_PERIOD,
1105 0 EMAILS_RPLD_BY_GOAL_IN_PERIOD,
1106 0 AGENT_EMAILS_RPLD_BY_GOAL,
1107 0 EMAILS_TRNSFRD_OUT_IN_PERIOD,
1108 0 EMAILS_TRNSFRD_IN_IN_PERIOD,
1109 0 EMAILS_ASSIGNED_IN_PERIOD,
1110 0 EMAILS_AUTO_ROUTED_IN_PERIOD,
1111 0 EMAILS_AUTO_UPTD_SR_IN_PERIOD,
1112 0 EMAILS_DELETED_IN_PERIOD,
1113 0 EMAIL_RESP_TIME_IN_PERIOD,
1114 0 SR_CREATED_IN_PERIOD,
1115 0 EMAILS_RSL_AND_TRFD_IN_PERIOD,
1116 0 EMAILS_AUTO_REPLIED_IN_PERIOD,
1117 0 EMAILS_AUTO_DELETED_IN_PERIOD,
1118 0 EMAILS_AUTO_RESOLVED_IN_PERIOD,
1119 COUNT(distinct mitm.media_id) emails_composed_in_period,
1120 COUNT(DISTINCT intr.interaction_id) emails_orr_count_in_period,
1121 0 EMAILS_REROUTED_IN_PERIOD,
1122 0 LEADS_CREATED_IN_PERIOD
1123 FROM
1124 JTF_IH_MEDIA_ITEMS mitm,
1125 JTF_IH_MEDIA_ITEM_LC_SEGS segs,
1126 JTF_IH_MEDIA_ITM_LC_SEG_TYS seg_type,
1127 JTF_IH_ACTIVITIES actv,
1128 JTF_IH_INTERACTIONS intr,
1129 --
1130 --Changes for R12
1131 --
1132 (
1133 select name, max(route_classification_id) route_classification_id
1134 from iem_route_classifications
1135 group by name
1136 ) irc
1137 WHERE mitm.MEDIA_ITEM_TYPE = 'EMAIL'
1138 AND mitm.direction = 'OUTBOUND'
1139 AND mitm.media_id = segs.media_id
1140 AND segs.milcs_type_id = seg_type.milcs_type_id
1141 AND seg_type.milcs_code = 'EMAIL_COMPOSE'
1142 AND mitm.classification = irc.name(+)
1143 AND mitm.media_id = actv.media_id
1144 AND actv.interaction_id = intr.interaction_id
1145 AND intr.LAST_UPDATE_DATE BETWEEN g_collect_start_date and g_collect_end_date
1146 AND intr.outcome_id IS NOT NULL
1147 GROUP BY
1148 nvl(mitm.source_id, -1),
1149 nvl(irc.route_classification_id, -1),
1150 nvl(intr.resource_id, -1),
1151 nvl(intr.party_id, -1),
1152 trunc(intr.last_update_date),
1153 to_number(to_char(intr.last_update_date, 'J')),
1154 --1/2hr
1155 --nvl(lpad(to_char(intr.last_update_date,'HH24:'),3,'0') ||
1156 --decode(sign(to_number(to_char(intr.last_update_date,'MI'))-29),0,'00',1,'30',-1,'00'),'00:00'),
1157 NVL(intr.outcome_id,-1),
1158 NVL(intr.result_id,-1),
1159 NVL(intr.reason_id,-1)
1160 ) inv2
1161 GROUP BY
1162 inv2.email_account_id,
1163 inv2.email_classification_id,
1164 inv2.agent_id,
1165 inv2.party_id,
1166 inv2.time_id,
1167 inv2.period_start_date,
1168 inv2.period_start_time,
1169 inv2.outcome_id,
1170 inv2.result_id,
1171 inv2.reason_id);
1172
1173 COMMIT;
1174
1175 write_log('Number of rows inserted in table bix_email_details_f : ' || to_char(SQL%ROWCOUNT));
1176
1177 g_rows_ins_upd := g_rows_ins_upd + SQL%ROWCOUNT;
1178
1179 /* Estimating statistics as we are going to update these rows for the open measures */
1180 DBMS_STATS.gather_table_stats(ownname => g_bix_schema,
1181 tabName => 'BIX_EMAIL_DETAILS_F',
1182 cascade => TRUE,
1183 degree => bis_common_parameters.get_degree_of_parallelism,
1184 estimate_percent => 10,
1185 granularity => 'GLOBAL');
1186
1187 write_log('Finished procedure collect_emails at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1188
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 write_log('Error in procedure collect_emails : Error : ' || sqlerrm);
1192 RAISE;
1193 END collect_emails;
1194
1195 PROCEDURE update_queue_measure (p_email_account_id IN OUT NOCOPY g_email_account_id_tab,
1196 p_email_classification_id IN OUT NOCOPY g_email_classification_id_tab,
1197 p_party_id IN OUT NOCOPY g_party_id_tab,
1198 p_period_start_date IN OUT NOCOPY g_period_start_date_tab,
1199 p_emails_in_queue IN OUT NOCOPY g_emails_in_queue_tab,
1200 p_total_queue_time IN OUT NOCOPY g_total_queue_time_tab,
1201 p_oldest_message_in_queue IN OUT NOCOPY g_oldest_message_in_queue_tab,
1202 p_acc_emails_one_day IN OUT NOCOPY g_acc_emails_one_day_tab,
1203 p_acc_emails_three_days IN OUT NOCOPY g_acc_emails_three_days_tab,
1204 p_acc_emails_week IN OUT NOCOPY g_acc_emails_week_tab,
1205 p_acc_emails_week_plus IN OUT NOCOPY g_acc_emails_week_plus_tab) IS
1206 BEGIN
1207
1208 write_log('Start of the procedure update_queue_measure at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
1209
1210 /* Update ICI summary table for the queue measures */
1211 FORALL i in p_email_account_id.FIRST .. p_email_account_id.LAST
1212 MERGE INTO bix_email_details_f bed
1213 USING (
1214 SELECT
1215 p_email_account_id(i) email_account_id,
1216 p_email_classification_id(i) email_classification_id,
1217 -1 agent_id,
1218 p_party_id(i) party_id,
1219 to_number(to_char(p_period_start_date(i), 'J')) time_id,
1220 --1/2hr change the period type id to 1 from -1
1221 1 period_type_id,
1222 trunc(p_period_start_date(i)) period_start_date,
1223 LPAD(TO_CHAR(p_period_start_date(i),'HH24:MI'), 5, '0') period_start_time,
1224 nvl(p_emails_in_queue(i),0) accumulated_emails_in_queue,
1225 nvl(p_total_queue_time(i),0) accumulated_queue_time,
1226 p_oldest_message_in_queue(i) oldest_email_queue_date,
1227 nvl(p_acc_emails_one_day(i),0) accumulated_emails_one_day,
1228 nvl(p_acc_emails_three_days(i),0) accumulated_emails_three_days,
1229 nvl(p_acc_emails_week(i),0) accumulated_emails_week,
1230 nvl(p_acc_emails_week_plus(i),0) accumulated_emails_week_plus
1231 FROM dual) change
1232 ON (bed.email_account_id = change.email_account_id
1233 AND bed.email_classification_id = change.email_classification_id
1234 AND bed.agent_id = change.agent_id
1235 AND bed.party_id = change.party_id
1236 AND bed.time_id = change.time_id
1237 AND bed.period_type_id = change.period_type_id
1238 AND bed.period_start_date = change.period_start_date
1239 AND bed.period_start_time = change.period_start_time
1240 AND bed.outcome_id = -1 AND bed.result_id = -1 AND bed.reason_id = -1 )
1241 WHEN MATCHED THEN
1242 UPDATE
1243 SET
1244 bed.accumulated_emails_in_queue = decode(change.accumulated_emails_in_queue, 0, bed.accumulated_emails_in_queue,
1245 NVL(bed.accumulated_emails_in_queue, 0) + change.accumulated_emails_in_queue),
1246 bed.accumulated_queue_time = decode(change.accumulated_queue_time, 0, bed.accumulated_queue_time,
1247 NVL(bed.accumulated_queue_time, 0) + change.accumulated_queue_time),
1248 bed.accumulated_emails_one_day = decode(change.accumulated_emails_one_day, 0, bed.accumulated_emails_one_day,
1249 NVL(bed.accumulated_emails_one_day, 0) + change.accumulated_emails_one_day),
1250 bed.accumulated_emails_three_days = decode(change.accumulated_emails_three_days, 0, bed.accumulated_emails_three_days,
1251 NVL(bed.accumulated_emails_three_days, 0) + change.accumulated_emails_three_days),
1252 bed.accumulated_emails_week = decode(change.accumulated_emails_week, 0, bed.accumulated_emails_week,
1253 NVL(bed.accumulated_emails_week, 0) + change.accumulated_emails_week),
1254 bed.accumulated_emails_week_plus = decode(change.accumulated_emails_week_plus, 0, bed.accumulated_emails_week_plus,
1255 NVL(bed.accumulated_emails_week_plus, 0) + change.accumulated_emails_week_plus),
1256 bed.oldest_email_queue_date =
1257 DECODE(change.oldest_email_queue_date,NULL,bed.oldest_email_queue_date,
1258 DECODE(bed.oldest_email_queue_date,NULL,change.oldest_email_queue_date,
1259 DECODE(SIGN(bed.oldest_email_queue_date - change.oldest_email_queue_date),
1260 -1,bed.oldest_email_queue_date, change.oldest_email_queue_date))),
1261 bed.last_update_date = g_sysdate,
1262 bed.last_updated_by = g_user_id
1263 WHEN NOT MATCHED THEN INSERT (
1264 bed.email_account_id,
1265 bed.email_classification_id,
1266 bed.agent_id,
1267 bed.party_id,
1268 bed.outcome_id,
1269 bed.result_id,
1270 bed.reason_id,
1271 bed.time_id,
1272 bed.period_type_id,
1273 bed.period_start_date,
1274 bed.period_start_time,
1275 bed.created_by,
1276 bed.creation_date,
1277 bed.last_updated_by,
1278 bed.last_update_date,
1279 bed.accumulated_emails_in_queue,
1280 bed.accumulated_queue_time,
1281 bed.oldest_email_queue_date,
1282 bed.accumulated_emails_one_day,
1283 bed.accumulated_emails_three_days,
1284 bed.accumulated_emails_week,
1285 bed.accumulated_emails_week_plus,
1286 bed.request_id,
1287 bed.program_application_id,
1288 bed.program_id,
1289 bed.program_update_date)
1290 VALUES (
1291 change.email_account_id,
1292 change.email_classification_id,
1293 change.agent_id,
1294 change.party_id,
1295 -1,
1296 -1,
1297 -1,
1298 change.time_id,
1299 change.period_type_id,
1300 change.period_start_date,
1301 change.period_start_time,
1302 g_user_id,
1303 g_sysdate,
1304 g_user_id,
1305 g_sysdate,
1306 decode(change.accumulated_emails_in_queue, 0, to_number(null), change.accumulated_emails_in_queue),
1307 decode(change.accumulated_queue_time, 0, to_number(null), change.accumulated_queue_time),
1308 change.oldest_email_queue_date,
1309 decode(change.accumulated_emails_one_day, 0, to_number(null), change.accumulated_emails_one_day),
1310 decode(change.accumulated_emails_three_days, 0, to_number(null), change.accumulated_emails_three_days),
1311 decode(change.accumulated_emails_week, 0, to_number(null), change.accumulated_emails_week),
1312 decode(change.accumulated_emails_week_plus, 0, to_number(null), change.accumulated_emails_week_plus),
1313 g_request_id,
1314 g_program_appl_id,
1315 g_program_id,
1316 g_sysdate);
1317
1318 COMMIT;
1319
1320 g_rows_ins_upd := g_rows_ins_upd + p_email_account_id.COUNT;
1321
1322 write_log('Finished procedure update_queue_measure at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
1323
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 write_log('Error in update_queue_measure : Error : ' || sqlerrm);
1327 RAISE;
1328 END update_queue_measure;
1329
1330 /***
1331 This splits the data to mutliple time buckets. Then it calls update_queue_measures
1332 which does the actual MERGE of the data
1333 ***/
1334
1335 PROCEDURE process_queue_measure_row(p_media_id IN OUT NOCOPY g_media_id_tab,
1336 p_email_account_id IN OUT NOCOPY g_email_account_id_tab,
1337 p_email_classification_id IN OUT NOCOPY g_email_classification_id_tab,
1338 p_party_id IN OUT NOCOPY g_party_id_tab,
1339 p_start_date_time IN OUT NOCOPY g_start_date_time_tab,
1340 p_end_date_time IN OUT NOCOPY g_end_date_time_tab,
1341 p_media_start_date_time IN OUT NOCOPY g_media_start_date_time_tab,
1342 p_period_start_date_time IN OUT NOCOPY g_start_date_time_tab,
1343 p_period_end_date_time IN OUT NOCOPY g_end_date_time_tab)
1344 IS
1345
1346 l_email_account_id g_email_account_id_tab;
1347 l_email_classification_id g_email_classification_id_tab;
1348 l_party_id g_party_id_tab;
1349 l_period_start_date g_period_start_date_tab;
1350 l_emails_in_queue g_emails_in_queue_tab;
1351 l_total_queue_time g_total_queue_time_tab;
1352 l_oldest_message_in_queue g_oldest_message_in_queue_tab;
1353 l_acc_emails_one_day g_acc_emails_one_day_tab;
1354 l_acc_emails_three_days g_acc_emails_three_days_tab;
1355 l_acc_emails_week g_acc_emails_week_tab;
1356 l_acc_emails_week_plus g_acc_emails_week_plus_tab;
1357
1358 l_temp_date DATE;
1359 l_begin_bucket_date DATE;
1360 l_end_bucket_date DATE;
1361 j NUMBER;
1362 l_party_id_temp NUMBER;
1363 l_diff_time NUMBER;
1364
1365 BEGIN
1366
1367 write_log('Start of the procedure process_queue_measure_row at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
1368
1369 /* Initialize the variables */
1370 j := 0;
1371 l_email_account_id := g_email_account_id_tab();
1372 l_email_classification_id := g_email_classification_id_tab();
1373 l_party_id := g_party_id_tab();
1374 l_period_start_date := g_period_start_date_tab();
1375 l_emails_in_queue := g_emails_in_queue_tab();
1376 l_total_queue_time := g_total_queue_time_tab();
1377 l_oldest_message_in_queue := g_oldest_message_in_queue_tab();
1378 l_acc_emails_one_day := g_acc_emails_one_day_tab();
1379 l_acc_emails_three_days := g_acc_emails_three_days_tab();
1380 l_acc_emails_week := g_acc_emails_week_tab();
1381 l_acc_emails_week_plus := g_acc_emails_week_plus_tab();
1382
1383 /* Loop through all the media returned by the cursor */
1384 FOR i IN p_media_id.FIRST .. p_media_id.LAST
1385 LOOP
1386
1387 l_begin_bucket_date := NULL;
1388 l_end_bucket_date := NULL;
1389 l_temp_date := NULL;
1390
1391 /* Get the start and the end bucket of time where the queue measures will be sliced into */
1392 /* if segment start date < collection start date , then start bucket = collection start date */
1393 /* else start bucket = time bucket of segment start date time */
1394 l_begin_bucket_date := p_period_start_date_time(i);
1395
1396 --1/2hr changed the addition to 1 from 1/48
1397 l_temp_date := l_begin_bucket_date + 1;
1398
1399 /* if segment end date > collection end date , then end bucket = collection end date */
1400 /* else end bucket = time bucket of segment end date time */
1401 l_end_bucket_date := p_period_end_date_time(i);
1402
1403 /* loop through the time range and slice the queue measures into appropiate 1/2 hr bucket */
1404 WHILE(l_begin_bucket_date <= l_end_bucket_date) LOOP
1405
1406 j := j + 1;
1407
1408 l_email_account_id.extend(1);
1409 l_email_classification_id.extend(1);
1410 l_party_id.extend(1);
1411 l_period_start_date.extend(1);
1412 l_emails_in_queue.extend(1);
1413 l_total_queue_time.extend(1);
1414 l_oldest_message_in_queue.extend(1);
1415 l_acc_emails_one_day.extend(1);
1416 l_acc_emails_three_days.extend(1);
1417 l_acc_emails_week.extend(1);
1418 l_acc_emails_week_plus.extend(1);
1419
1420 l_email_account_id(j) := p_email_account_id(i);
1421 l_email_classification_id(j) := p_email_classification_id(i);
1422 l_party_id(j) := p_party_id(i);
1423 l_period_start_date(j) := l_begin_bucket_date;
1424 l_emails_in_queue(j) := 0;
1425 l_total_queue_time(j) := 0;
1426 l_oldest_message_in_queue(j) := NULL;
1427 l_acc_emails_one_day(j) := 0;
1428 l_acc_emails_three_days(j) := 0;
1429 l_acc_emails_week(j) := 0;
1430 l_acc_emails_week_plus(j) := 0;
1431
1432 IF(p_end_date_time(i) BETWEEN l_begin_bucket_date AND l_temp_date
1433 AND p_end_date_time(i) <> g_collect_end_date ) THEN
1434 /* control reaching here means that the email has been removed from the queue in this time bucket */
1435 l_total_queue_time(j) := round((p_end_date_time(i) - p_start_date_time(i) ) * 24 * 60 * 60);
1436 ELSE
1437 /* control reaching here means that the email remains in queue at the end of time bucket */
1438 l_total_queue_time(j) := round((l_temp_date - p_start_date_time(i)) * 24 * 60 * 60);
1439 l_emails_in_queue(j) := 1;
1440 l_oldest_message_in_queue(j) := p_start_date_time(i);
1441
1442 /* Determine if the email is in the queue for 1 day , 3 days, a week or more than that */
1443 l_diff_time := l_temp_date - p_media_start_date_time(i);
1444
1445 IF (l_diff_time <= 1) THEN
1446 l_acc_emails_one_day(j) := 1;
1447 ELSIF (l_diff_time <= 3) THEN
1448 l_acc_emails_three_days(j) := 1;
1449 ELSIF (l_diff_time <= 7) THEN
1450 l_acc_emails_week(j) := 1;
1451 ELSE
1452 l_acc_emails_week_plus(j) := 1;
1453 END IF;
1454 END IF;
1455 --1/2hr changed from 1/48 to 1
1456 l_begin_bucket_date := l_begin_bucket_date + 1;
1457 --1/2hr changed from 1/48 to 1
1458 l_temp_date := l_temp_date + 1;
1459
1460 IF (j >= g_commit_chunk_size) THEN
1461 update_queue_measure (l_email_account_id,
1462 l_email_classification_id,
1463 l_party_id,
1464 l_period_start_date,
1465 l_emails_in_queue,
1466 l_total_queue_time,
1467 l_oldest_message_in_queue,
1468 l_acc_emails_one_day,
1469 l_acc_emails_three_days,
1470 l_acc_emails_week,
1471 l_acc_emails_week_plus);
1472
1473 l_email_account_id.TRIM(j);
1474 l_email_classification_id.TRIM(j);
1475 l_party_id.TRIM(j);
1476 l_period_start_date.TRIM(j);
1477 l_emails_in_queue.TRIM(j);
1478 l_total_queue_time.TRIM(j);
1479 l_oldest_message_in_queue.TRIM(j);
1480 l_acc_emails_one_day.TRIM(j);
1481 l_acc_emails_three_days.TRIM(j);
1482 l_acc_emails_week.TRIM(j);
1483 l_acc_emails_week_plus.TRIM(j);
1484
1485 j := 0;
1486 END IF;
1487
1488 END LOOP;-- End of inner While loop
1489
1490 END LOOP;
1491
1492 IF (l_email_account_id.COUNT >= 1) THEN
1493 update_queue_measure (l_email_account_id,
1494 l_email_classification_id,
1495 l_party_id,
1496 l_period_start_date,
1497 l_emails_in_queue,
1498 l_total_queue_time,
1499 l_oldest_message_in_queue,
1500 l_acc_emails_one_day,
1501 l_acc_emails_three_days,
1502 l_acc_emails_week,
1503 l_acc_emails_week_plus);
1504
1505 l_email_account_id.TRIM(j);
1506 l_email_classification_id.TRIM(j);
1507 l_party_id.TRIM(j);
1508 l_period_start_date.TRIM(j);
1509 l_emails_in_queue.TRIM(j);
1510 l_total_queue_time.TRIM(j);
1511 l_oldest_message_in_queue.TRIM(j);
1512 l_acc_emails_one_day.TRIM(j);
1513 l_acc_emails_three_days.TRIM(j);
1514 l_acc_emails_week.TRIM(j);
1515 l_acc_emails_week_plus.TRIM(j);
1516
1517 END IF;
1518
1519 write_log('Finished procedure process_queue_measure_row at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
1520
1521 EXCEPTION
1522 WHEN OTHERS THEN
1523 write_log('Error in process_queue_measure_row : Error : ' || sqlerrm);
1524 RAISE;
1525 END process_queue_measure_row;
1526
1527 /*====================================================================+
1528
1529 This procedure collects Queue measures. The queue cursor has two parts with union.
1530 The first part collects all the emails which are still in queue now.
1531 The second part of SQL collects all the emails which was in queue for some time and
1532 now they are no longer in queue .
1533
1534 Each eamil can be in queue for several days. Each email translates to multiple rows
1535 in summary table. for example if one emaiil is in queue for 2 days then two rows will
1536 be inserted one for each day with accumulated_emails_in_queue as 1 for both days.
1537
1538 If there is no emails in queue for particular combination dimensions
1539 this procedure populates null.
1540
1541 ====================================================================+*/
1542
1543
1544
1545 PROCEDURE collect_queue_measures AS
1546
1547 CURSOR queue_measures IS
1548 --
1549 --This is the query for collecting emails which are still in system queue
1550 --PERIOD START DATE will be either global start date or the start of the media segment
1551 --PERIOD END DATE will be the g_collect_end_date since the emails are still in
1552 --QUEUE.
1553 --
1554 SELECT /*+ use_hash(mitm) use_hash(mseg) use_hash(mtys) use_hash(cls) use_hash(inv2) */
1555 mitm.media_id MEDIA_ID,
1556 nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
1557 nvl(cls.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
1558 nvl(inv2.party_id, -1) PARTY_ID,
1559 mseg.start_date_time START_DATE_TIME,
1560 g_collect_end_date END_DATE_TIME,
1561 mitm.start_date_time MEDIA_START_DATE_TIME,
1562 --1/2hr
1563 decode(sign(mseg.start_date_time - g_collect_start_date), -1, g_collect_start_date,
1564 to_date(to_char(mseg.start_date_time ,'YYYY/MM/DD ')|| '00:'
1565 || '00',
1566 'YYYY/MM/DD HH24:MI')) PERIOD_START_DATE_TIME,
1567 --1/2hr do we need to modify the collect end data to capture data upto the second?
1568 g_collect_end_date -- - (( 29 * 60 + 59 )/(24*60*60))
1569 PERIOD_END_DATE_TIME
1570 FROM
1571 JTF_IH_MEDIA_ITEMS mitm,
1572 JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
1573 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys,
1574 --
1575 --Changes for R12
1576 --
1577 (
1578 select name, max(route_classification_id) route_classification_id
1579 from iem_route_classifications
1580 group by name
1581 ) cls,
1582 (
1583 --????Changed for party_id
1584 --SELECT /*+ use_hash(actv) use_hash(intr) */
1585 --actv.media_id media_id,
1586 --min(intr.party_id) party_id
1587 --FROM
1588 --jtf_ih_activities actv,
1589 --jtf_ih_interactions intr
1590 --WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
1591 --AND actv.interaction_id = intr.interaction_id
1592 --GROUP BY actv.media_id
1593 SELECT /*+ use_hash(actv) use_hash(intr) */
1594 distinct actv.media_id media_id,
1595 first_value(intr.party_id)
1596 over(partition by actv.media_id order by actv.interaction_id desc) party_id
1597 FROM
1598 jtf_ih_activities actv,
1599 jtf_ih_interactions intr
1600 WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
1601 AND actv.interaction_id = intr.interaction_id
1602 ) inv2
1603 WHERE mitm.MEDIA_ITEM_TYPE = 'EMAIL'
1604 AND mitm.DIRECTION = 'INBOUND'
1605 AND mitm.classification = cls.name(+)
1606 AND mitm.MEDIA_ID = mseg.MEDIA_ID
1607 AND mseg.START_DATE_TIME < g_collect_end_date
1608 AND mseg.MILCS_TYPE_ID = mtys.MILCS_TYPE_ID
1609 AND mtys.MILCS_CODE IN ('EMAIL_PROCESSING') /* Requeued segment removed for bug 5337716 */
1610 AND mitm.media_id = inv2.media_id
1611 AND NOT EXISTS
1612 (
1613 SELECT /*+ use_hash(mseg1) use_hash(mtys1) */
1614 1
1615 FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg1,
1616 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys1
1617 WHERE mseg.MEDIA_ID = mseg1.MEDIA_ID
1618 AND mseg1.MILCS_TYPE_ID = mtys1.MILCS_TYPE_ID
1619 AND mtys1.MILCS_CODE IN
1620 ('EMAIL_FETCH', 'EMAIL_RESOLVED', 'EMAIL_AUTO_REDIRECTED', 'EMAIL_AUTO_DELETED',
1621 'EMAIL_AUTO_REPLY', 'EMAIL_OPEN', 'EMAIL_AUTO_ROUTED', 'EMAIL_AUTO_UPDATED_SR',
1622 'EMAIL_ASSIGNED', 'EMAIL_ASSIGN_OPEN','EMAIL_DELETED')
1623 --
1624 --This condition is required since the email might have been fetched once but
1625 --then re-queued. In this case the email is still in QUEUE and wihout the following
1626 --condition will miss the record
1627 --
1628 AND mseg1.START_DATE_TIME >= mseg.START_DATE_TIME
1629 AND mseg1.START_DATE_TIME < g_collect_end_date
1630 )
1631 UNION
1632 --
1633 --This query is for emails which were in QUEUE for some time but are no longer in QUEUE
1634 --
1635 SELECT /*+ use_hash(mitm2) use_hash(inv2) use_hash(inv1) use_hash(mseg2) use_hash(mtys2) use_hash(cls2) */
1636 mitm2.media_id MEDIA_ID,
1637 nvl(mitm2.source_id, -1) EMAIL_ACCOUNT_ID,
1638 nvl(cls2.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
1639 nvl(inv1.party_id, -1) PARTY_ID,
1640 mseg2.start_date_time START_DATE_TIME,
1641 min(inv2.start_date_time) END_DATE_TIME,
1642 mitm2.start_date_time MEDIA_START_DATE_TIME,
1643 --1/2hr
1644 decode(sign(mseg2.start_date_time - g_collect_start_date), -1, g_collect_start_date,
1645 to_date(to_char(mseg2.start_date_time ,'YYYY/MM/DD ')|| '00:'
1646 || '00',
1647 'YYYY/MM/DD HH24:MI')) PERIOD_START_DATE_TIME,
1648 --1/2hr removed 1/2 hr lag in collect time
1649 decode(sign(g_collect_end_date - min(inv2.start_date_time)), -1, g_collect_end_date, -- - (( 29 * 60 + 59 )/(24*60*60))
1650 to_date(to_char(min(inv2.start_date_time) ,'YYYY/MM/DD ')|| '00:'
1651 || '00',
1652 'YYYY/MM/DD HH24:MI')) PERIOD_END_DATE_TIME
1653 FROM
1654 JTF_IH_MEDIA_ITEMS mitm2,
1655 (
1656 SELECT /*+ use_hash(mseg3) use_hash(mtys3) */
1657 mseg3.media_id,
1658 mseg3.resource_id,
1659 mseg3.start_date_time
1660 FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg3,
1661 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys3
1662 WHERE mseg3.MILCS_TYPE_ID = mtys3.MILCS_TYPE_ID
1663 AND mtys3.MILCS_CODE IN
1664 ('EMAIL_FETCH', 'EMAIL_RESOLVED', 'EMAIL_AUTO_REDIRECTED', 'EMAIL_AUTO_DELETED',
1665 'EMAIL_AUTO_REPLY', 'EMAIL_OPEN', 'EMAIL_AUTO_ROUTED', 'EMAIL_AUTO_UPDATED_SR',
1666 'EMAIL_ASSIGNED', 'EMAIL_ASSIGN_OPEN','EMAIL_DELETED')
1667 AND mseg3.START_DATE_TIME BETWEEN g_collect_start_date AND g_collect_end_date
1668 ) inv2,
1669 (
1670 --????Change for party_id
1671 --SELECT /*+ use_hash(actv) use_hash(intr) */
1672 --actv.media_id media_id,
1673 --min(intr.party_id) party_id
1674 --FROM
1675 --jtf_ih_activities actv,
1676 --jtf_ih_interactions intr
1677 --WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
1678 --AND actv.interaction_id = intr.interaction_id
1679 --GROUP BY actv.media_id
1680 SELECT /*+ use_hash(actv) use_hash(intr) */
1681 distinct actv.media_id media_id,
1682 first_value(intr.party_id)
1683 over(partition by actv.media_id order by actv.interaction_id desc) party_id
1684 FROM
1685 jtf_ih_activities actv,
1686 jtf_ih_interactions intr
1687 WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
1688 AND actv.interaction_id = intr.interaction_id
1689 ) inv1,
1690 JTF_IH_MEDIA_ITEM_LC_SEGS mseg2,
1691 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys2,
1692 --
1693 --Changes for R12
1694 --
1695 (
1696 select name, max(route_classification_id) route_classification_id
1697 from iem_route_classifications
1698 group by name
1699 ) cls2
1700 WHERE mitm2.MEDIA_ITEM_TYPE = 'EMAIL'
1701 AND mitm2.DIRECTION = 'INBOUND'
1702 AND mitm2.classification = cls2.name(+)
1703 AND mitm2.MEDIA_ID = inv2.MEDIA_ID
1704 AND inv2.MEDIA_ID = mseg2.MEDIA_ID
1705 AND mseg2.START_DATE_TIME < g_collect_end_date
1706 AND mseg2.MILCS_TYPE_ID = mtys2.MILCS_TYPE_ID
1707 AND inv2.START_DATE_TIME >= mseg2.START_DATE_TIME
1708 AND mitm2.media_id = inv1.media_id --???test with outer join removed here
1709 AND mtys2.MILCS_CODE IN ('EMAIL_PROCESSING')/* Requeued segment removed for bug 5337716 */
1710 GROUP BY
1711 mitm2.media_id,
1712 nvl(mitm2.source_id, -1),
1713 nvl(cls2.route_classification_id, -1),
1714 nvl(inv1.party_id, -1),
1715 mseg2.start_date_time,
1716 mitm2.start_date_time;
1717
1718 l_media_id g_media_id_tab;
1719 l_email_account_id g_email_account_id_tab;
1720 l_email_classification_id g_email_classification_id_tab;
1721 l_party_id g_party_id_tab;
1722 l_start_date_time g_start_date_time_tab;
1723 l_end_date_time g_end_date_time_tab;
1724 l_media_start_date_time g_media_start_date_time_tab;
1725 l_period_start_date_time g_start_date_time_tab;
1726 l_period_end_date_time g_end_date_time_tab;
1727
1728 l_no_of_records NUMBER;
1729 BEGIN
1730
1731 write_log('Start of the procedure collect_queue_measures at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1732
1733 OPEN queue_measures;
1734
1735 LOOP
1736
1737 /* Bulk collect queue information from OLTP tables and process them row by row */
1738 FETCH queue_measures BULK COLLECT INTO
1739 l_media_id,
1740 l_email_account_id,
1741 l_email_classification_id,
1742 l_party_id,
1743 l_start_date_time,
1744 l_end_date_time,
1745 l_media_start_date_time,
1746 l_period_start_date_time,
1747 l_period_end_date_time
1748 LIMIT g_commit_chunk_size;
1749
1750 l_no_of_records := l_media_id.COUNT;
1751
1752 IF (l_no_of_records > 0) THEN
1753 process_queue_measure_row(
1754 l_media_id,
1755 l_email_account_id,
1756 l_email_classification_id,
1757 l_party_id,
1758 l_start_date_time,
1759 l_end_date_time,
1760 l_media_start_date_time,
1761 l_period_start_date_time,
1762 l_period_end_date_time);
1763
1764 l_media_id.TRIM(l_no_of_records);
1765 l_email_account_id.TRIM(l_no_of_records);
1766 l_email_classification_id.TRIM(l_no_of_records);
1767 l_party_id.TRIM(l_no_of_records);
1768 l_start_date_time.TRIM(l_no_of_records);
1769 l_end_date_time.TRIM(l_no_of_records);
1770 l_media_start_date_time.TRIM(l_no_of_records);
1771 l_period_start_date_time.TRIM(l_no_of_records);
1772 l_period_end_date_time.TRIM(l_no_of_records);
1773 END IF;
1774
1775 EXIT WHEN queue_measures%NOTFOUND;
1776
1777 END LOOP;
1778
1779 CLOSE queue_measures;
1780
1781 /* Again estimating statistics here as these rows will be updated by open and resolution measures */
1782 DBMS_STATS.gather_table_stats(ownname => g_bix_schema,
1783 tabName => 'BIX_EMAIL_DETAILS_F',
1784 cascade => TRUE,
1785 degree => bis_common_parameters.get_degree_of_parallelism,
1786 estimate_percent => 10,
1787 granularity => 'GLOBAL');
1788
1789 write_log('Finished procedure collect_queue_measures at : ' ||
1790 to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1791 EXCEPTION
1792 WHEN OTHERS THEN
1793 write_log('Error in procedure collect_queue_measures : Error : ' || sqlerrm);
1794 IF (queue_measures%ISOPEN) THEN
1795 CLOSE queue_measures;
1796 END IF;
1797 RAISE;
1798 END collect_queue_measures;
1799
1800 PROCEDURE update_open_measure (p_email_account_id IN OUT NOCOPY g_email_account_id_tab,
1801 p_email_classification_id IN OUT NOCOPY g_email_classification_id_tab,
1802 p_party_id IN OUT NOCOPY g_party_id_tab,
1803 p_agent_id IN OUT NOCOPY g_resource_id_tab,
1804 p_period_start_date IN OUT NOCOPY g_period_start_date_tab,
1805 p_emails_open IN OUT NOCOPY g_emails_open_tab,
1806 p_total_open_age IN OUT NOCOPY g_total_open_age_tab,
1807 p_oldest_open_message IN OUT NOCOPY g_oldest_open_message_tab,
1808 p_acc_emails_one_day IN OUT NOCOPY g_acc_emails_one_day_tab,
1809 p_acc_emails_three_days IN OUT NOCOPY g_acc_emails_three_days_tab,
1810 p_acc_emails_week IN OUT NOCOPY g_acc_emails_week_tab,
1811 p_acc_emails_week_plus IN OUT NOCOPY g_acc_emails_week_plus_tab) IS
1812 BEGIN
1813
1814 write_log('Start of the procedure update_open_measure at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
1815
1816 /* Update ICI summary table with open measure */
1817 FORALL i in p_email_account_id.FIRST .. p_email_account_id.LAST
1818 MERGE INTO bix_email_details_f bed
1819 USING (
1820 SELECT
1821 p_email_account_id(i) email_account_id,
1822 p_email_classification_id(i) email_classification_id,
1823 p_agent_id(i) agent_id,
1824 p_party_id(i) party_id,
1825 to_number(to_char(p_period_start_date(i), 'J')) time_id,
1826 --1/2hr changed period type id to 1 from -1
1827 1 period_type_id,
1828 trunc(p_period_start_date(i)) period_start_date,
1829 LPAD(TO_CHAR(p_period_start_date(i),'HH24:MI'), 5, '0') period_start_time,
1830 nvl(p_emails_open(i),0) accumulated_open_emails,
1831 nvl(p_total_open_age(i),0) accumulated_open_age,
1832 p_oldest_open_message(i) oldest_email_open_date,
1833 nvl(p_acc_emails_one_day(i),0) accumulated_emails_one_day,
1834 nvl(p_acc_emails_three_days(i),0) accumulated_emails_three_days,
1835 nvl(p_acc_emails_week(i),0) accumulated_emails_week,
1836 nvl(p_acc_emails_week_plus(i),0) accumulated_emails_week_plus
1837 FROM dual) change
1838 ON (bed.email_account_id = change.email_account_id
1839 AND bed.email_classification_id = change.email_classification_id
1840 AND bed.agent_id = change.agent_id
1841 AND bed.party_id = change.party_id
1842 AND bed.time_id = change.time_id
1843 AND bed.period_type_id = change.period_type_id
1844 AND bed.period_start_date = change.period_start_date
1845 AND bed.period_start_time = change.period_start_time
1846 AND bed.outcome_id = -1 AND bed.result_id = -1 AND bed.reason_id = -1 )
1847 WHEN MATCHED THEN
1848 UPDATE
1849 SET
1850 bed.accumulated_open_emails = decode(change.accumulated_open_emails, 0, bed.accumulated_open_emails,
1851 NVL(bed.accumulated_open_emails, 0) + change.accumulated_open_emails),
1852 bed.accumulated_open_age = decode(change.accumulated_open_age, 0, bed.accumulated_open_age,
1853 NVL(bed.accumulated_open_age, 0) + change.accumulated_open_age),
1854 bed.accumulated_emails_one_day = decode(change.accumulated_emails_one_day, 0, bed.accumulated_emails_one_day,
1855 NVL(bed.accumulated_emails_one_day, 0) + change.accumulated_emails_one_day),
1856 bed.accumulated_emails_three_days = decode(change.accumulated_emails_three_days, 0, bed.accumulated_emails_three_days,
1857 NVL(bed.accumulated_emails_three_days, 0) + change.accumulated_emails_three_days),
1858 bed.accumulated_emails_week = decode(change.accumulated_emails_week, 0, bed.accumulated_emails_week,
1859 NVL(bed.accumulated_emails_week, 0) + change.accumulated_emails_week),
1860 bed.accumulated_emails_week_plus = decode(change.accumulated_emails_week_plus, 0, bed.accumulated_emails_week_plus,
1861 NVL(bed.accumulated_emails_week_plus, 0) + change.accumulated_emails_week_plus),
1862 bed.oldest_email_open_date =
1863 DECODE(change.oldest_email_open_date,NULL,bed.oldest_email_open_date,
1864 DECODE(bed.oldest_email_open_date,NULL,change.oldest_email_open_date,
1865 DECODE(SIGN(bed.oldest_email_open_date - change.oldest_email_open_date),
1866 -1,bed.oldest_email_open_date, change.oldest_email_open_date))),
1867 bed.last_update_date = g_sysdate,
1868 bed.last_updated_by = g_user_id
1869 WHEN NOT MATCHED THEN INSERT (
1870 bed.email_account_id,
1871 bed.email_classification_id,
1872 bed.agent_id,
1873 bed.party_id,
1874 bed.outcome_id,
1875 bed.result_id,
1876 bed.reason_id,
1877 bed.time_id,
1878 bed.period_type_id,
1879 bed.period_start_date,
1880 bed.period_start_time,
1881 bed.created_by,
1882 bed.creation_date,
1883 bed.last_updated_by,
1884 bed.last_update_date,
1885 bed.accumulated_open_emails,
1886 bed.accumulated_open_age,
1887 bed.oldest_email_open_date,
1888 bed.accumulated_emails_one_day,
1889 bed.accumulated_emails_three_days,
1890 bed.accumulated_emails_week,
1891 bed.accumulated_emails_week_plus,
1892 bed.request_id,
1893 bed.program_application_id,
1894 bed.program_id,
1895 bed.program_update_date)
1896 VALUES (
1897 change.email_account_id,
1898 change.email_classification_id,
1899 change.agent_id,
1900 change.party_id,
1901 -1,
1902 -1,
1903 -1,
1904 change.time_id,
1905 change.period_type_id,
1906 change.period_start_date,
1907 change.period_start_time,
1908 g_user_id,
1909 g_sysdate,
1910 g_user_id,
1911 g_sysdate,
1912 decode(change.accumulated_open_emails, 0, to_number(null), change.accumulated_open_emails),
1913 decode(change.accumulated_open_age, 0, to_number(null), change.accumulated_open_age),
1914 change.oldest_email_open_date,
1915 decode(change.accumulated_emails_one_day, 0, to_number(null), change.accumulated_emails_one_day),
1916 decode(change.accumulated_emails_three_days, 0, to_number(null), change.accumulated_emails_three_days),
1917 decode(change.accumulated_emails_week, 0, to_number(null), change.accumulated_emails_week),
1918 decode(change.accumulated_emails_week_plus, 0, to_number(null), change.accumulated_emails_week_plus),
1919 g_request_id,
1920 g_program_appl_id,
1921 g_program_id,
1922 g_sysdate);
1923
1924 COMMIT;
1925
1926 g_rows_ins_upd := g_rows_ins_upd + p_email_account_id.COUNT;
1927
1928 write_log('Finished procedure update_open_measure at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
1929 EXCEPTION
1930 WHEN OTHERS THEN
1931 write_log('Error in update_open_measure : Error : ' || sqlerrm);
1932 RAISE;
1933 END update_open_measure;
1934
1935 /* This procedure collects all the open related measures
1936 This splits the data to mutliple time buckets. Then it calls update_open_measures
1937 which does the actual MERGE of the data
1938 */
1939 PROCEDURE process_open_measure_row(p_media_id IN OUT NOCOPY g_media_id_tab,
1940 p_email_account_id IN OUT NOCOPY g_email_account_id_tab,
1941 p_email_classification_id IN OUT NOCOPY g_email_classification_id_tab,
1942 p_resource_id IN OUT NOCOPY g_resource_id_tab,
1943 p_party_id IN OUT NOCOPY g_party_id_tab,
1944 p_start_date_time IN OUT NOCOPY g_start_date_time_tab,
1945 p_end_date_time IN OUT NOCOPY g_end_date_time_tab,
1946 p_media_start_date_time IN OUT NOCOPY g_media_start_date_time_tab,
1947 p_period_start_date_time IN OUT NOCOPY g_start_date_time_tab,
1948 p_period_end_date_time IN OUT NOCOPY g_end_date_time_tab) IS
1949
1950 l_email_account_id g_email_account_id_tab;
1951 l_email_classification_id g_email_classification_id_tab;
1952 l_party_id g_party_id_tab;
1953 l_agent_id g_resource_id_tab;
1954 l_period_start_date g_period_start_date_tab;
1955 l_emails_open g_emails_open_tab;
1956 l_total_open_age g_total_open_age_tab;
1957 l_oldest_open_message g_oldest_open_message_tab;
1958 l_acc_emails_one_day g_acc_emails_one_day_tab;
1959 l_acc_emails_three_days g_acc_emails_three_days_tab;
1960 l_acc_emails_week g_acc_emails_week_tab;
1961 l_acc_emails_week_plus g_acc_emails_week_plus_tab;
1962
1963 l_temp_date DATE;
1964 l_begin_bucket_date DATE;
1965 l_end_bucket_date DATE;
1966 l_next_seg_start_date DATE;
1967 l_party_id_temp NUMBER;
1968 j NUMBER;
1969 l_diff_time NUMBER;
1970
1971 BEGIN
1972
1973 write_log('Start of the procedure process_open_measure_row at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1974
1975 /* Initialize the variables */
1976 j := 0;
1977 l_email_account_id := g_email_account_id_tab();
1978 l_email_classification_id := g_email_classification_id_tab();
1979 l_party_id := g_party_id_tab();
1980 l_agent_id := g_resource_id_tab();
1981 l_period_start_date := g_period_start_date_tab();
1982 l_emails_open := g_emails_open_tab();
1983 l_total_open_age := g_total_open_age_tab();
1984 l_oldest_open_message := g_oldest_open_message_tab();
1985 l_acc_emails_one_day := g_acc_emails_one_day_tab();
1986 l_acc_emails_three_days := g_acc_emails_three_days_tab();
1987 l_acc_emails_week := g_acc_emails_week_tab();
1988 l_acc_emails_week_plus := g_acc_emails_week_plus_tab();
1989
1990 /* Loop through all the media returned by the cursor */
1991 FOR i IN p_media_id.FIRST .. p_media_id.LAST
1992 LOOP
1993 l_begin_bucket_date := NULL;
1994 l_end_bucket_date := NULL;
1995 l_temp_date := NULL;
1996
1997 /* Get the start and the end bucket of time where the open measures will be sliced into */
1998 /* if segment start date < collection start date , then start bucket = collection start date */
1999 /* else start bucket = time bucket of segment start date time */
2000 l_begin_bucket_date := p_period_start_date_time(i);
2001 --1/2hr changed increment to 1 from 1/48
2002 --l_temp_date := l_begin_bucket_date + 1/48;
2003 l_temp_date := l_begin_bucket_date + 1;
2004 l_next_seg_start_date := p_end_date_time(i);
2005
2006 /* if segment end date > collection end date , then end bucket = collection end date */
2007 /* else end bucket = time bucket of segment end date time */
2008 l_end_bucket_date := p_period_end_date_time(i);
2009
2010 /* loop through the time range and slice the open measures into appropiate 1/2 hr bucket */
2011 WHILE(l_begin_bucket_date <= l_end_bucket_date) LOOP
2012
2013 j := j + 1;
2014
2015 l_email_account_id.extend(1);
2016 l_email_classification_id.extend(1);
2017 l_party_id.extend(1);
2018 l_agent_id.extend(1);
2019 l_period_start_date.extend(1);
2020 l_emails_open.extend(1);
2021 l_total_open_age.extend(1);
2022 l_oldest_open_message.extend(1);
2023 l_acc_emails_one_day.extend(1);
2024 l_acc_emails_three_days.extend(1);
2025 l_acc_emails_week.extend(1);
2026 l_acc_emails_week_plus.extend(1);
2027
2028 l_email_account_id(j) := p_email_account_id(i);
2029 l_email_classification_id(j) := p_email_classification_id(i);
2030 l_party_id(j) := p_party_id(i);
2031 l_agent_id(j) := p_resource_id(i);
2032 l_period_start_date(j) := l_begin_bucket_date;
2033 l_emails_open(j) := 0;
2034 l_total_open_age(j) := 0;
2035 l_oldest_open_message(j) := NULL;
2036 l_acc_emails_one_day(j) := 0;
2037 l_acc_emails_three_days(j) := 0;
2038 l_acc_emails_week(j) := 0;
2039 l_acc_emails_week_plus(j) := 0;
2040
2041 IF (l_next_seg_start_date BETWEEN l_begin_bucket_date AND l_temp_date
2042 AND l_next_seg_start_date <> g_collect_end_date ) THEN
2043 /* control reaching here means that the email is no longer open at the end of this time bucket */
2044 l_total_open_age(j) := round((l_next_seg_start_date - p_start_date_time(i) ) * 24 * 60 * 60);
2045 ELSE
2046 /* control reaching here means that the email is still open at the end of this time bucket */
2047 l_total_open_age(j) := round((l_temp_date - p_start_date_time(i)) * 24 * 60 * 60);
2048 l_emails_open(j) := 1;
2049 l_oldest_open_message(j) := p_start_date_time(i);
2050
2051 /* Determine if the email is in the queue for 1 day , 3 days, a week or more than that */
2052 l_diff_time := l_temp_date - p_media_start_date_time(i);
2053
2054 IF (l_diff_time <= 1) THEN
2055 l_acc_emails_one_day(j) := 1;
2056 ELSIF (l_diff_time <= 3) THEN
2057 l_acc_emails_three_days(j) := 1;
2058 ELSIF (l_diff_time <= 7) THEN
2059 l_acc_emails_week(j) := 1;
2060 ELSE
2061 l_acc_emails_week_plus(j) := 1;
2062 END IF;
2063
2064 END IF;
2065
2066 IF (j >= g_commit_chunk_size) THEN
2067 update_open_measure (l_email_account_id,
2068 l_email_classification_id,
2069 l_party_id,
2070 l_agent_id,
2071 l_period_start_date,
2072 l_emails_open,
2073 l_total_open_age,
2074 l_oldest_open_message,
2075 l_acc_emails_one_day,
2076 l_acc_emails_three_days,
2077 l_acc_emails_week,
2078 l_acc_emails_week_plus);
2079
2080 l_email_account_id.TRIM(j);
2081 l_email_classification_id.TRIM(j);
2082 l_party_id.TRIM(j);
2083 l_agent_id.TRIM(j);
2084 l_period_start_date.TRIM(j);
2085 l_emails_open.TRIM(j);
2086 l_total_open_age.TRIM(j);
2087 l_oldest_open_message.TRIM(j);
2088 l_acc_emails_one_day.TRIM(j);
2089 l_acc_emails_three_days.TRIM(j);
2090 l_acc_emails_week.TRIM(j);
2091 l_acc_emails_week_plus.TRIM(j);
2092
2093 j := 0;
2094 END IF;
2095 --1/2hr changed increment to 1 from 1/48
2096 --l_begin_bucket_date := l_begin_bucket_date + 1/48;
2097 l_begin_bucket_date := l_begin_bucket_date + 1;
2098 --1/2hr changed increment to 1 from 1/48
2099 --l_temp_date := l_temp_date + 1/48;
2100 l_temp_date := l_temp_date + 1;
2101 END LOOP;-- End of inner While loop
2102
2103 END LOOP;
2104
2105 IF (l_email_account_id.COUNT >= 1) THEN
2106 update_open_measure (l_email_account_id,
2107 l_email_classification_id,
2108 l_party_id,
2109 l_agent_id,
2110 l_period_start_date,
2111 l_emails_open,
2112 l_total_open_age,
2113 l_oldest_open_message,
2114 l_acc_emails_one_day,
2115 l_acc_emails_three_days,
2116 l_acc_emails_week,
2117 l_acc_emails_week_plus);
2118
2119 l_email_account_id.TRIM(j);
2120 l_email_classification_id.TRIM(j);
2121 l_party_id.TRIM(j);
2122 l_agent_id.TRIM(j);
2123 l_period_start_date.TRIM(j);
2124 l_emails_open.TRIM(j);
2125 l_total_open_age.TRIM(j);
2126 l_oldest_open_message.TRIM(j);
2127 l_acc_emails_one_day.TRIM(j);
2128 l_acc_emails_three_days.TRIM(j);
2129 l_acc_emails_week.TRIM(j);
2130 l_acc_emails_week_plus.TRIM(j);
2131
2132 END IF;
2133
2134 write_log('Finished procedure process_open_measure_row at ' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
2135 EXCEPTION
2136 WHEN OTHERS THEN
2137 write_log('Error in process_open_measure_row : Error : ' || sqlerrm);
2138 RAISE;
2139 END process_open_measure_row;
2140
2141 /*============================================================================================+
2142
2143 This procedure collects all the open related measures. The queue cursor has two parts with union.
2144 The first part collects all the emails whIch are still Open (in Agent inbox) now.
2145 The second part of SQL collects all the emails which was open for some time and
2146 now they are no longer open.
2147
2148 Each eamil can be sitting in agent inbox for several days. Each email translates to multiple rows
2149 in summary table. for example if one emaiil is in agent inbox for 2 days then two rows will
2150 be inserted one for each day with accumulated_open_emails as 1 for both days.
2151
2152 ================================================================================================*/
2153
2154 PROCEDURE collect_open_measures AS
2155
2156 CURSOR open_measures IS
2157 SELECT /*+ use_hash(mitm) use_hash(mseg) use_hash(mtys) use_hash(cls) use_hash(inv2) */
2158 mitm.media_id MEDIA_ID,
2159 nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
2160 nvl(cls.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
2161 nvl(mseg.resource_id,-1) RESOURCE_ID,
2162 nvl(inv2.party_id, -1) PARTY_ID,
2163 mseg.start_date_time START_DATE_TIME,
2164 g_collect_end_date END_DATE_TIME,
2165 mitm.start_date_time MEDIA_START_DATE_TIME,
2166 --1/2hr
2167 decode(sign(mseg.start_date_time - g_collect_start_date), -1, g_collect_start_date,
2168 to_date(to_char(mseg.start_date_time ,'YYYY/MM/DD ')|| '00:'
2169 || '00',
2170 'YYYY/MM/DD HH24:MI')) PERIOD_START_DATE_TIME,
2171 --1/2hr
2172 g_collect_end_date
2173 PERIOD_END_DATE_TIME
2174 FROM
2175 JTF_IH_MEDIA_ITEMS mitm,
2176 JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
2177 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys,
2178 --
2179 --Changes for R12
2180 --
2181 (
2182 select name, max(route_classification_id) route_classification_id
2183 from iem_route_classifications
2184 group by name
2185 ) cls,
2186 (
2187 --???Changed for party
2188 --SELECT /*+ use_hash(actv) use_hash(intr) */
2189 --actv.media_id media_id,
2190 --min(intr.party_id) party_id
2191 --FROM
2192 --jtf_ih_activities actv,
2193 --jtf_ih_interactions intr
2194 --WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
2195 --AND actv.interaction_id = intr.interaction_id
2196 --GROUP BY actv.media_id
2197 SELECT /*+ use_hash(actv) use_hash(intr) */
2198 distinct actv.media_id media_id,
2199 first_value(intr.party_id)
2200 over(partition by actv.media_id order by actv.interaction_id desc) party_id
2201 FROM
2202 jtf_ih_activities actv,
2203 jtf_ih_interactions intr
2204 WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
2205 AND actv.interaction_id = intr.interaction_id
2206 ) inv2
2207 WHERE mitm.media_item_type = 'EMAIL'
2208 AND mitm.direction = 'INBOUND'
2209 AND mitm.classification = cls.name(+)
2210 AND mitm.media_id = mseg.media_id
2211 AND mseg.start_date_time < g_collect_end_date
2212 AND mseg.milcs_type_id = mtys.milcs_type_id
2213 AND mtys.milcs_code IN ('EMAIL_FETCH','EMAIL_TRANSFER', 'EMAIL_ASSIGN_OPEN', 'EMAIL_AUTO_ROUTED', 'EMAIL_ASSIGNED')
2214 AND mitm.media_id = inv2.media_id --??? test with outer join removed here
2215 AND NOT EXISTS
2216 (
2217 SELECT /*+ use_hash(mseg1) use_hash(mtys1) */
2218 1
2219 FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg1,
2220 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys1
2221 WHERE mseg.media_id = mseg1.media_id
2222 -- AND mseg.resource_id = mseg1.resource_id
2223 /* Commenting this join out because the supervisor can perform some of the operations below.
2224 Irrespective of which user did it, the email is not open any more */
2225 AND mseg1.milcs_type_id = mtys1.milcs_type_id
2226 AND mtys1.milcs_code IN ('EMAIL_REPLY','EMAIL_DELETED','EMAIL_TRANSFERRED', 'EMAIL_ESCALATED', 'EMAIL_REQUEUED',
2227 'EMAIL_ASSIGNED','EMAIL_REROUTED_DIFF_ACCT', 'EMAIL_REROUTED_DIFF_CLASS')
2228 AND mseg1.START_DATE_TIME >= mseg.START_DATE_TIME
2229 AND mseg1.start_date_time < g_collect_end_date
2230 AND mseg1.milcs_id <> mseg.milcs_id
2231 )
2232 UNION
2233 SELECT /*+ use_hash(mitm) use_hash(inv2) use_hash(inv1) use_hash(mseg) use_hash(mtys) use_hash(cls) */
2234 mitm.media_id MEDIA_ID,
2235 nvl(mitm.source_id, -1) EMAIL_ACCOUNT_ID,
2236 nvl(cls.route_classification_id, -1) EMAIL_CLASSIFICATION_ID,
2237 nvl(mseg.resource_id, -1) RESOURCE_ID,
2238 nvl(inv1.party_id, -1) PARTY_ID,
2239 mseg.start_date_time START_DATE_TIME,
2240 min(inv2.start_date_time) END_DATE_TIME,
2241 mitm.start_date_time MEDIA_START_DATE_TIME,
2242 --1/2hr
2243 decode(sign(mseg.start_date_time - g_collect_start_date), -1, g_collect_start_date,
2244 to_date(to_char(mseg.start_date_time ,'YYYY/MM/DD ')|| '00:'
2245 || '00',
2246 'YYYY/MM/DD HH24:MI')) PERIOD_START_DATE_TIME,
2247 decode(sign(g_collect_end_date - min(inv2.start_date_time)), -1, g_collect_end_date - (( 29 * 60 + 59 )/(24*60*60)),
2248 to_date(to_char(min(inv2.start_date_time) ,'YYYY/MM/DD ')|| '00:'
2249 || '00',
2250 'YYYY/MM/DD HH24:MI')) PERIOD_END_DATE_TIME
2251 FROM
2252 JTF_IH_MEDIA_ITEMS mitm,
2253 (
2254 SELECT /*+ use_hash(mseg1) use_hash(mtys1) */
2255 mseg1.media_id MEDIA_ID,
2256 mseg1.resource_id RESOURCE_ID,
2257 mseg1.start_date_time START_DATE_TIME
2258 FROM JTF_IH_MEDIA_ITEM_LC_SEGS mseg1,
2259 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys1
2260 WHERE mseg1.MILCS_TYPE_ID = mtys1.MILCS_TYPE_ID
2261 AND mtys1.MILCS_CODE IN ('EMAIL_REPLY','EMAIL_DELETED','EMAIL_TRANSFERRED', 'EMAIL_ESCALATED',
2262 'EMAIL_ASSIGNED','EMAIL_REQUEUED', 'EMAIL_REROUTED_DIFF_ACCT', 'EMAIL_REROUTED_DIFF_CLASS')
2263 AND mseg1.START_DATE_TIME BETWEEN g_collect_start_date AND g_collect_end_date
2264 ) inv2,
2265 (
2266 --????Change for party_id
2267 --SELECT /*+ use_hash(actv) use_hash(intr) */
2268 --actv.media_id media_id,
2269 --min(intr.party_id) party_id
2270 --FROM
2271 --jtf_ih_activities actv,
2272 --jtf_ih_interactions intr
2273 --WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
2274 --AND actv.interaction_id = intr.interaction_id
2275 --GROUP BY actv.media_id
2276 SELECT /*+ use_hash(actv) use_hash(intr) */
2277 distinct actv.media_id media_id,
2278 first_value(intr.party_id)
2279 over(partition by actv.media_id order by actv.interaction_id desc) party_id
2280 FROM
2281 jtf_ih_activities actv,
2282 jtf_ih_interactions intr
2283 WHERE actv.start_date_time BETWEEN g_collect_start_date AND g_collect_end_date
2284 AND actv.interaction_id = intr.interaction_id
2285 ) inv1,
2286 JTF_IH_MEDIA_ITEM_LC_SEGS mseg,
2287 JTF_IH_MEDIA_ITM_LC_SEG_TYS mtys,
2288 --
2289 --Changes for R12
2290 --
2291 (
2292 select name, max(route_classification_id) route_classification_id
2293 from iem_route_classifications
2294 group by name
2295 ) cls
2296 WHERE mitm.media_id = inv2.media_id
2297 AND inv2.media_id = mseg.media_id
2298 -- AND inv2.resource_id = mseg.resource_id
2299 /* Commenting this join out because the supervisor performs some operations like delete and requeued.
2300 Irrespective of which user did it, the email was not open. Lets say Email fetched by agent a, email transferred by a,
2301 email transfer to b, email requeue to c. If we remove this condition, we anyways take min (inv2.start_date_time),
2302 so a will get the email transferred start date time (done by him), b will get requeue start date time (done by c) */
2303 AND mitm.media_item_type = 'EMAIL'
2304 AND mitm.direction = 'INBOUND'
2305 AND mitm.classification = cls.name(+)
2306 AND mseg.start_date_time < g_collect_end_date
2307 AND mseg.milcs_type_id = mtys.milcs_type_id
2308 AND inv2.START_DATE_TIME >= mseg.START_DATE_TIME
2309 AND mtys.milcs_code IN ('EMAIL_FETCH','EMAIL_TRANSFER', 'EMAIL_ASSIGN_OPEN', 'EMAIL_AUTO_ROUTED')
2310 AND mitm.media_id = inv1.media_id --???test with outer join removed here
2311 GROUP BY
2312 mitm.media_id,
2313 nvl(mitm.source_id, -1),
2314 nvl(cls.route_classification_id, -1),
2315 nvl(mseg.resource_id, -1),
2316 nvl(inv1.party_id, -1),
2317 mseg.start_date_time,
2318 mitm.start_date_time;
2319
2320 l_media_id g_media_id_tab;
2321 l_email_account_id g_email_account_id_tab;
2322 l_email_classification_id g_email_classification_id_tab;
2323 l_resource_id g_resource_id_tab;
2324 l_party_id g_party_id_tab;
2325 l_start_date_time g_start_date_time_tab;
2326 l_end_date_time g_end_date_time_tab;
2327 l_media_start_date_time g_media_start_date_time_tab;
2328 l_period_start_date_time g_start_date_time_tab;
2329 l_period_end_date_time g_end_date_time_tab;
2330
2331 l_no_of_records NUMBER;
2332 BEGIN
2333
2334 write_log('Start of the procedure collect_open_measures at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
2335
2336 OPEN open_measures;
2337
2338 LOOP
2339
2340 /* Bulk collect open information and process them row by row */
2341 FETCH open_measures BULK COLLECT INTO
2342 l_media_id,
2343 l_email_account_id,
2344 l_email_classification_id,
2345 l_resource_id,
2346 l_party_id,
2347 l_start_date_time,
2348 l_end_date_time,
2349 l_media_start_date_time,
2350 l_period_start_date_time,
2351 l_period_end_date_time
2352 LIMIT g_commit_chunk_size;
2353
2354 l_no_of_records := l_media_id.COUNT;
2355
2356 IF (l_no_of_records > 0) THEN
2357 process_open_measure_row(
2358 l_media_id,
2359 l_email_account_id,
2360 l_email_classification_id,
2361 l_resource_id,
2362 l_party_id,
2363 l_start_date_time,
2364 l_end_date_time,
2365 l_media_start_date_time,
2366 l_period_start_date_time,
2367 l_period_end_date_time);
2368
2369 l_media_id.TRIM(l_no_of_records);
2370 l_email_account_id.TRIM(l_no_of_records);
2371 l_email_classification_id.TRIM(l_no_of_records);
2372 l_resource_id.TRIM(l_no_of_records);
2373 l_party_id.TRIM(l_no_of_records);
2374 l_start_date_time.TRIM(l_no_of_records);
2375 l_end_date_time.TRIM(l_no_of_records);
2376 l_media_start_date_time.TRIM(l_no_of_records);
2377 l_period_start_date_time.TRIM(l_no_of_records);
2378 l_period_end_date_time.TRIM(l_no_of_records);
2379 END IF;
2380
2381 EXIT WHEN open_measures%NOTFOUND;
2382
2383 END LOOP;
2384
2385 CLOSE open_measures;
2386
2387 /* Estimating statistics as these rows will be updated by resolution measures */
2388 DBMS_STATS.gather_table_stats(ownname => g_bix_schema,
2389 tabName => 'BIX_EMAIL_DETAILS_F',
2390 cascade => TRUE,
2391 degree => bis_common_parameters.get_degree_of_parallelism,
2392 estimate_percent => 10,
2393 granularity => 'GLOBAL');
2394
2395 write_log('Finished procedure collect_open_measures at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
2396 EXCEPTION
2397 WHEN OTHERS THEN
2398 write_log('Error in procedure collect_open_measures : Error : ' || sqlerrm);
2399 IF (open_measures%ISOPEN) THEN
2400 CLOSE open_measures;
2401 END IF;
2402 RAISE;
2403 END collect_open_measures;
2404
2405 /*
2406 This procedure collect all data by Day. This procedure inturn calls serveral other procedures
2407 to collect different measures. It calls the following procedures .
2408
2409 collect_emails: This procedure collects all additive mesaues like fetched, replied etc.
2410 collect_queue_measures : This procedure collects Queue Measures
2411 collect_open_measures : This procedure collects Open measures
2412 collect_resolutions: This procedure collects One Done resolution measures.
2413 */
2414
2415 PROCEDURE collect_day IS
2416 BEGIN
2417
2418 write_log('Start of the procedure collect_half_hour at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
2419
2420 /* collects all the Email additive measures from oltp tables */
2421 write_log('Calling procedure collect_emails');
2422 collect_emails;
2423 write_log('End procedure collect_emails');
2424
2425 /* collect queue informations like queue time */
2426 write_log('Calling procedure collect_queue_measures');
2427 collect_queue_measures;
2428 write_log('End procedure collect_queue_measures');
2429
2430 /* collect open informations like # of emails open at end of time bucket */
2431 write_log('Calling procedure collect_open_measures');
2432 collect_open_measures;
2433 write_log('End procedure collect_open_measures');
2434
2435 /* Collect the resolutions measures */
2436 write_log('Calling procedure collect_resolutions');
2437 collect_resolutions;
2438 write_log('End procedure collect_resolutions');
2439
2440 write_log('Finished procedure collect_half_hour at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
2441
2442 EXCEPTION
2443 WHEN OTHERS THEN
2444 write_log('Error in procedure collect_half_hour : Error : ' || sqlerrm);
2445 RAISE;
2446 END collect_day;
2447
2448
2449 /*====================================================================
2450
2451 This procedure summarizes day level rows to week, period, Quarter and Year.
2452
2453 NOTE:
2454 For accumulated measures we need to go to the end of the time bucket. Example
2455 the last day of the week or month and use the acumulated measures from that time
2456 for the ROLLUP. FOr this we use FIRST_VALUE clause.
2457
2458 =======================================================================*/
2459
2460 PROCEDURE summarize_data IS
2461
2462 BEGIN
2463
2464 write_log('Start of the procedure summarize_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
2465
2466 write_log('Merging half hour rows to day, week, month, quarter, year bucket in table bix_email_details_f');
2467
2468 /* Rollup half hour informations to day, week, month, quarter, year time bucket for table bix_email_details_f */
2469 /* An outer group by sql is required after rollup as rollup produces two rows for weeks spanning tow months */
2470 /* the rollup of oldess_email_open_date, oldest_email_queue_date, accumulated_open_age, accumulated_queue_time */
2471 /* are not calculated as simple min or sum will produce the wrong result ; we have to fix in the future rlease */
2472
2473 INSERT /*+ append */ INTO bix_email_details_f (
2474 agent_id,
2475 email_account_id,
2476 email_classification_id,
2477 party_id,
2478 time_id,
2479 period_type_id,
2480 period_start_date,
2481 period_start_time,
2482 outcome_id,
2483 result_id,
2484 reason_id,
2485 created_by,
2486 creation_date,
2487 last_updated_by,
2488 last_update_date,
2489 emails_offered_in_period,
2490 emails_fetched_in_period,
2491 emails_replied_in_period,
2492 emails_rpld_by_goal_in_period,
2493 AGENT_EMAILS_RPLD_BY_GOAL,
2494 emails_deleted_in_period,
2495 emails_trnsfrd_out_in_period,
2496 emails_trnsfrd_in_in_period,
2497 emails_rsl_and_trfd_in_period,
2498 emails_assigned_in_period,
2499 emails_auto_routed_in_period,
2500 emails_auto_uptd_sr_in_period,
2501 sr_created_in_period,
2502 oldest_email_open_date,
2503 oldest_email_queue_date,
2504 email_resp_time_in_period,
2505 agent_resp_time_in_period,
2506 accumulated_open_emails,
2507 accumulated_open_age,
2508 accumulated_emails_in_queue,
2509 accumulated_queue_time,
2510 accumulated_emails_one_day,
2511 accumulated_emails_three_days,
2512 accumulated_emails_week,
2513 accumulated_emails_week_plus,
2514 one_rsln_in_period,
2515 two_rsln_in_period,
2516 three_rsln_in_period,
2517 four_rsln_in_period,
2518 interaction_threads_in_period,
2519 emails_orr_count_in_period,
2520 EMAILS_AUTO_REPLIED_IN_PERIOD,
2521 EMAILS_AUTO_DELETED_IN_PERIOD,
2522 EMAILS_AUTO_RESOLVED_IN_PERIOD,
2523 emails_composed_in_period,
2524 emails_rerouted_in_period,
2525 leads_created_in_period,
2526 request_id,
2527 program_application_id,
2528 program_id,
2529 program_update_date )
2530 (SELECT
2531 rlp.agent_id agent_id,
2532 rlp.email_account_id email_account_id,
2533 rlp.email_classification_id email_classification_id,
2534 rlp.party_id party_id,
2535 rlp.time_id time_id,
2536 rlp.period_type_id period_type_id,
2537 rlp.period_start_date period_start_date,
2538 rlp.period_start_time period_start_time,
2539 rlp.outcome_id outcome_id,
2540 rlp.result_id result_id,
2541 rlp.reason_id reason_id,
2542 g_user_id created_by,
2543 g_sysdate creation_date,
2544 g_user_id last_updated_by,
2545 g_sysdate last_update_date,
2546 decode(sum(rlp.emails_offered_in_period), 0, to_number(null), sum(rlp.emails_offered_in_period))
2547 emails_offered_in_period,
2548 decode(sum(rlp.emails_fetched_in_period), 0, to_number(null), sum(rlp.emails_fetched_in_period))
2549 emails_fetched_in_period,
2550 decode(sum(rlp.emails_replied_in_period), 0, to_number(null), sum(rlp.emails_replied_in_period))
2551 emails_replied_in_period,
2552 decode(sum(rlp.emails_rpld_by_goal_in_period), 0, to_number(null), sum(rlp.emails_rpld_by_goal_in_period))
2553 emails_rpld_by_goal_in_period,
2554 decode(sum(rlp.AGENT_EMAILS_RPLD_BY_GOAL), 0, to_number(null), sum(rlp.AGENT_EMAILS_RPLD_BY_GOAL))
2555 AGENT_EMAILS_RPLD_BY_GOAL,
2556 decode(sum(rlp.emails_deleted_in_period), 0, to_number(null), sum(rlp.emails_deleted_in_period))
2557 emails_deleted_in_period,
2558 decode(sum(rlp.emails_trnsfrd_out_in_period), 0, to_number(null), sum(rlp.emails_trnsfrd_out_in_period))
2559 emails_trnsfrd_out_in_period,
2560 decode(sum(rlp.emails_trnsfrd_in_in_period), 0, to_number(null), sum(rlp.emails_trnsfrd_in_in_period))
2561 emails_trnsfrd_in_in_period,
2562 decode(sum(rlp.emails_rsl_and_trfd_in_period), 0, to_number(null), sum(rlp.emails_rsl_and_trfd_in_period))
2563 emails_rsl_and_trfd_in_period,
2564 decode(sum(rlp.emails_assigned_in_period), 0, to_number(null), sum(rlp.emails_assigned_in_period))
2565 emails_assigned_in_period,
2566 decode(sum(rlp.emails_auto_routed_in_period), 0, to_number(null), sum(rlp.emails_auto_routed_in_period))
2567 emails_auto_routed_in_period,
2568 decode(sum(rlp.emails_auto_uptd_sr_in_period), 0, to_number(null), sum(rlp.emails_auto_uptd_sr_in_period))
2569 emails_auto_uptd_sr_in_period,
2570 decode(sum(rlp.sr_created_in_period), 0, to_number(null), sum(rlp.sr_created_in_period))
2571 sr_created_in_period,
2572 min(rlp.oldest_email_open_date) oldest_email_open_date,
2573 min(rlp.oldest_email_queue_date) oldest_email_queue_date,
2574 decode(sum(rlp.email_resp_time_in_period), 0, to_number(null), sum(rlp.email_resp_time_in_period))
2575 email_resp_time_in_period,
2576 decode(sum(rlp.agent_resp_time_in_period), 0, to_number(null), sum(rlp.agent_resp_time_in_period))
2577 agent_resp_time_in_period,
2578 min(rlp.acc_open_emails) acc_open_emails,
2579 min(rlp.acc_open_age) acc_open_age,
2580 min(rlp.acc_emails_in_queue) acc_emails_in_queue,
2581 min(rlp.acc_queue_time) acc_queue_time,
2582 min(rlp.acc_emails_one_day) acc_emails_one_day,
2583 min(rlp.acc_emails_three_days) acc_emails_three_days,
2584 min(rlp.acc_emails_week) acc_emails_week,
2585 min(rlp.acc_emails_week_plus) acc_emails_week_plus,
2586 decode(sum(rlp.one_rsln_in_period), 0, to_number(null), sum(rlp.one_rsln_in_period))
2587 one_rsln_in_period,
2588 decode(sum(rlp.two_rsln_in_period), 0, to_number(null), sum(rlp.two_rsln_in_period))
2589 two_rsln_in_period,
2590 decode(sum(rlp.three_rsln_in_period), 0, to_number(null), sum(rlp.three_rsln_in_period))
2591 three_rsln_in_period,
2592 decode(sum(rlp.four_rsln_in_period), 0, to_number(null), sum(rlp.four_rsln_in_period))
2593 four_rsln_in_period,
2594 decode(sum(rlp.interaction_threads_in_period), 0, to_number(null), sum(rlp.interaction_threads_in_period))
2595 interaction_threads_in_period,
2596 decode(sum(rlp.emails_orr_count_in_period),0,to_number(null),sum(emails_orr_count_in_period)) emails_orr_count_in_period,
2597 decode(sum(rlp.EMAILS_AUTO_REPLIED_IN_PERIOD),0,to_number(null),sum(EMAILS_AUTO_REPLIED_IN_PERIOD)) EMAILS_AUTO_REPLIED_IN_PERIOD,
2598 decode(sum(rlp.EMAILS_AUTO_DELETED_IN_PERIOD),0,to_number(null),sum(EMAILS_AUTO_DELETED_IN_PERIOD)) EMAILS_AUTO_DELETED_IN_PERIOD,
2599 decode(sum(rlp.EMAILS_AUTO_RESOLVED_IN_PERIOD),0,to_number(null),sum(EMAILS_AUTO_RESOLVED_IN_PERIOD)) EMAILS_AUTO_RESOLVED_IN_PERIOD,
2600 decode(sum(rlp.emails_composed_in_period),0,to_number(null),sum(emails_composed_in_period)) emails_composed_in_period,
2601 decode(sum(rlp.EMAILS_REROUTED_IN_PERIOD),0,to_number(null),sum(EMAILS_REROUTED_IN_PERIOD)) EMAILS_REROUTED_IN_PERIOD,
2602 decode(sum(rlp.leads_created_in_period),0,to_number(null),sum(leads_created_in_period)) leads_created_in_period,
2603 g_request_id request_id,
2604 g_program_appl_id program_application_id,
2605 g_program_id program_id,
2606 g_sysdate program_update_date
2607 FROM (
2608 SELECT
2609 inv2.agent_id agent_id,
2610 inv2.email_account_id email_account_id,
2611 inv2.email_classification_id email_classification_id,
2612 inv2.party_id party_id,
2613 decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
2614 decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), inv2.ent_year_id),
2615 inv2.ent_qtr_id), inv2.ent_period_id), inv2.week_id) time_id,
2616 decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
2617 decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null),
2618 128), 64), 32), 16) period_type_id,
2619 decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
2620 decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_date(null), min(inv2.ent_year_start_date)),
2621 min(inv2.ent_qtr_start_date)), min(inv2.ent_period_start_date)), min(inv2.week_start_date))
2622 period_start_date,
2623 '00:00' period_start_time,
2624 inv2.outcome_id,
2625 inv2.result_id,
2626 inv2.reason_id,
2627 sum(inv2.emails_offered_in_period) emails_offered_in_period,
2628 sum(inv2.emails_fetched_in_period) emails_fetched_in_period,
2629 sum(inv2.emails_replied_in_period) emails_replied_in_period,
2630 sum(inv2.emails_rpld_by_goal_in_period) emails_rpld_by_goal_in_period,
2631 sum(inv2.AGENT_EMAILS_RPLD_BY_GOAL) AGENT_EMAILS_RPLD_BY_GOAL,
2632 sum(inv2.emails_deleted_in_period) emails_deleted_in_period,
2633 sum(inv2.emails_trnsfrd_out_in_period) emails_trnsfrd_out_in_period,
2634 sum(inv2.emails_trnsfrd_in_in_period) emails_trnsfrd_in_in_period,
2635 sum(inv2.emails_rsl_and_trfd_in_period) emails_rsl_and_trfd_in_period,
2636 sum(inv2.emails_assigned_in_period) emails_assigned_in_period,
2637 sum(inv2.emails_auto_routed_in_period) emails_auto_routed_in_period,
2638 sum(inv2.emails_auto_uptd_sr_in_period) emails_auto_uptd_sr_in_period,
2639 sum(inv2.sr_created_in_period) sr_created_in_period,
2640 to_date(null) oldest_email_open_date,
2641 to_date(null) oldest_email_queue_date,
2642 sum(inv2.email_resp_time_in_period) email_resp_time_in_period,
2643 sum(inv2.agent_resp_time_in_period) agent_resp_time_in_period,
2644 sum(inv2.one_rsln_in_period) one_rsln_in_period,
2645 sum(inv2.two_rsln_in_period) two_rsln_in_period,
2646 sum(inv2.three_rsln_in_period) three_rsln_in_period,
2647 sum(inv2.four_rsln_in_period) four_rsln_in_period,
2648 sum(inv2.interaction_threads_in_period) interaction_threads_in_period,
2649 sum(inv2.emails_orr_count_in_period) emails_orr_count_in_period,
2650 sum(inv2.EMAILS_AUTO_REPLIED_IN_PERIOD) EMAILS_AUTO_REPLIED_IN_PERIOD,
2651 sum(inv2.EMAILS_AUTO_DELETED_IN_PERIOD) EMAILS_AUTO_DELETED_IN_PERIOD,
2652 sum(inv2.EMAILS_AUTO_RESOLVED_IN_PERIOD) EMAILS_AUTO_RESOLVED_IN_PERIOD,
2653 sum(inv2.emails_composed_in_period) emails_composed_in_period,
2654 sum(inv2.emails_rerouted_in_period) emails_rerouted_in_period,
2655 sum(inv2.leads_created_in_period) leads_created_in_period,
2656 decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
2657 decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_open_emails)),
2658 min(inv2.qtr_acc_open_emails)), min(inv2.period_acc_open_emails)), min(inv2.week_acc_open_emails))
2659 acc_open_emails,
2660 to_number(null) acc_open_age,
2661 decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
2662 decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_emails_in_queue)),
2663 min(inv2.qtr_acc_emails_in_queue)), min(inv2.period_acc_emails_in_queue)), min(inv2.week_acc_emails_in_queue))
2664 acc_emails_in_queue,
2665 to_number(null) acc_queue_time,
2666 decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
2667 decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_emails_one_day)),
2668 min(inv2.qtr_acc_emails_one_day)), min(inv2.period_acc_emails_one_day)), min(inv2.week_acc_emails_one_day))
2669 acc_emails_one_day,
2670 decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
2671 decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_emails_three_days)),
2672 min(inv2.qtr_acc_emails_three_days)), min(inv2.period_acc_emails_three_days)),
2673 min(inv2.week_acc_emails_three_days)) acc_emails_three_days,
2674 decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
2675 decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_emails_week)),
2676 min(inv2.qtr_acc_emails_week)), min(inv2.period_acc_emails_week)), min(inv2.week_acc_emails_week))
2677 acc_emails_week,
2678 decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
2679 decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null), min(inv2.year_acc_emails_week_plus)),
2680 min(inv2.qtr_acc_emails_week_plus)), min(inv2.period_acc_emails_week_plus)),
2681 min(inv2.week_acc_emails_week_plus)) acc_emails_week_plus
2682 FROM
2683 (SELECT /*+ use_hash(bed) use_hash(ftd) */
2684 bed.agent_id agent_id,
2685 bed.email_account_id email_account_id,
2686 bed.email_classification_id email_classification_id,
2687 bed.party_id party_id,
2688 bed.outcome_id,
2689 bed.result_id,
2690 bed.reason_id,
2691 ftd.ent_year_id ent_year_id,
2692 ftd.ent_year_start_date ent_year_start_date,
2693 ftd.ent_qtr_id ent_qtr_id,
2694 ftd.ent_qtr_start_date ent_qtr_start_date,
2695 ftd.ent_period_id ent_period_id,
2696 ftd.ent_period_start_date ent_period_start_date,
2697 ftd.week_id week_id,
2698 ftd.week_start_date week_start_date,
2699 ftd.report_date_julian report_date_julian,
2700 bed.period_start_date period_start_date,
2701 bed.emails_offered_in_period emails_offered_in_period,
2702 bed.emails_fetched_in_period emails_fetched_in_period,
2703 bed.emails_replied_in_period emails_replied_in_period,
2704 bed.emails_rpld_by_goal_in_period emails_rpld_by_goal_in_period,
2705 bed.AGENT_EMAILS_RPLD_BY_GOAL AGENT_EMAILS_RPLD_BY_GOAL,
2706 bed.emails_deleted_in_period emails_deleted_in_period,
2707 bed.emails_trnsfrd_out_in_period emails_trnsfrd_out_in_period,
2708 bed.emails_trnsfrd_in_in_period emails_trnsfrd_in_in_period,
2709 bed.emails_rsl_and_trfd_in_period emails_rsl_and_trfd_in_period,
2710 bed.emails_assigned_in_period emails_assigned_in_period,
2711 bed.emails_auto_routed_in_period emails_auto_routed_in_period,
2712 bed.emails_auto_uptd_sr_in_period emails_auto_uptd_sr_in_period,
2713 bed.sr_created_in_period sr_created_in_period,
2714 bed.email_resp_time_in_period email_resp_time_in_period,
2715 bed.agent_resp_time_in_period agent_resp_time_in_period,
2716 bed.one_rsln_in_period one_rsln_in_period,
2717 bed.two_rsln_in_period two_rsln_in_period,
2718 bed.three_rsln_in_period three_rsln_in_period,
2719 bed.four_rsln_in_period four_rsln_in_period,
2720 bed.interaction_threads_in_period interaction_threads_in_period,
2721 bed.emails_orr_count_in_period emails_orr_count_in_period,
2722 bed.EMAILS_AUTO_REPLIED_IN_PERIOD EMAILS_AUTO_REPLIED_IN_PERIOD,
2723 bed.EMAILS_AUTO_DELETED_IN_PERIOD EMAILS_AUTO_DELETED_IN_PERIOD,
2724 bed.EMAILS_AUTO_RESOLVED_IN_PERIOD EMAILS_AUTO_RESOLVED_IN_PERIOD,
2725 bed.emails_composed_in_period emails_composed_in_period,
2726 bed.emails_rerouted_in_period emails_rerouted_in_period,
2727 bed.leads_created_in_period leads_created_in_period,
2728 --1/2hr removing day measures since they have already been done
2729 -- first_value(bed.accumulated_open_emails)
2730 -- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2731 -- ftd.report_date_julian
2732 -- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2733 -- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_open_emails,
2734 first_value(bed.accumulated_open_emails)
2735 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2736 outcome_id, result_id, reason_id,
2737 ftd.week_id
2738 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2739 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_open_emails,
2740 first_value(bed.accumulated_open_emails)
2741 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2742 outcome_id, result_id, reason_id,
2743 ftd.ent_period_id
2744 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2745 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_open_emails,
2746 first_value(bed.accumulated_open_emails)
2747 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2748 outcome_id, result_id, reason_id,
2749 ftd.ent_qtr_id
2750 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2751 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_open_emails,
2752 first_value(bed.accumulated_open_emails)
2753 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2754 outcome_id, result_id, reason_id,
2755 ftd.ent_year_id
2756 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2757 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_open_emails,
2758 --1/2hr removing day measures since they have already been done
2759 --first_value(bed.accumulated_emails_in_queue)
2760 -- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2761 -- ftd.report_date_julian
2762 -- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2763 -- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_emails_in_queue,
2764 first_value(bed.accumulated_emails_in_queue)
2765 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2766 outcome_id, result_id, reason_id,
2767 ftd.week_id
2768 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2769 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_emails_in_queue,
2770 first_value(bed.accumulated_emails_in_queue)
2771 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2772 outcome_id, result_id, reason_id,
2773 ftd.ent_period_id
2774 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2775 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_emails_in_queue,
2776 first_value(bed.accumulated_emails_in_queue)
2777 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2778 outcome_id, result_id, reason_id,
2779 ftd.ent_qtr_id
2780 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2781 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_emails_in_queue,
2782 first_value(bed.accumulated_emails_in_queue)
2783 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2784 outcome_id, result_id, reason_id,
2785 ftd.ent_year_id
2786 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2787 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_in_queue,
2788 --1/2hr removing day measures since they have already been done
2789 -- first_value(bed.accumulated_emails_one_day)
2790 -- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2791 -- ftd.report_date_julian
2792 -- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2793 -- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_emails_one_day,
2794 first_value(bed.accumulated_emails_one_day)
2795 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2796 outcome_id, result_id, reason_id,
2797 ftd.week_id
2798 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2799 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_emails_one_day,
2800 first_value(bed.accumulated_emails_one_day)
2801 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2802 outcome_id, result_id, reason_id,
2803 ftd.ent_period_id
2804 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2805 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_emails_one_day,
2806 first_value(bed.accumulated_emails_one_day)
2807 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2808 outcome_id, result_id, reason_id,
2809 ftd.ent_qtr_id
2810 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2811 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_emails_one_day,
2812 first_value(bed.accumulated_emails_one_day)
2813 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2814 outcome_id, result_id, reason_id,
2815 ftd.ent_year_id
2816 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2817 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_one_day,
2818 --1/2hr removing day measures since they have already been done
2819 --first_value(bed.accumulated_emails_three_days)
2820 -- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2821 -- ftd.report_date_julian
2822 -- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2823 -- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_emails_three_days,
2824 first_value(bed.accumulated_emails_three_days)
2825 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2826 outcome_id, result_id, reason_id,
2827 ftd.week_id
2828 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2829 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_emails_three_days,
2830 first_value(bed.accumulated_emails_three_days)
2831 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2832 outcome_id, result_id, reason_id,
2833 ftd.ent_period_id
2834 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2835 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_emails_three_days,
2836 first_value(bed.accumulated_emails_three_days)
2837 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2838 outcome_id, result_id, reason_id,
2839 ftd.ent_qtr_id
2840 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2841 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_emails_three_days,
2842 first_value(bed.accumulated_emails_three_days)
2843 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2844 outcome_id, result_id, reason_id,
2845 ftd.ent_year_id
2846 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2847 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_three_days,
2848 --1/2hr removing day measures since they have already been done
2849 --first_value(bed.accumulated_emails_week)
2850 -- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2851 -- ftd.report_date_julian
2852 -- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2853 -- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_emails_week,
2854 first_value(bed.accumulated_emails_week)
2855 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2856 outcome_id, result_id, reason_id,
2857 ftd.week_id
2858 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2859 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_emails_week,
2860 first_value(bed.accumulated_emails_week)
2861 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2862 outcome_id, result_id, reason_id,
2863 ftd.ent_period_id
2864 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2865 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_emails_week,
2866 first_value(bed.accumulated_emails_week)
2867 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2868 outcome_id, result_id, reason_id,
2869 ftd.ent_qtr_id
2870 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2871 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_emails_week,
2872 first_value(bed.accumulated_emails_week)
2873 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2874 outcome_id, result_id, reason_id,
2875 ftd.ent_year_id
2876 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2877 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_week,
2878 --1/2hr removing day measures since they have already been done
2879 --first_value(bed.accumulated_emails_week_plus)
2880 -- over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2881 -- ftd.report_date_julian
2882 -- order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2883 -- lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) day_acc_emails_week_plus,
2884 first_value(bed.accumulated_emails_week_plus)
2885 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2886 outcome_id, result_id, reason_id,
2887 ftd.week_id
2888 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2889 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) week_acc_emails_week_plus,
2890 first_value(bed.accumulated_emails_week_plus)
2891 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2892 outcome_id, result_id, reason_id,
2893 ftd.ent_period_id
2894 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2895 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) period_acc_emails_week_plus,
2896 first_value(bed.accumulated_emails_week_plus)
2897 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2898 outcome_id, result_id, reason_id,
2899 ftd.ent_qtr_id
2900 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2901 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) qtr_acc_emails_week_plus,
2902 first_value(bed.accumulated_emails_week_plus)
2903 over (partition by bed.agent_id, bed.email_account_id, bed.email_classification_id, bed.party_id,
2904 outcome_id, result_id, reason_id,
2905 ftd.ent_year_id
2906 order by to_date(to_char(bed.period_start_date, 'dd/mm/yyyy ') ||
2907 lpad(bed.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_week_plus
2908 FROM bix_email_details_f bed,
2909 fii_time_day ftd
2910 WHERE bed.time_id = ftd.report_date_julian
2911 --1/2hr changed period_Type_id to 1 from -1
2912 --AND bed.period_type_id = -1) inv2
2913 AND bed.period_type_id = 1) inv2
2914 GROUP BY
2915 inv2.agent_id,
2916 inv2.email_account_id,
2917 inv2.email_classification_id,
2918 inv2.party_id,
2919 inv2.outcome_id,
2920 inv2.result_id,
2921 inv2.reason_id,
2922 ROLLUP(
2923 inv2.ent_year_id,
2924 inv2.ent_qtr_id,
2925 inv2.ent_period_id,
2926 inv2.week_id)
2927 HAVING
2928 decode(inv2.week_id, null, decode(inv2.ent_period_id, null,
2929 decode(inv2.ent_qtr_id, null, decode(inv2.ent_year_id, null, to_number(null),
2930 128), 64), 32), 16) IS NOT NULL) rlp
2931 GROUP BY
2932 rlp.agent_id,
2933 rlp.email_account_id,
2934 rlp.email_classification_id,
2935 rlp.party_id,
2936 rlp.time_id,
2937 rlp.period_type_id,
2938 rlp.period_start_date,
2939 rlp.period_start_time,
2940 rlp.outcome_id,
2941 rlp.result_id,
2942 rlp.reason_id);
2943
2944 g_rows_ins_upd := g_rows_ins_upd + SQL%ROWCOUNT;
2945
2946 COMMIT;
2947
2948 write_log('Finished procedure summarize_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
2949
2950 EXCEPTION
2951 WHEN OTHERS THEN
2952 write_log('Error in procedure summarize_data : Error : ' || sqlerrm);
2953 RAISE;
2954 END summarize_data;
2955
2956 PROCEDURE check_missing_date(p_start_date IN DATE,
2957 p_end_date IN DATE,
2958 p_has_missing_date OUT NOCOPY BOOLEAN) IS
2959 l_count1 NUMBER;
2960 l_count2 NUMBER;
2961 BEGIN
2962
2963 p_has_missing_date := FALSE;
2964
2965 SELECT count(*)
2966 INTO l_count1
2967 FROM fii_time_day
2968 WHERE report_date between trunc(p_start_date) and trunc(p_end_date);
2969
2970 SELECT (trunc(p_end_date) - trunc(p_start_date)) + 1
2971 INTO l_count2
2972 FROM dual;
2973
2974 IF (l_count1 < l_count2) THEN
2975 p_has_missing_date := TRUE;
2976 END IF;
2977
2978 write_log('Finished procedure check_missing_date at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
2979
2980 EXCEPTION
2981 WHEN OTHERS THEN
2982 write_log('Error in procedure check_missing_date : Error : ' || sqlerrm);
2983 RAISE;
2984 END check_missing_date;
2985
2986 /*
2987 This procedure Calls the following procedures
2988 1. Collect_day : which collects all the email center measures by day
2989 2. summarise_data : This procedure summarizes the data to Week, Month , Quarter and Year from Day rows collected in
2990 collect_day procedure.
2991 */
2992
2993
2994
2995 PROCEDURE MAIN ( errbuf OUT NOCOPY VARCHAR2,
2996 retcode OUT NOCOPY VARCHAR2,
2997 p_start_date IN VARCHAR2,
2998 p_end_date IN VARCHAR2) IS
2999
3000 l_has_missing_date BOOLEAN := FALSE;
3001 l_no_of_workers NUMBER;
3002
3003 BEGIN
3004 errbuf := null;
3005 retcode := 0;
3006
3007 write_log('Collection start date as specified by the user : ' || p_start_date);
3008 write_log('Collection end date as specified by the user : ' || p_end_date);
3009
3010 /* get the collection date range */
3011 g_collect_start_date := TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
3012 g_collect_end_date := TO_DATE(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
3013
3014 /* Check if the time dimension is populated for the collection date range ; if not exit */
3015 check_missing_date(g_collect_start_date, g_collect_end_date, l_has_missing_date);
3016 IF (l_has_missing_date) THEN
3017 write_log('Time dimension is not populated for the entire collection date range');
3018 RAISE G_TIME_DIM_MISSING;
3019 END IF;
3020
3021 write_log('Force Enabling parallel query and parallel dml');
3022 EXECUTE IMMEDIATE ' Alter Session force parallel query';
3023 EXECUTE IMMEDIATE ' Alter Session enable parallel DML';
3024
3025
3026 write_log('Calling procedure collect_day');
3027 collect_day;
3028 write_log('End procedure collect_day');
3029
3030 /* Summarize data to day, week, month, quater and year time buckets */
3031 write_log('Calling procedure summarize_data');
3032 summarize_data;
3033 write_log('End procedure summarize_data');
3034
3035 write_log('Total Rows Inserted/Updated : ' || to_char(g_rows_ins_upd));
3036
3037 DBMS_STATS.gather_table_stats(ownname => g_bix_schema,
3038 tabName => 'BIX_EMAIL_DETAILS_F',
3039 cascade => TRUE,
3040 degree => bis_common_parameters.get_degree_of_parallelism,
3041 estimate_percent => 10,
3042 granularity => 'GLOBAL');
3043
3044 /* Insert the status into collect log table */
3045 write_log('Calling procedure WRAPUP');
3046 bis_collection_utilities.wrapup(
3047 p_status => TRUE,
3048 p_count => g_rows_ins_upd,
3049 p_message => NULL,
3050 p_period_from => g_collect_start_date,
3051 p_period_to => g_collect_end_date);
3052 write_log('End Proceddure WRAPUP');
3053
3054 write_log('Finished Procedure BIX_EMALS_SUMMARY_PKG with success at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
3055
3056 EXCEPTION
3057 WHEN G_TIME_DIM_MISSING THEN
3058 retcode := -1;
3059 errbuf := 'Time Dimension is not populated for the entire collection range';
3060 bis_collection_utilities.wrapup(
3061 p_status => FALSE,
3062 p_count => 0,
3063 p_message => 'eMail summary package failed : Error : Time dimension is not populated',
3064 p_period_from => g_collect_start_date,
3065 p_period_to => g_collect_end_date);
3066 WHEN OTHERS THEN
3067 clean_up;
3068 retcode := SQLCODE;
3069 errbuf := SQLERRM;
3070 bis_collection_utilities.wrapup(
3071 p_status => FALSE,
3072 p_count => 0,
3073 p_message => 'eMail summary package failed : error : ' || sqlerrm,
3074 p_period_from => g_collect_start_date,
3075 p_period_to => g_collect_end_date);
3076 END MAIN;
3077
3078 /* This is the procedure called from concurrent program. The data is collected from BIS Global start Date to current date. This procedure calls MAIN with start date and End date.
3079 */
3080
3081
3082 PROCEDURE load (errbuf OUT NOCOPY VARCHAR2,
3083 retcode OUT NOCOPY VARCHAR2)
3084 IS
3085 l_start_date DATE;
3086 l_end_date DATE;
3087
3088 BEGIN
3089
3090 init;
3091 write_log('End procedure init');
3092
3093 /* Get the global start date */
3094 l_start_date := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
3095
3096
3097 /* Round the start date to the nearest half hour , less than start date */
3098 SELECT
3099 TO_DATE(TO_CHAR(l_start_date,'YYYY/MM/DD') || ' ' ||
3100 LPAD(TO_CHAR(l_start_date,'HH24:'),3,'0') ||
3101 DECODE(SIGN(TO_NUMBER(TO_CHAR(l_start_date,'MI'))-29),0,'00:00',1,'30:00',-1,'00:00'),
3102 'YYYY/MM/DD HH24:MI:SS')
3103 INTO
3104 l_start_date
3105 FROM DUAL;
3106
3107 l_end_date := sysdate;
3108
3109
3110
3111 /* Round the end date to the nearest half hour , less than end date */
3112 SELECT
3113 TO_DATE(TO_CHAR(l_end_date,'YYYY/MM/DD') || ' ' ||
3114 LPAD(TO_CHAR(l_end_date,'HH24:'),3,'0') ||
3115 DECODE(SIGN(TO_NUMBER(TO_CHAR(l_end_date,'MI'))-29),0,'00:00',1,'30:00',-1,'00:00'),
3116 'YYYY/MM/DD HH24:MI:SS') - 1/86400
3117 INTO
3118 l_end_date
3119 FROM DUAL;
3120
3121 --???? For Debugging Purposes
3122 --l_end_date := sysdate;
3123
3124 Main(errbuf,
3125 retcode,
3126 TO_CHAR(l_start_date, 'YYYY/MM/DD HH24:MI:SS'),
3127 TO_CHAR(l_end_date, 'YYYY/MM/DD HH24:MI:SS'));
3128 EXCEPTION
3129 WHEN OTHERS THEN
3130 RAISE;
3131 END load;
3132
3133 END BIX_EMAILS_LOAD_PKG;