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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             count_by_uld.sql                            *

rem * Module Name:          count_by_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 count the number of bags on a flight, on a ULD       *

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_by_uld

CREATE OR REPLACE
FUNCTION count_by_uld
  (p_flight_id       IN flight.flight_id%TYPE
  ,p_uld_no          IN ulds.uld_no%TYPE
  ,p_exception       IN NUMBER
  ) RETURN pax.no_of_bags%TYPE IS
  v_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;
     
      /* count everything */
    IF p_exception = '0' THEN
      IF v_flight_type = 'O' THEN
         SELECT COUNT(*)
         INTO v_count
         FROM BAG
         WHERE 
         loaded_status = 'Y'
         AND nvl(uld_flight_id,-999) = p_flight_id
         AND uld_no = p_uld_no;
      ELSIF v_flight_type = 'I' THEN
         SELECT COUNT(*)
          INTO v_count
          FROM inbound_uld_v1 iuld,  inbound_bag_v1 ibag
         WHERE iuld.inbound_flight_id = p_flight_id
           AND iuld.uld_no = p_uld_no
           AND ibag.inbound_uld_id  = iuld.inbound_uld_id
           AND ibag.loaded_status = 'Y'
           AND iuld.result_uld_status IN ('CREATED','AMENDED','UNPROCESSED')
           AND ibag.result_bag_status IN ('CREATED','AMENDED','UNPROCESSED');
      END IF;
    ELSE 
      /* only count exceptions */
      IF v_flight_type = 'O' THEN
         SELECT COUNT(*)
         INTO v_count
         FROM BAG
         WHERE 
         loaded_status = 'Y'
         AND nvl(uld_flight_id,-999) = p_flight_id
         AND uld_no = p_uld_no
         AND ltrim(bag_exception) is not null  ;
      ELSIF v_flight_type = 'I' THEN
         SELECT COUNT(*)
          INTO v_count
          FROM inbound_uld_v1 iuld,  inbound_bag_v1 ibag
         WHERE iuld.inbound_flight_id = p_flight_id
           AND iuld.uld_no = p_uld_no
           AND ibag.inbound_uld_id  = iuld.inbound_uld_id
           AND ibag.loaded_status = 'Y'
           AND TRIM(ibag.bag_exception) IS NOT NULL
           AND iuld.result_uld_status IN ('CREATED','AMENDED','UNPROCESSED')
           AND ibag.result_bag_status IN ('CREATED','AMENDED','UNPROCESSED');
      END IF;
    END IF;

    RETURN v_count;

  END count_by_uld;
/
