text: USE `Ratemanagement3`;
  
  INSERT INTO `tblJobType` (`JobTypeID`, `Code`, `Title`, `Description`, `CreatedDate`, `CreatedBy`, `ModifiedDate`, `ModifiedBy`) VALUES (24, 'ICU', 'IP Upload', NULL, '2017-06-07 13:05:46', 'System', NULL, NULL);
  INSERT INTO `tblRateSheetFormate` (`RateSheetFormateID`, `Title`, `Description`, `Customer`, `Vendor`, `Status`, `created_at`, `CreatedBy`, `updated_at`, `UpdatedBy`) VALUES (5, 'Vos 2.0', NULL, 1, 1, 1, '2017-06-13 00:00:00', NULL, NULL, NULL);
  INSERT INTO `tblCompanyConfiguration` (`CompanyID`, `Key`, `Value`) VALUES (1, 'HIDE_AVGRATEMINUTE', '0');
  
  CREATE TABLE IF NOT EXISTS `tblTempAccountIP` (
    `tblTempAccountIPID` bigint(20) NOT NULL auto_increment,
    `CompanyID` int(11) NULL,
    `AccountName` varchar(100) NULL,
    `IP` longtext NULL,
    `Type` varchar(50) NULL,
    `ProcessID` varchar(50) NULL,
    `ServiceID` int(11) NULL DEFAULT '0',
    `created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` varchar(50) NULL,
    PRIMARY KEY (`tblTempAccountIPID`),
    KEY `IX_ProcessID`(`ProcessID`)
  ) ENGINE=InnoDB;
  
  DROP PROCEDURE IF EXISTS `prc_WSProcessImportAccountIP`;
  DELIMITER |
  CREATE PROCEDURE `prc_WSProcessImportAccountIP`(
  	IN `p_processId` VARCHAR(200),
  	IN `p_companyId` 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     
      );
      
      -- delete duplicate ip
      
      DELETE n1
  		FROM tblTempAccountIP n1
  		INNER JOIN (
  			SELECT MAX(tblTempAccountIPID) as tblTempAccountIPID FROM tblTempAccountIP WHERE ProcessID = p_processId
  			GROUP BY IP
  			HAVING COUNT(*)>1
  		) n2 ON n1.tblTempAccountIPID = n2.tblTempAccountIPID
  		WHERE n1.ProcessID = p_processId;
  		
  		-- delete already exits ip
  		
  		DELETE tblTempAccountIP
  				FROM tblTempAccountIP
  				INNER JOIN(
  					SELECT DISTINCT ta.IP FROM tblTempAccountIP ta LEFT JOIN tblAccountAuthenticate aa 
  						ON (SELECT fnFIND_IN_SET(CONCAT(IFNULL(aa.CustomerAuthValue,''),',',IFNULL(aa.VendorAuthValue,'')),ta.IP)) > 0
  					WHERE ta.ProcessID = p_processId AND aa.CompanyID = p_companyId
  				) aold on aold.IP = tblTempAccountIP.IP;
   		
  		
   		DROP TEMPORARY TABLE IF EXISTS tmp_accountipimport;
  		CREATE TEMPORARY TABLE tmp_accountipimport (				  
  						  `CompanyID` INT,				  
  						  `AccountID` INT,
  						  `AccountName` VARCHAR(100),
  						  `IP` LONGTEXT,
  						  `Type` VARCHAR(50),
  						  `ProcessID` VARCHAR(50),
  						  `ServiceID` INT,
  						  `created_at` DATETIME,
  						  `created_by` VARCHAR(50)
  		) ENGINE=InnoDB;  
  
  		INSERT INTO tmp_accountipimport(`CompanyID`,`AccountName`,`IP`,`Type`,`ProcessID`,`ServiceID`,`created_at`,`created_by`)
  		select CompanyID,AccountName,IP,Type,ProcessID,ServiceID,created_at,created_by FROM tblTempAccountIP WHERE ProcessID = p_processId;
  		
  		UPDATE tmp_accountipimport ta LEFT JOIN tblAccount a ON ta.AccountName=a.AccountName
  				SET ta.AccountID = a.AccountID
  		WHERE a.AccountID IS NOT NULL AND a.AccountType=1 AND a.CompanyId=p_companyId;
  		
  		DROP TEMPORARY TABLE IF EXISTS tmp_accountcustomerip;
  			CREATE TEMPORARY TABLE tmp_accountcustomerip (				  
  							  `CompanyID` INT,				  
  							  `AccountID` INT,				  
  							  `CustomerAuthRule` VARCHAR(50),
  							  `CustomerAuthValue` VARCHAR(8000),
  							  `ServiceID` INT
  			) ENGINE=InnoDB;  
  		
  		DROP TEMPORARY TABLE IF EXISTS tmp_accountvendorip;
  			CREATE TEMPORARY TABLE tmp_accountvendorip (				  
  							  `CompanyID` INT,		
  							  `AccountID` INT,				  		
  							  `VendorAuthRule` VARCHAR(50),
  							  `VendorAuthValue` VARCHAR(500),
  							  `ServiceID` INT
  			) ENGINE=InnoDB;  
  		INSERT INTO tmp_accountcustomerip(CompanyID,AccountID,CustomerAuthRule,CustomerAuthValue,ServiceID)
  		select CompanyID,AccountID,'IP' as CustomerAuthRule, GROUP_CONCAT(IP) as CustomerAuthValue,ServiceID from tmp_accountipimport where Type='Customer' GROUP BY AccountID,ServiceID;
  		
  		INSERT INTO tmp_accountvendorip(CompanyID,AccountID,VendorAuthRule,VendorAuthValue,ServiceID)
  		select CompanyID,AccountID,'IP' as VendorAuthRule, GROUP_CONCAT(IP) as VendorAuthValue,ServiceID from tmp_accountipimport where Type='Vendor' GROUP BY AccountID,ServiceID;
  		
  		-- insert authentication
  		
  		INSERT INTO tblAccountAuthenticate(CompanyID,AccountID,CustomerAuthRule,CustomerAuthValue,ServiceID)				
  			SELECT ac.CompanyID,ac.AccountID,ac.CustomerAuthRule,ac.CustomerAuthValue,ac.ServiceID
  				FROM tmp_accountcustomerip ac LEFT JOIN tblAccountAuthenticate aa
  					ON ac.AccountID=aa.AccountID AND ac.ServiceID=aa.ServiceID
  			 WHERE aa.AccountID IS NULL;
  
  					 
  			INSERT INTO tblAccountAuthenticate(CompanyID,AccountID,VendorAuthRule,VendorAuthValue,ServiceID)
  			SELECT av.CompanyID,av.AccountID,av.VendorAuthRule,av.VendorAuthValue,av.ServiceID 
  				FROM tmp_accountvendorip av LEFT JOIN tblAccountAuthenticate aa
  					ON av.AccountID=aa.AccountID AND av.ServiceID=aa.ServiceID
  			WHERE aa.AccountID IS NULL;
  		
  		-- update authentication
  		
  		UPDATE tmp_accountcustomerip ac LEFT JOIN tblAccountAuthenticate aa ON ac.AccountID=aa.AccountID AND ac.ServiceID=aa.ServiceID
  				SET	aa.CustomerAuthRule='IP',aa.CustomerAuthValue =
  					CASE WHEN((aa.CustomerAuthValue IS NULL) OR (aa.CustomerAuthValue='') OR (aa.CustomerAuthRule!='IP'))
  								THEN
  									  ac.CustomerAuthValue
  								ELSE
  									  CONCAT(aa.CustomerAuthValue,',',ac.CustomerAuthValue) 
  								END
  			WHERE ac.AccountID IS NOT NULL AND aa.AccountID IS NOT NULL;					
  	
  							
  			UPDATE tmp_accountvendorip av LEFT JOIN tblAccountAuthenticate aa ON av.AccountID=aa.AccountID AND av.ServiceID=aa.ServiceID
  				SET aa.VendorAuthRule='IP',aa.VendorAuthValue =
  					CASE WHEN (aa.VendorAuthValue IS NULL) OR (aa.VendorAuthValue='') OR (aa.VendorAuthRule!='IP')
  								THEN
  									 av.VendorAuthValue
  								ELSE
  									CONCAT(aa.VendorAuthValue,',',av.VendorAuthValue) 
  								END
  			 WHERE av.AccountID IS NOT NULL AND aa.AccountID IS NOT NULL;	
  					
  			
  			SET v_AffectedRecords_ = v_AffectedRecords_ + FOUND_ROWS();	
  
  			INSERT INTO tmp_JobLog_ (Message)
  			SELECT CONCAT(v_AffectedRecords_, ' Records Uploaded \n\r ' );	
  			
  			DELETE  FROM tblTempAccountIP WHERE ProcessID = p_processId;
  		
  		SELECT * from tmp_JobLog_;
  	      
      SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  
  END|
  DELIMITER ;
  
  DROP PROCEDURE IF EXISTS `prc_CronJobAllPending`;
  DELIMITER |
  CREATE PROCEDURE `prc_CronJobAllPending`(
  	IN `p_CompanyID` INT
  )
  BEGIN
  	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  	
      SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'CDR'
  		AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  	ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'CDR'
  		AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  
     
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID,
  	   TBL1.JobLoggedUserID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'BI'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'BI'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'CD'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		AND j.Options like '%"Format":"Porta"%'	
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'CD'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  		AND j.Options like '%"Format":"Porta"%'	
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  
     
  	SELECT
  		tblCronJobCommand.Command,
  		tblCronJob.CronJobID
  	FROM tblCronJob
  	INNER JOIN tblCronJobCommand
  		ON tblCronJobCommand.CronJobCommandID = tblCronJob.CronJobCommandID
  	WHERE tblCronJob.CompanyID = p_CompanyID
  	AND tblCronJob.Status = 1
  	AND tblCronJob.Active = 0;
  
  
  	
  	
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'BIS'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'BIS'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'VD'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		AND j.Options like '%"Format":"Porta"%'	
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'VD'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  		AND j.Options like '%"Format":"Porta"%'	
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'RCC'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'RCC'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  	
  	 
  
  	
  
  
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'INU'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'INU'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  	
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'CD'
  			AND js.Code = 'p'
  			AND j.CompanyID = p_CompanyID
  			AND j.Options like '%"Format":"Rate Sheet"%'
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'CD'
  			AND js.Code = 'I'
  			AND j.CompanyID = p_CompanyID
  			AND j.Options like '%"Format":"Rate Sheet"%'
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'BIR'
  			AND js.Code = 'p'
  			AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'BIR'
  			AND js.Code = 'I'
  			AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'BLE'
  			AND js.Code = 'p'
  			AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'BLE'
  			AND js.Code = 'I'
  			AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'BAE'
  			AND js.Code = 'p'
  			AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'BAE'
  			AND js.Code = 'I'
  			AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'VU'
  			AND js.Code = 'p'
  			AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'VU'
  			AND js.Code = 'I'
  			AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  	 
      SELECT
      	  "CodeDeckUpload",
          TBL1.JobID,
          TBL1.Options,
          TBL1.AccountID
      FROM
      (
          SELECT
              j.Options,
              j.AccountID,
              j.JobID,
              j.JobLoggedUserID,
              @row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			   @prev_JobLoggedUserID  := j.JobLoggedUserID,
   			   @prev_created_at  := created_at
          FROM tblJob j
          INNER JOIN tblJobType jt
              ON j.JobTypeID = jt.JobTypeID
          INNER JOIN tblJobStatus js
              ON j.JobStatusID = js.JobStatusID
           ,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
          WHERE jt.Code = 'CDU'
              AND js.Code = 'p'
              AND j.CompanyID = p_CompanyID
           ORDER BY j.JobLoggedUserID,j.created_at ASC
      ) TBL1
      LEFT JOIN
      (
          SELECT
              JobLoggedUserID
          FROM tblJob j
          INNER JOIN tblJobType jt
              ON j.JobTypeID = jt.JobTypeID
          INNER JOIN tblJobStatus js
              ON j.JobStatusID = js.JobStatusID
          WHERE jt.Code = 'CDU'
              AND js.Code = 'I'
              AND j.CompanyID = p_CompanyID
      ) TBL2
          ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
      WHERE TBL1.rowno = 1
      AND TBL2.JobLoggedUserID IS NULL;
  
  	   
      SELECT
          TBL1.JobID,
          TBL1.Options,
          TBL1.AccountID
      FROM
      (
          SELECT
              j.Options,
              j.AccountID,
              j.JobID,
              j.JobLoggedUserID,
              @row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  				@prev_JobLoggedUserID  := j.JobLoggedUserID,
  				@prev_created_at  := created_at
          FROM tblJob j
          INNER JOIN tblJobType jt
              ON j.JobTypeID = jt.JobTypeID
          INNER JOIN tblJobStatus js
              ON j.JobStatusID = js.JobStatusID
           ,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
          WHERE jt.Code = 'IR'
              AND js.Code = 'p'
              AND j.CompanyID = p_CompanyID
           ORDER BY j.JobLoggedUserID,j.created_at ASC
      ) TBL1
      LEFT JOIN
      (
          SELECT
              JobLoggedUserID
          FROM tblJob j
          INNER JOIN tblJobType jt
              ON j.JobTypeID = jt.JobTypeID
          INNER JOIN tblJobStatus js
              ON j.JobStatusID = js.JobStatusID
          WHERE jt.Code = 'IR'
              AND js.Code = 'I'
              AND j.CompanyID = p_CompanyID
      ) TBL2
          ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
      WHERE TBL1.rowno = 1
      AND TBL2.JobLoggedUserID IS NULL;
  
  
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'CD'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		AND j.Options like '%"Format":"Sippy"%'	
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'CD'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  		AND j.Options like '%"Format":"Sippy"%'	
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'VD'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		AND j.Options like '%"Format":"Sippy"%'
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'VD'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  		AND j.Options like '%"Format":"Sippy"%'	
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'CD'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		AND (j.Options like '%"Format":"Vos 3.2"%' OR j.Options like '%"Format":"Vos 2.0"%')	
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'CD'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  		AND (j.Options like '%"Format":"Vos 3.2"%' OR j.Options like '%"Format":"Vos 2.0"%')		
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'VD'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		AND (j.Options like '%"Format":"Vos 3.2"%' OR j.Options like '%"Format":"Vos 2.0"%')		
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'VD'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  		AND (j.Options like '%"Format":"Vos 3.2"%' OR j.Options like '%"Format":"Vos 2.0"%')		
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  
  
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'GRT'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'GRT'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  	
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'RTU'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'RTU'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  	
  	
      SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'VDR'
  		AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  	ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'VDR'
  		AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  	
  	
  	
  		
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'MGA'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'MGA'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  	
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'DSU'
          AND js.Code = 'P'
  		AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'DSU'
          AND js.Code = 'I'
  		AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  	
  	-- Quickbook post
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'QIP'
  			AND js.Code = 'p'
  			AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'QIP'
  			AND js.Code = 'I'
  			AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  	
  	-- Account Import Ip
  	
  	SELECT
  		TBL1.JobID,
  		TBL1.Options,
  		TBL1.AccountID
  	FROM
  	(
  		SELECT
  			j.Options,
  			j.AccountID,
  			j.JobID,
  			j.JobLoggedUserID,
  			@row_num := IF(@prev_JobLoggedUserID=j.JobLoggedUserID and @prev_created_at <= j.created_at ,@row_num+1,1) AS rowno,
  			@prev_JobLoggedUserID  := j.JobLoggedUserID,
  			@prev_created_at  := created_at
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		,(SELECT @row_num := 1) x,(SELECT @prev_JobLoggedUserID := '') y,(SELECT @prev_created_at := '') z
  		WHERE jt.Code = 'ICU'
  			AND js.Code = 'p'
  			AND j.CompanyID = p_CompanyID
  		ORDER BY j.JobLoggedUserID,j.created_at ASC
  	) TBL1
  	LEFT JOIN
  	(
  		SELECT
  			JobLoggedUserID
  		FROM tblJob j
  		INNER JOIN tblJobType jt
  			ON j.JobTypeID = jt.JobTypeID
  		INNER JOIN tblJobStatus js
  			ON j.JobStatusID = js.JobStatusID
  		WHERE jt.Code = 'ICU'
  			AND js.Code = 'I'
  			AND j.CompanyID = p_CompanyID
  	) TBL2
  		ON TBL1.JobLoggedUserID = TBL2.JobLoggedUserID
  	WHERE TBL1.rowno = 1
  	AND TBL2.JobLoggedUserID IS NULL;
  	
  	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
  END|
  DELIMITER ;
  
  DROP PROCEDURE IF EXISTS `prc_GetAccounts`;
  DELIMITER |
  CREATE PROCEDURE `prc_GetAccounts`(
  	IN `p_CompanyID` int,
  	IN `p_userID` int ,
  	IN `p_IsVendor` int ,
  	IN `p_isCustomer` int ,
  	IN `p_activeStatus` int,
  	IN `p_VerificationStatus` int,
  	IN `p_AccountNo` VARCHAR(100),
  	IN `p_ContactName` VARCHAR(50),
  	IN `p_AccountName` VARCHAR(50),
  	IN `p_tags` VARCHAR(50),
  	IN `p_IPCLI` VARCHAR(50),
  	IN `p_low_balance` INT,
  	IN `p_PageNumber` INT,
  	IN `p_RowspPage` INT,
  	IN `p_lSortCol` VARCHAR(50),
  	IN `p_SortOrder` VARCHAR(5),
  	IN `p_isExport` INT 
  )
  BEGIN
  	DECLARE v_OffSet_ int;
  	DECLARE v_Round_ int;
  	
  	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
  	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
  	SELECT fnGetRoundingPoint(p_CompanyID) INTO v_Round_;
  
  	IF p_isExport = 0
  	THEN
  
  		SELECT
  			tblAccount.AccountID,
  			tblAccount.Number, 
  			tblAccount.AccountName,
  			CONCAT(tblAccount.FirstName,' ',tblAccount.LastName) as Ownername,
  			tblAccount.Phone, 
  			CONCAT((SELECT Symbol FROM tblCurrency WHERE tblCurrency.CurrencyId = tblAccount.CurrencyId) ,ROUND(COALESCE(abc.SOAOffset,0),v_Round_)) as OutStandingAmount,
  			CONCAT((SELECT Symbol FROM tblCurrency WHERE tblCurrency.CurrencyId = tblAccount.CurrencyId) ,ROUND(COALESCE(abc.UnbilledAmount,0),v_Round_) - ROUND(COALESCE(abc.VendorUnbilledAmount,0),v_Round_)) as UnbilledAmount,
  			CONCAT((SELECT Symbol FROM tblCurrency WHERE tblCurrency.CurrencyId = tblAccount.CurrencyId) ,ROUND(COALESCE(abc.PermanentCredit,0),v_Round_)) as PermanentCredit,
  			tblAccount.Email, 
  			tblAccount.IsCustomer, 
  			tblAccount.IsVendor,
  			tblAccount.VerificationStatus,
  			tblAccount.Address1,
  			tblAccount.Address2,
  			tblAccount.Address3,
  			tblAccount.City,
  			tblAccount.Country,
  			tblAccount.PostCode,
  			tblAccount.Picture,			
  			IF ( (CASE WHEN abc.BalanceThreshold LIKE '%p' THEN REPLACE(abc.BalanceThreshold, 'p', '')/ 100 * abc.PermanentCredit ELSE abc.BalanceThreshold END) < abc.BalanceAmount AND abc.BalanceThreshold <> 0 ,1,0) as BalanceWarning,
  			CONCAT((SELECT Symbol FROM tblCurrency WHERE tblCurrency.CurrencyId = tblAccount.CurrencyId) ,ROUND(COALESCE(abc.UnbilledAmount,0),v_Round_)) as CUA,
  			CONCAT((SELECT Symbol FROM tblCurrency WHERE tblCurrency.CurrencyId = tblAccount.CurrencyId) ,ROUND(COALESCE(abc.VendorUnbilledAmount,0),v_Round_)) as VUA,
  			CONCAT((SELECT Symbol FROM tblCurrency WHERE tblCurrency.CurrencyId = tblAccount.CurrencyId) ,ROUND(COALESCE(abc.BalanceAmount,0),v_Round_)) as AE,
  			CONCAT((SELECT Symbol FROM tblCurrency WHERE tblCurrency.CurrencyId = tblAccount.CurrencyId) ,IF(ROUND(COALESCE(abc.PermanentCredit,0),v_Round_) - ROUND(COALESCE(abc.BalanceAmount,0),v_Round_)<0,0,ROUND(COALESCE(abc.PermanentCredit,0),v_Round_) - ROUND(COALESCE(abc.BalanceAmount,0),v_Round_))) as ACL,
  			abc.BalanceThreshold,
  			tblAccount.Blocked
  		FROM tblAccount
  		LEFT JOIN tblAccountBalance abc
  			ON abc.AccountID = tblAccount.AccountID
  		LEFT JOIN tblUser
  			ON tblAccount.Owner = tblUser.UserID
  		LEFT JOIN tblContact
  			ON tblContact.Owner=tblAccount.AccountID
  		LEFT JOIN tblAccountAuthenticate
  			ON tblAccountAuthenticate.AccountID = tblAccount.AccountID
  		LEFT JOIN tblCLIRateTable
  			ON tblCLIRateTable.AccountID = tblAccount.AccountID
  		WHERE   tblAccount.CompanyID = p_CompanyID
  			AND tblAccount.AccountType = 1
  			AND tblAccount.Status = p_activeStatus
  			AND tblAccount.VerificationStatus = p_VerificationStatus
  			AND (p_userID = 0 OR tblAccount.Owner = p_userID)
  			AND ((p_IsVendor = 0 OR tblAccount.IsVendor = 1))
  			AND ((p_isCustomer = 0 OR tblAccount.IsCustomer = 1))
  			AND ((p_AccountNo = '' OR tblAccount.Number LIKE p_AccountNo))
  			AND ((p_AccountName = '' OR tblAccount.AccountName LIKE Concat('%',p_AccountName,'%')))
  			AND ((p_IPCLI = '' OR tblCLIRateTable.CLI LIKE CONCAT('%',p_IPCLI,'%') OR CONCAT(IFNULL(tblAccountAuthenticate.CustomerAuthValue,''),',',IFNULL(tblAccountAuthenticate.VendorAuthValue,'')) LIKE CONCAT('%',p_IPCLI,'%')))
  			AND ((p_tags = '' OR tblAccount.tags LIKE Concat(p_tags,'%')))
  			AND ((p_ContactName = '' OR (CONCAT(IFNULL(tblContact.FirstName,'') ,' ', IFNULL(tblContact.LastName,''))) LIKE CONCAT('%',p_ContactName,'%')))
  			AND (p_low_balance = 0 OR ( p_low_balance = 1 AND abc.BalanceThreshold <> 0 AND (CASE WHEN abc.BalanceThreshold LIKE '%p' THEN REPLACE(abc.BalanceThreshold, 'p', '')/ 100 * abc.PermanentCredit ELSE abc.BalanceThreshold END) < abc.BalanceAmount) )
  		GROUP BY tblAccount.AccountID
  		ORDER BY
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'AccountNameASC') THEN tblAccount.AccountName
  			END ASC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'AccountNameDESC') THEN tblAccount.AccountName
  			END DESC,                
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'NumberDESC') THEN tblAccount.Number
  			END DESC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'NumberASC') THEN tblAccount.Number
  			END ASC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'OwnernameDESC') THEN tblUser.FirstName
  			END DESC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'OwnernameASC') THEN tblUser.FirstName
  			END ASC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'PhoneDESC') THEN tblAccount.Phone
  			END DESC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'PhoneASC') THEN tblAccount.Phone
  			END ASC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'OutStandingAmountDESC') THEN abc.SOAOffset
  			END DESC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'OutStandingAmountASC') THEN abc.SOAOffset
  			END ASC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'PermanentCreditDESC') THEN abc.PermanentCredit
  			END DESC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'PermanentCreditASC') THEN abc.PermanentCredit
  			END ASC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'UnbilledAmountDESC') THEN (ROUND(COALESCE(abc.UnbilledAmount,0),v_Round_) - ROUND(COALESCE(abc.VendorUnbilledAmount,0),v_Round_))
  			END DESC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'UnbilledAmountASC') THEN (ROUND(COALESCE(abc.UnbilledAmount,0),v_Round_) - ROUND(COALESCE(abc.VendorUnbilledAmount,0),v_Round_))
  			END ASC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EmailDESC') THEN tblAccount.Email
  			END DESC,
  			CASE
  				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EmailASC') THEN tblAccount.Email
  			END ASC
  		LIMIT p_RowspPage OFFSET v_OffSet_;
  
  		SELECT
  			COUNT(DISTINCT tblAccount.AccountID) AS totalcount
  		FROM tblAccount
  		LEFT JOIN tblAccountBalance abc
  			ON abc.AccountID = tblAccount.AccountID
  		LEFT JOIN tblUser
  			ON tblAccount.Owner = tblUser.UserID
  		LEFT JOIN tblContact
  			ON tblContact.Owner=tblAccount.AccountID
  		LEFT JOIN tblAccountAuthenticate
  			ON tblAccountAuthenticate.AccountID = tblAccount.AccountID
  		LEFT JOIN tblCLIRateTable
  			ON tblCLIRateTable.AccountID = tblAccount.AccountID
  		WHERE   tblAccount.CompanyID = p_CompanyID
  			AND tblAccount.AccountType = 1
  			AND tblAccount.Status = p_activeStatus
  			AND tblAccount.VerificationStatus = p_VerificationStatus
  			AND (p_userID = 0 OR tblAccount.Owner = p_userID)
  			AND ((p_IsVendor = 0 OR tblAccount.IsVendor = 1))
  			AND ((p_isCustomer = 0 OR tblAccount.IsCustomer = 1))
  			AND ((p_AccountNo = '' OR tblAccount.Number LIKE p_AccountNo))
  			AND ((p_AccountName = '' OR tblAccount.AccountName LIKE Concat('%',p_AccountName,'%')))
  			AND ((p_IPCLI = '' OR tblCLIRateTable.CLI LIKE CONCAT('%',p_IPCLI,'%') OR CONCAT(IFNULL(tblAccountAuthenticate.CustomerAuthValue,''),',',IFNULL(tblAccountAuthenticate.VendorAuthValue,'')) LIKE CONCAT('%',p_IPCLI,'%')))
  			AND ((p_tags = '' OR tblAccount.tags LIKE Concat(p_tags,'%')))
  			AND ((p_ContactName = '' OR (CONCAT(IFNULL(tblContact.FirstName,'') ,' ', IFNULL(tblContact.LastName,''))) LIKE CONCAT('%',p_ContactName,'%')))
  			AND (p_low_balance = 0 OR ( p_low_balance = 1 AND abc.BalanceThreshold <> 0 AND (CASE WHEN abc.BalanceThreshold LIKE '%p' THEN REPLACE(abc.BalanceThreshold, 'p', '')/ 100 * abc.PermanentCredit ELSE abc.BalanceThreshold END) < abc.BalanceAmount) );
  
  	END IF;
  	IF p_isExport = 1
  	THEN
  		SELECT
  			tblAccount.Number as NO,
  			tblAccount.AccountName,
  			CONCAT(tblAccount.FirstName,' ',tblAccount.LastName) as Name,
  			tblAccount.Phone, 
  			CONCAT((SELECT Symbol FROM tblCurrency WHERE tblCurrency.CurrencyId = tblAccount.CurrencyId) ,ROUND(COALESCE(abc.SOAOffset,0),v_Round_)) as 'OutStanding',
  			tblAccount.Email,
  			CONCAT((SELECT Symbol FROM tblCurrency WHERE tblCurrency.CurrencyId = tblAccount.CurrencyId) ,ROUND(COALESCE(abc.UnbilledAmount,0),v_Round_)  - ROUND(COALESCE(abc.VendorUnbilledAmount,0),v_Round_)) as 'Unbilled Amount',
  			CONCAT((SELECT Symbol FROM tblCurrency WHERE tblCurrency.CurrencyId = tblAccount.CurrencyId) ,ROUND(COALESCE(abc.PermanentCredit,0),v_Round_)) as 'Credit Limit',
  			CONCAT(tblUser.FirstName,' ',tblUser.LastName) as 'Account Owner'
  		FROM tblAccount
  		LEFT JOIN tblAccountBalance abc
  			ON abc.AccountID = tblAccount.AccountID
  		LEFT JOIN tblUser
  			ON tblAccount.Owner = tblUser.UserID
  		LEFT JOIN tblContact
  			ON tblContact.Owner=tblAccount.AccountID
  		LEFT JOIN tblAccountAuthenticate
  			ON tblAccountAuthenticate.AccountID = tblAccount.AccountID
  		LEFT JOIN tblCLIRateTable
  			ON tblCLIRateTable.AccountID = tblAccount.AccountID
  		WHERE   tblAccount.CompanyID = p_CompanyID
  			AND tblAccount.AccountType = 1
  			AND tblAccount.Status = p_activeStatus
  			AND tblAccount.VerificationStatus = p_VerificationStatus
  			AND (p_userID = 0 OR tblAccount.Owner = p_userID)
  			AND ((p_IsVendor = 0 OR tblAccount.IsVendor = 1))
  			AND ((p_isCustomer = 0 OR tblAccount.IsCustomer = 1))
  			AND ((p_AccountNo = '' OR tblAccount.Number LIKE p_AccountNo))
  			AND ((p_AccountName = '' OR tblAccount.AccountName LIKE Concat('%',p_AccountName,'%')))
  			AND ((p_IPCLI = '' OR tblCLIRateTable.CLI LIKE CONCAT('%',p_IPCLI,'%') OR CONCAT(IFNULL(tblAccountAuthenticate.CustomerAuthValue,''),',',IFNULL(tblAccountAuthenticate.VendorAuthValue,'')) LIKE CONCAT('%',p_IPCLI,'%')))
  			AND ((p_tags = '' OR tblAccount.tags LIKE Concat(p_tags,'%')))
  			AND ((p_ContactName = '' OR (CONCAT(IFNULL(tblContact.FirstName,'') ,' ', IFNULL(tblContact.LastName,''))) LIKE CONCAT('%',p_ContactName,'%')))
  			AND (p_low_balance = 0 OR ( p_low_balance = 1 AND abc.BalanceThreshold <> 0 AND (CASE WHEN abc.BalanceThreshold LIKE '%p' THEN REPLACE(abc.BalanceThreshold, 'p', '')/ 100 * abc.PermanentCredit ELSE abc.BalanceThreshold END) < abc.BalanceAmount) )
  		GROUP BY tblAccount.AccountID;
  	END IF;
  	IF p_isExport = 2
  	THEN
  		SELECT
  			tblAccount.AccountID,
  			tblAccount.AccountName			
  		FROM tblAccount
  		LEFT JOIN tblAccountBalance abc
  			ON abc.AccountID = tblAccount.AccountID
  		LEFT JOIN tblUser
  			ON tblAccount.Owner = tblUser.UserID
  		LEFT JOIN tblContact
  			ON tblContact.Owner=tblAccount.AccountID
  		LEFT JOIN tblAccountAuthenticate
  			ON tblAccountAuthenticate.AccountID = tblAccount.AccountID
  		LEFT JOIN tblCLIRateTable
  			ON tblCLIRateTable.AccountID = tblAccount.AccountID
  		WHERE   tblAccount.CompanyID = p_CompanyID
  			AND tblAccount.AccountType = 1
  			AND tblAccount.Status = p_activeStatus
  			AND tblAccount.VerificationStatus = p_VerificationStatus
  			AND (p_userID = 0 OR tblAccount.Owner = p_userID)
  			AND ((p_IsVendor = 0 OR tblAccount.IsVendor = 1))
  			AND ((p_isCustomer = 0 OR tblAccount.IsCustomer = 1))
  			AND ((p_AccountNo = '' OR tblAccount.Number LIKE p_AccountNo))
  			AND ((p_AccountName = '' OR tblAccount.AccountName LIKE Concat('%',p_AccountName,'%')))
  			AND ((p_IPCLI = '' OR tblCLIRateTable.CLI LIKE CONCAT('%',p_IPCLI,'%') OR CONCAT(IFNULL(tblAccountAuthenticate.CustomerAuthValue,''),',',IFNULL(tblAccountAuthenticate.VendorAuthValue,'')) LIKE CONCAT('%',p_IPCLI,'%')))
  			AND ((p_tags = '' OR tblAccount.tags LIKE Concat(p_tags,'%')))
  			AND ((p_ContactName = '' OR (CONCAT(IFNULL(tblContact.FirstName,'') ,' ', IFNULL(tblContact.LastName,''))) LIKE CONCAT('%',p_ContactName,'%')))
  			AND (p_low_balance = 0 OR ( p_low_balance = 1 AND abc.BalanceThreshold <> 0 AND (CASE WHEN abc.BalanceThreshold LIKE '%p' THEN REPLACE(abc.BalanceThreshold, 'p', '')/ 100 * abc.PermanentCredit ELSE abc.BalanceThreshold END) < abc.BalanceAmount) )
  		GROUP BY tblAccount.AccountID;
  	END IF;
  	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  END|
  DELIMITER ;
  
  DROP PROCEDURE IF EXISTS `prc_WSGenerateVendorVersion3VosSheet`;
  DELIMITER |
  CREATE PROCEDURE `prc_WSGenerateVendorVersion3VosSheet`(
  	IN `p_VendorID` INT ,
  	IN `p_Trunks` varchar(200) ,
  	IN `p_Effective` VARCHAR(50),
  	IN `p_Format` VARCHAR(50)
  )
  BEGIN
           SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
           
          call vwVendorVersion3VosSheet(p_VendorID,p_Trunks,p_Effective);
          
          IF p_Effective = 'Now' OR p_Format = 'Vos 2.0'		
  		  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' AND p_Format = 'Vos 3.2'		
  		  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 ;
  
  
  DROP PROCEDURE IF EXISTS `prc_WSGenerateVersion3VosSheet`;
  DELIMITER |
  CREATE PROCEDURE `prc_WSGenerateVersion3VosSheet`(
  	IN `p_CustomerID` INT ,
  	IN `p_trunks` varchar(200) ,
  	IN `p_Effective` VARCHAR(50),
  	IN `p_Format` 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;
     
      /* if you chnage this table change in all sheet download and customer rate */
      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' OR p_Format = 'Vos 2.0'		
  		  THEN	
              
          SELECT distinct 
                  IFNULL(RatePrefix, '') as `Rate Prefix` ,
                  Concat(IFNULL(AreaPrefix,''), Code) as `Area Prefix` ,
                  'International' as `Rate Type` ,
                  Description  as `Area Name`,
                  Rate / 60  as `Billing Rate`,
                  IntervalN as `Billing Cycle`,
                  Rate as `Minute Cost` ,
                  'No Lock'  as `Lock Type`,
                  CASE WHEN Interval1 != IntervalN  
                 	 THEN Concat('0,', Rate, ',',Interval1)
                  ELSE 
  					 	 ''
                  END as `Section Rate`,
                  0 AS `Billing Rate for Calling Card Prompt`,
                  0  as `Billing Cycle for Calling Card Prompt`
          FROM   tmp_customerrateall_
          ORDER BY `Rate Prefix`; 
         
  		 END IF; 
  		 
  	 	IF p_Effective = 'Future' AND p_Format = 'Vos 3.2'		
  		  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 ;
  
  DROP PROCEDURE IF EXISTS `prc_GetTicketDashboardTimeline`;
  DELIMITER |
  CREATE PROCEDURE `prc_GetTicketDashboardTimeline`(
  	IN `p_CompanyID` INT,
  	IN `P_Group` INT,
  	IN `P_Agent` INT,
  	IN `p_Time` DATETIME,
  	IN `p_TicketID` INT,
  	IN `p_PageNumber` INT,
  	IN `p_RowsPage` INT
  )
  BEGIN
  	DECLARE v_MAXDATE DATETIME;
  
  	SELECT MAX(created_at) as created_at INTO v_MAXDATE FROM tblTicketDashboardTimeline;
  	IF(p_PageNumber=0)
  	THEN
  		DELETE FROM tblTicketDashboardTimeline WHERE created_at_table < DATE_SUB(p_Time, INTERVAL 1 MONTH);
  		INSERT INTO tblTicketDashboardTimeline
  		SELECT
  			NULL,
  			tc.CompanyID,
  			1 as TimeLineType,
  			CONCAT(IFNULL(u.FirstName,''),' ',IFNULL(u.LastName,'')) as UserName,
  			u.UserID,
  			CASE WHEN tc.AccountID != 0 THEN tc.AccountID
  					WHEN tc.ContactID != 0 THEN tc.ContactID
  					ELSE 0 END as CustomerID,
  			CASE WHEN tc.AccountID != 0 THEN 1
  					WHEN tc.ContactID != 0 THEN 2
  					ELSE 0 END as CustomerType,
  			acel.EmailCall,
  			tc.TicketID,
  		/*	CASE WHEN tc.AccountEmailLogID != 0 AND acel.EmailParent=0 THEN 1 ELSE 0 END as TicketSubmit,*/
  		   0 as TicketSubmit,
  			tc.Subject,
  			acel.AccountEmailLogID as ID,
  			tc.Agent,
  			tc.`Group`,
  			0 as TicketFieldID,
  			0 as TicketFieldValueFromID,
  			0 as TicketFieldValueToID,
  			acel.created_at,
  			p_Time
  		FROM AccountEmailLog acel
  		INNER JOIN tblTickets tc ON tc.TicketID = acel.TicketID
  		LEFT JOIN tblAccount ac ON ac.AccountID = acel.AccountID
  		LEFT JOIN tblUser u ON u.UserID = acel.UserID
  		WHERE acel.CompanyID = p_CompanyID
  		AND (v_MAXDATE IS NULL OR acel.created_at > v_MAXDATE)
  		/*AND acel.EmailCall = 0*/
  		AND (p_TicketID = 0 OR (tc.TicketID = p_TicketID))
  		AND u.UserID IS NOT NULL;
  
  		INSERT INTO tblTicketDashboardTimeline
  		SELECT
  		NULL,
  		tc.CompanyID,
  		2 as TimeLineType,
  		CONCAT(IFNULL(u.FirstName,''),' ',IFNULL(u.LastName,''))  as UserName,
  		TN.UserID as UserID,
  		0 as CustomerID,
  		0 as CustomerType,
  		0 as EmailCall,
  		tc.TicketID,
  		0 as TicketSubmit,
  		tc.Subject,
  		TN.NoteID as ID,
  		tc.Agent,
  		tc.`Group`,
  		0 as TicketFieldID,
  		0 as TicketFieldValueFromID,
  		0 as TicketFieldValueToID,
  		TN.created_at,
  		p_Time
  		FROM `tblNote` TN
  		INNER JOIN tblTickets tc ON tc.TicketID = TN.TicketID
  		INNER JOIN tblUser u ON u.UserID = TN.UserID
  		WHERE TN.CompanyID = p_CompanyID
  		AND (v_MAXDATE IS NULL OR TN.created_at > v_MAXDATE)
  		AND (p_TicketID = 0 OR (tc.TicketID = p_TicketID));
  
  		INSERT INTO tblTicketDashboardTimeline
  		SELECT
  			NULL,
  			tc.CompanyID,
  			3 as TimeLineType,
  			/*CASE WHEN tl.AccountID = 0 THEN CONCAT(IFNULL(u.FirstName,''),' ',IFNULL(u.LastName,'')) ELSE CONCAT(IFNULL(a.FirstName,''),' ',IFNULL(a.LastName,''))  END as UserName,*/
  			CASE 
  				WHEN tl.AccountID != 0
  				THEN
  					CONCAT(IFNULL(a.FirstName,''),' ',IFNULL(a.LastName,''))					
  				WHEN tl.UserID != 0
  				THEN
  					CONCAT(IFNULL(u.FirstName,''),' ',IFNULL(u.LastName,''))
  				ELSE
  					CONCAT(IFNULL(c.FirstName,''),' ',IFNULL(c.LastName,''))				
  			END
  				as UserName,
  			tl.UserID as UserID,
  			CASE WHEN tl.TicketFieldID = 0 THEN
  				CASE WHEN tc.AccountID != 0 THEN tc.AccountID
  						WHEN tc.ContactID != 0 THEN tc.ContactID
  						ELSE 0 END
  			ELSE
  				tl.AccountID
  			END as CustomerID,
  			CASE WHEN tl.TicketFieldID = 0 THEN
  				CASE WHEN tc.AccountID != 0 THEN 1
  						WHEN tc.ContactID != 0 THEN 2
  						ELSE 0 END
  				ELSE 0 END as CustomerType,
  			0 as EmailCall,
  			tc.TicketID,
  			IF(tl.NewTicket = 0,0,1) as TicketSubmit,
  			tc.Subject,
  			tl.TicketLogID as ID,
  			tc.Agent,
  			tc.`Group`,
  			tl.TicketFieldID,
  			tl.TicketFieldValueFromID,
  			tl.TicketFieldValueToID,
  			tl.created_at,
  			p_Time
  		FROM tblTicketLog tl
  		INNER JOIN tblTickets tc ON tc.TicketID = tl.TicketID
  		LEFT JOIN tblUser u ON u.UserID = tl.UserID
  		LEFT JOIN tblAccount a ON a.AccountID = tl.AccountID
  		LEFT JOIN tblContact c ON c.ContactID = tc.ContactID
  		WHERE tl.CompanyID = p_CompanyID
  		AND (v_MAXDATE IS NULL OR tl.created_at > v_MAXDATE)
  		AND (p_TicketID = 0 OR (tc.TicketID = p_TicketID));
  	END IF;
  	SELECT * FROM tblTicketDashboardTimeline tl
  	WHERE (P_Agent = 0 OR tl.AgentID = p_Agent)
  	AND(P_Group = 0 OR tl.`GroupID` = p_Group)
  	AND (p_TicketID = 0 OR (tl.TicketID = p_TicketID))
  	AND tl.CompanyID = p_CompanyID
  	ORDER BY created_at DESC
  	LIMIT p_RowsPage OFFSET p_PageNumber;	
  END|
  DELIMITER ;