CREATE OR REPLACE PROCEDURE GERA_CREDITO_RESSARC_ST(
   P_NRSEQUMAPAPU IN  MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_CDEMPRESA    IN  MAPAAPURAC.CDEMPRESA%TYPE,
   P_CDINSCRICAO  IN  MAPAAPURAC.CDINSCESTA%TYPE)
AS
   V_IDVRINFCREDSTRS   PARAMESTSPED.IDVRINFCREDSTRS%TYPE;
   V_CDAJCREDSTRS      PARAMESTSPED.CDAJCREDSTRS%TYPE;
   V_DSAJCREDSTRS      PARAMESTSPED.DSAJCREDSTRS%TYPE;
   V_IDESCREDSTRS      PARAMESTSPED.IDESCREDSTRS%TYPE;
   V_CDTPOPCREDSTRS    PARAMESTSPED.CDTPOPCREDSTRS%TYPE;
   V_NRSEQITMAPR       ITMAPARECUP.NRSEQITMAPR%TYPE;
   V_NRSEQDETITMAPA    DETITMAPARECUP.NRSEQDETITMAPA%TYPE;
BEGIN
   /*VERIFICA SE A INSCRICAO UTILIZA A ROTINA DE GERACAO DE CREDITO DE RESSARCIMENTO DE ICMS ST*/
   BEGIN
      SELECT IDVRINFCREDSTRS, CDAJCREDSTRS, DSAJCREDSTRS, IDESCREDSTRS, CDTPOPCREDSTRS
        INTO V_IDVRINFCREDSTRS, V_CDAJCREDSTRS, V_DSAJCREDSTRS, V_IDESCREDSTRS, V_CDTPOPCREDSTRS
        FROM PARAMESTSPED
       WHERE CDEMPRESA    = P_CDEMPRESA
         AND CDINSCESTA   = P_CDINSCRICAO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDVRINFCREDSTRS := 'N';
         V_CDAJCREDSTRS    := '';
         V_DSAJCREDSTRS    := '';
         V_IDESCREDSTRS    := '';
         V_CDTPOPCREDSTRS  := '';
   END;

   DELETE FROM ITMAPARECUP
    WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
      AND IDTIPOITMAPR = 'VI'
      AND IDRESSARCST  = 'S';

   IF V_IDVRINFCREDSTRS = 'S' THEN
      BEGIN
         SELECT MAX(NRSEQITMAPR)
           INTO V_NRSEQITMAPR
           FROM ITMAPARECUP I
          WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU
            AND I.IDTIPOITMAPR = 'VI';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NRSEQITMAPR := NULL;
      END;

      FOR C_NF IN (SELECT N.CDFILIAL, N.NRLANCTONF
                     FROM NOTAFISCAL N, IMPOSTONF I
                    WHERE N.CDFILIAL     = I.CDFILIAL
                      AND N.NRLANCTONF   = I.NRLANCTONF
                      AND N.IDENTRSAIDOP = V_IDESCREDSTRS
                      AND N.CDTIPOOPER   = V_CDTPOPCREDSTRS
                      AND I.NRSEQUMAPAPU = P_NRSEQUMAPAPU) LOOP

         V_NRSEQITMAPR := LPAD(NVL(V_NRSEQITMAPR,0)+1,10,'0');
         INSERT INTO ITMAPARECUP
            (NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, DSITEMMAPR, VRITEMMAPR,
             CDFILIAL, NRLANCTONF, CDAJUSTEITMAPA, IDCOMPOEC197, IDRESSARCST)
         VALUES
            (P_NRSEQUMAPAPU, 'VI', V_NRSEQITMAPR, V_DSAJCREDSTRS, 0,
             C_NF.CDFILIAL, C_NF.NRLANCTONF, V_CDAJCREDSTRS, 'I', 'S');

         FOR C_AJUSTE IN (SELECT T.CDFILIAL, T.NRLANCTONF, T.NRSEQITPROD, T.CDIMPOSTO,
                                 T.NRITPROIMPOS, SUM(VRIMPITENTR) AS VRRESSARCST
                            FROM INFDOCFISCREFE I, NOTAFISCAL N, ITEMPROIMPOS T, IMPOSTO IM
                           WHERE I.CDFILIAL      = C_NF.CDFILIAL
                             AND I.NRLANCTONF    = C_NF.NRLANCTONF
                             AND T.VRIMPITENTR   > 0
                             AND T.CDIMPOSTO     = IM.CDIMPOSTO
                             AND IM.CDIMPOSTONFE = '00'
                             AND N.CDFILIAL      = T.CDFILIAL
                             AND N.NRLANCTONF    = T.NRLANCTONF
                             AND I.IDTPDOCREFE   = 'refNFe'
                             AND I.CDREFNFE      = N.NRACESSONFE
                           GROUP BY T.CDFILIAL, T.NRLANCTONF, T.NRSEQITPROD, T.CDIMPOSTO, T.NRITPROIMPOS
                           UNION ALL
                          SELECT T.CDFILIAL, T.NRLANCTONF, T.NRSEQITPROD, T.CDIMPOSTO,
                                 T.NRITPROIMPOS, SUM(VRIMPITENTR) AS VRRESSARCST
                            FROM INFDOCFISCREFE I, NOTAFISCAL N, ITEMPROIMPOS T, IMPOSTO IM
                           WHERE I.CDFILIAL      = C_NF.CDFILIAL
                             AND I.NRLANCTONF    = C_NF.NRLANCTONF
                             AND T.VRIMPITENTR   > 0
                             AND T.CDIMPOSTO     = IM.CDIMPOSTO
                             AND IM.CDIMPOSTONFE = '00'
                             AND N.CDFILIAL      = T.CDFILIAL
                             AND N.NRLANCTONF    = T.NRLANCTONF
                             AND I.IDTPDOCREFE   = 'refNF'
                             AND I.NRNF          = SUBSTR(LPAD(NRNOTAFISC,20,'0'),12,9)
                             AND I.DTEMISSAO     = N.DTEMISSAO
                           GROUP BY T.CDFILIAL, T.NRLANCTONF, T.NRSEQITPROD, T.CDIMPOSTO, T.NRITPROIMPOS
                           ORDER BY 1, 2, 3) LOOP --CDFILIAL, NRLANCTONF, NRSEQITPROD

            UPDATE ITMAPARECUP
               SET VRITEMMAPR   = NVL(VRITEMMAPR,0) + C_AJUSTE.VRRESSARCST
             WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
               AND IDTIPOITMAPR = 'VI'
               AND NRSEQITMAPR  = V_NRSEQITMAPR;

            NOVO_CODIGO('DETITMAPARECUP',V_NRSEQDETITMAPA);
            INSERT INTO DETITMAPARECUP
               (NRSEQDETITMAPA, NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, CDFILIAL, NRLANCTONF,
                NRSEQITPROD, CDIMPOSTO, NRITPROIMPOS, VRIMPOSTOIT)
            VALUES
               (V_NRSEQDETITMAPA, P_NRSEQUMAPAPU, 'VI', V_NRSEQITMAPR, C_AJUSTE.CDFILIAL,C_AJUSTE.NRLANCTONF,
                C_AJUSTE.NRSEQITPROD, C_AJUSTE.CDIMPOSTO, C_AJUSTE.NRITPROIMPOS, C_AJUSTE.VRRESSARCST);

         END LOOP;
      END LOOP;
   END IF;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      RAISE;
END;
/