IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'UFD_EST_PROD_COMIS_VALORES_HISTORICO') AND TYPE IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION UFD_EST_PROD_COMIS_VALORES_HISTORICO
GO 

CREATE FUNCTION [UFD_EST_PROD_COMIS_VALORES_HISTORICO](@CD_EMP    INT, @CD_FILIAL INT, @CD_PROD   INT, @CD_FUN    INT, @TP_VALOR  INT, @FLAG_PRE_VENCIDOS INT = 0, @DT_VD DATETIME)  
            
 
RETURNS @RS_TAB_COMIS TABLE(CD_TBL_COMIS       INT
								, FLAG_DESC_OUTR_TBL INT
								, PERC_COMIS         MONEY
								, TP_NIVEL           INT
								, CD_PROD            INT
								, CD_FILIAL          INT
								, CD_EMP             INT
								, TP_VALOR			 TINYINT
								)



AS
	 BEGIN
		 

		DECLARE @TBL_TP TABLE(TP_VALOR TINYINT);

		DECLARE @RS_TAB_ATIVA AS TABLE(	 CD_EMP       INT
										, CD_TBL_COMIS INT
										, TP_VALOR     TINYINT
										 UNIQUE CLUSTERED(CD_EMP, CD_TBL_COMIS)
										);

		DECLARE @RS_DADOS_TBL_COMIS AS TABLE(CD_TBL_COMIS       INT
												, TP_VALOR           INT
												, FLAG_DESC_OUTR_TBL INT
												, PERC_COMIS         MONEY
												, TP_NIVEL           INT
												);

		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;
           
 -------------------------------------------------------------------------------      
 --CONSUTA TABELAS DE COMISSÃO PELO TIPO(%/R$) DE COMISSÃO  
 -------------------------------------------------------------------------------      

	IF @TP_VALOR = 2
		BEGIN
			INSERT INTO @TBL_TP
			SELECT
				   1
			UNION ALL
			SELECT
				   0;

			INSERT INTO @RS_TAB_ATIVA
			SELECT DISTINCT
				   COMIS.CD_EMP
				 , COMIS.CD_TBL_COMIS
				 , A.TP_VALOR
			FROM
				(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS, CD_CARGO ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM FP_CARGO_EST_PROD_TBL_COMIS_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 ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND COMIS.CD_EMP = AUD.CD_EMP 
								AND COMIS.CD_TBL_COMIS = AUD.CD_TBL_COMIS
								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 A.STS_TBL = 0
				   AND FUN.CD_FUN = @CD_FUN
				   --AND COMIS.CD_GRP_ECON = @CD_GRP_ECON;
		END;
		ELSE
		BEGIN
			INSERT INTO @TBL_TP
			SELECT
				   @TP_VALOR;
			INSERT INTO @RS_TAB_ATIVA
			SELECT DISTINCT
				   COMIS.CD_EMP
				 , COMIS.CD_TBL_COMIS
				 , A.TP_VALOR
			FROM
				(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM FP_CARGO_EST_PROD_TBL_COMIS_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 ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND COMIS.CD_EMP = AUD.CD_EMP 
								AND COMIS.CD_TBL_COMIS = AUD.CD_TBL_COMIS
								AND AUD.STS_TBL = 0 
								AND AUD.TP_VALOR = @TP_VALOR
							 ) 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 A.STS_TBL = 0
				   AND FUN.CD_FUN = @CD_FUN
				   --AND A.TP_VALOR = @TP_VALOR
				   --AND COMIS.CD_GRP_ECON = @CD_GRP_ECON;
		END;                     

-------------------------------------------------------------------------------------------------------------------------------      
--Consultando as tbls de COMISSÃO no NÍVEL 1 - PRODUTOS      
-------------------------------------------------------------------------------------------------------------------------------      

	INSERT INTO @RS_DADOS_TBL_COMIS
	SELECT
		   CD_TBL_COMIS
		 , A.TP_VALOR
		 , FLAG_DESC_OUTR_TBL
		 , PERC_COMIS
		 , TP_NIVEL
	FROM
		@TBL_TP T
		CROSS APPLY (SELECT TOP 1
							   A.CD_TBL_COMIS AS CD_TBL_COMIS
							 , A.TP_VALOR AS TP_VALOR
							 , A.FLAG_DESC_OUTR_TBL AS FLAG_DESC_OUTR_TBL
							 , CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END AS PERC_COMIS
							 , 1 AS TP_NIVEL
						FROM
							--EST_PROD_TBL_COMIS A
							--INNER JOIN EST_PROD_TBL_COMIS_EST_PROD B ON A.CD_EMP = B.CD_EMP
							--											AND A.CD_TBL_COMIS = B.CD_TBL_COMIS
							--INNER JOIN EST_PROD_TBL_COMIS_PRC_FILIAL FIL ON B.CD_EMP = FIL.CD_EMP
							--												AND B.CD_TBL_COMIS = FIL.CD_TBL_COMIS
							(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
								AND AUD.TP_VALOR = T.TP_VALOR 
							 ) Q
						WHERE Q.RN = 1
					) A   
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS, CD_PROD ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_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 = AUD.CD_TBL_COMIS 
							 ) 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, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_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 = aud.CD_TBL_COMIS
							 ) 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 = RS_TAB_ATIVA.CD_TBL_COMIS
							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
														FROM
															@RS_DADOS_TBL_COMIS T
														WHERE  T.CD_TBL_COMIS = A.CD_TBL_COMIS
													)
													AND A.CD_EMP = @CD_EMP
													--AND A.TP_VALOR = T.TP_VALOR
											ORDER BY
														
													CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END DESC, A.DT_CAD DESC
										) A;  
      
 -------------------------------------------------------------------------------------------------------------------------------      
 --Consultando as tbls de COMISSÃO no NÍVEL 2 - FAMÍLIAS      
 -------------------------------------------------------------------------------------------------------------------------------      

	INSERT INTO @RS_DADOS_TBL_COMIS
	SELECT
		   CD_TBL_COMIS
		 , A.TP_VALOR
		 , FLAG_DESC_OUTR_TBL
		 , PERC_COMIS
		 , TP_NIVEL
	FROM
		@TBL_TP T
		CROSS APPLY
		(SELECT TOP 1
					A.CD_TBL_COMIS AS CD_TBL_COMIS
					, A.TP_VALOR AS TP_VALOR
					, A.FLAG_DESC_OUTR_TBL AS FLAG_DESC_OUTR_TBL
					, CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END AS PERC_COMIS
					, 2 AS TP_NIVEL
			FROM
				--EST_PROD_TBL_COMIS A
				--INNER JOIN EST_PROD_TBL_COMIS_EST_ARV_MERC_FAMILIA B ON A.CD_EMP = B.CD_EMP
				--														AND A.CD_TBL_COMIS = B.CD_TBL_COMIS
				--INNER JOIN EST_PROD_TBL_COMIS_PRC_FILIAL FIL ON B.CD_EMP = FIL.CD_EMP
				--												AND B.CD_TBL_COMIS = FIL.CD_TBL_COMIS
				(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
								AND AUD.TP_VALOR = T.TP_VALOR 
							 ) Q
						WHERE Q.RN = 1
					) A   
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS, CD_ARV_MERC_FAMILIA ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_EST_ARV_MERC_FAMILIA_audit_trigger AUD --select * from EST_PROD_TBL_COMIS_EST_ARV_MERC_FAMILIA
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = AUD.CD_EMP AND      
								A.CD_TBL_COMIS = AUD.CD_TBL_COMIS 
							 ) 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, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_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 = aud.CD_TBL_COMIS
							 ) 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 = RS_TAB_ATIVA.CD_TBL_COMIS
			WHERE  FIL.CD_FILIAL = @CD_FILIAL
					AND A.CD_EMP = @CD_EMP
					AND NOT EXISTS ( SELECT  T.CD_TBL_COMIS FROM @RS_DADOS_TBL_COMIS T WHERE  T.CD_TBL_COMIS = A.CD_TBL_COMIS )
					--AND A.TP_VALOR = T.TP_VALOR
			ORDER BY
					CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END DESC, A.DT_CAD DESC) AS A;    
          
-------------------------------------------------------------------------------------------------------------------------------          
--Consultando as tbls de COMISSÃO no NÍVEL 3 - MARCAS      
-------------------------------------------------------------------------------------------------------------------------------      

	INSERT INTO @RS_DADOS_TBL_COMIS
	SELECT
		   CD_TBL_COMIS
		 , A.TP_VALOR
		 , FLAG_DESC_OUTR_TBL
		 , PERC_COMIS
		 , TP_NIVEL
	FROM
		@TBL_TP T
		CROSS APPLY (SELECT TOP 1
							   A.CD_TBL_COMIS AS CD_TBL_COMIS
							 , A.TP_VALOR AS TP_VALOR
							 , A.FLAG_DESC_OUTR_TBL AS FLAG_DESC_OUTR_TBL
							 , CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END AS PERC_COMIS
							 , 3 AS TP_NIVEL
						FROM
							--EST_PROD_TBL_COMIS A
							--INNER JOIN EST_PROD_TBL_COMIS_EST_MC B ON A.CD_EMP = B.CD_EMP
							--										  AND A.CD_TBL_COMIS = B.CD_TBL_COMIS
							--INNER JOIN EST_PROD_TBL_COMIS_PRC_FILIAL FIL ON B.CD_EMP = FIL.CD_EMP
							--												AND B.CD_TBL_COMIS = FIL.CD_TBL_COMIS
							(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
								AND AUD.TP_VALOR = T.TP_VALOR 
							 ) Q
						WHERE Q.RN = 1
					) A   
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS, CD_MC ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_EST_MC_audit_trigger AUD --select * from EST_PROD_TBL_COMIS_EST_MC
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = AUD.CD_EMP AND      
								A.CD_TBL_COMIS = AUD.CD_TBL_COMIS 
							 ) 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, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_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 = aud.CD_TBL_COMIS
							 ) 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 = RS_TAB_ATIVA.CD_TBL_COMIS
						WHERE  FIL.CD_FILIAL = @CD_FILIAL
							   AND A.CD_EMP = @CD_EMP
							   AND NOT EXISTS( SELECT T.CD_TBL_COMIS FROM @RS_DADOS_TBL_COMIS T WHERE  T.CD_TBL_COMIS = A.CD_TBL_COMIS)
							   --AND A.TP_VALOR = T.TP_VALOR
						ORDER BY
								 CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END DESC, A.DT_CAD DESC) A;
      
 -------------------------------------------------------------------------------------------------------------------------------      
 --Consultando as tbls de COMISSÃO no NÍVEL 4 - FABRICANTES      
 -------------------------------------------------------------------------------------------------------------------------------      

	INSERT INTO @RS_DADOS_TBL_COMIS 
	SELECT
			CD_TBL_COMIS
			, A.TP_VALOR
			, FLAG_DESC_OUTR_TBL
			, PERC_COMIS
			, TP_NIVEL
	FROM
		@TBL_TP T
		CROSS APPLY (SELECT TOP 1
						   A.CD_TBL_COMIS AS CD_TBL_COMIS
						 , A.TP_VALOR AS TP_VALOR
						 , A.FLAG_DESC_OUTR_TBL AS FLAG_DESC_OUTR_TBL
						 , CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END AS PERC_COMIS
						 , 4 AS TP_NIVEL
					FROM
						--EST_PROD_TBL_COMIS A
						--INNER JOIN EST_PROD_TBL_COMIS_EST_PROD_FABRIC B ON A.CD_EMP = B.CD_EMP
						--												   AND A.CD_TBL_COMIS = B.CD_TBL_COMIS
						--INNER JOIN EST_PROD_TBL_COMIS_PRC_FILIAL FIL ON B.CD_EMP = FIL.CD_EMP
						--												AND B.CD_TBL_COMIS = FIL.CD_TBL_COMIS
						(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
								AND AUD.TP_VALOR = T.TP_VALOR 
							 ) Q
						WHERE Q.RN = 1
					) A   
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS, CD_FABRIC ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_EST_PROD_FABRIC_audit_trigger AUD --select * from EST_PROD_TBL_COMIS_EST_PROD_FABRIC
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = AUD.CD_EMP AND      
								A.CD_TBL_COMIS = AUD.CD_TBL_COMIS 
							 ) 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, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_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 = aud.CD_TBL_COMIS
							 ) 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 = RS_TAB_ATIVA.CD_TBL_COMIS
					WHERE  FIL.CD_FILIAL = @CD_FILIAL
						   AND A.CD_EMP = @CD_EMP
						   AND NOT EXISTS( SELECT T.CD_TBL_COMIS FROM @RS_DADOS_TBL_COMIS T WHERE  T.CD_TBL_COMIS = A.CD_TBL_COMIS )
						   --AND A.TP_VALOR = T.TP_VALOR
					ORDER BY
							CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END DESC,  A.DT_CAD DESC) A; 

-------------------------------------------------------------------------------------------------------------------------------      
 --Consultando as tbls de COMISSÃO no NÍVEL 5 - CATEGORIAS      
 -------------------------------------------------------------------------------------------------------------------------------      

	INSERT INTO @RS_DADOS_TBL_COMIS
	SELECT
		CD_TBL_COMIS
		, A.TP_VALOR
		, FLAG_DESC_OUTR_TBL
		, PERC_COMIS
		, TP_NIVEL
	FROM
		@TBL_TP T
		CROSS APPLY ( SELECT TOP 1
							A.CD_TBL_COMIS AS CD_TBL_COMIS
							, A.TP_VALOR AS TP_VALOR
							, A.FLAG_DESC_OUTR_TBL AS FLAG_DESC_OUTR_TBL
							, CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END AS PERC_COMIS
							, 5 AS TP_NIVEL
					FROM
						--EST_PROD_TBL_COMIS A
						--INNER JOIN EST_PROD_TBL_COMIS_EST_ARV_MERC_CATEGORIA B ON A.CD_EMP = B.CD_EMP
						--															AND A.CD_TBL_COMIS = B.CD_TBL_COMIS
						--INNER JOIN EST_PROD_TBL_COMIS_PRC_FILIAL FIL ON B.CD_EMP = FIL.CD_EMP
						--												AND B.CD_TBL_COMIS = FIL.CD_TBL_COMIS
						(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
								AND AUD.TP_VALOR = T.TP_VALOR 
							 ) Q
						WHERE Q.RN = 1
					) A   
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS, CD_ARV_MERC_CATEG ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_EST_ARV_MERC_CATEGORIA_audit_trigger AUD --select * from EST_PROD_TBL_COMIS_EST_ARV_MERC_CATEGORIA
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = AUD.CD_EMP AND      
								A.CD_TBL_COMIS = AUD.CD_TBL_COMIS 
							 ) 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, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_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 = aud.CD_TBL_COMIS
							 ) 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 = RS_TAB_ATIVA.CD_TBL_COMIS
					WHERE  FIL.CD_FILIAL = @CD_FILIAL
							AND A.CD_EMP = @CD_EMP
							AND NOT EXISTS( SELECT T.CD_TBL_COMIS FROM @RS_DADOS_TBL_COMIS T WHERE  T.CD_TBL_COMIS = A.CD_TBL_COMIS)
							--AND A.TP_VALOR = T.TP_VALOR
					ORDER BY
								CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END DESC, A.DT_CAD DESC) A;
		
 ------------------------------------------------------------------------------------------------------------------------------      
 --Consultando as tbls de COMISSÃO no NÍVEL 6 - LINHAS      
 -------------------------------------------------------------------------------------------------------------------------------      

	INSERT INTO @RS_DADOS_TBL_COMIS
	SELECT
		CD_TBL_COMIS
		, A.TP_VALOR
		, FLAG_DESC_OUTR_TBL
		, PERC_COMIS
		, TP_NIVEL
	FROM
		@TBL_TP T
		CROSS APPLY (SELECT TOP 1
							A.CD_TBL_COMIS AS CD_TBL_COMIS
							, A.TP_VALOR AS TP_VALOR
							, A.FLAG_DESC_OUTR_TBL AS FLAG_DESC_OUTR_TBL
							, CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END AS PERC_COMIS
							, 6 AS TP_NIVEL
					FROM
						--EST_PROD_TBL_COMIS A
						--INNER JOIN EST_PROD_TBL_COMIS_EST_ARV_MERC_LINHA B ON A.CD_EMP = B.CD_EMP
						--														AND A.CD_TBL_COMIS = B.CD_TBL_COMIS
						--INNER JOIN EST_PROD_TBL_COMIS_PRC_FILIAL FIL ON B.CD_EMP = FIL.CD_EMP
						--												AND B.CD_TBL_COMIS = FIL.CD_TBL_COMIS
						(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
								AND AUD.TP_VALOR = T.TP_VALOR 
							 ) Q
						WHERE Q.RN = 1
					) A   
		CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS, CD_ARV_MERC_LINHA ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_EST_ARV_MERC_LINHA_audit_trigger AUD --select * from EST_PROD_TBL_COMIS_EST_ARV_MERC_LINHA
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								AND A.CD_EMP = AUD.CD_EMP AND      
								A.CD_TBL_COMIS = AUD.CD_TBL_COMIS 
							 ) 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, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_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 = aud.CD_TBL_COMIS
							 ) 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 = RS_TAB_ATIVA.CD_TBL_COMIS
					WHERE  FIL.CD_FILIAL = @CD_FILIAL
							AND A.CD_EMP = @CD_EMP
							AND NOT EXISTS ( SELECT T.CD_TBL_COMIS FROM @RS_DADOS_TBL_COMIS T WHERE  T.CD_TBL_COMIS = A.CD_TBL_COMIS )
							--AND A.TP_VALOR = T.TP_VALOR
					ORDER BY
								CASE WHEN @FLAG_PRE_VENCIDOS = 1 THEN B.PERC_COMIS_PRE_VENC ELSE B.PERC_COMIS END DESC, A.DT_CAD DESC) A;
 
 -------------------------------------------------------------------------------------------------------------------------------      
 --Consultando as tbls de COMISSÃO no NÍVEL 7 - FILIAL      
 -------------------------------------------------------------------------------------------------------------------------------      

	INSERT INTO @RS_DADOS_TBL_COMIS
	SELECT
		CD_TBL_COMIS
		, A.TP_VALOR
		, FLAG_DESC_OUTR_TBL
		, PERC_COMIS
		, TP_NIVEL
	FROM
		@TBL_TP T
		CROSS APPLY (SELECT TOP 1
								A.CD_TBL_COMIS AS CD_TBL_COMIS
								, A.TP_VALOR AS TP_VALOR
								, A.FLAG_DESC_OUTR_TBL AS FLAG_DESC_OUTR_TBL
								, B.PERC_COMIS AS PERC_COMIS
								, 7 AS TP_NIVEL
						FROM
							--EST_PROD_TBL_COMIS A
							--INNER JOIN EST_PROD_TBL_COMIS_PRC_FILIAL B ON A.CD_EMP = B.CD_EMP
							--												AND A.CD_TBL_COMIS = B.CD_TBL_COMIS
							(SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_audit_trigger AUD
								WHERE COALESCE(DT_EVENT,'2000-01-01') <= @DT_VD
								--AND AUD.STS_TBL = 0 
								AND AUD.TP_VALOR = T.TP_VALOR 
							 ) Q
						WHERE Q.RN = 1
					) A   
					CROSS APPLY (SELECT *
						FROM (
								SELECT ROW_NUMBER() OVER(PARTITION BY CD_TBL_COMIS, CD_FILIAL ORDER BY COALESCE(DT_EVENT,'2000-01-01') DESC) AS RN, *
								FROM EST_PROD_TBL_COMIS_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 = aud.CD_TBL_COMIS
							 ) 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 = RS_TAB_ATIVA.CD_TBL_COMIS
						WHERE  B.CD_FILIAL = @CD_FILIAL
								AND A.CD_EMP = @CD_EMP
								AND NOT EXISTS(SELECT T.CD_TBL_COMIS FROM @RS_DADOS_TBL_COMIS T WHERE  T.CD_TBL_COMIS = A.CD_TBL_COMIS )
								--AND A.TP_VALOR = T.TP_VALOR
						ORDER BY
									A.DT_CAD DESC
								, B.PERC_COMIS DESC) A;

-------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------

	INSERT INTO @RS_TAB_COMIS
	SELECT TOP 1
		   CD_TBL_COMIS
		 , FLAG_DESC_OUTR_TBL
		 , PERC_COMIS
		 , TP_NIVEL
		 , @CD_PROD AS CD_PROD
		 , @CD_FILIAL AS CD_FILIAL
		 , @CD_EMP AS CD_EMP
		 , TP_VALOR
	FROM
		@RS_DADOS_TBL_COMIS A
	ORDER BY
			 TP_NIVEL ASC
		   , PERC_COMIS DESC;
		    
		 RETURN;
	 END;

GO


