/*****************************************************************************
*
* Filename     - mws_tracking.sql
*
* Project Name    - UltraTrak Database
*
* Source Code Identifier - UAS/URBRS/SS/10127
*
* Description     - Procedures for use by MWS to process bag tracking information
*
* Language     - Oracle PL/SQL
*
* File History    - For the development history run the Show History report in PVCS
*
* Copyright Ultra Electronics Airport Systems, 2003
*
*****************************************************************************/

PROMPT CREATE OR REPLACE PACKAGE mws_tracking

CREATE OR REPLACE PACKAGE mws_tracking
AS

/******************************************************************************
 *
 * Public Type Definitions
 *
 * Note: Some of the below are defined as VARCHAR2's. This is because the MWS
 * code can't handle the trimming of data, so we don't return CHARs which are
 * space padded.
 *
 ******************************************************************************/

   -- Define the types of record sets (for the MWS)
   TYPE site_id_type               IS
      TABLE OF location.site_id%TYPE                 INDEX BY BINARY_INTEGER;
   TYPE location_code_type         IS
      TABLE OF VARCHAR2(6)                           INDEX BY BINARY_INTEGER;
   TYPE location_description_type  IS
      TABLE OF location.location_description%TYPE    INDEX BY BINARY_INTEGER;
   TYPE group_name_type            IS
      TABLE OF VARCHAR2(15)                          INDEX BY BINARY_INTEGER;
   TYPE loc_group_description_type IS
      TABLE OF location_group.description%TYPE       INDEX BY BINARY_INTEGER;
   TYPE last_user_type             IS
      TABLE OF tagtrak_users.last_user%TYPE          INDEX BY BINARY_INTEGER;
   TYPE archive_flag_type          IS
      TABLE OF tagtrak_users.archive_flag%TYPE       INDEX BY BINARY_INTEGER;
   TYPE location_group_type        IS
      TABLE OF VARCHAR2(2)                           INDEX BY BINARY_INTEGER;
   TYPE st_ev_no_type              IS
      TABLE OF tracking_events.event_no%TYPE         INDEX BY BINARY_INTEGER;
   TYPE description_type           IS
      TABLE OF VARCHAR2(255)                         INDEX BY BINARY_INTEGER;
   TYPE mandatory_flag_type        IS
      TABLE OF tracking_events.mandatory_flag%TYPE   INDEX BY BINARY_INTEGER;
   TYPE type_type                  IS
      TABLE OF VARCHAR2(6)                           INDEX BY BINARY_INTEGER;
   TYPE agent_code_type            IS
      TABLE OF VARCHAR2(5)                           INDEX BY BINARY_INTEGER;
   TYPE location_type              IS
      TABLE OF VARCHAR2(6)                           INDEX BY BINARY_INTEGER;
   TYPE location_desc_type         IS
      TABLE OF VARCHAR2(56)                          INDEX BY BINARY_INTEGER;
   TYPE automatic_flag_type        IS
      TABLE OF tracking_events.automatic_flag%TYPE   INDEX BY BINARY_INTEGER;
   TYPE link_event_type            IS
      TABLE OF tracking_events.link_event%TYPE       INDEX BY BINARY_INTEGER;
   TYPE bag_tag_type               IS
      TABLE OF VARCHAR2(10)                          INDEX BY BINARY_INTEGER;
   TYPE pax_id_type                IS
      TABLE OF pax.pax_id%TYPE                       INDEX BY BINARY_INTEGER;
   TYPE pax_full_inits_type        IS
      TABLE OF VARCHAR2(60)                          INDEX BY BINARY_INTEGER;
   TYPE pax_surname_type           IS
      TABLE OF pax.pax_surname%type                      INDEX BY BINARY_INTEGER;
   TYPE timestamp_type             IS
      TABLE OF tracking_log.timestamp%TYPE           INDEX BY BINARY_INTEGER;
   TYPE loader_type                IS
      TABLE OF VARCHAR2(15)                          INDEX BY BINARY_INTEGER;
   TYPE group_no_type              IS
      TABLE OF tracking_log.group_no%TYPE            INDEX BY BINARY_INTEGER;
   TYPE flight_no_type             IS
      TABLE OF VARCHAR2(8)                           INDEX BY BINARY_INTEGER;
   TYPE prev_airport_type          IS
      TABLE OF flight_info_inbound.prev_airport%TYPE INDEX BY BINARY_INTEGER;
   TYPE flight_destination_type    IS
      TABLE OF VARCHAR2(3)                           INDEX BY BINARY_INTEGER;
   TYPE flight_sch_time_type       IS
      TABLE OF VARCHAR2(4)                           INDEX BY BINARY_INTEGER;
   TYPE ext_flight_sch_time_type   IS
      TABLE OF VARCHAR2(12)                          INDEX BY BINARY_INTEGER;
   TYPE flight_date_type           IS
      TABLE OF VARCHAR2(5)                           INDEX BY BINARY_INTEGER;
   TYPE apron_type                 IS
      TABLE OF VARCHAR2(5)                           INDEX BY BINARY_INTEGER;
   TYPE integer_type               IS
      TABLE OF integer                               INDEX BY BINARY_INTEGER;
   TYPE time_diff_type             IS
      TABLE OF VARCHAR2(20)                          INDEX BY BINARY_INTEGER;

   -- This variable limits the number of rows that each stored procedure will return
   -- by default the MWS will only display 999 rows
   MAX_ROWS integer := 99999;

/******************************************************************************
 *
 * PROCEDURE GET_LOCATIONS
 *
 * Description:
 *
 * This procedure returns the locations for an associated site_id for display
 * on the MWS.
 *
 * Parameters:
 *
 * p_site_id              - site_id to return locations for
 * p_site_id_ret          - returned site_id to return locations for
 * p_location_code        - returned location_code
 * p_location_description - returned location_description
 * p_group_name           - returned group_name
 * p_last_user            - returned last_user
 * p_archive_flag         - returned archive_flag
 *
 ******************************************************************************/

PROCEDURE get_locations
(
   p_site_id              IN  site_info.site_id%TYPE,
   p_site_id_ret          OUT site_id_type,
   p_location_code        OUT location_code_type,
   p_location_description OUT location_description_type,
   p_group_name           OUT group_name_type,
   p_last_user            OUT last_user_type,
   p_archive_flag         OUT archive_flag_type
);

/******************************************************************************
 *
 * PROCEDURE AMEND_LOCATION
 *
 * Description:
 *
 * This procedure amends the location table. If the loginname provided IS NOT
 * a TTADMIN user then the amendment is rejected.
 *
 * Parameters:
 *
 * p_type_of_amendment       - type of amendment to perform I, U, D
 * p_loginname               - logged on users name
 * p_location_code           - location_code
 * p_site_id                 - site_id
 * p_location_description    - location_description
 * p_group_name              - group_name
 * p_last_user               - last_user
 * p_ignore                  - Whether to ignore this location for missing bag 
 *                             processing 
 *
 ******************************************************************************/

PROCEDURE amend_location
(
   p_type_of_amendment        IN char,
   p_loginname                IN tagtrak_users.loginname%TYPE,
   p_location_code            IN location.location_code%TYPE,
   p_site_id                  IN location.site_id%TYPE,
   p_location_description     IN location.location_description%TYPE,
   p_group_name               IN location.group_name%TYPE,
   p_last_user                IN location.last_user%TYPE,
   p_ignore                   IN location.ignore_for_missing_bag%TYPE
);

/******************************************************************************
 *
 * PROCEDURE GET_BAG_STATUS
 *
 * Description:
 *
 * This procedure retrieves rows from the TRACKING_EVENTS and BAG_STATUS tables
 * for display on the MWS. If the loginname provided is a TTADMIN user then all
 * information for all agents for the site is returned otherwise information
 * matching the users current agent_code and group_name is returned.
 *
 * Parameters:
 *
 * p_loginname               - logged on users name
 * p_st_ev_no                - returned status_no and event_no
 * p_description             - returned description of status or event
 * p_mandatory_flag          - returned tracking_events.mandatory_flag
 * p_type                    - returned EVENT or STATUS
 * p_group_name              - returned tracking_events.group_name
 * p_agent_code              - returned tracking_events.agent_code
 * p_location                - returned tracking_events.location
 * p_automatic_flag          - returned tracking_events.automatic_flag
 * p_link_event              - returned tracking_events.link_event
 *
 ******************************************************************************/

PROCEDURE get_bag_status
(
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_st_ev_no                 OUT st_ev_no_type,
   p_description              OUT description_type,
   p_mandatory_flag           OUT mandatory_flag_type,
   p_type                     OUT type_type,
   p_group_name               OUT group_name_type,
   p_agent_code               OUT agent_code_type,
   p_location                 OUT location_type,
   p_automatic_flag           OUT automatic_flag_type,
   p_link_event               OUT link_event_type
);

/******************************************************************************
 *
 * PROCEDURE GET_GROUPS
 *
 * Description:
 *
 * This procedure retrieves group_names from the USER_ROLE table for the provided
 * logged on user. If the provided user is a TTADMIN user then all groups
 * for the users site are returned else just the current users group_name is returned.
 *
 * Parameters:
 *
 * p_loginname               - logged on users name
 * p_group_name              - returned group_name
 *
 ******************************************************************************/

PROCEDURE get_groups
(
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_group_name               OUT group_name_type
);

/******************************************************************************
 *
 * PROCEDURE GET_AGENT_CODES
 *
 * Description:
 *
 * This procedure retrieves agent_codes from the AGENTS table for the site of the
 * logged on user. If the provided user is a TTADMIN user then all agent_codes
 * for the users site are returned else just the current users agent_code is returned.
 *
 * Parameters:
 *
 * p_loginname               - logged on users name
 * p_agent_code              - returned group_name
 *
 ******************************************************************************/

PROCEDURE get_agent_codes
(
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_agent_code               OUT agent_code_type
);

/******************************************************************************
 *
 * PROCEDURE GET_LINK_EVENTS
 *
 * Description:
 *
 * This procedure retrieves link_events from the TRACKING_EVENTS table for the provided
 * user. If the loginname is TTADMIN then all events are returned else just
 * events belonging to the logged on users group and agent are returned.
 *
 * Parameters:
 *
 * p_loginname               - logged on users name
 * p_link_event              - returned link_event
 * p_link_description        - returned link_description
 *
 ******************************************************************************/

PROCEDURE get_link_events
(
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_link_event               OUT link_event_type,
   p_link_description         OUT description_type
);

/******************************************************************************
 *
 * PROCEDURE AMEND_BAG_STATUS
 *
 * Description:
 *
 * This procedure amends either the tracking_events or bag_status table.
 * If the link_event references an event for a different group, agent_code
 * combination then the amendment is rejected.
 *
 * Parameters:
 *
 * p_type_of_amendment       - type of amendment to perform I, U, D
 * p_st_ev_no                - status_no or event_no (ignored if inserting)
 * p_st_ev                   - indicates status_no or event_no (S or E)
 * p_description             - status or event description
 * p_mandatory_flag          - mandatory_flag (ignored if amending a status)
 * p_automatic_flag          - automatic_flag (ignored if amending a status)
 * p_group_name              - group_name
 * p_agent_code              - agent_code
 * p_location                - location (ignored if amending a status)
 * p_link_event              - link_event (ignored if amending a status)
 *
 ******************************************************************************/

PROCEDURE amend_bag_status
(
   p_type_of_amendment        IN char,
   p_st_ev_no                 IN tracking_events.event_no%TYPE,
   p_st_ev                    IN char,
   p_description              IN tracking_events.description%TYPE,
   p_mandatory_flag           IN tracking_events.mandatory_flag%TYPE,
   p_automatic_flag           IN tracking_events.automatic_flag%TYPE,
   p_group_name               IN tracking_events.group_name%TYPE,
   p_agent_code               IN tracking_events.agent_code%TYPE,
   p_location                 IN tracking_events.location%TYPE,
   p_link_event               IN tracking_events.link_event%TYPE
);

/******************************************************************************
 *
 * PROCEDURE GET_BAG_STATUS_TRACK
 *
 * Description:
 *
 * This procedure retrieves information regarding the tracking of bags for a flight
 * as made by tracking status actions.
 *
 * Parameters:
 *
 * p_flight_id               - flight_id of flight to retrieve bag status for
 * p_user_pref               - return bag tags in 'A'lpha or 'N'umeric format
 * p_bag_tag                 - returned bag_tag
 * p_pax_id                  - returned pax_id
 * p_pax_name                - returned pax_name
 * p_date                    - returned date of status
 * p_description             - returned description
 * p_loader                  - returned loader loginname
 *
 ******************************************************************************/

PROCEDURE get_bag_status_track
(
   p_flight_id                IN  flight.flight_id%type,
   p_user_pref                IN  VARCHAR2,
   p_bag_tag                  OUT bag_tag_type,
   p_pax_id                   OUT pax_id_type,
   p_pax_name                 OUT pax_surname_type,
   p_date                     OUT timestamp_type,
   p_description              OUT description_type,
   p_loader                   OUT loader_type
);

/******************************************************************************
 *
 * FUNCTION GET_BAG_STATUS_TRACK_2
 *
 * Description:
 *
 * This function retrieves information regarding the tracking of bags for a flight
 * as made by tracking status actions.
 *
 * Parameters:
 *
 * p_flight_id               - flight_id of flight to retrieve bag status for
 * p_user_pref               - would user prefer bag tags to be returned in 
 *                             'A'lpha or 'N'umeric format
 *
 ******************************************************************************/

FUNCTION get_bag_status_track_2
(p_flight_id  IN  flight.flight_id%TYPE,
 p_user_pref  IN  VARCHAR2)
RETURN SYS_REFCURSOR;


/******************************************************************************
 *
 * PROCEDURE GET_BAG_TRACK
 *
 * Description:
 *
 * This procedure retrieves information regarding the tracking of bags for a flight
 * as made by any tracking status or event. If the provided login name is a TTADMIN
 * user then tracking can be viewed for any tag/flight, otherwise the user is limited
 * to tags/flights that they operate.
 *
 * Parameters:
 *
 * p_flight_id               - flight_id of flight to retrieve bag status for
 * p_bag_tag                 - bag_tag of tag to track
 * p_loginname               - login name of user
 * p_pax_id                  - the PAX ID selected from the combobox on the MWS
 * p_out_pax_id              - returned pax_id
 * p_location                - returned location
 * p_st_ev_no                - returned status or event number
 * p_description             - returned status or event description
 * p_group_no                - returned ULD or group number
 * p_time                    - returned time of trace
 * p_loader                  - returned loader loginname
 * p_time_step               - returned step time
 * p_time_cum                - returned cumulative time
 *
 ******************************************************************************/

PROCEDURE get_bag_track
(
   p_flight_id             IN     flight.flight_id%TYPE,
   p_bag_tag               IN     tracking_log.bag_tag%TYPE,
   p_loginname             IN     tagtrak_users.loginname%TYPE,
   p_pax_id                IN     pax.pax_id%TYPE,
   p_surname               IN     char,
   p_out_pax_id               OUT pax_id_type,
   p_location                 OUT location_desc_type,
   p_st_ev_no                 OUT st_ev_no_type,
   p_description              OUT description_type,
   p_group_no                 OUT group_no_type,
   p_time                     OUT timestamp_type,
   p_loader                   OUT loader_type,
   p_time_step                OUT time_diff_type,
   p_time_cum                 OUT time_diff_type
);

/******************************************************************************
 *
 * FUNCTION GET_BAG_TRACK_2
 *
 * Description:
 *
 * This function retrieves information regarding the tracking of bags for a flight
 * as made by any tracking status or event. It is the same as procedure GET_BAG_TRACK
 * but returns the information in a REF CURSOR.
 *
 * Parameters:
 *
 * p_flight_id               - flight_id of flight to retrieve bag status for
 * p_bag_tag                 - bag_tag of tag to track
 * p_loginname               - login name of user
 *
 ******************************************************************************/

FUNCTION get_bag_track_2
(
   p_flight_id                IN  flight.flight_id%TYPE,
   p_bag_tag                  IN  tracking_log.bag_tag%TYPE,
   p_loginname                IN  tagtrak_users.loginname%TYPE
)
RETURN SYS_REFCURSOR;


/******************************************************************************
 *
 * FUNCTION HELPER_DATE
 *
 * Description:
 *
 * This function converts a string from one of the following formats
 *
 *  DDMON
 *  DD MON
 *  DDMONYY
 *  DDMON YY
 *  DDMONYYYY
 *  DD MONYYYY
 *  DD MON YYYY
 *
 *  to a date. If it fails, it returns sysdate.
 *
 * Parameters:
 *
 * p_date                    - date in a string
 *
 * Returns:
 *
 * date object
 *
 ******************************************************************************/

FUNCTION helper_date
(
   p_date                IN  varchar2
)
   RETURN date;

/******************************************************************************
 *
 * PROCEDURE GET_BAG_TRACK_BY_LOCATION
 *
 * Description:
 *
 * This procedure retrieves information regarding the tracking of bags passing a
 * specified location. If the provided login name is a TTADMIN
 * user then tracking can be viewed for any tag/flight, otherwise the user is limited
 * to viewing tags/flights that they operate.
 *
 * The addition of a non-NULL flight_id can be used to view bags for a specific flight.
 *
 * Parameters:
 *
 * p_location                - location to view bags passing through
 * p_date                    - date to view
 * p_loginname               - login name of user
 * p_flight_id               - optional flight_id
 * p_bag_tag                 - returned bag_tag
 * p_pax_id                  - returned pax_id
 * p_location_out            - returned location
 * p_st_ev_no                - returned status or event number
 * p_description             - returned status or event description
 * p_group_no                - returned ULD or group number
 * p_time                    - returned time of trace
 * p_loader                  - returned loader loginname
 * p_arr_flight              - returned arrival flight
 * p_org                     - returned origin
 * p_dep_flight              - returned departure flight
 * p_des                     - returned destination
 * p_user_pref               - users preferred bag tag format (Alpha or Numeric)
 *
 ******************************************************************************/

PROCEDURE get_bag_track_by_location
(
   p_location                 IN  tracking_log.location%TYPE,
   p_date                     IN  varchar2,
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_flight_id                IN  flight.flight_id%TYPE,
   p_user_pref                IN  VARCHAR2,
   p_bag_tag                  OUT bag_tag_type,
   p_pax_id                   OUT pax_id_type,
   p_location_out             OUT location_type,
   p_st_ev_no                 OUT st_ev_no_type,
   p_description              OUT description_type,
   p_group_no                 OUT group_no_type,
   p_time                     OUT timestamp_type,
   p_loader                   OUT loader_type,
   p_arr_flight               OUT flight_no_type,
   p_org                      OUT prev_airport_type,
   p_dep_flight               OUT flight_no_type,
   p_des                      OUT flight_destination_type
);

/******************************************************************************
 *
 * FUNCTION GET_BAG_TRACK_BY_LOCATION_2
 *
 * Description:
 *
 * This function performs exactly the same function as GET_BAG_TRACK_BY_LOCATION
 * but returns the data via a REF CURSOR as the result of the function.
 *
 * Parameters:
 *
 * p_location                - location to view bags passing through
 * p_date                    - date to view
 * p_loginname               - login name of user
 * p_flight_id               - optional flight_id
 * p_user_pref               - preferred format for bag tags, i.e. alphanumeric
 *                             or numeric.
 ******************************************************************************/

FUNCTION get_bag_track_by_location_2
(
   p_location   IN  tracking_log.location%TYPE,
   p_date       IN  VARCHAR2,
   p_loginname  IN  tagtrak_users.loginname%TYPE,
   p_flight_id  IN  flight.flight_id%TYPE,
   p_user_pref  IN  VARCHAR2
)
RETURN SYS_REFCURSOR;


/******************************************************************************
 *
 * PROCEDURE HELPER_ADDITIONAL_INFO
 *
 * Description:
 *
 * Helper function to get additional information for the
 * get_exceed_track_time stored procedure.
 *
 * Parameters:
 *
 * p_flight_id               - optional flight_id
 * p_bag_tag                 - returned bag_tag
 * p_location                - location to view bags passing through
 * p_pax_id                  - returned pax_id
 * p_group_no                - returned ULD or group number
 * p_loader                  - returned loader loginname
 * p_dep_flight              - returned departure flight
 * p_flight_sch_time         - returned scheduled time
 * p_flight_date             - returned date
 *
 ******************************************************************************/

PROCEDURE helper_additional_info
(
   p_flight_id                IN  flight.flight_id%TYPE,
   p_bag_tag                  IN  tracking_log.bag_tag%TYPE,
   p_location                 IN  tracking_log.location%TYPE,
   p_pax_id                   OUT pax.pax_id%TYPE,
   p_group_no                 OUT tracking_log.group_no%TYPE,
   p_loader                   OUT tagtrak_users.loginname%TYPE,
   p_dep_flight               OUT flight.flight_no%TYPE,
   p_flight_sch_time          OUT flight.flight_sch_time%TYPE,
   p_flight_date              OUT flight.flight_date%TYPE
);

/******************************************************************************
 *
 * PROCEDURE GET_EXCEED_TRACK_TIME
 *
 * Description:
 *
 * This procedure retrieves information regarding the tracking of bags that have
 * exceeded a specified time between two locations. If the provided login name is a TTADMIN
 * user then tracking can be viewed for any tag/flight, otherwise the user is limited
 * to viewing tags/flights that they operate.
 *
 * The addition of a non-NULL flight_id can be used to view bags for a specific flight.
 *
 * Parameters:
 *
 * p_location1               - location1
 * p_location2               - location1
 * p_date                    - flight date to view
 * p_max_time                - maximum time in MINS
 * p_loginname               - login name of user
 * p_flight_id               - optional flight_id
 * p_bag_tag                 - returned bag_tag
 * p_pax_id                  - returned pax_id
 * p_group_no                - returned ULD or group number
 * p_loader                  - returned loader loginname
 * p_dep_flight              - returned departure flight
 * p_flight_sch_time         - returned departure flight time
 * p_flight_date             - returned departure flight date
 * p_time_taken              - returned time taken betweeen locations
 * p_user_pref               - users preferred bag tag format (Alpha or Numeric)
 *
 ******************************************************************************/

PROCEDURE get_exceed_track_time
(
   p_location1                IN  tracking_log.location%TYPE,
   p_location2                IN  tracking_log.location%TYPE,
   p_flight_date              IN  flight.flight_date%TYPE,
   p_max_time                 IN  char,
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_flight_id                IN  flight.flight_id%TYPE,
   p_user_pref                IN  VARCHAR2,
   p_bag_tag                  OUT bag_tag_type,
   p_group_no                 OUT group_no_type,
   p_loader                   OUT loader_type,
   p_dep_flight               OUT flight_no_type,
   p_flight_sch_time          OUT flight_sch_time_type,
   p_flight_date_out          OUT flight_date_type,
   p_time_taken               OUT integer_type,
   p_pax_id                   OUT pax_id_type                                                         -- UAS/FALCP/CA/00571
);

/******************************************************************************
 *
 * FUNCTION GET_EXCEED_TRACK_TIME_2
 *
 * Description:
 *
 * This function retrieves information regarding the tracking of bags that have
 * exceeded a specified time between two locations. If the provided login name 
 * is a TTADMIN user then tracking can be viewed for any tag/flight, otherwise 
 * the user is limited to viewing tags/flights that they operate.
 *
 * The addition of a non-NULL flight_id can be used to view bags for a specific 
 * flight.
 *
 * Parameters:
 *
 * p_location1               - location1
 * p_location2               - location1
 * p_date                    - flight date to view
 * p_max_time                - maximum time in MINS
 * p_loginname               - login name of user
 * p_flight_id               - optional flight_id
 * p_user_pref               - users preferred bag tag format (Alpha or Numeric)
 *
 ******************************************************************************/

FUNCTION get_exceed_track_time_2(p_location1    IN  tracking_log.location%TYPE,
                                 p_location2    IN  tracking_log.location%TYPE,
                                 p_flight_date  IN  flight.flight_date%TYPE,
                                 p_max_time     IN  char,
                                 p_loginname    IN  tagtrak_users.loginname%TYPE,
                                 p_flight_id    IN  flight.flight_id%TYPE,
                                 p_user_pref    IN  VARCHAR2) RETURN SYS_REFCURSOR;


/******************************************************************************
 *
 * FUNCTION HELPER_YEAR
 *
 * Description:
 *
 * This function takes a flight_date such as 05JAN and returns a varchar2
 * containing the flight date and the year.
 * The year is calculated by looking at the current sysdate and comparing it
 * against the flight date month.
 * If the current sysdate is JAN and the flight date is DEC then the year for
 * the flight is assumed to be this year minus 1
 * If the current sysdate is DEC and the flight date is JAN then the year for
 * the flight is assumed to be this year plus 1
 * Else the year is the same year as sysdate
 *
 * Parameters:
 *
 * p_date                    - flight date in a string DDMONHH24MI
 *
 * Returns:
 *
 * date object with the corresponding year
 *
 ******************************************************************************/
FUNCTION helper_year
(
   p_date                IN  varchar2
)
   RETURN date;

/******************************************************************************
 *
 * PROCEDURE HELPER_CRITICAL_ADD_INFO
 *
 * This procedure selects the additional information required for the
 * get_critical_track stored procedure.
 *
 * Parameters:
 *
 * p_flight_id
 * p_pax_id
 * p_bag_tag
 * p_timed_at
 *
 * Returns:
 *
 * p_location
 * p_st_ev_no
 * p_description
 * p_group_no
 * p_loader
 * p_dep_flight
 * p_flight_sch_time
 * p_stand
 *
 ******************************************************************************/

PROCEDURE helper_critical_add_info
(
   p_flight_id                IN  flight.flight_id%TYPE,
   p_pax_id                   IN  pax.pax_id%TYPE,
   p_bag_tag                  IN  tracking_log.bag_tag%TYPE,
   p_timed_at                 IN  tracking_log.timestamp%TYPE,
   p_location                 OUT tracking_log.location%TYPE,
   p_st_ev_no                 OUT tracking_log.event_no%TYPE,
   p_description              OUT tracking_events.description%TYPE,
   p_group_no                 OUT tracking_log.group_no%TYPE,
   p_loader                   OUT tagtrak_users.loginname%TYPE,
   p_dep_flight               OUT flight.flight_no%TYPE,
   p_flight_sch_time          OUT flight.flight_sch_time%TYPE,
   p_stand                    OUT flight_info.apron%TYPE
);

/******************************************************************************
 *
 * PROCEDURE GET_CRITICAL_TRACK
 *
 * Description:
 *
 * This procedure retrieves information regarding the tracking of bags that have
 * exceeded a specified time between the last track time of a bag and the departure
 * time of it's associated flight. If the provided login name is a TTADMIN
 * user then tracking can be viewed for any tag/flight, otherwise the user is limited
 * to viewing tags/flights that they operate.
 *
 * The addition of a non-NULL flight_id can be used to view bags for a specific flight.
 *
 * Parameters:
 *
 * p_terminal                - terminal
 * p_max_time                - maximum time
 * p_loginname               - login name of user
 * p_flight_id               - optional flight_id
 * p_bag_tag                 - returned bag_tag
 * p_pax_id                  - returned pax_id
 * p_location                - returned location
 * p_st_ev_no                - returned status or event number
 * p_description             - returned status or event description
 * p_group_no                - returned ULD or group number
 * p_time                    - returned time of trace
 * p_loader                  - returned loader loginname
 * p_dep_flight              - returned departure flight
 * p_flight_sch_time         - returned departure time
 * p_delta_time              - returned delta time
 * p_stand                   - returned apron
 * p_user_pref               - users preferred bag tag format (Alpha or Numeric)
 *
 ******************************************************************************/

PROCEDURE get_critical_track
(
   p_terminal                 IN  flight_info.terminal%TYPE,
   p_max_time                 IN  char,
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_flight_id                IN  flight.flight_id%TYPE,
   p_user_pref                IN  VARCHAR2,
   p_bag_tag                  OUT bag_tag_type,
   p_pax_id                   OUT pax_id_type,
   p_location                 OUT location_type,
   p_st_ev_no                 OUT st_ev_no_type,
   p_description              OUT description_type,
   p_group_no                 OUT group_no_type,
   p_time                     OUT timestamp_type,
   p_loader                   OUT loader_type,
   p_dep_flight               OUT flight_no_type,
   p_flight_sch_time          OUT ext_flight_sch_time_type,
   p_delta_time               OUT integer_type,
   p_stand                    OUT apron_type

);

/******************************************************************************
 *
 * FUNCTION GET_CRITICAL_TRACK_2
 *
 * Description:
 *
 * This function retrieves information regarding the tracking of bags that have
 * exceeded a specified time between the last track time of a bag and the departure
 * time of its associated flight. If the provided login name is a TTADMIN
 * user then tracking can be viewed for any tag/flight, otherwise the user is limited
 * to viewing tags/flights that they operate.
 *
 * The addition of a non-NULL flight_id can be used to view bags for a specific flight.
 *
 * Parameters:
 *
 * p_terminal                - terminal
 * p_max_time                - maximum time
 * p_loginname               - login name of user
 * p_flight_id               - optional flight_id
 * p_user_pref               - users preferred bag tag format (Alpha or Numeric)
 *
 ******************************************************************************/
FUNCTION get_critical_track_2(p_terminal   IN flight_info.terminal%TYPE,
                              p_max_time   IN char,
                              p_loginname  IN tagtrak_users.loginname%TYPE,
                              p_flight_id  IN flight.flight_id%TYPE,
                              p_user_pref  IN VARCHAR2) RETURN SYS_REFCURSOR;


/******************************************************************************
 *
 * PROCEDURE GET_MISSED_TRACK
 *
 * Description:
 *
 * This procedure retrieves information regarding the tracking of bags that have
 * missed their corresponding flights.
 * If the provided login name is a TTADMIN user then tracking can be viewed for
 * any tag/flight, otherwise the user is limited to viewing tags/flights that
 * they operate.
 *
 * The addition of a non-NULL flight_id can be used to view bags for a specific flight.
 *
 * Parameters:
 *
 * p_loginname               - login name of user
 * p_bag_tag                 - returned bag_tag
 * p_inc_unpos_ulds          - 1 to inc unpos ULDS else 0
 * p_user_pref               - users preferred bag tag format (Alpha or Numeric)
 * p_flight_id               - optional flight_id
 * p_pax_id                  - returned pax_id
 * p_location                - returned location
 * p_st_ev_no                - returned status or event number
 * p_description             - returned status or event description
 * p_group_no                - returned ULD or group number
 * p_time                    - returned time of trace
 * p_loader                  - returned loader loginname
 * p_dep_flight              - returned departure flight
 * p_flight_sch_time         - returned departure time
 *
 ******************************************************************************/

PROCEDURE get_missed_track
(
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_flight_id                IN  flight.flight_id%TYPE,
   p_inc_unpos_ulds           IN  INTEGER,
   p_user_pref                IN  VARCHAR2,                                                        -- UAS/FALCP/CA/00571
   p_bag_tag                  OUT bag_tag_type,
   p_pax_id                   OUT pax_id_type,
   p_location                 OUT location_type,
   p_st_ev_no                 OUT st_ev_no_type,
   p_description              OUT description_type,
   p_group_no                 OUT group_no_type,
   p_time                     OUT timestamp_type,
   p_loader                   OUT loader_type,
   p_dep_flight               OUT flight_no_type,
   p_flight_sch_time          OUT ext_flight_sch_time_type
);

/******************************************************************************
 *
 * FUNCTION GET_MISSED_TRACK_2
 *
 * Description:
 *
 * This function retrieves information regarding the tracking of bags that have
 * missed their corresponding flights.
 * If the provided login name is a TTADMIN user then tracking can be viewed for
 * any tag/flight, otherwise the user is limited to viewing tags/flights that
 * they operate.
 *
 * The addition of a non-NULL p_flight_id can be used to view bags for a specific
 * flight.
 *
 * Parameters:
 *
 * p_loginname               - login name of user
 * p_bag_tag                 - returned bag_tag
 * p_inc_unpos_ulds          - 1 to inc unpos ULDS else 0
 * p_user_pref               - users preferred bag tag format (Alpha or Numeric)
 *
 ******************************************************************************/

FUNCTION get_missed_track_2(p_loginname       IN tagtrak_users.loginname%TYPE,
                            p_flight_id       IN flight.flight_id%TYPE,
                            p_inc_unpos_ulds  IN INTEGER,
                            p_user_pref       IN VARCHAR2) RETURN SYS_REFCURSOR;

/******************************************************************************
 *
 * FUNCTION LAST_SEEN_LOCATION
 *
 * Description:
 *
 * This function returns the location a bag was last seen at by finding the most 
 * recent record out of the bsm tables, bag_trace and tracking_log for that bag
 * and getting the location from that.
 * 
 * Parameters:
 *
 * p_pax_id               - pax_id of pax you wish to search for.
 * p_bag_tag              - bag_tag of bag you wish search for.
 *
 ******************************************************************************/
 
FUNCTION last_seen_location(p_pax_id  pax.pax_id%TYPE,
                            p_bag_tag bag.bag_tag%TYPE)
RETURN location.location_code%TYPE;

/******************************************************************************
 *
 * FUNCTION LAST_SEEN_TIME
 *
 * Description:
 *
 * This function returns the time a bag was last seen by finding the most 
 * recent record out of the bsm tables, bag_trace and tracking_log for that bag.
 * 
 * Parameters:
 *
 * p_pax_id               - pax_id of pax you wish to search for.
 * p_bag_tag              - bag_tag of bag you wish search for.
 *
 ******************************************************************************/
 
FUNCTION last_seen_time(p_pax_id  pax.pax_id%TYPE,
                        p_bag_tag bag.bag_tag%TYPE)
RETURN DATE;

END mws_tracking;
/

PROMPT CREATE OR REPLACE PACKAGE BODY mws_tracking

CREATE OR REPLACE PACKAGE BODY mws_tracking
AS

/******************************************************************************
 *
 * PROCEDURE GET_LOCATIONS
 *
 ******************************************************************************/

PROCEDURE get_locations
(
   p_site_id              IN  site_info.site_id%TYPE,
   p_site_id_ret          OUT site_id_type,
   p_location_code        OUT location_code_type,
   p_location_description OUT location_description_type,
   p_group_name           OUT group_name_type,
   p_last_user            OUT last_user_type,
   p_archive_flag         OUT archive_flag_type
)
IS
   -- this cursor selects all the locations for this site_id
   CURSOR c_locations IS
      SELECT
         site_id, location_code, location_description, group_name,
         last_user, archive_flag
      FROM
         location
      WHERE
         site_id = p_site_id
      ORDER BY location_code;

   -- loop counter
   l_row_counter     BINARY_INTEGER;

BEGIN

   -- Initialise the row counter
   l_row_counter := 0;

   -- loop through the locations for the site
   FOR this_row IN c_locations LOOP

      -- increment the row count
      l_row_counter := l_row_counter + 1;

      -- add the data to the return arrays
      p_site_id_ret(l_row_counter) := TRIM(this_row.site_id);
      p_location_code(l_row_counter) := TRIM(this_row.location_code);
      p_location_description(l_row_counter) :=
         TRIM(this_row.location_description);
      p_group_name(l_row_counter) := TRIM(this_row.group_name);
      p_last_user(l_row_counter) := TRIM(this_row.last_user);
      p_archive_flag(l_row_counter) := TRIM(this_row.archive_flag);

      -- ensure we do not return too much data to the MWS
      EXIT WHEN l_row_counter = MAX_ROWS;

   END LOOP; -- of looping through cursor c_locations

END get_locations;

/******************************************************************************
 *
 * PROCEDURE AMEND_LOCATION
 *
 ******************************************************************************/

PROCEDURE amend_location
(
   p_type_of_amendment        IN char,
   p_loginname                IN tagtrak_users.loginname%TYPE,
   p_location_code            IN location.location_code%TYPE,
   p_site_id                  IN location.site_id%TYPE,
   p_location_description     IN location.location_description%TYPE,
   p_group_name               IN location.group_name%TYPE,
   p_last_user                IN location.last_user%TYPE,
   p_ignore                   IN location.ignore_for_missing_bag%TYPE
)
IS
BEGIN

   -- Only a TTADMIN user or super user can perform the amendment
   IF instr(p_loginname, 'TTADMIN') != 1 and
      not User_Maintenance.User_Has_Privilege(p_loginname, 'SUPER_USER')
   THEN
      raise_application_error(-20001,
         'Sorry, you must be a TTADMIN user or super user to add a location');
   END IF;

   -- what type of amendment are they performing?
   IF p_type_of_amendment = 'I' -- insert
   THEN

      INSERT INTO location (site_id, location_code, location_description,
                            group_name, last_user, ignore_for_missing_bag) values
                           (p_site_id, p_location_code,
                            p_location_description, p_group_name, p_last_user, p_ignore);

   ELSIF p_type_of_amendment = 'U' -- update
   THEN

      UPDATE location SET location_description = p_location_description,
                          group_name = p_group_name,
                          last_user = p_last_user,
                          ignore_for_missing_bag = p_ignore
         WHERE location_code = p_location_code AND site_id = p_site_id;

   ELSIF p_type_of_amendment = 'D' -- DELETE
   THEN

      DELETE FROM location WHERE location_code = p_location_code
                                 AND site_id = p_site_id;

   END IF;

END amend_location;

/******************************************************************************
 *
 * PROCEDURE GET_BAG_STATUS
 *
 ******************************************************************************/

PROCEDURE get_bag_status
(
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_st_ev_no                 OUT st_ev_no_type,
   p_description              OUT description_type,
   p_mandatory_flag           OUT mandatory_flag_type,
   p_type                     OUT type_type,
   p_group_name               OUT group_name_type,
   p_agent_code               OUT agent_code_type,
   p_location                 OUT location_type,
   p_automatic_flag           OUT automatic_flag_type,
   p_link_event               OUT link_event_type
)
IS
   -- this cursor selects all the configured events and status for this site
   CURSOR c_bag_status_ttadmin IS
      SELECT
         event_no st_ev_no, description, mandatory_flag, 'EVENT' type,
         group_name, agent_code, location, automatic_flag, link_event
      FROM
         tracking_events
      WHERE agent_code in (SELECT agent_code FROM agents WHERE
                           site_id = (SELECT site_id FROM agents WHERE
                           agent_code = (SELECT agent_code FROM tagtrak_users
                           WHERE loginname = p_loginname)))
      UNION
      SELECT status_no st_ev_no, description, '' mandatory_flag, 'STATUS' type,
             group_name, agent_code, '' location, '' automatic_flag,
             to_number(NULL) link_event
      FROM
         BAG_STATUS
      WHERE agent_code in (SELECT agent_code FROM agents WHERE site_id =
                          (SELECT site_id FROM agents WHERE agent_code =
                          (SELECT agent_code FROM tagtrak_users WHERE
                          loginname = p_loginname)))
      ORDER BY st_ev_no;

   -- this cursor selects all the configured events and status
   -- for this users group_name/agent_code combination
   CURSOR c_bag_status IS
      SELECT
         event_no st_ev_no, description, mandatory_flag, 'EVENT' type,
         group_name, agent_code, location, automatic_flag, link_event
      FROM
         tracking_events
      WHERE agent_code = (SELECT agent_code FROM tagtrak_users WHERE
                         loginname = p_loginname) AND
            group_name = (SELECT group_name FROM tagtrak_users WHERE
                         loginname = p_loginname)
      UNION
      SELECT status_no st_ev_no, description, '' mandatory_flag, 'STATUS' type,
             group_name, agent_code, '' location, '' automatic_flag,
             to_number(NULL) link_event
      FROM
         BAG_STATUS
      WHERE agent_code = (SELECT agent_code FROM tagtrak_users
                         WHERE loginname = p_loginname) AND
            group_name = (SELECT group_name FROM tagtrak_users
                         WHERE loginname = p_loginname)
      ORDER BY st_ev_no;

   -- loop counter
   l_row_counter     BINARY_INTEGER;

BEGIN

   -- Initialise the row counter
   l_row_counter := 0;

   -- if they are a TTADMIN user then they can see everything else
   -- they are filtered on agent and group_name
   IF instr(p_loginname, 'TTADMIN') = 1
   THEN

      -- loop through the bag status and events
      FOR this_row IN c_bag_status_ttadmin LOOP

         -- increment the row count
         l_row_counter := l_row_counter + 1;

         -- add the data to the return arrays
         p_st_ev_no(l_row_counter) := TRIM(this_row.st_ev_no);
         p_description (l_row_counter) := TRIM(this_row.description);
         p_mandatory_flag(l_row_counter) := TRIM(this_row.mandatory_flag);
         p_type(l_row_counter) := TRIM(this_row.type);
         p_group_name(l_row_counter) := TRIM(this_row.group_name);
         p_agent_code(l_row_counter) := TRIM(this_row.agent_code);
         p_location(l_row_counter) := TRIM(this_row.location);
         p_automatic_flag(l_row_counter) := TRIM(this_row.automatic_flag);
         p_link_event(l_row_counter) := TRIM(this_row.link_event);

         -- ensure we do not return too much data to the MWS
         EXIT WHEN l_row_counter = MAX_ROWS;

      END LOOP; -- end of looping through cursor c_bag_status_ttadmin

   ELSE -- they are not a TTADMIN user

      -- loop through the bag status and events
      FOR this_row IN c_bag_status LOOP

         -- increment the row count
         l_row_counter := l_row_counter + 1;

         -- add the data to the return arrays
         p_st_ev_no(l_row_counter) := TRIM(this_row.st_ev_no);
         p_description (l_row_counter) := TRIM(this_row.description);
         p_mandatory_flag(l_row_counter) := TRIM(this_row.mandatory_flag);
         p_type(l_row_counter) := TRIM(this_row.type);
         p_group_name(l_row_counter) := TRIM(this_row.group_name);
         p_agent_code(l_row_counter) := TRIM(this_row.agent_code);
         p_location(l_row_counter) := TRIM(this_row.location);
         p_automatic_flag(l_row_counter) := TRIM(this_row.automatic_flag);
         p_link_event(l_row_counter) := TRIM(this_row.link_event);

         -- ensure we do not return too much data to the MWS
         EXIT WHEN l_row_counter = MAX_ROWS;

      END LOOP; -- end of looping through cursor c_bag_status

   END IF; -- end of are they/not a TTADMIN user

END get_bag_status;

/******************************************************************************
 *
 * PROCEDURE GET_GROUPS
 *
 ******************************************************************************/

PROCEDURE get_groups
(
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_group_name               OUT group_name_type
)
IS

   -- this cursor selects all the group_names for the site
   CURSOR c_groups_ttadmin IS
      SELECT
         user_role_name group_name
      FROM
         user_role
      WHERE agent_code in (SELECT agent_code FROM agents WHERE
                          site_id = (SELECT site_id FROM agents WHERE
                          agent_code = (SELECT agent_code FROM tagtrak_users WHERE
                          loginname = p_loginname)))
      ORDER BY group_name;

   -- this cursor selects the logged on users group_name
   CURSOR c_groups IS
      SELECT
         group_name
      FROM
         tagtrak_users WHERE loginname = p_loginname
      ORDER BY group_name;

   -- loop counter
   l_row_counter     BINARY_INTEGER;

BEGIN

   -- Initialise the row counter
   l_row_counter := 0;

   IF instr(p_loginname, 'TTADMIN') = 1
   THEN

      -- loop through the group names
      FOR this_row IN c_groups_ttadmin LOOP

         -- increment the row count
         l_row_counter := l_row_counter + 1;

         -- add the data to the return arrays
         p_group_name(l_row_counter) := TRIM(this_row.group_name);

         -- ensure we do not return too much data to the MWS
         EXIT WHEN l_row_counter = MAX_ROWS;

      END LOOP; -- end of looping through cursor c_groups_ttadmin

   ELSE -- they are not a TTADMIN user

      -- loop through the group names
      FOR this_row IN c_groups LOOP

         -- increment the row count
         l_row_counter := l_row_counter + 1;

         -- add the data to the return arrays
         p_group_name(l_row_counter) := TRIM(this_row.group_name);

         -- ensure we do not return too much data to the MWS
         EXIT WHEN l_row_counter = MAX_ROWS;

      END LOOP; -- end of looping through cursor c_groups

   END IF; -- end of are they/not a TTADMIN user

END get_groups;

/******************************************************************************
 *
 * PROCEDURE GET_AGENT_CODES
 *
 ******************************************************************************/

PROCEDURE get_agent_codes
(
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_agent_code               OUT agent_code_type
)
IS

   -- this cursor selects all the agent_codes for this site
   CURSOR c_agents_ttadmin IS
      SELECT
         agent_code
      FROM
         agents
      WHERE site_id = (SELECT site_id FROM agents WHERE agent_code =
                      (SELECT agent_code FROM tagtrak_users WHERE
                      loginname = p_loginname))
      ORDER BY agent_code;

   -- this cursor selects the logged on users agent_code
   CURSOR c_agents IS
      SELECT
         agent_code
      FROM
         tagtrak_users WHERE loginname = p_loginname
      ORDER BY agent_code;

   -- loop counter
   l_row_counter     BINARY_INTEGER;

BEGIN

   -- Initialise the row counter
   l_row_counter := 0;

   IF instr(p_loginname, 'TTADMIN') = 1
   THEN

      -- loop through the agents
      FOR this_row IN c_agents_ttadmin LOOP

         -- increment the row count
         l_row_counter := l_row_counter + 1;

         -- add the data to the return arrays
         p_agent_code(l_row_counter) := TRIM(this_row.agent_code);

         -- ensure we do not return too much data to the MWS
         EXIT WHEN l_row_counter = MAX_ROWS;

      END LOOP; -- end of looping through c_agents_ttadmin

   ELSE -- they are not a TTADMIN user

      -- loop through the agents
      FOR this_row IN c_agents LOOP

         -- increment the row count
         l_row_counter := l_row_counter + 1;

         -- add the data to the return arrays
         p_agent_code(l_row_counter) := TRIM(this_row.agent_code);

         -- ensure we do not return too much data to the MWS
         EXIT WHEN l_row_counter = MAX_ROWS;

      END LOOP; -- end of looping through cursor c_agents

   END IF; -- end of are they/not a TTADMIN user

END get_agent_codes;

/******************************************************************************
 *
 * PROCEDURE GET_LINK_EVENTS
 *
 ******************************************************************************/

PROCEDURE get_link_events
(
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_link_event               OUT link_event_type,
   p_link_description         OUT description_type
)
IS
   -- this cursor selects all the events that exist at this site
   CURSOR c_link_events_ttadmin IS
      SELECT
         event_no, description
      FROM
         tracking_events
      WHERE agent_code in (SELECT agent_code FROM agents WHERE site_id =
                          (SELECT site_id FROM agents WHERE agent_code =
                          (SELECT agent_code FROM tagtrak_users WHERE
                          loginname = p_loginname)))
      ORDER BY event_no;

   -- this cursor selects only the events for this users group_name/agent_code
   CURSOR c_link_events IS
      SELECT
         event_no, description
      FROM
         tracking_events WHERE (agent_code, group_name) in
         (SELECT agent_code, group_name FROM tagtrak_users
         WHERE loginname = p_loginname)
      ORDER BY agent_code;

   -- loop counter
   l_row_counter     BINARY_INTEGER;

BEGIN

   -- Initialise the row counter
   l_row_counter := 0;

   IF instr(p_loginname, 'TTADMIN') = 1
   THEN

      -- loop through the link_events
      FOR this_row IN c_link_events_ttadmin LOOP

         -- increment the row count
         l_row_counter := l_row_counter + 1;

         -- add the data to the return arrays
         p_link_event(l_row_counter) := TRIM(this_row.event_no);
         p_link_description(l_row_counter) := TRIM(this_row.description);

         -- ensure we do not return too much data to the MWS
         EXIT WHEN l_row_counter = MAX_ROWS;

      END LOOP; -- end of looping through cursor c_link_events_ttadmin

   ELSE -- they are not a TTADMIN user

      -- loop through the link_events
      FOR this_row IN c_link_events LOOP

         -- increment the row count
         l_row_counter := l_row_counter + 1;

         -- add the data to the return arrays
         p_link_event(l_row_counter) := TRIM(this_row.event_no);

         -- ensure we do not return too much data to the MWS
         EXIT WHEN l_row_counter = MAX_ROWS;

      END LOOP; -- end of looping through cursor c_link_events

   END IF; -- end of are they/not a TTADMIN user

END get_link_events;

/******************************************************************************
 *
 * PROCEDURE AMEND_BAG_STATUS
 *
 ******************************************************************************/

PROCEDURE amend_bag_status
(
   p_type_of_amendment        IN char,
   p_st_ev_no                 IN tracking_events.event_no%TYPE,
   p_st_ev                    IN char,
   p_description              IN tracking_events.description%TYPE,
   p_mandatory_flag           IN tracking_events.mandatory_flag%TYPE,
   p_automatic_flag           IN tracking_events.automatic_flag%TYPE,
   p_group_name               IN tracking_events.group_name%TYPE,
   p_agent_code               IN tracking_events.agent_code%TYPE,
   p_location                 IN tracking_events.location%TYPE,
   p_link_event               IN tracking_events.link_event%TYPE
)
IS
   l_group_name tracking_events.group_name%TYPE;
   l_agent_code tracking_events.agent_code%TYPE;
BEGIN

   -- ensure that the linked_event belongs to the same group_name/agent_code combination
   IF p_link_event IS NOT NULL
   THEN

      BEGIN
           
         SELECT group_name, agent_code INTO l_group_name, l_agent_code
         FROM tracking_events WHERE event_no = p_link_event;
      
         IF l_group_name != p_group_name or l_agent_code != p_agent_code THEN
            
            raise_application_error(-20002,
            'Sorry, the specified linked event does not belong '||
            'to the same group_name/agent_code combination as this event');
            
         END IF;
            
      EXCEPTION WHEN no_data_found THEN
         raise_application_error (-20000, 'link event does not exist');
           
      END;
     
   END IF;

   -- what type of amendment are they performing?
   IF p_type_of_amendment = 'I' -- insert
   THEN
      IF p_st_ev = 'E'
      THEN
         INSERT INTO tracking_events (event_no, description, mandatory_flag,
                                      automatic_flag, group_name,
                                      agent_code, location, link_event) values
                                     (p_st_ev_no, p_description, p_mandatory_flag,
                                      p_automatic_flag, p_group_name,
                                      p_agent_code, p_location, p_link_event);
      ELSE
         INSERT INTO bag_status (status_no, description, group_name,
                                 agent_code) values
                                (p_st_ev_no, p_description, p_group_name,
                                 p_agent_code);
      END IF;

   ELSIF p_type_of_amendment = 'U' -- update
   THEN

      IF p_st_ev = 'E'
      THEN
         UPDATE tracking_events SET description = p_description,
                                    mandatory_flag = p_mandatory_flag,
                                    automatic_flag = p_automatic_flag,
                                    group_name = p_group_name,
                                    agent_code = p_agent_code,
                                    location = p_location,
                                    link_event = p_link_event
            WHERE event_no = p_st_ev_no;

      ELSE
         UPDATE bag_status SET description = p_description,
                               group_name = p_group_name,
                               agent_code = p_agent_code
            WHERE status_no = p_st_ev_no;
      END IF;

   ELSIF p_type_of_amendment = 'D' -- DELETE
   THEN

      IF p_st_ev = 'E'
      THEN
         DELETE FROM tracking_events WHERE event_no = p_st_ev_no;
      ELSE
         DELETE FROM bag_status WHERE status_no = p_st_ev_no;
      END IF;

   END IF;

END amend_bag_status;

/******************************************************************************
 *
 * PROCEDURE GET_BAG_STATUS_TRACK
 *
 ******************************************************************************/

PROCEDURE get_bag_status_track
(
   p_flight_id                IN  flight.flight_id%type,
   p_user_pref                IN  VARCHAR2,
   p_bag_tag                  OUT bag_tag_type,
   p_pax_id                   OUT pax_id_type,
   p_pax_name                 OUT pax_surname_type,
   p_date                     OUT timestamp_type,
   p_description              OUT description_type,
   p_loader                   OUT loader_type
)
IS
   -- this cursor select status from the tracking_log table
   -- for a particular flight_id
   -- it expands pax_id, event description etc if possible,
   -- else they are returned as NULL

   CURSOR c_bag_status_track IS
      SELECT
         get_user_bag_tag_no_exceptions(tracking_log.bag_tag, p_user_pref) t_bag_tag, bag.pax_id pax_id,
         pax.pax_surname pax_name, tracking_log.timestamp timestamp,
         bag_status.description description, tagtrak_users.loginname loader
      FROM
         tracking_log, bag, pax, bag_status, tagtrak_users
      WHERE
            tracking_log.flight_id = p_flight_id AND
            tracking_log.status_no IS NOT NULL AND tracking_log.status_no > -1
         AND
            substr(bag.bag_tag(+),2,9) = substr(tracking_log.bag_tag,2,9)
         AND
            pax.pax_id(+) = bag.pax_id
         AND
            pax.flight_id = p_flight_id
         AND
            bag_status.status_no(+) = tracking_log.status_no
         AND
            tagtrak_users.user_id(+) = tracking_log.user_id
   UNION
      SELECT
         get_user_bag_tag_no_exceptions(tracking_log.bag_tag, p_user_pref) t_bag_tag, tracking_log.pax_id pax_id,
         'UNKNOWN', tracking_log.timestamp timestamp,
         bag_status.description description, tagtrak_users.loginname loader
      FROM
         tracking_log, bag_status, tagtrak_users
      WHERE
            tracking_log.flight_id = p_flight_id AND
            tracking_log.status_no IS NOT NULL AND tracking_log.status_no > -1
         AND
            tracking_log.pax_id = 0
         AND
            bag_status.status_no(+) = tracking_log.status_no
         AND
            tagtrak_users.user_id(+) = tracking_log.user_id
      ORDER BY timestamp, t_bag_tag;

   -- loop counter
   l_row_counter     BINARY_INTEGER;

BEGIN

   -- Initialise the row counter
   l_row_counter := 0;

   -- loop through the tracking entries
   FOR this_row IN c_bag_status_track LOOP

      -- increment the row count
      l_row_counter := l_row_counter + 1;

      -- add the data to the return arrays
      p_bag_tag(l_row_counter) := TRIM(this_row.t_bag_tag);
      p_pax_id(l_row_counter) := TRIM(this_row.pax_id);
      p_pax_name(l_row_counter) := TRIM(this_row.pax_name);
      p_date(l_row_counter) := TRIM(this_row.timestamp);
      p_description(l_row_counter) := TRIM(this_row.description);
      p_loader(l_row_counter) := TRIM(this_row.loader);

      -- ensure we do not return too much data to the MWS
      EXIT WHEN l_row_counter = MAX_ROWS;

   END LOOP; -- end of looping through cursor c_bag_status_track

END get_bag_status_track;

/******************************************************************************
 *
 * FUNCTION GET_BAG_STATUS_TRACK_2
 *
 ******************************************************************************/

FUNCTION get_bag_status_track_2
(p_flight_id  IN  flight.flight_id%TYPE,
 p_user_pref  IN  VARCHAR2)
RETURN SYS_REFCURSOR IS

  l_cursor SYS_REFCURSOR;

BEGIN

  -- this cursor select status from the tracking_log table
  -- for a particular flight_id
  -- it expands pax_id, event description etc if possible,
  -- else they are returned as NULL
  OPEN l_cursor FOR
  SELECT get_user_bag_tag_no_exceptions(tracking_log.bag_tag, p_user_pref) t_bag_tag,
         bag.pax_id pax_id, trim(pax.pax_surname) pax_name,
         tracking_log.timestamp timestamp, trim(bag_status.description) description,
         trim(tagtrak_users.loginname) loader
  FROM tracking_log, bag, pax, bag_status, tagtrak_users
  WHERE tracking_log.flight_id = p_flight_id
  AND tracking_log.status_no IS NOT NULL
  AND tracking_log.status_no > -1
  AND substr(bag.bag_tag(+),2,9) = substr(tracking_log.bag_tag,2,9)
  AND pax.pax_id(+) = bag.pax_id
  AND pax.flight_id = p_flight_id
  AND bag_status.status_no(+) = tracking_log.status_no
  AND tagtrak_users.user_id(+) = tracking_log.user_id
  UNION
  SELECT trim(get_user_bag_tag_no_exceptions(tracking_log.bag_tag, p_user_pref)) t_bag_tag,
         tracking_log.pax_id pax_id, 'UNKNOWN',
         tracking_log.timestamp timestamp, trim(bag_status.description) description,
         trim(tagtrak_users.loginname) loader
  FROM tracking_log, bag_status, tagtrak_users
  WHERE tracking_log.flight_id = p_flight_id
  AND tracking_log.status_no IS NOT NULL
  AND tracking_log.status_no > -1
  AND tracking_log.pax_id = 0
  AND bag_status.status_no(+) = tracking_log.status_no
  AND tagtrak_users.user_id(+) = tracking_log.user_id
  ORDER BY timestamp, t_bag_tag;

  RETURN l_cursor;

END get_bag_status_track_2;


/******************************************************************************
 *
 * PROCEDURE 
 *
 ******************************************************************************/

PROCEDURE get_bag_track
(
   p_flight_id             IN     flight.flight_id%TYPE,
   p_bag_tag               IN     tracking_log.bag_tag%TYPE,
   p_loginname             IN     tagtrak_users.loginname%TYPE,
   p_pax_id                IN     pax.pax_id%TYPE,
   p_surname               IN     char,
   p_out_pax_id               OUT pax_id_type,
   p_location                 OUT location_desc_type,
   p_st_ev_no                 OUT st_ev_no_type,
   p_description              OUT description_type,
   p_group_no                 OUT group_no_type,
   p_time                     OUT timestamp_type,
   p_loader                   OUT loader_type,
   p_time_step                OUT time_diff_type,
   p_time_cum                 OUT time_diff_type
)
IS

   -- varible to hold unknown bag bpm config value
   l_unknown_bag_bpm          db_config.db_value%type;

   -- get tracking information for a particular bag_tag/flight_id combo
   -- (do not check flight access rights)
   CURSOR c_bag_track_ttadmin (n_bag_tag tracking_log.bag_tag%TYPE) IS                             -- UAS/FALCP/CA/00571
      WITH flight_id_list AS (SELECT p_flight_id flight_id FROM DUAL
                              UNION
                              SELECT flight_id FROM flight WHERE op_flight_id = p_flight_id)
      SELECT
         pax_id, 0 st_ev_no, flight_id, location, location_description, description, group_no, timestamp,
         tagtrak_users.loginname loader
      FROM
         tracking_log_and_bag_trace_v, tagtrak_users
      WHERE
            substr(tracking_log_and_bag_trace_v.bag_tag,2,9) = substr(n_bag_tag,2,9)                                                   -- UAS/FALCP/CA/00571
         AND
            tagtrak_users.user_id(+) = tracking_log_and_bag_trace_v.user_id
         AND
            (flight_id IN (SELECT flight_id FROM flight_id_list)
             OR
            (flight_id = 0 AND description like 'BPM%' AND l_unknown_bag_bpm = 'Y'))
         AND
            (pax_id = p_pax_id OR pax_id = 0)
         AND
            ((description like 'BSM%' AND description like p_surname) OR description not like 'BSM%')
      ORDER BY timestamp,nvl(tagtrak_users.loginname,'     ');

   -- get tracking information for a particular bag_tag/flight_id combo
   -- and check flight access rights
   CURSOR c_bag_track (n_bag_tag tracking_log.bag_tag%TYPE) IS                                     -- UAS/FALCP/CA/00571
      WITH flight_id_list AS (SELECT p_flight_id flight_id FROM DUAL
                              UNION
                              SELECT flight_id FROM flight WHERE op_flight_id = p_flight_id)
      SELECT
         pax_id, 0 st_ev_no, flight_id, location, location_description, description, group_no, timestamp,
         tagtrak_users.loginname loader
      FROM
         tracking_log_and_bag_trace_v, tagtrak_users
      WHERE
            substr(tracking_log_and_bag_trace_v.bag_tag,2,9) = substr(n_bag_tag,2,9)                                                   -- UAS/FALCP/CA/00571
         AND
            tagtrak_users.user_id(+) = tracking_log_and_bag_trace_v.user_id
         AND 
            (flight_id IN (SELECT flight_id FROM flight_id_list)
             OR
            (flight_id = 0 AND description like 'BPM%' AND l_unknown_bag_bpm = 'Y'))
         AND
            (pax_id = p_pax_id OR pax_id = 0)
         AND
            ((description like 'BSM%' AND description like p_surname) OR description not like 'BSM%')
         AND
            ( 
               (flight_id = 0) 
                  OR
               (flight_id = (SELECT flight_id FROM flight_access 
                  WHERE tracking_log_and_bag_trace_v.flight_id = flight_id AND agent_code = 
               (SELECT agent_code FROM tagtrak_users WHERE 
                  loginname = p_loginname))
               )
            )
      ORDER BY timestamp,nvl(tagtrak_users.loginname,'     ');

   -- loop counter
   l_row_counter     BINARY_INTEGER;

   -- this holds the first events timestamp
   l_first_timestamp  date;

   -- this holds the previous events timestamp
   l_last_timestamp  date;

   -- this holds the numeric form of the given bag tag
   v_bag_tag         tracking_log.bag_tag%TYPE;                                                    -- UAS/FALCP/CA/00571

BEGIN

   select db_value into l_unknown_bag_bpm from live.db_config where db_item='UNKNOWN_BAG_BPM';

   -- Convert the given bag tag to numeric format
   v_bag_tag := get_numeric_bag_tag(p_bag_tag);                                                    -- UAS/FALCP/CA/00571

   -- Initialise the row counter
   l_row_counter := 0;

   IF instr(p_loginname, 'TTADMIN') = 1
   THEN

      -- loop through the tracking entries
      FOR this_row IN c_bag_track_ttadmin(v_bag_tag) LOOP                                          -- UAS/FALCP/CA/00571

         -- increment the row count
         l_row_counter := l_row_counter + 1;

         -- is this the first row ? remember the timestamp
         IF l_row_counter = 1
         THEN
            l_first_timestamp := this_row.timestamp;
            l_last_timestamp := l_first_timestamp;
         END IF;

         -- add the data to the return arrays
         p_out_pax_id(l_row_counter) := TRIM(this_row.pax_id);
         p_location(l_row_counter) := TRIM(this_row.location) || ' - ' || TRIM(this_row.location_description);
         p_st_ev_no(l_row_counter) := TRIM(this_row.st_ev_no);

         IF substrb(TRIM(this_row.description), 1, 3) = 'BPM' AND TRIM(this_row.flight_id) = 0 AND l_unknown_bag_bpm = 'Y'
         THEN
            p_description(l_row_counter) := '*' || TRIM(this_row.description);
         ELSE
            p_description(l_row_counter) := TRIM(this_row.description);
         END IF;

         p_group_no(l_row_counter) := TRIM(this_row.group_no);
         p_time(l_row_counter) := TRIM(this_row.timestamp);
         p_loader(l_row_counter) := TRIM(this_row.loader);
         -- the following rows display the time difference in "DDD Days HH24:MI:SS" format
         p_time_step(l_row_counter) :=
            to_number(to_char(to_date('01JAN0001 00:00:00','DDMONYYYY HH24:MI:SS') +
                              (this_row.timestamp - l_last_timestamp),
                              'DDD')) - 1 || ' Days ' ||
            to_char(to_date('01JAN0001 00:00:00','DDMONYYYY HH24:MI:SS') + (this_row.timestamp - l_last_timestamp),
                    'HH24:MI:SS');
         p_time_cum(l_row_counter) :=
            to_number(to_char(to_date('01JAN0001 00:00:00','DDMONYYYY HH24:MI:SS') +
                              (this_row.timestamp - l_first_timestamp)
                              , 'DDD')) - 1 || ' Days ' ||
            to_char(to_date('01JAN0001 00:00:00','DDMONYYYY HH24:MI:SS') + (this_row.timestamp - l_first_timestamp),
                    'HH24:MI:SS');

         -- remember this rows timestamp to calc the step time
         l_last_timestamp := this_row.timestamp;

         -- ensure we do not return too much data to the MWS
         EXIT WHEN l_row_counter = MAX_ROWS;

      END LOOP; -- end of looping through c_bag_track_ttadmin

   ELSE -- they are not a TTADMIN user

      -- loop through the tracking entries
      FOR this_row IN c_bag_track(v_bag_tag) LOOP                                                  -- UAS/FALCP/CA/00571

         -- increment the row count
         l_row_counter := l_row_counter + 1;

         -- is this the first row ? remember the timestamp
         IF l_row_counter = 1
         THEN
            l_first_timestamp := this_row.timestamp;
            l_last_timestamp := l_first_timestamp;
         END IF;

         -- add the data to the return arrays
         p_out_pax_id(l_row_counter) := TRIM(this_row.pax_id);
         p_location(l_row_counter) := TRIM(this_row.location) || ' - ' || TRIM(this_row.location_description);
         p_st_ev_no(l_row_counter) := TRIM(this_row.st_ev_no);

         IF substrb(TRIM(this_row.description), 1, 3) = 'BPM' AND TRIM(this_row.flight_id) = 0  AND l_unknown_bag_bpm = 'Y'
         THEN
            p_description(l_row_counter) := '*' || TRIM(this_row.description);
         ELSE
            p_description(l_row_counter) := TRIM(this_row.description);
         END IF;

         p_group_no(l_row_counter) := TRIM(this_row.group_no);
         p_time(l_row_counter) := TRIM(this_row.timestamp);
         p_loader(l_row_counter) := TRIM(this_row.loader);
         -- the following rows display the time difference in "DDD Days HH24:MI:SS" format
         p_time_step(l_row_counter) :=
            to_number(to_char(to_date('01JAN0001 00:00:00','DDMONYYYY HH24:MI:SS') +
                              (this_row.timestamp - l_last_timestamp),
                              'DDD')) - 1 || ' Days ' ||
            to_char(to_date('01JAN0001 00:00:00','DDMONYYYY HH24:MI:SS') + (this_row.timestamp - l_last_timestamp),
                    ' HH24:MI:SS');
         p_time_cum(l_row_counter) :=
            to_number(to_char(to_date('01JAN0001 00:00:00','DDMONYYYY HH24:MI:SS') +
                              (this_row.timestamp - l_first_timestamp)
                              , 'DDD')) - 1 || ' Days ' ||
            to_char(to_date('01JAN0001 00:00:00','DDMONYYYY HH24:MI:SS') + (this_row.timestamp - l_first_timestamp),
                    ' HH24:MI:SS');

         -- remember this rows timestamp to calc the step time
         l_last_timestamp := this_row.timestamp;

         -- ensure we do not return too much data to the MWS
         EXIT WHEN l_row_counter = MAX_ROWS;

      END LOOP; -- end of looping through c_bag_track

   END IF; -- end of are they/not a TTADMIN user

END get_bag_track;

/******************************************************************************
 *
 * FUNCTION GET_BAG_TRACK_2 
 *
 ******************************************************************************/

FUNCTION get_bag_track_2
(
   p_flight_id                IN  flight.flight_id%TYPE,
   p_bag_tag                  IN  tracking_log.bag_tag%TYPE,
   p_loginname                IN  tagtrak_users.loginname%TYPE
)
RETURN SYS_REFCURSOR
IS
  -- this holds the numeric form of the given bag tag
  l_bag_tag  tracking_log.bag_tag%TYPE;                                -- UAS/FALCP/CA/00571
  l_cursor   SYS_REFCURSOR;

BEGIN

  -- Convert the given bag tag to numeric format
  l_bag_tag := get_numeric_bag_tag(p_bag_tag);                         -- UAS/FALCP/CA/00571

  IF instr(p_loginname, 'TTADMIN') = 1 THEN

    OPEN l_cursor FOR
    WITH flight_id_list AS (SELECT p_flight_id flight_id FROM DUAL
                            UNION
                            SELECT flight_id FROM flight WHERE op_flight_id = p_flight_id)
    SELECT TRIM(tlbt.pax_id) pax_id,
           TRIM(tlbt.location) || ' - ' || TRIM(tlbt.location_description) location,
           0 st_ev_no,
           TRIM(tlbt.description) description,
           TRIM(tlbt.group_no) group_no,
           tlbt.TIMESTAMP,
           TRIM(tu.loginname) loader,
           TRIM(tu.agent_code) agent
    FROM tracking_log_and_bag_trace_v tlbt, tagtrak_users tu
    WHERE substr(tlbt.bag_tag,2,9) = substr(l_bag_tag,2,9)
    AND tu.user_id(+) = tlbt.user_id
    AND tlbt.flight_id IN (SELECT flight_id FROM flight_id_list)
    ORDER BY timestamp, nvl(tu.loginname,'     ');

  ELSE -- they are not a TTADMIN user

    OPEN l_cursor FOR
    WITH flight_id_list AS (SELECT p_flight_id flight_id FROM DUAL
                            UNION
                            SELECT flight_id FROM flight WHERE op_flight_id = p_flight_id)
    SELECT TRIM(tlbt.pax_id) pax_id,
           TRIM(tlbt.location) || ' - ' || TRIM(tlbt.location_description) location,
           0 st_ev_no,
           TRIM(tlbt.description) description,
           TRIM(tlbt.group_no) group_no,
           tlbt.TIMESTAMP,
           TRIM(tu.loginname) loader,
           TRIM(tu.agent_code) agent
    FROM tracking_log_and_bag_trace_v tlbt, tagtrak_users tu
    WHERE substr(tlbt.bag_tag,2,9) = substr(l_bag_tag,2,9)
    AND tu.user_id(+) = tlbt.user_id
    AND tlbt.flight_id IN (SELECT flight_id FROM flight_id_list)
    AND (tlbt.flight_id = 0
         OR EXISTS (SELECT flight_id
                    FROM flight_access fa, tagtrak_users tu2
                    WHERE tlbt.flight_id = fa.flight_id
                    AND fa.agent_code = tu2.agent_code
                    AND tu2.loginname = p_loginname))
    ORDER BY timestamp, nvl(tu.loginname,'     ');

  END IF; -- end of are they/not a TTADMIN user

  RETURN l_cursor;

END get_bag_track_2;


/******************************************************************************
 *
 * FUNCTION HELPER_DATE
 *
 ******************************************************************************/

FUNCTION helper_date
(
   p_date                IN  varchar2
)
   RETURN date
IS
BEGIN
    -- this function converts a string into a date
    -- it supports DDMON
    --           , DD MON
    --           , DDMONYY
    --           , DDMON YY
    --           , DDMONYYYY
    --           , DD MONYYYY
    --           , DD MON YYYY
    -- If no valid date format is entered then today is returned

    IF LENGTH(p_date) = 5 -- DDMON
    THEN
       RETURN(TO_DATE(p_date,'DDMON'));
    ELSIF LENGTH(p_date) = 6 -- DD MON
    THEN
       RETURN(TO_DATE(p_date,'DD MON'));
    ELSIF LENGTH(p_date) = 7 -- DDMONYY
    THEN
       RETURN(TO_DATE(p_date,'DDMONYY'));
    ELSIF LENGTH(p_date) = 8 -- DDMON YY
    THEN
       RETURN(TO_DATE(p_date,'DDMON YY'));
    ELSIF LENGTH(p_date) = 9 -- DDMONYYYY
    THEN
       RETURN(TO_DATE(p_date,'DDMONYYYY'));
    ELSIF LENGTH(p_date) = 10 -- DD MONYYYY
    THEN
       RETURN(TO_DATE(p_date,'DD MONYYYY'));
    ELSIF LENGTH(p_date) = 11 -- DD MON YYYY
    THEN
       RETURN(TO_DATE(p_date,'DD MON YYYY'));
    ELSE
       RETURN(sysdate); -- simply return todays date
    END IF;

END helper_date;

/******************************************************************************
 *
 * PROCEDURE GET_BAG_TRACK_BY_LOCATION
 *
 ******************************************************************************/

PROCEDURE get_bag_track_by_location
(
   p_location              IN     tracking_log.location%TYPE,
   p_date                  IN     varchar2,
   p_loginname             IN     tagtrak_users.loginname%TYPE,
   p_flight_id             IN     flight.flight_id%TYPE,
   p_user_pref             IN     VARCHAR2,                           -- UAS/FALCP/CA/00571
   p_bag_tag                  OUT bag_tag_type,
   p_pax_id                   OUT pax_id_type,
   p_location_out             OUT location_type,
   p_st_ev_no                 OUT st_ev_no_type,
   p_description              OUT description_type,
   p_group_no                 OUT group_no_type,
   p_time                     OUT timestamp_type,
   p_loader                   OUT loader_type,
   p_arr_flight               OUT flight_no_type,
   p_org                      OUT prev_airport_type,
   p_dep_flight               OUT flight_no_type,
   p_des                      OUT flight_destination_type
)
IS
   -- get tracking information for a particular location and date
   -- (do not check flight access rights)
   -- this cursor does not take the flight_id INTO account
   CURSOR c1_bag_track_loc_ttadmin IS
      SELECT
         get_user_bag_tag_no_exceptions(tb.bag_tag, p_user_pref) t_bag_tag, -- UAS/FALCP/CA/00571
         tb.pax_id pax_id,
         tb.location location,
         0 st_ev_no, tb.description description,
         tb.group_no group_no, tb.timestamp timestamp,
         tagtrak_users.loginname loader, pax.inbound_flight_no arr_flight,
         pax.inbound_boarding_point org, flight.flight_no dep_flight,
         flight_dests.flight_destination des
      FROM
         tagtrak_users, pax, flight, flight_dests,
           ( SELECT bt.bag_tag bag_tag,
                    bt.pax_id pax_id,
                    bt.flight_id_scan flight_id,
                    bt.location_code location,
                    decode(bt.error_id,
                           0,
                           bt.trace_action,
                           substr('ERROR: ' ||
                                  TRIM(bt.trace_action) || ' - ' ||
                                  TRIM(error.error_description),
                                  1,
                                  60)) description,
                    bt.uld_no group_no,
                    decode(TRIM(db_config.db_value),
                           'Y',
                           bt.action_time,
                           bt.bag_trace_time) timestamp,
                    bt.user_id user_id
               FROM bag_trace bt, error, db_config
              WHERE bt.error_id = error.error_id(+)
                AND db_config.db_item = 'ALLOW_BAG_WITH_NOFLT'
                AND bt.location_code = p_location
                AND ((TRIM(db_config.db_value) = 'Y' 
                      AND to_char(helper_date(p_date),'YYYYMMDD')
                          = to_char(bt.action_time,'YYYYMMDD')
                      ) 
                     OR
                     (TRIM(db_config.db_value) = 'N' 
                      AND to_char(helper_date(p_date),'YYYYMMDD')
                          = to_char(bt.bag_trace_time,'YYYYMMDD')
                     )
                    )
                AND (bt.pax_id =
                    (SELECT MAX(pax_id)
                        FROM bag_trace bt1
                       WHERE bt1.location_code = bt.location_code
                         AND ((TRIM(db_config.db_value) = 'Y' 
                               AND to_char(helper_date(p_date),'YYYYMMDD')
                                   = to_char(bt1.action_time,'YYYYMMDD')
                               ) 
                              OR
                              (TRIM(db_config.db_value) = 'N' 
                               AND to_char(helper_date(p_date),'YYYYMMDD')
                                   = to_char(bt1.bag_trace_time,'YYYYMMDD')
                              )
                             )
                         AND bt1.bag_tag = bt.bag_tag
                         AND ((TRIM(db_config.db_value) = 'Y' 
                               AND bt.action_time = bt1.action_time
                               ) 
                              OR
                              (TRIM(db_config.db_value) = 'N' 
                               AND bt.bag_trace_time = bt1.bag_trace_time
                               )
                             )
                         AND bt.error_id = bt1.error_id
                         AND bt.trace_action = bt1.trace_action
                         AND nvl(bt1.uld_no, '-') =
                             nvl(bt.uld_no, '-')))
             UNION ALL
             SELECT tl.bag_tag bag_tag, -- UAS/FALCP/CA/00571
                    tl.pax_id pax_id,
                    tl.flight_id flight_id,
                    tl.location location, -- UAS/FALCP/CA/00571
                    decode(tl.status_no,
                           NULL,
                           tracking_events.description,
                           bag_status.description) description,
                    tl.group_no group_no,
                    tl.timestamp timestamp,
                    tl.user_id user_id
               FROM tracking_log tl, tracking_events, bag_status
              WHERE
                   (tl.status_no IS NOT NULL OR
                   (tl.event_no IS NOT NULL AND tl.event_no > -1))
                AND tl.event_no = tracking_events.event_no(+)
                AND tl.status_no = bag_status.status_no(+)
                AND tl.location = p_location
                AND to_char(helper_date(p_date), 'YYYYMMDD') =
                    to_char(tl.timestamp, 'YYYYMMDD')
           ) tb
      WHERE
            tagtrak_users.user_id(+) = tb.user_id
         AND
            pax.pax_id(+) = tb.pax_id
         AND
            flight.flight_id(+) = tb.flight_id
         AND
            flight_dests.flight_id(+) = tb.flight_id
         AND
            flight_dests.dest_order(+) = 1
         AND
       (
               (
                  flight.site_id = (SELECT site_id FROM agents WHERE
                  agent_code = (SELECT agent_code FROM tagtrak_users
                  WHERE loginname = p_loginname))
          )
          OR
          (
                  tb.flight_id = 0 AND
                  tb.user_id IN
                    (SELECT t2.user_id
                    FROM tagtrak_users t1, tagtrak_users t2,
                         agents a1, agents a2
                    WHERE t1.loginname = p_loginname AND
                          t1.agent_code = a1.agent_code AND
                          a1.site_id = a2.site_id AND
                          a2.agent_code = t2.agent_code)

          )
       )
      ORDER BY timestamp, t_bag_tag;

   -- get tracking information for a particular location and date
   -- (this cursor checks flight access rights)
   -- this cursor does not take the flight_id INTO account
   CURSOR c1_bag_track_loc IS
      SELECT
         get_user_bag_tag_no_exceptions(tb.bag_tag, p_user_pref) t_bag_tag,                                     -- UAS/FALCP/CA/00571
         tb.pax_id pax_id, tb.location location,                                        -- UAS/FALCP/CA/00571
         0 st_ev_no, tb.description description,
         tb.group_no group_no, tb.timestamp timestamp, 
         tagtrak_users.loginname loader, pax.inbound_flight_no arr_flight, 
         pax.inbound_boarding_point org, flight.flight_no dep_flight, 
         flight_dests.flight_destination des
      FROM
         tagtrak_users, 
         pax, flight, flight_dests,
           ( SELECT bt.bag_tag bag_tag,
                    bt.pax_id pax_id,
                    bt.flight_id_scan flight_id,
                    bt.location_code location,
                    decode(bt.error_id,
                           0,
                           bt.trace_action,
                           substr('ERROR: ' ||
                                  TRIM(bt.trace_action) || ' - ' ||
                                  TRIM(error.error_description),
                                  1,
                                  60)) description,
                    bt.uld_no group_no,
                    decode(TRIM(db_config.db_value),
                           'Y',
                           bt.action_time,
                           bt.bag_trace_time) timestamp,
                    bt.user_id user_id
               FROM bag_trace bt, error, db_config
              WHERE bt.error_id = error.error_id(+)
                AND db_config.db_item = 'ALLOW_BAG_WITH_NOFLT'
                AND bt.location_code = p_location
                AND ((TRIM(db_config.db_value) = 'Y' 
                      AND to_char(helper_date(p_date),'YYYYMMDD')
                          = to_char(bt.action_time,'YYYYMMDD')
                      ) 
                     OR
                     (TRIM(db_config.db_value) = 'N' 
                      AND to_char(helper_date(p_date),'YYYYMMDD')
                          = to_char(bt.bag_trace_time,'YYYYMMDD')
                     )
                    )
                AND (bt.pax_id =
                    (SELECT MAX(pax_id)
                        FROM bag_trace bt1
                       WHERE bt1.location_code = bt.location_code
                         AND ((TRIM(db_config.db_value) = 'Y' 
                               AND to_char(helper_date(p_date),'YYYYMMDD')
                                   = to_char(bt1.action_time,'YYYYMMDD')
                               ) 
                              OR
                              (TRIM(db_config.db_value) = 'N' 
                               AND to_char(helper_date(p_date),'YYYYMMDD')
                                   = to_char(bt1.bag_trace_time,'YYYYMMDD')
                              )
                             )
                         AND bt1.bag_tag = bt.bag_tag
                         AND ((TRIM(db_config.db_value) = 'Y' 
                               AND bt.action_time = bt1.action_time
                               ) 
                              OR
                              (TRIM(db_config.db_value) = 'N' 
                               AND bt.bag_trace_time = bt1.bag_trace_time
                               )
                             )
                         AND bt.error_id = bt1.error_id
                         AND bt.trace_action = bt1.trace_action
                         AND nvl(bt1.uld_no, '-') =
                             nvl(bt.uld_no, '-')))
             UNION ALL
             SELECT tl.bag_tag bag_tag, -- UAS/FALCP/CA/00571
                    tl.pax_id pax_id,
                    tl.flight_id flight_id,
                    tl.location location, -- UAS/FALCP/CA/00571
                    decode(tl.status_no,
                           NULL,
                           tracking_events.description,
                           bag_status.description) description,
                    tl.group_no group_no,
                    tl.timestamp timestamp,
                    tl.user_id user_id
               FROM tracking_log tl, tracking_events, bag_status
              WHERE
                   (tl.status_no IS NOT NULL OR
                   (tl.event_no IS NOT NULL AND tl.event_no > -1))
                AND tl.event_no = tracking_events.event_no(+)
                AND tl.status_no = bag_status.status_no(+)
                AND tl.location = p_location
                AND to_char(helper_date(p_date), 'YYYYMMDD') =
                   to_char(tl.timestamp, 'YYYYMMDD')
           ) tb
      WHERE
            tagtrak_users.user_id(+) = tb.user_id
         AND
            pax.pax_id(+) = tb.pax_id
         AND
            flight.flight_id(+) = tb.flight_id
         AND
            flight_dests.flight_id(+) = tb.flight_id
         AND
            flight_dests.dest_order(+) = 1
         AND
            (
               (
                  tb.flight_id = (SELECT flight_id FROM flight_access
                   WHERE tb.flight_id = flight_id AND agent_code =
                   (SELECT agent_code FROM tagtrak_users
                    WHERE loginname = p_loginname))
               )
               OR
               (
                  tb.flight_id = 0 AND
                  tb.user_id IN
                    (SELECT t2.user_id
                    FROM tagtrak_users t1, tagtrak_users t2,
                         agents a1, agents a2
                    WHERE t1.loginname = p_loginname AND
                          t1.agent_code = a1.agent_code AND
                          a1.site_id = a2.site_id AND
                          a2.agent_code = t2.agent_code)
               )
            )
      ORDER BY timestamp, t_bag_tag;

   -- get tracking information for a particular location
   -- and date (do not check flight access rights)
   -- this cursor DOES take the flight_id INTO account
   CURSOR c2_bag_track_loc_ttadmin IS
      WITH flight_id_list AS (SELECT p_flight_id flight_id FROM DUAL
                              UNION
                              SELECT flight_id FROM flight WHERE op_flight_id = p_flight_id)
      SELECT
         get_user_bag_tag_no_exceptions(tb.bag_tag, p_user_pref) t_bag_tag, tb.pax_id pax_id,                  -- UAS/FALCP/CA/00571
         tb.location location, 0 st_ev_no,
         tb.description description,
         tb.group_no group_no, tb.timestamp timestamp,
         tagtrak_users.loginname loader, pax.inbound_flight_no arr_flight,
         pax.inbound_boarding_point org, flight.flight_no dep_flight,
         flight_dests.flight_destination des
      FROM
         tagtrak_users,
         pax, flight, flight_dests,
           (SELECT bt.bag_tag bag_tag,
                   bt.pax_id pax_id,
                   bt.flight_id_scan flight_id,
                   bt.location_code location,
                   decode(bt.error_id,
                          0,
                          bt.trace_action,
                          substr('ERROR: ' ||
                                 TRIM(bt.trace_action) || ' - ' ||
                                 TRIM(error.error_description),
                                 1,
                                 60)) description,
                   bt.uld_no group_no,
                   decode(TRIM(db_config.db_value),
                          'Y',
                          bt.action_time,
                          bt.bag_trace_time) timestamp,
                   bt.user_id user_id
              FROM bag_trace bt, error, db_config
             WHERE bt.flight_id_scan IN (SELECT flight_id FROM flight_id_list)
               AND bt.error_id = error.error_id(+)
               AND db_config.db_item = 'ALLOW_BAG_WITH_NOFLT'
               AND bt.location_code = p_location
                AND ((TRIM(db_config.db_value) = 'Y' 
                      AND to_char(helper_date(p_date),'YYYYMMDD')
                          = to_char(bt.action_time,'YYYYMMDD')
                      ) 
                     OR
                     (TRIM(db_config.db_value) = 'N' 
                      AND to_char(helper_date(p_date),'YYYYMMDD')
                          = to_char(bt.bag_trace_time,'YYYYMMDD')
                     )
                    )
            UNION ALL
            SELECT tl.bag_tag bag_tag, -- UAS/FALCP/CA/00571
                   tl.pax_id pax_id,
                   tl.flight_id flight_id,
                   tl.location location, -- UAS/FALCP/CA/00571
                   decode(tl.status_no,
                          NULL,
                          tracking_events.description,
                          bag_status.description) description,
                   tl.group_no group_no,
                   tl.timestamp timestamp,
                   tl.user_id user_id
              FROM tracking_log tl, tracking_events, bag_status
             WHERE tl.flight_id IN (SELECT flight_id FROM flight_id_list)
               AND (tl.status_no IS NOT NULL OR
                   (tl.event_no IS NOT NULL AND tl.event_no > -1))
               AND tl.event_no = tracking_events.event_no(+)
               AND tl.status_no = bag_status.status_no(+)
               AND tl.location = p_location
               AND to_char(helper_date(p_date), 'YYYYMMDD') =
                   to_char(tl.timestamp, 'YYYYMMDD')
            ) tb
      WHERE
            tagtrak_users.user_id(+) = tb.user_id
         AND
            pax.pax_id(+) = tb.pax_id
         AND
            flight.flight_id(+) = tb.flight_id
         AND
            flight_dests.flight_id(+) = tb.flight_id
         AND
            flight_dests.dest_order(+) = 1
         AND
            (
               tb.flight_id = (SELECT flight_id FROM flight_access
                WHERE tb.flight_id = flight_id AND agent_code =
                (SELECT agent_code FROM tagtrak_users
                 WHERE loginname = p_loginname))

            )
      ORDER BY timestamp, t_bag_tag;

   -- get tracking information for a particular location and
   -- date (this cursor checks flight access rights)
   -- this cursor DOES take the flight_id INTO account
   CURSOR c2_bag_track_loc IS
      WITH flight_id_list AS (SELECT p_flight_id flight_id FROM DUAL
                              UNION
                              SELECT flight_id FROM flight WHERE op_flight_id = p_flight_id)
      SELECT
         get_user_bag_tag_no_exceptions(tb.bag_tag, p_user_pref) t_bag_tag, tb.pax_id pax_id,                  -- UAS/FALCP/CA/00571
         tb.location location, 0 st_ev_no,
         tb.description description,
         tb.group_no group_no, tb.timestamp timestamp,
         tagtrak_users.loginname loader, pax.inbound_flight_no arr_flight,
         pax.inbound_boarding_point org, flight.flight_no dep_flight,
         flight_dests.flight_destination des
      FROM
         tagtrak_users, pax,
         flight, flight_dests,
           (SELECT bt.bag_tag bag_tag,
                   bt.pax_id pax_id,
                   bt.flight_id_scan flight_id,
                   bt.location_code location,
                   decode(bt.error_id,
                          0,
                          bt.trace_action,
                          substr('ERROR: ' ||
                                 TRIM(bt.trace_action) || ' - ' ||
                                 TRIM(error.error_description),
                                 1,
                                 60)) description,
                   bt.uld_no group_no,
                   decode(TRIM(db_config.db_value),
                          'Y',
                          bt.action_time,
                          bt.bag_trace_time) timestamp,
                   bt.user_id user_id
              FROM bag_trace bt, error, db_config
             WHERE bt.flight_id_scan IN (SELECT flight_id FROM flight_id_list)
               AND bt.error_id = error.error_id(+)
               AND db_config.db_item = 'ALLOW_BAG_WITH_NOFLT'
               AND bt.location_code = p_location
                AND ((TRIM(db_config.db_value) = 'Y' 
                      AND to_char(helper_date(p_date),'YYYYMMDD')
                          = to_char(bt.action_time,'YYYYMMDD')
                      ) 
                     OR
                     (TRIM(db_config.db_value) = 'N' 
                      AND to_char(helper_date(p_date),'YYYYMMDD')
                          = to_char(bt.bag_trace_time,'YYYYMMDD')
                     )
                    )
            UNION ALL
            SELECT tl.bag_tag bag_tag, -- UAS/FALCP/CA/00571
                   tl.pax_id pax_id,
                   tl.flight_id flight_id,
                   tl.location location, -- UAS/FALCP/CA/00571
                   decode(tl.status_no,
                          NULL,
                          tracking_events.description,
                          bag_status.description) description,
                   tl.group_no group_no,
                   tl.timestamp timestamp,
                   tl.user_id user_id
              FROM tracking_log tl, tracking_events, bag_status
             WHERE tl.flight_id IN (SELECT flight_id FROM flight_id_list)
               AND (tl.status_no IS NOT NULL OR
                   (tl.event_no IS NOT NULL AND tl.event_no > -1))
               AND tl.event_no = tracking_events.event_no(+)
               AND tl.status_no = bag_status.status_no(+)
               AND tl.location = p_location
               AND to_char(helper_date(p_date), 'YYYYMMDD') =
                   to_char(tl.timestamp, 'YYYYMMDD')
           ) tb
      WHERE
            tagtrak_users.user_id(+) = tb.user_id
         AND
            pax.pax_id(+) = tb.pax_id
         AND
            flight.flight_id(+) = tb.flight_id
         AND
            flight_dests.flight_id(+) = tb.flight_id
         AND
            flight_dests.dest_order(+) = 1
         AND
            (
               tb.flight_id = (SELECT flight_id FROM flight_access
                WHERE tb.flight_id = flight_id AND agent_code =
                (SELECT agent_code FROM tagtrak_users
                 WHERE loginname = p_loginname))
            )
      ORDER BY timestamp, t_bag_tag;

   -- loop counter
   l_row_counter     BINARY_INTEGER;

BEGIN

   -- Initialise the row counter
   l_row_counter := 0;

   -- if they are TTADMIN then do not check flight access privs
   IF instr(p_loginname, 'TTADMIN') = 1
   THEN

      -- if they have not given us a flight_id then return all
      -- entries that have passed this location
      IF p_flight_id is NULL or p_flight_id = 0
      THEN

         -- loop through the tracking entries
         FOR this_row IN c1_bag_track_loc_ttadmin LOOP

            -- increment the row count
            l_row_counter := l_row_counter + 1;

            -- add the data to the return arrays
            p_bag_tag(l_row_counter) := TRIM(this_row.t_bag_tag);
            p_pax_id(l_row_counter) := TRIM(this_row.pax_id);
            p_location_out(l_row_counter) := TRIM(this_row.location);
            p_st_ev_no(l_row_counter) := TRIM(this_row.st_ev_no);
            p_description(l_row_counter) := TRIM(this_row.description);
            p_group_no(l_row_counter) := TRIM(this_row.group_no);
            p_time(l_row_counter) := TRIM(this_row.timestamp);
            p_loader(l_row_counter) := TRIM(this_row.loader);
            p_arr_flight(l_row_counter) := TRIM(this_row.arr_flight);
            p_org(l_row_counter) := TRIM(this_row.org);
            p_dep_flight(l_row_counter) := TRIM(this_row.dep_flight);
            p_des(l_row_counter) := TRIM(this_row.des);

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through c1_bag_track_loc_ttadmin

      ELSE -- they have given us a flight_id

         -- loop through the tracking entries
         FOR this_row IN c2_bag_track_loc_ttadmin LOOP

            -- increment the row count
            l_row_counter := l_row_counter + 1;

            -- add the data to the return arrays
            p_bag_tag(l_row_counter) := TRIM(this_row.t_bag_tag);
            p_pax_id(l_row_counter) := TRIM(this_row.pax_id);
            p_location_out(l_row_counter) := TRIM(this_row.location);
            p_st_ev_no(l_row_counter) := TRIM(this_row.st_ev_no);
            p_description(l_row_counter) := TRIM(this_row.description);
            p_group_no(l_row_counter) := TRIM(this_row.group_no);
            p_time(l_row_counter) := TRIM(this_row.timestamp);
            p_loader(l_row_counter) := TRIM(this_row.loader);
            p_arr_flight(l_row_counter) := TRIM(this_row.arr_flight);
            p_org(l_row_counter) := TRIM(this_row.org);
            p_dep_flight(l_row_counter) := TRIM(this_row.dep_flight);
            p_des(l_row_counter) := TRIM(this_row.des);

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through c2_bag_track_loc_ttadmin

      END IF; -- end of they have given us flight_id

   ELSE -- they are not TTADMIN and flight access must be checked

      -- if they have not given us a flight_id then return all entries
      -- that have passed this location
      IF p_flight_id is NULL or p_flight_id = 0
      THEN

         -- loop through the tracking entries
         FOR this_row IN c1_bag_track_loc LOOP

            -- increment the row count
            l_row_counter := l_row_counter + 1;

            -- add the data to the return arrays
            p_bag_tag(l_row_counter) := TRIM(this_row.t_bag_tag);
            p_pax_id(l_row_counter) := TRIM(this_row.pax_id);
            p_location_out(l_row_counter) := TRIM(this_row.location);
            p_st_ev_no(l_row_counter) := TRIM(this_row.st_ev_no);
            p_description(l_row_counter) := TRIM(this_row.description);
            p_group_no(l_row_counter) := TRIM(this_row.group_no);
            p_time(l_row_counter) := TRIM(this_row.timestamp);
            p_loader(l_row_counter) := TRIM(this_row.loader);
            p_arr_flight(l_row_counter) := TRIM(this_row.arr_flight);
            p_org(l_row_counter) := TRIM(this_row.org);
            p_dep_flight(l_row_counter) := TRIM(this_row.dep_flight);
            p_des(l_row_counter) := TRIM(this_row.des);

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through c1_bag_track_loc

      ELSE -- they have given us a flight_id

         -- loop through the tracking entries
         FOR this_row IN c2_bag_track_loc LOOP

            -- increment the row count
            l_row_counter := l_row_counter + 1;

            -- add the data to the return arrays
            p_bag_tag(l_row_counter) := TRIM(this_row.t_bag_tag);
            p_pax_id(l_row_counter) := TRIM(this_row.pax_id);
            p_location_out(l_row_counter) := TRIM(this_row.location);
            p_st_ev_no(l_row_counter) := TRIM(this_row.st_ev_no);
            p_description(l_row_counter) := TRIM(this_row.description);
            p_group_no(l_row_counter) := TRIM(this_row.group_no);
            p_time(l_row_counter) := TRIM(this_row.timestamp);
            p_loader(l_row_counter) := TRIM(this_row.loader);
            p_arr_flight(l_row_counter) := TRIM(this_row.arr_flight);
            p_org(l_row_counter) := TRIM(this_row.org);
            p_dep_flight(l_row_counter) := TRIM(this_row.dep_flight);
            p_des(l_row_counter) := TRIM(this_row.des);

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through c2_bag_track_loc

      END IF; -- end of they have given us flight_id

   END IF; -- end of they are not a TTADMIN user and have to check
           -- flight access

END get_bag_track_by_location;

/******************************************************************************
 *
 * FUNCTION GET_BAG_TRACK_BY_LOCATION_2
 *
 ******************************************************************************/

FUNCTION get_bag_track_by_location_2
(
  p_location   IN tracking_log.location%TYPE,
  p_date       IN VARCHAR2,
  p_loginname  IN tagtrak_users.loginname%TYPE,
  p_flight_id  IN flight.flight_id%TYPE,
  p_user_pref  IN VARCHAR2 -- UAS/FALCP/CA/00571
)
RETURN SYS_REFCURSOR
IS

  l_cursor          SYS_REFCURSOR;

BEGIN

  -- if they are TTADMIN then do not check flight access privs
  IF instr(p_loginname, 'TTADMIN') = 1 THEN

    -- if they have not given us a flight_id then return all
    -- entries that have passed this location
    IF p_flight_id is NULL or p_flight_id = 0 THEN

      OPEN l_cursor FOR
      SELECT TRIM(get_user_bag_tag_no_exceptions(tlbt.bag_tag, p_user_pref)) t_bag_tag,
             TRIM(tlbt.pax_id) pax_id,
             TRIM(tlbt.location) location,
             0 st_ev_no, TRIM(tlbt.description) description,
             TRIM(tlbt.group_no) group_no, tlbt.timestamp,
             TRIM(tu.loginname) loader, TRIM(pax.inbound_flight_no) arr_flight,
             TRIM(pax.inbound_boarding_point) org, TRIM(flight.flight_no) dep_flight,
             TRIM(fd.flight_destination) des
      FROM tracking_log_and_bag_trace_v tlbt, tagtrak_users tu,
           pax, flight, flight_dests fd
      WHERE trim(tlbt.location) = trim(p_location)
      AND TO_CHAR(helper_date(p_date),'YYYYMMDD') = TO_CHAR(tlbt.timestamp,'YYYYMMDD')
      AND tu.user_id(+) = tlbt.user_id
      AND pax.pax_id(+) = tlbt.pax_id
      AND flight.flight_id(+) = tlbt.flight_id
      AND fd.flight_id(+) = tlbt.flight_id
      AND fd.dest_order(+) = 1
      AND (flight.site_id = (SELECT site_id
                             FROM agents
                             WHERE agent_code = (SELECT agent_code
                                                 FROM tagtrak_users
                                                 WHERE loginname = p_loginname))
           OR (tlbt.flight_id = 0
               AND tlbt.user_id IN (SELECT t2.user_id
                                    FROM tagtrak_users t1,
                                         tagtrak_users t2,
                                         agents a1,
                                         agents a2
                                    WHERE t1.loginname = p_loginname
                                    AND t1.agent_code = a1.agent_code
                                    AND a1.site_id = a2.site_id
                                    AND a2.agent_code = t2.agent_code)))
      ORDER BY timestamp, t_bag_tag;

    ELSE -- they have given us a flight_id

      OPEN l_cursor FOR
      WITH flight_id_list AS (SELECT p_flight_id flight_id FROM DUAL
                              UNION
                              SELECT flight_id FROM flight WHERE op_flight_id = p_flight_id)
      SELECT TRIM(get_user_bag_tag_no_exceptions(tlbt.bag_tag, p_user_pref)) t_bag_tag,
             TRIM(tlbt.pax_id) pax_id,
             TRIM(tlbt.location) location, 0 st_ev_no,
             TRIM(tlbt.description) description,
             TRIM(tlbt.group_no) group_no,
             tlbt.timestamp timestamp,
             TRIM(tu.loginname) loader, TRIM(pax.inbound_flight_no) arr_flight,
             TRIM(pax.inbound_boarding_point) org, TRIM(flight.flight_no) dep_flight,
             TRIM(fd.flight_destination) des
      FROM tracking_log_and_bag_trace_v tlbt, tagtrak_users tu,
           pax, flight, flight_dests fd
      WHERE tlbt.location = p_location
      AND TO_CHAR(helper_date(p_date),'YYYYMMDD') = TO_CHAR(tlbt.timestamp,'YYYYMMDD')
      AND tu.user_id(+) = tlbt.user_id
      AND pax.pax_id(+) = tlbt.pax_id
      AND tlbt.flight_id IN (SELECT flight_id FROM flight_id_list)
      AND flight.flight_id(+) = tlbt.flight_id
      AND fd.flight_id(+) = tlbt.flight_id
      AND fd.dest_order(+) = 1
      AND EXISTS (SELECT 1
                  FROM flight_access fa, tagtrak_users tu
                  WHERE fa.flight_id = tlbt.flight_id
                  AND fa.agent_code = tu.agent_code
                  AND tu.loginname = p_loginname)
      ORDER BY timestamp, t_bag_tag;

    END IF; -- end of they have given us flight_id

  ELSE -- they are not TTADMIN and flight access must be checked

    -- if they have not given us a flight_id then return all entries
    -- that have passed this location
    IF p_flight_id is NULL or p_flight_id = 0 THEN

      OPEN l_cursor FOR
      SELECT TRIM(get_user_bag_tag_no_exceptions(tlbt.bag_tag, p_user_pref)) t_bag_tag,
             TRIM(tlbt.pax_id) pax_id, TRIM(tlbt.location) location,
             0 st_ev_no, TRIM(tlbt.description) description,
             TRIM(tlbt.group_no) group_no, tlbt.timestamp timestamp,
             TRIM(tu.loginname) loader, TRIM(pax.inbound_flight_no) arr_flight,
             TRIM(pax.inbound_boarding_point) org, TRIM(flight.flight_no) dep_flight,
             TRIM(fd.flight_destination) des
      FROM tracking_log_and_bag_trace_v tlbt, tagtrak_users tu,
           pax, flight, flight_dests fd
      WHERE tlbt.location = p_location
      AND TO_CHAR(helper_date(p_date),'YYYYMMDD') = TO_CHAR(tlbt.timestamp,'YYYYMMDD')
      AND tu.user_id(+) = tlbt.user_id
      AND pax.pax_id(+) = tlbt.pax_id
      AND flight.flight_id(+) = tlbt.flight_id
      AND fd.flight_id(+) = tlbt.flight_id
      AND fd.dest_order(+) = 1
      AND ((tlbt.flight_id = 0
            AND tlbt.user_id IN (SELECT t2.user_id
                                 FROM tagtrak_users t1, tagtrak_users t2,
                                      agents a1, agents a2
                                 WHERE t1.loginname = p_loginname
                                 AND t1.agent_code = a1.agent_code
                                 AND a1.site_id = a2.site_id
                                 AND a2.agent_code = t2.agent_code))
           OR tlbt.flight_id = (SELECT flight_id
                                FROM flight_access
                                WHERE tlbt.flight_id = flight_id
                                AND agent_code = (SELECT agent_code
                                                  FROM tagtrak_users
                                                  WHERE loginname = p_loginname)))
      ORDER BY timestamp, t_bag_tag;

    ELSE -- they have given us a flight_id

      OPEN l_cursor FOR
      WITH flight_id_list AS (SELECT p_flight_id flight_id FROM DUAL
                              UNION
                              SELECT flight_id FROM flight WHERE op_flight_id = p_flight_id)
      SELECT TRIM(get_user_bag_tag_no_exceptions(tlbt.bag_tag, p_user_pref)) t_bag_tag,
             TRIM(tlbt.pax_id) pax_id,
             TRIM(tlbt.location) location, 0 st_ev_no,
             TRIM(tlbt.description) description,
             TRIM(tlbt.group_no) group_no,
             tlbt.timestamp timestamp,
             TRIM(tu.loginname) loader, TRIM(pax.inbound_flight_no) arr_flight,
             TRIM(pax.inbound_boarding_point) org, TRIM(flight.flight_no) dep_flight,
             TRIM(fd.flight_destination) des
      FROM tracking_log_and_bag_trace_v tlbt, tagtrak_users tu,
           pax, flight, flight_dests fd
      WHERE tlbt.location = p_location
      AND to_char(helper_date(p_date),'YYYYMMDD') = TO_CHAR(tlbt.timestamp,'YYYYMMDD')
      AND tu.user_id(+) = tlbt.user_id
      AND tlbt.flight_id IN (SELECT flight_id FROM flight_id_list)
      AND pax.pax_id(+) = tlbt.pax_id
      AND flight.flight_id(+) = tlbt.flight_id
      AND fd.flight_id(+) = tlbt.flight_id
      AND fd.dest_order(+) = 1
      AND tlbt.flight_id = (SELECT flight_id
                            FROM flight_access
                            WHERE tlbt.flight_id = flight_id
                            AND agent_code = (SELECT agent_code
                                              FROM tagtrak_users
                                              WHERE loginname = p_loginname))
      ORDER BY timestamp, t_bag_tag;

    END IF; -- end of they have given us flight_id

  END IF; -- end of they are not a TTADMIN user and have to check
           -- flight access

  RETURN l_cursor;

END get_bag_track_by_location_2;


/******************************************************************************
 *
 * PROCEDURE HELPER_ADDITIONAL_INFO
 *
 ******************************************************************************/

PROCEDURE helper_additional_info
(
   p_flight_id                IN  flight.flight_id%TYPE,
   p_bag_tag                  IN  tracking_log.bag_tag%TYPE,
   p_location                 IN  tracking_log.location%TYPE,
   p_pax_id                   OUT pax.pax_id%TYPE,
   p_group_no                 OUT tracking_log.group_no%TYPE,
   p_loader                   OUT tagtrak_users.loginname%TYPE,
   p_dep_flight               OUT flight.flight_no%TYPE,
   p_flight_sch_time          OUT flight.flight_sch_time%TYPE,
   p_flight_date              OUT flight.flight_date%TYPE
)
IS
   CURSOR c_additional_info (n_bag_tag tracking_log.bag_tag%TYPE) IS
      SELECT
         tracking_log_and_bag_trace_v.pax_id pax_id, tracking_log_and_bag_trace_v.group_no group_no,
         tagtrak_users.loginname loader, flight_no,
         flight_sch_time, flight_date, tracking_log_and_bag_trace_v.timestamp timestamp
      FROM
         tracking_log_and_bag_trace_v, flight, tagtrak_users
      WHERE
            tracking_log_and_bag_trace_v.flight_id = p_flight_id
         AND
            substr(tracking_log_and_bag_trace_v.bag_tag,2,9) = substr(p_bag_tag,2,9)
         AND
            tracking_log_and_bag_trace_v.location = p_location
         AND
            tagtrak_users.user_id(+) = tracking_log_and_bag_trace_v.user_id
         AND
            tracking_log_and_bag_trace_v.flight_id = flight.flight_id
      ORDER BY timestamp;

   -- loop counter
   l_row_counter     BINARY_INTEGER;

   -- this holds the numeric form of the given bag tag
   v_bag_tag         tracking_log.bag_tag%TYPE; 

BEGIN

   -- Convert the given bag tag to numeric format
   v_bag_tag := get_numeric_bag_tag(p_bag_tag);

   -- Initialise the row counter
   l_row_counter := 1;

   -- loop through the data returned (BUT ONLY process the first row)
   FOR this_row IN c_additional_info (v_bag_tag) LOOP

      -- add the data to the return variables
      p_pax_id := TRIM(this_row.pax_id);
      p_group_no := TRIM(this_row.group_no);
      p_loader := TRIM(this_row.loader);
      p_dep_flight := TRIM(this_row.flight_no);
      p_flight_sch_time := TRIM(this_row.flight_sch_time);
      p_flight_date := TRIM(this_row.flight_date);

      -- only return one row
      EXIT WHEN l_row_counter = 1;

   END LOOP; -- end of looping through c_additional_info

END helper_additional_info;

/******************************************************************************
 *
 * PROCEDURE GET_EXCEED_TRACK_TIME
 *
 ******************************************************************************/

PROCEDURE get_exceed_track_time
(
   p_location1                IN  tracking_log.location%TYPE,
   p_location2                IN  tracking_log.location%TYPE,
   p_flight_date              IN  flight.flight_date%TYPE,
   p_max_time                 IN  char,
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_flight_id                IN  flight.flight_id%TYPE,
   p_user_pref                IN  VARCHAR2,
   p_bag_tag                  OUT bag_tag_type,
   p_group_no                 OUT group_no_type,
   p_loader                   OUT loader_type,
   p_dep_flight               OUT flight_no_type,
   p_flight_sch_time          OUT flight_sch_time_type,
   p_flight_date_out          OUT flight_date_type,
   p_time_taken               OUT integer_type,
   p_pax_id                   OUT pax_id_type                     
)
IS
   -- get a list of times for bags that have reached location1 and location2
   -- and also a list
   -- of times for bags that have reached location1 but have not yet reached
   -- location2
   -- this list will then be processed and for rows that have times than exceed
   -- a specified amount
   -- they will be outputed to the MWS

   -- this cursor does not filter on flight
   CURSOR c1_exceed_time_ttadmin IS
      SELECT
         t1.bag_tag t_bag_tag, t1.flight_id flt_id, MIN(t1.timestamp) first_time,
         MIN(t2.timestamp) second_time,
         (MIN(t2.timestamp) - MIN(t1.timestamp)) * 1440 elapsed_time
      FROM
         tracking_log_and_bag_trace_v t1, tracking_log_and_bag_trace_v t2, flight
      WHERE
            t1.location=p_location1 AND t2.location=p_location2
         AND
            substr(t1.bag_tag,2,9) = substr(t2.bag_tag,2,9) AND
            (
               (
                  t1.flight_id = t2.flight_id AND
                  t1.flight_id = flight.flight_id
               )
          OR
          (
                  t1.flight_id = 0 AND t2.flight_id = 0
               )
       )
         AND
            (
          (flight.flight_date = p_flight_date AND t1.flight_id != 0 AND t2.flight_id != 0)
          OR
          (
                  t1.flight_id = 0 AND t2.flight_id = 0 AND
             (
                (
                        to_char(t1.timestamp,'DDMON') = p_flight_date
                     )
                OR
           (
                        to_char(t2.timestamp,'DDMON') = p_flight_date
                     )
        )
          )
       )
         AND
       (
               (
                  flight.site_id = (SELECT site_id FROM agents WHERE agent_code =
                   (SELECT agent_code FROM tagtrak_users WHERE
                     loginname = p_loginname))
          )
          OR
          (
              t1.flight_id = 0 AND t1.user_id in
              (select user_id from tagtrak_users where agent_code in
               (select agent_code from agents where site_id =
                (select site_id from tagtrak_users where loginname = p_loginname)
               )
         )
         AND
         t2.flight_id = 0 AND t2.user_id in
          (select user_id from tagtrak_users where agent_code in
           (select agent_code from agents where site_id =
              (select site_id from tagtrak_users where loginname = p_loginname)
           )
          )
           )
       )

      GROUP BY
         t1.bag_tag, t1.flight_id;

   -- this cursor checks flight access rights
   -- this cursor does not filter on flight
   CURSOR c1_exceed_time IS
      SELECT
         t1.bag_tag t_bag_tag, t1.flight_id flt_id, MIN(t1.timestamp) first_time,
         MIN(t2.timestamp) second_time,
         (MIN(t2.timestamp) - MIN(t1.timestamp)) * 1440 elapsed_time
      FROM
         tracking_log_and_bag_trace_v t1, tracking_log_and_bag_trace_v t2, flight
      WHERE
            t1.location=p_location1 AND t2.location=p_location2
         AND
            substr(t1.bag_tag,2,9) = substr(t2.bag_tag,2,9) AND
            (
          (
                  t1.flight_id = t2.flight_id AND
                  t1.flight_id = flight.flight_id
               )
          OR
          (
                  t1.flight_id = 0 AND t2.flight_id = 0
               )
       )
         AND
            (
          (
                  flight.flight_date = p_flight_date AND t1.flight_id != 0 AND t2.flight_id != 0
               )
          OR
          (
                   t1.flight_id = 0 AND t2.flight_id = 0 AND
                   (
            (
                         to_char(t1.timestamp,'DDMON') = p_flight_date
                      )
               OR
            (
                         to_char(t2.timestamp,'DDMON') = p_flight_date
                      )
         )
           )
        )
         AND
       (
               (t1.flight_id = (SELECT flight_id FROM flight_access WHERE
                 t1.flight_id = flight_id AND agent_code =
                  (SELECT agent_code FROM tagtrak_users WHERE loginname = p_loginname))
      )
                OR
                (
         t1.flight_id = 0 AND t1.user_id in
          (select user_id from tagtrak_users where agent_code in
           (select agent_code from agents where site_id =
            (select site_id from tagtrak_users where loginname = p_loginname)
           )
          )
          AND
          t2.flight_id = 0 AND t2.user_id in
           (select user_id from tagtrak_users where agent_code in
            (select agent_code from agents where site_id =
             (select site_id from tagtrak_users where loginname = p_loginname)
            )
           )
      )
       )
      GROUP BY
         t1.bag_tag, t1.flight_id;

   -- this cursor DOES filter on flight
   CURSOR c2_exceed_time_ttadmin IS
      SELECT
         t1.bag_tag t_bag_tag, t1.flight_id flt_id, MIN(t1.timestamp) first_time,
         MIN(t2.timestamp) second_time,
         (MIN(t2.timestamp) - MIN(t1.timestamp)) * 1440 elapsed_time
      FROM
         tracking_log_and_bag_trace_v t1, tracking_log_and_bag_trace_v t2, flight
      WHERE
            t1.location=p_location1 AND t2.location=p_location2
         AND
            substr(t1.bag_tag,2,9) = substr(t2.bag_tag,2,9) AND
            t1.flight_id = t2.flight_id AND
            t1.flight_id = flight.flight_id
         AND
            flight.flight_id = p_flight_id
      GROUP BY
         t1.bag_tag, t1.flight_id;

   -- this cursor checks flight access rights
   -- this cursor DOES filter on flight
   CURSOR c2_exceed_time IS
      SELECT
         t1.bag_tag t_bag_tag, t1.flight_id flt_id, MIN(t1.timestamp) first_time,
         MIN(t2.timestamp) second_time,
         (MIN(t2.timestamp) - MIN(t1.timestamp)) * 1440 elapsed_time
      FROM
         tracking_log_and_bag_trace_v t1, tracking_log_and_bag_trace_v t2, flight
      WHERE
            t1.location=p_location1 AND t2.location=p_location2
         AND
            substr(t1.bag_tag,2,9) = substr(t2.bag_tag,2,9) AND
            t1.flight_id = t2.flight_id AND
            t1.flight_id = flight.flight_id
         AND
            flight.flight_id = p_flight_id
         AND
            t1.flight_id = (SELECT flight_id FROM flight_access WHERE
            t1.flight_id = flight_id AND agent_code =
            (SELECT agent_code FROM tagtrak_users WHERE loginname = p_loginname))
      GROUP BY
         t1.bag_tag, t1.flight_id;

   -- loop counter
   l_row_counter     BINARY_INTEGER;

   -- information from the helper function
   l_pax_id                   pax.pax_id%TYPE;
   l_group_no                 tracking_log.group_no%TYPE;
   l_loader                   tagtrak_users.loginname%TYPE;
   l_dep_flight               flight.flight_no%TYPE;
   l_flight_sch_time          flight.flight_sch_time%TYPE;
   l_flight_date_out          flight.flight_date%TYPE;

BEGIN
   -- Initialise the row counter
   l_row_counter := 0;

   -- if they are TTADMIN then do not check flight access privs
   IF instr(p_loginname, 'TTADMIN') = 1
   THEN

      -- if they have not given us a flight_id then process all entries
      IF p_flight_id is NULL or p_flight_id = 0
      THEN
         -- loop through the tracking entries
         FOR this_row IN c1_exceed_time_ttadmin LOOP

            -- does it exceed the provided max_time
            IF this_row.elapsed_time > to_number(p_max_time)
            THEN

               -- get the rest of the flight details add to the results set
               helper_additional_info(this_row.flt_id, this_row.t_bag_tag,
                                      p_location1, l_pax_id, l_group_no,
                                      l_loader, l_dep_flight, l_flight_sch_time,
                                      l_flight_date_out);

               -- increment the row count
               l_row_counter := l_row_counter + 1;

               -- now add the data to the return arrays
               p_bag_tag(l_row_counter) := TRIM(get_user_bag_tag_no_exceptions(this_row.t_bag_tag, p_user_pref));
               p_pax_id(l_row_counter) := TRIM(l_pax_id);
               p_group_no(l_row_counter) := TRIM(l_group_no);
               p_loader(l_row_counter) := TRIM(l_loader);
               p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
               p_flight_sch_time(l_row_counter) := TRIM(l_flight_sch_time);
               p_flight_date_out(l_row_counter) := TRIM(l_flight_date_out);
               p_time_taken(l_row_counter) := TRIM(this_row.elapsed_time);

            END IF; -- end of time has exceeded supplied time

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c1_exceed_time_ttadmin

      ELSE -- we are to filter on the flight_id

         -- loop through the tracking entries
         FOR this_row IN c2_exceed_time_ttadmin LOOP

            -- does it exceed the provided max_time
            IF this_row.elapsed_time > to_number(p_max_time)
            THEN

               -- get the rest of the flight details add to the results set
               helper_additional_info(this_row.flt_id, this_row.t_bag_tag,
                                      p_location1, l_pax_id, l_group_no,
                                      l_loader, l_dep_flight, l_flight_sch_time,
                                      l_flight_date_out);

               -- increment the row count
               l_row_counter := l_row_counter + 1;

               -- now add the data to the return arrays
               p_bag_tag(l_row_counter) := TRIM(get_user_bag_tag_no_exceptions(this_row.t_bag_tag, p_user_pref));
               p_pax_id(l_row_counter) := TRIM(l_pax_id);
               p_group_no(l_row_counter) := TRIM(l_group_no);
               p_loader(l_row_counter) := TRIM(l_loader);
               p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
               p_flight_sch_time(l_row_counter) := TRIM(l_flight_sch_time);
               p_flight_date_out(l_row_counter) := TRIM(l_flight_date_out);
               p_time_taken(l_row_counter) := TRIM(this_row.elapsed_time);

            END IF; -- end of time has exceeded supplied time

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c2_exceed_time_ttadmin

      END IF; -- end of are we to filter on flight_id or not

   ELSE -- we are NOT a TTADMIN user so check flight access

      -- if they have not given us a flight_id then process all entries
      IF p_flight_id is NULL or p_flight_id = 0
      THEN
         -- loop through the tracking entries
         FOR this_row IN c1_exceed_time LOOP

            -- does it exceed the provided max_time
            IF this_row.elapsed_time > to_number(p_max_time)
            THEN

               -- get the rest of the flight details add to the results set
               helper_additional_info(this_row.flt_id, this_row.t_bag_tag,
                                      p_location1, l_pax_id, l_group_no,
                                      l_loader, l_dep_flight, l_flight_sch_time,
                                      l_flight_date_out);

               -- increment the row count
               l_row_counter := l_row_counter + 1;

               -- now add the data to the return arrays
               p_bag_tag(l_row_counter) := TRIM(get_user_bag_tag_no_exceptions(this_row.t_bag_tag, p_user_pref));
               p_pax_id(l_row_counter) := TRIM(l_pax_id);
               p_group_no(l_row_counter) := TRIM(l_group_no);
               p_loader(l_row_counter) := TRIM(l_loader);
               p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
               p_flight_sch_time(l_row_counter) := TRIM(l_flight_sch_time);
               p_flight_date_out(l_row_counter) := TRIM(l_flight_date_out);
               p_time_taken(l_row_counter) := TRIM(this_row.elapsed_time);

            END IF; -- end of time has exceeded supplied time

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c1_exceed_time

      ELSE -- we are to filter on the flight_id

         -- loop through the tracking entries
         FOR this_row IN c2_exceed_time LOOP

            -- does it exceed the provided max_time
            IF this_row.elapsed_time > to_number(p_max_time)
            THEN

               -- get the rest of the flight details add to the results set
               helper_additional_info(this_row.flt_id, this_row.t_bag_tag,
                                      p_location1, l_pax_id, l_group_no,
                                      l_loader, l_dep_flight, l_flight_sch_time,
                                      l_flight_date_out);

               -- increment the row count
               l_row_counter := l_row_counter + 1;

               -- now add the data to the return arrays
               p_bag_tag(l_row_counter) := TRIM(get_user_bag_tag_no_exceptions(this_row.t_bag_tag, p_user_pref));
               p_pax_id(l_row_counter) := TRIM(l_pax_id);
               p_group_no(l_row_counter) := TRIM(l_group_no);
               p_loader(l_row_counter) := TRIM(l_loader);
               p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
               p_flight_sch_time(l_row_counter) := TRIM(l_flight_sch_time);
               p_flight_date_out(l_row_counter) := TRIM(l_flight_date_out);
               p_time_taken(l_row_counter) := TRIM(this_row.elapsed_time);

            END IF; -- end of time has exceeded supplied time

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c2_exceed_time

      END IF; -- end of are to filter on flight_id or not

   END IF; -- end of are they/not a TTADMIN user

END get_exceed_track_time;


/******************************************************************************
 *
 * FUNCTION GET_EXCEED_TRACK_TIME_2
 *
 ******************************************************************************/

FUNCTION get_exceed_track_time_2(p_location1    IN  tracking_log.location%TYPE,
                                 p_location2    IN  tracking_log.location%TYPE,
                                 p_flight_date  IN  flight.flight_date%TYPE,
                                 p_max_time     IN  char,
                                 p_loginname    IN  tagtrak_users.loginname%TYPE,
                                 p_flight_id    IN  flight.flight_id%TYPE,
                                 p_user_pref    IN  VARCHAR2) RETURN SYS_REFCURSOR IS

  l_bag_tag          bag_tag_type;
  l_pax_id           pax_id_type;
  l_group_no         group_no_type;
  l_loader           loader_type;
  l_dep_flight       flight_no_type;
  l_flight_sch_time  flight_sch_time_type;
  l_time_taken       integer_type;
  l_flight_date_out  flight_date_type;

  l_results_tab      exceed_track_time_tab := exceed_track_time_tab();
  l_cursor           SYS_REFCURSOR;

BEGIN

  get_exceed_track_time(p_location1,
                        p_location2,
                        p_flight_date,
                        p_max_time,
                        p_loginname,
                        p_flight_id,
                        p_user_pref,
                        l_bag_tag,
                        l_group_no,
                        l_loader,
                        l_dep_flight,
                        l_flight_sch_time,
                        l_flight_date_out,
                        l_time_taken,
                        l_pax_id);

  FOR n IN nvl(l_bag_tag.FIRST,1)..nvl(l_bag_tag.LAST,0) LOOP

    BEGIN

      --make room in sql table.
      l_results_tab.extend(1);
      --use constructor to populate.
      l_results_tab(n) := exceed_track_time_rec(l_bag_tag(n),
                                                l_group_no(n),
                                                l_loader(n),
                                                l_dep_flight(n),
                                                l_flight_sch_time(n),
                                                l_flight_date_out(n),
                                                l_time_taken(n),
                                                l_pax_id(n));

    EXCEPTION WHEN no_data_found THEN
      NULL; --don't worry about gaps in the sql tables.

    END;

  END LOOP;

  --Build ref cursor from sql table.
  OPEN l_cursor FOR
  SELECT *
  FROM TABLE(CAST(l_results_tab AS exceed_track_time_tab));

  RETURN l_cursor;

END get_exceed_track_time_2;


/******************************************************************************
 *
 * FUNCTION HELPER_YEAR
 *
 ******************************************************************************/

FUNCTION helper_year
(
   p_date                IN  varchar2
)
   RETURN date
IS
   l_new_date date;
BEGIN
   -- this function takes a flight_date such as 05JAN and returns a varchar2
   -- containing the flight date and the year.
   -- The year is calculated by looking at the current sysdate and comparing it
   -- against the flight date month.
   -- If the current sysdate is JAN and the flight date is DEC then the year for
   -- the flight is assumed to be this year minus 1
   -- If the current sysdate is DEC and the flight date is JAN then the year for
   -- the flight is assumed to be this year plus 1
   -- Else the year is the same year as sysdate

   IF TO_CHAR(sysdate,'MON') = 'JAN' AND
      TO_CHAR(TO_DATE(p_date,'DDMONHH24MI'),'MON') = 'DEC'
   THEN

      l_new_date :=
         TO_DATE(p_date||TO_CHAR(to_number(TO_CHAR(sysdate,'YYYY')) - 1),
                'DDMONHH24MIYYYY');

   ELSIF TO_CHAR(sysdate,'MON') = 'DEC' AND
         TO_CHAR(TO_DATE(p_date,'DDMONHH24MI'),'MON') = 'JAN'
   THEN

      l_new_date :=
         TO_DATE(p_date||TO_CHAR(to_number(TO_CHAR(sysdate,'YYYY')) + 1),
                 'DDMONHH24MIYYYY');

   ELSE

      l_new_date :=
         TO_DATE(p_date||TO_CHAR(to_number(TO_CHAR(sysdate,'YYYY'))),
                 'DDMONHH24MIYYYY');

   END IF; -- end of getting correct year for the flight

   RETURN(l_new_date);

END helper_year;

/******************************************************************************
 *
 * PROCEDURE HELPER_MAX_LOG_TIME
 *
 ******************************************************************************/

PROCEDURE helper_max_log_time
(
   p_pax_id                   IN   pax.pax_id%TYPE,
   p_bag_tag                  IN   tracking_log_and_bag_trace_v.bag_tag%TYPE,
   p_timed_at                 OUT  tracking_log_and_bag_trace_v.timestamp%TYPE
)
IS

   -- this holds the numeric form of the given bag tag
   v_bag_tag         tracking_log.bag_tag%TYPE;

BEGIN
 
   -- Convert the given bag tag to numeric format
   v_bag_tag := get_numeric_bag_tag(p_bag_tag);

   -- Initialise date to null. This will be returned if no logs
   -- can be found for the given bag/pax

   p_timed_at := null;

   -- Find the latest timestamp for the bag

   SELECT 
      MAX(timestamp) 
   INTO
      p_timed_at
   FROM 
      tracking_log_and_bag_trace_v
   WHERE
      pax_id = p_pax_id AND
      substr(bag_tag,2,9) = substr(v_bag_tag,2,9);

EXCEPTION WHEN OTHERS THEN
   NULL;

END; -- helper_max_log_time



/******************************************************************************
 *
 * PROCEDURE HELPER_CRITICAL_ADD_INFO
 *
 ******************************************************************************/

PROCEDURE helper_critical_add_info
(
   p_flight_id                IN  flight.flight_id%TYPE,
   p_pax_id                   IN  pax.pax_id%TYPE,
   p_bag_tag                  IN  tracking_log.bag_tag%TYPE,
   p_timed_at                 IN  tracking_log.timestamp%TYPE,
   p_location                 OUT tracking_log.location%TYPE,
   p_st_ev_no                 OUT tracking_log.event_no%TYPE,
   p_description              OUT tracking_events.description%TYPE,
   p_group_no                 OUT tracking_log.group_no%TYPE,
   p_loader                   OUT tagtrak_users.loginname%TYPE,
   p_dep_flight               OUT flight.flight_no%TYPE,
   p_flight_sch_time          OUT flight.flight_sch_time%TYPE,
   p_stand                    OUT flight_info.apron%TYPE
)
IS
   CURSOR c_additional_info_track (n_bag_tag tracking_log.bag_tag%TYPE) IS
         SELECT
            tracking_log_and_bag_trace_v.location location, 0 st_ev_no,
            tracking_log_and_bag_trace_v.description description,
            tracking_log_and_bag_trace_v.group_no group_no, tagtrak_users.loginname loader
         FROM
            tracking_log_and_bag_trace_v, tagtrak_users
         WHERE
            tagtrak_users.user_id(+) = tracking_log_and_bag_trace_v.user_id AND
            tracking_log_and_bag_trace_v.timestamp = p_timed_at AND
            tracking_log_and_bag_trace_v.pax_id = p_pax_id AND
            substr(tracking_log_and_bag_trace_v.bag_tag,2,9) = substr(n_bag_tag,2,9);

   -- loop counter
   l_row_counter     BINARY_INTEGER;

   -- this holds the numeric form of the given bag tag
   v_bag_tag         tracking_log.bag_tag%TYPE;

BEGIN

   -- Convert the given bag tag to numeric format
   v_bag_tag := get_numeric_bag_tag(p_bag_tag);

   -- Initialise the row counter
   l_row_counter := 0;

   -- loop through the data returned (BUT ONLY process the first row)
   FOR this_row IN c_additional_info_track (v_bag_tag) LOOP

      -- increment the row counter
      l_row_counter := l_row_counter + 1;

      -- add the data to the return variables
      p_location        := TRIM(this_row.location);
      p_st_ev_no        := TRIM(this_row.st_ev_no);
      p_description     := TRIM(this_row.description);
      p_group_no        := TRIM(this_row.group_no);
      p_loader          := TRIM(this_row.loader);

      -- only process one row
      EXIT WHEN l_row_counter = 1;

   END LOOP; -- end of looping through c_additional_info_track

   -- if we got no rows then indicate the bag has not been tracked
   IF l_row_counter = 0 THEN
      p_location := 'NONE';
      p_description := 'NOT TRACKED';
      p_loader := 'NONE';
   END IF;

   BEGIN
      -- now get the flight information
      SELECT flight_no, flight_sch_time INTO p_dep_flight, p_flight_sch_time
      FROM flight
      WHERE
         flight.flight_id=p_flight_id;

   EXCEPTION WHEN NO_DATA_FOUND THEN
      NULL;
   END;

   BEGIN
      -- now get the stand information
      SELECT apron INTO p_stand FROM flight_info, flight
      WHERE
         flight.flight_id = p_flight_id AND
         flight_info.flight_no = flight.flight_no AND
         flight_info.flight_date = flight.flight_date;

   EXCEPTION WHEN NO_DATA_FOUND THEN
      NULL;
   END;


END; -- helper_critical_add_info

/******************************************************************************
 *
 * PROCEDURE GET_CRITICAL_TRACK
 *
 ******************************************************************************/

PROCEDURE get_critical_track
(
   p_terminal                 IN  flight_info.terminal%TYPE,
   p_max_time                 IN  char,
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_flight_id                IN  flight.flight_id%TYPE,
   p_user_pref                IN  VARCHAR2,
   p_bag_tag                  OUT bag_tag_type,
   p_pax_id                   OUT pax_id_type,
   p_location                 OUT location_type,
   p_st_ev_no                 OUT st_ev_no_type,
   p_description              OUT description_type,
   p_group_no                 OUT group_no_type,
   p_time                     OUT timestamp_type,
   p_loader                   OUT loader_type,
   p_dep_flight               OUT flight_no_type,
   p_flight_sch_time          OUT ext_flight_sch_time_type,
   p_delta_time               OUT integer_type,
   p_stand                    OUT apron_type
)
IS
   -- get a list of bags that have not been loaded and the time
   -- between the last scan and
   -- the departure time of the associated flight is less than the
   -- time that has been supplied

   -- this cursor does not check flight access
   -- this cursor does not filter on flight
   CURSOR c1_critical_ttadmin IS
      SELECT
         MAX(tracking_log_and_bag_trace_v.timestamp) timed_at,
         helper_year(flight.flight_date||flight_sch_time) flt_date,
         tracking_log_and_bag_trace_v.bag_tag tl_bag_tag,
         tracking_log_and_bag_trace_v.pax_id tl_pax_id,
         tracking_log_and_bag_trace_v.flight_id tl_flight_id
      FROM
         tracking_log_and_bag_trace_v, flight, bag, flight_info
      WHERE
         flight.flight_id = tracking_log_and_bag_trace_v.flight_id AND
         substr(bag.bag_tag,2,9) = substr(tracking_log_and_bag_trace_v.bag_tag,2,9) AND
         bag.uld_no is NULL AND
         bag.pax_id = tracking_log_and_bag_trace_v.pax_id AND
         tracking_log_and_bag_trace_v.flight_id = flight.flight_id AND
         flight_info.flight_no = flight.flight_no AND
         flight_info.flight_date = flight.flight_date AND
         flight_info.terminal = p_terminal AND
         flight.site_id = (SELECT site_id FROM agents WHERE agent_code =
            (SELECT agent_code FROM tagtrak_users WHERE loginname = p_loginname))
      GROUP BY
         helper_year(flight.flight_date||flight_sch_time),
         tracking_log_and_bag_trace_v.bag_tag,
         tracking_log_and_bag_trace_v.pax_id,
         tracking_log_and_bag_trace_v.flight_id
      UNION ALL
      SELECT
         MAX(tracking_log_and_bag_trace_v.timestamp) timed_at,
         helper_year(flight.flight_date||flight_sch_time) flt_date,
         tracking_log_and_bag_trace_v.bag_tag tl_bag_tag, 
         tracking_log_and_bag_trace_v.pax_id tl_pax_id,
         tracking_log_and_bag_trace_v.flight_id tl_flight_id
      FROM
         tracking_log_and_bag_trace_v, flight, unknown_bag, flight_info
      WHERE
         unknown_bag.ok_to_fly = 0 AND
         flight.flight_id = tracking_log_and_bag_trace_v.flight_id AND
         substr(unknown_bag.bag_tag,2,9) = substr(tracking_log_and_bag_trace_v.bag_tag,2,9) AND
         unknown_bag.pax_id = tracking_log_and_bag_trace_v.pax_id AND
         unknown_bag.flight_id = flight.flight_id AND
         flight_info.flight_no = flight.flight_no AND
         flight_info.flight_date = flight.flight_date AND
         flight_info.terminal = p_terminal AND
         flight.site_id = (SELECT site_id FROM agents WHERE agent_code =
            (SELECT agent_code FROM tagtrak_users WHERE loginname = p_loginname))
      GROUP BY
         helper_year(flight.flight_date||flight_sch_time),
         tracking_log_and_bag_trace_v.bag_tag, 
         tracking_log_and_bag_trace_v.pax_id,
         tracking_log_and_bag_trace_v.flight_id;

   -- this cursor DOES check flight access
   -- this cursor does not filter on flight
   CURSOR c1_critical IS
      SELECT
         MAX(tracking_log_and_bag_trace_v.timestamp) timed_at,
         helper_year(flight.flight_date||flight_sch_time) flt_date,
         tracking_log_and_bag_trace_v.bag_tag tl_bag_tag, 
         tracking_log_and_bag_trace_v.pax_id tl_pax_id,
         tracking_log_and_bag_trace_v.flight_id tl_flight_id
      FROM
         tracking_log_and_bag_trace_v, flight, bag, flight_info
      WHERE
         flight.flight_id = tracking_log_and_bag_trace_v.flight_id AND
         substr(bag.bag_tag,2,9) = substr(tracking_log_and_bag_trace_v.bag_tag,2,9) AND
         bag.uld_no is NULL AND
         bag.pax_id = tracking_log_and_bag_trace_v.pax_id AND
         tracking_log_and_bag_trace_v.flight_id = flight.flight_id AND
         flight_info.flight_no = flight.flight_no AND
         flight_info.flight_date = flight.flight_date AND
         flight_info.terminal = p_terminal  AND
         flight.flight_id = (SELECT flight_id FROM flight_access WHERE
         flight.flight_id = flight_access.flight_id AND agent_code =
            (SELECT agent_code FROM tagtrak_users WHERE loginname =
               p_loginname))
      GROUP BY
         helper_year(flight.flight_date||flight_sch_time),
         tracking_log_and_bag_trace_v.bag_tag, 
         tracking_log_and_bag_trace_v.pax_id,
         tracking_log_and_bag_trace_v.flight_id
      UNION ALL
      SELECT
         MAX(tracking_log_and_bag_trace_v.timestamp) timed_at,
         helper_year(flight.flight_date||flight_sch_time) flt_date,
         tracking_log_and_bag_trace_v.bag_tag tl_bag_tag, 
         tracking_log_and_bag_trace_v.pax_id tl_pax_id,
         tracking_log_and_bag_trace_v.flight_id tl_flight_id
      FROM
         tracking_log_and_bag_trace_v, flight, unknown_bag, flight_info
      WHERE
         unknown_bag.ok_to_fly = 0 AND
         flight.flight_id = tracking_log_and_bag_trace_v.flight_id AND
         substr(unknown_bag.bag_tag,2,9) = substr(tracking_log_and_bag_trace_v.bag_tag,2,9) AND
         unknown_bag.pax_id = tracking_log_and_bag_trace_v.pax_id AND
         unknown_bag.flight_id = flight.flight_id AND
         flight_info.flight_no = flight.flight_no AND
         flight_info.flight_date = flight.flight_date AND
         flight_info.terminal = p_terminal  AND
         flight.flight_id = (SELECT flight_id FROM flight_access WHERE
         flight.flight_id = flight_access.flight_id AND agent_code =
            (SELECT agent_code FROM tagtrak_users WHERE loginname =
               p_loginname))
      GROUP BY
         helper_year(flight.flight_date||flight_sch_time),
         tracking_log_and_bag_trace_v.bag_tag, 
         tracking_log_and_bag_trace_v.pax_id,
         tracking_log_and_bag_trace_v.flight_id;

   -- this cursor does not check flight access
   -- this cursor DOES filter on flight
   CURSOR c2_critical_ttadmin IS
      SELECT
         MAX(tracking_log_and_bag_trace_v.timestamp) timed_at,
         helper_year(flight.flight_date||flight_sch_time) flt_date,
         tracking_log_and_bag_trace_v.bag_tag tl_bag_tag, 
         tracking_log_and_bag_trace_v.pax_id tl_pax_id,
         tracking_log_and_bag_trace_v.flight_id tl_flight_id
      FROM
         tracking_log_and_bag_trace_v, flight, bag, pax, flight_info
      WHERE
         flight.flight_id = tracking_log_and_bag_trace_v.flight_id AND
         substr(bag.bag_tag,2,9) = substr(tracking_log_and_bag_trace_v.bag_tag,2,9) AND
         bag.uld_no is NULL AND
         bag.pax_id = tracking_log_and_bag_trace_v.pax_id AND
         tracking_log_and_bag_trace_v.flight_id = flight.flight_id AND
         flight_info.flight_no = flight.flight_no AND
         flight_info.flight_date = flight.flight_date AND
         flight_info.terminal = p_terminal
         AND
            flight.flight_id = p_flight_id
      GROUP BY
         helper_year(flight.flight_date||flight_sch_time),
         tracking_log_and_bag_trace_v.bag_tag, 
         tracking_log_and_bag_trace_v.pax_id,
         tracking_log_and_bag_trace_v.flight_id
      UNION ALL
      SELECT
         MAX(tracking_log_and_bag_trace_v.timestamp) timed_at,
         helper_year(flight.flight_date||flight_sch_time) flt_date,
         tracking_log_and_bag_trace_v.bag_tag tl_bag_tag,
         tracking_log_and_bag_trace_v.pax_id tl_pax_id,
         tracking_log_and_bag_trace_v.flight_id tl_flight_id
      FROM
         tracking_log_and_bag_trace_v, flight, unknown_bag, flight_info
      WHERE
         unknown_bag.ok_to_fly = 0 AND
         flight.flight_id = tracking_log_and_bag_trace_v.flight_id AND
         substr(unknown_bag.bag_tag,2,9) = substr(tracking_log_and_bag_trace_v.bag_tag,2,9) AND
         unknown_bag.pax_id = tracking_log_and_bag_trace_v.pax_id AND
         unknown_bag.flight_id = flight.flight_id AND
         flight_info.flight_no = flight.flight_no AND
         flight_info.flight_date = flight.flight_date AND
         flight_info.terminal = p_terminal
         AND
            flight.flight_id = p_flight_id
      GROUP BY
         helper_year(flight.flight_date||flight_sch_time),
         tracking_log_and_bag_trace_v.bag_tag, 
         tracking_log_and_bag_trace_v.pax_id,
         tracking_log_and_bag_trace_v.flight_id;

   -- this cursor DOES check flight access
   -- this cursor DOES filter on flight
   CURSOR c2_critical IS
      SELECT
         MAX(tracking_log_and_bag_trace_v.timestamp) timed_at,
         helper_year(flight.flight_date||flight_sch_time) flt_date,
         tracking_log_and_bag_trace_v.bag_tag tl_bag_tag,  
         tracking_log_and_bag_trace_v.pax_id tl_pax_id,
         tracking_log_and_bag_trace_v.flight_id tl_flight_id
      FROM
         tracking_log_and_bag_trace_v, flight, bag, flight_info
      WHERE
         flight.flight_id = tracking_log_and_bag_trace_v.flight_id AND
         substr(bag.bag_tag,2,9) = substr(tracking_log_and_bag_trace_v.bag_tag,2,9) AND
         bag.uld_no is NULL AND
         bag.pax_id = tracking_log_and_bag_trace_v.pax_id AND
         tracking_log_and_bag_trace_v.flight_id = flight.flight_id AND
         flight_info.flight_no = flight.flight_no AND
         flight_info.flight_date = flight.flight_date AND
         flight_info.terminal = p_terminal AND
         flight.flight_id = (SELECT flight_id FROM flight_access WHERE
         flight.flight_id = flight_access.flight_id AND agent_code =
            (SELECT agent_code FROM tagtrak_users WHERE loginname =
               p_loginname))
         AND
            flight.flight_id = p_flight_id
      GROUP BY
         helper_year(flight.flight_date||flight_sch_time),
         tracking_log_and_bag_trace_v.bag_tag, 
         tracking_log_and_bag_trace_v.pax_id,
         tracking_log_and_bag_trace_v.flight_id
      UNION ALL
      SELECT
         MAX(tracking_log_and_bag_trace_v.timestamp) timed_at,
         helper_year(flight.flight_date||flight_sch_time) flt_date,
         tracking_log_and_bag_trace_v.bag_tag tl_bag_tag,
         tracking_log_and_bag_trace_v.pax_id tl_pax_id,
         tracking_log_and_bag_trace_v.flight_id tl_flight_id
      FROM
         tracking_log_and_bag_trace_v, flight, unknown_bag, flight_info
      WHERE
         unknown_bag.ok_to_fly = 0 AND
         flight.flight_id = tracking_log_and_bag_trace_v.flight_id AND
         substr(unknown_bag.bag_tag,2,9) = substr(tracking_log_and_bag_trace_v.bag_tag,2,9) AND
         unknown_bag.pax_id = tracking_log_and_bag_trace_v.pax_id AND
         unknown_bag.flight_id = flight.flight_id AND
         flight_info.flight_no = flight.flight_no AND
         flight_info.flight_date = flight.flight_date AND
         flight_info.terminal = p_terminal AND
         flight.flight_id = (SELECT flight_id FROM flight_access WHERE
         flight.flight_id = flight_access.flight_id AND agent_code =
            (SELECT agent_code FROM tagtrak_users WHERE loginname =
               p_loginname))
         AND
            flight.flight_id = p_flight_id
      GROUP BY
         helper_year(flight.flight_date||flight_sch_time),
         tracking_log_and_bag_trace_v.bag_tag, 
         tracking_log_and_bag_trace_v.pax_id,
         tracking_log_and_bag_trace_v.flight_id;

   -- loop counter
   l_row_counter     BINARY_INTEGER;

   -- local vars for the helper function
   l_pax_id          pax.pax_id%TYPE;
   l_location        tracking_log.location%TYPE;
   l_st_ev_no        tracking_log.event_no%TYPE;
   l_description     bsm_log.bsm_log%TYPE;
   l_group_no        tracking_log.group_no%TYPE;
   l_loader          tagtrak_users.loginname%TYPE;
   l_dep_flight      flight.flight_no%TYPE;
   l_flight_sch_time flight.flight_sch_time%TYPE;
   l_stand           flight_info.apron%TYPE;

BEGIN

   -- Initialise the row counter
   l_row_counter := 0;

   IF instr(p_loginname, 'TTADMIN') = 1
   THEN

      -- if they have not given us a flight_id then process all entries
      IF p_flight_id is NULL or p_flight_id = 0
      THEN
         -- loop through the tracking entries
         FOR this_row IN c1_critical_ttadmin LOOP

            -- is the last scan time less then x minutes before departure?
            IF this_row.flt_date - this_row.timed_at < p_max_time/1440
            THEN

               -- get the rest of the flight details add to the results set
               helper_critical_add_info(this_row.tl_flight_id,
                                        this_row.tl_pax_id,
                                        this_row.tl_bag_tag,
                                        this_row.timed_at,
                                        l_location,
                                        l_st_ev_no, l_description,
                                        l_group_no,
                                        l_loader, l_dep_flight,
                                        l_flight_sch_time, l_stand);

               -- increment the row count
               l_row_counter := l_row_counter + 1;

               -- now add the data to the return arrays
               p_bag_tag(l_row_counter) := TRIM(get_user_bag_tag_no_exceptions(this_row.tl_bag_tag, p_user_pref));
               p_pax_id(l_row_counter) := TRIM(this_row.tl_pax_id);
               p_location(l_row_counter) := TRIM(l_location);
               p_st_ev_no(l_row_counter) := TRIM(l_st_ev_no);
               p_description(l_row_counter) := TRIM(l_description);
               p_group_no(l_row_counter) := TRIM(l_group_no);
               p_time(l_row_counter) := this_row.timed_at;
               p_loader(l_row_counter) := TRIM(l_loader);
               p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
               p_flight_sch_time(l_row_counter) :=
                  TRIM(TO_CHAR(this_row.flt_date,'HH24MI (DDMON)'));
               p_delta_time(l_row_counter) := (this_row.flt_date - this_row.timed_at) * 1440;
               p_stand(l_row_counter) := TRIM(l_stand);

            END IF; -- end of time is less than supplied time

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c1_critical_ttadmin

      ELSE -- we are to filter on the flight_id

         -- loop through the tracking entries
         FOR this_row IN c2_critical_ttadmin LOOP

            -- is the last scan time less then x minutes before departure?
            IF this_row.flt_date - this_row.timed_at < p_max_time/1440
            THEN

               -- get the rest of the flight details add to the results set
               helper_critical_add_info(this_row.tl_flight_id,
                                        this_row.tl_pax_id,
                                        this_row.tl_bag_tag,
                                        this_row.timed_at,
                                        l_location,
                                        l_st_ev_no, l_description,
                                        l_group_no,
                                        l_loader, l_dep_flight,
                                        l_flight_sch_time, l_stand);

               -- increment the row count
               l_row_counter := l_row_counter + 1;

               -- now add the data to the return arrays
               p_bag_tag(l_row_counter) := TRIM(get_user_bag_tag_no_exceptions(this_row.tl_bag_tag, p_user_pref));
               p_pax_id(l_row_counter) := TRIM(this_row.tl_pax_id);
               p_location(l_row_counter) := TRIM(l_location);
               p_st_ev_no(l_row_counter) := TRIM(l_st_ev_no);
               p_description(l_row_counter) := TRIM(l_description);
               p_group_no(l_row_counter) := TRIM(l_group_no);
               p_time(l_row_counter) := this_row.timed_at;
               p_loader(l_row_counter) := TRIM(l_loader);
               p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
               p_flight_sch_time(l_row_counter) := TRIM(l_flight_sch_time);
               p_delta_time(l_row_counter) := (this_row.flt_date - this_row.timed_at) * 1440;
               p_stand(l_row_counter) := TRIM(l_stand);

            END IF; -- end of time is less than supplied time

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c2_critical_ttadmin

      END IF; -- end of are we to filter on flight_id

   ELSE -- we are not TTADMIN and have to check flight access

      -- if they have not given us a flight_id then process all entries
      IF p_flight_id is NULL or p_flight_id = 0
      THEN
         -- loop through the tracking entries
         FOR this_row IN c1_critical LOOP

            -- is the last scan time less then x minutes before departure?
            IF this_row.flt_date - this_row.timed_at < p_max_time/1440
            THEN

               -- get the rest of the flight details add to the results set
               helper_critical_add_info(this_row.tl_flight_id,
                                        this_row.tl_pax_id,
                                        this_row.tl_bag_tag,
                                        this_row.timed_at,
                                        l_location,
                                        l_st_ev_no, l_description,
                                        l_group_no,
                                        l_loader, l_dep_flight,
                                        l_flight_sch_time, l_stand);

               -- increment the row count
               l_row_counter := l_row_counter + 1;

               -- now add the data to the return arrays
               p_bag_tag(l_row_counter) := TRIM(get_user_bag_tag_no_exceptions(this_row.tl_bag_tag, p_user_pref));
               p_pax_id(l_row_counter) := TRIM(this_row.tl_pax_id);
               p_location(l_row_counter) := TRIM(l_location);
               p_st_ev_no(l_row_counter) := TRIM(l_st_ev_no);
               p_description(l_row_counter) := TRIM(l_description);
               p_group_no(l_row_counter) := TRIM(l_group_no);
               p_time(l_row_counter) := this_row.timed_at;
               p_loader(l_row_counter) := TRIM(l_loader);
               p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
               p_flight_sch_time(l_row_counter) :=
                  TRIM(TO_CHAR(this_row.flt_date,'HH24MI (DDMON)'));
               p_delta_time(l_row_counter) := (this_row.flt_date - this_row.timed_at) * 1440;
               p_stand(l_row_counter) := TRIM(l_stand);

            END IF; -- end of time is less than supplied time

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c1_critical

      ELSE -- we are to filter on the flight_id

         -- loop through the tracking entries
         FOR this_row IN c2_critical LOOP

            -- is the last scan time less then x minutes before departure?
            IF this_row.flt_date - this_row.timed_at < p_max_time/1440
            THEN

               -- get the rest of the flight details add to the results set
               helper_critical_add_info(this_row.tl_flight_id,
                                        this_row.tl_pax_id,
                                        this_row.tl_bag_tag,
                                        this_row.timed_at,
                                        l_location,
                                        l_st_ev_no, l_description,
                                        l_group_no,
                                        l_loader, l_dep_flight,
                                        l_flight_sch_time, l_stand);

               -- increment the row count
               l_row_counter := l_row_counter + 1;

               -- now add the data to the return arrays
               p_bag_tag(l_row_counter) := TRIM(get_user_bag_tag_no_exceptions(this_row.tl_bag_tag, p_user_pref));
               p_pax_id(l_row_counter) := TRIM(this_row.tl_pax_id);
               p_location(l_row_counter) := TRIM(l_location);
               p_st_ev_no(l_row_counter) := TRIM(l_st_ev_no);
               p_description(l_row_counter) := TRIM(l_description);
               p_group_no(l_row_counter) := TRIM(l_group_no);
               p_time(l_row_counter) := this_row.timed_at;
               p_loader(l_row_counter) := TRIM(l_loader);
               p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
               p_flight_sch_time(l_row_counter) := TRIM(l_flight_sch_time);
               p_delta_time(l_row_counter) := (this_row.flt_date - this_row.timed_at) * 1440;
               p_stand(l_row_counter) := TRIM(l_stand);

            END IF; -- end of time is less than supplied time

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c2_critical

      END IF; -- end of are we to filter on flight_id

   END IF; -- end of are we are TTADMIN user or not

END get_critical_track;

/******************************************************************************
 *
 * FUNCTION GET_CRITICAL_TRACK_2
 *
 ******************************************************************************/

FUNCTION get_critical_track_2(p_terminal   IN flight_info.terminal%TYPE,
                              p_max_time   IN char,
                              p_loginname  IN tagtrak_users.loginname%TYPE,
                              p_flight_id  IN flight.flight_id%TYPE,
                              p_user_pref  IN VARCHAR2) RETURN SYS_REFCURSOR IS

  l_bag_tag          bag_tag_type;
  l_pax_id           pax_id_type;
  l_location         location_type;
  l_st_ev_no         st_ev_no_type;
  l_description      description_type;
  l_group_no         group_no_type;
  l_time             timestamp_type;
  l_loader           loader_type;
  l_dep_flight       flight_no_type;
  l_flight_sch_time  ext_flight_sch_time_type;
  l_delta_time       integer_type;
  l_stand            apron_type;

  l_results_tab      critical_track_tab := critical_track_tab();
  l_cursor           SYS_REFCURSOR;

BEGIN

  get_critical_track(p_terminal,
                     p_max_time,
                     p_loginname,
                     p_flight_id,
                     p_user_pref,
                     l_bag_tag,
                     l_pax_id,
                     l_location,
                     l_st_ev_no,
                     l_description,
                     l_group_no,
                     l_time,
                     l_loader,
                     l_dep_flight,
                     l_flight_sch_time,
                     l_delta_time,
                     l_stand);

   FOR n IN nvl(l_bag_tag.FIRST,1)..nvl(l_bag_tag.LAST,0) LOOP

     BEGIN

       --make room in sql table.
       l_results_tab.extend(1);
       --use constructor to populate.
       l_results_tab(n) := critical_track_rec(l_bag_tag(n),
                                              l_pax_id(n),
                                              l_location(n),
                                              l_st_ev_no(n),
                                              l_description(n),
                                              l_group_no(n),
                                              l_time(n),
                                              l_loader(n),
                                              l_dep_flight(n),
                                              l_flight_sch_time(n),
                                              l_delta_time(n),
                                              l_stand(n));

    EXCEPTION WHEN no_data_found THEN
      NULL; --don't worry about gaps in the sql tables.

    END;

  END LOOP;

  --Build ref cursor from sql table.
  OPEN l_cursor FOR
  SELECT *
  FROM TABLE(CAST(l_results_tab AS critical_track_tab));

  RETURN l_cursor;

END get_critical_track_2;


/******************************************************************************
 *
 * PROCEDURE GET_MISSED_TRACK
 *
 ******************************************************************************/

PROCEDURE get_missed_track
(
   p_loginname                IN  tagtrak_users.loginname%TYPE,
   p_flight_id                IN  flight.flight_id%TYPE,
   p_inc_unpos_ulds           IN  INTEGER, -- 1 if ULDs do not have to be positioned
                                           -- 0 if they do
   p_user_pref                IN  VARCHAR2,                                                        -- UAS/FALCP/CA/00571
   p_bag_tag                  OUT bag_tag_type,
   p_pax_id                   OUT pax_id_type,
   p_location                 OUT location_type,
   p_st_ev_no                 OUT st_ev_no_type,
   p_description              OUT description_type,
   p_group_no                 OUT group_no_type,
   p_time                     OUT timestamp_type,
   p_loader                   OUT loader_type,
   p_dep_flight               OUT flight_no_type,
   p_flight_sch_time          OUT ext_flight_sch_time_type
)
IS
   -- get a list of bags that have not been loaded and the flight has departed

   -- this cursor does not check flight access
   -- this cursor does not filter on flight
   CURSOR c1_missed_track_ttadmin IS
      SELECT
         flight_sch_time||' ('||flight_date||')' combo_flight_sch_time,
         get_user_bag_tag_no_exceptions(bag.bag_tag, p_user_pref) tl_bag_tag, pax.pax_id tl_pax_id, flight.flight_id tl_flight_id 
      FROM
         flight, bag, pax, ulds
      WHERE
         bag.pax_id = pax.pax_id AND
         pax.flight_id = flight.flight_id AND
         flight.flight_status = 'PD' AND
         bag.delete_marker = 0 AND
         flight.site_id = (SELECT site_id FROM agents WHERE agent_code =
         (SELECT agent_code FROM tagtrak_users WHERE loginname = p_loginname)) AND
         bag.uld_no = ulds.uld_no(+) AND      
         (
            (
               p_inc_unpos_ulds = 0 AND
               bag.uld_no IS NULL
            )
            OR
            (
               p_inc_unpos_ulds = 1 AND
               (
                  (
                     bag.uld_no IS NULL
                  )
                  OR
                  (
                     bag.uld_no IS NOT NULL AND
                     flight.flight_id = ulds.flight_id AND
                     ulds.uld_loaded_status = 'N'
                  )
               )
            )
         );

   -- this cursor DOES check flight access
   -- this cursor does not filter on flight
   CURSOR c1_missed_track IS
      SELECT
         flight_sch_time||' ('||flight_date||')' combo_flight_sch_time,
         get_user_bag_tag_no_exceptions(bag.bag_tag, p_user_pref) tl_bag_tag, pax.pax_id tl_pax_id, flight.flight_id tl_flight_id 
      FROM
         flight, bag, pax, ulds
      WHERE
         bag.pax_id = pax.pax_id AND
         pax.flight_id = flight.flight_id AND
         flight.flight_status = 'PD' AND
         bag.delete_marker = 0 AND
         flight.flight_id = (SELECT flight_id FROM flight_access WHERE
         flight.flight_id = flight_access.flight_id AND agent_code =
            (SELECT agent_code FROM tagtrak_users WHERE loginname =
               p_loginname)) AND
         bag.uld_no = ulds.uld_no(+) AND
         (
            (
               p_inc_unpos_ulds = 0 AND
               bag.uld_no IS NULL
            )
            OR
            (
               p_inc_unpos_ulds = 1 AND
               (
                  (
                     bag.uld_no IS NULL
                  )
                  OR
                  (
                     bag.uld_no IS NOT NULL AND
                     flight.flight_id = ulds.flight_id AND
                     ulds.uld_loaded_status = 'N'
                  )
               )
            )
         );

   -- this cursor does not check flight access
   -- this cursor DOES filter on flight
   CURSOR c2_missed_track_ttadmin IS
      SELECT
         flight_sch_time combo_flight_sch_time,
         get_user_bag_tag_no_exceptions(bag.bag_tag, p_user_pref) tl_bag_tag, pax.pax_id tl_pax_id, flight.flight_id tl_flight_id 
      FROM
         flight, bag, pax, ulds
      WHERE
         flight.flight_id = p_flight_id AND
         bag.pax_id = pax.pax_id AND
         pax.flight_id = flight.flight_id AND
         flight.flight_status = 'PD' AND
         bag.delete_marker = 0 AND
         bag.uld_no = ulds.uld_no(+) AND
         (
            (
               p_inc_unpos_ulds = 0 AND
               bag.uld_no IS NULL
            )
            OR
            (
               p_inc_unpos_ulds = 1 AND
               (
                  (
                     bag.uld_no IS NULL
                  )
                  OR
                  (
                     bag.uld_no IS NOT NULL AND
                     flight.flight_id = ulds.flight_id AND
                     ulds.uld_loaded_status = 'N'
                  )
               )
            )
         );

   -- this cursor DOES check flight access
   -- this cursor DOES filter on flight
   CURSOR c2_missed_track IS
      SELECT
         flight_sch_time combo_flight_sch_time,
         get_user_bag_tag_no_exceptions(bag.bag_tag, p_user_pref) tl_bag_tag, pax.pax_id tl_pax_id, flight.flight_id tl_flight_id 
      FROM
         flight, bag, pax, ulds
      WHERE
         flight.flight_id = p_flight_id AND
         bag.pax_id = pax.pax_id AND
         pax.flight_id = flight.flight_id AND
         flight.flight_status = 'PD' AND
         bag.delete_marker = 0 AND
         flight.flight_id = (SELECT flight_id FROM flight_access WHERE
         flight.flight_id = flight_access.flight_id AND agent_code =
            (SELECT agent_code FROM tagtrak_users WHERE loginname =
               p_loginname)) AND
         bag.uld_no = ulds.uld_no(+) AND
         (
            (
               p_inc_unpos_ulds = 0 AND
               bag.uld_no IS NULL
            )
            OR
            (
               p_inc_unpos_ulds = 1 AND
               (
                  (
                     bag.uld_no IS NULL
                  )
                  OR
                  (
                     bag.uld_no IS NOT NULL AND
                     flight.flight_id = ulds.flight_id AND
                     ulds.uld_loaded_status = 'N'
                  )
               )
            )
         );

   -- loop counter
   l_row_counter     BINARY_INTEGER;

   -- local vars for the helper function
   l_location        tracking_log.location%TYPE;
   l_st_ev_no        tracking_log.event_no%TYPE;
   l_description     bsm_log.bsm_log%TYPE;
   l_group_no        tracking_log.group_no%TYPE;
   l_loader          tagtrak_users.loginname%TYPE;
   l_dep_flight      flight.flight_no%TYPE;
   l_flight_sch_time flight.flight_sch_time%TYPE;
   l_stand           flight_info.apron%TYPE;
   l_timed_at        tracking_log.timestamp%TYPE;

BEGIN

   -- Initialise the row counter
   l_row_counter := 0;

   IF instr(p_loginname, 'TTADMIN') = 1
   THEN

      -- if they have not given us a flight_id then process all entries
      IF p_flight_id is NULL or p_flight_id = 0
      THEN
         -- loop through the tracking entries
         FOR this_row IN c1_missed_track_ttadmin LOOP

            -- get the latest timestamp for this bag
            helper_max_log_time (this_row.tl_pax_id, this_row.tl_bag_tag, l_timed_at);

            -- get the rest of the flight details add to the results set
            -- reuse the function from the critical track stored procedure
            helper_critical_add_info(this_row.tl_flight_id,
                                     this_row.tl_pax_id, 
                                     this_row.tl_bag_tag, 
                                     l_timed_at,
                                     l_location, l_st_ev_no,
                                     l_description, l_group_no,
                                     l_loader, l_dep_flight, l_flight_sch_time,
                                     l_stand);

            -- increment the row count
            l_row_counter := l_row_counter + 1;

            -- now add the data to the return arrays
            p_bag_tag(l_row_counter) := TRIM(this_row.tl_bag_tag);
            p_pax_id(l_row_counter) := TRIM(this_row.tl_pax_id);
            p_location(l_row_counter) := TRIM(l_location);
            p_st_ev_no(l_row_counter) := TRIM(l_st_ev_no);
            p_description(l_row_counter) := TRIM(l_description);
            p_group_no(l_row_counter) := TRIM(l_group_no);
            p_time(l_row_counter) := l_timed_at;
            p_loader(l_row_counter) := TRIM(l_loader);
            p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
            p_flight_sch_time(l_row_counter) :=
               TRIM(this_row.combo_flight_sch_time);

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c1_missed_track_ttadmin

      ELSE -- we are to filter on the flight_id

         -- loop through the tracking entries
         FOR this_row IN c2_missed_track_ttadmin LOOP

            -- get the latest timestamp for this bag
            helper_max_log_time (this_row.tl_pax_id, this_row.tl_bag_tag, l_timed_at);

            -- get the rest of the flight details add to the results set
            -- reuse the function from the critical track stored procedure
            helper_critical_add_info(this_row.tl_flight_id, 
                                     this_row.tl_pax_id,
                                     this_row.tl_bag_tag,
                                     l_timed_at,
                                     l_location, l_st_ev_no,
                                     l_description, l_group_no,
                                     l_loader, l_dep_flight, l_flight_sch_time,
                                     l_stand);

            -- increment the row count
            l_row_counter := l_row_counter + 1;

            -- now add the data to the return arrays
            p_bag_tag(l_row_counter) := TRIM(this_row.tl_bag_tag);
            p_pax_id(l_row_counter) := TRIM(this_row.tl_pax_id);
            p_location(l_row_counter) := TRIM(l_location);
            p_st_ev_no(l_row_counter) := TRIM(l_st_ev_no);
            p_description(l_row_counter) := TRIM(l_description);
            p_group_no(l_row_counter) := TRIM(l_group_no);
            p_time(l_row_counter) := l_timed_at;
            p_loader(l_row_counter) := TRIM(l_loader);
            p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
            p_flight_sch_time(l_row_counter) :=
               TRIM(this_row.combo_flight_sch_time);

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c2_missed_track_ttadmin

      END IF; -- end of are we to filter on flight_id

   ELSE -- we are not TTADMIN and have to check flight access

      -- if they have not given us a flight_id then process all entries
      IF p_flight_id is NULL or p_flight_id = 0
      THEN
         -- loop through the tracking entries
         FOR this_row IN c1_missed_track LOOP

            -- get the latest timestamp for this bag
            helper_max_log_time (this_row.tl_pax_id, this_row.tl_bag_tag, l_timed_at);

            -- get the rest of the flight details add to the results set
            -- reuse the function from the critical track stored procedure
            helper_critical_add_info(this_row.tl_flight_id,
                                     this_row.tl_pax_id, 
                                     this_row.tl_bag_tag,
                                     l_timed_at,
                                     l_location, l_st_ev_no,
                                     l_description, l_group_no,
                                     l_loader, l_dep_flight, l_flight_sch_time,
                                     l_stand);

            -- increment the row count
            l_row_counter := l_row_counter + 1;

            -- now add the data to the return arrays
            p_bag_tag(l_row_counter) := TRIM(this_row.tl_bag_tag);
            p_pax_id(l_row_counter) := TRIM(this_row.tl_pax_id);
            p_location(l_row_counter) := TRIM(l_location);
            p_st_ev_no(l_row_counter) := TRIM(l_st_ev_no);
            p_description(l_row_counter) := TRIM(l_description);
            p_group_no(l_row_counter) := TRIM(l_group_no);
            p_time(l_row_counter) := l_timed_at;
            p_loader(l_row_counter) := TRIM(l_loader);
            p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
            p_flight_sch_time(l_row_counter) :=
               TRIM(this_row.combo_flight_sch_time);

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c1_missed_track

      ELSE -- we are to filter on the flight_id

         -- loop through the tracking entries
         FOR this_row IN c2_missed_track LOOP

            -- get the latest timestamp for this bag
            helper_max_log_time (this_row.tl_pax_id, get_numeric_bag_tag(this_row.tl_bag_tag), l_timed_at);

            -- get the rest of the flight details add to the results set
            -- reuse the function from the critical track stored procedure
            helper_critical_add_info(this_row.tl_flight_id,
                                     this_row.tl_pax_id, 
                                     this_row.tl_bag_tag,
                                     l_timed_at,
                                     l_location, l_st_ev_no,
                                     l_description, l_group_no,
                                     l_loader, l_dep_flight, l_flight_sch_time,
                                     l_stand);

            -- increment the row count
            l_row_counter := l_row_counter + 1;

            -- now add the data to the return arrays
            p_bag_tag(l_row_counter) := TRIM(this_row.tl_bag_tag);
            p_pax_id(l_row_counter) := TRIM(this_row.tl_pax_id);
            p_location(l_row_counter) := TRIM(l_location);
            p_st_ev_no(l_row_counter) := TRIM(l_st_ev_no);
            p_description(l_row_counter) := TRIM(l_description);
            p_group_no(l_row_counter) := TRIM(l_group_no);
            p_time(l_row_counter) := l_timed_at;
            p_loader(l_row_counter) := TRIM(l_loader);
            p_dep_flight(l_row_counter) := TRIM(l_dep_flight);
            p_flight_sch_time(l_row_counter) :=
               TRIM(this_row.combo_flight_sch_time);

            -- ensure we do not return too much data to the MWS
            EXIT WHEN l_row_counter = MAX_ROWS;

         END LOOP; -- end of looping through cursor c2_missed_track

      END IF; -- end of are we to filter on flight_id

   END IF; -- end of are we are TTADMIN user or not

END get_missed_track;

/******************************************************************************
 *
 * FUNCTION GET_MISSED_TRACK_2
 *
 ******************************************************************************/

FUNCTION get_missed_track_2(p_loginname       IN tagtrak_users.loginname%TYPE,
                            p_flight_id       IN flight.flight_id%TYPE,
                            p_inc_unpos_ulds  IN INTEGER,
                            p_user_pref       IN VARCHAR2) RETURN SYS_REFCURSOR IS

  l_bag_tag          bag_tag_type;
  l_pax_id           pax_id_type;
  l_location         location_type;
  l_st_ev_no         st_ev_no_type;
  l_description      description_type;
  l_group_no         group_no_type;
  l_time             timestamp_type;
  l_loader           loader_type;
  l_dep_flight       flight_no_type;
  l_flight_sch_time  ext_flight_sch_time_type;

  l_results_tab      missed_track_tab := missed_track_tab();
  l_cursor           SYS_REFCURSOR;

BEGIN

  get_missed_track(p_loginname,
                   p_flight_id,
                   p_inc_unpos_ulds,
                   p_user_pref,
                   l_bag_tag,
                   l_pax_id,
                   l_location,
                   l_st_ev_no,
                   l_description,
                   l_group_no,
                   l_time,
                   l_loader,
                   l_dep_flight,
                   l_flight_sch_time);


   FOR n IN nvl(l_bag_tag.FIRST, 1)..nvl(l_bag_tag.LAST, 0) LOOP

     BEGIN

       --make room in sql table.
       l_results_tab.extend(1);
       --use constructor to populate.
       l_results_tab(n) := missed_track_rec(l_bag_tag(n),
                                            l_pax_id(n),
                                            l_location(n),
                                            l_st_ev_no(n),
                                            l_description(n),
                                            l_group_no(n),
                                            l_time(n),
                                            l_loader(n),
                                            l_dep_flight(n),
                                            l_flight_sch_time(n));

    EXCEPTION WHEN no_data_found THEN
      NULL; --don't worry about gaps in the sql tables.

    END;

  END LOOP;

  --Build ref cursor from sql table.
  OPEN l_cursor FOR
  SELECT *
  FROM TABLE(CAST(l_results_tab AS missed_track_tab));

  RETURN l_cursor;

END get_missed_track_2;

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

FUNCTION last_seen_location(p_pax_id  pax.pax_id%TYPE,
                            p_bag_tag bag.bag_tag%TYPE)
RETURN location.location_code%TYPE IS

  l_retval location.location_code%TYPE;

BEGIN

  BEGIN

    SELECT location TIMESTAMP INTO l_retval
    FROM (SELECT location
    FROM tracking_log_and_bag_trace_v tv
    WHERE SUBSTR(tv.bag_tag,2,9) = SUBSTR(p_bag_tag,2,9)
    AND tv.pax_id = p_pax_id
    AND tv.location NOT LIKE 'AU%'
    AND tv.description NOT LIKE 'BPM%' --  CA2627
    ORDER BY TIMESTAMP DESC)
    WHERE ROWNUM = 1;

  EXCEPTION WHEN no_data_found THEN

    NULL;

  END;

  RETURN l_retval;

END last_seen_location;

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

FUNCTION last_seen_time(p_pax_id  pax.pax_id%TYPE,
                        p_bag_tag bag.bag_tag%TYPE)
RETURN DATE IS

  l_retval DATE;

BEGIN

  BEGIN

    SELECT timestamp INTO l_retval
    FROM (SELECT timestamp
    FROM tracking_log_and_bag_trace_v tv
    WHERE SUBSTR(tv.bag_tag,2,9) = SUBSTR(p_bag_tag,2,9)
    AND tv.pax_id = p_pax_id
    AND tv.location NOT LIKE 'AU%'
    AND tv.description NOT LIKE 'BPM%'  -- CA2627
    ORDER BY timestamp DESC)
    WHERE ROWNUM = 1;

  EXCEPTION WHEN no_data_found THEN

    NULL;

  END;

  RETURN l_retval;

END last_seen_time;

END mws_tracking;
/

