CREATE OR REPLACE PROCEDURE APAGA_PREVPAXVOO_PROD
(P_CDFILIAL IN VARCHAR2,
 P_CDFILIPROC IN VARCHAR2,
 P_NRPREVISAOPROD IN NUMBER,
 P_CDCIA IN VARCHAR2,
 P_NRVOO IN VARCHAR2,
 P_CDTRECHO IN VARCHAR2,
 P_CDOPERADOR IN VARCHAR2,
 P_NRORG IN NUMBER DEFAULT 1,
 P_NRLISTAERROS IN OUT NUMBER
)
AS
   V_CONTADOR NUMBER;
   V_VOO VARCHAR2(50);
   V_DATA VARCHAR2(50);
   V_IDPREVISAOPROD VARCHAR2(1);
BEGIN   
   SELECT MAX(CO.SGCIA || P.NRVOO || ' ' || BO.SGBASE || ' - ' || BD.SGBASE),  MAX(TO_CHAR(PD.DTPREVISAOPROD, 'DD/MM/YYYY')), COUNT(1), MAX(P.IDPREVISAOPROD)
     INTO V_VOO,V_DATA, V_CONTADOR, V_IDPREVISAOPROD
     FROM PREVPAXVOOPRODM P
          INNER JOIN PREVPAXPRODM PD ON PD.CDFILIAL = P.CDFILIAL AND
                                        PD.NRPREVISAOPROD = P.NRPREVISAOPROD
          INNER JOIN VOOTRECHO VT ON VT.CDFILIAL = P.CDFILIAL AND
                                     VT.CDCIA = P.CDCIA AND
                                     VT.NRVOO = P.NRVOO AND
                                     VT.CDTRECHO = P.CDTRECHO
          INNER JOIN BASE BO ON BO.CDBASE = VT.CDBASEORI
          LEFT JOIN BASE BD ON BD.CDBASE = P.CDBASEDES
          INNER JOIN COMPANHIA CO ON CO.CDCIA = P.CDCIA
    WHERE P.CDFILIAL = P_CDFILIAL
      AND P.NRPREVISAOPROD = P_NRPREVISAOPROD
      AND P.CDCIA = P_CDCIA
      AND P.NRVOO = P_NRVOO
      AND P.CDTRECHO = P_CDTRECHO;

   IF V_IDPREVISAOPROD <> 'P' THEN
      /*Previsao do voo $param[0] na data $param[1] ja aprovada*/
      INSERE_LISTA_ERROS(P_CDFILIPROC, 'APPERRMSGSTR' || 'APP-00054' || '$$' || 
                                       V_VOO || '$$' ||
                                       V_DATA
                                       || 'APPERRMSGEND',
                         P_CDOPERADOR, P_NRORG, P_NRLISTAERROS);
      RETURN;
   END IF;

   DELETE FROM HPREVPAXSERVPRODM H
    WHERE EXISTS (SELECT 1
                    FROM PREVPAXSERVPRODM P
                   WHERE H.CDFILIAL = P.CDFILIAL                                      
                     AND H.NRPREVISAOPROD = P.NRPREVISAOPROD
                     AND H.NRPREVPAXSERVPROD = P.NRPREVPAXSERVPROD
                     AND P.CDFILIAL = P_CDFILIAL
                     AND P.NRPREVISAOPROD = P_NRPREVISAOPROD
                     AND P.CDCIA = P_CDCIA
                     AND P.NRVOO = P_NRVOO
                     AND P.CDTRECHO =  P_CDTRECHO);

   DELETE FROM PREVPAXSERVPRODM
    WHERE CDFILIAL = P_CDFILIAL
      AND NRPREVISAOPROD = P_NRPREVISAOPROD
      AND CDCIA = P_CDCIA
      AND NRVOO = P_NRVOO
      AND CDTRECHO = P_CDTRECHO;

   DELETE FROM LOGPREVPAXSERPROD
    WHERE CDFILIAL = P_CDFILIAL
      AND NRPREVISAOPROD = P_NRPREVISAOPROD
      AND CDCIA = P_CDCIA
      AND NRVOO = P_NRVOO
      AND CDTRECHO = P_CDTRECHO;

   DELETE FROM PREVPAXSERPRODM
    WHERE CDFILIAL = P_CDFILIAL
      AND NRPREVISAOPROD = P_NRPREVISAOPROD
      AND CDCIA = P_CDCIA
      AND NRVOO = P_NRVOO
      AND CDTRECHO = P_CDTRECHO;

   DELETE FROM LOGPREVPAXCLAPROD
    WHERE CDFILIAL = P_CDFILIAL
      AND NRPREVISAOPROD = P_NRPREVISAOPROD
      AND CDCIA = P_CDCIA
      AND NRVOO = P_NRVOO
      AND CDTRECHO = P_CDTRECHO;

   DELETE FROM PREVPAXCLAPRODM
    WHERE CDFILIAL = P_CDFILIAL
      AND NRPREVISAOPROD = P_NRPREVISAOPROD
      AND CDCIA = P_CDCIA
      AND NRVOO = P_NRVOO
      AND CDTRECHO = P_CDTRECHO;

   DELETE FROM PREVPAXVOOPRODM
    WHERE CDFILIAL = P_CDFILIAL
      AND NRPREVISAOPROD = P_NRPREVISAOPROD
      AND CDCIA = P_CDCIA
      AND NRVOO = P_NRVOO
      AND CDTRECHO = P_CDTRECHO;
END;
/