IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_TRIB_IBS_CBS]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[UFD_EST_PROD_TRIB_IBS_CBS]
GO 

CREATE FUNCTION [dbo].[UFD_EST_PROD_TRIB_IBS_CBS](
    @CD_EMP           INT,
    @CD_FILIAL        INT,
    @CD_PROD          INT,
    @QT_IT            MONEY,
    @VLR_BASE_ICMS_IT 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
    INSERT INTO @RS_RETURN
    SELECT 
        T.CD_TRIB_IBS_CBS,
        T.CST_IBS_CBS,
        T.CLASS_TRIB_IBS_CBS,
        -- Base já vem pronta (multiplicada pelo item)
        @VLR_BASE_ICMS_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,
        (T.PERC_IBS_UF * @VLR_BASE_ICMS_IT) / 100 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,
        (T.PERC_IBS_MUN * @VLR_BASE_ICMS_IT) / 100 AS VLR_IBS_MUN,
        ((T.PERC_IBS_UF * @VLR_BASE_ICMS_IT) / 100) + ((T.PERC_IBS_MUN * @VLR_BASE_ICMS_IT) / 100) 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,
        (T.PERC_CBS * @VLR_BASE_ICMS_IT) / 100 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
