IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[F_VLR_CUSTO_EST_FECHA_PROC_MOV]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[F_VLR_CUSTO_EST_FECHA_PROC_MOV]
GO 
--select dbo.F_VLR_CUSTO_EST_FECHA_PROC_MOV(1,12,26696,'2016-03-29',27)
CREATE  FUNCTION [dbo].[F_VLR_CUSTO_EST_FECHA_PROC_MOV](@CD_EMP 		INT,
														@CD_FILIAL 		INT,
														@CD_PROD 		INT,
														@DT_PROC 		DATETIME,
														@TIPO_CUSTO		INT)
RETURNS MONEY AS  
BEGIN
/*
@TIPO_CUSTO - 0 - Custo Medio no periodo (Obs. não forçar para vir qualquer custo, pois dá alteração no fechamento contábil).
              1 - Custo gerencial
              2 - Custo Medio cadastro produto - CPL
              3 - Custo Medio qualquer
              4 - Custo medio fechamento
			  5 - Custo Fechamento
			  6 - Custo de entrada
			  7 - Custo gerencial - CPL
			  9 - Valor de Saldo Custo
			  10 - Valor Custo Md Inicial
			  11 - Valor Custo Md Inicial sobreposto por Ajuste no custo
			 	
			  --Flutuante
			  20 - Custo Medio no flut
			  21 - Custo gerencial flut
			  23 - Custo Medio qualquer FLUT
              24 - Custo medio fechamento 
			  27 - Acerto de custo medio 
			  
			  --Qtdes
			  30 - Qtde inicial

*/
DECLARE @VLR_CUSTO MONEY 
DECLARE @DT_ATU AS DATETIME
DECLARE @DT_MAX AS DATETIME

SET @DT_ATU = CONVERT(VARCHAR, GETDATE(),112);
SET @DT_PROC = CONVERT(VARCHAR, @DT_PROC,112);


SET @DT_MAX = (SELECT MAX(DT_PROC) DT_PROC
					FROM EST_FECHA_PROC_MOV with (nolock)
					WHERE 
					CD_EMP =1 
					AND CD_FILIAL	=@CD_FILIAL
					AND CD_PROD		=@CD_PROD
					AND DT_PROC		<=@DT_PROC)

IF @TIPO_CUSTO in (0,3)
	begin
		
	 
		SET @VLR_CUSTO = (SELECT VLR_CUSTO_MD_NOVO
								 FROM EST_FECHA_PROC_MOV with (nolock)
								 WHERE 
									CD_EMP =1 
									AND CD_FILIAL	=@CD_FILIAL
									AND CD_PROD		=@CD_PROD
									AND DT_PROC		= @DT_MAX) 
		IF @TIPO_CUSTO=0 --Se for custo medio ja sai..
		begin
			IF @VLR_CUSTO IS NULL
			begin
				SET @VLR_CUSTO = 0
			end	
			RETURN ISNULL(@VLR_CUSTO,0)
		end

		IF @TIPO_CUSTO=3
		BEGIN														 
			if @VLR_CUSTO IS NULL
				BEGIN
					
					SET @VLR_CUSTO = (SELECT	
										CASE WHEN ISNULL(VLR_CUSTO_MD,0) =0 THEN
											ISNULL(VLR_CUSTO,0) ELSE VLR_CUSTO_MD END
					FROM 
						EST_FECHA
					WHERE 
						CD_EMP =1 
						AND CD_FILIAL	=@CD_FILIAL
						AND CD_PROD		=@CD_PROD
						AND ANO_FECHA	=YEAR(@DT_PROC)
						AND MES_FECHA	=MONTH(@DT_PROC))
				END   													
		END
end
 

IF @TIPO_CUSTO in (20,23)
	begin 
		SET @VLR_CUSTO = (SELECT VLR_CUSTO_MD_NOVO
								 FROM EST_FECHA_PROC_MOV with (nolock)
								 WHERE 
									CD_EMP =@CD_EMP 
									AND CD_FILIAL	=@CD_FILIAL
									AND CD_PROD		=@CD_PROD
									AND DT_PROC		= @DT_MAX) 
		IF @TIPO_CUSTO=23
		BEGIN														 
			if @VLR_CUSTO IS NULL
				BEGIN
					
					SET @VLR_CUSTO = (SELECT	
										CASE WHEN ISNULL(VLR_CUSTO_MD_FLUTUANTE,0) =0 THEN
											ISNULL(VLR_CUSTO_MD,0) ELSE VLR_CUSTO_MD_FLUTUANTE END
					FROM 
						EST_FECHA
					WHERE 
						CD_EMP =@CD_EMP 
						AND CD_FILIAL	=@CD_FILIAL
						AND CD_PROD		=@CD_PROD
						AND ANO_FECHA	=YEAR(@DT_PROC)
						AND MES_FECHA	=MONTH(@DT_PROC))
				END   													
		END
end


IF @TIPO_CUSTO = 1
	begin
		SET @VLR_CUSTO = ISNULL((SELECT VLR_CUSTO_GER_NOVO  
										FROM EST_FECHA_PROC_MOV with (nolock)
										WHERE 
											CD_EMP =@CD_EMP 
											AND CD_FILIAL	=@CD_FILIAL
											AND CD_PROD		=@CD_PROD
											AND DT_PROC		=@DT_MAX),0) 
	end

IF @TIPO_CUSTO = 21
	begin
		SET @VLR_CUSTO = ISNULL((SELECT VLR_CUSTO_GER_NOVO  
										FROM EST_FECHA_PROC_MOV with (nolock)
										WHERE 
											CD_EMP =@CD_EMP 
											AND CD_FILIAL	=@CD_FILIAL
											AND CD_PROD		=@CD_PROD
											AND DT_PROC		= @DT_MAX),0) 
	end

IF @TIPO_CUSTO = 2 
	begin		
		SET @VLR_CUSTO = ISNULL((SELECT 
									VLR_CUSTO_MD 
								  FROM 
									EST_PROD_CPL  with (nolock)
								  WHERE 
									CD_EMP		= @CD_EMP AND 
									CD_FILIAL	= @CD_FILIAL AND 
									CD_PROD		= @CD_PROD),0) 
	end
  
 
 if @TIPO_CUSTO in(4,5)
	BEGIN 
		SET @VLR_CUSTO = (SELECT	
							CASE WHEN @TIPO_CUSTO = 4 then
								ISNULL(VLR_CUSTO_MD,0)
							else
								ISNULL(VLR_CUSTO,0)
							end
		FROM 
			EST_FECHA
		WHERE 
			CD_EMP =@CD_EMP 
			AND CD_FILIAL	=@CD_FILIAL
			AND CD_PROD		=@CD_PROD
			AND ANO_FECHA	=YEAR(@DT_PROC)
			AND MES_FECHA	=MONTH(@DT_PROC))
			
		IF ISNULL(@VLR_CUSTO,0) =0 
			BEGIN
				--SE NAO TEVE FECHAMENTO PEGA O VLR ATUAL
				IF YEAR (@DT_PROC) = YEAR(@DT_ATU) AND MONTH (@DT_PROC) = MONTH(@DT_ATU) 
				BEGIN
					SET @TIPO_CUSTO=0 
				END
			END  
	END   					 
	
if @TIPO_CUSTO in(24,25)
BEGIN 
		SET @VLR_CUSTO = (SELECT	
								CASE WHEN @TIPO_CUSTO = 24 THEN
									ISNULL(VLR_CUSTO_MD_FLUTUANTE,0)
								ELSE
									ISNULL(VLR_CUSTO_MD,0)
								END
							FROM 
								EST_FECHA
							WHERE 
								CD_EMP =@CD_EMP 
								AND CD_FILIAL	=@CD_FILIAL
								AND CD_PROD		=@CD_PROD
								AND ANO_FECHA	=YEAR(@DT_PROC)
								AND MES_FECHA	=MONTH(@DT_PROC))
			
		IF ISNULL(@VLR_CUSTO,0) =0 
			BEGIN
				--SE NAO TEVE FECHAMENTO PEGA O VLR ATUAL
				IF YEAR (@DT_PROC) = YEAR(@DT_ATU) AND MONTH (@DT_PROC) = MONTH(@DT_ATU) 
				BEGIN
					SET @TIPO_CUSTO=0 
				END
			END  
END   		


IF @TIPO_CUSTO =26
	begin
		 
		SET @VLR_CUSTO = (SELECT VLR_CUSTO_MD_INI 
								 FROM EST_FECHA_PROC_MOV
								 WHERE 
									CD_EMP =@CD_EMP 
									AND CD_FILIAL	=@CD_FILIAL
									AND CD_PROD		=@CD_PROD
									AND DT_PROC		= @DT_MAX) 
	end
	

 if @TIPO_CUSTO =6
	BEGIN 
		SET @VLR_CUSTO = ISNULL((SELECT CASE WHEN ISNULL(VLR_AJUSTE,0)=0 THEN VLR_ENT ELSE VLR_AJUSTE END
										FROM EST_FECHA_PROC_MOV with (nolock)
										WHERE 
											CD_EMP =@CD_EMP 
											AND CD_FILIAL	=@CD_FILIAL
											AND CD_PROD		=@CD_PROD
											AND VLR_ENT > 0
											AND DT_PROC		= (SELECT MAX(DT_PROC) DT_PROC
																FROM EST_FECHA_PROC_MOV
																WHERE 
																	CD_EMP =@CD_EMP 
																	AND CD_FILIAL	=@CD_FILIAL
																	AND CD_PROD		=@CD_PROD
																	AND DT_PROC		<=@DT_PROC
																	AND VLR_ENT > 0)),0) 
	END   	

IF @TIPO_CUSTO = 7 
	begin
		SET @VLR_CUSTO = ISNULL((SELECT 
									VLR_CUSTO_GER 
								  FROM 
									EST_PROD_CPL  with (nolock)
								  WHERE 
									CD_EMP		= @CD_EMP AND 
									CD_FILIAL	= @CD_FILIAL AND 
									CD_PROD		= @CD_PROD),0) 
	end
 	
if @VLR_CUSTO=0  OR @VLR_CUSTO IS NULL
	begin
		IF NOT @TIPO_CUSTO IN (0,4,5)
		BEGIN
			SET @VLR_CUSTO = ISNULL((SELECT 
										VLR_CUSTO
									  FROM 
										EST_PROD_CPL  with (nolock)
									  WHERE 
										CD_EMP		= @CD_EMP AND 
										CD_FILIAL	= @CD_FILIAL AND 
										CD_PROD		= @CD_PROD),0) 
		END
	end

IF @TIPO_CUSTO = 9 
	begin
		SET @VLR_CUSTO = ISNULL((SELECT VLR_SALDO_CUSTO 
								 FROM EST_FECHA_PROC_MOV with (nolock)
								 WHERE 
									CD_EMP =@CD_EMP 
									AND CD_FILIAL	=@CD_FILIAL
									AND CD_PROD		=@CD_PROD
									AND DT_PROC		=  @DT_MAX) ,0)
	end


	
IF @TIPO_CUSTO =10
	begin
		
	 
		SET @VLR_CUSTO = (SELECT VLR_CUSTO_MD_INI 
								 FROM EST_FECHA_PROC_MOV with (nolock)
								 WHERE 
									CD_EMP =@CD_EMP 
									AND CD_FILIAL	=@CD_FILIAL
									AND CD_PROD		=@CD_PROD
									AND DT_PROC		=@DT_MAX) 
	end

	
IF @TIPO_CUSTO =11
	begin
		
	 
		SET @VLR_CUSTO = (SELECT case when ISNULL(VLR_AJUSTE,0) <> 0 THEN
												ISNULL(VLR_AJUSTE,0)
										ELSE VLR_CUSTO_MD_INI END 
								 FROM EST_FECHA_PROC_MOV with (nolock)
								 WHERE 
									CD_EMP =@CD_EMP 
									AND CD_FILIAL	=@CD_FILIAL
									AND CD_PROD		=@CD_PROD
									AND DT_PROC		= @DT_MAX) 
	end
	
IF @TIPO_CUSTO =30
	begin
		
		SET @VLR_CUSTO = (SELECT QT_EST_INI_DIA 
								 FROM EST_FECHA_PROC_MOV with (nolock)
								 WHERE 
									CD_EMP =@CD_EMP 
									AND CD_FILIAL	=@CD_FILIAL
									AND CD_PROD		=@CD_PROD
									AND DT_PROC		= @DT_MAX) 
	end

IF @TIPO_CUSTO=27
	begin
		 
		SET @VLR_CUSTO = (SELECT VLR_AJUSTE 
								 FROM EST_FECHA_PROC_MOV
								 WHERE 
									CD_EMP =@CD_EMP 
									AND CD_FILIAL	=@CD_FILIAL
									AND CD_PROD		=@CD_PROD
									AND DT_PROC		= @DT_MAX) 
	end

IF @VLR_CUSTO IS NULL
	begin
		SET @VLR_CUSTO = 0
	end
	
RETURN ISNULL(@VLR_CUSTO,0)
END