/****** Object:  StoredProcedure [dbo].[P_PDV_VD_IT_DEVOLUCAO_REPROCESSAR_CUSTO]    Script Date: 10/09/2013 18:32:56 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_PDV_VD_IT_DEVOLUCAO_REPROCESSAR_CUSTO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_PDV_VD_IT_DEVOLUCAO_REPROCESSAR_CUSTO]
GO
GO
GO
CREATE PROCEDURE [dbo].[P_PDV_VD_IT_DEVOLUCAO_REPROCESSAR_CUSTO]( @MES INT=NULL, 
																	@ANO INT=NULL,
																	@CD_FILIAL_PAR INT=0,
																	@TIPO_CUSTO INT=0,
																	@DT_PROC DATETIME=NULL,
																	@CD_PROD 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;
			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
	 
/*****************************************************************************************************************************/
--Relacao produtos
/*****************************************************************************************************************************/
	IF  OBJECT_ID('TEMPDB.DBO.#RsProd_Dev') IS NOT NULL
	begin
		 DROP TABLE #RsProd_Dev
	end

	CREATE TABLE #RsProd_Dev (CD_EMP INT,
								CD_FILIAL INT,
								CD_PROD INT )  

/*****************************************************************************************************************************/
	DECLARE @NMTBL VARCHAR(100), @CMD VARCHAR(500);
	SET @NMTBL = 'EST_FECHA_PROC_MOV_PEND_TBL_PROC_' +CONVERT(VARCHAR,@CD_FILIAL_PAR);

	IF NOT OBJECT_ID(@NMTBL) IS NULL
	BEGIN
				
		SET @CMD = 'INSERT INTO #RsProd_Dev(CD_EMP, CD_FILIAL, CD_PROD)
					SELECT  DISTINCT CD_EMP, CD_FILIAL, CD_PROD
					FROM ' + @NMTBL + ' WHERE CD_EMP= 1'
		SET @CMD = @CMD+ ' AND CD_FILIAL =' + convert(varchar,@CD_FILIAL_PAR) 
		
		IF NOT @DT_PROC  IS NULL
			SET @CMD = @CMD+ ' AND DT_PROC =''' + convert(varchar,@DT_PROC,112)  + ''''


		EXEC (@CMD);
	END 

	if @CD_PROD > 0 or @CD_FILIAL_PAR > 0
		truncate table #RsProd_Dev;

	if not exists(select top 1 cd_emp from #RsProd_Dev)
	begin
		truncate table #RsProd_Dev;

		INSERT INTO #RsProd_Dev(CD_EMP, CD_FILIAL, CD_PROD)
		SELECT DISTINCT 
			i.CD_EMP, 
			i.CD_FILIAL, 
			i.CD_PROD
		FROM 
			PDV_VD_DEV_EST_PROD_IMP i
		WHERE
			i.CD_EMP =1
			AND i.CD_FILIAL = CASE WHEN @CD_FILIAL_PAR > 0 THEN 
								@CD_FILIAL_PAR ELSE  i.CD_FILIAL  END
			AND i.CD_PROD = CASE WHEN @CD_PROD > 0 THEN 
								@CD_PROD  ELSE  i.CD_PROD  END

			and i.dt_devo between @DT_ini and @DT_FIM 
	end
		
	CREATE UNIQUE CLUSTERED INDEX ix_RsProd_Dev ON #RsProd_Dev (CD_PROD,CD_FILIAL,CD_EMP);			
		
/*****************************************************************************************************************************/ 

	
	PRINT 'Custo do movimento de devolucao de vendas ' + CONVERT(VARCHAR, GETDATE (),108)
	
	DECLARE cFilialPdvDevo 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 cFilialPdvDevo
	FETCH NEXT FROM cFilialPdvDevo INTO @CD_FILIAL

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

				PRINT 'Filial ' + CONVERT(VARCHAR, @CD_FILIAL) + ' -  Processando dia  ' + CONVERT(VARCHAR, @DIA, 112) + ' ' + CONVERT(VARCHAR, GETDATE (),108)
				IF @TIPO_CUSTO = 0 
					begin
						UPDATE
							I
						SET 
							VLR_CUSTO_MEDIO = dbo.F_VLR_CUSTO_EST_FECHA_PROC_MOV (I.CD_EMP,I.CD_FILIAL,I.CD_PROD,I.DT_DEVO,0),
							VLR_CUSTO = dbo.F_VLR_CUSTO_EST_FECHA_PROC_MOV (I.CD_EMP,I.CD_FILIAL,I.CD_PROD,I.DT_DEVO,3)
						FROM
							PDV_VD_DEV_EST_PROD_IMP I
							INNER JOIN #RsProd_Dev P ON 
							i.CD_EMP	= p.CD_EMP AND
							i.CD_FILIAL = p.CD_FILIAL AND
							i.CD_PROD	= p.CD_PROD
						WHERE
							P.CD_EMP = 1 
							AND I.CD_FILIAL = @CD_FILIAL 
							AND I.DT_DEVO = @DIA;
					end					
				
				IF @TIPO_CUSTO = 1 
					begin
						UPDATE
							I
						SET 
							VLR_CUSTO_MEDIO = dbo.F_VLR_CUSTO_EST_FECHA_PROC_MOV (I.CD_EMP,I.CD_FILIAL,I.CD_PROD,I.DT_DEVO,2),
							VLR_CUSTO = dbo.F_VLR_CUSTO_EST_FECHA_PROC_MOV (I.CD_EMP,I.CD_FILIAL,I.CD_PROD,I.DT_DEVO,2)
						FROM
							PDV_VD_DEV_EST_PROD_IMP I
							INNER JOIN #RsProd_Dev P ON 
							i.CD_EMP	= p.CD_EMP AND
							i.CD_FILIAL = p.CD_FILIAL AND
							i.CD_PROD	= p.CD_PROD
						WHERE
							P.CD_EMP = 1 
							AND I.CD_FILIAL = @CD_FILIAL 
							AND I.DT_DEVO = @DIA;
					end					

				SELECT @DIA = @DIA + 1
			END		
		FETCH NEXT FROM  cFilialPdvDevo INTO @CD_FILIAL 
	END
CLOSE cFilialPdvDevo
DEALLOCATE cFilialPdvDevo
GO


