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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             get_best_time.sql                           *

rem * Module Name:          get_best_time                               *

rem * Subsystem Name:       stored_procs                                *

rem * Originating Author:   James Creegan                               *

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

rem *                                                                   *

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

rem *                                                                   *

rem * Description:                                                      *

rem *  Function to find the most accurate arrival time for a flight     *

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 CREATE OR REPLACE FUNCTION get_best_time

CREATE OR REPLACE 
FUNCTION get_best_time
( p_flight_no IN flight.flight_no%TYPE,
  p_flight_date IN flight.flight_date%TYPE,
  p_site_id IN flight.site_id%TYPE)
RETURN flight_info_v.in_schd_arrival_time%TYPE
IS
  l_airport_code flight_info_v.in_airport_code%TYPE;
  l_best_date    flight_info_v.out_schd_flight_time%TYPE;
BEGIN
  SELECT airport_code
  INTO   l_airport_code
  FROM   site_info
  WHERE  site_id = p_site_id;

  SELECT NVL(in_act_arrival_time, NVL(in_est_arrival_time, in_schd_arrival_time))
  INTO l_best_date
  FROM flight_info_v
  WHERE in_flight_no = p_flight_no AND
        in_flight_date  = p_flight_date AND
        in_airport_code = l_airport_code;    

  RETURN(l_best_date);
EXCEPTION 
WHEN OTHERS THEN
  RETURN TO_DATE(NULL);
END;
/
