IF EXISTS (SELECT 1 FROM SYS.PROCEDURES WHERE NAME = 'P_PDV_VD_CONVENIO_FECHA_CONV')
    BEGIN 
    DROP PROCEDURE P_PDV_VD_CONVENIO_FECHA_CONV
    END 
GO
CREATE PROCEDURE P_PDV_VD_CONVENIO_FECHA_CONV (@CD_EMP INT ,@CD_FILIAL INT ,@DT_VD DATETIME )
AS

SET NOCOUNT ON;
DECLARE @FLAG_FECHA_DIARIO int = 1;

CREATE TABLE #V_PDV_VD_CONVENIO (CD_EMP            INT
									, CD_FILIAL         INT
									, CD_VD				INT
									, CD_CONV           INT
									, DS_CONV           VARCHAR(500)
									, VLR_CONV          MONEY
									, VLR_CONV_PBM      MONEY
									, DT_VD             DATETIME
									, DEV               TINYINT
									, TIPO_CONV         TINYINT
									, FLAG_FECHA_DIARIO TINYINT
									, ESP_CLI			int
									);

CREATE TABLE #V_PDV_VD_CONVENIO_AUX (CD_EMP            INT
									, CD_FILIAL         INT
									, CD_VD				INT
									, CD_CONV           INT
									, DS_CONV           VARCHAR(500)
									, VLR_CONV          MONEY
									, VLR_CONV_PBM      MONEY
									, DT_VD             DATETIME
									, DEV               TINYINT
									, TIPO_CONV         TINYINT
									, FLAG_FECHA_DIARIO TINYINT
									, CD_CLI            INT
									 , ESP_CLI			int
									);
   
  
	 declare @comando as varchar(max)

	set @comando ='
	INSERT INTO #V_PDV_VD_CONVENIO
	SELECT DISTINCT
		   dbo.PDV_VD.CD_EMP
		 , dbo.PDV_VD.CD_FILIAL
		 , dbo.PDV_VD.CD_VD
		 , ISNULL(dbo.RC_CLI_CONV.CD_CONV, 0) AS CD_CONV
		 , ISNULL(dbo.RC_CLI_CONV.DS_CONV, ''SEM CLASSIFICACAO'') AS DS_CONV
		 , dbo.PDV_VD.VLR_CONV
		 , ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM
		 , dbo.PDV_VD.DT_VD
		 , 0 AS DEV
		 , 0 AS TIPO_CONV
		 , ISNULL(dbo.RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
		 , RC_CLI.ESP_CLI
	FROM
		 dbo.PDV_VD
		 INNER JOIN PDV_VD_RC_VD_CONV ON PDV_VD.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP
										 AND PDV_VD.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL
										 AND PDV_VD.CD_VD = PDV_VD_RC_VD_CONV.CD_VD
		 INNER JOIN dbo.RC_VD_CONV ON dbo.PDV_VD_RC_VD_CONV.CD_EMP = dbo.RC_VD_CONV.CD_EMP
									  AND dbo.PDV_VD_RC_VD_CONV.CD_CTR = dbo.RC_VD_CONV.CD_CTR
		 INNER JOIN dbo.RC_CLI ON dbo.RC_CLI.CD_EMP = dbo.RC_VD_CONV.CD_EMP
								  AND dbo.RC_CLI.CD_CLI = dbo.RC_VD_CONV.CD_CLI
		 INNER JOIN dbo.RC_CLI_CONV ON dbo.RC_VD_CONV.CD_EMP = dbo.RC_CLI_CONV.CD_EMP
									   AND dbo.RC_VD_CONV.CD_CONV = dbo.RC_CLI_CONV.CD_CONV
	WHERE PDV_VD.CD_EMP = ' + convert(varchar,@CD_EMP) + '
		  AND PDV_VD.CD_FILIAL = ' + convert(varchar,@CD_FILIAL) + '
		  AND PDV_VD.DT_VD = '''+ convert(varchar,@DT_VD,112) +'''
		  AND dbo.PDV_VD.ST_VD = 0
		  AND dbo.PDV_VD.VLR_CONV > 0
		  AND RC_CLI_CONV.FLAG_FECHA_DIARIO = 1;'
	
	exec (@comando);
-----------------------------------------------------------------------------------------------------------------------------								  

	 delete from #V_PDV_VD_CONVENIO where ESP_CLI=3;

	 delete from a from
		#V_PDV_VD_CONVENIO  a
		join dbo.V_PDV_VD_DEV_CONVENIOS b on 
						  a.CD_EMP		= b.CD_EMP
					  AND a.CD_FILIAL	= b.CD_FILIAL
					  AND a.CD_VD		= b.CD_VD
					  AND b.STS_NF = 0 ;
	 delete from a from
		#V_PDV_VD_CONVENIO  a
		join dbo.PDV_VD_IT_VD_PBM b on 
						  a.CD_EMP		= b.CD_EMP
					  AND a.CD_FILIAL	= b.CD_FILIAL
					  AND a.CD_VD		= b.CD_VD ;
-----------------------------------------------------------------------------------------------------------------------------				 

 	 
	--Convenio que tem registro PBM    
	set @comando ='INSERT INTO #V_PDV_VD_CONVENIO                    
	SELECT DISTINCT
		   dbo.PDV_VD.CD_EMP
		 , dbo.PDV_VD.CD_FILIAL
		 , dbo.PDV_VD.CD_VD
		 , ISNULL(dbo.RC_CLI_CONV.CD_CONV, 0) AS CD_CONV
		 , ISNULL(dbo.RC_CLI_CONV.DS_CONV, ''SEM CLASSIFICACAO'') AS DS_CONV
		 , dbo.PDV_VD.VLR_CONV
		 , CASE
			   WHEN PDV_VD.CD_CLI_PBM > 0 THEN 0
			   ELSE ISNULL(PDV_VD.VLR_CONV_PBM, 0)
		   END AS VLR_CONV_PBM
		 , dbo.PDV_VD.DT_VD
		 , 0 AS DEV
		 , 0 AS TIPO_CONV
		 , ISNULL(dbo.RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
		 , RC_CLI.ESP_CLI 
	FROM
		dbo.PDV_VD WITH (nolock)
		INNER JOIN PDV_VD_RC_VD_CONV WITH (nolock) ON PDV_VD.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP
													  AND PDV_VD.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL
													  AND PDV_VD.CD_VD = PDV_VD_RC_VD_CONV.CD_VD
		INNER JOIN dbo.RC_VD_CONV WITH (nolock) ON dbo.PDV_VD_RC_VD_CONV.CD_EMP = dbo.RC_VD_CONV.CD_EMP
												   AND dbo.PDV_VD_RC_VD_CONV.CD_CTR = dbo.RC_VD_CONV.CD_CTR
		INNER JOIN dbo.RC_CLI WITH (nolock) ON dbo.RC_CLI.CD_EMP = dbo.RC_VD_CONV.CD_EMP
											   AND dbo.RC_CLI.CD_CLI = dbo.RC_VD_CONV.CD_CLI
		INNER JOIN dbo.RC_CLI_CONV WITH (nolock) ON dbo.RC_VD_CONV.CD_EMP = dbo.RC_CLI_CONV.CD_EMP
													AND dbo.RC_VD_CONV.CD_CONV = dbo.RC_CLI_CONV.CD_CONV
	WHERE  PDV_VD.CD_EMP =  ' + convert(varchar,@CD_EMP) + ' 
		   AND PDV_VD.CD_FILIAL = ' + convert(varchar, @CD_FILIAL) + ' 
		   AND PDV_VD.DT_VD = '''+ convert(varchar,@DT_VD,112) +''' 
		   AND dbo.PDV_VD.ST_VD = 0
		   AND dbo.PDV_VD.VLR_CONV > 0
		   AND RC_CLI_CONV.FLAG_FECHA_DIARIO = 1 
		   AND EXISTS 	(
								SELECT
									   CD_EMP
								FROM
									PDV_VD_IT_VD_PBM WITH (nolock)
								WHERE  CD_EMP = PDV_VD.CD_EMP
									   AND CD_FILIAL = PDV_VD.CD_FILIAL
									   AND CD_VD = PDV_VD.CD_VD
							);'  
	exec (@comando);
-----------------------------------------------------------------------------------------------------------------

	 delete from #V_PDV_VD_CONVENIO where NOT ESP_CLI IN(2,3,6);  

	 delete from a from
		#V_PDV_VD_CONVENIO  a
		join dbo.V_PDV_VD_DEV_CONVENIOS b on 
						  a.CD_EMP		= b.CD_EMP
					  AND a.CD_FILIAL	= b.CD_FILIAL
					  AND a.CD_VD		= b.CD_VD
					  AND b.STS_NF = 0 ; 

 -----------------------------------------------------------------------------------------------------------------
	
	INSERT INTO #V_PDV_VD_CONVENIO_AUX
	SELECT DISTINCT
		   PDV_VD.CD_EMP
		 , PDV_VD.CD_FILIAL
		 , PDV_VD.CD_VD
		 , NULL AS CD_CONV
		 , NULL AS DS_CONV
		 , PDV_VD.VLR_CONV
		 , ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM
		 , PDV_VD.DT_VD
		 , 0 AS DEV
		 , 1 AS TIPO_CONV
		 , 0 AS FLAG_FECHA_DIARIO
		 , RC_CLI.CD_CLI
		 , RC_CLI.ESP_CLI
	FROM
		dbo.PDV_VD WITH (nolock)
		INNER JOIN PDV_VD_IT_VD_PBM WITH (nolock) ON PDV_VD_IT_VD_PBM.CD_EMP = PDV_VD.CD_EMP
													 AND PDV_VD_IT_VD_PBM.CD_FILIAL = PDV_VD.CD_FILIAL
													 AND PDV_VD_IT_VD_PBM.CD_VD = PDV_VD.CD_VD
		INNER JOIN dbo.RC_CLI WITH (nolock) ON PDV_VD_IT_VD_PBM.CD_EMP = RC_CLI.CD_EMP
											   AND PDV_VD_IT_VD_PBM.CD_CLI_PBM = RC_CLI.CD_CLI
	WHERE  PDV_VD.CD_EMP = @CD_EMP
		   AND PDV_VD.CD_FILIAL = @CD_FILIAL
		   AND PDV_VD.DT_VD = @DT_VD
		   AND PDV_VD.ST_VD = 0
		   AND PDV_VD_IT_VD_PBM.STS_VD_PBM = 0
----------------------------------------------------------------------------------------------------------------------------------------------------
	 
	 delete from #V_PDV_VD_CONVENIO_AUX where ESP_CLI<>3;  


	 delete from a from
		#V_PDV_VD_CONVENIO_AUX  a
		join dbo.V_PDV_VD_DEV_CONVENIOS b on 
						  a.CD_EMP		= b.CD_EMP
					  AND a.CD_FILIAL	= b.CD_FILIAL
					  AND a.CD_VD		= b.CD_VD
					  AND b.STS_NF = 0 ;
	 delete from a from
		#V_PDV_VD_CONVENIO_AUX  a
		join dbo.PDV_VD_RC_VD_CONV b on 
						  a.CD_EMP		= b.CD_EMP
					  AND a.CD_FILIAL	= b.CD_FILIAL
					  AND a.CD_VD		= b.CD_VD ;
----------------------------------------------------------------------------------------------------------------------------------------------------
	UPDATE AUX
	  SET
		  CD_CONV = RC_CLI_CONV.CD_CONV
		, DS_CONV = RC_CLI_CONV.DS_CONV
		, FLAG_FECHA_DIARIO = RC_CLI_CONV.FLAG_FECHA_DIARIO
	FROM   #V_PDV_VD_CONVENIO_AUX AUX
		   INNER JOIN dbo.RC_CLI_CONV_VINC_RC_CLI ON AUX.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP
													 AND AUX.CD_CLI = RC_CLI_CONV_VINC_RC_CLI.CD_CLI
		   INNER JOIN dbo.RC_CLI_CONV ON RC_CLI_CONV_VINC_RC_CLI.CD_EMP = RC_CLI_CONV.CD_EMP
										 AND RC_CLI_CONV_VINC_RC_CLI.CD_CONV = RC_CLI_CONV.CD_CONV;


----------------------------------------------------------------------------------------------------------------------------------------------------
	INSERT INTO #V_PDV_VD_CONVENIO
	SELECT
		   CD_EMP
		 , CD_FILIAL
		 , 0 CD_VD
		 , CD_CONV
		 , DS_CONV
		 , VLR_CONV
		 , VLR_CONV_PBM
		 , DT_VD
		 , DEV
		 , TIPO_CONV
		 , FLAG_FECHA_DIARIO
		 , 0 ESP_CLI
	FROM
		#V_PDV_VD_CONVENIO_AUX
	WHERE  FLAG_FECHA_DIARIO = 1; 
 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



	SELECT
		   V_PDV_VD_CONVENIO.CD_CONV
		 , V_PDV_VD_CONVENIO.DS_CONV
		 , SUM(VLR_CONV) AS VLR_CONV
	FROM
		#V_PDV_VD_CONVENIO AS V_PDV_VD_CONVENIO
		LEFT OUTER JOIN RC_VD_CONV_FECHA_LOTE ON V_PDV_VD_CONVENIO.CD_EMP = RC_VD_CONV_FECHA_LOTE.CD_EMP
												 AND V_PDV_VD_CONVENIO.CD_FILIAL = RC_VD_CONV_FECHA_LOTE.CD_FILIAL
												 AND V_PDV_VD_CONVENIO.CD_CONV = RC_VD_CONV_FECHA_LOTE.CD_CONV
												 AND V_PDV_VD_CONVENIO.DT_VD = RC_VD_CONV_FECHA_LOTE.DT_MOV
	WHERE   V_PDV_VD_CONVENIO.CD_EMP = @CD_EMP
			AND V_PDV_VD_CONVENIO.CD_FILIAL = @CD_FILIAL
			AND V_PDV_VD_CONVENIO.DT_VD = @DT_VD
			AND (V_PDV_VD_CONVENIO.TIPO_CONV = 0
				 OR V_PDV_VD_CONVENIO.FLAG_FECHA_DIARIO = 1)
			AND V_PDV_VD_CONVENIO.DEV = 0
			AND V_PDV_VD_CONVENIO.CD_CONV > 0
	GROUP BY
			 V_PDV_VD_CONVENIO.CD_CONV
		   , V_PDV_VD_CONVENIO.DS_CONV
	HAVING SUM(VLR_CONV) > 0
	UNION ALL
	SELECT
		   V_PDV_VD_CONVENIO.CD_CONV
		 , V_PDV_VD_CONVENIO.DS_CONV
		 , SUM(VLR_CONV_PBM) AS VLR_CONV
	FROM
		#V_PDV_VD_CONVENIO AS V_PDV_VD_CONVENIO
		LEFT OUTER JOIN RC_VD_CONV_FECHA_LOTE ON V_PDV_VD_CONVENIO.CD_EMP = RC_VD_CONV_FECHA_LOTE.CD_EMP
												 AND V_PDV_VD_CONVENIO.CD_FILIAL = RC_VD_CONV_FECHA_LOTE.CD_FILIAL
												 AND V_PDV_VD_CONVENIO.CD_CONV = RC_VD_CONV_FECHA_LOTE.CD_CONV
												 AND V_PDV_VD_CONVENIO.DT_VD = RC_VD_CONV_FECHA_LOTE.DT_MOV
	WHERE  V_PDV_VD_CONVENIO.CD_EMP = @CD_EMP
		   AND V_PDV_VD_CONVENIO.CD_FILIAL = @CD_FILIAL
		   AND V_PDV_VD_CONVENIO.DT_VD = @DT_VD
		   AND (V_PDV_VD_CONVENIO.TIPO_CONV = 1
				OR V_PDV_VD_CONVENIO.FLAG_FECHA_DIARIO = 1)
		   AND V_PDV_VD_CONVENIO.DEV = 0
	GROUP BY
			 V_PDV_VD_CONVENIO.CD_CONV
		   , V_PDV_VD_CONVENIO.DS_CONV
	HAVING SUM(VLR_CONV_PBM) > 0
	ORDER BY
			 V_PDV_VD_CONVENIO.DS_CONV; 


GO
	SP_RECOMPILE P_PDV_VD_CONVENIO_FECHA_CONV