IF  EXISTS (SELECT 1 FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_TBL_DESC_ANIVERSARIO]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[UFD_EST_PROD_TBL_DESC_ANIVERSARIO]
GO
/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 20221106
'Job.................:  
'Nome................: UFD_EST_PROD_TBL_DESC_ANIVERSARIO
'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
*/

CREATE FUNCTION UFD_EST_PROD_TBL_DESC_ANIVERSARIO(@CD_EMP INT, @CD_FILIAL INT, @CD_PROD INT, @CD_CLI 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

-- VERSÃO 23/09/2020

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

	DECLARE @MES INT
	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 @MES = COALESCE((SELECT COALESCE(DATEPART( MONTH, DT_NASC),0) FROM RC_CLI WHERE CD_EMP = @CD_EMP AND CD_CLI = @CD_CLI) ,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_EST_PROD_DESC_ANIVER ANIVER ON    
					A.CD_EMP =  ANIVER.CD_EMP AND    
					A.CD_TBL_DESC = ANIVER.CD_TBL_DESC   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
					@MES       = ANIVER.MES_ANIVER AND
					1          = A.STS_TBL_DESC AND    
					11         = 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 = 1)) OR 
					  ((@USA_DESCONTO_PROPZ_IDENT = 0) AND (A.FLAG_TP_DESCONTO_PROPZ <> 1))
					)
																			   
																			
	  


		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_EST_PROD_DESC_ANIVER ANIVER ON    
					A.CD_EMP =  ANIVER.CD_EMP AND    
					A.CD_TBL_DESC = ANIVER.CD_TBL_DESC   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
					@MES       = ANIVER.MES_ANIVER AND
					1          = A.STS_TBL_DESC AND    
					11         = 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 = 1)) OR 
					  ((@USA_DESCONTO_PROPZ_IDENT = 0) AND (A.FLAG_TP_DESCONTO_PROPZ <> 1))
					)															   
																			
	  
		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