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;
   V_MAXIMONSU      INTEGER;
   V_MINIMONSU      INTEGER;
   V_NRNSUMINCONSUL VARCHAR2(15);
BEGIN

   BEGIN
      SELECT MAX(NRNSUMINCONSUL)
        INTO V_NRNSUMINCONSUL
        FROM NSUNFE
       WHERE NRINSJURIEST  = P_NRINSJURIEST
         AND IDAMBIENTENFE = P_IDAMBIENTENFE;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_NRNSUMINCONSUL := NULL;
   END;

   IF (V_NRNSUMINCONSUL IS NULL) THEN
      BEGIN
         SELECT TO_NUMBER(NVL(MIN(N3.NRNSU), 0))
           INTO V_MINIMONSU
           FROM NSUNFE N3
          WHERE N3.NRINSJURIEST  = P_NRINSJURIEST
            AND N3.IDAMBIENTENFE = P_IDAMBIENTENFE
            AND TRUNC(N3.DTINSNRNSU) >= TRUNC(SYSDATE - 90);
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
              V_MINIMONSU := 0;
      END;
   ELSE
      V_MINIMONSU := TO_NUMBER(V_NRNSUMINCONSUL);
   END IF;

   BEGIN
      SELECT TO_NUMBER(NVL(MAX(N3.NRNSU), 0))
        INTO V_MAXIMONSU
        FROM NSUNFE N3
       WHERE N3.NRINSJURIEST = P_NRINSJURIEST
         AND N3.IDAMBIENTENFE = P_IDAMBIENTENFE;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
           V_MAXIMONSU := 0;
   END;
   V_DATETIMEATUAL := SYSDATE;

   FOR C_NRNSU IN V_MINIMONSU..V_MAXIMONSU
      LOOP

      IF C_NRNSU > 0 THEN
         BEGIN
            SELECT NVL(COUNT(NRNSU), 0)
              INTO V_EXISTEREGISTRO
              FROM NSUNFE
             WHERE NRNSU = LPAD(C_NRNSU, 15, '0')
               AND NRINSJURIEST  = P_NRINSJURIEST
               AND IDAMBIENTENFE = P_IDAMBIENTENFE;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_EXISTEREGISTRO := 0;
         END;

         IF V_EXISTEREGISTRO = 0 THEN
            INSERT INTO NSUNFE
               (NRINSJURIEST, IDAMBIENTENFE, NRNSU, DTINSNRNSU,
                DTUPDNRNSU, IDNRNSUFALTANTE, NRNSUMINCONSUL, IDCONSULTADO)
            VALUES
               (P_NRINSJURIEST, P_IDAMBIENTENFE, LPAD(C_NRNSU, 15, '0'), V_DATETIMEATUAL,
                V_DATETIMEATUAL, 'S', LPAD(C_NRNSU, 15, '0'), 'N');

            IF P_NRLISTA IS NOT NULL THEN
               INSERT INTO LISTA
                  (NRLISTA, CDLISTA)
                VALUES
                  (P_NRLISTA, LPAD(C_NRNSU, 15, '0'));
            END IF;
         END IF;

         COMMIT;
      END IF;
   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;
/