CREATE OR REPLACE PROCEDURE REVALIDA_STATUS_PLANOVOOM
(
P_CDFILIAL IN VARCHAR2,
P_CDCIA IN VARCHAR2,
P_NRVOO IN VARCHAR2,
P_CDTRECHO IN VARCHAR2,
P_DTHRPLAVOOPV IN DATE,
P_CDOPERADOR IN VARCHAR2,
P_NRORG IN NUMBER DEFAULT 1
)
AS
V_QTEMITIDA NUMBER;
V_QTPENDENTE NUMBER;
V_SITUACAO_VOO VARCHAR2(1);
BEGIN
   
   V_SITUACAO_VOO := NULL;
   
   SELECT SUM(CASE WHEN IDSITSERSP = '2' THEN 1 ELSE 0 END) AS QT_EMITIDA,
          SUM(CASE WHEN IDSITSERSP IN '1' THEN 1 ELSE 0 END) AS QT_PENDENTE
    INTO   V_QTEMITIDA, V_QTPENDENTE
    FROM   SERVPLANOM
    WHERE  CDFILIAL     = P_CDFILIAL AND
           CDCIA        = P_CDCIA    AND
           NRVOO        = P_NRVOO    AND
           CDTRECHO     = P_CDTRECHO AND
           DTHRPLAVOOPV = P_DTHRPLAVOOPV;
   IF V_QTPENDENTE > 0 THEN
     V_SITUACAO_VOO := '1';
   ELSIF V_QTEMITIDA > 0 AND V_QTPENDENTE = 0 THEN
     V_SITUACAO_VOO := '2';
   END IF;
   
   UPDATE PLANOVOOM
      SET IDSITABASPV  = NVL(V_SITUACAO_VOO, IDSITABASPV),
          CDOPERULTATU = P_CDOPERADOR,
          DTULTATU = SYSDATE,
          NRORGULTATU = P_NRORG
   WHERE  CDFILIAL     = P_CDFILIAL AND
          CDCIA        = P_CDCIA    AND
          NRVOO        = P_NRVOO    AND
          CDTRECHO     = P_CDTRECHO AND
          DTHRPLAVOOPV = P_DTHRPLAVOOPV;
END;
/