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
)
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)

    INSERT INTO @RS_RETURN
    SELECT 
        T.CD_TRIB_IBS_CBS,
        T.CST_IBS_CBS,
        T.CLASS_TRIB_IBS_CBS,
        @VLR_BASE_CALC_IT AS VLR_BC_IBS_CBS,
        T.PERC_IBS_UF,
        0 AS PERC_IBS_UF_DIFERIMENTO,
        0 AS VLR_IBS_UF_DIFERIMENTO,
        0 AS VLR_IBS_UF_DEV_TRIB,
        0 AS PERC_IBS_UF_RED,
        0 AS PERC_IBS_UF_RED_EFET,
        ROUND((T.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100,2) AS VLR_IBS_UF,
        T.PERC_IBS_MUN,
        0 AS PERC_IBS_MUN_DIFERIMENTO,
        0 AS VLR_IBS_MUN_DIFERIMENTO,
        0 AS VLR_IBS_MUN_DEV_TRIB,
        0 AS PERC_IBS_MUN_RED,
        0 AS PERC_IBS_MUN_RED_EFET,
       ROUND( (T.PERC_IBS_MUN * @VLR_BASE_CALC_IT) / 100,2) AS VLR_IBS_MUN,
       ROUND(((T.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100),2) + ROUND(((T.PERC_IBS_MUN * @VLR_BASE_CALC_IT) / 100),2) AS VLR_IBS,
        T.PERC_CBS,
        0 AS PERC_CBS_DIFERIMENTO,
        0 AS VLR_CBS_DIFERIMENTO,
        0 AS VLR_CBS_DEV_TRIB,
        0 AS PERC_CBS_RED,
        0 AS PERC_CBS_RED_EFET,
        ROUND((T.PERC_CBS * @VLR_BASE_CALC_IT) / 100,2) AS VLR_CBS
    FROM EST_PROD_CPL CPL
    INNER JOIN EST_TRIB_IBS_CBS T 
        ON CPL.CD_TRIB_IBS_CBS_VD = T.CD_TRIB_IBS_CBS
    WHERE CPL.CD_PROD = @CD_PROD
      AND CPL.CD_FILIAL = @CD_FILIAL;

    RETURN;
END
GO


