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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             count_bags.sql                              *

rem * Module Name:          count_bags                                  *

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 specified 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_bags

CREATE OR REPLACE FUNCTION
count_bags
(
 p_flight_id ulds.flight_id%TYPE,
 p_exception_flag NUMBER
)
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 p_exception_flag = 0 THEN
      IF v_flight_type = 'O' THEN
         --  count all bags 
          SELECT COUNT(*) 
          INTO l_count
          FROM bag, pax 
          WHERE  
            bag.loaded_status = 'Y' AND
            bag.pax_id    = pax.pax_id AND
            pax.flight_id = p_flight_id ;
      ELSIF v_flight_type = 'I' THEN
          SELECT COUNT(*) 
          INTO l_count
          FROM inbound_bag_v1 ibag, inbound_pax_v1 ipax 
          WHERE  
            ibag.loaded_status = 'Y' AND
            ibag.inbound_pax_id    = ipax.inbound_pax_id AND
            ipax.inbound_flight_id = p_flight_id AND
            ipax.result_pax_status IN ('CREATED','AMENDED','UNPROCESSED') AND
            ibag.result_bag_status IN ('CREATED','AMENDED','UNPROCESSED');
      END IF;      
   ELSE
      IF v_flight_type = 'O' THEN   
         -- count exception bags 
          SELECT COUNT(*)
          INTO l_count
          FROM bag, pax 
          WHERE  
            bag.loaded_status = 'Y' AND
            bag.pax_id    = pax.pax_id AND
            pax.flight_id = p_flight_id AND
            ltrim(bag.bag_exception) IS NOT NULL ;
      ELSIF v_flight_type = 'I' THEN
          SELECT COUNT(*)
          INTO l_count
          FROM inbound_bag_v1 ibag, inbound_pax_v1 ipax 
          WHERE  
            ibag.loaded_status = 'Y' AND
            ibag.inbound_pax_id    = ipax.inbound_pax_id AND
            ipax.inbound_flight_id = p_flight_id AND
            ltrim(ibag.bag_exception) IS NOT NULL AND
            ipax.result_pax_status IN ('CREATED','AMENDED','UNPROCESSED') AND
            ibag.result_bag_status IN ('CREATED','AMENDED','UNPROCESSED');
      END IF;
   END IF;
   RETURN(l_count);
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;
/
