/****** Object:  StoredProcedure [dbo].[P_PDV_VD_IT_REPROCESSAR_CUSTO]    Script Date: 11/13/2013 11:05:15 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_DETE_IT_REPROCESSAR_CUSTO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_DETE_IT_REPROCESSAR_CUSTO]
GO
GO
GO
--P_EST_PROD_DETE_IT_REPROCESSAR_CUSTO  10,2013,999
--Versao 2356
CREATE PROCEDURE [dbo].[P_EST_PROD_DETE_IT_REPROCESSAR_CUSTO]( @MES INT=NULL, 
														@ANO INT=NULL,
														@CD_FILIAL_PAR 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
	 
    
/*************************************************************************************************/

 
IF  OBJECT_ID('TEMPDB.DBO.#RsProd_Det') IS NOT NULL
	BEGIN
		 DROP TABLE #RsProd_Det
	END
	 
	CREATE TABLE #RsProd_Det(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_Det (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 
	BEGIN
		delete from #RsProd_Det
		INSERT INTO #RsProd_Det(CD_EMP, CD_FILIAL, CD_PROD)
		SELECT 1, @CD_FILIAL_PAR, @CD_PROD
	END 

	if not exists(select top 1 cd_emp from #RsProd_Det)
	begin
		INSERT INTO #RsProd_Det(CD_EMP, CD_FILIAL, CD_PROD)
		select DISTINCT D.CD_EMP, D.CD_FILIAL, I.CD_PROD
		from 
			EST_PROD_DETE_IT I
			INNER JOIN EST_PROD_DETE D ON 
			I.CD_EMP = D.CD_EMP AND 
			I.CD_LOTE = D.CD_LOTE  
		where
			D.CD_EMP = 1 
			AND D.CD_FILIAL = @CD_FILIAL_PAR 
			AND D.DT_CAD BETWEEN @DT_INI AND @DT_FIM
	end

/*************************************************************************************************/



	PRINT 'Custo do movimento de produtos deteriorados ' + CONVERT(VARCHAR, GETDATE (),108)
	
	DECLARE cFilialDeter 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 cFilialDeter
	FETCH NEXT FROM cFilialDeter 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)
				begin
					UPDATE
						EST_PROD_DETE_IT
					SET 
						EST_PROD_DETE_IT.VLR_CUSTO_PROD = dbo.F_VLR_CUSTO_EST_FECHA_PROC_MOV (EST_PROD_DETE.CD_EMP,EST_PROD_DETE.CD_FILIAL,EST_PROD_DETE_IT.CD_PROD,EST_PROD_DETE.DT_CAD,5)
					FROM
						EST_PROD_DETE_IT 
						INNER JOIN EST_PROD_DETE ON 
						EST_PROD_DETE_IT.CD_EMP = EST_PROD_DETE.CD_EMP AND 
						EST_PROD_DETE_IT.CD_LOTE = EST_PROD_DETE.CD_LOTE

						inner join #RsProd_Det d on 
						EST_PROD_DETE.CD_EMP = d.CD_EMP AND 
						EST_PROD_DETE.CD_FILIAL = d.CD_FILIAL 
						AND EST_PROD_DETE_IT.CD_PROD = d.CD_PROD 
					WHERE
						EST_PROD_DETE.CD_EMP = 1 
						AND EST_PROD_DETE.CD_FILIAL = @CD_FILIAL 
						AND EST_PROD_DETE.DT_CAD = @DIA 

					UPDATE
						I
					SET 
						I.VLR_CUSTO_MD= dbo.F_VLR_CUSTO_EST_FECHA_PROC_MOV (I.CD_EMP,I.CD_FILIAL,I.CD_PROD,I.DT_CAD, 0)
					FROM
						EST_PROD_IMP I
						inner join #RsProd_Det d on 
						I.CD_EMP = d.CD_EMP AND 
						I.CD_FILIAL = d.CD_FILIAL 
						AND I.CD_PROD = d.CD_PROD 
					WHERE
						I.CD_EMP = 1 
						AND I.CD_FILIAL = @CD_FILIAL 
						AND I.DT_CAD = @DIA 
						  
				end					
				SELECT @DIA = @DIA + 1
			END		
		FETCH NEXT FROM  cFilialDeter INTO @CD_FILIAL 
	END
CLOSE cFilialDeter
DEALLOCATE cFilialDeter