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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             count_ulds.sql                              *

rem * Module Name:          count_ulds                                  *

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 count the number of ULDs loaded onto 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 count_ulds

CREATE OR REPLACE FUNCTION
count_ulds
(
 p_flight_id ulds.flight_id%TYPE
)
RETURN NUMBER 
IS
   l_count NUMBER;
   v_flight_type flight.flight_type%TYPE;
BEGIN
   BEGIN
    SELECT flight_type
      INTO v_flight_type
      FROM flight
     WHERE flight_id = p_flight_id;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
    v_flight_type := NULL;
   END;
   IF v_flight_type = 'O' THEN   
      SELECT COUNT(*) 
      INTO l_count
      FROM ulds 
      WHERE flight_id = p_flight_id;
   ELSIF v_flight_type = 'I' THEN
      SELECT COUNT(*) 
      INTO l_count
      FROM inbound_uld_v1 iuld
      WHERE inbound_flight_id = p_flight_id
        AND result_uld_status IN ('CREATED','AMENDED','UNPROCESSED');   
   END IF;
  RETURN(l_count);
END;
/
