DROP TABLE TMP_INUTILIZACAO_NFE
/
CREATE GLOBAL TEMPORARY TABLE TMP_INUTILIZACAO_NFE
(
   NRNOTAFISCALELE NUMBER
) ON COMMIT DELETE ROWS
/
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_INI_INTERVALO     NUMBER;
   V_FIM_INTERVALO     NUMBER;
   V_SOMA_INTERVALO    NUMBER;
   V_AUXILIAR          NUMBER;
   V_NRGRAVAINTINUTNFE NUMBER;
   V_NRSEQUENCIAL      VARCHAR2(20);
BEGIN
   V_INI_INTERVALO     := 0;
   V_FIM_INTERVALO     := 0;
   V_SOMA_INTERVALO    := 0;
   V_AUXILIAR          := 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;
      IF P_MODOPERACIONAL = '3' THEN
         INSERT INTO TMP_INUTILIZACAO_NFE (NRNOTAFISCALELE)
         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 CDSERIENFESCAN = 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 CDSERIEENTSCAN = P_CDSERIENFE) SERIE
                  GROUP BY CDEMPRESA, CDINSCESTA, CDINSCMUNI, CDSERIESAIDA, CDSERIEENTRADA) SERIE
          WHERE INSCRESTAD.CDEMPRESA = NOTAFISCAL.CDEMPRESA
            AND INSCRESTAD.CDINSCESTA = NOTAFISCAL.CDINSCESTA
            AND SERIE.CDEMPRESA = NOTAFISCAL.CDEMPRESA
            AND SERIE.CDINSCESTA = NOTAFISCAL.CDINSCESTA
            AND SERIE.CDINSCMUNI = NOTAFISCAL.CDINSCMUNI
            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.IDTIPOEMISNFE = '3'
            AND NOTAFISCAL.IDNFGERADAVIAXML <> 'S'
            AND NOTAFISCAL.IDTPAMBNFE IN ('0', P_IDTPAMBIENTENFE)
         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 C_INTERVALO IN (SELECT (SELECT NVL(MAX(N3.NRNOTAFISCALELE) + 1, 1)
                                       FROM (SELECT NRNOTAFISCALELE
                                               FROM TMP_INUTILIZACAO_NFE) N3
                                      WHERE N3.NRNOTAFISCALELE < N1.NRNOTAFISCALELE)
                                         NRINICIAL, (N1.NRNOTAFISCALELE - 1) NRFINAL
                                       FROM (SELECT NRNOTAFISCALELE
                                               FROM TMP_INUTILIZACAO_NFE) N1
                                              WHERE N1.NRNOTAFISCALELE <> 1
                                                AND NOT EXISTS (SELECT N2.NRNOTAFISCALELE
                                                                  FROM (SELECT NRNOTAFISCALELE
                                                                          FROM TMP_INUTILIZACAO_NFE) N2
                                                                         WHERE N2.NRNOTAFISCALELE = N1.NRNOTAFISCALELE - 1)
                              ORDER BY NRINICIAL) LOOP
            V_AUXILIAR := C_INTERVALO.NRFINAL - C_INTERVALO.NRINICIAL;
            IF V_AUXILIAR > 1000 THEN
               WHILE V_AUXILIAR > 1000 LOOP
                  V_INI_INTERVALO := C_INTERVALO.NRINICIAL + V_SOMA_INTERVALO;
                  V_FIM_INTERVALO := V_INI_INTERVALO + 999;
                  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_AUXILIAR := V_AUXILIAR - 1000;
                  V_SOMA_INTERVALO := V_SOMA_INTERVALO + 1000;
               END LOOP;
               IF V_AUXILIAR > 0 THEN
                  V_INI_INTERVALO := V_FIM_INTERVALO + 1;
                  V_FIM_INTERVALO := V_INI_INTERVALO + V_AUXILIAR;
                  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);
               END IF;
               V_SOMA_INTERVALO := 0;
            ELSE
               INSERT INTO GRAVAINTINUTNFE
                  (CDEMPRESA, NRCNPJINSESTNFE, CDSERNFE, INTERVALOINI, INTERVALOFIN, IDTPAMBIENTENFE, DTHRCONSINUTNFE)
               VALUES
                  (P_CDEMPRESA, P_NRINSJURIEST, P_CDSERIENFE, LPAD(TO_CHAR(C_INTERVALO.NRINICIAL), 9, '0'), LPAD(TO_CHAR(C_INTERVALO.NRFINAL), 9, '0'), P_IDTPAMBIENTENFE, SYSDATE);
            END IF;
         END LOOP;
      ELSE
         INSERT INTO TMP_INUTILIZACAO_NFE (NRNOTAFISCALELE)
         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 = NOTAFISCAL.CDEMPRESA
            AND INSCRESTAD.CDINSCESTA = NOTAFISCAL.CDINSCESTA
            AND SERIE.CDEMPRESA = NOTAFISCAL.CDEMPRESA
            AND SERIE.CDINSCESTA = NOTAFISCAL.CDINSCESTA
            AND SERIE.CDINSCMUNI = NOTAFISCAL.CDINSCMUNI
            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.IDTIPOEMISNFE <> '3'
            AND NOTAFISCAL.IDNFGERADAVIAXML <> 'S'
            AND NOTAFISCAL.IDTPAMBNFE IN ('0', P_IDTPAMBIENTENFE)
         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 C_INTERVALO IN (SELECT (SELECT NVL(MAX(N3.NRNOTAFISCALELE) + 1, 1)
                                       FROM (SELECT NRNOTAFISCALELE
                                               FROM TMP_INUTILIZACAO_NFE) N3
                                      WHERE N3.NRNOTAFISCALELE < N1.NRNOTAFISCALELE)
                                         NRINICIAL, (N1.NRNOTAFISCALELE - 1) NRFINAL
                                       FROM (SELECT NRNOTAFISCALELE
                                               FROM TMP_INUTILIZACAO_NFE) N1
                                              WHERE N1.NRNOTAFISCALELE <> 1
                                                AND NOT EXISTS (SELECT N2.NRNOTAFISCALELE
                                                                  FROM (SELECT NRNOTAFISCALELE
                                                                          FROM TMP_INUTILIZACAO_NFE) N2
                                                                         WHERE N2.NRNOTAFISCALELE = N1.NRNOTAFISCALELE - 1)
                              ORDER BY NRINICIAL) LOOP
            V_AUXILIAR := C_INTERVALO.NRFINAL - C_INTERVALO.NRINICIAL;
            IF V_AUXILIAR > 1000 THEN
               WHILE V_AUXILIAR > 1000 LOOP
                  V_INI_INTERVALO := C_INTERVALO.NRINICIAL + V_SOMA_INTERVALO;
                  V_FIM_INTERVALO := V_INI_INTERVALO + 999;
                  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_AUXILIAR := V_AUXILIAR - 1000;
                  V_SOMA_INTERVALO := V_SOMA_INTERVALO + 1000;
               END LOOP;
               IF V_AUXILIAR > 0 THEN
                  V_INI_INTERVALO := V_FIM_INTERVALO + 1;
                  V_FIM_INTERVALO := V_INI_INTERVALO + V_AUXILIAR;
                  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);
               END IF;
               V_SOMA_INTERVALO := 0;
            ELSE
               INSERT INTO GRAVAINTINUTNFE
                  (CDEMPRESA, NRCNPJINSESTNFE, CDSERNFE, INTERVALOINI, INTERVALOFIN, IDTPAMBIENTENFE, DTHRCONSINUTNFE)
               VALUES
                  (P_CDEMPRESA, P_NRINSJURIEST, P_CDSERIENFE, LPAD(TO_CHAR(C_INTERVALO.NRINICIAL), 9, '0'), LPAD(TO_CHAR(C_INTERVALO.NRFINAL), 9, '0'), P_IDTPAMBIENTENFE, SYSDATE);
            END IF;
         END LOOP;
      END IF;
      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;
/

-- VERSAO --
BEGIN ATUVEROBJ('INUTILIZACAO_NFE','6.35.043'); END;
/
