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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             get_dest.sql                                *

rem * Module Name:          get_dest                                    *

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 extract the destination of a flight using its  FID   *

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_dest 

CREATE OR REPLACE 
FUNCTION get_dest 
 (p_flight_id IN flight.flight_id%TYPE)
RETURN flight_dests.flight_destination%TYPE
IS
  l_local_dest flight_dests.flight_destination%TYPE;
  l_first_dest_source  flight_dests.dest_source%TYPE;
  l_dest_count         NUMBER;

BEGIN

    /* dest source may be single or mixed F (FIDS/FOM ?), B (BSM) and M (MWS). 
       We can always guarantee the next destination if the first dest_source 
       is 'F' for single or multi-destination flights */

    SELECT dest_source
    INTO   l_first_dest_source
    FROM   flight_dests
    WHERE  flight_id = p_flight_id   
    AND    dest_order=1;
  
    SELECT COUNT(*)
    INTO   l_dest_count
    FROM   flight_dests
    WHERE  flight_id = p_flight_id;
  
    IF l_first_dest_source = 'F' THEN

        SELECT flight_destination
        INTO   l_local_dest
        FROM   flight_dests
        WHERE  flight_id = p_flight_id
        AND    dest_order=1;  -- We can only trust the order from FIDS

    ELSIF ((l_first_dest_source = 'B' OR l_first_dest_source = 'M') AND l_dest_count = 1) THEN

    /* If flight_dests is populated by BSMs or MWS and the first dest_source is not 'F' 
       then we can only guarantee the first destination for single destination flights */

  SELECT flight_destination
  INTO l_local_dest
  FROM flight_dests
  WHERE flight_id=p_flight_id
        AND    dest_order=1;

    ELSE
      
        l_local_dest := '';

    END IF;     
 
  RETURN (l_local_dest);

END;
/

