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

CREATE FUNCTION UFD_EST_PROD_TBL_DESC_LEVE_PAGUE(@CD_EMP INT, @CD_FILIAL INT, @CD_PROD INT, @QTDE INT, @CD_FRM_PGTO INT , @TP_ORIG_VENDA INT )
RETURNS @RETURN_REC 
TABLE(
	CD_TBL_DESC 		INT,
	TP_DESC 			INT,
	QTDE_PAG    		INT,
	FLAG_DESC_FIX 		INT,
	PERC_DESC			MONEY,
	PERC_DESC_MAX		MONEY,
	TP_NIVEL			INT,
	AGREGACAO    		INT
)
AS 
BEGIN

-- VERSÃO 23/09/2020

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

	DECLARE @TRABALHACOMDESCONTOFORMADEPAGAMENTO INT = 0 

	DECLARE @RS_TMP_DESC TABLE(
		CD_TBL_DESC 		INT,
		TP_DESC 			INT,
		QTDE_PAG    		INT,
		TP_NIVEL			INT,
		AGREGACAO    		INT,
		DT_INI              DATETIME
	)

	-------------------------------------------------------------------------------------------------------------------------------    
	--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 


	-------------------------------------------------------------------------------------------------------------------------------    
	--EXISTE ARVORE MERCADOLOGICA    
	-------------------------------------------------------------------------------------------------------------------------------     
	IF EXISTS(SELECT 1 FROM @EST_PROD_ARV_MERC)
	BEGIN

		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_QTDE,    
					A.CD_TP_DESCONTO
			FROM EST_PROD_TBL_DESC_QTDE A INNER JOIN 
			     EST_PROD_TBL_DESC_QTDE_PRC_FILIAL FIL ON    
					A.CD_EMP = FIL.CD_EMP AND    
					A.CD_TBL_DESC_QTDE = FIL.CD_TBL_DESC_QTDE  
			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    
					24         = A.CD_TP_DESCONTO AND
					@QTDE      >= A.QTDE_LEVE

					and a.DT_INI <=dbo.F_HOJE()
					and a.DT_FIM >=dbo.F_HOJE()
               AND A.ORIG_VENDA IN ( 0 , @TP_ORIG_VENDA  )


			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_QTDE_FORMA_PGTO B  
									WHERE B.CD_EMP             = RS_TAB_ATIVA.CD_EMP
									  AND B.CD_TBL_DESC_QTDE   = RS_TAB_ATIVA.CD_TBL_DESC   
									  AND B.CD_FRM_PGTO        = @CD_FRM_PGTO  
										) ;
				
			END 

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

		IF EXISTS(SELECT 1 FROM @RS_TAB_ATIVA)
		BEGIN
			-------------------------------------------------------------------------------------------------------------------------------    
			--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_QTDE AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,  
						(ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_PAGUE) + (@QTDE - (ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_LEVE)) AS QTDE_PG, --CALCULA A QUANTIDADE DE PROTUDOS QUE SERAO PAGOS  
						1 AS TP_NIVEL,
						A.AGREGACAO,
					    A.DT_INI      
						FROM EST_PROD_TBL_DESC_QTDE A    
						INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_PROD B ON    
							A.CD_EMP = B.CD_EMP AND    
							A.CD_TBL_DESC_QTDE = B.CD_TBL_DESC_QTDE    
						INNER JOIN @RS_TAB_ATIVA TBA ON    
							A.CD_EMP = TBA.CD_EMP AND    
							A.CD_TBL_DESC_QTDE = 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_QTDE AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,  
						(ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_PAGUE) + (@QTDE - (ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_LEVE)) AS QTDE_PG, --CALCULA A QUANTIDADE DE PROTUDOS QUE SERAO PAGOS  
						2 AS TP_NIVEL,
						A.AGREGACAO,
					    A.DT_INI      
				FROM EST_PROD_TBL_DESC_QTDE A    
						INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_ARV_MERC_FAMILIA B ON    
							A.CD_EMP = B.CD_EMP AND    
							A.CD_TBL_DESC_QTDE = B.CD_TBL_DESC_QTDE
						INNER JOIN @RS_TAB_ATIVA TBA ON    
							A.CD_EMP = TBA.CD_EMP AND    
							A.CD_TBL_DESC_QTDE = 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_QTDE AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,  
						(ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_PAGUE) + (@QTDE - (ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_LEVE)) AS QTDE_PG, --CALCULA A QUANTIDADE DE PROTUDOS QUE SERAO PAGOS  
						3 AS TP_NIVEL,
						A.AGREGACAO,
					    A.DT_INI      
				FROM EST_PROD_TBL_DESC_QTDE A    
						INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_MC B ON    
							A.CD_EMP = B.CD_EMP AND    
							A.CD_TBL_DESC_QTDE = B.CD_TBL_DESC_QTDE    
						INNER JOIN @RS_TAB_ATIVA TBA ON    
							A.CD_EMP = TBA.CD_EMP AND    
							A.CD_TBL_DESC_QTDE = 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_QTDE AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,  
						(ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_PAGUE) + (@QTDE - (ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_LEVE)) AS QTDE_PG, --CALCULA A QUANTIDADE DE PROTUDOS QUE SERAO PAGOS  
						4 AS TP_NIVEL,
						A.AGREGACAO,
					    A.DT_INI       
				FROM EST_PROD_TBL_DESC_QTDE A    
						INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_PROD_FABRIC B ON    
							A.CD_EMP = B.CD_EMP AND    
							A.CD_TBL_DESC_QTDE = B.CD_TBL_DESC_QTDE 
						INNER JOIN @RS_TAB_ATIVA TBA ON    
							B.CD_EMP = TBA.CD_EMP AND    
							B.CD_TBL_DESC_QTDE = 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_QTDE AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,  
						(ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_PAGUE) + (@QTDE - (ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_LEVE)) AS QTDE_PG, --CALCULA A QUANTIDADE DE PROTUDOS QUE SERAO PAGOS  
						5 AS TP_NIVEL,
						A.AGREGACAO,
					    A.DT_INI    
				FROM EST_PROD_TBL_DESC_QTDE A    
						INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_ARV_MERC_CATEGORIA B ON    
							A.CD_EMP = B.CD_EMP AND    
							A.CD_TBL_DESC_QTDE = B.CD_TBL_DESC_QTDE 
						INNER JOIN @RS_TAB_ATIVA TBA ON    
							A.CD_EMP = TBA.CD_EMP AND    
							B.CD_TBL_DESC_QTDE = 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_QTDE AS CD_TBL_DESC,    
						A.CD_TP_DESCONTO AS TP_DESC,  
						(ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_PAGUE) + (@QTDE - (ABS(@QTDE / A.QTDE_LEVE) * A.QTDE_LEVE)) AS QTDE_PG, --CALCULA A QUANTIDADE DE PROTUDOS QUE SERAO PAGOS  
						6 AS TP_NIVEL,
						A.AGREGACAO,
					    A.DT_INI   
				FROM EST_PROD_TBL_DESC_QTDE A    
						INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_ARV_MERC_LINHA B ON    
							A.CD_EMP = B.CD_EMP AND    
							A.CD_TBL_DESC_QTDE = B.CD_TBL_DESC_QTDE    
						INNER JOIN @RS_TAB_ATIVA TBA ON    
							A.CD_EMP = TBA.CD_EMP AND    
							B.CD_TBL_DESC_QTDE = 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			
			     
		END
	END

	INSERT INTO @RETURN_REC --cd_tbl_desc_qtde, tp_desc, qtde_pag, 1, ROUND((1-(CAST(qtde_pag AS numeric)/int_cd_prod))*100,2), ROUND((1-(CAST(qtde_pag AS numeric)/int_cd_prod))*100,2), tp_nivel, agregacao
	SELECT TOP 
			1 CD_TBL_DESC, 
			TP_DESC, 
			QTDE_PAG, 
			0, 
			ROUND((1-(CAST(QTDE_PAG AS NUMERIC)/@QTDE))*100,2), 
			ROUND((1-(CAST(QTDE_PAG AS NUMERIC)/@QTDE))*100,2), 
			TP_NIVEL, 
			AGREGACAO
		  FROM @RS_TMP_DESC 
		 ORDER BY  TP_NIVEL ASC, QTDE_PAG DESC, DT_INI DESC

	RETURN
END