/*****************************************************************************
*
* Filename         - $Workfile:   terminating_count.sql  $
*
*                     Name              Date      Version     CR Number
*                Martin Helliwell     20/12/01      1.0        CR15540
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live Schema Package
*
* Language         - Oracle PL/SQL script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2001
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   terminating_count.sql  $  $Revision:   1.0  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/procedures/terminating_count.svl  $
* 
*    Rev 1.0   20 Dec 2001 13:29:32   tagtrak
* OR:
* CR: 15540
* REV: 1.0
* DESC: Count bags by uld, flight, dest, class
*
*****************************************************************************/
PROMPT CREATE OR REPLACE FUNCTION terminating_count

CREATE OR REPLACE
FUNCTION terminating_count
  (p_flight_id       IN flight.flight_id%TYPE
  ,p_pax_class       IN pax.pax_class%TYPE
  ,p_uld_no          IN ulds.uld_no%TYPE
  ,p_bag_destination IN bag.bag_destination%TYPE
  ) RETURN pax.no_of_bags%TYPE IS
  v_count pax.no_of_bags%TYPE;
  BEGIN
    IF p_pax_class IN ('Y','F','C') THEN
      SELECT COUNT(*)
      INTO v_count
      FROM bag_v2
      WHERE pax_id NOT IN 
        (SELECT pax_id
         FROM onward_flight)
      AND delete_marker = 0
      AND loaded_status = 'Y'
      AND flight_id = p_flight_id
      AND pax_class = p_pax_class
      AND uld_no = p_uld_no
      AND bag_destination = p_bag_destination;
    ELSIF p_pax_class = 'O' THEN
      SELECT COUNT(*)
      INTO v_count
      FROM bag_v2
      WHERE pax_id NOT IN 
        (SELECT pax_id
         FROM onward_flight)
      AND delete_marker = 0
      AND loaded_status = 'Y'
      AND flight_id = p_flight_id
      AND pax_class NOT IN ('Y','F','C')
      AND uld_no = p_uld_no
      AND bag_destination = p_bag_destination;
    ELSIF p_pax_class = 'T' THEN
      SELECT COUNT(*)
      INTO v_count
      FROM bag_v2
      WHERE pax_id NOT IN 
        (SELECT pax_id
         FROM onward_flight)
      AND delete_marker = 0
      AND loaded_status = 'Y'
      AND flight_id = p_flight_id
      AND uld_no = p_uld_no
      AND bag_destination = p_bag_destination;
    END IF;

    RETURN v_count;

  END terminating_count;
/
