CREATE OR REPLACE PROCEDURE GERA_INFADIC_BENEFICIO(
   P_NRSEQUMAPAPU IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_CDIMPOSTO    IN MAPAAPURAC.CDIMPOSTO%TYPE,
   P_CDEMPRESA    IN MAPAAPURAC.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN  MAPAAPURAC.CDINSCESTA%TYPE)
AS
   V_NRSEQINFADIC    NUMBER;
   V_VRINFADICNF     NUMBER;
   V_VRINFADICTOT    NUMBER;
   V_CODINFADIC      VARCHAR2(10);
   V_DSINFADIC       VARCHAR2(255);
   V_CDCHAVENOTA     VARCHAR2(15);
   V_NRSEQITMAPR     VARCHAR2(10);
   V_NRSEQDETITMAPA  VARCHAR2(20);
   V_CDBENEFICIO     VARCHAR2(15);
BEGIN

   /******** DELETA OS REGISTROS NA TABELA, DEVIDO A REAPURACAO DE MAPA ********/
   DELETE FROM INFADICMAPA
    WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
      AND IDORIGEMINF  = 'B';

   /******** BUSCA O MAIOR SEQUENCIAL DE INFORMACOES ADICIONAIS UTILIZADO NO MAPA ********/
   BEGIN
      SELECT MAX(NRSEQINFADIC)
        INTO V_NRSEQINFADIC
        FROM INFADICMAPA
       WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_NRSEQINFADIC := 0;
   END;

   V_CODINFADIC := NULL;
   /******** BUSCA AS NOTAS FISCAIS PRESENTES NO MAPA QUE TENHAM O CODIGO DE BENEFICIO FISCAL PREENCHIDO ********/
   FOR C_NF IN (SELECT IT.CDFILIAL, IT.NRLANCTONF, SUM(ROUND(IT.VRCONTITIMP,2)) AS VRCONTABIL,
                       IT.VRALIQITEM, IT.CDBENEFFISCAL, TB.IDBENEFICIO, TB.DSBENEFICIO
                  FROM TPBENEFICIOFISC TB, ITEMPROIMPOS IT, IMPOSTO IM, NOTAFISCAL NF,
                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                          FROM IMPOSTONF I
                         WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU) IP
                 WHERE IP.CDFILIAL      = NF.CDFILIAL
                   AND IP.NRLANCTONF    = NF.NRLANCTONF
                   AND NF.CDFILIAL      = IT.CDFILIAL
                   AND NF.NRLANCTONF    = IT.NRLANCTONF
                   AND IT.CDIMPOSTO     = IM.CDIMPOSTO
                   AND IM.CDIMPOSTONFE  = P_CDIMPOSTO
                   AND NF.IDENTRSAIDOP  = 'S'
                   AND IT.CDBENEFFISCAL = TB.CDBENEFICIO
                   AND NF.CDEMPRESA     = TB.CDEMPRESA
                   AND NF.CDINSCESTA    = TB.CDINSCESTA
                   AND TB.IDBENEFICIO   = 'IOP'
                   AND IT.VRALIQITEM    > 0
                 GROUP BY IT.CDFILIAL, IT.NRLANCTONF, IT.VRALIQITEM, IT.CDBENEFFISCAL, TB.IDBENEFICIO, TB.DSBENEFICIO
                 ORDER BY TB.IDBENEFICIO, IT.CDBENEFFISCAL) LOOP

       IF V_CODINFADIC IS NULL THEN
          V_CODINFADIC := C_NF.CDBENEFFISCAL;
          V_DSINFADIC  := C_NF.DSBENEFICIO;
       END IF;

       /***** CALCULA O VAOR DO BENEFICIO, QUANDO O MESMO FOR ISENCAO DE OPERACOES COM ORGAO PUBLICO *****/
       IF C_NF.IDBENEFICIO = 'IOP' THEN
          V_VRINFADICNF := ROUND((C_NF.VRCONTABIL * 100 / (100 - C_NF.VRALIQITEM)) - C_NF.VRCONTABIL,2);
       END IF;

       /***** QUANDO TROCAR O CODIGO DO BENEFICIO, INSERE OS DADOS NA TABELA REFERENTE A TOTALIZACAO DO BENEFICIO ANTERIOR *****/
       IF (V_CODINFADIC <> C_NF.CDBENEFFISCAL) THEN
          IF V_VRINFADICTOT > 0 THEN
             V_NRSEQINFADIC := NVL(V_NRSEQINFADIC,0) + 1;
             INSERT INTO INFADICMAPA
                (NRSEQUMAPAPU, NRSEQINFADIC, IDORIGEMINF, CDINFADICMAPA, DSINFADICMAPA, VRINFADICMAPA)
             VALUES
                (P_NRSEQUMAPAPU, LPAD(V_NRSEQINFADIC,5,'0'), 'B', V_CODINFADIC, V_DSINFADIC, V_VRINFADICTOT);
             V_VRINFADICTOT := NVL(V_VRINFADICNF,0);
             V_CODINFADIC   := NULL;
             V_DSINFADIC    := NULL;
          END IF;
       ELSE
          V_VRINFADICTOT := NVL(V_VRINFADICTOT,0) + V_VRINFADICNF;
       END IF;
   END LOOP;

   /***** AO TERMINAR O LOOP, INSERE O VALOR TOTAL DO ULTIMO BENEFICIO *****/
   IF V_VRINFADICTOT > 0 THEN
      V_NRSEQINFADIC := NVL(V_NRSEQINFADIC,0) + 1;
      INSERT INTO INFADICMAPA
         (NRSEQUMAPAPU, NRSEQINFADIC, IDORIGEMINF, CDINFADICMAPA, DSINFADICMAPA, VRINFADICMAPA)
      VALUES
         (P_NRSEQUMAPAPU, LPAD(V_NRSEQINFADIC,5,'0'), 'B', V_CODINFADIC, V_DSINFADIC, V_VRINFADICTOT);
   END IF;

   /******** Deleta da ITMAPARECUP as NFs que vieram da IMPOSTONF que possuem valores informativos,
             gerados de forma automatica pela rotina ********/
   DELETE ITMAPARECUP
    WHERE NRSEQUMAPAPU     = P_NRSEQUMAPAPU
      AND IDTIPOITMAPR     = 'VI'
      AND IDAJUSTEAPURAUTO = 'S';

   /******** Busca o ultimo sequencial na ITMAPARECUP ********/
   V_NRSEQITMAPR := 0;
   BEGIN
      SELECT MAX(NRSEQITMAPR) AS NRSEQITMAPR
        INTO V_NRSEQITMAPR
        FROM ITMAPARECUP
       WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
         AND IDTIPOITMAPR = 'VI';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_NRSEQITMAPR := 0;
   END;

   /*Verifica se existe NF-e ou NFC-e/SAT com codigo do beneficio fiscal de desoneracao*/
   V_CDCHAVENOTA  := '';
   V_CDBENEFICIO  := '';
   FOR C_NF IN (SELECT IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD,
                       '' AS CDFILIALVENDA, '' AS CDCAIXA, '' AS NRSEQVENDA,
                       '' AS NRSEQUITVEND, IT.VRALIQITEM+NVL(IT.VRADICALIQFECP,0) AS VRALIQUOTA,
                       SUM(ROUND(IT.VRCONTITIMP,2) +
                           ROUND(VALOR_REDUCAO(IT.CDFILIAL, IT.NRLANCTONF,IT.CDSITUTRIBIT, IT.NRSEQITPROD, IT.CDFISCOPERIT, '', IP.CDMODDOCFISC, 'N'),2)) AS VRCONTABIL,
                       IT.CDIMPOSTO, IT.NRITPROIMPOS, IP.IDSISTEMA, SUM(VRIMPOSTODESON) AS VRIMPICMSDES, TP.CDBENEFICIO AS CDBENEE115,
                       TP.CDAJUSTENF AS CDBENEC197, TP.DSBENEFICIO
                  FROM ITEMPROIMPOS IT, IMPOSTO IM, TPBENEFICIOFISC TP,
                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF, N.IDSISTEMA, N.CDMODDOCFISC,
                               N.CDEMPRESA, N.CDINSCESTA, N.IDENTRSAIDOP||N.CDTIPOOPER AS IDCHAVEOPER
                          FROM IMPOSTONF I, NOTAFISCAL N
                         WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU
                           AND I.CDFILIAL     = N.CDFILIAL
                           AND I.NRLANCTONF   = N.NRLANCTONF
                           AND N.IDSISTEMA    <> 'FIS') IP
                 WHERE IT.CDFILIAL      = IP.CDFILIAL
                   AND IT.NRLANCTONF    = IP.NRLANCTONF
                   AND IT.CDIMPOSTO     = IM.CDIMPOSTO
                   AND IM.CDIMPOSTONFE  = P_CDIMPOSTO
                   AND IT.NRITPROIMPOS  = '1'
                   AND TP.CDBENEFICIO   = IT.CDBENEFFISCAL
                   AND TP.CDEMPRESA     = IP.CDEMPRESA
                   AND TP.CDINSCESTA    = IP.CDINSCESTA
                   AND ((INSTR(TP.CDTPOPBENEFICIO, '_' ||IP.IDCHAVEOPER||'_' ) > 0) OR (TP.CDTPOPBENEFICIO IS NULL))
                 GROUP BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.VRALIQITEM+NVL(IT.VRADICALIQFECP,0),
                       IT.CDIMPOSTO, IT.NRITPROIMPOS, IP.IDSISTEMA, TP.CDBENEFICIO, TP.CDAJUSTENF, TP.DSBENEFICIO
                 UNION ALL
                SELECT IP.CDFILIALNOTA AS CDFILIAL, IP.NRLANCTONF, '' AS NRSEQITPROD,
                       IP.CDFILIALVENDA, IP.CDCAIXA, IP.NRSEQVENDA, IT.NRSEQUITVEND, IT.VRALIQICMSDES AS VRALIQUOTA,
                       SUM(ROUND(IT.VRBASECALCICMS,2)) AS VRCONTABIL, IT.CDIMPOSTO, '' AS NRITPROIMPOS, IP.IDSISTEMA,
                       SUM(IT.VRIMPICMSDES) AS VRIMPICMSDES, TP.CDBENEFICIO AS CDBENEE115,
                       TP.CDAJUSTENF AS CDBENEC197, TP.DSBENEFICIO
                  FROM ITEMVENDA IV, ITVENDAIMPOS IT, IMPOSTO IM, TPBENEFICIOFISC TP,
                       (SELECT DISTINCT R.CDFILIALNOTA, R.NRLANCTONF, R.CDFILIALVENDA, R.CDCAIXA, R.NRSEQVENDA, N.IDSISTEMA,
                               N.CDEMPRESA, N.CDINSCESTA, N.IDENTRSAIDOP||N.CDTIPOOPER AS IDCHAVEOPER
                          FROM IMPOSTONF I, NOTAFISCAL N,  RELACNFCEAPROV R
                         WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU
                           AND I.CDFILIAL     = N.CDFILIAL
                           AND I.NRLANCTONF   = N.NRLANCTONF
                           AND N.IDSISTEMA    = 'FIS'
                           AND N.CDFILIAL     = R.CDFILIALNOTA
                           AND N.NRLANCTONF   = R.NRLANCTONF) IP
                 WHERE IT.CDFILIAL      = IP.CDFILIALVENDA
                   AND IT.CDCAIXA       = IP.CDCAIXA
                   AND IT.NRSEQVENDA    = IP.NRSEQVENDA
                   AND IT.CDFILIAL      = IV.CDFILIAL
                   AND IT.CDCAIXA       = IV.CDCAIXA
                   AND IT.NRSEQVENDA    = IV.NRSEQVENDA
                   AND IT.NRSEQUITVEND  = IV.NRSEQUITVEND
                   AND IT.CDIMPOSTO     = IM.CDIMPOSTO
                   AND IM.CDIMPOSTONFE  = P_CDIMPOSTO
                   AND TP.CDBENEFICIO   = IV.CDCBENEFIT
                   AND TP.CDEMPRESA     = IP.CDEMPRESA
                   AND TP.CDINSCESTA    = IP.CDINSCESTA
                   AND ((INSTR(TP.CDTPOPBENEFICIO, '_' ||IP.IDCHAVEOPER||'_' ) > 0) OR (TP.CDTPOPBENEFICIO IS NULL))
                 GROUP BY IP.CDFILIALNOTA, IP.NRLANCTONF, IP.CDFILIALVENDA, IP.CDCAIXA, IP.NRSEQVENDA,
                       IT.NRSEQUITVEND, IT.VRALIQICMSDES, IT.CDIMPOSTO, IP.IDSISTEMA, TP.CDBENEFICIO,
                       TP.CDAJUSTENF, TP.DSBENEFICIO
                 ORDER BY 14, 1, 2 /*CDBENEFICIO, CDFILIAL, NRLANCTONF*/) LOOP

       /*SOMA OS VALORES DO ICMS DESONERADO*/
       /*Valor do ICMS desonerado = Preco na Nota Fiscal * (1 - (Aliquota * (1 - Percentual de reducao da BC))) / (1 - Aliquota) - Preco na Nota Fiscal*/

       /*QUANDO FOR UMA NOVA NOTA, INSERE OS DADOS DO AJUSTE DA NOTA*/
       IF NVL(V_CDCHAVENOTA,' ') <> C_NF.CDFILIAL||C_NF.NRLANCTONF THEN
          /*INSERE O REGISTRO DE AJUSTE PARA GERAR O C197/D197*/
          V_NRSEQITMAPR := NVL(V_NRSEQITMAPR,0) + 1;
          INSERT INTO ITMAPARECUP
             (NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, DSITEMMAPR, VRITEMMAPR, CDITEMOCOR, DSITEMOCOR,
              DSFLEGALOCOR, NRLANCTONF, CDFILIAL, CDAJUSTEITMAPA, CDAJUSTEITSEF, CDITPROCEDENCIA,
              IDCREDPRESUMIDO, IDDIFALMANUAL, IDCARGATRIBUTARIA, IDCOMPOEC197, CDCFOPPADRAO,
              VROUTROSITMAPR, IDAJUSTEAPURAUTO)
          VALUES
             (P_NRSEQUMAPAPU, 'VI', LPAD(V_NRSEQITMAPR,10,'0'), C_NF.CDBENEE115, 0, '', '',
              '', C_NF.NRLANCTONF, C_NF.CDFILIAL, C_NF.CDBENEC197, '', '',
              'N', 'N', 'N', 'I', '',
              C_NF.VRIMPICMSDES, 'S');
          V_CDCHAVENOTA := C_NF.CDFILIAL||C_NF.NRLANCTONF;
       /*QUANDO FOR A MESMA NOTA, ATUALIZA OS VALORES DO AJUSTE*/
       ELSE
          UPDATE ITMAPARECUP
             SET VROUTROSITMAPR = NVL(VROUTROSITMAPR,0) + NVL(C_NF.VRIMPICMSDES,0)
           WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
             AND IDTIPOITMAPR = 'VI'
             AND NRSEQITMAPR  = LPAD(V_NRSEQITMAPR,10,'0');
       END IF;

       /*INSERE OS AJUSTES POR ITENS*/
       NOVO_CODIGO('DETITMAPARECUP',V_NRSEQDETITMAPA);
       INSERT INTO DETITMAPARECUP
          (NRSEQDETITMAPA, NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR,
           CDFILIAL, NRLANCTONF, NRSEQITPROD, CDIMPOSTO, NRITPROIMPOS,
           VROUTROSIMPIT, VROUTALIQ, VROUTBASE, CDFILIALVND, CDCAIXA,
           NRSEQVENDA, NRSEQUITVEND)
       VALUES
          (TO_NUMBER(V_NRSEQDETITMAPA), P_NRSEQUMAPAPU, 'VI', LPAD(V_NRSEQITMAPR,10,'0'),
           C_NF.CDFILIAL, C_NF.NRLANCTONF, C_NF.NRSEQITPROD, C_NF.CDIMPOSTO, C_NF.NRITPROIMPOS,
           C_NF.VRIMPICMSDES, C_NF.VRALIQUOTA, C_NF.VRCONTABIL, C_NF.CDFILIALVENDA, C_NF.CDCAIXA,
           C_NF.NRSEQVENDA, C_NF.NRSEQUITVEND);

       /*QUANDO FOR UMA NOVO AJUTE, INSERE OS DADOS NAS INFORMACOES ADICIONAIS*/
       IF (NVL(V_CDBENEFICIO,' ') <> C_NF.CDBENEE115) THEN
          /*INSERE O REGISTRO DE DADOS ADICIONAIS*/
          V_NRSEQINFADIC := NVL(V_NRSEQINFADIC,0) + 1;
          INSERT INTO INFADICMAPA
             (NRSEQUMAPAPU, NRSEQINFADIC, IDORIGEMINF, CDINFADICMAPA, DSINFADICMAPA, VRINFADICMAPA)
          VALUES
             (P_NRSEQUMAPAPU, LPAD(V_NRSEQINFADIC,5,'0'), 'B', C_NF.CDBENEE115, C_NF.DSBENEFICIO, C_NF.VRIMPICMSDES);
          V_CDBENEFICIO := C_NF.CDBENEE115;
       /*QUANDO FOR A MESMA NOTA, ATUALIZA OS VALORES DO AJUSTE*/
       ELSE
          UPDATE INFADICMAPA
             SET VRINFADICMAPA = NVL(VRINFADICMAPA,0) + NVL(C_NF.VRIMPICMSDES,0)
           WHERE NRSEQUMAPAPU  = P_NRSEQUMAPAPU
             AND NRSEQINFADIC  = LPAD(V_NRSEQINFADIC,5,'0')
             AND IDORIGEMINF   = 'B'
             AND CDINFADICMAPA = C_NF.CDBENEE115;
       END IF;
   END LOOP;
END;
/