IF  EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_TBL_DESC_CONV]') 
            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_CONV]
GO

CREATE FUNCTION UFD_EST_PROD_TBL_DESC_CONV(@CD_EMP INT, @CD_FILIAL INT, @CD_PROD INT, @CD_CLI INT, @CD_CONV INT, @IS_RECEITA INT, @LIBERA_VD_CONV INT, @CD_FRM_PGTO INT , @TP_ORIG_VENDA INT , @USA_DESCONTO_PROPZ_IDENT INT = 0)
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,
	LIBERA_VD_CONV      INT,
	EXCLUE_OFERTA       INT,
	QT_MAX              INT
)
AS 
BEGIN

-- VERSÃO 23/09/2020


	-- SELECT * FROM UFD_EST_PROD_TBL_DESC_CONV(1, 2, 3, 101, 1 , 0)

	DECLARE @CD_PLANO_VENDA                   INT
	DECLARE @QTDE_PROD_MAX_CONV               INT
	DECLARE @QTDE_PROD_MAX_CONV_BY_PROD       INT
	DECLARE	@PROCEDURE_DESC_EXTRA             VARCHAR(250)
	DECLARE @SQL                              VARCHAR(MAX)
	DECLARE @TRABALHACOMDESCONTOFORMADEPAGAMENTO INT = 0 
	
	SET @CD_PLANO_VENDA                   = 0
	SET @QTDE_PROD_MAX_CONV               = -1
	SET @QTDE_PROD_MAX_CONV_BY_PROD       = -1
	SET	@PROCEDURE_DESC_EXTRA             = 'N'


	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,
		DT_INI              DATETIME,
		LIBERA_VD_CONV      INT,
		EXCLUE_OFERTA       INT,
		QT_MAX              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)    

	IF @CD_CONV > 0
	BEGIN
		-------------------------------------------------------------------------------------------------------------------------------    
		--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 


		-------------------------------------------------------------------------------------------------------------------------------    
		--EXISTE ARVORE MERCADOLOGICA    
		-------------------------------------------------------------------------------------------------------------------------------     
		IF EXISTS(SELECT 1 FROM @EST_PROD_ARV_MERC)
		BEGIN
			SET @CD_PLANO_VENDA = COALESCE( 
									(	SELECT COALESCE(RC_CLI_CONV_RC_CLI.CD_PLANO_VENDA,0)
										  FROM	RC_CLI_CONV_RC_CLI 
										 WHERE @CD_EMP  = RC_CLI_CONV_RC_CLI.CD_EMP 
										   AND @CD_CLI  = RC_CLI_CONV_RC_CLI.CD_CLI
										   AND @CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV 
									), 0)
									
									
			SET @TRABALHACOMDESCONTOFORMADEPAGAMENTO = ISNULL (( SELECT VALOR FROM PRC_FILIAL_CONFIG WHERE CD_CHAVE = 'USA_DESCONTO_PGTO' AND CD_FILIAL = @CD_FILIAL), 0)									

			INSERT INTO @RS_TAB_ATIVA    
				SELECT  A.CD_EMP,    
						A.CD_TBL_DESC,    
						A.CD_TP_DESCONTO
				FROM EST_PROD_TBL_DESC A INNER JOIN 
					 EST_PROD_TBL_DESC_PRC_FILIAL FIL ON    
						A.CD_EMP = FIL.CD_EMP AND    
						A.CD_TBL_DESC = FIL.CD_TBL_DESC INNER JOIN 
					RC_CLI_CONV_EST_PROD_TBL_DESC CONV ON     
						A.CD_EMP = CONV.CD_EMP AND 
						A.CD_TBL_DESC = CONV.CD_TBL_DESC AND 
						@CD_CONV = CONV.CD_CONV AND 
						@CD_PLANO_VENDA = CONV.CD_PLANO_VENDA
				WHERE	@CD_EMP    = A.CD_EMP AND        
						@CD_FILIAL = FIL.CD_FILIAL AND
						CONVERT(DATE,GETDATE()) BETWEEN A.DT_INI AND A.DT_FIM AND
						1          = A.STS_TBL_DESC AND    
						3          = A.CD_TP_DESCONTO AND
						0          = A.FLAG_DESC_RECEITA
						AND A.ORIG_VENDA IN ( 0 , @TP_ORIG_VENDA )
						AND	(
					      ((@USA_DESCONTO_PROPZ_IDENT = 1) AND (A.FLAG_TP_DESCONTO_PROPZ IN (0,1))) OR 
					      ((@USA_DESCONTO_PROPZ_IDENT = 0) AND (A.FLAG_TP_DESCONTO_PROPZ =0))
					    )

			IF @IS_RECEITA > 0
			BEGIN
				INSERT INTO @RS_TAB_ATIVA    
				SELECT  A.CD_EMP,    
						A.CD_TBL_DESC,    
						A.CD_TP_DESCONTO
				FROM EST_PROD_TBL_DESC A INNER JOIN 
					 EST_PROD_TBL_DESC_PRC_FILIAL FIL ON    
						A.CD_EMP = FIL.CD_EMP AND    
						A.CD_TBL_DESC = FIL.CD_TBL_DESC INNER JOIN 
					RC_CLI_CONV_EST_PROD_TBL_DESC CONV ON     
						A.CD_EMP = CONV.CD_EMP AND 
						A.CD_TBL_DESC = CONV.CD_TBL_DESC AND 
						@CD_CONV = CONV.CD_CONV AND 
						@CD_PLANO_VENDA = CONV.CD_PLANO_VENDA
				WHERE	@CD_EMP    = A.CD_EMP AND        
						@CD_FILIAL = FIL.CD_FILIAL AND
						CONVERT(DATE,GETDATE()) BETWEEN A.DT_INI AND A.DT_FIM AND
						1          = A.STS_TBL_DESC AND    
						3          = A.CD_TP_DESCONTO AND
						1          = A.FLAG_DESC_RECEITA
						AND A.ORIG_VENDA IN ( 0 , @TP_ORIG_VENDA )
						AND	(
					      ((@USA_DESCONTO_PROPZ_IDENT = 1) AND (A.FLAG_TP_DESCONTO_PROPZ IN (0,1))) OR  
					      ((@USA_DESCONTO_PROPZ_IDENT = 0) AND (A.FLAG_TP_DESCONTO_PROPZ =0))
					    )
				  
			END

			
			IF @TRABALHACOMDESCONTOFORMADEPAGAMENTO = 1  
			BEGIN   
				DELETE FROM RS_TAB_ATIVA    
				  from @RS_TAB_ATIVA RS_TAB_ATIVA
				 WHERE NOT EXISTS (SELECT B.CD_EMP
									 FROM EST_PROD_TBL_DESC_FORMA_PGTO B  
									WHERE B.CD_EMP        = RS_TAB_ATIVA.CD_EMP
									  AND B.CD_TBL_DESC   = RS_TAB_ATIVA.CD_TBL_DESC   
									  AND B.CD_FORMA_PGTO = @CD_FRM_PGTO  
										) ;
				
			END 




			-------------------------------------------------------------------------------------------------------------------------------    
			--EXISTE TABELA ATIVA  
			-------------------------------------------------------------------------------------------------------------------------------     

			IF EXISTS(SELECT 1 FROM @RS_TAB_ATIVA)
			BEGIN

				SET @QTDE_PROD_MAX_CONV = COALESCE((SELECT COALESCE(VALOR, -1) FROM PRC_EMP_CONFIG WHERE CD_EMP = 1 AND CD_CHAVE LIKE 'CONVENIO_LIMITE_PRODUTOS'), -1)
				SET @QTDE_PROD_MAX_CONV_BY_PROD = COALESCE((SELECT COALESCE(QTDE_PROD, -1) FROM RC_CLI_CONV_EST_PROD_CONV_LIMITE WHERE CD_EMP = 1 AND @CD_CONV = CD_CONV AND @CD_PROD = CD_PROD ),-1);

				IF @QTDE_PROD_MAX_CONV_BY_PROD > -1 
					SET @QTDE_PROD_MAX_CONV = @QTDE_PROD_MAX_CONV_BY_PROD

				
				-------------------------------------------------------------------------------------------------------------------------------    
				--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 1 - PRODUTOS    
				-------------------------------------------------------------------------------------------------------------------------------    
				INSERT INTO @RS_TMP_DESC     
					SELECT    
							A.CD_TBL_DESC AS CD_TBL_DESC,    
							A.CD_TP_DESCONTO AS TP_DESC,    
							A.FLAG_DESC_FIX,    
							B.PERC_DESC AS PERC_DESC,    
							B.PERC_DESC_MAX AS PERC_DESC_MAX,    
							1 AS TP_NIVEL,
							A.DT_INI,
							@LIBERA_VD_CONV,
							0,
							@QTDE_PROD_MAX_CONV      
					FROM EST_PROD_TBL_DESC A    
							INNER JOIN EST_PROD_TBL_DESC_EST_PROD B ON    
								A.CD_EMP = B.CD_EMP AND    
								A.CD_TBL_DESC = B.CD_TBL_DESC    
							INNER JOIN @RS_TAB_ATIVA TBA ON    
								A.CD_EMP = TBA.CD_EMP AND    
								A.CD_TBL_DESC = TBA.CD_TBL_DESC     
							INNER JOIN @EST_PROD_ARV_MERC ARV ON    
								B.CD_EMP = ARV.CD_EMP AND    
								B.CD_PROD = ARV.CD_PROD  
			
				-------------------------------------------------------------------------------------------------------------------------------    
				--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 2 - FAMILIAS    
				-------------------------------------------------------------------------------------------------------------------------------    				
				INSERT INTO @RS_TMP_DESC     
					SELECT    
							A.CD_TBL_DESC AS CD_TBL_DESC,    
							A.CD_TP_DESCONTO AS TP_DESC,    
							A.FLAG_DESC_FIX,    
							B.PERC_DESC AS PERC_DESC,    
							B.PERC_DESC_MAX AS PERC_DESC_MAX,    
							2 AS TP_NIVEL,
							A.DT_INI,
							@LIBERA_VD_CONV,
							0,
							@QTDE_PROD_MAX_CONV      
					FROM EST_PROD_TBL_DESC A    
							INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_FAMILIA B ON    
								A.CD_EMP = B.CD_EMP AND    
								A.CD_TBL_DESC = B.CD_TBL_DESC    
							INNER JOIN @RS_TAB_ATIVA TBA ON    
								A.CD_EMP = TBA.CD_EMP AND    
								A.CD_TBL_DESC = TBA.CD_TBL_DESC     
							INNER JOIN @EST_PROD_ARV_MERC ARV ON    
								B.CD_EMP = ARV.CD_EMP AND    
								B.CD_ARV_MERC_FAMILIA = ARV.CD_ARV_MERC_FAMILIA 
			
				-------------------------------------------------------------------------------------------------------------------------------        
				--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 3 - MARCAS    
				-------------------------------------------------------------------------------------------------------------------------------    				
				INSERT INTO @RS_TMP_DESC     
					SELECT    
							A.CD_TBL_DESC AS CD_TBL_DESC,    
							A.CD_TP_DESCONTO AS TP_DESC,    
							A.FLAG_DESC_FIX,    
							B.PERC_DESC AS PERC_DESC,    
							B.PERC_DESC_MAX AS PERC_DESC_MAX,    
							3 AS TP_NIVEL,
							A.DT_INI,
							@LIBERA_VD_CONV,
							0,
							@QTDE_PROD_MAX_CONV      
					FROM EST_PROD_TBL_DESC A    
							INNER JOIN EST_PROD_TBL_DESC_EST_MC B ON    
								A.CD_EMP = B.CD_EMP AND    
								A.CD_TBL_DESC = B.CD_TBL_DESC    
							INNER JOIN @RS_TAB_ATIVA TBA ON    
								A.CD_EMP = TBA.CD_EMP AND    
								A.CD_TBL_DESC = TBA.CD_TBL_DESC     
							INNER JOIN @EST_PROD_ARV_MERC ARV ON    
								B.CD_EMP = ARV.CD_EMP AND    
								B.CD_MC = ARV.CD_MC        

				-------------------------------------------------------------------------------------------------------------------------------    
				--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 4 - FABRICANTES    
				-------------------------------------------------------------------------------------------------------------------------------    
				INSERT INTO @RS_TMP_DESC     
					SELECT   
							A.CD_TBL_DESC AS CD_TBL_DESC,    
							A.CD_TP_DESCONTO AS TP_DESC,    
							A.FLAG_DESC_FIX,    
							B.PERC_DESC AS PERC_DESC,    
							B.PERC_DESC_MAX AS PERC_DESC_MAX,   
							4 AS TP_NIVEL,
							A.DT_INI,
							@LIBERA_VD_CONV,
							0,
							@QTDE_PROD_MAX_CONV       
					FROM EST_PROD_TBL_DESC A    
							INNER JOIN EST_PROD_TBL_DESC_EST_PROD_FABRIC B ON    
								A.CD_EMP = B.CD_EMP AND    
								A.CD_TBL_DESC = B.CD_TBL_DESC 
							INNER JOIN @RS_TAB_ATIVA TBA ON    
								A.CD_EMP = TBA.CD_EMP AND    
								A.CD_TBL_DESC = TBA.CD_TBL_DESC 
							INNER JOIN @EST_PROD_ARV_MERC ARV ON    
								B.CD_EMP = ARV.CD_EMP AND    
								B.CD_FABRIC = ARV.CD_FABRIC

				------------------------------------------------------------------------------------------------------------------------------    
				--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 5 - CATEGORIAS    
				------------------------------------------------------------------------------------------------------------------------------- 
                INSERT INTO @RS_TMP_DESC     
					SELECT   
							A.CD_TBL_DESC AS CD_TBL_DESC,    
							A.CD_TP_DESCONTO AS TP_DESC,    
							A.FLAG_DESC_FIX,    
							B.PERC_DESC AS PERC_DESC,    
							B.PERC_DESC_MAX AS PERC_DESC_MAX,   
							5 AS TP_NIVEL,
							A.DT_INI,
							@LIBERA_VD_CONV,
							0,
							@QTDE_PROD_MAX_CONV    
					FROM EST_PROD_TBL_DESC A    
							INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_CATEGORIA B ON    
								A.CD_EMP = B.CD_EMP AND    
								A.CD_TBL_DESC = B.CD_TBL_DESC 
							INNER JOIN @RS_TAB_ATIVA TBA ON    
								A.CD_EMP = TBA.CD_EMP AND    
								A.CD_TBL_DESC = TBA.CD_TBL_DESC   
							INNER JOIN @EST_PROD_ARV_MERC ARV ON    
								B.CD_EMP = ARV.CD_EMP AND    
								B.CD_ARV_MERC_CATEG = ARV.CD_ARV_MERC_CATEG 				
				
							
				-------------------------------------------------------------------------------------------------------------------------------    
				--CONSULTANDO AS TBLS DE DESCONTO PARA VERIFICAR SE HA ALGUM DESCONTO NO NIVEL 6 -  LINHAS   
				------------------------------------------------------------------------------------------------------------------------------- 
                INSERT INTO @RS_TMP_DESC     
					SELECT   
							A.CD_TBL_DESC AS CD_TBL_DESC,    
							A.CD_TP_DESCONTO AS TP_DESC,    
							A.FLAG_DESC_FIX,    
							B.PERC_DESC AS PERC_DESC,    
							B.PERC_DESC_MAX AS PERC_DESC_MAX,   
							6 AS TP_NIVEL,
							A.DT_INI,
							@LIBERA_VD_CONV,
							0,
							@QTDE_PROD_MAX_CONV   
					FROM EST_PROD_TBL_DESC A    
							INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_LINHA B ON    
								A.CD_EMP = B.CD_EMP AND    
								A.CD_TBL_DESC = B.CD_TBL_DESC    
							INNER JOIN @RS_TAB_ATIVA TBA ON    
								A.CD_EMP = TBA.CD_EMP AND    
								A.CD_TBL_DESC = TBA.CD_TBL_DESC 
							INNER JOIN @EST_PROD_ARV_MERC ARV ON    
								B.CD_EMP = ARV.CD_EMP AND       
								B.CD_ARV_MERC_LINHA = ARV.CD_ARV_MERC_LINHA				
				  
							
				
				SET @PROCEDURE_DESC_EXTRA =  COALESCE((
												SELECT 
														COALESCE(A.PROCEDURE_DESCONTO, 'N') AS PROCEDURE_DESCONTO
												FROM  	RC_CLI_CONV AS A
												WHERE  	A.CD_EMP = @CD_EMP AND 
														A.CD_CONV = @CD_CONV
											), 'N');

				IF @PROCEDURE_DESC_EXTRA <> 'N'
				BEGIN
					IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@PROCEDURE_DESC_EXTRA) AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
					BEGIN
						SET @SQL = 'INSERT INTO @RETURN_REC
										SELECT CD_TBL_DESC ,TP_DESC ,FLAG_DESC_FIX ,PERC_DESC ,PERC_DESC_MAX ,TP_NIVEL ,LIBERA_VD_CONV ,EXCLUE_OFERTA ,QT_MAX 
										FROM '+ @PROCEDURE_DESC_EXTRA +'(@CD_EMP, @CD_FILIAL, @CD_PROD, @CD_CLI, @CD_CONV,@CD_PLANO_VENDA, @IS_RECEITA, @LIBERA_VD_CONV, @QTDE_PROD_MAX_CONV)
								   '
						EXEC SP_EXECUTESQL @SQL

						IF EXISTS (SELECT * FROM @RETURN_REC)
							RETURN
					END
				END


			END
		END	

	END

	
	INSERT INTO @RETURN_REC
	SELECT TOP 1 CD_TBL_DESC ,TP_DESC ,FLAG_DESC_FIX ,PERC_DESC ,PERC_DESC_MAX ,TP_NIVEL ,LIBERA_VD_CONV ,EXCLUE_OFERTA ,QT_MAX
		  FROM @RS_TMP_DESC 
		 ORDER BY TP_NIVEL ASC, PERC_DESC DESC, DT_INI DESC

	RETURN
END