DECLARE
   V_QTDIDSTATUSMANIF NUMBER;
   V_QTDRESTANTE NUMBER;
BEGIN
   FOR C_NOTADESTINADA IN (SELECT NRACESSONFE, COUNT(*)
                             FROM NOTADESTINADA
                            GROUP BY NRACESSONFE
                           HAVING COUNT(*) >= 2)
   LOOP
      V_QTDIDSTATUSMANIF := 0;
      V_QTDRESTANTE := 0;
      BEGIN
         SELECT COUNT(*)
           INTO V_QTDIDSTATUSMANIF
           FROM (SELECT IDSTATUSMANIF
                   FROM NOTADESTINADA
                  WHERE NRACESSONFE = C_NOTADESTINADA.NRACESSONFE
                  GROUP BY IDSTATUSMANIF);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_QTDIDSTATUSMANIF := 0;
         END;
      END;
      IF V_QTDIDSTATUSMANIF = 1 THEN
         DELETE 
           FROM NOTADESTINADA
          WHERE NRACESSONFE = C_NOTADESTINADA.NRACESSONFE
            AND NRNFDESTINADA <> (SELECT MAX(NRNFDESTINADA)
                                    FROM NOTADESTINADA
                                   WHERE NRACESSONFE = C_NOTADESTINADA.NRACESSONFE);
      ELSE
         DELETE 
           FROM NOTADESTINADA
          WHERE NRACESSONFE = C_NOTADESTINADA.NRACESSONFE
            AND NRNFDESTINADA NOT IN (SELECT NRNFDESTINADA
                                        FROM NOTADESTINADA
                                       WHERE NRACESSONFE = C_NOTADESTINADA.NRACESSONFE
                                         AND (IDSTATUSMANIF = (SELECT MAX(IDSTATUSMANIF)
                                                                 FROM NOTADESTINADA
                                                                WHERE NRACESSONFE = C_NOTADESTINADA.NRACESSONFE
                                                                  AND IDSTATUSMANIF <> '5')));
         BEGIN
            SELECT COUNT(*)
              INTO V_QTDRESTANTE
              FROM NOTADESTINADA
             WHERE NRACESSONFE = C_NOTADESTINADA.NRACESSONFE;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_QTDRESTANTE := 0;
            END;
         END;
         IF V_QTDRESTANTE > 1 THEN
            DELETE 
              FROM NOTADESTINADA
             WHERE NRACESSONFE = C_NOTADESTINADA.NRACESSONFE
               AND NRNFDESTINADA <> (SELECT MAX(NRNFDESTINADA)
                                       FROM NOTADESTINADA
                                      WHERE NRACESSONFE = C_NOTADESTINADA.NRACESSONFE);
         END IF;
      END IF;
   END LOOP;
   
   COMMIT;
END;
/

ALTER TABLE NOTADESTINADA ADD CONSTRAINT UK_NOTADESTINADA_NRACESSONFE UNIQUE (NRACESSONFE)
/