IF EXISTS (SELECT *
           FROM SYSOBJECTS O, SYSUSERS U
           WHERE O.NAME = 'VALOR_IMPOSTO_ITEM_AF' AND
                 TYPE = 'FN' AND
                 U.UID = O.UID  AND
                 U.NAME = USER)
DROP FUNCTION VALOR_IMPOSTO_ITEM_AF
GO
CREATE FUNCTION VALOR_IMPOSTO_ITEM_AF
   (@P_CDFILDEST   VARCHAR(04),
    @P_CDFILLANC   VARCHAR(04),
    @P_NRAUTOFORN  VARCHAR(10),
    @P_DTPROGENTAF DATETIME,
    @P_CDPRODUTO   VARCHAR(10),
    @P_CDMARCA     VARCHAR(05)) RETURNS FLOAT
AS
BEGIN
   DECLARE @V_VRIMPITMAF DOUBLE PRECISION;

   SET @V_VRIMPITMAF = 0;

   SELECT @V_VRIMPITMAF = SUM(
                              CASE IP.IDTRATIMPO
                              WHEN 'A'
                              THEN VRIMPITMAF
                              ELSE (VRIMPITMAF * -1)END
                              * ((IT.QTPROGENTAF - ISNULL(IT.QTEFETENTRAF,0)) / IT.QTPROGENTAF))
   FROM IMPITMAF I, IMPOSTO IP, ITEMAUTF IT
   WHERE (I.CDFILDEST   = @P_CDFILDEST)    AND
         (I.CDFILLANC   = @P_CDFILLANC)    AND
         (I.NRAUTOFORN  = @P_NRAUTOFORN)   AND
         (I.DTPROGENTAF = @P_DTPROGENTAF)  AND
         (I.CDPRODUTO   = ISNULL(@P_CDPRODUTO,I.CDPRODUTO))   AND
         (I.CDMARCA     = ISNULL(@P_CDMARCA,I.CDMARCA))     AND
         (IDTRATIMPO IN ('A', 'D'))       AND
         (I.CDFILDEST   = IT.CDFILDEST)   AND
         (I.CDFILLANC   = IT.CDFILLANC)   AND
         (I.NRAUTOFORN  = IT.NRAUTOFORN)  AND
         (I.DTPROGENTAF = IT.DTPROGENTAF) AND
         (I.CDPRODUTO   = IT.CDPRODUTO)   AND
         (I.CDMARCA     = IT.CDMARCA)     AND
         (IT.QTPROGENTAF <> 0)            AND
         (I.CDIMPOSTO = IP.CDIMPOSTO)     AND
         ISNULL(IP.IDACRPRODENTRNF, 'N') = 'N';

   RETURN(@V_VRIMPITMAF);
END
GO

-- VERSAO --
EXEC ATUVEROBJ 'VALOR_IMPOSTO_ITEM_AF','5.05.022';
GO

