IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_PDV_VD_CONVENIO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_PDV_VD_CONVENIO]
GO

--EXEC P_PDV_VD_CONVENIO 5,2014,0
CREATE PROCEDURE [dbo].[P_PDV_VD_CONVENIO]( @MES INT=NULL, 
														@ANO INT=NULL,
														@CD_FILIAL_PAR INT=0,
														@DT_PROC DATETIME=NULL,
														@QTDE_DIAS_PROC INT=0)  AS
	DECLARE @DT_INI DATETIME
	DECLARE @DT_FIM DATETIME
	DECLARE @DIA DATETIME
	DECLARE @CD_FILIAL AS INT
	DECLARE @AGORA DATETIME
	 
	
	SET NOCOUNT ON
	IF  @DT_PROC IS NULL
		begin
			IF @ANO IS NULL OR @MES IS NULL
				begin
					SELECT @AGORA=DATEADD(month, -1, GETDATE()), @ANO=YEAR(@AGORA), @MES=MONTH(@AGORA)
				end
			
			SET @DT_INI = CONVERT(DATETIME, (CONVERT(VARCHAR, (@ANO*100+@MES)) + '01')) ;
			SET @DT_FIM = dbo.F_FINAL_MES (@DT_INI) ;
		end
		
	IF  NOT @DT_PROC IS NULL
		begin
			SET @DT_INI = @DT_PROC - @QTDE_DIAS_PROC;
			SET @DT_FIM = @DT_PROC;
		end
	
	--RODA SO ATE ONTEM
	IF  @DT_FIM > dbo.F_HOJE()
	BEGIN
		SET @DT_FIM = dbo.F_HOJE() -1 
	END
	
	DECLARE cFilialPdvConv CURSOR FAST_FORWARD READ_ONLY FOR 
	SELECT  
	 	CD_FILIAL
	FROM 
		PRC_FILIAL
	WHERE 
		CD_EMP = 1  
		AND CD_FILIAL = CASE WHEN @CD_FILIAL_PAR = 0 THEN CD_FILIAL ELSE @CD_FILIAL_PAR END
	OPEN cFilialPdvConv
	FETCH NEXT FROM cFilialPdvConv INTO @CD_FILIAL

	WHILE @@FETCH_STATUS=0
	BEGIN
		SET @DIA = @DT_INI
		WHILE @DT_FIM >= @DIA
			BEGIN

				if exists(select top 1 PDV_VD.CD_EMP from 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 RC_VD_CONV ON
															PDV_VD_RC_VD_CONV.CD_EMP = RC_VD_CONV.CD_EMP AND
															PDV_VD_RC_VD_CONV.CD_CTR = RC_VD_CONV.CD_CTR
				
							WHERE
								PDV_VD.CD_EMP = 1
								AND PDV_VD.CD_FILIAL =@CD_FILIAL
								AND PDV_VD.DT_VD =@DIA
								AND ISNULL(PDV_VD.CD_CONV,0) =0  )
				begin
					PRINT 'Filial ' + CONVERT(VARCHAR, @CD_FILIAL) + ' -  Processando dia  ' + CONVERT(VARCHAR, @DIA, 112) + ' ' + CONVERT(VARCHAR, GETDATE (),108);

					UPDATE
						PDV_VD
					SET 
						PDV_VD.CD_CONV = RC_VD_CONV.CD_CONV
					FROM
						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 RC_VD_CONV ON
						PDV_VD_RC_VD_CONV.CD_EMP = RC_VD_CONV.CD_EMP AND
						PDV_VD_RC_VD_CONV.CD_CTR = RC_VD_CONV.CD_CTR
					WHERE
						PDV_VD.CD_EMP = 1
						AND PDV_VD.CD_FILIAL =@CD_FILIAL
						AND PDV_VD.DT_VD =@DIA
						AND ISNULL(PDV_VD.CD_CONV,0) =0 ;
				end
								
				SELECT @DIA = @DIA + 1
			END		
		FETCH NEXT FROM  cFilialPdvConv INTO @CD_FILIAL 
	END
CLOSE cFilialPdvConv
DEALLOCATE cFilialPdvConv
