CREATE OR REPLACE
FUNCTION TOTAL_PAX_COC
  (P_CDFILIAL IN VARCHAR2,
   P_CDCIA IN VARCHAR2,
   P_NRVOO IN VARCHAR2,
   P_CDTRECHO IN VARCHAR2,
   P_DATA IN DATE,
   P_CDCLASSE IN VARCHAR2,
   P_QTPAX IN FLOAT,
   P_QTPAXSBY IN FLOAT,   
   P_TIPO IN VARCHAR2, /*MAX - MAXIMA, PER - PERCENTUAL, HIS - HISTORICA, MED - MEDIA*/
   P_REFPAX IN FLOAT,
   P_RETORNO IN VARCHAR2 DEFAULT 'T')/* 'T' TOTAL  'P' PAX  'S' STANDBY*/
RETURN FLOAT
IS
   V_DTHRPLAVOOPV DATE;
   V_TOTAL FLOAT;
BEGIN
   IF P_TIPO = 'MAX' THEN
      RETURN CASE P_RETORNO 
               WHEN 'T' THEN NVL(P_QTPAX, 0) + NVL(P_QTPAXSBY, 0)
               WHEN 'P' THEN NVL(P_QTPAX, 0)
               ELSE  NVL(P_QTPAXSBY, 0)
             END;
   ELSIF P_TIPO = 'PER' THEN
      RETURN CASE P_RETORNO 
               WHEN 'T' THEN CEIL((NVL(P_QTPAX, 0) + NVL(P_QTPAXSBY, 0)) * NVL(P_REFPAX, 0)/100)
               WHEN 'P' THEN CEIL( NVL(P_QTPAX, 0) * NVL(P_REFPAX, 0)/100)
               ELSE  CEIL( NVL(P_QTPAXSBY, 0) * NVL(P_REFPAX, 0)/100)
             END;      
   ELSIF P_TIPO = 'HIS' THEN   
      /*Retorna o ultimo plano de voo*/
      BEGIN
         SELECT MAX(DTHRPLAVOOPV)
                INTO V_DTHRPLAVOOPV
           FROM PLANOVOO        
          WHERE CDFILIAL = P_CDFILIAL
            AND CDCIA = P_CDCIA
            AND NRVOO = P_NRVOO
            AND CDTRECHO = P_CDTRECHO
            AND DTHRPLAVOOPV < P_DATA;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_DTHRPLAVOOPV := NULL;
      END;
      /*Determina a quantidade de passageiros do ultimo plano de voo*/
      BEGIN
         SELECT CASE P_RETORNO
                  WHEN 'T' THEN NVL(QTTOTPAXAT, 0) + NVL(QTPAXSBY, 0)
                  WHEN 'P' THEN NVL(QTTOTPAXAT, 0)
                  ELSE NVL(QTPAXSBY, 0)
                END
           INTO V_TOTAL
           FROM PREVISAOPAX 
          WHERE CDFILIAL = P_CDFILIAL AND
                CDCIA = P_CDCIA AND
                NRVOO = P_NRVOO AND
                CDTRECHO  = P_CDTRECHO AND
                DTHRPLAVOOPV = V_DTHRPLAVOOPV AND
                CDCLASSE = P_CDCLASSE;      
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_TOTAL := 0.00;
      END;
      RETURN V_TOTAL;
   ELSIF P_TIPO = 'MED' THEN
      BEGIN
         SELECT CASE P_RETORNO
                  WHEN 'T' THEN SUM(NVL(QTTOTPAXAT, 0))/COUNT(1) +
                                SUM(NVL(QTPAXSBY,0))/COUNT(1)
                  WHEN 'P' THEN SUM(NVL(QTTOTPAXAT, 0))/COUNT(1)
                  ELSE SUM(NVL(QTPAXSBY,0))/COUNT(1)
                END
                INTO V_TOTAL             
                FROM PREVISAOPAX
               WHERE CDFILIAL = P_CDFILIAL
                 AND CDCIA = P_CDCIA
                 AND NRVOO = P_NRVOO
                 AND CDTRECHO = P_CDTRECHO
                 AND CDCLASSE = P_CDCLASSE
                 AND DTHRPLAVOOPV >= P_DATA - NVL(P_REFPAX, 0)
                 AND DTHRPLAVOOPV < P_DATA
               GROUP BY CDFILIAL, CDCIA, NRVOO, CDTRECHO, CDCLASSE;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_TOTAL := 0.00;
      END;
      
      RETURN V_TOTAL;
   ELSIF P_TIPO = 'ZER' THEN
      RETURN 0.0;   
   END IF;
   RETURN 0.0;
END;
/