create or replace FUNCTION VALOR_IMPOSTO_NFE_REDUZ(
   P_CDFILIAL       IN ITEMPRODUTO.CDFILIAL%TYPE,
   P_NRLANCTONF     IN ITEMPRODUTO.NRLANCTONF%TYPE,
   P_CDIMPOSTONFE   IN IMPOSTO.CDIMPOSTO%TYPE,
   P_IDVALOR        IN ITEMPROIMPOS.IDVALORINFIT%TYPE,
   P_CDFISCAL       IN ITEMPROIMPOS.CDFISCALIT%TYPE DEFAULT NULL) RETURN NUMBER
AS
   V_VRBASECALCIT ITEMPROIMPOS.VRBASECALCIT%TYPE;
   V_VRALIQITEM   ITEMPROIMPOS.VRALIQITEM%TYPE;
   V_VRIMPOSTOIT  ITEMPROIMPOS.VRIMPOSTOIT%TYPE;
   V_VRPERREDBCIT ITEMPROIMPOS.VRPERREDBCIT%TYPE;
   V_VRCONTITIMPO ITEMPROIMPOS.VRCONTITIMP%TYPE;
   V_VRTOTAITEM   ITEMPRODUTO.VRTOTAITEM%TYPE;
   V_VRICMS       ITEMPROIMPOS.VRIMPOSTOIT%TYPE;
   V_CDEMPRESA    VARCHAR2(02);
   V_CDFILIMOVI   VARCHAR2(04);
   V_IDEXCICMSEMPR VARCHAR2(01);
   V_IDEXCICMSFILI VARCHAR2(01);
BEGIN

    BEGIN
      SELECT CDEMPRESA, CDFILIEMIT
        INTO V_CDEMPRESA, V_CDFILIMOVI
        FROM NOTAFISCAL
       WHERE CDFILIAL = P_CDFILIAL
         AND NRLANCTONF = P_NRLANCTONF;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          V_CDEMPRESA  := NULL;
          V_CDFILIMOVI := NULL;
    END;

    BEGIN
       SELECT NVL(E.IDEXCICMSBCPICO,'N'), NVL(F.IDEXCICMSBCPICO,'N')
         INTO V_IDEXCICMSEMPR, V_IDEXCICMSFILI
         FROM EMPRESA E, FILIAL F
        WHERE E.CDEMPRESA = V_CDEMPRESA
          AND F.CDFILIAL =  V_CDFILIMOVI;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          V_IDEXCICMSEMPR := 'N';
          V_IDEXCICMSFILI := 'N';
    END;

    IF (NVL(V_IDEXCICMSFILI,'N') = 'S') OR (NVL(V_IDEXCICMSEMPR,'N') = 'S') THEN
       FOR C_ITENS IN (
                       SELECT NVL(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',VRIMPOSTOIT,0),2)),0) AS VRICMS, NRSEQITPROD
                         FROM ITEMPROIMPOS IT, IMPOSTO IM
                        WHERE (IT.CDFILIAL     = P_CDFILIAL    ) AND
                              (IT.NRLANCTONF   = P_NRLANCTONF  ) AND
                              (IT.CDIMPOSTO    = IM.CDIMPOSTO  ) AND
                              (IM.CDIMPOSTONFE = '00'          ) AND
                              (IT.NRITPROIMPOS = '1'           ) 
                              GROUP BY NRSEQITPROD)LOOP
      V_VRICMS := C_ITENS.VRICMS;
      SELECT NVL(SUM(ROUND((VRBASECALCIT),2)),0),
             NVL(MAX(VRALIQITEM),0),
             NVL(MAX(VRPERREDBCIT),0),
             NVL(SUM(ROUND(VRCONTITIMP,2)),0)
        INTO V_VRBASECALCIT,
             V_VRALIQITEM,
             V_VRPERREDBCIT,
             V_VRCONTITIMPO
        FROM ITEMPROIMPOS IT, IMPOSTO IM
       WHERE (IT.CDFILIAL     = P_CDFILIAL    ) AND
             (IT.NRLANCTONF   = P_NRLANCTONF  ) AND
             (IT.CDIMPOSTO    = IM.CDIMPOSTO  ) AND
             (IM.CDIMPOSTONFE = P_CDIMPOSTONFE) AND
             (IT.NRITPROIMPOS = '1'           ) AND
             (IT.NRSEQITPROD   = C_ITENS.NRSEQITPROD) AND
             (IT.CDFISCALIT = NVL(P_CDFISCAL, IT.CDFISCALIT));


      V_VRBASECALCIT := NVL(V_VRBASECALCIT,0) - NVL(V_VRICMS,0);
      V_VRIMPOSTOIT := NVL(V_VRIMPOSTOIT,0) + ROUND((V_VRBASECALCIT*V_VRALIQITEM)/100,2);

       END LOOP;

    ELSE
      SELECT NVL(SUM(ROUND(VRBASECALCIT,2)),0),
             NVL(MAX(VRALIQITEM),0),
             NVL(SUM(ROUND(VRIMPOSTOIT,2)),0),
             NVL(MAX(VRPERREDBCIT),0),
             NVL(SUM(ROUND(VRCONTITIMP,2)),0)
        INTO V_VRBASECALCIT,
             V_VRALIQITEM,
             V_VRIMPOSTOIT,
             V_VRPERREDBCIT,
             V_VRCONTITIMPO
        FROM ITEMPROIMPOS IT, IMPOSTO IM
       WHERE (IT.CDFILIAL     = P_CDFILIAL    ) AND
             (IT.NRLANCTONF   = P_NRLANCTONF  ) AND
             (IT.CDIMPOSTO    = IM.CDIMPOSTO  ) AND
             (IM.CDIMPOSTONFE = P_CDIMPOSTONFE) AND
             (IT.NRITPROIMPOS = '1'           ) AND
             (IT.CDFISCALIT = NVL(P_CDFISCAL, IT.CDFISCALIT));

    END IF;

      IF P_IDVALOR = 'A' THEN
         RETURN(NVL(V_VRALIQITEM,0));
      ELSIF P_IDVALOR = 'B' THEN
         RETURN(NVL(V_VRBASECALCIT,0));
      ELSIF P_IDVALOR = 'I' THEN
         RETURN(NVL(V_VRIMPOSTOIT,0));
      ELSIF P_IDVALOR = 'P' THEN
         RETURN(NVL(V_VRPERREDBCIT,0));
      ELSIF P_IDVALOR = 'C' THEN
         RETURN(NVL(V_VRCONTITIMPO,0));
      END IF;
END;
/