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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             get_arr_stand.sql                           *

rem * Module Name:          get_arr_stand                               *

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 return the stand that a flight will be arriving from *

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_dep_stand

CREATE OR REPLACE
FUNCTION get_dep_stand (
  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 stand_setup.stand%TYPE IS
  l_airport_code FLIGHT_INFO.AIRPORT_CODE%TYPE;
  l_gate FLIGHT_INFO.GATE%TYPE;
BEGIN
  /* What the screens call 'STAND' is actually the GATE */
  /* value in the FLIGHT_INFO table                     */

  SELECT airport_code
  INTO l_airport_code
  FROM site_info
  WHERE site_id=p_site_id;
  
  BEGIN
    /* Find out the gate value that the plane will land at */
    SELECT gate 
    INTO l_gate 
    FROM flight_info
    WHERE 
          airport_code = l_airport_code 
      AND normalise_flight_no(flight_no) = normalise_flight_no(p_flight_no)
      AND flight_date  = p_flight_date;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
    l_gate := NULL;
  END;
  RETURN rtrim(l_gate);
end;
/
