CREATE OR REPLACE FUNCTION VOO_DECOLA_DATA
(
P_CDFILIAL IN VARCHAR2,
P_CDCIA IN VARCHAR2,
P_NRVOO IN VARCHAR2,
P_CDTRECHO IN VARCHAR2,
P_DTDATA IN DATE
) RETURN VARCHAR2
AS
   V_IDSITVOO VARCHAR2(1);
   V_IDSITVOOTRE VARCHAR2(1);
   V_DIASEMANA VARCHAR2(1);
   V_DECOLA_NO_DIASEMANA VARCHAR2(1);
   V_DECOLA VARCHAR2(1);
   V_IDSEGUNDA VARCHAR2(1);
   V_IDTERCA VARCHAR2(1);
   V_IDQUARTA VARCHAR2(1);
   V_IDQUINTA VARCHAR2(1);
   V_IDSEXTA VARCHAR2(1);
   V_IDSABADO VARCHAR2(1);
   V_IDDOMINGO VARCHAR2(1);
   V_CIAFILI_ATIVA VARCHAR2(1);
   V_NRVOORETROBILL VARCHAR2(5);
   V_NRSEQVOOTRECHODATA NUMBER;
   V_DECOLAGEM_POR_DIA NUMBER;
   V_DECOLAGEM_NO_DIA NUMBER;
BEGIN
   SELECT MAX(V.IDSITVOO), MAX(VT.IDSITVOOTRE),
          MAX(VD.IDVOODOM), MAX(VD.IDVOOSEG), MAX(VD.IDVOOTER), MAX(VD.IDVOOQUA), MAX(VD.IDVOOQUI), MAX(VD.IDVOOSEX), MAX(VD.IDVOOSAB), 
          MAX(CF.IDATIVO), MAX(NRVOORETROBILL), MAX(NRSEQVOOTRECHODATA)
     INTO V_IDSITVOO, V_IDSITVOOTRE,
          V_IDDOMINGO, V_IDSEGUNDA, V_IDTERCA, V_IDQUARTA, V_IDQUINTA, V_IDSEXTA, V_IDSABADO, 
          V_CIAFILI_ATIVA, V_NRVOORETROBILL, V_NRSEQVOOTRECHODATA
     FROM VOO V
          INNER JOIN VOOTRECHO VT ON VT.CDFILIAL = V.CDFILIAL AND
                                     VT.CDCIA = V.CDCIA AND
                                     VT.NRVOO = V.NRVOO
          INNER JOIN VOOTRECHODATA VD ON VD.CDFILIAL = VT.CDFILIAL AND
                                         VD.CDCIA = VT.CDCIA AND
                                         VD.NRVOO = VT.NRVOO AND
                                         VD.CDTRECHO = VT.CDTRECHO
          INNER JOIN CIAFILI CF ON CF.CDFILIAL = V.CDFILIAL AND
                                   CF.CDCIA = V.CDCIA
    WHERE V.CDFILIAL = P_CDFILIAL
      AND V.CDCIA = P_CDCIA
      AND V.NRVOO = P_NRVOO
      AND VT.CDTRECHO = P_CDTRECHO
      AND P_DTDATA BETWEEN VD.DTINIVIGENCIA AND NVL(VD.DTFIMVIGENCIA, P_DTDATA);
   
   V_IDSITVOO := CASE WHEN V_IDSITVOO = '1' THEN 'S'
                     ELSE 'N' END;

   V_IDSITVOOTRE := CASE WHEN V_IDSITVOOTRE = '1' THEN 'S'
                     ELSE 'N' END;
   SELECT COUNT(1)
     INTO V_DECOLAGEM_POR_DIA
     FROM VOOTRECHODATADIA
    WHERE CDFILIAL = P_CDFILIAL
      AND CDCIA = P_CDCIA
      AND NRVOO = P_NRVOO
      AND CDTRECHO = P_CDTRECHO
      AND NRSEQVOOTRECHODATA = V_NRSEQVOOTRECHODATA;

   IF V_DECOLAGEM_POR_DIA > 0 THEN
      V_DECOLA_NO_DIASEMANA := 'N';
      SELECT COUNT(1)
        INTO V_DECOLAGEM_NO_DIA
        FROM VOOTRECHODATADIA
       WHERE CDFILIAL = P_CDFILIAL
         AND CDCIA = P_CDCIA
         AND NRVOO = P_NRVOO
         AND CDTRECHO = P_CDTRECHO
         AND NRSEQVOOTRECHODATA = V_NRSEQVOOTRECHODATA
         AND DTDIA = P_DTDATA;
      IF V_DECOLAGEM_NO_DIA > 0 THEN
         V_DECOLA_NO_DIASEMANA := 'S';
      END IF;
   ELSE

     V_DIASEMANA := TO_CHAR(P_DTDATA, 'D');
     V_DECOLA_NO_DIASEMANA := CASE WHEN  (
                                 V_DIASEMANA = '1' AND V_IDDOMINGO = 'S' OR
                                 V_DIASEMANA = '2' AND V_IDSEGUNDA = 'S' OR
                                 V_DIASEMANA = '3' AND V_IDTERCA = 'S' OR
                                 V_DIASEMANA = '4' AND V_IDQUARTA = 'S' OR
                                 V_DIASEMANA = '5' AND V_IDQUINTA = 'S' OR
                                 V_DIASEMANA = '6' AND V_IDSEXTA = 'S' OR
                                 V_DIASEMANA = '7' AND V_IDSABADO = 'S'
                              ) THEN 'S'
                              ELSE 'N' END;
   END IF;

   /*Decola se voo e trecho esta ativo e se decola no dia da semana indicado pela data*/
   V_DECOLA := CASE WHEN V_IDSITVOO = 'S' AND  
                         V_IDSITVOOTRE = 'S' AND 
                         V_DECOLA_NO_DIASEMANA = 'S' AND 
                         V_CIAFILI_ATIVA = 'S' AND
                         ((V_NRVOORETROBILL <> P_NRVOO) OR
                          (V_NRVOORETROBILL IS NULL))
                         THEN 'S'
               ELSE 'N' END;

   RETURN V_DECOLA;
END;
/