CREATE OR REPLACE FUNCTION REVISAR_PREVPAXPROD_VOO
(
P_CDFILIAL IN VARCHAR2,
P_NRPREVISAOPROD IN VARCHAR2,
P_DTPREVISAOPROD IN DATE,
P_IDPREVISAOPROD IN VARCHAR2,
P_CDCIA IN VARCHAR2,
P_NRVOO IN VARCHAR2,
P_CDTRECHO IN VARCHAR2,
P_NRORG IN NUMBER DEFAULT 1
) RETURN VARCHAR2
AS
  V_DECOLA_DIA VARCHAR(1);
  V_DTPREVISAO DATE;
  V_REVISAR VARCHAR(10);  
  V_IDCONSIDERAPLANPROD VARCHAR2(1);
  V_CONSIDERA_PLANPROD VARCHAR2(1);
  V_EXISTE_PLANOVOO_CANCELADO NUMBER;
BEGIN 
  
  V_REVISAR := 'VALIDO';
  V_DTPREVISAO := P_DTPREVISAOPROD;

  /* Data da Previsao*/
  IF V_DTPREVISAO IS NULL THEN
    SELECT MAX(P.DTPREVISAOPROD)
      INTO V_DTPREVISAO
        FROM PREVPAXPRODM P
      WHERE P.CDFILIAL       = P_CDFILIAL
      AND   P.NRPREVISAOPROD = P_NRPREVISAOPROD
      AND   P.NRORG          = P_NRORG;
  END IF;

  /*Se ja tiver passado da data de previsao retorna que a previsao e valida*/
  IF TRUNC(SYSDATE) > V_DTPREVISAO THEN
     RETURN V_REVISAR;
  END IF;
  
  /* Verifica decolagem do Voo na data da Previsao*/
  V_DECOLA_DIA := VOO_DECOLA_DATA(P_CDFILIAL, P_CDCIA, P_NRVOO, P_CDTRECHO, V_DTPREVISAO);

  SELECT MAX(IDCONSIDERAPLANPROD)
    INTO V_IDCONSIDERAPLANPROD
    FROM VOO
   WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND NRVOO = P_NRVOO;

  /*Verifica se existe plano de voo cancelado para o voo na data*/
  SELECT COUNT(1)
    INTO V_EXISTE_PLANOVOO_CANCELADO
    FROM PLANOVOOM
   WHERE CDFILIAL = P_CDFILIAL
     AND CDCIA = P_CDCIA
     AND NRVOO = P_NRVOO
     AND CDTRECHO = P_CDTRECHO
     AND DTHRPLAVOOPV = V_DTPREVISAO
     AND IDSITABASPV IN ('3','4');

  IF V_DECOLA_DIA = 'S' AND NVL(V_IDCONSIDERAPLANPROD, 'S') = 'S' AND V_EXISTE_PLANOVOO_CANCELADO = 0 THEN
    V_CONSIDERA_PLANPROD := 'S';
  ELSE
    V_CONSIDERA_PLANPROD := 'N';
  END IF;

    
  V_REVISAR := CASE
                WHEN (V_CONSIDERA_PLANPROD = 'S' AND  P_IDPREVISAOPROD = 'C')
                    THEN 'ATIVAR'
                WHEN (V_CONSIDERA_PLANPROD = 'N' AND P_IDPREVISAOPROD <> 'C')
                    THEN 'INATIVAR'
                ELSE 'VALIDO' END;
                
  RETURN V_REVISAR;
END;
/


CREATE OR REPLACE FUNCTION REVISAR_PREVPAXPROD
(
P_CDFILIAL IN VARCHAR2,
P_NRPREVISAOPROD IN VARCHAR2,
P_NRORG IN NUMBER DEFAULT 1
) RETURN VARCHAR2
AS
  V_DTPREVISAOPROD DATE;
  V_REVISAR_VOO VARCHAR(10);
  V_REVISAR VARCHAR(1);  
  V_EXISTE_NOVOSVOOS NUMBER;
BEGIN 
  
  V_REVISAR := 'N';
  V_REVISAR_VOO := 'VALIDO';
  /* Data da Previsao*/
  SELECT MAX(P.DTPREVISAOPROD)
    INTO V_DTPREVISAOPROD
      FROM PREVPAXPRODM P
    WHERE P.CDFILIAL       = P_CDFILIAL
    AND   P.NRPREVISAOPROD = P_NRPREVISAOPROD
    AND   P.NRORG          = P_NRORG;

  /*Se ja tiver passado da data de previsao retorna que a previsao e valida*/
  IF TRUNC(SYSDATE) > V_DTPREVISAOPROD THEN
     RETURN V_REVISAR;
  END IF;

    /* Valida todos os Voos da Previsao de Passageiros*/

  FOR C_PREV_VOO IN (
      SELECT PV.CDCIA, PV.NRVOO, PV.CDTRECHO, PV.IDPREVISAOPROD
        FROM PREVPAXVOOPRODM PV
        WHERE PV.CDFILIAL       = P_CDFILIAL
        AND   PV.NRPREVISAOPROD = P_NRPREVISAOPROD
        AND   PV.NRORG          = P_NRORG
  ) LOOP
      /*  
          REVISAR_PREVPAXPROD_VOO()
              INATIVAR;
              ATIVAR;
              VALIDO;
      */
      V_REVISAR_VOO := REVISAR_PREVPAXPROD_VOO(P_CDFILIAL, P_NRPREVISAOPROD, NULL, C_PREV_VOO.IDPREVISAOPROD, C_PREV_VOO.CDCIA, C_PREV_VOO.NRVOO, C_PREV_VOO.CDTRECHO, P_NRORG);
      IF V_REVISAR_VOO <> 'VALIDO' THEN
          V_REVISAR := 'S';
          EXIT;
      END IF;
  
  END LOOP;

  SELECT COUNT(1)
    INTO V_EXISTE_NOVOSVOOS
    FROM VOOTRECHO VT
         INNER JOIN VOO VO ON VO.CDFILIAL = VT.CDFILIAL AND
                              VO.CDCIA = VT.CDCIA AND
                              VO.NRVOO = VT.NRVOO
         INNER JOIN VOOTRECHODATA VTD ON VTD.CDFILIAL = VT.CDFILIAL AND
                                         VTD.CDCIA = VT.CDCIA AND
                                         VTD.NRVOO = VT.NRVOO AND
                                         VTD.CDTRECHO = VT.CDTRECHO AND
                                         V_DTPREVISAOPROD BETWEEN VTD.DTINIVIGENCIA AND NVL(VTD.DTFIMVIGENCIA, V_DTPREVISAOPROD)
   WHERE VOO_DECOLA_DATA(VT.CDFILIAL, VT.CDCIA, VT.NRVOO, VT.CDTRECHO, V_DTPREVISAOPROD) = 'S'
     AND VO.IDCONSIDERAPLANPROD = 'S'
     AND VT.CDFILIAL = P_CDFILIAL
     AND NOT EXISTS
         (SELECT 1
            FROM PREVPAXVOOPRODM PV
            WHERE PV.CDFILIAL = P_CDFILIAL
              AND PV.NRPREVISAOPROD = P_NRPREVISAOPROD
              AND PV.NRORG = P_NRORG
              AND PV.CDFILIAL = VT.CDFILIAL
              AND PV.CDCIA = VT.CDCIA
              AND PV.NRVOO = VT.NRVOO
              AND PV.CDTRECHO = VT.CDTRECHO)
     AND EXISTS
     (
      SELECT CV.CDCLASSE, CL.SGCLASSE, CV.QTPAXVOO AS QTPAX
         FROM CONFIGVOO CV
        INNER JOIN CLASSE CL
        ON   CL.CDCLASSE = CV.CDCLASSE
        AND  CL.NRORG    = CV.NRORG
        WHERE CV.CDFILIAL = P_CDFILIAL
          AND CV.CDCIA = VT.CDCIA
          AND CV.NRVOO = VT.NRVOO
          AND CV.CDTRECHO = VT.CDTRECHO
          AND EXISTS (SELECT CDCLASSE
                        FROM ORDCLASSEPROD
                       WHERE ORDCLASSEPROD.CDFILIAL = CV.CDFILIAL
                         AND ORDCLASSEPROD.CDCLASSE = CV.CDCLASSE)

        UNION ALL

       SELECT C.CDCLASSE, CL.SGCLASSE, C.QTPAXEQUI AS QTPAX
         FROM CONFIGEQUI C
        INNER JOIN CLASSE CL
        ON   CL.CDCLASSE = C.CDCLASSE
        AND  CL.NRORG    = C.NRORG
        WHERE C.CDFILIAL = VT.CDFILIAL
          AND C.CDCIA = VT.CDCIA
          AND C.CDEQUIP = VTD.CDEQUIP 
          AND NOT EXISTS (SELECT 1
                            FROM CONFIGVOO
                           WHERE CDFILIAL = VT.CDFILIAL
                             AND CDCIA = VT.CDCIA
                             AND NRVOO = VT.NRVOO
                             AND CDTRECHO = VT.CDTRECHO)
          AND EXISTS (SELECT CDCLASSE
                        FROM ORDCLASSEPROD
                       WHERE ORDCLASSEPROD.CDFILIAL = C.CDFILIAL
                         AND ORDCLASSEPROD.CDCLASSE = C.CDCLASSE)
     );

  IF V_EXISTE_NOVOSVOOS > 0 THEN
     V_REVISAR := 'S';
  END IF;

  RETURN V_REVISAR;
END;
/