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


CREATE FUNCTION [dbo].[UFD_EST_PROD_TRIB_IBS_CBS_RECALCULO](
    @CD_EMP	INT
	,@CD_FILIAL INT
	,@CD_DEST_ORIG INT
	,@CD_PROD INT
	,@QT_PROD INT
	,@VLR_IT MONEY
	,@VLR_DESC_IT MONEY
	,@VLR_FRETE MONEY
	,@VLR_OUTRAS MONEY
	,@VLR_FECOP MONEY
	,@VLR_ICMS MONEY
	,@VLR_PIS MONEY
	,@VLR_COFINS MONEY
	,@CD_MOV_OP INT
)
RETURNS @RS_RETURN TABLE(
    CD_TRIB_IBS_CBS           INT,
    CST_IBS_CBS               VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS,
    CLASS_TRIB_IBS_CBS        VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
    VLR_BC_IBS_CBS            MONEY,
    PERC_IBS_UF               MONEY,
    PERC_IBS_UF_DIFERIMENTO   MONEY,
    VLR_IBS_UF_DIFERIMENTO    MONEY,
    VLR_IBS_UF_DEV_TRIB       MONEY,
    PERC_IBS_UF_RED           MONEY,
    PERC_IBS_UF_RED_EFET      MONEY,
    VLR_IBS_UF                MONEY,
    PERC_IBS_MUN              MONEY,
    PERC_IBS_MUN_DIFERIMENTO  MONEY,
    VLR_IBS_MUN_DIFERIMENTO   MONEY,
    VLR_IBS_MUN_DEV_TRIB      MONEY,
    PERC_IBS_MUN_RED          MONEY,
    PERC_IBS_MUN_RED_EFET     MONEY,
    VLR_IBS_MUN               MONEY,
    VLR_IBS                   MONEY,
    PERC_CBS                  MONEY,
    PERC_CBS_DIFERIMENTO      MONEY,
    VLR_CBS_DIFERIMENTO       MONEY,
    VLR_CBS_DEV_TRIB          MONEY,
    PERC_CBS_RED              MONEY,
    PERC_CBS_RED_EFET         MONEY,
    VLR_CBS                   MONEY
	,CD_TRIB_IBS_CBS_REG INT
	,CST_REG	VARCHAR(10)	COLLATE SQL_Latin1_General_CP1_CI_AS
	,CLASS_TRIB_REG VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS
	,PERC_EFET_REG_IBS_UF MONEY
	,VLR_TRIB_REG_IBS_UF MONEY
	,PERC_EFET_REG_IBS_MUN MONEY
	,VLR_TRIB_REG_IBS_MUN MONEY
	,PERC_EFET_REG_CBS MONEY
	,VLR_TRIB_REG_CBS MONEY
	,PERC_IBS_UF_COMPRA_GOV MONEY
	,VLR_TRIB_IBS_UF_COMPRA_GOV MONEY
	,PERC_IBS_MUN_COMPRA_GOV MONEY
	,VLR_TRIB_IBS_MUN_COMPRA_GOV MONEY
	,PERC_CBS_COMPRA_GOV MONEY
	,VLR_TRIB_CBS_COMPRA_GOV MONEY
)

AS
BEGIN

	DECLARE @VLR_BASE_CALC_IT MONEY = 0
	DECLARE @VLR_PRODUTO_TOTAL MONEY = 0
	DECLARE @FLAG_FRETE_BASE_ICMS INT = 0
	DECLARE @INSC_SUFRAMA VARCHAR(50)
	DECLARE @DEST_ORIG INT
	DECLARE @TIPO_ENTE_GOVERNAMENTAL INT
	
	
	/*Suframa e Ente Governamental*/

			SELECT @DEST_ORIG = DEST_ORIG 
			FROM GLB_MOV_OP 
			WHERE CD_MOV_OP = @CD_MOV_OP

			IF @DEST_ORIG = 0 
            BEGIN 
                SELECT TOP 1 @INSC_SUFRAMA = D.INSC_SUFRAMA,
							@TIPO_ENTE_GOVERNAMENTAL = ISNULL(d.TIPO_ENTE_GOVERNAMENTAL,0)
                FROM RC_CLI D 
                WHERE 
                        D.CD_EMP        = @CD_EMP
                        AND D.CD_CLI    = @CD_DEST_ORIG
            END
            IF @DEST_ORIG = 1 
            BEGIN 
                SELECT TOP 1 @INSC_SUFRAMA = D.INSC_SUFRAMA,
							@TIPO_ENTE_GOVERNAMENTAL = ISNULL(d.TIPO_ENTE_GOVERNAMENTAL,0)
                FROM PG_FORN D 
                WHERE 
                        D.CD_EMP        = @CD_EMP
                        AND D.CD_FORN   = @CD_DEST_ORIG     
            END
            IF @DEST_ORIG = 2 
            BEGIN 
                SELECT TOP 1 @INSC_SUFRAMA = D.INSC_SUFRAMA,
							@TIPO_ENTE_GOVERNAMENTAL = 0
                FROM 
                    PRC_FILIAL D 
                WHERE 
                    D.CD_EMP        = @CD_EMP
                    AND D.CD_FILIAL = @CD_DEST_ORIG         
            END


	/*Fim Suframa e Ente Governamental*/	
	
	SELECT 
            TOP 1 @FLAG_FRETE_BASE_ICMS=FLAG_FRETE_BASE_ICMS
        FROM 
             PRC_REGIAO_FISCAL_PRC_FILIAL INNER JOIN PRC_REGIAO_FISCAL ON
             PRC_REGIAO_FISCAL_PRC_FILIAL.CD_REGIAO_FISCAL = PRC_REGIAO_FISCAL.CD_REGIAO_FISCAL
        WHERE 
            PRC_REGIAO_FISCAL_PRC_FILIAL.CD_EMP = @CD_EMP 
            AND PRC_REGIAO_FISCAL_PRC_FILIAL.CD_FILIAL = @CD_FILIAL;
			

	SET @VLR_PRODUTO_TOTAL = (@QT_PROD*@VLR_IT)

	IF ISNULL(@VLR_DESC_IT,0) > 0
        BEGIN
            SET @VLR_PRODUTO_TOTAL = @VLR_PRODUTO_TOTAL - @VLR_DESC_IT  --CALCULANDO TOTAL PRODUTO
     END
	 
	 SET @VLR_PRODUTO_TOTAL = @VLR_PRODUTO_TOTAL + CASE WHEN @FLAG_FRETE_BASE_ICMS = 1 THEN (@VLR_FRETE * @QT_PROD) ELSE 0 END + @VLR_OUTRAS /*Copiado da fun  o de tributa  o*/
	 

	SET @VLR_FECOP = ROUND(@VLR_FECOP, 2);
	SET @VLR_ICMS = ROUND(@VLR_ICMS, 2);
	SET @VLR_PIS = ROUND(@VLR_PIS, 2);
	SET @VLR_COFINS = ROUND(@VLR_COFINS, 2);

	SET @VLR_BASE_CALC_IT = @VLR_PRODUTO_TOTAL - (ISNULL(@VLR_PIS,0) + ISNULL(@VLR_COFINS,0) + ISNULL(@VLR_ICMS,0) + ISNULL(@VLR_FECOP,0))
	
	
	DECLARE @EST_PROD_CPL_IBS_CBS AS TABLE (
		CD_FILIAL INT,
		CD_PROD INT,
		CD_TRIB_IBS_CBS INT,
		CD_MOV_OP INT,
		CD_REGIAO_FISCAL INT
	)
	
	
	DECLARE @EST_PROD_CPL_IBS_CBS_REG AS TABLE (
		CD_FILIAL INT,
		CD_PROD INT,
		CD_TRIB_IBS_CBS INT,
		CD_MOV_OP INT,
		CD_REGIAO_FISCAL INT
	)
	
	IF ISNULL(@INSC_SUFRAMA,'') <> '' AND EXISTS (SELECT 1 FROM EST_TRIB_IBS_CBS WHERE REGIAO_ZFM_ALC = 1)
	BEGIN
		INSERT INTO @EST_PROD_CPL_IBS_CBS
		SELECT PRF.CD_FILIAL,
			   @CD_PROD,
			   E.CD_TRIB_IBS_CBS,
			   @CD_MOV_OP,
			   PR.CD_REGIAO_FISCAL
		FROM EST_TRIB_IBS_CBS E
				INNER JOIN EST_TRIB_IBS_CBS_NCM N
					ON E.CD_TRIB_IBS_CBS = N.CD_TRIB_IBS_CBS
				INNER JOIN EST_PROD P
					ON P.NR_NCM = N.NR_NCM
				INNER JOIN EST_TRIB_IBS_CBS_GLB_MOV_OP G
					ON E.CD_TRIB_IBS_CBS = G.CD_TRIB_IBS_CBS
				INNER JOIN PRC_REGIAO_FISCAL PR
					ON PR.CD_REGIAO_FISCAL = E.CD_REGIAO_FISCAL
				INNER JOIN PRC_REGIAO_FISCAL_PRC_FILIAL PRF
					ON PRF.CD_REGIAO_FISCAL = E.CD_REGIAO_FISCAL
		WHERE P.CD_PROD = @CD_PROD
		AND G.CD_MOV_OP = @CD_MOV_OP
		AND E.STS_TABELA = 0
		AND E.REGIAO_ZFM_ALC = 1
		AND PRF.CD_FILIAL = @CD_FILIAL  
		
		INSERT INTO @EST_PROD_CPL_IBS_CBS_REG
		SELECT TOP 1
					PRF.CD_FILIAL,
					P.CD_PROD,
					E.CD_TRIB_IBS_CBS,
					G.CD_MOV_OP,
					PR.CD_REGIAO_FISCAL
				FROM EST_TRIB_IBS_CBS E
				INNER JOIN EST_TRIB_IBS_CBS_NCM N
					ON E.CD_TRIB_IBS_CBS = N.CD_TRIB_IBS_CBS
				INNER JOIN EST_PROD P
					ON P.NR_NCM = N.NR_NCM
				INNER JOIN EST_TRIB_IBS_CBS_GLB_MOV_OP G
					ON E.CD_TRIB_IBS_CBS = G.CD_TRIB_IBS_CBS
				INNER JOIN PRC_REGIAO_FISCAL PR
					ON PR.CD_REGIAO_FISCAL = E.CD_REGIAO_FISCAL
				INNER JOIN PRC_REGIAO_FISCAL_PRC_FILIAL PRF
					ON PRF.CD_REGIAO_FISCAL = E.CD_REGIAO_FISCAL
				OUTER APPLY (SELECT TOP 1 EXC.CD_PROD
							 FROM EST_TRIB_IBS_CBS_EST_PROD_EXCECAO EXC
							 INNER JOIN EST_TRIB_IBS_CBS_GLB_MOV_OP_EXCECAO MOVEX ON MOVEX.CD_TRIB_IBS_CBS = EXC.CD_TRIB_IBS_CBS
							 WHERE EXC.CD_TRIB_IBS_CBS = E.CD_TRIB_IBS_CBS
							 AND EXC.CD_PROD = @CD_PROD
							 AND EXC.CD_REGIAO_FISCAL = PR.CD_REGIAO_FISCAL
							 AND MOVEX.CD_MOV_OP = @CD_MOV_OP) EXC
		WHERE P.CD_PROD = @CD_PROD
		AND PRF.CD_FILIAL = @CD_FILIAL
		AND G.CD_MOV_OP = @CD_MOV_OP
		AND E.STS_TABELA = 0
		AND isnull(E.REGIAO_ZFM_ALC,0) = 0
		ORDER BY ISNULL(EXC.CD_PROD,0) DESC, E.CD_TRIB_IBS_CBS DESC
		
	END
	ELSE
	BEGIN
		INSERT INTO @EST_PROD_CPL_IBS_CBS
		SELECT TOP 1
					PRF.CD_FILIAL,
					P.CD_PROD,
					E.CD_TRIB_IBS_CBS,
					G.CD_MOV_OP,
					PR.CD_REGIAO_FISCAL
				FROM EST_TRIB_IBS_CBS E
				INNER JOIN EST_TRIB_IBS_CBS_NCM N
					ON E.CD_TRIB_IBS_CBS = N.CD_TRIB_IBS_CBS
				INNER JOIN EST_PROD P
					ON P.NR_NCM = N.NR_NCM
				INNER JOIN EST_TRIB_IBS_CBS_GLB_MOV_OP G
					ON E.CD_TRIB_IBS_CBS = G.CD_TRIB_IBS_CBS
				INNER JOIN PRC_REGIAO_FISCAL PR
					ON PR.CD_REGIAO_FISCAL = E.CD_REGIAO_FISCAL
				INNER JOIN PRC_REGIAO_FISCAL_PRC_FILIAL PRF
					ON PRF.CD_REGIAO_FISCAL = E.CD_REGIAO_FISCAL
				OUTER APPLY (SELECT TOP 1 EXC.CD_PROD
							 FROM EST_TRIB_IBS_CBS_EST_PROD_EXCECAO EXC
							 INNER JOIN EST_TRIB_IBS_CBS_GLB_MOV_OP_EXCECAO MOVEX ON MOVEX.CD_TRIB_IBS_CBS = EXC.CD_TRIB_IBS_CBS
							 WHERE EXC.CD_TRIB_IBS_CBS = E.CD_TRIB_IBS_CBS
							 AND EXC.CD_PROD = @CD_PROD
							 AND EXC.CD_REGIAO_FISCAL = PR.CD_REGIAO_FISCAL
							 AND MOVEX.CD_MOV_OP = @CD_MOV_OP) EXC
		WHERE P.CD_PROD = @CD_PROD
		AND PRF.CD_FILIAL = @CD_FILIAL
		AND G.CD_MOV_OP = @CD_MOV_OP
		AND E.STS_TABELA = 0
		AND isnull(E.REGIAO_ZFM_ALC,0) = 0
		ORDER BY ISNULL(EXC.CD_PROD,0) DESC, E.CD_TRIB_IBS_CBS DESC
	END
	
	
	
	DECLARE @PERC_RED_COMPRA_GOV MONEY
	
	IF @TIPO_ENTE_GOVERNAMENTAL > 0
	BEGIN
		
		SELECT TOP 1 @PERC_RED_COMPRA_GOV = CASE @TIPO_ENTE_GOVERNAMENTAL
													WHEN 1 THEN PERC_RED_COMPRA_GOV_UNIAO
													WHEN 2 THEN PERC_RED_COMPRA_GOV_ESTADO
													WHEN 3 THEN PERC_RED_COMPRA_GOV_DF
													WHEN 4 THEN PERC_RED_COMPRA_GOV_MUNICIPIO
											 ELSE NULL END
		FROM EST_TRIB_COMPRA_GOV
		WHERE STS_TABELA = 0
		
	END
	

INSERT INTO @RS_RETURN
    SELECT T.CD_TRIB_IBS_CBS,
        COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) AS CST_IBS_CBS,
        CASE WHEN EXC.CD_PROD IS NULL THEN T.CLASS_TRIB_IBS_CBS ELSE EXC.CLASS_TRIB_IBS_CBS END AS CLASS_TRIB_IBS_CBS,

		CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) = '410' THEN 0 ELSE ROUND(@VLR_BASE_CALC_IT,2) END AS VLR_BC_IBS_CBS,
        
		T.PERC_IBS_UF,
        T.PERC_IBS_UF_DIFERIMENTO AS PERC_IBS_UF_DIFERIMENTO,
		
		CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN
			ROUND(( COALESCE(EXC.PERC_IBS_UF_RED_EFET, T.PERC_IBS_UF_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_UF_DIFERIMENTO / 100,2) 
		ELSE
			ROUND((T.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_UF_DIFERIMENTO / 100,2)
		END AS VLR_IBS_UF_DIFERIMENTO,

        0 AS VLR_IBS_UF_DEV_TRIB,
        CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_UF_RED ELSE EXC.PERC_IBS_UF_RED END AS PERC_IBS_UF_RED,
        COALESCE(EXC.PERC_IBS_UF_RED_EFET, T.PERC_IBS_UF_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) AS PERC_IBS_UF_RED_EFET,
		
        CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN
			ROUND(((COALESCE(EXC.PERC_IBS_UF_RED_EFET, T.PERC_IBS_UF_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) * @VLR_BASE_CALC_IT) / 100)-
			((COALESCE(EXC.PERC_IBS_UF_RED_EFET, T.PERC_IBS_UF_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_UF_DIFERIMENTO / 100),2) 
		ELSE
			ROUND(((T.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100)-((T.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_UF_DIFERIMENTO / 100),2)
        END  AS VLR_IBS_UF,
		
		T.PERC_IBS_MUN,
        T.PERC_IBS_MUN_DIFERIMENTO AS PERC_IBS_MUN_DIFERIMENTO,
        
		CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN
			ROUND((T.PERC_IBS_MUN_RED_EFET * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_MUN_DIFERIMENTO / 100,2) 
		ELSE
			ROUND((T.PERC_IBS_MUN * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_MUN_DIFERIMENTO / 100,2)
		END AS VLR_IBS_MUN_DIFERIMENTO,
		
        0 AS VLR_IBS_MUN_DEV_TRIB,
        CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_MUN_RED ELSE EXC.PERC_IBS_MUN_RED END AS PERC_IBS_MUN_RED,
        COALESCE(EXC.PERC_IBS_MUN_RED_EFET,T.PERC_IBS_MUN_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) AS PERC_IBS_MUN_RED_EFET,
		
		CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN
			ROUND(((COALESCE(EXC.PERC_IBS_MUN_RED_EFET,T.PERC_IBS_MUN_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) * @VLR_BASE_CALC_IT) / 100)-
			((COALESCE(EXC.PERC_IBS_MUN_RED_EFET,T.PERC_IBS_MUN_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_MUN_DIFERIMENTO / 100),2) 
		ELSE
			ROUND(((T.PERC_IBS_MUN * @VLR_BASE_CALC_IT) / 100)-((T.PERC_IBS_MUN * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_MUN_DIFERIMENTO / 100),2)
        END  AS VLR_IBS_MUN,
		
		CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN
			ROUND((((COALESCE(EXC.PERC_IBS_UF_RED_EFET, T.PERC_IBS_UF_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END)* @VLR_BASE_CALC_IT) / 100)-
			((COALESCE(EXC.PERC_IBS_UF_RED_EFET, T.PERC_IBS_UF_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_UF_DIFERIMENTO / 100)) 
			+ (((COALESCE(EXC.PERC_IBS_MUN_RED_EFET,T.PERC_IBS_MUN_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END)* @VLR_BASE_CALC_IT) / 100)-
			((COALESCE(EXC.PERC_IBS_MUN_RED_EFET,T.PERC_IBS_MUN_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END)* @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_MUN_DIFERIMENTO / 100)),2) 
	   ELSE
			ROUND((((T.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100)-((T.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_UF_DIFERIMENTO / 100)) 
			+ (((T.PERC_IBS_MUN * @VLR_BASE_CALC_IT) / 100)-((T.PERC_IBS_MUN * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_MUN_DIFERIMENTO / 100)),2) 
	   END AS VLR_IBS,
	   
        T.PERC_CBS,
        T.PERC_CBS_DIFERIMENTO AS PERC_CBS_DIFERIMENTO,
       
	   	CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN
			ROUND((T.PERC_CBS_RED_EFET * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) * @VLR_BASE_CALC_IT) / 100 * T.PERC_CBS_DIFERIMENTO / 100,2) 
		ELSE
			ROUND((T.PERC_CBS * @VLR_BASE_CALC_IT) / 100 * T.PERC_CBS_DIFERIMENTO / 100,2)
		END AS VLR_CBS_DIFERIMENTO,
	   
        0 AS VLR_CBS_DEV_TRIB,
       CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_CBS_RED ELSE EXC.PERC_CBS_RED END AS PERC_CBS_RED,
       COALESCE(EXC.PERC_CBS_RED_EFET,T.PERC_CBS_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) AS PERC_CBS_RED_EFET,
		CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN
			ROUND(((COALESCE(EXC.PERC_CBS_RED_EFET,T.PERC_CBS_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) * @VLR_BASE_CALC_IT) / 100)-
			((COALESCE(EXC.PERC_CBS_RED_EFET,T.PERC_CBS_RED_EFET) * (CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN @PERC_RED_COMPRA_GOV ELSE 1 END) * @VLR_BASE_CALC_IT) / 100 * T.PERC_CBS_DIFERIMENTO / 100),2) 
		ELSE
			ROUND(((T.PERC_CBS * @VLR_BASE_CALC_IT) / 100)-((T.PERC_CBS * @VLR_BASE_CALC_IT) / 100 * T.PERC_CBS_DIFERIMENTO / 100),2)
        END  AS VLR_CBS,
		/*Reg para registros que s o suframa (Nota Regular)*/
		 ISNULL(REG.CD_TRIB_IBS_CBS,0) AS CD_TRIB_IBS_CBS_REG
		,ISNULL(REG.CST_IBS_CBS,'') AS CST_REG
		,ISNULL(REG.CLASS_TRIB_IBS_CBS,'') AS CLASS_TRIB_REG
		,ISNULL(REG.PERC_IBS_UF_RED_EFET,0) AS PERC_EFET_REG_IBS_UF
		,ISNULL(REG.VLR_IBS_UF,0) AS VLR_TRIB_REG_IBS_UF
		,ISNULL(REG.PERC_IBS_MUN_RED_EFET,0) AS PERC_EFET_REG_IBS_MUN
		,ISNULL(REG.VLR_IBS_MUN,0) AS VLR_TRIB_REG_IBS_MUN
		,ISNULL(REG.PERC_CBS_RED_EFET,0) AS PERC_EFET_REG_CBS
		,ISNULL(REG.VLR_CBS,0) AS VLR_TRIB_REG_CBS
		,CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN 
															CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN COALESCE(EXC.PERC_IBS_UF_RED_EFET, T.PERC_IBS_UF_RED_EFET)
															ELSE T.PERC_IBS_UF END
		 ELSE 0 END AS PERC_IBS_UF_COMPRA_GOV
		,CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN
															CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN
																ROUND(((COALESCE(EXC.PERC_IBS_UF_RED_EFET, T.PERC_IBS_UF_RED_EFET) * @VLR_BASE_CALC_IT) / 100)-
																((COALESCE(EXC.PERC_IBS_UF_RED_EFET, T.PERC_IBS_UF_RED_EFET) * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_UF_DIFERIMENTO / 100),2) 
																ELSE
																	ROUND(((T.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100)-((T.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_UF_DIFERIMENTO / 100),2)
																END
			ELSE 0 END AS VLR_TRIB_IBS_UF_COMPRA_GOV
		,CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN 
															CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN COALESCE(EXC.PERC_IBS_MUN_RED_EFET, T.PERC_IBS_MUN_RED_EFET)
															ELSE T.PERC_IBS_MUN END 
		 ELSE 0 END AS PERC_IBS_MUN_COMPRA_GOV 
		,CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN
															CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN
																ROUND(((COALESCE(EXC.PERC_IBS_MUN_RED_EFET,T.PERC_IBS_MUN_RED_EFET) * @VLR_BASE_CALC_IT) / 100)-
																((COALESCE(EXC.PERC_IBS_MUN_RED_EFET,T.PERC_IBS_MUN_RED_EFET) * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_MUN_DIFERIMENTO / 100),2) 
															ELSE
																ROUND(((T.PERC_IBS_MUN * @VLR_BASE_CALC_IT) / 100)-((T.PERC_IBS_MUN * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_MUN_DIFERIMENTO / 100),2)
															END
			ELSE 0 END AS VLR_TRIB_IBS_MUN_COMPRA_GOV
		,CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN
															CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN COALESCE(EXC.PERC_CBS_RED_EFET, T.PERC_CBS_RED_EFET)
															ELSE T.PERC_CBS END  
		 ELSE 0 END AS PERC_CBS_COMPRA_GOV
		, CASE WHEN ISNULL(@PERC_RED_COMPRA_GOV,0) > 0 THEN
																	CASE WHEN COALESCE(CST_EXC.CD_TRIB_CST, CST_CAB.CD_TRIB_CST) IN ('011','200','515') THEN
																		ROUND(((COALESCE(EXC.PERC_CBS_RED_EFET,T.PERC_CBS_RED_EFET) * @VLR_BASE_CALC_IT) / 100)-
																		((COALESCE(EXC.PERC_CBS_RED_EFET,T.PERC_CBS_RED_EFET) * @VLR_BASE_CALC_IT) / 100 * T.PERC_CBS_DIFERIMENTO / 100),2) 
																	ELSE
																		ROUND(((T.PERC_CBS * @VLR_BASE_CALC_IT) / 100)-((T.PERC_CBS * @VLR_BASE_CALC_IT) / 100 * T.PERC_CBS_DIFERIMENTO / 100),2)
																	END
			ELSE 0 END AS VLR_TRIB_CBS_COMPRA_GOV
		
     FROM @EST_PROD_CPL_IBS_CBS EST_PROD_CPL_IBS_CBS 
    INNER JOIN EST_TRIB_IBS_CBS T 
        ON EST_PROD_CPL_IBS_CBS.CD_TRIB_IBS_CBS = T.CD_TRIB_IBS_CBS
	LEFT JOIN GLB_TBL_IBS_CBS_CST CST_CAB ON CST_CAB.CD_TRIB = T.CD_TRIB
	OUTER APPLY (SELECT TOP 1 EXC.*
							 FROM EST_TRIB_IBS_CBS_EST_PROD_EXCECAO EXC
							 INNER JOIN EST_TRIB_IBS_CBS_GLB_MOV_OP_EXCECAO MOVEX ON MOVEX.CD_TRIB_IBS_CBS = EXC.CD_TRIB_IBS_CBS
							 WHERE EXC.CD_TRIB_IBS_CBS = T.CD_TRIB_IBS_CBS
							 AND EXC.CD_PROD = EST_PROD_CPL_IBS_CBS.CD_PROD 
							 AND EXC.CD_REGIAO_FISCAL = EST_PROD_CPL_IBS_CBS.CD_REGIAO_FISCAL
							 AND MOVEX.CD_MOV_OP = EST_PROD_CPL_IBS_CBS.CD_MOV_OP
				) EXC
	LEFT JOIN GLB_TBL_IBS_CBS_CST CST_EXC ON CST_EXC.CD_TRIB = EXC.CD_TRIB
	OUTER APPLY (SELECT TOP 1 T2.CD_TRIB_IBS_CBS,
							  COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) AS CST_IBS_CBS,
							  CASE WHEN EXC2.CD_PROD IS NULL THEN T2.CLASS_TRIB_IBS_CBS ELSE EXC2.CLASS_TRIB_IBS_CBS END AS CLASS_TRIB_IBS_CBS,
							  ----
							  CASE WHEN COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '011' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '200' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '515' THEN
								CASE WHEN EXC2.CD_PROD IS NULL THEN T2.PERC_IBS_UF_RED_EFET ELSE EXC2.PERC_IBS_UF_RED_EFET END 
							  ELSE 
								 T2.PERC_IBS_UF
							  END AS PERC_IBS_UF_RED_EFET,
							  ----
							  CASE WHEN COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '011' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '200' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '515' THEN
									ROUND(((CASE WHEN EXC2.CD_PROD IS NULL THEN T2.PERC_IBS_UF_RED_EFET ELSE EXC2.PERC_IBS_UF_RED_EFET END * @VLR_BASE_CALC_IT) / 100)-
									((CASE WHEN EXC2.CD_PROD IS NULL THEN T2.PERC_IBS_UF_RED_EFET ELSE EXC2.PERC_IBS_UF_RED_EFET END * @VLR_BASE_CALC_IT) / 100 * T2.PERC_IBS_UF_DIFERIMENTO / 100),2) 
								ELSE
									ROUND(((T2.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100)-((T2.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100 * T2.PERC_IBS_UF_DIFERIMENTO / 100),2)
								END  AS VLR_IBS_UF,
								----
								CASE WHEN COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '011' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '200' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '515' THEN
										CASE WHEN EXC2.CD_PROD IS NULL THEN T2.PERC_IBS_MUN_RED_EFET ELSE EXC2.PERC_IBS_MUN_RED_EFET  END
								ELSE 
									 T2.PERC_IBS_MUN
							    END AS PERC_IBS_MUN_RED_EFET,
							  ----
							  CASE WHEN COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '011' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '200' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '515' THEN
									ROUND(((CASE WHEN EXC2.CD_PROD IS NULL THEN T2.PERC_IBS_MUN_RED_EFET ELSE EXC2.PERC_IBS_MUN_RED_EFET END * @VLR_BASE_CALC_IT) / 100)-
									((CASE WHEN EXC2.CD_PROD IS NULL THEN T2.PERC_IBS_MUN_RED_EFET ELSE EXC2.PERC_IBS_MUN_RED_EFET END * @VLR_BASE_CALC_IT) / 100 * T2.PERC_IBS_MUN_DIFERIMENTO / 100),2) 
								ELSE
									ROUND(((T2.PERC_IBS_MUN * @VLR_BASE_CALC_IT) / 100)-((T2.PERC_IBS_MUN * @VLR_BASE_CALC_IT) / 100 * T2.PERC_IBS_MUN_DIFERIMENTO / 100),2)
								END  AS VLR_IBS_MUN,
								---
								CASE WHEN COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '011' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '200' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '515' THEN
										CASE WHEN EXC2.CD_PROD IS NULL THEN T2.PERC_CBS_RED_EFET ELSE EXC2.PERC_CBS_RED_EFET END
								ELSE 
									 T2.PERC_CBS
							    END AS PERC_CBS_RED_EFET,
								---
								CASE WHEN COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '011' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '200' OR COALESCE(CST_EXC2.CD_TRIB_CST, CST_CAB2.CD_TRIB_CST) = '515' THEN
									ROUND(((CASE WHEN EXC2.CD_PROD IS NULL THEN T2.PERC_CBS_RED_EFET ELSE EXC2.PERC_CBS_RED_EFET END * @VLR_BASE_CALC_IT) / 100)-
									((CASE WHEN EXC2.CD_PROD IS NULL THEN T2.PERC_CBS_RED_EFET ELSE EXC2.PERC_CBS_RED_EFET END * @VLR_BASE_CALC_IT) / 100 * T2.PERC_CBS_DIFERIMENTO / 100),2) 
								ELSE
									ROUND(((T2.PERC_CBS * @VLR_BASE_CALC_IT) / 100)-((T2.PERC_CBS * @VLR_BASE_CALC_IT) / 100 * T2.PERC_CBS_DIFERIMENTO / 100),2)
								END  AS VLR_CBS
				 FROM @EST_PROD_CPL_IBS_CBS_REG REG
				 INNER JOIN EST_TRIB_IBS_CBS T2 
										ON REG.CD_TRIB_IBS_CBS = T2.CD_TRIB_IBS_CBS
				LEFT JOIN GLB_TBL_IBS_CBS_CST CST_CAB2 ON CST_CAB2.CD_TRIB = T2.CD_TRIB

				OUTER APPLY (SELECT TOP 1 EXC2.*
							 FROM EST_TRIB_IBS_CBS_EST_PROD_EXCECAO EXC2
							 INNER JOIN EST_TRIB_IBS_CBS_GLB_MOV_OP_EXCECAO MOVEX2 ON MOVEX2.CD_TRIB_IBS_CBS = EXC2.CD_TRIB_IBS_CBS
							 WHERE EXC2.CD_TRIB_IBS_CBS = T2.CD_TRIB_IBS_CBS
							 AND EXC.CD_PROD = @CD_PROD 
							 AND EXC.CD_REGIAO_FISCAL = T2.CD_REGIAO_FISCAL
							 AND MOVEX2.CD_MOV_OP = @CD_MOV_OP
				) EXC2
				LEFT JOIN GLB_TBL_IBS_CBS_CST CST_EXC2 ON CST_EXC2.CD_TRIB = EXC2.CD_TRIB) REG /*Suframa*/

    RETURN;
END
GO


