/*****************************************************************************
*
* Filename         - $Workfile:   mws_web_spec.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       -
*
* Design Spec
* Section          -
*
* Contents         - Functions and procedures used by the MWS
*
* Language         - Oracle PL/SQL
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2007
*
*****************************************************************************/

PROMPT CREATE OR REPLACE PACKAGE live.mws_web

CREATE OR REPLACE PACKAGE MWS_WEB AS

  SUBTYPE error_msg_type      IS VARCHAR2(512);
  SUBTYPE error_num_type      IS NUMBER;
  SUBTYPE parameter_string    IS error_msg_type;

  dd_mon_yyyy  VARCHAR2(11);
  SUBTYPE date_string IS dd_mon_yyyy%TYPE;

  bpm_mode    BOOLEAN     DEFAULT TRUE;

-------------------------------------------------------------------------------------
-- FAS/FALCP/CA/1215  - BPM Functionality
-------------------------------------------------------------------------------------
FUNCTION bpm1_required( p_pax_id     IN bag.pax_id%TYPE
                       ,p_bag_tag2a  IN bag.bag_tag2a%TYPE
                       ,p_bag_tag2b  IN bag.bag_tag2b%TYPE
                       ,p_bag_tag3   IN bag.bag_tag3%TYPE
                        ) RETURN BOOLEAN;

FUNCTION bpm2_required( p_flight_id  IN flight.flight_id%TYPE
             ,p_bpm_type             IN varchar2
                        ) RETURN BOOLEAN;

FUNCTION f_bpm_type
   (p_action          IN bag_trace.trace_action%TYPE
   ,p_pax_sequence_no IN pax.pax_sequence_no%TYPE DEFAULT NULL
   ,p_uld_no          IN ulds.uld_no%TYPE DEFAULT NULL
    ) RETURN mail_indiv.msg_type%TYPE;

FUNCTION f_pax_sequence_no
  (p_pax_id          IN     pax.pax_id%TYPE
  ,p_flight_id       IN     flight.flight_id%TYPE
  ) RETURN pax.pax_sequence_no%TYPE;

FUNCTION store_bpm1
  (p_location_code   IN   location.location_code%TYPE
  ,p_user_id         IN   tagtrak_users.user_id%TYPE
  ,p_flight_id       IN   flight.flight_id%TYPE
  ,p_pax_id          IN   pax.pax_id%TYPE
  ,p_bag_tag         IN   bag.bag_tag%TYPE
  ,p_pax_sequence_no IN   pax.pax_sequence_no%TYPE
  ,p_action          IN   bag_trace.trace_action%TYPE
  ,p_process_name    IN   system_cfg.process_name%TYPE DEFAULT '-'
  ,p_unit_name       IN   system_cfg.unit_name%TYPE DEFAULT '-'
  ,p_dcsid           IN   system_cfg.dcsid%TYPE DEFAULT '-'
  ,p_subunit_name    IN   system_cfg.subunit_name%TYPE  DEFAULT '-'
  ) RETURN BOOLEAN;


PROCEDURE store_bpm2
  (p_location_code     IN   location.location_code%TYPE
  ,p_user_id           IN   tagtrak_users.user_id%TYPE
  ,p_flight_id         IN   flight.flight_id%TYPE
  ,p_action            IN   bag_trace.trace_action%TYPE
  ,p_source_uld_no     IN   ulds.uld_no%TYPE
  ,p_destination_uld_no IN  ulds.uld_no%TYPE
  ,p_bpmid             IN   bpm_data.bpmid%TYPE
  ,p_scanner_id        IN   kit.scanner_id%TYPE DEFAULT '0'
  ,p_pax_id            IN   pax.pax_id%TYPE
  ,p_process_name      IN   system_cfg.process_name%TYPE DEFAULT '-'
  ,p_unit_name         IN   system_cfg.unit_name%TYPE DEFAULT '-'
  ,p_dcsid             IN   system_cfg.dcsid%TYPE DEFAULT '-'
  ,p_subunit_name      IN   system_cfg.subunit_name%TYPE DEFAULT '-'
  );

-------------------------------------------------------------------------------------
-- FAS/FALCP/CA/00581 - Nigel  06/04/04  make this previously private function public
-------------------------------------------------------------------------------------
FUNCTION f_pax_id
        (p_flight_id flight.flight_id%TYPE
        ,p_bag_tag   bag.bag_tag%TYPE) RETURN pax.pax_id%TYPE;

--------------------------------------------------------------------------------------------------------------------------
-- FAS/FALCP/CA/00581 - Nigel  06/04/04  add overloaded version of previous function to deal with RUSH (or GATE/CREW) bags
--------------------------------------------------------------------------------------------------------------------------
FUNCTION f_pax_id
        (p_flight_id flight.flight_id%TYPE
        ,p_bag_tag   bag.bag_tag%TYPE
        ,p_bag_type  bag.bag_exception%TYPE) RETURN pax.pax_id%TYPE;

FUNCTION scanner_privilege
  (p_app_priv_name IN app_priv.app_priv_name%TYPE) RETURN BOOLEAN;

PRAGMA RESTRICT_REFERENCES (scanner_privilege, WNDS, WNPS);

FUNCTION jam_privilege
        (p_app_priv_code IN app_priv.app_priv_code%TYPE) RETURN app_priv.app_priv_code%TYPE;

PRAGMA RESTRICT_REFERENCES (jam_privilege, WNDS, WNPS);

PROCEDURE mws_load_a_bag
  (p_location_code    IN    location.location_code%TYPE
  ,p_user_id          IN    tagtrak_users.user_id%TYPE
  ,p_flight_id        IN    flight.flight_id%TYPE
  ,p_uld_no           IN    ulds.uld_no%TYPE
  ,p_bag_tag          IN    bag.bag_tag%TYPE
  ,p_serial_no        IN    kit.serial_number%TYPE
  ,p_error_message    OUT   error_msg_type
  ,p_error_number     OUT   error_num_type
  );

PROCEDURE load_a_bag
  (p_location_code    IN    location.location_code%TYPE
  ,p_user_id          IN    tagtrak_users.user_id%TYPE
  ,p_flight_id        IN    flight.flight_id%TYPE
  ,p_uld_no           IN    ulds.uld_no%TYPE
  ,p_bag_tag          IN    bag.bag_tag%TYPE
  ,p_serial_no        IN    kit.serial_number%TYPE
  ,p_error_message    OUT   error_msg_type
  ,p_error_number     OUT   error_num_type
  );

PROCEDURE unload_a_bag
  (p_location_code    IN    location.location_code%TYPE
  ,p_user_id          IN    tagtrak_users.user_id%TYPE
  ,p_flight_id        IN    flight.flight_id%TYPE
  ,p_bag_tag          IN    bag.bag_tag%TYPE
  ,p_serial_no        IN    kit.serial_number%TYPE
  ,p_error_message    OUT   error_msg_type
  ,p_error_number     OUT   error_num_type
  );

PROCEDURE stop_bags
        (p_location_code        IN          location.location_code%TYPE
        ,p_user_id              IN          tagtrak_users.user_id%TYPE
        ,p_flight_id            IN          flight.flight_id%TYPE
        ,p_pax_id               IN          pax.pax_id%TYPE
        ,p_serial_no            IN          kit.serial_number%TYPE
        ,p_error_message        OUT         error_msg_type
        ,p_error_number         OUT         error_num_type
        );

PROCEDURE clear_bags
        (p_location_code        IN          location.location_code%TYPE
        ,p_user_id              IN          tagtrak_users.user_id%TYPE
        ,p_flight_id            IN          flight.flight_id%TYPE
        ,p_pax_id               IN          pax.pax_id%TYPE
        ,p_serial_no            IN          kit.serial_number%TYPE
        ,p_error_message        OUT         error_msg_type
        ,p_error_number         OUT         error_num_type
        );


PROCEDURE transfer_bag
        (p_location_code    IN    location.location_code%TYPE
        ,p_user_id          IN    tagtrak_users.user_id%TYPE
        ,p_flight_id        IN    flight.flight_id%TYPE
        ,p_bag_tag          IN    bag.bag_tag%TYPE
        ,p_source_uld_no    IN    ulds.uld_no%TYPE
        ,p_destination_uld_no IN  ulds.uld_no%TYPE
        ,p_serial_no        IN    kit.serial_number%TYPE
        ,p_error_message    OUT   error_msg_type
        ,p_error_number     OUT   error_num_type
        );

PROCEDURE transfer_uld
        (p_location_code            IN          location.location_code%TYPE
        ,p_user_id                  IN          tagtrak_users.user_id%TYPE
        ,p_flight_id                IN          flight.flight_id%TYPE
        ,p_source_uld_no            IN          ulds.uld_no%TYPE
        ,p_destination_uld_no       IN          ulds.uld_no%TYPE
        ,p_serial_no                IN          kit.serial_number%TYPE
        ,p_error_message            OUT         error_msg_type
        ,p_error_number             OUT         error_num_type
        );

PROCEDURE add_user
        (p_user_id              IN          tagtrak_users.user_id%TYPE
        ,p_loginname            IN          tagtrak_users.loginname%TYPE
        ,p_firstname            IN          tagtrak_users.firstname%TYPE
        ,p_surname              IN          tagtrak_users.surname%TYPE
        ,p_tagtrak_password     IN          tagtrak_users.tagtrak_password%TYPE
        ,p_group_name           IN          tagtrak_users.group_name%TYPE
        ,p_agent_code           IN          tagtrak_users.agent_code%TYPE
        ,p_error_message        OUT         error_msg_type
        ,p_error_number         OUT         error_num_type
        );

PROCEDURE retire_user
        (p_last_user            IN          tagtrak_users.user_id%TYPE
        ,p_user_id              IN          tagtrak_users.user_id%TYPE
        ,p_error_message        OUT         error_msg_type
        ,p_error_number         OUT         error_num_type
        );

PROCEDURE unretire_user
        (p_last_user            IN          tagtrak_users.user_id%TYPE
        ,p_user_id              IN          tagtrak_users.user_id%TYPE
        ,p_error_message        OUT         error_msg_type
        ,p_error_number         OUT         error_num_type
        );

PROCEDURE unlock_user
        (p_last_user            IN          tagtrak_users.user_id%TYPE
        ,p_user_id              IN          tagtrak_users.user_id%TYPE
        ,p_error_message        OUT         error_msg_type
        ,p_error_number         OUT         error_num_type
        );

PROCEDURE change_group_name
        (p_last_user          IN      tagtrak_users.user_id%TYPE
        ,p_user_id            IN      tagtrak_users.user_id%TYPE
        ,p_group_name         IN      tagtrak_users.group_name%TYPE
        ,p_error_message      OUT     error_msg_type
        ,p_error_number       OUT     error_num_type
        );

PROCEDURE add_user_group
        (p_last_user            IN          user_group.last_user%TYPE
        ,p_group_name           IN          user_group.group_name%TYPE
        ,p_agent_code           IN          user_group.agent_code%TYPE
        ,p_error_message        OUT         error_msg_type
        ,p_error_number         OUT         error_num_type
        );

PROCEDURE add_a_flight
        (p_flight_no          IN      flight.flight_no%TYPE
        ,p_flight_date        IN      flight.flight_date%TYPE
        ,p_flight_sch_time    IN   flight.flight_sch_time%TYPE
        ,p_agent_code         IN      agents.agent_code%TYPE
        ,p_flight_destination IN      VARCHAR2
        ,p_user_id            IN      tagtrak_users.user_id%TYPE
        ,p_serial_no          IN      kit.serial_number%TYPE
        ,p_error_message      OUT     error_msg_type
        ,p_error_number       OUT     error_num_type
        );

PROCEDURE add_a_flight_2
  (p_flight_no          IN     flight.flight_no%TYPE
  ,p_flight_date        IN     flight.flight_date%TYPE
  ,p_flight_sch_time    IN     flight.flight_sch_time%TYPE
  ,p_agent_code         IN     agents.agent_code%TYPE
  ,p_flight_destination IN     VARCHAR2
  ,p_user_id            IN     tagtrak_users.user_id%TYPE
  ,p_serial_no          IN     kit.serial_number%TYPE
  ,p_error_message         OUT error_msg_type
  ,p_error_number          OUT error_num_type
  ,p_flight_id             OUT flight.flight_id%TYPE
  );

PROCEDURE update_a_flight
  (p_flight_no          IN      flight.flight_no%TYPE,
   p_flight_date        IN      flight.flight_date%TYPE,
   p_flight_sch_time    IN      flight.flight_sch_time%TYPE,
   p_agent_code         IN      agents.agent_code%TYPE,
   p_flight_destination IN      VARCHAR2,
   p_user_id            IN      tagtrak_users.user_id%TYPE,
   p_serial_no          IN      kit.serial_number%TYPE,
   p_error_message         OUT  error_msg_type,
   p_error_number          OUT  error_num_type
  );

PROCEDURE delete_a_flight
        (p_flight_no          IN      flight.flight_no%TYPE
        ,p_flight_date        IN      flight.flight_date%TYPE
        ,p_agent_code         IN      agents.agent_code%TYPE
        ,p_user_id            IN      tagtrak_users.user_id%TYPE
        ,p_serial_no          IN      kit.serial_number%TYPE
        ,p_error_message      OUT     error_msg_type
        ,p_error_number       OUT     error_num_type
        );

PROCEDURE loading_progress
        (p_flight_id                    IN      flight.flight_id%TYPE
        ,p_flight_no_in         IN      flight.flight_no%TYPE
        ,p_time_1               IN      flight.flight_sch_time%TYPE
        ,p_time_2               IN      flight.flight_sch_time%TYPE
        ,p_agent_code           IN      agents.agent_code%TYPE
        ,p_flight_no_out        OUT     flts_ldg_prog_by_time_pack.char8
        ,p_flight_status        OUT     flts_ldg_prog_by_time_pack.char2
        ,p_flight_destination   OUT     flts_ldg_prog_by_time_pack.char3
        ,p_flight_sch_time      OUT     flts_ldg_prog_by_time_pack.char4
        ,p_description          OUT     flts_ldg_prog_by_time_pack.char7
        ,tf                     OUT     flts_ldg_prog_by_time_pack.tnumber
        ,tj                     OUT     flts_ldg_prog_by_time_pack.tnumber
        ,ty                     OUT     flts_ldg_prog_by_time_pack.tnumber
        ,tr                     OUT     flts_ldg_prog_by_time_pack.tnumber
        ,lf                     OUT     flts_ldg_prog_by_time_pack.tnumber
        ,lj                     OUT     flts_ldg_prog_by_time_pack.tnumber
        ,ly                     OUT     flts_ldg_prog_by_time_pack.tnumber
        ,lr                     OUT     flts_ldg_prog_by_time_pack.tnumber
        );

FUNCTION loading_progress_2
 (p_flight_id           IN      flight.flight_id%TYPE,
  p_flight_no_in        IN      flight.flight_no%TYPE,
  p_time_1              IN      flight.flight_sch_time%TYPE,
  p_time_2              IN      flight.flight_sch_time%TYPE,
  p_agent_code          IN      agents.agent_code%TYPE
  ) RETURN SYS_REFCURSOR;

PROCEDURE action_duplicate_tag
        (p_location_code            IN          location.location_code%TYPE
        ,p_user_id                  IN          tagtrak_users.user_id%TYPE
        ,p_flight_id                IN          flight.flight_id%TYPE
        ,p_bag_tag                  IN          bag.bag_tag%TYPE
        ,p_source_uld_no            IN          ulds.uld_no%TYPE
        ,p_destination_uld_no       IN          ulds.uld_no%TYPE
        ,p_pax_id                   IN          pax.pax_id%TYPE
        ,p_function_type            IN          bag_trace.trace_action%TYPE
        ,p_serial_no                IN          kit.serial_number%TYPE
        ,p_error_message            OUT         error_msg_type
        ,p_error_number             OUT         error_num_type
        );

PROCEDURE pax_bag_count (p_flight_id        flight.flight_id%TYPE
        ,p_flight_no_in                     flight.flight_no%TYPE
        ,p_time_1                           flight.flight_sch_time%TYPE
        ,p_time_2                           flight.flight_sch_time%TYPE
        ,p_agent_code                       agents.agent_code%TYPE
        ,p_flight_no_out            OUT     flts_px_bg_cnt_by_tim_mws_pack.char8
        ,p_flight_destination       OUT     flts_px_bg_cnt_by_tim_mws_pack.char3
        ,p_flight_sch_time          OUT     flts_px_bg_cnt_by_tim_mws_pack.char4
        ,p_type                     OUT     flts_px_bg_cnt_by_tim_mws_pack.char5
        ,tf                         OUT     flts_px_bg_cnt_by_tim_mws_pack.tnumber
        ,lf                         OUT     flts_px_bg_cnt_by_tim_mws_pack.tnumber
        ,tj                         OUT     flts_px_bg_cnt_by_tim_mws_pack.tnumber
        ,lj                         OUT     flts_px_bg_cnt_by_tim_mws_pack.tnumber
        ,ty                         OUT     flts_px_bg_cnt_by_tim_mws_pack.tnumber
        ,ly                         OUT     flts_px_bg_cnt_by_tim_mws_pack.tnumber
        ,tr                         OUT     flts_px_bg_cnt_by_tim_mws_pack.tnumber
        ,lr                         OUT     flts_px_bg_cnt_by_tim_mws_pack.tnumber
        ,tot                        OUT     flts_px_bg_cnt_by_tim_mws_pack.tnumber
        );

FUNCTION pax_bag_count_2
        (p_flight_id              flight.flight_id%TYPE,
         p_flight_no_in           flight.flight_no%TYPE,
         p_time_1                 flight.flight_sch_time%TYPE,
         p_time_2                 flight.flight_sch_time%TYPE,
         p_agent_code             agents.agent_code%TYPE
         ) RETURN SYS_REFCURSOR;

PROCEDURE board_passenger
  (p_flight_id              IN          flight.flight_id%TYPE
        ,p_pax_id_1         IN          pax.pax_id%TYPE DEFAULT NULL
        ,p_pax_id_2         IN          pax.pax_id%TYPE DEFAULT NULL
        ,p_pax_id_3         IN          pax.pax_id%TYPE DEFAULT NULL
        ,p_pax_id_4         IN          pax.pax_id%TYPE DEFAULT NULL
        ,p_pax_id_5         IN          pax.pax_id%TYPE DEFAULT NULL
        ,p_type             IN          INTEGER
        ,p_user_id          IN          tagtrak_users.user_id%TYPE
        ,p_serial_no        IN          kit.serial_number%TYPE
        ,p_error_message    OUT         error_msg_type
        ,p_error_number     OUT         error_num_type
        );

/******************************************************************************
 *
 * PROCEDURE clear_a_bag
 *
 * Description:
 *   Removes a bag from a flight and creates a bag_trace record.
 *
 * Parameters:
 *   p_location_code - The location the operation is being done from
 *   p_user_id - user_id of user carrying out the operation
 *   p_flight_id - flight_id of flight bag is on
 *   p_bag_tag - bag_tag of bag to be removed.
 *   p_serial_no - serial_no of workstation used to carry out the operation.
 *   p_pax_id - pax_id of pax the bag belongs to
 *   p_error_message
 *   p_error_number
 *   p_error_description
 *
 * Errors raised:
 *   bag_issuer_not_known
 *   invalid_bag_tag_format
 *
 ******************************************************************************/
PROCEDURE clear_a_bag(p_location_code      IN      location.location_code%TYPE,
                      p_user_id            IN      tagtrak_users.user_id%TYPE,
                      p_flight_id          IN      flight.flight_id%TYPE,
                      p_bag_tag            IN      bag.bag_tag%TYPE,
                      p_serial_no          IN      kit.serial_number%TYPE,
                      p_pax_id             IN      pax.pax_id%TYPE,
                      p_error_message         OUT  error_msg_type,
                      p_error_number          OUT  error_num_type,
                      p_error_description     OUT  error.error_description%TYPE);

/******************************************************************************
 *
 * PROCEDURE clear_a_bag
 *
 * Description:
 *   Wrapper procedure that just calls the other clear_a_bag (above), passing 0
 *   as the pax_id
 *
 * Parameters: - As for the other clear_a_bag above but without p_pax_id
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/
PROCEDURE clear_a_bag(p_location_code      IN      location.location_code%TYPE,
                      p_user_id            IN      tagtrak_users.user_id%TYPE,
                      p_flight_id          IN      flight.flight_id%TYPE,
                      p_bag_tag            IN      bag.bag_tag%TYPE,
                      p_serial_no          IN      kit.serial_number%TYPE,
                      p_error_message         OUT  error_msg_type,
                      p_error_number          OUT  error_num_type,
                      p_error_description     OUT  error.error_description%TYPE);

PROCEDURE clear_an_untagged_crew_bag
        (p_location_code      IN      location.location_code%TYPE
        ,p_user_id            IN      tagtrak_users.user_id%TYPE
        ,p_flight_id          IN      flight.flight_id%TYPE
        ,p_no_of_bags         IN      NUMBER
        ,p_serial_no          IN      kit.serial_number%TYPE
        ,p_error_message      OUT     error_msg_type
        ,p_error_number       OUT     error_num_type
        ,p_error_description    OUT         error.error_description%TYPE
        );

PROCEDURE update_role
        (p_user_role_name       IN          user_role.user_role_name%TYPE
        ,p_agent_code           IN          agents.agent_code%TYPE
        ,p_granted_privilege    IN          parameter_string
        ,p_revoked_privilege    IN          parameter_string
        ,p_error_message        OUT         error_msg_type
        ,p_error_number         OUT         error_num_type
        );

PROCEDURE copy_role
        (p_user_role_name_from IN          user_role.user_role_name%TYPE
        ,p_user_role_name_to   IN          user_role.user_role_name%TYPE
        ,p_agent_code          IN          agents.agent_code%TYPE
        ,p_error_message       OUT         error_msg_type
        ,p_error_number        OUT         error_num_type
        );

PROCEDURE check_privilege
        (p_user_id                  IN      tagtrak_users.user_id%TYPE
        ,p_app_priv_name            IN      app_priv.app_priv_name%TYPE
        ,p_flight_id                IN      number
        ,p_valid_privilege          OUT     tagtrak_users.retired%TYPE
        ,p_error_message            OUT     error_msg_type
        ,p_error_number             OUT     error_num_type
        );

PROCEDURE add_flight_privileges
        (p_id                   IN          flight_privileges.id%TYPE
        ,p_originating_agent    IN          flight_privileges.originating_agent%TYPE
        ,p_airline_alpha_code   IN          flight_privileges.airline_alpha_code%TYPE
        ,p_agent_code           IN          flight_privileges.agent_code%TYPE
        ,p_flight_no            IN          flight_privileges.flight_no%TYPE
        ,p_flight_date          IN          flight_privileges.flight_date%TYPE
        ,p_last_user            IN          flight_privileges.last_user%TYPE
        ,p_error_message        OUT         error_msg_type
        ,p_error_number         OUT         error_num_type
        );

PROCEDURE update_bpm_status
        (p_granted_bpm_status   IN      parameter_string
        ,p_revoked_bpm_status   IN      parameter_string
        ,p_error_message        OUT     error_msg_type
        ,p_error_number         OUT     error_num_type
        );

/******************************************************************************
 *
 * PROCEDURE get_new_date
 *
 * Description:
 *   Increment or decrement dates of 'DD MON YYYY' format by a number
 *   p_day_month is set to 'M' for Month. Also find the last day of the
 *   month in 'DD MON YYYY' format when given only 'MON YYYY'.
 *
 * Parameters:
 *   p_old_date - The date to be modifed as a string. Should either be in DD MON YYYY
 *                or MON YYYY format
 *   p_increment - The number of days or months to be added to p_old_date
 *   p_month_day - the type of transformation:
 *                 M - takes a MON YYYY date and adds a number of months to it.
 *                 E - takes a MON YYYY date and gets the last day of that month to
 *                     return a DD MON YYYY date
 *                 Otherwise - takes a DD MON YYYY date and adds a number of days to it.
 *    p_new_date - the resulting date as a string.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE get_new_date
        (p_old_date   IN     date_string,
         p_increment  IN     NUMBER DEFAULT 1,
         p_month_day  IN     CHAR DEFAULT NULL,
         p_new_date      OUT date_string
        );

PROCEDURE set_a_security_bag
        (p_location_code             IN          location.location_code%TYPE
        ,p_user_id                   IN          tagtrak_users.user_id%TYPE
        ,p_flight_id                 IN          flight.flight_id%TYPE
        ,p_bag_tag                   IN          bag.bag_tag%TYPE
        ,p_security_clearance_status IN          bag.security_clearance_status%TYPE
        ,p_serial_no                 IN          kit.serial_number%TYPE
        ,p_error_message             OUT         error_msg_type
        ,p_error_number              OUT         error_num_type
        );

PROCEDURE set_flight_clearance_status
        (action               IN      bag_trace.trace_action%TYPE
        ,p_flight_no          IN      flight.flight_no%TYPE
        ,p_agent_code         IN      agents.agent_code%TYPE
        ,p_error_message      OUT     error_msg_type
        ,p_error_number       OUT     error_num_type
        );

PROCEDURE set_a_duplicate_security_bag
        (p_location_code             IN          location.location_code%TYPE
        ,p_user_id                   IN          tagtrak_users.user_id%TYPE
        ,p_flight_id                 IN          flight.flight_id%TYPE
        ,p_bag_tag                   IN          bag.bag_tag%TYPE
        ,p_security_clearance_status IN          bag.security_clearance_status%TYPE
        ,p_pax_id                    IN          pax.pax_id%TYPE
        ,p_serial_no                 IN          kit.serial_number%TYPE
        ,p_error_message             OUT         error_msg_type
        ,p_error_number              OUT         error_num_type
        );

PROCEDURE flight_restriction
        (p_flight_id          IN      flight.flight_id%TYPE
        ,p_agent_code         IN      agents.agent_code%TYPE
        ,p_restricted_agent   IN      agents.agent_code%TYPE
        ,p_user_id            IN      tagtrak_users.user_id%TYPE
        ,p_serial_no          IN      kit.serial_number%TYPE
        ,p_error_message      OUT     error_msg_type
        ,p_error_number       OUT     error_num_type
        );

PROCEDURE add_hold_config
        (p_user_id           IN  NUMBER,
        p_aircraft_subtype   IN  hold_config.aircraft_subtype%TYPE,
        p_carrier_alpha_code IN  hold_config.carrier_alpha_code%TYPE,
        p_position_string    IN  hold_config.position%TYPE,
        p_description        IN  hold_config.description%TYPE
        ,p_error_message     OUT error_msg_type
        ,p_error_number      OUT error_num_type);

PROCEDURE delete_hold_config
        (p_user_id           IN  NUMBER,
        p_aircraft_subtype   IN  hold_config.aircraft_subtype%TYPE,
        p_carrier_alpha_code IN  hold_config.carrier_alpha_code%TYPE
        ,p_error_message     OUT error_msg_type
        ,p_error_number      OUT error_num_type);

PROCEDURE update_hold_config
        (p_user_id           IN  NUMBER,
        p_aircraft_subtype   IN  hold_config.aircraft_subtype%TYPE,
        p_carrier_alpha_code IN  hold_config.carrier_alpha_code%TYPE,
        p_position_string    IN  hold_config.position%TYPE,
        p_description        IN  hold_config.description%TYPE
        ,p_error_message     OUT error_msg_type
        ,p_error_number      OUT error_num_type);

FUNCTION mws_get_user_bag_tag (
      p_tag            IN  VARCHAR2,
      p_user_pref      IN  VARCHAR2,
      p_bag_must_exist IN  NUMBER       DEFAULT 1 )
      RETURN               VARCHAR2;

FUNCTION mws_build_user_bag_tag (
      p_t1             IN  VARCHAR2,
      p_t2a            IN  VARCHAR2,
      p_t2b            IN  VARCHAR2,
      p_t3             IN  NUMBER,
      p_user_pref      IN  VARCHAR2,
      p_bag_must_exist IN  NUMBER       DEFAULT 1 )
      RETURN               VARCHAR2;

FUNCTION mws_get_numeric_bag_tag (
      p_tag            IN  VARCHAR2,
      p_bag_must_exist IN  NUMBER       DEFAULT 1 )
      RETURN               VARCHAR2;

PROCEDURE mws_validate_bag_tag (
      p_tag            IN  VARCHAR2,
      a_bag_tag        OUT VARCHAR2,
      n_bag_tag        OUT VARCHAR2,
      retval           OUT NUMBER );

PROCEDURE validate_partial_bag_tag (
      p_partial_tag    IN  VARCHAR2,
      p_numeric_tag    OUT VARCHAR2 );

FUNCTION get_privs_as_varchar ( p_group_name IN USER_GROUP.GROUP_NAME%TYPE,
p_agent_code IN USER_GROUP.AGENT_CODE%TYPE )
RETURN VARCHAR2;

FUNCTION are_privs_a_subset ( p_group_name1 IN USER_GROUP.GROUP_NAME%TYPE,
p_agent_code1 IN USER_GROUP.AGENT_CODE%TYPE,
p_group_name2 IN USER_GROUP.GROUP_NAME%TYPE,
p_agent_code2 IN USER_GROUP.AGENT_CODE%TYPE )
RETURN INTEGER;

FUNCTION departure_time
        (p_flight_date              IN flight.flight_date%TYPE
        ,p_flight_sch_time          IN OUT flight.flight_sch_time%TYPE)
RETURN VARCHAR2;

PROCEDURE add_a_pax_and_bag
        (p_location_code      IN      location.location_code%TYPE
        ,p_user_id            IN      tagtrak_users.user_id%TYPE
        ,p_serial_no          IN      kit.serial_number%TYPE
        ,p_flight_id          IN      flight.flight_id%TYPE
        ,p_bag_tag            IN      bag.bag_tag%TYPE
        ,p_credential_value   IN      varchar2
        ,p_exception_code     IN      bag_exception_types.bag_excep_code%TYPE
        ,p_pax_destination    IN      pax.pax_destination%TYPE
        ,p_pax_initials       IN      pax.pax_initials%TYPE
        ,p_pax_surname        IN      pax.pax_surname%TYPE
        ,p_booked_flight_id   IN      varchar2  --pax.booked_flight_id%TYPE
        ,p_conn_type          IN      pax.connection_type%TYPE
        ,p_embassy            IN      varchar2 -- DIPL bags only
        ,p_seat_number        IN      varchar2 -- DIPL bags only
        ,p_delivery_instr     IN      varchar2 -- RUSG bags only
        ,p_additional_bags    IN      varchar2 --number  -- CREW bags only
        ,p_pax_id             IN      pax.pax_id%TYPE
        ,p_wt_ref             IN      bag.worldtracer_ref%TYPE
        ,p_kg_weight          IN      bag.kg_weight%TYPE
        ,p_no_of_bags         IN      pax.no_of_bags%TYPE
        ,p_error_message      OUT     error_msg_type
        ,p_error_number       OUT     error_num_type
        ,p_error_description  OUT     error.error_description%TYPE
        ) ;

PROCEDURE rec_uld_posn (p_uld_no          IN     ulds.uld_no%TYPE,
                        fltid             IN     flight.flight_id%TYPE,
                        posn              IN     ulds.uld_hold_no%TYPE,
                        p_user_id         IN     tagtrak_users.user_id%type,
                        locn              IN     ulds.location_code%TYPE,
                        uld_loaded_status IN     ulds.uld_loaded_status%TYPE,
                        force_pd_load     IN     VARCHAR2 DEFAULT 'N',
                        retval               OUT NUMBER
                       );

/******************************************************************************
 *
 * PROCEDURE stop_multiple_passengers
 *
 * Description:
 *   Stop all of the bags belonging to a list of passengers on a single flight.
 *
 * In parameters:
 *   p_location_code      - location code of user stopping bags
 *   p_user_id            - user_id of user stopping bags
 *   p_flight_id          - flight_id of flight containing bags
 *   p_serial_no          - serial number of user stopping bags
 *   p_pax_list           - list of passengers to stop bags for
 *   p_reason             - the reason for stopping the bags
 *
 * Out parameters:
 *   p_error_message_list - List of error messages received when stopping bags
 *   p_error_number_list  - List of error numbers received when stopping bags
 *
 ******************************************************************************/
PROCEDURE stop_multiple_passengers
        (p_location_code       IN   location.location_code%TYPE
        ,p_user_id             IN   tagtrak_users.user_id%TYPE
        ,p_flight_id           IN   flight.flight_id%TYPE
        ,p_serial_no           IN   kit.serial_number%TYPE
        ,p_pax_list            IN   pax_list_type
        ,p_reason              IN   VARCHAR2
        ,p_error_message_list  OUT  error_msg_list_type
        ,p_error_number_list   OUT  error_num_list_type
        );

/******************************************************************************
 *
 * PROCEDURE clear_multiple_passengers
 *
 * Description:
 *   Clear all of the bags belonging to a list of passengers on a single flight.
 *
 * In parameters:
 *   p_location_code      - location code of user clearing bags
 *   p_user_id            - user_id of user clearing bags
 *   p_flight_id          - flight_id of flight containing bags
 *   p_serial_no          - serial number of user clearing bags
 *   p_pax_list           - list of passengers to clear bags for
 *
 * Out parameters:
 *   p_error_message_list - List of error messages received when clearing bags
 *   p_error_number_list  - List of error numbers received when clearing bags
 *
 ******************************************************************************/
PROCEDURE clear_multiple_passengers
        (p_location_code       IN   location.location_code%TYPE
        ,p_user_id             IN   tagtrak_users.user_id%TYPE
        ,p_flight_id           IN   flight.flight_id%TYPE
        ,p_serial_no           IN   kit.serial_number%TYPE
        ,p_pax_list            IN   pax_list_type
        ,p_error_message_list  OUT  error_msg_list_type
        ,p_error_number_list   OUT  error_num_list_type
        );

/******************************************************************************
 *
 * FUNCTION pax_stopped_bag_count
 *
 * Description:
 *   Returns a count of how many stopped bags a given passenger has.
 *
 * Parameters:
 *   p_pax_id - pax_id of pax to count bags for
 *
 ******************************************************************************/
FUNCTION pax_stopped_bag_count(p_pax_id IN pax.pax_id%TYPE)
      RETURN INTEGER;

/******************************************************************************
 *
 * FUNCTION pax_unstopped_bag_count
 *
 * Description:
 *   Returns a count of how many non-stopped (clear) bags a given passenger has.
 *
 * Parameters:
 *   p_pax_id - pax_id of pax to count bags for
 *
 ******************************************************************************/
FUNCTION pax_unstopped_bag_count(p_pax_id IN pax.pax_id%TYPE)
      RETURN INTEGER;


PROCEDURE stop_bags_2
        (p_location_code        IN          location.location_code%TYPE
        ,p_user_id              IN          tagtrak_users.user_id%TYPE
        ,p_flight_id            IN          flight.flight_id%TYPE
        ,p_pax_id               IN          pax.pax_id%TYPE
        ,p_serial_no            IN          kit.serial_number%TYPE
        ,p_reason               IN          VARCHAR2
        ,p_error_message        OUT         error_msg_type
        ,p_error_number         OUT         error_num_type
        );

END mws_web;
/
