/*******************************
P_IDTIPO:
   I -> ICMS NORMAL;
   C -> CARGA TRIBUTÁRIA
P_IDVALOR:
   C -> VALOR CONTÁBIL
   B -> BASE DE CÁLCULO
   A -> ALÍQUOTA
   I -> VALOR DO IMPOSTO
********************************/

CREATE OR REPLACE FUNCTION VALOR_IMPOSTO_NFE_CARGA(
   P_CDFILIAL       IN ITEMPRODUTO.CDFILIAL%TYPE,
   P_NRLANCTONF     IN ITEMPRODUTO.NRLANCTONF%TYPE,
   P_IDVALOR        IN ITEMPROIMPOS.IDVALORINFIT%TYPE,
   P_IDTIPO         IN ITEMPROIMPOS.IDVALORINFIT%TYPE,
   P_CDFISCAL       IN ITEMPROIMPOS.CDFISCALIT%TYPE DEFAULT NULL,
   P_NRITPROIMPOS   IN ITEMPROIMPOS.NRITPROIMPOS%TYPE DEFAULT '1') RETURN NUMBER
AS
   V_VRCONTABILNF IMPOSTONF.VRCONTABILNF%TYPE;
   V_VRBASECALCNF IMPOSTONF.VRBASECALCNF%TYPE;
   V_VRALIQNF     IMPOSTONF.VRALIQNF%TYPE;
   V_VRIMPOSTONF  IMPOSTONF.VRIMPOSTONF%TYPE;
BEGIN
   SELECT NVL(SUM(VRCONTABILNF),0),
          NVL(SUM(VRBASECALCNF),0),
          NVL(MAX(VRALIQNF),0),
          NVL(SUM(VRIMPOSTONF),0)
     INTO V_VRCONTABILNF, V_VRBASECALCNF, V_VRALIQNF, V_VRIMPOSTONF
     FROM (SELECT NVL(SUM(ROUND(IT.VRCONTITIMP,2)),0) AS VRCONTABILNF,
                  NVL(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0),2)),0) AS VRBASECALCNF,
                  NVL(MAX(DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0)),0) AS VRALIQNF,
                  NVL(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT,0),2)),0) AS VRIMPOSTONF
             FROM ITEMPROIMPOS IT, IMPOSTO IP
            WHERE IT.CDFILIAL     = P_CDFILIAL
              AND IT.NRLANCTONF   = P_NRLANCTONF
              AND IT.CDIMPOSTO    = IP.CDIMPOSTO
              AND IP.CDIMPOSTONFE = '00'
              AND P_IDTIPO        = 'I'
              AND IT.CDFISCALIT   = NVL(P_CDFISCAL, IT.CDFISCALIT)
              AND IT.NRITPROIMPOS = P_NRITPROIMPOS
              AND NOT EXISTS (SELECT *
                                FROM ITEMPROIMPOS I, IMPOSTO IM
                               WHERE I.CDFILIAL      = IT.CDFILIAL
                                 AND I.NRLANCTONF    = IT.NRLANCTONF
                                 AND I.NRSEQITPROD   = IT.NRSEQITPROD
                                 AND I.CDIMPOSTO     = IM.CDIMPOSTO
                                 AND IM.CDIMPOSTONFE = '03')
            UNION ALL
           SELECT NVL(SUM(ROUND(IT.VRCONTITIMP,2)),0) AS VRCONTABILNF,
                  NVL(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0),2)),0) AS VRBASECALCNF,
                  NVL(MAX(DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0)),0) AS VRALIQNF,
                  NVL(SUM(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT, 0),2)),0) AS VRIMPOSTONF
             FROM ITEMPROIMPOS IT, IMPOSTO IP
            WHERE IT.CDFILIAL     = P_CDFILIAL
              AND IT.NRLANCTONF   = P_NRLANCTONF
              AND IT.CDIMPOSTO    = IP.CDIMPOSTO
              AND IP.CDIMPOSTONFE = '03'
              AND IT.CDFISCALIT   = NVL(P_CDFISCAL, IT.CDFISCALIT)
              AND IT.NRITPROIMPOS = P_NRITPROIMPOS
              AND P_IDTIPO        = 'C'
            UNION ALL
           SELECT NVL(SUM(ROUND(IP.VRCONTABILNF,2)),0) AS VRCONTABILNF,
                  NVL(SUM(ROUND(IP.VRBASECALCNF,2)),0) AS VRBASECALCNF,
                  NVL(MAX(IP.VRALIQNF),0) AS VRALIQNF,
                  NVL(SUM(ROUND(IP.VRIMPOSTONF,2)), 0) AS VRIMPOSTONF
             FROM IMPOSTONF IP,IMPOSTO I, NOTAFISCAL N
            WHERE IP.CDFILIAL    = P_CDFILIAL
              AND IP.NRLANCTONF  = P_NRLANCTONF
              AND IP.CDFILIAL    = N.CDFILIAL
              AND IP.NRLANCTONF  = N.NRLANCTONF
              AND N.IDSISTEMA    = 'FIS'
      	     AND CDIMPOSTONFE   = '00'
              AND I.CDIMPOSTO    = IP.CDIMPOSTO
              AND CDFISCALNF     = NVL(P_CDFISCAL, CDFISCALNF)
              AND IP.IDIMPOSTONF = P_NRITPROIMPOS
              AND P_IDTIPO       = 'I'
            UNION ALL
           SELECT NVL(SUM(ROUND(IP.VRCONTABILNF,2)),0) AS VRCONTABILNF,
                  NVL(SUM(ROUND(IP.VRBASECALCNF,2)),0) AS VRBASECALCNF,
                  NVL(MAX(IP.VRALIQNF),0) AS VRALIQNF,
                  NVL(SUM(ROUND(IP.VRIMPOSTONF,2)), 0) AS VRIMPOSTONF
             FROM IMPOSTONF IP,IMPOSTO I, NOTAFISCAL N
            WHERE IP.CDFILIAL    = P_CDFILIAL
              AND IP.NRLANCTONF  = P_NRLANCTONF
              AND IP.CDFILIAL    = N.CDFILIAL
              AND IP.NRLANCTONF  = N.NRLANCTONF
              AND N.IDSISTEMA    = 'FIS'
              AND I.CDIMPOSTO    = IP.CDIMPOSTO
              AND CDIMPOSTONFE   = '03'
              AND CDFISCALNF     = NVL(P_CDFISCAL, CDFISCALNF)
              AND IP.IDIMPOSTONF = P_NRITPROIMPOS
              AND P_IDTIPO       = 'C');

   IF P_IDVALOR = 'A' THEN
      RETURN V_VRALIQNF;
   ELSIF P_IDVALOR = 'B' THEN
      RETURN V_VRBASECALCNF;
   ELSIF P_IDVALOR = 'I' THEN
      RETURN V_VRIMPOSTONF;
   ELSIF P_IDVALOR = 'C' THEN
      RETURN V_VRCONTABILNF;
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('VALOR_IMPOSTO_NFE_CARGA','0.00.000'); END;
/

