-- MIS_BBV_PKG_IMPORT ------------------------------------------------------------------------ function MIS_BBV_FN_GET_DURATION_MILLIS (p_duration_millis IN varchar2) return number; FUNCTION mis_bbv_fn_get_number_type(p_called_number in varchar2, p_facilitiesused in varchar2) return varchar2; procedure mis_bbv_sp_daily_import; procedure mis_bbv_sp_pop_daily_actions(p_date IN varchar2, o_result OUT number); procedure mis_bbv_sp_pop_account_summary(p_date IN varchar2, o_result OUT number); procedure mis_bbv_sp_load_call_data(o_result OUT number); procedure mis_bbv_sp_pop_call_data_type1(o_result OUT NUMBER); procedure mis_bbv_sp_pop_call_data_type2(o_result OUT NUMBER); procedure mis_bbv_sp_pop_calldata_type41(o_result OUT NUMBER); procedure mis_bbv_sp_pop_calldata_type42(o_result OUT NUMBER); End MIS_BBV_PKG_IMPORT; PACKAGE BODY "MIS_BBV_PKG_IMPORT" AS v_pk_name CONSTANT VARCHAR2(40) := 'mis_bbv_pkg_import'; v_sp_name VARCHAR2(40) := ''; v_count INTEGER; v_stats_date VARCHAR2(10); v_todays_date VARCHAR2(10); v_result NUMBER := 0; v_date DATE := ''; v_debug INTEGER := 0; x BINARY_INTEGER := 0; y BINARY_INTEGER := 0; z BINARY_INTEGER := 0; v_hrs NUMBER := 0; v_mins NUMBER := 0; v_secs NUMBER := 0; v_millis NUMBER := 0; pos1 INTEGER; pos2 INTEGER; pos3 INTEGER; v_dur_millis NUMBER(38) := 0; v_number_type VARCHAR2(50) := NULL; TYPE t_bbv_logs IS TABLE OF mis_bbv_time_log%ROWTYPE INDEX BY BINARY_INTEGER; vt_bbv_logs t_bbv_logs; -------------------------------------------------------------------------- CURSOR c_onnet_like IS SELECT called_number_from AS c_number FROM mis_bbv_onnet WHERE onnet_flag = 'Y' AND range_flag = 'N'; CURSOR c_onnet_range IS SELECT called_number_from ,called_number_to FROM mis_bbv_onnet WHERE onnet_flag = 'Y' AND range_flag = 'Y'; ----------------------------------------------------------------------------------------------------------------------- FUNCTION mis_bbv_fn_get_duration_millis(p_duration_millis IN VARCHAR2) RETURN NUMBER IS BEGIN pos1 := instr(TRIM(p_duration_millis) ,':' ,1 ,1); pos2 := instr(TRIM(p_duration_millis) ,':' ,1 ,2); pos3 := instr(TRIM(p_duration_millis) ,' ' ,1 ,1); v_hrs := to_number(substr(TRIM(p_duration_millis) ,1 ,pos1 - 1)); v_mins := to_number(substr(TRIM(p_duration_millis) ,pos1 + 1 ,2)); v_secs := to_number(substr(TRIM(p_duration_millis) ,pos2 + 1 ,2)); v_millis := to_number(substr(TRIM(p_duration_millis) ,pos3 + 1 ,3)); v_dur_millis := v_millis + v_secs * 1000 + v_mins * 60 * 1000 + v_hrs * 3600 * 1000; RETURN v_dur_millis; END mis_bbv_fn_get_duration_millis; ----------------------------------------------------------------------------------------------------------------------- FUNCTION mis_bbv_fn_get_number_type(p_called_number IN VARCHAR2 ,p_facilitiesused IN VARCHAR2) RETURN VARCHAR2 IS v_called_number VARCHAR2(40); BEGIN v_number_type := NULL; v_sp_name := 'mis_bbv_fn_get_number_type'; IF p_called_number = '1571' THEN v_number_type := '1571'; END IF; IF p_called_number = '1475' THEN v_number_type := '1475'; END IF; IF (p_called_number = '1471') AND (p_facilitiesused = 'callreturn') THEN v_number_type := '1471'; END IF; IF p_called_number = '118500' THEN v_number_type := '118500'; END IF; IF p_called_number = '118505' THEN v_number_type := '118505'; END IF; -- FIx for bug 2516 -- if p_called_number in ('0044800', '0044808', '0044500', '44800', '44808', '44500' ) then IF p_called_number LIKE '0044800%' OR p_called_number LIKE '0044808%' OR p_called_number LIKE '0044500%' OR p_called_number LIKE '44800%' OR p_called_number LIKE '44808%' OR p_called_number LIKE '44500%' THEN v_number_type := 'Free calls'; END IF; IF (p_called_number LIKE '00441%') OR (p_called_number LIKE '00442%') OR (p_called_number LIKE '441%') OR (p_called_number LIKE '442%') THEN v_number_type := 'National'; END IF; IF (p_called_number LIKE '00%') AND (p_called_number NOT LIKE '0044%') THEN v_number_type := 'International'; END IF; IF (p_called_number LIKE '00447%') OR (p_called_number LIKE '447%') THEN v_number_type := 'Mobile'; END IF; IF (p_called_number LIKE '00449%') OR (p_called_number LIKE '449%') THEN v_number_type := 'Premium'; END IF; -- Fix for bug 2516 /*if ((p_called_number like '00448%') or (p_called_number like '448%')) and (p_called_number not in ('0044800', '0044808', '44800', '44808')) then */ IF ((p_called_number LIKE '00448%') OR (p_called_number LIKE '448%')) AND (p_called_number NOT LIKE '0044800%' AND p_called_number NOT LIKE '0044808%' AND p_called_number NOT LIKE '44800%' AND p_called_number NOT LIKE '44808%') THEN v_number_type := 'NonGeo'; END IF; IF (p_called_number LIKE '1%') AND (p_called_number NOT IN ('1471', '1475', '1571', '118500', '118505')) THEN v_number_type := '1x'; END IF; -- Fix for bug 2516 -- (p_called_number not in ('0044500', '44500')) then IF ((p_called_number LIKE '00445%') OR (p_called_number LIKE '445%')) AND (p_called_number NOT LIKE '0044500%' AND p_called_number NOT LIKE '44500%') THEN IF (p_called_number LIKE '00445%') THEN v_called_number := substr(TRIM(p_called_number) ,5); ELSE IF (p_called_number LIKE '445%') THEN v_called_number := substr(TRIM(p_called_number) ,3); END IF; END IF; FOR v_row IN c_onnet_like LOOP IF v_called_number LIKE v_row.c_number THEN v_number_type := 'Onnet'; END IF; EXIT WHEN v_called_number LIKE v_row.c_number; END LOOP; IF v_number_type IS NULL THEN FOR v_row IN c_onnet_range LOOP IF (v_called_number >= v_row.called_number_from AND v_called_number <= v_row.called_number_to) THEN v_number_type := 'Onnet'; END IF; EXIT WHEN(v_called_number >= v_row.called_number_from AND v_called_number <= v_row.called_number_to); END LOOP; END IF; END IF; IF v_number_type IS NULL THEN v_number_type := 'Others'; END IF; IF p_facilitiesused = 'returnedcall' THEN v_number_type := v_number_type || '/14713'; END IF; IF p_called_number = '14713' THEN v_number_type := '14713'; END IF; RETURN v_number_type; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbfl_error.raise_general(v_pk_name || '.' || v_sp_name); END mis_bbv_fn_get_number_type; ----------------------------------------------------------------------------------------------------------------------- PROCEDURE mis_bbv_sp_daily_import IS BEGIN v_sp_name := 'mis_bbv_sp_daily_import'; SELECT to_char(SYSDATE ,'DD-MM-YYYY') INTO v_todays_date FROM dual; SELECT to_char(last_date_processed + 1 ,'DD-MM-YYYY') INTO v_stats_date FROM mis_job_control WHERE package_name = v_pk_name; v_date := to_date(v_stats_date ,'DD-MM-YYYY'); IF to_date(v_stats_date ,'DD-MM-YYYY') < to_date(v_todays_date ,'DD-MM-YYYY') THEN v_result := 1; ELSE v_result := 0; dbfl_error.raise_error('S' ,v_pk_name || ' No data for ' || v_stats_date || ' found. No processing done' ,'MIS'); END IF; IF v_result = 1 THEN BEGIN mis_bbv_sp_pop_daily_actions(v_stats_date ,v_result); END; END IF; IF v_result = 1 THEN BEGIN mis_bbv_sp_pop_account_summary(v_stats_date ,v_result); END; END IF; IF v_result = 1 THEN BEGIN mis_bbv_sp_load_call_data(v_result); END; END IF; IF v_result = 1 THEN UPDATE mis_job_control SET last_date_processed = v_date ,update_date = SYSDATE WHERE package_name = v_pk_name; COMMIT; dbfl_error.raise_error('I' ,v_pk_name || ' Completed OK' ,'MIS'); ELSE ROLLBACK; dbfl_error.raise_error('S' ,v_pk_name || ' failed and was rolled back' ,'MIS'); END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbfl_error.raise_general(v_pk_name || '.' || v_sp_name); END mis_bbv_sp_daily_import; ----------------------------------------------------------------------------------------------------------------------- PROCEDURE mis_bbv_sp_pop_daily_actions(p_date IN VARCHAR2 ,o_result OUT NUMBER) IS CURSOR c_get_actions IS SELECT a.origin ,b.account_type ,c.action FROM mis_bbv_origin a ,mis_bbv_account_type b ,mis_bbv_action_list c; BEGIN v_sp_name := 'mis_bbv_sp_pop_daily_actions'; v_date := to_date(p_date ,'DD-MM-YYYY'); IF v_debug = 1 THEN dbms_output.put_line(v_sp_name || ' running'); END IF; SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; EXECUTE IMMEDIATE 'truncate table R1OSS_BBV_ACCOUNT_SUMMARY '; EXECUTE IMMEDIATE 'truncate table R1OSS_BBV_ACCOUNTS '; EXECUTE IMMEDIATE 'truncate table mis_bbv_mailbox_info'; EXECUTE IMMEDIATE 'truncate table R1OSS_BUSINESS_CANCEL_ORDER '; SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Truncatation of R1OSS Tables'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; INSERT INTO r1oss_bbv_account_summary (customer_id ,email_id ,origin ,account_type ,line1 ,line2 ,status ,call_plan ,btbb_status ,olnumbertype ,number_type2 ,cpe_code ,line_1_tariff ,line_2_tariff) (SELECT customer_id ,email_id ,upper(origin) ,decode(account_type ,'bbvBtbus' ,'BBV_BUS' ,'bbvBtBusiness' ,'BBV_BUS' , -- Added by Amit for IPCentrix changes 'bbvBtBusFens' ,'BBV_BUS' , -- Added by Amit for IPCentrix changes 'bbvBtCon' ,'BBV_CON' , -- Added by Amit for IPCentrix changes 'bbvBTConsumer' ,'BBV_CON' , -- Added by Amit for IPCentrix changes 'bbvNonBTConsumer' ,'BBV_CON' ,'bbvNonBtCon' ,'BBV_CON' ,'bbvConEoi' ,'BBV_CON_EOI' , --Added by Raghu Meda for EOI Customer Changes account_type) ,decode(ltrim(lower(line1) ,'0') ,NULL ,NULL ,'none' ,NULL ,'0044' || ltrim(lower(line1) ,'0')) ,decode(ltrim(lower(line2) ,'0') ,NULL ,NULL ,'none' ,NULL ,'0044' || ltrim(lower(line2) ,'0')) ,lower(status) ,decode(upper(call_plan) ,'ANY' ,'ANYTIME' ,'ANYBB' ,'ANYTIME' ,'PER' ,'E+W' ,'FRE-PER' ,'E+W' ,upper(call_plan)) ,nvl(upper(btbb_status) ,'N') ,number_type1 ,number_type2 ,cpe_code , --bug 4057 line_1_tariff , --bug 4057 line_2_tariff --bug 4057 FROM bbv_account_summary@r1oss.bt.com); SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insertion into R1OSS_BBV_ACCOUNT_SUMMARY'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; -------------------------------- SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; INSERT INTO r1oss_bbv_accounts (customer_id ,email_id ,registration_date ,activation_date ,ceased_date) (SELECT customer_id ,email_id ,registration_date ,activation_date ,ceased_date FROM bbv_accounts@r1oss.bt.com WHERE customer_id IS NOT NULL); COMMIT; SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insertion into R1OSS_BBV_ACCOUNTS'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; -------------------------------- SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; INSERT INTO mis_bbv_mailbox_info (tel_no ,order_date ,customer_type ,order_type ,order_channel ,prev_mailbox_type ,cur_mailbox_type) (SELECT voip_number ,order_date ,customer_type ,order_type ,order_channel ,previous_mailbox ,current_mailbox FROM mailbox_orders@r1oss.bt.com); SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insertion into mis_bbv_mailbox_info'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; -------------------------------- SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; INSERT INTO mis_bbv_action_log (action_log_id ,action_date ,customer_id ,user_name ,origin ,account_type ,action ,create_date ,update_date) (SELECT a.action_log_id ,a.actiondate ,a.customer_id ,a.user_name ,upper(a.origin) ,upper(b.account_type) ,lower(c.action) ,trunc(SYSDATE) ,trunc(SYSDATE) FROM bbv_mis_actions@r1oss.bt.com a ,r1oss_bbv_account_summary b ,mis_bbv_action_list c WHERE a.actiondate >= v_date AND a.actiondate < v_date + 1 AND b.customer_id = a.customer_id AND a.action_id = c.action_id AND b.account_type IN ('BBV_CON', 'BBV_BUS', 'BBV_CON_EOI') --Addition by Raghu Meda for EOI GROUP BY a.action_log_id ,a.actiondate ,a.customer_id ,a.user_name ,upper(a.origin) ,upper(b.account_type) ,lower(c.action) ,trunc(SYSDATE) ,trunc(SYSDATE)); SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insertion into MIS_BBV_ACTION_LOG'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; -------------------------------- SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; FOR v_row IN c_get_actions LOOP SELECT COUNT(1) INTO v_count FROM mis_bbv_action_log WHERE action_date >= v_date AND action_date < v_date + 1 AND action = v_row.action AND account_type = v_row.account_type AND origin = v_row.origin; INSERT INTO mis_bbv_daily_actions VALUES (v_date ,v_row.origin ,v_row.account_type ,v_row.action ,v_count ,SYSDATE ,SYSDATE); END LOOP; SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insertion into MIS_BBV_DAILY_ACTIONS'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; --Added for Cancel Order Report for Business Customers By Avinash------ SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; INSERT INTO r1oss_business_cancel_order (order_ref_no ,cease_reason ,archived) (SELECT order_ref_no ,cancel_reason ,archived FROM business_cancel_order_view@r1oss.bt.com); COMMIT; SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insert rows into R1OSS_BUSINESS_CANCEL_ORDER'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; o_result := 1; EXCEPTION WHEN OTHERS THEN dbfl_error.raise_general(v_pk_name || '.' || v_sp_name); o_result := 0; END mis_bbv_sp_pop_daily_actions; ----------------------------------------------------------------------------------------------------------------------- PROCEDURE mis_bbv_sp_pop_account_summary(p_date IN VARCHAR2 ,o_result OUT NUMBER) IS BEGIN v_sp_name := 'mis_bbv_sp_pop_account_summary'; v_date := to_date(p_date ,'DD-MM-YYYY'); IF v_debug = 1 THEN dbms_output.put_line(v_sp_name || ' running'); END IF; SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; INSERT INTO mis_bbv_acc_summary (stats_date ,origin ,account_type ,status ,total ,create_date ,update_date) (SELECT v_date ,origin ,account_type ,nvl(status ,'NULL') ,COUNT(1) ,SYSDATE ,SYSDATE FROM r1oss_bbv_account_summary WHERE account_type IN ('BBV_CON', 'BBV_BUS', 'BBV_CON_EOI') --Added by Raghu for EOI GROUP BY v_date ,origin ,account_type ,status); SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insertion into MIS_BBV_ACC_SUMMARY'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; INSERT INTO mis_bbv_geo_summary (stats_date ,origin ,account_type ,number_type ,total ,create_date ,update_date) (SELECT trunc(b.registration_date) ,a.origin ,a.account_type ,a.olnumbertype ,COUNT(1) ,SYSDATE ,SYSDATE FROM r1oss_bbv_account_summary a ,r1oss_bbv_accounts b WHERE olnumbertype IS NOT NULL AND olnumbertype != 'NONGEO' AND account_type = 'BBV_CON' AND a.customer_id = b.customer_id AND trunc(b.registration_date) = to_date(p_date ,'DD-MM-YYYY') GROUP BY trunc(b.registration_date) ,a.origin ,a.account_type ,a.olnumbertype); --Added by Raghu for EOI INSERT INTO mis_bbv_geo_summary (stats_date ,origin ,account_type ,number_type ,total ,create_date ,update_date) (SELECT trunc(b.registration_date) ,a.origin ,a.account_type ,a.olnumbertype ,COUNT(1) ,SYSDATE ,SYSDATE FROM r1oss_bbv_account_summary a ,r1oss_bbv_accounts b WHERE olnumbertype IS NOT NULL AND account_type = 'BBV_CON_EOI' AND a.customer_id = b.customer_id AND trunc(b.registration_date) = to_date(p_date ,'DD-MM-YYYY') GROUP BY trunc(b.registration_date) ,a.origin ,a.account_type ,a.olnumbertype); SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insertion into MIS_BBV_GEO_SUMMARY'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; o_result := 1; EXCEPTION WHEN OTHERS THEN dbfl_error.raise_general(v_pk_name || '.' || v_sp_name); o_result := 0; END mis_bbv_sp_pop_account_summary; ----------------------------------------------------------------------------------------------------------------------- PROCEDURE mis_bbv_sp_load_call_data(o_result OUT NUMBER) IS v_sp_name VARCHAR2(100) := 'mis_bbv_sp_load_call_data'; sqlmnt VARCHAR2(4000); v_job_run VARCHAR2(12); BEGIN SELECT to_char(last_date_processed + 1 ,'DD-MM-YYYY') INTO v_job_run FROM mis_job_control WHERE package_name = 'mis_bbv_pkg_import'; SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; EXECUTE IMMEDIATE 'truncate table mis_bbv_call_log_bbv'; sqlmnt := 'insert into mis_bbv_call_log_bbv (select * from mis_bbv_call_log where trunc(starttime)=to_date(''' || v_job_run || ''',''DD/mm/yy'')' || ' )'; EXECUTE IMMEDIATE (sqlmnt); COMMIT; SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Create Temp table for Call Log'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; mis_bbv_sp_pop_call_data_type1(o_result); IF o_result = 1 THEN mis_bbv_sp_pop_call_data_type2(o_result); END IF; IF o_result = 1 THEN mis_bbv_sp_pop_calldata_type41(o_result); END IF; IF o_result = 1 THEN mis_bbv_sp_pop_calldata_type42(o_result); END IF; IF o_result = 1 THEN BEGIN INSERT INTO mis_bbv_call_data_period_days (call_date ,customer_id ,origin ,account_type ,duration) (SELECT call_date ,customer_id ,origin ,account_type ,duration FROM mis_bbv_call_data WHERE trunc(call_date) = v_date); COMMIT; -- This code is added by Amit as the earlier code for hilton was not working properly INSERT INTO mis_hilton_summary (SELECT sequence_nbr ,cli ,destinationnumber ,codecinfo ,starttime ,endtime ,startchg ,stopchg ,duration ,durationmillis ,v_date FROM mis_bbv_call_log_bbv WHERE destinationnumber IN (SELECT * FROM mis_premium_numbers)); COMMIT; -- Addition ends here EXCEPTION WHEN OTHERS THEN NULL; END; END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbfl_error.raise_general(v_pk_name || '.' || v_sp_name); o_result := 0; END mis_bbv_sp_load_call_data; ----------------------------------------------------------------------------------------------------------------------- PROCEDURE mis_bbv_sp_pop_call_data_type1(o_result OUT NUMBER) IS v_start_time DATE; v_sp_name VARCHAR2(100) := 'mis_bbv_sp_pop_call_data_type1'; v_btc_time DATE; TYPE t_bbv_call_data IS RECORD( call_data_id mis_bbv_call_data.call_data_id%TYPE ,call_date mis_bbv_call_data.call_date%TYPE ,customer_id mis_bbv_call_data.customer_id%TYPE ,user_name mis_bbv_call_data.user_name%TYPE ,origin mis_bbv_call_data.origin%TYPE ,account_type mis_bbv_call_data.account_type%TYPE ,call_type mis_bbv_call_data.call_type%TYPE ,calling_number mis_bbv_call_data.calling_number%TYPE ,called_number mis_bbv_call_data.called_number%TYPE ,duration mis_bbv_call_data.duration%TYPE ,duration_millis mis_bbv_call_data.duration_millis%TYPE ,partial_cdrs mis_bbv_call_data.partial_cdrs%TYPE ,termination_code mis_bbv_call_data.termination_code%TYPE ,termination_desc mis_bbv_call_data.termination_desc%TYPE ,success_flag mis_bbv_call_data.success_flag%TYPE ,number_type mis_bbv_call_data.number_type%TYPE ,create_date mis_bbv_call_data.create_date%TYPE ,update_date mis_bbv_call_data.update_date%TYPE); TYPE t_bbv_cdr_tab IS TABLE OF t_bbv_call_data INDEX BY PLS_INTEGER; vt_bbv_cdr t_bbv_cdr_tab; long_dur_cdr t_bbv_cdr_tab; TYPE t_bbv_call_log_update IS RECORD( sequence_nbr mis_bbv_call_log.sequence_nbr%TYPE ,create_date mis_bbv_call_log.create_date%TYPE); TYPE t_bbv_call_log_tab IS TABLE OF t_bbv_call_log_update INDEX BY PLS_INTEGER; vt_bbv_call_log_tab t_bbv_call_log_tab; CURSOR c_get_bbv_call_data_calltype1 IS SELECT a.sequence_nbr AS call_data_id ,a.starttime AS call_date ,b.customer_id ,b.email_id ,b.origin ,b.account_type ,a.call_type ,a.cli AS calling_number ,a.destinationnumber AS called_number ,a.originalto AS originalto ,a.durationmillis AS duration_millis ,a.siprtc AS termination_code ,a.endreason AS termination_desc ,a.partialrecordreason ,a.chgid ,a.facilitiesused ,a.create_date FROM mis_bbv_call_log_bbv a ,(SELECT customer_id ,email_id ,origin ,account_type ,line1 ,line2 ,status ,call_plan ,btbb_status FROM r1oss_bbv_account_summary WHERE account_type IN ('BBV_CON', 'BBV_CON_EOI') --Added for EOI /* updated for R22 MIS changes start */ AND TRIM(status) NOT IN ('config_failed', 'order_failed', 'ceased')) b /* updated for R22 MIS changes end */ WHERE a.call_group = 'BTR_BBV' AND (a.partialsequencenbr IS NULL) AND ((a.call_type = 1) AND (a.cli = b.line1)); CURSOR c_get_bbv_longdur_calltype1(v_chgid IN VARCHAR2, v_call_type IN NUMBER) IS SELECT a.sequence_nbr AS call_data_id ,a.starttime AS call_date ,b.customer_id ,b.email_id ,b.origin ,b.account_type ,a.call_type ,a.cli AS calling_number ,a.destinationnumber AS called_number ,a.originalto AS originalto ,a.durationmillis AS duration_millis ,a.siprtc AS termination_code ,a.endreason AS termination_desc ,a.facilitiesused ,a.partialsequencenbr ,a.partialrecordreason ,a.create_date FROM mis_bbv_call_log_bbv a , -- r1oss_bbv_account_summary b -- Changed for live bugs 2515 and (SELECT customer_id ,email_id ,origin ,account_type ,line1 ,line2 ,status ,call_plan ,btbb_status FROM r1oss_bbv_account_summary WHERE account_type IN ('BBV_CON', 'BBV_CON_EOI') -- to consider only consumer customers, as MIS collects only consumer data. /* updated for R22 MIS changes start */ AND TRIM(status) NOT IN ('config_failed', 'order_failed', 'ceased')) b -- to prevent duplicate numbers - BUG 2542 /* updated for R22 MIS changes end */ WHERE a.call_group = 'BTR_BBV' AND a.chgid = v_chgid AND a.call_type = v_call_type AND a.partialsequencenbr IS NOT NULL AND (a.cli = b.line1) ORDER BY a.partialsequencenbr; CURSOR bst_time IS SELECT bst_start_date AS bst_start_date ,bst_end_date AS bst_end_date FROM mis_bst_time; BEGIN SELECT SYSDATE INTO v_start_time FROM dual; x := 0; z := 0; vt_bbv_call_log_tab(z).sequence_nbr := NULL; vt_bbv_call_log_tab(z).create_date := NULL; SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; SELECT SYSDATE INTO v_btc_time FROM dual; FOR v_row IN c_get_bbv_call_data_calltype1 LOOP IF v_row.calling_number LIKE 'sip%' OR v_row.called_number LIKE 'sip%' THEN dbfl_error.raise_error('W' ,'Invalid format for the calling/called number - ' || v_row.calling_number || ' -> ' || v_row.called_number || ' for call_data_id :' || v_row.call_data_id ,v_pk_name || '- c_get_bbv_call_data_calltype1'); END IF; IF lower(v_row.partialrecordreason) = 'lastcdr' THEN long_dur_cdr(0).duration := 0; long_dur_cdr(0).duration_millis := 0; y := 1; FOR v_row1 IN c_get_bbv_longdur_calltype1(v_row.chgid ,v_row.call_type) LOOP IF v_row1.calling_number LIKE 'sip%' OR v_row1.called_number LIKE 'sip%' THEN dbfl_error.raise_error('W' ,'Invalid format for the calling/called number - ' || v_row1.calling_number || ' -> ' || v_row1.called_number || ' for call_data_id :' || v_row1.call_data_id ,v_pk_name || '- c_get_bbv_longdur_calltype1'); END IF; IF v_row1.partialsequencenbr = 1 THEN long_dur_cdr(0).call_data_id := v_row1.call_data_id; long_dur_cdr(0).call_date := v_row1.call_date; long_dur_cdr(0).customer_id := v_row1.customer_id; long_dur_cdr(0).user_name := v_row1.email_id; long_dur_cdr(0).origin := v_row1.origin; long_dur_cdr(0).account_type := v_row1.account_type; long_dur_cdr(0).call_type := v_row1.call_type; long_dur_cdr(0).calling_number := v_row1.calling_number; long_dur_cdr(0).called_number := v_row1.called_number; IF v_row1.called_number = 'msml' THEN pos1 := instr(TRIM(v_row1.originalto) ,':' ,1 ,1); pos2 := instr(TRIM(v_row1.originalto) ,'@' ,1 ,1); long_dur_cdr(0).called_number := substr(TRIM(v_row1.originalto) ,pos1 + 1 ,pos2 - pos1 - 1); END IF; long_dur_cdr(0).number_type := mis_bbv_fn_get_number_type(long_dur_cdr(0) .called_number ,v_row1.facilitiesused); END IF; long_dur_cdr(0).duration_millis := long_dur_cdr(0) .duration_millis + mis_bbv_fn_get_duration_millis(v_row1.duration_millis); IF lower(v_row1.partialrecordreason) = 'lastcdr' THEN long_dur_cdr(0).termination_code := v_row1.termination_code; long_dur_cdr(0).termination_desc := v_row1.termination_desc; long_dur_cdr(0).partial_cdrs := y; END IF; y := y + 1; vt_bbv_call_log_tab(z).sequence_nbr := v_row1.call_data_id; vt_bbv_call_log_tab(z).create_date := v_row1.create_date; z := z + 1; END LOOP; long_dur_cdr(0).duration := round(long_dur_cdr(0) .duration_millis / 1000); vt_bbv_cdr(x) := long_dur_cdr(0); ELSE vt_bbv_cdr(x).duration_millis := 0; vt_bbv_cdr(x).duration := 0; vt_bbv_cdr(x).call_data_id := v_row.call_data_id; vt_bbv_cdr(x).call_date := v_row.call_date; vt_bbv_cdr(x).customer_id := v_row.customer_id; vt_bbv_cdr(x).user_name := v_row.email_id; vt_bbv_cdr(x).origin := v_row.origin; vt_bbv_cdr(x).account_type := v_row.account_type; vt_bbv_cdr(x).call_type := v_row.call_type; vt_bbv_cdr(x).calling_number := v_row.calling_number; vt_bbv_cdr(x).called_number := v_row.called_number; IF v_row.called_number = 'msml' THEN pos1 := instr(TRIM(v_row.originalto) ,':' ,1 ,1); pos2 := instr(TRIM(v_row.originalto) ,'@' ,1 ,1); vt_bbv_cdr(x).called_number := substr(TRIM(v_row.originalto) ,pos1 + 1 ,pos2 - pos1 - 1); END IF; vt_bbv_cdr(x).duration_millis := mis_bbv_fn_get_duration_millis(v_row.duration_millis); vt_bbv_cdr(x).duration := round(vt_bbv_cdr(x) .duration_millis / 1000); vt_bbv_cdr(x).partial_cdrs := 0; vt_bbv_cdr(x).termination_code := v_row.termination_code; vt_bbv_cdr(x).termination_desc := v_row.termination_desc; vt_bbv_cdr(x).number_type := mis_bbv_fn_get_number_type(vt_bbv_cdr(x) .called_number ,v_row.facilitiesused); END IF; IF vt_bbv_cdr(x) .termination_code = 200 OR vt_bbv_cdr(x) .duration_millis > 0 THEN vt_bbv_cdr(x).success_flag := 'Y'; ELSE vt_bbv_cdr(x).success_flag := 'N'; END IF; FOR v_row IN bst_time LOOP IF vt_bbv_cdr(x) .call_date >= v_row.bst_start_date AND vt_bbv_cdr(x) .call_date <= v_row.bst_end_date THEN vt_bbv_cdr(x).call_date := to_date(to_char(vt_bbv_cdr(x) .call_date + (1 / 24) ,'dd-mm-yyyy hh:mi:ss') ,'dd-mm-yyyy hh24:mi:ss'); END IF; IF v_btc_time >= v_row.bst_start_date AND v_btc_time <= v_row.bst_end_date THEN v_btc_time := to_date(to_char(v_btc_time + (1 / 24) ,'dd-mm-yyyy hh:mi:ss') ,'dd-mm-yyyy hh24:mi:ss'); END IF; END LOOP; INSERT INTO mis_bbv_call_data (call_data_id ,call_date ,customer_id ,user_name ,origin ,account_type ,call_type ,calling_number ,called_number ,duration ,duration_millis ,partial_cdrs ,termination_code ,termination_desc ,success_flag ,number_type ,create_date ,update_date) VALUES (vt_bbv_cdr(x) .call_data_id , vt_bbv_cdr(x) .call_date , vt_bbv_cdr(x) .customer_id , vt_bbv_cdr(x) .user_name , vt_bbv_cdr(x) .origin , vt_bbv_cdr(x) .account_type , vt_bbv_cdr(x) .call_type , vt_bbv_cdr(x) .calling_number , vt_bbv_cdr(x) .called_number , vt_bbv_cdr(x) .duration , vt_bbv_cdr(x) .duration_millis , vt_bbv_cdr(x) .partial_cdrs , vt_bbv_cdr(x) .termination_code , vt_bbv_cdr(x) .termination_desc , vt_bbv_cdr(x) .success_flag , vt_bbv_cdr(x) .number_type ,v_btc_time ,v_btc_time); x := x + 1; vt_bbv_call_log_tab(z).sequence_nbr := v_row.call_data_id; vt_bbv_call_log_tab(z).create_date := v_row.create_date; z := z + 1; END LOOP; SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insert rows into MIS_BBV_CALL_DATA for call type 1'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; o_result := 1; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbfl_error.raise_general(v_pk_name || '.' || v_sp_name); o_result := 0; END mis_bbv_sp_pop_call_data_type1; ------------------------------------------------------------------------------------- PROCEDURE mis_bbv_sp_pop_call_data_type2(o_result OUT NUMBER) IS v_start_time DATE; v_sp_name VARCHAR2(100) := 'mis_bbv_sp_pop_call_data_type2'; v_btc_time DATE; TYPE t_bbv_call_data IS RECORD( call_data_id mis_bbv_call_data.call_data_id%TYPE ,call_date mis_bbv_call_data.call_date%TYPE ,customer_id mis_bbv_call_data.customer_id%TYPE ,user_name mis_bbv_call_data.user_name%TYPE ,origin mis_bbv_call_data.origin%TYPE ,account_type mis_bbv_call_data.account_type%TYPE ,call_type mis_bbv_call_data.call_type%TYPE ,calling_number mis_bbv_call_data.calling_number%TYPE ,called_number mis_bbv_call_data.called_number%TYPE ,duration mis_bbv_call_data.duration%TYPE ,duration_millis mis_bbv_call_data.duration_millis%TYPE ,partial_cdrs mis_bbv_call_data.partial_cdrs%TYPE ,termination_code mis_bbv_call_data.termination_code%TYPE ,termination_desc mis_bbv_call_data.termination_desc%TYPE ,success_flag mis_bbv_call_data.success_flag%TYPE ,number_type mis_bbv_call_data.number_type%TYPE ,create_date mis_bbv_call_data.create_date%TYPE ,update_date mis_bbv_call_data.update_date%TYPE); TYPE t_bbv_cdr_tab IS TABLE OF t_bbv_call_data INDEX BY PLS_INTEGER; vt_bbv_cdr t_bbv_cdr_tab; long_dur_cdr t_bbv_cdr_tab; TYPE t_bbv_call_log_update IS RECORD( sequence_nbr mis_bbv_call_log.sequence_nbr%TYPE ,create_date mis_bbv_call_log.create_date%TYPE); TYPE t_bbv_call_log_tab IS TABLE OF t_bbv_call_log_update INDEX BY PLS_INTEGER; vt_bbv_call_log_tab t_bbv_call_log_tab; CURSOR c_get_bbv_call_data_calltype2 IS SELECT a.sequence_nbr AS call_data_id ,a.starttime AS call_date ,b.customer_id ,b.email_id ,b.origin ,b.account_type ,a.call_type ,a.cli AS calling_number ,a.destinationnumber AS called_number ,a.originalto AS originalto ,a.durationmillis AS duration_millis ,a.siprtc AS termination_code ,a.endreason AS termination_desc ,a.partialrecordreason ,a.chgid ,a.facilitiesused ,a.create_date FROM mis_bbv_call_log_bbv a , -- r1oss_bbv_account_summary b -- Changed for live bugs 2515 and (SELECT customer_id ,email_id ,origin ,account_type ,line1 ,line2 ,status ,call_plan ,btbb_status FROM r1oss_bbv_account_summary WHERE account_type IN ('BBV_CON', 'BBV_CON_EOI') -- to consider only consumer customers, as MIS collects only consumer data. /* updated for R22 MIS changes start */ AND TRIM(status) NOT IN ('config_failed', 'order_failed', 'ceased')) b -- to prevent duplicate numbers - BUG 2542 /* updated for R22 MIS changes end */ WHERE a.call_group = 'BTR_BBV' AND (a.partialsequencenbr IS NULL) -- OR lower(A.PARTIALRECORDREASON) = 'lastcdr') AND ((a.call_type = 2) AND (a.destinationnumber IS NOT NULL) AND (a.destinationnumber = b.line1)); -- FOR UPDATE OF a.processed_flag; CURSOR c_get_bbv_longdur_calltype2(v_chgid IN VARCHAR2, v_call_type IN NUMBER) IS SELECT a.sequence_nbr AS call_data_id ,a.starttime AS call_date ,b.customer_id ,b.email_id ,b.origin ,b.account_type ,a.call_type ,a.cli AS calling_number ,a.destinationnumber AS called_number ,a.originalto AS originalto ,a.durationmillis AS duration_millis ,a.siprtc AS termination_code ,a.endreason AS termination_desc ,a.facilitiesused ,a.partialsequencenbr ,a.partialrecordreason ,a.create_date FROM mis_bbv_call_log_bbv a , -- r1oss_bbv_account_summary b -- Changed for live bugs 2515 and (SELECT customer_id ,email_id ,origin ,account_type ,line1 ,line2 ,status ,call_plan ,btbb_status FROM r1oss_bbv_account_summary WHERE account_type IN ('BBV_CON', 'BBV_CON_EOI') -- to consider only consumer customers, as MIS collects only consumer data. /* updated for R22 MIS changes start */ AND TRIM(status) NOT IN ('config_failed', 'order_failed', 'ceased')) b -- to prevent duplicate numbers - BUG 2542 /* updated for R22 MIS changes end */ WHERE a.call_group = 'BTR_BBV' AND a.chgid = v_chgid AND a.call_type = v_call_type AND a.partialsequencenbr IS NOT NULL AND ((a.destinationnumber IS NOT NULL) AND (a.destinationnumber = b.line1)) ORDER BY a.partialsequencenbr; -- FOR UPDATE OF a.processed_flag; CURSOR bst_time IS SELECT bst_start_date AS bst_start_date ,bst_end_date AS bst_end_date FROM mis_bst_time; BEGIN SELECT SYSDATE INTO v_start_time FROM dual; x := 0; z := 0; vt_bbv_call_log_tab(z).sequence_nbr := NULL; vt_bbv_call_log_tab(z).create_date := NULL; --- THIS IS FOR MIS PERFORMANCE TUNING--- --/* SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; --*/ SELECT SYSDATE INTO v_btc_time FROM dual; FOR v_row IN c_get_bbv_call_data_calltype2 LOOP IF v_row.calling_number LIKE 'sip%' OR v_row.called_number LIKE 'sip%' THEN dbfl_error.raise_error('W' ,'Invalid format for the calling/called number - ' || v_row.calling_number || ' -> ' || v_row.called_number || ' for call_data_id :' || v_row.call_data_id ,v_pk_name || '- c_get_bbv_call_data_calltype2'); END IF; IF lower(v_row.partialrecordreason) = 'lastcdr' THEN long_dur_cdr(0).duration := 0; long_dur_cdr(0).duration_millis := 0; y := 1; FOR v_row1 IN c_get_bbv_longdur_calltype2(v_row.chgid ,v_row.call_type) LOOP IF v_row1.calling_number LIKE 'sip%' OR v_row1.called_number LIKE 'sip%' THEN dbfl_error.raise_error('W' ,'Invalid format for the calling/called number - ' || v_row1.calling_number || ' -> ' || v_row1.called_number || ' for call_data_id :' || v_row1.call_data_id ,v_pk_name || '- c_get_bbv_longdur_calltype2'); END IF; IF v_row1.partialsequencenbr = 1 THEN long_dur_cdr(0).call_data_id := v_row1.call_data_id; long_dur_cdr(0).call_date := v_row1.call_date; long_dur_cdr(0).customer_id := v_row1.customer_id; long_dur_cdr(0).user_name := v_row1.email_id; long_dur_cdr(0).origin := v_row1.origin; long_dur_cdr(0).account_type := v_row1.account_type; long_dur_cdr(0).call_type := v_row1.call_type; long_dur_cdr(0).calling_number := v_row1.calling_number; long_dur_cdr(0).called_number := v_row1.called_number; long_dur_cdr(0).number_type := mis_bbv_fn_get_number_type(long_dur_cdr(0) .called_number ,v_row1.facilitiesused); END IF; long_dur_cdr(0).duration_millis := long_dur_cdr(0) .duration_millis + mis_bbv_fn_get_duration_millis(v_row1.duration_millis); IF lower(v_row1.partialrecordreason) = 'lastcdr' THEN long_dur_cdr(0).termination_code := v_row1.termination_code; long_dur_cdr(0).termination_desc := v_row1.termination_desc; long_dur_cdr(0).partial_cdrs := y; END IF; y := y + 1; vt_bbv_call_log_tab(z).sequence_nbr := v_row1.call_data_id; vt_bbv_call_log_tab(z).create_date := v_row1.create_date; z := z + 1; END LOOP; long_dur_cdr(0).duration := round(long_dur_cdr(0) .duration_millis / 1000); vt_bbv_cdr(x) := long_dur_cdr(0); ELSE vt_bbv_cdr(x).duration_millis := 0; vt_bbv_cdr(x).duration := 0; vt_bbv_cdr(x).call_data_id := v_row.call_data_id; vt_bbv_cdr(x).call_date := v_row.call_date; vt_bbv_cdr(x).customer_id := v_row.customer_id; vt_bbv_cdr(x).user_name := v_row.email_id; vt_bbv_cdr(x).origin := v_row.origin; vt_bbv_cdr(x).account_type := v_row.account_type; vt_bbv_cdr(x).call_type := v_row.call_type; vt_bbv_cdr(x).calling_number := v_row.calling_number; vt_bbv_cdr(x).called_number := v_row.called_number; vt_bbv_cdr(x).duration_millis := mis_bbv_fn_get_duration_millis(v_row.duration_millis); vt_bbv_cdr(x).duration := round(vt_bbv_cdr(x) .duration_millis / 1000); vt_bbv_cdr(x).partial_cdrs := 0; vt_bbv_cdr(x).termination_code := v_row.termination_code; vt_bbv_cdr(x).termination_desc := v_row.termination_desc; vt_bbv_cdr(x).number_type := mis_bbv_fn_get_number_type(vt_bbv_cdr(x) .called_number ,v_row.facilitiesused); END IF; IF vt_bbv_cdr(x) .termination_code = 200 OR vt_bbv_cdr(x) .duration_millis > 0 THEN vt_bbv_cdr(x).success_flag := 'Y'; ELSE vt_bbv_cdr(x).success_flag := 'N'; END IF; FOR v_row IN bst_time LOOP IF vt_bbv_cdr(x) .call_date >= v_row.bst_start_date AND vt_bbv_cdr(x) .call_date <= v_row.bst_end_date THEN vt_bbv_cdr(x).call_date := to_date(to_char(vt_bbv_cdr(x) .call_date + (1 / 24) ,'dd-mm-yyyy hh:mi:ss') ,'dd-mm-yyyy hh24:mi:ss'); END IF; IF v_btc_time >= v_row.bst_start_date AND v_btc_time <= v_row.bst_end_date THEN v_btc_time := to_date(to_char(v_btc_time + (1 / 24) ,'dd-mm-yyyy hh:mi:ss') ,'dd-mm-yyyy hh24:mi:ss'); END IF; END LOOP; INSERT INTO mis_bbv_call_data (call_data_id ,call_date ,customer_id ,user_name ,origin ,account_type ,call_type ,calling_number ,called_number ,duration ,duration_millis ,partial_cdrs ,termination_code ,termination_desc ,success_flag ,number_type ,create_date ,update_date) VALUES (vt_bbv_cdr(x) .call_data_id , vt_bbv_cdr(x) .call_date , vt_bbv_cdr(x) .customer_id , vt_bbv_cdr(x) .user_name , vt_bbv_cdr(x) .origin , vt_bbv_cdr(x) .account_type , vt_bbv_cdr(x) .call_type , vt_bbv_cdr(x) .calling_number , vt_bbv_cdr(x) .called_number , vt_bbv_cdr(x) .duration , vt_bbv_cdr(x) .duration_millis , vt_bbv_cdr(x) .partial_cdrs , vt_bbv_cdr(x) .termination_code , vt_bbv_cdr(x) .termination_desc , vt_bbv_cdr(x) .success_flag , vt_bbv_cdr(x) .number_type ,v_btc_time ,v_btc_time); x := x + 1; vt_bbv_call_log_tab(z).sequence_nbr := v_row.call_data_id; vt_bbv_call_log_tab(z).create_date := v_row.create_date; z := z + 1; END LOOP; --- THIS IS FOR MIS PERFORMANCE TUNING--- --/* SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insert rows into MIS_BBV_CALL_DATA for call type 2'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; --*/ --- THIS IS FOR MIS PERFORMANCE TUNING--- /* SELECT SYSDATE INTO vt_bbv_logs(0).start_time from dual; for v_i in 0..vt_bbv_call_log_tab.last loop update mis_bbv_call_log set processed_flag = 'Y', update_date = sysdate WHERE SEQUENCE_NBR = vt_bbv_call_log_tab(v_i).SEQUENCE_NBR and create_date = vt_bbv_call_log_tab(v_i).create_date; end loop; --- THIS IS FOR MIS PERFORMANCE TUNING--- --/* SELECT SYSDATE INTO vt_bbv_logs(0).end_time from dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Updating processed flag in mis_bbv_call_log for call type 2'; vt_bbv_logs(0).duration := vt_bbv_logs(0).end_time - vt_bbv_logs(0).start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date from dual; insert into mis_bbv_time_log values vt_bbv_logs(0); --*/ o_result := 1; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbfl_error.raise_general(v_pk_name || '.' || v_sp_name); o_result := 0; END mis_bbv_sp_pop_call_data_type2; ------------------------------------------------------------------------------------- PROCEDURE mis_bbv_sp_pop_calldata_type41(o_result OUT NUMBER) IS v_start_time DATE; v_sp_name VARCHAR2(100) := 'mis_bbv_sp_pop_calldata_type41'; v_btc_time DATE; TYPE t_bbv_call_data IS RECORD( call_data_id mis_bbv_call_data.call_data_id%TYPE ,call_date mis_bbv_call_data.call_date%TYPE ,customer_id mis_bbv_call_data.customer_id%TYPE ,user_name mis_bbv_call_data.user_name%TYPE ,origin mis_bbv_call_data.origin%TYPE ,account_type mis_bbv_call_data.account_type%TYPE ,call_type mis_bbv_call_data.call_type%TYPE ,calling_number mis_bbv_call_data.calling_number%TYPE ,called_number mis_bbv_call_data.called_number%TYPE ,duration mis_bbv_call_data.duration%TYPE ,duration_millis mis_bbv_call_data.duration_millis%TYPE ,partial_cdrs mis_bbv_call_data.partial_cdrs%TYPE ,termination_code mis_bbv_call_data.termination_code%TYPE ,termination_desc mis_bbv_call_data.termination_desc%TYPE ,success_flag mis_bbv_call_data.success_flag%TYPE ,number_type mis_bbv_call_data.number_type%TYPE ,create_date mis_bbv_call_data.create_date%TYPE ,update_date mis_bbv_call_data.update_date%TYPE); TYPE t_bbv_cdr_tab IS TABLE OF t_bbv_call_data INDEX BY PLS_INTEGER; vt_bbv_cdr t_bbv_cdr_tab; long_dur_cdr t_bbv_cdr_tab; TYPE t_bbv_call_log_update IS RECORD( sequence_nbr mis_bbv_call_log.sequence_nbr%TYPE ,create_date mis_bbv_call_log.create_date%TYPE); TYPE t_bbv_call_log_tab IS TABLE OF t_bbv_call_log_update INDEX BY PLS_INTEGER; vt_bbv_call_log_tab t_bbv_call_log_tab; CURSOR c_get_bbv_call_data_calltype41 IS SELECT a.sequence_nbr AS call_data_id ,a.starttime AS call_date ,b.customer_id ,b.email_id ,b.origin ,b.account_type ,a.call_type ,a.cli AS calling_number ,a.destinationnumber AS called_number ,a.originalto AS originalto ,a.durationmillis AS duration_millis ,a.siprtc AS termination_code ,a.endreason AS termination_desc ,a.partialrecordreason ,a.chgid ,a.facilitiesused ,a.create_date FROM mis_bbv_call_log_bbv a , -- r1oss_bbv_account_summary b -- Changed for live bugs 2515 and (SELECT customer_id ,email_id ,origin ,account_type ,line1 ,line2 ,status ,call_plan ,btbb_status FROM r1oss_bbv_account_summary WHERE account_type IN ('BBV_CON', 'BBV_CON_EOI') -- to consider only consumer customers, as MIS collects only consumer data. /* updated for R22 MIS changes start */ AND TRIM(status) NOT IN ('config_failed', 'order_failed', 'ceased')) b -- to prevent duplicate numbers - BUG 2542 /* updated for R22 MIS changes end */ WHERE a.call_group = 'BTR_BBV' AND (a.partialsequencenbr IS NULL) -- OR lower(A.PARTIALRECORDREASON) = 'lastcdr') AND a.call_type = 4 AND ((a.destinationnumber IS NOT NULL) AND (concat('0044' ,substr(a.destinationnumber ,-10 ,length(a.destinationnumber))) = b.line1)); -- FOR UPDATE OF a.processed_flag; CURSOR c_get_bbv_longdur_calltype41(v_chgid IN VARCHAR2, v_call_type IN NUMBER) IS SELECT a.sequence_nbr AS call_data_id ,a.starttime AS call_date ,b.customer_id ,b.email_id ,b.origin ,b.account_type ,a.call_type ,a.cli AS calling_number ,a.destinationnumber AS called_number ,a.originalto AS originalto ,a.durationmillis AS duration_millis ,a.siprtc AS termination_code ,a.endreason AS termination_desc ,a.facilitiesused ,a.partialsequencenbr ,a.partialrecordreason ,a.create_date FROM mis_bbv_call_log_bbv a , -- r1oss_bbv_account_summary b -- Changed for live bugs 2515 and (SELECT customer_id ,email_id ,origin ,account_type ,line1 ,line2 ,status ,call_plan ,btbb_status FROM r1oss_bbv_account_summary WHERE account_type IN ('BBV_CON', 'BBV_CON_EOI') -- to consider only consumer customers, as MIS collects only consumer data. /* updated for R22 MIS changes start */ AND TRIM(status) NOT IN ('config_failed', 'order_failed', 'ceased')) b -- to prevent duplicate numbers - BUG 2542 /* updated for R22 MIS changes end */ WHERE a.call_group = 'BTR_BBV' AND a.chgid = v_chgid AND a.call_type = v_call_type AND a.partialsequencenbr IS NOT NULL AND a.call_type = 4 AND ((a.destinationnumber IS NOT NULL) AND (concat('0044' ,substr(a.destinationnumber ,-10 ,length(a.destinationnumber))) = b.line1)) ORDER BY a.partialsequencenbr; -- FOR UPDATE OF a.processed_flag; CURSOR bst_time IS SELECT bst_start_date AS bst_start_date ,bst_end_date AS bst_end_date FROM mis_bst_time; BEGIN SELECT SYSDATE INTO v_start_time FROM dual; x := 0; z := 0; vt_bbv_call_log_tab(z).sequence_nbr := NULL; vt_bbv_call_log_tab(z).create_date := NULL; --- THIS IS FOR MIS PERFORMANCE TUNING--- --/* SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; --*/ SELECT SYSDATE INTO v_btc_time FROM dual; FOR v_row IN c_get_bbv_call_data_calltype41 LOOP IF v_row.calling_number LIKE 'sip%' OR v_row.called_number LIKE 'sip%' THEN dbfl_error.raise_error('W' ,'Invalid format for the calling/called number - ' || v_row.calling_number || ' -> ' || v_row.called_number || ' for call_data_id :' || v_row.call_data_id ,v_pk_name || '- c_get_bbv_call_data_calltype41'); END IF; IF lower(v_row.partialrecordreason) = 'lastcdr' THEN long_dur_cdr(0).duration := 0; long_dur_cdr(0).duration_millis := 0; y := 1; FOR v_row1 IN c_get_bbv_longdur_calltype41(v_row.chgid ,v_row.call_type) LOOP IF v_row1.calling_number LIKE 'sip%' OR v_row1.called_number LIKE 'sip%' THEN dbfl_error.raise_error('W' ,'Invalid format for the calling/called number - ' || v_row1.calling_number || ' -> ' || v_row1.called_number || ' for call_data_id :' || v_row1.call_data_id ,v_pk_name || '- c_get_bbv_longdur_calltype41'); END IF; IF v_row1.partialsequencenbr = 1 THEN long_dur_cdr(0).call_data_id := v_row1.call_data_id; long_dur_cdr(0).call_date := v_row1.call_date; long_dur_cdr(0).customer_id := v_row1.customer_id; long_dur_cdr(0).user_name := v_row1.email_id; long_dur_cdr(0).origin := v_row1.origin; long_dur_cdr(0).account_type := v_row1.account_type; long_dur_cdr(0).call_type := 2; long_dur_cdr(0).calling_number := v_row1.calling_number; long_dur_cdr(0).called_number := v_row1.called_number; long_dur_cdr(0).number_type := mis_bbv_fn_get_number_type(long_dur_cdr(0) .called_number ,v_row1.facilitiesused); END IF; long_dur_cdr(0).duration_millis := long_dur_cdr(0) .duration_millis + mis_bbv_fn_get_duration_millis(v_row1.duration_millis); IF lower(v_row1.partialrecordreason) = 'lastcdr' THEN long_dur_cdr(0).termination_code := v_row1.termination_code; long_dur_cdr(0).termination_desc := v_row1.termination_desc; long_dur_cdr(0).partial_cdrs := y; END IF; y := y + 1; vt_bbv_call_log_tab(z).sequence_nbr := v_row1.call_data_id; vt_bbv_call_log_tab(z).create_date := v_row1.create_date; z := z + 1; END LOOP; long_dur_cdr(0).duration := round(long_dur_cdr(0) .duration_millis / 1000); vt_bbv_cdr(x) := long_dur_cdr(0); ELSE vt_bbv_cdr(x).duration_millis := 0; vt_bbv_cdr(x).duration := 0; vt_bbv_cdr(x).call_data_id := v_row.call_data_id; vt_bbv_cdr(x).call_date := v_row.call_date; vt_bbv_cdr(x).customer_id := v_row.customer_id; vt_bbv_cdr(x).user_name := v_row.email_id; vt_bbv_cdr(x).origin := v_row.origin; vt_bbv_cdr(x).account_type := v_row.account_type; vt_bbv_cdr(x).call_type := 2; vt_bbv_cdr(x).calling_number := v_row.calling_number; vt_bbv_cdr(x).called_number := v_row.called_number; vt_bbv_cdr(x).duration_millis := mis_bbv_fn_get_duration_millis(v_row.duration_millis); vt_bbv_cdr(x).duration := round(vt_bbv_cdr(x) .duration_millis / 1000); vt_bbv_cdr(x).partial_cdrs := 0; vt_bbv_cdr(x).termination_code := v_row.termination_code; vt_bbv_cdr(x).termination_desc := v_row.termination_desc; vt_bbv_cdr(x).number_type := mis_bbv_fn_get_number_type(vt_bbv_cdr(x) .called_number ,v_row.facilitiesused); END IF; IF vt_bbv_cdr(x) .termination_code = 200 OR vt_bbv_cdr(x) .duration_millis > 0 THEN vt_bbv_cdr(x).success_flag := 'Y'; ELSE vt_bbv_cdr(x).success_flag := 'N'; END IF; FOR v_row IN bst_time LOOP IF vt_bbv_cdr(x) .call_date >= v_row.bst_start_date AND vt_bbv_cdr(x) .call_date <= v_row.bst_end_date THEN vt_bbv_cdr(x).call_date := to_date(to_char(vt_bbv_cdr(x) .call_date + (1 / 24) ,'dd-mm-yyyy hh:mi:ss') ,'dd-mm-yyyy hh24:mi:ss'); END IF; IF v_btc_time >= v_row.bst_start_date AND v_btc_time <= v_row.bst_end_date THEN v_btc_time := to_date(to_char(v_btc_time + (1 / 24) ,'dd-mm-yyyy hh:mi:ss') ,'dd-mm-yyyy hh24:mi:ss'); END IF; END LOOP; INSERT INTO mis_bbv_call_data (call_data_id ,call_date ,customer_id ,user_name ,origin ,account_type ,call_type ,calling_number ,called_number ,duration ,duration_millis ,partial_cdrs ,termination_code ,termination_desc ,success_flag ,number_type ,create_date ,update_date) VALUES (vt_bbv_cdr(x) .call_data_id , vt_bbv_cdr(x) .call_date , vt_bbv_cdr(x) .customer_id , vt_bbv_cdr(x) .user_name , vt_bbv_cdr(x) .origin , vt_bbv_cdr(x) .account_type , vt_bbv_cdr(x) .call_type , vt_bbv_cdr(x) .calling_number , vt_bbv_cdr(x) .called_number , vt_bbv_cdr(x) .duration , vt_bbv_cdr(x) .duration_millis , vt_bbv_cdr(x) .partial_cdrs , vt_bbv_cdr(x) .termination_code , vt_bbv_cdr(x) .termination_desc , vt_bbv_cdr(x) .success_flag , vt_bbv_cdr(x) .number_type ,v_btc_time ,v_btc_time); x := x + 1; vt_bbv_call_log_tab(z).sequence_nbr := v_row.call_data_id; vt_bbv_call_log_tab(z).create_date := v_row.create_date; z := z + 1; END LOOP; --- THIS IS FOR MIS PERFORMANCE TUNING--- --/* SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insert rows into MIS_BBV_CALL_DATA for call type 4 - part1'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; --*/ --- THIS IS FOR MIS PERFORMANCE TUNING--- /* SELECT SYSDATE INTO vt_bbv_logs(0).start_time from dual; for v_i in 0..vt_bbv_call_log_tab.last loop update mis_bbv_call_log set processed_flag = 'Y', update_date = sysdate WHERE SEQUENCE_NBR = vt_bbv_call_log_tab(v_i).SEQUENCE_NBR and create_date = vt_bbv_call_log_tab(v_i).create_date; end loop; --- THIS IS FOR MIS PERFORMANCE TUNING--- --/* SELECT SYSDATE INTO vt_bbv_logs(0).end_time from dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Updating processed flag in mis_bbv_call_log for call type 4 - part1'; vt_bbv_logs(0).duration := vt_bbv_logs(0).end_time - vt_bbv_logs(0).start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date from dual; insert into mis_bbv_time_log values vt_bbv_logs(0); --*/ o_result := 1; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbfl_error.raise_general(v_pk_name || '.' || v_sp_name); o_result := 0; END mis_bbv_sp_pop_calldata_type41; ------------------------------------------------------------------------------------- PROCEDURE mis_bbv_sp_pop_calldata_type42(o_result OUT NUMBER) IS v_start_time DATE; v_sp_name VARCHAR2(100) := 'mis_bbv_sp_pop_calldata_type42'; v_btc_time DATE; TYPE t_bbv_call_data IS RECORD( call_data_id mis_bbv_call_data.call_data_id%TYPE ,call_date mis_bbv_call_data.call_date%TYPE ,customer_id mis_bbv_call_data.customer_id%TYPE ,user_name mis_bbv_call_data.user_name%TYPE ,origin mis_bbv_call_data.origin%TYPE ,account_type mis_bbv_call_data.account_type%TYPE ,call_type mis_bbv_call_data.call_type%TYPE ,calling_number mis_bbv_call_data.calling_number%TYPE ,called_number mis_bbv_call_data.called_number%TYPE ,duration mis_bbv_call_data.duration%TYPE ,duration_millis mis_bbv_call_data.duration_millis%TYPE ,partial_cdrs mis_bbv_call_data.partial_cdrs%TYPE ,termination_code mis_bbv_call_data.termination_code%TYPE ,termination_desc mis_bbv_call_data.termination_desc%TYPE ,success_flag mis_bbv_call_data.success_flag%TYPE ,number_type mis_bbv_call_data.number_type%TYPE ,create_date mis_bbv_call_data.create_date%TYPE ,update_date mis_bbv_call_data.update_date%TYPE); TYPE t_bbv_cdr_tab IS TABLE OF t_bbv_call_data INDEX BY PLS_INTEGER; vt_bbv_cdr t_bbv_cdr_tab; long_dur_cdr t_bbv_cdr_tab; TYPE t_bbv_call_log_update IS RECORD( sequence_nbr mis_bbv_call_log.sequence_nbr%TYPE ,create_date mis_bbv_call_log.create_date%TYPE); TYPE t_bbv_call_log_tab IS TABLE OF t_bbv_call_log_update INDEX BY PLS_INTEGER; vt_bbv_call_log_tab t_bbv_call_log_tab; -- Following code is added as per suggestion of Ranga for better performance------ CURSOR c_get_bbv_call_data_calltype42 IS SELECT a.sequence_nbr AS call_data_id ,a.starttime AS call_date ,b.customer_id ,b.email_id ,b.origin ,b.account_type ,a.call_type ,a.cli AS calling_number ,a.destinationnumber AS called_number ,a.originalto AS originalto ,a.durationmillis AS duration_millis ,a.siprtc AS termination_code ,a.endreason AS termination_desc ,a.partialrecordreason ,a.chgid ,a.facilitiesused ,a.create_date FROM mis_bbv_call_log_bbv a ,(SELECT customer_id ,email_id ,origin ,account_type ,line1 ,line2 ,status ,call_plan ,btbb_status FROM r1oss_bbv_account_summary WHERE account_type IN ('BBV_CON', 'BBV_CON_EOI') /* updated for R22 MIS changes start */ AND TRIM(status) NOT IN ('config_failed', 'order_failed', 'ceased')) b -- to prevent duplicate numbers - BUG 2542 /* updated for R22 MIS changes end */ WHERE a.call_group = 'BTR_BBV' AND a.partialsequencenbr IS NULL AND a.call_type = 4 AND NOT EXISTS (SELECT c.line1 FROM r1oss_bbv_account_summary c WHERE c.line1 = concat('0044' ,substr(a.destinationnumber ,-10))) AND (concat('0044' ,substr(a.cli ,-10)) IN (b.line1)); --- Addition ends here ----------------- /** -- Following code is commented as per suggestion of Ranga for better performance------ CURSOR c_get_bbv_call_data_calltype42 IS SELECT a.sequence_nbr as call_data_id, a.startTime as call_date, b.customer_id, b.email_id, b.origin, b.account_type, a.call_type, a.cli as calling_number, a.destinationnumber as called_number, a.ORIGINALTO as ORIGINALTO, a.durationMillis as duration_millis, a.SIPRTC as termination_code, a.endreason as termination_desc, a.PARTIALRECORDREASON, a.chgid, a.facilitiesused, a.create_date from mis_bbv_call_log_bbv a, -- r1oss_bbv_account_summary b -- Changed for live bugs 2515 and ( SELECT CUSTOMER_ID,EMAIL_ID,ORIGIN,ACCOUNT_TYPE,LINE1,LINE2,STATUS,CALL_PLAN,BTBB_STATUS FROM R1OSS_BBV_ACCOUNT_SUMMARY WHERE ACCOUNT_TYPE in ('BBV_CON','BBV_CON_EOI') -- to consider only consumer customers, as MIS collects only consumer data. AND trim(STATUS) not in ('config_failed','order_failed')) b -- to prevent duplicate numbers - BUG 2542 WHERE a.CALL_GROUP = 'BTR_BBV' AND (A.PARTIALSEQUENCENBR IS NULL) -- OR lower(A.PARTIALRECORDREASON) = 'lastcdr') and a.call_type =4 and ((concat ('0044',substr (a.destinationnumber,-10,length(a.destinationnumber))) NOT IN (SELECT distinct LINE1 FROM r1oss_bbv_account_summary where line1 is not null --UNION SELECT distinct LINE2 FROM r1oss_bbv_account_summary where line2 is not null )) and (concat ('0044',substr (a.cli,-10,length(a.cli))) IN (b.line1))); -- FOR UPDATE OF a.processed_flag; **/ -- Comment ends here CURSOR c_get_bbv_longdur_calltype42(v_chgid IN VARCHAR2, v_call_type IN NUMBER) IS SELECT a.sequence_nbr AS call_data_id ,a.starttime AS call_date ,b.customer_id ,b.email_id ,b.origin ,b.account_type ,a.call_type ,a.cli AS calling_number ,a.destinationnumber AS called_number ,a.originalto AS originalto ,a.durationmillis AS duration_millis ,a.siprtc AS termination_code ,a.endreason AS termination_desc ,a.facilitiesused ,a.partialsequencenbr ,a.partialrecordreason ,a.create_date FROM mis_bbv_call_log_bbv a , -- r1oss_bbv_account_summary b -- Changed for live bugs 2515 and (SELECT customer_id ,email_id ,origin ,account_type ,line1 ,line2 ,status ,call_plan ,btbb_status FROM r1oss_bbv_account_summary WHERE account_type IN ('BBV_CON', 'BBV_CON_EOI') -- to consider only consumer customers, as MIS collects only consumer data. /* updated for R22 MIS changes start */ AND TRIM(status) NOT IN ('config_failed', 'order_failed', 'ceased')) b -- to prevent duplicate numbers - BUG 2542 /* updated for R22 MIS changes end */ WHERE a.processed_flag = 'N' AND a.call_group = 'BTR_BBV' AND a.chgid = v_chgid AND a.call_type = v_call_type AND a.partialsequencenbr IS NOT NULL AND a.call_type = 4 AND ((concat('0044' ,substr(a.destinationnumber ,-10 ,length(a.destinationnumber))) NOT IN (SELECT DISTINCT line1 FROM r1oss_bbv_account_summary WHERE line1 IS NOT NULL)) AND (concat('0044' ,substr(a.cli ,-10 ,length(a.cli))) IN (b.line1, b.line2))) ORDER BY a.partialsequencenbr; -- FOR UPDATE OF a.processed_flag; CURSOR bst_time IS SELECT bst_start_date AS bst_start_date ,bst_end_date AS bst_end_date FROM mis_bst_time; BEGIN SELECT SYSDATE INTO v_start_time FROM dual; x := 0; z := 0; vt_bbv_call_log_tab(z).sequence_nbr := NULL; vt_bbv_call_log_tab(z).create_date := NULL; --- THIS IS FOR MIS PERFORMANCE TUNING--- --/* SELECT SYSDATE INTO vt_bbv_logs(0).start_time FROM dual; --*/ SELECT SYSDATE INTO v_btc_time FROM dual; FOR v_row IN c_get_bbv_call_data_calltype42 LOOP IF v_row.calling_number LIKE 'sip%' OR v_row.called_number LIKE 'sip%' THEN dbfl_error.raise_error('W' ,'Invalid format for the calling/called number - ' || v_row.calling_number || ' -> ' || v_row.called_number || ' for call_data_id :' || v_row.call_data_id ,v_pk_name || '- c_get_bbv_call_data_calltype42'); END IF; IF lower(v_row.partialrecordreason) = 'lastcdr' THEN long_dur_cdr(0).duration := 0; long_dur_cdr(0).duration_millis := 0; y := 1; FOR v_row1 IN c_get_bbv_longdur_calltype42(v_row.chgid ,v_row.call_type) LOOP IF v_row1.calling_number LIKE 'sip%' OR v_row1.called_number LIKE 'sip%' THEN dbfl_error.raise_error('W' ,'Invalid format for the calling/called number - ' || v_row1.calling_number || ' -> ' || v_row1.called_number || ' for call_data_id :' || v_row1.call_data_id ,v_pk_name || '- c_get_bbv_longdur_calltype42'); END IF; IF v_row1.partialsequencenbr = 1 THEN long_dur_cdr(0).call_data_id := v_row1.call_data_id; long_dur_cdr(0).call_date := v_row1.call_date; long_dur_cdr(0).customer_id := v_row1.customer_id; long_dur_cdr(0).user_name := v_row1.email_id; long_dur_cdr(0).origin := v_row1.origin; long_dur_cdr(0).account_type := v_row1.account_type; long_dur_cdr(0).call_type := 1; long_dur_cdr(0).calling_number := v_row1.calling_number; long_dur_cdr(0).called_number := v_row1.called_number; IF v_row1.called_number = 'msml' THEN pos1 := instr(TRIM(v_row1.originalto) ,':' ,1 ,1); pos2 := instr(TRIM(v_row1.originalto) ,'@' ,1 ,1); long_dur_cdr(0).called_number := substr(TRIM(v_row1.originalto) ,pos1 + 1 ,pos2 - pos1 - 1); END IF; long_dur_cdr(0).number_type := mis_bbv_fn_get_number_type(long_dur_cdr(0) .called_number ,v_row1.facilitiesused); END IF; long_dur_cdr(0).duration_millis := long_dur_cdr(0) .duration_millis + mis_bbv_fn_get_duration_millis(v_row1.duration_millis); IF lower(v_row1.partialrecordreason) = 'lastcdr' THEN long_dur_cdr(0).termination_code := v_row1.termination_code; long_dur_cdr(0).termination_desc := v_row1.termination_desc; long_dur_cdr(0).partial_cdrs := y; END IF; y := y + 1; vt_bbv_call_log_tab(z).sequence_nbr := v_row1.call_data_id; vt_bbv_call_log_tab(z).create_date := v_row1.create_date; z := z + 1; END LOOP; long_dur_cdr(0).duration := round(long_dur_cdr(0) .duration_millis / 1000); vt_bbv_cdr(x) := long_dur_cdr(0); ELSE vt_bbv_cdr(x).duration_millis := 0; vt_bbv_cdr(x).duration := 0; vt_bbv_cdr(x).call_data_id := v_row.call_data_id; vt_bbv_cdr(x).call_date := v_row.call_date; vt_bbv_cdr(x).customer_id := v_row.customer_id; vt_bbv_cdr(x).user_name := v_row.email_id; vt_bbv_cdr(x).origin := v_row.origin; vt_bbv_cdr(x).account_type := v_row.account_type; vt_bbv_cdr(x).call_type := 1; vt_bbv_cdr(x).calling_number := v_row.calling_number; vt_bbv_cdr(x).called_number := v_row.called_number; IF v_row.called_number = 'msml' THEN pos1 := instr(TRIM(v_row.originalto) ,':' ,1 ,1); pos2 := instr(TRIM(v_row.originalto) ,'@' ,1 ,1); vt_bbv_cdr(x).called_number := substr(TRIM(v_row.originalto) ,pos1 + 1 ,pos2 - pos1 - 1); END IF; vt_bbv_cdr(x).duration_millis := mis_bbv_fn_get_duration_millis(v_row.duration_millis); vt_bbv_cdr(x).duration := round(vt_bbv_cdr(x) .duration_millis / 1000); vt_bbv_cdr(x).partial_cdrs := 0; vt_bbv_cdr(x).termination_code := v_row.termination_code; vt_bbv_cdr(x).termination_desc := v_row.termination_desc; vt_bbv_cdr(x).number_type := mis_bbv_fn_get_number_type(vt_bbv_cdr(x) .called_number ,v_row.facilitiesused); END IF; IF vt_bbv_cdr(x) .termination_code = 200 OR vt_bbv_cdr(x) .duration_millis > 0 THEN vt_bbv_cdr(x).success_flag := 'Y'; ELSE vt_bbv_cdr(x).success_flag := 'N'; END IF; FOR v_row IN bst_time LOOP IF vt_bbv_cdr(x) .call_date >= v_row.bst_start_date AND vt_bbv_cdr(x) .call_date <= v_row.bst_end_date THEN vt_bbv_cdr(x).call_date := to_date(to_char(vt_bbv_cdr(x) .call_date + (1 / 24) ,'dd-mm-yyyy hh:mi:ss') ,'dd-mm-yyyy hh24:mi:ss'); END IF; IF v_btc_time >= v_row.bst_start_date AND v_btc_time <= v_row.bst_end_date THEN v_btc_time := to_date(to_char(v_btc_time + (1 / 24) ,'dd-mm-yyyy hh:mi:ss') ,'dd-mm-yyyy hh24:mi:ss'); END IF; END LOOP; INSERT INTO mis_bbv_call_data (call_data_id ,call_date ,customer_id ,user_name ,origin ,account_type ,call_type ,calling_number ,called_number ,duration ,duration_millis ,partial_cdrs ,termination_code ,termination_desc ,success_flag ,number_type ,create_date ,update_date) VALUES (vt_bbv_cdr(x) .call_data_id , vt_bbv_cdr(x) .call_date , vt_bbv_cdr(x) .customer_id , vt_bbv_cdr(x) .user_name , vt_bbv_cdr(x) .origin , vt_bbv_cdr(x) .account_type , vt_bbv_cdr(x) .call_type , vt_bbv_cdr(x) .calling_number , vt_bbv_cdr(x) .called_number , vt_bbv_cdr(x) .duration , vt_bbv_cdr(x) .duration_millis , vt_bbv_cdr(x) .partial_cdrs , vt_bbv_cdr(x) .termination_code , vt_bbv_cdr(x) .termination_desc , vt_bbv_cdr(x) .success_flag , vt_bbv_cdr(x) .number_type ,v_btc_time ,v_btc_time); x := x + 1; vt_bbv_call_log_tab(z).sequence_nbr := v_row.call_data_id; vt_bbv_call_log_tab(z).create_date := v_row.create_date; z := z + 1; END LOOP; --- THIS IS FOR MIS PERFORMANCE TUNING--- --/* SELECT SYSDATE INTO vt_bbv_logs(0).end_time FROM dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Insert rows into MIS_BBV_CALL_DATA for call type 4 - part2'; vt_bbv_logs(0).duration := vt_bbv_logs(0) .end_time - vt_bbv_logs(0) .start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date FROM dual; INSERT INTO mis_bbv_time_log VALUES vt_bbv_logs (0); COMMIT; --*/ --- THIS IS FOR MIS PERFORMANCE TUNING--- /* SELECT SYSDATE INTO vt_bbv_logs(0).start_time from dual; for v_i in 0..vt_bbv_call_log_tab.last loop update mis_bbv_call_log set processed_flag = 'Y', update_date = sysdate WHERE SEQUENCE_NBR = vt_bbv_call_log_tab(v_i).SEQUENCE_NBR and create_date = vt_bbv_call_log_tab(v_i).create_date; end loop; --- THIS IS FOR MIS PERFORMANCE TUNING--- --/* SELECT SYSDATE INTO vt_bbv_logs(0).end_time from dual; vt_bbv_logs(0).package_name := v_pk_name; vt_bbv_logs(0).proc_name := v_sp_name; vt_bbv_logs(0).query := 'Updating processed flag in mis_bbv_call_log for call type 4 - part 2'; vt_bbv_logs(0).duration := vt_bbv_logs(0).end_time - vt_bbv_logs(0).start_time; SELECT SYSDATE INTO vt_bbv_logs(0).update_date from dual; insert into mis_bbv_time_log values vt_bbv_logs(0); --*/ o_result := 1; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbfl_error.raise_general(v_pk_name || '.' || v_sp_name); o_result := 0; END mis_bbv_sp_pop_calldata_type42; ------------------------------------------------------------------------------------- END mis_bbv_pkg_import;