IF  EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_TBL_DESC_PRECO_FABRICA]') 
            AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

/****** Object:  UserDefinedFunction [dbo].[UFD_DESCRICAO_DESC]    Script Date: 14/08/2013 16:42:21 ******/
DROP FUNCTION [dbo].[UFD_EST_PROD_TBL_DESC_PRECO_FABRICA]
GO

CREATE FUNCTION UFD_EST_PROD_TBL_DESC_PRECO_FABRICA(@CD_EMP INT, @CD_FILIAL INT, @CD_CLI INT, @CD_PROD INT, @CD_FRM_PGTO INT)
RETURNS @RETURN_REC 
TABLE(
	CD_TBL_DESC 		INT,
	TP_DESC 			INT,
	FLAG_DESC_FIX 		INT,
	PERC_DESC			MONEY,
	PERC_DESC_MAX		MONEY,
	TP_NIVEL			INT
)
AS 
BEGIN

-- VERSÃO 23/09/2020

	-- SELECT * FROM UFD_EST_PROD_TBL_DESC_PRECO_FABRICA(1, 1, 1, 1)

	
	DECLARE @TRABALHACOMDESCONTOFORMADEPAGAMENTO INT = 0 
	
	DECLARE @RS_TMP_DESC TABLE(
		CD_TBL_DESC 		INT,
		TP_DESC 			INT,
		FLAG_DESC_FIX 		INT,
		PERC_DESC			MONEY,
		PERC_DESC_MAX		MONEY,
		TP_NIVEL			INT
	)

	-------------------------------------------------------------------------------------------------------------------------------    
	--DECLARANDO TABELA DE ARVORE MERCADOLOGICA DO PRODUTO    
	-------------------------------------------------------------------------------------------------------------------------------    
	DECLARE @RS_TAB_ATIVA TABLE ( CD_EMP INT,    
								  CD_TBL_DESC INT,    
								  TP_DESC INT)    

	-------------------------------------------------------------------------------------------------------------------------------    
	--DECLARANDO TABELA DE ARVORE MERCADOLOGICA DO PRODUTO    
	-------------------------------------------------------------------------------------------------------------------------------    
	DECLARE @EST_PROD_ARV_MERC TABLE (  CD_EMP     INT,    
										CD_PROD     INT,    
										CD_ARV_MERC_CATEG  INT,    
										CD_ARV_MERC_LINHA  INT,    
										CD_MC     INT,    
										CD_ARV_MERC_FAMILIA  INT,            
										CD_FABRIC    INT)    


	-------------------------------------------------------------------------------------------------------------------------------    
	--BUSCANDO ARVORE MERC. DO PRODUTO    
	-------------------------------------------------------------------------------------------------------------------------------     
	INSERT INTO @EST_PROD_ARV_MERC    			
		SELECT     
			ARV.CD_EMP     ,    
			ARV.CD_PROD     ,    
			ARV.CD_ARV_MERC_CATEG  ,    
			ARV.CD_ARV_MERC_LINHA  ,    
			ARV.CD_MC     ,    
			ARV.CD_ARV_MERC_FAMILIA  ,            
			P.CD_FABRIC     
		FROM EST_PROD_EST_ARV_MERCADOLOGICA ARV 
				INNER JOIN EST_PROD P ON    
					ARV.CD_EMP  = P.CD_EMP    
					AND ARV.CD_PROD = P.CD_PROD    
		WHERE   P.CD_EMP = @CD_EMP     
				AND P.CD_PROD = @CD_PROD 
	
	SET @TRABALHACOMDESCONTOFORMADEPAGAMENTO = ISNULL (( SELECT VALOR FROM PRC_FILIAL_CONFIG WHERE CD_CHAVE = 'USA_DESCONTO_PGTO' AND CD_FILIAL = @CD_FILIAL), 0)
	-------------------------------------------------------------------------------------------------------------------------------    
	--EXISTE ARVORE MERCADOLOGICA    
	-------------------------------------------------------------------------------------------------------------------------------     
	IF EXISTS(SELECT 1 FROM @EST_PROD_ARV_MERC)
	BEGIN

		INSERT INTO @RS_TMP_DESC     
		SELECT 0, 13, 1, ROUND( CAST(( 1 - ( PRECO_TBL.VLR_FORN / PRECO.VLR_TABELA ))  * 100 AS NUMERIC), 2), ROUND( CAST(( 1 - ( PRECO_TBL.VLR_FORN / PRECO.VLR_TABELA ) ) * 100 AS NUMERIC),2), 1 
			FROM RC_CLI_EST_PROD CLI INNER JOIN 
					V_EST_PROD_PRECO_TBL PRECO_TBL ON 
						CLI.CD_EMP = PRECO_TBL.CD_EMP AND 
						CLI.CD_PROD = PRECO_TBL.CD_PROD INNER JOIN 
					EST_PROD_PRECO PRECO ON 
						CLI.CD_EMP = PRECO.CD_EMP AND 
						CLI.CD_PROD = PRECO.CD_PROD
			WHERE 	@CD_EMP    = PRECO.CD_EMP AND
					@CD_FILIAL = PRECO.CD_FILIAL AND
					@CD_CLI    = CLI.CD_CLI AND
					@CD_PROD   = PRECO.CD_PROD
	END
	
	
		IF @TRABALHACOMDESCONTOFORMADEPAGAMENTO = 1  
			BEGIN   
				DELETE FROM RS_TMP_DESC    
				  from @RS_TMP_DESC RS_TMP_DESC
				 WHERE NOT EXISTS (SELECT B.CD_EMP
									 FROM EST_PROD_TBL_DESC_FORMA_PGTO B  
									WHERE B.CD_TBL_DESC   = RS_TMP_DESC.CD_TBL_DESC   
									  AND B.CD_FORMA_PGTO = @CD_FRM_PGTO  
										) ;
				
			END 

	

	INSERT INTO @RETURN_REC
	SELECT TOP 1 CD_TBL_DESC, TP_DESC, FLAG_DESC_FIX, PERC_DESC, PERC_DESC_MAX, TP_NIVEL 
		  FROM @RS_TMP_DESC 
		 ORDER BY PERC_DESC DESC

	RETURN
END