
rem *********************************************************************

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Filename:             flight_info_v.sql                           *

rem * Module Name:          flight_info_v                               *

rem * Subsystem Name:       views                                       *

rem * Originating Author:   James Creegan                               *

rem * Design Reference:     FAS/BBTRK/SD/225                            *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Description:                                                      *

rem *  View built from flight_info and flight_info_inbound providing    *

rem * info about a flight as it will be when it arrives at airport X    *
 
rem * and as it was as it departed from previous airport Y              *

rem *********************************************************************

rem *                                                                   *

rem * Program Identification Array:                                     *

rem *                                                                   *

rem * "%Z%%M% VERSION %I% %E% %U%"

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Modification History:                                             *

rem *                                                                   *

rem * Rel Date/Time      Proj/Id   CA Ref/Details Of Change             *

rem * --- -------------- --------  ------------------------------------ *

rem * 1.0 28 May 03                File initially checked in            *

rem *********************************************************************/

PROMPT Creating view flight_info_v

CREATE OR REPLACE FORCE VIEW flight_info_v
 (IN_FLIGHT_NO,
  IN_FLIGHT_DATE,
  IN_AIRPORT_CODE,
  IN_SCHD_ARRIVAL_TIME,
  IN_EST_ARRIVAL_TIME,
  IN_ACT_ARRIVAL_TIME,
  IN_GATE,
  IN_APRON,
  IN_BLOCK_OFF,
  IN_AIRCRAFT_TYPE,
  IN_AIRCRAFT_REG,
  IN_PREV_AIRPORT,
  IN_PREV_DEP_TIME,
  IN_INTERFACE_ID,
  IN_RAW_DATA_ID,
  OUT_FLIGHT_NO,
  OUT_FLIGHT_DATE,
  OUT_AIRPORT_CODE,
  OUT_DEPARTURE_TIME,
  OUT_SCHD_FLIGHT_TIME,
  OUT_EST_FLIGHT_TIME,
  OUT_ACT_FLIGHT_TIME,
  OUT_AIRCRAFT_TYPE,
  OUT_AIRCRAFT_REG)
AS
  /*********************************************************/
  /* Information for an outbound flight can also be stored */
  /* in the inbound_flight_info table (arrival time etc.)  */
  /*********************************************************/
SELECT 
  inbound.flight_no,
  inbound.flight_date,
  inbound.airport_code,
  inbound.schd_arrival_time,
  inbound.est_arrival_time,
  inbound.act_arrival_time,
  inbound.gate,
  inbound.apron,
  inbound.block_off,
  inbound.aircraft_type,
  inbound.aircraft_registration,
  inbound.prev_airport,
  inbound.prev_departure_time,
  inbound.interface_id,
  inbound.raw_data_id,
  outbound.flight_no,
  outbound.flight_date,
  outbound.airport_code,
  outbound.departure_time,
  outbound.schd_flight_time,
  outbound.est_flight_time,
  outbound.act_flight_time,
  outbound.aircraft_type,
  outbound.aircraft_registration
FROM
  flight_info outbound, flight_info_inbound inbound
WHERE
  /****************************************************************/
  /* Recover all available information from the flight_info table */
  /* for this flight. Use both the outbound and inbound rows      */
  /****************************************************************/
  outbound.flight_no = inbound.flight_no       AND
  outbound.airport_code = inbound.prev_airport AND
  /****************************************************************/
  /* The departure time from the last airport should be within a  */
  /* set time window of the previous departure time from the      */
  /* outbound flight. In this case, 15 minutes.                   */
  /****************************************************************/
  (ABS(outbound.act_flight_time  - inbound.prev_departure_time ) < 15/60/24 OR
   ABS(outbound.est_flight_time  - inbound.prev_departure_time ) < 15/60/24 OR
   ABS(outbound.schd_flight_time - inbound.prev_departure_time ) < 15/60/24 );
