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

DROP FUNCTION [dbo].[UFD_EST_PROD_TBL_DESC_TELEVENDAS]
GO
/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 20221106
'Job.................:  
'Nome................: [UFD_EST_PROD_TBL_DESC_TELEVENDAS]
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: 
'Dt Ult Alteracão....: 2022-11-06
'Alterado por........: Marcos barros
=======================================================================================
Tag #I002 - Merge @TP_ORIG_VENDA e @USA_DESCONTO_PROPZ_IDENT

	-- SELECT * FROM UFD_EST_PROD_TBL_DESC_TELEVENDAS(1, 1, 1, 0)

*/
CREATE FUNCTION UFD_EST_PROD_TBL_DESC_TELEVENDAS(@CD_EMP INT, @CD_FILIAL INT, @CD_PROD INT, @IS_RECEITA 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
)
AS 
BEGIN

	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,
		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,    
					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  
			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    
					15         = 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  
			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    
					15         = 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
			-------------------------------------------------------------------------------------------------------------------------------    
			--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      
				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      
				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      
				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       
				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    
							B.CD_EMP = TBA.CD_EMP AND    
							B.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    
				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    
							B.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   
				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    
							B.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
							
			   
							
			 
						   
						
		END
	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  TP_NIVEL ASC, PERC_DESC DESC, DT_INI DESC 

	RETURN
END