CREATE OR REPLACE VIEW CONFIG_PAX_VOO (CDFILIAL, CDCIA, SGCIA, NRVOO, CDTRECHO, SGPREFIXO, CDEQUIP, HRDECOLVOO, IDSITVOO, IDTIPVOO, IDSITVOOTRE, DTINIVIGVOO, DTFIMVIGVOO, IDVOODOM, IDVOOSEG, IDVOOTER, IDVOOQUA, IDVOOQUI, IDVOOSEX, IDVOOSAB, CDCLASSE, SGCLASSE, QTPAX, QTPAXSBY, IDCLASSE, QTDIASPROD, VRPEROCUPACAO, NRTURNOPROD, DSTURNO, CDBASEORI, SGBASEORI, CDBASEDES, SGBASEDES, IDTIPOCALCULO, VRPERCOCUPACAO) AS 
  SELECT V.CDFILIAL, V.CDCIA, CI.SGCIA, V.NRVOO, VT.CDTRECHO, V.SGPREFIXO, V.CDEQUIP, VT.HRDECOLVOO,
       V.IDSITVOO, V.IDTIPVOO, VT.IDSITVOOTRE, V.DTINIVIGVOO, V.DTFIMVIGVOO,
       IDVOODOM, IDVOOSEG, IDVOOTER,
       IDVOOQUA, IDVOOQUI, IDVOOSEX, IDVOOSAB,
       /*Classe do voo, de acordo com a configuracao existente */
       CASE 
         WHEN CV.CDFILIAL IS NOT NULL THEN CCV.CDCLASSE
         WHEN CP.CDFILIAL IS NOT NULL THEN CCP.CDCLASSE
         WHEN CE.CDFILIAL IS NOT NULL THEN CCE.CDCLASSE           
       END AS CDCLASSE,
       CASE 
         WHEN CV.CDFILIAL IS NOT NULL THEN CCV.SGCLASSE
         WHEN CP.CDFILIAL IS NOT NULL THEN CCP.SGCLASSE
         WHEN CE.CDFILIAL IS NOT NULL THEN CCE.SGCLASSE           
       END AS SGCLASSE,       
       CASE 
         WHEN CV.CDFILIAL IS NOT NULL THEN NVL(CV.QTPAXVOO, 0)
         WHEN CP.CDFILIAL IS NOT NULL THEN NVL(CP.QTPAXPREFIXO, 0)
         WHEN CE.CDFILIAL IS NOT NULL THEN NVL(CE.QTPAXEQUI, 0)
         ELSE 0.00 
       END AS QTPAX,
       CASE 
         WHEN CV.CDFILIAL IS NOT NULL THEN NVL(CV.QTPAXSBYVOO, 0)
         WHEN CP.CDFILIAL IS NOT NULL THEN NVL(CP.QTPAXSBYPREF, 0)
         WHEN CE.CDFILIAL IS NOT NULL THEN NVL(CE.QTPAXSBYEQUI, 0)
         ELSE 0.00 
       END AS QTPAXSBY,       
       CASE 
         WHEN CV.CDFILIAL IS NOT NULL THEN CCV.IDCLASSE
         WHEN CP.CDFILIAL IS NOT NULL THEN CCP.IDCLASSE
         WHEN CE.CDFILIAL IS NOT NULL THEN CCE.IDCLASSE
         ELSE '0' 
       END AS IDCLASSE,       
       NVL(V.QTDIASPROD, 0),
       CASE WHEN NVL(V.VRPEROCUPACAO, 0) = 0 THEN NVL(CF.VRPERCONVOO, 0) ELSE V.VRPEROCUPACAO END AS VRPEROCUPACAO,
       V.NRTURNOPROD, TU.DSTURNO,
       VT.CDBASEORI, BO.SGBASE AS BASEORI, VT.CDBASEDES, BD.SGBASE AS BASEDES,
       CASE 
         WHEN CV.CDFILIAL IS NOT NULL THEN CV.IDTIPOCALCULO
         WHEN CP.CDFILIAL IS NOT NULL THEN CP.IDTIPOCALCULO
         WHEN CE.CDFILIAL IS NOT NULL THEN CE.IDTIPOCALCULO
         ELSE '0' 
       END AS IDTIPOCALCULO,
       CASE 
         WHEN CV.CDFILIAL IS NOT NULL THEN CV.VRPERCOCUPACAO
         WHEN CP.CDFILIAL IS NOT NULL THEN CP.VRPERCOCUPACAO
         WHEN CE.CDFILIAL IS NOT NULL THEN CE.VRPERCOCUPACAO
         ELSE 0.000 
       END AS VRPERCOCUPACAO
FROM VOO V
     INNER JOIN VOOTRECHO VT ON VT.CDFILIAL = V.CDFILIAL AND
                                VT.CDCIA = V.CDCIA AND
                                VT.NRVOO = V.NRVOO
     INNER JOIN COMPANHIA CI ON CI.CDCIA = V.CDCIA
     LEFT JOIN BASE BO ON BO.CDBASE = VT.CDBASEORI
     LEFT JOIN BASE BD ON BD.CDBASE = VT.CDBASEDES
     LEFT JOIN TURNO TU ON TU.NRTURNO = V.NRTURNOPROD
     LEFT JOIN CIAFILI CF ON CF.CDFILIAL = V.CDFILIAL AND
                             CF.CDCIA = V.CDCIA
     /* Maximo por voo e classe */
      LEFT JOIN CONFIGVOO CV ON CV.CDFILIAL = V.CDFILIAL AND
                                CV.CDCIA = V.CDCIA AND
                                CV.NRVOO = V.NRVOO AND
                                CV.CDTRECHO = VT.CDTRECHO
      LEFT JOIN CLASSE CCV ON CCV.CDCLASSE = CV.CDCLASSE
      /* Maximo por prefixo e classe, caso nao encontrado estas informacoes por voo */
      LEFT JOIN CONFIGPREF CP ON CP.CDFILIAL = V.CDFILIAL AND
                                 CP.CDCIA = V.CDCIA AND
                                 CP.SGPREFIXO = V.SGPREFIXO AND
                                 CV.CDFILIAL IS NULL
      LEFT JOIN CLASSE CCP ON CCP.CDCLASSE = CP.CDCLASSE
      /* Maximo por equipamento e classe, caso nao encontrado por voo nem por prefixo */
      LEFT JOIN CONFIGEQUI CE ON CE.CDFILIAL = V.CDFILIAL AND
                                 CE.CDCIA = V.CDCIA AND
                                 CE.CDEQUIP = V.CDEQUIP AND
                                 CV.CDFILIAL IS NULL AND
                                 CP.CDFILIAL IS NULL
      LEFT JOIN CLASSE CCE ON CCE.CDCLASSE = CE.CDCLASSE
/