[Home] [Help]
25:
26: TYPE g_media_id_tab IS TABLE OF jtf_ih_media_items.media_id%TYPE;
27: TYPE g_email_account_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
28: TYPE g_email_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
29: TYPE g_resource_id_tab IS TABLE OF bix_email_details_f.agent_id%TYPE;
30: TYPE g_start_date_time_tab IS TABLE OF jtf_ih_media_item_lc_segs.start_date_time%TYPE;
31: TYPE g_end_date_time_tab IS TABLE OF jtf_ih_media_item_lc_segs.end_date_time%TYPE;
32: TYPE g_media_start_date_time_tab IS TABLE OF jtf_ih_media_items.start_date_time%TYPE;
33:
91: l_status VARCHAR2(30);
92: l_industry VARCHAR2(30);
93: BEGIN
94:
95: IF (BIS_COLLECTION_UTILITIES.SETUP('BIX_EMAIL_DETAILS_F') = FALSE) THEN
96: RAISE_APPLICATION_ERROR(-20000, 'BIS_COLLECTION_UTILITIES.setup has failed');
97: END IF;
98:
99: write_log('Start of the procedure init at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
166: g_no_of_jobs := l_count;
167:
168: write_log('Number of workers that need to be instantiated : ' || to_char(l_count));
169:
170: Delete BIX_WORKER_JOBS WHERE OBJECT_NAME = 'BIX_EMAIL_DETAILS_F';
171:
172: IF (l_count > 0) THEN
173: l_start_date_range := g_collect_start_date;
174:
191: , END_DATE_RANGE
192: , WORKER_NUMBER
193: , STATUS)
194: VALUES (
195: 'BIX_EMAIL_DETAILS_F'
196: , l_start_date_range
197: , l_end_date_range
198: , l_count
199: , 'UNASSIGNED');
513: TYPE party_id_tab IS TABLE OF jtf_ih_interactions.party_id%TYPE;
514: TYPE start_date_time_tab IS TABLE OF jtf_ih_interactions.start_date_time%TYPE;
515: TYPE source_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
516: TYPE route_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
517: TYPE one_done_rsln_tab IS TABLE OF bix_email_details_f.one_rsln_in_period%TYPE;
518: TYPE two_done_rsln_tab IS TABLE OF bix_email_details_f.two_rsln_in_period%TYPE;
519: TYPE three_done_rsln_tab IS TABLE OF bix_email_details_f.three_rsln_in_period%TYPE;
520: TYPE four_done_rsln_tab IS TABLE OF bix_email_details_f.four_rsln_in_period%TYPE;
521: TYPE intr_thread_tab IS TABLE OF bix_email_details_f.interaction_threads_in_period%TYPE;
514: TYPE start_date_time_tab IS TABLE OF jtf_ih_interactions.start_date_time%TYPE;
515: TYPE source_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
516: TYPE route_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
517: TYPE one_done_rsln_tab IS TABLE OF bix_email_details_f.one_rsln_in_period%TYPE;
518: TYPE two_done_rsln_tab IS TABLE OF bix_email_details_f.two_rsln_in_period%TYPE;
519: TYPE three_done_rsln_tab IS TABLE OF bix_email_details_f.three_rsln_in_period%TYPE;
520: TYPE four_done_rsln_tab IS TABLE OF bix_email_details_f.four_rsln_in_period%TYPE;
521: TYPE intr_thread_tab IS TABLE OF bix_email_details_f.interaction_threads_in_period%TYPE;
522: TYPE week_id_tab IS TABLE OF fii_time_day.week_id%TYPE;
515: TYPE source_id_tab IS TABLE OF jtf_ih_media_items.source_id%TYPE;
516: TYPE route_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
517: TYPE one_done_rsln_tab IS TABLE OF bix_email_details_f.one_rsln_in_period%TYPE;
518: TYPE two_done_rsln_tab IS TABLE OF bix_email_details_f.two_rsln_in_period%TYPE;
519: TYPE three_done_rsln_tab IS TABLE OF bix_email_details_f.three_rsln_in_period%TYPE;
520: TYPE four_done_rsln_tab IS TABLE OF bix_email_details_f.four_rsln_in_period%TYPE;
521: TYPE intr_thread_tab IS TABLE OF bix_email_details_f.interaction_threads_in_period%TYPE;
522: TYPE week_id_tab IS TABLE OF fii_time_day.week_id%TYPE;
523: TYPE ent_period_id_tab IS TABLE OF fii_time_day.ent_period_id%TYPE;
516: TYPE route_classification_id_tab IS TABLE OF iem_route_classifications.route_classification_id%TYPE;
517: TYPE one_done_rsln_tab IS TABLE OF bix_email_details_f.one_rsln_in_period%TYPE;
518: TYPE two_done_rsln_tab IS TABLE OF bix_email_details_f.two_rsln_in_period%TYPE;
519: TYPE three_done_rsln_tab IS TABLE OF bix_email_details_f.three_rsln_in_period%TYPE;
520: TYPE four_done_rsln_tab IS TABLE OF bix_email_details_f.four_rsln_in_period%TYPE;
521: TYPE intr_thread_tab IS TABLE OF bix_email_details_f.interaction_threads_in_period%TYPE;
522: TYPE week_id_tab IS TABLE OF fii_time_day.week_id%TYPE;
523: TYPE ent_period_id_tab IS TABLE OF fii_time_day.ent_period_id%TYPE;
524: TYPE ent_qtr_id_tab IS TABLE OF fii_time_day.ent_qtr_id%TYPE;
517: TYPE one_done_rsln_tab IS TABLE OF bix_email_details_f.one_rsln_in_period%TYPE;
518: TYPE two_done_rsln_tab IS TABLE OF bix_email_details_f.two_rsln_in_period%TYPE;
519: TYPE three_done_rsln_tab IS TABLE OF bix_email_details_f.three_rsln_in_period%TYPE;
520: TYPE four_done_rsln_tab IS TABLE OF bix_email_details_f.four_rsln_in_period%TYPE;
521: TYPE intr_thread_tab IS TABLE OF bix_email_details_f.interaction_threads_in_period%TYPE;
522: TYPE week_id_tab IS TABLE OF fii_time_day.week_id%TYPE;
523: TYPE ent_period_id_tab IS TABLE OF fii_time_day.ent_period_id%TYPE;
524: TYPE ent_qtr_id_tab IS TABLE OF fii_time_day.ent_qtr_id%TYPE;
525: TYPE ent_year_id_tab IS TABLE OF fii_time_day.ent_year_id%TYPE;
526: TYPE week_start_date_tab IS TABLE OF fii_time_day.week_start_date%TYPE;
527: TYPE ent_period_start_date_tab IS TABLE OF fii_time_day.ent_period_start_date%TYPE;
528: TYPE ent_qtr_start_date_tab IS TABLE OF fii_time_day.ent_qtr_start_date%TYPE;
529: TYPE ent_year_start_date_tab IS TABLE OF fii_time_day.ent_year_start_date%TYPE;
530: TYPE curr_depth_tab IS TABLE OF bix_email_details_f.one_rsln_in_period%TYPE;
531:
532: l_root_interaction_id root_interaction_id_tab;
533: l_agent_id agent_id_tab;
534: l_party_id party_id_tab;
717: so one and done becomes 0.. */
718:
719: FORALL i IN l_root_interaction_id.FIRST .. l_root_interaction_id.LAST
720:
721: MERGE INTO bix_email_details_f bed
722: USING (
723: SELECT
724: l_agent_id(i) agent_id
725: ,l_email_account_id(i) email_account_id
873: ,decode(change.four_rsln_in_period, 0, to_number(null), change.four_rsln_in_period)
874: ,decode(change.interaction_threads_in_period, 0, to_number(null), change.interaction_threads_in_period));
875:
876:
877: write_log('Total rows inserted/updated in bix_email_details_f for resolution : ' ||
878: to_char(l_root_interaction_id.COUNT * 6));
879: g_rows_ins_upd := g_rows_ins_upd + (l_root_interaction_id.COUNT * 6);
880:
881: /* Update the bix_interactions_temp table to keep track of depth by interaction */
978: IF (g_worker.COUNT > 0) THEN
979: FOR i IN g_worker.FIRST .. g_worker.LAST
980: LOOP
981: LOOP
982: DELETE bix_email_details_f
983: WHERE request_id = g_worker(i)
984: AND rownum <= g_commit_chunk_size ;
985:
986: l_rows_deleted := SQL%ROWCOUNT;
997:
998: /* Delete the rows from eMail summary table inserted in the current run */
999: write_log('Deleting data from summary tables inserted through this main program');
1000: LOOP
1001: DELETE bix_email_details_f
1002: WHERE request_id = g_request_id
1003: AND rownum <= g_commit_chunk_size ;
1004:
1005: l_rows_deleted := SQL%ROWCOUNT;
1064: l_email_service_level := 24 * 60 * 60;
1065: END IF;
1066: write_log('The service level for the whole email center : ' || to_char(l_email_service_level) || ' seconds');
1067:
1068: write_log('Merging additive measures into table bix_email_details_f');
1069:
1070: --
1071: --Merge additive measures into the staging table - staging table introduced
1072: --to avoid issues with ROLLUP
4226: write_log('Number of rows updated in table bix_email_details_stg for Emails in Queue ' || to_char(SQL%ROWCOUNT));
4227:
4228: COMMIT;
4229:
4230: write_log('Number of rows updated in table bix_email_details_f : ' || to_char(SQL%ROWCOUNT));
4231:
4232: g_rows_ins_upd := g_rows_ins_upd + SQL%ROWCOUNT;
4233:
4234: write_log('Finished procedure collect_emails at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
4245: BEGIN
4246:
4247: write_log('Start of the procedure rollup_negatives at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
4248:
4249: write_log('Merging day rows to week, month, quarter, year bucket in table bix_email_details_f');
4250:
4251: /* Rollup half hour information to day, week, month, quarter, year time bucket for table bix_email_details_f */
4252:
4253:
4247: write_log('Start of the procedure rollup_negatives at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
4248:
4249: write_log('Merging day rows to week, month, quarter, year bucket in table bix_email_details_f');
4250:
4251: /* Rollup half hour information to day, week, month, quarter, year time bucket for table bix_email_details_f */
4252:
4253:
4254: MERGE INTO bix_email_details_f bead
4255: USING (
4250:
4251: /* Rollup half hour information to day, week, month, quarter, year time bucket for table bix_email_details_f */
4252:
4253:
4254: MERGE INTO bix_email_details_f bead
4255: USING (
4256: SELECT
4257: rlp.agent_id agent_id,
4258: rlp.email_account_id email_account_id,
4384: sum(inv2.THREE_RSLN_IN_PERIOD) THREE_RSLN_IN_PERIOD,
4385: sum(inv2.FOUR_RSLN_IN_PERIOD) FOUR_RSLN_IN_PERIOD,
4386: sum(inv2.INTERACTION_THREADS_IN_PERIOD) INTERACTION_THREADS_IN_PERIOD
4387: FROM
4388: (SELECT /*+ index(bead BIX_EMAIL_DETAILS_F_N1) */
4389: bead.agent_id agent_id,
4390: bead.email_account_id email_account_id,
4391: bead.email_classification_id email_classification_id,
4392: bead.party_id party_id,
4572: bead.outcome_id, bead.result_id, bead.reason_id,
4573: ftd.ent_year_id
4574: order by to_date(to_char(bead.period_start_date, 'dd/mm/yyyy ') ||
4575: lpad(bead.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_week_plus
4576: FROM bix_email_details_f bead,
4577: fii_time_day ftd
4578: WHERE bead.time_id = ftd.report_date_julian
4579: AND bead.period_type_id = 1
4580: --AND bead.period_start_date >= p_min_date --DOES NOT PERFORM INDEX SCAN IF USING PERIOD_START_DATE
4897: BEGIN
4898:
4899: write_log('Start of the procedure summarize_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
4900:
4901: write_log('Merging day rows to week, month, quarter, year bucket in table bix_email_details_f');
4902:
4903: /* Rollup half hour informations to day, week, month, quarter, year time bucket for table bix_email_details_f */
4904: MERGE INTO bix_email_details_stg bead
4905: USING (
4899: write_log('Start of the procedure summarize_data at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
4900:
4901: write_log('Merging day rows to week, month, quarter, year bucket in table bix_email_details_f');
4902:
4903: /* Rollup half hour informations to day, week, month, quarter, year time bucket for table bix_email_details_f */
4904: MERGE INTO bix_email_details_stg bead
4905: USING (
4906: SELECT
4907: rlp.agent_id agent_id,
5024: min(inv2.week_acc_emails_week_plus)) acc_emails_week_plus,
5025: sum(inv2.EMAILS_REROUTED_IN_PERIOD) EMAILS_REROUTED_IN_PERIOD,
5026: sum(inv2.LEADS_CREATED_IN_PERIOD) LEADS_CREATED_IN_PERIOD
5027: FROM
5028: (SELECT /*+ index(bead BIX_EMAIL_DETAILS_F_N1) */
5029: bead.agent_id agent_id,
5030: bead.email_account_id email_account_id,
5031: bead.email_classification_id email_classification_id,
5032: bead.party_id party_id,
5458: IS
5459:
5460: BEGIN
5461:
5462: MERGE INTO BIX_EMAIL_DETAILS_F summ
5463: USING
5464: (
5465: SELECT
5466: AGENT_ID,
5793: );
5794:
5795: g_rows_ins_upd := g_rows_ins_upd + SQL%ROWCOUNT;
5796: --IF (g_debug_flag = 'Y') THEN
5797: --write_log('Total rows moved into BIX_EMAIL_DETAILS_F : ' ||
5798: --g_rows_ins_upd);
5799: --END IF;
5800:
5801: COMMIT;
5854: l_wip_cnt,
5855: l_completed_cnt,
5856: l_total_cnt
5857: FROM BIX_WORKER_JOBS
5858: WHERE object_name = 'BIX_EMAIL_DETAILS_F';
5859:
5860: write_log('Job status - Unassigned: '||l_unassigned_cnt||
5861: ' In Process: '||l_wip_cnt||
5862: ' Completed: '||l_completed_cnt||
5879: SET status = 'IN PROCESS',
5880: worker_number = p_worker_no
5881: WHERE status = 'UNASSIGNED'
5882: AND rownum < 2
5883: AND object_name = 'BIX_EMAIL_DETAILS_F';
5884:
5885: l_count := sql%rowcount;
5886: COMMIT;
5887: END IF;
5904: INTO l_start_date_range, l_end_date_range
5905: FROM BIX_WORKER_JOBS
5906: WHERE worker_number = p_worker_no
5907: AND status = 'IN PROCESS'
5908: AND object_name = 'BIX_EMAIL_DETAILS_F';
5909:
5910: g_collect_start_date := l_start_date_range;
5911: g_collect_end_date := l_end_date_range;
5912:
5919: UPDATE BIX_WORKER_JOBS
5920: SET status = 'COMPLETED'
5921: WHERE status = 'IN PROCESS'
5922: AND worker_number = p_worker_no
5923: AND object_name = 'BIX_EMAIL_DETAILS_F';
5924:
5925: COMMIT;
5926:
5927: EXCEPTION
5931: UPDATE BIX_WORKER_JOBS
5932: SET status = 'FAILED'
5933: WHERE worker_number = p_worker_no
5934: AND status = 'IN PROCESS'
5935: AND object_name = 'BIX_EMAIL_DETAILS_F';
5936:
5937: COMMIT;
5938: write_log('Error in worker');
5939: RAISE G_CHILD_PROCESS_ISSUE;
6048: l_wip_cnt,
6049: l_failed_cnt,
6050: l_tot_cnt
6051: FROM BIX_WORKER_JOBS
6052: WHERE OBJECT_NAME = 'BIX_EMAIL_DETAILS_F';
6053:
6054: IF (l_failed_cnt > 0) THEN
6055: RAISE G_CHILD_PROCESS_ISSUE;
6056: END IF;
6253:
6254:
6255:
6256:
6257: BIS_COLLECTION_UTILITIES.get_last_refresh_dates('BIX_EMAIL_DETAILS_F',
6258: l_last_start_date,
6259: l_last_end_date,
6260: l_last_period_from,
6261: l_last_period_to);
6332: --
6333:
6334:
6335: /*
6336: DELETE BIX_EMAIL_DETAILS_F
6337: WHERE period_start_date >= trunc(l_min_media_date)
6338: AND period_type_id = 1;
6339: */
6340: --Update all the measures to 0 except the onedone,twodone resolution measures and interaction threads
6337: WHERE period_start_date >= trunc(l_min_media_date)
6338: AND period_type_id = 1;
6339: */
6340: --Update all the measures to 0 except the onedone,twodone resolution measures and interaction threads
6341: update bix_email_details_f
6342: set
6343: LAST_UPDATED_BY =g_user_id,
6344: LAST_UPDATE_DATE =g_sysdate,
6345: EMAILS_OFFERED_IN_PERIOD =null,
6390: --PERFORM BETTER
6391: --
6392:
6393: COMMIT;
6394: write_log ('Finished deleting bix_email_details_f');
6395:
6396: DELETE BIX_INTERACTIONS_TEMP BIXTEMP
6397: WHERE EXISTS
6398: (