/****** Object:  UserDefinedFunction [dbo].[UFD_EST_PROD_PRECO_TBL]    Script Date: 07/11/2013 13:36:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_PRECO_TBL]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_EST_PROD_PRECO_TBL]
GO
GO
GO
/*
	Funcao que busca o valor do preco de custo do produto
*/

--SELECT DBO.UFD_EST_PROD_PRECO_TBL(1,99,299001,1)
CREATE FUNCTION [dbo].[UFD_EST_PROD_PRECO_TBL](
									 @CD_EMP		INT,
									 @CD_FILIAL INT,
									 @CD_PROD	INT,
									 @TIPO_PRECO INT =0)
RETURNS MONEY AS
BEGIN

DECLARE @VLR_PROD		MONEY		--Valor do produto final
DECLARE @CD_CTR_GRP     INT
DECLARE @ctrPmc         INT

SET @VLR_PROD =0;
SET @CD_CTR_GRP=ISNULL((SELECT CD_CTR_GRP
							FROM EST_PROD_PRECO_TBL_GRUPO_PRC_FILIAL
							WHERE
								CD_EMP = @CD_EMP
								AND CD_FILIAL = @CD_FILIAL),0)
--Buscar preco de custo
IF @TIPO_PRECO=0  
begin
	--VLR_TBL
	
	Preco: 
	SET @TIPO_PRECO=0    
		SET @VLR_PROD =	(SELECT  CASE WHEN ISNULL(VLR_FABRICA,0) > 0 THEN
										ISNULL(VLR_FABRICA,0)
									ELSE ISNULL(VLR_COMPRA_GRP,0)
									END AS VLR_COMPRA
								FROM EST_PROD
								WHERE     
									CD_EMP = @CD_EMP 
									AND CD_PROD = @CD_PROD);
 
	-- Custo cpl
	IF @VLR_PROD <= 0 
	begin			 	
	   SET @VLR_PROD = (SELECT 
							CASE WHEN ISNULL(VLR_CUSTO,0)=0 THEN
									ISNULL(VLR_CUSTO_MD,0)
									ELSE ISNULL(VLR_CUSTO,0) END
						FROM 
						   EST_PROD_CPL	
						WHERE
							CD_EMP = @CD_EMP
   							AND CD_FILIAL = @CD_FILIAL
	   						AND CD_PROD = @CD_PROD)
	end


	--Preco Fabrica
	if @VLR_PROD <=0
	begin
		SET @VLR_PROD =	(SELECT  ISNULL(VLR_FORN,0) AS VLR_FORN
								FROM EST_PROD_PRECO_TBL
								WHERE     
								CD_EMP = @CD_EMP 
								AND CD_PROD = @CD_PROD
								AND CD_CTR_GRP = @CD_CTR_GRP)
	end	
	  
end

--Buscar preco de compra
IF @TIPO_PRECO=1 
begin
	
	set @ctrPmc = ISNULL((SELECT VALOR FROM 
							PRC_EMP_CONFIG 
							WHERE CD_CHAVE= 'FLAG_CTR_PMC_PRECO_COMPRA'),0) ; 

	if @ctrPmc =1
	begin
		SET @ctrPmc = ISNULL(( SELECT 1 PMC FROM EST_PROD 
	 						WHERE CD_EMP = @CD_EMP AND CD_PROD = @CD_PROD AND CTR_PMC=1),0);
	end
	 
	--Se nao tem controle de pmc 
	if @ctrPmc =0
	begin
		 SET @VLR_PROD =	(SELECT  CASE WHEN ISNULL(VLR_FABRICA,0) > 0 THEN
										ISNULL(VLR_FABRICA,0)
									ELSE ISNULL(VLR_COMPRA_GRP,0)
									END AS VLR_COMPRA
								FROM EST_PROD
								WHERE     
									CD_EMP = @CD_EMP 
									AND CD_PROD = @CD_PROD);
		
		
		IF @VLR_PROD <=0
		BEGIN
			SET @VLR_PROD =	(SELECT   ISNULL(VLR_CUSTO,0) 
								FROM EST_PROD_CPL
								WHERE     
									CD_EMP = @CD_EMP 
									AND CD_PROD = @CD_PROD
									AND CD_FILIAL =@CD_FILIAL);
		END
	end

	--Fabrica
	IF @VLR_PROD <=0 
	begin 
		SET @VLR_PROD =	ISNULL((SELECT 
								ISNULL(VLR_FORN,0) AS VLR_FORN
								FROM
									EST_PROD_PRECO_TBL
								WHERE     
									CD_EMP = @CD_EMP 
									AND CD_PROD = @CD_PROD
									AND CD_CTR_GRP = @CD_CTR_GRP),0) 
		if @VLR_PROD<=0
		begin
		   SET @VLR_PROD = (SELECT  VLR_COMPRA 
							FROM  EST_PROD_CPL	
							WHERE
								CD_EMP = @CD_EMP
   								AND CD_FILIAL = @CD_FILIAL
	   							AND CD_PROD = @CD_PROD)
		end
	end
 
	IF @VLR_PROD <=0 
	begin 
		SET @VLR_PROD =	(SELECT  CASE WHEN ISNULL(VLR_FABRICA,0) > 0 THEN
									ISNULL(VLR_FABRICA,0)
								ELSE ISNULL(VLR_COMPRA_GRP,0)
								END AS VLR_COMPRA
							FROM EST_PROD
							WHERE     
								CD_EMP = @CD_EMP 
								AND CD_PROD = @CD_PROD)
	end
	
	--Busca o valor de custo
	IF @VLR_PROD <= 0 
	begin			 	
	   SET @VLR_PROD = (SELECT  CASE WHEN ISNULL(VLR_CUSTO,0)=0 THEN
										ISNULL(VLR_CUSTO_MD,0)
									ELSE ISNULL(VLR_CUSTO,0) END
						FROM 
						   EST_PROD_CPL	
						WHERE
							CD_EMP = @CD_EMP
   							AND CD_FILIAL = @CD_FILIAL
	   						AND CD_PROD = @CD_PROD)
	end

	-- Tenta achar preco pela ultima entrada.
	-- Muito lento retirado
	/*IF @VLR_PROD <= 0 
	begin
		Declare @cdNf int 

	   set @cdNf =(SELECT MAX(DISTINCT CD_NF) AS CD_NF
			   		FROM         
				   		EST_NF_ENT_IT
					    WHERE     
							CD_EMP = @CD_EMP
							AND CD_FILIAL = @CD_FILIAL
							AND CD_PROD = @CD_PROD);
	   	
	   SET @VLR_PROD = (SELECT  VLR_IT
		   				  FROM EST_NF_ENT_IT
				   		  WHERE 
					   			CD_EMP = @CD_EMP
						   		AND CD_FILIAL = @CD_FILIAL
   								AND CD_PROD = @CD_PROD
	   							AND CD_NF = @cdNF)	
	end*/
	 			
	IF @VLR_PROD <= 0 
		goto Preco --se nao encontrou tipo 1 vai buscar no 0 alguma informacao
end

RETURN (ISNULL(@VLR_PROD,0))
END

