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;
      BEGIN
         SELECT NRULTNUMINUTILIZADO
           INTO V_MIN
           FROM (SELECT TO_NUMBER(NVL(NRULTNUMINUTILIZADO, 0)) AS NRULTNUMINUTILIZADO
                   FROM NUMINUTILNFELE
                  WHERE CDEMPRESA  = P_CDEMPRESA
                    AND CDINSCESTA = P_CDINSCESTA
                    AND CDSERIE = P_CDSERIENFE
                    AND NRINSJURIESTNFE = P_NRINSJURIEST
                    AND IDTPAMBNFENUM IN ('0', P_IDTPAMBIENTENFE)
               ORDER BY NRULTNUMINUTILIZADO DESC)
         WHERE ROWNUM = 1;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_MIN := 0;
      END;

      IF V_MIN = 0 THEN
         BEGIN
            SELECT NRULTNUMINUTILIZADO
              INTO V_MIN
              FROM (SELECT TO_NUMBER(NVL(NRINUTILNFELE, 0)) AS NRULTNUMINUTILIZADO
                      FROM NUMINUTILNFELE
                     WHERE CDEMPRESA  = P_CDEMPRESA
                       AND CDINSCESTA = P_CDINSCESTA
                       AND CDSERIE = P_CDSERIENFE
                       AND NRINSJURIESTNFE = P_NRINSJURIEST
                       AND IDTPAMBNFENUM IN ('0', P_IDTPAMBIENTENFE)
                       AND NRPROTOCINUTNFE <> 'TEKNISA'
                       AND NRPROTOCINUTNFE IS NOT NULL
                  ORDER BY NRINUTILNFELE DESC)
            WHERE ROWNUM = 1;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_MIN := 0;
         END;
      END IF;
      SELECT NRNOTAFISCALELE AS NRMAX
        INTO V_MAX
        FROM (SELECT TO_NUMBER(NVL(NRNOTAFISCALELE, 0)) AS NRNOTAFISCALELE
                FROM NOTAFISCAL, INSCRESTAD,
                     (SELECT CDEMPRESA, CDINSCESTA, CDSERIE AS CDSERIESAIDA
                        FROM SERIENF
                       WHERE CDEMPRESA  = P_CDEMPRESA
                         AND CDINSCESTA = P_CDINSCESTA
                         AND CDSERIENFE = P_CDSERIENFE
                       GROUP BY CDEMPRESA, CDINSCESTA, CDSERIE) SERIES,
                     (SELECT CDSERIEENTR AS CDSERIEENTRADA
                        FROM SERIEENTR
                       WHERE CDSERIEENTRNFE = P_CDSERIENFE) SERIEE
               WHERE INSCRESTAD.CDEMPRESA = P_CDEMPRESA
                 AND INSCRESTAD.CDINSCESTA = P_CDINSCESTA
                 AND DECODE(IDENTRSAIDOP, 'S', SERIES.CDSERIESAIDA, SERIEE.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 NOTAFISCAL.IDSTATUSNFE <> 'P'
                 AND NOTAFISCAL.CDINSCESTA = SERIES.CDINSCESTA
                 AND INSCRESTAD.CDEMPRESA = NOTAFISCAL.CDEMPRESA
                 AND INSCRESTAD.CDINSCESTA = NOTAFISCAL.CDINSCESTA
               UNION ALL
              SELECT TO_NUMBER(NRINUTILNFELE) AS NRNOTAFISCALELE
                FROM NUMINUTILNFELE
               WHERE CDEMPRESA = P_CDEMPRESA
                 AND NRINSJURIESTNFE = P_NRINSJURIEST
                 AND CDSERIE = P_CDSERIENFE
                 AND CDINSCESTA = P_CDINSCESTA
                 AND IDTPAMBNFENUM IN ('0', P_IDTPAMBIENTENFE)
            ORDER BY NRNOTAFISCALELE DESC)
       WHERE ROWNUM = 1;

      FOR NRNOTA IN V_MIN .. V_MAX LOOP
         BEGIN
            SELECT NRNOTAFISCALELE
              INTO V_NOTA_ENCONTRADA
              FROM (SELECT NRNOTAFISCALELE
                      FROM NOTAFISCAL N JOIN INSCRESTAD I
                                          ON I.CDEMPRESA = N.CDEMPRESA
                                         AND I.CDINSCESTA = N.CDINSCESTA
                                        JOIN SERIENF S
                                          ON S.CDEMPRESA = N.CDEMPRESA
                                         AND S.CDINSCESTA = N.CDINSCESTA
                                         AND S.CDSERIE = N.CDSERIE
                     WHERE N.CDEMPRESA = P_CDEMPRESA
                       AND N.CDINSCESTA = P_CDINSCESTA
                       AND S.CDSERIENFE = P_CDSERIENFE
                       AND IDENTRSAIDOP = 'S'
                       AND NVL(N.NRINSJURIEST, I.NRINSJURIEST) = P_NRINSJURIEST
                       AND N.IDTPAMBNFE IN ('0', P_IDTPAMBIENTENFE)
                       AND N.IDSTATUSNFE <> 'P'
                       AND N.NRNOTAFISCALELE = LPAD(TO_CHAR(NRNOTA), 9, '0'))
             WHERE ROWNUM = 1;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NOTA_ENCONTRADA := NULL;
         END;

         --PROCURA NA ENTRADA
         IF V_NOTA_ENCONTRADA IS NULL THEN
            BEGIN
               SELECT NRNOTAFISCALELE
                 INTO V_NOTA_ENCONTRADA
                 FROM (SELECT NRNOTAFISCALELE
                         FROM NOTAFISCAL N JOIN INSCRESTAD I
                                             ON I.CDEMPRESA = N.CDEMPRESA
                                            AND I.CDINSCESTA = N.CDINSCESTA
                                           JOIN SERIEENTR S
                                             ON S.CDSERIEENTR = N.CDSERIEENTR
                        WHERE N.CDEMPRESA = P_CDEMPRESA
                          AND N.CDINSCESTA = P_CDINSCESTA
                          AND S.CDSERIEENTRNFE = P_CDSERIENFE
                          AND IDENTRSAIDOP = 'E'
                          AND NVL(N.NRINSJURIEST, I.NRINSJURIEST) = P_NRINSJURIEST
                          AND N.IDTPAMBNFE IN ('0', P_IDTPAMBIENTENFE)
                          AND N.IDSTATUSNFE <> 'P'
                          AND N.IDNFGERADAVIAXML <> 'S'
                          AND N.NRNOTAFISCALELE = LPAD(TO_CHAR(NRNOTA), 9, '0'))
                WHERE ROWNUM = 1;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_NOTA_ENCONTRADA := NULL;
            END;

            --PROCURA INUTILIZADA
            IF V_NOTA_ENCONTRADA IS NULL THEN
               BEGIN
                  SELECT NRINUTILNFELE
                    INTO V_NOTA_ENCONTRADA
                    FROM NUMINUTILNFELE
                   WHERE CDEMPRESA = P_CDEMPRESA
                     AND NRINSJURIESTNFE = P_NRINSJURIEST
                     AND CDSERIE = P_CDSERIENFE
                     AND IDTPAMBNFENUM IN ('0', P_IDTPAMBIENTENFE)
                     AND CDINSCESTA = P_CDINSCESTA
                     AND NRINUTILNFELE = LPAD(TO_CHAR(NRNOTA), 9, '0');
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_NOTA_ENCONTRADA := NULL;
               END;
            END IF;
         END IF;

         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 --FIM DO INTERVALO NO NUMERO ANTERIOR
               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
                     -- MODELO OPERACIONAL NORMAL
                     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
                     -- MODELO OPERACIONAL SCAN
                     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;
/
