IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'UFD_EST_PROD_COMIS_REGRESSAO_VALORES_HISTORICO') AND TYPE IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION UFD_EST_PROD_COMIS_REGRESSAO_VALORES_HISTORICO
GO 
--SELECT * FROM UFD_EST_PROD_COMIS_REGRESSAO_VALORES (1,48,36846,0,32)
CREATE FUNCTION [UFD_EST_PROD_COMIS_REGRESSAO_VALORES_HISTORICO]  (@CD_EMP  INT,      
															@CD_FILIAL INT,  
															@CD_PROD INT,      
															@CD_FUN  INT,  
															@PERC_DESC MONEY,
															@PERC_DESC_CAD MONEY,
															@DT_VD DATETIME)  
RETURNS @RS_TAB_COMIS TABLE (CD_TBL_COMIS_REGRESSAO  INT,  
							  TP_NIVEL	INT,  
							  PERC_COMIS	MONEY,   
							  CD_PROD		INT,  
							  CD_FILIAL   INT,  
							  CD_EMP		INT)  
 AS                
BEGIN                
          
DECLARE @RS_TAB_ATIVA AS TABLE (CD_EMP INT, 
								 CD_TBL_COMIS_REGRESSAO INT,
								 FLAG_REGRESSAO_ACRESCIMO_DESCONTO INT
								 UNIQUE CLUSTERED (CD_EMP, CD_TBL_COMIS_REGRESSAO) ) 

DECLARE @RS_DADOS_TBL_COMIS AS TABLE (CD_TBL_COMIS_REGRESSAO INT, 
									  TP_NIVEL		INT, 
									  PERC_COMIS	MONEY,  
									  DESC_INI		MONEY,  
									  DESC_FIM		MONEY)  

DECLARE @CD_GRP_ECON INT
SET @CD_GRP_ECON = (SELECT TOP 1 CD_GRP_ECON
					FROM PRC_GRP_ECON_PRC_FILIAL
					WHERE
						CD_EMP =@CD_EMP
						AND CD_FILIAL =@CD_FILIAL)

 -------------------------------------------------------------------------------------------------------------------------------      
 --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_SEG INT,      
          CD_ARV_MERC_PAPEL INT,      
          CD_ARV_MERC_MIX INT,      
          CD_ARV_MERC_CONSUMO INT,      
          CD_ARV_MERC_LINHA INT,      
          CD_ARV_MERC_MTZ_BCG INT,      
          CD_MC   INT,      
          CD_ARV_MERC_FAMILIA INT,              
          CD_APRES   INT,          
          CD_ARV_MERC_EQUI INT,      
          CD_GRP_EXPO  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_SEG   ,      
		ARV.CD_ARV_MERC_PAPEL  ,      
		ARV.CD_ARV_MERC_MIX   ,      
		ARV.CD_ARV_MERC_CONSUMO  ,      
		ARV.CD_ARV_MERC_LINHA  ,      
		ARV.CD_ARV_MERC_MTZ_BCG  ,      
		ARV.CD_MC     ,      
		ARV.CD_ARV_MERC_FAMILIA  ,              
		ARV.CD_APRES    ,          
		ARV.CD_ARV_MERC_EQUI  ,      
		ARV.CD_GRP_EXPO    ,      
		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;
           
 -------------------------------------------------------------------------------      
 --CONSULTA TABELAS DE COMISSÃO PELO TIPO(%/R$) DE COMISSÃO  
 -------------------------------------------------------------------------------      
	INSERT INTO @RS_TAB_ATIVA      
	SELECT  DISTINCT 
		A.CD_EMP,      
		A.CD_TBL_COMIS_REGRESSAO,
		ISNULL(FLAG_REGRESSAO_ACRESCIMO_DESCONTO,0)
	FROM 
		--FP_CARGO_EST_PROD_TBL_COMIS_REGRESSAO COMIS   
		 (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_CARGO ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM FP_CARGO_EST_PROD_TBL_COMIS_REGRESSAO_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND AUD.CD_GRP_ECON=@CD_GRP_ECON 
							 ) Q
						WHERE Q.RN = 1
					) COMIS
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO A ON  
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND COMIS.CD_EMP = AUD.CD_EMP 
								AND COMIS.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO
								AND AUD.STS_TBL = 0 
							 ) Q
						WHERE Q.RN = 1
					) A
		INNER JOIN FP_FUNC FUN ON  
		COMIS.CD_EMP = FUN.CD_EMP AND  
		COMIS.CD_CARGO = FUN.CD_CARGO  
	WHERE 
		A.CD_EMP = @CD_EMP 
		AND FUN.CD_FUN = @CD_FUN 
		--AND COMIS.CD_GRP_ECON=@CD_GRP_ECON


		
--Analisando o tipo de desconto por diferenca de acrescimento 
--de desconto pelo vendedor


IF EXISTS(SELECT
			TOP 1 CD_EMP
		FROM
			@RS_TAB_ATIVA
		WHERE
			FLAG_REGRESSAO_ACRESCIMO_DESCONTO=1)                     
 begin
	if (@PERC_DESC_CAD < @PERC_DESC)
	begin
		SET @PERC_DESC=abs(@PERC_DESC_CAD-@PERC_DESC)
	end
	else
	begin
		SET @PERC_DESC=0
	end
 end


 --select *
 --from EST_PROD_TBL_COMIS_REGRESSAO_audit_trigger
 -------------------------------------------------------------------------------------------------------------------------------      
 --Consultando as tbls de COMISSÃO no NÍVEL 1 - PRODUTOS      
 -------------------------------------------------------------------------------------------------------------------------------      
	INSERT INTO @RS_DADOS_TBL_COMIS       
	SELECT TOP 1      
		A.CD_TBL_COMIS_REGRESSAO AS CD_TBL_COMIS_REGRESSAO, 
		1 AS TIPO_NIVEL,	     
		D.PERC_COMIS,      
		D.DESC_INI,      
		D.DESC_FIM   
	FROM 
		(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
							 ) Q
						WHERE Q.RN = 1
					) A   
					--SELECT * FROM EST_PROD_TBL_COMIS_REGRESSAO_audit_trigger
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, PERC_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								and A.CD_EMP = AUD.CD_EMP AND    	 
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO
							 ) Q
						WHERE Q.RN = 1
					) D
		    
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_EST_PROD B ON  
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_PROD ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_EST_PROD_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = AUD.CD_EMP AND      
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) B
		    
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND B.CD_EMP = aud.CD_EMP AND      
								B.CD_TBL_COMIS_REGRESSAO = aud.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) FIL
		    
		INNER JOIN @RS_TAB_ATIVA RS_TAB_ATIVA ON      
		B.CD_EMP = RS_TAB_ATIVA.CD_EMP AND      
		B.CD_TBL_COMIS_REGRESSAO = RS_TAB_ATIVA.CD_TBL_COMIS_REGRESSAO       
		INNER JOIN @EST_PROD_ARV_MERC ARV ON      
		B.CD_EMP = ARV.CD_EMP AND      
		B.CD_PROD = ARV.CD_PROD             
	WHERE       
		FIL.CD_FILIAL = @CD_FILIAL      
		AND NOT EXISTS (SELECT 
						T.CD_TBL_COMIS_REGRESSAO 
					 FROM 
						@RS_DADOS_TBL_COMIS T 
					 WHERE 
						T.CD_TBL_COMIS_REGRESSAO = A.CD_TBL_COMIS_REGRESSAO)      
		AND @PERC_DESC BETWEEN D.DESC_INI AND D.DESC_FIM   
   ORDER BY 
		D.PERC_COMIS DESC  

		--select * from EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL_audit_trigger
		
 -------------------------------------------------------------------------------------------------------------------------------      
 --Consultando as tbls de COMISSÃO no NÍVEL 2 - FAMÍLIAS      
 -------------------------------------------------------------------------------------------------------------------------------      
	INSERT INTO @RS_DADOS_TBL_COMIS       
	SELECT TOP 1      
		A.CD_TBL_COMIS_REGRESSAO AS CD_TBL_COMIS_REGRESSAO, 
		2 AS TIPO_NIVEL,	     
		D.PERC_COMIS,      
		D.DESC_INI,      
		D.DESC_FIM   
	FROM 
		--EST_PROD_TBL_COMIS_REGRESSAO A 
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		--A.CD_EMP = D.CD_EMP AND    	 
		--A.CD_TBL_COMIS_REGRESSAO = D.CD_TBL_COMIS_REGRESSAO  
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_EST_ARV_MERC_FAMILIA B ON      
		--A.CD_EMP = B.CD_EMP AND      
		--A.CD_TBL_COMIS_REGRESSAO = B.CD_TBL_COMIS_REGRESSAO      
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		--B.CD_EMP = FIL.CD_EMP AND      
		--B.CD_TBL_COMIS_REGRESSAO = FIL.CD_TBL_COMIS_REGRESSAO  
		(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
							 ) Q
						WHERE Q.RN = 1
					) A   
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, PERC_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								and A.CD_EMP = AUD.CD_EMP AND    	 
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO
							 ) Q
						WHERE Q.RN = 1
					) D
		    
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_EST_PROD B ON  
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_ARV_MERC_FAMILIA ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_EST_ARV_MERC_FAMILIA_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = AUD.CD_EMP AND      
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) B

		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND B.CD_EMP = aud.CD_EMP AND      
								B.CD_TBL_COMIS_REGRESSAO = aud.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) FIL
		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      
		INNER JOIN @RS_TAB_ATIVA RS_TAB_ATIVA ON      
		A.CD_EMP = RS_TAB_ATIVA.CD_EMP AND      
		B.CD_TBL_COMIS_REGRESSAO = RS_TAB_ATIVA.CD_TBL_COMIS_REGRESSAO          
	WHERE       
		FIL.CD_FILIAL = @CD_FILIAL      
		AND NOT EXISTS (SELECT 
						T.CD_TBL_COMIS_REGRESSAO 
					 FROM 
						@RS_DADOS_TBL_COMIS T 
					 WHERE 
						T.CD_TBL_COMIS_REGRESSAO = A.CD_TBL_COMIS_REGRESSAO)      
		AND @PERC_DESC BETWEEN D.DESC_INI AND D.DESC_FIM   
   ORDER BY 
		D.PERC_COMIS DESC  
  
		
 -------------------------------------------------------------------------------------------------------------------------------          
 --Consultando as tbls de COMISSÃO no NÍVEL 3 - MARCAS      
 -------------------------------------------------------------------------------------------------------------------------------      
	INSERT INTO @RS_DADOS_TBL_COMIS       
	SELECT TOP 1      
		A.CD_TBL_COMIS_REGRESSAO AS CD_TBL_COMIS_REGRESSAO, 
		3 AS TIPO_NIVEL,	     
		D.PERC_COMIS,      
		D.DESC_INI,      
		D.DESC_FIM   
	FROM 
		--EST_PROD_TBL_COMIS_REGRESSAO A     
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		--A.CD_EMP = D.CD_EMP AND    	 
		--A.CD_TBL_COMIS_REGRESSAO = D.CD_TBL_COMIS_REGRESSAO  
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_EST_MC B ON      
		--A.CD_EMP = B.CD_EMP AND      
		--A.CD_TBL_COMIS_REGRESSAO = B.CD_TBL_COMIS_REGRESSAO      
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		--B.CD_EMP = FIL.CD_EMP AND      
		--B.CD_TBL_COMIS_REGRESSAO = FIL.CD_TBL_COMIS_REGRESSAO   
		(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
							 ) Q
						WHERE Q.RN = 1
					) A   
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, PERC_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								and A.CD_EMP = AUD.CD_EMP AND    	 
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO
							 ) Q
						WHERE Q.RN = 1
					) D
		    
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_EST_PROD B ON  
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_MC ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_EST_MC_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = AUD.CD_EMP AND      
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) B

		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND B.CD_EMP = aud.CD_EMP AND      
								B.CD_TBL_COMIS_REGRESSAO = aud.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) FIL
		INNER JOIN @EST_PROD_ARV_MERC ARV ON      
		B.CD_EMP = ARV.CD_EMP AND      
		B.CD_MC  = ARV.CD_MC      
		INNER JOIN @RS_TAB_ATIVA RS_TAB_ATIVA ON      
		A.CD_EMP = RS_TAB_ATIVA.CD_EMP AND      
		B.CD_TBL_COMIS_REGRESSAO = RS_TAB_ATIVA.CD_TBL_COMIS_REGRESSAO          
	WHERE       
		FIL.CD_FILIAL = @CD_FILIAL      
		AND NOT EXISTS (SELECT 
						T.CD_TBL_COMIS_REGRESSAO 
					 FROM 
						@RS_DADOS_TBL_COMIS T 
					 WHERE 
						T.CD_TBL_COMIS_REGRESSAO = A.CD_TBL_COMIS_REGRESSAO)      
		AND @PERC_DESC BETWEEN D.DESC_INI AND D.DESC_FIM   
   ORDER BY 
		D.PERC_COMIS DESC 
 -------------------------------------------------------------------------------------------------------------------------------      
 --Consultando as tbls de COMISSÃO no NÍVEL 4 - FABRICANTES      
 -------------------------------------------------------------------------------------------------------------------------------      
	INSERT INTO @RS_DADOS_TBL_COMIS       
	SELECT TOP 1      
		A.CD_TBL_COMIS_REGRESSAO AS CD_TBL_COMIS_REGRESSAO, 
		4 AS TIPO_NIVEL,	     
		D.PERC_COMIS,      
		D.DESC_INI,      
		D.DESC_FIM   
	FROM 
		--EST_PROD_TBL_COMIS_REGRESSAO A   
		
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		--A.CD_EMP = D.CD_EMP AND    	 
		--A.CD_TBL_COMIS_REGRESSAO = D.CD_TBL_COMIS_REGRESSAO     
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_EST_PROD_FABRIC B ON      
		--A.CD_EMP = B.CD_EMP AND      
		--A.CD_TBL_COMIS_REGRESSAO = B.CD_TBL_COMIS_REGRESSAO      
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		--B.CD_EMP = FIL.CD_EMP AND      
		--B.CD_TBL_COMIS_REGRESSAO = FIL.CD_TBL_COMIS_REGRESSAO  
		(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
							 ) Q
						WHERE Q.RN = 1
					) A   
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, PERC_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								and A.CD_EMP = AUD.CD_EMP AND    	 
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO
							 ) Q
						WHERE Q.RN = 1
					) D
		    
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_EST_PROD B ON SELECT * FROM EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS_audit_trigger 
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_FABRIC ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_EST_PROD_FABRIC_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = AUD.CD_EMP AND      
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) B

		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND B.CD_EMP = aud.CD_EMP AND      
								B.CD_TBL_COMIS_REGRESSAO = aud.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) FIL
		INNER JOIN @EST_PROD_ARV_MERC ARV ON      
		B.CD_EMP = ARV.CD_EMP AND      
		B.CD_FABRIC = ARV.CD_FABRIC       
		INNER JOIN @RS_TAB_ATIVA RS_TAB_ATIVA ON      
		B.CD_EMP = RS_TAB_ATIVA.CD_EMP AND      
		B.CD_TBL_COMIS_REGRESSAO = RS_TAB_ATIVA.CD_TBL_COMIS_REGRESSAO          
	     
	WHERE       
		FIL.CD_FILIAL = @CD_FILIAL      
		AND NOT EXISTS (SELECT 
						T.CD_TBL_COMIS_REGRESSAO 
					 FROM 
						@RS_DADOS_TBL_COMIS T 
					 WHERE 
						T.CD_TBL_COMIS_REGRESSAO = A.CD_TBL_COMIS_REGRESSAO)      
		AND @PERC_DESC BETWEEN D.DESC_INI AND D.DESC_FIM   
   ORDER BY 
		D.PERC_COMIS DESC  

		 
-------------------------------------------------------------------------------------------------------------------------------      
 --Consultando as tbls de COMISSÃO no NÍVEL 5 - CATEGORIAS      
 -------------------------------------------------------------------------------------------------------------------------------      
	INSERT INTO @RS_DADOS_TBL_COMIS       
	SELECT TOP 1      
		A.CD_TBL_COMIS_REGRESSAO AS CD_TBL_COMIS_REGRESSAO, 
		5 AS TIPO_NIVEL,	     
		D.PERC_COMIS,      
		D.DESC_INI,      
		D.DESC_FIM      
	FROM 
		--EST_PROD_TBL_COMIS_REGRESSAO A      
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		--A.CD_EMP = D.CD_EMP AND    	 
		--A.CD_TBL_COMIS_REGRESSAO = D.CD_TBL_COMIS_REGRESSAO     
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_EST_ARV_MERC_CATEGORIA B ON      
		--A.CD_EMP = B.CD_EMP AND      
		--A.CD_TBL_COMIS_REGRESSAO = B.CD_TBL_COMIS_REGRESSAO      
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		--B.CD_EMP = FIL.CD_EMP AND      
		--B.CD_TBL_COMIS_REGRESSAO = FIL.CD_TBL_COMIS_REGRESSAO 
		(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
							 ) Q
						WHERE Q.RN = 1
					) A   
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, PERC_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								and A.CD_EMP = AUD.CD_EMP AND    	 
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO
							 ) Q
						WHERE Q.RN = 1
					) D
		    
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_EST_PROD B ON  
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_ARV_MERC_CATEG ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_EST_ARV_MERC_CATEGORIA_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = AUD.CD_EMP AND      
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) B

		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND B.CD_EMP = aud.CD_EMP AND      
								B.CD_TBL_COMIS_REGRESSAO = aud.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) FIL
		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      
		INNER JOIN @RS_TAB_ATIVA RS_TAB_ATIVA ON      
		A.CD_EMP = RS_TAB_ATIVA.CD_EMP AND      
		B.CD_TBL_COMIS_REGRESSAO = RS_TAB_ATIVA.CD_TBL_COMIS_REGRESSAO          
	WHERE       
		FIL.CD_FILIAL = @CD_FILIAL      
		AND NOT EXISTS (SELECT 
						T.CD_TBL_COMIS_REGRESSAO 
					 FROM 
						@RS_DADOS_TBL_COMIS T 
					 WHERE 
						T.CD_TBL_COMIS_REGRESSAO = A.CD_TBL_COMIS_REGRESSAO)      
		AND @PERC_DESC BETWEEN D.DESC_INI AND D.DESC_FIM   
   ORDER BY 
		D.PERC_COMIS DESC  
		
 ----------------------------------------------------------------------------------------------------------------------------      
 --Consultando as tbls de COMISSÃO no NÍVEL 6 - LINHAS      
 -----------------------------------------------------------------------------------------------------------------------------      
	INSERT INTO @RS_DADOS_TBL_COMIS       
	SELECT TOP 1      
		A.CD_TBL_COMIS_REGRESSAO AS CD_TBL_COMIS_REGRESSAO, 
		6 AS TIPO_NIVEL,	     
		D.PERC_COMIS,      
		D.DESC_INI,      
		D.DESC_FIM
	FROM 
		--EST_PROD_TBL_COMIS_REGRESSAO A   
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		--A.CD_EMP = D.CD_EMP AND      
		--A.CD_TBL_COMIS_REGRESSAO = D.CD_TBL_COMIS_REGRESSAO      
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_EST_ARV_MERC_LINHA B ON      
		--A.CD_EMP = B.CD_EMP AND      
		--A.CD_TBL_COMIS_REGRESSAO = B.CD_TBL_COMIS_REGRESSAO      
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		--B.CD_EMP = FIL.CD_EMP AND      
		--B.CD_TBL_COMIS_REGRESSAO = FIL.CD_TBL_COMIS_REGRESSAO
		(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
							 ) Q
						WHERE Q.RN = 1
					) A   
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, PERC_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								and A.CD_EMP = AUD.CD_EMP AND    	 
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO
							 ) Q
						WHERE Q.RN = 1
					) D
		    
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_EST_PROD B ON  
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_ARV_MERC_LINHA ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_EST_ARV_MERC_LINHA_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = AUD.CD_EMP AND      
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) B

		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND B.CD_EMP = aud.CD_EMP AND      
								B.CD_TBL_COMIS_REGRESSAO = aud.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) FIL
		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      
		INNER JOIN @RS_TAB_ATIVA RS_TAB_ATIVA ON      
		A.CD_EMP = RS_TAB_ATIVA.CD_EMP AND      
		B.CD_TBL_COMIS_REGRESSAO = RS_TAB_ATIVA.CD_TBL_COMIS_REGRESSAO          
	WHERE       
		FIL.CD_FILIAL = @CD_FILIAL AND       
		NOT EXISTS (SELECT 
					T.CD_TBL_COMIS_REGRESSAO 
					FROM 
					@RS_DADOS_TBL_COMIS T 
					WHERE 
					T.CD_TBL_COMIS_REGRESSAO = A.CD_TBL_COMIS_REGRESSAO)      
		AND @PERC_DESC BETWEEN D.DESC_INI AND D.DESC_FIM
	ORDER BY 
		D.PERC_COMIS DESC;

 
 -------------------------------------------------------------------------------------------------------------------------------      
 --Consultando as tbls de COMISSÃO no NÍVEL 7 - FILIAL      
 -------------------------------------------------------------------------------------------------------------------------------      
	INSERT INTO @RS_DADOS_TBL_COMIS       
	SELECT TOP 1      
		A.CD_TBL_COMIS_REGRESSAO AS CD_TBL_COMIS_REGRESSAO, 
		7 AS TIPO_NIVEL,	     
		D.PERC_COMIS,      
		D.DESC_INI,      
		D.DESC_FIM    
	FROM 
		--EST_PROD_TBL_COMIS_REGRESSAO A     
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		--A.CD_EMP = D.CD_EMP AND      
		--A.CD_TBL_COMIS_REGRESSAO = D.CD_TBL_COMIS_REGRESSAO      
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL B ON      
		--A.CD_EMP = B.CD_EMP AND      
		--A.CD_TBL_COMIS_REGRESSAO = B.CD_TBL_COMIS_REGRESSAO  
		(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
							 ) Q
						WHERE Q.RN = 1
					) A   
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS D ON
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, PERC_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_DESCONTOS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								and A.CD_EMP = AUD.CD_EMP AND    	 
								A.CD_TBL_COMIS_REGRESSAO = AUD.CD_TBL_COMIS_REGRESSAO
							 ) Q
						WHERE Q.RN = 1
					) D
		    
		--INNER JOIN EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL FIL ON      
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS_REGRESSAO, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_REGRESSAO_PRC_FILIAL_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = aud.CD_EMP AND      
								A.CD_TBL_COMIS_REGRESSAO = aud.CD_TBL_COMIS_REGRESSAO  
							 ) Q
						WHERE Q.RN = 1
					) B
		INNER JOIN @RS_TAB_ATIVA RS_TAB_ATIVA ON      
		A.CD_EMP = RS_TAB_ATIVA.CD_EMP AND      
		B.CD_TBL_COMIS_REGRESSAO = RS_TAB_ATIVA.CD_TBL_COMIS_REGRESSAO          
	WHERE       
			b.CD_FILIAL = @CD_FILIAL AND       
			NOT EXISTS (SELECT 
						T.CD_TBL_COMIS_REGRESSAO 
						FROM 
						@RS_DADOS_TBL_COMIS T 
						WHERE 
						T.CD_TBL_COMIS_REGRESSAO = A.CD_TBL_COMIS_REGRESSAO)      
			AND @PERC_DESC BETWEEN D.DESC_INI AND D.DESC_FIM
		ORDER BY 
			D.PERC_COMIS DESC;


	INSERT INTO @RS_TAB_COMIS   
	SELECT TOP 1 
		CD_TBL_COMIS_REGRESSAO,  
		TP_NIVEL,
		PERC_COMIS,   
		@CD_PROD AS CD_PROD,  
		@CD_FILIAL AS CD_FILIAL,  
		@CD_EMP AS CD_EMP  
	FROM 
		@RS_DADOS_TBL_COMIS A  
	WHERE
		@PERC_DESC BETWEEN DESC_INI AND DESC_FIM
	ORDER BY 
		TP_NIVEL ASC, 
		PERC_COMIS DESC  
  
RETURN  
  
END        
  
GO


