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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             get_transfer_time.sql                       *

rem * Module Name:          get_transfer_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 give the number of minutes difference between the    *

rem *  arr and departure times of an in and outbound pair of flights    *

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_transfer_time 

CREATE OR REPLACE 
FUNCTION get_transfer_time 
  (p_in_flight_no    IN FLIGHT.FLIGHT_NO%TYPE
  ,p_in_flight_date  IN FLIGHT.FLIGHT_DATE%TYPE
  ,p_out_flight_no   IN FLIGHT.FLIGHT_NO%TYPE
  ,p_out_flight_date IN FLIGHT.FLIGHT_DATE%TYPE
  ,p_site_id         IN FLIGHT.SITE_ID%TYPE)
RETURN number is
  l_arr_airport FLIGHT_INFO.AIRPORT_CODE%TYPE;
  l_in_arrival_time DATE;
  l_out_departure_time DATE;
  l_difference NUMBER;
BEGIN

  SELECT airport_code 
  INTO l_arr_airport
  FROM site_info
  WHERE site_id = p_site_id ;
  
  /* Get the arrival time at the next station for the inbound flight */
  SELECT NVL(in_act_arrival_time, NVL(in_est_arrival_time, in_schd_arrival_time))
  INTO l_in_arrival_time
  FROM flight_info_v 
  WHERE
        in_airport_code = l_arr_airport 
    AND out_flight_no    = p_in_flight_no
    AND out_flight_date  = p_in_flight_date;

  /* we can only trust the date portion of the departure_time field */
  SELECT TO_DATE(flight_est_date||' '||TO_CHAR(departure_time,'DDMONYYYY'), 'HH24MI DDMONYYYY')
  INTO l_out_departure_time
  FROM flight_outbound_v1
  WHERE
        site_id      = p_site_id
    AND flight_no    = p_out_flight_no
    AND flight_date  = p_out_flight_date;

  /* the difference must be in minutes */
  l_difference := 24*60*(l_out_departure_time - l_in_arrival_time) ;

  RETURN ROUND(l_difference);
EXCEPTION
  WHEN OTHERS THEN -- e.g. one of the time values is not provided 
    RETURN 0;
END get_transfer_time; 
/
