CREATE OR REPLACE PROCEDURE VALIDA_NF_DELIVERYS_PLANOVOOM
(
P_CDFILIAL IN VARCHAR2,
P_CDCIA IN VARCHAR2,
P_NRVOO IN VARCHAR2,
P_CDTRECHO IN VARCHAR2,
P_DTHRPLAVOOPV IN DATE,
P_CLASSE IN VARCHAR2,
P_VD_DELIVERY_CANCELADA IN VARCHAR2,
P_NRORG IN NUMBER DEFAULT 1
) 
AS
  V_CONTADOR NUMBER;
BEGIN
   /*Verifica se o plano de voo possui notas fiscais geradas*/
   SELECT COUNT(1)
     INTO V_CONTADOR
     FROM SERVPLANOM
    WHERE CDFILIAL = P_CDFILIAL
      AND CDCIA = P_CDCIA
      AND NRVOO = P_NRVOO
      AND CDTRECHO = P_CDTRECHO
      AND DTHRPLAVOOPV = P_DTHRPLAVOOPV
      AND NRLANCTONF IS NOT NULL
      AND IDSITSERSP <> '3'
      AND ((P_CLASSE = 'TODAS') OR (CDCLASSE = P_CLASSE));

   IF V_CONTADOR > 0 THEN

      RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00048' || 'APPERRMSGEND' , TRUE);
   END IF;

   /*Verifica se existem deliverys emitidas para o plano de voo*/
   SELECT COUNT(1)
     INTO V_CONTADOR
     FROM DELIVERYM
    WHERE CDFILIAL = P_CDFILIAL
      AND CDCIA = P_CDCIA
      AND NRVOO = P_NRVOO
      AND CDTRECHO = P_CDTRECHO
      AND DTHRPLAVOOPV = P_DTHRPLAVOOPV
      AND IDSITDEL <> '3'
      AND EXISTS (SELECT 1
                    FROM SERVPLANOM
                   WHERE CDFILIAL = P_CDFILIAL
             AND CDCIA = P_CDCIA
             AND NRVOO = P_NRVOO
             AND CDTRECHO = P_CDTRECHO
             AND DTHRPLAVOOPV = P_DTHRPLAVOOPV
             AND CDFILIAL = DELIVERYM.CDFILIAL
             AND NRDELIVERY = DELIVERYM.NRDELIVERY
             AND ((P_CLASSE = 'TODAS') OR (CDCLASSE = P_CLASSE)));
   IF V_CONTADOR > 0 THEN
      RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00049' || 'APPERRMSGEND' , TRUE);
   END IF;

   /*Valida deliveries canceladas para o plano de voo*/
   IF P_VD_DELIVERY_CANCELADA = 'S' THEN
     SELECT COUNT(1)
       INTO V_CONTADOR
       FROM DELIVERYM
      WHERE CDFILIAL = P_CDFILIAL
        AND CDCIA = P_CDCIA
        AND NRVOO = P_NRVOO
        AND CDTRECHO = P_CDTRECHO
        AND DTHRPLAVOOPV = P_DTHRPLAVOOPV
        AND IDSITDEL = '3';
     IF V_CONTADOR > 0 THEN
        RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00050' || 'APPERRMSGEND' , TRUE);
     END IF;
   END IF;
END;
/