IF  EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_TBL_DESC_CARGO]') 
            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_CARGO]
GO
-- SELECT * FROM UFD_EST_PROD_TBL_DESC_CARGO(1, 7, 1,1) 
CREATE FUNCTION UFD_EST_PROD_TBL_DESC_CARGO (@CD_EMP INT, @CD_FILIAL INT, @CD_PROD INT, @CD_VEND INT, @CD_FRM_PGTO INT)
RETURNS  @RETURN_REC 
TABLE(
	CD_TBL_DESC 		INT,
	PERC_DESC			MONEY,
	PERC_DESC_MAX		MONEY,
	TP_NIVEL			INT

)
AS 
BEGIN

-- VERSÃO 23/09/2020
	
	DECLARE @RS_TMP_DESC TABLE( CD_TBL_DESC 		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_CARGO 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)    

	-------------------------------------------------------------------------------------------------------------------------------    
    --Busca o cargo do vendedor
    ------------------------------------------------------------------------------------------------------------------------------- 
	DECLARE @CD_CARGO INT
	SET @CD_CARGO =ISNULL((SELECT CD_CARGO FROM FP_FUNC WHERE CD_EMP=@CD_EMP AND CD_FUN = @CD_VEND),0);
	
	IF @CD_CARGO=0 
	   RETURN;
    
	DECLARE @TRABALHACOMDESCONTOFORMADEPAGAMENTO INT = 0 
		SET @TRABALHACOMDESCONTOFORMADEPAGAMENTO = ISNULL (( SELECT VALOR FROM PRC_FILIAL_CONFIG WHERE CD_CHAVE = 'USA_DESCONTO_PGTO' AND CD_FILIAL = @CD_FILIAL), 0)
	-------------------------------------------------------------------------------------------------------------------------------    
    --EXISTE TABELA ATIVA  
    ------------------------------------------------------------------------------------------------------------------------------- 
    INSERT INTO @RS_TAB_ATIVA    
    SELECT  CARGO.CD_EMP,
		  CARGO.CD_TBL_DESC_CARGO			 
    FROM 
		   
	   EST_PROD_TBL_DESC_CARGO CARGO
	   INNER JOIN EST_PROD_TBL_DESC_CARGO_PRC_FILIAL FIL ON    
	   CARGO.CD_EMP = FIL.CD_EMP AND    
	   CARGO.CD_TBL_DESC_CARGO = FIL.CD_TBL_DESC_CARGO  		  
	   join FP_CARGO_EST_PROD_TBL_DESC_CARGO fp on 
	   CARGO.CD_EMP			 = fp.CD_EMP AND    
	   CARGO.CD_TBL_DESC_CARGO = fp.CD_TBL_DESC_CARGO  		
    WHERE	
	   CARGO.CD_EMP=@CD_EMP
	   and fil.CD_FILIAL=@CD_FILIAL
	   and 1	 = CARGO.STS_TBL
	   and fp.CD_CARGO=@CD_CARGO;
	
	
			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_CARGO   
									  AND B.CD_FORMA_PGTO = @CD_FRM_PGTO  
										) ;
				
			END 
	
   
    IF NOT EXISTS(SELECT 1 FROM @RS_TAB_ATIVA)
	   RETURN;

	-------------------------------------------------------------------------------------------------------------------------------    
	--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 = 1     
				AND P.CD_PROD = @CD_PROD

				    
	IF NOT EXISTS(SELECT 1 FROM @EST_PROD_ARV_MERC)
	   RETURN; 

    -------------------------------------------------------------------------------------------------------------------------------    
    --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_CARGO AS CD_TBL_DESC,        
	   B.PERC_DESC AS PERC_DESC,    
	   B.PERC_DESC AS PERC_DESC_MAX,    
	   1 AS TP_NIVEL,
	   A.DT_INI      
    FROM 
	   EST_PROD_TBL_DESC_CARGO A    
	   INNER JOIN EST_PROD_TBL_DESC_CARGO_EST_PROD B ON    
	   A.CD_EMP = B.CD_EMP AND    
	   A.CD_TBL_DESC_CARGO = B.CD_TBL_DESC_CARGO     
	    
	   INNER JOIN @RS_TAB_ATIVA TBA ON    
	   A.CD_EMP = TBA.CD_EMP AND    
	   A.CD_TBL_DESC_CARGO = TBA.CD_TBL_DESC_CARGO     
	   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_CARGO AS CD_TBL_DESC,        
	   B.PERC_DESC AS PERC_DESC,    
	   B.PERC_DESC AS PERC_DESC_MAX,    
	   2 AS TP_NIVEL,
	   A.DT_INI      
    FROM 
	   EST_PROD_TBL_DESC_CARGO A    
	   INNER JOIN EST_PROD_TBL_DESC_CARGO_EST_ARV_MERC_FAMILIA B ON    
		  A.CD_EMP = B.CD_EMP AND    
		  A.CD_TBL_DESC_CARGO = B.CD_TBL_DESC_CARGO 
		     
	   INNER JOIN @RS_TAB_ATIVA TBA ON    
		  A.CD_EMP = TBA.CD_EMP AND    
		  A.CD_TBL_DESC_CARGO = TBA.CD_TBL_DESC_CARGO
	   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_CARGO AS CD_TBL_DESC,         
	   B.PERC_DESC AS PERC_DESC,    
	   B.PERC_DESC AS PERC_DESC_MAX,    
	   3 AS TP_NIVEL,
	   A.DT_INI      
    FROM 
	   EST_PROD_TBL_DESC_CARGO A    
	   INNER JOIN EST_PROD_TBL_DESC_CARGO_EST_MC B ON    
	   A.CD_EMP = B.CD_EMP AND    
	   A.CD_TBL_DESC_CARGO = B.CD_TBL_DESC_CARGO    

	   INNER JOIN @RS_TAB_ATIVA TBA ON    
	   A.CD_EMP = TBA.CD_EMP AND    
	   A.CD_TBL_DESC_CARGO = TBA.CD_TBL_DESC_CARGO     
	   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_CARGO AS CD_TBL_DESC,            
	   B.PERC_DESC AS PERC_DESC,    
	   B.PERC_DESC AS PERC_DESC_MAX,   
	   4 AS TP_NIVEL,
	   A.DT_INI       
    FROM 
	   EST_PROD_TBL_DESC_CARGO A    
	   INNER JOIN EST_PROD_TBL_DESC_CARGO_EST_PROD_FABRIC B ON    
	   A.CD_EMP = B.CD_EMP AND    
	   A.CD_TBL_DESC_CARGO = B.CD_TBL_DESC_CARGO
	   INNER JOIN @RS_TAB_ATIVA TBA ON    
	   B.CD_EMP = TBA.CD_EMP AND    
	   B.CD_TBL_DESC_CARGO = TBA.CD_TBL_DESC_CARGO  
	   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_CARGO AS CD_TBL_DESC,        
		  B.PERC_DESC AS PERC_DESC,    
		  B.PERC_DESC AS PERC_DESC_MAX,   
		  5 AS TP_NIVEL,
		  A.DT_INI    
	   FROM 
		  EST_PROD_TBL_DESC_CARGO A    
		  INNER JOIN EST_PROD_TBL_DESC_CARGO_EST_ARV_MERC_CATEGORIA B ON    
		  A.CD_EMP = B.CD_EMP AND    
		  A.CD_TBL_DESC_CARGO = B.CD_TBL_DESC_CARGO
		  INNER JOIN @RS_TAB_ATIVA TBA ON    
		  A.CD_EMP = TBA.CD_EMP AND    
		  B.CD_TBL_DESC_CARGO = TBA.CD_TBL_DESC_CARGO   
		  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_CARGO AS CD_TBL_DESC,        
		  B.PERC_DESC AS PERC_DESC,    
		  B.PERC_DESC AS PERC_DESC_MAX,   
		  6 AS TP_NIVEL,
		  A.DT_INI   
	   FROM 
		  EST_PROD_TBL_DESC_CARGO A    
		  INNER JOIN EST_PROD_TBL_DESC_CARGO_EST_ARV_MERC_LINHA B ON    
		  A.CD_EMP = B.CD_EMP AND    
		  A.CD_TBL_DESC_CARGO = B.CD_TBL_DESC_CARGO   
		  INNER JOIN @RS_TAB_ATIVA TBA ON    
		  A.CD_EMP = TBA.CD_EMP AND    
		  B.CD_TBL_DESC_CARGO = TBA.CD_TBL_DESC_CARGO 
		  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;    
    
    -------------------------------------------------------------------------------------------------------------------------------    
    --Resultado final
    ------------------------------------------------------------------------------------------------------------------------------- 
	INSERT INTO @RETURN_REC
	SELECT TOP 
		  1 
		  CD_TBL_DESC,
		  PERC_DESC, 
		  PERC_DESC_MAX, 
		  TP_NIVEL
		  
	FROM @RS_TMP_DESC 
	ORDER BY 
	   TP_NIVEL ASC, 
	   PERC_DESC DESC,
	   DT_INI ASC;

	RETURN
END

	