CREATE OR REPLACE PROCEDURE CANCELA_PLANO_VOOM(
  P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
  P_CDCIA        IN COMPANHIA.CDCIA%TYPE,
  P_NRVOO        IN VOO.NRVOO%TYPE,
  P_CDTRECHO     IN VOOTRECHO.CDTRECHO%TYPE,
  P_DTHRPLAVOOPV IN PLANOVOOM.DTHRPLAVOOPV%TYPE,
  P_TIPOOPER     IN CHAR DEFAULT '1',
  P_CDOPERADOR   IN VARCHAR2,
  P_NRORG IN NUMBER DEFAULT 1
)AS

  V_SITUACAO            PLANOVOOM.IDSITABASPV%TYPE;

BEGIN

  SELECT NVL(MAX(IDSITABASPV),0)
  INTO   V_SITUACAO
  FROM   PLANOVOOM
  WHERE  CDFILIAL     = P_CDFILIAL AND
         CDCIA        = P_CDCIA    AND
         NRVOO        = P_NRVOO    AND
         CDTRECHO     = P_CDTRECHO AND
         DTHRPLAVOOPV = P_DTHRPLAVOOPV;

  IF V_SITUACAO = '3' THEN
    RAISE_APPLICATION_ERROR( -20502, 'APPERRMSGSTR' || 'APP-00015' || 'APPERRMSGEND', FALSE);

    /*Manutecao nao permitida. O voo ja esta cancelado.', false);*/
  END IF;

  IF V_SITUACAO = '2' THEN
    RAISE_APPLICATION_ERROR( -20502, 'APPERRMSGSTR' || 'APP-00049' || 'APPERRMSGEND', FALSE);    
  END IF;

  /* CANCELAMENTO DO VOO */
  IF P_TIPOOPER = '1' THEN
  BEGIN
    UPDATE PLANOVOOM
    SET    IDSITABASPV   = '3',
           DTULTATU = SYSDATE,
           CDOPERULTATU = P_CDOPERADOR,
           NRORGULTATU = P_NRORG
    WHERE  CDFILIAL      = P_CDFILIAL     AND
           CDCIA         = P_CDCIA        AND
           NRVOO         = P_NRVOO        AND
           CDTRECHO      = P_CDTRECHO     AND
           DTHRPLAVOOPV  = P_DTHRPLAVOOPV AND
           IDSITABASPV  IN ('1','4');

    UPDATE SERVPLANOM
    SET    IDSITSERSP    = '3',
           DTULTATU = SYSDATE,
           CDOPERULTATU = P_CDOPERADOR,
           NRORGULTATU = P_NRORG
    WHERE  CDFILIAL      = P_CDFILIAL     AND
           CDCIA         = P_CDCIA        AND
           NRVOO         = P_NRVOO        AND
           CDTRECHO      = P_CDTRECHO     AND
           DTHRPLAVOOPV  = P_DTHRPLAVOOPV AND
           NRDELIVERY IS NULL             AND
           NRLANCTONF IS NULL         AND
           IDSITSERSP    = '1';
  END;
  /* CANCELAMENTO DO VOO COM COBRANCA */
  ELSIF P_TIPOOPER = '2' THEN
  BEGIN
    UPDATE PLANOVOOM
    SET    IDSITABASPV   = '4',
           DTULTATU = SYSDATE,
           CDOPERULTATU = P_CDOPERADOR,
           NRORGULTATU = P_NRORG
    WHERE  CDFILIAL      = P_CDFILIAL     AND
           CDCIA         = P_CDCIA        AND
           NRVOO         = P_NRVOO        AND
           CDTRECHO      = P_CDTRECHO     AND
           DTHRPLAVOOPV  = P_DTHRPLAVOOPV AND
           IDSITABASPV  <> '4';
  END;
  END IF;

END;
/