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_MOV_OP INT
	,@CD_DEST_ORIG INT
	,@CD_PROD INT
	,@QT_PROD INT
	,@VLR_IT MONEY
	,@PERC_DESC_IT MONEY
	,@VLR_FRETE MONEY
	,@VLR_OUTRAS MONEY
	,@VLR_BASE_PIS_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 @ABATER_ICMS_DESONERADO_VALOR_NOTA_FISCAL INT = 0 
	DECLARE @VLR_ICMS MONEY = 0
	DECLARE @VLR_PIS MONEY = 0
	DECLARE @VLR_COFINS MONEY = 0
	DECLARE @VLR_ICMS_DESONERADO MONEY = 0
	DECLARE @VLR_BASE_CALC_IT MONEY = 0
	DECLARE @VLR_PRODUTO_TOTAL MONEY = 0
	DECLARE @VLR_DESC_IT MONEY = 0
	declare @VLR_FECOP MONEY = 0
	DECLARE @PERC_PIS MONEY = 0
	DECLARE @PERC_COFINS MONEY = 0

	SET @VLR_PRODUTO_TOTAL = (@QT_PROD*@VLR_IT)

	IF @PERC_DESC_IT > 0
        BEGIN
            SET @VLR_DESC_IT = ((@VLR_IT*@PERC_DESC_IT)/100.00)
            SET @VLR_PRODUTO_TOTAL = (@QT_PROD*@VLR_IT) - ((@QT_PROD*@VLR_IT)*(@PERC_DESC_IT/100.00))  --CALCULANDO TOTAL PRODUTO
     END
	
	--SET @ABATER_ICMS_DESONERADO_VALOR_NOTA_FISCAL = ISNULL((SELECT ISNULL(CFG.VALOR,0) AS VALOR
	--														FROM PRC_FILIAL_CONFIG CFG
	--														WHERE CFG.CD_CHAVE = 'ABATER_ICMS_DESONERADO_VALOR_NOTA_FISCAL'
	--														AND CFG.CD_FILIAL = @CD_FILIAL),0) /*Ja faz na função*/

	

	SELECT @VLR_ICMS = VLR_ICMS,
		   @PERC_PIS = PERC_PIS,
		   @PERC_COFINS = PERC_COFINS,
		   @VLR_ICMS_DESONERADO = VLR_ICMS_DESONERADO,
		   @VLR_FECOP = VLR_FECOP
	FROM [dbo].[UFD_EST_PROD_TRIB] (@CD_EMP, 
										@CD_FILIAL,
										@CD_MOV_OP, 
										@CD_DEST_ORIG, 
										@CD_PROD, 
										0, 
										0, 
										0,
										@QT_PROD,
										@VLR_IT,
										@PERC_DESC_IT,
										0,
										@VLR_FRETE,
										@VLR_OUTRAS,
										0,
										0
										) U

	SET @VLR_PIS = ROUND((@VLR_BASE_PIS_COFINS * @PERC_PIS) / 100.00, 4);
	SET @VLR_COFINS = ROUND((@VLR_BASE_PIS_COFINS * @PERC_COFINS) / 100.00, 4);

	SET @VLR_BASE_CALC_IT = @VLR_PRODUTO_TOTAL - (@VLR_PIS + @VLR_COFINS + @VLR_ICMS + @VLR_FECOP + @VLR_ICMS_DESONERADO)

    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,
        (T.PERC_IBS_UF * @VLR_BASE_CALC_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_CALC_IT) / 100 AS VLR_IBS_MUN,
        ((T.PERC_IBS_UF * @VLR_BASE_CALC_IT) / 100) + ((T.PERC_IBS_MUN * @VLR_BASE_CALC_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_CALC_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


