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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             through_uld.sql                             *

rem * Module Name:          through_uld                                 *

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 determine whether a given flight is a through 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 through_uld

CREATE OR REPLACE
FUNCTION through_uld (
  p_uld_no IN ulds.uld_no%TYPE,
  p_flight_id IN ulds.flight_id%TYPE
 )
RETURN VARCHAR2 IS
  l_count NUMBER;
BEGIN
  /* Is this airport one of the destinations of this flight? */
  SELECT COUNT(*) INTO l_count FROM uld_dests, flight_dests 
  WHERE uld_dests.uld_no    = p_uld_no AND
        uld_dests.flight_id = p_flight_id AND
        uld_dests.uld_destination = flight_dests.flight_destination AND
        flight_dests.dest_order = 1 AND
        flight_dests.dest_source = 'F' AND -- ordering is only trustworthy from the FIDS source
        flight_dests.flight_id = p_flight_id;
  /* return whether the ULD is stopping at the destination airport */
  /* or is a through flight                                        */
  IF l_count=0 THEN 
    RETURN ('Y');
  ELSE
    RETURN ('N');
  END IF;
END through_uld;
/

