CREATE OR REPLACE VIEW PAD_VOOEQUIPAMENTO ("DTDATA", "CDFILIAL", "CDCIA", "NRVOO", "CDTRECHO", "CDEQUIP", "SGPREFIXO", "SGEQUIP", "SGBASEORI", "SGBASEDES", "NRSERVICOCIAFILI", "QTSERV", "VRLOADSE", "NRPROPAX", "DSSERVICO", "IDSERVICO", "CDCLASSE", "CDCICLO", "SGCLASSE", "IDCICLO", "IDPROPSERV", "QTSESTBY", "IDSITSERVICO", "IDTIPMANUAL", "IDTPPRECO", "IDFATSE", "IDSERVESP", "SGUNIDADE", "IDORIGEM") AS 
  SELECT PA.DTDATA, PA.CDFILIAL,PA.CDCIA,PA.NRVOO,PA.CDTRECHO,PA.CDEQUIP , PA.SGPREFIXO, PA.SGEQUIP,
       BO.SGBASE SGBASEORI,BD.SGBASE SGBASEDES  ,PA.NRSERVICOCIAFILI, PA.QTSERVOO,PA.VRLOADSEVOO,
       PA.NRPROPAXVOO, PA.DSSERVICOCIAFILI, PA.IDSERVICO ,PA.CDCLASSE,PA.CDCICLO,CL.SGCLASSE,
       CI.IDCICLO,PA.IDPROPSEVOO,PA.QTSESTBYVOO,PA.IDSITSERVICO,
       PA.IDTIPMANUAL,PA.IDTPPRECO,PA.IDFATSE,PA.IDSERVESP,PA.SGUNIDADE, PA.IDORIGEM
FROM 
   (
      SELECT PV.CDFILIAL,PV.CDCIA,PV.NRVOO,PV.CDTRECHO,VO.SGPREFIXO,
             VD.CDEQUIP,EQ.SGEQUIP,VT.CDBASEORI,VT.CDBASEDES,
             PV.QTSERVOO,PV.VRLOADSEVOO,PV.NRPROPAXVOO,SF.DSSERVICOCIAFILI,PV.CDCLASSE,
             PV.CDCICLO,SG.IDSERVICO,PV.IDPROPSEVOO,PV.QTSESTBYVOO,
             SF.IDSITSERVICO,SF.IDTIPMANUAL,SF.IDTPPRECO,SF.IDFATSE,
             SF.IDSERVESP,SG.SGUNIDADE, PV.NRSERVICOCIAFILI, DA.DTDATA, 'V' AS IDORIGEM
        FROM DATA DA CROSS JOIN
             PADVOOTREM PV
             INNER JOIN SERVICOCIAFILI SF ON SF.CDFILIAL = PV.CDFILIAL
                                           AND SF.CDCIA = PV.CDCIA
                                           AND SF.NRSERVICOCIAFILI = PV.NRSERVICOCIAFILI
             INNER JOIN SERVICOGEN SG ON SG.NRSERVICOGEN = SF.NRSERVICOGEN
             INNER JOIN VOOTRECHO VT ON VT.CDFILIAL = PV.CDFILIAL AND
                                        VT.CDCIA = PV.CDCIA AND
                                        VT.NRVOO = PV.NRVOO AND
                                        VT.CDTRECHO = PV.CDTRECHO
             INNER JOIN VOO VO ON VO.CDFILIAL = VT.CDFILIAL AND
                                  VO.CDCIA = VT.CDCIA AND
                                  VO.NRVOO = VT.NRVOO
             LEFT JOIN VOOTRECHODATA VD ON VD.CDFILIAL = VT.CDFILIAL AND
                                           VD.CDCIA = VT.CDCIA AND
                                           VD.NRVOO = VT.NRVOO AND
                                           VD.CDTRECHO = VT.CDTRECHO AND
                                           DA.DTDATA BETWEEN VD.DTINIVIGENCIA AND NVL(VD.DTFIMVIGENCIA, DA.DTDATA)
             LEFT JOIN EQUIPAMENTO EQ ON EQ.CDEQUIP = VD.CDEQUIP
       WHERE PV.NRSEQPADVOOTREMDATA = ITEM_VIGENTE_PADVOOTREM(PV.CDFILIAL, PV.CDCIA, PV.NRVOO, PV.CDTRECHO, DA.DTDATA)
         AND SG.IDSERVICO <> '4'
       UNION ALL
      SELECT PE.CDFILIAL,PE.CDCIA,VT.NRVOO,VT.CDTRECHO,NULL AS SGPREFIXO,
             PE.CDEQUIP,EQ.SGEQUIP,VT.CDBASEORI,VT.CDBASEDES,
             PE.QTSERVOO,0 AS VRLOADSEEQ, 0 AS NRPROPAXEQ,SF.DSSERVICOCIAFILI,PE.CDCLASSE,
             PE.CDCICLO,SG.IDSERVICO,'5' AS IDPROPSEEQ,0 AS QTSESTBYEQ,
             SF.IDSITSERVICO,SF.IDTIPMANUAL,SF.IDTPPRECO,SF.IDFATSE,
             SF.IDSERVESP,SG.SGUNIDADE, PE.NRSERVICOCIAFILI, DA.DTDATA, 'E' AS IDORIGEM
      FROM DATA DA CROSS JOIN 
           VOOTRECHO VT INNER JOIN PADEQUIPM PE ON PE.CDFILIAL = VT.CDFILIAL AND
                                                   PE.CDCIA  = VT.CDCIA
           INNER JOIN SERVICOCIAFILI SF ON SF.CDFILIAL = PE.CDFILIAL
                                         AND SF.CDCIA = PE.CDCIA
                                         AND SF.NRSERVICOCIAFILI = PE.NRSERVICOCIAFILI
           INNER JOIN SERVICOGEN SG ON SG.NRSERVICOGEN = SF.NRSERVICOGEN           
           INNER JOIN EQUIPAMENTO EQ ON EQ.CDEQUIP = PE.CDEQUIP
      WHERE PE.NRSEQPADEQUIPMDATA = ITEM_VIGENTE_PADEQUIPM(PE.CDFILIAL, PE.CDCIA, PE.CDEQUIP, DA.DTDATA) AND            
            SG.IDSERVICO <> '4' AND
            NOT EXISTS (SELECT 1
                           FROM PADVOOTREM PV
                                INNER JOIN SERVICOCIAFILI PVSF ON PVSF.CDFILIAL = PV.CDFILIAL
                                         AND PVSF.CDCIA = PV.CDCIA
                                         AND PVSF.NRSERVICOCIAFILI = PV.NRSERVICOCIAFILI
                                INNER JOIN SERVICOGEN PVSG ON PVSG.NRSERVICOGEN = PVSF.NRSERVICOGEN
                           WHERE PV.CDFILIAL = PE.CDFILIAL
                             AND PV.CDCIA = PE.CDCIA
                             AND PV.NRVOO = VT.NRVOO
                             AND PV.CDTRECHO = VT.CDTRECHO
                             AND PVSG.IDSERVICO = SG.IDSERVICO
                             AND PV.NRSEQPADVOOTREMDATA = ITEM_VIGENTE_PADVOOTREM(PV.CDFILIAL, PV.CDCIA, PV.NRVOO, PV.CDTRECHO, DA.DTDATA))
   ) PA
     INNER JOIN CLASSE CL ON CL.CDCLASSE = PA.CDCLASSE
     INNER JOIN CICLO CI ON CI.CDCICLO = PA.CDCICLO
     INNER JOIN BASE BO ON BO.CDBASE = PA.CDBASEORI
     INNER JOIN BASE BD ON BD.CDBASE = PA.CDBASEDES
/