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_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),
    CLASS_TRIB_IBS_CBS        VARCHAR(50),
    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
)
AS
BEGIN

	DECLARE @VLR_BASE_CALC_IT MONEY = 0
	DECLARE @VLR_PRODUTO_TOTAL MONEY = 0
	DECLARE @FLAG_FRETE_BASE_ICMS INT = 0
	
	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 - (@VLR_PIS + @VLR_COFINS + @VLR_ICMS + @VLR_FECOP)
	
	
	DECLARE @EST_PROD_CPL_IBS_CBS AS TABLE (
		CD_FILIAL INT,
		CD_PROD INT,
		CD_TRIB_IBS_CBS INT,
		CD_MOV_OP INT
	)
	
	
	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
			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
						 WHERE EXC.CD_TRIB_IBS_CBS = E.CD_TRIB_IBS_CBS
						 AND EXC.CD_PROD = @CD_PROD) 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
	ORDER BY ISNULL(EXC.CD_PROD,0) DESC, E.CD_TRIB_IBS_CBS DESC
	

INSERT INTO @RS_RETURN
    SELECT 
        T.CD_TRIB_IBS_CBS,
        T.CST_IBS_CBS,
        CASE WHEN EXC.CD_PROD IS NULL THEN T.CLASS_TRIB_IBS_CBS ELSE T.CLASS_TRIB_IBS_CBS_EXC END AS CLASS_TRIB_IBS_CBS,

        CASE WHEN T.CST_IBS_CBS = '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 T.CST_IBS_CBS = '011' OR T.CST_IBS_CBS = '200' OR T.CST_IBS_CBS = '515' THEN
			ROUND(( CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_UF_RED_EFET ELSE PERC_IBS_UF_RED_EFET_EXC 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 PERC_IBS_UF_RED_EXC END AS PERC_IBS_UF_RED,
        CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_UF_RED_EFET ELSE PERC_IBS_UF_RED_EFET_EXC END AS PERC_IBS_UF_RED_EFET,
		
        CASE WHEN T.CST_IBS_CBS = '011' OR T.CST_IBS_CBS = '200' OR T.CST_IBS_CBS = '515' THEN
			ROUND(((CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_UF_RED_EFET ELSE PERC_IBS_UF_RED_EFET_EXC END * @VLR_BASE_CALC_IT) / 100)-
			((CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_UF_RED_EFET ELSE PERC_IBS_UF_RED_EFET_EXC 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 T.CST_IBS_CBS = '011' OR T.CST_IBS_CBS = '200' OR T.CST_IBS_CBS = '515' THEN
			ROUND((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_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 PERC_IBS_MUN_RED_EXC END AS PERC_IBS_MUN_RED,
        CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_MUN_RED_EFET ELSE PERC_IBS_MUN_RED_EFET_EXC END AS PERC_IBS_MUN_RED_EFET,
		
		CASE WHEN T.CST_IBS_CBS = '011' OR T.CST_IBS_CBS = '200' OR T.CST_IBS_CBS = '515' THEN
			ROUND(((CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_MUN_RED_EFET ELSE PERC_IBS_MUN_RED_EFET_EXC END * @VLR_BASE_CALC_IT) / 100)-
			((CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_MUN_RED_EFET ELSE PERC_IBS_MUN_RED_EFET_EXC 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 T.CST_IBS_CBS = '011' OR T.CST_IBS_CBS = '200' OR T.CST_IBS_CBS = '515' THEN
			ROUND((((CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_UF_RED_EFET ELSE PERC_IBS_UF_RED_EFET_EXC END * @VLR_BASE_CALC_IT) / 100)-
			((CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_UF_RED_EFET ELSE PERC_IBS_UF_RED_EFET_EXC END * @VLR_BASE_CALC_IT) / 100 * T.PERC_IBS_UF_DIFERIMENTO / 100)) 
			+ (((CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_MUN_RED_EFET ELSE PERC_IBS_MUN_RED_EFET_EXC END * @VLR_BASE_CALC_IT) / 100)-
			((CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_IBS_MUN_RED_EFET ELSE PERC_IBS_MUN_RED_EFET_EXC 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 T.CST_IBS_CBS = '011' OR T.CST_IBS_CBS = '200' OR T.CST_IBS_CBS = '515' THEN
			ROUND((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_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 PERC_CBS_RED_EXC END AS PERC_CBS_RED,
       CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_CBS_RED_EFET ELSE PERC_CBS_RED_EFET_EXC END AS PERC_CBS_RED_EFET,
		CASE WHEN T.CST_IBS_CBS = '011' OR T.CST_IBS_CBS = '200' OR T.CST_IBS_CBS = '515' THEN
			ROUND(((CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_CBS_RED_EFET ELSE PERC_CBS_RED_EFET_EXC END * @VLR_BASE_CALC_IT) / 100)-
			((CASE WHEN EXC.CD_PROD IS NULL THEN T.PERC_CBS_RED_EFET ELSE PERC_CBS_RED_EFET_EXC 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
		
    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 EST_TRIB_IBS_CBS_EST_PROD_EXCECAO EXC
		ON EXC.CD_PROD =  EST_PROD_CPL_IBS_CBS.CD_PROD AND  EXC.CD_TRIB_IBS_CBS = T.CD_TRIB_IBS_CBS


    RETURN;
END
GO


