create or replace PROCEDURE INUTILIZACAO_NFE(P_CDEMPRESA IN EMPRESA.CDEMPRESA%TYPE,
                           P_NRINSJURIEST IN INSCRESTAD.NRINSJURIEST%TYPE,
                           P_CDINSCESTA IN INSCRESTAD.CDINSCESTA%TYPE,
                           P_CDINSCMUNI IN INSCRMUNICIP.CDINSCMUNI%TYPE,
                           P_CDSERIENFE IN SERIENF.CDSERIENFE%TYPE,
                           P_IDTPAMBIENTENFE IN INSCRESTAD.IDTPAMBIENTENFE%TYPE,
                           P_MODOPERACIONAL IN NOTAFISCAL.IDTIPOEMISNFE%TYPE,
                           P_GERAL IN VARCHAR2 DEFAULT 'N')
IS
   V_MIN               NUMBER;
   V_MAX               NUMBER;   
   V_INI_INTERVALO     NUMBER;
   V_FIM_INTERVALO     NUMBER;
   V_NOTA_ENCONTRADA   VARCHAR(9);
   V_NRGRAVAINTINUTNFE NUMBER;
   V_NRSEQUENCIAL      VARCHAR2(20);

BEGIN
   V_INI_INTERVALO     := 0;
   V_FIM_INTERVALO     := 0;
   V_NRGRAVAINTINUTNFE := 0;
   V_NRSEQUENCIAL      := NULL;

   IF NVL(P_GERAL, 'N') = 'N' THEN
      DELETE
        FROM GRAVAINTINUTNFE
       WHERE CDEMPRESA = P_CDEMPRESA
         AND NRCNPJINSESTNFE = P_NRINSJURIEST
         AND CDSERNFE = P_CDSERIENFE
         AND IDTPAMBIENTENFE = P_IDTPAMBIENTENFE;


      SELECT NVL(MIN(NRNOTAFISCALELE), 0) AS NRMIN, NVL(MAX(NRNOTAFISCALELE), 0) AS NRMAX
        INTO V_MIN,V_MAX
        FROM(
         SELECT TO_NUMBER(NRNOTAFISCALELE) AS NRNOTAFISCALELE
           FROM NOTAFISCAL, INSCRESTAD,
                (SELECT CDEMPRESA, CDINSCESTA, CDINSCMUNI, CDSERIESAIDA, CDSERIEENTRADA
                   FROM (SELECT CDEMPRESA, CDINSCESTA, CDINSCMUNI, CDSERIE AS CDSERIESAIDA, '' AS CDSERIEENTRADA
                          FROM SERIENF
                         WHERE CDEMPRESA  = P_CDEMPRESA
                           AND CDINSCESTA = P_CDINSCESTA
                           AND CDINSCMUNI = P_CDINSCMUNI
                           AND CDSERIENFE = P_CDSERIENFE
                         UNION ALL
                        SELECT P_CDEMPRESA AS CDEMPRESA, P_CDINSCESTA AS CDINSCESTA, P_CDINSCMUNI AS CDINSCMUNI, '' AS CDSERIESAIDA, CDSERIEENTR AS CDSERIEENTRADA
                          FROM SERIEENTR
                         WHERE CDSERIEENTRNFE = P_CDSERIENFE) SERIE
               GROUP BY CDEMPRESA, CDINSCESTA, CDINSCMUNI, CDSERIESAIDA, CDSERIEENTRADA) SERIE
          WHERE INSCRESTAD.CDEMPRESA = P_CDEMPRESA
            AND INSCRESTAD.CDINSCESTA = P_CDINSCESTA
            AND DECODE(IDENTRSAIDOP, 'S', SERIE.CDSERIESAIDA, SERIE.CDSERIEENTRADA) = DECODE(IDENTRSAIDOP, 'S', NOTAFISCAL.CDSERIE, NOTAFISCAL.CDSERIEENTR)
            AND NOTAFISCAL.CDEMPRESA = P_CDEMPRESA
            AND NVL(NOTAFISCAL.NRINSJURIEST, INSCRESTAD.NRINSJURIEST) = P_NRINSJURIEST
            AND NOTAFISCAL.IDNFGERADAVIAXML <> 'S'
            AND NOTAFISCAL.IDTPAMBNFE IN ('0', P_IDTPAMBIENTENFE)
            AND TRUNC(NOTAFISCAL.DTEMISSAO) >= TRUNC(ADD_MONTHS(SYSDATE, - 12))
            AND NOTAFISCAL.IDSTATUSNFE <> 'P'
            AND NOTAFISCAL.CDINSCESTA = SERIE.CDINSCESTA
            AND NOTAFISCAL.CDINSCMUNI = SERIE.CDINSCMUNI
          UNION ALL
         SELECT TO_NUMBER(NRINUTILNFELE) AS NRNOTAFISCALELE
           FROM NUMINUTILNFELE
          WHERE CDEMPRESA = P_CDEMPRESA
            AND NRINSJURIESTNFE = P_NRINSJURIEST
            AND CDSERIE = P_CDSERIENFE
            --AND NVL(IDTPEMISINUTIL, '1') = '3'
            AND IDTPAMBNFENUM IN ('0', P_IDTPAMBIENTENFE));

      FOR NRNOTA IN V_MIN .. V_MAX LOOP
         BEGIN
            SELECT DISTINCT NOTA_ENCONTRADA
              INTO V_NOTA_ENCONTRADA
              FROM (
                  SELECT NRNOTAFISCALELE AS NOTA_ENCONTRADA
                    FROM NOTAFISCAL, INSCRESTAD,
                         (SELECT CDEMPRESA, CDINSCESTA, CDINSCMUNI, CDSERIESAIDA, CDSERIEENTRADA
                            FROM (SELECT CDEMPRESA, CDINSCESTA, CDINSCMUNI, CDSERIE AS CDSERIESAIDA, '' AS CDSERIEENTRADA
                                    FROM SERIENF
                                   WHERE CDEMPRESA  = P_CDEMPRESA
                                     AND CDINSCESTA = P_CDINSCESTA
                                     AND CDINSCMUNI = P_CDINSCMUNI
                                     AND CDSERIENFE = P_CDSERIENFE
                                   UNION ALL
                                  SELECT P_CDEMPRESA AS CDEMPRESA, P_CDINSCESTA AS CDINSCESTA, P_CDINSCMUNI AS CDINSCMUNI, '' AS CDSERIESAIDA, CDSERIEENTR AS CDSERIEENTRADA
                                    FROM SERIEENTR
                                   WHERE CDSERIEENTRNFE = P_CDSERIENFE) SERIE
                        GROUP BY CDEMPRESA, CDINSCESTA, CDINSCMUNI, CDSERIESAIDA, CDSERIEENTRADA) SERIE
                   WHERE INSCRESTAD.CDEMPRESA = P_CDEMPRESA
                     AND INSCRESTAD.CDINSCESTA = P_CDINSCESTA
                     AND DECODE(IDENTRSAIDOP, 'S', SERIE.CDSERIESAIDA, SERIE.CDSERIEENTRADA) = DECODE(IDENTRSAIDOP, 'S', NOTAFISCAL.CDSERIE, NOTAFISCAL.CDSERIEENTR)
                     AND NOTAFISCAL.CDEMPRESA = P_CDEMPRESA
                     AND NVL(NOTAFISCAL.NRINSJURIEST, INSCRESTAD.NRINSJURIEST) = P_NRINSJURIEST
                     AND NOTAFISCAL.IDNFGERADAVIAXML <> 'S'
                     AND NOTAFISCAL.IDTPAMBNFE IN ('0', P_IDTPAMBIENTENFE)
                     AND TRUNC(NOTAFISCAL.DTEMISSAO) >= TRUNC(ADD_MONTHS(SYSDATE, - 12))
                     AND NOTAFISCAL.IDSTATUSNFE <> 'P'
                     AND NOTAFISCAL.CDINSCESTA = SERIE.CDINSCESTA
                     AND NOTAFISCAL.CDINSCMUNI = SERIE.CDINSCMUNI
                     AND NOTAFISCAL.NRNOTAFISCALELE = LPAD(TO_CHAR(NRNOTA), 9, '0')
               UNION ALL
                  SELECT NRINUTILNFELE AS NOTA_ENCONTRADA
                    FROM NUMINUTILNFELE
                   WHERE CDEMPRESA = P_CDEMPRESA
                     AND NRINSJURIESTNFE = P_NRINSJURIEST
                     AND CDSERIE = P_CDSERIENFE
                     AND IDTPAMBNFENUM IN ('0', P_IDTPAMBIENTENFE)
                     AND NRINUTILNFELE = LPAD(TO_CHAR(NRNOTA), 9, '0'));
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NOTA_ENCONTRADA := NULL;
         END;
         IF V_NOTA_ENCONTRADA IS NULL THEN
            IF V_INI_INTERVALO = 0 THEN
               V_INI_INTERVALO := LPAD(TO_CHAR(NRNOTA), 9, '0');
            END IF;
            V_FIM_INTERVALO := LPAD(TO_CHAR(NRNOTA), 9, '0');
         ELSE
            IF (V_INI_INTERVALO <> 0) AND (V_FIM_INTERVALO <> NRNOTA) THEN
               INSERT INTO GRAVAINTINUTNFE
                  (CDEMPRESA, NRCNPJINSESTNFE, CDSERNFE, INTERVALOINI, INTERVALOFIN, IDTPAMBIENTENFE, DTHRCONSINUTNFE)
               VALUES
                  (P_CDEMPRESA, P_NRINSJURIEST, P_CDSERIENFE, LPAD(TO_CHAR(V_INI_INTERVALO), 9, '0'), LPAD(TO_CHAR(V_FIM_INTERVALO), 9, '0'), P_IDTPAMBIENTENFE, SYSDATE);
               V_INI_INTERVALO := 0;
               V_FIM_INTERVALO := 0;
            END IF;
         END IF;

      END LOOP;
      COMMIT;
   ELSE
      IF NVL(P_GERAL, 'N') = 'S' THEN
         DELETE FROM GRAVAINTINUTNFE;
         FOR C_INSCRESTAD IN (SELECT INSCRESTAD.CDEMPRESA, INSCRESTAD.CDINSCESTA, INSCRESTAD.NRINSJURIEST, INSCRMUNICIP.CDINSCMUNI,
                                     INSCRESTAD.IDTPAMBIENTENFE, SERIENF.CDSERIE, SERIENF.CDSERIENFE, SERIENF.CDSERIENFESCAN
                                FROM INSCRESTAD, INSCRMUNICIP, SERIENF, PARAMEMPRESA
                               WHERE INSCRMUNICIP.CDEMPRESA = INSCRESTAD.CDEMPRESA
                                 AND INSCRESTAD.CDEMPRESA = PARAMEMPRESA.CDEMPRESA
                                 AND SERIENF.CDEMPRESA = INSCRESTAD.CDEMPRESA
                                 AND SERIENF.CDINSCESTA = INSCRESTAD.CDINSCESTA
                                 AND SERIENF.CDINSCMUNI = INSCRMUNICIP.CDINSCMUNI
                                 AND INSCRESTAD.NRSERIECERTIFIC IS NOT NULL
                                 AND SERIENF.CDSERIENFE IS NOT NULL
                                 AND NVL(PARAMEMPRESA.IDEMPUTILIZANFE, 'N') = 'S'
                               UNION
                              SELECT FILIAL.CDEMPRESA, FILIAL.CDINSCESTA, FILIAL.NRINSJURFILI, FILIAL.CDINSCMUNI,
                                     INSCRESTAD.IDTPAMBIENTENFE, FILIAL.CDSERIE, SERIENF.CDSERIENFE, SERIENF.CDSERIENFESCAN
                                FROM FILIAL, INSCRESTAD, SERIENF, PARAMEMPRESA
                               WHERE FILIAL.CDEMPRESA = INSCRESTAD.CDEMPRESA
                                 AND FILIAL.CDINSCESTA = INSCRESTAD.CDINSCESTA
                                 AND FILIAL.CDEMPRESA = SERIENF.CDEMPRESA
                                 AND FILIAL.CDINSCESTA = SERIENF.CDINSCESTA
                                 AND FILIAL.CDINSCMUNI = SERIENF.CDINSCMUNI
                                 AND FILIAL.CDSERIE = SERIENF.CDSERIE
                                 AND INSCRESTAD.CDEMPRESA = PARAMEMPRESA.CDEMPRESA
                                 AND FILIAL.IDENDEMISFIL <> 'I'
                                 AND INSCRESTAD.NRSERIECERTIFIC IS NOT NULL
                                 AND SERIENF.CDSERIENFE IS NOT NULL
                                 AND NVL(PARAMEMPRESA.IDEMPUTILIZANFE, 'N') = 'S') LOOP
            IF C_INSCRESTAD.CDSERIENFE IS NOT NULL THEN
               BEGIN
                  SELECT NRSEQUENCIAL
                    INTO V_NRSEQUENCIAL
                    FROM NOVOCODIGO
                   WHERE CDCONTADOR LIKE 'NOTAFISCALELE' || C_INSCRESTAD.CDEMPRESA || C_INSCRESTAD.NRINSJURIEST || C_INSCRESTAD.CDSERIENFE || C_INSCRESTAD.IDTPAMBIENTENFE;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_NRSEQUENCIAL := NULL;
               END;
               IF (V_NRSEQUENCIAL IS NOT NULL) OR (V_NRSEQUENCIAL <> '') THEN
                  BEGIN
                     SELECT COUNT(*) AS NRGRAVAINTINUTNFE
                       INTO V_NRGRAVAINTINUTNFE
                       FROM GRAVAINTINUTNFE
                      WHERE CDEMPRESA = C_INSCRESTAD.CDEMPRESA
                        AND NRCNPJINSESTNFE = C_INSCRESTAD.NRINSJURIEST
                        AND CDSERNFE = C_INSCRESTAD.CDSERIENFE
                        AND IDTPAMBIENTENFE = C_INSCRESTAD.IDTPAMBIENTENFE;
                  EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        V_NRGRAVAINTINUTNFE := 0;
                  END;
                  IF V_NRGRAVAINTINUTNFE = 0 THEN
                     INUTILIZACAO_NFE(C_INSCRESTAD.CDEMPRESA,
                                      C_INSCRESTAD.NRINSJURIEST,
                                      C_INSCRESTAD.CDINSCESTA,
                                      C_INSCRESTAD.CDINSCMUNI,
                                      C_INSCRESTAD.CDSERIENFE,
                                      C_INSCRESTAD.IDTPAMBIENTENFE,
                                      '1',
                                      'N');
                  END IF;
               END IF;
            END IF;
            IF C_INSCRESTAD.CDSERIENFESCAN IS NOT NULL THEN
               BEGIN
                  SELECT NRSEQUENCIAL
                    INTO V_NRSEQUENCIAL
                    FROM NOVOCODIGO
                   WHERE CDCONTADOR LIKE 'NOTAFISCALELE' || C_INSCRESTAD.CDEMPRESA || C_INSCRESTAD.NRINSJURIEST || C_INSCRESTAD.CDSERIENFESCAN || C_INSCRESTAD.IDTPAMBIENTENFE;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_NRSEQUENCIAL := NULL;
               END;
               IF (V_NRSEQUENCIAL IS NOT NULL) OR (V_NRSEQUENCIAL <> '') THEN
                  BEGIN
                     SELECT COUNT(*) AS NRGRAVAINTINUTNFE
                       INTO V_NRGRAVAINTINUTNFE
                       FROM GRAVAINTINUTNFE
                      WHERE CDEMPRESA = C_INSCRESTAD.CDEMPRESA
                        AND NRCNPJINSESTNFE = C_INSCRESTAD.NRINSJURIEST
                        AND CDSERNFE = C_INSCRESTAD.CDSERIENFESCAN
                        AND IDTPAMBIENTENFE = C_INSCRESTAD.IDTPAMBIENTENFE;
                  EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        V_NRGRAVAINTINUTNFE := 0;
                  END;
                  IF V_NRGRAVAINTINUTNFE = 0 THEN
                     INUTILIZACAO_NFE(C_INSCRESTAD.CDEMPRESA,
                                      C_INSCRESTAD.NRINSJURIEST,
                                      C_INSCRESTAD.CDINSCESTA, 
                                      C_INSCRESTAD.CDINSCMUNI,
                                      C_INSCRESTAD.CDSERIENFESCAN,
                                      C_INSCRESTAD.IDTPAMBIENTENFE,
                                      '3',
                                      'N');
                  END IF;
               END IF;
            END IF;
         END LOOP;
      END IF;
   END IF;
END;
/