eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BIX_EMAILS_LOAD_PKG dependencies on BIX_EMAIL_DETAILS_F

Line 21: TYPE g_resource_id_tab IS TABLE OF bix_email_details_f.agent_id%TYPE;

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;

Line 22: TYPE g_party_id_tab IS TABLE OF bix_email_details_f.party_id%TYPE;

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;

Line 26: TYPE g_period_start_date_tab IS TABLE OF bix_email_details_f.period_start_date%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;

Line 28: TYPE g_emails_open_tab IS TABLE OF bix_email_details_f.accumulated_open_emails%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;

Line 29: TYPE g_total_open_age_tab IS TABLE OF bix_email_details_f.accumulated_open_age%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;

Line 30: TYPE g_oldest_open_message_tab IS TABLE OF bix_email_details_f.oldest_email_open_date%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;

Line 31: TYPE g_emails_in_queue_tab IS TABLE OF bix_email_details_f.accumulated_emails_in_queue%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;

Line 32: TYPE g_total_queue_time_tab IS TABLE OF bix_email_details_f.accumulated_queue_time%TYPE;

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;

Line 33: TYPE g_oldest_message_in_queue_tab IS TABLE OF bix_email_details_f.oldest_email_queue_date%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;

Line 34: TYPE g_acc_emails_one_day_tab IS TABLE OF bix_email_details_f.accumulated_emails_one_day%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:

Line 35: TYPE g_acc_emails_three_days_tab IS TABLE OF bix_email_details_f.accumulated_emails_three_days%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

Line 36: TYPE g_acc_emails_week_tab IS TABLE OF bix_email_details_f.accumulated_emails_week%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

Line 37: TYPE g_acc_emails_week_plus_tab IS TABLE OF bix_email_details_f.accumulated_emails_week_plus%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:

Line 77: IF (BIS_COLLECTION_UTILITIES.SETUP('BIX_EMAIL_DETAILS_F') = FALSE) THEN

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'));

Line 117: write_log('Truncating the table bix_email_details_f and bix_interactions_temp');

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');

Line 118: BIS_COLLECTION_UTILITIES.deleteLogForObject('BIX_EMAIL_DETAILS_F');

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:

Line 119: Truncate_Table('BIX_EMAIL_DETAILS_F');

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');

Line 121: write_log('Done truncating the table bix_email_details_f and bix_interactions_temp');

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';

Line 284: TYPE one_done_rsln_tab IS TABLE OF bix_email_details_f.one_rsln_in_period%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;

Line 285: TYPE two_done_rsln_tab IS TABLE OF bix_email_details_f.two_rsln_in_period%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;

Line 286: TYPE three_done_rsln_tab IS TABLE OF bix_email_details_f.three_rsln_in_period%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;

Line 287: TYPE four_done_rsln_tab IS TABLE OF bix_email_details_f.four_rsln_in_period%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;

Line 288: TYPE intr_thread_tab IS TABLE OF bix_email_details_f.interaction_threads_in_period%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;

Line 293: TYPE curr_depth_tab IS TABLE OF bix_email_details_f.one_rsln_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;

Line 368: MERGE INTO bix_email_details_f bed

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

Line 461: write_log('Total rows inserted/updated in bix_email_details_f for resolution : ' ||

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 */

Line 529: write_log('Truncating the table bix_email_details_f and bix_interactions_temp');

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');

Line 530: BIS_COLLECTION_UTILITIES.deleteLogForObject('BIX_EMAIL_DETAILS_F');

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:

Line 531: Truncate_Table('BIX_EMAIL_DETAILS_F');

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'));

Line 533: write_log('Done truncating the table bix_email_details_f and bix_interactions_temp');

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

Line 567: write_log('Merging additive measures into table bix_email_details_f');

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,

Line 569: /* Insert / Update additive measures to summary table bix_email_details_f */

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,

Line 570: INSERT /+ APPEND PARALLEL(bed) / INTO BIX_EMAIL_DETAILS_F bed

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,

Line 1175: write_log('Number of rows inserted in table bix_email_details_f : ' || to_char(SQL%ROWCOUNT));

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 */

Line 1181: tabName => 'BIX_EMAIL_DETAILS_F',

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');

Line 1212: MERGE INTO bix_email_details_f bed

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,

Line 1783: tabName => 'BIX_EMAIL_DETAILS_F',

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');

Line 1818: MERGE INTO bix_email_details_f bed

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,

Line 2389: tabName => 'BIX_EMAIL_DETAILS_F',

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');

Line 2466: write_log('Merging half hour rows to day, week, month, quarter, year bucket in table bix_email_details_f');

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 */

Line 2468: /* Rollup half hour informations to day, week, month, quarter, year time bucket for table bix_email_details_f */

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:

Line 2473: INSERT /+ append / INTO 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,

Line 2908: FROM bix_email_details_f bed,

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

Line 3038: tabName => 'BIX_EMAIL_DETAILS_F',

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');