CREATE OR REPLACE FUNCTION USR_VALOR_CONTABIL(
   P_CDFILIAL    IN ITEMPRODUTO.CDFILIAL%TYPE,
   P_NRLANCTONF  IN ITEMPRODUTO.NRLANCTONF%TYPE,
   P_NRSEQITPROD IN ITEMPRODUTO.NRSEQITPROD%TYPE)

RETURN NUMBER

IS

   V_VRCONTITEM  NUMBER;
   V_VRNOTAFISC  NUMBER;
   V_VRIMPOSTONF NUMBER;
   V_VRIMPOSTOT  NUMBER;
   V_DIFERENCA   NUMBER;
   V_NRSEQITPROD ITEMPRODUTO.NRSEQITPROD%TYPE;

BEGIN
   SELECT MIN(NRSEQITPROD)
   INTO V_NRSEQITPROD
   FROM ITEMPRODUTO
   WHERE CDFILIAL   = P_CDFILIAL
     AND NRLANCTONF = P_NRLANCTONF;

   IF P_NRSEQITPROD = V_NRSEQITPROD THEN
      V_VRCONTITEM := 0;
      FOR C_VRCONTITEM IN (SELECT USR_CONTCTBL_PROD(I.CDFILIAL, I.NRLANCTONF, P.CDPRODUTO, P.CDARVPROD) AS CDCONTPROD,
                                  NVL(ROUND(SUM(I.VRCONTITEM),2),0) AS VRCONTITEM
                           FROM ITEMPRODUTO I, PRODUTO P
                           WHERE I.CDFILIAL   = P_CDFILIAL
                             AND I.NRLANCTONF = P_NRLANCTONF
                             AND I.CDPRODUTO  = P.CDPRODUTO
                           GROUP BY USR_CONTCTBL_PROD(I.CDFILIAL, I.NRLANCTONF, P.CDPRODUTO, P.CDARVPROD)) LOOP

         V_VRCONTITEM := V_VRCONTITEM + C_VRCONTITEM.VRCONTITEM;
      END LOOP;

      SELECT NVL(ROUND(SUM(VRNOTAFISC),2),0)
      INTO V_VRNOTAFISC
      FROM NOTAFISCAL
      WHERE CDFILIAL   = P_CDFILIAL
        AND NRLANCTONF = P_NRLANCTONF;

      SELECT NVL(SUM(ROUND(VRIMPOSTOIT,2)),0)
      INTO V_VRIMPOSTONF
      FROM ITEMPROIMPOS II
      WHERE CDFILIAL   = P_CDFILIAL
        AND NRLANCTONF = P_NRLANCTONF
        AND USR_CONTCTBL_IMPOSTO(II.CDFILIAL, II.NRLANCTONF, II.CDIMPOSTO) IS NOT NULL;

      SELECT NVL(SUM(ROUND(VRTOTIMPONF,2)),0)
      INTO V_VRIMPOSTOT
      FROM IMPOSNFTOT I, IMPOSTO IP
      WHERE I.CDFILIAL    = P_CDFILIAL
        AND I.NRLANCTONF  = P_NRLANCTONF
        AND I.CDIMPOSTO   = IP.CDIMPOSTO
        AND IP.IDPRODNOTA = 'C'
        AND USR_CONTCTBL_IMPOSTO(I.CDFILIAL, I.NRLANCTONF, I.CDIMPOSTO) IS NOT NULL;

      V_DIFERENCA := ((V_VRCONTITEM + V_VRIMPOSTONF + V_VRIMPOSTOT) - (V_VRNOTAFISC));

      IF ABS(V_DIFERENCA) <= 0.02 THEN
         SELECT NVL(SUM(VRCONTITEM),0)
         INTO V_VRCONTITEM
         FROM ITEMPRODUTO
         WHERE CDFILIAL    = P_CDFILIAL
           AND NRLANCTONF  = P_NRLANCTONF
           AND NRSEQITPROD = P_NRSEQITPROD;

         V_VRCONTITEM := V_VRCONTITEM - V_DIFERENCA;
      END IF;
   ELSE
      SELECT NVL(SUM(VRCONTITEM),0)
      INTO V_VRCONTITEM
      FROM ITEMPRODUTO
      WHERE CDFILIAL    = P_CDFILIAL
        AND NRLANCTONF  = P_NRLANCTONF
        AND NRSEQITPROD = P_NRSEQITPROD;
   END IF;

   RETURN(V_VRCONTITEM);
END;
/


-- VERSAO --
BEGIN ATUVEROBJ('USR_VALOR_CONTABIL','4.07.014'); END;
/

