      DROP TABLE IF EXISTS tmp_CloneRateSheetDetail_ ;
      CREATE TEMPORARY TABLE tmp_CloneRateSheetDetail_ LIKE tmp_RateSheetDetail_;
      INSERT tmp_CloneRateSheetDetail_ SELECT * FROM tmp_RateSheetDetail_;


      INSERT INTO tmp_RateSheetRate_
      SELECT tbl.RateID,
             description,
             Code,
             tbl.Interval1,
             tbl.IntervalN,
             tbl.Rate,
             v_trunkDescription_,
             'NEW' as `change`,
             tbl.EffectiveDate
      FROM   (
			SELECT
				rt.RateID,				
				rt.Interval1,
				rt.IntervalN,
				rt.Rate,
				rt.EffectiveDate
         FROM   tmp_RateTableRate_ rt
         LEFT JOIN tblRateSheet
         	ON tblRateSheet.RateSheetID =
            	v_lastRateSheetID_
         LEFT JOIN tmp_RateSheetDetail_ as  rsd
         	ON rsd.RateID = rt.RateID AND rsd.RateSheetID = v_lastRateSheetID_
			WHERE  rsd.ratesheetdetailsid IS NULL

			UNION

			SELECT
				trc2.RateID,            
            trc2.Interval1,
            trc2.IntervalN,
            trc2.Rate,
            trc2.EffectiveDate
         FROM   tmp_CustomerRates_ trc2
         LEFT JOIN tblRateSheet
         	ON tblRateSheet.RateSheetID =
         		v_lastRateSheetID_
         LEFT JOIN tmp_CloneRateSheetDetail_ as  rsd2
         	ON rsd2.RateID = trc2.RateID AND rsd2.RateSheetID = v_lastRateSheetID_
			WHERE  rsd2.ratesheetdetailsid IS NULL

			) AS tbl
      INNER JOIN tblRate
      	ON tbl.RateID = tblRate.RateID;

      INSERT INTO tmp_RateSheetRate_
      SELECT tbl.RateID,
             description,
             Code,
             tbl.Interval1,
             tbl.IntervalN,
             tbl.Rate,
             v_trunkDescription_,
             tbl.`change`,
             tbl.EffectiveDate
      FROM   (SELECT rt.RateID,
                     description,
                     tblRate.Code,
                     rt.Interval1,
                     rt.IntervalN,
                     rt.Rate,
                     rsd5.Rate AS rate2,
                     rt.EffectiveDate,
                     CASE
                                WHEN rsd5.Rate > rt.Rate
                                     AND rsd5.Destination !=
                                         description THEN
                                'NAME CHANGE & DECREASE'
                                ELSE
                                  CASE
                                    WHEN rsd5.Rate > rt.Rate
                                         AND rt.EffectiveDate <= Now() THEN
                                    'DECREASE'
                                    ELSE
                                      CASE
                                        WHEN ( rsd5.Rate >
                                               rt.Rate
                                               AND rt.EffectiveDate > Now()
                                             )
                                      THEN
                                        'PENDING DECREASE'
                                        ELSE
                                          CASE
                                            WHEN ( rsd5.Rate <
                                                   rt.Rate
                                                   AND rt.EffectiveDate <=
                                                       Now() )
                                          THEN
                                            'INCREASE'
                                            ELSE
                                              CASE
                                                WHEN ( rsd5.Rate
                                                       <
                                                       rt.Rate
                                                       AND rt.EffectiveDate >
                                                           Now() )
                                              THEN
                                                'PENDING INCREASE'
                                                ELSE
                                                  CASE
                                                    WHEN
                                              rsd5.Destination !=
                                              description THEN
                                                    'NAME CHANGE'
                                                    ELSE 'NO CHANGE'
                                                  END
                                              END
                                          END
                                      END
                                  END
                              END as `Change`
              FROM   tblRate
                     INNER JOIN tmp_RateTableRate_ rt
                             ON rt.RateID = tblRate.RateID
                     INNER JOIN tblRateSheet
                             ON tblRateSheet.RateSheetID = v_lastRateSheetID_
                     INNER JOIN tmp_RateSheetDetail_ as  rsd5
                             ON rsd5.RateID = rt.RateID
                                AND rsd5.RateSheetID =
                                    v_lastRateSheetID_
              UNION
              SELECT trc4.RateID,
                     description,
                     tblRate.Code,
                     trc4.Interval1,
                     trc4.IntervalN,
                     trc4.Rate,
                     rsd6.Rate AS rate2,
                     trc4.EffectiveDate,
                     CASE
                                WHEN rsd6.Rate > trc4.Rate
                                     AND rsd6.Destination !=
                                         description THEN
                                'NAME CHANGE & DECREASE'
                                ELSE
                                  CASE
                                    WHEN rsd6.Rate > trc4.Rate
                                         AND trc4.EffectiveDate <= Now() THEN
                                    'DECREASE'
                                    ELSE
                                      CASE
                                        WHEN ( rsd6.Rate >
                                               trc4.Rate
                                               AND trc4.EffectiveDate > Now()
                                             )
                                      THEN
                                        'PENDING DECREASE'
                                        ELSE
                                          CASE
                                            WHEN ( rsd6.Rate <
                                                   trc4.Rate
                                                   AND trc4.EffectiveDate <=
                                                       Now() )
                                          THEN
                                            'INCREASE'
                                            ELSE
                                              CASE
                                                WHEN ( rsd6.Rate
                                                       <
                                                       trc4.Rate
                                                       AND trc4.EffectiveDate >
                                                           Now() )
                                              THEN
                                                'PENDING INCREASE'
                                                ELSE
                                                  CASE
                                                    WHEN
                                              rsd6.Destination !=
                                              description THEN
                                                    'NAME CHANGE'
                                                    ELSE 'NO CHANGE'
                                                  END
                                              END
                                          END
                                      END
                                  END
                              END as  `Change`
              FROM   tblRate
                     INNER JOIN tmp_CustomerRates_ trc4
                             ON trc4.RateID = tblRate.RateID
                     INNER JOIN tblRateSheet
                             ON tblRateSheet.RateSheetID = v_lastRateSheetID_
                     INNER JOIN tmp_CloneRateSheetDetail_ as rsd6
                             ON rsd6.RateID = trc4.RateID
                                AND rsd6.RateSheetID =
                                    v_lastRateSheetID_
				  ) AS tbl ;

      INSERT INTO tmp_RateSheetRate_
      SELECT tblRateSheetDetails.RateID,
             tblRateSheetDetails.Destination,
             tblRateSheetDetails.Code,
             tblRateSheetDetails.Interval1,
             tblRateSheetDetails.IntervalN,
             tblRateSheetDetails.Rate,
             v_trunkDescription_,
             'DELETE',
             tblRateSheetDetails.EffectiveDate
      FROM   tblRate
             INNER JOIN tblRateSheetDetails
                     ON tblRate.RateID = tblRateSheetDetails.RateID
                        AND tblRateSheetDetails.RateSheetID = v_lastRateSheetID_
             LEFT JOIN (SELECT DISTINCT RateID,
                                        effectivedate
                        FROM   tmp_RateTableRate_
                        UNION
                        SELECT DISTINCT RateID,
                                        effectivedate
                        FROM tmp_CustomerRates_) AS TBL
                    ON TBL.RateID = tblRateSheetDetails.RateID
      WHERE  `change` != 'DELETE'
             AND TBL.RateID IS NULL ;
	
	DROP TEMPORARY TABLE IF EXISTS tmp_RateSheetRate4_;	
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_RateSheetRate4_ as (select * from tmp_RateSheetRate_);
	DELETE n1 FROM tmp_RateSheetRate_ n1, tmp_RateSheetRate4_ n2 WHERE n1.EffectiveDate < n2.EffectiveDate
	AND  n1.RateId = n2.RateId;

      IF v_IncludePrefix_ = 1
        THEN
            SELECT rsr.RateID AS rateid,
                 rsr.Interval1 AS interval1,
                 rsr.IntervalN AS intervaln,
                 rsr.Destination AS destination,
                 rsr.Codes AS codes,
                 v_Prefix_ AS `tech prefix`,
                 CONCAT(rsr.Interval1,'/',rsr.IntervalN) AS `interval`,
                 FORMAT(rsr.Rate,6) AS `rate per minute (usd)`,
                 rsr.`level`,
                 rsr.`change`,
                 rsr.EffectiveDate AS `effective date`
            FROM   tmp_RateSheetRate_ rsr
            WHERE rsr.Rate > 0
            ORDER BY rsr.Destination,rsr.Codes desc;
      ELSE
            SELECT rsr.RateID AS rateid ,
                           rsr.Interval1 AS interval1,
                           rsr.IntervalN AS intervaln,
                           rsr.Destination AS destination,
                           rsr.Codes AS codes,
                           CONCAT(rsr.Interval1,'/',rsr.IntervalN) AS  `interval`,
                           FORMAT(rsr.Rate, 6) AS `rate per minute (usd)`,
                           rsr.`level`,
                           rsr.`change`,
                           rsr.EffectiveDate AS `effective date`
            FROM   tmp_RateSheetRate_ rsr
            WHERE rsr.Rate > 0
          	ORDER BY rsr.Destination,rsr.Codes DESC;
        END IF; 
        
        SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGenerateRateTable` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGenerateRateTable`(
	IN `p_jobId` INT,
	IN `p_RateGeneratorId` INT,
	IN `p_RateTableId` INT,
	IN `p_rateTableName` VARCHAR(200),
	IN `p_EffectiveDate` VARCHAR(10),
	IN `p_delete_exiting_rate` INT,
	IN `p_EffectiveRate` VARCHAR(50)


)
GenerateRateTable:BEGIN    		
   
    
	DECLARE v_RTRowCount_ INT;       
	DECLARE v_RatePosition_ INT;
	DECLARE v_Use_Preference_ INT;
	DECLARE v_CurrencyID_ INT;
	DECLARE v_CompanyCurrencyID_ INT;
	DECLARE v_Average_ TINYINT;  
	DECLARE v_CompanyId_ INT;
	DECLARE v_codedeckid_ INT;
	DECLARE v_trunk_ INT; 
	DECLARE v_rateRuleId_ INT;
	DECLARE v_RateGeneratorName_ VARCHAR(200);
	DECLARE v_pointer_ INT ;
	DECLARE v_rowCount_ INT ;

	
	
	DECLARE v_tmp_code_cnt int ;
	DECLARE v_tmp_code_pointer int;
	DECLARE v_p_code varchar(50);
	DECLARE v_Codlen_ int;
	DECLARE v_p_code__ VARCHAR(50);
	DECLARE v_Commit int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN      
		   ROLLBACK;	  
		   CALL prc_WSJobStatusUpdate(p_jobId, 'F', 'RateTable generation failed', ''); 
               		   
    END; 
	
     SET @@session.collation_connection='utf8_unicode_ci';
     SET @@session.character_set_client='utf8';
     
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
   

    SET p_EffectiveDate = CAST(p_EffectiveDate AS DATE);
       
    
    	IF p_rateTableName IS NOT NULL
	 	THEN


        SET v_RTRowCount_ = (SELECT
                COUNT(*)
            FROM tblRateTable
            WHERE RateTableName = p_rateTableName
            AND CompanyId = (SELECT
                    CompanyId
                FROM tblRateGenerator
                WHERE RateGeneratorID = p_RateGeneratorId));

        IF v_RTRowCount_ > 0
        THEN
            CALL prc_WSJobStatusUpdate  (p_jobId, 'F', 'RateTable Name is already exist, Please try using another RateTable Name', '');
            LEAVE GenerateRateTable;
        END IF;
    	END IF;

		DROP TEMPORARY TABLE IF EXISTS tmp_Rates_;
		CREATE TEMPORARY TABLE tmp_Rates_  (
		  code VARCHAR(50) COLLATE utf8_unicode_ci,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates_code (`code`) ,
  	     UNIQUE KEY `unique_code` (`code`)

		);
		DROP TEMPORARY TABLE IF EXISTS tmp_Rates2_;
		CREATE TEMPORARY TABLE tmp_Rates2_  (
		  code VARCHAR(50) COLLATE utf8_unicode_ci,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates2_code (`code`)
		);


		DROP TEMPORARY TABLE IF EXISTS tmp_Codedecks_;
		CREATE TEMPORARY TABLE tmp_Codedecks_ (
        CodeDeckId INT
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Raterules_;

     	CREATE TEMPORARY TABLE tmp_Raterules_  (
        rateruleid INT,
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        RowNo INT,
        INDEX tmp_Raterules_code (`code`),
        INDEX tmp_Raterules_rateruleid (`rateruleid`),
        INDEX tmp_Raterules_RowNo (`RowNo`)
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_;
     	CREATE TEMPORARY TABLE tmp_Vendorrates_  (
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        AccountId INT,
        RowNo INT,
        PreferenceRank INT,
        INDEX tmp_Vendorrates_code (`code`),
        INDEX tmp_Vendorrates_rate (`rate`)
    	);
    	
    	
    	DROP TEMPORARY TABLE IF EXISTS tmp_VRatesstage2_;
     	CREATE TEMPORARY TABLE tmp_VRatesstage2_  (
		RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        FinalRankNumber int,
        INDEX tmp_Vendorrates_stage2__code (`RowCode`)
    	);
     DROP TEMPORARY TABLE IF EXISTS tmp_dupVRatesstage2_;
     	CREATE TEMPORARY TABLE tmp_dupVRatesstage2_  (
		RowCode VARCHAR(50)  COLLATE utf8_unicode_ci,
        FinalRankNumber int,
        INDEX tmp_dupVendorrates_stage2__code (`RowCode`)
    	);
     DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_stage3_;
     CREATE TEMPORARY TABLE tmp_Vendorrates_stage3_  (
        RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        INDEX tmp_Vendorrates_stage2__code (`RowCode`)
    	);    	
    	
     	DROP TEMPORARY TABLE IF EXISTS tmp_code_;
     	CREATE TEMPORARY TABLE tmp_code_  (
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        INDEX tmp_code_code (`code`)
    	);

     DROP TEMPORARY TABLE IF EXISTS tmp_all_code_;
  CREATE TEMPORARY TABLE tmp_all_code_ ( 
     RowCode  varchar(50) COLLATE utf8_unicode_ci,
     Code  varchar(50) COLLATE utf8_unicode_ci,
     RowNo int,
     INDEX Index2 (Code)
   );

 

      DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage_;
       CREATE TEMPORARY TABLE tmp_VendorRate_stage_ (
          RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
          AccountId INT ,
          AccountName VARCHAR(100) ,
          Code VARCHAR(50) COLLATE utf8_unicode_ci,
          Rate DECIMAL(18,6) ,
          ConnectionFee DECIMAL(18,6) ,
          EffectiveDate DATETIME ,
          Description VARCHAR(255),
          Preference INT,
          MaxMatchRank int ,
          prev_prev_RowCode VARCHAR(50),
          prev_AccountID int
        );

 DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_;
  CREATE TEMPORARY TABLE tmp_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) COLLATE utf8_unicode_ci,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50) COLLATE utf8_unicode_ci  
   );
   DROP TEMPORARY TABLE IF EXISTS tmp_final_VendorRate_;
  CREATE TEMPORARY TABLE tmp_final_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) COLLATE utf8_unicode_ci,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
     FinalRankNumber int,
		INDEX IX_CODE (RowCode)
   );
   
   DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates_;
   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates_(
			AccountId int,
            AccountName varchar(200),
			Code varchar(50) COLLATE utf8_unicode_ci,
			Description varchar(200) ,
			Rate DECIMAL(18,6) ,
               ConnectionFee DECIMAL(18,6) , 
			EffectiveDate date,
			TrunkID int,
			CountryID int,
			RateID int,
			Preference int,
			INDEX IX_CODE (Code)			
	);
	
	DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates1_;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates1_(
		AccountId int,
		AccountName varchar(200),
		Code varchar(50),
		Description varchar(200),
		Rate DECIMAL(18,6) ,
		ConnectionFee DECIMAL(18,6) , 
		EffectiveDate date,
		TrunkID int,
		CountryID int,
		RateID int,
		Preference int,
		INDEX IX_Code (Code),
		INDEX tmp_VendorCurrentRates_AccountId (`AccountId`,`TrunkID`,`RateId`,`EffectiveDate`)
	);

    	SELECT CurrencyID INTO v_CurrencyID_ FROM  tblRateGenerator WHERE RateGeneratorId = p_RateGeneratorId;

    	SELECT
        UsePreference,  
        rateposition,
        companyid ,
        CodeDeckId,
        tblRateGenerator.TrunkID,
        tblRateGenerator.UseAverage  ,
        tblRateGenerator.RateGeneratorName INTO v_Use_Preference_, v_RatePosition_, v_CompanyId_, v_codedeckid_, v_trunk_, v_Average_, v_RateGeneratorName_
    	FROM tblRateGenerator
    	WHERE RateGeneratorId = p_RateGeneratorId;


	SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = v_CompanyId_;


      

    	INSERT INTO tmp_Raterules_
	   SELECT
            rateruleid,
            tblRateRule.code,
            @row_num := @row_num+1 AS RowID
        FROM tblRateRule,(SELECT @row_num := 0) x
        WHERE rategeneratorid = p_RateGeneratorId
        ORDER BY tblRateRule.Code DESC;

	     INSERT INTO tmp_Codedecks_
	        SELECT DISTINCT
	            tblVendorTrunk.CodeDeckId
	        FROM tblRateRule
	        INNER JOIN tblRateRuleSource
	            ON tblRateRule.RateRuleId = tblRateRuleSource.RateRuleId
	        INNER JOIN tblAccount
	            ON tblAccount.AccountID = tblRateRuleSource.AccountId and tblAccount.IsVendor = 1
			JOIN tblVendorTrunk
	                ON tblAccount.AccountId = tblVendorTrunk.AccountID
	                AND  tblVendorTrunk.TrunkID = v_trunk_
	                AND tblVendorTrunk.Status = 1
	        WHERE RateGeneratorId = p_RateGeneratorId;

	    SET v_pointer_ = 1;
	    SET v_rowCount_ = (SELECT COUNT(distinct Code ) FROM tmp_Raterules_);

		
		
		
		insert into tmp_code_
          SELECT  DISTINCT LEFT(f.Code, x.RowNo) as loopCode FROM (
          SELECT @RowNo  := @RowNo + 1 as RowNo
          FROM mysql.help_category 
          ,(SELECT @RowNo := 0 ) x
          limit 15
          ) x
          INNER JOIN 
          (SELECT 
                    distinct
	                tblRate.code
	            FROM tblRate
	            JOIN tmp_Raterules_ rr
	                ON tblRate.code LIKE (REPLACE(rr.code,
	                '*', '%%'))
					where  tblRate.CodeDeckId = v_codedeckid_    	                
	          Order by tblRate.code 
          ) as f
          ON   x.RowNo   <= LENGTH(f.Code) 
          order by loopCode   desc;

 
  
	
          
		SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = v_CompanyId_;
		SET @IncludeAccountIds = (SELECT GROUP_CONCAT(AccountId) from tblRateRule rr inner join  tblRateRuleSource rrs on rr.RateRuleId = rrs.RateRuleId where rr.RateGeneratorId = p_RateGeneratorId ) ;
     
	
	
	  
	 INSERT INTO tmp_VendorCurrentRates1_ 
	 Select DISTINCT AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference					
      FROM (
				SELECT  tblVendorRate.AccountId,tblAccount.AccountName, tblRate.Code, tblRate.Description, 
				 CASE WHEN  tblAccount.CurrencyId = v_CurrencyID_
								THEN
									   tblVendorRate.Rate
					 WHEN  v_CompanyCurrencyID_ = v_CurrencyID_  
							 THEN
								  (
								   ( tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ) )
								 )
							  ELSE 
							  (
										
								  (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = v_CurrencyID_ and  CompanyID = v_CompanyId_ )
									* (tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ))
							  )
							 END
								as  Rate,
								 ConnectionFee,
			  DATE_FORMAT (tblVendorRate.EffectiveDate, '%Y-%m-%d') AS EffectiveDate, 
			    tblVendorRate.TrunkID, tblRate.CountryID, tblRate.RateID,IFNULL(vp.Preference, 5) AS Preference,
				@row_num := IF(@prev_AccountId = tblVendorRate.AccountID AND @prev_TrunkID = tblVendorRate.TrunkID AND @prev_RateId = tblVendorRate.RateID AND @prev_EffectiveDate >= tblVendorRate.EffectiveDate, @row_num + 1, 1) AS RowID,
				@prev_AccountId := tblVendorRate.AccountID,
				@prev_TrunkID := tblVendorRate.TrunkID,
				@prev_RateId := tblVendorRate.RateID,
				@prev_EffectiveDate := tblVendorRate.EffectiveDate
			  FROM      tblVendorRate
			  
					 Inner join tblVendorTrunk vt on vt.CompanyID = v_CompanyId_ AND vt.AccountID = tblVendorRate.AccountID and 
							
							  vt.Status =  1 and vt.TrunkID =  v_trunk_  
                              inner join tmp_Codedecks_ tcd on vt.CodeDeckId = tcd.CodeDeckId
						INNER JOIN tblAccount   ON  tblAccount.CompanyID = v_CompanyId_ AND tblVendorRate.AccountId = tblAccount.AccountID and tblAccount.IsVendor = 1
						INNER JOIN tblRate ON tblRate.CompanyID = v_CompanyId_  AND tblRate.CodeDeckId = vt.CodeDeckId  AND    tblVendorRate.RateId = tblRate.RateID
						INNER JOIN tmp_code_ tcode ON tcode.Code  = tblRate.Code 
						LEFT JOIN tblVendorPreference vp
										 ON vp.AccountId = tblVendorRate.AccountId
										 AND vp.TrunkID = tblVendorRate.TrunkID
										 AND vp.RateId = tblVendorRate.RateId
						LEFT OUTER JOIN tblVendorBlocking AS blockCode   ON tblVendorRate.RateId = blockCode.RateId
																	  AND tblVendorRate.AccountId = blockCode.AccountId
																	  AND tblVendorRate.TrunkID = blockCode.TrunkID
						LEFT OUTER JOIN tblVendorBlocking AS blockCountry    ON tblRate.CountryID = blockCountry.CountryId
																	  AND tblVendorRate.AccountId = blockCountry.AccountId
																	  AND tblVendorRate.TrunkID = blockCountry.TrunkID
						
						,(SELECT @row_num := 1,  @prev_AccountId := '',@prev_TrunkID := '', @prev_RateId := '', @prev_EffectiveDate := '') x
					 
			  WHERE      
						(
					  		(p_EffectiveRate = 'now' AND EffectiveDate <= NOW()) 
								OR 
							(p_EffectiveRate = 'future' AND EffectiveDate > NOW())
							  	OR 
							(p_EffectiveRate = 'effective' AND EffectiveDate <= p_EffectiveDate)
						)
						AND tblAccount.IsVendor = 1
						AND tblAccount.Status = 1
						AND tblAccount.CurrencyId is not NULL
						AND tblVendorRate.TrunkID = v_trunk_
						AND blockCode.RateId IS NULL
					   AND blockCountry.CountryId IS NULL
						AND ( @IncludeAccountIds = NULL
							  OR ( @IncludeAccountIds IS NOT NULL
								   AND FIND_IN_SET(tblVendorRate.AccountId,@IncludeAccountIds) > 0                    
								 )
							)
				ORDER BY tblVendorRate.AccountId, tblVendorRate.TrunkID, tblVendorRate.RateId, tblVendorRate.EffectiveDate DESC
		) tbl
		order by Code asc;
		
      INSERT INTO tmp_VendorCurrentRates_ 
	  Select AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference 
      FROM (
			  SELECT * ,
				@row_num := IF(@prev_AccountId = AccountID AND @prev_TrunkID = TrunkID AND @prev_RateId = RateID AND @prev_EffectiveDate >= EffectiveDate, @row_num + 1, 1) AS RowID,
				@prev_AccountId := AccountID,
				@prev_TrunkID := TrunkID,
				@prev_RateId := RateID,
				@prev_EffectiveDate := EffectiveDate
			  FROM tmp_VendorCurrentRates1_
			  ,(SELECT @row_num := 1,  @prev_AccountId := '',@prev_TrunkID := '', @prev_RateId := '', @prev_EffectiveDate := '') x
           ORDER BY AccountId, TrunkID, RateId, EffectiveDate DESC	
		) tbl
		 WHERE RowID = 1 
		order by Code asc;
		
		 
			
			
	
	
     
     
     
          
          
        insert into tmp_all_code_ (RowCode,Code,RowNo)
               select RowCode , loopCode,RowNo
               from (
                    select   RowCode , loopCode,
                    @RowNo := ( CASE WHEN (@prev_Code  = tbl1.RowCode  ) THEN @RowNo + 1
                                   ELSE 1
                                   END
                                
                              )      as RowNo,
                    @prev_Code := tbl1.RowCode

                    from (
                              SELECT distinct f.Code as RowCode, LEFT(f.Code, x.RowNo) as loopCode FROM (
                                SELECT @RowNo  := @RowNo + 1 as RowNo
                                FROM mysql.help_category 
                                ,(SELECT @RowNo := 0 ) x
                                limit 15
                              ) x
                              INNER JOIN 
										( 
											select distinct Code from
											tmp_VendorCurrentRates_ 
										) AS f
                              ON  x.RowNo   <= LENGTH(f.Code) 
                              order by RowCode desc,  LENGTH(loopCode) DESC 
                    ) tbl1
                    , ( Select @RowNo := 0 ) x
                ) tbl order by RowCode desc,  LENGTH(loopCode) DESC ;


	 
	 
     


                                 
                                             DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage_1;
                                             CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorRate_stage_1 as (select * from tmp_VendorRate_stage_);
                                             
                                              insert ignore into tmp_VendorRate_stage_1 (
                                                	     RowCode,
                                                  	AccountId ,
                                                  	AccountName ,
                                                  	Code ,
                                                  	Rate ,
                                                  	ConnectionFee,                                                           
                                                  	EffectiveDate , 
                                                  	Description ,
                                                  	Preference
                                             )
                                             SELECT  
                                             distinct                                
                                        	RowCode,
                                        	v.AccountId ,
                                        	v.AccountName ,
                                        	v.Code ,
                                        	v.Rate ,
                                        	v.ConnectionFee,                                                           
                                        	v.EffectiveDate , 
                                        	v.Description ,
                                        	v.Preference
                                             FROM tmp_VendorCurrentRates_ v  
                                             Inner join  tmp_all_code_
                                   			SplitCode   on v.Code = SplitCode.Code 
                                            where  SplitCode.Code is not null 
                                            order by AccountID,SplitCode.RowCode desc ,LENGTH(SplitCode.RowCode), v.Code desc, LENGTH(v.Code)  desc;


                                                 
                                             insert into tmp_VendorRate_stage_
                                             SELECT   
                    						RowCode,
                    						v.AccountId ,
                    						v.AccountName ,
                    						v.Code ,
                    						v.Rate ,
                    						v.ConnectionFee,                                                           
                    						v.EffectiveDate ,
                    						v.Description ,
                    						v.Preference, 
                    						@rank := ( CASE WHEN ( @prev_RowCode   = RowCode and   @prev_AccountID = v.AccountId   ) 
                                                            THEN @rank + 1  
                                                            ELSE 1  END ) AS MaxMatchRank,
                    						
                    						@prev_RowCode := RowCode	 as prev_RowCode,															
                                                  @prev_AccountID := v.AccountId as prev_AccountID
                    					 FROM tmp_VendorRate_stage_1 v  
                                             , (SELECT  @prev_RowCode := '',  @rank := 0 , @prev_Code := '' , @prev_AccountID := Null) f
                                             order by AccountID,RowCode desc ;  


                                       truncate tmp_VendorRate_;
                                       insert into tmp_VendorRate_
                                       select
									 AccountId ,
									 AccountName ,
									 Code ,
									 Rate ,
         						           ConnectionFee,                                                           
									 EffectiveDate ,
									 Description ,
									 Preference,
									 RowCode
							   from tmp_VendorRate_stage_ 
                                           where MaxMatchRank = 1 order by RowCode desc; 
						
 
		 
		
			
		
		
	  WHILE v_pointer_ <= v_rowCount_
	    DO

             SET v_rateRuleId_ = (SELECT rateruleid FROM tmp_Raterules_ rr WHERE rr.RowNo = v_pointer_);
           

		INSERT INTO tmp_Rates2_ (code,rate,ConnectionFee)
		select  code,rate,ConnectionFee from tmp_Rates_;

		
		
		truncate tmp_final_VendorRate_;
                               
		IF( v_Use_Preference_ = 0 )
     	THEN
     			
			insert into tmp_final_VendorRate_
			SELECT
			     AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
					  vr.AccountId ,
				     vr.AccountName ,
				     vr.Code ,
				     vr.Rate ,
                 vr.ConnectionFee,
				     vr.EffectiveDate ,
				     vr.Description ,
				     vr.Preference,
				     vr.RowCode,
   			         @rank := CASE WHEN ( @prev_RowCode = vr.RowCode  AND @prev_Rate <  vr.Rate ) THEN @rank+1
                                      WHEN ( @prev_RowCode  = vr.RowCode  AND @prev_Rate = vr.Rate) THEN @rank 
						   ELSE 
						   1
						   END 
				  		  AS FinalRankNumber,
				  	@prev_RowCode  := vr.RowCode,
					@prev_Rate  := vr.Rate
					from (
								select tmpvr.*
							  from tmp_VendorRate_  tmpvr
							  inner JOIN tmp_Raterules_ rr ON rr.RateRuleId = v_rateRuleId_ and tmpvr.RowCode LIKE (REPLACE(rr.code,'*', '%%')) 
			     			  inner JOIN tblRateRuleSource rrs ON  rrs.RateRuleId = rr.rateruleid  and rrs.AccountId = tmpvr.AccountId
					) vr
					,(SELECT @rank := 0 , @prev_RowCode := '' , @prev_Rate := 0  ) x 
				 	order by vr.RowCode,vr.Rate,vr.AccountId ASC
				 
				) tbl1
				where FinalRankNumber <= v_RatePosition_;
				 
		 ELSE 
		 
		 	 insert into tmp_final_VendorRate_
			SELECT
				AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
     				 	vr.AccountId ,
				     vr.AccountName ,
				     vr.Code ,
				     vr.Rate ,
                 vr.ConnectionFee,
				     vr.EffectiveDate ,
				     vr.Description ,
				     vr.Preference,
				     vr.RowCode,
                              @preference_rank := CASE WHEN (@prev_Code  = vr.RowCode  AND @prev_Preference > vr.Preference  )   THEN @preference_rank + 1 
                                                       WHEN (@prev_Code  = vr.RowCode  AND @prev_Preference = vr.Preference AND @prev_Rate < vr.Rate) THEN @preference_rank + 1 
                                                       WHEN (@prev_Code  = vr.RowCode  AND @prev_Preference = vr.Preference AND @prev_Rate = vr.Rate) THEN @preference_rank 
                                                       ELSE 1 END AS FinalRankNumber,
                              @prev_Code := vr.RowCode,
                              @prev_Preference := vr.Preference,
                              @prev_Rate := vr.Rate
					from (
						select tmpvr.*
					  from tmp_VendorRate_  tmpvr
					  inner JOIN tmp_Raterules_ rr ON rr.RateRuleId = v_rateRuleId_ and tmpvr.RowCode LIKE (REPLACE(rr.code,'*', '%%')) 
	     			  inner JOIN tblRateRuleSource rrs ON  rrs.RateRuleId = rr.rateruleid  and rrs.AccountId = tmpvr.AccountId
						) vr
               
                          ,(SELECT @preference_rank := 0 , @prev_Code := ''  , @prev_Preference := 5,  @prev_Rate := 0) x
     			 	 order by vr.RowCode ASC ,vr.Preference DESC ,vr.Rate ASC ,vr.AccountId ASC
                         
				) tbl1
				where 				FinalRankNumber <= v_RatePosition_;
		 
		 END IF;
		 
		 
		 
		truncate   tmp_VRatesstage2_;
		
	               INSERT INTO tmp_VRatesstage2_         
     			SELECT
     				vr.RowCode,
     				vr.code,
     				vr.rate,
     				vr.ConnectionFee,
                         vr.FinalRankNumber
     			FROM tmp_final_VendorRate_ vr
     			 left join tmp_Rates2_ rate on rate.Code = vr.RowCode 
     			WHERE  rate.code is null
                   order by vr.FinalRankNumber desc ;

			
		 
	        IF v_Average_ = 0 
	        THEN              
                                   insert into tmp_dupVRatesstage2_
                                   SELECT RowCode , MAX(FinalRankNumber) AS MaxFinalRankNumber
                                           FROM tmp_VRatesstage2_ GROUP BY RowCode;

                              truncate tmp_Vendorrates_stage3_;
                              INSERT INTO tmp_Vendorrates_stage3_ 
                              select  vr.RowCode as RowCode , vr.rate as rate , vr.ConnectionFee as  ConnectionFee
                              from tmp_VRatesstage2_ vr
                              INNER JOIN tmp_dupVRatesstage2_ vr2
                                ON (vr.RowCode = vr2.RowCode AND  vr.FinalRankNumber = vr2.FinalRankNumber);

                    INSERT IGNORE INTO tmp_Rates_
                    SELECT RowCode,
				CASE WHEN rule_mgn.RateRuleId is not null
				THEN
					CASE WHEN AddMargin LIKE '%p'
						THEN ( vRate.rate + (CAST(REPLACE(AddMargin, 'p', '') AS DECIMAL(18, 2)) / 100) * vRate.rate)
					 ELSE  vRate.rate + AddMargin
					END
				ELSE
				vRate.rate
				END as Rate,
                    ConnectionFee
				  FROM tmp_Vendorrates_stage3_ vRate
				  left join tblRateRuleMargin rule_mgn on  rule_mgn.RateRuleId = v_rateRuleId_ and vRate.rate Between rule_mgn.MinRate and rule_mgn.MaxRate;
                                   
                 			 
						 
	         ELSE 

	           INSERT IGNORE INTO tmp_Rates_
				SELECT RowCode,
						CASE WHEN rule_mgn.AddMargin is not null
						THEN
							CASE WHEN AddMargin LIKE '%p'
								THEN ( vRate.rate + (CAST(REPLACE(AddMargin, 'p', '') AS DECIMAL(18, 2)) / 100) * vRate.rate) 
							 ELSE  vRate.rate + AddMargin 
							END 
						ELSE 
						vRate.rate
						END as Rate,
                              ConnectionFee
				  FROM ( 
								select RowCode,
				                    AVG(Rate) as Rate,
				                    AVG(ConnectionFee) as ConnectionFee
								 	from tmp_VRatesstage2_
							 		group by RowCode 
				     )  vRate
				  left join tblRateRuleMargin rule_mgn on  rule_mgn.RateRuleId = v_rateRuleId_ and vRate.rate Between rule_mgn.MinRate and rule_mgn.MaxRate;
	        END IF;
	        
	       	
	         SET v_pointer_ = v_pointer_ + 1;
			 
			
	     END WHILE;	  
    
  
  
        START TRANSACTION;

	    IF p_RateTableId = -1
	    THEN
	
	        INSERT INTO tblRateTable (CompanyId, RateTableName, RateGeneratorID, TrunkID, CodeDeckId,CurrencyID)
	            VALUES (v_CompanyId_, p_rateTableName, p_RateGeneratorId, v_trunk_, v_codedeckid_,v_CurrencyID_);
	
	        SET p_RateTableId = LAST_INSERT_ID();
	
	        INSERT INTO tblRateTableRate (RateID,
	        RateTableId,
	        Rate,
	        EffectiveDate,
	        PreviousRate,
	        Interval1,
	        IntervalN,
	        ConnectionFee
	        )
	            SELECT DISTINCT
	                RateId,
	                p_RateTableId,
	                 Rate,	               
	                p_EffectiveDate,
	                Rate,
	                Interval1,
	                IntervalN,
	                ConnectionFee
	            FROM tmp_Rates_ rate
	            INNER JOIN tblRate
	                ON rate.code  = tblRate.Code 
	            WHERE tblRate.CodeDeckId = v_codedeckid_;
	
	    ELSE
	    
	    	IF p_delete_exiting_rate = 1
	    	 THEN
			 	 DELETE tblRateTableRate
		            FROM tblRateTableRate 
		        WHERE tblRateTableRate.RateTableId = p_RateTableId;
		    END IF;	
	
	        INSERT INTO tblRateTableRate (RateID,
	        RateTableId,
	        Rate,
	        EffectiveDate,
	        PreviousRate,
	        Interval1,
	        IntervalN,
	        ConnectionFee
	        )
	            SELECT DISTINCT
	                tblRate.RateId,
	                p_RateTableId RateTableId,
	                rate.Rate,
	                p_EffectiveDate EffectiveDate,
	                rate.Rate,
	                tblRate.Interval1,
	                tblRate.IntervalN,
	                rate.ConnectionFee
	            FROM tmp_Rates_ rate
	            INNER JOIN tblRate 
	                ON rate.code  = tblRate.Code 
	            LEFT JOIN tblRateTableRate tbl1 
	                ON tblRate.RateId = tbl1.RateId
	                AND tbl1.RateTableId = p_RateTableId
	            LEFT JOIN tblRateTableRate tbl2 
	            ON tblRate.RateId = tbl2.RateId
	            and tbl2.EffectiveDate = p_EffectiveDate
	            AND tbl2.RateTableId = p_RateTableId
	            WHERE  (    tbl1.RateTableRateID IS NULL 
	                    OR
	                    (
	                        tbl2.RateTableRateID IS NULL 
	                        AND  tbl1.EffectiveDate != p_EffectiveDate                       
	                        
	                    )
	                 )
	            AND tblRate.CodeDeckId = v_codedeckid_;
	
	        UPDATE tblRateTableRate
	        INNER JOIN tblRate 
	            ON tblRate.RateId = tblRateTableRate.RateId
	            AND tblRateTableRate.RateTableId = p_RateTableId
	            AND tblRateTableRate.EffectiveDate = p_EffectiveDate
	         INNER JOIN tmp_Rates_ as rate
	          ON  rate.code  = tblRate.Code 
	          SET tblRateTableRate.PreviousRate = tblRateTableRate.Rate,
	            tblRateTableRate.EffectiveDate = p_EffectiveDate,
	            tblRateTableRate.Rate = rate.Rate,
	            tblRateTableRate.ConnectionFee = rate.ConnectionFee,
	            tblRateTableRate.updated_at = NOW(),
	            tblRateTableRate.ModifiedBy = 'RateManagementService',
	            tblRateTableRate.Interval1 = tblRate.Interval1,
	            tblRateTableRate.IntervalN = tblRate.IntervalN
	        WHERE tblRate.CodeDeckId = v_codedeckid_
	        AND rate.rate != tblRateTableRate.Rate;
	
	        DELETE tblRateTableRate
	            FROM tblRateTableRate 
	        WHERE tblRateTableRate.RateTableId = p_RateTableId
	            AND RateId NOT IN (SELECT DISTINCT
	                    RateId
	                FROM tmp_Rates_ rate
	                INNER JOIN tblRate 
	                    ON rate.code  = tblRate.Code 
	                WHERE tblRate.CodeDeckId = v_codedeckid_)
	            AND tblRateTableRate.EffectiveDate = p_EffectiveDate;	
	
	
	    END IF;


		UPDATE tblRateTable
	   SET RateGeneratorID = p_RateGeneratorId,
		  TrunkID = v_trunk_,
		  CodeDeckId = v_codedeckid_,
		  updated_at = now()
		WHERE RateTableID = p_RateTableId;
     
      SELECT p_RateTableId as RateTableID;
     
    	CALL prc_WSJobStatusUpdate(p_jobId, 'S', 'RateTable Created Successfully', '');
    
    COMMIT;  
    

    	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    	


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGenerateRateTableTest` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGenerateRateTableTest`(IN `p_jobId` INT
, IN `p_RateGeneratorId` INT
, IN `p_RateTableId` INT
, IN `p_rateTableName` VARCHAR(200) 
, IN `p_EffectiveDate` VARCHAR(10))
GenerateRateTable:BEGIN    		
   
    
	DECLARE v_RTRowCount_ INT;       
	DECLARE v_RatePosition_ INT;
	DECLARE v_Use_Preference_ INT;
	DECLARE v_CurrencyID_ INT;
	DECLARE v_CompanyCurrencyID_ INT;
	DECLARE v_Average_ TINYINT;  
	DECLARE v_CompanyId_ INT;
	DECLARE v_codedeckid_ INT;
	DECLARE v_trunk_ INT; 
	DECLARE v_rateRuleId_ INT;
	DECLARE v_RateGeneratorName_ VARCHAR(200);
	DECLARE v_pointer_ INT ;
	DECLARE v_rowCount_ INT ;

	
	
	DECLARE v_tmp_code_cnt int ;
	DECLARE v_tmp_code_pointer int;
	DECLARE v_p_code varchar(50);
	DECLARE v_Codlen_ int;
	DECLARE v_p_code__ VARCHAR(50);
	DECLARE v_Commit int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN      
		   ROLLBACK;	  
		   CALL prc_WSJobStatusUpdate(p_jobId, 'F', 'RateTable generation failed', ''); 
               		   
    END; 
	
     SET @@session.collation_connection='utf8_unicode_ci';
     SET @@session.character_set_client='utf8';
     
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
   

    SET p_EffectiveDate = CAST(p_EffectiveDate AS DATE);
       
    
    	IF p_rateTableName IS NOT NULL
	 	THEN


        SET v_RTRowCount_ = (SELECT
                COUNT(*)
            FROM tblRateTable
            WHERE RateTableName = p_rateTableName
            AND CompanyId = (SELECT
                    CompanyId
                FROM tblRateGenerator
                WHERE RateGeneratorID = p_RateGeneratorId));

        IF v_RTRowCount_ > 0
        THEN
            CALL prc_WSJobStatusUpdate  (p_jobId, 'F', 'RateTable Name is already exist, Please try using another RateTable Name', '');
            LEAVE GenerateRateTable;
        END IF;
    	END IF;

		DROP TEMPORARY TABLE IF EXISTS tmp_Rates_;
		CREATE TEMPORARY TABLE tmp_Rates_  (
		  code VARCHAR(50) COLLATE utf8_unicode_ci,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates_code (`code`) 
		);
		DROP TEMPORARY TABLE IF EXISTS tmp_Rates2_;
		CREATE TEMPORARY TABLE tmp_Rates2_  (
		  code VARCHAR(50) COLLATE utf8_unicode_ci,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates2_code (`code`)
		);


		DROP TEMPORARY TABLE IF EXISTS tmp_Codedecks_;
		CREATE TEMPORARY TABLE tmp_Codedecks_ (
        CodeDeckId INT
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Raterules_;

     	CREATE TEMPORARY TABLE tmp_Raterules_  (
        rateruleid INT,
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        RowNo INT,
        INDEX tmp_Raterules_code (`code`),
        INDEX tmp_Raterules_rateruleid (`rateruleid`),
        INDEX tmp_Raterules_RowNo (`RowNo`)
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_;
     	CREATE TEMPORARY TABLE tmp_Vendorrates_  (
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        AccountId INT,
        RowNo INT,
        PreferenceRank INT,
        INDEX tmp_Vendorrates_code (`code`),
        INDEX tmp_Vendorrates_rate (`rate`)
    	);
    	
    	
    	DROP TEMPORARY TABLE IF EXISTS tmp_VRatesstage2_;
     	CREATE TEMPORARY TABLE tmp_VRatesstage2_  (
		RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        FinalRankNumber int,
        INDEX tmp_Vendorrates_stage2__code (`RowCode`)
    	);
     DROP TEMPORARY TABLE IF EXISTS tmp_dupVRatesstage2_;
     	CREATE TEMPORARY TABLE tmp_dupVRatesstage2_  (
		RowCode VARCHAR(50)  COLLATE utf8_unicode_ci,
        FinalRankNumber int,
        INDEX tmp_dupVendorrates_stage2__code (`RowCode`)
    	);
     DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_stage3_;
     CREATE TEMPORARY TABLE tmp_Vendorrates_stage3_  (
        RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        INDEX tmp_Vendorrates_stage2__code (`RowCode`)
    	);    	
    	
     	DROP TEMPORARY TABLE IF EXISTS tmp_code_;
     	CREATE TEMPORARY TABLE tmp_code_  (
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        INDEX tmp_code_code (`code`)
    	);

     DROP TEMPORARY TABLE IF EXISTS tmp_all_code_;
  CREATE TEMPORARY TABLE tmp_all_code_ ( 
     RowCode  varchar(50) COLLATE utf8_unicode_ci,
     Code  varchar(50) COLLATE utf8_unicode_ci,
     RowNo int,
     INDEX Index2 (Code)
   );

 

      DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage_;
       CREATE TEMPORARY TABLE tmp_VendorRate_stage_ (
          RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
          AccountId INT ,
          AccountName VARCHAR(100) ,
          Code VARCHAR(50) COLLATE utf8_unicode_ci,
          Rate DECIMAL(18,6) ,
          ConnectionFee DECIMAL(18,6) ,
          EffectiveDate DATETIME ,
          Description VARCHAR(255),
          Preference INT,
          MaxMatchRank int ,
          prev_prev_RowCode VARCHAR(50),
          prev_AccountID int
        );

 DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_;
  CREATE TEMPORARY TABLE tmp_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) COLLATE utf8_unicode_ci,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50) COLLATE utf8_unicode_ci  
   );
   DROP TEMPORARY TABLE IF EXISTS tmp_final_VendorRate_;
  CREATE TEMPORARY TABLE tmp_final_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) COLLATE utf8_unicode_ci,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
     FinalRankNumber int,
		INDEX IX_CODE (RowCode)
   );
   
   DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates_;
   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates_(
			AccountId int,
            AccountName varchar(200),
			Code varchar(50) COLLATE utf8_unicode_ci,
			Description varchar(200) ,
			Rate DECIMAL(18,6) ,
               ConnectionFee DECIMAL(18,6) , 
			EffectiveDate date,
			TrunkID int,
			CountryID int,
			RateID int,
			Preference int,
			INDEX IX_CODE (Code)			
	);
	
	DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates1_;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates1_(
		AccountId int,
		AccountName varchar(200),
		Code varchar(50),
		Description varchar(200),
		Rate DECIMAL(18,6) ,
		ConnectionFee DECIMAL(18,6) , 
		EffectiveDate date,
		TrunkID int,
		CountryID int,
		RateID int,
		Preference int,
		INDEX IX_Code (Code),
		INDEX tmp_VendorCurrentRates_AccountId (`AccountId`,`TrunkID`,`RateId`,`EffectiveDate`)
	);

    	SELECT CurrencyID INTO v_CurrencyID_ FROM  tblRateGenerator WHERE RateGeneratorId = p_RateGeneratorId;

    	SELECT
        UsePreference,  
        rateposition,
        companyid ,
        CodeDeckId,
        tblRateGenerator.TrunkID,
        tblRateGenerator.UseAverage  ,
        tblRateGenerator.RateGeneratorName INTO v_Use_Preference_, v_RatePosition_, v_CompanyId_, v_codedeckid_, v_trunk_, v_Average_, v_RateGeneratorName_
    	FROM tblRateGenerator
    	WHERE RateGeneratorId = p_RateGeneratorId;


	SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = v_CompanyId_;


      

    	INSERT INTO tmp_Raterules_
	   SELECT
            rateruleid,
            tblRateRule.code,
            @row_num := @row_num+1 AS RowID
        FROM tblRateRule,(SELECT @row_num := 0) x
        WHERE rategeneratorid = p_RateGeneratorId
        ORDER BY tblRateRule.Code DESC;

	     INSERT INTO tmp_Codedecks_
	        SELECT DISTINCT
	            tblVendorTrunk.CodeDeckId
	        FROM tblRateRule
	        INNER JOIN tblRateRuleSource
	            ON tblRateRule.RateRuleId = tblRateRuleSource.RateRuleId
	        INNER JOIN tblAccount
	            ON tblAccount.AccountID = tblRateRuleSource.AccountId and tblAccount.IsVendor = 1
			JOIN tblVendorTrunk
	                ON tblAccount.AccountId = tblVendorTrunk.AccountID
	                AND  tblVendorTrunk.TrunkID = v_trunk_
	                AND tblVendorTrunk.Status = 1
	        WHERE RateGeneratorId = p_RateGeneratorId;

	    SET v_pointer_ = 1;
	    SET v_rowCount_ = (SELECT COUNT(distinct Code ) FROM tmp_Raterules_);

		
		
		
		insert into tmp_code_
          SELECT  DISTINCT LEFT(f.Code, x.RowNo) as loopCode FROM (
          SELECT @RowNo  := @RowNo + 1 as RowNo
          FROM mysql.help_category 
          ,(SELECT @RowNo := 0 ) x
          limit 15
          ) x
          INNER JOIN 
          (SELECT 
                    distinct
	                tblRate.code
	            FROM tblRate
	            JOIN tmp_Raterules_ rr
	                ON tblRate.code LIKE (REPLACE(rr.code,
	                '*', '%%'))
					where  tblRate.CodeDeckId = v_codedeckid_    	                
	          Order by tblRate.code 
          ) as f
          ON   x.RowNo   <= LENGTH(f.Code) 
          order by loopCode   desc;

 
  
	
          
		SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = v_CompanyId_;
		SET @IncludeAccountIds = (SELECT GROUP_CONCAT(AccountId) from tblRateRule rr inner join  tblRateRuleSource rrs on rr.RateRuleId = rrs.RateRuleId where rr.RateGeneratorId = p_RateGeneratorId ) ;
     
	
	
	  
	 INSERT INTO tmp_VendorCurrentRates1_ 
	 Select DISTINCT AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference					
      FROM (
				SELECT  tblVendorRate.AccountId,tblAccount.AccountName, tblRate.Code, tblRate.Description, 
				 CASE WHEN  tblAccount.CurrencyId = v_CurrencyID_
								THEN
									   tblVendorRate.Rate
					 WHEN  v_CompanyCurrencyID_ = v_CurrencyID_  
							 THEN
								  (
								   ( tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ) )
								 )
							  ELSE 
							  (
										
								  (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = v_CurrencyID_ and  CompanyID = v_CompanyId_ )
									* (tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ))
							  )
							 END
								as  Rate,
								 ConnectionFee,
			  DATE_FORMAT (tblVendorRate.EffectiveDate, '%Y-%m-%d') AS EffectiveDate, 
			    tblVendorRate.TrunkID, tblRate.CountryID, tblRate.RateID,IFNULL(vp.Preference, 5) AS Preference,
				@row_num := IF(@prev_AccountId = tblVendorRate.AccountID AND @prev_TrunkID = tblVendorRate.TrunkID AND @prev_RateId = tblVendorRate.RateID AND @prev_EffectiveDate >= tblVendorRate.EffectiveDate, @row_num + 1, 1) AS RowID,
				@prev_AccountId := tblVendorRate.AccountID,
				@prev_TrunkID := tblVendorRate.TrunkID,
				@prev_RateId := tblVendorRate.RateID,
				@prev_EffectiveDate := tblVendorRate.EffectiveDate
			  FROM      tblVendorRate
			  
					 Inner join tblVendorTrunk vt on vt.CompanyID = v_CompanyId_ AND vt.AccountID = tblVendorRate.AccountID and 
							
							  vt.Status =  1 and vt.TrunkID =  v_trunk_  
                              inner join tmp_Codedecks_ tcd on vt.CodeDeckId = tcd.CodeDeckId
						INNER JOIN tblAccount   ON  tblAccount.CompanyID = v_CompanyId_ AND tblVendorRate.AccountId = tblAccount.AccountID and tblAccount.IsVendor = 1
						INNER JOIN tblRate ON tblRate.CompanyID = v_CompanyId_  AND tblRate.CodeDeckId = vt.CodeDeckId  AND    tblVendorRate.RateId = tblRate.RateID
						INNER JOIN tmp_code_ tcode ON tcode.Code  = tblRate.Code 
						LEFT JOIN tblVendorPreference vp
										 ON vp.AccountId = tblVendorRate.AccountId
										 AND vp.TrunkID = tblVendorRate.TrunkID
										 AND vp.RateId = tblVendorRate.RateId
						LEFT OUTER JOIN tblVendorBlocking AS blockCode   ON tblVendorRate.RateId = blockCode.RateId
																	  AND tblVendorRate.AccountId = blockCode.AccountId
																	  AND tblVendorRate.TrunkID = blockCode.TrunkID
						LEFT OUTER JOIN tblVendorBlocking AS blockCountry    ON tblRate.CountryID = blockCountry.CountryId
																	  AND tblVendorRate.AccountId = blockCountry.AccountId
																	  AND tblVendorRate.TrunkID = blockCountry.TrunkID
						
						,(SELECT @row_num := 1,  @prev_AccountId := '',@prev_TrunkID := '', @prev_RateId := '', @prev_EffectiveDate := '') x
					 
			  WHERE      
						( EffectiveDate <= NOW() )
						AND tblAccount.IsVendor = 1
						AND tblAccount.Status = 1
						AND tblAccount.CurrencyId is not NULL
						AND tblVendorRate.TrunkID = v_trunk_
						AND blockCode.RateId IS NULL
					   AND blockCountry.CountryId IS NULL
						AND ( @IncludeAccountIds = NULL
							  OR ( @IncludeAccountIds IS NOT NULL
								   AND FIND_IN_SET(tblVendorRate.AccountId,@IncludeAccountIds) > 0                    
								 )
							)
				ORDER BY tblVendorRate.AccountId, tblVendorRate.TrunkID, tblVendorRate.RateId, tblVendorRate.EffectiveDate DESC
		) tbl
		order by Code asc;
		
      INSERT INTO tmp_VendorCurrentRates_ 
	  Select AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference 
      FROM (
			  SELECT * ,
				@row_num := IF(@prev_AccountId = AccountID AND @prev_TrunkID = TrunkID AND @prev_RateId = RateID AND @prev_EffectiveDate >= EffectiveDate, @row_num + 1, 1) AS RowID,
				@prev_AccountId := AccountID,
				@prev_TrunkID := TrunkID,
				@prev_RateId := RateID,
				@prev_EffectiveDate := EffectiveDate
			  FROM tmp_VendorCurrentRates1_
			  ,(SELECT @row_num := 1,  @prev_AccountId := '',@prev_TrunkID := '', @prev_RateId := '', @prev_EffectiveDate := '') x
           ORDER BY AccountId, TrunkID, RateId, EffectiveDate DESC	
		) tbl
		 WHERE RowID = 1 
		order by Code asc;
		
	
	
     
     
     
          
          
        insert into tmp_all_code_ (RowCode,Code,RowNo)
               select RowCode , loopCode,RowNo
               from (
                    select   RowCode , loopCode,
                    @RowNo := ( CASE WHEN (@prev_Code  = tbl1.RowCode  ) THEN @RowNo + 1
                                   ELSE 1
                                   END
                                
                              )      as RowNo,
                    @prev_Code := tbl1.RowCode

                    from (
                              SELECT distinct f.Code as RowCode, LEFT(f.Code, x.RowNo) as loopCode FROM (
                                SELECT @RowNo  := @RowNo + 1 as RowNo
                                FROM mysql.help_category 
                                ,(SELECT @RowNo := 0 ) x
                                limit 15
                              ) x
                              INNER JOIN 
										( 
											select distinct Code from
											tmp_VendorCurrentRates_ 
										) AS f
                              ON  x.RowNo   <= LENGTH(f.Code) 
                              order by RowCode desc,  LENGTH(loopCode) DESC 
                    ) tbl1
                    , ( Select @RowNo := 0 ) x
                ) tbl order by RowCode desc,  LENGTH(loopCode) DESC ;


	 
	 
     


                                 
                                             DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage_1;
                                             CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorRate_stage_1 as (select * from tmp_VendorRate_stage_);
                                             
                                              insert ignore into tmp_VendorRate_stage_1 (
                                                	     RowCode,
                                                  	AccountId ,
                                                  	AccountName ,
                                                  	Code ,
                                                  	Rate ,
                                                  	ConnectionFee,                                                           
                                                  	EffectiveDate , 
                                                  	Description ,
                                                  	Preference
                                             )
                                             SELECT  
                                             distinct                                
                                        	RowCode,
                                        	v.AccountId ,
                                        	v.AccountName ,
                                        	v.Code ,
                                        	v.Rate ,
                                        	v.ConnectionFee,                                                           
                                        	v.EffectiveDate , 
                                        	v.Description ,
                                        	v.Preference
                                             FROM tmp_VendorCurrentRates_ v  
                                             Inner join  tmp_all_code_
                                   			SplitCode   on v.Code = SplitCode.Code 
                                            where  SplitCode.Code is not null 
                                            order by AccountID,SplitCode.RowCode desc ,LENGTH(SplitCode.RowCode), v.Code desc, LENGTH(v.Code)  desc;


                                                 
                                             insert into tmp_VendorRate_stage_
                                             SELECT   
                    						RowCode,
                    						v.AccountId ,
                    						v.AccountName ,
                    						v.Code ,
                    						v.Rate ,
                    						v.ConnectionFee,                                                           
                    						v.EffectiveDate ,
                    						v.Description ,
                    						v.Preference, 
                    						@rank := ( CASE WHEN ( @prev_RowCode   = RowCode and   @prev_AccountID = v.AccountId   ) 
                                                            THEN @rank + 1  
                                                            ELSE 1  END ) AS MaxMatchRank,
                    						
                    						@prev_RowCode := RowCode	 as prev_RowCode,															
                                                  @prev_AccountID := v.AccountId as prev_AccountID
                    					 FROM tmp_VendorRate_stage_1 v  
                                             , (SELECT  @prev_RowCode := '',  @rank := 0 , @prev_Code := '' , @prev_AccountID := Null) f
                                             order by AccountID,RowCode desc ;  


                                       truncate tmp_VendorRate_;
                                       insert into tmp_VendorRate_
                                       select
									 AccountId ,
									 AccountName ,
									 Code ,
									 Rate ,
         						           ConnectionFee,                                                           
									 EffectiveDate ,
									 Description ,
									 Preference,
									 RowCode
							   from tmp_VendorRate_stage_ 
                                           where MaxMatchRank = 1 order by RowCode desc; 
						
 
		 
		
			
		
		
	  WHILE v_pointer_ <= v_rowCount_
	    DO

             SET v_rateRuleId_ = (SELECT rateruleid FROM tmp_Raterules_ rr WHERE rr.RowNo = v_pointer_);
           

		INSERT INTO tmp_Rates2_ (code,rate,ConnectionFee)
		select  code,rate,ConnectionFee from tmp_Rates_;

		
		
		truncate tmp_final_VendorRate_;
                               
		IF( v_Use_Preference_ = 0 )
     	THEN
     			
			insert into tmp_final_VendorRate_
			SELECT
			     AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
					  vr.AccountId ,
				     vr.AccountName ,
				     vr.Code ,
				     vr.Rate ,
                 vr.ConnectionFee,
				     vr.EffectiveDate ,
				     vr.Description ,
				     vr.Preference,
				     vr.RowCode,
   			         @rank := CASE WHEN ( @prev_RowCode = vr.RowCode  AND @prev_Rate <  vr.Rate ) THEN @rank+1
                                      WHEN ( @prev_RowCode  = vr.RowCode  AND @prev_Rate = vr.Rate) THEN @rank 
						   ELSE 
						   1
						   END 
				  		  AS FinalRankNumber,
				  	@prev_RowCode  := vr.RowCode,
					@prev_Rate  := vr.Rate
					from (
								select tmpvr.*
							  from tmp_VendorRate_  tmpvr
							  inner JOIN tmp_Raterules_ rr ON rr.RateRuleId = v_rateRuleId_ and tmpvr.RowCode LIKE (REPLACE(rr.code,'*', '%%')) 
			     			  inner JOIN tblRateRuleSource rrs ON  rrs.RateRuleId = rr.rateruleid  and rrs.AccountId = tmpvr.AccountId
					) vr
					,(SELECT @rank := 0 , @prev_RowCode := '' , @prev_Rate := 0  ) x 
				 	order by vr.RowCode,vr.Rate,vr.AccountId ASC
				 
				) tbl1
				where FinalRankNumber <= v_RatePosition_;
				 
		 ELSE 
		 
		 	 insert into tmp_final_VendorRate_
			SELECT
				AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
     				 	vr.AccountId ,
				     vr.AccountName ,
				     vr.Code ,
				     vr.Rate ,
                 vr.ConnectionFee,
				     vr.EffectiveDate ,
				     vr.Description ,
				     vr.Preference,
				     vr.RowCode,
                              @preference_rank := CASE WHEN (@prev_Code  = vr.RowCode  AND @prev_Preference > vr.Preference  )   THEN @preference_rank + 1 
                                                       WHEN (@prev_Code  = vr.RowCode  AND @prev_Preference = vr.Preference AND @prev_Rate < vr.Rate) THEN @preference_rank + 1 
                                                       WHEN (@prev_Code  = vr.RowCode  AND @prev_Preference = vr.Preference AND @prev_Rate = vr.Rate) THEN @preference_rank 
                                                       ELSE 1 END AS FinalRankNumber,
                              @prev_Code := vr.RowCode,
                              @prev_Preference := vr.Preference,
                              @prev_Rate := vr.Rate
					from (
						select tmpvr.*
					  from tmp_VendorRate_  tmpvr
					  inner JOIN tmp_Raterules_ rr ON rr.RateRuleId = v_rateRuleId_ and tmpvr.RowCode LIKE (REPLACE(rr.code,'*', '%%')) 
	     			  inner JOIN tblRateRuleSource rrs ON  rrs.RateRuleId = rr.rateruleid  and rrs.AccountId = tmpvr.AccountId
						) vr
               
                          ,(SELECT @preference_rank := 0 , @prev_Code := ''  , @prev_Preference := 5,  @prev_Rate := 0) x
     			 	 order by vr.RowCode ASC ,vr.Preference DESC ,vr.Rate ASC ,vr.AccountId ASC
                         
				) tbl1
				where 				FinalRankNumber <= v_RatePosition_;
		 
		 END IF;
		 
		 
		 
		truncate   tmp_VRatesstage2_;
		
	               INSERT INTO tmp_VRatesstage2_         
     			SELECT
     				vr.RowCode,
     				vr.code,
     				vr.rate,
     				vr.ConnectionFee,
                         vr.FinalRankNumber
     			FROM tmp_final_VendorRate_ vr
     			 left join tmp_Rates2_ rate on rate.Code = vr.RowCode 
     			WHERE  rate.code is null
                   order by vr.FinalRankNumber desc ;

			
		 
	        IF v_Average_ = 0 
	        THEN              
                                   insert into tmp_dupVRatesstage2_
                                   SELECT RowCode , MAX(FinalRankNumber) AS MaxFinalRankNumber
                                           FROM tmp_VRatesstage2_ GROUP BY RowCode;

                              truncate tmp_Vendorrates_stage3_;
                              INSERT INTO tmp_Vendorrates_stage3_ 
                              select  vr.RowCode as RowCode , vr.rate as rate , vr.ConnectionFee as  ConnectionFee
                              from tmp_VRatesstage2_ vr
                              INNER JOIN tmp_dupVRatesstage2_ vr2
                                ON (vr.RowCode = vr2.RowCode AND  vr.FinalRankNumber = vr2.FinalRankNumber);

                    INSERT INTO tmp_Rates_
                    SELECT RowCode,
				CASE WHEN rule_mgn.RateRuleId is not null
				THEN
					CASE WHEN AddMargin LIKE '%p'
						THEN ( vRate.rate + (CAST(REPLACE(AddMargin, 'p', '') AS DECIMAL(18, 2)) / 100) * vRate.rate)
					 ELSE  vRate.rate + AddMargin
					END
				ELSE
				vRate.rate
				END as Rate,
                    ConnectionFee
				  FROM tmp_Vendorrates_stage3_ vRate
				  left join tblRateRuleMargin rule_mgn on  rule_mgn.RateRuleId = v_rateRuleId_ and vRate.rate Between rule_mgn.MinRate and rule_mgn.MaxRate;
                                   
                 			 
						 
	         ELSE 

	           INSERT INTO tmp_Rates_
				SELECT RowCode,
						CASE WHEN rule_mgn.AddMargin is not null
						THEN
							CASE WHEN AddMargin LIKE '%p'
								THEN ( vRate.rate + (CAST(REPLACE(AddMargin, 'p', '') AS DECIMAL(18, 2)) / 100) * vRate.rate) 
							 ELSE  vRate.rate + AddMargin 
							END 
						ELSE 
						vRate.rate
						END as Rate,
                              ConnectionFee
				  FROM ( 
								select RowCode,
				                    AVG(Rate) as Rate,
				                    AVG(ConnectionFee) as ConnectionFee
								 	from tmp_VRatesstage2_
							 		group by RowCode 
				     )  vRate
				  left join tblRateRuleMargin rule_mgn on  rule_mgn.RateRuleId = v_rateRuleId_ and vRate.rate Between rule_mgn.MinRate and rule_mgn.MaxRate;
	        END IF;
	        
	       	
	         SET v_pointer_ = v_pointer_ + 1;
			 
			
	     END WHILE;	   
  
  		

    	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    	

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGenerateRateTableWithPrefix` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGenerateRateTableWithPrefix`(
	IN `p_jobId` INT,
	IN `p_RateGeneratorId` INT,
	IN `p_RateTableId` INT,
	IN `p_rateTableName` VARCHAR(200),
	IN `p_EffectiveDate` VARCHAR(10),
	IN `p_delete_exiting_rate` INT,
	IN `p_EffectiveRate` VARCHAR(50)


)
GenerateRateTable:BEGIN   		
   
    
	DECLARE v_RTRowCount_ INT;       
	DECLARE v_RatePosition_ INT;
	DECLARE v_Use_Preference_ INT;
	DECLARE v_CurrencyID_ INT;
	DECLARE v_CompanyCurrencyID_ INT;
	DECLARE v_Average_ TINYINT;  
	DECLARE v_CompanyId_ INT;
	DECLARE v_codedeckid_ INT;
	DECLARE v_trunk_ INT; 
	DECLARE v_rateRuleId_ INT;
	DECLARE v_RateGeneratorName_ VARCHAR(200);
	DECLARE v_pointer_ INT ;
	DECLARE v_rowCount_ INT ;

	
	
	DECLARE v_tmp_code_cnt int ;
	DECLARE v_tmp_code_pointer int;
	DECLARE v_p_code varchar(50);
	DECLARE v_Codlen_ int;
	DECLARE v_p_code__ VARCHAR(50);
	DECLARE v_Commit int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN      
             show warnings;
		   ROLLBACK;	  
		   CALL prc_WSJobStatusUpdate(p_jobId, 'F', 'RateTable generation failed', ''); 
               		   
    END; 
	
     SET @@session.collation_connection='utf8_unicode_ci';
     SET @@session.character_set_client='utf8';
     
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
   

    SET p_EffectiveDate = CAST(p_EffectiveDate AS DATE);
       
    
    	IF p_rateTableName IS NOT NULL
	 	THEN


        SET v_RTRowCount_ = (SELECT
                COUNT(*)
            FROM tblRateTable
            WHERE RateTableName = p_rateTableName
            AND CompanyId = (SELECT
                    CompanyId
                FROM tblRateGenerator
                WHERE RateGeneratorID = p_RateGeneratorId));

        IF v_RTRowCount_ > 0
        THEN
            CALL prc_WSJobStatusUpdate  (p_jobId, 'F', 'RateTable Name is already exist, Please try using another RateTable Name', '');
            LEAVE GenerateRateTable;
        END IF;
    	END IF;

		DROP TEMPORARY TABLE IF EXISTS tmp_Rates_;
		CREATE TEMPORARY TABLE tmp_Rates_  (
		  code VARCHAR(50) COLLATE utf8_unicode_ci,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates_code (`code`),
 	     UNIQUE KEY `unique_code` (`code`) 
		);
		DROP TEMPORARY TABLE IF EXISTS tmp_Rates2_;
		CREATE TEMPORARY TABLE tmp_Rates2_  (
		  code VARCHAR(50) COLLATE utf8_unicode_ci,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates2_code (`code`)
		);


		DROP TEMPORARY TABLE IF EXISTS tmp_Codedecks_;
		CREATE TEMPORARY TABLE tmp_Codedecks_ (
        CodeDeckId INT
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Raterules_;

     	CREATE TEMPORARY TABLE tmp_Raterules_  (
        rateruleid INT,
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        RowNo INT,
        INDEX tmp_Raterules_code (`code`),
        INDEX tmp_Raterules_rateruleid (`rateruleid`),
        INDEX tmp_Raterules_RowNo (`RowNo`)
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_;
     	CREATE TEMPORARY TABLE tmp_Vendorrates_  (
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        AccountId INT,
        RowNo INT,
        PreferenceRank INT,
        INDEX tmp_Vendorrates_code (`code`),
        INDEX tmp_Vendorrates_rate (`rate`)
    	);
    	
    	
    	DROP TEMPORARY TABLE IF EXISTS tmp_VRatesstage2_;
     	CREATE TEMPORARY TABLE tmp_VRatesstage2_  (
		RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        FinalRankNumber int,
        INDEX tmp_Vendorrates_stage2__code (`RowCode`)
    	);
     DROP TEMPORARY TABLE IF EXISTS tmp_dupVRatesstage2_;
     	CREATE TEMPORARY TABLE tmp_dupVRatesstage2_  (
		RowCode VARCHAR(50)  COLLATE utf8_unicode_ci,
        FinalRankNumber int,
        INDEX tmp_dupVendorrates_stage2__code (`RowCode`)
    	);
     DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_stage3_;
     CREATE TEMPORARY TABLE tmp_Vendorrates_stage3_  (
        RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        INDEX tmp_Vendorrates_stage2__code (`RowCode`)
    	);    	
    	
     	DROP TEMPORARY TABLE IF EXISTS tmp_code_;
     	CREATE TEMPORARY TABLE tmp_code_  (
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        INDEX tmp_code_code (`code`)
    	);

     DROP TEMPORARY TABLE IF EXISTS tmp_all_code_;
  CREATE TEMPORARY TABLE tmp_all_code_ ( 
     RowCode  varchar(50) COLLATE utf8_unicode_ci,
     Code  varchar(50) COLLATE utf8_unicode_ci,
     RowNo int,
     INDEX Index2 (Code)
   );

 

      DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage_;
       CREATE TEMPORARY TABLE tmp_VendorRate_stage_ (
          RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
          AccountId INT ,
          AccountName VARCHAR(100) ,
          Code VARCHAR(50) COLLATE utf8_unicode_ci,
          Rate DECIMAL(18,6) ,
          ConnectionFee DECIMAL(18,6) ,
          EffectiveDate DATETIME ,
          Description VARCHAR(255),
          Preference INT,
          MaxMatchRank int ,
          prev_prev_RowCode VARCHAR(50),
          prev_AccountID int
        );

 DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_;
  CREATE TEMPORARY TABLE tmp_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) COLLATE utf8_unicode_ci,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50) COLLATE utf8_unicode_ci  
   );
   DROP TEMPORARY TABLE IF EXISTS tmp_final_VendorRate_;
  CREATE TEMPORARY TABLE tmp_final_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) COLLATE utf8_unicode_ci,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
     FinalRankNumber int,
		INDEX IX_CODE (RowCode)
   );
   
   DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates_;
   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates_(
			AccountId int,
            AccountName varchar(200),
			Code varchar(50) COLLATE utf8_unicode_ci,
			Description varchar(200) ,
			Rate DECIMAL(18,6) ,
               ConnectionFee DECIMAL(18,6) , 
			EffectiveDate date,
			TrunkID int,
			CountryID int,
			RateID int,
			Preference int,
			INDEX IX_CODE (Code)			
	);
	DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates1_;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates1_(
		AccountId int,
		AccountName varchar(200),
		Code varchar(50),
		Description varchar(200),
		Rate DECIMAL(18,6) ,
		ConnectionFee DECIMAL(18,6) , 
		EffectiveDate date,
		TrunkID int,
		CountryID int,
		RateID int,
		Preference int,
		INDEX IX_Code (Code),
		INDEX tmp_VendorCurrentRates_AccountId (`AccountId`,`TrunkID`,`RateId`,`EffectiveDate`)
	);

    	SELECT CurrencyID INTO v_CurrencyID_ FROM  tblRateGenerator WHERE RateGeneratorId = p_RateGeneratorId;

    	SELECT
        UsePreference,  
        rateposition,
        companyid ,
        CodeDeckId,
        tblRateGenerator.TrunkID,
        tblRateGenerator.UseAverage  ,
        tblRateGenerator.RateGeneratorName INTO v_Use_Preference_, v_RatePosition_, v_CompanyId_, v_codedeckid_, v_trunk_, v_Average_, v_RateGeneratorName_
    	FROM tblRateGenerator
    	WHERE RateGeneratorId = p_RateGeneratorId;


	SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = v_CompanyId_;


      

    	INSERT INTO tmp_Raterules_
	   SELECT
            rateruleid,
            tblRateRule.code,
            @row_num := @row_num+1 AS RowID
        FROM tblRateRule,(SELECT @row_num := 0) x
        WHERE rategeneratorid = p_RateGeneratorId
        ORDER BY tblRateRule.Code DESC;

	     INSERT INTO tmp_Codedecks_
	        SELECT DISTINCT
	            tblVendorTrunk.CodeDeckId
	        FROM tblRateRule
	        INNER JOIN tblRateRuleSource
	            ON tblRateRule.RateRuleId = tblRateRuleSource.RateRuleId
	        INNER JOIN tblAccount
	            ON tblAccount.AccountID = tblRateRuleSource.AccountId and tblAccount.IsVendor = 1
			JOIN tblVendorTrunk
	                ON tblAccount.AccountId = tblVendorTrunk.AccountID
	                AND  tblVendorTrunk.TrunkID = v_trunk_
	                AND tblVendorTrunk.Status = 1
	        WHERE RateGeneratorId = p_RateGeneratorId;

	    SET v_pointer_ = 1;
	    SET v_rowCount_ = (SELECT COUNT(distinct Code ) FROM tmp_Raterules_);

		
		
		
		
		
		
		insert into tmp_code_
		SELECT
			tblRate.code
		FROM tblRate
		JOIN tmp_Codedecks_ cd
			ON tblRate.CodeDeckId = cd.CodeDeckId
		JOIN tmp_Raterules_ rr
			ON tblRate.code LIKE (REPLACE(rr.code,'*', '%%'))
		Order by tblRate.code ;
			

		
		

 
  
	
          
		SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = v_CompanyId_;
		SET @IncludeAccountIds = (SELECT GROUP_CONCAT(AccountId) from tblRateRule rr inner join  tblRateRuleSource rrs on rr.RateRuleId = rrs.RateRuleId where rr.RateGeneratorId = p_RateGeneratorId ) ;
     
	
     
	 INSERT INTO tmp_VendorCurrentRates1_ 
	 Select DISTINCT AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference					
      FROM (
				SELECT  tblVendorRate.AccountId,tblAccount.AccountName, tblRate.Code, tblRate.Description, 
				 CASE WHEN  tblAccount.CurrencyId = v_CurrencyID_
								THEN
									   tblVendorRate.Rate
					 WHEN  v_CompanyCurrencyID_ = v_CurrencyID_  
							 THEN
								  (
								   ( tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ) )
								 )
							  ELSE 
							  (
										
								  (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = v_CurrencyID_ and  CompanyID = v_CompanyId_ )
									* (tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ))
							  )
							 END
								as  Rate,
								 ConnectionFee,
			  DATE_FORMAT (tblVendorRate.EffectiveDate, '%Y-%m-%d') AS EffectiveDate, 
			    tblVendorRate.TrunkID, tblRate.CountryID, tblRate.RateID,IFNULL(vp.Preference, 5) AS Preference,
				@row_num := IF(@prev_AccountId = tblVendorRate.AccountID AND @prev_TrunkID = tblVendorRate.TrunkID AND @prev_RateId = tblVendorRate.RateID AND @prev_EffectiveDate >= tblVendorRate.EffectiveDate, @row_num + 1, 1) AS RowID,
				@prev_AccountId := tblVendorRate.AccountID,
				@prev_TrunkID := tblVendorRate.TrunkID,
				@prev_RateId := tblVendorRate.RateID,
				@prev_EffectiveDate := tblVendorRate.EffectiveDate
			  FROM      tblVendorRate
			  
					 Inner join tblVendorTrunk vt on vt.CompanyID = v_CompanyId_ AND vt.AccountID = tblVendorRate.AccountID and 
							
							  vt.Status =  1 and vt.TrunkID =  v_trunk_  
                              inner join tmp_Codedecks_ tcd on vt.CodeDeckId = tcd.CodeDeckId
						INNER JOIN tblAccount   ON  tblAccount.CompanyID = v_CompanyId_ AND tblVendorRate.AccountId = tblAccount.AccountID and tblAccount.IsVendor = 1
						INNER JOIN tblRate ON tblRate.CompanyID = v_CompanyId_  AND tblRate.CodeDeckId = vt.CodeDeckId  AND    tblVendorRate.RateId = tblRate.RateID
						INNER JOIN tmp_code_ tcode ON tcode.Code  = tblRate.Code 
						LEFT JOIN tblVendorPreference vp
										 ON vp.AccountId = tblVendorRate.AccountId
										 AND vp.TrunkID = tblVendorRate.TrunkID
										 AND vp.RateId = tblVendorRate.RateId
						LEFT OUTER JOIN tblVendorBlocking AS blockCode   ON tblVendorRate.RateId = blockCode.RateId
																	  AND tblVendorRate.AccountId = blockCode.AccountId
																	  AND tblVendorRate.TrunkID = blockCode.TrunkID
						LEFT OUTER JOIN tblVendorBlocking AS blockCountry    ON tblRate.CountryID = blockCountry.CountryId
																	  AND tblVendorRate.AccountId = blockCountry.AccountId
																	  AND tblVendorRate.TrunkID = blockCountry.TrunkID
						
						,(SELECT @row_num := 1,  @prev_AccountId := '',@prev_TrunkID := '', @prev_RateId := '', @prev_EffectiveDate := '') x
					 
			  WHERE      
						(
					  		(p_EffectiveRate = 'now' AND EffectiveDate <= NOW()) 
								OR 
							(p_EffectiveRate = 'future' AND EffectiveDate > NOW())
							  	OR 
							(p_EffectiveRate = 'effective' AND EffectiveDate <= p_EffectiveDate)
						)
						AND tblAccount.IsVendor = 1
						AND tblAccount.Status = 1
						AND tblAccount.CurrencyId is not NULL
						AND tblVendorRate.TrunkID = v_trunk_
						AND blockCode.RateId IS NULL
					   AND blockCountry.CountryId IS NULL
						AND ( @IncludeAccountIds = NULL
							  OR ( @IncludeAccountIds IS NOT NULL
								   AND FIND_IN_SET(tblVendorRate.AccountId,@IncludeAccountIds) > 0                    
								 )
							)
				ORDER BY tblVendorRate.AccountId, tblVendorRate.TrunkID, tblVendorRate.RateId, tblVendorRate.EffectiveDate DESC
		) tbl
		order by Code asc;
		
      INSERT INTO tmp_VendorCurrentRates_ 
	  Select AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference 
      FROM (
			  SELECT * ,
				@row_num := IF(@prev_AccountId = AccountID AND @prev_TrunkID = TrunkID AND @prev_RateId = RateID AND @prev_EffectiveDate >= EffectiveDate, @row_num + 1, 1) AS RowID,
				@prev_AccountId := AccountID,
				@prev_TrunkID := TrunkID,
				@prev_RateId := RateID,
				@prev_EffectiveDate := EffectiveDate
			  FROM tmp_VendorCurrentRates1_
			  ,(SELECT @row_num := 1,  @prev_AccountId := '',@prev_TrunkID := '', @prev_RateId := '', @prev_EffectiveDate := '') x
           ORDER BY AccountId, TrunkID, RateId, EffectiveDate DESC	
		) tbl
		 WHERE RowID = 1 
		order by Code asc;
		
		
		 
			
			
	
	
     
     
     
           
		
		
		insert into tmp_all_code_ (RowCode,Code,RowNo)
               select RowCode , loopCode,RowNo
               from (
                    select   RowCode , loopCode,
                    @RowNo := ( CASE WHEN (@prev_Code  = tbl1.RowCode  ) THEN @RowNo + 1
                                   ELSE 1
                                   END
                                
                              )      as RowNo,
                    @prev_Code := tbl1.RowCode
                    from (
							select distinct Code as RowCode, Code as  loopCode from
							tmp_VendorCurrentRates_ 
                    ) tbl1
                    , ( Select @RowNo := 0 ) x
                ) tbl order by RowCode desc,  LENGTH(loopCode) DESC ;
		
		
		
        

	 
	 
     


                                 
                                          
 
		                        
								
								insert into tmp_VendorRate_
                                       select
									 AccountId ,
									 AccountName ,
									 Code ,
									 Rate ,
         						     ConnectionFee,                                                           
									 EffectiveDate ,
									 Description ,
									 Preference,
									 Code as RowCode
							   from tmp_VendorCurrentRates_;
							   

		
			
		
		
	  WHILE v_pointer_ <= v_rowCount_
	    DO

             SET v_rateRuleId_ = (SELECT rateruleid FROM tmp_Raterules_ rr WHERE rr.RowNo = v_pointer_);
           

		INSERT INTO tmp_Rates2_ (code,rate,ConnectionFee)
		select  code,rate,ConnectionFee from tmp_Rates_;

		
		
		truncate tmp_final_VendorRate_;
                               
		IF( v_Use_Preference_ = 0 )
     	THEN
     			
			insert into tmp_final_VendorRate_
			SELECT
			     AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
					  vr.AccountId ,
				     vr.AccountName ,
				     vr.Code ,
				     vr.Rate ,
                 vr.ConnectionFee,
				     vr.EffectiveDate ,
				     vr.Description ,
				     vr.Preference,
				     vr.RowCode,
   			         @rank := CASE WHEN ( @prev_RowCode = vr.RowCode  AND @prev_Rate <=  vr.Rate ) THEN @rank+1
                                      
						   ELSE 
						   1
						   END 
				  		  AS FinalRankNumber,
				  	@prev_RowCode  := vr.RowCode,
					@prev_Rate  := vr.Rate
					from (
								select tmpvr.*
							  from tmp_VendorRate_  tmpvr
							  inner JOIN tmp_Raterules_ rr ON rr.RateRuleId = v_rateRuleId_ and tmpvr.RowCode LIKE (REPLACE(rr.code,'*', '%%')) 
			     			  inner JOIN tblRateRuleSource rrs ON  rrs.RateRuleId = rr.rateruleid  and rrs.AccountId = tmpvr.AccountId
					) vr
					,(SELECT @rank := 0 , @prev_RowCode := '' , @prev_Rate := 0  ) x 
				 	order by vr.RowCode,vr.Rate,vr.AccountId ASC
				 
				) tbl1
				where FinalRankNumber <= v_RatePosition_;
				 
		 ELSE 
		 
		 	 insert into tmp_final_VendorRate_
			SELECT
				AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
     				 	vr.AccountId ,
				     vr.AccountName ,
				     vr.Code ,
				     vr.Rate ,
                 vr.ConnectionFee,
				     vr.EffectiveDate ,
				     vr.Description ,
				     vr.Preference,
				     vr.RowCode,
                              @preference_rank := CASE WHEN (@prev_Code  = vr.RowCode  AND @prev_Preference > vr.Preference  )   THEN @preference_rank + 1 
                                                       WHEN (@prev_Code  = vr.RowCode  AND @prev_Preference = vr.Preference AND @prev_Rate <= vr.Rate) THEN @preference_rank + 1 
                                                      
                                                       ELSE 1 END AS FinalRankNumber,
                              @prev_Code := vr.RowCode,
                              @prev_Preference := vr.Preference,
                              @prev_Rate := vr.Rate
					from (
						select tmpvr.*
					  from tmp_VendorRate_  tmpvr
					  inner JOIN tmp_Raterules_ rr ON rr.RateRuleId = v_rateRuleId_ and tmpvr.RowCode LIKE (REPLACE(rr.code,'*', '%%')) 
	     			  inner JOIN tblRateRuleSource rrs ON  rrs.RateRuleId = rr.rateruleid  and rrs.AccountId = tmpvr.AccountId
						) vr
               
                          ,(SELECT @preference_rank := 0 , @prev_Code := ''  , @prev_Preference := 5,  @prev_Rate := 0) x
     			 	 order by vr.RowCode ASC ,vr.Preference DESC ,vr.Rate ASC ,vr.AccountId ASC
                         
				) tbl1
				where FinalRankNumber <= v_RatePosition_;
		 
		 END IF;
		 
		 
		 
		truncate   tmp_VRatesstage2_;
		
	               INSERT INTO tmp_VRatesstage2_         
     			SELECT
     				vr.RowCode,
     				vr.code,
     				vr.rate,
     				vr.ConnectionFee,
                         vr.FinalRankNumber
     			FROM tmp_final_VendorRate_ vr
     			 left join tmp_Rates2_ rate on rate.Code = vr.RowCode 
     			WHERE  rate.code is null
                   order by vr.FinalRankNumber desc ;

			
		 
	        IF v_Average_ = 0 
	        THEN              
                                   insert into tmp_dupVRatesstage2_
                                   SELECT RowCode , MAX(FinalRankNumber) AS MaxFinalRankNumber
                                           FROM tmp_VRatesstage2_ GROUP BY RowCode;

                              truncate tmp_Vendorrates_stage3_;
                              INSERT INTO tmp_Vendorrates_stage3_ 
                              select  vr.RowCode as RowCode , vr.rate as rate , vr.ConnectionFee as  ConnectionFee
                              from tmp_VRatesstage2_ vr
                              INNER JOIN tmp_dupVRatesstage2_ vr2
                                ON (vr.RowCode = vr2.RowCode AND  vr.FinalRankNumber = vr2.FinalRankNumber);

                    INSERT IGNORE INTO tmp_Rates_
                   SELECT
	                    RowCode,
	                    IFNULL((SELECT 
	                            CASE WHEN vRate.rate  BETWEEN minrate AND maxrate THEN vRate.rate
	                                + (CASE WHEN addmargin LIKE '%p' THEN ((CAST(REPLACE(addmargin, 'p', '') AS DECIMAL(18, 2)) / 100) * vRate.rate) ELSE addmargin
	                                END) ELSE vRate.rate
	                            END
	                        FROM tblRateRuleMargin 	                        
	                        WHERE rateruleid = v_rateRuleId_ 
									and vRate.rate  BETWEEN minrate AND maxrate LIMIT 1
							),vRate.Rate) as Rate,
	                	ConnectionFee
	                	FROM tmp_Vendorrates_stage3_ vRate;
                                   
                 			 
						 
	         ELSE 

	           INSERT IGNORE INTO tmp_Rates_
					SELECT
	                    RowCode,
	                    IFNULL((SELECT 
	                            CASE WHEN vRate.rate  BETWEEN minrate AND maxrate THEN vRate.rate
	                                + (CASE WHEN addmargin LIKE '%p' THEN ((CAST(REPLACE(addmargin, 'p', '') AS DECIMAL(18, 2)) / 100) * vRate.rate) ELSE addmargin
	                                END) ELSE vRate.rate
	                            END
	                        FROM tblRateRuleMargin 	                        
	                        WHERE rateruleid = v_rateRuleId_ 
									and vRate.rate  BETWEEN minrate AND maxrate LIMIT 1
							),vRate.Rate) as Rate,
	                	ConnectionFee
	                FROM ( 
								select RowCode,
				                    AVG(Rate) as Rate,
				                    AVG(ConnectionFee) as ConnectionFee
								 	from tmp_VRatesstage2_
							 		group by RowCode 
				     )  vRate;
				  
				  
				  
				  
	        END IF;
	        
	       	
	         SET v_pointer_ = v_pointer_ + 1;
			 
			
	     END WHILE;	  
     
  
        START TRANSACTION;

	    IF p_RateTableId = -1
	    THEN
	
	        INSERT INTO tblRateTable (CompanyId, RateTableName, RateGeneratorID, TrunkID, CodeDeckId,CurrencyID)
	            VALUES (v_CompanyId_, p_rateTableName, p_RateGeneratorId, v_trunk_, v_codedeckid_,v_CurrencyID_);
	
	        SET p_RateTableId = LAST_INSERT_ID();
	
	        INSERT INTO tblRateTableRate (RateID,
	        RateTableId,
	        Rate,
	        EffectiveDate,
	        PreviousRate,
	        Interval1,
	        IntervalN,
	        ConnectionFee
	        )
	            SELECT DISTINCT
	                RateId,
	                p_RateTableId,
	                 Rate,	               
	                p_EffectiveDate,
	                Rate,
	                Interval1,
	                IntervalN,
	                ConnectionFee
	            FROM tmp_Rates_ rate
	            INNER JOIN tblRate
	                ON rate.code  = tblRate.Code 
	            WHERE tblRate.CodeDeckId = v_codedeckid_;
	
	    ELSE
				
			 IF p_delete_exiting_rate = 1
	         THEN
			 	 DELETE tblRateTableRate
		            FROM tblRateTableRate 
		        WHERE tblRateTableRate.RateTableId = p_RateTableId;
		     END IF;	
			
	        INSERT INTO tblRateTableRate (RateID,
	        RateTableId,
	        Rate,
	        EffectiveDate,
	        PreviousRate,
	        Interval1,
	        IntervalN,
	        ConnectionFee
	        )
	            SELECT DISTINCT
	                tblRate.RateId,
	                p_RateTableId RateTableId,
	                rate.Rate,
	                p_EffectiveDate EffectiveDate,
	                rate.Rate,
	                tblRate.Interval1,
	                tblRate.IntervalN,
	                rate.ConnectionFee
	            FROM tmp_Rates_ rate
	            INNER JOIN tblRate 
	                ON rate.code  = tblRate.Code 
	            LEFT JOIN tblRateTableRate tbl1 
	                ON tblRate.RateId = tbl1.RateId
	                AND tbl1.RateTableId = p_RateTableId
	            LEFT JOIN tblRateTableRate tbl2 
	            ON tblRate.RateId = tbl2.RateId
	            and tbl2.EffectiveDate = p_EffectiveDate
	            AND tbl2.RateTableId = p_RateTableId
	            WHERE  (    tbl1.RateTableRateID IS NULL 
	                    OR
	                    (
	                        tbl2.RateTableRateID IS NULL 
	                        AND  tbl1.EffectiveDate != p_EffectiveDate                       
	                        
	                    )
	                 )
	            AND tblRate.CodeDeckId = v_codedeckid_;
	
	        UPDATE tblRateTableRate
	        INNER JOIN tblRate 
	            ON tblRate.RateId = tblRateTableRate.RateId
	            AND tblRateTableRate.RateTableId = p_RateTableId
	            AND tblRateTableRate.EffectiveDate = p_EffectiveDate
	         INNER JOIN tmp_Rates_ as rate
	          ON  rate.code  = tblRate.Code 
	          SET tblRateTableRate.PreviousRate = tblRateTableRate.Rate,
	            tblRateTableRate.EffectiveDate = p_EffectiveDate,
	            tblRateTableRate.Rate = rate.Rate,
	            tblRateTableRate.ConnectionFee = rate.ConnectionFee,
	            tblRateTableRate.updated_at = NOW(),
	            tblRateTableRate.ModifiedBy = 'RateManagementService',
	            tblRateTableRate.Interval1 = tblRate.Interval1,
	            tblRateTableRate.IntervalN = tblRate.IntervalN
	        WHERE tblRate.CodeDeckId = v_codedeckid_
	        AND rate.rate != tblRateTableRate.Rate;
	
	        DELETE tblRateTableRate
	            FROM tblRateTableRate 
	        WHERE tblRateTableRate.RateTableId = p_RateTableId
	            AND RateId NOT IN (SELECT DISTINCT
	                    RateId
	                FROM tmp_Rates_ rate
	                INNER JOIN tblRate 
	                    ON rate.code  = tblRate.Code 
	                WHERE tblRate.CodeDeckId = v_codedeckid_)
	            AND tblRateTableRate.EffectiveDate = p_EffectiveDate;	
	
	
	    END IF;


		UPDATE tblRateTable
	   SET RateGeneratorID = p_RateGeneratorId,
		  TrunkID = v_trunk_,
		  CodeDeckId = v_codedeckid_,
		  updated_at = now()
		WHERE RateTableID = p_RateTableId;
     
      SELECT p_RateTableId as RateTableID;
     
    	CALL prc_WSJobStatusUpdate(p_jobId, 'S', 'RateTable Created Successfully', '');
    
    COMMIT;  
    

    	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    	


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGenerateRateTableWithPrefix_07_11_2016` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGenerateRateTableWithPrefix_07_11_2016`(IN `p_jobId` INT
, IN `p_RateGeneratorId` INT
, IN `p_RateTableId` INT
, IN `p_rateTableName` VARCHAR(200) 
, IN `p_EffectiveDate` VARCHAR(10))
GenerateRateTable:BEGIN   		
   
    
	DECLARE v_RTRowCount_ INT;       
	DECLARE v_RatePosition_ INT;
	DECLARE v_Use_Preference_ INT;
	DECLARE v_CurrencyID_ INT;
	DECLARE v_CompanyCurrencyID_ INT;
	DECLARE v_Average_ TINYINT;  
	DECLARE v_CompanyId_ INT;
	DECLARE v_codedeckid_ INT;
	DECLARE v_trunk_ INT; 
	DECLARE v_rateRuleId_ INT;
	DECLARE v_RateGeneratorName_ VARCHAR(200);
	DECLARE v_pointer_ INT ;
	DECLARE v_rowCount_ INT ;

	
	
	DECLARE v_tmp_code_cnt int ;
	DECLARE v_tmp_code_pointer int;
	DECLARE v_p_code varchar(50);
	DECLARE v_Codlen_ int;
	DECLARE v_p_code__ VARCHAR(50);
	DECLARE v_Commit int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN      
             show warnings;
		   ROLLBACK;	  
		   CALL prc_WSJobStatusUpdate(p_jobId, 'F', 'RateTable generation failed', ''); 
               		   
    END; 
	
     SET @@session.collation_connection='utf8_unicode_ci';
     SET @@session.character_set_client='utf8';
     
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
   

    SET p_EffectiveDate = CAST(p_EffectiveDate AS DATE);
       
    
    
    	

		DROP TEMPORARY TABLE IF EXISTS tmp_Rates_;
		CREATE TEMPORARY TABLE tmp_Rates_  (
		  code VARCHAR(50) COLLATE utf8_unicode_ci,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates_code (`code`) 
		);
		DROP TEMPORARY TABLE IF EXISTS tmp_Rates2_;
		CREATE TEMPORARY TABLE tmp_Rates2_  (
		  code VARCHAR(50) COLLATE utf8_unicode_ci,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates2_code (`code`)
		);


		DROP TEMPORARY TABLE IF EXISTS tmp_Codedecks_;
		CREATE TEMPORARY TABLE tmp_Codedecks_ (
        CodeDeckId INT
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Raterules_;

     	CREATE TEMPORARY TABLE tmp_Raterules_  (
        rateruleid INT,
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        RowNo INT,
        INDEX tmp_Raterules_code (`code`),
        INDEX tmp_Raterules_rateruleid (`rateruleid`),
        INDEX tmp_Raterules_RowNo (`RowNo`)
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_;
     	CREATE TEMPORARY TABLE tmp_Vendorrates_  (
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        AccountId INT,
        RowNo INT,
        PreferenceRank INT,
        INDEX tmp_Vendorrates_code (`code`),
        INDEX tmp_Vendorrates_rate (`rate`)
    	);
    	
    	
    	DROP TEMPORARY TABLE IF EXISTS tmp_VRatesstage2_;
     	CREATE TEMPORARY TABLE tmp_VRatesstage2_  (
		RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        FinalRankNumber int,
        INDEX tmp_Vendorrates_stage2__code (`RowCode`)
    	);
     DROP TEMPORARY TABLE IF EXISTS tmp_dupVRatesstage2_;
     	CREATE TEMPORARY TABLE tmp_dupVRatesstage2_  (
		RowCode VARCHAR(50)  COLLATE utf8_unicode_ci,
        FinalRankNumber int,
        INDEX tmp_dupVendorrates_stage2__code (`RowCode`)
    	);
     DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_stage3_;
     CREATE TEMPORARY TABLE tmp_Vendorrates_stage3_  (
        RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        INDEX tmp_Vendorrates_stage2__code (`RowCode`)
    	);    	
    	
     	DROP TEMPORARY TABLE IF EXISTS tmp_code_;
     	CREATE TEMPORARY TABLE tmp_code_  (
        code VARCHAR(50) COLLATE utf8_unicode_ci,
        INDEX tmp_code_code (`code`)
    	);

     DROP TEMPORARY TABLE IF EXISTS tmp_all_code_;
  CREATE TEMPORARY TABLE tmp_all_code_ ( 
     RowCode  varchar(50) COLLATE utf8_unicode_ci,
     Code  varchar(50) COLLATE utf8_unicode_ci,
     RowNo int,
     INDEX Index2 (Code)
   );

 

      DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage_;
       CREATE TEMPORARY TABLE tmp_VendorRate_stage_ (
          RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
          AccountId INT ,
          AccountName VARCHAR(100) ,
          Code VARCHAR(50) COLLATE utf8_unicode_ci,
          Rate DECIMAL(18,6) ,
          ConnectionFee DECIMAL(18,6) ,
          EffectiveDate DATETIME ,
          Description VARCHAR(255),
          Preference INT,
          MaxMatchRank int ,
          prev_prev_RowCode VARCHAR(50),
          prev_AccountID int
        );

 DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_;
  CREATE TEMPORARY TABLE tmp_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) COLLATE utf8_unicode_ci,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50) COLLATE utf8_unicode_ci  
   );
   DROP TEMPORARY TABLE IF EXISTS tmp_final_VendorRate_;
  CREATE TEMPORARY TABLE tmp_final_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) COLLATE utf8_unicode_ci,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50) COLLATE utf8_unicode_ci,
     FinalRankNumber int,
		INDEX IX_CODE (RowCode)
   );
   
   DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates_;
   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates_(
			AccountId int,
            AccountName varchar(200),
			Code varchar(50) COLLATE utf8_unicode_ci,
			Description varchar(200) ,
			Rate DECIMAL(18,6) ,
               ConnectionFee DECIMAL(18,6) , 
			EffectiveDate date,
			TrunkID int,
			CountryID int,
			RateID int,
			Preference int,
			INDEX IX_CODE (Code)			
	);
	DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates1_;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates1_(
		AccountId int,
		AccountName varchar(200),
		Code varchar(50),
		Description varchar(200),
		Rate DECIMAL(18,6) ,
		ConnectionFee DECIMAL(18,6) , 
		EffectiveDate date,
		TrunkID int,
		CountryID int,
		RateID int,
		Preference int,
		INDEX IX_Code (Code),
		INDEX tmp_VendorCurrentRates_AccountId (`AccountId`,`TrunkID`,`RateId`,`EffectiveDate`)
	);

    	SELECT CurrencyID INTO v_CurrencyID_ FROM  tblRateGenerator WHERE RateGeneratorId = p_RateGeneratorId;

    	SELECT
        UsePreference,  
        rateposition,
        companyid ,
        CodeDeckId,
        tblRateGenerator.TrunkID,
        tblRateGenerator.UseAverage  ,
        tblRateGenerator.RateGeneratorName INTO v_Use_Preference_, v_RatePosition_, v_CompanyId_, v_codedeckid_, v_trunk_, v_Average_, v_RateGeneratorName_
    	FROM tblRateGenerator
    	WHERE RateGeneratorId = p_RateGeneratorId;


	SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = v_CompanyId_;


      

    	INSERT INTO tmp_Raterules_
	   SELECT
            rateruleid,
            tblRateRule.code,
            @row_num := @row_num+1 AS RowID
        FROM tblRateRule,(SELECT @row_num := 0) x
        WHERE rategeneratorid = p_RateGeneratorId
        ORDER BY tblRateRule.Code DESC;

	     INSERT INTO tmp_Codedecks_
	        SELECT DISTINCT
	            tblVendorTrunk.CodeDeckId
	        FROM tblRateRule
	        INNER JOIN tblRateRuleSource
	            ON tblRateRule.RateRuleId = tblRateRuleSource.RateRuleId
	        INNER JOIN tblAccount
	            ON tblAccount.AccountID = tblRateRuleSource.AccountId and tblAccount.IsVendor = 1
			JOIN tblVendorTrunk
	                ON tblAccount.AccountId = tblVendorTrunk.AccountID
	                AND  tblVendorTrunk.TrunkID = v_trunk_
	                AND tblVendorTrunk.Status = 1
	        WHERE RateGeneratorId = p_RateGeneratorId;

	    SET v_pointer_ = 1;
	    SET v_rowCount_ = (SELECT COUNT(distinct Code ) FROM tmp_Raterules_);

		
		
		
		
		
		
		insert into tmp_code_
		SELECT
			tblRate.code
		FROM tblRate
		JOIN tmp_Codedecks_ cd
			ON tblRate.CodeDeckId = cd.CodeDeckId
		JOIN tmp_Raterules_ rr
			ON tblRate.code LIKE (REPLACE(rr.code,'*', '%%'))
		Order by tblRate.code ;
			

		
		

 
  
	
          
		SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = v_CompanyId_;
		SET @IncludeAccountIds = (SELECT GROUP_CONCAT(AccountId) from tblRateRule rr inner join  tblRateRuleSource rrs on rr.RateRuleId = rrs.RateRuleId where rr.RateGeneratorId = p_RateGeneratorId ) ;
     
	
     
	 INSERT INTO tmp_VendorCurrentRates1_ 
	 Select DISTINCT AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference					
      FROM (
				SELECT  tblVendorRate.AccountId,tblAccount.AccountName, tblRate.Code, tblRate.Description, 
				 CASE WHEN  tblAccount.CurrencyId = v_CurrencyID_
								THEN
									   tblVendorRate.Rate
					 WHEN  v_CompanyCurrencyID_ = v_CurrencyID_  
							 THEN
								  (
								   ( tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ) )
								 )
							  ELSE 
							  (
										
								  (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = v_CurrencyID_ and  CompanyID = v_CompanyId_ )
									* (tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ))
							  )
							 END
								as  Rate,
								 ConnectionFee,
			  DATE_FORMAT (tblVendorRate.EffectiveDate, '%Y-%m-%d') AS EffectiveDate, 
			    tblVendorRate.TrunkID, tblRate.CountryID, tblRate.RateID,IFNULL(vp.Preference, 5) AS Preference,
				@row_num := IF(@prev_AccountId = tblVendorRate.AccountID AND @prev_TrunkID = tblVendorRate.TrunkID AND @prev_RateId = tblVendorRate.RateID AND @prev_EffectiveDate >= tblVendorRate.EffectiveDate, @row_num + 1, 1) AS RowID,
				@prev_AccountId := tblVendorRate.AccountID,
				@prev_TrunkID := tblVendorRate.TrunkID,
				@prev_RateId := tblVendorRate.RateID,
				@prev_EffectiveDate := tblVendorRate.EffectiveDate
			  FROM      tblVendorRate
			  
					 Inner join tblVendorTrunk vt on vt.CompanyID = v_CompanyId_ AND vt.AccountID = tblVendorRate.AccountID and 
							
							  vt.Status =  1 and vt.TrunkID =  v_trunk_  
                              inner join tmp_Codedecks_ tcd on vt.CodeDeckId = tcd.CodeDeckId
						INNER JOIN tblAccount   ON  tblAccount.CompanyID = v_CompanyId_ AND tblVendorRate.AccountId = tblAccount.AccountID and tblAccount.IsVendor = 1
						INNER JOIN tblRate ON tblRate.CompanyID = v_CompanyId_  AND tblRate.CodeDeckId = vt.CodeDeckId  AND    tblVendorRate.RateId = tblRate.RateID
						INNER JOIN tmp_code_ tcode ON tcode.Code  = tblRate.Code 
						LEFT JOIN tblVendorPreference vp
										 ON vp.AccountId = tblVendorRate.AccountId
										 AND vp.TrunkID = tblVendorRate.TrunkID
										 AND vp.RateId = tblVendorRate.RateId
						LEFT OUTER JOIN tblVendorBlocking AS blockCode   ON tblVendorRate.RateId = blockCode.RateId
																	  AND tblVendorRate.AccountId = blockCode.AccountId
																	  AND tblVendorRate.TrunkID = blockCode.TrunkID
						LEFT OUTER JOIN tblVendorBlocking AS blockCountry    ON tblRate.CountryID = blockCountry.CountryId
																	  AND tblVendorRate.AccountId = blockCountry.AccountId
																	  AND tblVendorRate.TrunkID = blockCountry.TrunkID
						
						,(SELECT @row_num := 1,  @prev_AccountId := '',@prev_TrunkID := '', @prev_RateId := '', @prev_EffectiveDate := '') x
					 
			  WHERE      
						( EffectiveDate <= NOW() )
						AND tblAccount.IsVendor = 1
						AND tblAccount.Status = 1
						AND tblAccount.CurrencyId is not NULL
						AND tblVendorRate.TrunkID = v_trunk_
						AND blockCode.RateId IS NULL
					   AND blockCountry.CountryId IS NULL
						AND ( @IncludeAccountIds = NULL
							  OR ( @IncludeAccountIds IS NOT NULL
								   AND FIND_IN_SET(tblVendorRate.AccountId,@IncludeAccountIds) > 0                    
								 )
							)
				ORDER BY tblVendorRate.AccountId, tblVendorRate.TrunkID, tblVendorRate.RateId, tblVendorRate.EffectiveDate DESC
		) tbl
		order by Code asc;
		
      INSERT INTO tmp_VendorCurrentRates_ 
	  Select AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference 
      FROM (
			  SELECT * ,
				@row_num := IF(@prev_AccountId = AccountID AND @prev_TrunkID = TrunkID AND @prev_RateId = RateID AND @prev_EffectiveDate >= EffectiveDate, @row_num + 1, 1) AS RowID,
				@prev_AccountId := AccountID,
				@prev_TrunkID := TrunkID,
				@prev_RateId := RateID,
				@prev_EffectiveDate := EffectiveDate
			  FROM tmp_VendorCurrentRates1_
			  ,(SELECT @row_num := 1,  @prev_AccountId := '',@prev_TrunkID := '', @prev_RateId := '', @prev_EffectiveDate := '') x
           ORDER BY AccountId, TrunkID, RateId, EffectiveDate DESC	
		) tbl
		 WHERE RowID = 1 
		order by Code asc;
		
	
	
     
     
     
           
		
		
		insert into tmp_all_code_ (RowCode,Code,RowNo)
               select RowCode , loopCode,RowNo
               from (
                    select   RowCode , loopCode,
                    @RowNo := ( CASE WHEN (@prev_Code  = tbl1.RowCode  ) THEN @RowNo + 1
                                   ELSE 1
                                   END
                                
                              )      as RowNo,
                    @prev_Code := tbl1.RowCode
                    from (
							select distinct Code as RowCode, Code as  loopCode from
							tmp_VendorCurrentRates_ 
                    ) tbl1
                    , ( Select @RowNo := 0 ) x
                ) tbl order by RowCode desc,  LENGTH(loopCode) DESC ;
		
		
		
        

	 
	 
     


                                 
                                          
 
		                        
								
								insert into tmp_VendorRate_
                                       select
									 AccountId ,
									 AccountName ,
									 Code ,
									 Rate ,
         						     ConnectionFee,                                                           
									 EffectiveDate ,
									 Description ,
									 Preference,
									 Code as RowCode
							   from tmp_VendorCurrentRates_;
							   

		
			
		
		
	  WHILE v_pointer_ <= v_rowCount_
	    DO

             SET v_rateRuleId_ = (SELECT rateruleid FROM tmp_Raterules_ rr WHERE rr.RowNo = v_pointer_);
           

		INSERT INTO tmp_Rates2_ (code,rate,ConnectionFee)
		select  code,rate,ConnectionFee from tmp_Rates_;

		
		
		truncate tmp_final_VendorRate_;
                               
		IF( v_Use_Preference_ = 0 )
     	THEN
     			
			insert into tmp_final_VendorRate_
			SELECT
			     AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
					  vr.AccountId ,
				     vr.AccountName ,
				     vr.Code ,
				     vr.Rate ,
                 vr.ConnectionFee,
				     vr.EffectiveDate ,
				     vr.Description ,
				     vr.Preference,
				     vr.RowCode,
   			         @rank := CASE WHEN ( @prev_RowCode = vr.RowCode  AND @prev_Rate <=  vr.Rate ) THEN @rank+1
                                      
						   ELSE 
						   1
						   END 
				  		  AS FinalRankNumber,
				  	@prev_RowCode  := vr.RowCode,
					@prev_Rate  := vr.Rate
					from (
								select tmpvr.*
							  from tmp_VendorRate_  tmpvr
							  inner JOIN tmp_Raterules_ rr ON rr.RateRuleId = v_rateRuleId_ and tmpvr.RowCode LIKE (REPLACE(rr.code,'*', '%%')) 
			     			  inner JOIN tblRateRuleSource rrs ON  rrs.RateRuleId = rr.rateruleid  and rrs.AccountId = tmpvr.AccountId
					) vr
					,(SELECT @rank := 0 , @prev_RowCode := '' , @prev_Rate := 0  ) x 
				 	order by vr.RowCode,vr.Rate,vr.AccountId ASC
				 
				) tbl1
				where FinalRankNumber <= v_RatePosition_;
				 
		 ELSE 
		 
		 	 insert into tmp_final_VendorRate_
			SELECT
				AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
     				 	vr.AccountId ,
				     vr.AccountName ,
				     vr.Code ,
				     vr.Rate ,
                 vr.ConnectionFee,
				     vr.EffectiveDate ,
				     vr.Description ,
				     vr.Preference,
				     vr.RowCode,
                              @preference_rank := CASE WHEN (@prev_Code  = vr.RowCode  AND @prev_Preference > vr.Preference  )   THEN @preference_rank + 1 
                                                       WHEN (@prev_Code  = vr.RowCode  AND @prev_Preference = vr.Preference AND @prev_Rate <= vr.Rate) THEN @preference_rank + 1 
                                                      
                                                       ELSE 1 END AS FinalRankNumber,
                              @prev_Code := vr.RowCode,
                              @prev_Preference := vr.Preference,
                              @prev_Rate := vr.Rate
					from (
						select tmpvr.*
					  from tmp_VendorRate_  tmpvr
					  inner JOIN tmp_Raterules_ rr ON rr.RateRuleId = v_rateRuleId_ and tmpvr.RowCode LIKE (REPLACE(rr.code,'*', '%%')) 
	     			  inner JOIN tblRateRuleSource rrs ON  rrs.RateRuleId = rr.rateruleid  and rrs.AccountId = tmpvr.AccountId
						) vr
               
                          ,(SELECT @preference_rank := 0 , @prev_Code := ''  , @prev_Preference := 5,  @prev_Rate := 0) x
     			 	 order by vr.RowCode ASC ,vr.Preference DESC ,vr.Rate ASC ,vr.AccountId ASC
                         
				) tbl1
				where FinalRankNumber <= v_RatePosition_;
		 
		 END IF;
		 
		 
		 
		truncate   tmp_VRatesstage2_;
		
	               INSERT INTO tmp_VRatesstage2_         
     			SELECT
     				vr.RowCode,
     				vr.code,
     				vr.rate,
     				vr.ConnectionFee,
                         vr.FinalRankNumber
     			FROM tmp_final_VendorRate_ vr
     			 left join tmp_Rates2_ rate on rate.Code = vr.RowCode 
     			WHERE  rate.code is null
                   order by vr.FinalRankNumber desc ;

			
		 
	        IF v_Average_ = 0 
	        THEN              
                                   insert into tmp_dupVRatesstage2_
                                   SELECT RowCode , MAX(FinalRankNumber) AS MaxFinalRankNumber
                                           FROM tmp_VRatesstage2_ GROUP BY RowCode;

                              truncate tmp_Vendorrates_stage3_;
                              INSERT INTO tmp_Vendorrates_stage3_ 
                              select  vr.RowCode as RowCode , vr.rate as rate , vr.ConnectionFee as  ConnectionFee
                              from tmp_VRatesstage2_ vr
                              INNER JOIN tmp_dupVRatesstage2_ vr2
                                ON (vr.RowCode = vr2.RowCode AND  vr.FinalRankNumber = vr2.FinalRankNumber);

                    INSERT INTO tmp_Rates_
                   SELECT
	                    RowCode,
	                    IFNULL((SELECT 
	                            CASE WHEN vRate.rate  BETWEEN minrate AND maxrate THEN vRate.rate
	                                + (CASE WHEN addmargin LIKE '%p' THEN ((CAST(REPLACE(addmargin, 'p', '') AS DECIMAL(18, 2)) / 100) * vRate.rate) ELSE addmargin
	                                END) ELSE vRate.rate
	                            END
	                        FROM tblRateRuleMargin 	                        
	                        WHERE rateruleid = v_rateRuleId_ 
									and vRate.rate  BETWEEN minrate AND maxrate LIMIT 1
							),vRate.Rate) as Rate,
	                	ConnectionFee
	                	FROM tmp_Vendorrates_stage3_ vRate;
                                   
                 			 
						 
	         ELSE 

	           INSERT INTO tmp_Rates_
					SELECT
	                    RowCode,
	                    IFNULL((SELECT 
	                            CASE WHEN vRate.rate  BETWEEN minrate AND maxrate THEN vRate.rate
	                                + (CASE WHEN addmargin LIKE '%p' THEN ((CAST(REPLACE(addmargin, 'p', '') AS DECIMAL(18, 2)) / 100) * vRate.rate) ELSE addmargin
	                                END) ELSE vRate.rate
	                            END
	                        FROM tblRateRuleMargin 	                        
	                        WHERE rateruleid = v_rateRuleId_ 
									and vRate.rate  BETWEEN minrate AND maxrate LIMIT 1
							),vRate.Rate) as Rate,
	                	ConnectionFee
	                FROM ( 
								select RowCode,
				                    AVG(Rate) as Rate,
				                    AVG(ConnectionFee) as ConnectionFee
								 	from tmp_VRatesstage2_
							 		group by RowCode 
				     )  vRate;
				  
				  
				  
				  
	        END IF;
	        
	       	
	         SET v_pointer_ = v_pointer_ + 1;
			 
			
	     END WHILE;	  
     
     
     SELECT DISTINCT
     
          rate.Code,
          p_RateTableId,
           Rate,	               
          p_EffectiveDate,
          Rate,
          Interval1,
          IntervalN,
          ConnectionFee
      FROM tmp_Rates_ rate
      INNER JOIN tblRate
          ON rate.code  = tblRate.Code 
      WHERE tblRate.CodeDeckId = v_codedeckid_
		and rate.Code = '998';
  
       
    

    	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    	


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGenerateRateTable_old-25-4-2016` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGenerateRateTable_old-25-4-2016`(IN `p_jobId` INT
, IN `p_RateGeneratorId` INT
, IN `p_RateTableId` INT
, IN `p_rateTableName` VARCHAR(200) 
, IN `p_EffectiveDate` VARCHAR(10))
GenerateRateTable:BEGIN    		
   
    
	DECLARE v_RTRowCount_ INT;       
	DECLARE v_RatePosition_ INT;
	DECLARE v_Use_Preference_ INT;
	DECLARE v_CurrencyID_ INT;
	DECLARE v_CompanyCurrencyID_ INT;
	DECLARE v_Average_ TINYINT;  
	DECLARE v_CompanyId_ INT;
	DECLARE v_codedeckid_ INT;
	DECLARE v_trunk_ INT; 
	DECLARE v_rateRuleId_ INT;
	DECLARE v_RateGeneratorName_ VARCHAR(200);
	DECLARE v_pointer_ INT ;
	DECLARE v_rowCount_ INT ;

	
	
	DECLARE v_tmp_code_cnt int ;
	DECLARE v_tmp_code_pointer int;
	DECLARE v_p_code varchar(50);
	DECLARE v_Codlen_ int;
	DECLARE v_p_code__ VARCHAR(50);
	DECLARE v_Commit int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN      
             show warnings;
		   ROLLBACK;	  
		   CALL prc_WSJobStatusUpdate(p_jobId, 'F', 'RateTable generation failed', ''); 
               		   
    END;
	
     SET @@global.collation_connection='utf8_unicode_ci';
     SET @@local.collation_connection='utf8_unicode_ci';

     SET @@local.character_set_client='utf8';
     SET @@local.character_set_client='utf8';
     
 
	
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
   

    SET p_EffectiveDate = CAST(p_EffectiveDate AS DATE);
       
    
    	IF p_rateTableName IS NOT NULL
	 	THEN


        SET v_RTRowCount_ = (SELECT
                COUNT(*)
            FROM tblRateTable
            WHERE RateTableName = p_rateTableName
            AND CompanyId = (SELECT
                    CompanyId
                FROM tblRateGenerator
                WHERE RateGeneratorID = p_RateGeneratorId));

        IF v_RTRowCount_ > 0
        THEN
            CALL prc_WSJobStatusUpdate  (p_jobId, 'F', 'RateTable Name is already exist, Please try using another RateTable Name', '');
            LEAVE GenerateRateTable;
        END IF;
    	END IF;

		DROP TEMPORARY TABLE IF EXISTS tmp_Rates_;
		CREATE TEMPORARY TABLE tmp_Rates_  (
		  code VARCHAR(50) ,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates_code (`code`) 
		);
		DROP TEMPORARY TABLE IF EXISTS tmp_Rates2_;
		CREATE TEMPORARY TABLE tmp_Rates2_  (
		  code VARCHAR(50) ,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates2_code (`code`)
		);


		DROP TEMPORARY TABLE IF EXISTS tmp_Codedecks_;
		CREATE TEMPORARY TABLE tmp_Codedecks_ (
        CodeDeckId INT
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Raterules_;

     	CREATE TEMPORARY TABLE tmp_Raterules_  (
        rateruleid INT,
        code VARCHAR(50)  ,
        RowNo INT,
        INDEX tmp_Raterules_code (`code`),
        INDEX tmp_Raterules_rateruleid (`rateruleid`),
        INDEX tmp_Raterules_RowNo (`RowNo`)
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_;
     	CREATE TEMPORARY TABLE tmp_Vendorrates_  (
        code VARCHAR(50)  ,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        AccountId INT,
        RowNo INT,
        PreferenceRank INT,
        INDEX tmp_Vendorrates_code (`code`),
        INDEX tmp_Vendorrates_rate (`rate`)
    	);
    	
    	
    	DROP TEMPORARY TABLE IF EXISTS tmp_VRatesstage2_;
     	CREATE TEMPORARY TABLE tmp_VRatesstage2_  (
		RowCode VARCHAR(50)  ,
        code VARCHAR(50)  ,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        FinalRankNumber int,
        INDEX tmp_Vendorrates_stage2__code (`RowCode`)
    	);
     DROP TEMPORARY TABLE IF EXISTS tmp_dupVRatesstage2_;
     	CREATE TEMPORARY TABLE tmp_dupVRatesstage2_  (
		RowCode VARCHAR(50)  ,
        FinalRankNumber int,
        INDEX tmp_dupVendorrates_stage2__code (`RowCode`)
    	);
     DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_stage3_;
     CREATE TEMPORARY TABLE tmp_Vendorrates_stage3_  (
        RowCode VARCHAR(50)  ,
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        INDEX tmp_Vendorrates_stage2__code (`RowCode`)
    	);    	
    	
     	DROP TEMPORARY TABLE IF EXISTS tmp_code_;
     	CREATE TEMPORARY TABLE tmp_code_  (
        code VARCHAR(50)  ,
        INDEX tmp_code_code (`code`)
    	);

     DROP TEMPORARY TABLE IF EXISTS tmp_all_code_;
  CREATE TEMPORARY TABLE tmp_all_code_ ( 
     RowCode  varchar(50)  ,
     Code  varchar(50)  ,
     RowNo int
     
   );

 

      DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage_;
       CREATE TEMPORARY TABLE tmp_VendorRate_stage_ (
          RowCode VARCHAR(50)  ,
          AccountId INT ,
          AccountName VARCHAR(100) ,
          Code VARCHAR(50)  ,
          Rate DECIMAL(18,6) ,
          ConnectionFee DECIMAL(18,6) ,
          EffectiveDate DATETIME ,
          Description VARCHAR(255),
          Preference INT,
          MaxMatchRank int ,
          prev_prev_RowCode VARCHAR(50),
          prev_AccountID int
        );

 DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_;
  CREATE TEMPORARY TABLE tmp_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50)  ,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50)   
   );
   DROP TEMPORARY TABLE IF EXISTS tmp_final_VendorRate_;
  CREATE TEMPORARY TABLE tmp_final_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50)  ,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50) ,
     FinalRankNumber int
   );
   
   DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates_;
   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates_(
			AccountId int,
            AccountName varchar(200),
			Code varchar(50) ,
			Description varchar(200) ,
			Rate DECIMAL(18,6) ,
               ConnectionFee DECIMAL(18,6) , 
			EffectiveDate date,
			TrunkID int,
			CountryID int,
			RateID int,
			Preference int,
			INDEX tmp_VendorCurrentRates_AccountId (`AccountId`,`TrunkID`,`RateId`,`EffectiveDate`)
	);
  DROP TEMPORARY TABLE IF EXISTS tmp_VendorRateByRank_;
  CREATE TEMPORARY TABLE tmp_VendorRateByRank_ (      
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) ,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     rankname INT
   );

     	DROP TEMPORARY TABLE IF EXISTS tmp_SplitRowCodes_;
		CREATE TEMPORARY TABLE tmp_SplitRowCodes_  (
		  code VARCHAR(50) ,
		  INDEX tmp_Rates_code (`code`) 
		);
  
     
    	SELECT CurrencyID INTO v_CurrencyID_ FROM  tblRateGenerator WHERE RateGeneratorId = p_RateGeneratorId;

    	SELECT
        UsePreference,  
        rateposition,
        companyid ,
        CodeDeckId,
        tblRateGenerator.TrunkID,
        tblRateGenerator.UseAverage  ,
        tblRateGenerator.RateGeneratorName INTO v_Use_Preference_, v_RatePosition_, v_CompanyId_, v_codedeckid_, v_trunk_, v_Average_, v_RateGeneratorName_
    	FROM tblRateGenerator
    	WHERE RateGeneratorId = p_RateGeneratorId;


	SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = v_CompanyId_;


      

    	INSERT INTO tmp_Raterules_
	   SELECT
            rateruleid,
            tblRateRule.code,
            @row_num := @row_num+1 AS RowID
        FROM tblRateRule,(SELECT @row_num := 0) x
        WHERE rategeneratorid = p_RateGeneratorId
        ORDER BY tblRateRule.Code DESC;

	     INSERT INTO tmp_Codedecks_
	        SELECT DISTINCT
	            tblVendorTrunk.CodeDeckId
	        FROM tblRateRule
	        INNER JOIN tblRateRuleSource
	            ON tblRateRule.RateRuleId = tblRateRuleSource.RateRuleId
	        INNER JOIN tblAccount
	            ON tblAccount.AccountID = tblRateRuleSource.AccountId
			JOIN tblVendorTrunk
	                ON tblAccount.AccountId = tblVendorTrunk.AccountID
	                AND  tblVendorTrunk.TrunkID = v_trunk_
	                AND tblVendorTrunk.Status = 1
	        WHERE RateGeneratorId = p_RateGeneratorId;

	    SET v_pointer_ = 1;
	    SET v_rowCount_ = (SELECT COUNT(distinct Code ) FROM tmp_Raterules_);

		
		
		
		insert into tmp_code_
          SELECT  DISTINCT LEFT(f.Code, x.RowNo) as loopCode FROM (
          SELECT @RowNo  := @RowNo + 1 as RowNo
          FROM mysql.help_category 
          ,(SELECT @RowNo := 0 ) x
          limit 15
          ) x
          INNER JOIN 
          (SELECT 
                    distinct
	                tblRate.code
	            FROM tblRate
	            JOIN tmp_Codedecks_ cd
	                ON tblRate.CodeDeckId = cd.CodeDeckId
	            JOIN tmp_Raterules_ rr
	                ON tblRate.code LIKE (REPLACE(rr.code,
	                '*', '%%'))
	          Order by tblRate.code 
          ) as f
          ON   x.RowNo   <= LENGTH(f.Code) 
          order by loopCode   desc;


          	
  
	
          
		SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = v_CompanyId_;
		SET @IncludeAccountIds = (SELECT GROUP_CONCAT(AccountId) from tblRateRule rr inner join  tblRateRuleSource rrs on rr.RateRuleId = rrs.RateRuleId where rr.RateGeneratorId = p_RateGeneratorId ) ;
     
	
     
	 INSERT INTO tmp_VendorCurrentRates_ 
	 Select DISTINCT AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference					
      FROM (
				SELECT  tblVendorRate.AccountId,tblAccount.AccountName, tblRate.Code, tblRate.Description, 
				 CASE WHEN  tblAccount.CurrencyId = v_CurrencyID_
								THEN
									   tblVendorRate.Rate
					 WHEN  v_CompanyCurrencyID_ = v_CurrencyID_  
							 THEN
								  (
								   ( tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ) )
								 )
							  ELSE 
							  (
										
								  (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = v_CurrencyID_ and  CompanyID = v_CompanyId_ )
									* (tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ))
							  )
							 END
								as  Rate,
								 ConnectionFee,
			  DATE_FORMAT (tblVendorRate.EffectiveDate, '%Y-%m-%d') AS EffectiveDate, 
			    tblVendorRate.TrunkID, tblRate.CountryID, tblRate.RateID,IFNULL(vp.Preference, 5) AS Preference,
				@row_num := IF(@prev_AccountId = tblVendorRate.AccountID AND @prev_TrunkID = tblVendorRate.TrunkID AND @prev_RateId = tblVendorRate.RateID AND @prev_EffectiveDate >= tblVendorRate.EffectiveDate, @row_num + 1, 1) AS RowID,
				@prev_AccountId := tblVendorRate.AccountID,
				@prev_TrunkID := tblVendorRate.TrunkID,
				@prev_RateId := tblVendorRate.RateID,
				@prev_EffectiveDate := tblVendorRate.EffectiveDate
			  FROM      tblVendorRate
			  
					 Inner join tblVendorTrunk vt on vt.CompanyID = v_CompanyId_ AND vt.AccountID = tblVendorRate.AccountID and 
							
							  vt.Status =  1 and vt.TrunkID =  v_trunk_  
                              inner join tmp_Codedecks_ tcd on vt.CodeDeckId = tcd.CodeDeckId
						INNER JOIN tblAccount   ON  tblAccount.CompanyID = v_CompanyId_ AND tblVendorRate.AccountId = tblAccount.AccountID 
						INNER JOIN tblRate ON tblRate.CompanyID = v_CompanyId_  AND tblRate.CodeDeckId = vt.CodeDeckId  AND    tblVendorRate.RateId = tblRate.RateID
						INNER JOIN tmp_code_ tcode ON tcode.Code  = tblRate.Code 
						LEFT JOIN tblVendorPreference vp
										 ON vp.AccountId = tblVendorRate.AccountId
										 AND vp.TrunkID = tblVendorRate.TrunkID
										 AND vp.RateId = tblVendorRate.RateId
						LEFT OUTER JOIN tblVendorBlocking AS blockCode   ON tblVendorRate.RateId = blockCode.RateId
																	  AND tblVendorRate.AccountId = blockCode.AccountId
																	  AND tblVendorRate.TrunkID = blockCode.TrunkID
						LEFT OUTER JOIN tblVendorBlocking AS blockCountry    ON tblRate.CountryID = blockCountry.CountryId
																	  AND tblVendorRate.AccountId = blockCountry.AccountId
																	  AND tblVendorRate.TrunkID = blockCountry.TrunkID
						
						,(SELECT @row_num := 1,  @prev_AccountId := '',@prev_TrunkID := '', @prev_RateId := '', @prev_EffectiveDate := '') x
					 
			  WHERE      
						( EffectiveDate <= NOW() )
						AND tblAccount.IsVendor = 1
						AND tblAccount.Status = 1
						AND tblAccount.CurrencyId is not NULL
						AND tblVendorRate.TrunkID = v_trunk_
						AND blockCode.RateId IS NULL
					   AND blockCountry.CountryId IS NULL
						AND ( @IncludeAccountIds = NULL
							  OR ( @IncludeAccountIds IS NOT NULL
								   AND FIND_IN_SET(tblVendorRate.AccountId,@IncludeAccountIds) > 0                    
								 )
							)
				ORDER BY tblVendorRate.AccountId, tblVendorRate.TrunkID, tblVendorRate.RateId, tblVendorRate.EffectiveDate DESC	
		) tbl
		 WHERE RowID = 1
		order by Code asc;
		
	
	
     
     
     
          
          
        insert into tmp_all_code_ (RowCode,Code,RowNo)
               select RowCode , loopCode,RowNo
               from (
                    select   RowCode , loopCode,
                    @RowNo := ( CASE WHEN (@prev_Code = tbl1.RowCode  ) THEN @RowNo + 1
                                   ELSE 1
                                   END
                                
                              )      as RowNo,
                    @prev_Code := tbl1.RowCode

                    from (
                              SELECT distinct f.Code as RowCode, LEFT(f.Code, x.RowNo) as loopCode FROM (
                                SELECT @RowNo  := @RowNo + 1 as RowNo
                                FROM mysql.help_category 
                                ,(SELECT @RowNo := 0 ) x
                                limit 15
                              ) x
                              INNER JOIN tmp_code_ AS f
                              ON  x.RowNo   <= LENGTH(f.Code) 
                              order by RowCode desc,  LENGTH(loopCode) DESC 
                    ) tbl1
                    , ( Select @RowNo := 0 ) x
                ) tbl order by RowCode desc,  LENGTH(loopCode) DESC ;


	IF v_Use_Preference_ = 1 THEN

	
	INSERT IGNORE INTO tmp_VendorRateByRank_
	  SELECT
		AccountID,
		AccountName,
		Code,
		Rate,
		ConnectionFee,
		EffectiveDate,
		Description,
		Preference,
		preference_rank
	  FROM (SELECT
		  AccountID,
		  AccountName,
		  Code,
		  Rate,
		  ConnectionFee,
		  EffectiveDate,
		  Description,
		  Preference,
		  @preference_rank := CASE WHEN (@prev_Code  = Code  AND @prev_Preference > Preference  ) THEN @preference_rank + 1 
								   WHEN (@prev_Code = Code  AND @prev_Preference = Preference AND @prev_Rate < Rate) THEN @preference_rank + 1 
								   WHEN (@prev_Code = Code  AND @prev_Preference = Preference AND @prev_Rate = Rate) THEN @preference_rank 
								   ELSE 1 
								   END AS preference_rank,
		  @prev_Code := Code,
		  @prev_Preference := Preference,
		  @prev_Rate := Rate
		FROM tmp_VendorCurrentRates_ AS preference,
			 (SELECT @preference_rank := 0 , @prev_Code := ''  , @prev_Preference := 5,  @prev_Rate := 0) x
		ORDER BY preference.Code ASC, preference.Preference DESC, preference.Rate ASC,preference.AccountId ASC
		   
		 ) tbl
	  WHERE preference_rank <= v_RatePosition_
	  ORDER BY Code, preference_rank;

	ELSE


	
	INSERT IGNORE INTO tmp_VendorRateByRank_
	  SELECT
		AccountID,
		AccountName,
		Code,
		Rate,
		ConnectionFee,
		EffectiveDate,
		Description,
		Preference,
		RateRank
	  FROM (SELECT
		  AccountID,
		  AccountName,
		  Code,
		  Rate,
		  ConnectionFee,
		  EffectiveDate,
		  Description,
		  Preference,
		  @rank := CASE WHEN (@prev_Code = Code  AND @prev_Rate < Rate) THEN @rank + 1 
						WHEN (@prev_Code  = Code  AND @prev_Rate = Rate) THEN @rank 
						ELSE 1 
						END AS RateRank,
		  @prev_Code := Code,
		  @prev_Rate := Rate
		FROM tmp_VendorCurrentRates_ AS rank,
			 (SELECT @rank := 0 , @prev_Code := '' , @prev_Rate := 0) f
		ORDER BY rank.Code ASC, rank.Rate,rank.AccountId ASC) tbl
	  WHERE RateRank <= v_RatePosition_
	  ORDER BY Code, RateRank;

	END IF;	

     


                                 
                                             DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage_1;
                                             CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorRate_stage_1 as (select * from tmp_VendorRate_stage_);
                                             
                                              insert ignore into tmp_VendorRate_stage_1 (
                                                	     RowCode,
                                                  	AccountId ,
                                                  	AccountName ,
                                                  	Code ,
                                                  	Rate ,
                                                  	ConnectionFee,                                                           
                                                  	EffectiveDate , 
                                                  	Description ,
                                                  	Preference
                                             )
                                             SELECT  
                                             distinct                                
                                        	RowCode,
                                        	v.AccountId ,
                                        	v.AccountName ,
                                        	v.Code ,
                                        	v.Rate ,
                                        	v.ConnectionFee,                                                           
                                        	v.EffectiveDate , 
                                        	v.Description ,
                                        	v.Preference
                                             FROM tmp_VendorRateByRank_ v  
                                             left join  tmp_all_code_
                                   			SplitCode   on v.Code = SplitCode.Code 
                                            where  SplitCode.Code is not null and rankname <= v_RatePosition_ 
                                            order by AccountID,SplitCode.RowCode desc ,LENGTH(SplitCode.RowCode), v.Code desc, LENGTH(v.Code)  desc;


                                                 
                                             insert into tmp_VendorRate_stage_
                                             SELECT   
                    						RowCode,
                    						v.AccountId ,
                    						v.AccountName ,
                    						v.Code ,
                    						v.Rate ,
                    						v.ConnectionFee,                                                           
                    						v.EffectiveDate ,
                    						v.Description ,
                    						v.Preference, 
                    						@rank := ( CASE WHEN ( @prev_RowCode   = RowCode and   @prev_AccountID = v.AccountId   ) 
                                                            THEN @rank + 1  
                                                            ELSE 1  END ) AS MaxMatchRank,
                    						
                    						@prev_RowCode := RowCode	 as prev_RowCode,															
                                                  @prev_AccountID := v.AccountId as prev_AccountID
                    					 FROM tmp_VendorRate_stage_1 v  
                                             , (SELECT  @prev_RowCode := '',  @rank := 0 , @prev_Code := '' , @prev_AccountID := Null) f
                                             order by AccountID,RowCode desc ;  


                                       truncate tmp_VendorRate_;
                                       insert into tmp_VendorRate_
                                       select
									 AccountId ,
									 AccountName ,
									 Code ,
									 Rate ,
         						           ConnectionFee,                                                           
									 EffectiveDate ,
									 Description ,
									 Preference,
									 RowCode
							   from tmp_VendorRate_stage_ 
                                           where MaxMatchRank = 1 order by RowCode desc; 
						
 
		 
		truncate tmp_final_VendorRate_;
                               
		IF( v_Use_Preference_ = 0 )
     	THEN
     			
			insert into tmp_final_VendorRate_
			SELECT
					 AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
					AccountId ,
				     AccountName ,
				     Code ,
				     Rate ,
                         ConnectionFee,
				     EffectiveDate ,
				     Description ,
				     Preference,
				     RowCode,
   			         @rank := CASE WHEN ( @prev_RowCode = RowCode  AND @prev_Rate <  Rate ) THEN @rank+1
                                      WHEN ( @prev_RowCode = RowCode  AND @prev_Rate = Rate) THEN @rank 
						   ELSE 
						   1
						   END 
				  		  AS FinalRankNumber,
				  	@prev_RowCode  := RowCode,
					@prev_Rate  := Rate
					from tmp_VendorRate_ 
					,(SELECT @rank := 0 , @prev_RowCode := '' , @prev_Rate := 0  ) x 
				 	order by RowCode,Rate,AccountId ASC
				 
				) tbl1
				where 
				FinalRankNumber <= v_RatePosition_;
				 
		 ELSE 
		 
		 	 insert into tmp_final_VendorRate_
			SELECT
				AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
     					AccountId ,
     				     AccountName ,
     				     Code ,
     				     Rate ,
                             ConnectionFee,
     				     EffectiveDate ,
     				     Description ,
     				     Preference,
     				     RowCode,
                              @preference_rank := CASE WHEN (@prev_Code = RowCode  AND @prev_Preference > Preference  )   THEN @preference_rank + 1 
                                                       WHEN (@prev_Code = RowCode  AND @prev_Preference = Preference AND @prev_Rate < Rate) THEN @preference_rank + 1 
                                                       WHEN (@prev_Code = RowCode  AND @prev_Preference = Preference AND @prev_Rate = Rate) THEN @preference_rank 
                                                       ELSE 1 END AS FinalRankNumber,
                              @prev_Code := RowCode,
                              @prev_Preference := Preference,
                              @prev_Rate := Rate
					from tmp_VendorRate_ 
                          ,(SELECT @preference_rank := 0 , @prev_Code := ''  , @prev_Preference := 5,  @prev_Rate := 0) x
     			 	 order by RowCode ASC ,Preference DESC ,Rate ASC ,AccountId ASC
                         
				) tbl1
				where 
				FinalRankNumber <= v_RatePosition_;
		 
		 END IF;
			
		
		
	  WHILE v_pointer_ <= v_rowCount_
	    DO

             SET v_rateRuleId_ = (SELECT rateruleid FROM tmp_Raterules_ rr WHERE rr.RowNo = v_pointer_);
             SET @IncludeAccountIds = (SELECT GROUP_CONCAT(AccountId) from tblRateRuleSource where RateRuleId = v_rateRuleId_ ) ;

		INSERT INTO tmp_Rates2_ (code,rate,ConnectionFee)
		select  code,rate,ConnectionFee from tmp_Rates_;

		truncate   tmp_VRatesstage2_;
		
	               INSERT INTO tmp_VRatesstage2_         
     			SELECT
     				vr.RowCode,
     				vr.code,
     				vr.rate,
     				vr.ConnectionFee,
                         vr.FinalRankNumber
     			FROM tmp_final_VendorRate_ vr
                     JOIN tmp_Raterules_ rr ON rr.RateRuleId = v_rateRuleId_ and vr.RowCode LIKE (REPLACE(rr.code,'*', '%%'))
     			 left join tmp_Rates2_ rate on rate.Code = vr.RowCode 
     			WHERE vr.FinalRankNumber <= v_RatePosition_ 
                    AND rate.code is null
                   order by vr.FinalRankNumber desc ;

			
		 
	        IF v_Average_ = 0 
	        THEN              
                                   insert into tmp_dupVRatesstage2_
                                   SELECT RowCode , MAX(FinalRankNumber) AS MaxFinalRankNumber
                                           FROM tmp_VRatesstage2_ GROUP BY RowCode;

                              truncate tmp_Vendorrates_stage3_;
                              INSERT INTO tmp_Vendorrates_stage3_ 
                              select  vr.RowCode as RowCode , vr.rate as rate , vr.ConnectionFee as  ConnectionFee
                              from tmp_VRatesstage2_ vr
                              INNER JOIN tmp_dupVRatesstage2_ vr2
                                ON (vr.RowCode = vr2.RowCode AND  vr.FinalRankNumber = vr2.FinalRankNumber);

                    INSERT INTO tmp_Rates_
                    SELECT RowCode,
				CASE WHEN rule_mgn.RateRuleId is not null
				THEN
					CASE WHEN AddMargin LIKE '%p'
						THEN ( vRate.rate + (CAST(REPLACE(AddMargin, 'p', '') AS DECIMAL(18, 2)) / 100) * vRate.rate)
					 ELSE  vRate.rate + AddMargin
					END
				ELSE
				vRate.rate
				END as Rate,
                    ConnectionFee
				  FROM tmp_Vendorrates_stage3_ vRate
				  left join tblRateRuleMargin rule_mgn on  rule_mgn.RateRuleId = v_rateRuleId_ and vRate.rate Between rule_mgn.MinRate and rule_mgn.MaxRate;
                                   
                 			 
						 
	         ELSE 

	           INSERT INTO tmp_Rates_
				SELECT RowCode,
						CASE WHEN rule_mgn.AddMargin is not null
						THEN
							CASE WHEN AddMargin LIKE '%p'
								THEN ( vRate.rate + (CAST(REPLACE(AddMargin, 'p', '') AS DECIMAL(18, 2)) / 100) * vRate.rate) 
							 ELSE  vRate.rate + AddMargin 
							END 
						ELSE 
						vRate.rate
						END as Rate,
                              ConnectionFee
				  FROM ( 
								select RowCode,
				                    AVG(Rate) as Rate,
				                    AVG(ConnectionFee) as ConnectionFee
								 	from tmp_VRatesstage2_
							 		group by RowCode 
				     )  vRate
				  left join tblRateRuleMargin rule_mgn on  rule_mgn.RateRuleId = v_rateRuleId_ and vRate.rate Between rule_mgn.MinRate and rule_mgn.MaxRate;
	        END IF;
	        
	       	
	         SET v_pointer_ = v_pointer_ + 1;
			 
			
	     END WHILE;

		  
    
  
   
        START TRANSACTION;

	    IF p_RateTableId = -1
	    THEN
	
	        INSERT INTO tblRateTable (CompanyId, RateTableName, RateGeneratorID, TrunkID, CodeDeckId,CurrencyID)
	            VALUES (v_CompanyId_, p_rateTableName, p_RateGeneratorId, v_trunk_, v_codedeckid_,v_CurrencyID_);
	
	        SET p_RateTableId = LAST_INSERT_ID();
	
	        INSERT INTO tblRateTableRate (RateID,
	        RateTableId,
	        Rate,
	        EffectiveDate,
	        PreviousRate,
	        Interval1,
	        IntervalN,
	        ConnectionFee
	        )
	            SELECT DISTINCT
	                RateId,
	                p_RateTableId,
	                 Rate,	               
	                p_EffectiveDate,
	                Rate,
	                Interval1,
	                IntervalN,
	                ConnectionFee
	            FROM tmp_Rates_ rate
	            INNER JOIN tblRate
	                ON rate.code  = tblRate.Code 
	            WHERE tblRate.CodeDeckId = v_codedeckid_;
	
	    ELSE
	
	        INSERT INTO tblRateTableRate (RateID,
	        RateTableId,
	        Rate,
	        EffectiveDate,
	        PreviousRate,
	        Interval1,
	        IntervalN,
	        ConnectionFee
	        )
	            SELECT DISTINCT
	                tblRate.RateId,
	                p_RateTableId RateTableId,
	                rate.Rate,
	                p_EffectiveDate EffectiveDate,
	                rate.Rate,
	                tblRate.Interval1,
	                tblRate.IntervalN,
	                rate.ConnectionFee
	            FROM tmp_Rates_ rate
	            INNER JOIN tblRate 
	                ON rate.code  = tblRate.Code 
	            LEFT JOIN tblRateTableRate tbl1 
	                ON tblRate.RateId = tbl1.RateId
	                AND tbl1.RateTableId = p_RateTableId
	            LEFT JOIN tblRateTableRate tbl2 
	            ON tblRate.RateId = tbl2.RateId
	            and tbl2.EffectiveDate = p_EffectiveDate
	            AND tbl2.RateTableId = p_RateTableId
	            WHERE  (    tbl1.RateTableRateID IS NULL 
	                    OR
	                    (
	                        tbl2.RateTableRateID IS NULL 
	                        AND  tbl1.EffectiveDate != p_EffectiveDate                       
	                        
	                    )
	                 )
	            AND tblRate.CodeDeckId = v_codedeckid_;
	
	        UPDATE tblRateTableRate
	        INNER JOIN tblRate 
	            ON tblRate.RateId = tblRateTableRate.RateId
	            AND tblRateTableRate.RateTableId = p_RateTableId
	            AND tblRateTableRate.EffectiveDate = p_EffectiveDate
	         INNER JOIN tmp_Rates_ as rate
	          ON  rate.code  = tblRate.Code 
	          SET tblRateTableRate.PreviousRate = tblRateTableRate.Rate,
	            tblRateTableRate.EffectiveDate = p_EffectiveDate,
	            tblRateTableRate.Rate = rate.Rate,
	            tblRateTableRate.ConnectionFee = rate.ConnectionFee,
	            tblRateTableRate.updated_at = NOW(),
	            tblRateTableRate.ModifiedBy = 'RateManagementService',
	            tblRateTableRate.Interval1 = tblRate.Interval1,
	            tblRateTableRate.IntervalN = tblRate.IntervalN
	        WHERE tblRate.CodeDeckId = v_codedeckid_
	        AND rate.rate != tblRateTableRate.Rate;
	
	        DELETE tblRateTableRate
	            FROM tblRateTableRate 
	        WHERE tblRateTableRate.RateTableId = p_RateTableId
	            AND RateId NOT IN (SELECT DISTINCT
	                    RateId
	                FROM tmp_Rates_ rate
	                INNER JOIN tblRate 
	                    ON rate.code  = tblRate.Code 
	                WHERE tblRate.CodeDeckId = v_codedeckid_)
	            AND tblRateTableRate.EffectiveDate = p_EffectiveDate;	
	
	
	    END IF;


		UPDATE tblRateTable
	   SET RateGeneratorID = p_RateGeneratorId,
		  TrunkID = v_trunk_,
		  CodeDeckId = v_codedeckid_,
		  updated_at = now()
		WHERE RateTableID = p_RateTableId;
     
      SELECT p_RateTableId as RateTableID;
     
    	CALL prc_WSJobStatusUpdate(p_jobId, 'S', 'RateTable Created Successfully', '');
    
    COMMIT;     
    

    	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    	

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGenerateRateTable_OLD19042016` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGenerateRateTable_OLD19042016`(IN `p_jobId` INT
, IN `p_RateGeneratorId` INT
, IN `p_RateTableId` INT
, IN `p_rateTableName` VARCHAR(200) 
, IN `p_EffectiveDate` VARCHAR(10))
GenerateRateTable:BEGIN
		
		DECLARE v_RTRowCount_ INT;       
		DECLARE v_RatePosition_ INT;
		DECLARE v_Use_Preference_ INT;
		DECLARE v_CurrencyID_ INT;
		DECLARE v_CompanyCurrencyID_ INT;
    	DECLARE v_Average_ TINYINT;  
    	DECLARE v_CompanyId_ INT;
    	DECLARE v_codedeckid_ INT;
    	DECLARE v_trunk_ INT; 
    	DECLARE v_rateRuleId_ INT;
    	DECLARE v_RateGeneratorName_ VARCHAR(200);
    	DECLARE v_pointer_ INT ;
    	DECLARE v_rowCount_ INT ;
    	
    	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

    	 


    	SET p_EffectiveDate = CAST(p_EffectiveDate AS DATE);
       

    	IF p_rateTableName IS NOT NULL
	 	THEN


        SET v_RTRowCount_ = (SELECT
                COUNT(*)
            FROM tblRateTable
            WHERE RateTableName = p_rateTableName
            AND CompanyId = (SELECT
                    CompanyId
                FROM tblRateGenerator
                WHERE RateGeneratorID = p_RateGeneratorId));

        IF v_RTRowCount_ > 0
        THEN
            CALL prc_WSJobStatusUpdate  (p_jobId, 'F', 'RateTable Name is already exist, Please try using another RateTable Name', '');
            LEAVE GenerateRateTable;
        END IF;
    	END IF;

		DROP TEMPORARY TABLE IF EXISTS tmp_Rates_;
		CREATE TEMPORARY TABLE tmp_Rates_  (
		  code VARCHAR(50) ,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates_code (`code`) 
		);
		DROP TEMPORARY TABLE IF EXISTS tmp_Rates2_;
		CREATE TEMPORARY TABLE tmp_Rates2_  (
		  code VARCHAR(50) ,
		  rate DECIMAL(18, 6),
		  ConnectionFee DECIMAL(18, 6),
		  INDEX tmp_Rates2_code (`code`)
		);


		DROP TEMPORARY TABLE IF EXISTS tmp_Codedecks_;
		CREATE TEMPORARY TABLE tmp_Codedecks_ (
        CodeDeckId INT
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Raterules_;

     	CREATE TEMPORARY TABLE tmp_Raterules_  (
        rateruleid INT,
        code VARCHAR(50),
        RowNo INT,
        INDEX tmp_Raterules_code (`code`),
        INDEX tmp_Raterules_rateruleid (`rateruleid`),
        INDEX tmp_Raterules_RowNo (`RowNo`)
    	);
     	DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_;
     	CREATE TEMPORARY TABLE tmp_Vendorrates_  (
        code VARCHAR(50),
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        AccountId INT,
        RowNo INT,
        PreferenceRank INT,
        INDEX tmp_Vendorrates_code (`code`),
        INDEX tmp_Vendorrates_rate (`rate`)
    	);
    	
    	
    	DROP TEMPORARY TABLE IF EXISTS tmp_Vendorrates_stage2_;
     	CREATE TEMPORARY TABLE tmp_Vendorrates_stage2_  (
        code VARCHAR(50),
        rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        INDEX tmp_Vendorrates_stage2__code (`code`)
    	);
    	
    	
     	DROP TEMPORARY TABLE IF EXISTS tmp_code_;
     	CREATE TEMPORARY TABLE tmp_code_  (
        code VARCHAR(50),
        rateid INT,
        countryid INT,
        INDEX tmp_code_code (`code`),
        INDEX tmp_code_rateid (`rateid`)
    	);
    	DROP TEMPORARY TABLE IF EXISTS tmp_tblVendorRate_;
     	CREATE TEMPORARY TABLE tmp_tblVendorRate_  (
			AccountId INT,
	      RateID INT,
	      Rate FLOAT,
	      ConnectionFee FLOAT,
	      TrunkId INT,
	      EffectiveDate DATE,
			RowID INT,
			INDEX tmp_tblVendorRate_AccountId (`AccountId`,`TrunkId`,`RateID`),
			INDEX tmp_tblVendorRate_EffectiveDate (`EffectiveDate`),
			INDEX tmp_tblVendorRate_Rate (`Rate`)
		);


    	SET  v_Use_Preference_ = (SELECT ifnull( JSON_EXTRACT(Options, '$.Use_Preference'),0)  FROM  tblJob WHERE JobID = p_jobId);
    	SELECT CurrencyID INTO v_CurrencyID_ FROM  tblRateGenerator WHERE RateGeneratorId = p_RateGeneratorId;

    	SELECT
        rateposition,
        companyid ,
        CodeDeckId,
        tblRateGenerator.TrunkID,
        tblRateGenerator.UseAverage  ,
        tblRateGenerator.RateGeneratorName INTO v_RatePosition_, v_CompanyId_, v_codedeckid_, v_trunk_, v_Average_, v_RateGeneratorName_
    	FROM tblRateGenerator
    	WHERE RateGeneratorId = p_RateGeneratorId;
     SET v_codedeckid_ = 2;

		SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = v_CompanyId_;

    	INSERT INTO tmp_Raterules_
			SELECT
            rateruleid,
            tblRateRule.code,
            @row_num := @row_num+1 AS RowID
        FROM tblRateRule,(SELECT @row_num := 0) x
        WHERE rategeneratorid = p_RateGeneratorId
        ORDER BY tblRateRule.code DESC;

	     INSERT INTO tmp_Codedecks_
	        SELECT DISTINCT
	            tblVendorTrunk.CodeDeckId
	        FROM tblRateRule
	        INNER JOIN tblRateRuleSource
	            ON tblRateRule.RateRuleId = tblRateRuleSource.RateRuleId
	        INNER JOIN tblAccount
	            ON tblAccount.AccountID = tblRateRuleSource.AccountId
			JOIN tblVendorTrunk
	                ON tblAccount.AccountId = tblVendorTrunk.AccountID
	                AND  tblVendorTrunk.TrunkID = v_trunk_
	                AND tblVendorTrunk.Status = 1
	        WHERE RateGeneratorId = p_RateGeneratorId;

	    SET v_pointer_ = 1;
	    SET v_rowCount_ = (SELECT COUNT(*)FROM tmp_Raterules_);

	    WHILE v_pointer_ <= v_rowCount_
	    DO
	        SET v_rateRuleId_ = (SELECT
	                rateruleid
	            FROM tmp_Raterules_ rr
	            WHERE rr.rowno = v_pointer_);

	        INSERT INTO tmp_code_
	            SELECT
	                tblRate.code,
	                tblRate.rateid,
	                tblRate.CountryID

	            FROM tblRate
	            JOIN tmp_Codedecks_ cd
	                ON tblRate.CodeDeckId = cd.CodeDeckId
	            JOIN tmp_Raterules_ rr
	                ON tblRate.code LIKE (REPLACE(rr.code,
	                '*', '%%'))
	            WHERE rr.rowno = v_pointer_;



				 INSERT INTO tmp_tblVendorRate_
	          SELECT AccountId,
	          RateID,
	          Rate,
	          ConnectionFee,
	          TrunkId,
	          EffectiveDate,
				 RowID
				 FROM(SELECT AccountId,
	          RateID,
	          Rate,
	          ConnectionFee,
	          TrunkId,
	          EffectiveDate,
				 @row_num := IF(@prev_RateId=tblVendorRate.RateId AND @prev_TrunkID=tblVendorRate.TrunkID AND @prev_AccountId=tblVendorRate.AccountId and @prev_EffectiveDate >= tblVendorRate.EffectiveDate ,@row_num+1,1) AS RowID,
				 @prev_RateId  := tblVendorRate.RateId,
				 @prev_TrunkID  := tblVendorRate.TrunkID,
				 @prev_AccountId  := tblVendorRate.AccountId,
				 @prev_EffectiveDate  := tblVendorRate.EffectiveDate
	          FROM tblVendorRate ,(SELECT @row_num := 1) x,(SELECT @prev_RateId := '') y,(SELECT @prev_TrunkID := '') z ,(SELECT @prev_AccountId := '') v ,(SELECT @prev_EffectiveDate := '') u
	          WHERE TrunkId = v_trunk_
				 ORDER BY tblVendorRate.AccountId , tblVendorRate.TrunkID, tblVendorRate.RateId, tblVendorRate.EffectiveDate DESC) TBLVENDOR;

	       INSERT INTO tmp_Vendorrates_   (code ,rate ,  ConnectionFee , AccountId ,RowNo ,PreferenceRank )
			  SELECT code,rate,ConnectionFee,AccountId,RowNo,PreferenceRank FROM(SELECT *,
				 @row_num := IF(@prev_Code2 = code AND  @prev_Preference <= Preference ,(@row_num+1),1) AS PreferenceRank,
				 @prev_Code2  := Code,
				 @prev_Rate2  := rate,
				 @prev_Preference  := Preference
				 FROM (SELECT code,
				rate,
				ConnectionFee,
				AccountId,
				Preference,
				@row_num := IF(@prev_Code = code AND @prev_Rate <= rate ,(@row_num+1),1) AS RowNo,
				@prev_Code  := Code,
				@prev_Rate  := rate
				from  (
				SELECT DISTINCT
	                c.code,
					 CASE WHEN  tblAccount.CurrencyId = v_CurrencyID_
						THEN
							tmp_tblVendorRate_.rate
	               WHEN  v_CompanyCurrencyID_ = v_CurrencyID_  
					 THEN
						  (
						   ( tmp_tblVendorRate_.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ) )
						 )
					  ELSE 
					  (
					  			
      					  (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = v_CurrencyID_ and  CompanyID = v_CompanyId_ )
					  		* (tmp_tblVendorRate_.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = v_CompanyId_ ))
					  )
					 END
						as  Rate,
	                tmp_tblVendorRate_.ConnectionFee,
	                tblAccount.AccountId,
					Preference

	            FROM tmp_Raterules_ rateRule
	            JOIN tblRateRuleSource
	                ON tblRateRuleSource.rateruleid = rateRule.rateruleid
	            JOIN tblAccount
	                ON tblAccount.AccountId = tblRateRuleSource.AccountId
	            JOIN tmp_tblVendorRate_
	                ON tblAccount.AccountId = tmp_tblVendorRate_.AccountId
	                AND tmp_tblVendorRate_.TrunkId = v_trunk_ and RowID = 1

	            JOIN tblVendorTrunk
	                ON tmp_tblVendorRate_.AccountId = tblVendorTrunk.AccountID
	                AND tmp_tblVendorRate_.TrunkId = tblVendorTrunk.TrunkID
	                AND tblVendorTrunk.Status = 1
	            JOIN tmp_code_ c
	                ON c.RateID = tmp_tblVendorRate_.RateId
	            LEFT JOIN tblVendorBlocking blockCountry
	                ON tblAccount.AccountId = blockCountry.AccountId
	                AND c.CountryID = blockCountry.CountryId
	                AND blockCountry.TrunkID = v_trunk_
	            LEFT JOIN tblVendorBlocking blockCode
	                ON tblAccount.AccountId = blockCode.AccountId
	                AND c.RateID = blockCode.RateId
	                AND blockCode.TrunkID = v_trunk_
	            LEFT JOIN tblVendorPreference
	                ON tblAccount.AccountId = tblVendorPreference.AccountId
	                AND c.RateID = tblVendorPreference.RateId
	                AND tblVendorPreference.TrunkID = v_trunk_
	            WHERE rateRule.rowno = v_pointer_
	            AND blockCode.VendorBlockingId IS NULL
	            AND blockCountry.VendorBlockingId IS NULL
	            AND CAST(tmp_tblVendorRate_.EffectiveDate AS DATE) <= CAST(NOW() AS DATE)
				) VNDRTBL ,(SELECT @row_num := 1) x,(SELECT @prev_Code := '') y,(SELECT @prev_Rate := '') z
				ORDER BY code , rate
				) VNDRTBL ,(SELECT @row_num := 1) x,(SELECT @prev_Code2 := '') y,(SELECT @prev_Rate2 := '') z,(SELECT @prev_Preference := '') v
			 ORDER BY code,Preference desc,rate) LASTTBL;

	       DELETE FROM tmp_tblVendorRate_;

			 INSERT INTO tmp_Rates2_ (code,rate,ConnectionFee)
			 select  code,rate,ConnectionFee from tmp_Rates_;
 
			 
            truncate   tmp_Vendorrates_stage2_;
            INSERT INTO tmp_Vendorrates_stage2_         
				SELECT
                    vr.code,
                    vr.rate,
                    vr.ConnectionFee
                FROM tmp_Vendorrates_ vr
                left join tmp_Rates2_ rate on rate.code = vr.code 
                WHERE 
                rate.code is null
	             and   (
	                    (v_Use_Preference_ =0 and vr.RowNo <= v_RatePosition_ ) or 
	                    (v_Use_Preference_ =1 and vr.PreferenceRank <= v_RatePosition_)
	                )
	                
					order by
						CASE WHEN v_Use_Preference_ =1 THEN 
							PreferenceRank
						ELSE 
							RowNo
						END desc;
							
	        IF v_Average_ = 0 
	        THEN
	           
	           	
	                 
	              INSERT INTO tmp_Rates_
	                SELECT
	                    code,
	                    (SELECT 
	                            CASE WHEN vRate.rate >= minrate AND
	                            vRate.rate <= maxrate THEN vRate.rate
	                                + (CASE WHEN addmargin LIKE '%p' THEN ((CAST(REPLACE(addmargin, 'p', '') AS DECIMAL(18, 2)) / 100) * vRate.rate) ELSE addmargin
	                                END) ELSE vRate.rate
	                            END
	                        FROM tblRateRuleMargin 
	                        WHERE rateruleid = v_rateRuleId_ LIMIT 1
							) as Rate,
	                	ConnectionFee
	                FROM ( 
             					select  max(code) as code , max(rate) as rate , max(ConnectionFee) as  ConnectionFee,
											@row_num := IF(@prev_Code = code   ,(@row_num+1),1) AS RowNoNew,
											@prev_Code  := code
								 	from tmp_Vendorrates_stage2_
										 , (SELECT @row_num := 1 , @prev_Code := '' ) t
							 		group by code 
						 ) vRate;
						 
						 
	         ELSE 
	            
	           INSERT INTO tmp_Rates_
	                SELECT
	                    code,
	                     (SELECT 
	                            CASE WHEN vRate.rate >= minrate AND
	                            vRate.rate <= maxrate THEN vRate.rate
	                                + (CASE WHEN addmargin LIKE '%p' THEN ((CAST(REPLACE(addmargin, 'p', '') AS DECIMAL(18, 2)) / 100)
	                                    * vRate.rate) ELSE addmargin
	                                END) ELSE vRate.rate
	                            END
	                        FROM tblRateRuleMargin 
	                        WHERE rateruleid = v_rateRuleId_ LIMIT 1 
								) as Rate,
	                    ConnectionFee
	                FROM ( 
			                select code,
				                    AVG(Rate) as Rate,
				                    AVG(ConnectionFee) as ConnectionFee
								 	from tmp_Vendorrates_stage2_
							 		group by code 
						 ) vRate;
	
	        END IF;
	        DELETE FROM tmp_Vendorrates_;
	        DELETE FROM tmp_code_;
	
	        SET v_pointer_ = v_pointer_ + 1;
	    END WHILE;

		 
		 
	   
	   
		

	    IF p_RateTableId = -1
	    THEN
	
	        INSERT INTO tblRateTable (CompanyId, RateTableName, RateGeneratorID, TrunkID, CodeDeckId,CurrencyID)
	            VALUES (v_CompanyId_, p_rateTableName, p_RateGeneratorId, v_trunk_, v_codedeckid_,v_CurrencyID_);
	
	        SET p_RateTableId = LAST_INSERT_ID();
	
	        INSERT INTO tblRateTableRate (RateID,
	        RateTableId,
	        Rate,
	        EffectiveDate,
	        PreviousRate,
	        Interval1,
	        IntervalN,
	        ConnectionFee
	        )
	            SELECT DISTINCT
	                RateId,
	                p_RateTableId,
	                Rate,
	                p_EffectiveDate,
	                Rate,
	                Interval1,
	                IntervalN,
	                ConnectionFee
	            FROM tmp_Rates_ rate
	            INNER JOIN tblRate
	                ON rate.code  = tblRate.Code 
	            WHERE tblRate.CodeDeckId = v_codedeckid_;
	
	    ELSE
	
	        INSERT INTO tblRateTableRate (RateID,
	        RateTableId,
	        Rate,
	        EffectiveDate,
	        PreviousRate,
	        Interval1,
	        IntervalN,
	        ConnectionFee
	        )
	            SELECT DISTINCT
	                tblRate.RateId,
	                p_RateTableId,
	                rate.Rate,
	                p_EffectiveDate,
	                rate.Rate,
	                tblRate.Interval1,
	                tblRate.IntervalN,
	                rate.ConnectionFee
	            FROM tmp_Rates_ rate
	            INNER JOIN tblRate 
	                ON rate.code  = tblRate.Code 
	            LEFT JOIN tblRateTableRate tbl1 
	                ON tblRate.RateId = tbl1.RateId
	                AND tbl1.RateTableId = p_RateTableId
	            LEFT JOIN tblRateTableRate tbl2 
	            ON tblRate.RateId = tbl2.RateId
	            and tbl2.EffectiveDate = p_EffectiveDate
	            AND tbl2.RateTableId = p_RateTableId
	            WHERE  (    tbl1.RateTableRateID IS NULL 
	                    OR
	                    (
	                        tbl2.RateTableRateID IS NULL 
	                        AND  tbl1.EffectiveDate != p_EffectiveDate                       
	                        
	                    )
	                 )
	            AND tblRate.CodeDeckId = v_codedeckid_;
	
	        UPDATE tblRateTableRate
	        INNER JOIN tblRate 
	            ON tblRate.RateId = tblRateTableRate.RateId
	            AND tblRateTableRate.RateTableId = p_RateTableId
	            AND tblRateTableRate.EffectiveDate = p_EffectiveDate
	         INNER JOIN tmp_Rates_ as rate
	          ON  rate.code  = tblRate.Code 
	          SET tblRateTableRate.PreviousRate = tblRateTableRate.Rate,
	            tblRateTableRate.EffectiveDate = p_EffectiveDate,
	            tblRateTableRate.Rate = rate.Rate,
	            tblRateTableRate.ConnectionFee = rate.ConnectionFee,
	            tblRateTableRate.updated_at = NOW(),
	            tblRateTableRate.ModifiedBy = 'RateManagementService',
	            tblRateTableRate.Interval1 = tblRate.Interval1,
	            tblRateTableRate.IntervalN = tblRate.IntervalN
	        WHERE tblRate.CodeDeckId = v_codedeckid_
	        AND rate.rate != tblRateTableRate.Rate;
	
	        DELETE tblRateTableRate
	            FROM tblRateTableRate 
	        WHERE tblRateTableRate.RateTableId = p_RateTableId
	            AND RateId NOT IN (SELECT DISTINCT
	                    RateId
	                FROM tmp_Rates_ rate
	                INNER JOIN tblRate 
	                    ON rate.code  = tblRate.Code 
	                WHERE tblRate.CodeDeckId = v_codedeckid_)
	            AND tblRateTableRate.EffectiveDate = p_EffectiveDate;
	
	
	
	    END IF;


		UPDATE tblRateTable
	   SET RateGeneratorID = p_RateGeneratorId,
		  TrunkID = v_trunk_,
		  CodeDeckId = v_codedeckid_,
		  updated_at = now()
		WHERE RateTableID = p_RateTableId;

    	SELECT p_RateTableId as RateTableID;

    	CALL prc_WSJobStatusUpdate(p_jobId, 'S', 'RateTable Created Successfully', '');
    	
    	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    	

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGenerateSippySheet` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGenerateSippySheet`(IN `p_CustomerID` INT , IN `p_Trunks` varchar(200) , IN `p_Effective` VARCHAR(50))
BEGIN
    
    DECLARE v_codedeckid_ INT;
    DECLARE v_ratetableid_ INT;
    DECLARE v_RateTableAssignDate_ DATETIME;
    DECLARE v_NewA2ZAssign_ INT;
    DECLARE v_companyid_ INT;
    DECLARE v_TrunkID_ INT;
    DECLARE v_pointer_ INT ;
    DECLARE v_rowCount_ INT ;
   
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
   
    
    DROP TEMPORARY TABLE IF EXISTS tmp_customerrateall_;
    CREATE TEMPORARY TABLE tmp_customerrateall_ (
        RateID INT,
        Code VARCHAR(50),
        Description VARCHAR(200),
        Interval1 INT,
        IntervalN INT,
        ConnectionFee DECIMAL(18, 6),
        RoutinePlanName VARCHAR(50),
        Rate DECIMAL(18, 6),
        EffectiveDate DATE,
        LastModifiedDate DATETIME,
        LastModifiedBy VARCHAR(50),
        CustomerRateId INT,
        TrunkID INT,
        RateTableRateId INT,
        IncludePrefix TINYINT,
        Prefix VARCHAR(50),
        RatePrefix VARCHAR(50),
        AreaPrefix VARCHAR(50)
    );
        
    DROP TEMPORARY TABLE IF EXISTS tmp_trunks_;
    CREATE TEMPORARY TABLE tmp_trunks_  (
        TrunkID INT,
        RowNo INT
    );
            
            
    SELECT 
        CompanyId INTO v_companyid_
    FROM tblAccount
    WHERE AccountID = p_CustomerID; 
        
        
        
    INSERT INTO tmp_trunks_
    SELECT TrunkID,
        @row_num := @row_num+1 AS RowID
    FROM tblCustomerTrunk,(SELECT @row_num := 0) x
    WHERE  FIND_IN_SET(tblCustomerTrunk.TrunkID,p_Trunks)!= 0 
        AND tblCustomerTrunk.AccountID = p_CustomerID;
        
    SET v_pointer_ = 1;
    SET v_rowCount_ = (SELECT COUNT(*)FROM tmp_trunks_);
        
        
    WHILE v_pointer_ <= v_rowCount_
    DO
         
        SET v_TrunkID_ = (SELECT TrunkID FROM tmp_trunks_ t WHERE t.RowNo = v_pointer_);
     
        CALL prc_GetCustomerRate(v_companyid_,p_CustomerID,v_TrunkID_,null,null,null,p_Effective,1,0,0,0,'','',-1);
        
        INSERT INTO tmp_customerrateall_
        SELECT * FROM tmp_customerrate_;
        
        SET v_pointer_ = v_pointer_ + 1;
    END WHILE;
            
    SELECT 
            'A' as `Action [A|D|U|S|SA` ,
            '' as id,
            Concat(IFNULL(Prefix,''), Code) as Prefix,
            Description as COUNTRY ,
            Interval1 as `Interval 1`,
            IntervalN as `Interval N`,
            Rate as `Price 1`,
            Rate as `Price N`,
            0  as Forbidden,
            0 as `Grace Period`,
            'NOW' as `Activation Date`,
            '' as `Expiration Date` 
    FROM tmp_customerrateall_

    ORDER BY Prefix;
        
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGenerateVendorSippySheet` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGenerateVendorSippySheet`(IN `p_VendorID` INT  , IN `p_Trunks` varchar(200), IN `p_Effective` VARCHAR(50))
BEGIN
	
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
         
        call vwVendorSippySheet(p_VendorID,p_Trunks,p_Effective); 
        SELECT 
				 `Action [A|D|U|S|SA`,
                id ,
                vendorRate.Prefix,
                COUNTRY,
                Preference ,
                `Interval 1` ,
                `Interval N` ,
                `Price 1` ,
                `Price N` ,
                `1xx Timeout` ,
                `2xx Timeout` ,
                `Huntstop` ,
                Forbidden ,
                `Activation Date` ,
                `Expiration Date`
        FROM    tmp_VendorSippySheet_ vendorRate     
        WHERE   vendorRate.AccountId = p_VendorID
        And  FIND_IN_SET(vendorRate.TrunkId,p_Trunks) != 0;
        
      SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGenerateVendorVersion3VosSheet` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGenerateVendorVersion3VosSheet`(IN `p_VendorID` INT , IN `p_Trunks` varchar(200) , IN `p_Effective` VARCHAR(50))
BEGIN
         SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
         
        call vwVendorVersion3VosSheet(p_VendorID,p_Trunks,p_Effective);
        
        IF p_Effective = 'Now'		
		  THEN
		  
	        SELECT  `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    tmp_VendorVersion3VosSheet_
	        WHERE   AccountID = p_VendorID
	        AND  FIND_IN_SET(TrunkId,p_Trunks)!= 0
	        ORDER BY `Rate Prefix`;
        
        END IF;
        
        IF p_Effective = 'Future'		
		  THEN
		  
	        SELECT  CONCAT(tmp_VendorVersion3VosSheet_.EffectiveDate,' 00:00') as `Time of timing replace`,
						 'Append replace' as `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    tmp_VendorVersion3VosSheet_
	        WHERE   AccountID = p_VendorID
	        AND  FIND_IN_SET(TrunkId,p_Trunks)!= 0
	        ORDER BY `Rate Prefix`;
        
        END IF;
        
        SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGenerateVersion3VosSheet` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGenerateVersion3VosSheet`(IN `p_CustomerID` INT , IN `p_trunks` varchar(200) , IN `p_Effective` VARCHAR(50))
BEGIN
    
    DECLARE v_codedeckid_ INT;
    DECLARE v_ratetableid_ INT;
    DECLARE v_RateTableAssignDate_ DATETIME;
    DECLARE v_NewA2ZAssign_ INT;
    DECLARE v_companyid_ INT;
    DECLARE v_TrunkID_ INT;
    DECLARE v_pointer_ INT ;
    DECLARE v_rowCount_ INT ;
   
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
   
    
    DROP TEMPORARY TABLE IF EXISTS tmp_customerrateall_;
    CREATE TEMPORARY TABLE tmp_customerrateall_ (
        RateID INT,
        Code VARCHAR(50),
        Description VARCHAR(200),
        Interval1 INT,
        IntervalN INT,
        ConnectionFee DECIMAL(18, 6),
        RoutinePlanName VARCHAR(50),
        Rate DECIMAL(18, 6),
        EffectiveDate DATE,
        LastModifiedDate DATETIME,
        LastModifiedBy VARCHAR(50),
        CustomerRateId INT,
        TrunkID INT,
        RateTableRateId INT,
        IncludePrefix TINYINT,
        Prefix VARCHAR(50),
        RatePrefix VARCHAR(50),
        AreaPrefix VARCHAR(50)
    );
        
    DROP TEMPORARY TABLE IF EXISTS tmp_trunks_;
    CREATE TEMPORARY TABLE tmp_trunks_  (
        TrunkID INT,
        RowNo INT
    );
            
            
    SELECT 
        CompanyId INTO v_companyid_
    FROM tblAccount
    WHERE AccountID = p_CustomerID; 
        
        
        
    INSERT INTO tmp_trunks_
    SELECT TrunkID,
        @row_num := @row_num+1 AS RowID
    FROM tblCustomerTrunk,(SELECT @row_num := 0) x
    WHERE  FIND_IN_SET(tblCustomerTrunk.TrunkID,p_Trunks)!= 0 
        AND tblCustomerTrunk.AccountID = p_CustomerID;
        
    SET v_pointer_ = 1;
    SET v_rowCount_ = (SELECT COUNT(*)FROM tmp_trunks_);
        
        
    WHILE v_pointer_ <= v_rowCount_
    DO
         
        SET v_TrunkID_ = (SELECT TrunkID FROM tmp_trunks_ t WHERE t.RowNo = v_pointer_);
     
        CALL prc_GetCustomerRate(v_companyid_,p_CustomerID,v_TrunkID_,null,null,null,p_Effective,1,0,0,0,'','',-1);
        
        INSERT INTO tmp_customerrateall_
        SELECT * FROM tmp_customerrate_;
        
        SET v_pointer_ = v_pointer_ + 1;
    END WHILE;
    
    	IF p_Effective = 'Now'		
		  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'		
		  THEN	
            
        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_
        ORDER BY `Rate Prefix`; 
       
		 END IF;
   
   SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGetJobDetails` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGetJobDetails`(IN `p_jobId` int)
BEGIN
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  SELECT tblJob.CompanyID 
  , IFNULL(tblUser.EmailAddress,'')  as EmailAddress
  , tblJob.Title as JobTitle
  , IFNULL(tblJob.JobStatusMessage,'') as JobStatusMessage
  , tblJobStatus.Title as StatusTitle
  ,OutputFilePath
  ,AccountID 
  ,tblJobType.Title as JobType
  FROM tblJob   
    JOIN tblJobStatus 
      ON tblJob.JobStatusID = tblJobStatus.JobStatusID    
    JOIN tblUser
      ON tblUser.UserID = tblJob.JobLoggedUserID
    JOIN tblJobType
	 	ON tblJob.JobTypeID = tblJobType.JobTypeID  
  WHERE tblJob.JobID = p_jobId;   
  
  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSInsertJobLog` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSInsertJobLog`(IN `p_companyId` INT , IN `p_accountId` INT , IN `p_processId` VARCHAR(50) , IN `p_process` VARCHAR(50) , IN `p_message` LONGTEXT)
BEGIN
	 
  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
	INSERT  INTO tblLog
    ( CompanyID ,
      AccountID ,
      ProcessID ,
      Process ,
      Message
    )
    SELECT  p_companyId ,
            ( CASE WHEN ( p_accountId IS NOT NULL
                          AND p_accountId > 0
                        ) THEN p_accountId
                   ELSE ( SELECT    AccountID
                          FROM      tblJob
                          WHERE     ProcessID = p_processId
                        )
              END ) ,
            p_processId ,
            p_process ,
            p_message;
     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;   
  
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSJobStatusUpdate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSJobStatusUpdate`(IN `p_JobId` int , IN `p_Status` varchar(5) , IN `p_JobStatusMessage` longtext , IN `p_OutputFilePath` longtext
)
BEGIN
  DECLARE v_JobStatusId_ int;
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

  SELECT  JobStatusId INTO v_JobStatusId_ FROM tblJobStatus WHERE Code = p_Status;

  UPDATE tblJob
  SET JobStatusID = v_JobStatusId_
  , JobStatusMessage = p_JobStatusMessage
  , OutputFilePath = p_OutputFilePath
  , updated_at = NOW()
  , ModifiedBy = 'WindowsService'
  WHERE JobID = p_JobId;
  
  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSProcessCodeDeck` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSProcessCodeDeck`(
	IN `p_processId` VARCHAR(200),
	IN `p_companyId` INT
)
BEGIN

    DECLARE v_AffectedRecords_ INT DEFAULT 0;     
    DECLARE   v_CodeDeckId_ INT;
    DECLARE errormessage longtext;
	 DECLARE errorheader longtext;
	 DECLARE countrycount INT DEFAULT 0;
    
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
	 DROP TEMPORARY TABLE IF EXISTS tmp_JobLog_;
    CREATE TEMPORARY TABLE tmp_JobLog_  ( 
        Message longtext   
    );

    SELECT CodeDeckId INTO v_CodeDeckId_ FROM tblTempCodeDeck WHERE ProcessId = p_processId AND CompanyId = p_companyId LIMIT 1;
    
    DELETE n1 
	 FROM tblTempCodeDeck n1 
	 INNER JOIN (
	 	SELECT MAX(TempCodeDeckRateID) as TempCodeDeckRateID FROM tblTempCodeDeck WHERE ProcessId = p_processId
		GROUP BY Code
		HAVING COUNT(*)>1
	) n2 
	 	ON n1.TempCodeDeckRateID = n2.TempCodeDeckRateID
	WHERE n1.ProcessId = p_processId;
 		
 	
	 SELECT COUNT(*) INTO countrycount FROM tblTempCodeDeck WHERE ProcessId = p_processId AND Country !='';
	  
 
    UPDATE tblTempCodeDeck
    SET  
        tblTempCodeDeck.Interval1 = CASE WHEN tblTempCodeDeck.Interval1 is not null  and tblTempCodeDeck.Interval1 > 0
                                    THEN    
                                        tblTempCodeDeck.Interval1
                                    ELSE
                                    CASE WHEN tblTempCodeDeck.Interval1 is null and (tblTempCodeDeck.Description LIKE '%gambia%' OR tblTempCodeDeck.Description LIKE '%mexico%')
                                            THEN 
                                            60
                                    ELSE CASE WHEN tblTempCodeDeck.Description LIKE '%USA%' 
                                            THEN 
                                            6
                                            ELSE 
                                            1
                                        END
                                    END
                                    END,
        tblTempCodeDeck.IntervalN = CASE WHEN tblTempCodeDeck.IntervalN is not null  and tblTempCodeDeck.IntervalN > 0
                                    THEN    
                                        tblTempCodeDeck.IntervalN
                                    ELSE
                                        CASE WHEN tblTempCodeDeck.Description LIKE '%mexico%' THEN 
                                        60
                                    ELSE CASE
                                        WHEN tblTempCodeDeck.Description LIKE '%USA%' THEN 
                                        6
                                    ELSE 
                                        1
                                    END
                                    END
                                    END
    WHERE tblTempCodeDeck.ProcessId = p_processId;
  
    UPDATE tblTempCodeDeck t
	    SET t.CountryId = fnGetCountryIdByCodeAndCountry (t.Code ,t.Country) 
	 WHERE t.ProcessId = p_processId ;
  
   IF countrycount > 0
   THEN
	  	UPDATE tblTempCodeDeck t
		    SET t.CountryId = fnGetCountryIdByCodeAndCountry (t.Code ,t.Description) 
		 WHERE t.ProcessId = p_processId AND  t.CountryId IS NULL;
	END IF;	 

 IF ( SELECT COUNT(*)
                 FROM   tblTempCodeDeck
                 WHERE  tblTempCodeDeck.ProcessId = p_processId
                        AND tblTempCodeDeck.Action = 'D'
               ) > 0
            THEN
      DELETE  tblRate
            FROM    tblRate
                    INNER JOIN tblTempCodeDeck ON tblTempCodeDeck.Code = tblRate.Code
                                                  AND tblRate.CompanyID = p_companyId AND tblRate.CodeDeckId = v_CodeDeckId_
                    LEFT OUTER JOIN tblCustomerRate ON tblRate.RateID = tblCustomerRate.RateID
                    LEFT OUTER JOIN tblRateTableRate ON tblRate.RateID = tblRateTableRate.RateID
                    LEFT OUTER JOIN tblVendorRate ON tblRate.RateID = tblVendorRate.RateId
            WHERE   tblTempCodeDeck.Action = 'D'
          AND tblTempCodeDeck.CompanyID = p_companyId
          AND tblTempCodeDeck.CodeDeckId = v_CodeDeckId_
          AND tblTempCodeDeck.ProcessId = p_processId                    
                    AND tblCustomerRate.CustomerRateID IS NULL
                    AND tblRateTableRate.RateTableRateID IS NULL
                    AND tblVendorRate.VendorRateID IS NULL ;   
		END IF; 
      SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();
  
  
  		SELECT GROUP_CONCAT(Code) into errormessage FROM(
	      SELECT distinct tblRate.Code as Code,1 as a
	      FROM    tblRate
	                    INNER JOIN tblTempCodeDeck ON tblTempCodeDeck.Code = tblRate.Code
	      	    AND tblRate.CompanyID = p_companyId AND tblRate.CodeDeckId = v_CodeDeckId_
	          WHERE   tblTempCodeDeck.Action = 'D'
	          AND tblTempCodeDeck.ProcessId = p_processId
	          AND tblTempCodeDeck.CompanyID = p_companyId AND tblTempCodeDeck.CodeDeckId = v_CodeDeckId_)as tbl GROUP BY a;
	          
	   IF errormessage IS NOT NULL
          THEN
                    INSERT INTO tmp_JobLog_ (Message)                    
                    SELECT distinct 
						  CONCAT(tblRate.Code , ' FAILED TO DELETE - CODE IS IN USE')
					      FROM   tblRate
					              INNER JOIN tblTempCodeDeck ON tblTempCodeDeck.Code = tblRate.Code
					      	    AND tblRate.CompanyID = p_companyId AND tblRate.CodeDeckId = v_CodeDeckId_
					          WHERE   tblTempCodeDeck.Action = 'D'
					          AND tblTempCodeDeck.ProcessId = p_processId
					          AND tblTempCodeDeck.CompanyID = p_companyId AND tblTempCodeDeck.CodeDeckId = v_CodeDeckId_;
	 	END IF;
      
      UPDATE  tblRate
      JOIN tblTempCodeDeck ON tblRate.Code = tblTempCodeDeck.Code
            AND tblTempCodeDeck.ProcessId = p_processId
            AND tblRate.CompanyID = p_companyId
            AND tblRate.CodeDeckId = v_CodeDeckId_
            AND tblTempCodeDeck.Action != 'D'
		SET   tblRate.Description = tblTempCodeDeck.Description,
            tblRate.Interval1 = tblTempCodeDeck.Interval1,
            tblRate.IntervalN = tblTempCodeDeck.IntervalN;
  
  		IF countrycount > 0
  		THEN
  		
	  		UPDATE  tblRate
	      JOIN tblTempCodeDeck ON tblRate.Code = tblTempCodeDeck.Code
	            AND tblTempCodeDeck.ProcessId = p_processId
	            AND tblRate.CompanyID = p_companyId
	            AND tblRate.CodeDeckId = v_CodeDeckId_
	            AND tblTempCodeDeck.Action != 'D'
			SET   tblRate.CountryID = tblTempCodeDeck.CountryId;
		
		END IF;
      
      SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();
      
            INSERT  INTO tblRate
                    ( CountryID ,
                      CompanyID ,
                      CodeDeckId,
                      Code ,
                      Description,
                      Interval1,
                      IntervalN
                    )
                    SELECT  DISTINCT
              tblTempCodeDeck.CountryId ,
                            tblTempCodeDeck.CompanyId ,
                            tblTempCodeDeck.CodeDeckId,
                            tblTempCodeDeck.Code ,
                            tblTempCodeDeck.Description,
                            tblTempCodeDeck.Interval1,
                            tblTempCodeDeck.IntervalN
                    FROM    tblTempCodeDeck left join tblRate on(tblRate.CompanyID = p_companyId AND  tblRate.CodeDeckId = v_CodeDeckId_ AND tblTempCodeDeck.Code=tblRate.Code)
                    WHERE  tblRate.RateID is null
                            AND tblTempCodeDeck.ProcessId = p_processId
              AND tblTempCodeDeck.CompanyID = p_companyId
              AND tblTempCodeDeck.CodeDeckId = v_CodeDeckId_
                            AND tblTempCodeDeck.Action != 'D';
  
      SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();
    
	 INSERT INTO tmp_JobLog_ (Message)
	 SELECT CONCAT(v_AffectedRecords_, ' Records Uploaded \n\r ' );
	 
	DELETE  FROM tblTempCodeDeck WHERE   tblTempCodeDeck.ProcessId = p_processId;
 	 SELECT * from tmp_JobLog_;
	      
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SELECT * from tmp_JobLog_ limit 0 , 20;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSProcessDialString` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSProcessDialString`(IN `p_processId` VARCHAR(200) , IN `p_dialStringId` INT)
BEGIN

    DECLARE v_AffectedRecords_ INT DEFAULT 0;         
    DECLARE totalduplicatecode INT(11) DEFAULT 0;	 
    DECLARE errormessage longtext;
	 DECLARE errorheader longtext;
    
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
	 DROP TEMPORARY TABLE IF EXISTS tmp_JobLog_;
    CREATE TEMPORARY TABLE tmp_JobLog_  ( 
        Message longtext   
    );
    
      
    
    DELETE n1 FROM tblTempDialString n1, tblTempDialString n2 WHERE n1.TempDialStringID < n2.TempDialStringID 
	 	AND n1.DialString = n2.DialString
		AND  n1.ChargeCode = n2.ChargeCode
		AND  n1.DialStringID = n2.DialStringID
		AND  n1.ProcessId = n2.ProcessId
 		AND  n1.DialStringID = p_dialStringId and n2.DialStringID = p_dialStringId
 		AND  n1.ProcessId = p_processId and n2.ProcessId = p_processId;
 		
 	
			SELECT COUNT(*) INTO totalduplicatecode FROM(
				SELECT COUNT(DialString) as c,DialString 
					FROM tblTempDialString 
						WHERE DialStringID = p_dialStringId
							 AND ProcessId = p_processId
					   GROUP BY DialString HAVING c>1) AS tbl;
				
			
			IF  totalduplicatecode > 0
			THEN	
				
				INSERT INTO tmp_JobLog_ (Message)
				  SELECT DISTINCT 
				  CONCAT(DialString , ' DUPLICATE CODE')
				  	FROM(
						SELECT   COUNT(DialString) as c,DialString
							 FROM tblTempDialString
									WHERE DialStringID = p_dialStringId
										 AND ProcessId = p_processId
						  GROUP BY DialString HAVING c>1) AS tbl;				
					
			END IF;	

IF  totalduplicatecode = 0 	
THEN	
 
    
	IF ( SELECT COUNT(*)
                 FROM   tblTempDialString
                 WHERE  tblTempDialString.ProcessId = p_processId
						AND tblTempDialString.DialStringID = p_dialStringId
                        AND tblTempDialString.Action = 'D'
               ) > 0
            THEN
		DELETE  tblDialStringCode
            FROM    tblDialStringCode
                    INNER JOIN tblTempDialString
						ON tblDialStringCode.DialString = tblTempDialString.DialString
                        AND tblDialStringCode.ChargeCode = tblTempDialString.ChargeCode
						AND tblDialStringCode.DialStringID = tblTempDialString.DialStringID
            WHERE   tblTempDialString.Action = 'D' AND tblTempDialString.DialStringID = p_dialStringId AND tblTempDialString.ProcessId = p_processId;   
		END IF; 
		
		SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();
	  
		
		      
		UPDATE  tblDialStringCode
		JOIN tblTempDialString ON tblDialStringCode.DialString = tblTempDialString.DialString
      		AND tblDialStringCode.ChargeCode = tblTempDialString.ChargeCode            
			AND tblDialStringCode.DialStringID = tblTempDialString.DialStringID
			AND tblTempDialString.ProcessId = p_processId
            AND tblTempDialString.DialStringID = p_dialStringId
            AND tblTempDialString.Action != 'D'
		SET   tblDialStringCode.Description = tblTempDialString.Description,
            tblDialStringCode.Forbidden = tblTempDialString.Forbidden;
  
      
		SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();
      
	  
		
		
            INSERT  INTO tblDialStringCode
                    ( DialStringID ,
                      DialString ,
                      ChargeCode,
                      Description ,
                      Forbidden
                    )
                    SELECT  DISTINCT
				tblTempDialString.DialStringID ,
                            tblTempDialString.DialString ,
                            tblTempDialString.ChargeCode,
                            tblTempDialString.Description ,
                            tblTempDialString.Forbidden
                    FROM    tblTempDialString left join tblDialStringCode
						on(tblDialStringCode.DialStringID = tblTempDialString.DialStringID
							AND  tblDialStringCode.DialString = tblTempDialString.DialString)
							
                    WHERE  tblDialStringCode.DialStringCodeID is null
                            AND tblTempDialString.ProcessId = p_processId
							AND tblTempDialString.DialStringID = p_dialStringId
                            AND tblTempDialString.Action != 'D';
  
		SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();
		
 END IF;   
 
		INSERT INTO tmp_JobLog_ (Message)
		SELECT CONCAT(v_AffectedRecords_, ' Records Uploaded \n\r ' );
	 
	 	DELETE  FROM tblTempDialString WHERE   tblTempDialString.ProcessId = p_processId;
		SELECT * from tmp_JobLog_; 
	      
		SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSProcessImportAccount` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSProcessImportAccount`(
	IN `p_processId` VARCHAR(200) ,
	IN `p_companyId` INT,
	IN `p_companygatewayid` INT,
	IN `p_tempaccountid` TEXT,
	IN `p_option` INT
)
BEGIN

   DECLARE v_AffectedRecords_ INT DEFAULT 0;         
	DECLARE totalduplicatecode INT(11);	 
	DECLARE errormessage longtext;
	DECLARE errorheader longtext;
	DECLARE v_accounttype INT DEFAULT 0;
	
	SET sql_mode = '';	    
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET SESSION sql_mode='';
    
	 DROP TEMPORARY TABLE IF EXISTS tmp_JobLog_;
    CREATE TEMPORARY TABLE tmp_JobLog_  ( 
        Message longtext     
    );
    
    DROP TEMPORARY TABLE IF EXISTS tmp_accountimport;
				CREATE TEMPORARY TABLE tmp_accountimport (				  
				  `AccountType` tinyint(3) default 0,
				  `CompanyId` INT,
				  `Title` VARCHAR(100),
				  `Owner` INT,
				  `Number` VARCHAR(50),
				  `AccountName` VARCHAR(100),
				  `NamePrefix` VARCHAR(50),
				  `FirstName` VARCHAR(50),
				  `LastName` VARCHAR(50),				  
				  `LeadSource` VARCHAR(50),
				  `Email` VARCHAR(100),
				  `Phone` VARCHAR(50),
				  `Address1` VARCHAR(100),
				  `Address2` VARCHAR(100),
				  `Address3` VARCHAR(100),
				  `City` VARCHAR(50),
				  `PostCode` VARCHAR(50),
				  `Country` VARCHAR(50),
				  `Status` INT,
				  `tags` VARCHAR(250),
				  `Website` VARCHAR(100),
				  `Mobile` VARCHAR(50),
				  `Fax` VARCHAR(50),
				  `Skype` VARCHAR(50),
				  `Twitter` VARCHAR(50),
				  `Employee` VARCHAR(50),
				  `Description` longtext,
				  `BillingEmail` VARCHAR(200),
				  `CurrencyId` INT,
				  `VatNumber` VARCHAR(50),
				  `created_at` datetime,
				  `created_by` VARCHAR(100),
				  `VerificationStatus` tinyint(3) default 0
				) ENGINE=InnoDB;  
				
   IF p_option = 0 
   THEN
   
   SELECT DISTINCT(AccountType) INTO v_accounttype from tblTempAccount WHERE ProcessID=p_processId;
   
	DELETE n1 FROM tblTempAccount n1, tblTempAccount n2 WHERE n1.tblTempAccountID < n2.tblTempAccountID 	 	
		AND  n1.CompanyId = n2.CompanyId		   
		AND  n1.AccountName = n2.AccountName		
		AND  n1.ProcessID = n2.ProcessID
 		AND  n1.ProcessID = p_processId and n2.ProcessID = p_processId;   
		 
		 select count(*) INTO totalduplicatecode FROM(
				SELECT count(`Number`) as n,`Number` FROM tblTempAccount where ProcessID = p_processId  GROUP BY `Number` HAVING n>1) AS tbl;   
				
				
		 IF  totalduplicatecode > 0
				THEN
						SELECT GROUP_CONCAT(Number) into errormessage FROM(
							select distinct Number, 1 as a FROM(
								SELECT count(`Number`) as n,`Number` FROM tblTempAccount where ProcessID = p_processId  GROUP BY `Number` HAVING n>1) AS tbl) as tbl2 GROUP by a;				
								
						SELECT 'DUPLICATE AccountNumber : \n\r' INTO errorheader;		
						
						INSERT INTO tmp_JobLog_ (Message)
							 SELECT CONCAT(errorheader ,errormessage);
							 
							 delete FROM tblTempAccount WHERE Number IN (
								  SELECT Number from(
								  	SELECT count(`Number`) as n,`Number` FROM tblTempAccount where ProcessID = p_processId  GROUP BY `Number` HAVING n>1
									  ) as tbl
								);
							
			END IF;		

			INSERT  INTO tblAccount
				  (	AccountType ,
					CompanyId ,
					Title,
					Owner ,
					`Number`,
					AccountName,
					NamePrefix,
					FirstName,
					LastName,
					LeadStatus,
					LeadSource,
					Email,
					Phone,
					Address1,
					Address2,
					Address3,
					City,
					PostCode,
					Country,
					Status,
					tags,
					Website,
					Mobile,
					Fax,
					Skype,
					Twitter,
					Employee,
					Description,
					BillingEmail,
					CurrencyId,
					VatNumber,
					created_at,
					created_by,
					VerificationStatus
                   )            
				   
				   SELECT  DISTINCT
					ta.AccountType,
					ta.CompanyId,
					ta.Title,
					ta.Owner,
					ta.Number as Number,
					ta.AccountName,
					ta.NamePrefix,
					ta.FirstName,
					ta.LastName,
					ta.LeadStatus,
					ta.LeadSource,
					ta.Email,
					ta.Phone,
					ta.Address1,
					ta.Address2,
					ta.Address3,
					ta.City,
					ta.PostCode,
					ta.Country,
					ta.Status,
					ta.tags,
					ta.Website,
					ta.Mobile,
					ta.Fax,
					ta.Skype,
					ta.Twitter,
					ta.Employee,
					ta.Description,
					ta.BillingEmail,
					ta.Currency as CurrencyId,
					ta.VatNumber,
					ta.created_at,
					ta.created_by,
					2 as VerificationStatus
					from tblTempAccount ta
						left join tblAccount a on ta.AccountName = a.AccountName
						 	AND ta.CompanyId = a.CompanyId
							AND ta.AccountType = a.AccountType 
						where ta.ProcessID = p_processId
						   AND ta.AccountType = v_accounttype
							AND a.AccountID is null
							AND ta.CompanyID = p_companyId;
			
  
      SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();
    
	 INSERT INTO tmp_JobLog_ (Message)
	 SELECT CONCAT(v_AffectedRecords_, ' Records Uploaded \n\r ' );
	 
	  DELETE  FROM tblTempAccount WHERE   tblTempAccount.ProcessID = p_processId;
	
	END IF;
	
		
	IF p_option = 1 	
   THEN
   
   		INSERT INTO tmp_accountimport
				select ta.AccountType ,
					ta.CompanyId ,
					ta.Title,
					ta.Owner ,
					ta.Number,
					ta.AccountName,
					ta.NamePrefix,
					ta.FirstName,
					ta.LastName,					
					ta.LeadSource,
					ta.Email,
					ta.Phone,
					ta.Address1,
					ta.Address2,
					ta.Address3,
					ta.City,
					ta.PostCode,
					ta.Country,
					ta.Status,
					ta.tags,
					ta.Website,
					ta.Mobile,
					ta.Fax,
					ta.Skype,
					ta.Twitter,
					ta.Employee,
					ta.Description,
					ta.BillingEmail,
					ta.Currency as CurrencyId,
					ta.VatNumber,
					ta.created_at,
					ta.created_by,
					2 as VerificationStatus				
				 FROM tblTempAccount ta
				left join tblAccount a on ta.AccountName=a.AccountName
					AND ta.CompanyId = a.CompanyId
					AND ta.AccountType = a.AccountType
				where ta.CompanyID = p_companyId 
				AND ta.ProcessID = p_processId
				AND ta.AccountType = 1
				AND a.AccountID is null			
				AND (p_tempaccountid = '' OR ( p_tempaccountid != '' AND FIND_IN_SET(ta.tblTempAccountID,p_tempaccountid) ))
				AND (p_companygatewayid = 0 OR ( ta.CompanyGatewayID = p_companygatewayid))
				group by ta.AccountName;
				
				
			SELECT GROUP_CONCAT(Number) into errormessage FROM(
			SELECT distinct ta.Number as Number,1 as an  from tblTempAccount ta 
				left join tblAccount a on ta.Number = a.Number				
					AND ta.CompanyId = a.CompanyId
					AND ta.AccountType = a.AccountType
				where ta.CompanyID = p_companyId 
				AND ta.ProcessID = p_processId
				AND ta.AccountType = 1
				AND a.AccountID is not null			
				AND (p_tempaccountid = '' OR ( p_tempaccountid != '' AND FIND_IN_SET(ta.tblTempAccountID,p_tempaccountid) ))
				AND (p_companygatewayid = 0 OR ( ta.CompanyGatewayID = p_companygatewayid)))tbl GROUP by an;
			
		  IF errormessage is not null
		  THEN
		  
		  		SELECT 'AccountNumber Already EXITS : \n\r' INTO errorheader;								
						INSERT INTO tmp_JobLog_ (Message)		
							 SELECT CONCAT(errorheader ,errormessage);
							 
				delete FROM tmp_accountimport WHERE Number IN (
								  SELECT Number from(
								  	SELECT distinct ta.Number as Number,1 as an  from tblTempAccount ta 
										left join tblAccount a on ta.Number = a.Number				
											AND ta.CompanyId = a.CompanyId
											AND ta.AccountType = a.AccountType
										where ta.CompanyID = p_companyId 
										AND ta.ProcessID = p_processId
										AND ta.AccountType = 1
										AND a.AccountID is not null			
										AND (p_tempaccountid = '' OR ( p_tempaccountid != '' AND FIND_IN_SET(ta.tblTempAccountID,p_tempaccountid) ))
										AND (p_companygatewayid = 0 OR ( ta.CompanyGatewayID = p_companygatewayid))
									  ) as tbl
								);			 
		  	
		  END IF;	
		
		INSERT  INTO tblAccount
				  (	AccountType ,
					CompanyId ,
					Title,
					Owner ,
					`Number`,
					AccountName,
					NamePrefix,
					FirstName,
					LastName,
					LeadSource,
					Email,
					Phone,
					Address1,
					Address2,
					Address3,
					City,
					PostCode,
					Country,
					Status,
					tags,
					Website,
					Mobile,
					Fax,
					Skype,
					Twitter,
					Employee,
					Description,
					BillingEmail,
				    CurrencyId,
					VatNumber,
					created_at,
					created_by,
					VerificationStatus
                   )
			SELECT  
					AccountType ,
					CompanyId ,
					Title,
					Owner ,
					`Number`,
					AccountName,
					NamePrefix,
					FirstName,
					LastName,
					LeadSource,
					Email,
					Phone,
					Address1,
					Address2,
					Address3,
					City,
					PostCode,
					Country,
					Status,
					tags,
					Website,
					Mobile,
					Fax,
					Skype,
					Twitter,
					Employee,
					Description,
					BillingEmail,
				    CurrencyId,
					VatNumber,
					created_at,
					created_by,
					VerificationStatus
				from tmp_accountimport;
								

			SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();	

			INSERT INTO tmp_JobLog_ (Message)
			SELECT CONCAT(v_AffectedRecords_, ' Records Uploaded \n\r ' );	
			
		
   
   END IF;   
   
 	 SELECT * from tmp_JobLog_;	      
	      
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSProcessRateTableRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSProcessRateTableRate`(
	IN `p_ratetableid` INT,
	IN `p_replaceAllRates` INT,
	IN `p_effectiveImmediately` INT,
	IN `p_processId` VARCHAR(200),
	IN `p_addNewCodesToCodeDeck` INT,
	IN `p_companyId` INT
)
BEGIN
	DECLARE v_AffectedRecords_ INT DEFAULT 0;
	 DECLARE     v_CodeDeckId_ INT ;
	 DECLARE totalduplicatecode INT(11);	 
	 DECLARE errormessage longtext;
	 DECLARE errorheader longtext;
    
	 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;    
	 
    DROP TEMPORARY TABLE IF EXISTS tmp_JobLog_;
    CREATE TEMPORARY TABLE tmp_JobLog_ (
        Message longtext
    );
    DROP TEMPORARY TABLE IF EXISTS tmp_TempRateTableRate_;
    CREATE TEMPORARY TABLE tmp_TempRateTableRate_ (
			`CodeDeckId` int ,
			`Code` varchar(50) ,
			`Description` varchar(200) ,
			`Rate` decimal(18, 6) ,
			`EffectiveDate` Datetime ,
			`Change` varchar(100) ,
			`ProcessId` varchar(200) ,
			`Preference` int ,
			`ConnectionFee` decimal(18, 6),
			`Interval1` int,
			`IntervalN` int,
			INDEX tmp_EffectiveDate (`EffectiveDate`),
			INDEX tmp_Code (`Code`),
			INDEX tmp_Change (`Change`)
    );
    
     DELETE n1 FROM tblTempRateTableRate n1 
	  INNER JOIN 
			(
			  SELECT MIN(EffectiveDate) as EffectiveDate,Code 
			  FROM tblTempRateTableRate WHERE ProcessId = p_processId
			GROUP BY Code
			HAVING COUNT(*)>1
			)n2 
			ON n1.Code = n2.Code
			AND n2.EffectiveDate = n1.EffectiveDate
			WHERE n1.ProcessId = p_processId;

		  INSERT INTO tmp_TempRateTableRate_
        SELECT distinct `CodeDeckId`,`Code`,`Description`,`Rate`,`EffectiveDate`,`Change`,`ProcessId`,`Preference`,`ConnectionFee`,`Interval1`,`IntervalN` FROM tblTempRateTableRate WHERE tblTempRateTableRate.ProcessId = p_processId;
		 
      
		
	 	     SELECT CodeDeckId INTO v_CodeDeckId_ FROM tmp_TempRateTableRate_ WHERE ProcessId = p_processId  LIMIT 1;

            UPDATE tmp_TempRateTableRate_ as tblTempRateTableRate
            LEFT JOIN tblRate
                ON tblRate.Code = tblTempRateTableRate.Code
                AND tblRate.CompanyID = p_companyId
                AND tblRate.CodeDeckId = tblTempRateTableRate.CodeDeckId
                AND tblRate.CodeDeckId =  v_CodeDeckId_
            SET  
                tblTempRateTableRate.Interval1 = CASE WHEN tblTempRateTableRate.Interval1 is not null  and tblTempRateTableRate.Interval1 > 0
                                            THEN    
                                                tblTempRateTableRate.Interval1
                                            ELSE
                                            CASE WHEN tblRate.Interval1 is not null  
                                            THEN    
                                                tblRate.Interval1
                                            ELSE CASE WHEN tblTempRateTableRate.Interval1 is null and (tblTempRateTableRate.Description LIKE '%gambia%' OR tblTempRateTableRate.Description LIKE '%mexico%')
                                                 THEN 
                                                    60
                                            ELSE CASE WHEN tblTempRateTableRate.Description LIKE '%USA%' 
                                                 THEN 
                                                    6
                                                 ELSE 
                                                    1
                                                END
                                            END

                                            END
                                            END,
                tblTempRateTableRate.IntervalN = CASE WHEN tblTempRateTableRate.IntervalN is not null  and tblTempRateTableRate.IntervalN > 0
                                            THEN    
                                                tblTempRateTableRate.IntervalN
                                            ELSE
                                                CASE WHEN tblRate.IntervalN is not null 
                                          THEN
                                            tblRate.IntervalN
                                          ElSE
                                            CASE
                                                WHEN tblTempRateTableRate.Description LIKE '%mexico%' THEN 60
                                            ELSE CASE
                                                WHEN tblTempRateTableRate.Description LIKE '%USA%' THEN 6
                                                
                                            ELSE 
                                            1
                                            END
                                            END
                                          END
                                          END;
                                           
          DROP TEMPORARY TABLE IF EXISTS tmp_TempRateTableRate2_;
			 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_TempRateTableRate2_ as (select * from tmp_TempRateTableRate_);   
 
			 IF  p_effectiveImmediately = 1
            THEN
                UPDATE tmp_TempRateTableRate_
                SET EffectiveDate = DATE_FORMAT (NOW(), '%Y-%m-%d')
                WHERE EffectiveDate < DATE_FORMAT (NOW(), '%Y-%m-%d');
          END IF;
          
          
          select count(*) INTO totalduplicatecode FROM(
				SELECT count(code) as c,code FROM tmp_TempRateTableRate_  GROUP BY Code,EffectiveDate HAVING c>1) AS tbl;
			
			
			IF  totalduplicatecode > 0
				THEN
						SELECT GROUP_CONCAT(code) into errormessage FROM(
							select distinct code, 1 as a FROM(
								SELECT   count(code) as c,code FROM tmp_TempRateTableRate_  GROUP BY Code,EffectiveDate HAVING c>1) AS tbl) as tbl2 GROUP by a;				
						INSERT INTO tmp_JobLog_ (Message)		
						SELECT DISTINCT
                        CONCAT(code , ' DUPLICATE CODE')
                        FROM(
								SELECT   count(code) as c,code FROM tmp_TempRateTableRate_  GROUP BY Code,EffectiveDate HAVING c>1) as tbl;
							
			END IF;
			
			IF  totalduplicatecode = 0
			THEN					

            IF  p_addNewCodesToCodeDeck = 1
            THEN

                INSERT INTO tblRate (CompanyID,
                Code,
                Description,
                CreatedBy,
                CountryID,
                CodeDeckId,
                Interval1,
                IntervalN)
                    SELECT DISTINCT
                        p_companyId,
                        vc.Code,
                        vc.Description,
                        'WindowsService',
                       
                        fnGetCountryIdByCodeAndCountry (vc.Code ,vc.Description) AS CountryID,
                        CodeDeckId,
                        Interval1,
                        IntervalN
                    FROM
                    (
                        SELECT DISTINCT
                            tblTempRateTableRate.Code,
                            tblTempRateTableRate.Description,
                            tblTempRateTableRate.CodeDeckId,
                            tblTempRateTableRate.Interval1,
                            tblTempRateTableRate.IntervalN

                        FROM tmp_TempRateTableRate_  as tblTempRateTableRate
                        LEFT JOIN tblRate
					             ON tblRate.Code = tblTempRateTableRate.Code
					             AND tblRate.CompanyID = p_companyId
					             AND tblRate.CodeDeckId = tblTempRateTableRate.CodeDeckId
                        WHERE tblRate.RateID IS NULL
                        AND tblTempRateTableRate.`Change` NOT IN ('Delete', 'R', 'D', 'Blocked', 'Block')
							) vc;
                         
                   
                        
                        


                SELECT GROUP_CONCAT(code) into errormessage FROM(	
                    SELECT DISTINCT
                        tblTempRateTableRate.Code as Code,1 as a
                    FROM tmp_TempRateTableRate_  as tblTempRateTableRate 
                    INNER JOIN tblRate
				             ON tblRate.Code = tblTempRateTableRate.Code
				             AND tblRate.CompanyID = p_companyId
				             AND tblRate.CodeDeckId = tblTempRateTableRate.CodeDeckId
						  WHERE tblRate.CountryID IS NULL
                    AND tblTempRateTableRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked','Block')) as tbl GROUP BY a;
                    
                    IF errormessage IS NOT NULL
	                 THEN
	                 		INSERT INTO tmp_JobLog_ (Message)
		                  SELECT DISTINCT
      	                  CONCAT(tblTempRateTableRate.Code , ' INVALID CODE - COUNTRY NOT FOUND ')
      	                  FROM tmp_TempRateTableRate_  as tblTempRateTableRate 
                    INNER JOIN tblRate
				             ON tblRate.Code = tblTempRateTableRate.Code
				             AND tblRate.CompanyID = p_companyId
				             AND tblRate.CodeDeckId = tblTempRateTableRate.CodeDeckId
						  WHERE tblRate.CountryID IS NULL
                    AND tblTempRateTableRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked','Block');
                    
					 	 END IF;
					 	 
            ELSE
                SELECT GROUP_CONCAT(code) into errormessage FROM(
                    SELECT DISTINCT
                        c.Code as code, 1 as a
                    FROM
                    (
                        SELECT DISTINCT
                            tblTempRateTableRate.Code,
                            tblTempRateTableRate.Description
                        FROM tmp_TempRateTableRate_  as tblTempRateTableRate 
                        LEFT JOIN tblRate
			                ON tblRate.Code = tblTempRateTableRate.Code
			                AND tblRate.CompanyID = p_companyId
			                AND tblRate.CodeDeckId = tblTempRateTableRate.CodeDeckId
								WHERE tblRate.RateID IS NULL
                        AND tblTempRateTableRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked', 'Block')) c) as tbl GROUP BY a;
                        
                 IF errormessage IS NOT NULL
                  THEN
                     INSERT INTO tmp_JobLog_ (Message)
	                  SELECT DISTINCT
                        CONCAT(tbl.Code , ' CODE DOES NOT EXIST IN CODE DECK')
                         FROM
               	     (
                        SELECT DISTINCT
                            tblTempRateTableRate.Code,
                            tblTempRateTableRate.Description
                        FROM tmp_TempRateTableRate_  as tblTempRateTableRate 
                        LEFT JOIN tblRate
			                ON tblRate.Code = tblTempRateTableRate.Code
			                AND tblRate.CompanyID = p_companyId
			                AND tblRate.CodeDeckId = tblTempRateTableRate.CodeDeckId
								WHERE tblRate.RateID IS NULL
                        AND tblTempRateTableRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked', 'Block')) as tbl;									

					 	END IF;


            END IF;

            IF  p_replaceAllRates = 1
            THEN


                DELETE FROM tblRateTableRate
                WHERE RateTableId = p_ratetableid;

            END IF;

            DELETE tblRateTableRate
                FROM tblRateTableRate
                JOIN tblRate
                    ON tblRate.RateID = tblRateTableRate.RateId
                    AND tblRate.CompanyID = p_companyId
                    JOIN tmp_TempRateTableRate_ as tblTempRateTableRate
                        ON tblRate.Code = tblTempRateTableRate.Code
            WHERE tblRateTableRate.RateTableId = p_ratetableid
                AND tblTempRateTableRate.Change IN ('Delete', 'R', 'D', 'Blocked', 'Block');
           

            UPDATE tblRateTableRate
					INNER JOIN tblRate
					ON tblRateTableRate.RateId = tblRate.RateId
					AND tblRateTableRate.RateTableId = p_ratetableid
					INNER JOIN tmp_TempRateTableRate_ as tblTempRateTableRate
					ON tblRate.Code = tblTempRateTableRate.Code
					AND tblRate.CompanyID = p_companyId
					AND tblRate.CodeDeckId = tblTempRateTableRate.CodeDeckId
					AND tblRateTableRate.RateId = tblRate.RateId
					SET tblRateTableRate.ConnectionFee = tblTempRateTableRate.ConnectionFee,
					tblRateTableRate.Interval1 = tblTempRateTableRate.Interval1,
					tblRateTableRate.IntervalN = tblTempRateTableRate.IntervalN
					            WHERE tblRateTableRate.RateTableId = p_ratetableid;

            DELETE tblTempRateTableRate
                FROM tmp_TempRateTableRate_ as tblTempRateTableRate
                JOIN tblRate
                    ON tblRate.Code = tblTempRateTableRate.Code
                    JOIN tblRateTableRate
                        ON tblRateTableRate.RateId = tblRate.RateId
                        AND tblRate.CompanyID = p_companyId
                        AND tblRate.CodeDeckId = tblTempRateTableRate.CodeDeckId
                        AND tblRateTableRate.RateTableId = p_ratetableid
                        AND tblTempRateTableRate.Rate = tblRateTableRate.Rate
                        AND (
                        tblRateTableRate.EffectiveDate = tblTempRateTableRate.EffectiveDate 
                        OR  
                        (
                        DATE_FORMAT (tblRateTableRate.EffectiveDate, '%Y-%m-%d') = DATE_FORMAT (tblTempRateTableRate.EffectiveDate, '%Y-%m-%d')
                        )
                        OR 1 = (CASE
                            WHEN tblTempRateTableRate.EffectiveDate > NOW() THEN 1 
                            ELSE 0
                        END)
                        )
            WHERE  tblTempRateTableRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked', 'Block');

            SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();


            UPDATE tmp_TempRateTableRate_ as tblTempRateTableRate
            JOIN tblRate
                ON tblRate.Code = tblTempRateTableRate.Code
                AND tblRate.CompanyID = p_companyId
                AND tblRate.CodeDeckId = tblTempRateTableRate.CodeDeckId
                JOIN tblRateTableRate
                    ON tblRateTableRate.RateId = tblRate.RateId
                    AND tblRateTableRate.RateTableId = p_ratetableid
				SET tblRateTableRate.Rate = tblTempRateTableRate.Rate
            WHERE tblTempRateTableRate.Rate <> tblRateTableRate.Rate
            AND tblTempRateTableRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked', 'Block')
            AND DATE_FORMAT (tblRateTableRate.EffectiveDate, '%Y-%m-%d') = DATE_FORMAT (tblTempRateTableRate.EffectiveDate, '%Y-%m-%d');

            SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();


            INSERT INTO tblRateTableRate (RateTableId,
            RateId,
            Rate,
            EffectiveDate,
            ConnectionFee,
            Interval1,
            IntervalN
            )
                SELECT DISTINCT
                    p_ratetableid,
                    tblRate.RateID,
                    tblTempRateTableRate.Rate,
                    tblTempRateTableRate.EffectiveDate,
                    tblTempRateTableRate.ConnectionFee,
                    tblTempRateTableRate.Interval1,
                    tblTempRateTableRate.IntervalN
                FROM tmp_TempRateTableRate_ as tblTempRateTableRate
                JOIN tblRate
                    ON tblRate.Code = tblTempRateTableRate.Code
                    AND tblRate.CompanyID = p_companyId
                    AND tblRate.CodeDeckId = tblTempRateTableRate.CodeDeckId
                LEFT JOIN tblRateTableRate
						   ON tblRate.RateID = tblRateTableRate.RateId
						   AND tblRateTableRate.RateTableId = p_ratetableid
						   AND tblRateTableRate.EffectiveDate =  tblTempRateTableRate.EffectiveDate
					 WHERE tblRateTableRate.RateTableRateID IS NULL
                AND tblTempRateTableRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked','Block')
                AND tblTempRateTableRate.EffectiveDate >= DATE_FORMAT (NOW(), '%Y-%m-%d');

            SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();

	END IF;
      
	 INSERT INTO tmp_JobLog_ (Message)
	 SELECT CONCAT(v_AffectedRecords_ , ' Records Uploaded \n\r ' );
	 
 	 SELECT * from tmp_JobLog_;
	 DELETE  FROM tblTempRateTableRate WHERE  ProcessId = p_processId;
	 
	 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSProcessVendorRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSProcessVendorRate`(
	IN `p_accountId` INT,
	IN `p_trunkId` INT,
	IN `p_replaceAllRates` INT,
	IN `p_effectiveImmediately` INT,
	IN `p_processId` VARCHAR(200),
	IN `p_addNewCodesToCodeDeck` INT,
	IN `p_companyId` INT,
	IN `p_forbidden` INT,
	IN `p_preference` INT,
	IN `p_dialstringid` INT,
	IN `p_dialcodeSeparator` VARCHAR(50)
)
BEGIN

    DECLARE v_AffectedRecords_ INT DEFAULT 0;
	 DECLARE     v_CodeDeckId_ INT ;
    DECLARE totaldialstringcode INT(11) DEFAULT 0;	 
    DECLARE newstringcode INT(11) DEFAULT 0;
	 DECLARE totalduplicatecode INT(11);	 
	 DECLARE errormessage longtext;
	 DECLARE errorheader longtext;
    
	 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;    
	 
	 
    DROP TEMPORARY TABLE IF EXISTS tmp_JobLog_;
    CREATE TEMPORARY TABLE tmp_JobLog_ (
        Message longtext
    );
    
    
    
    DROP TEMPORARY TABLE IF EXISTS tmp_split_VendorRate_;
    CREATE TEMPORARY TABLE tmp_split_VendorRate_ (
    		`TempVendorRateID` int,
			`CodeDeckId` int ,
			`Code` varchar(50) ,
			`Description` varchar(200) ,
			`Rate` decimal(18, 6) ,
			`EffectiveDate` Datetime ,
			`Change` varchar(100) ,
			`ProcessId` varchar(200) ,
			`Preference` varchar(100) ,
			`ConnectionFee` decimal(18, 6),
			`Interval1` int,
			`IntervalN` int,
			`Forbidden` varchar(100) ,
			INDEX tmp_EffectiveDate (`EffectiveDate`),
			INDEX tmp_Code (`Code`),
            INDEX tmp_CC (`Code`,`Change`),
			INDEX tmp_Change (`Change`)
    );
    
    DROP TEMPORARY TABLE IF EXISTS tmp_TempVendorRate_;
    CREATE TEMPORARY TABLE tmp_TempVendorRate_ (
			`CodeDeckId` int ,
			`Code` varchar(50) ,
			`Description` varchar(200) ,
			`Rate` decimal(18, 6) ,
			`EffectiveDate` Datetime ,
			`Change` varchar(100) ,
			`ProcessId` varchar(200) ,
			`Preference` varchar(100) ,
			`ConnectionFee` decimal(18, 6),
			`Interval1` int,
			`IntervalN` int,
			`Forbidden` varchar(100) ,
			INDEX tmp_EffectiveDate (`EffectiveDate`),
			INDEX tmp_Code (`Code`),
            INDEX tmp_CC (`Code`,`Change`),
			INDEX tmp_Change (`Change`)
    );
    
    
    
    DROP TEMPORARY TABLE IF EXISTS tmp_Delete_VendorRate;
	CREATE TEMPORARY TABLE tmp_Delete_VendorRate (
     	VendorRateID INT,
      AccountId INT,
      TrunkID INT,
      RateId INT,
      Code VARCHAR(50),
      Description VARCHAR(200),
      Rate DECIMAL(18, 6),
      EffectiveDate DATETIME,
		Interval1 INT,
		IntervalN INT,
		ConnectionFee DECIMAL(18, 6),
		deleted_at DATETIME,
        INDEX tmp_VendorRateDiscontinued_VendorRateID (`VendorRateID`)
	);

    
    		CALL  prc_checkDialstringAndDupliacteCode(p_companyId,p_processId,p_dialstringid,p_effectiveImmediately,p_dialcodeSeparator);
    		
    		SELECT COUNT(*) AS COUNT INTO newstringcode from tmp_JobLog_; 
	
	 
    IF newstringcode = 0
    THEN
   		
	         IF  p_addNewCodesToCodeDeck = 1
            THEN

					
                INSERT INTO tblRate (CompanyID,
                Code,
                Description,
                CreatedBy,
                CountryID,
                CodeDeckId,
                Interval1,
                IntervalN)
                    SELECT DISTINCT
                        p_companyId,
                        vc.Code,
                        vc.Description,
                        'WindowsService',
                       
                       fnGetCountryIdByCodeAndCountry (vc.Code ,vc.Description) AS CountryID,
                        CodeDeckId,
                        Interval1,
                        IntervalN
                    FROM
                    (
                        SELECT DISTINCT
                            tblTempVendorRate.Code,
                            tblTempVendorRate.Description,
                            tblTempVendorRate.CodeDeckId,
                            tblTempVendorRate.Interval1,
                            tblTempVendorRate.IntervalN

                        FROM tmp_TempVendorRate_  as tblTempVendorRate
                   	     LEFT JOIN tblRate
						             ON tblRate.Code = tblTempVendorRate.Code
						             AND tblRate.CompanyID = p_companyId
						             AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
	                        WHERE tblRate.RateID IS NULL
	                     	   AND tblTempVendorRate.`Change` NOT IN ('Delete', 'R', 'D', 'Blocked', 'Block')) vc; 
                  						


               	SELECT GROUP_CONCAT(Code) into errormessage FROM(
                    SELECT DISTINCT
                        tblTempVendorRate.Code as Code, 1 as a
                    FROM tmp_TempVendorRate_  as tblTempVendorRate 
	                    INNER JOIN tblRate
					             ON tblRate.Code = tblTempVendorRate.Code
					             AND tblRate.CompanyID = p_companyId
					             AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
							  WHERE tblRate.CountryID IS NULL
	                 		   AND tblTempVendorRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked','Block')) as tbl GROUP BY a;
                    
                  IF errormessage IS NOT NULL
                  THEN
                  
                    INSERT INTO tmp_JobLog_ (Message)
                    	 SELECT DISTINCT
                        CONCAT(tblTempVendorRate.Code , ' INVALID CODE - COUNTRY NOT FOUND')
                        FROM tmp_TempVendorRate_  as tblTempVendorRate 
                    INNER JOIN tblRate
				             ON tblRate.Code = tblTempVendorRate.Code
				             AND tblRate.CompanyID = p_companyId
				             AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
						  WHERE tblRate.CountryID IS NULL
                    AND tblTempVendorRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked','Block');
                        
					 	END IF;		
					 
            ELSE
                
                SELECT GROUP_CONCAT(code) into errormessage FROM(
                    SELECT DISTINCT
                        c.Code as code, 1 as a
                    FROM
                    (
                        SELECT DISTINCT
                            tblTempVendorRate.Code,
                            tblTempVendorRate.Description
                        FROM tmp_TempVendorRate_  as tblTempVendorRate 
                      	  LEFT JOIN tblRate
				                ON tblRate.Code = tblTempVendorRate.Code
				          	      AND tblRate.CompanyID = p_companyId
				         	       AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
									WHERE tblRate.RateID IS NULL
	                  	      AND tblTempVendorRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked', 'Block')) c) as tbl GROUP BY a;
                        
                  IF errormessage IS NOT NULL
                  THEN
                    INSERT INTO tmp_JobLog_ (Message)
                    		SELECT DISTINCT
                        CONCAT(tbl.Code , ' CODE DOES NOT EXIST IN CODE DECK')
                        FROM
                    (
                        SELECT DISTINCT
                            tblTempVendorRate.Code,
                            tblTempVendorRate.Description
                        FROM tmp_TempVendorRate_  as tblTempVendorRate 
                        LEFT JOIN tblRate
			                ON tblRate.Code = tblTempVendorRate.Code
			                AND tblRate.CompanyID = p_companyId
			                AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
								WHERE tblRate.RateID IS NULL
                        AND tblTempVendorRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked', 'Block')) as tbl;
                        
					 	END IF;		


            END IF;

            IF  p_replaceAllRates = 1
            THEN


                DELETE FROM tblVendorRate
                WHERE AccountId = p_accountId
                    AND TrunkID = p_trunkId;

            END IF;
            				
				
       
		 
		          
            	INSERT INTO tmp_Delete_VendorRate(
			 	VendorRateID,
		      AccountId,
		      TrunkID,
		      RateId,
		      Code,
		      Description,
		      Rate,
		      EffectiveDate,
				Interval1,
				IntervalN,
				ConnectionFee,
				deleted_at
			 )
    		SELECT tblVendorRate.VendorRateID,
    			    p_accountId AS AccountId,
					 p_trunkId AS TrunkID,
					 tblVendorRate.RateId,
					 tblRate.Code,
					 tblRate.Description,
					 tblVendorRate.Rate,
					 tblVendorRate.EffectiveDate,
					 tblVendorRate.Interval1,
					 tblVendorRate.IntervalN,
					 tblVendorRate.ConnectionFee,
					 now() AS deleted_at  		
                FROM tblVendorRate
                JOIN tblRate
                    ON tblRate.RateID = tblVendorRate.RateId
                    AND tblRate.CompanyID = p_companyId
                    JOIN tmp_TempVendorRate_ as tblTempVendorRate
                        ON tblRate.Code = tblTempVendorRate.Code
            WHERE tblVendorRate.AccountId = p_accountId
                AND tblVendorRate.TrunkId = p_trunkId
                AND tblTempVendorRate.Change IN ('Delete', 'R', 'D', 'Blocked', 'Block');    
           
           
			  	CALL prc_InsertDiscontinuedVendorRate(p_accountId,p_trunkId); 

            UPDATE tblVendorRate
					INNER JOIN tblRate
						ON tblVendorRate.RateId = tblRate.RateId
						AND tblVendorRate.AccountId = p_accountId
						AND tblVendorRate.TrunkId = p_trunkId
					INNER JOIN tmp_TempVendorRate_ as tblTempVendorRate
						ON tblRate.Code = tblTempVendorRate.Code
						AND tblRate.CompanyID = p_companyId
						AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
						AND tblVendorRate.RateId = tblRate.RateId
					SET tblVendorRate.ConnectionFee = tblTempVendorRate.ConnectionFee,
						tblVendorRate.Interval1 = tblTempVendorRate.Interval1,
							tblVendorRate.IntervalN = tblTempVendorRate.IntervalN
					WHERE tblVendorRate.AccountId = p_accountId
			            AND tblVendorRate.TrunkId = p_trunkId ;
			    
				 
            IF  p_forbidden = 1 OR p_dialstringid > 0
				THEN
					
					INSERT INTO tblVendorBlocking
					(
						 `AccountId`
						 ,`RateId`
						 ,`TrunkID`
						 ,`BlockedBy`
					)
					SELECT distinct
					   p_accountId as AccountId,
					   tblRate.RateID as RateId,						
						p_trunkId as TrunkID,
						'RMService' as BlockedBy
					 FROM tmp_TempVendorRate_ as tblTempVendorRate
					 INNER JOIN tblRate 
						ON tblRate.Code = tblTempVendorRate.Code
						AND tblRate.CompanyID = p_companyId
			         AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
			       LEFT JOIN tblVendorBlocking vb 			       		
					 	ON vb.AccountId=p_accountId
						 AND vb.RateId = tblRate.RateID
						 AND vb.TrunkID = p_trunkId   
					WHERE tblTempVendorRate.Forbidden IN('B')
					 AND vb.VendorBlockingId is null;
					 
					 DELETE tblVendorBlocking 
					 FROM tblVendorBlocking 
					INNER JOIN(
						select VendorBlockingId 
						FROM `tblVendorBlocking` tv
							INNER JOIN(
							 SELECT 
							 	tblRate.RateId as RateId
							 FROM tmp_TempVendorRate_ as tblTempVendorRate
							INNER JOIN tblRate 
								ON tblRate.Code = tblTempVendorRate.Code
								AND tblRate.CompanyID = p_companyId
					         AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
							WHERE tblTempVendorRate.Forbidden IN('UB')
					     )tv1 on  tv.AccountId=p_accountId
						  	AND tv.TrunkID=p_trunkId
						  	AND tv.RateId = tv1.RateID
					 )vb2 on vb2.VendorBlockingId = tblVendorBlocking.VendorBlockingId;
	
				END IF;
				
				
				
				IF  p_preference = 1
				THEN
				
				INSERT INTO tblVendorPreference
					(
						 `AccountId`
						 ,`Preference`
						 ,`RateId`
						 ,`TrunkID`
						 ,`CreatedBy`
						 ,`created_at`
					)
				SELECT 
					   p_accountId AS AccountId,
					   tblTempVendorRate.Preference as Preference,
					   tblRate.RateID AS RateId,						
						p_trunkId AS TrunkID,
						'RMService' AS CreatedBy,
						NOW() AS created_at
					 FROM tmp_TempVendorRate_ as tblTempVendorRate
					INNER JOIN tblRate 
						ON tblRate.Code = tblTempVendorRate.Code
						AND tblRate.CompanyID = p_companyId
			         AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
					LEFT JOIN tblVendorPreference vp 
						ON vp.RateId=tblRate.RateID
						AND vp.AccountId = p_accountId 	
						AND vp.TrunkID = p_trunkId
					WHERE  tblTempVendorRate.Preference IS NOT NULL
					 AND  tblTempVendorRate.Preference > 0
					 AND  vp.VendorPreferenceID IS NULL;
					 
					 
					 UPDATE tblVendorPreference
					 	INNER JOIN tblRate 
					 		ON tblVendorPreference.RateId=tblRate.RateID
				      INNER JOIN tmp_TempVendorRate_ as tblTempVendorRate
							ON tblTempVendorRate.Code = tblRate.Code							
				         AND tblTempVendorRate.CodeDeckId = tblRate.CodeDeckId   
				         AND tblRate.CompanyID = p_companyId
				      SET tblVendorPreference.Preference = tblTempVendorRate.Preference
						WHERE tblVendorPreference.AccountId = p_accountId  
							AND tblVendorPreference.TrunkID = p_trunkId
							AND  tblTempVendorRate.Preference IS NOT NULL
							AND  tblTempVendorRate.Preference > 0
							AND tblVendorPreference.VendorPreferenceID IS NOT NULL; 
							
						DELETE tblVendorPreference
							from	tblVendorPreference
					 	INNER JOIN tblRate 
					 		ON tblVendorPreference.RateId=tblRate.RateID
				      INNER JOIN tmp_TempVendorRate_ as tblTempVendorRate
							ON tblTempVendorRate.Code = tblRate.Code							
				         AND tblTempVendorRate.CodeDeckId = tblRate.CodeDeckId   
				         AND tblRate.CompanyID = p_companyId
						WHERE tblVendorPreference.AccountId = p_accountId  
							AND tblVendorPreference.TrunkID = p_trunkId
							AND  tblTempVendorRate.Preference IS NOT NULL
							AND  tblTempVendorRate.Preference = '' 
							AND tblVendorPreference.VendorPreferenceID IS NOT NULL; 
					 
				END IF; 
				         

            DELETE tblTempVendorRate
                FROM tmp_TempVendorRate_ as tblTempVendorRate
                JOIN tblRate
                    ON tblRate.Code = tblTempVendorRate.Code
                    JOIN tblVendorRate
                        ON tblVendorRate.RateId = tblRate.RateId
                        AND tblRate.CompanyID = p_companyId
                        AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
                        AND tblVendorRate.AccountId = p_accountId
                        AND tblVendorRate.TrunkId = p_trunkId
                        AND tblTempVendorRate.Rate = tblVendorRate.Rate
                        AND (
                        tblVendorRate.EffectiveDate = tblTempVendorRate.EffectiveDate 
                        OR  
                        (
                        DATE_FORMAT (tblVendorRate.EffectiveDate, '%Y-%m-%d') = DATE_FORMAT (tblTempVendorRate.EffectiveDate, '%Y-%m-%d')
                        )
                        OR 1 = (CASE
                            WHEN tblTempVendorRate.EffectiveDate > NOW() THEN 1 
                            ELSE 0
                        END)
                        )
            WHERE  tblTempVendorRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked', 'Block'); 

            SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();

				
            UPDATE tmp_TempVendorRate_ as tblTempVendorRate
            JOIN tblRate
                ON tblRate.Code = tblTempVendorRate.Code
                AND tblRate.CompanyID = p_companyId
                AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
                JOIN tblVendorRate
                    ON tblVendorRate.RateId = tblRate.RateId
                    AND tblVendorRate.AccountId = p_accountId
                    AND tblVendorRate.TrunkId = p_trunkId
				SET tblVendorRate.Rate = tblTempVendorRate.Rate
            WHERE tblTempVendorRate.Rate <> tblVendorRate.Rate
            AND tblTempVendorRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked', 'Block')
            AND DATE_FORMAT (tblVendorRate.EffectiveDate, '%Y-%m-%d') = DATE_FORMAT (tblTempVendorRate.EffectiveDate, '%Y-%m-%d');



            SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();

				
            INSERT INTO tblVendorRate (AccountId,
            TrunkID,
            RateId,
            Rate,
            EffectiveDate,
            ConnectionFee,
            Interval1,
            IntervalN
            )
                SELECT DISTINCT
                    p_accountId,
                    p_trunkId,
                    tblRate.RateID,
                    tblTempVendorRate.Rate,
                    tblTempVendorRate.EffectiveDate,
                    tblTempVendorRate.ConnectionFee,
                    tblTempVendorRate.Interval1,
                    tblTempVendorRate.IntervalN
                FROM tmp_TempVendorRate_ as tblTempVendorRate
                JOIN tblRate
                    ON tblRate.Code = tblTempVendorRate.Code
                    AND tblRate.CompanyID = p_companyId
                    AND tblRate.CodeDeckId = tblTempVendorRate.CodeDeckId
                LEFT JOIN tblVendorRate
					      ON tblRate.RateID = tblVendorRate.RateId
					      AND tblVendorRate.AccountId = p_accountId
					      AND tblVendorRate.trunkid = p_trunkId
					      AND tblTempVendorRate.EffectiveDate = tblVendorRate.EffectiveDate
					WHERE tblVendorRate.VendorRateID IS NULL
                AND tblTempVendorRate.Change NOT IN ('Delete', 'R', 'D', 'Blocked','Block')
                AND tblTempVendorRate.EffectiveDate >= DATE_FORMAT (NOW(), '%Y-%m-%d');

            SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS(); 

	 
 END IF;	 
	 
	 					 INSERT INTO tmp_JobLog_ (Message)
	 	SELECT CONCAT(v_AffectedRecords_ , ' Records Uploaded \n\r ' );
	 
 	 SELECT * FROM tmp_JobLog_;
      DELETE  FROM tblTempVendorRate WHERE  ProcessId = p_processId; 
	 
	 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSUpdateAllInProgressJobStatusToPending` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSUpdateAllInProgressJobStatusToPending`(IN `p_companyid` INT, IN `p_ModifiedBy` LONGTEXT )
BEGIN
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  UPDATE tblJob 
  SET JobStatusID = ( Select tblJobStatus.JobStatusID from tblJobStatus where tblJobStatus.Code = 'P' )
  , updated_at = NOW()
  , ModifiedBy = p_ModifiedBy
  where 
  CompanyID =  p_companyid
 AND JobStatusID = ( Select tblJobStatus.JobStatusID from tblJobStatus where tblJobStatus.Code = 'I' )    ;
 
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_split` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `sp_split`(IN `toSplit` text, IN `target` char(255))
BEGIN
	
	SET @tableName = 'tmpSplit';
	SET @fieldName = 'variable';

	
	SET @sql := CONCAT('DROP TABLE IF EXISTS ', @tableName);
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

	
	SET @sql := CONCAT('CREATE TEMPORARY TABLE ', @tableName, ' (', @fieldName, ' VARCHAR(1000))');
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

	
	SET @vars := toSplit;
	SET @vars := CONCAT("('", REPLACE(@vars, ",", "'),('"), "')");

	
	SET @sql := CONCAT('INSERT INTO ', @tableName, ' VALUES ', @vars);
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

	
	IF target IS NULL THEN
		SET @sql := CONCAT('SELECT TRIM(`', @fieldName, '`) AS `', @fieldName, '` FROM ', @tableName);
	ELSE
		SET @sql := CONCAT('INSERT INTO ', target, ' SELECT TRIM(`', @fieldName, '`) FROM ', @tableName);
	END IF;

	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `testProc` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `testProc`(IN `p_code` TEXT, IN `p_country` TEXT)
BEGIN

DECLARE v_countryId int;

DROP TEMPORARY TABLE IF EXISTS tmp_Prefix;    
CREATE TEMPORARY TABLE tmp_Prefix (Prefix varchar(500) , CountryID int);

INSERT INTO tmp_Prefix 
SELECT DISTINCT
 tblCountry.Prefix,
 tblCountry.CountryID
FROM tblCountry
LEFT OUTER JOIN
 (
	SELECT
	 Prefix
	FROM tblCountry
	GROUP BY Prefix
	HAVING COUNT(*) > 1) d 
	ON tblCountry.Prefix = d.Prefix
Where (p_code LIKE CONCAT(tblCountry.Prefix, '%') AND d.Prefix IS NULL)
	 OR (p_code LIKE CONCAT(tblCountry.Prefix, '%') AND d.Prefix IS NOT NULL 
 		AND (p_country LIKE CONCAT('%', tblCountry.Country, '%'))) ;


DROP TEMPORARY TABLE IF EXISTS tmp_PrefixCopy;  
CREATE TEMPORARY TABLE tmp_PrefixCopy (SELECT * FROM tmp_Prefix);

SELECT DISTINCT p.CountryID , p_code  
FROM tmp_Prefix  p 
JOIN (
		SELECT MAX(Prefix) AS  Prefix 
		 FROM  tmp_PrefixCopy 
	  ) AS MaxPrefix
	 ON MaxPrefix.Prefix = p.Prefix ;
	 


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `vwVendorCurrentRates` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `vwVendorCurrentRates`(IN `p_AccountID` INT, IN `p_Trunks` LONGTEXT, IN `p_Effective` VARCHAR(50))
BEGIN	

	DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates_;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates_(
		AccountId int,
		Code varchar(50),
		Description varchar(200),
		Rate float,
		EffectiveDate date,
		TrunkID int,
		CountryID int,
		RateID int,
		Interval1 INT,
		IntervalN varchar(100),
		ConnectionFee float
    );
    
    DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_;
    CREATE TEMPORARY TABLE tmp_VendorRate_ (
        TrunkId INT,
	 	  RateId INT,
        Rate DECIMAL(18,6),
        EffectiveDate DATE,
        Interval1 INT,
        IntervalN INT,
        ConnectionFee DECIMAL(18,6),
        INDEX tmp_RateTable_RateId (`RateId`)  
    );
        INSERT INTO tmp_VendorRate_
        SELECT   `TrunkID`, `RateId`, `Rate`, `EffectiveDate`, `Interval1`, `IntervalN`, `ConnectionFee`
		  FROM tblVendorRate WHERE tblVendorRate.AccountId =  p_AccountID 
								AND FIND_IN_SET(tblVendorRate.TrunkId,p_Trunks) != 0 
                        AND 
								(
					  				(p_Effective = 'Now' AND EffectiveDate <= NOW()) 
								  	OR 
								  	(p_Effective = 'Future' AND EffectiveDate > NOW())
								  	OR 
								  	(p_Effective = 'All')
								);

    DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate4_;
       CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorRate4_ as (select * from tmp_VendorRate_);	        
      DELETE n1 FROM tmp_VendorRate_ n1, tmp_VendorRate4_ n2 WHERE n1.EffectiveDate < n2.EffectiveDate 
 	   AND n1.TrunkID = n2.TrunkID
	   AND  n1.RateId = n2.RateId
		AND n1.EffectiveDate <= NOW()
		AND n2.EffectiveDate <= NOW();
		

    INSERT INTO tmp_VendorCurrentRates_ 
    SELECT DISTINCT
    p_AccountID,
    r.Code,
    r.Description,
    v_1.Rate,
    DATE_FORMAT (v_1.EffectiveDate, '%Y-%m-%d') AS EffectiveDate,
    v_1.TrunkID,
    r.CountryID,
    r.RateID,
   	CASE WHEN v_1.Interval1 is not null
   		THEN v_1.Interval1
    	ELSE r.Interval1
    END as  Interval1,
    CASE WHEN v_1.IntervalN is not null
    	THEN v_1.IntervalN
        ELSE r.IntervalN
    END IntervalN,
    v_1.ConnectionFee
    FROM tmp_VendorRate_ AS v_1
	INNER JOIN tblRate AS r
    	ON r.RateID = v_1.RateId;	 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `vwVendorSippySheet` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `vwVendorSippySheet`(IN `p_AccountID` INT, IN `p_Trunks` LONGTEXT, IN `p_Effective` VARCHAR(50))
BEGIN

DROP TEMPORARY TABLE IF EXISTS tmp_VendorSippySheet_;
   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorSippySheet_(
			RateID int,
			`Action [A|D|U|S|SA` varchar(50),
			id varchar(10),
			Prefix varchar(50),
			COUNTRY varchar(200),
			Preference int,
			`Interval 1` int,
			`Interval N` int,
			`Price 1` float,
			`Price N` float,
			`1xx Timeout` int,
			`2xx Timeout` INT,
			Huntstop int,
			Forbidden int,
			`Activation Date` varchar(10),
			`Expiration Date` varchar(10),
			AccountID int,
			TrunkID int
	);
	

call vwVendorCurrentRates(p_AccountID,p_Trunks,p_Effective); 

INSERT INTO tmp_VendorSippySheet_ 
SELECT
    NULL AS RateID,
    'A' AS `Action [A|D|U|S|SA`,
    '' AS id,
    Concat('' , tblTrunk.Prefix ,vendorRate.Code) AS Prefix,
    vendorRate.Description AS COUNTRY,
    IFNULL(tblVendorPreference.Preference,5) as Preference,
    vendorRate.Interval1 as `Interval 1`,
    vendorRate.IntervalN as `Interval N`,
    vendorRate.Rate AS `Price 1`,
    vendorRate.Rate AS `Price N`,
    10 AS `1xx Timeout`,
    60 AS `2xx Timeout`,
    0 AS Huntstop,
    CASE
        WHEN (tblVendorBlocking.VendorBlockingId IS NOT NULL AND
        	FIND_IN_SET(vendorRate.TrunkId,tblVendorBlocking.TrunkId) != 0
			OR
            (blockCountry.VendorBlockingId IS NOT NULL AND
            FIND_IN_SET(vendorRate.TrunkId,blockCountry.TrunkId) != 0
            )
            ) THEN 1
        ELSE 0
    END  AS Forbidden,
    'NOW' AS `Activation Date`,
    '' AS `Expiration Date`,
    tblAccount.AccountID,
    tblTrunk.TrunkID
FROM tmp_VendorCurrentRates_ AS vendorRate
INNER JOIN tblAccount
    ON vendorRate.AccountId = tblAccount.AccountID
LEFT OUTER JOIN tblVendorBlocking
    ON vendorRate.RateID = tblVendorBlocking.RateId
    AND tblAccount.AccountID = tblVendorBlocking.AccountId
    AND vendorRate.TrunkID = tblVendorBlocking.TrunkID
LEFT OUTER JOIN tblVendorBlocking AS blockCountry
    ON vendorRate.CountryID = blockCountry.CountryId
    AND tblAccount.AccountID = blockCountry.AccountId
    AND vendorRate.TrunkID = blockCountry.TrunkID
LEFT JOIN tblVendorPreference 
  ON tblVendorPreference.AccountId = vendorRate.AccountId
  AND tblVendorPreference.TrunkID = vendorRate.TrunkID
  AND tblVendorPreference.RateId = vendorRate.RateID
INNER JOIN tblTrunk
    ON tblTrunk.TrunkID = vendorRate.TrunkID
WHERE (vendorRate.Rate > 0);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `vwVendorVersion3VosSheet` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `vwVendorVersion3VosSheet`(IN `p_AccountID` INT, IN `p_Trunks` LONGTEXT, IN `p_Effective` VARCHAR(50))
BEGIN



	DROP TEMPORARY TABLE IF EXISTS tmp_VendorVersion3VosSheet_;
   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorVersion3VosSheet_(
			RateID int,
			`Rate Prefix` varchar(50),
			`Area Prefix` varchar(50),
			`Rate Type` varchar(50),
			`Area Name` varchar(200),
			`Billing Rate` float,
			`Billing Cycle` int,
			`Minute Cost` float,
			`Lock Type` varchar(50),
			`Section Rate` varchar(50),
			`Billing Rate for Calling Card Prompt` float,
			`Billing Cycle for Calling Card Prompt` INT,
			AccountID int,
			TrunkID int,
			EffectiveDate date
	);
	 Call vwVendorCurrentRates(p_AccountID,p_Trunks,p_Effective);	
	 
	 
INSERT INTO tmp_VendorVersion3VosSheet_	 
SELECT


    NULL AS RateID,
    IFNULL(tblTrunk.RatePrefix, '') AS `Rate Prefix`,
    Concat('' , IFNULL(tblTrunk.AreaPrefix, '') , vendorRate.Code) AS `Area Prefix`,
    'International' AS `Rate Type`,
    vendorRate.Description AS `Area Name`,
    vendorRate.Rate / 60 AS `Billing Rate`,
    CASE
        WHEN vendorRate.Description LIKE '%mexico%' THEN 
		  	60
        ELSE 
		  1
    END AS `Billing Cycle`,
    CAST(vendorRate.Rate AS DECIMAL(18, 5)) AS `Minute Cost`,
    CASE
        WHEN (tblVendorBlocking.VendorBlockingId IS NOT NULL AND
        FIND_IN_SET(vendorRate.TrunkId,tblVendorBlocking.TrunkId) != 0
             OR
            (blockCountry.VendorBlockingId IS NOT NULL AND
             FIND_IN_SET(vendorRate.TrunkId,blockCountry.TrunkId) != 0
            )) THEN 'No Lock'
        ELSE 'No Lock'
    END
    AS `Lock Type`,
        CASE WHEN vendorRate.Interval1 != vendorRate.IntervalN 
                                      THEN 
                    Concat('0,', vendorRate.Rate, ',',vendorRate.Interval1)
                                      ELSE ''
                                 END as `Section Rate`,
    0 AS `Billing Rate for Calling Card Prompt`,
    0 AS `Billing Cycle for Calling Card Prompt`,
    tblAccount.AccountID,
    vendorRate.TrunkId,
    vendorRate.EffectiveDate
FROM tmp_VendorCurrentRates_ AS vendorRate
INNER JOIN tblAccount 
    ON vendorRate.AccountId = tblAccount.AccountID
LEFT OUTER JOIN tblVendorBlocking
    ON vendorRate.TrunkId = tblVendorBlocking.TrunkID
    AND vendorRate.RateID = tblVendorBlocking.RateId
    AND tblAccount.AccountID = tblVendorBlocking.AccountId
LEFT OUTER JOIN tblVendorBlocking AS blockCountry
    ON vendorRate.TrunkId = blockCountry.TrunkID
    AND vendorRate.CountryID = blockCountry.CountryId
    AND tblAccount.AccountID = blockCountry.AccountId
INNER JOIN tblTrunk
    ON tblTrunk.TrunkID = vendorRate.TrunkId
WHERE (vendorRate.Rate > 0);



END ;;
DELIMITER ;


