DELIMITER $$ USE `signalmash_prod2`$$ DROP PROCEDURE IF EXISTS `AddTariff`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `AddTariff`(var_account INT(11), var_type VARCHAR(255), var_name VARCHAR(100), var_description VARCHAR(255), int_day_num INT(11)) BEGIN DECLARE tariff_id INT(11); INSERT INTO `tariff` (`idaccount`,`type`,`name`, `description`, `day_num`) VALUES (var_account, var_type, var_name, var_description, int_day_num); SET @tariff_id=LAST_INSERT_ID(); INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES (var_account, @tariff_id, '1', 'voice', 'voice_api', 0,0,0, 'app_calling' ), (var_account, @tariff_id, '2', 'voice', 'voice_api', 0,0,0, 'sip_interface' ), (var_account, @tariff_id, '3', 'voice', 'voice_api', 0,0,0, 'secure_media' ); INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES (var_account, @tariff_id, '1', 'voice', 'inteligent', 0, 0,0, 'conference_calls' ), (var_account, @tariff_id, '2', 'voice', 'inteligent', 0, 0,0, 'call_recording' ), (var_account, @tariff_id, '3', 'voice', 'inteligent', 0, 0,0, 'call_recording_storage' ), (var_account, @tariff_id, '4', 'voice', 'inteligent', 0, 0,0, 'answering_machine_detection' ), (var_account, @tariff_id, '5', 'voice', 'inteligent', 0, 0,0, 'Speech-to-text_Transcription' ); INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES (var_account, @tariff_id, '1', 'voice', 'number', 0, 0,0, 'local_Number' ), (var_account, @tariff_id, '2', 'voice', 'number', 0, 0,0, 'toll_free_number' ); INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES (var_account, @tariff_id, '1', 'voice', 'depth_voice', 0, 0,0, 'united_states_toll_free' ), (var_account, @tariff_id, '2', 'voice', 'depth_voice', 0, 0,0, 'united_states_lower 48 (Zone 1)' ), (var_account, @tariff_id, '3', 'voice', 'depth_voice', 0, 0,0, 'united_states - Hawaii (Zone 2)808' ), (var_account, @tariff_id, '4', 'voice', 'depth_voice', 0, 0,0, 'united_states - Alaska (Zone 3)907' ), (var_account, @tariff_id, '5', 'voice', 'depth_voice',0, 0,0, 'united_state High Cost (Zone 4)' ), (var_account, @tariff_id, '6', 'voice', 'depth_voice',0, 0,0, 'united_state High Cost (Zone 5)' ), (var_account, @tariff_id, '7', 'voice', 'depth_voice',0, 0,0, 'sip_trunking' ); INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES (var_account, @tariff_id, '1', 'elastic', 'sip_trunking', 0, 0,0, 'local_calls' ), (var_account, @tariff_id, '2', 'elastic', 'sip_trunking', 0, 0,0, 'toll_free_call' ); INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES (var_account, @tariff_id, '1', 'elastic', 'inteligent', 0, 0,0, 'unlimited_call_concurrency' ), (var_account, @tariff_id, '2', 'elastic', 'inteligent', 0, 0,0, 'secure_trunking' ), (var_account, @tariff_id, '3', 'elastic', 'inteligent', 0, 0,0, 'emergency_calling' ), (var_account, @tariff_id, '4', 'elastic', 'inteligent', 0, 0,0, 'call_recording' ), (var_account, @tariff_id, '5', 'elastic', 'inteligent', 0, 0,0, 'call_recording_storage' ), (var_account, @tariff_id, '6', 'elastic', 'inteligent', 0, 0,0, 'T.38_fax_support' ); INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES (var_account, @tariff_id, '1', 'elastic', 'number', 0, 0,0, 'local_Number' ), (var_account, @tariff_id, '2', 'elastic', 'number', 0, 0,0, 'toll_free_number' ); INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES (var_account, @tariff_id, '1', 'elastic', 'depth_voice',0,0,0, 'united_states_toll_free' ), (var_account, @tariff_id, '2', 'elastic', 'depth_voice',0,0,0, 'united_states_lower 48 (Zone 1)' ), (var_account, @tariff_id, '3', 'elastic', 'depth_voice',0,0,0, 'united_states - Hawaii (Zone 2)' ), (var_account, @tariff_id, '4', 'elastic', 'depth_voice',0,0,0, 'united_states - Alaska (Zone 3)' ), (var_account, @tariff_id, '5', 'elastic', 'depth_voice',0, 0,0, 'united_state High Cost (Zone 4)' ), (var_account, @tariff_id, '6', 'elastic', 'depth_voice',0, 0,0, 'united_state High Cost (Zone 5)' ), (var_account, @tariff_id, '7', 'elastic', 'depth_voice',0, 0,0, 'sip_trunking' ); INSERT INTO `sms_rate` (`idaccount`, id_tariff, `priority`, `category`, `type`, `MO_rates`, `MT_rates`, `subcategory`, `categoryvalue`) VALUES (var_account, @tariff_id, '1' ,'10_dlc', 'unregistered',0,0, 'SMS', '10 DLC (10 digit long code)'), (var_account, @tariff_id, '2' ,'10_dlc', 'unregistered',0,0, 'MMS', '10 DLC (10 digit long code)'), (var_account, @tariff_id, '3' ,'toll_free', 'unregistered',0,0, 'SMS', 'Toll Free'), (var_account, @tariff_id, '4' ,'toll_free', 'unregistered',0,0, 'MMS', 'Toll Free'), (var_account, @tariff_id, '5' ,'short_code', 'unregistered',0,0, 'SMS', 'Standard Rate Short Code'), (var_account, @tariff_id, '6' ,'short_code', 'unregistered',0,0, 'MMS', 'Standard Rate Short Code'), (var_account, @tariff_id, '7' ,'free_to_end_user', 'unregistered',0,0, 'SMS', 'Free To End User Short Code'), (var_account, @tariff_id, '8' ,'free_to_end_user', 'unregistered',0,0, 'MMS', 'Free To End User Short Code'); INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `billing_type`, `billing_frequency`, `category`, `proration`) VALUES (var_account, @tariff_id, '1', 'message', 'message_activation', 0,0,0,0,0, 'local_number', 'true' ), (var_account, @tariff_id, '2', 'message', 'message_activation', 0,0,0,0,0, 'toll_free_number', 'true' ), (var_account, @tariff_id, '3', 'message', 'message_activation', 0,0,0,0,0, 'short_code_lease', 'true' ), (var_account, @tariff_id, '4', 'message', 'message_activation', 0,0,0,0,0, 'activate_messaging_Phone_Number', 'true' ), (var_account, @tariff_id, '5', 'message', 'message_activation', 0,0,0,0,0, 'vanity_number', 'true' ), (var_account, @tariff_id, '6', 'message', 'message_activation', 0,0,0,0,0, 'vanity_short_code', 'true' ), (var_account, @tariff_id, '7', 'message', 'message_activation', 0,0,0,0,0, 'migrated_short_code', 'true' ), (var_account, @tariff_id, '8', 'message', 'message_activation', 0,0,0,0,0, 'imported_ott_mash', 'true'); INSERT INTO `lookup_api_rates` (`idaccount`,`idtariff`,`priority`, `type`, `rate`, `category`) VALUES (var_account, @tariff_id, '1', 'lookup', 0, 'lrn' ), (var_account, @tariff_id, '2', 'lookup', 0, 'lrn_dnc' ); INSERT INTO `campaign_rates` (`idaccount`,`idtariff`,`priority`, `type`, `rate`, `mrc`, `category`) VALUES (var_account, @tariff_id, '1', 'campaign', 0, 0, 'campaign_review' ), (var_account, @tariff_id, '2', 'campaign', 0, 0, 'campaign_redundant' ); INSERT INTO `message_activation_pricing` (`idaccount`,`idtariff`,`priority`, `type`,`mrc`,`nrc`, `category`) VALUES (var_account, @tariff_id, '1', 'message_activation', 0, 0, 'local_number' ), (var_account, @tariff_id, '2', 'message_activation', 0, 0, 'toll_free_number' ), (var_account, @tariff_id, '3', 'message_activation', 0, 0, 'short_code' ); SELECT @tariff_id `idtariff`, var_name `name`; END$$ DELIMITER ;