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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             bag_count_terminating.sql                   *

rem * Module Name:          bag_count_terminating                       *

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 return number of bags terminating at this point      *

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 bag_count_terminating

CREATE OR REPLACE
FUNCTION bag_count_terminating
( p_in_flight_id   IN flight.flight_id%TYPE,
  p_exception_flag IN NUMBER
)
 RETURN NUMBER IS
  l_count number;
BEGIN
/* count how many bags from a specified inbound flight */
/* are terminating here                                */

  IF p_exception_flag=0 THEN
  /* count all */
    SELECT COUNT(bag.bag_tag)
    INTO l_count
    FROM bag, pax 
    WHERE
    bag.loaded_status = 'Y' AND 
    pax.flight_id     = p_in_flight_id AND
    pax.pax_id        = bag.pax_id AND
    NOT EXISTS  
    (SELECT * FROM onward_flight WHERE bag.pax_id = onward_flight.pax_id AND
                                       onward_flight.dest_order = 1 );
  ELSE
  /* only count the exceptions */
    SELECT COUNT(ltrim(bag.bag_exception))
    INTO l_count
    FROM bag, pax
    WHERE
      bag.loaded_status = 'Y' AND
      pax.flight_id     = p_in_flight_id AND  
      pax.pax_id        = bag.pax_id AND
    NOT EXISTS  
    (SELECT * FROM onward_flight WHERE bag.pax_id = onward_flight.pax_id AND
                                       onward_flight.dest_order = 1 );
  END IF;
  RETURN l_count;
END bag_count_terminating;
/
