 /****** OBJECT:  USERDEFINEDFUNCTION [DBO].[UFD_BUSCA_PRECO_PRODUTO]    SCRIPT DATE: 04/29/2014 23:12:42 ******/
IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UFD_BUSCA_PRECO_PRODUTO_GROSS_UP]') 
			AND TYPE IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [DBO].[UFD_BUSCA_PRECO_PRODUTO_GROSS_UP]
GO

CREATE FUNCTION [DBO].[UFD_BUSCA_PRECO_PRODUTO_GROSS_UP](@CD_EMP	 INT
													,@CD_FILIAL  INT
													,@CD_PROD	 INT
													,@TIPO_PRECO INT=0
													,@TP_DEST	INT=0
													,@CD_DEST		INT=0 
													,@VLR_PROD_PAD MONEY=0) 
RETURNS MONEY WITH ENCRYPTION AS
BEGIN

	DECLARE @UF_DEST VARCHAR(2)
	DECLARE @UF_ORIG VARCHAR(2)
	DECLARE @ALIQ_OP MONEY
	DECLARE @VLR_PROD MONEY
	DECLARE @CD_DEST_TRIB INT, @CD_MOV_OP INT

	-- @TP_DEST
	-- 0 - CLIENTE
	-- 1 - FORNECEDOR 
	-- 2 - FILIAL
			 
	DECLARE @CD_ARV_MERC_LINHA	INT =0;
	DECLARE @TP_PRC_GROS		INT =4;
	DECLARE @PERC_ACRES			MONEY =0;
	DECLARE @ST					INT =0 ; 
	DECLARE @TP_AGREGA			MONEY=0;
	DECLARE @PERCPIS			MONEY=0; 
	DECLARE @PERCCOFINS			MONEY=0;
	DECLARE @PERC_RED			MONEY=0;
	DECLARE @PERC_ALIQ_FECOP	MONEY=0;		
	DECLARE @TP_MOV INT
	DECLARE @TP_TRANSA INT 		

	--DECLARE @CD_CST_PIS INT =0;
	--DECLARE @CD_CST_COFINS INT =0;
			 
/*
	IF EXISTS( SELECT TOP 1 CD_EMP 
					FROM EST_PROD
					WHERE
							CD_EMP = @CD_EMP 
							AND CD_PROD  = @CD_PROD
							AND PIS_COFINS = 1 )
	BEGIN  

		SET @PERCPIS  = ISNULL((SELECT TOP 1 PERC_IMP FROM GLB_IMPOSTOS WHERE TIPO_IMPOSTO = 1),0);
		SET @PERCCOFINS  = ISNULL((SELECT TOP 1 PERC_IMP FROM GLB_IMPOSTOS WHERE TIPO_IMPOSTO = 2),0);

		SET @PERCPIS  =@PERCPIS+@PERCCOFINS
	END
	--TRIBUTACAO INDIVIDUAL PIS COFINS
	IF EXISTS( SELECT TOP 1 CD_EMP 
					FROM EST_PROD
					WHERE
							CD_EMP = @CD_EMP 
							AND CD_PROD  = @CD_PROD 
							AND FLAG_TRIB_PIS_COFINS_PROD = 1)
	BEGIN 
				 
		SELECT 
			@CD_CST_PIS		= CD_CST_PIS_SAIDA,
			@CD_CST_COFINS  = CD_CST_COFINS_SAIDA
		FROM
			EST_PROD 
		WHERE
			CD_EMP = @CD_EMP 
			AND CD_PROD  = @CD_PROD 
			AND FLAG_TRIB_PIS_COFINS_PROD = 1;

		-- OPERAÇÃO TRIBUTÁVEL A ALÍQUOTA ZERO
		IF 	 @CD_CST_PIS=6
			SET @PERCPIS=0; 
		IF 	 @CD_CST_PIS=6
			SET @PERCCOFINS= 0;

		SET @PERCPIS  =@PERCPIS+@PERCCOFINS;

	END
*/			
	SET @UF_ORIG = (SELECT UF  FROM 
							GLB_CID C
							INNER JOIN PRC_FILIAL A ON 
							C.CD_CID = A.CD_CID
						WHERE
							A.CD_EMP = @CD_EMP 
							AND A.CD_FILIAL = @CD_FILIAL);

	IF @TP_DEST = 0
	BEGIN
		SET @UF_DEST = (SELECT UF  FROM 
							GLB_CID C
							INNER JOIN RC_CLI A ON 
							C.CD_CID = A.CD_CID
						WHERE
							A.CD_EMP = @CD_EMP 
							AND A.CD_CLI = @CD_DEST);
				
		SET @TP_MOV = 0
		SET @TP_TRANSA = 1
	END

	IF @TP_DEST = 1
	BEGIN
		SET @UF_DEST = (SELECT UF  FROM 
							GLB_CID C
							INNER JOIN PG_FORN A ON 
							C.CD_CID = A.CD_CID
						WHERE
							A.CD_EMP = @CD_EMP 
							AND A.CD_FORN = @CD_DEST);
									
		SET @TP_MOV = 0
		SET @TP_TRANSA = 1
	END

	IF @TP_DEST = 2
	BEGIN
		SET @UF_DEST = (SELECT UF  FROM 
							GLB_CID C
							INNER JOIN PRC_FILIAL A ON 
							C.CD_CID = A.CD_CID
						WHERE
							A.CD_EMP = @CD_EMP 
							AND A.CD_FILIAL = @CD_DEST);
									
		--VERIFICANDO SE O DESTINO POSSUI O MESMO GRUPO ECONOMICO DA ORIGEM			
		IF ISNULL((SELECT CD_GRP_ECON FROM PRC_GRP_ECON_PRC_FILIAL WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL),0) 
			= ISNULL((SELECT CD_GRP_ECON FROM PRC_GRP_ECON_PRC_FILIAL WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_DEST),0) 
			BEGIN
				SET @TP_MOV = 10
				SET @TP_TRANSA = 1										
			END
		ELSE	
			BEGIN
				SET @TP_MOV = 0
				SET @TP_TRANSA = 1										
			END 
			
	END
			

	IF @CD_DEST = 0 AND @TP_DEST = 2
	BEGIN 
		SET @TP_MOV=10
	END 
		
		
	IF @TP_MOV = 10
		BEGIN
			SET @PERCPIS = 0
			SET @PERCCOFINS = 0
		END


		
	IF EXISTS(SELECT CD_EMP 
				FROM PRC_EMP_CONFIG 
				WHERE CD_EMP = @CD_EMP 
					AND CD_CHAVE = 'FLAG_CONS_TRIB_GRUPO' 
					AND VALOR = '1')
		OR EXISTS(SELECT CD_EMP 
					FROM PRC_FILIAL_CONFIG 
					WHERE CD_EMP = @CD_EMP 
						AND CD_FILIAL = @CD_FILIAL 
						AND CD_CHAVE = 'FLAG_CONS_TRIB_GRUPO' 
						AND VALOR = '1')
		BEGIN 	
			IF @TP_DEST = 2 --SE FOR FILIAL UTILIZA NATUREZA DE TRANSFERENCIA, CASO CONTRÁRIO UTILIZA VENDA CONFORME ESTA NA TRIBUTAÇÃO ANTIGA
				BEGIN 
					--SE TP = VENDA E FOR DEST FILIAL BUSCA O CD_CLI DA FILIAL
					SET @CD_DEST_TRIB=@CD_DEST 
					IF @TP_MOV = 0
					BEGIN 
						--SE O DEST. DA NATUREZA FOR CLIENTE....
						IF ISNULL((SELECT TOP 1 DEST_ORIG FROM GLB_MOV_OP
										WHERE TP_MOV=@TP_MOV AND TP_TRANSA=@TP_TRANSA),0) = 0
						BEGIN
							SELECT
								@CD_DEST_TRIB=CD_CLI
							FROM PRC_FILIAL_RC_CLI
							WHERE
								CD_FILIAL= @CD_DEST
								AND CD_EMP=@CD_EMP;
						END
					END

					
					SELECT TOP 1
						@CD_MOV_OP   = CD_MOV_OP 
					FROM 
						GLB_MOV_OP
					WHERE 
						TP_MOV = @TP_MOV
						AND TP_MOV_OP_TRANSA = @TP_TRANSA
						AND DEST_ORIG = @TP_DEST
						AND STS_MOV =0
						AND TP_PRECO=4--CUSTOMEDIO;
						 
					SELECT  						
						@PERC_RED 	= CONVERT(MONEY,(CASE WHEN A.TP_RED_BASE_IMP IN (0,2) 
											AND A.PERC_RED> 0 THEN (A.PERC_RED / 100.0000) ELSE 1 END)),
						@ALIQ_OP 	= A.PERC_ICMS,
						@ST			= A.SUBS_TRIB, 
						@PERCPIS		= PERC_PIS,		
						@PERCCOFINS		= PERC_COFINS
					   ,@PERC_ALIQ_FECOP=PERC_ALIQ_FECOP
					FROM 
						UFD_EST_PROD_TRIB(@CD_EMP, @CD_FILIAL, @CD_MOV_OP, @CD_DEST_TRIB, @CD_PROD, 0, @TP_MOV, @TP_TRANSA,0,0,0,0,0,0,0,0) A
				END 
			ELSE	
				BEGIN 
						
					SELECT TOP 1 
						@CD_MOV_OP   = CD_MOV_OP 
					FROM 
						GLB_MOV_OP
					WHERE 
						TP_MOV = @TP_MOV
						AND TP_MOV_OP_TRANSA = @TP_TRANSA
						AND DEST_ORIG = @TP_DEST
						AND STS_MOV =0
						AND TP_PRECO=4--CUSTOMEDIO;

					SELECT  						
						@PERC_RED 	= CONVERT(MONEY,(CASE WHEN A.TP_RED_BASE_IMP IN (0,2) 
										AND A.PERC_RED> 0 THEN (A.PERC_RED / 100.0000) ELSE 1 END)),
						@ALIQ_OP 	= A.PERC_ICMS,
						@ST			= A.SUBS_TRIB, 
						@PERCPIS		= PERC_PIS,		
						@PERCCOFINS		= PERC_COFINS
					   ,@PERC_ALIQ_FECOP=PERC_ALIQ_FECOP	
					FROM 
						UFD_EST_PROD_TRIB(@CD_EMP, @CD_FILIAL, @CD_MOV_OP, @CD_DEST, @CD_PROD, 0, @TP_MOV, @TP_TRANSA,0,0,0,0,0,0,0,0) A
				END
		END
	ELSE 
		BEGIN
			IF @UF_DEST<> @UF_ORIG
				BEGIN 
					SELECT  
						@PERC_RED 	= CONVERT(MONEY,(CASE WHEN A.TP_RED_BASE_IMP IN (0,2) 
										AND A.PERC_RED> 0 THEN (A.PERC_RED / 100.0000) ELSE 1 END)),
						@ALIQ_OP	= A.PERC_ICMS,
						@ST			= T.SUBS_TRIB 
					FROM 
						EST_PROD_CPL_TRIB_INTER A
						INNER JOIN EST_TRIB T ON 
						A.CD_TRIB =T.CD_TRIB
					WHERE
						CD_FILIAL = @CD_FILIAL
						AND A.CD_EMP =@CD_EMP
						AND A.CD_PROD = @CD_PROD
						AND A.TP_MOV= @TP_MOV
						AND A.TP_TRANSA = @TP_TRANSA 
						AND A.UF = @UF_DEST 
				END
			ELSE
				BEGIN
					SELECT 
						@PERC_RED 	= CONVERT(MONEY,(CASE WHEN A.TP_RED_BASE_IMP IN (0,2)
															AND A.PERC_RED> 0 THEN (A.PERC_RED / 100.0000) ELSE 1 END)),
						@ALIQ_OP	= A.PERC_ICMS,
						@ST			= T.SUBS_TRIB 
					FROM 
						EST_PROD_CPL_TRIB_EST A
						INNER JOIN EST_TRIB T ON 
						A.CD_TRIB =T.CD_TRIB
					WHERE
						A.CD_FILIAL = @CD_FILIAL
						AND A.CD_EMP =@CD_EMP
						AND A.CD_PROD = @CD_PROD
						AND A.TP_MOV= @TP_MOV
						AND A.TP_TRANSA = @TP_TRANSA 
				END
		END
				
	/*
	0 - PREÇO DE VENDAS
	1 - PREÇO DE CUSTO
	2 - PREÇO DE COMPRA
	3 - PREÇO DE FABRICA
	4 - PREÇO DE CUSTO MÉDIO
	5 - PREÇO DE COMPRA GRUPO
	*/ 
			
	SET @CD_ARV_MERC_LINHA = ISNULL((SELECT TOP 1  CD_ARV_MERC_LINHA FROM EST_PROD_EST_ARV_MERCADOLOGICA 
			WHERE CD_EMP=@CD_EMP AND CD_PROD=@CD_PROD),0);

	SELECT 
		@TP_PRC_GROS	= TP_PRECO,	
		@PERC_ACRES		= CASE WHEN @ST =1 THEN PERC_ST ELSE PERC_SEM_ST END,
		@TP_AGREGA		= TIPO
	FROM GLB_CONFIG_PRECO_OPERADOR_LOG
	WHERE
		CD_EMP=@CD_EMP 
		AND CD_CTR_DEPO= (SELECT CD_CTR_DEPO FROM PRC_FILIAL_DEPOSITO WHERE CD_EMP=@CD_EMP AND CD_FILIAL=@CD_FILIAL)
		AND CD_ARV_MERC_LINHA= @CD_ARV_MERC_LINHA; 
				 
	SET @TP_PRC_GROS = ISNULL(@TP_PRC_GROS,4);
	SET @PERC_ACRES	 = ISNULL(@PERC_ACRES,0);
	SET @TP_AGREGA	= ISNULL(@TP_AGREGA,0);
	
	SET @VLR_PROD=@VLR_PROD_PAD;
	
	IF @VLR_PROD =0
	BEGIN
		-- 0 - PREÇO DE VENDAS
		IF @TP_PRC_GROS =0 
			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);  
		-- 1 - PREÇO DE CUSTO
		IF @TP_PRC_GROS =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); 

		-- 2 - PREÇO DE COMPRA
		IF @TP_PRC_GROS =2 
			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); 

		-- 3 - PREÇO DE FABRICA
		IF @TP_PRC_GROS =3
		BEGIN 
			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 = ISNULL((SELECT CD_CTR_GRP FROM EST_PROD_PRECO_TBL_GRUPO_PRC_FILIAL
								WHERE  CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL),0) ),0)
		END
		-- 4 - PREÇO DE CUSTO MÉDIO
		IF @TP_PRC_GROS =4 
			SET @VLR_PROD =	ISNULL(DBO.F_VLR_CUSTO_EST_FECHA_PROC_MOV (@CD_EMP,@CD_FILIAL,@CD_PROD,GETDATE(),0),0)   
			 
		-- 5 - PREÇO DE COMPRA GRUPO
		IF @TP_PRC_GROS =5 
			SET @VLR_PROD =	 ISNULL((SELECT TOP 1 VLR_CUSTO_GRP FROM  EST_PROD  
									WHERE   CD_EMP		= @CD_EMP 
										AND CD_PROD		= @CD_PROD),0);
	END
			 
	/*
	--ISNULL(DBO.F_VLR_CUSTO_EST_FECHA_PROC_MOV (@CD_EMP,@CD_FILIAL,@CD_PROD,GETDATE(),0),0)   
	IF @PERC_MARGEM > 0 
		SET @VLR_PROD =@VLR_PROD+(@VLR_PROD * (@PERC_MARGEM/100.00));
	*/

	IF @TP_AGREGA = 0 
		SET	@PERC_ACRES = (100.000 + @PERC_ACRES)/100.0000
	ELSE
		SET	@PERC_ACRES = (100.000 - @PERC_ACRES)/100.0000
			
	SET @VLR_PROD =@VLR_PROD* @PERC_ACRES;

	--SET @ALIQ_OP=@ALIQ_OP;
			
	IF ISNULL(@PERC_RED,0) = 0
		BEGIN
			SET @PERC_RED = 1
		END 
						
	IF @ALIQ_OP + @PERCPIS + @PERCCOFINS + @PERC_ALIQ_FECOP > 0 
		BEGIN 
			--SET @VLR_PROD = (@VLR_PROD * @PERC_RED) / (1-@ALIQ_OP/100.0000); 
			--NOVA FORMULA REPASSADA

			DECLARE @DATADEDUZIR DATE = NULL
			DECLARE @DEDUZIR_ICMS_NORMAL_BASE_PISCOFINS INT = NULL 

			SET @DATADEDUZIR = ISNULL((SELECT DBO.UFD_BUSCA_PARAM_SISTEMA(1, 'DT_INICIO_EXCLUSAO_ICMS_PIS_COFINS', 1, @CD_FILIAL)),0)

			 SELECT @DEDUZIR_ICMS_NORMAL_BASE_PISCOFINS = ISNULL(DEDUZIR_ICMS_NORMAL_BASE_PISCOFINS,0)
			 FROM PRC_FILIAL 
			 WHERE CD_FILIAL = @CD_FILIAL

			IF @DEDUZIR_ICMS_NORMAL_BASE_PISCOFINS = 1 AND GETDATE() >= @DATADEDUZIR /*FARMAGOI-45456*/
			BEGIN
				SET @VLR_PROD = @VLR_PROD / (1-((@PERC_ALIQ_FECOP/100.0000) + ((@ALIQ_OP* @PERC_RED)/100.0000) + ((1-((@ALIQ_OP* @PERC_RED)/100.0000))*((@PERCPIS/100.0000)+(@PERCCOFINS/100.0000)))))
			END
			ELSE
			BEGIN
				SET @VLR_PROD = @VLR_PROD / (1-(((@ALIQ_OP* @PERC_RED)+(@PERCPIS+@PERCCOFINS+@PERC_ALIQ_FECOP))/100.0000)); 
			END
		END 
	
		  
	--SE O VLR DO PRODUTO FICAR MENOR QUE ZERO,
	IF @VLR_PROD < 0 
		SET @VLR_PROD = 0

	
	RETURN (ISNULL(@VLR_PROD,0))
			
END
