/*****************************************************************************
*
* Filename         - $Workfile:   load_where.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 
*
* Design Spec
* Section          - UAS/FALCP/SS/1959
*                    UAS/FALCP/CA/1878
*
* Contents         - Live Schema Stored Procedures used by the Load Where
*                    loading strategy
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/


PROMPT CREATE OR REPLACE PACKAGE load_where

CREATE OR REPLACE PACKAGE load_where IS

FUNCTION get_bag_details(
      p_user_id       IN  tagtrak_users.user_id%TYPE,
      p_bag_tag       IN  bag.bag_tag%TYPE,
      p_flight_id_in  IN  flight.flight_id%TYPE,
      p_db_bag_tag    OUT bag.bag_tag%TYPE,
      p_flight_id_out OUT flight.flight_id%TYPE,
      p_pax_id        OUT pax.pax_id%TYPE)
      RETURN error.error_id%TYPE;

FUNCTION check_bag_to_be_loaded(
      p_user_id      IN  tagtrak_users.user_id%TYPE,
      p_flight_id    IN  flight.flight_id%TYPE,
      p_db_bag_tag   IN  bag.bag_tag%TYPE,
      p_pax_id       IN  pax.pax_id%TYPE,
      p_failed_event OUT tracking_events.event_no%TYPE,
      p_group_no     OUT bag_group.group_no%TYPE,
      p_security_result OUT bag.security_result%type,
      p_security_reason OUT bag.security_reason%type)
      RETURN error.error_id%TYPE;

FUNCTION find_uld(
      p_db_bag_tag IN  bag.bag_tag%TYPE,
      p_pax_id     IN  pax.pax_id%TYPE,
      p_flight_id  IN  flight.flight_id%TYPE,
      p_uld_no     OUT ulds.uld_no%TYPE)
      RETURN error.error_id%TYPE;

PROCEDURE send_bpms(
      p_load_result IN NUMBER,
      p_flight_id   IN flight.flight_id%TYPE,
      p_pax_id      IN pax.pax_id%TYPE,
      p_db_bag_tag  IN bag.bag_tag%TYPE,
      p_scanner_id  IN CHAR,
      p_user_id     IN tagtrak_users.user_id%TYPE,
      p_location    IN location.location_code%TYPE);

END load_where;
/


PROMPT CREATE OR REPLACE PACKAGE BODY load_where

CREATE OR REPLACE PACKAGE BODY load_where IS

-- Define weak REF CURSOR type
TYPE gen_curtype IS REF CURSOR;

-- Define a type to match the bag search
TYPE bag_search_type IS RECORD(
   bag_tag         bag.bag_tag%TYPE,
   delete_marker   bag.delete_marker%TYPE,
   pax_id          pax.pax_id%TYPE,
   flight_id       flight.flight_id%TYPE);


/******************************************************************************
 *
 * FUNCTION get_bag_details
 *
 * Description:
 *   Function to retrieve the bag tag (as stored in the database), flight ID
 *   and passenger ID for a given bag tag that has been scanned. It is possible
 *   to also supply a FLIGHT_ID.
 *   The scanner user's user ID must be supplied so that the user's access to
 *   the flight(s) in question can be checked. If multiple bags are found, the
 *   appropriate error condition will be returned (see below), as well as a
 *   list of possible dates.
 *
 *   This procedure will be called once or twice for a particular bag:
 *   1) With p_bag_tag set, but p_flight_id_in NULL (straight after scan)
 *   2) With p_bag_tag + p_flight_id_in set, after user has entered FLT and date
 *
 * Input Parameters:
 *   p_user_id       - The scanner user's user ID
 *   p_bag_tag       - The bag tag as input or scanned
 *   p_flight_id_in  - The FLIGHT_ID of this bag's flight (may be NULL)
 *
 * Output Parameters:
 *   p_db_bag_tag    - The bag tag as stored in the database
 *   p_flight_id_out - The FLIGHT_ID of the flight which this bag is on
 *   p_pax_id        - The PAX_ID of this bag's passenger
 *
 * Returns:
 *   SUCCESS                - Exactly one bag was found matching the parameters
 *   UNKNOWN_TAG            - No bags were found matching the parameters
 *   CLOSED_FLIGHT          - The parameters only match bags on closed flights
 *   ACCESS_DENIED          - The parameters only match bags on flights to which
 *                            the user doesn't have access
 *   DELETED_TAG            - The parameters only match deleted bag(s)
 *   DUPLICATE_TAG          - More information needs to be supplied (FLIGHT_ID)
 *   UNRESOLVABLE_DUPLICATE - > 1 bags match the parameters on the same flight
 *
 * Errors Raised:
 *   None
 *
 ******************************************************************************/
FUNCTION get_bag_details(
      p_user_id       IN  tagtrak_users.user_id%TYPE,
      p_bag_tag       IN  bag.bag_tag%TYPE,
      p_flight_id_in  IN  flight.flight_id%TYPE,
      p_db_bag_tag    OUT bag.bag_tag%TYPE,
      p_flight_id_out OUT flight.flight_id%TYPE,
      p_pax_id        OUT pax.pax_id%TYPE)
      RETURN error.error_id%TYPE IS

   -- Bag search cursor
   -- If the flight_id parameter is 0, it is ignored
   CURSOR c_bag_search(
      p_c_bag_tag2 IN bag.bag_tag2a%TYPE,
      p_c_bag_tag3 IN bag.bag_tag3%TYPE,
      p_c_flight_id IN flight.flight_id%TYPE) IS
   SELECT b.bag_tag, b.delete_marker, p.pax_id, f.flight_id
   FROM bag b, flight f, pax p
   WHERE b.pax_id = p.pax_id
      AND p.flight_id = f.flight_id
      AND b.bag_tag3 = p_c_bag_tag3
      AND (b.bag_tag2a = p_c_bag_tag2 OR b.bag_tag2b = p_c_bag_tag2)
      AND (p_c_flight_id = 0 OR p_c_flight_id = f.flight_id);

   -- Bag tag storage
   l_tag1             bag.bag_tag1%TYPE;
   l_tag2a            bag.bag_tag2a%TYPE;
   l_tag2b            bag.bag_tag2b%TYPE;
   l_tag3             bag.bag_tag3%TYPE;

   -- Information returned from split_tags
   l_tagno_num        bag.bag_tag%TYPE;
   l_tag_type         NUMBER;

   -- Information returned from validate_access and loading_prohibited
   l_user_ok          NUMBER;
   l_flight_pd        BOOLEAN;

   -- Bags found
   l_ok_bag_count     NUMBER := 0;
   l_del_bag_count    NUMBER := 0;
   l_unauth_bag_count NUMBER := 0;
   l_pd_flt_bag_count NUMBER := 0;
   l_date_list        VARCHAR2(500) := '';
   l_ok_bag_rec       bag_search_type;
   l_failed_bag_rec   bag_search_type;

   -- Value to return
   l_return_value     error.error_id%TYPE := 0;
BEGIN
   -- Initialise out parameters
   p_flight_id_out := 0;
   p_pax_id        := 0;

   -- Split tag into constituent parts
   split_tags(p_bag_tag, l_tag1, l_tag2a, l_tag2b, l_tag3, l_tagno_num,
      l_tag_type);

   -- Search for bag(s) matching the input criteria
   -- Note: use tag_2b if available, drop back to tag2a if not
   FOR bag_search_rec IN c_bag_search(NVL(l_tag2b, l_tag2a), l_tag3,
      NVL(p_flight_id_in, 0))
   LOOP
      -- See if the user has access to the flight in question
      user_authentication.validate_access(p_user_id, bag_search_rec.flight_id,
         NULL, NULL, l_user_ok);

      -- Check that the flight has not departed
      l_flight_pd := loading_prohibited(bag_search_rec.flight_id);

      IF l_flight_pd THEN
         -- FAILURE => Increment PD flight count
         l_pd_flt_bag_count := l_pd_flt_bag_count + 1;
         l_failed_bag_rec   := bag_search_rec;
      ELSIF l_user_ok != 0 THEN
         -- FAILURE => Increment unauthorised bag count
         l_unauth_bag_count := l_unauth_bag_count + 1;
         l_failed_bag_rec   := bag_search_rec;
      ELSIF bag_search_rec.delete_marker != 0 THEN
         -- FAILURE => Increment deleted bag count
         l_del_bag_count  := l_del_bag_count + 1;
         l_failed_bag_rec := bag_search_rec;
      ELSE
         -- SUCCESS => Increment "OK" bag count
         l_ok_bag_count := l_ok_bag_count + 1;
         l_ok_bag_rec   := bag_search_rec;
      END IF;
   END LOOP;

   -- Set return value as appropriate
   IF l_ok_bag_count = 0 THEN

      IF l_pd_flt_bag_count > 0 THEN
         -- Bag(s) were found matching tag, but flight has post departed
         l_return_value := -1081; -- CLOSED_FLIGHT
      ELSIF l_unauth_bag_count > 0 THEN
         -- Bag(s) were found matching tag, but user doesn't have access
         l_return_value := -1031; -- ACCESS_DENIED
      ELSIF l_del_bag_count > 0 THEN
         -- Bag(s) were found matching tag, but were deleted
         l_return_value := -1019; -- DELETED_TAG
      ELSE
         -- No bags match the tag
         l_return_value := -1002; -- UNKNOWN_TAG
      END IF;

      -- If exactly one failed bag was found, pass out its details for tracing
      IF (l_unauth_bag_count + l_del_bag_count) = 1 THEN
         p_db_bag_tag    := l_failed_bag_rec.bag_tag;
         p_flight_id_out := l_failed_bag_rec.flight_id;
         p_pax_id        := l_failed_bag_rec.pax_id;
      END IF;

   ELSIF l_ok_bag_count = 1 THEN
      -- Exactly one bag matches the tag
      l_return_value  := 0; -- SUCCESS
      p_db_bag_tag    := l_ok_bag_rec.bag_tag;
      p_flight_id_out := l_ok_bag_rec.flight_id;
      p_pax_id        := l_ok_bag_rec.pax_id;
   ELSE
      -- More than one bag matches the tag
      IF (p_flight_id_in IS NULL) OR (p_flight_id_in = 0) THEN
         -- Indicate that the user needs to supply more information
         l_return_value := -1003; -- DUPLICATE_TAG
      ELSE
         -- p_flight_id_in has been supplied and we're getting multiple bags
         l_return_value := -1021; -- UNRESOLVABLE_DUPLICATE
      END IF;
   END IF;

   RETURN l_return_value;
END get_bag_details;


/******************************************************************************
 *
 * FUNCTION check_bag_to_be_loaded
 *
 * Description:
 *   Performs various checks on the status of the bag to be loaded, and the
 *   corresponding passenger, to ensure that it is ok to be loaded.
 *
 * Input Parameters:
 *   p_user_id      - The scanner user's user ID
 *   p_flight_id    - The FLIGHT_ID of this bag's flight
 *   p_db_bag_tag   - The bag tag as stored in the database
 *   p_pax_id       - The PAX_ID of this bag's passenger
 *
 * Output Parameters:
 *   p_failed_event - Event number if mandatory tracking event fails
 *   p_group_no     - Group number if bag is grouped
 *
 * Returns:
 *   SUCCESS        - Exactly one bag was found matching the parameters
 *   Various errors - see code for details
 *
 * Errors Raised:
 *   None
 *
 ******************************************************************************/
FUNCTION check_bag_to_be_loaded(
      p_user_id      IN  tagtrak_users.user_id%TYPE,
      p_flight_id    IN  flight.flight_id%TYPE,
      p_db_bag_tag   IN  bag.bag_tag%TYPE,
      p_pax_id       IN  pax.pax_id%TYPE,
      p_failed_event OUT tracking_events.event_no%TYPE,
      p_group_no     OUT bag_group.group_no%TYPE,
      p_security_result OUT bag.security_result%type,
      p_security_reason OUT bag.security_reason%type)
      RETURN error.error_id%TYPE IS
   -- Cursor for selecting the bag
   -- Note primary key is combination of bag_tag and pax_id
   CURSOR c_bag IS
   SELECT * FROM bag
   WHERE bag_tag = p_db_bag_tag AND pax_id = p_pax_id;

   -- Cursor for selecting the passenger
   CURSOR c_pax IS
   SELECT * from pax
   WHERE pax_id = p_pax_id;

   -- Bag/passenger details storage
   l_bag bag%ROWTYPE;
   l_pax pax%ROWTYPE;

   -- Mandatory event checking out parameters
   l_event_description tracking_events.description%TYPE;
   l_trk_return_code   NUMBER;

   -- Value to return
   l_return_value error.error_id%TYPE := 0; -- SUCCESS
BEGIN
   -- Read bag details
   OPEN c_bag;
   FETCH c_bag INTO l_bag;

   -- Check bag was found
   IF c_bag%NOTFOUND THEN
      CLOSE c_bag;
      l_return_value := -1002; -- UNKNOWN_TAG
   ELSE
      CLOSE c_bag;

      -- Check mandatory events have been cleared and that bag is not grouped
      tracking.check_events_clear(p_user_id, l_bag.bag_tag, p_pax_id, p_flight_id,
         p_failed_event, l_event_description, l_trk_return_code, p_group_no);

      -- Check return value from mandatory event check, and bag status flags
      IF l_trk_return_code != 0 THEN
         l_return_value := l_trk_return_code; -- BAG_GROUPED/EVENT_NOT_CLEARED
      ELSIF l_bag.loaded_status = 'Y' THEN
         l_return_value := -20062; -- BAG_ALREADY_LOADED
      ELSIF l_bag.security_clearance_status = 'N' THEN
	 p_security_result := l_bag.security_result;
	 p_security_reason := l_bag.security_reason;
         l_return_value := -1068; -- NOT_SECURITY_CLEARED
      END IF;
   END IF;

   IF l_return_value = 0 THEN
      -- If everything's still ok, read passenger details
      OPEN c_pax;
      FETCH c_pax INTO l_pax;

      -- Check pax was found
      IF c_pax%NOTFOUND THEN
         CLOSE c_pax;
         l_return_value := -601; -- NO_PAX_FOUND
      ELSE
         CLOSE c_pax;

         -- Check pax status and VFDB flags
         IF l_pax.vfdb = 'Y' and l_bag.authority_to_load_dcs != 'Y' then
            l_return_value := -1110; -- PAX_VFDB
         ELSIF l_pax.pax_status = 'S' THEN
            l_return_value := -1047; -- STANDBY_PAX
         ELSIF l_pax.pax_status = 'N' THEN
            l_return_value := -1048; -- NOT_CHECKED_IN
         ELSIF l_pax.pax_status NOT IN ('C', 'B', 'S', 'N') THEN
            l_return_value := -1049; -- STATUS_NOT_CBSN
         END IF;
      END IF;
   END IF;

   IF l_return_value = 0 THEN
      -- If everything's still ok, check bag ATL flags
      IF l_bag.authority_to_load_dcs != 'Y' THEN
         l_return_value := -1004; -- NOT_ATL_BY_DCS
      ELSIF l_bag.authority_to_load_brs != 'Y' THEN
         l_return_value := -1005; -- NOT_ATL_BY_BRS
      END IF;
   END IF;

   RETURN l_return_value;
END check_bag_to_be_loaded;


/******************************************************************************
 *
 * PRIVATE FUNCTION check_segregate_results
 *
 * Description:
 *   Checks output parameters from a baggage_segregation_strategies.segregate
 *   call to see if the bag is ok to load into this ULD or not.
 *
 * Input Parameters:
 *   p_precision_loading - Whether the carrier has precision loading enabled
 *   p_first_onward_dest - The passenger's first onward destination
 *   p_uld_destination   - The ULD destination
 *   p_uld_dest_count    - The number of ULD destinations
 *   p_uld_class_list    - The ULD destination class list
 *   p_uld_onward_flight - The ULD destination onward flight
 *   p_uld_onward_date   - The ULD destination onward flight date
 *   p_got_onward_class  \
 *   p_got_onward_dest    |
 *   p_got_better_uld     |_ OUT parameters from the call to
 *   p_got_grp_dest       |  baggage_segregation_strategies.segregate
 *   p_got_type           |
 *   p_got_local_through /
 *
 * Returns:
 *   TRUE   - If segregation results passed for this bag/ULD
 *   FALSE  - If segregation results failed for this bag/ULD
 *
 * Errors Raised:
 *   None
 *
 ******************************************************************************/
FUNCTION check_segregate_results(
      p_precision_loading IN carrier_lookup.precision_loading%TYPE,
      p_first_onward_dest IN onward_flight.dest%TYPE,
      p_uld_destination   IN uld_dests.uld_destination%TYPE,
      p_uld_dest_count    IN NUMBER,
      p_uld_class_list    IN uld_dests.uld_class_list%TYPE,
      p_uld_onward_flight IN uld_dests.onward_flight_no%TYPE,
      p_uld_onward_date   IN uld_dests.onward_flight_date%TYPE,
      p_got_onward_class  IN NUMBER,
      p_got_onward_dest   IN NUMBER,
      p_got_better_uld    IN NUMBER,
      p_got_grp_dest      IN NUMBER,
      p_got_type          IN NUMBER,
      p_got_local_through IN NUMBER)
      RETURN BOOLEAN IS
   l_this_uld_ok BOOLEAN := FALSE;
BEGIN
   IF (p_got_type = 1) AND (p_got_better_uld = 0) THEN
      IF (p_precision_loading != 'Y') OR (p_first_onward_dest IS NULL) THEN
         -- Ok if precision loading is off, or passenger has no onward dests
         l_this_uld_ok := TRUE;
      ELSE
         -- Further checks for passengers with onward destination(s)
         IF (p_got_onward_dest = 1)      -- Onward destination(s) match exactly
            OR (p_got_grp_dest = 1)      -- Onward destination(s) match groups
            OR ((p_uld_destination = '*')        -- ULD matches all onward
               AND (p_uld_onward_flight IS NULL) -- destinations, and onward
               AND (p_uld_onward_date IS NULL))  -- flight/date is not specified
            OR (p_uld_dest_count = 1)  -- ULD has no onward destinations defined
            THEN
            IF (p_got_onward_class = 1) OR (p_uld_class_list = '*') THEN
               -- Segregation strategies passed
               l_this_uld_ok := TRUE;
            END IF;
         END IF;
      END IF;
   END IF;

   /*
    * Note: p_got_local_through is not being checked. It is not checked in
    * check_bag_for_load.sql either, and it is not clear whether it should
    * be AND or OR compared with the other checks here.
    */

   RETURN l_this_uld_ok;
END check_segregate_results;


/******************************************************************************
 *
 * PRIVATE FUNCTION perform_uld_search
 *
 * Description:
 *   Performs a search of the ULDS table to find a ULD which is suitable for
 *   loading the bag matching the input parameters.
 *
 * Input Parameters:
 *   p_precision_loading   - Whether the carrier has precision loading enabled
 *   p_flight_id           - The FLIGHT_ID of the bag's flight
 *   p_bag_destination     - The bag destination
 *   p_group_destinations  - List of possible group destinations for the bag
 *   p_bag_connection_type - The bag connection type
 *   p_uld_type            - The ULD type
 *   p_pax_id              - The passenger's PAX_ID
 *   p_pax_class           - The passenger's class
 *   p_pax_bags_class      - The passenger's bag class
 *   p_first_onward_dest   - The passenger's first onward destination
 *   p_first_onward_flight - The passenger's first onward flight number
 *
 * Output Parameters:
 *   p_uld_no              - If exactly one appropriate ULD was found, this
 *                           holds its number. Otherwise NULL
 *
 * Returns:
 *   SUCCESS                    - If exactly one appropriate ULD was found
 *   NO_MATCHING_OPEN_ULD       - If no appropriate ULDs were found
 *   SEVERAL_MATCHING_OPEN_ULDS - If more than one appropriate ULD was found
 *
 * Errors Raised:
 *   None
 *
 ******************************************************************************/
FUNCTION perform_uld_search(
      p_precision_loading   IN  carrier_lookup.precision_loading%TYPE,
      p_flight_id           IN  flight.flight_id%TYPE,
      p_bag_destination     IN  bag.bag_destination%TYPE,
      p_group_destinations  IN  VARCHAR2,
      p_bag_connection_type IN  bag.connection_type%TYPE,
      p_uld_type            IN  ulds.uld_type%TYPE,
      p_pax_id              IN  pax.pax_id%TYPE,
      p_pax_class           IN  pax.pax_class%TYPE,
      p_pax_bags_class      IN  pax.pax_bags_class%TYPE,
      p_first_onward_dest   IN  onward_flight.dest%TYPE,
      p_first_onward_flight IN  onward_flight.no%TYPE,
      p_uld_no              OUT ulds.uld_no%TYPE)
      RETURN error.error_id%TYPE IS

   -- ULD search cursor
   -- Note p_group_destinations is comma separated, with each dest in quotes
   CURSOR c_uld_search IS
   SELECT u.uld_no, u.uld_type, u.connection_type, ud.uld_class_list,
      ud.uld_destination, ud.onward_flight_no, ud.onward_flight_date
   FROM ulds u, uld_dests ud
   WHERE u.uld_no = ud.uld_no
     AND u.close_flag = 'N'         -- Open ULDs only
     AND u.pd_marker  = 'N'         -- Flights which haven't post departed
     AND u.uld_no NOT IN (SELECT hold_no FROM holds)  -- Ignore holds
     AND u.flight_id = p_flight_id  -- Flight matches
     AND u.flight_id = ud.flight_id
     AND (ud.uld_destination = '*'  -- Destination matches
       OR ud.uld_destination = p_bag_destination
       OR INSTR(NVL(p_group_destinations, '?'),
          '''' || ud.uld_destination || '''') > 0)
     AND (p_pax_class = '*'         -- Class matches
       OR ud.uld_class_list = '*'
       OR INSTR(NVL(ud.uld_class_list, '?'), p_pax_class) > 0)
     AND (NVL(p_precision_loading, '?') != 'Y'
       OR u.uld_type = p_uld_type); -- (precision loading) ULD type matches

   -- ULDs found
   l_ok_uld_count      NUMBER := 0;
   l_uld_dest_count    NUMBER;

   -- Storage of baggage_segregation_strategies.segregate 'out' parameters
   l_got_onward_class  NUMBER;
   l_got_onward_dest   NUMBER;
   l_got_better_uld    NUMBER;
   l_got_grp_dest      NUMBER;
   l_got_type          NUMBER;
   l_got_local_through NUMBER;
   l_onward_flight_ok  NUMBER;
   l_onward_carrier_ok NUMBER;

   -- Connect info
   l_got_onward        NUMBER;
   l_connect_retval    NUMBER;

   -- Check if a particular ULD is suitable for loading or not
   l_this_uld_ok       BOOLEAN;

   -- CA02138 - Store details of ULDs with no connecting terminal
   l_no_con_term_uld_count NUMBER := 0;
   l_no_con_term_uld_no    ulds.uld_no%TYPE;

   -- CA02488 - Store details of ULDs with matching onward flight number
   l_matching_onward_flt_uld_ct NUMBER := 0;
   l_matching_onward_flt_uld_no ulds.uld_no%TYPE;

   -- CA02488 - Store details of ULDs with matching onward carrier
   l_matching_onward_carrier_ct NUMBER := 0;
   l_matching_onward_carrier_no ulds.uld_no%TYPE;

   -- Value to return
   l_return_value      error.error_id%TYPE := 0; -- SUCCESS

BEGIN
   -- Search for potentially appropriate ULDs
   FOR uld_search_rec IN c_uld_search
   LOOP
      -- Count the number of destinations this ULD has
      SELECT COUNT(*) INTO l_uld_dest_count
      FROM uld_dests
      WHERE flight_id = p_flight_id AND uld_no = uld_search_rec.uld_no;

      -- Perform baggage segregation strategies
      baggage_segregation_strategies.segregate(p_pax_id,
         uld_search_rec.uld_no, p_bag_destination, p_pax_class,
         NVL(p_first_onward_dest, '-'), p_flight_id, p_precision_loading,
         uld_search_rec.uld_type,
         l_got_onward_class, l_got_onward_dest, l_got_better_uld,
         l_got_grp_dest, l_got_type, l_got_local_through, l_onward_flight_ok,
         l_onward_carrier_ok);

      -- See if this ULD passed the baggage segregation strategies
      l_this_uld_ok := check_segregate_results(p_precision_loading,
         p_first_onward_dest, uld_search_rec.uld_destination,
         l_uld_dest_count, uld_search_rec.uld_class_list,
         uld_search_rec.onward_flight_no, uld_search_rec.onward_flight_date,
         l_got_onward_class, l_got_onward_dest, l_got_better_uld,
         l_got_grp_dest, l_got_type, l_got_local_through);

      -- Perform connection checks
      IF l_this_uld_ok THEN

         IF p_first_onward_dest IS NULL THEN
            l_got_onward := 0;
         ELSE
            l_got_onward := 1;
         END IF;

         check_connect_info(p_flight_id, p_bag_connection_type,
            uld_search_rec.connection_type, uld_search_rec.uld_no,
            l_got_onward, p_pax_bags_class, p_bag_destination,
            l_connect_retval);

         IF l_connect_retval < 0 THEN
            -- CA02488 - ignore connection info if onward flight matches
            IF p_first_onward_flight IS NOT NULL
                  AND l_connect_retval = -1071 -- NOT_CONNECTING_ULD
                  AND l_onward_flight_ok = 1 THEN
               l_this_uld_ok := TRUE;
            ELSE
               l_this_uld_ok := FALSE;
            END IF;
         END IF;
      END IF;

      IF l_this_uld_ok THEN
         -- Only increment l_ok_uld_count if this is definitely a different
         -- ULD (we could have found the same ULD with a different dest/group)
         IF NVL(p_uld_no, '?') != uld_search_rec.uld_no THEN
            p_uld_no := uld_search_rec.uld_no;
            l_ok_uld_count := l_ok_uld_count + 1;

            -- CA02138 - Record ULDs with no connecting terminal
            IF p_bag_connection_type IS NULL
                  AND uld_search_rec.connection_type IS NULL THEN
               l_no_con_term_uld_no := uld_search_rec.uld_no;
               l_no_con_term_uld_count := l_no_con_term_uld_count + 1;
            END IF;

            -- CA02488 - Record ULDs with matching onward flight/carrier
            IF p_first_onward_flight IS NOT NULL THEN -- Pax has onward flight
               IF l_onward_flight_ok = 1 THEN -- Pax onward flight matches
                  l_matching_onward_flt_uld_no := uld_search_rec.uld_no;
                  l_matching_onward_flt_uld_ct := l_matching_onward_flt_uld_ct + 1;
               ELSIF l_onward_carrier_ok = 1 THEN -- Pax onward carrier matches
                  l_matching_onward_carrier_no := uld_search_rec.uld_no;
                  l_matching_onward_carrier_ct := l_matching_onward_carrier_ct + 1;
               END IF;
            END IF; -- If p_first_onward_flight is not null
         END IF;
      END IF; -- If l_this_uld_ok
   END LOOP;

   IF l_ok_uld_count < 1 THEN
      l_return_value := -1150;    -- NO_MATCHING_OPEN_ULD
   ELSIF l_ok_uld_count = 1 THEN
      l_return_value := 0;        -- SUCCESS
   ELSE -- l_ok_uld_count > 1

      -- CA02138/CA02488 - Is one of the matching ULDs better than the others?
      IF (p_first_onward_flight IS NOT NULL)
            AND (l_matching_onward_flt_uld_ct = 1) THEN
         -- Pax has onward flight + one ULD with matching onward flight found
         p_uld_no := l_matching_onward_flt_uld_no;
         l_return_value := 0;     -- SUCCESS

      ELSIF (p_first_onward_flight IS NOT NULL)
            AND (l_matching_onward_carrier_ct = 1) THEN
         -- Pax has onward flight + one ULD with matching onward carrier found
         p_uld_no := l_matching_onward_carrier_no;
         l_return_value := 0;     -- SUCCESS

      ELSIF (p_bag_connection_type IS NULL) AND (l_no_con_term_uld_count = 1) THEN
         -- Bag has no connecting terminal and one appropriate ULD was found
         p_uld_no := l_no_con_term_uld_no;
         l_return_value := 0;     -- SUCCESS

      ELSE
         -- Multiple suitable ULDs, so blank p_uld_no and return an error
         p_uld_no := NULL;
         l_return_value := -1151; -- SEVERAL_MATCHING_OPEN_ULDS
      END IF;
   END IF;

   RETURN l_return_value;
END perform_uld_search;


/******************************************************************************
 *
 * FUNCTION find_uld
 *
 * Description:
 *   Finds a ULD which is suitable for loading the bag which matches the given
 *   bag tag, passenger and flight.
 *
 * Input Parameters:
 *   p_db_bag_tag          - The bag's tag, as stored in the database BAG table
 *   p_pax_id              - The passenger's PAX_ID
 *   p_flight_id           - The FLIGHT_ID of the bag's flight
 *
 * Output Parameters:
 *   p_uld_no              - If exactly one appropriate ULD was found, this
 *                           holds its number. Otherwise NULL
 *
 * Returns:
 *   SUCCESS                    - If exactly one appropriate ULD was found
 *   NO_MATCHING_OPEN_ULD       - If no appropriate ULDs were found
 *   SEVERAL_MATCHING_OPEN_ULDS - If more than one appropriate ULD was found
 *
 * Errors Raised:
 *   None
 *
 ******************************************************************************/
FUNCTION find_uld(
      p_db_bag_tag IN  bag.bag_tag%TYPE,
      p_pax_id     IN  pax.pax_id%TYPE,
      p_flight_id  IN  flight.flight_id%TYPE,
      p_uld_no     OUT ulds.uld_no%TYPE)
      RETURN error.error_id%TYPE IS
   -- Cursor for selecting the bag
   -- Note primary key is combination of bag_tag and pax_id
   CURSOR c_bag IS
   SELECT * FROM bag
   WHERE bag_tag = p_db_bag_tag AND pax_id = p_pax_id;

   -- Cursor for selecting group destinations
   CURSOR c_group_destinations(p_dest IN group_iata_dests.dest%TYPE) IS
   SELECT group_dest from group_iata_dests WHERE dest = p_dest;

   -- Bag/passenger/ULD details storage
   l_bag                 bag%ROWTYPE;
   l_first_onward_dest   onward_flight.dest%TYPE;
   l_first_onward_flight onward_flight.no%TYPE;
   l_group_destinations  VARCHAR2(100);
   l_pax_class           pax.pax_class%TYPE;
   l_pax_bags_class      pax.pax_bags_class%TYPE;
   l_uld_type            ulds.uld_type%TYPE;

   -- Whether carrier has precision loading enabled or not
   l_precision_loading  carrier_lookup.precision_loading%TYPE;

   -- Value to return
   l_return_value       error.error_id%TYPE;
BEGIN
   -- Read bag details
   OPEN c_bag;
   FETCH c_bag INTO l_bag;

   -- Check bag was found
   IF c_bag%NOTFOUND THEN
      CLOSE c_bag;
      l_return_value := -1002; -- UNKNOWN_TAG
   ELSE
      CLOSE c_bag;

      -- Read group dests for this bag dest
      FOR group_dest_rec IN c_group_destinations(l_bag.bag_destination)
      LOOP
         l_group_destinations := l_group_destinations || ',''' ||
            group_dest_rec.group_dest || '''';
      END LOOP;

      -- Determine the passenger's class and first onward destination
      SELECT p.pax_class, p.pax_bags_class, o.dest, o.no
      INTO l_pax_class, l_pax_bags_class, l_first_onward_dest,
         l_first_onward_flight
      FROM pax p, onward_flight o
      WHERE p.pax_id = p_pax_id
      AND o.pax_id(+) = p.pax_id AND o.dest_order(+) = 1;

      -- Determine whether carrier has precision loading enabled
      SELECT NVL(c.precision_loading, 'N') INTO l_precision_loading
      FROM flight f, carrier_lookup c
      WHERE f.flight_id = p_flight_id
      AND c.alpha_code = substr(f.flight_no, 1, 3);

      -- If precision loading is enabled, set the appropriate ULD type
      IF l_precision_loading = 'Y' THEN
         IF l_first_onward_dest IS NULL THEN
            -- Bag is ok if ULD type is L, and first onward dest is null
            l_uld_type := 'L';
         ELSE
            -- Bag also ok if ULD type is T and first onward dest is not null
            l_uld_type := 'T';
         END IF;
      END IF;

      -- Perform the ULD search
      l_return_value := perform_uld_search(l_precision_loading, p_flight_id,
         l_bag.bag_destination, l_group_destinations, l_bag.connection_type,
         l_uld_type, p_pax_id, l_pax_class, l_pax_bags_class,
         l_first_onward_dest, l_first_onward_flight, p_uld_no);

      -- If precision loading is enabled, and no ULDs were found in the first
      -- search, perform a second search for mixed ULDs (uld_type = 'M')
      IF (l_return_value = -1150) -- NO_MATCHING_OPEN_ULD
            AND (l_precision_loading = 'Y') THEN
         l_uld_type := 'M';

         l_return_value := perform_uld_search(l_precision_loading, p_flight_id,
            l_bag.bag_destination, l_group_destinations, l_bag.connection_type,
            l_uld_type, p_pax_id, l_pax_class, l_pax_bags_class,
            l_first_onward_dest, l_first_onward_flight, p_uld_no);
      END IF;
   END IF; -- If bag was found

   RETURN l_return_value;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- One of the SELECT INTO statements failed - ie. data is inconsistent
      RETURN -502; -- FAIL
END find_uld;


/******************************************************************************
 *
 * PROCEDURE send_bpms
 *
 * Description:
 *   Sends BPMs (if appropriate) as a result of an attempted bag load.
 *   For a successful load, BPMs of type 1 (ordinary) or 2 (rush bag) are sent.
 *   For an unsuccessful load, (BagFlo) BPMs of type 10, 11 or 2 are sent.
 *
 * Input Parameters:
 *   p_load_result         - The result of the bag_load action
 *   p_flight_id           - The FLIGHT_ID of the bag's flight
 *   p_pax_id              - The passenger's PAX_ID
 *   p_db_bag_tag          - The bag's tag, as stored in the database BAG table
 *   p_scanner_id          - The scaner ID
 *   p_user_id             - The scanner user's ID
 *   p_location            - The scanner user's location
 *
 * Errors Raised:
 *   None
 *
 ******************************************************************************/
PROCEDURE send_bpms(
      p_load_result IN NUMBER,
      p_flight_id   IN flight.flight_id%TYPE,
      p_pax_id      IN pax.pax_id%TYPE,
      p_db_bag_tag  IN bag.bag_tag%TYPE,
      p_scanner_id  IN CHAR,
      p_user_id     IN tagtrak_users.user_id%TYPE,
      p_location    IN location.location_code%TYPE) IS

   -- get_bpm_status IN parameters
   l_process_name  bag.process_name%TYPE;
   l_unit_name     bag.unit_name%TYPE;
   l_dcsid         bag.dcsid%TYPE;
   l_subunit_name  bag.subunit_name%TYPE;
   l_bag_exception bag.bag_exception%TYPE;

   -- get_bpm_status OUT parameters
   l_bpm_status    char(1);

   -- mail_bpm1/store_bpm_user IN OUT parameters
   l_flight_id     flight.flight_id%TYPE;
   l_pax_id        pax.pax_id%TYPE;
   l_db_bag_tag    bag.bag_tag%TYPE;
   l_user_id       tagtrak_users.user_id%TYPE;
   l_location      location.location_code%TYPE;

   l_bpm_id        NUMBER; -- BPM ID
   l_bpm_type      VARCHAR(2); -- BPM type
   l_pax_seq_no    pax.pax_sequence_no%TYPE := 0; -- Pax sequence number
   l_mail_bpm_rv   NUMBER; -- Mail BPM return value
BEGIN
   BEGIN
      SELECT NVL(b.process_name, 'DUMMY'),
             NVL(b.unit_name, '1'),
             NVL(b.dcsid, 'DUMMY'),
             NVL(b.subunit_name, '0'),
             b.bag_exception,
             NVL(p.pax_sequence_no, 0)
      INTO l_process_name, l_unit_name, l_dcsid, l_subunit_name,
         l_bag_exception, l_pax_seq_no
      FROM bag b, pax p
      WHERE p.pax_id(+) = p_pax_id AND b.pax_id = p_pax_id
         AND b.bag_tag = p_db_bag_tag;
   EXCEPTION
      -- Use flight if bag not found
      WHEN OTHERS THEN
         BEGIN
            SELECT NVL(process_name, 'DUMMY'),
                   NVL(unit_name, '1'),
                   NVL(dcsid, 'DUMMY'),
                   NVL(subunit_name, '0'),
                   NULL bag_exception
            INTO l_process_name, l_unit_name, l_dcsid, l_subunit_name,
               l_bag_exception
            FROM flight
            WHERE flight_id = p_flight_id;
         EXCEPTION
            -- If not flight found either, use dummy values
            WHEN NO_DATA_FOUND THEN
               l_process_name  := 'DUMMY';
               l_unit_name     := '1';
               l_dcsid         := 'DUMMY';
               l_subunit_name  := '0';
               l_bag_exception := NULL;
         END;
   END;

   -- Copy IN params to local vars for mail_bpm1/store_bpm_user IN OUT params
   l_flight_id  := p_flight_id;
   l_pax_id     := p_pax_id;
   l_db_bag_tag := p_db_bag_tag;
   l_user_id    := p_user_id;
   l_location   := p_location;

   -- Decide on initial BPM type for get_bpm_status
   IF l_pax_seq_no = 999 THEN -- Rush bag
      l_bpm_type := '2';
   ELSE
      l_bpm_type := '1';
   END IF;

   -- Get BPM status
   get_bpm_status(l_process_name, l_unit_name, l_dcsid, l_subunit_name,
      l_bag_exception, l_flight_id, l_bpm_type, l_bpm_status);

   -- Send BPMs for successful bag load if status flag is ok
   IF (p_load_result = 0) AND (l_bpm_status = 'Y') THEN
      generate_bpmid(l_bpm_id);

      IF l_pax_seq_no = 999 THEN -- Rush bag
         l_bpm_type := '2';
      ELSE
         l_bpm_type := '1';
      END IF;

      mail_bpm1(l_flight_id, l_pax_id, l_db_bag_tag, l_bpm_type, l_bpm_id,
         l_process_name, l_unit_name, l_dcsid, l_subunit_name, p_scanner_id,
         l_mail_bpm_rv);

      store_bpm_user(l_bpm_id, l_user_id, l_location);
   END IF;

   -- Send BagFlo BPMs for unsuccessful bag load if status flags are ok
   -- (but not if load failed because of duplicate tag)
   IF (p_load_result != 0)            -- SUCCESS
         AND (p_load_result != -1003)  -- DUPLICATE_TAG
         AND (p_load_result != -1011)  -- UNKNOWN_DUPLICATE_TAG
         AND (p_load_result != -1021)  -- UNRESOLVABLE_DUPLICATE
         THEN

      IF p_load_result = -1004 THEN    -- NOT_ATL_BY_DCS
         l_bpm_type := '10';
      ELSIF p_load_result = -1002 THEN -- UNKNOWN_TAG
         l_bpm_type := '11';
      ELSE
         l_bpm_type := '12';
      END IF;

      get_bpm_status(l_process_name, l_unit_name, l_dcsid, l_subunit_name,
         l_bag_exception, l_flight_id, l_bpm_type, l_bpm_status);

      IF l_bpm_status = 'Y' THEN
         generate_bpmid(l_bpm_id);
      mail_bpm1(l_flight_id, l_pax_id, l_db_bag_tag, l_bpm_type, l_bpm_id,
         l_process_name, l_unit_name, l_dcsid, l_subunit_name, p_scanner_id,
         l_mail_bpm_rv);

      store_bpm_user(l_bpm_id, l_user_id, l_location);
      END IF;
   END IF;
END send_bpms;


END load_where;
/

