/****** Object:  StoredProcedure [dbo].[P_EST_PROD_INVENTARIO]    Script Date: 04/30/2013 17:54:03 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_INVENTARIO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_INVENTARIO]
GO
go
--EXEC P_EST_PROD_INVENTARIO 1, 2016,12,1,1,0,0,0,'',1
CREATE PROCEDURE [dbo].[P_EST_PROD_INVENTARIO] (
										@CD_EMP	INT, 
										@ANO INT,
										@MES INT,
										@CD_FILIAL INT=NULL,
										@INCLUI_NEGATIVOS INT=0, 
										@FLUTUANTE INT=1,
										@UNIFICARINVENTARIO INT=0, 
										@FILIALAGRUPAMENTO INT=0,
										@FILIAISUNIFICAR  VARCHAR(MAX)='',
										@TIPOCUSTO INT=0)
	with encryption 
AS 
/*
	@TIPOCUSTO - 0 - Custo medio
	             1 - ultimo custo
*/
set nocount on;
DECLARE @DT_REF AS DATETIME;  
SET @DT_REF = CONVERT(VARCHAR(10), (@MES+@ANO*100))+'01' ; 
SET @DT_REF = dbo.F_FINAL_MES(@DT_REF); 
declare @EstTerceiros int=0;

if exists (select cd_emp from prc_filial_config
			 where 
				cd_emp=@cd_emp and cd_Filial =@CD_FILIAL
				AND valor='1'
				and cd_chave='INCLUI_EST_EM_TERCEIRO_REG_INVENTARIO')
BEGIN
    SET @EstTerceiros=1
END
			 
Print 'Inicio - Dt Ref.: ' + convert(varchar, @DT_REF,112) + ' - ' + convert(varchar, GETDATE(),112)
  
IF @UNIFICARINVENTARIO=0
begin
	DELETE FROM 
		EST_PROD_INVENTARIO 
	WHERE
		CD_EMP =@CD_EMP 
		AND CASE WHEN ISNULL(@CD_FILIAL,0)=0 THEN
				ISNULL(@CD_FILIAL,0)
			ELSE CD_FILIAL  END  = ISNULL(@CD_FILIAL,0)
			AND DT_REF= @DT_REF;

	Print 'Excluiu registros anteriores - Dt Ref.: ' + convert(varchar, @DT_REF,112) + '  -  ' + convert(varchar, GETDATE(),112)
	--SELECT * FROM EST_PROD_INVENTARIO WHERE CD_EMP =1 AND CD_FILIAL = 4 AND CD_PROD = 520626
	INSERT INTO EST_PROD_INVENTARIO (CD_EMP,CD_FILIAL,CD_PROD,
										DT_REF,QT_EST,VLR_PROD,
										VLR_PROD_ANTERIOR,DT_CAD,
										VLR_AJUSTE_CUSTO, VLR_ACERTO_MOV, VLR_ESTORNO ) 
	SELECT 
		@CD_EMP,
		EST_FECHA.CD_FILIAL,  
		EST_FECHA.CD_PROD,  
		@DT_REF,
		(ISNULL(EST_FECHA.QT_EST,0) 
			+ Case when @EstTerceiros=1 then  ISNULL(EST_FECHA.QT_EST_EM_TERC,0)else 0 end
						+ CASE WHEN @FLUTUANTE=1 THEN 
								ISNULL(EST_FECHA.QT_EST_FLUT,0) 
						ELSE 0 END)  AS QT_EST,  
		 
		CASE WHEN @TIPOCUSTO=0 THEN 
			ISNULL(EST_FECHA.VLR_CUSTO_MD,0)  
		ELSE
			ISNULL(EST_FECHA.VLR_CUSTO,0)  	
		END AS  VLR_PROD, 
		0 VLR_PROD_ANTERIOR, 
		GETDATE() DT_CAD, 
		ISNULL(EST_FECHA.VLR_ACERTO_CUSTO,0)	VLR_AJUSTE_CUSTO,	
		ISNULL(EST_FECHA.VLR_SALDO_CUSTO,0)	VLR_ACERTO_MOV	,
		ISNULL(EST_FECHA.VLR_ESTORNO_NF,0)	VLR_ESTORNO 
	FROM 
		EST_FECHA 		
		INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA arv ON
		EST_FECHA.CD_EMP =arv.CD_EMP AND
		EST_FECHA.CD_PROD = arv.CD_PROD 
		INNER JOIN EST_ARV_MERC_LINHA linha ON
		arv.CD_EMP =linha.CD_EMP AND
		arv.CD_ARV_MERC_LINHA = linha.CD_ARV_MERC_LINHA 
	WHERE 
		EST_FECHA.CD_EMP =@CD_EMP 
		AND CASE WHEN ISNULL(@CD_FILIAL,0)=0 THEN
				ISNULL(@CD_FILIAL,0)
			ELSE EST_FECHA.CD_FILIAL  END  = ISNULL(@CD_FILIAL,0)
		AND EST_FECHA.ANO_FECHA =@ANO  
		AND EST_FECHA.MES_FECHA =@MES
		AND CASE WHEN @INCLUI_NEGATIVOS = 0 THEN
			 (ISNULL(EST_FECHA.QT_EST,0) 
				+ Case when @EstTerceiros=1 then  ISNULL(EST_FECHA.QT_EST_EM_TERC,0)else 0 end
				+ CASE WHEN @FLUTUANTE=1 THEN ISNULL(EST_FECHA.QT_EST_FLUT,0) ELSE 0 END)
			  ELSE 1 END > 0 
		AND linha.FLAG_LINHA_NAO_FAZ_BALANCO=0

	--Valor compra grupo atual.
	IF @TIPOCUSTO = 2 
	BEGIN
		print 'gerando inventario pelo custo grupo'
		UPDATE 
			I 
			SET VLR_PROD = EST_NF_ENT_IT.VLR_IT
		FROM
			EST_PROD_INVENTARIO i
			JOIN EST_NF_ENT_IT ON EST_NF_ENT_IT.CD_EMP = i.CD_EMP
								AND EST_NF_ENT_IT.CD_PROD = i.CD_PROD
			INNER JOIN EST_PROD_ENT_GRP_ECON_EST_NF_ENT_IT ON EST_NF_ENT_IT.CD_EMP = EST_PROD_ENT_GRP_ECON_EST_NF_ENT_IT.CD_EMP
															AND EST_NF_ENT_IT.CD_FILIAL = EST_PROD_ENT_GRP_ECON_EST_NF_ENT_IT.CD_FILIAL
															AND EST_NF_ENT_IT.CD_NF = EST_PROD_ENT_GRP_ECON_EST_NF_ENT_IT.CD_NF
															AND EST_NF_ENT_IT.CD_PROD = EST_PROD_ENT_GRP_ECON_EST_NF_ENT_IT.CD_PROD
		WHERE 
			EST_NF_ENT_IT.CD_EMP = @CD_EMP
			AND i.DT_REF = @DT_REF
			AND CASE WHEN ISNULL(@CD_FILIAL, 0) = 0 THEN ISNULL(@CD_FILIAL, 0)
					ELSE i.CD_FILIAL END = ISNULL(@CD_FILIAL, 0);

	END

end

IF @UNIFICARINVENTARIO=1
begin
CREATE TABLE #RS_FILIAL  (CD_EMP INT, CD_FILIAL INT)  
  
INSERT INTO #RS_FILIAL  
SELECT   
	1,  
	ID   
FROM   
	UFD_PRM_IN_STRING(@FILIAISUNIFICAR)  

	DELETE FROM 
		EST_PROD_INVENTARIO 
	WHERE
		CD_EMP =@CD_EMP 
		AND CD_FILIAL = @FILIALAGRUPAMENTO
		AND DT_REF= @DT_REF;

	Print 'Excluiu registros anteriores - Dt Ref.: ' + convert(varchar, @DT_REF,112) + '  -  ' + convert(varchar, GETDATE(),112)
	INSERT INTO EST_PROD_INVENTARIO (CD_EMP,CD_FILIAL,CD_PROD,
						DT_REF,QT_EST,VLR_PROD,
						VLR_PROD_ANTERIOR,DT_CAD) 
	SELECT 
		@CD_EMP,
		@FILIALAGRUPAMENTO,  
		EST_FECHA.CD_PROD,  
		@DT_REF,
		SUM((ISNULL(EST_FECHA.QT_EST,0) 
			+ Case when @EstTerceiros=1 then  ISNULL(EST_FECHA.QT_EST_EM_TERC,0)else 0 end
						+ CASE WHEN @FLUTUANTE=1 THEN
								ISNULL(EST_FECHA.QT_EST_FLUT,0)
						ELSE 0 END))  AS QT_EST,  	
		
		CASE WHEN @TIPOCUSTO=0 THEN 
			AVG(isnull(EST_FECHA.VLR_CUSTO_MD,0))   
		ELSE
			AVG(ISNULL(EST_FECHA.VLR_CUSTO,0))    	
		END AS  VLR_PROD, 
		  0 VLR_PROD_ANTERIOR, 
		GETDATE() DT_CAD
	FROM 
		EST_FECHA  
		INNER JOIN #RS_FILIAL filial on
		EST_FECHA.CD_EMP = filial.CD_EMP AND
		EST_FECHA.CD_FILIAL = filial.CD_FILIAL 
		INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA arv ON
		EST_FECHA.CD_EMP =arv.CD_EMP AND
		EST_FECHA.CD_PROD = arv.CD_PROD 
		INNER JOIN EST_ARV_MERC_LINHA linha ON
		arv.CD_EMP =linha.CD_EMP AND
		arv.CD_ARV_MERC_LINHA = linha.CD_ARV_MERC_LINHA 
	WHERE 
		EST_FECHA.ANO_FECHA =@ANO  
		AND EST_FECHA.MES_FECHA =@MES
		AND CASE WHEN @INCLUI_NEGATIVOS = 0 THEN
			 (ISNULL(EST_FECHA.QT_EST,0) 
				+ Case when @EstTerceiros=1 then  ISNULL(EST_FECHA.QT_EST_EM_TERC,0)else 0 end
				+ CASE WHEN @FLUTUANTE=1 THEN
					ISNULL(EST_FECHA.QT_EST_FLUT,0) ELSE 0 END)
			  ELSE 1 END > 0 
		AND linha.FLAG_LINHA_NAO_FAZ_BALANCO=0
	GROUP BY
		EST_FECHA.CD_PROD
end 
Print 'Fim da rotina - Dt Ref.: ' + convert(varchar, @DT_REF,112) + '  -  ' + convert(varchar, GETDATE(),112)


