CREATE OR REPLACE PROCEDURE INSERE_NSU_FALTANTE(
   P_NRLISTA        IN VARCHAR2,
   P_NRINSJURIEST   IN VARCHAR2,
   P_IDAMBIENTENFE  IN VARCHAR2)
AS
   V_DATETIMEATUAL DATE;
   V_EXISTEREGISTRO INTEGER;
BEGIN
   V_DATETIMEATUAL := SYSDATE;
   FOR C_NRNSUFALTANTE IN (SELECT (SELECT NVL(MAX(N3.NRNSU) + 1, 1)
                                     FROM NSUNFE N3
                                    WHERE N3.NRINSJURIEST = P_NRINSJURIEST
                                      AND N3.IDAMBIENTENFE = P_IDAMBIENTENFE 
                                      AND N3.NRNSU < N1.NRNSU) AS NRINICIAL, 
                                  (N1.NRNSU - 1) AS NRFINAL
                             FROM NSUNFE N1
                            WHERE N1.NRINSJURIEST = P_NRINSJURIEST
                              AND N1.IDAMBIENTENFE = P_IDAMBIENTENFE 
                              AND N1.NRNSU > (SELECT MIN(NRNSU)
                                                FROM NSUNFE
                                               WHERE NRINSJURIEST = P_NRINSJURIEST
                                                 AND IDAMBIENTENFE = P_IDAMBIENTENFE)
                              AND NOT EXISTS (SELECT N2.NRNSU
                                                FROM NSUNFE N2
                                               WHERE N2.NRINSJURIEST = P_NRINSJURIEST
                                                 AND N2.IDAMBIENTENFE = P_IDAMBIENTENFE
                                                 AND N2.NRNSU = N1.NRNSU - 1)
                            ORDER BY NRINICIAL)
   LOOP
      FOR C_NRNSU IN C_NRNSUFALTANTE.NRINICIAL..C_NRNSUFALTANTE.NRFINAL 
      LOOP
         
         IF C_NRNSU > 0 THEN
            BEGIN
               SELECT NVL(COUNT(NRNSU), 0)
                 INTO V_EXISTEREGISTRO
                 FROM NSUNFE
                WHERE NRNSU = LPAD(C_NRNSU, 15, '0'); 
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_EXISTEREGISTRO := 0;
            END;
            
            IF V_EXISTEREGISTRO = 0 THEN
               INSERT INTO NSUNFE
                  (NRINSJURIEST, IDAMBIENTENFE, NRNSU, DTINSNRNSU,
                   DTUPDNRNSU, IDNRNSUFALTANTE)
               VALUES
                  (P_NRINSJURIEST, P_IDAMBIENTENFE, LPAD(C_NRNSU, 15, '0'), V_DATETIMEATUAL,
                   V_DATETIMEATUAL, 'S');
            END IF;
            
            IF P_NRLISTA IS NOT NULL THEN
               INSERT INTO LISTA
                  (NRLISTA, CDLISTA)
                VALUES
                  (P_NRLISTA, LPAD(C_NRNSU, 15, '0'));
            END IF;
            
            COMMIT;
         END IF;
      END LOOP;
   END LOOP;
   
   V_EXISTEREGISTRO := 0;
   
   FOR C_NSUNFE_NAO_CONSULTADO IN (SELECT NRINSJURIEST, IDAMBIENTENFE, NRNSU
                                     FROM NSUNFE
                                    WHERE NRACESSONFE IS NULL
                                      AND NRINSJURIEST = P_NRINSJURIEST
                                      AND IDAMBIENTENFE = P_IDAMBIENTENFE
                                      AND IDCONSULTADO = 'N'
                                    ORDER BY NRNSU)
   LOOP
      IF C_NSUNFE_NAO_CONSULTADO.NRNSU > 0 THEN
         UPDATE NSUNFE
            SET IDNRNSUFALTANTE = 'S'
          WHERE NRINSJURIEST = C_NSUNFE_NAO_CONSULTADO.NRINSJURIEST
            AND IDAMBIENTENFE = C_NSUNFE_NAO_CONSULTADO.IDAMBIENTENFE
            AND NRNSU = C_NSUNFE_NAO_CONSULTADO.NRNSU;
   
            IF P_NRLISTA IS NOT NULL THEN
   
               BEGIN
                  SELECT NVL(COUNT(CDLISTA), 0)
                    INTO V_EXISTEREGISTRO
                    FROM LISTA
                   WHERE NRLISTA = P_NRLISTA
                     AND CDLISTA = LPAD(C_NSUNFE_NAO_CONSULTADO.NRNSU, 15, '0');
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_EXISTEREGISTRO := 0;
               END;
   
               IF V_EXISTEREGISTRO = 0 THEN
                  INSERT INTO LISTA
                     (NRLISTA, CDLISTA)
                   VALUES
                     (P_NRLISTA, LPAD(C_NSUNFE_NAO_CONSULTADO.NRNSU, 15, '0'));
               END IF;
            END IF;
   
         COMMIT;
      END IF;
   END LOOP;
END;
/