IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_BUSCA_PRECO_PRODUTO]') 
			AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_BUSCA_PRECO_PRODUTO]
GO
  
CREATE FUNCTION [dbo].[UFD_BUSCA_PRECO_PRODUTO](@CD_EMP	 INT
													,@CD_FILIAL  INT
													,@CD_PROD	 INT
													,@TIPO_PRECO INT=0
													,@TP_DEST	INT=0
													,@CD_DEST	INT=0 
													) 
RETURNS MONEY WITH ENCRYPTION AS
BEGIN

-- @TP_DEST
-- 0 - Cliente
-- 1 - Fornecedor 
-- 2 - Filial

	--0 Custo médio
	--1	Custo
	--2 Compra
	--3 Custo Grupo
	--4 Compra Grupo
	--5 Fabrica
	--6 Venda
	--7 Oferta
	--8 Operador Logistico
	--9 Qualquer preço
	-- 10 Ultima NF sai
	-- 11 Ultima NF ent
	-- 12 Ultima PDV
	-- 13 Franquia
	-- 14 Ressuprimento
	-- 15 Ressuprimento a Custo MEdio
	-- 16 Gross up
	-- 17 Vlr Preco Transf. Custo Origem.

	DECLARE @VLR_PROD	MONEY 
	DECLARE @ID		INT
	DECLARE @Step	INT

	IF EXISTS (SELECT VALOR FROM PRC_EMP_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'TP_ANALISE_PRECO_RESSUP' AND VALOR = 1) -- VALIDA A NIVEL DE EMPRESA SE O PRECO SERA O DA ORIGEM OU DO DESTINO: 1 - ORIGEM, 0 - DESTINO
	BEGIN
		SET @TIPO_PRECO = COALESCE((SELECT VALOR FROM PRC_FILIAL_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'FLAG_TP_PRECO_CUSTO_RESSUP_FILIAL' AND CD_FILIAL = @CD_FILIAL), @TIPO_PRECO)
	END ELSE
	BEGIN
		IF EXISTS (SELECT VALOR FROM PRC_FILIAL_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'FLAG_TP_PRECO_CUSTO_RESSUP_FILIAL' AND CD_FILIAL = @CD_DEST)
		BEGIN
			SET @TIPO_PRECO = COALESCE((SELECT CASE VALOR WHEN '' THEN NULL ELSE VALOR END AS VALOR FROM PRC_FILIAL_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'FLAG_TP_PRECO_CUSTO_RESSUP_FILIAL' AND CD_FILIAL = @CD_DEST), @TIPO_PRECO)
		END
	END
	
	set @Step =-1;
	set @VLR_PROD = 0;

	--0 Custo medio
	IF @TIPO_PRECO in(0,9) 
	begin 
		CustoMedio:
		set @Step =0;
		--SET @VLR_PROD =	ISNULL(dbo.F_VLR_CUSTO_EST_FECHA_PROC_MOV (@CD_EMP,@CD_FILIAL,@CD_PROD,getdate(),0),0)   
		SET @VLR_PROD =	 ISNULL((SELECT TOP 1  VLR_CUSTO_MD
								  FROM  EST_PROD_CPL 
								  WHERE 
									CD_EMP		= @CD_EMP AND 
									CD_FILIAL	= @CD_FILIAL AND 
									CD_PROD		= @CD_PROD),0); 

		IF (@TIPO_PRECO=0 or @VLR_PROD>0)
		begin
			goto Saida
		end
	end
	 
	--1 Custo
	IF @TIPO_PRECO in(1,9)
	begin 
		Custo:
		set @Step =1;
		SET @VLR_PROD =	 ISNULL((SELECT TOP 1  VLR_CUSTO 
								  FROM  EST_PROD_CPL 
								  WHERE 
									CD_EMP		= @CD_EMP AND 
									CD_FILIAL	= @CD_FILIAL AND 
									CD_PROD		= @CD_PROD),0); 
									 
		IF (@TIPO_PRECO=1 or  @VLR_PROD>0)
		begin
			goto Saida
		end
	end

	--2 Compra
	IF @TIPO_PRECO in(2,9)
	begin
		Compra:	
		set @Step =2;
		SET @ID =ISNULL((SELECT CD_CTR_GRP FROM EST_PROD_PRECO_TBL_GRUPO_PRC_FILIAL
								WHERE  CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL),0);

		SET @VLR_PROD =	ISNULL((SELECT TOP 1 VLR_FORN FROM
									EST_PROD_PRECO_TBL
								 WHERE     
									CD_EMP = @CD_EMP 
									AND CD_PROD = @CD_PROD
									AND CD_CTR_GRP = @ID ),0)
		if @VLR_PROD = 0
		begin
		SET @VLR_PROD =	 ISNULL((SELECT TOP 1  VLR_COMPRA
								  FROM  EST_PROD_CPL 
								  WHERE 
									CD_EMP		= @CD_EMP AND 
									CD_FILIAL	= @CD_FILIAL AND 
									CD_PROD		= @CD_PROD),0); 
		end

		if @VLR_PROD = 0
		begin
			SET @VLR_PROD =	 ISNULL((SELECT TOP 1 VLR_COMPRA_GRP FROM  EST_PROD
										WHERE   CD_EMP		= @CD_EMP 
											AND CD_PROD		= @CD_PROD),0);
		 
		end

		IF (@TIPO_PRECO=2 or  @VLR_PROD>0)
		begin
			goto Saida
		end
	end
	
	--3 Custo Grupo
	IF @TIPO_PRECO in(3,9)
	begin 
		CustoGrupo:
		set @Step =3;
		SET @VLR_PROD =	 ISNULL((SELECT TOP 1 VLR_CUSTO_GRP FROM  EST_PROD  
										WHERE   CD_EMP		= @CD_EMP 
											AND CD_PROD		= @CD_PROD),0);
											 
		IF (@TIPO_PRECO=3 or  @VLR_PROD>0)
		begin
			goto Saida
		end
	end 
  
	--4 Compra Grupo
	IF @TIPO_PRECO in(4,9)
	begin 
		CompraGrupo:
		set @Step =4;
		SET @VLR_PROD =	 ISNULL((SELECT TOP 1 VLR_COMPRA_GRP FROM  EST_PROD
										WHERE   CD_EMP		= @CD_EMP 
											AND CD_PROD		= @CD_PROD),0);
 
		IF (@TIPO_PRECO=4 or  @VLR_PROD>0)
		begin
			goto Saida
		end
	end

	--5 Fabrica
	IF @TIPO_PRECO in(5,9)
	begin 
		Fabrica:
		set @Step =5;
		SET @ID =ISNULL((SELECT CD_CTR_GRP FROM EST_PROD_PRECO_TBL_GRUPO_PRC_FILIAL
								WHERE  CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL),0);

		SET @VLR_PROD =	ISNULL((SELECT TOP 1 VLR_FORN FROM
									EST_PROD_PRECO_TBL
								 WHERE     
									CD_EMP = @CD_EMP 
									AND CD_PROD = @CD_PROD
									AND CD_CTR_GRP = @ID ),0)
		IF (@TIPO_PRECO=5 or  @VLR_PROD>0)
		begin
			goto Saida
		end
	end
	
	--6 Venda
	IF @TIPO_PRECO in(6,9)
	begin 
		Venda:
		set @Step =6;
		SET @VLR_PROD =	ISNULL((SELECT TOP 1 VLR_TABELA
								 FROM
									EST_PROD_PRECO 
								 WHERE     
									CD_EMP = @CD_EMP 
									AND CD_PROD = @CD_PROD
									AND CD_FILIAL = @CD_FILIAL),0);
		IF (@TIPO_PRECO=6 or  @VLR_PROD>0)
		begin
			goto Saida
		end
	end
	
	--7 Oferta
	IF @TIPO_PRECO in(7,9)
	begin 
		Oferta:
		set @Step =7;
		SET @VLR_PROD =	ISNULL((SELECT TOP 1 VLR_OFERTA
								 FROM
									EST_PROD_PRECO 
								 WHERE     
									CD_EMP = @CD_EMP 
									AND CD_PROD = @CD_PROD
									AND CD_FILIAL = @CD_FILIAL),0);
		IF (@TIPO_PRECO=7 or  @VLR_PROD>0)
		begin
			goto Saida
		end
	end
	 
	--8 Operador Logistico
	IF @TIPO_PRECO in(8,9)
	begin 
		OperadorLogistico:
		set @Step =8;
		SET @VLR_PROD =	 ISNULL((SELECT  TOP 1   VLR_PRECO_OPERADOR_LOG FROM  EST_PROD
										WHERE   CD_EMP		= @CD_EMP 
											AND CD_PROD		= @CD_PROD),0);
		
		IF (@TIPO_PRECO=8 or  @VLR_PROD>0)
		begin
			goto Saida
		end 
	end

	-- 10 Ultima NF sai
	IF @TIPO_PRECO in(10,9)
	begin 
		UltNFSai:
		set @Step =10;
		SET @VLR_PROD = ISNULL((SELECT   TOP 1      
	   									VLR_IT
		   						  FROM
			   							EST_NF_SAI_IT
				   				  WHERE 
					   					CD_EMP = @CD_EMP
						   				AND CD_FILIAL = @CD_FILIAL
   										AND CD_PROD = @CD_PROD
	   									AND CD_NF = (SELECT 
		   													MAX(DISTINCT CD_NF) AS CD_NF
			   											FROM         
				   											EST_NF_SAI_IT
					      								WHERE     
						   									CD_EMP = @CD_EMP
							   								AND CD_FILIAL = @CD_FILIAL
															AND CD_PROD = @CD_PROD)),0)		 	
		IF (@TIPO_PRECO=10 or  @VLR_PROD>0)
		begin
			goto Saida
		end
	end
	 
	-- 11 Ultima NF ent
	IF @TIPO_PRECO in(11,9)
	begin 
	   UltNFEnt:
	   set @Step =11;
	   SET @VLR_PROD = ISNULL( (SELECT TOP 1        
	   							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 = (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))	,0)				 	
	  	IF (@TIPO_PRECO=11 or  @VLR_PROD>0)
		begin
			goto Saida
		end
	end

	-- 12 Ultima PDV
	IF @TIPO_PRECO in(12,9)
	begin 
		UltPDV:	   
		set @Step =12;
		SET @VLR_PROD = ISNULL( (SELECT TOP 1   
	   								VLR_IT
		   						FROM PDV_VD_IT
				   				WHERE 
					   				CD_EMP = @CD_EMP
						   			AND CD_FILIAL  = @CD_FILIAL
   									AND CD_PROD		= @CD_PROD
	   								AND CD_VD = (SELECT 
		   												MAX(DISTINCT CD_VD) AS CD_VD
			   										FROM         
				   										PDV_VD_IT
					      							WHERE     
						   								CD_EMP = @CD_EMP
							   							AND CD_FILIAL = @CD_FILIAL
														AND CD_PROD = @CD_PROD))	,0)				 	
	  	IF (@TIPO_PRECO=12 or  @VLR_PROD>0)
		begin
			goto Saida
		end
	end

	--13 Franquia
	IF @TIPO_PRECO in(13,9)
	begin 
		Franquia:
		set @Step =13;
		SET @VLR_PROD =	 ISNULL((SELECT  TOP 1  VLR_PRECO_FRANQUIA FROM  EST_PROD
										WHERE   CD_EMP		= @CD_EMP 
											AND CD_PROD		= @CD_PROD),0);
		
		IF (@TIPO_PRECO=13 or  @VLR_PROD>0)
		begin
			goto Saida
		end 
	end
	  
	--14 Ressuprimento 
	IF @TIPO_PRECO in(14,15,9)
	begin 
	 
		Ressuprimento:  
		if @TP_DEST=2 --Filial >> Busca preço de tabela de transf. ressup.
		begin
		    --Deposito principal
			set @VLR_PROD=ISNULL((SELECT 
										MIN(B.VLR_TRANSF)
									FROM 
										PRC_FILIAL_DEPOSITO D
										JOIN GLB_CONFIG_PRECO_RESSUP A ON 
										D.CD_CTR_DEPO =A.CD_CTR_DEPO
										JOIN GLB_CONFIG_PRECO_RESSUP_EST_PROD B ON 
										A.CD_CONFIG = B.CD_CONFIG
										JOIN GLB_CONFIG_PRECO_RESSUP_PRC_FILIAL C ON 
										A.CD_CONFIG = C.CD_CONFIG
									WHERE
										D.CD_EMP=@CD_EMP
										AND D.CD_FILIAL = @CD_FILIAL--FILIAL DEPOSITO PRINCIPAL
										AND C.CD_FILIAL = @CD_DEST-- FILIAL DESTINO/CLIENTE
										AND B.CD_PROD	= @CD_PROD),0)

			--Analisando se o deposito e o auxiliar para tambem usar o mesmo valor
			IF @VLR_PROD=0
			begin
				set @VLR_PROD=ISNULL((SELECT 
								MIN(B.VLR_TRANSF)
							FROM 
								PRC_FILIAL_DEPOSITO_AUXILIAR D
								JOIN GLB_CONFIG_PRECO_RESSUP A ON 
								D.CD_CTR_DEPO =A.CD_CTR_DEPO
								JOIN GLB_CONFIG_PRECO_RESSUP_EST_PROD B ON 
								A.CD_CONFIG = B.CD_CONFIG
								JOIN GLB_CONFIG_PRECO_RESSUP_PRC_FILIAL C ON 
								A.CD_CONFIG = C.CD_CONFIG
							WHERE
								D.CD_EMP=@CD_EMP
								AND D.CD_FILIAL_DEPO_AUX = @CD_FILIAL--FILIAL DEPOSITO AUXILIAR
								AND C.CD_FILIAL = @CD_DEST-- FILIAL DESTINO/CLIENTE
								AND B.CD_PROD	= @CD_PROD),0)
			end
		end
	
		--CustoMedio
		IF isnull(@VLR_PROD,0)=0
		BEGIN 
			if @Step=-1 
				Goto CustoMedio;
			if @Step=0 
			  set @Step=-1
		end  

		--Custo 
		IF isnull(@VLR_PROD,0)=0
		BEGIN 
			if @Step=-1
				Goto Custo;
			if @Step=1
			  set @Step=-1
		end   

		--Compra
		IF isnull(@VLR_PROD,0)=0
		BEGIN 
			if @Step=-1
				Goto Compra;
			if @Step=2 
			  set @Step=-1
		end   
		
		--CustoGrupo 
		IF isnull(@VLR_PROD,0)=0
		BEGIN  
			if @Step=-1
				Goto CustoGrupo;
			if @Step=3
				set @Step=-1
		end   

		--CompraGrupo 
		IF isnull(@VLR_PROD,0)=0
		BEGIN  
			if @Step=-1
				Goto CompraGrupo;
			if @Step=4 
				set @Step=-1
		end   
		--Operador Logistico 
		IF isnull(@VLR_PROD,0)=0
		begin 
			if @Step=-1
				Goto OperadorLogistico;
			if @Step=8 
				set @Step=-1
		end   

		IF (@TIPO_PRECO in(14,15) or @VLR_PROD>0)
		begin
			goto Saida
		end
	end

	--14 Ressuprimento 
	IF @TIPO_PRECO= 17
	begin 
		--Deposito principal
		set @VLR_PROD=ISNULL((SELECT 
									MIN(B.VLR_CUSTO_IT)
								FROM 
									PRC_FILIAL_DEPOSITO D
									JOIN GLB_CONFIG_PRECO_RESSUP A ON 
									D.CD_CTR_DEPO =A.CD_CTR_DEPO
									JOIN GLB_CONFIG_PRECO_RESSUP_EST_PROD B ON 
									A.CD_CONFIG = B.CD_CONFIG
									JOIN GLB_CONFIG_PRECO_RESSUP_PRC_FILIAL C ON 
									A.CD_CONFIG = C.CD_CONFIG
								WHERE
									D.CD_EMP=@CD_EMP
									AND D.CD_FILIAL = @CD_FILIAL--Filial deposito principal
									AND C.CD_FILIAL = @CD_DEST	-- Filial destino/cliente
									AND B.CD_PROD	= @CD_PROD),0)

		--Analisando se o deposito e o auxiliar para tambem usar o mesmo valor
		IF @VLR_PROD=0
		begin
			set @VLR_PROD=ISNULL((SELECT 
							MIN(B.VLR_CUSTO_IT)
						FROM 
							PRC_FILIAL_DEPOSITO_AUXILIAR D
							JOIN GLB_CONFIG_PRECO_RESSUP A ON 
							D.CD_CTR_DEPO =A.CD_CTR_DEPO
							JOIN GLB_CONFIG_PRECO_RESSUP_EST_PROD B ON 
							A.CD_CONFIG = B.CD_CONFIG
							JOIN GLB_CONFIG_PRECO_RESSUP_PRC_FILIAL C ON 
							A.CD_CONFIG = C.CD_CONFIG
						WHERE
							D.CD_EMP=@CD_EMP
							AND D.CD_FILIAL_DEPO_AUX = @CD_FILIAL--FILIAL DEPOSITO AUXILIAR
							AND C.CD_FILIAL = @CD_DEST-- FILIAL DESTINO/CLIENTE
							AND B.CD_PROD	= @CD_PROD),0)
		end
	end


	--Gross up
	IF (@TIPO_PRECO =16 )
		begin 
			SET @VLR_PROD = dbo.UFD_BUSCA_PRECO_PRODUTO_GROSS_UP (@CD_EMP,@CD_FILIAL,@CD_PROD,@TIPO_PRECO,@TP_DEST,@CD_DEST,0);
						goto Saida
		end

		 

Saida:

	
	

	RETURN (ISNULL(@VLR_PROD,0))

end

 