/*****************************************************************************
 *
 * Filename         - $Workfile:   hbmd_pbc.sql  $
 *
 *                    Name              Date      Version     CR Number
 * Author           - Chris Reason    08/07/03
 * Last Update      - See revision history below.
 *
 * Project Name     - FALCP.
 *
 * Source Code
 * Identifier       - N/A.
 *
 * Design Spec
 * Section          - N/A.
 *
 * Contents         - Live schema package: Creates the hbmd_pbc package.
 *                    This package returns pax/bag counts that include HBMD
 *                    totals only (for loaded, crew, crew_untagged, rush,
 *                    gate, mail, dipl and standby totals).
 *
 * Language         - Oracle PL/SQL.
 *
 * Classification   - Commercial In Confidence
 *                    (c) Ultra Electronics Limited, 2003
 *
 *****************************************************************************/

PROMPT CREATE OR REPLACE PACKAGE hbmd_pbc 

CREATE OR REPLACE PACKAGE hbmd_pbc AS

/******************************************************************************
 *
 * PROCEDURE get_hbmd_bag_count
 *
 * Description:
 *   This procedure derives and returns the HBMD eligible bag counts (loaded,
 *   crew, rush, standby) for a flight.
 *
 * Parameters:
 *   p_flight_id    - Internal flight row pointer.
 *   p_bags_loaded  - Number of HBMD loaded bags for the flight.
 *   p_bags_crew    - Number of HBMD crew bags for the flight.
 *   p_bags_rush    - Number of HBMD rush bags for the flight.
 *   p_bags_gate    - Number of HBMD gate bags for the flight.
 *   p_bags_mail    - Number of HBMD mail bags for the flight.
 *   p_bags_dipl    - Number of HBMD dipl bags for the flight.
 *   p_bags_standby - Number of HBMD standby bags for the flight.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE get_hbmd_bag_count
   (p_flight_id    IN  flight.flight_id%TYPE,
    p_bags_loaded  OUT INTEGER,
    p_bags_crew    OUT INTEGER,
    p_bags_rush    OUT INTEGER,
    p_bags_gate    OUT INTEGER,
    p_bags_mail    OUT INTEGER,
    p_bags_dipl    OUT INTEGER,
    p_bags_standby OUT INTEGER );

END hbmd_pbc;
/

PROMPT CREATE OR REPLACE PACKAGE BODY hbmd_pbc

CREATE OR REPLACE PACKAGE BODY hbmd_pbc AS

/******************************************************************************
 *
 * FUNCTION bags_loaded
 *
 * Description:
 *   This function derives and returns the sum of the flight's loaded bags
 *   that are eligible to be included in a HBMD count.
 *
 * Parameters:
 *   p_flight_id - Internal flight row pointer.
 *
 * Errors raised:
 *   None.
 *
 * Returns:
 *   A flight's loaded bags for a HBMD count.
 *
 ******************************************************************************/

FUNCTION bags_loaded(p_flight_id IN flight.flight_id%TYPE)
   RETURN INTEGER IS
   l_bags_loaded INTEGER DEFAULT 0;
BEGIN
   SELECT COUNT(*) INTO l_bags_loaded
   FROM bag b, pax p
   WHERE b.delete_marker = 0
     AND b.loaded_status = 'Y'
     AND p.pax_status IN ('C','B')
     AND b.authority_to_load_dcs = 'Y'
     AND (b.bag_exception IS NULL  or   --  some bags have exception of CREW or AMXL
          b.bag_exception not in ('GATE','RUSH','MAIL','DIPL','CREW') )
     AND b.hbmd_flag = 'Y'
     AND b.pax_id = p.pax_id
     AND p_flight_id = p.flight_id;
   RETURN NVL(l_bags_loaded, 0);
END bags_loaded;

/******************************************************************************
 *
 * FUNCTION bags_crew
 *
 * Description:
 *   This function derives and returns the sum of the flight's crew bags
 *   that are eligible to be included in a HBMD count.
 *
 * Parameters:
 *   p_flight_id - Internal flight row pointer.
 *
 * Errors raised:
 *   None.
 *
 * Returns:
 *   A flight's crew bags for a HBMD count.
 *
 ******************************************************************************/

FUNCTION bags_crew(p_flight_id IN flight.flight_id%TYPE)
   RETURN INTEGER IS
   l_bags_crew INTEGER DEFAULT 0;
BEGIN
   SELECT COUNT(*) INTO l_bags_crew
   FROM bag b, pax p
   WHERE b.delete_marker = 0
     AND b.loaded_status = 'Y'
     AND p.pax_status IN ('C','B')
     AND b.loaded_status = 'Y'
     AND b.bag_exception = 'CREW'
     AND b.hbmd_flag = 'Y'
     AND b.pax_id = p.pax_id 
     AND p_flight_id = p.flight_id;
   RETURN NVL(l_bags_crew, 0);
END bags_crew;

/******************************************************************************
 *
 * FUNCTION bags_crew_untagged
 *
 * Description:
 *   This function derives and returns the sum of the flight's untagged crew
 *   bags that are eligible to be included in a HBMD count.
 *
 * Parameters:
 *   p_flight_id - Internal flight row pointer.
 *
 * Errors raised:
 *   None.
 *
 * Returns:
 *   A flight's untagged crew bags for a HBMD count.
 *
 ******************************************************************************/

FUNCTION bags_crew_untagged(p_flight_id IN flight.flight_id%TYPE)
   RETURN INTEGER IS
   l_bags_crew_untagged INTEGER DEFAULT 0;
BEGIN
   SELECT no_of_bags INTO l_bags_crew_untagged
   FROM  pax p, bag_exception_types bet
   WHERE p.exception_type_id = bet.bag_excep_id
     AND p.pax_sequence_no = bet.bag_excep_pax_seq
     AND bet.bag_excep_code = 'CREW'
     AND flight_id = p_flight_id;
   RETURN NVL(l_bags_crew_untagged, 0);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
       RETURN 0;
END bags_crew_untagged;

/******************************************************************************
 *
 * FUNCTION bags_rush
 *
 * Description:
 *   This function derives and returns the sum of the flight's rush bags
 *   that are eligible to be included in a HBMD count.
 *
 * Parameters:
 *   p_flight_id - Internal flight row pointer.
 *
 * Errors raised:
 *   None.
 *
 * Returns:
 *   A flight's rush bags for a HBMD count.
 *
 ******************************************************************************/

FUNCTION bags_rush(p_flight_id IN flight.flight_id%TYPE) RETURN INTEGER IS
   l_bags_rush INTEGER DEFAULT 0;
BEGIN
   SELECT COUNT(*) INTO l_bags_rush
   FROM bag b, pax p
   WHERE b.delete_marker = 0
     AND b.loaded_status = 'Y'
     AND p.pax_status IN ('C','B')
     AND b.authority_to_load_dcs = 'Y'
     AND b.bag_exception = 'RUSH'
     AND b.hbmd_flag = 'Y'
     AND b.pax_id = p.pax_id
     AND p_flight_id = p.flight_id;
   RETURN NVL(l_bags_rush, 0);
END bags_rush;

/******************************************************************************
 *
 * FUNCTION bags_gate
 *
 * Description:
 *   This function derives and returns the sum of the flight's gate bags
 *   that are eligible to be included in a HBMD count.
 *
 * Parameters:
 *   p_flight_id - Internal flight row pointer.
 *
 * Errors raised:
 *   None.
 *
 * Returns:
 *   A flight's gate bags for a HBMD count.
 *
 ******************************************************************************/

FUNCTION bags_gate(p_flight_id IN flight.flight_id%TYPE) RETURN INTEGER IS
   l_bags_gate INTEGER DEFAULT 0;
BEGIN
   SELECT COUNT(*) INTO l_bags_gate
   FROM bag b, pax p
   WHERE b.delete_marker = 0
     AND b.loaded_status = 'Y'
     AND p.pax_status IN ('C','B')
     AND b.authority_to_load_dcs = 'Y'
     AND b.bag_exception = 'GATE'
     AND b.hbmd_flag = 'Y'
     AND b.pax_id = p.pax_id
     AND p_flight_id = p.flight_id;
   RETURN NVL(l_bags_gate, 0);
END bags_gate;


/******************************************************************************
 *
 * FUNCTION bags_mail
 *
 * Description:
 *   This function derives and returns the sum of the flight's mail bags
 *   that are eligible to be included in a HBMD count.
 *
 * Parameters:
 *   p_flight_id - Internal flight row pointer.
 *
 * Errors raised:
 *   None.
 *
 * Returns:
 *   A flight's rush bags for a HBMD count.
 *
 ******************************************************************************/

FUNCTION bags_mail(p_flight_id IN flight.flight_id%TYPE) RETURN INTEGER IS
   l_bags_mail INTEGER DEFAULT 0;
BEGIN
   SELECT COUNT(*) INTO l_bags_mail
   FROM bag b, pax p
   WHERE b.delete_marker = 0
     AND b.loaded_status = 'Y'
     AND p.pax_status IN ('C','B')
     AND b.authority_to_load_dcs = 'Y'
     AND b.bag_exception = 'MAIL'
     AND b.hbmd_flag = 'Y'
     AND b.pax_id = p.pax_id
     AND p_flight_id = p.flight_id;
   RETURN NVL(l_bags_mail, 0);
END bags_mail;


/******************************************************************************
 *
 * FUNCTION bags_dipl
 *
 * Description:
 *   This function derives and returns the sum of the flight's dipl bags
 *   that are eligible to be included in a HBMD count.
 *
 * Parameters:
 *   p_flight_id - Internal flight row pointer.
 *
 * Errors raised:
 *   None.
 *
 * Returns:
 *   A flight's dipl bags for a HBMD count.
 *
 ******************************************************************************/

FUNCTION bags_dipl(p_flight_id IN flight.flight_id%TYPE) RETURN INTEGER IS
   l_bags_dipl INTEGER DEFAULT 0;
BEGIN
   SELECT COUNT(*) INTO l_bags_dipl
   FROM bag b, pax p
   WHERE b.delete_marker = 0
     AND b.loaded_status = 'Y'
     AND p.pax_status IN ('C','B')
     AND b.authority_to_load_dcs = 'Y'
     AND b.bag_exception = 'DIPL'
     AND b.hbmd_flag = 'Y'
     AND b.pax_id = p.pax_id
     AND p_flight_id = p.flight_id;
   RETURN NVL(l_bags_dipl, 0);
END bags_dipl;


/******************************************************************************
 *
 * FUNCTION bags_standby
 *
 * Description:
 *   This function derives and returns the sum of the flight's standby bags
 *   that are eligible to be included in a HBMD count.
 *
 * Parameters:
 *   p_flight_id - Internal flight row pointer.
 *
 * Errors raised:
 *   None.
 *
 * Returns:
 *   A flight's standby bags for a HBMD count.
 *
 ******************************************************************************/

FUNCTION bags_standby(p_flight_id IN flight.flight_id%TYPE) RETURN INTEGER IS
   l_bags_standby INTEGER DEFAULT 0;
BEGIN
   SELECT COUNT(*) INTO l_bags_standby
   FROM bag b, pax p
   WHERE b.delete_marker = 0
     AND b.loaded_status = 'Y'
     AND p.pax_status = 'S'
     AND b.hbmd_flag = 'Y'
     AND b.pax_id = p.pax_id
     AND p_flight_id = p.flight_id;
   RETURN NVL(l_bags_standby, 0);
END bags_standby;


/******************************************************************************
 *
 * PROCEDURE get_hbmd_bag_count
 *
 * Description:
 *   This procedure derives and returns the HBMD eligible bag counts (loaded,
 *   crew, rush, standby) for a flight.
 *
 * Parameters:
 *   p_flight_id    - Internal flight row pointer.
 *   p_bags_loaded  - Number of HBMD loaded bags for the flight.
 *   p_bags_crew    - Number of HBMD crew bags for the flight.
 *   p_bags_rush    - Number of HBMD rush bags for the flight.
 *   p_bags_gate    - Number of HBMD gate bags for the flight.
 *   p_bags_mail    - Number of HBMD mail bags for the flight.
 *   p_bags_dipl    - Number of HBMD dipl bags for the flight.
 *   p_bags_standby - Number of HBMD standby bags for the flight.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE get_hbmd_bag_count
   (p_flight_id    IN  flight.flight_id%TYPE,
    p_bags_loaded  OUT INTEGER,
    p_bags_crew    OUT INTEGER,
    p_bags_rush    OUT INTEGER,
    p_bags_gate    OUT INTEGER,
    p_bags_mail    OUT INTEGER,
    p_bags_dipl    OUT INTEGER,
    p_bags_standby OUT INTEGER) 
IS
   p_tagged   VARCHAR2(1);
BEGIN
   -- Derive the bag counts to be returned
   p_bags_loaded := NVL( bags_loaded(p_flight_id), 0 );
   
   SELECT tag_crew_bags
   INTO p_tagged
   FROM flight f,
        carrier_lookup cl
   WHERE f.flight_id = p_flight_id
   AND   cl.alpha_code = substr(f.flight_no, 1, 3);

   IF p_tagged = 'Y'
   THEN
      p_bags_crew :=  NVL( bags_crew(p_flight_id), 0 );
   ELSE
      p_bags_crew := NVL( bags_crew_untagged(p_flight_id), 0 ) ;
   END IF;

   p_bags_rush := NVL( bags_rush(p_flight_id), 0 );
   p_bags_standby := NVL( bags_standby(p_flight_id), 0 );
   p_bags_gate := NVL( bags_gate(p_flight_id), 0 );
   p_bags_mail := NVL( bags_mail(p_flight_id), 0 );
   p_bags_dipl := NVL( bags_dipl(p_flight_id), 0 );

END get_hbmd_bag_count;

END hbmd_pbc;
/

