text: CREATE DEFINER=`neon-user`@`192.168.1.25` PROCEDURE `prc_WSGenerateVersion3VosSheet`(
  	IN `p_CustomerID` INT ,
  	IN `p_trunks` varchar(200) ,
  	IN `p_Effective` VARCHAR(50),
  	IN `p_CustomDate` DATE,
  	IN `p_Format` VARCHAR(50)
  
  )
  BEGIN
  
  	-- get customer rates
  	CALL vwCustomerRate(p_CustomerID,p_Trunks,p_Effective,p_CustomDate);
  
  	IF p_Effective = 'Now' OR p_Format = 'Vos 2.0'
  	THEN
  
  		SELECT distinct
  			IFNULL(RatePrefix, '') as `Rate Prefix` ,
  			Concat(IFNULL(AreaPrefix,''), Code) as `Area Prefix` ,
  			'International' as `Rate Type` ,
  			Description  as `Area Name`,
  			Rate / 60  as `Billing Rate`,
  			IntervalN as `Billing Cycle`,
  			Rate as `Minute Cost` ,
  			'No Lock'  as `Lock Type`,
  			CASE WHEN Interval1 != IntervalN
  			THEN 
  				Concat('0,', Rate, ',',Interval1)
  			ELSE
  				''
  			END as `Section Rate`,
  			0 AS `Billing Rate for Calling Card Prompt`,
  			0  as `Billing Cycle for Calling Card Prompt`
  		FROM   tmp_customerrateall_
  		ORDER BY `Rate Prefix`;
  
  	END IF;
  
  	IF (p_Effective = 'Future' OR  p_Effective = 'All' OR p_Effective = 'CustomDate') AND p_Format = 'Vos 3.2'
  	THEN
  	
  		DROP TEMPORARY TABLE IF EXISTS tmp_customerrateall_2_ ;
  		CREATE TEMPORARY TABLE tmp_customerrateall_2_ SELECT * FROM tmp_customerrateall_;
  
  		SELECT 
  			`Time of timing replace`,
  			`Mode of timing replace`,
  			`Rate Prefix` ,
  			`Area Prefix` ,
  			`Rate Type` ,
  			`Area Name` ,
  			`Billing Rate` ,
  			`Billing Cycle`,
  			`Minute Cost` ,
  			`Lock Type` ,
  			`Section Rate` ,
  			`Billing Rate for Calling Card Prompt` ,
  			`Billing Cycle for Calling Card Prompt`
  		FROM 
  		(
  			SELECT distinct
  				CONCAT(EffectiveDate,' 00:00') as `Time of timing replace`,
  				'Append replace' as `Mode of timing replace`,
  				IFNULL(RatePrefix, '') as `Rate Prefix` ,
  				Concat(IFNULL(AreaPrefix,''), Code) as `Area Prefix` ,
  				'International' as `Rate Type` ,
  				Description  as `Area Name`,
  				Rate / 60  as `Billing Rate`,
  				IntervalN as `Billing Cycle`,
  				Rate as `Minute Cost` ,
  				'No Lock'  as `Lock Type`,
  				CASE WHEN Interval1 != IntervalN
  				THEN 
  					Concat('0,', Rate, ',',Interval1)
  				ELSE
  					''
  				END as `Section Rate`,
  				0 AS `Billing Rate for Calling Card Prompt`,
  				0  as `Billing Cycle for Calling Card Prompt`
  			FROM   tmp_customerrateall_2_
  			-- ORDER BY `Rate Prefix`;
  			
  			UNION ALL
  			
  			SELECT distinct
  				CONCAT(EffectiveDate,' 00:00') as `Time of timing replace`,
  				'Delete' as `Mode of timing replace`,
  				IFNULL(RatePrefix, '') as `Rate Prefix` ,
  				Concat(IFNULL(AreaPrefix,''), Code) as `Area Prefix` ,
  				'International' as `Rate Type` ,
  				Description  as `Area Name`,
  				Rate / 60  as `Billing Rate`,
  				IntervalN as `Billing Cycle`,
  				Rate as `Minute Cost` ,
  				'No Lock'  as `Lock Type`,
  				CASE WHEN Interval1 != IntervalN
  				THEN 
  					Concat('0,', Rate, ',',Interval1)
  				ELSE
  					''
  				END as `Section Rate`,
  				0 AS `Billing Rate for Calling Card Prompt`,
  				0  as `Billing Cycle for Calling Card Prompt`
  			FROM   tmp_customerrateall_
  			WHERE  EndDate IS NOT NULL
  			
  			UNION ALL
  			
  			SELECT distinct
  				CONCAT(EffectiveDate,' 00:00') as `Time of timing replace`,
  				'Delete' as `Mode of timing replace`,
  				IFNULL(RatePrefix, '') as `Rate Prefix` ,
  				Concat(IFNULL(AreaPrefix,''), Code) as `Area Prefix` ,
  				'International' as `Rate Type` ,
  				Description  as `Area Name`,
  				Rate / 60  as `Billing Rate`,
  				IntervalN as `Billing Cycle`,
  				Rate as `Minute Cost` ,
  				'No Lock'  as `Lock Type`,
  				CASE WHEN Interval1 != IntervalN
  				THEN 
  					Concat('0,', Rate, ',',Interval1)
  				ELSE
  					''
  				END as `Section Rate`,
  				0 AS `Billing Rate for Calling Card Prompt`,
  				0  as `Billing Cycle for Calling Card Prompt`
  			FROM   tmp_customerrateall_archive_
  		) tmp
  		ORDER BY `Rate Prefix`;
  
  	END IF;
  
  	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  END