/*****************************************************************************
*
* Filename               - tracking.sql
*
* Project Name           - UltraTrak Database
*
* Design Documentation   - UAS/URBRS/SS/10127
*
* Description            - Procedures for use by MWS/scanner/tcmt to create 
*                          user-configurable logs of various events in the 
*                          baggage handling process and related functionality
*                          (e.g. Bag Groups, unknown bags etc.)
*
* Language               - Oracle PL/SQL
*
* File History           - For the development history run the Show History report in PVCS
*
* Copyright Ultra Electronics Airport Systems, 2003
*
*****************************************************************************/
    
CREATE OR REPLACE PACKAGE tracking
AS

/******************************************************************************
 *
 * Private Type Definitions
 *
 ******************************************************************************/

   -- Define the types of record sets
   TYPE event_record_type IS TABLE OF tracking_events.event_no%TYPE INDEX BY BINARY_INTEGER;
   TYPE bags_type         IS TABLE OF bag.bag_tag%TYPE              INDEX BY BINARY_INTEGER;
   TYPE return_code_type  IS TABLE OF number                        INDEX BY BINARY_INTEGER;
   TYPE flights_type      IS TABLE OF flight.flight_id%TYPE         INDEX BY BINARY_INTEGER;

   -- Error codes used to indicate unknown tag to scanner
   v_tag_unknown_no_flt    NUMBER := -1002;
   v_tag_unknown           NUMBER := -20043;

   DUPLICATE_TAG           NUMBER := -1003;
   DELETED_TAG             NUMBER := -1019;
   UNRESOLVABLE_DUPLICATE  NUMBER := -1021;
   ACCESS_DENIED           NUMBER := -1031;

/******************************************************************************
 *
 * PROCEDURE AUTO_EVENTS
 *
 * Description:
 *
 * This procedure fires an automatic events that apply to a bag. It should be
 * called when any operation that affects a bag is carried out - e.g. when
 * it is loaded, logged, etc.
 *
 * Parameters:
 *
 * p_bag_tag          - tag that logs should be created for
 * p_flight_id        - flight id associated with bag
 * p_uld_no           - uld that logs should be created for
 * p_pax_id           - passenger that bag belongs to
 * p_user_id          - user carrying out this operation
 * p_location         - location of event
 * p_event_count      - number of automatic events logged
 * p_no_of_errs       - number of events that failed
 *
 ******************************************************************************/

PROCEDURE auto_events
(
   p_bag_tag       IN bag.bag_tag%type,
   p_flight_id     IN flight.flight_id%type,
   p_uld_no        IN ulds.uld_no%type,
   p_pax_id        IN pax.pax_id%type,
   p_pax_seq       IN pax.pax_sequence_no%type,
   p_user_id       IN tagtrak_users.user_id%type,
   p_location      IN location.location_code%type,
   p_bag_group     IN bag_group.group_no%type,
   p_event_count   IN OUT integer,
   p_no_of_errs    OUT integer
);

/******************************************************************************
 *
 * PROCEDURE CREATE_TRACKING_LOG
 *
 * Description:
 *
 * This procedure is used to create bag status entries and then checks whether
 * the current user has access to and is at the correct location to fire the
 * tracking event to create a log. If the tracking event has linked events
 * configured then this stored procedure loops round each creating tracking
 * logs for each. The create_log stored procedure is used to actually go and
 * create the logs
 *
 * Parameters:
 *
 * p_bag_tag          - bag tag that log is to created for
 * p_bag_group        - bag group that the log is to be created for
 * p_pax_id           - associated passenger id, this is always 0 from the scanner
 * p_flight_id        - flight id associated with given bag
 * p_user_id          - user carrying out this operation
 * p_location         - location of event that caused this log
 * p_event_no         - number of the event that the log should be created for
 * p_status_no        - status of bag that the log should be created for
 * p_force            - flag to indicate whether location and group of user
 *                      must match that of the event in order to fire (MWS
 *                      users can fire events for any location)
 * p_error_event      - on error, set to number of event that failed (if return
 *                      code non zero and this is null the error occurred when
 *                      logging a bag status)
 * p_return_code      - this returns an error code indicating what the outcome
 *                      of the attempt to create a tracking log was.
 * p_deleted_bag_flag - this is needed to show when a bag has been found to be
 *                      deleted and we want to create a tracking log against an
 *                      unknown bag instead.
 *
 ******************************************************************************/

PROCEDURE create_tracking_log
(
   p_bag_tag          IN  bag.bag_tag%type,
   p_bag_group        IN  bag_group.group_no%type,
   p_pax_id           IN  pax.pax_id%type,
   p_flight_id        IN  flight.flight_id%type,
   p_user_id          IN  tagtrak_users.user_id%type,
   p_location         IN  location.location_code%type,
   p_event_no         IN  tracking_events.event_no%type,
   p_status_no        IN  bag_status.status_no%type,
   p_scanner_id       IN  kit.scanner_id%type,
   p_force            IN  char,
   p_error_event      OUT tracking_events.event_no%type,
   p_return_code      OUT number,
   p_deleted_bag_flag IN  boolean
);

/******************************************************************************
 *
 * PROCEDURE CREATE_TRACKING_LOG_FROM_SCANR
 *
 * Description:
 *
 * This a wrapper procedure for the create_tracking_log procedure. This
 * procedure is called by scanner, which will not normally pass the pax id
 *
 * Parameters:
 *
 * p_bag_tag          - bag tag that log is to created for
 * p_bag_group        - bag group that the log is to be created for
 * p_flight_id        - flight id associated with given bag
 * p_user_id          - user carrying out this operation
 * p_location         - location of event that caused this log
 * p_event_no         - number of the event that the log should be created for
 * p_error_event      - on error, set to number of event that failed (if return
 *                      code non zero and this is null the error occurred when
 *                      logging a bag status)
 * p_status_no        - status of bag that the log should be created for
 * p_timestamp        - Time the bag is created
 *
 ******************************************************************************/

PROCEDURE create_tracking_log_from_scanr
(
   p_bag_tag          IN  bag.bag_tag%type,
   p_bag_group        IN  bag_group.group_no%type,
   p_flight_id        IN  flight.flight_id%type,
   p_user_id          IN  tagtrak_users.user_id%type,
   p_location         IN  location.location_code%type,
   p_event_no         IN  tracking_events.event_no%type,
   p_status_no        IN  bag_status.status_no%type,
   p_scanner_id       IN  kit.scanner_id%type,
   p_error_event      OUT tracking_events.event_no%type,
   p_timestamp        OUT char,
   p_return_code      OUT number
);

/******************************************************************************
 *
 * PROCEDURE CREATE_TRACKING_LOG_FROM_MWS
 *
 * Description:
 *
 * This a wrapper procedure for the create_tracking_log procedure. This
 *
 * Parameters:
 *
 * p_bag_tag          - bag tag that log is to created for
 * p_bag_group        - bag group that the log is to be created for
 * p_flight_id        - flight id associated with given bag
 * p_user_id          - user carrying out this operation
 * p_location         - location of event that caused this log
 * p_event_no         - number of the event that the log should be created for
 * p_status_no        - status of bag that the log should be created for
 * p_pax_id           - the pax id of the bag that the event/status tracking log is created
 * p_error_event      - returns a recordset of the error event to where the function is called
 * p_return_code      - returns a recordset of the return code to where the function is called
 *
 ******************************************************************************/

 PROCEDURE create_tracking_log_from_mws
 (
    p_bag_tag          IN  bag.bag_tag%type,
    p_bag_group        IN  bag_group.group_no%type,
    p_flight_id        IN  flight.flight_id%type,
    p_user_id          IN  tagtrak_users.user_id%type,
    p_location         IN  location.location_code%type,
    p_event_no         IN  tracking_events.event_no%type,
    p_status_no        IN  bag_status.status_no%type,
    p_pax_id           IN  pax.pax_id%type,
    p_error_event      OUT event_record_type,
    p_return_code      OUT return_code_type
);

/******************************************************************************
 *
 * PROCEDURE CREATE_TRACKING_LOG_FROM_MWS_2
 *
 * Description:
 *
 * This a wrapper procedure for the create_tracking_log procedure. This
 *
 * Parameters:
 *
 * p_bag_tag          - bag tag that log is to created for
 * p_bag_group        - bag group that the log is to be created for
 * p_flight_id        - flight id associated with given bag
 * p_user_id          - user carrying out this operation
 * p_location         - location of event that caused this log
 * p_event_no         - number of the event that the log should be created for
 * p_status_no        - status of bag that the log should be created for
 * p_pax_id           - the pax id of the bag that the event/status tracking log 
 *                      is created for
 * p_error_event      - the value of the error event is passed back
 * p_return_code      - the return code to pass back to the calling function
 *
 ******************************************************************************/

PROCEDURE create_tracking_log_from_mws_2
(
   p_bag_tag          IN  bag.bag_tag%TYPE,
   p_bag_group        IN  bag_group.group_no%TYPE,
   p_flight_id        IN  flight.flight_id%TYPE,
   p_user_id          IN  tagtrak_users.user_id%TYPE,
   p_location         IN  location.location_code%TYPE,
   p_event_no         IN  tracking_events.event_no%TYPE,
   p_status_no        IN  bag_status.status_no%TYPE,
   p_pax_id           IN  pax.pax_id%TYPE,
   p_error_event      OUT tracking_events.event_no%TYPE,
   p_return_code      OUT NUMBER
);


/******************************************************************************
 *
 * PROCEDURE CREATE_BAG_GROUP
 *
 * Description:
 *
 * This procedure creates the specified bag group in the BAG_GROUP table. If
 * the group already exist the procedure just returns the number of bags in
 * the group.
 *
 * Parameters:
 *
 * p_user_id     - User requesting creation of bag group
 * p_bag_group   - Bag group number
 * p_bag_count   - number of bags in the group (0 on creation)
 * p_return_code - indicates whether group was successfully added or not
 *
 ******************************************************************************/

PROCEDURE create_bag_group
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_bag_group   IN  bag_group.group_no%type,
   p_holding_pen IN  VARCHAR2,
   p_bag_count   OUT number,
   p_return_code OUT number
);

/******************************************************************************
 *
 * PROCEDURE REMOVE_BAG_GROUP
 *
 * Description:
 *
 * This procedure removes the specified bag group from the BAG_GROUP table.
 *
 * Parameters:
 *
 * p_user_id     - User requesting creation of bag group
 * p_bag_group   - Bag group number
 * p_force       - If set and bags still in group, bags are removed, then
 *                 group removed. If not set and group not empty,
 *                 BAG_GROUP_NOT_EMPTY is returned
 * p_return_code - indicates whether group was successfully removed
 *
 ******************************************************************************/

PROCEDURE remove_bag_group
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_bag_group   IN  bag_group.group_no%type,
   p_force       IN  varchar2,
   p_location    IN  location.location_code%type,
   p_scanner_id  IN  kit.id%type,
   p_return_code OUT number
);

/******************************************************************************
 *
 * PROCEDURE ADD_BAG_TO_GROUP
 *
 * Description:
 *
 * This procedure adds the specified bag to the given bag group
 *
 * Parameters:
 *
 * p_user_id     - User requesting bag to be added to group
 * p_bag_group   - Bag group number
 * p_return_code - indicates whether bag was successfully added to the group
 *
 ******************************************************************************/

PROCEDURE add_bag_to_group
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_bag_group   IN  bag_group.group_no%type,
   p_bag_tag     IN  bag.bag_tag%type,
   p_flight_id   IN  flight.flight_id%type,
   p_location    IN  location.location_code%type,
   p_scanner_id  IN  kit.id%type,
   p_bag_count   OUT number,
   p_return_code OUT number
);

/******************************************************************************
 *
 * PROCEDURE REMOVE_BAG_FROM_GROUP
 *
 * Description:
 *
 * This procedure removes the specified bag from the given bag group
 *
 * Parameters:
 *
 * p_user_id     - User requesting removal of bag from group
 * p_bag_group   - Bag group number
 * p_return_code - indicates whether bag was successfully removed from the group
 *
 ******************************************************************************/

PROCEDURE remove_bag_from_group
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_bag_group   IN  bag_group.group_no%type,
   p_bag_tag     IN  bag.bag_tag%type,
   p_flight_id   IN  flight.flight_id%type,
   p_location    IN  location.location_code%type,
   p_scanner_id  IN  kit.id%type,
   p_bag_count   OUT number,
   p_return_code OUT number
);

/******************************************************************************
 *
 * PROCEDURE GET_BAG_GROUP
 *
 * Description:
 *
 * This procedure gets the bag group that a given bag belongs to (for a given
 * tag and flight)
 *
 * Parameters:
 *
 * p_bag_tag2a    - bag (2a) to find group for
 * p_bag_tag2b    - bag (2b) to find group for
 * p_bag_tag3     - bag (3) to find group for
 * p_flight_id    - flight associated with bag
 * p_bag_group    - returned group that the bag is in
 *
 ******************************************************************************/

PROCEDURE get_bag_group
(
   p_bag_tag2a   IN   bag.bag_tag2a%type,
   p_bag_tag2b   IN   bag.bag_tag2b%type,
   p_bag_tag3    IN   bag.bag_tag3%type,
   p_flight_id   IN   flight.flight_id%type,
   p_bag_group   OUT  bag_group.group_no%type
);


/******************************************************************************
 *
 * PROCEDURE VALIDATE_EVENT
 *
 * Description:
 *
 * This procedure checks that an event of the given number exists in the
 * TRACKING_EVENTS table for the given user (group/agent). If no matching
 * rows are found the procedure will return NO_RECORDS_FOUND (-651).
 *
 * Parameters:
 *
 * p_user_id     - User to check event for
 * p_event_no    - event to check event for
 * p_description - returned event description
 * p_return_code - indicates whether event exists/user allowed access to event
 *                 (0 - OK/-651 NOT OK (No records found))
 *
 ******************************************************************************/


PROCEDURE validate_event
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_event_no    IN  tracking_events.event_no%type,
   p_description OUT tracking_events.description%type,
   p_return_code OUT number
);

/******************************************************************************
 *
 * PROCEDURE VALIDATE_STATUS
 *
 * Description:
 *
 * This procedure checks that a status of the given number exists in the
 * BAG_STATUS table for the given user (group/agent). If no matching
 * rows are found the procedure will return NO_RECORDS_FOUND (-651).
 *
 * Parameters:
 *
 * p_user_id     - User to check event for
 * p_event_no    - event to check event for
 * p_description - returned status description
 * p_return_code - indicates whether event exists/user allowed access to event
 *                 (0 - OK/-651 NOT OK (No records found))
 *
 ******************************************************************************/


PROCEDURE validate_status
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_status_no   IN  bag_status.status_no%type,
   p_description OUT bag_status.description%type,
   p_return_code OUT number
);

/******************************************************************************
 *
 * PROCEDURE CHECK_EVENTS_CLEAR
 *
 * Description:
 *
 * This procedure checks whether all mandatory events in TRACKING_EVENTS for
 * the matching AGENT/GROUP_NAME or where mandatory events are to apply site-wide
 * are cleared. i.e. the bag needs to have an entry in TRACKING_LOG for each entry
 * in TRACKING_EVENTS where mandatory = 'Y' either for that agent or for any agent
 * at that site.
 * This procedure is called during loading a bag by the MWS and scanner.
 *
 * Parameters:
 *
 * p_user_id           - user calling the procedure
 * p_bag_tag           - tag to be checked
 * p_pax_id            - passenger id (this is always 0 from the scanner)
 * p_flight_id         - flight id associated with bag
 * p_failed_event      - if a mandatory event has not been cleared this
 *                       is set to the event number
 * p_event_description - description of event that has not been cleared
 * p_return_code       - 0 indicates all events cleared, EVENT_NOT_CLEARED
 *                       otherwise.
 * p_bag_group         - contains the bag group the bag is loaded into if the bag is already grouped
 *
 ******************************************************************************/


PROCEDURE check_events_clear
(
   p_user_id             IN  tagtrak_users.user_id%type,
   p_bag_tag             IN  bag.bag_tag%type,
   p_pax_id              IN  pax.pax_id%type,
   p_flight_id           IN  flight.flight_id%type,
   p_failed_event        OUT tracking_events.event_no%type,
   p_event_description   OUT tracking_events.description%type,
   p_return_code         OUT number,
   p_bag_group           OUT bag_group.group_no%type
);

/******************************************************************************
 *
 * PROCEDURE TRANSFER_GROUP
 *
 * Description:
 *
 * This procedure transfers the contents of one bag group into another
 *
 * Parameters:
 *
 * p_user_id     - User requesting creation transfer of group
 * p_source_bag_group   - Source bag group number
 * p_dest_bag_group     - Dest bag group number
 * p_location    - the location the handler is at
 * p_scanner_id  - the scanner id the handler is using.
 * p_return_code - indicates whether group was successfully removed
 *
 ******************************************************************************/

PROCEDURE transfer_group
(
   p_user_id           IN      tagtrak_users.user_id%type,
   p_source_bag_group  IN      bag_group.group_no%type,
   p_dest_bag_group    IN      bag_group.group_no%type,
   p_location          IN      location.location_code%type,
   p_scanner_id        IN      kit.id%type,
   p_return_code       IN OUT  number,
   p_bag_count            OUT  number
);


/******************************************************************************
 *
 * PROCEDURE GET_BAG_GROUP_DETAILS
 *
 * Description:
 *
 * This procedure retrieves the details of the specified bag group
 *
 * Parameters:
 *
 * p_user_id     - User requesting bag group details
 * p_bag_group   - Bag group to be retrieved
 * p_no_bags     - Number of Bags in bag group
 * p_agent_code  - Agent Code for bag group
 * p_user_group  - User Group for bag group
 * p_holding_pen - Holding Pen flag
 * p_return_code - indicates whether group was successfully removed
 *
 ******************************************************************************/

PROCEDURE get_bag_group_details
(
   p_user_id           IN      tagtrak_users.user_id%TYPE,
   p_bag_group         IN      bag_group.group_no%TYPE,
   p_no_bags              OUT  NUMBER,
   p_agent_code           OUT  agents.agent_code%TYPE,
   p_user_group           OUT  user_group.group_name%TYPE,
   p_holding_pen          OUT  bag_group.holding_pen%TYPE,
   p_return_code          OUT  number
);

END tracking;
/

CREATE OR REPLACE PACKAGE BODY tracking
AS

/******************************************************************************
 *
 * Private Procedures and Functions
 *
 ******************************************************************************/

/******************************************************************************
 *
 * PROCEDURE GET_BAGS_IN_GROUP
 *
 * Description:
 *
 * Given a group, this procedure returns a table containing all the bags that
 * are in it
 *
 * Parameters:
 *
 * p_group_no - group to look for
 * p_bags   - output table containing bags
 * p_no_of_bags - number of bags returned
 *
 ******************************************************************************/

PROCEDURE get_bags_in_group
(
   p_group_no   IN      bag_group.group_no%type,
   p_bags       OUT     bags_type,
   p_flights    OUT     flights_type,
   p_no_of_bags OUT     integer
)
IS
   l_count integer := 1;

   CURSOR c_bags_in_group IS

     SELECT b.bag_tag, p.flight_id FROM bag b, pax p WHERE
         b.group_no = p_group_no AND
         b.pax_id = p.pax_id
      UNION
     SELECT bag_tag, flight_id FROM unknown_bag WHERE
         group_no = p_group_no;

BEGIN

   -- Loop, storing each bag tag in the group

   FOR l_this_rec IN c_bags_in_group LOOP

      p_bags(l_count) := l_this_rec.bag_tag;
      p_flights(l_count) := l_this_rec.flight_id;
      l_count := l_count + 1;

   END LOOP;

   p_no_of_bags := l_count-1;

END get_bags_in_group;

/******************************************************************************
 *
 * PROCEDURE GET_BAGS_IN_ULD
 *
 * Description:
 *
 * Given a uld, this procedure returns a table containing all the bags that
 * are in it
 *
 * Parameters:
 *
 * p_uld_no - uld to look for
 * p_bags   - output table containing bags
 * p_no_of_bags - number of bags returned
 *
 ******************************************************************************/

PROCEDURE get_bags_in_uld
(
   p_user_id    IN      tagtrak_users.user_id%type,
   p_uld_no     IN      ulds.uld_no%type,
   p_flight_id  IN OUT  flight.flight_id%type,
   p_bags       OUT     bags_type,
   p_no_of_bags OUT     integer
)
IS

   l_count integer := 1;

   CURSOR c_bags_flt_not_known IS

     SELECT b.bag_tag, u.flight_id
     FROM   bag b,
            pax p,
            flight_outbound_v1 f,
            ulds u,
            agents a,
            tagtrak_users tu
     WHERE  b.pax_id = p.pax_id AND
            p.flight_id = f.flight_id AND
            f.flight_status != 'PD' AND
            p.flight_id = u.flight_id AND
            b.uld_no = p_uld_no AND
            b.uld_no = u.uld_no AND
            f.site_id = a.site_id AND
            a.agent_code = tu.agent_code AND
            tu.user_id = p_user_id;

   CURSOR c_bags_flt_known IS
      SELECT b.bag_tag, u.flight_id FROM bag b, pax p, ulds u WHERE
         b.pax_id = p.pax_id AND
         p.flight_id = u.flight_id AND
         p.flight_id = p_flight_id AND
         b.uld_no = p_uld_no AND
         b.uld_no = u.uld_no;
BEGIN

   -- Loop, storing each bag tag in the uld

   IF p_flight_id IS NULL OR p_flight_id = 0 THEN

      -- We don't know the flight id for this ULD.
      -- We should be able to uniquely identify
      -- it by finding one whose flight is not
      -- post departed

      FOR l_this_rec IN c_bags_flt_not_known LOOP

         IF p_flight_id IS NULL OR p_flight_id = 0 THEN

            p_flight_id := l_this_rec.flight_id;

         END IF;

         p_bags(l_count) := l_this_rec.bag_tag;
         l_count := l_count + 1;

      END LOOP;

   ELSE

      -- We have been given the flight id

      FOR l_this_rec IN c_bags_flt_known LOOP

         p_bags(l_count) := l_this_rec.bag_tag;
         l_count := l_count + 1;

      END LOOP;

   END IF;

   p_no_of_bags := l_count - 1;

END get_bags_in_uld;

/******************************************************************************
 *
 * PROCEDURE GET_BAGS_FOR_FLIGHT
 *
 * Description:
 *
 * Given a flight, this procedure returns a table containing all the bags that
 * are on it
 *
 * Parameters:
 *
 * p_flight_id  - flight to look for
 * p_bags       - output table containing bags
 * p_no_of_bags - number of bags returned
 *
 ******************************************************************************/

PROCEDURE get_bags_for_flight
(
   p_flight_id  IN   flight.flight_id%type,
   p_bags       OUT  bags_type,
   p_no_of_bags OUT  integer
)
IS

   l_count integer := 1;

   CURSOR c_bags IS
      SELECT b.bag_tag FROM bag b, pax p WHERE
         p.flight_id = p_flight_id AND
         b.pax_id = p.pax_id
      UNION
      SELECT bag_tag FROM unknown_bag WHERE
         flight_id = p_flight_id;
BEGIN

   -- Loop, storing each bag tag in the uld

   FOR l_this_rec IN c_bags LOOP

      p_bags(l_count) := l_this_rec.bag_tag;
      l_count := l_count + 1;

   END LOOP;

   p_no_of_bags := l_count - 1;

END get_bags_for_flight;



/******************************************************************************
 *
 * PROCEDURE GET_BAGS_FOR_PAX_SEQ
 *
 * Description:
 *
 * Given a flight and a passenger sequence number, this procedure returns a
 * table containing all bags for the passenger
 *
 * Parameters:
 *
 * p_flight_id  - flight to look for
 * p_pax_seq    - sequence number of the passenger
 * p_bags       - output table containing bags
 * p_no_of_bags - number of bags returned
 *
 ******************************************************************************/

PROCEDURE get_bags_for_pax_seq
(
   p_flight_id  IN   flight.flight_id%type,
   p_pax_seq    IN   pax.pax_sequence_no%type,
   p_bags       OUT  bags_type,
   p_no_of_bags OUT  integer
)
IS

   l_count integer := 1;

   CURSOR c_bags IS
      SELECT b.bag_tag FROM bag b, pax p WHERE
         p.flight_id = p_flight_id AND
         b.pax_id = p.pax_id AND
         p.pax_sequence_no = p_pax_seq;
BEGIN

   -- Loop, storing each bag tag in the uld

   FOR l_this_rec IN c_bags LOOP

      p_bags(l_count) := l_this_rec.bag_tag;
      l_count := l_count + 1;

   END LOOP;

   p_no_of_bags := l_count - 1;

END get_bags_for_pax_seq;



/******************************************************************************
 *
 * PROCEDURE GET_BAGS_FOR_PAX_ID
 *
 * Description:
 *
 * Given a flight and a passenger id number, this procedure returns a
 * table containing all bags for the passenger
 *
 * Parameters:
 *
 * p_flight_id  - flight to look for
 * p_pax_id     - number of the passenger
 * p_bags       - output table containing bags
 * p_no_of_bags - number of bags returned
 *
 ******************************************************************************/

PROCEDURE get_bags_for_pax_id
(
   p_flight_id  IN   flight.flight_id%type,
   p_pax_id     IN   pax.pax_id%type,
   p_bags       OUT  bags_type,
   p_no_of_bags OUT  integer
)
IS

   l_count integer := 1;

   CURSOR c_bags IS
      SELECT b.bag_tag FROM bag b, pax p WHERE
         p.flight_id = p_flight_id AND
         b.pax_id = p.pax_id       AND
         p.pax_id = p_pax_id;

BEGIN

   -- Loop, storing each bag tag in the uld

   FOR l_this_rec IN c_bags LOOP

      p_bags(l_count) := l_this_rec.bag_tag;
      l_count := l_count + 1;

   END LOOP;

   p_no_of_bags := l_count - 1;

END get_bags_for_pax_id;


/******************************************************************************
 *
 * PROCEDURE GET_GROUP_AND_AGENT
 *
 * Description:
 *
 * Given a user id this procedure returns the corresponding group and agent
 *
 * Parameters:
 *
 * p_user_id          - user id to find the group and agent for
 * p_group_name       - returns group name
 * p_agent_code       - returned agent code
 *
 ******************************************************************************/

PROCEDURE get_group_and_agent
(
   p_user_id    IN  tagtrak_users.user_id%type,
   p_group_name OUT user_group.group_name%type,
   p_agent_code OUT agents.agent_code%type
)
IS
BEGIN

   SELECT group_name, agent_code INTO p_group_name, p_agent_code
      FROM tagtrak_users WHERE user_id = p_user_id;
END get_group_and_agent;

/******************************************************************************
 *
 * PROCEDURE GET_FLIGHT_FOR_UNKNOWN_BAG
 *
 * Description:
 *
 * This procedure attempts to identify the given bag tag in the unknown_bag
 * table
 *
 * Parameters:
 *
 * p_bag_tag          - bag tag to get flight for
 * p_flight_id        - flight id associated with given bag.
 * p_site_id          - site for the user
 * p_return_code      - indicates whether bags was successfully identified
 *
 ******************************************************************************/

PROCEDURE get_flight_for_unknown_bag
(
   p_bag_tag2a   IN      bag.bag_tag2a%type,
   p_bag_tag2b   IN      bag.bag_tag2b%type,
   p_bag_tag3    IN      bag.bag_tag3%type,
   p_pax_id      IN      pax.pax_id%type,
   p_flight_id   IN OUT  flight.flight_id%type,
   p_site_id     IN      site_info.site_id%type,
   p_return_code OUT     number
)
IS
   l_flight_id flight.flight_id%type := p_flight_id;
BEGIN

   -- Initialise the return code
   p_return_code := 0;

   -- The tag is unknown, therefore it might exist as an unknown bag where
   -- the BSM might have been delayed or otherwise not received yet.

   IF (p_flight_id = 0 AND config.get_value('ALLOW_BAG_WITH_NOFLT') = 'N') THEN

      -- A flight id of zero has been supplied and unknown bags must always
      -- be associated with flights so return error code indicating that
      -- the scanner should prompt user for a flight
      p_return_code := v_tag_unknown;

   ELSE

      -- Use the flight ID parameter that has been given to us, it might be an
      -- actual value or it might be 0.
      SELECT ub.flight_id INTO l_flight_id FROM unknown_bag ub, tagtrak_users tu, agents a WHERE
         (ub.bag_tag2a = p_bag_tag2a OR ub.bag_tag2b = p_bag_tag2b)    AND
          ub.bag_tag3 = p_bag_tag3   AND
          ub.flight_id = p_flight_id AND
          ub.user_id = tu.user_id AND
          tu.agent_code = a.agent_code AND
          a.site_id = p_site_id;

      IF (config.get_value('ALLOW_BAG_WITH_NOFLT') = 'Y') THEN

         p_return_code := v_tag_unknown_no_flt;

      END IF;


   END IF;

   -- If we get here then we must have found a single flight_id from the parameter given.
   p_flight_id := l_flight_id;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

      -- If we didn't find anything with the given flight ID parameter, then
      -- we need to check for unknown bags existing with other flight ID's

      IF p_flight_id = 0 AND config.get_value('ALLOW_BAG_WITH_NOFLT') = 'Y' THEN

         -- Unknown bags do not have to be associated with flights
         -- However, if more than one unknown bag with this tag
         -- already exists we must prompt the user to select one
         BEGIN

            SELECT flight_id INTO l_flight_id FROM unknown_bag ub, tagtrak_users tu, agents a WHERE
               (ub.bag_tag2a = p_bag_tag2a OR ub.bag_tag2b = p_bag_tag2b)    AND
                ub.bag_tag3 = p_bag_tag3   AND
                ub.flight_id != 0 AND
                ub.user_id = tu.user_id AND
                tu.agent_code = a.agent_code AND
                a.site_id = p_site_id;

            -- When here no exception raised, so there must be a single unknown bag
            -- which is associated with a flight - this flight will now be used
            p_flight_id := l_flight_id;

         EXCEPTION

            WHEN TOO_MANY_ROWS THEN

               -- When here multiple unknown bags associated with flights have been found,
               -- so we must ask the user to select one
               p_return_code := v_tag_unknown;

            WHEN NO_DATA_FOUND THEN
               -- Not found any bags with associated flights.
               NULL;
         END;

         IF p_return_code = 0 THEN

            -- When here we have either a) not found a unknown bag at all in which case
            -- flight id remains 0 (unknown bag will be created later) or b) found a
            -- single unknown bag which is associated with a flight which we will use,
            -- so return standard unknown tag value (which will not result in the user
            -- being prompted to enter a flight).
            p_return_code := v_tag_unknown_no_flt;

         END IF;

      ELSE
         -- Return error code indicating that this is an unknown tag
         -- which the scanner should prompt for a flight for
         p_return_code := v_tag_unknown;

      END IF;

END get_flight_for_unknown_bag;


/******************************************************************************
 *
 * FUNCTION GET_FLIGHT_FOR_BAG
 *
 * Description:
 *
 * If a blank flight is passed this function tries to obtain the flight id
 * for the given bag. If a flight id is supplied, this function checks that the
 * flight/bag combination is valid. UNKNOWN_TAG or duplicate tag may be returned.
 *
 * Parameters:
 *
 * p_bag_tag2a        - bag tag 2a component
 * p_bag_tag2b        - bag tag 2b component
 * p_bag_tag3         - bag tag 3 component
 * p_pax_id           - the pax ID owning the bag we are looking for the flight for (this
 *                      may be set and it may not)
 * p_flight_id        - flight id associated with given bag
 * p_site_id          - the site ID we are operating at, this is only used to try and
 *                      find the flight ID if we do not have the flight ID.
 * p_return_code      - indicates whether bags was successfully identified
 * p_deleted_bag_flag - This flag is used to indicate whether we should return deleted_bag
 *                      errors to the calling function. Sometimes we may not want to if we
 *                      want to force the creation of an unknown bag that is then loggable.
 *
 ******************************************************************************/

PROCEDURE get_flight_for_bag
(
   p_bag_tag2a         IN      bag.bag_tag2a%type,
   p_bag_tag2b         IN      bag.bag_tag2b%type,
   p_bag_tag3          IN      bag.bag_tag3%type,
   p_pax_id            IN      pax.pax_id%type,
   p_flight_id         IN OUT  flight.flight_id%type,
   p_site_id           IN      site_info.site_id%type,
   p_return_code          OUT  number,
   p_deleted_bag_flag  IN      boolean,
   p_check_group       IN      boolean
)
IS

   l_flight_id flight.flight_id%type;
   l_pax_id    pax.pax_id%type;
   l_delete_marker bag.delete_marker%type := 0;
   l_group_no  bag.group_no%type;

BEGIN

   p_return_code := 0;

   IF p_flight_id IS NOT NULL AND p_flight_id != 0 THEN

      -- Flight supplied, check its valid
      -- Use the PAX ID supplied if possible, otherwise allow it to be 0 or NULL

      SELECT p.flight_id, b.delete_marker, b.group_no INTO l_flight_id, l_delete_marker, l_group_no FROM bag b, pax p WHERE
            (b.bag_tag2a = trim(p_bag_tag2a) OR b.bag_tag2b = p_bag_tag2b)    AND
             b.bag_tag3 = p_bag_tag3   AND
             b.pax_id = p.pax_id       AND
             p.flight_id = p_flight_id AND
            (p.pax_id = p_pax_id OR p_pax_id = 0 OR p_pax_id IS NULL);

   ELSE

      -- Flight ID not supplied, so try to find it

      SELECT p.flight_id, b.delete_marker, b.group_no INTO l_flight_id, l_delete_marker, l_group_no FROM bag b, pax p, flight f WHERE
          (b.bag_tag2a = p_bag_tag2a OR b.bag_tag2b = p_bag_tag2b)    AND
          b.bag_tag3 = p_bag_tag3 AND
          b.pax_id = p.pax_id AND
          f.flight_id = p.flight_id AND
          f.site_id = p_site_id;

   END IF;

   -- Did we find a bag that was deleted?
   IF l_delete_marker = 1 THEN

       -- if bag is deleted and the group no is not set then check for unknown bags
       IF (p_deleted_bag_flag = TRUE AND (p_check_group = FALSE OR (p_check_group = TRUE AND l_group_no is null))) THEN

          -- If we found a bag tag with the delete marker set and the flag is set then
          -- we know the SQL will find a deleted bag already and we want to ignore it.
          raise NO_DATA_FOUND;

       END IF;

       -- If the bag is deleted, the group_no is null and unknown bags can be created with no flight id
       -- then reset the flight id being returned so

       p_return_code := DELETED_TAG;

   END IF;

   p_flight_id := l_flight_id;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

      -- We haven't found the bag in the bag table, so check whether this bag
      -- already exists as an unknown bag

      l_flight_id := p_flight_id;

      get_flight_for_unknown_bag(p_bag_tag2a, p_bag_tag2b, p_bag_tag3, p_pax_id,
                                 l_flight_id, p_site_id, p_return_code);

      p_flight_id := l_flight_id;

   WHEN TOO_MANY_ROWS THEN
      -- Duplicate tag found

      IF p_flight_id IS NOT NULL AND p_flight_id != 0 THEN

         -- If a unresolvable duplicate returned check if a unique bag
         -- can be found by ignoring deleted tags

         BEGIN

            l_flight_id := p_flight_id;

            SELECT p.flight_id INTO l_flight_id FROM bag b, pax p WHERE
             (b.bag_tag2a = trim(p_bag_tag2a) OR b.bag_tag2b = p_bag_tag2b)    AND
             b.bag_tag3 = p_bag_tag3   AND
             b.pax_id = p.pax_id       AND
             p.flight_id = p_flight_id AND
             b.delete_marker = 0;

           -- If we get here without getting an exception then we must have been able to
            -- resolve that there is a unique flight, but deleted bags on another flight
            -- match the tag numbers. So we can safely return the flight ID selected.
            p_flight_id := l_flight_id;

         EXCEPTION

            WHEN TOO_MANY_ROWS THEN
               -- Still couldn't resolve duplicates
               p_return_code := UNRESOLVABLE_DUPLICATE;

         END;

      ELSE

         -- We weren't provided with a flight ID, so if we had one then we might be able
         -- to resolve this bag. Return to the calling function that this is a potentially
         -- resolvable duplicate
         p_return_code := DUPLICATE_TAG;

      END IF;

END get_flight_for_bag;

/******************************************************************************
 *
 * PROCEDURE INSERT_TRACKING_LOG
 *
 * Description:
 *
 * This procedure simply inserts the given data into the TRACKING_LOG table
 *
 * Parameters:
 *
 * p_bag_group    - group to create log for
 * p_bag_tag      - bag to create log for
 * p_flight_id    - flight associated with bag
 * p_location     - location the event occurred at
 * p_event_no     - event to log
 * p_status_no    - status of bag
 * p_user_id      - user requesting creation of log
 *
 ******************************************************************************/

PROCEDURE insert_tracking_log
(
   p_bag_group   IN  bag_group.group_no%type,
   p_bag_tag     IN  bag.bag_tag%type,
   p_bag_tag1    IN  bag.bag_tag1%type,
   p_bag_tag2a   IN  bag.bag_tag2a%type,
   p_bag_tag2b   IN  bag.bag_tag2b%type,
   p_bag_tag3    IN  bag.bag_tag3%type,
   p_pax_id      IN  pax.pax_id%type,
   p_flight_id   IN  flight.flight_id%type,
   p_location    IN  location.location_code%type,
   p_event_no    IN  tracking_events.event_no%type,
   p_status_no   IN  bag_status.status_no%type,
   p_user_id     IN  tagtrak_users.user_id%type
)
IS

   l_pax_id     pax.pax_id%type;
   l_error_code number := 0;

BEGIN

   -- Make sure we've got the pax id
   IF p_pax_id IS NULL or p_pax_id = 0 THEN

      BEGIN

         -- we want to try and select the PAX ID from the bag table first because the
         -- unknown_bag table might have the pax_id set to 0, that wouldn't help us
         -- create a tracking log

         SELECT p.pax_id INTO l_pax_id FROM bag b, pax p WHERE
            (b.bag_tag2a = p_bag_tag2a OR b.bag_tag2b = p_bag_tag2b) AND
            b.bag_tag3 = p_bag_tag3 AND
            b.pax_id  = p.pax_id  AND
            p.flight_id = p_flight_id AND
            b.delete_marker != '1';

      EXCEPTION

         WHEN OTHERS THEN

            BEGIN
               SELECT pax_id INTO l_pax_id FROM unknown_bag WHERE
                  (bag_tag2a = p_bag_tag2a OR bag_tag2b = p_bag_tag2b) AND
                  bag_tag3 = p_bag_tag3 AND
                  flight_id = p_flight_id;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN

                   -- We have not been able to get the pax id,
                   -- so we will not be able to log the bag
                   l_error_code := -1;
            END;
      END;

   ELSE

      l_pax_id := p_pax_id;

   END IF;

   IF l_error_code = 0 THEN

      INSERT INTO TRACKING_LOG
      (
          GROUP_NO,
          BAG_TAG,
          BAG_TAG1,
          BAG_TAG2A,
          BAG_TAG2B,
          BAG_TAG3,
          PAX_ID,
          FLIGHT_ID,
          TIMESTAMP,
          LOCATION,
          EVENT_NO,
          STATUS_NO,
          USER_ID
      )
      VALUES
      (
          p_bag_group,
          p_bag_tag,
          p_bag_tag1,
          p_bag_tag2a,
          p_bag_tag2b,
          p_bag_tag3,
          l_pax_id,
          p_flight_id,
          sysdate,
          p_location,
          p_event_no,
          p_status_no,
          p_user_id
      );

      COMMIT;

   END IF;

EXCEPTION

   WHEN DUP_VAL_ON_INDEX THEN
      -- This can happen when tracking log fired manually, but the
      -- for a event configured to be automatic. This prevents
      -- duplicate logs being generated
      null;

END insert_tracking_log;

/******************************************************************************
 *
 * PROCEDURE GET_BAG_GROUP
 *
 ******************************************************************************/

PROCEDURE get_bag_group
(
   p_bag_tag2a   IN   bag.bag_tag2a%type,
   p_bag_tag2b   IN   bag.bag_tag2b%type,
   p_bag_tag3    IN   bag.bag_tag3%type,
   p_flight_id   IN   flight.flight_id%type,
   p_bag_group   OUT  bag_group.group_no%type
)
IS
BEGIN

   p_bag_group := null;

   SELECT rtrim(b.group_no) INTO p_bag_group FROM bag b, pax p WHERE
      (b.bag_tag2a = p_bag_tag2a OR b.bag_tag2b = p_bag_tag2b) AND
      b.bag_tag3 = p_bag_tag3 AND
      b.pax_id = p.pax_id AND
      b.delete_marker = 0 AND
      p.flight_id = p_flight_id;

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

         -- We haven't found a group for this known tag.
         -- Check whether this is an unknown tag

        BEGIN

           SELECT rtrim(group_no) INTO p_bag_group FROM unknown_bag WHERE
              (bag_tag2a = p_bag_tag2a OR bag_tag2b = p_bag_tag2b) AND
              bag_tag3 = p_bag_tag3 AND
              pax_id = 0 AND
              flight_id = p_flight_id;

        EXCEPTION

           WHEN NO_DATA_FOUND THEN
              -- parameter to null to indicate this
              p_bag_group := null;
        END;

      WHEN TOO_MANY_ROWS THEN
         -- Unresolvable duplicate tag found. We cannot uniquely
         -- identify this bag, therefore cannnot determine group
         p_bag_group := null;

END get_bag_group;

/******************************************************************************
 *
 * PROCEDURE GET_ULD
 *
 * Description:
 *
 * This procedure gets the uld that a given bag belongs to
 *
 * Parameters:
 *
 * p_bag_tag      - bag to find group for
 * p_flight_id    - flight associated with bag
 * p_uld          - returned uld
 *
 ******************************************************************************/

PROCEDURE get_uld
(
   p_bag_tag2a   IN   bag.bag_tag2a%type,
   p_bag_tag2b   IN   bag.bag_tag2b%type,
   p_bag_tag3    IN   bag.bag_tag3%type,
   p_flight_id   IN   flight.flight_id%type,
   p_uld         OUT  ulds.uld_no%type
)
IS
BEGIN

   SELECT b.uld_no INTO p_uld FROM pax p, bag b WHERE
      p.flight_id = p_flight_id AND
      b.pax_id    = p.pax_id    AND
     (b.bag_tag2a = p_bag_tag2a OR b.bag_tag2b = p_bag_tag2b) AND
      b.bag_tag3  = p_bag_tag3;

   EXCEPTION

      WHEN NO_DATA_FOUND THEN
         -- We haven't found a group for this tag. Set group
         -- parameter to null to indicate this
         p_uld := null;

      WHEN TOO_MANY_ROWS THEN
         -- Unresolvable duplicate tag found. We cannot uniquely
         -- identify this bag, therefore cannnot determine group
         p_uld := null;

END get_uld;


/******************************************************************************
 *
 * PROCEDURE CREATE_LOG
 *
 ******************************************************************************/

PROCEDURE create_log
(
   p_bag_tag          IN  bag.bag_tag%type,
   p_bag_tag1         IN  bag.bag_tag1%type,
   p_bag_tag2a        IN  bag.bag_tag2a%type,
   p_bag_tag2b        IN  bag.bag_tag2b%type,
   p_bag_tag3         IN  bag.bag_tag3%type,
   p_group_or_uld     IN  bag_group.group_no%type,
   p_pax_id           IN  pax.pax_id%type,
   p_flight_id        IN  flight.flight_id%type,
   p_user_id          IN  tagtrak_users.user_id%type,
   p_location         IN  location.location_code%type,
   p_event_no         IN  tracking_events.event_no%type,
   p_status_no        IN  bag_status.status_no%type,
   p_scanner_id       IN  kit.scanner_id%type,
   p_return_code      OUT number,
   p_deleted_bag_flag IN  boolean

)
IS
   l_group          tagtrak_users.group_name%type;
   l_agent          tagtrak_users.agent_code%type;
   l_event_row      tracking_events%rowtype;
   l_group_or_uld   bag.group_no%type := NULL; -- stores group of given bag
   l_flight_id      flight.flight_id%type;
   l_log_count      integer := 0;
   l_site_id        agents.site_id%type;
   l_bag_group      bag_group.group_no%type;
   l_access_allowed integer;
   l_unknown_but_OK_to_continue boolean;
   l_uld_no         ulds.uld_no%type;
   l_user_id        tagtrak_users.user_id%type;
   l_uld_count      integer := 0;
   l_trace_action   char(10);
   l_trace_time     char(5);
   l_scanner_id     kit.scanner_id%type;

   CURSOR c_bags_in_group_cursor IS
      SELECT b.bag_tag, b.bag_tag1, b.bag_tag2a, b.bag_tag2b, b.bag_tag3, p.flight_id FROM bag b, pax p
         WHERE b.group_no = p_group_or_uld AND
               b.pax_id = p.pax_id
      UNION
      SELECT bag_tag, bag_tag1, bag_tag2a, bag_tag2b, bag_tag3, flight_id FROM unknown_bag
         WHERE group_no = p_group_or_uld;

   CURSOR c_bags_in_uld_cursor (l_user_id tagtrak_users.user_id%type) IS
      SELECT b.bag_tag, b.bag_tag1, b.bag_tag2a, b.bag_tag2b, b.bag_tag3, p.flight_id
      FROM   bag b,
             pax p,
             flight_outbound_v1 f,
             ulds u,
             agents a,
             tagtrak_users tu
      WHERE  TRIM(u.uld_no) = p_group_or_uld    AND
             u.flight_id = f.flight_id    AND
             f.flight_id = p.flight_id    AND
             f.flight_status != 'PD'      AND
             b.pax_id = p.pax_id          AND
             b.uld_no = u.uld_no          AND
             f.site_id = a.site_id        AND
             a.agent_code = tu.agent_code AND
             tu.user_id = l_user_id;
BEGIN

   p_return_code := 0;

   -- First we need to get the site of the user
   SELECT a.site_id, a.agent_code, t.group_name INTO l_site_id, l_agent, l_group FROM agents a, tagtrak_users t WHERE
      t.user_id = p_user_id AND
      t.agent_code = a.agent_code;

   -- Check whether a bag tag or a group has been passed.
   -- If a bag is passed, we need to look up the group for the bag
   -- before creating the log. If a group is passed we need to
   -- create one log per bag in the group

   IF p_bag_tag IS NOT NULL THEN

      l_flight_id := p_flight_id;

      -- check that we can uniquely identify this bag
      get_flight_for_bag(p_bag_tag2a, p_bag_tag2b, p_bag_tag3, p_pax_id, l_flight_id, l_site_id, p_return_code, p_deleted_bag_flag, FALSE);

      l_unknown_but_OK_to_continue := FALSE;

      IF p_return_code = v_tag_unknown_no_flt THEN
         l_unknown_but_OK_to_continue := TRUE;
         p_return_code := 0;
      END IF;

      IF p_return_code = 0 THEN

         -- Try to get a bag group for this tag so we can add it to the log

         get_bag_group(p_bag_tag2a, p_bag_tag2b, p_bag_tag3, l_flight_id, l_group_or_uld);

         IF l_group_or_uld IS NULL THEN

            -- we have not been able to find a group associated
            -- with the bag, so see if its in a uld
            get_uld(p_bag_tag2a, p_bag_tag2b, p_bag_tag3, l_flight_id, l_group_or_uld);

         END IF;

      END IF;

      IF p_return_code = 0 THEN

         IF l_flight_id != 0 THEN

            -- Check that this user has access to the flight
            SELECT count(*) INTO l_access_allowed FROM flight_access WHERE
               flight_id = l_flight_id AND agent_code = l_agent;

         ELSE

            -- No flight associated with this bag, so ignore check
            l_access_allowed := 1;

         END IF;

         -- Either access is allowed from the above select or the user is TTADMIN
         -- so has permission to create the tracking log anyway.
         IF (l_access_allowed > 0 OR user_maintenance.user_has_privilege(p_user_id, 'SUPER_USER')) THEN

            -- Now we have the event details we can create the tracking log
            insert_tracking_log
            (
                l_group_or_uld,
                p_bag_tag,
                p_bag_tag1,
                p_bag_tag2a,
                p_bag_tag2b,
                p_bag_tag3,
                p_pax_id,
                l_flight_id,
                p_location,
                p_event_no,
                p_status_no,
                p_user_id
            );

         ELSE

            -- User does not have access to this flight
            p_return_code := ACCESS_DENIED;

         END IF;

      END IF;

   ELSE

      -- A bag group has been passed to this procedure
      -- We need to loop and for each bag in the group create a log

      FOR l_bag_rec IN c_bags_in_group_cursor LOOP
         -- Now we have the event details so we can create the tracking log
         insert_tracking_log
         (
            p_group_or_uld,
            l_bag_rec.bag_tag,
            l_bag_rec.bag_tag1,
            l_bag_rec.bag_tag2a,
            l_bag_rec.bag_tag2b,
            l_bag_rec.bag_tag3,
            p_pax_id,
            l_bag_rec.flight_id,
            p_location,
            p_event_no,
            p_status_no,
            p_user_id
         );

         l_log_count := l_log_count + 1;

      END LOOP;

      IF l_log_count = 0 THEN

         -- No bags found for group, maybe a ULD has been passed

         FOR l_bag_rec IN c_bags_in_uld_cursor(p_user_id) LOOP

            insert_tracking_log
            (
               p_group_or_uld,
               l_bag_rec.bag_tag,
               l_bag_rec.bag_tag1,
               l_bag_rec.bag_tag2a,
               l_bag_rec.bag_tag2b,
               l_bag_rec.bag_tag3,
               p_pax_id,
               l_bag_rec.flight_id,
               p_location,
               p_event_no,
               p_status_no,
               p_user_id
            );
            
            l_log_count := l_log_count + 1; -- CA02209

         END LOOP;
         
         IF l_log_count = 0 THEN
	    p_return_code := -20053;   -- CA02209 No matching group or uld
         END IF;

         -- if a ULD exists create a ULD_TRACE entry for this event

         l_uld_no := p_group_or_uld;
         l_user_id := p_user_id;

         BEGIN

            SELECT count(ULD_NO) INTO l_uld_count
            FROM ulds u, flight f
            WHERE u.uld_no = l_uld_no AND
                  u.flight_id = f.flight_id;

         EXCEPTION

            WHEN NO_DATA_FOUND THEN

               l_uld_count := 0;

         END;

         -- ensure scanner id is not null as uld trace will reject it
         IF p_scanner_id IS NULL THEN
            l_scanner_id := '0';
         ELSE
            l_scanner_id := p_scanner_id;
         END IF;

         IF l_uld_count > 0 THEN

            -- either event number or status number must be populated but not both
            IF (p_event_no != 0 AND p_event_no IS NOT NULL) THEN

               l_trace_action := 'EVENT ' || to_char(p_event_no);

            ELSIF (p_status_no != 0 AND p_status_no IS NOT NULL) THEN

               l_trace_action := 'STATUS ' || to_char(p_status_no);

            END IF;

            add_uld_trace_record(l_uld_no, p_flight_id, l_user_id, l_scanner_id, p_location, l_trace_time, l_trace_action, 'N');

         END IF;

      END IF;

   END IF;

   IF l_unknown_but_OK_to_continue = TRUE AND p_return_code = 0 THEN
      p_return_code := v_tag_unknown_no_flt;
   END IF;

END create_log;


/******************************************************************************
 *
 * Public Procedures and Functions
 *
 ******************************************************************************/

/******************************************************************************
 *
 * PROCEDURE AUTO_EVENTS
 *
 ******************************************************************************/

PROCEDURE auto_events
(
   p_bag_tag       IN  bag.bag_tag%type,
   p_flight_id     IN  flight.flight_id%type,
   p_uld_no        IN  ulds.uld_no%type,
   p_pax_id        IN  pax.pax_id%type,
   p_pax_seq       IN  pax.pax_sequence_no%type,
   p_user_id       IN  tagtrak_users.user_id%type,
   p_location      IN  location.location_code%type,
   p_bag_group     IN  bag_group.group_no%type,
   p_event_count   IN OUT integer,
   p_no_of_errs    OUT integer
)
IS
   l_group         tagtrak_users.group_name%type;
   l_agent         tagtrak_users.agent_code%type;
   l_bags          bags_type;
   l_flights       flights_type;
   l_no_of_bags    integer;
   l_current_bag   integer;
   l_flight_id     flight.flight_id%type;
   l_error_event   tracking_events.event_no%type;
   l_return_code   number := 0;
   l_timestamp     date;
   l_pax_id        pax.pax_id%type;
   l_error_code    number := 0;
   l_bag_tag1      bag.bag_tag1%type;
   l_bag_tag2a     bag.bag_tag2a%type;
   l_bag_tag2b     bag.bag_tag2b%type;
   l_bag_tag3      bag.bag_tag3%type;
   l_bag_tagno     bag.bag_tag%type;
   l_bag_tag_type  number;

   CURSOR c_auto_events IS
      SELECT * FROM tracking_events WHERE
         agent_code = l_agent   AND
         (group_name = l_group  OR
          group_name IS NULL)   AND
         automatic_flag = 'Y'   AND
         (location = p_location OR
          location IS NULL);
BEGIN

   split_tags (p_bag_tag, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3, l_bag_tagno, l_bag_tag_type);

   IF (p_event_count > 0) THEN

      -- This case where the event count is greater than 0 only occurs from a Load Bag
      -- call. A problem was noticed because of since the auto events were triggered
      -- prior to the load bag attempt, it was impossible to tell whether the load was
      -- successful or not. Therefore db_funcs.c fires automatic events before the Load
      -- Bag operation attempt with the ULD assumed to be 'NULL' then afterwards if the
      -- automatic events count is greater than zero then we go back and update the ULD
      -- number in tracking logs created that match all the parameters we have and were
      -- within the last second since we assume separate bag tag scans would be outside
      -- that time period. The actual count of events isn't important, just that it is
      -- greater than 0.

      BEGIN
         -- Have we got a pax_id?
         IF p_pax_id IS NULL or p_pax_id = 0 THEN

            -- No, try and fetch it from the bag or unknown bag tables
            BEGIN

               SELECT p.pax_id INTO l_pax_id FROM bag b, pax p WHERE
                  (b.bag_tag2a = l_bag_tag2a OR b.bag_tag2b = l_bag_tag2b) AND
                  b.bag_tag3 = l_bag_tag3 AND
                  b.pax_id  = p.pax_id  AND
                  p.flight_id = p_flight_id
               UNION
               SELECT pax_id FROM unknown_bag WHERE
                  (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND
                  bag_tag3 = l_bag_tag3 AND
                  flight_id = p_flight_id;

            EXCEPTION

               WHEN OTHERS THEN
                  -- We have not been able to get the pax id,
                  -- so we will not be able to log the bag
                  l_error_code := -1;
            END;

         ELSE

            -- Yes, use the pax_id parameter we have been passed.
            l_pax_id := p_pax_id;

         END IF;

         IF l_error_code = 0 THEN

            -- Find the latest timestamp used in the tracking log table that matches
            -- the rest of the parameters, use that timestamp and take a second off
            -- it (since any more scans are bound to take longer) and then update
            -- the row with the uld number.

            SELECT max(timestamp-(1/86400))
              INTO l_timestamp
              FROM tracking_log
             WHERE (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b)
               AND bag_tag3 = l_bag_tag3
               AND pax_id = l_pax_id
               AND flight_id = p_flight_id
               AND location = p_location
               AND user_id = p_user_id;

            UPDATE tracking_log
               SET group_no = p_uld_no
             WHERE (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b)
               AND bag_tag3 = l_bag_tag3
               AND pax_id = l_pax_id
               AND flight_id = p_flight_id
               AND location = p_location
               AND user_id = p_user_id
               AND timestamp > l_timestamp;

            COMMIT;
         END IF;
      EXCEPTION
          when no_data_found then
             null;
      END;

  ELSE

      -- The normal case for auto_events being called will have the event_count set to 0.
      -- Therefore for the vast majority of times we will come directly into this section
      -- of code. The only exception is where we are updating the ULD number following a
      -- successful bag load.

      p_event_count := 0;
      p_no_of_errs  := 1;

      get_group_and_agent(p_user_id, l_group, l_agent);

      l_flight_id := p_flight_id;

      IF (p_bag_tag IS NULL OR p_bag_tag = '0') THEN

         -- No specific bag tag has been given so we must have to create tracking logs
         -- with something else;
         --                      A Bag group
         --                   or a ULD
         --                   or a flight id and pax seq
         --                   or a flight id and pax id
         --                   or just a flight id
         -- Use one of the above conditions to populate a table of bag tags that we can
         -- use later to create the tracking logs.
         -- Note if we have been given a bag group then that can be for multiple flights,
         -- so in that case we would populate a table of flight ids relating to the table
         -- of bag tags. In all other cases we have a particular flight.

         -- No tag given, so see if a group has been given.
         IF p_bag_group IS NOT NULL THEN

            -- a group, not a bag tag has been given, so we'll log
            -- all bags in it
            get_bags_in_group(p_bag_group, l_bags, l_flights, l_no_of_bags);

         -- No tag given so see if a uld has been given.
         ELSIF p_uld_no IS NOT NULL THEN

            -- A uld, not a bag tag has been given, so we'll log
            -- all bags in it
            get_bags_in_uld(p_user_id, p_uld_no, l_flight_id, l_bags, l_no_of_bags);

         ELSE
            -- No tag or uld given so see if we've got a flight
            IF p_flight_id IS NOT NULL THEN

               IF p_pax_seq IS NOT NULL AND p_pax_seq != 0 THEN

                  -- Have pax seq and flight, so log all bags for this
                  -- passenger
                  get_bags_for_pax_seq(l_flight_id, p_pax_seq, l_bags, l_no_of_bags);

               ELSIF p_pax_id IS NOT NULL AND p_pax_id != 0 THEN

                  -- Have pax id and flight, so log all bags for this
                  -- passenger
                  get_bags_for_pax_id(l_flight_id, p_pax_id, l_bags, l_no_of_bags);

               ELSE
                  -- Only have flight, so we'll log every bag on this flight
                  -- for each automatic event
                  get_bags_for_flight(l_flight_id, l_bags, l_no_of_bags);

               END IF;

            END IF;

         END IF;

      ELSE

         -- A tag has been passed. Now check whether we have been passed a pax id,
         -- but no flight id. If we have we must get the flight as this is required
         -- in order to create a log

         IF (l_flight_id IS NULL OR l_flight_id = 0) AND
            (p_pax_id IS NOT NULL AND p_pax_id != 0)
         THEN

            SELECT flight_id INTO l_flight_id FROM pax WHERE
               pax_id = p_pax_id AND rownum = 1;

         END IF;

      END IF;

      FOR l_event_rec IN c_auto_events LOOP

         IF (p_bag_tag IS NULL OR p_bag_tag = '0') THEN

            -- We dont have a single bagtag so we must have populated the table of
            -- bagtags from what we have been given, so create a log for each bag
            -- from the table of bagtags

            l_current_bag := 1;

            WHILE l_current_bag <= l_no_of_bags LOOP

               -- Were we given a bag group?
               IF (p_bag_group IS NOT NULL) THEN

                  -- If we were given a bag group then we could have bags from more than
                  -- one flight, therefore we need to fetch the relevant flight id from
                  -- the table of flight id's we fetched earlier.
                  l_flight_id := l_flights(l_current_bag);
               END IF;

               create_tracking_log
               (
                  l_bags(l_current_bag),
                  null, -- no bag group associated with auto events as
                        -- operation always applies to a bag only
                  p_pax_id,
                  l_flight_id,
                  p_user_id,
                  p_location,
                  l_event_rec.event_no,
                  null, -- no status, only interested in events here
                  '0',
                  'N',
                  l_error_event,
                  l_return_code,
                  FALSE -- no deleted bags here
               );

               -- Check the next bag in the table.
               l_current_bag := l_current_bag + 1;

           END LOOP;

         ELSE

            -- A single tag has been given so create a log for it
            create_tracking_log
            (
               p_bag_tag,
               null, -- no bag group associated with auto events as
                     -- operation always applies to a bag only
               p_pax_id,
               l_flight_id,
               p_user_id,
               p_location,
               l_event_rec.event_no,
               null, -- no status, only interested in events here
               '0',
               'N',
               l_error_event,
               l_return_code,
               FALSE -- no deleted bags here
            );

         END IF;

         IF l_return_code != 0 THEN

            -- Keep a count of the number of events that failed
            p_no_of_errs := p_no_of_errs + 1;

         END IF;

         -- Keep a count of the events we fired, whether successfully or not
         p_event_count := p_event_count + 1;
         l_return_code := 0;

      END LOOP;

   -- Subtract 1 from no of errs, to ensure count correct
   p_no_of_errs := p_no_of_errs - 1;

   END IF;

END auto_events;

/******************************************************************************
 *
 * PROCEDURE CREATE_TRACKING_LOG
 *
 ******************************************************************************/

PROCEDURE create_tracking_log
(
   p_bag_tag          IN  bag.bag_tag%type,
   p_bag_group        IN  bag_group.group_no%type,
   p_pax_id           IN  pax.pax_id%type,
   p_flight_id        IN  flight.flight_id%type,
   p_user_id          IN  tagtrak_users.user_id%type,
   p_location         IN  location.location_code%type,
   p_event_no         IN  tracking_events.event_no%type,
   p_status_no        IN  bag_status.status_no%type,
   p_scanner_id       IN  kit.scanner_id%type,
   p_force            IN  char,
   p_error_event      OUT tracking_events.event_no%type, -- stores no of failed event if any
   p_return_code      OUT number,
   p_deleted_bag_flag IN  boolean
)
IS
   l_group         tagtrak_users.group_name%type;
   l_agent         tagtrak_users.agent_code%type;
   l_event_row     tracking_events%rowtype;
   l_event_no      tracking_events.event_no%type;
   l_event_history event_record_type; -- used to store events that have been logged
   l_events_logged number := 0; -- how many events have been logged
   l_current_event number; -- temp event count
   l_temp          number; -- temp return value used when logging failures
   l_bag_tag1      bag.bag_tag1%type;
   l_bag_tag2a     bag.bag_tag2a%type;
   l_bag_tag2b     bag.bag_tag2b%type;
   l_bag_tag3      bag.bag_tag3%type;
   l_bag_tagno     bag.bag_tag%type;
   l_bag_tag_type  number;
   l_ret_code      number := 0;

BEGIN

   p_return_code := 0;

   -- Initialise error event flag. This is used to indicate which linked event
   -- failed to the scanner. If the creation of a bag status log failed this
   -- will still be set to null, which will indicate that it was the logging
   -- of the bag status that failed
   p_error_event := null;

   split_tags (p_bag_tag, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3, l_bag_tagno, l_bag_tag_type);

   get_group_and_agent(p_user_id, l_group, l_agent);

   -- Have we been given a status to log from the parameters?
   IF p_status_no IS NOT NULL AND p_status_no != 0 THEN

      -- Bag status was given, so we can create this log first before checking for any
      -- events that we may need to log as well
      create_log(l_bag_tagno, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3,
                 p_bag_group, p_pax_id, p_flight_id, p_user_id, p_location, null,
                 p_status_no, p_scanner_id, p_return_code, p_deleted_bag_flag);
   END IF;

   l_event_no := p_event_no;

   WHILE (l_event_no IS NOT NULL AND l_event_no != 0) AND
         (p_return_code = 0 or p_return_code = v_tag_unknown_no_flt)
   LOOP

      -- Get details of the event to create a log for
      -- Note: if location in tracking_events is null the event can apply to any location
      BEGIN

         -- Is this being called by a super-user?
         IF user_maintenance.user_has_privilege(p_user_id, 'SUPER_USER') THEN

            -- This stored procedure is being called by a super user so we can ignore the
            -- agent and group associated with the tracking event. We dont need to care
            -- about the force flag, since for MWS users the force flag will be
            -- automatically set if it is a super user calling this stored procedure.
            -- Scanner users cannot set the force flag at all.
            SELECT * INTO l_event_row FROM tracking_events WHERE event_no = l_event_no;

         ELSE
            -- The stored procedure isn't being called by a super user we need to include
            -- a check for the agent code and group name when we are searching for an
            -- event that matches the parameter.
            SELECT * INTO l_event_row FROM tracking_events WHERE
                  event_no = l_event_no AND agent_code = l_agent AND
                  (group_name = l_group OR group_name IS NULL);

         END IF;

      EXCEPTION

         WHEN NO_DATA_FOUND THEN

            -- Either the given event does not exist, or the user does not have access to it
            p_return_code := -651;
      END;

      IF (p_return_code = 0 or p_return_code = v_tag_unknown_no_flt) THEN

         -- Check that given location matches that of the event, or the event location is
         -- null (in which case the event can occur at any location)

         IF l_event_row.location IS NOT NULL AND
            l_event_row.location != p_location AND
            p_force != 'Y'
         THEN

            -- Location of user is not the location that the event
            -- occurs at - return EVENT_LOCATION_INVALID
            p_return_code := -20050;

         ELSE

            -- Either the location matches or we need to force the log creation.

            -- create the log for the event
            create_log(l_bag_tagno, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3,
                       p_bag_group, p_pax_id, p_flight_id, p_user_id, p_location,
                       l_event_no, null, p_scanner_id, p_return_code, p_deleted_bag_flag);

            IF (p_return_code = 0 or p_return_code = v_tag_unknown_no_flt) THEN

               -- Event logged without error, now that has been fired, look at whether
               -- there is a linked event that now needs to be fired.
               IF l_event_row.link_event IS NOT NULL THEN

                  -- Check that the next event has not fired previously. If it has an
                  -- infinite chain of events has been configured and we want to return
                  -- an error to the user.

                  l_current_event := 1;

                  WHILE l_current_event <= l_events_logged AND
                        l_event_history(l_current_event) != l_event_no
                  LOOP

                     l_current_event := l_current_event + 1;

                  END LOOP;

                  IF l_current_event <= l_events_logged THEN

                     -- Recursive chain of events detected - return EVENT_RECURSION_DETECTED
                     p_return_code := -20051;

                  ELSE

                     -- Remember that we have fired this event. This allows us
                     -- to detect infinite event loops
                     l_events_logged := l_events_logged + 1;
                     l_event_history(l_events_logged) := l_event_no;

                     -- Move to the next event, we will now loop round and fire this event
                     l_event_no := l_event_row.link_event;

                  END IF;

               ELSE

                  -- link event is null so we've come to the end of the chain and setting
                  -- the event number to null will let us exit the loop.
                  l_event_no := null;

               END IF; -- END OF OF EVENT NOT NULL

            END IF; -- END OF IF p_return_code = 0

         END IF; -- END OF LOCATION MATCHES

      END IF; -- END OF IF p_return_code = 0

      -- If an event has failed, return it number to indicates where the chain of
      -- events failed
      IF (p_return_code != 0 AND p_return_code != v_tag_unknown_no_flt AND p_return_code != DELETED_TAG ) THEN

         -- Set parameter to inform calling procedure which event failed
         p_error_event := l_event_no;

         -- Now create a log for the error, to allow it to be viewed through the MWS
         create_log(l_bag_tagno, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3, p_bag_group,
                    p_pax_id, p_flight_id, p_user_id, p_location, p_return_code,
                    null, p_scanner_id, l_ret_code, p_deleted_bag_flag);

      END IF;

   END LOOP; -- END OF WHILE l_event_no is not null

   -- Save the transaction
   COMMIT;

END create_tracking_log;


/******************************************************************************
 *
 * PROCEDURE CREATE_TRACKING_LOG_FROM_SCANR
 *
 ******************************************************************************/

PROCEDURE create_tracking_log_from_scanr
(
   p_bag_tag          IN  bag.bag_tag%type,
   p_bag_group        IN  bag_group.group_no%type,
   p_flight_id        IN  flight.flight_id%type,
   p_user_id          IN  tagtrak_users.user_id%type,
   p_location         IN  location.location_code%type,
   p_event_no         IN  tracking_events.event_no%type,
   p_status_no        IN  bag_status.status_no%type,
   p_scanner_id       IN  kit.scanner_id%type,
   p_error_event      OUT tracking_events.event_no%type, -- stores no of failed event if any
   p_timestamp        OUT char,
   p_return_code      OUT number
)

IS
   l_bag_tag1      bag.bag_tag1%type;
   l_bag_tag2a     bag.bag_tag2a%type;
   l_bag_tag2b     bag.bag_tag2b%type;
   l_bag_tag3      bag.bag_tag3%type;
   l_bag_tagno     bag.bag_tag%type;
   l_bag_tag_type  number;
   l_initial_ret   number;
   l_deleted_bag_tag  boolean := FALSE;
   l_current_time  date;
   l_airport_code  pax.departure_airport_code%type := null;

BEGIN

   -- Get time log created to display in the scanner
   l_airport_code := get_offset.get_user_location(p_user_id);
   l_current_time := sysdate + get_offset.airport_code(l_airport_code);
   p_timestamp := to_char(l_current_time, 'HH24:MI');

   -- attempt to create the tracking log, defaulting the values we dont get from the scanner
   create_tracking_log
   (
      p_bag_tag,
      p_bag_group,
      0,
      p_flight_id,
      p_user_id,
      p_location,
      p_event_no,
      p_status_no,
      p_scanner_id,
      'N',
      p_error_event,
      p_return_code,
      FALSE
   );

   IF (( (p_return_code = v_tag_unknown_no_flt) OR (p_return_code = v_tag_unknown) ) AND
       ( (p_flight_id IS NOT NULL AND p_flight_id != 0) OR
         (config.get_value('ALLOW_BAG_WITH_NOFLT') = 'Y') )
      OR (p_return_code = DELETED_TAG))
   THEN

      -- We could not create the log because the bag tag number is unknown or deleted.
      -- Create an unknown bag tag and try again. This code is here to prevent
      -- automatic events from creating unknown tags or multiple entries in the
      -- unknown_bag table

      split_tags (p_bag_tag, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3, l_bag_tagno, l_bag_tag_type);

      IF ((config.get_value('ALLOW_BAG_WITH_NOFLT') = 'Y') OR
          (p_flight_id IS NOT NULL AND p_flight_id != 0)) THEN

          unauthorised_bags.add_unknown_bag(l_bag_tagno, l_bag_tag1, l_bag_tag2a,
                                            l_bag_tag2b, l_bag_tag3, p_flight_id, 0,
                                            p_return_code, p_user_id, p_bag_group, p_location);
      END IF;

      IF (p_return_code = DELETED_TAG) THEN
         -- If we found the bag tag was deleted then we must set a flag so that we
         -- know an unknown bag has been created already and to assign the tracking
         -- log to that.
         l_deleted_bag_tag := TRUE;
      END IF;

      create_tracking_log ( l_bag_tagno, p_bag_group, 0, p_flight_id, p_user_id, p_location,
                            p_event_no, p_status_no, p_scanner_id, 'N', p_error_event, p_return_code, l_deleted_bag_tag );

      IF (config.get_value('ALLOW_BAG_WITH_NOFLT') = 'Y') THEN
         -- Reset original
         p_return_code := v_tag_unknown_no_flt;
      END IF;

      IF (config.get_value('UNKNOWN_BAGS_WARNING') != 'Y' AND p_return_code = v_tag_unknown_no_flt) THEN
         -- If the config value is set then we dont want to display the warning
         -- messages on the scanner. Therefore reset the error value returned
         -- to the scanner to 0 to indicate 'complete success'.
         p_return_code := 0;
      END IF;

   END IF;

END create_tracking_log_from_scanr;

/******************************************************************************
 *
 * PROCEDURE CREATE_TRACKING_LOG_FROM_MWS
 *
 ******************************************************************************/

PROCEDURE create_tracking_log_from_mws
(
   p_bag_tag          IN  bag.bag_tag%type,
   p_bag_group        IN  bag_group.group_no%type,
   p_flight_id        IN  flight.flight_id%type,
   p_user_id          IN  tagtrak_users.user_id%type,
   p_location         IN  location.location_code%type,
   p_event_no         IN  tracking_events.event_no%type,
   p_status_no        IN  bag_status.status_no%type,
   p_pax_id           IN  pax.pax_id%type,
   p_error_event      OUT event_record_type,
   p_return_code      OUT return_code_type
)
IS

   l_error_event    tracking_events.event_no%type;
   l_return_code    number := 0;
   l_force          char;
BEGIN

   if user_maintenance.user_has_privilege(p_user_id, 'SUPER_USER') then
      l_force := 'Y';
   else
      l_force := 'N';
   end if;

   create_tracking_log
   (
      p_bag_tag,
      p_bag_group,
      p_pax_id,
      p_flight_id,
      p_user_id,
      p_location,
      p_event_no,
      p_status_no,
      '0',
      l_force,
      l_error_event,
      l_return_code,
      FALSE
   );

   p_error_event(1) := l_error_event;
   p_return_code(1) := l_return_code;

   p_error_event(2) := 0;
   p_return_code(2) := 0;

END create_tracking_log_from_mws;


/******************************************************************************
 *
 * PROCEDURE CREATE_TRACKING_LOG_FROM_MWS_2
 *
 ******************************************************************************/

PROCEDURE create_tracking_log_from_mws_2
(
   p_bag_tag          IN  bag.bag_tag%TYPE,
   p_bag_group        IN  bag_group.group_no%TYPE,
   p_flight_id        IN  flight.flight_id%TYPE,
   p_user_id          IN  tagtrak_users.user_id%TYPE,
   p_location         IN  location.location_code%TYPE,
   p_event_no         IN  tracking_events.event_no%TYPE,
   p_status_no        IN  bag_status.status_no%TYPE,
   p_pax_id           IN  pax.pax_id%TYPE,
   p_error_event      OUT tracking_events.event_no%TYPE,
   p_return_code      OUT NUMBER
)

IS

   l_error_event    tracking_events.event_no%TYPE;
   l_return_code    NUMBER := 0;
   l_force          CHAR;

BEGIN

   IF user_maintenance.user_has_privilege(p_user_id, 'SUPER_USER') THEN
      l_force := 'Y';
   ELSE
      l_force := 'N';
   END IF;

   create_tracking_log
   (
      p_bag_tag,
      p_bag_group,
      p_pax_id,
      p_flight_id,
      p_user_id,
      p_location,
      p_event_no,
      p_status_no,
      0,
      l_force,
      l_error_event,
      l_return_code,
      FALSE
   );

   p_error_event := l_error_event;
   p_return_code := l_return_code;

END create_tracking_log_from_mws_2;


/******************************************************************************
 *
 * PROCEDURE CREATE_BAG_GROUP
 *
 ******************************************************************************/

PROCEDURE create_bag_group
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_bag_group   IN  bag_group.group_no%type,
   p_holding_pen IN  VARCHAR2,
   p_bag_count   OUT  number,
   p_return_code OUT number
)
IS
   l_bag_count      bag_group.no_of_bags%type := 0;
   l_group_exists   boolean := TRUE;
   l_no_access      boolean := FALSE;
   l_uld_count      number := 0;
   l_site_id        agents.site_id%type;
   l_group          tagtrak_users.group_name%type;
   l_agent          tagtrak_users.agent_code%type;
BEGIN

   p_return_code := 0;
   p_bag_count := 0;

   -- First check whether this group already exists

   BEGIN
      SELECT no_of_bags INTO l_bag_count FROM bag_group WHERE
         group_no = p_bag_group;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         l_group_exists := FALSE;
   END;

   IF l_group_exists = FALSE THEN

      -- First we need to get the site of the user
      SELECT a.site_id INTO l_site_id FROM agents a, tagtrak_users t WHERE
         t.user_id = p_user_id AND
         t.agent_code = a.agent_code;

      -- Group does not exist, so may need to create it. Need to
      -- check that there isn't a uld with this no. first.

      SELECT count(*) INTO l_uld_count FROM ulds u, flight f WHERE
         rtrim(u.uld_no) = p_bag_group AND
         u.flight_id = f.flight_id AND
         f.site_id = l_site_id;

      IF l_uld_count > 0 THEN

         -- A uld with this name already exists
         p_return_code := pkg_constants.ERROR_ULD_NAME_EXISTS;

      ELSE

         -- No uld exist with this name, so it's O.K. to create the bag group

         -- Need group name and agent in order to create bag group
         get_group_and_agent(p_user_id, l_group, l_agent);

         -- Now create the bag group

         INSERT INTO bag_group
         (
            group_no,
            no_of_bags,
            user_id,
            site_id,
            agent_code,
            group_name,
            holding_pen
         )
         values
         (
            p_bag_group,
            0,
            p_user_id,
            l_site_id,
            l_agent,
            l_group,
            p_holding_pen
         );

      END IF;

   ELSE
      -- We found a group exists, but does it exist for our current user?

      BEGIN

         -- We need to get the agent code for the current user
         SELECT agent_code INTO l_agent
           FROM tagtrak_users WHERE
              user_id = p_user_id;

         SELECT no_of_bags INTO l_bag_count FROM bag_group WHERE
            group_no = p_bag_group AND
            agent_code = l_agent;

      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            l_no_access := TRUE;
      END;

      IF l_no_access = FALSE THEN
         -- Group already exists, so just return no of bags
         p_bag_count := l_bag_count;
      ELSE
         -- The bag group exists but we have no access to it
         p_return_code := pkg_constants.ERROR_FLIGHT_ACCESS_DENIED;
      END IF;

   END IF;

   -- Save the transaction
   COMMIT;

END create_bag_group;


/******************************************************************************
 *
 * PROCEDURE REMOVE_BAG_GROUP
 *
 ******************************************************************************/

PROCEDURE remove_bag_group
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_bag_group   IN  bag_group.group_no%type,
   p_force       IN  varchar2,
   p_location    IN  location.location_code%type,
   p_scanner_id  IN  kit.id%type,
   p_return_code OUT number
)
IS
   l_site_id     agents.site_id%type;
   l_bag_count   number;
   l_group       tagtrak_users.group_name%type;
   l_agent       tagtrak_users.agent_code%type;
   l_user_id     tagtrak_users.user_id%type;
   l_bag_tag1      bag.bag_tag1%type;
   l_bag_tag2a     bag.bag_tag2a%type;
   l_bag_tag2b     bag.bag_tag2b%type;
   l_bag_tag3      bag.bag_tag3%type;
   l_bag_tagno     bag.bag_tag%type;
   l_bag_tag_type  number;

CURSOR c_bag_group_contents IS
    SELECT b.bag_tag, p.pax_id, f.flight_id FROM bag b, pax p, flight f WHERE
            b.group_no = p_bag_group AND
              p.pax_id = b.pax_id AND
              p.flight_id = f.flight_id AND f.flight_id IN
                    (SELECT flight_id FROM flight WHERE site_id = l_site_id)
    UNION
    SELECT ub.bag_tag, ub.pax_id, ub.flight_id FROM unknown_bag ub, bag_group bg WHERE
           ub.group_no = p_bag_group AND
           bg.group_no = p_bag_group AND
           bg.site_id = l_site_id;


BEGIN

   p_return_code := 0;

   -- First we need to get the site and agent code of the user
   SELECT a.site_id, t.agent_code INTO l_site_id, l_agent FROM agents a, tagtrak_users t WHERE
      t.user_id = p_user_id AND
      t.agent_code = a.agent_code;

   -- First we need to check whether the group is empty
   BEGIN

      SELECT no_of_bags INTO l_bag_count FROM bag_group WHERE
         group_no = p_bag_group AND
         site_id = l_site_id;

   EXCEPTION

      WHEN NO_DATA_FOUND THEN
         -- UNKNOWN_BAG_GROUP
         p_return_code := pkg_constants.ERROR_UNKNOWN_BAG_GROUP;

   END;

   IF p_return_code = 0 THEN

      -- Group exists, check that we are allowed to delete it - i.e. are
      -- we TTADMIN OR do we belong to the same group and agent as the
      -- user that created the group?

      IF NOT user_maintenance.user_has_privilege(p_user_id, 'SUPER_USER') THEN

         BEGIN

            SELECT no_of_bags INTO l_bag_count FROM bag_group WHERE
               group_no = p_bag_group AND
               site_id = l_site_id AND
               agent_code = l_agent;

         EXCEPTION

            WHEN NO_DATA_FOUND THEN
               -- ACCESS_DENIED
               p_return_code := pkg_constants.ERROR_FLIGHT_ACCESS_DENIED;
         END;

      END IF;

   END IF;

   IF p_return_code = 0 AND l_bag_count > 0 THEN

      -- Group not empty
      IF p_force = 'Y' THEN

         -- use the local variable because the stored procedure needs to write back the value.
         l_user_id := p_user_id;

         FOR l_this_rec IN c_bag_group_contents LOOP

            -- Split the tag value into components for entering into the bag trace table
            split_tags (l_this_rec.bag_tag, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3, l_bag_tagno, l_bag_tag_type);

              -- We need to use l_user_id here because the stored proc writes back to the variable
            add_bag_trace_record ( l_user_id,
                 p_location,
                 p_return_code,    -- error code
                 l_bag_tagno,
                 l_bag_tag1,
                 l_bag_tag2a,
                 l_bag_tag2b,
                 l_bag_tag3,
                 'UNGROUPED',
                 p_scanner_id,
                 l_this_rec.flight_id,
                 l_this_rec.pax_id,
                 p_bag_group
            );
         END LOOP;

         -- If we managed to get this far then we must have an actual bag to
         -- update, otherwise we'd hit the exception and update the unknown_bag
         -- table instead.
         UPDATE bag SET group_no = null WHERE
            group_no = p_bag_group AND
              pax_id IN
                 (SELECT pax_id FROM pax WHERE flight_id IN
                    (SELECT flight_id FROM flight WHERE site_id = l_site_id));

         -- Force flag set, so empty the group
         UPDATE unknown_bag SET group_no = null WHERE
             group_no = p_bag_group;

      ELSE

         -- BAG_GROUP_NOT_EMPTY
         p_return_code := pkg_constants.ERROR_BAG_GROUP_NOT_EMPTY;

      END IF;

   END IF;

   IF p_return_code = 0 THEN

      -- Now O.K. to remove the group
      DELETE FROM bag_group WHERE
         group_no = p_bag_group AND
         site_id = l_site_id;

   END IF;

   -- Save the transaction
   COMMIT;

END remove_bag_group;

/******************************************************************************
 *
 * PROCEDURE ADD_BAG_TO_GROUP
 *
 ******************************************************************************/

PROCEDURE add_bag_to_group
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_bag_group   IN  bag_group.group_no%type,
   p_bag_tag     IN  bag.bag_tag%type,
   p_flight_id   IN  flight.flight_id%type,
   p_location    IN  location.location_code%type,
   p_scanner_id  IN  kit.id%type,
   p_bag_count   OUT number,
   p_return_code OUT number
)
IS
   l_site_id       agents.site_id%type;
   l_bag_count     number := 0;
   l_bag_group     bag_group.group_no%type;
   l_uld_no        ulds.uld_no%type;
   l_group         tagtrak_users.group_name%type;
   l_agent         tagtrak_users.agent_code%type;
   l_flight_id     flight.flight_id%type;
   l_access_check  number := 0;
   l_bag_tag1      bag.bag_tag1%type;
   l_bag_tag2a     bag.bag_tag2a%type;
   l_bag_tag2b     bag.bag_tag2b%type;
   l_bag_tag3      bag.bag_tag3%type;
   l_bag_tagno     bag.bag_tag%type;
   l_bag_tag_type  number;
   l_pax_id        pax.pax_id%type;
   l_user_id       tagtrak_users.user_id%type := p_user_id;
   l_log_code      number := 0;
   l_holding_pen   bag_group.holding_pen%TYPE;
BEGIN

   p_return_code := 0;
   p_bag_count := 0;
   l_user_id := p_user_id;
   split_tags (p_bag_tag, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3, l_bag_tagno, l_bag_tag_type);

   -- First we need to get the site of the user
   SELECT a.site_id, a.agent_code, t.group_name INTO l_site_id, l_agent, l_group FROM agents a, tagtrak_users t WHERE
      t.user_id = p_user_id AND
      t.agent_code = a.agent_code;

   -- Check that the given group exists
   BEGIN

      SELECT no_of_bags, holding_pen INTO l_bag_count, l_holding_pen FROM bag_group WHERE
         group_no = p_bag_group AND
         site_id = l_site_id;

   EXCEPTION

      WHEN NO_DATA_FOUND THEN
         -- UNKNOWN_BAG_GROUP
         p_return_code := pkg_constants.ERROR_UNKNOWN_BAG_GROUP;
   END;

   -- Find out whether we have access to the bag group and how many bags are in it.
   IF p_return_code = 0 THEN
      BEGIN

         SELECT no_of_bags INTO l_bag_count FROM bag_group WHERE
            group_no = p_bag_group AND
            site_id = l_site_id AND
            agent_code = l_agent AND
            group_name = DECODE(l_holding_pen, 'Y', group_name, l_group);

      EXCEPTION

         WHEN NO_DATA_FOUND THEN
            -- ACCESS_DENIED
            p_return_code := pkg_constants.ERROR_FLIGHT_ACCESS_DENIED;

      END;

   END IF;

   IF p_return_code = 0 THEN

      l_flight_id := p_flight_id;

      -- attempt to get the flight/check the bag is not a duplicate
      -- Set the delete flag to true so that deleted bags can be added to a group as a unknown bag
      get_flight_for_bag(l_bag_tag2a, l_bag_tag2b, l_bag_tag3, NULL, l_flight_id, l_site_id, p_return_code, TRUE, TRUE);

dbms_output.put_line('Return code from get_flight_for_bag := ' || p_return_code);
dbms_output.put_line('Flight ID := ' || l_flight_id);

      -- if a DELETED_TAG has been returned here then there is a deleted bag which
      -- is already grouped so set the return code to the correct error
      IF (p_return_code = DELETED_TAG) THEN
         p_return_code := -20055;
      END IF;

      IF (( (p_return_code = v_tag_unknown_no_flt) OR (p_return_code = v_tag_unknown) ) AND
          ( (l_flight_id IS NOT NULL AND l_flight_id != 0) OR
         (config.get_value('ALLOW_BAG_WITH_NOFLT') = 'Y') ))
      THEN

         IF (p_return_code = v_tag_unknown) THEN
            -- Clear error code as unknown bag about to be added
            -- for this given flight
            p_return_code := 0;
         END IF;

         -- We must have an unknown bag and it cannot exist in the unknown_bag
         -- table since get_flight_for_bag would not return -1002 (v_tag_unknown_no_flt)
         -- if it existed. Therefore we need to add the unknown bag entry to the table
         -- since we also have the flight ID.
         unauthorised_bags.add_unknown_bag(l_bag_tagno, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3, l_flight_id, 0, p_return_code, p_user_id, NULL, p_location);
      END IF;

      IF (p_return_code = 0 OR p_return_code = v_tag_unknown_no_flt) THEN

         -- We may now have a flight for the bag. We may not
         -- have the flight id if this is an unknown bag

         IF l_flight_id != 0 THEN

            -- Check that we have access to the flight
            SELECT count(*) INTO l_access_check FROM flight_access WHERE
               flight_id = l_flight_id AND
               agent_code = l_agent;
         ELSE

            -- No flight associated with this bag, so ignore check
            l_access_check := 1;

         END IF;

         IF l_access_check = 0 THEN

            -- ACCESS_DENIED
            p_return_code := pkg_constants.ERROR_FLIGHT_ACCESS_DENIED;

         ELSE

            -- Have access to the flight, so check that the
            -- bag isn't already loaded

            BEGIN

               SELECT uld_no, group_no INTO l_uld_no, l_bag_group FROM bag WHERE
                 (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND
                  bag_tag3 = l_bag_tag3 AND
                  delete_marker = 0 AND
                  pax_id IN (SELECT DISTINCT pax_id FROM pax WHERE flight_id = l_flight_id);

               IF l_uld_no IS NOT NULL THEN

                  -- BAG_ALREADY_LOADED
                  p_return_code := pkg_constants.ERROR_BAG_ALREADY_LOADED;

               ELSIF rtrim(l_bag_group) IS NOT NULL THEN

                  -- BAG_ALREADY_GROUPED
                  p_return_code := pkg_constants.ERROR_BAG_ALREADY_GROUPED;

               ELSE
                  -- we must have found a row with a blank group_no value - update it
                  UPDATE bag SET group_no = p_bag_group WHERE
                     (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND
                      bag_tag3 = l_bag_tag3 AND
                      delete_marker = 0 AND
                      pax_id IN
                         (SELECT distinct pax_id FROM pax WHERE flight_id = l_flight_id);

                  -- Increment the bag group
                  UPDATE bag_group SET no_of_bags = no_of_bags + 1 WHERE
                     group_no = p_bag_group;
               END IF;



            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  -- if we get here then we cant have found a row matching the
                  -- bag tag number and flight in the bag table, therefore it
                  -- must be an unknown bag and we need to update the group number
                  -- there instead.

                  SELECT group_no into l_bag_group FROM unknown_bag WHERE
                     (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND
                      bag_tag3 = l_bag_tag3 AND
                      flight_id = l_flight_id;

                  -- if we get here then we must have found a row in the unknown_bag table
                  IF rtrim(l_bag_group) IS NOT NULL THEN

                     -- BAG_ALREADY_GROUPED
                     p_return_code := pkg_constants.ERROR_BAG_ALREADY_GROUPED;

                  ELSE

                     -- we must have found a row with a blank group_no value - update it
                     UPDATE unknown_bag SET group_no = p_bag_group WHERE
                        (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND
                         bag_tag3 = l_bag_tag3 AND
                         flight_id = l_flight_id;

                     -- Increment the bag group
                     UPDATE bag_group SET no_of_bags = no_of_bags + 1 WHERE
                        group_no = p_bag_group;

                  END IF;

            END;

         END IF;

      END IF;

   END IF;

   BEGIN
      -- Try and find a pax ID if possible
      SELECT b.pax_id, p.flight_id
        INTO l_pax_id, l_flight_id
        FROM bag b, pax p, flight f
       WHERE (b.bag_tag2a = l_bag_tag2a OR b.bag_tag2b = l_bag_tag2b)
         AND b.bag_tag3 = l_bag_tag3
         AND b.pax_id = p.pax_id
         AND f.site_id = l_site_id
         AND p.flight_id = f.flight_id
         AND b.delete_marker = 0
         AND (p.flight_id = p_flight_id OR p_flight_id = '0');

   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         l_pax_id := 0;
         l_flight_id := p_flight_id;
      WHEN TOO_MANY_ROWS THEN
         l_pax_id := 0;
         l_flight_id := p_flight_id;
   END;

   -- Set the local return code to be the value of the error up until this point
   l_log_code := p_return_code;

   IF (p_return_code = v_tag_unknown_no_flt AND config.get_value('ALLOW_BAG_WITH_NOFLT') = 'Y')
   THEN
      l_log_code := 0;
   END IF;

   -- We need to use l_user_id here because the stored proc writes back to the variable
   add_bag_trace_record ( l_user_id,
           p_location,
           l_log_code,    -- error code
           l_bag_tagno,
           l_bag_tag1,
           l_bag_tag2a,
           l_bag_tag2b,
           l_bag_tag3,
           'GROUPED',
           p_scanner_id,
           l_flight_id,
           l_pax_id,
           p_bag_group
      );

   -- Now fetch the number of bags count for the group from the bag_group table
   -- Note: triggers on bag and unknown_bag table will have incremented no_of_bags
   --       in the bag_group table if a bag has been added
   SELECT no_of_bags INTO p_bag_count FROM bag_group WHERE group_no = p_bag_group AND site_id = l_site_id;

   -- Save the transaction
   COMMIT;

   IF (config.get_value('UNKNOWN_BAGS_WARNING') != 'Y' AND p_return_code = v_tag_unknown_no_flt) THEN
      -- If the config value is set then we dont want to display the warning
      -- messages on the scanner. Therefore reset the error value returned
      -- to the scanner to 0 to indicate 'complete success'.
      p_return_code := 0;
   END IF;

END add_bag_to_group;


/******************************************************************************
 *
 * PROCEDURE REMOVE_BAG_FROM_GROUP
 *
 ******************************************************************************/

PROCEDURE remove_bag_from_group
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_bag_group   IN  bag_group.group_no%type,
   p_bag_tag     IN  bag.bag_tag%type,
   p_flight_id   IN  flight.flight_id%type,
   p_location    IN  location.location_code%type,
   p_scanner_id  IN  kit.id%type,
   p_bag_count   OUT number,
   p_return_code OUT number
)
IS
   l_site_id                    agents.site_id%type;
   l_bag_count                  number := 0;
   l_current_group              bag.group_no%type := null;
   l_group                      tagtrak_users.group_name%type;
   l_agent                      tagtrak_users.agent_code%type;
   l_flight_id                  flight.flight_id%type := p_flight_id;
   l_access_check               number := 0;
   l_bag_tag1                   bag.bag_tag1%type;
   l_bag_tag2a                  bag.bag_tag2a%type;
   l_bag_tag2b                  bag.bag_tag2b%type;
   l_bag_tag3                   bag.bag_tag3%type;
   l_bag_tagno                  bag.bag_tag%type;
   l_bag_tag_type               number;
   l_unknown_bag_grouped_flag   number := 0;
   l_bag_grouped_flag           number := 0;
   l_pax_id                     pax.pax_id%type;
   l_user_id                    tagtrak_users.user_id%type := p_user_id;
   l_log_code                   number := 0;

BEGIN

   p_return_code := 0;
   p_bag_count := 0;

   split_tags (p_bag_tag, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3, l_bag_tagno, l_bag_tag_type);

   -- First check that the given group exists

   -- First we need to get the site and agent code of the user
   SELECT a.site_id, t.agent_code INTO l_site_id, l_agent FROM agents a, tagtrak_users t WHERE
      t.user_id = p_user_id AND
      t.agent_code = a.agent_code;

   BEGIN

      SELECT no_of_bags INTO l_bag_count FROM bag_group WHERE
         group_no = p_bag_group AND
         site_id = l_site_id;

   EXCEPTION

      WHEN NO_DATA_FOUND THEN
         -- UNKNOWN_BAG_GROUP
         p_return_code := pkg_constants.ERROR_UNKNOWN_BAG_GROUP;
   END;

   IF p_return_code = 0 THEN

      -- Group exists, now check that the user has access to it

      BEGIN

         SELECT no_of_bags INTO l_bag_count FROM bag_group WHERE
            group_no = p_bag_group AND
            agent_code = l_agent;

      EXCEPTION

         WHEN NO_DATA_FOUND THEN
            -- ACCESS_DENIED
            p_return_code := pkg_constants.ERROR_FLIGHT_ACCESS_DENIED;

      END;

   END IF;

   IF p_return_code = 0 THEN

      l_flight_id := p_flight_id;

      -- attempt to get the flight/check the bag is not a duplicate
      get_flight_for_bag(l_bag_tag2a, l_bag_tag2b, l_bag_tag3, NULL, l_flight_id, l_site_id, p_return_code, TRUE, TRUE);

      -- Doesn't matter if tag is deleted we still need to ungroup it
      IF (p_return_code = DELETED_TAG) THEN
         p_return_code := 0;
      END IF;

      IF (p_return_code = 0 OR p_return_code = v_tag_unknown_no_flt) THEN

         -- We now have a flight for the bag
         -- Check that we have access to the flight

         IF l_flight_id != 0 THEN

            SELECT count(*) INTO l_access_check FROM flight_access WHERE
               flight_id = l_flight_id AND
               agent_code = l_agent;
         ELSE

            -- No flight associated with this bag, so ignore check
            l_access_check := 1;

         END IF;

         IF l_access_check = 0 THEN

            -- ACCESS_DENIED
            p_return_code := ACCESS_DENIED;

         ELSE

            -- Have access to the flight, so check that the
            -- bag is in a group matching the given one
            -- The bag could be in the BAG table, UNKNOWN_BAG table
            -- or both so need to check them separately

            BEGIN

               SELECT group_no into l_current_group FROM bag WHERE
                  (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND
                   bag_tag3 = l_bag_tag3 AND
                   pax_id IN (SELECT DISTINCT pax_id FROM pax WHERE flight_id = l_flight_id) AND
                   group_no IS NOT NULL;

               -- Set a flag to indicate that it is an known bag so
               -- that we know for the update statement
               l_bag_grouped_flag := 1;

            EXCEPTION

               WHEN NO_DATA_FOUND THEN
                  l_bag_grouped_flag := 0;

            END;

            BEGIN
               SELECT group_no INTO l_current_group FROM unknown_bag WHERE
                  (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND
                   bag_tag3 = l_bag_tag3 AND
                   flight_id = l_flight_id AND
                   group_no IS NOT NULL;

               -- Set a flag to indicate that it is an unknown bag so
               -- that we know for the update statement
               l_unknown_bag_grouped_flag := 1;

            EXCEPTION

               WHEN NO_DATA_FOUND THEN
                  l_unknown_bag_grouped_flag := 0;
            END;

            IF (l_bag_grouped_flag = 0 AND l_unknown_bag_grouped_flag = 0) THEN

               -- BAG_NOT_GROUPED
               p_return_code := -20056;

            ELSIF l_current_group != p_bag_group THEN

               -- WRONG_BAG_GROUP
               p_return_code := -20057;

            ELSE

               -- clear the group number against the bag

               IF l_bag_grouped_flag = 1 THEN

                  -- If we get here then we do need to update the bag table,
                  UPDATE bag SET group_no = null WHERE
                     group_no = p_bag_group AND
                     (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND
                     bag_tag3 = l_bag_tag3 AND
                     pax_id IN
                        (SELECT distinct pax_id FROM pax WHERE flight_id = l_flight_id);
               END IF;

               IF l_unknown_bag_grouped_flag = 1 THEN
                  -- If we get here then we need to update the unknown_bag table.
                  UPDATE unknown_bag SET group_no = null WHERE
                     group_no = p_bag_group AND
                     flight_id = l_flight_id AND
                     (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND
                      bag_tag3 = l_bag_tag3;
               END IF;

               -- Only decrement the bag count by 1 even if both tables updated
                  IF (l_bag_grouped_flag = 1 OR l_unknown_bag_grouped_flag = 1) THEN
                     UPDATE bag_group SET no_of_bags = no_of_bags - 1 WHERE
                        group_no = p_bag_group;
                  END IF;

            END IF;

         END IF;

      END IF;

   END IF;

   BEGIN
      -- Try and find a pax ID if possible
      SELECT b.pax_id, p.flight_id
        INTO l_pax_id, l_flight_id
        FROM bag b, pax p, flight f
       WHERE (b.bag_tag2a = l_bag_tag2a OR b.bag_tag2b = l_bag_tag2b)
         AND b.bag_tag3 = l_bag_tag3
         AND b.pax_id = p.pax_id
         AND f.site_id = l_site_id
         AND p.flight_id = f.flight_id
         AND b.delete_marker = 0
         AND (p.flight_id = p_flight_id OR p_flight_id = '0');

   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         l_pax_id := 0;
         l_flight_id := p_flight_id;
      WHEN TOO_MANY_ROWS THEN
         l_pax_id := 0;
         l_flight_id := p_flight_id;
   END;

   -- Set the local return code to be the value of the error up until this point
   l_log_code := p_return_code;

   IF (p_return_code = v_tag_unknown_no_flt AND config.get_value('ALLOW_BAG_WITH_NOFLT') = 'Y')
   THEN
      l_log_code := 0;
   END IF;

   -- We need to user l_user_id here because the stored proc writes back to the variable
   add_bag_trace_record ( l_user_id,
           p_location,
           l_log_code,    -- error code
           l_bag_tagno,
           l_bag_tag1,
           l_bag_tag2a,
           l_bag_tag2b,
           l_bag_tag3,
           'UNGROUPED',
           p_scanner_id,
           l_flight_id,
           l_pax_id,
           p_bag_group
      );

   -- Now fetch the number of bags count for the group from the bag_group table
   -- Note: triggers on bag and unknown_bag table will have decremented no_of_bags
   --       in the bag_group table if a bag has been removed
   SELECT no_of_bags INTO p_bag_count FROM bag_group WHERE group_no = p_bag_group AND site_id = l_site_id;

   -- Save the transaction
   COMMIT;

   IF (config.get_value('UNKNOWN_BAGS_WARNING') != 'Y' AND p_return_code = v_tag_unknown_no_flt) THEN
      -- If the config value is set then we dont want to display the warning
      -- messages on the scanner. Therefore reset the error value returned
      -- to the scanner to 0 to indicate 'complete success'.
      p_return_code := 0;
   END IF;


END remove_bag_from_group;

/******************************************************************************
 *
 * PROCEDURE VALIDATE_EVENT
 *
 ******************************************************************************/

PROCEDURE validate_event
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_event_no    IN  tracking_events.event_no%type,
   p_description OUT tracking_events.description%type,
   p_return_code OUT number
)
IS
   l_group tagtrak_users.group_name%type;
   l_agent tagtrak_users.agent_code%type;
BEGIN

   p_return_code := 0;

   get_group_and_agent(p_user_id, l_group, l_agent);

   BEGIN

      SELECT description INTO p_description FROM tracking_events WHERE
         event_no = p_event_no AND
         agent_code = l_agent AND
         (group_name = l_group OR
          group_name IS NULL);

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

      -- No rows found, so set error code to indicate that event does
      -- not exist/user does not have access to this event

      p_return_code := -651;

   END;

END validate_event;

/******************************************************************************
 *
 * PROCEDURE VALIDATE_STATUS
 *
 ******************************************************************************/

PROCEDURE validate_status
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_status_no   IN  bag_status.status_no%type,
   p_description OUT bag_status.description%type,
   p_return_code OUT number
)
IS
   l_group tagtrak_users.group_name%type;
   l_agent tagtrak_users.agent_code%type;
BEGIN

   p_return_code := 0;

   get_group_and_agent(p_user_id, l_group, l_agent);

   BEGIN

      SELECT description INTO p_description FROM bag_status WHERE
         status_no = p_status_no AND
         agent_code = l_agent AND
         (group_name = l_group OR
          group_name IS NULL);

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

      -- No rows found, so set error code to indicate that event does
      -- not exist/user does not have access to this event

      p_return_code := -651;

   END;

END validate_status;


/******************************************************************************
 *
 * PROCEDURE CHECK_EVENTS_CLEAR
 *
 ******************************************************************************/

PROCEDURE check_events_clear
(
   p_user_id             IN  tagtrak_users.user_id%type,
   p_bag_tag             IN  bag.bag_tag%type,
   p_pax_id              IN  pax.pax_id%type,
   p_flight_id           IN  flight.flight_id%type,
   p_failed_event        OUT tracking_events.event_no%type,
   p_event_description   OUT tracking_events.description%type,
   p_return_code         OUT number,
   p_bag_group           OUT bag_group.group_no%type
)
IS
   l_site_id         agents.site_id%type;
   l_flight_id   flight.flight_id%type;
   l_bag_group   bag_group.group_no%type;
   l_group       tagtrak_users.group_name%type;
   l_agent       tagtrak_users.agent_code%type;
   l_bag_tag1      bag.bag_tag1%type;
   l_bag_tag2a     bag.bag_tag2a%type;
   l_bag_tag2b     bag.bag_tag2b%type;
   l_bag_tag3      bag.bag_tag3%type;
   l_bag_tagno     bag.bag_tag%type;
   l_bag_tag_type  number;
   l_holding_pen   bag_group.group_no%type;
   l_pen_count     NUMBER;
   l_retcode       NUMBER;

BEGIN

  -- Clear the return code
  p_return_code := 0;

  -- Set the local flight ID to the parameter passed in.
  l_flight_id := p_flight_id;

   -- First we need to get the site of the user
  SELECT a.site_id INTO l_site_id FROM agents a, tagtrak_users t WHERE
      t.user_id = p_user_id AND
      t.agent_code = a.agent_code;

  split_tags (p_bag_tag, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3, l_bag_tagno, l_bag_tag_type);

  -- No flight id given, so check that we can uniquely identify this bag
  get_flight_for_bag(l_bag_tag2a, l_bag_tag2b, l_bag_tag3, p_pax_id, l_flight_id, l_site_id, p_return_code, FALSE, FALSE);

  IF p_return_code = 0 THEN

     -- As the procedure is called just before loading a bag, check that the
     -- bag is not still grouped. If it is, the bag cannot yet be loaded
     get_bag_group(l_bag_tag2a, l_bag_tag2b, l_bag_tag3, l_flight_id, l_bag_group);

     IF (l_bag_group IS NOT NULL) THEN

        -- If the bag is in a holding pen, then attempt to remove it before loading
        BEGIN
           SELECT holding_pen INTO l_holding_pen FROM bag_group WHERE group_no = l_bag_group;
           IF (l_holding_pen = 'Y') THEN
              holding_pens.remove_bag_from_holding_pen(p_user_id, l_bag_group, p_bag_tag, l_flight_id,
                                                       '-', 0, l_pen_count, l_retcode);
              IF (l_retcode = 0) THEN
                 l_bag_group := NULL;
              END IF;
           END IF;
        EXCEPTION
           WHEN OTHERS THEN
              l_holding_pen := 'N';
        END;

     END IF;

     IF l_bag_group IS NOT NULL THEN

        -- BAG_GROUPED
        p_bag_group := l_bag_group;
        p_return_code := -20059;

     ELSE

        -- Get group and agent for use in next select
        get_group_and_agent(p_user_id, l_group, l_agent);

        -- The bag is not grouped, so check that all mandatory events are cleared
        -- If there is at least one event not cleared (logged), this select returns
        -- its number and description so that it can be reported to the scanner

        -- the site config entry MANDATORY_EVENTS_SITE_WIDE specifies if mandatory events for all agents
        -- for the site should be checked or just mandatory events for the current agent

        BEGIN

           SELECT event_no, description INTO p_failed_event, p_event_description FROM
              tracking_events WHERE mandatory_flag = 'Y' AND
                 (
                    (agent_code = l_agent)
                    OR
                    (
                       agent_code in (select agent_code from agents where agents.site_id=l_site_id)
                       and
                       'Y' in (select db_value from site_config where db_item='MANDATORY_EVENTS_SITE_WIDE'
                        and site_config.site_id=l_site_id)
                    )
                 )
                 AND
                 event_no NOT IN
                    (SELECT event_no FROM tracking_log WHERE
                     (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND bag_tag3 = l_bag_tag3 AND flight_id = l_flight_id) AND
                 event_no > 0 AND
                 ROWNUM=1;

           -- If here then a row was selected. There there are outstanding
           -- events to be cleared, so return EVENT_NOT_CLEARED
           p_return_code := -20060;

        EXCEPTION

           WHEN NO_DATA_FOUND THEN

              -- All mandatory events for this bag have been cleared
              null; -- return code will be left at 0 meaning no error
        END;

     END IF;

  ELSE

     -- Error occurred when identifing bag. Ignore this error in order
     -- to allow standard bag load error handling
     p_return_code := 0;
  END IF;

END check_events_clear;

/******************************************************************************
 *
 * PROCEDURE TRANSFER_GROUP
 *
 ******************************************************************************/

PROCEDURE transfer_group
(
   p_user_id            IN  tagtrak_users.user_id%type,
   p_source_bag_group   IN  bag_group.group_no%type,
   p_dest_bag_group     IN  bag_group.group_no%type,
   p_location           IN  location.location_code%type,
   p_scanner_id         IN  kit.id%type,
   p_return_code        IN OUT number,
   p_bag_count          OUT number
)
IS
   l_event_count   integer := 0;
   l_no_of_errs    integer := 0;
   l_site_id       site_info.site_id%type := 0;
   l_agent         agents.agent_code%type;
   l_group_name    tagtrak_users.group_name%type;
   l_bag_count     integer := 0;
   l_user_id       tagtrak_users.user_id%type := 0;
   l_bag_tag1      bag.bag_tag1%type;
   l_bag_tag2a     bag.bag_tag2a%type;
   l_bag_tag2b     bag.bag_tag2b%type;
   l_bag_tag3      bag.bag_tag3%type;
   l_bag_tagno     bag.bag_tag%type;
   l_bag_tag_type  number;
   l_ret_code      integer := 0;
   l_uld_count     integer := 0;
   l_temp_bag_count   integer := 0;

CURSOR c_bag_group_contents IS
    SELECT b.bag_tag, p.pax_id, f.flight_id FROM bag b, pax p, flight f WHERE
            b.group_no = p_source_bag_group AND
              p.pax_id = b.pax_id AND
              p.flight_id = f.flight_id AND f.flight_id IN
                    (SELECT flight_id FROM flight WHERE site_id = l_site_id)
    UNION
    SELECT bag_tag, pax_id, flight_id FROM unknown_bag WHERE
           group_no = p_source_bag_group AND
           ((bag_tag2b, bag_tag3) not in (select bag_tag2b, bag_tag3 from bag where group_no = p_source_bag_group)) AND
           user_id IN
               (SELECT tu.user_id
                  FROM tagtrak_users tu, agents a
                 WHERE tu.agent_code = a.agent_code
                   AND a.site_id = l_site_id);


BEGIN

   p_bag_count := 0;

dbms_output.put_line('Start; source group = ' || p_source_bag_group);

   -- Start by triggering any automatic events for bags in the source bag group.
   tracking.auto_events(NULL,
                        NULL,
                        NULL,
                        NULL,
                        NULL,
                        p_user_id,
                        p_location,
                        p_source_bag_group,
                        l_event_count,
                        l_no_of_errs);

dbms_output.put_line('After auto events; Event count = ' || l_event_count || ', No of errors = ' || l_no_of_errs);

   -- First we need to get the site and agent code of the user
   SELECT a.site_id, t.agent_code, t.group_name
     INTO l_site_id, l_agent, l_group_name
     FROM agents a, tagtrak_users t
    WHERE t.user_id = p_user_id
      AND t.agent_code = a.agent_code;

   -- First we need to check whether the group is empty
   BEGIN

      SELECT no_of_bags INTO l_bag_count FROM bag_groups_v1 WHERE
         group_no = p_source_bag_group AND
         site_id = l_site_id;

dbms_output.put_line('ok, count='|| l_bag_count);

      IF l_bag_count = 0 THEN
dbms_output.put_line('Empty group');
         p_return_code := -20063;

      END IF;

   EXCEPTION

      WHEN NO_DATA_FOUND THEN
         -- UNKNOWN_BAG_GROUP
         p_return_code := -20053;
dbms_output.put_line('Unknown bag group');

   END;

   IF p_return_code = 0 THEN

dbms_output.put_line('User check');

      -- Source group exists, check that we are allowed to access it

      IF NOT user_maintenance.user_has_privilege(p_user_id, 'SUPER_USER') THEN

         BEGIN

            SELECT no_of_bags INTO l_bag_count FROM bag_groups_v1 WHERE
               group_no = p_source_bag_group AND
               site_id = l_site_id AND
               agent_code = l_agent;

dbms_output.put_line('ok access check; count = ' || l_bag_count);

         EXCEPTION

            WHEN NO_DATA_FOUND THEN
               -- ACCESS_DENIED
               p_return_code := ACCESS_DENIED;
dbms_output.put_line('Access denied');
         END;

      END IF;

   END IF;

dbms_output.put_line('Here = ' || p_return_code || ', Dest group = ' || p_dest_bag_group);

   IF p_return_code = 0 THEN

dbms_output.put_line('User = ' || p_user_id || ', Dest Group = ' || p_dest_bag_group );
      -- We either want to check that the destination group exists or create it
      create_bag_group(p_user_id, p_dest_bag_group,'N',l_temp_bag_count, l_ret_code);
dbms_output.put_line('Bag count = ' || l_temp_bag_count || ', Ret code = ' || l_ret_code);
      p_return_code := l_ret_code;
   END IF;

   IF p_return_code = 0 AND l_bag_count > 0 THEN

      -- use the local variable because the stored procedure needs to write back the value.
      l_user_id := p_user_id;

      FOR l_this_rec IN c_bag_group_contents LOOP

dbms_output.put_line('Looping; tag = ' || l_this_rec.bag_tag);

         -- Split the tag value into components for entering into the bag trace table
         split_tags (l_this_rec.bag_tag, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3, l_bag_tagno, l_bag_tag_type);

         -- We need to use l_user_id here because the stored proc writes back to the variable
         add_bag_trace_record ( l_user_id,
              p_location,
              l_ret_code,    -- error code
              l_bag_tagno,
              l_bag_tag1,
              l_bag_tag2a,
              l_bag_tag2b,
              l_bag_tag3,
              'XFER_GROUP',
              p_scanner_id,
              l_this_rec.flight_id,
              l_this_rec.pax_id,
              p_dest_bag_group
         );

dbms_output.put_line('Updates');

         -- If we managed to get this far then we must have an actual bag to
         -- update, otherwise we'd hit the exception and update the unknown_bag
         -- table instead.
         UPDATE bag SET group_no = p_dest_bag_group WHERE
            (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND
            bag_tag3 = l_bag_tag3 AND
            group_no = p_source_bag_group AND
              pax_id IN
                 (SELECT pax_id FROM pax WHERE flight_id IN
                    (SELECT flight_id FROM flight WHERE site_id = l_site_id));

         --  Not an actual bag so must be an unknown bag
         UPDATE unknown_bag SET group_no = p_dest_bag_group WHERE
             group_no = p_source_bag_group AND
             (bag_tag2a = l_bag_tag2a OR bag_tag2b = l_bag_tag2b) AND
             bag_tag3 = l_bag_tag3 AND
             user_id IN
               (SELECT tu.user_id
                 FROM tagtrak_users tu, agents a
                 WHERE tu.agent_code = a.agent_code
                 AND a.site_id = l_site_id);


      -- decrement number of bags in source group by 1
      UPDATE bag_group SET no_of_bags = no_of_bags - 1 WHERE
          group_no = p_source_bag_group AND
          site_id = l_site_id AND
          agent_code = l_agent;

      -- increment number of bags in destination group by 1
      UPDATE bag_group SET no_of_bags = no_of_bags + 1 WHERE
          group_no = p_dest_bag_group AND
          site_id = l_site_id AND
          agent_code = l_agent;

      END LOOP;

      p_bag_count := l_bag_count;

      -- Now remove the source bag group. We shouldn't need to force it because
      -- the group should be empty. So if we set force to 'N' we will find out
      -- if any errors occur for whatever reason.
      remove_bag_group(p_user_id, p_source_bag_group, 'N', p_location, p_scanner_id, p_return_code);

   END IF;

   -- Save the transaction
   COMMIT;

END transfer_group;

/******************************************************************************
 *
 * PROCEDURE GET_BAG_GROUP_DETAILS
 *
 ******************************************************************************/

PROCEDURE get_bag_group_details
(
   p_user_id           IN      tagtrak_users.user_id%TYPE,
   p_bag_group         IN      bag_group.group_no%TYPE,
   p_no_bags              OUT  NUMBER,
   p_agent_code           OUT  agents.agent_code%TYPE,
   p_user_group           OUT  user_group.group_name%TYPE,
   p_holding_pen          OUT  bag_group.holding_pen%TYPE,
   p_return_code          OUT  number
) IS

   l_site_id     site_info.site_id%TYPE;

   invalid_site  EXCEPTION;

BEGIN

   -- Initialise
   p_no_bags     := 0;
   p_agent_code  := NULL;
   p_user_group  := NULL;
   p_holding_pen := NULL;
   p_return_code := 0;

   -- Get the Site ID for this user
   l_site_id := multi_site.get_user_siteid(p_user_id);

   -- Confirm we have a valid Site ID
   IF (NVL(l_site_id, 0) <= 0) THEN

      RAISE invalid_site;

   END IF;

   -- Now Fetch the Bag Group information for this site
   SELECT bg.no_of_bags,
          bg.agent_code,
          bg.group_name,
          bg.holding_pen
   INTO   p_no_bags,
          p_agent_code,
          p_user_group,
          p_holding_pen
   FROM   bag_group bg
   WHERE  bg.site_id = l_site_id
     AND  bg.group_no = p_bag_group;

   -- Everything OK
   return;

EXCEPTION

   WHEN invalid_site THEN

      p_return_code := pkg_constants.ERROR_USER_UNKNOWN;
      return;

   WHEN no_data_found THEN

      p_return_code := pkg_constants.ERROR_UNKNOWN_BAG_GROUP;
      return;

END get_bag_group_details;


END tracking;
/

