/*****************************************************************************
*
* Filename         - $Workfile:   mws_web.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
*
*****************************************************************************/

CREATE OR REPLACE PACKAGE BODY MWS_WEB AS

  process          VARCHAR2(30);
  g_error_message  error_msg_type;
  g_error_number   error_num_type DEFAULT 0;

  abort                  EXCEPTION;
  abandon                EXCEPTION;
  numeric_or_value_error EXCEPTION;
  integrity_constraint   EXCEPTION;
  check_constraint       EXCEPTION;
  reuse_bag              EXCEPTION;
  bag_issuer_not_known   EXCEPTION;
  invalid_bag_tag_format EXCEPTION;
  PRAGMA EXCEPTION_INIT(check_constraint, -2290);
  PRAGMA EXCEPTION_INIT(integrity_constraint, -2291);
  PRAGMA EXCEPTION_INIT(reuse_bag, -20028);
  PRAGMA EXCEPTION_INIT(bag_issuer_not_known, -20041);
  PRAGMA EXCEPTION_INIT(invalid_bag_tag_format, -20042);
  PRAGMA EXCEPTION_INIT(numeric_or_value_error, -6502);

  -- CA/02119
  -- This exceptions was previously in the declarative region of various
  -- subprograms.  However, nested calls relied on the exception being the
  -- same one at different depths.
  --
  -- Theres always a chance that other exceptions will fall foul of the same
  -- problem.  All of them anonymous excpetions would have been moved here,
  -- but there's no time to retest every invocation.
  --
  uld_closed      exception;

  CURSOR bags (p_pax_id pax.pax_id%TYPE) IS
  SELECT b.bag_tag,
         b.bag_tag1,
         NVL(b.bag_tag2a,'-') BAG_TAG2A,
         b.bag_tag2b,
         b.bag_tag3,
         b.uld_no    -- added ULD number (Nigel, 01/04/04, CA598)
  FROM bag b
  WHERE b.pax_id  = p_pax_id;

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
/*    return to the MWS the text for the db error_id */
FUNCTION get_error_description (errid in error.error_id%TYPE)
return varchar2
IS
 errd error.error_description%TYPE ;
BEGIN

  if errid = 0 then
                 return ' ';
  end if;

 select error_description
    into errd
    from error
    where error_id = errid;
  return   errd;

  EXCEPTION
    WHEN OTHERS THEN
     errd := 'Unknown Database Error '||errid ;
  return   errd;
END;

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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 IS
  v_bpm_status    system_cfg.bpm_status%TYPE      DEFAULT NULL;
  BEGIN
    IF p_bag_tag2a <> '-' THEN
      SELECT s.bpm_status
      INTO v_bpm_status
      FROM system_cfg s
      ,bag b
      WHERE s.process_name = b.process_name
      AND s.unit_name      = b.unit_name
      AND s.dcsid          = b.dcsid
      AND s.subunit_name   = b.subunit_name
      AND b.pax_id         = p_pax_id
      AND b.bag_tag2a      = p_bag_tag2a
      AND b.bag_tag3       = p_bag_tag3;
    ELSE
      SELECT s.bpm_status
      INTO v_bpm_status
      FROM system_cfg s
      ,bag b
      WHERE s.process_name = b.process_name
      AND s.unit_name      = b.unit_name
      AND s.dcsid          = b.dcsid
      AND s.subunit_name   = b.subunit_name
      AND b.pax_id         = p_pax_id
      AND b.bag_tag2b      = p_bag_tag2b
      AND b.bag_tag3       = p_bag_tag3;
    END IF;

    RETURN v_bpm_status = 'Y';

  END bpm1_required;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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 IS
  v_bpm_type              mail_indiv.msg_type%TYPE DEFAULT NULL;
  BEGIN
    IF p_action IN ('LOAD', 'LOAD WHERE', 'MWS_LOAD') THEN    -- UAS/FALCP/CA/00699
       BEGIN
           SELECT '2'
      INTO    v_bpm_type
      FROM   bag_exception_types
      WHERE  bag_excep_pax_seq = p_pax_sequence_no;

       EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_bpm_type := '1';
       END;
    ELSIF p_action = 'OFFLOAD' THEN
      v_bpm_type := 3;
    ELSIF p_action = 'XFER_ULD' THEN
      SELECT DECODE(COUNT(hold_no),0,'5','7')
      INTO v_bpm_type FROM holds WHERE hold_no = p_uld_no;
    ELSIF p_action = 'XFER' THEN
      SELECT DECODE(COUNT(hold_no),0,'6','8')
      INTO v_bpm_type FROM holds WHERE hold_no = p_uld_no;
    ELSIF p_action = 'CLEAR XRAY' THEN
      v_bpm_type := 14;
    ELSIF instr(p_action, 'NOT XRAY') != 0 THEN
      v_bpm_type := 15;
    ELSIF p_action = '-1004' THEN
      v_bpm_type := 10;
    ELSIF p_action = '-1002' THEN
      v_bpm_type := 11;
    ELSE -- some other kind of error
      v_bpm_type := 12;
    END IF;

    RETURN v_bpm_type;

  END f_bpm_type;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
FUNCTION bpm2_required
  (p_flight_id            IN flight.flight_id%TYPE
  ,p_bpm_type             IN varchar2
  ) RETURN BOOLEAN IS
  v_process       flight.process_name%type;
  v_unit          flight.unit_name%type;
  v_dcsid         flight.dcsid%type;
  v_subunit       flight.subunit_name%type;
  v_bpm_status    char(1);
  BEGIN
    SELECT f.process_name, f.unit_name, f.dcsid, f.subunit_name
    INTO   v_process, v_unit, v_dcsid, v_subunit
    FROM flight f
    WHERE f.flight_id = p_flight_id;

    get_bpm_status(v_process, v_unit, v_dcsid, v_subunit, null, p_flight_id, p_bpm_type, v_bpm_status);

    RETURN v_bpm_status = 'Y';

  END bpm2_required;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
FUNCTION valid_flight_prefix
        (p_alpha_code           IN          flight_restrict.alpha_code%TYPE
        ) RETURN BOOLEAN IS
    CURSOR c_alpha_code(p_alpha_code        flight_restrict.alpha_code%TYPE) IS
            SELECT  alpha_code
            FROM            flight_restrict
            WHERE           alpha_code = p_alpha_code
            FOR UPDATE OF alpha_code;
    v_alpha_code        flight_restrict.alpha_code%TYPE;
        BEGIN
    OPEN c_alpha_code(p_alpha_code);
    FETCH c_alpha_code
    INTO    v_alpha_code;
    IF c_alpha_code%NOTFOUND THEN
            RAISE NO_DATA_FOUND;
    END IF;
    CLOSE c_alpha_code;
    RETURN FALSE;
        EXCEPTION
    WHEN NO_DATA_FOUND THEN
            CLOSE c_alpha_code;
            RETURN TRUE;
        END valid_flight_prefix;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
FUNCTION valid_agent_code
        (p_agent_code           IN          agents.agent_code%TYPE
        ,p_flight_no            IN          flight.flight_no%TYPE
        ) RETURN BOOLEAN IS
        v_return_code       NUMBER;
        BEGIN
    valid_agent     (p_agent_code
                ,p_flight_no
                ,v_return_code
                );
    RETURN (v_return_code = 1);
        END valid_agent_code;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
FUNCTION departure_time
        (p_flight_date           IN           flight.flight_date%TYPE
        ,p_flight_sch_time          IN OUT          flight.flight_sch_time%TYPE)
        RETURN VARCHAR2 IS
        BEGIN
          IF p_flight_sch_time = '-' THEN
             p_flight_sch_time := '0000';
          END IF;

          RETURN TO_CHAR(ltr.correct_year(TO_DATE(CONCAT(LPAD(p_flight_date,5,'0'),p_flight_sch_time),'DDMONHH24MI')),'DD-MON-YYYY HH24:MI');
        END departure_time;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
FUNCTION f_flight_id
        (p_flight_no            IN          flight.flight_no%TYPE
        ,p_flight_date              IN          flight.flight_date%TYPE
  ,p_siteid             IN      flight.site_id%TYPE
        ) RETURN flight.flight_id%TYPE IS
        v_flight_id             flight.flight_id%TYPE;
        BEGIN
    SELECT  flight_id
    INTO            v_flight_id
    FROM            flight
    WHERE           flight_no       = p_flight_no
    AND         flight_date = p_flight_date
    AND     site_id     = p_siteid;

    RETURN v_flight_id;
        EXCEPTION
    WHEN NO_DATA_FOUND THEN
            g_error_number := -139;
            SELECT error_description INTO g_error_message FROM error WHERE error_id = -139;
            RAISE abort;
        END f_flight_id;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
FUNCTION valid_flight_digits (p_flight_no IN VARCHAR2) RETURN BOOLEAN IS
  i PLS_INTEGER;
  BEGIN
    FOR i IN 4 .. LENGTH(RTRIM(p_flight_no)) - 1 LOOP
      IF NOT (ASCII(SUBSTR(p_flight_no,i,1)) BETWEEN 48 AND 57) THEN
        RETURN FALSE;
      END IF;
    END LOOP;
    RETURN TRUE;
  END valid_flight_digits;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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 IS
  v_pax_sequence_no      pax.pax_sequence_no%TYPE;
  BEGIN
    process := 'f_pax_sequence_no : ';
    SELECT pax_sequence_no
    INTO    v_pax_sequence_no
    FROM    pax p
    WHERE   p.pax_id = p_pax_id
    AND     p.flight_id = p_flight_id
    AND     ROWNUM =1;

    RETURN v_pax_sequence_no;
  END f_pax_sequence_no;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
FUNCTION f_pax_id
        (p_flight_id    flight.flight_id%TYPE
        ,p_bag_tag              bag.bag_tag%TYPE
        ) RETURN pax.pax_id%TYPE IS

        v_pax_id        pax.pax_id%TYPE;
    l_bt1       bag_v5.bag_tag1%TYPE;
    l_bt2a      bag_v5.bag_tag2a%TYPE;
    l_bt2b      bag_v5.bag_tag2b%TYPE;
    l_bt3       bag_v5.bag_tag3%TYPE;
    l_bag_tag   bag_v5.bag_tag%TYPE;
    l_tag_type  NUMBER;

BEGIN

    split_tags(p_bag_tag, l_bt1, l_bt2a, l_bt2b, l_bt3, l_bag_tag, l_tag_type, 0);

    SELECT  pax_id
    INTO            v_pax_id
    FROM            bag_v5 b
    WHERE   b.flight_id = p_flight_id
    AND     b.bag_tag2b     = l_bt2b
    AND     b.bag_tag3      = l_bt3
    AND         b.delete_marker = 0;

    RETURN v_pax_id;

END f_pax_id;

--------------------------------------------------------------------------------------------------------------------------
-- 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 IS

    v_pax_id pax.pax_id%TYPE;
    l_bt1       bag_v5.bag_tag1%TYPE;
    l_bt2a      bag_v5.bag_tag2a%TYPE;
    l_bt2b      bag_v5.bag_tag2b%TYPE;
    l_bt3       bag_v5.bag_tag3%TYPE;
    l_bag_tag   bag_v5.bag_tag%TYPE;
    l_tag_type  NUMBER;

BEGIN

    split_tags(p_bag_tag, l_bt1, l_bt2a, l_bt2b, l_bt3, l_bag_tag, l_tag_type, 0);

    SELECT  pax_id
    INTO    v_pax_id
    FROM    bag_v5 b
    WHERE   b.flight_id     = p_flight_id
    AND     b.bag_tag2b     = l_bt2b
    AND     b.bag_tag3      = l_bt3
    AND     b.delete_marker = 0
    AND     b.bag_exception = p_bag_type;

    RETURN v_pax_id;

END f_pax_id;

----------------------------------------------------------------------
-- new function to get ULD number for bag/pax (Nigel, 01/04/04, CA598)
----------------------------------------------------------------------
FUNCTION uld_no_for_bag (p_bag_tag       bag.bag_tag%TYPE,
                         p_pax_id        bag.pax_id%TYPE,
                         p_bag_exception bag.bag_exception%TYPE DEFAULT NULL) RETURN bag.uld_no%TYPE IS
   CURSOR uld_no_for_bag IS
      SELECT b.uld_no
      FROM   bag b
      WHERE  b.bag_tag                      = p_bag_tag
      AND    b.pax_id                       = p_pax_id
      AND    NVL (b.bag_exception, CHR (2)) = NVL (p_bag_exception, CHR (2));
   l_uld_no bag.uld_no%TYPE;
BEGIN
   process := 'uld_no_for_bag : ';
   OPEN  uld_no_for_bag;
   FETCH uld_no_for_bag INTO l_uld_no;
   IF uld_no_for_bag%NOTFOUND THEN
      l_uld_no := NULL;
   END IF;
   CLOSE uld_no_for_bag;
   RETURN l_uld_no;
END uld_no_for_bag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
FUNCTION scanner_privilege
  (p_app_priv_name IN app_priv.app_priv_name%TYPE) RETURN BOOLEAN IS
  CURSOR c_app_priv_name (p_app_priv_name app_priv.app_priv_name%TYPE) IS
    SELECT app_priv_name
    FROM  app_priv
    WHERE app_priv_name = p_app_priv_name
    AND app_priv_code IS NOT NULL;
  v_app_priv_name       app_priv.app_priv_name%TYPE;
  BEGIN
    OPEN c_app_priv_name (p_app_priv_name);
    FETCH c_app_priv_name
    INTO v_app_priv_name;
    IF c_app_priv_name%NOTFOUND THEN
      RAISE NO_DATA_FOUND;
    END IF;
    CLOSE c_app_priv_name;
    RETURN TRUE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      CLOSE c_app_priv_name;
      RETURN FALSE;
  END scanner_privilege;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
FUNCTION jam_privilege
        (p_app_priv_code IN app_priv.app_priv_code%TYPE) RETURN app_priv.app_priv_code%TYPE IS
        CURSOR c_app_priv_code (p_app_priv_code app_priv.app_priv_code%TYPE) IS
    SELECT app_priv_code
    FROM    app_priv
    WHERE app_priv_code = p_app_priv_code;
        v_app_priv_code             app_priv.app_priv_code%TYPE;
        BEGIN
    OPEN c_app_priv_code (p_app_priv_code);
    FETCH c_app_priv_code
    INTO v_app_priv_code;
    IF c_app_priv_code%NOTFOUND THEN
      RAISE NO_DATA_FOUND;
    END IF;
    CLOSE c_app_priv_code;
    RETURN NULL;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
            CLOSE c_app_priv_code;
            RETURN p_app_priv_code;
        END jam_privilege;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
FUNCTION jam_privileges (p_group_name IN user_group.group_name%TYPE
                        , p_agent_code IN user_group.agent_code%TYPE
                        )
RETURN VARCHAR2 IS

    v_jam_privileges VARCHAR2(100);
BEGIN
    SELECT jam_privilege (priv1)  || jam_privilege (priv2)  || jam_privilege (priv3)  || jam_privilege (priv4)
        || jam_privilege (priv5)  || jam_privilege (priv6)  || jam_privilege (priv7)  || jam_privilege (priv8)
        || jam_privilege (priv9)  || jam_privilege (priv10) || jam_privilege (priv11) || jam_privilege (priv12)
        || jam_privilege (priv13) || jam_privilege (priv14) || jam_privilege (priv15) || jam_privilege (priv16)
        || jam_privilege (priv17) || jam_privilege (priv18) || jam_privilege (priv19) || jam_privilege (priv20)
        || jam_privilege (priv21) || jam_privilege (priv22) || jam_privilege (priv23) || jam_privilege (priv24)
        || jam_privilege (priv25) || jam_privilege (priv26) || jam_privilege (priv27) || jam_privilege (priv28)
        || jam_privilege (priv29) || jam_privilege (priv30) || jam_privilege (priv31) || jam_privilege (priv32)
        || jam_privilege (priv33) || jam_privilege (priv34) || jam_privilege (priv35) || jam_privilege (priv36)
        || jam_privilege (priv37) || jam_privilege (priv38) || jam_privilege (priv39) || jam_privilege (priv40)
        || jam_privilege (priv41) || jam_privilege (priv42) || jam_privilege (priv43) || jam_privilege (priv44)
        || jam_privilege (priv45) || jam_privilege (priv46) || jam_privilege (priv47) || jam_privilege (priv48)
        || jam_privilege (priv49) || jam_privilege (priv50) || jam_privilege (priv51) || jam_privilege (priv52)
        || jam_privilege (priv53) || jam_privilege (priv54) || jam_privilege (priv55) || jam_privilege (priv56)
        || jam_privilege (priv57) || jam_privilege (priv58) || jam_privilege (priv59) || jam_privilege (priv60)
        || jam_privilege (priv61) || jam_privilege (priv62) || jam_privilege (priv63) || jam_privilege (priv64) || jam_privilege (priv65)
        || jam_privilege (priv66) || jam_privilege (priv67) || jam_privilege (priv68) || jam_privilege (priv69) || jam_privilege (priv70)
        || jam_privilege (priv71) || jam_privilege (priv72) || jam_privilege (priv73) || jam_privilege (priv74) || jam_privilege (priv75)
        || jam_privilege (priv76) || jam_privilege (priv77) || jam_privilege (priv78) || jam_privilege (priv79) || jam_privilege (priv80)
        || jam_privilege (priv81) || jam_privilege (priv82) || jam_privilege (priv83) || jam_privilege (priv84) || jam_privilege (priv85)
        || jam_privilege (priv86) || jam_privilege (priv87) || jam_privilege (priv88) || jam_privilege (priv89) || jam_privilege (priv90)
        || jam_privilege (priv91) || jam_privilege (priv92) || jam_privilege (priv93) || jam_privilege (priv94) || jam_privilege (priv95)
        || jam_privilege (priv96) || jam_privilege (priv97) || jam_privilege (priv98) || jam_privilege (priv99) || jam_privilege (priv100) JAM_PRIVILEGES
    INTO
        v_jam_privileges
    FROM
        user_group
    WHERE
        agent_code = p_agent_code
    AND
        group_name = p_group_name;

    RETURN v_jam_privileges;

EXCEPTION
WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END jam_privileges;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
FUNCTION post_departure
  (p_flight_id          IN      flight.flight_id%TYPE
        ) RETURN BOOLEAN AS
  v_flight_status       flight.flight_status%TYPE;
  BEGIN

    process := 'post_departure : ';

    SELECT
      flight_status
    INTO
       v_flight_status
    FROM
      flight
    WHERE
      flight_id = p_flight_id;

    RETURN v_flight_status = 'PD';
  END post_departure;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-- This was previously a PROCEDURE, called if bpm1_required return TRUE.
-- With the new per-carrier BPM config this now does both jobs, checking and
-- storing the BPM; returning TRUE if it does.  Otherwise it returns FALSE.
-- This is needed due to some additional error handling that was dependent
-- on the return from bpm1_required.
--
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 AS
  v_process_name     system_cfg.process_name%TYPE;
  v_unit_name        system_cfg.unit_name%TYPE;
  v_dcsid            system_cfg.dcsid%TYPE;
  v_subunit_name     system_cfg.subunit_name%TYPE;
  v_pax_id           pax.pax_id%TYPE;
  v_location_code    location.location_code%TYPE;
  v_user_id          tagtrak_users.user_id%TYPE;
  v_bpmid            bpm_data.bpmid%TYPE DEFAULT 0;
  v_bpm_type         mail_indiv.msg_type%TYPE
  DEFAULT f_bpm_type(p_action,p_pax_sequence_no => p_pax_sequence_no);
  v_bpm_status       char(1);
  v_exception        bag.bag_exception%TYPE;
  v_return           BOOLEAN DEFAULT false;
  BEGIN

    -- See if the BPM type is supported
    --
    BEGIN
      SELECT RTRIM(process_name)
        ,RTRIM(unit_name)
        ,RTRIM(dcsid)
        ,RTRIM(subunit_name)
        ,TO_CHAR(pax_id)
   ,bag_exception
      INTO v_process_name
        ,v_unit_name
        ,v_dcsid
        ,v_subunit_name
        ,v_pax_id
        ,v_exception
      FROM bag
      WHERE pax_id  = p_pax_id
      AND SUBSTR(bag_tag,-9) = SUBSTR(p_bag_tag,-9);

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_process_name := '-';
    END;

    IF v_process_name = '-' THEN
      g_error_number := -1015;
      g_error_message := 'error for bag '
           || p_bag_tag ||
           ' for passenger '
           || p_pax_id ||
           ' on flight '
           || p_flight_id;
      RAISE abort;
    END IF;
    get_bpm_status(v_process_name, v_unit_name, v_dcsid, v_subunit_name,
         v_exception, p_flight_id, trim(v_bpm_type), v_bpm_status);

    if v_bpm_status='Y' then

      v_return := true;

      process := 'generate_bpmid : ';

      generate_bpmid(v_bpmid);

      process := 'mail_bpm1 : ';

    INSERT INTO mail_indiv
      (msg_address
      ,msg_type
      ,msg_text
      ,msg_time
      ,addressee_count
      ) VALUES
      ((rtrim(v_process_name) || rtrim(v_unit_name))
       ,'BPM'
       ,(rtrim(v_dcsid) ||','||
        rtrim(v_subunit_name) ||','||
        rtrim(v_bpm_type) ||','||
        TO_CHAR(p_flight_id) ||','||
        TO_CHAR(v_bpmid) ||','||
        v_pax_id ||','||
        p_bag_tag ||','||NULL
        )
       ,SYSDATE
       ,1
       );

       v_location_code := p_location_code;
       v_user_id := p_user_id;

       process := 'store_bpm_user : ';

       store_bpm_user(v_bpmid
         ,v_user_id
         ,v_location_code
         );
    END IF;

    RETURN v_return;

  END store_bpm1;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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 '-'
  ) AS
  v_process_name       system_cfg.process_name%TYPE;
  v_unit_name          system_cfg.unit_name%TYPE;
  v_dcsid              system_cfg.dcsid%TYPE;
  v_subunit_name       system_cfg.subunit_name%TYPE;
  v_location_code      location.location_code%TYPE;
  v_user_id            tagtrak_users.user_id%TYPE;
  v_bpmid              bpm_data.bpmid%TYPE;
  v_bpm_type           mail_indiv.msg_type%TYPE DEFAULT

  f_bpm_type(p_action,p_uld_no => p_destination_uld_no);

  BEGIN
    process := 'mail_bpm2 : ';

    BEGIN
      SELECT RTRIM(process_name)
        ,unit_name
        ,dcsid
        ,subunit_name
      INTO v_process_name
        ,v_unit_name
        ,v_dcsid
        ,v_subunit_name
      FROM flight
      WHERE flight_id = p_flight_id;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_process_name := '-';
    END;

    IF v_process_name = '-' THEN
      g_error_number := -1015;
      g_error_message := 'error for flight '
           || p_flight_id;
      RAISE abort;
    END IF;

    process := 'insert mail_indiv : ';

    INSERT INTO mail_indiv
      (msg_address
      ,msg_type
      ,msg_text
      ,msg_time
      ,addressee_count
      ) VALUES
      ((rtrim(v_process_name) || rtrim(v_unit_name))
      ,'BPM'
      ,(rtrim(v_dcsid) ||','||
        v_subunit_name ||','||
        rtrim(v_bpm_type) ||','||
        TO_CHAR(p_flight_id) ||','||
        TO_CHAR(p_bpmid) ||','||
        rtrim(p_source_uld_no) ||','||
        rtrim(p_destination_uld_no) ||','|| trim(p_scanner_id) ||','||p_pax_id
       )
      ,SYSDATE
      ,1
      );

    v_location_code := p_location_code;
    v_user_id       := p_user_id;
    v_bpmid         := p_bpmid;

    process := 'store_bpm_user : ';

    store_bpm_user(v_bpmid
      ,v_user_id
      ,v_location_code
      );
  END store_bpm2;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
PROCEDURE add_flight_dests
        (p_flight_id        IN          flight.flight_id%TYPE
        ,p_flight_destination IN        VARCHAR2
        ) AS
        i                       PLS_INTEGER;
        BEGIN
    IF p_flight_destination IS NULL
    OR MOD(LENGTH(p_flight_destination),3) <> 0
    OR LENGTH(p_flight_destination) NOT BETWEEN 3 AND 21 THEN
            g_error_number := -1;
            g_error_message := 'each dest must be 3 characters. List of dests "'
                        || RTRIM(p_flight_destination) ||
                        '" is '
                        || NVL(LENGTH(p_flight_destination),0) ||
                        ' chars which should be a multiple of 3 up to 21';
            RAISE abort;
    END IF;

    FOR i IN 1 .. LENGTH(p_flight_destination) LOOP
            IF MOD(i,3) = 0 THEN
        INSERT INTO flight_dests
                (flight_id
                ,flight_destination
                ,dest_order
                ,dest_source
                ) VALUES
                (p_flight_id
                ,SUBSTR(p_flight_destination,i-2,3)
                ,(i/3)
                ,'M'
                );
            END IF;
    END LOOP;
        END add_flight_dests;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
PROCEDURE update_user_group (p_user_role_name               IN user_role.user_role_name%TYPE
                            , p_agent_code               IN agents.agent_code%TYPE
                            ) AS

    SUBTYPE app_priv_code    IS app_priv.app_priv_code%TYPE;
    TYPE app_priv_code_table IS TABLE OF app_priv_code INDEX BY BINARY_INTEGER;

    t_app_priv_code  app_priv_code_table;
    v_app_priv_code  app_priv.app_priv_code%TYPE;
    v_user_id        tagtrak_users.user_id%TYPE;
    v_jam_privileges VARCHAR2(100);
    i                BINARY_INTEGER;
    j                BINARY_INTEGER;

    CURSOR c_app_priv_code (p_user_role_name user_role.user_role_name%TYPE, p_agent_code agents.agent_code%TYPE) IS
    SELECT app_priv_code
    FROM
          app_priv ap
        , user_role_priv urp
        , user_role ur
    WHERE
        user_role_name = p_user_role_name
    AND
        agent_code = p_agent_code
    AND
        ap.app_priv_id = urp.app_priv_id
    AND
        ur.user_role_id = urp.user_role_id
    AND
        ap.app_priv_code IS NOT NULL
    ORDER BY
        app_priv_code;

BEGIN
    FOR i IN 1 .. 100 LOOP
        t_app_priv_code(i) := NULL;
    END LOOP;

    OPEN c_app_priv_code(p_user_role_name, p_agent_code);

    i := 0;
    LOOP
        FETCH c_app_priv_code INTO v_app_priv_code;
        EXIT WHEN c_app_priv_code%NOTFOUND;

        i := i + 1;
        t_app_priv_code(i) := v_app_priv_code;
    END LOOP;

    CLOSE c_app_priv_code;

    v_jam_privileges := jam_privileges(p_user_role_name, p_agent_code);

    FOR j IN 1 .. NVL(LENGTH(v_jam_privileges),0) LOOP
        t_app_priv_code(i + j) := SUBSTR(v_jam_privileges,j,1);
    END LOOP;

    BEGIN
        SELECT user_id
        INTO v_user_id
        FROM tagtrak_users
        WHERE loginname = 'TTADMIN';
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        g_error_message := 'No user_id found for login name of TTADMIN';
        RAISE abort;
    END;

    IF t_app_priv_code(1) IS NOT NULL THEN
        UPDATE user_group
        SET priv1 = t_app_priv_code(1)
            ,               priv2 = t_app_priv_code(2)
            ,               priv3 = t_app_priv_code(3)
            ,               priv4 = t_app_priv_code(4)
            ,               priv5 = t_app_priv_code(5)
            ,               priv6 = t_app_priv_code(6)
            ,               priv7 = t_app_priv_code(7)
            ,               priv8 = t_app_priv_code(8)
            ,               priv9 = t_app_priv_code(9)
            ,               priv10 = t_app_priv_code(10)
            ,               priv11 = t_app_priv_code(11)
            ,               priv12 = t_app_priv_code(12)
            ,               priv13 = t_app_priv_code(13)
            ,               priv14 = t_app_priv_code(14)
            ,               priv15 = t_app_priv_code(15)
            ,               priv16 = t_app_priv_code(16)
            ,               priv17 = t_app_priv_code(17)
            ,               priv18 = t_app_priv_code(18)
            ,               priv19 = t_app_priv_code(19)
            ,               priv20 = t_app_priv_code(20)
            ,               priv21 = t_app_priv_code(21)
            ,               priv22 = t_app_priv_code(22)
            ,               priv23 = t_app_priv_code(23)
            ,               priv24 = t_app_priv_code(24)
            ,               priv25 = t_app_priv_code(25)
            ,               priv26 = t_app_priv_code(26)
            ,               priv27 = t_app_priv_code(27)
            ,               priv28 = t_app_priv_code(28)
            ,               priv29 = t_app_priv_code(29)
            ,               priv30 = t_app_priv_code(30)
            ,               priv31 = t_app_priv_code(31)
            ,               priv32 = t_app_priv_code(32)
            ,               priv33 = t_app_priv_code(33)
            ,               priv34 = t_app_priv_code(34)
            ,               priv35 = t_app_priv_code(35)
            ,               priv36 = t_app_priv_code(36)
            ,               priv37 = t_app_priv_code(37)
            ,               priv38 = t_app_priv_code(38)
            ,               priv39 = t_app_priv_code(39)
            ,               priv40 = t_app_priv_code(40)
            ,               priv41 = t_app_priv_code(41)
            ,               priv42 = t_app_priv_code(42)
            ,               priv43 = t_app_priv_code(43)
            ,               priv44 = t_app_priv_code(44)
            ,               priv45 = t_app_priv_code(45)
            ,               priv46 = t_app_priv_code(46)
            ,               priv47 = t_app_priv_code(47)
            ,               priv48 = t_app_priv_code(48)
            ,               priv49 = t_app_priv_code(49)
            ,               priv50 = t_app_priv_code(50)
            ,               priv51 = t_app_priv_code(51)
            ,               priv52 = t_app_priv_code(52)
            ,               priv53 = t_app_priv_code(53)
            ,               priv54 = t_app_priv_code(54)
            ,               priv55 = t_app_priv_code(55)
            ,               priv56 = t_app_priv_code(56)
            ,               priv57 = t_app_priv_code(57)
            ,               priv58 = t_app_priv_code(58)
            ,               priv59 = t_app_priv_code(59)
            ,               priv60 = t_app_priv_code(60)
            ,               priv61 = t_app_priv_code(61)
            ,               priv62 = t_app_priv_code(62)
            ,               priv63 = t_app_priv_code(63)
            ,               priv64 = t_app_priv_code(64)
            ,               priv65 = t_app_priv_code(65)
            ,               priv66 = t_app_priv_code(66)
            ,               priv67 = t_app_priv_code(67)
            ,               priv68 = t_app_priv_code(68)
            ,               priv69 = t_app_priv_code(69)
            ,               priv70 = t_app_priv_code(70)
            ,               priv71 = t_app_priv_code(71)
            ,               priv72 = t_app_priv_code(72)
            ,               priv73 = t_app_priv_code(73)
            ,               priv74 = t_app_priv_code(74)
            ,               priv75 = t_app_priv_code(75)
            ,               priv76 = t_app_priv_code(76)
            ,               priv77 = t_app_priv_code(77)
            ,               priv78 = t_app_priv_code(78)
            ,               priv79 = t_app_priv_code(79)
            ,               priv80 = t_app_priv_code(80)
            ,               priv81 = t_app_priv_code(81)
            ,               priv82 = t_app_priv_code(82)
            ,               priv83 = t_app_priv_code(83)
            ,               priv84 = t_app_priv_code(84)
            ,               priv85 = t_app_priv_code(85)
            ,               priv86 = t_app_priv_code(86)
            ,               priv87 = t_app_priv_code(87)
            ,               priv88 = t_app_priv_code(88)
            ,               priv89 = t_app_priv_code(89)
            ,               priv90 = t_app_priv_code(90)
            ,               priv91 = t_app_priv_code(91)
            ,               priv92 = t_app_priv_code(92)
            ,               priv93 = t_app_priv_code(93)
            ,               priv94 = t_app_priv_code(94)
            ,               priv95 = t_app_priv_code(95)
            ,               priv96 = t_app_priv_code(96)
            ,               priv97 = t_app_priv_code(97)
            ,               priv98 = t_app_priv_code(98)
            ,               priv99 = t_app_priv_code(99)
            ,               priv100 = t_app_priv_code(100)
            ,               last_user = v_user_id
        WHERE RPAD(SUBSTR (p_user_role_name,1,15),15) = group_name
        AND p_agent_code = agent_code;
    ELSE
        DELETE FROM user_group
        WHERE RPAD (SUBSTR (p_user_role_name,1,15),15) = group_name
        AND p_agent_code = agent_code;
    END IF;

    IF SQL%NOTFOUND AND t_app_priv_code(1) IS NOT NULL THEN
        INSERT INTO user_group
            (group_name
            ,priv1
            ,priv2
            ,priv3
            ,priv4
            ,priv5
            ,priv6
            ,priv7
            ,priv8
            ,priv9
            ,priv10
            ,priv11
            ,priv12
            ,priv13
            ,priv14
            ,priv15
            ,priv16
            ,priv17
            ,priv18
            ,priv19
            ,priv20
            ,priv21
            ,priv22
            ,priv23
            ,priv24
            ,priv25
            ,priv26
            ,priv27
            ,priv28
            ,priv29
            ,priv30
            ,priv31
            ,priv32
            ,priv33
            ,priv34
            ,priv35
            ,priv36
            ,priv37
            ,priv38
            ,priv39
            ,priv40
            ,priv41
            ,priv42
            ,priv43
            ,priv44
            ,priv45
            ,priv46
            ,priv47
            ,priv48
            ,priv49
            ,priv50
            ,priv51
            ,priv52
            ,priv53
            ,priv54
            ,priv55
            ,priv56
            ,priv57
            ,priv58
            ,priv59
            ,priv60
            ,priv61
            ,priv62
            ,last_user
            ,agent_code
            ,airport_code
            ,priv63
            ,priv64
            ,priv65
            ,priv66
            ,priv67
            ,priv68
            ,priv69
            ,priv70
            ,priv71
            ,priv72
            ,priv73
            ,priv74
            ,priv75
            ,priv76
            ,priv77
            ,priv78
            ,priv79
            ,priv80
            ,priv81
            ,priv82
            ,priv83
            ,priv84
            ,priv85
            ,priv86
            ,priv87
            ,priv88
            ,priv89
            ,priv90
            ,priv91
            ,priv92
            ,priv93
            ,priv94
            ,priv95
            ,priv96
            ,priv97
            ,priv98
            ,priv99
            ,priv100
            )
        VALUES (p_user_role_name
            ,t_app_priv_code(1)
            ,t_app_priv_code(2)
            ,t_app_priv_code(3)
            ,t_app_priv_code(4)
            ,t_app_priv_code(5)
            ,t_app_priv_code(6)
            ,t_app_priv_code(7)
            ,t_app_priv_code(8)
            ,t_app_priv_code(9)
            ,t_app_priv_code(10)
            ,t_app_priv_code(11)
            ,t_app_priv_code(12)
            ,t_app_priv_code(13)
            ,t_app_priv_code(14)
            ,t_app_priv_code(15)
            ,t_app_priv_code(16)
            ,t_app_priv_code(17)
            ,t_app_priv_code(18)
            ,t_app_priv_code(19)
            ,t_app_priv_code(20)
            ,t_app_priv_code(21)
            ,t_app_priv_code(22)
            ,t_app_priv_code(23)
            ,t_app_priv_code(24)
            ,t_app_priv_code(25)
            ,t_app_priv_code(26)
            ,t_app_priv_code(27)
            ,t_app_priv_code(28)
            ,t_app_priv_code(29)
            ,t_app_priv_code(30)
            ,t_app_priv_code(31)
            ,t_app_priv_code(32)
            ,t_app_priv_code(33)
            ,t_app_priv_code(34)
            ,t_app_priv_code(35)
            ,t_app_priv_code(36)
            ,t_app_priv_code(37)
            ,t_app_priv_code(38)
            ,t_app_priv_code(39)
            ,t_app_priv_code(40)
            ,t_app_priv_code(41)
            ,t_app_priv_code(42)
            ,t_app_priv_code(43)
            ,t_app_priv_code(44)
            ,t_app_priv_code(45)
            ,t_app_priv_code(46)
            ,t_app_priv_code(47)
            ,t_app_priv_code(48)
            ,t_app_priv_code(49)
            ,t_app_priv_code(50)
            ,t_app_priv_code(51)
            ,t_app_priv_code(52)
            ,t_app_priv_code(53)
            ,t_app_priv_code(54)
            ,t_app_priv_code(55)
            ,t_app_priv_code(56)
            ,t_app_priv_code(57)
            ,t_app_priv_code(58)
            ,t_app_priv_code(59)
            ,t_app_priv_code(60)
            ,t_app_priv_code(61)
            ,t_app_priv_code(62)
            ,v_user_id
            ,p_agent_code
            ,multi_site.get_agent_loc (p_agent_code)
            ,t_app_priv_code(63)
            ,t_app_priv_code(64)
            ,t_app_priv_code(65)
            ,t_app_priv_code(66)
            ,t_app_priv_code(67)
            ,t_app_priv_code(68)
            ,t_app_priv_code(69)
            ,t_app_priv_code(70)
            ,t_app_priv_code(71)
            ,t_app_priv_code(72)
            ,t_app_priv_code(73)
            ,t_app_priv_code(74)
            ,t_app_priv_code(75)
            ,t_app_priv_code(76)
            ,t_app_priv_code(77)
            ,t_app_priv_code(78)
            ,t_app_priv_code(79)
            ,t_app_priv_code(80)
            ,t_app_priv_code(81)
            ,t_app_priv_code(82)
            ,t_app_priv_code(83)
            ,t_app_priv_code(84)
            ,t_app_priv_code(85)
            ,t_app_priv_code(86)
            ,t_app_priv_code(87)
            ,t_app_priv_code(88)
            ,t_app_priv_code(89)
            ,t_app_priv_code(90)
            ,t_app_priv_code(91)
            ,t_app_priv_code(92)
            ,t_app_priv_code(93)
            ,t_app_priv_code(94)
            ,t_app_priv_code(95)
            ,t_app_priv_code(96)
            ,t_app_priv_code(97)
            ,t_app_priv_code(98)
            ,t_app_priv_code(99)
            ,t_app_priv_code(100)
            );
    END IF;

EXCEPTION
WHEN OTHERS THEN
    RAISE;
END update_user_group;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
  ) AS

  v_pax_id            pax.pax_id%TYPE                 := 0;
  v_pax_sequence_no   pax.pax_sequence_no%TYPE;
  v_bag_tag1          bag.bag_tag1%TYPE;
  v_bag_tag2a         bag.bag_tag2a%TYPE;
  v_bag_tag2b         bag.bag_tag2b%TYPE;
  v_bag_tag3          bag.bag_tag3%TYPE;

  v_user_id           tagtrak_users.user_id%TYPE;

  c_action            CONSTANT bag_trace.trace_action%TYPE := 'MWS_LOAD';       -- UAS/FALCP/CA/00699
  -- Variables for tracking
  l_tracking_err_event         tracking_events.event_no%type;
  l_tracking_event_description tracking_events.description%type;
  l_event_count                number;
  l_no_of_errs                 number;
  l_bag_group                  bag_group.group_no%type;
  close_flag          number(10);
  tag1                bag.bag_tag1%type;
  tag2a                                                   bag.bag_tag2a%type;
  tag2b               bag.bag_tag2b%type;
  tag3                bag.bag_tag3%type;
  tag                 bag.bag_tag%type;
  t                   number(10);

  n_bag_tag           bag.bag_tag%TYPE;
  l_exists            NUMBER;                                                            -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)

  l_tag_type          NUMBER;
  l_tag3s             VARCHAR2(6);
  l_tag_num           bag.bag_tag%TYPE;

 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                 *
  * PURPOSE                         *
  *                                 *
  * Load a bag onto a ULD (doesnot have commits or rollbacks) and used by inbound flight processing *
  *                                 *
  * PROCESS                           METHOD     TYPE       *
  *                                 *
  * Check flight_status using post_departure                  Function   Internal   *
  * Validate the suitability of the bag for loading           Query      Local      *
  * Load a bag onto a ULD using load_bag          Procedure  External   *
  * Maintain traceability using add_bag_trace_record          Procedure  External   *
  * Send Bag Processed Message using store_bpm1               Procedure  Internal   *
  *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
  BEGIN

    l_event_count := 0;

    SELECT Count(*)
    INTO l_exists
    FROM dual
    WHERE NOT EXISTS (SELECT 1 FROM ULDS WHERE FLIGHT_ID=p_flight_id AND ULD_NO=p_uld_no);

    IF l_exists = 1 THEN
      IF NOT is_it_hold (p_uld_no) THEN
        g_error_message := 'uld_no '
              || p_uld_no ||
              ' ';
        g_error_number := -1001;
        SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
        RAISE abort;
      END IF;
   END IF;

    IF post_departure(p_flight_id) THEN
      g_error_message := 'flight id '
           || p_flight_id ||
           ' ';
      g_error_number := -208;
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
      RAISE abort;
    END IF;

    split_tags(p_bag_tag, tag1,tag2a,tag2b,tag3,tag,t);

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

    /* Ensure bag tag is in numeric format */
    process := 'convert bag tag : ';
    n_bag_tag := get_numeric_bag_tag(p_bag_tag);

    /* Initialise the Tag Number components, in case loading fails and we need to write a trace record */
    split_tags(n_bag_tag, v_bag_tag1, v_bag_tag2a, v_bag_tag2b, l_tag3s, l_tag_num, l_tag_type);
    v_bag_tag3 := To_Number(l_tag3s);

                /* Need to obtain pax details for loading bag errors */

    BEGIN
      process := 'Query Select : ';
      SELECT p.pax_id
        ,p.pax_sequence_no
        ,b.bag_tag1
        ,b.bag_tag2a
        ,NVL(b.bag_tag2b,'-')
        ,b.bag_tag3
      INTO v_pax_id
        ,v_pax_sequence_no
        ,v_bag_tag1
        ,v_bag_tag2a
        ,v_bag_tag2b
        ,v_bag_tag3
      FROM pax p
        ,bag b
        ,flight f
      WHERE p.flight_id = f.flight_id
      AND p.pax_id      = b.pax_id
      AND b.bag_tag2b   = tag2b
      AND b.bag_tag3    = tag3
      AND f.flight_id = p_flight_id
      AND b.authority_to_load_dcs = 'Y'
      AND b.authority_to_load_brs = 'Y'
      AND b.delete_marker = 0
      AND b.loaded_status <> 'Y' ;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
      WHEN TOO_MANY_ROWS THEN
        g_error_number := -1003;
        SELECT error_description INTO g_error_message FROM error WHERE error_id = g_error_number;
        SELECT p.pax_id
              ,p.pax_sequence_no
              ,b.bag_tag1
              ,b.bag_tag2a
              ,NVL(b.bag_tag2b,'-')
              ,b.bag_tag3
        INTO   v_pax_id
              ,v_pax_sequence_no
              ,v_bag_tag1
              ,v_bag_tag2a
              ,v_bag_tag2b
              ,v_bag_tag3
        FROM  pax p
             ,bag b
             ,flight f
        WHERE p.flight_id = f.flight_id
        AND   p.pax_id = b.pax_id
        AND   b.bag_tag2b = tag2b
        AND   b.bag_tag3  = tag3
        AND   f.flight_id = p_flight_id
        AND   b.authority_to_load_dcs = 'Y'
        AND   b.authority_to_load_brs = 'Y'
        AND   b.delete_marker = 0
        AND   b.loaded_status <> 'Y'
        AND   ROWNUM = 1;
        -- If bagflo interface set up then store a BPM for the error
        -- and abandon further processing but commit the store of the BPM
        IF store_bpm1(p_location_code
        ,p_user_id
        ,p_flight_id
        ,v_pax_id
        ,n_bag_tag
        ,v_pax_sequence_no
        ,TO_CHAR(g_error_number)
         ) then
          RAISE abandon;
        END IF;

        g_error_number := -1003;
        SELECT error_description INTO g_error_message FROM error WHERE error_id = -1003;
        RAISE abort;
      WHEN OTHERS THEN
        RAISE;
    END ;

    /* CA 243: Give specific error messages for failure to load a bag from the MWS */
    BEGIN

      process := 'validate bag : ';

      SELECT DECODE(b.loaded_status
        ,'Y', -1011 -- Already loaded
                    ,DECODE(b.security_clearance_status
               ,'N', -1068 -- Not Security Cleared
                           ,DECODE(b.authority_to_load_dcs
          ,'N', -1004 -- Not ATL DCS
          ,DECODE(b.authority_to_load_brs
                 ,'N', -1005 -- Not ATL BRS
                 ,0))))
      INTO g_error_number
      FROM pax p
         , bag b
         , flight f
      WHERE p.flight_id = f.flight_id
      AND p.pax_id      = b.pax_id
      --AND b.bag_tag     = n_bag_tag (g)
      AND SUBSTR(b.bag_tag,-9) = SUBSTR(n_bag_tag,-9)
      AND b.delete_marker = 0
      AND f.flight_id   = p_flight_id;

      IF g_error_number <> 0 THEN
        g_error_message := 'bag '
             || n_bag_tag ||
             ' on flight '
             || p_flight_id ||
             ' cannot be loaded - ';
        SELECT CONCAT(g_error_message,error_description)
          INTO g_error_message FROM error WHERE error_id = g_error_number;

                                /*
                                 * CA1224 generate a bag trace entry when an MWS user
                                 * user tries to load any not cleared (status 'N') bag
                                 */
                                v_user_id := p_user_id;

                add_bag_trace_record(v_user_id
            ,p_location_code
            ,g_error_number
            ,n_bag_tag
            ,v_bag_tag1
            ,v_bag_tag2a
            ,v_bag_tag2b
            ,v_bag_tag3
            ,c_action -- trace_action MWS_LOAD
            ,0 -- scanner_id
            ,p_flight_id
            ,v_pax_id
            ,p_uld_no
            );
        RAISE abandon;
      ELSE
        IF (l_event_count > 0) THEN
           -- If we set automatic events then we want to update the rows created with the uld number as the load was successful

           tracking.auto_events(p_bag_tag, p_flight_id, p_uld_no, v_pax_id, 0, p_user_id,
                                p_location_code, NULL, l_event_count, l_no_of_errs);

        END IF;
      END IF;

    EXCEPTION
       WHEN abort THEN
         RAISE; -- let the next block handle any exceptions
       WHEN NO_DATA_FOUND THEN
        g_error_message := 'bag '
             || n_bag_tag ||
             ' not found for flight '
             || p_flight_id;
        g_error_number := SQLCODE;
        RAISE abort;
       WHEN TOO_MANY_ROWS THEN
        g_error_number := -1003;
        SELECT error_description INTO g_error_message FROM error WHERE error_id = g_error_number;
       WHEN OTHERS THEN
         RAISE;
    END;

    -- Fire any automatic tracking events for this bag (ignore errors, as these
    -- should not affect the operation itself)

    tracking.auto_events(n_bag_tag, p_flight_id, NULL, v_pax_id, 0, p_user_id,
                         p_location_code, NULL, l_event_count, l_no_of_errs);

    -- Check that all mandatory tracking events have been cleared

    tracking.check_events_clear (p_user_id, n_bag_tag, v_pax_id, p_flight_id,            -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                                 l_tracking_err_event, l_tracking_event_description, g_error_number, l_bag_group);


    IF g_error_number < 0 THEN

       g_error_message := 'Event ' || l_tracking_err_event || ' failed: ' ||
                        l_tracking_event_description;
       RAISE abort;

    END IF;

    process := 'load_bag : ';

    close_flag := wan_msg.get_uld_close_flag(p_uld_no, p_flight_id);

    IF (close_flag = -1099) THEN
        raise uld_closed;
    END IF;

    load_bag(v_bag_tag2a
            ,v_bag_tag2b
            ,v_bag_tag3
            ,v_pax_id
            ,p_uld_no
            ,p_location_code
            ,0 -- scanner_id
            ,p_flight_id
            ,g_error_number
            );

    IF g_error_number <> 0 THEN
      g_error_message := 'error for bag '
           || n_bag_tag ||
           ' in ULD '
           || p_uld_no ||
           ' - ';
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;

      -- If bagflo interface set up then store a BPM for the error
      -- and abandon further processing but commit the store of the BPM
      IF store_bpm1(p_location_code
      ,p_user_id
      ,p_flight_id
      ,v_pax_id
      ,n_bag_tag
      ,v_pax_sequence_no
            ,TO_CHAR(g_error_number)) then
        RAISE abandon;
      END IF;

      RAISE abort;
    END IF;

    v_user_id := p_user_id;

    process := 'add_bag_trace : ';

    add_bag_trace_record(v_user_id
            ,p_location_code
            ,0 -- error_code
            ,n_bag_tag
            ,v_bag_tag1
            ,v_bag_tag2a
            ,v_bag_tag2b
            ,v_bag_tag3
            ,c_action -- trace_action
            ,0 -- scanner_id
            ,p_flight_id
            ,v_pax_id
            ,p_uld_no    -- pass ULD number (Nigel, 01/04/04, CA598)
            );

    IF store_bpm1(p_location_code
    ,p_user_id
    ,p_flight_id
    ,v_pax_id
    ,n_bag_tag
    ,v_pax_sequence_no
    ,c_action
          ) then
   null;
    END IF;

    p_error_number := 0;
    p_error_message := 'load_a_bag successful for bag '
           || n_bag_tag ||
           ' in ULD '
           || p_uld_no ||
           ' on flight '
           || p_flight_id;

  EXCEPTION
    WHEN uld_closed THEN
     g_error_number    := -1099;
     p_error_number  := g_error_number;
     g_error_message   := 'Destination Uld is Closed';
     p_error_message := process || g_error_message;
     RAISE;
    WHEN abort THEN
      p_error_number := g_error_number;
      p_error_message := process || g_error_message;
      RAISE;
    WHEN abandon THEN
      p_error_number := g_error_number;
      p_error_message := process || g_error_message;
      RAISE;
    WHEN bag_issuer_not_known THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Baggage Tag Issuer Code not known';
      RAISE;
    WHEN invalid_bag_tag_format THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Invalid Baggage Tag format';
      RAISE;
    WHEN OTHERS THEN
      p_error_number := SQLCODE;
      p_error_message := process||SQLERRM;
      RAISE;
  END mws_load_a_bag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
  ) AS

 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                 *
  * PURPOSE                         *
  *                                 *
  * Load a bag onto a ULD                       *
  *                                 *
  * PROCESS                           METHOD     TYPE       *
  *                                 *
  * Check flight_status using post_departure                  Function   Internal   *
  * Validate the suitability of the bag for loading           Query      Local      *
  * Load a bag onto a ULD using load_bag          Procedure  External   *
  * Maintain traceability using add_bag_trace_record          Procedure  External   *
  * Send Bag Processed Message using store_bpm1               Procedure  Internal   *
  *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
  BEGIN

  mws_web.mws_load_a_bag(p_location_code
                        ,p_user_id
                        ,p_flight_id
                        ,p_uld_no
                        ,p_bag_tag
                        ,p_serial_no
                        ,p_error_message
                        ,p_error_number);
  COMMIT;
  EXCEPTION

  WHEN uld_closed THEN
      g_error_number    := -1099;
      p_error_number  := g_error_number;
      g_error_message   := 'Destination Uld is Closed';
      p_error_message := process || g_error_message;
      ROLLBACK;
    WHEN abort THEN
      p_error_number := g_error_number;
      p_error_message := process || g_error_message;
      ROLLBACK;
    WHEN abandon THEN
      p_error_number := g_error_number;
      p_error_message := process || g_error_message;
      COMMIT;
    WHEN bag_issuer_not_known THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Baggage Tag Issuer Code not known';
      ROLLBACK;
    WHEN invalid_bag_tag_format THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Invalid Baggage Tag format';
      ROLLBACK;
    WHEN OTHERS THEN
      p_error_number := SQLCODE;
      p_error_message := process||SQLERRM;
      ROLLBACK;
      raise_application_error(-20000,process || SQLERRM);
  END load_a_bag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
  ) AS
  v_uld_no            ulds.uld_no%TYPE;
  v_pax_id            pax.pax_id%TYPE;
  v_bag_tag1          bag.bag_tag1%TYPE;
  v_bag_tag2a         bag.bag_tag2a%TYPE;
  v_bag_tag2b         bag.bag_tag2b%TYPE;
  v_bag_tag3          bag.bag_tag3%TYPE;

  v_location_code     location.location_code%TYPE;
  v_user_id           tagtrak_users.user_id%TYPE;
  v_flight_id         flight.flight_id%TYPE;
  close_flag          number(10);
  c_action            CONSTANT bag_trace.trace_action%TYPE := 'OFFLOAD';
  tag1                bag.bag_tag1%type;
  tag2a               bag.bag_tag2a%type;
  tag2b               bag.bag_tag2b%type;
  tag3                bag.bag_tag3%type;
  tag                 bag.bag_tag%type;
  t                   number(10);
  -- Tracking variables
  l_event_count                number;
  l_no_of_errs                 number;
  n_bag_tag           bag.bag_tag%TYPE;                                                            -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                 *
  * PURPOSE                         *
  *                                 *
  * Unload a bag from a ULD                     *
  *                                 *
  * PROCESS                           METHOD     TYPE       *
  *                                 *
  * Check flight_status using post_departure                  Function   Internal   *
  * Validate the suitability of the bag for unloading         Query      Local      *
  * Unload a bag from a ULD using offload_bag                 Procedure  External   *
  * Maintain traceability using add_bag_trace_record          Procedure  External   *
  * Send Bag Processed Message using store_bpm1               Procedure  Internal   *
  * Obtain pax_sequence_no using f_pax_sequence_no            Function   Internal   *
  *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
  BEGIN

    IF post_departure(p_flight_id) THEN
      g_error_message := 'flight id '
           || p_flight_id ||
           ' ';
      g_error_number := -208;
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
      RAISE abort;
    END IF;

     split_tags(p_bag_tag, tag1,tag2a,tag2b,tag3,tag,t);

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

    -- Ensure bag tag is in numeric format
    process := 'convert bag tag : ';                                                               -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
    n_bag_tag := get_numeric_bag_tag(p_bag_tag);                                                   -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)

    BEGIN
      process := 'Query Select : ';
      SELECT b.pax_id
        ,b.bag_tag1
        ,b.bag_tag2a
        ,NVL(b.bag_tag2b,'-')
        ,b.bag_tag3
        ,b.uld_no
      INTO v_pax_id
        ,v_bag_tag1
        ,v_bag_tag2a
        ,v_bag_tag2b
        ,v_bag_tag3
        ,v_uld_no
      FROM bag_v2 b
      WHERE b.bag_tag2b = tag2b
      AND b.bag_tag3    = tag3
      AND b.flight_id = p_flight_id
      AND b.loaded_status = 'Y' ;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        g_error_message := 'bag '
             || n_bag_tag ||
             ' not found for flight '
             || p_flight_id;                                                                       -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
        g_error_number := 100;
        RAISE abort;
      WHEN TOO_MANY_ROWS THEN
        g_error_number := -1003;
        SELECT error_description INTO g_error_message FROM error WHERE error_id = g_error_number;
        RAISE abort;
      WHEN OTHERS THEN
        RAISE;
    END;

    -- Fire any automatic tracking events for this bag (ignore errors, as these
    -- should not affect the operation itself)
    tracking.auto_events(n_bag_tag, p_flight_id, NULL, v_pax_id, 0, p_user_id,                     -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                         p_location_code, NULL, l_event_count, l_no_of_errs);

    process := 'offload_bag : ';

    v_location_code         := p_location_code;
    v_flight_id             := p_flight_id;

    close_flag := wan_msg.get_uld_close_flag(v_uld_no, v_flight_id);

    IF (close_flag = -1099) THEN
        raise uld_closed;
    END IF;

    offload_bag  (v_bag_tag2a
     ,v_bag_tag2b
     ,v_bag_tag3
     ,v_pax_id
     ,v_uld_no
     ,v_location_code
     ,0 -- scanner_id
     ,v_flight_id
     ,g_error_number
     );

    IF g_error_number <> 0 THEN
      g_error_message :=  'error for bag '
            || n_bag_tag ||
            ' in ULD '
            || v_uld_no ||
            ' - ';                                                                                 -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
      RAISE abort;
    END IF;

    v_user_id := p_user_id;

    process := 'add_bag_trace : ';

    add_bag_trace_record(v_user_id
            ,p_location_code
            ,0 -- error_code
            ,n_bag_tag                                                                             -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
            ,v_bag_tag1
            ,v_bag_tag2a
            ,v_bag_tag2b
            ,v_bag_tag3
            ,c_action -- trace_action
            ,0 -- scanner_id
            ,p_flight_id
            ,v_pax_id
            ,v_uld_no    -- pass ULD number (Nigel, 01/04/04, CA598)
            );

    IF store_bpm1(p_location_code
    ,p_user_id
    ,p_flight_id
    ,v_pax_id
    ,n_bag_tag                                                                                     -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
    ,f_pax_sequence_no(v_pax_id, p_flight_id)
    ,c_action
          ) then
   null;
    END IF;

    p_error_number := 0;
    p_error_message :=  'offload_a_bag successful for bag '
            || n_bag_tag ||
            ' in ULD '
            || v_uld_no ||
            ' on flight '
            || p_flight_id;                                                                        -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
    COMMIT;

  EXCEPTION
    WHEN uld_closed THEN
     g_error_number    := -1099;
     p_error_number  := g_error_number;
     g_error_message   := 'Destination Uld is Closed';
     p_error_message := process || g_error_message;
     ROLLBACK;

    WHEN abort THEN
      p_error_number := g_error_number;
      p_error_message := process || g_error_message;
      ROLLBACK;
    WHEN bag_issuer_not_known THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Baggage Tag Issuer Code not known';
      ROLLBACK;
    WHEN invalid_bag_tag_format THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Invalid Baggage Tag format';
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,process || SQLERRM);
    END unload_a_bag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        v_bag_tag2a                     bag.bag_tag2a%TYPE;
        v_bag_tag2b                     bag.bag_tag2b%TYPE;
        v_user_id                   tagtrak_users.user_id%TYPE;

        c_action                    CONSTANT bag_trace.trace_action%TYPE := 'STOP';
        -- Tracking variables
        l_event_count                number;
        l_no_of_errs                 number;
        l_process         VARCHAR2(30);
        l_error_number NUMBER DEFAULT 0;
        l_error_message VARCHAR2(512);

        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Stop all the bags for a passenger                       *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Deny authority to load to the passenger                   Query      Local      *
        * Select all the bags for the passenger         Cursor     Internal   *
        * Maintain traceability for each bag using add_bag_trace_record         Procedure  External   *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

    -- Fire any automatic tracking events for this bag (ignore errors, as these
    -- should not affect the operation itself)

    tracking.auto_events(0, p_flight_id, NULL, p_pax_id, 0, p_user_id,
                         p_location_code, NULL, l_event_count, l_no_of_errs);

    v_user_id       := p_user_id;

    l_process := 'update bag : ';

    UPDATE  bag
    SET         authority_to_load_brs = 'N'
    WHERE           authority_to_load_brs <> 'N'
    AND         pax_id  =       p_pax_id;

    IF SQL%NOTFOUND THEN
            l_error_number := -1;
            l_error_message :=        'passenger '
                        || p_pax_id ||
                        ' has no unstopped bags';
            RAISE abort;
    END IF;

    v_user_id := p_user_id;

    l_process := 'add_bag_trace : ';

    FOR bag IN bags(p_pax_id) LOOP
            v_bag_tag2a     := bag.bag_tag2a;
            v_bag_tag2b     := bag.bag_tag2b;
            add_bag_trace_record(v_user_id
                                ,p_location_code
                                ,0      -- error_code
                                ,bag.bag_tag
                                ,bag.bag_tag1
                                ,v_bag_tag2a
                                ,v_bag_tag2b
                                ,bag.bag_tag3
                                ,c_action -- trace_action
                                ,0 -- scanner_id
                                ,p_flight_id
                                ,p_pax_id
                                ,bag.uld_no);    -- pass ULD number (Nigel, 01/04/04, CA598)
    END LOOP;

    p_error_number := 0;
    p_error_message :=      'stop_bags successful for passenger '
                        || p_pax_id;
    COMMIT;

        EXCEPTION
    WHEN abort THEN
            p_error_number := l_error_number;
            p_error_message := l_process || l_error_message;
            ROLLBACK;
    WHEN OTHERS THEN
            ROLLBACK;
            raise_application_error(-20000,l_process || SQLERRM);
        END stop_bags;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        v_bag_tag2a                     bag.bag_tag2a%TYPE;
        v_bag_tag2b                     bag.bag_tag2b%TYPE;
        v_user_id                   tagtrak_users.user_id%TYPE;

        c_action                    CONSTANT bag_trace.trace_action%TYPE := 'UNSTOP';
        -- Tracking variables
        l_event_count                number;
        l_no_of_errs                 number;
        l_process         VARCHAR2(30);
        l_error_number NUMBER DEFAULT 0;
        l_error_message VARCHAR2(512);

        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Clear all the bags for a passenger                      *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Grant authority to load to the passenger                  Query      Local      *
        * Select all the bags for the passenger         Cursor     Internal   *
        * Maintain traceability for each bag using add_bag_trace_record         Procedure  External   *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

    -- Fire any automatic tracking events for this bag (ignore errors, as these
    -- should not affect the operation itself)

    tracking.auto_events(0, p_flight_id, NULL, p_pax_id, 0, p_user_id,
                         p_location_code, NULL, l_event_count, l_no_of_errs);

    v_user_id       := p_user_id;

    l_process := 'update bag : ';

    UPDATE  bag
    SET         authority_to_load_brs = 'Y'
    WHERE           authority_to_load_brs <> 'Y'
    AND         pax_id  =       p_pax_id;

    IF SQL%NOTFOUND THEN
            l_error_number := -1;
            l_error_message :=        'passenger '
                        || p_pax_id ||
                        ' has no stopped bags';
            RAISE abort;
    END IF;

    v_user_id := p_user_id;

    l_process := 'add_bag_trace : ';

    FOR bag IN bags(p_pax_id) LOOP
            v_bag_tag2a     := bag.bag_tag2a;
            v_bag_tag2b     := bag.bag_tag2b;
            add_bag_trace_record(v_user_id
                                ,p_location_code
                                ,0      -- error_code
                                ,bag.bag_tag
                                ,bag.bag_tag1
                                ,v_bag_tag2a
                                ,v_bag_tag2b
                                ,bag.bag_tag3
                                ,c_action -- trace_action
                                ,0 -- scanner_id
                                ,p_flight_id
                                ,p_pax_id
                                ,bag.uld_no);    -- pass ULD number (Nigel, 01/04/04, CA598)
    END LOOP;

    p_error_number := 0;
    p_error_message :=      'clear_bags successful for passenger '
                        || p_pax_id;
    COMMIT;

        EXCEPTION
    WHEN abort THEN
            p_error_number := l_error_number;
            p_error_message := l_process || l_error_message;
            ROLLBACK;
    WHEN OTHERS THEN
            ROLLBACK;
            raise_application_error(-20000,l_process || SQLERRM);
END clear_bags;

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
  ) AS
  v_user_id           tagtrak_users.user_id%TYPE;
  v_bag_tag           bag.bag_tag%TYPE;
  v_bag_tag1          bag.bag_tag1%TYPE;
  v_bag_tag2a         bag.bag_tag2a%TYPE;
  v_bag_tag2b         bag.bag_tag2b%TYPE;
  v_bag_tag3          bag.bag_tag3%TYPE;
  v_pax_id            pax.pax_id%TYPE;
  v_source_uld_no     ulds.uld_no%TYPE;
  v_destination_uld_no ulds.uld_no%TYPE;
  v_location_code     location.location_code%TYPE;
  v_flight_id         flight.flight_id%TYPE;
  v_bpmid             bpm_data.bpmid%TYPE DEFAULT 0;
  sour_uld_closed          exception;
  dest_uld_closed          exception;
  close_flag          number(10);

  c_action            CONSTANT bag_trace.trace_action%TYPE := 'XFER';

  n_bag_tag           bag.bag_tag%TYPE;                                                            -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)

  -- Tracking variables
  l_event_count                number;
  l_no_of_errs                 number;
  l_exists                     number;

  BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                 *
  * PURPOSE                         *
  *                                 *
  * Transfer a bag from one ULD to another                  *
  *                                 *
  * PROCESS                           METHOD     TYPE       *
  *                                 *
  * Obtain pax_id and bag tag details             Query      Local      *
  * Generate a BPM id using generate_bpmid        Procedure  External   *
  * Transfer a bag from one uld to another using xfer_bag     Procedure  External   *
  * Maintain traceability using add_bag_trace_record          Procedure  External   *
  * Send Bag Processed Message using store_bpm2               Procedure  Internal   *
  *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    SELECT Count(*)
    INTO l_exists
    FROM dual
    WHERE NOT EXISTS (SELECT 1 FROM ULDS WHERE FLIGHT_ID=p_flight_id AND ULD_NO=p_destination_uld_no);

    IF l_exists = 1 THEN
      IF NOT is_it_hold (p_destination_uld_no) THEN
          g_error_message := 'uld_no '
                 || p_destination_uld_no ||
                 ' ';
        g_error_number := -211;
        SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
        RAISE abort;
      END IF;
          END IF;

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

    v_user_id := p_user_id;

    -- Ensure bag tag is in numeric format
    process := 'convert bag tag : ';                                                               -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
    n_bag_tag := get_numeric_bag_tag(p_bag_tag);

    BEGIN
      process := 'Query Select : ';
      SELECT b.pax_id
        ,b.bag_tag1
        ,NVL(b.bag_tag2a,'-')
        ,b.bag_tag2b
        ,b.bag_tag3
      INTO v_pax_id
        ,v_bag_tag1
        ,v_bag_tag2a
        ,v_bag_tag2b
        ,v_bag_tag3
      FROM bag_v2 b
      WHERE b.bag_tag         = n_bag_tag                                                          -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
      AND b.flight_id         = p_flight_id
      AND b.uld_no            = p_source_uld_no;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        g_error_message := 'bag '
             || n_bag_tag ||
             ' not found on ULD '
             || p_source_uld_no ||
             ' for flight '
             || p_flight_id;                                                                       -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
        g_error_number := SQLCODE;
        RAISE abort;
      WHEN TOO_MANY_ROWS THEN
        g_error_number := -1003;
        SELECT error_description INTO g_error_message FROM error WHERE error_id = g_error_number;
        RAISE abort;
      WHEN OTHERS THEN
        RAISE;
    END ;
    -- Fire any automatic tracking events for this bag (ignore errors, as these
    -- should not affect the operation itself)

    tracking.auto_events(n_bag_tag, p_flight_id, NULL, v_pax_id, 0, p_user_id,                     -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                         p_location_code, NULL, l_event_count, l_no_of_errs);

    process := 'generate_bpmid : ';

    IF bpm_mode THEN
      IF bpm2_required(p_flight_id,'6') THEN
        generate_bpmid(v_bpmid);
      ELSE
        v_bpmid := 0;
      END IF;
    ELSE
      v_bpmid := 0;
    END IF;

    v_bag_tag               := n_bag_tag;                                                          -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
    v_source_uld_no         := p_source_uld_no;
    v_destination_uld_no    := p_destination_uld_no;
    v_location_code         := p_location_code;
    v_flight_id             := p_flight_id;

    process := 'xfer_bag : ';

    close_flag := wan_msg.get_uld_close_flag(v_destination_uld_no, v_flight_id);

    IF (close_flag = -1099) THEN
       raise dest_uld_closed;
    END IF;

    close_flag := wan_msg.get_uld_close_flag(v_source_uld_no, v_flight_id);


    IF (close_flag = -1099) THEN
       raise sour_uld_closed;
    END IF;

    xfer_bag(v_bag_tag
            ,v_bag_tag2a
            ,v_bag_tag2b
            ,v_bag_tag3
            ,v_pax_id
            ,v_source_uld_no
            ,v_destination_uld_no
            ,v_location_code
            ,0
            ,NVL(v_bpmid,0)
            ,v_flight_id
            ,g_error_number
            );

    IF g_error_number <> 0 THEN
      g_error_message := 'error for bag '
           || n_bag_tag ||
           ' from ULD '
           || p_source_uld_no ||
           ' to ULD '
           || p_destination_uld_no ||
            ' - ';                                                                                 -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
      RAISE abort;
    END IF;

    process := 'add_bag_trace : ';

    add_bag_trace_record(v_user_id
            ,p_location_code
            ,0      -- error_code
            ,n_bag_tag                                                                             -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
            ,v_bag_tag1
            ,v_bag_tag2a
            ,v_bag_tag2b
            ,v_bag_tag3
            ,c_action -- trace_action
            ,0 -- scanner_id
            ,p_flight_id
            ,v_pax_id
            ,v_destination_uld_no    -- pass ULD number (Nigel, 01/04/04, CA598)
            );

    IF bpm_mode and bpm2_required(p_flight_id,'6') THEN
      process := 'store_bpm2 : ';
      store_bpm2(p_location_code
    ,p_user_id
    ,p_flight_id
    ,c_action
    ,p_source_uld_no
    ,p_destination_uld_no
    ,v_bpmid
    ,'0' -- Scanner_id
    ,v_pax_id
    );
    END IF;

    p_error_number := 0;
    p_error_message := 'transfer_bag successful for bag '
           || n_bag_tag ||
           ' on flight '
           || p_flight_id;                                                                         -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)

    COMMIT;

  EXCEPTION

    WHEN dest_uld_closed THEN
     g_error_number    := -1106;
     p_error_number  := g_error_number;
     g_error_message   := 'Destination Uld is Closed';
     p_error_message := process || g_error_message;
     ROLLBACK;

    WHEN sour_uld_closed THEN
     g_error_number    := -1107;
     p_error_number  := g_error_number;
     g_error_message   := 'Source Uld is Closed';
     p_error_message := process || g_error_message;
     ROLLBACK;

    WHEN abort THEN
      p_error_number := g_error_number;
      p_error_message := process || g_error_message;
      ROLLBACK;
    WHEN bag_issuer_not_known THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Baggage Tag Issuer Code not known';
      ROLLBACK;
    WHEN invalid_bag_tag_format THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Invalid Baggage Tag format';
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,process || SQLERRM);
  END transfer_bag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
  ) AS

  v_bag_tag           bag.bag_tag%TYPE;
  v_pax_id            pax.pax_id%TYPE;
  v_bpmid             bpm_data.bpmid%TYPE DEFAULT 0;
  c_action            CONSTANT bag_trace.trace_action%TYPE := 'XFER_ULD';
  sour_uld_closed     exception;
  dest_uld_closed     exception;
  close_flag          number(10);

  -- Tracking variables
  l_event_count                number;
  l_no_of_errs                 number;
   CURSOR bags(p_source_uld_no   ulds.uld_no%TYPE
             ,p_flight_id       flight.flight_id%TYPE
             ) IS
    SELECT b.bag_tag
    FROM bag_v1 b
    WHERE b.uld_no = p_source_uld_no
    AND b.uld_flight_id = p_flight_id;

  l_is_a_hold         number;
  l_exists            number;

  BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Transfer the contents of one ULD to another ULD                     *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Generate a BPM id using generate_bpmid        Procedure  External   *
        * Obtain bag tags in the ULD                    Cursor     Local      *
        * Transfer all bags from one uld to another using xfer_bag              Procedure  External   *
        * Send Bag Processed Message using store_bpm2               Procedure  Internal   *
        * Dispense with the old ULD using deassign_uld              Procedure  Internal   *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    SELECT Count(*)
    INTO l_exists
    FROM dual
    WHERE NOT EXISTS (SELECT 1 FROM ULDS WHERE FLIGHT_ID=p_flight_id AND ULD_NO=p_destination_uld_no);

    IF l_exists = 1 THEN
      IF NOT is_it_hold (p_destination_uld_no) THEN
          g_error_message := 'uld_no '
                 || p_destination_uld_no ||
                 ' ';
        g_error_number := -211;
        SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
        RAISE abort;
      END IF;
    END IF;

    -- Fire any automatic tracking events for this bag (ignore errors, as these
    -- should not affect the operation itself)

    tracking.auto_events(0, p_flight_id, p_source_uld_no, v_pax_id, 0, p_user_id,
                         p_location_code, NULL, l_event_count, l_no_of_errs);

    process := 'generate_bpmid : ';

    IF bpm2_required(p_flight_id,'5') THEN
      generate_bpmid(v_bpmid);
    ELSE
      v_bpmid := 0;
    END IF;

    process := 'transfer_uld : ';

    close_flag := wan_msg.get_uld_close_flag(p_destination_uld_no, p_flight_id);

    IF (close_flag = -1099) THEN
       raise dest_uld_closed;
    END IF;

    close_flag := wan_msg.get_uld_close_flag(p_source_uld_no, p_flight_id);

    IF (close_flag = -1099) THEN
       raise sour_uld_closed;
    END IF;


    OPEN bags(p_source_uld_no, p_flight_id);

    LOOP
      FETCH bags INTO v_bag_tag;
      IF bags%ROWCOUNT = 0 THEN
        g_error_number := -1;
        g_error_message := 'no bags for ULD '
             || p_source_uld_no ||
             ' on flight '
             || p_flight_id;
        CLOSE bags;
        RAISE abort;
      END IF;
      EXIT WHEN bags%NOTFOUND;
      transfer_bag(p_location_code
      ,p_user_id
      ,p_flight_id
      ,v_bag_tag
      ,p_source_uld_no
      ,p_destination_uld_no
      ,p_serial_no
      ,p_error_message
      ,p_error_number
      );
    END LOOP;

    CLOSE bags;

    process := 'store_bpm2 : ';

    IF bpm2_required(p_flight_id,'5') THEN
      process := 'store_bpm2 : ';
      store_bpm2(p_location_code
    ,p_user_id
    ,p_flight_id
    ,c_action
    ,p_source_uld_no
    ,p_destination_uld_no
    ,v_bpmid
    ,'0' -- scanner_id (not needed)
    ,'0' -- pax_id (not_needed)
    );
    END IF;

    -- if it's a hold then do not de-assign it, not needed to do so
    BEGIN
       SELECT COUNT(*) INTO l_is_a_hold FROM holds WHERE hold_no = p_source_uld_no;
       EXCEPTION WHEN NO_DATA_FOUND THEN
          l_is_a_hold := 0;
    END; -- of exception block

    IF l_is_a_hold = 0 THEN -- is it not a hold

       process := 'deassign_uld : ';

       deassign_uld(p_source_uld_no
                   ,p_user_id
                   ,p_location_code
                   ,g_error_number
                   );

       IF g_error_number <> 0 THEN
          g_error_message := 'error for ULD '
                         || p_source_uld_no ||
                            ' - ';
          SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;

          RAISE abort;
       END IF;

    END IF; -- end of if not a hold then de-assign ULD

    p_error_number := 0;
    p_error_message :=  'transfer_uld successful from ULD '
            || p_source_uld_no ||
            ' to ULD '
            || p_destination_uld_no ||
            ' on flight '
            || p_flight_id;

    COMMIT;

  EXCEPTION

    WHEN dest_uld_closed THEN
     g_error_number    := -1106;
     p_error_number  := g_error_number;
     g_error_message   := 'Destination Uld is Closed';
     p_error_message := process || g_error_message;
     ROLLBACK;

     WHEN sour_uld_closed THEN
     g_error_number    := -1107;
     p_error_number  := g_error_number;
     g_error_message   := 'Source Uld is Closed';
     p_error_message := process || g_error_message;
     ROLLBACK;

    WHEN abort THEN
      p_error_number := g_error_number;
      p_error_message := process || g_error_message;
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,process || SQLERRM);
  END transfer_uld;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        v_tagtrak_password              tagtrak_users.tagtrak_password%TYPE;
        l_process         VARCHAR2(30);
        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Add a new user to the tagtrak system                    *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Encrypt the password using encrypt_password               Procedure  External   *
        * Add a new user to the system                  Query      Local      *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    v_tagtrak_password := user_authentication.encrypt_password (p_tagtrak_password );
    l_process := 'add user : ';

    INSERT INTO tagtrak_users
    (user_id
    ,group_name
    ,surname
    ,firstname
    ,loginname
    ,retire_date
    ,create_date
    ,tagtrak_password
    ,last_user
    ,retired
    ,archive_flag
    ,agent_code
    ,lock_flag
    ,pwd_expired_flag
    ,pwd_set_date
    ,failed_logins
    ) VALUES
    (0
    ,p_group_name
    ,p_surname
    ,p_firstname
    ,p_loginname
    ,NULL
    ,SYSDATE
    ,v_tagtrak_password
    ,p_user_id
    ,'N'
    ,0
    ,p_agent_code
    ,0
    ,0
    ,SYSDATE
    ,0
    );


    p_error_number := 0;
    p_error_message := 'add_user : successful for '
                        || RTRIM(p_firstname) ||
                        ' '
                        || RTRIM(p_surname) ||
                        ' as '
                        || p_loginname;

    COMMIT;

        EXCEPTION
    WHEN integrity_constraint THEN
      p_error_number := SQLCODE;
      p_error_message := 'add_user : invalid group name / agent code combination for '
            || RTRIM(p_group_name) ||
                        ' and '
                        || p_agent_code;
      ROLLBACK;
    WHEN DUP_VAL_ON_INDEX THEN
            p_error_number := -20027;
            SELECT CONCAT('add_user : ', error_description)
            INTO p_error_message FROM error WHERE error_id = -20027;
      ROLLBACK;
    WHEN OTHERS THEN
            p_error_number := SQLCODE;
            p_error_message := 'add_user : ' || SQLERRM;
      ROLLBACK;
            raise_application_error(-20000,l_process || SQLERRM);
        END add_user;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        v_retired                   tagtrak_users.retired%TYPE;
        l_process         VARCHAR2(30);
        l_error_number NUMBER DEFAULT 0;
        l_error_message VARCHAR2(512);

        CURSOR retired_user(p_user_id tagtrak_users.user_id%TYPE) IS
    SELECT  retired
    FROM            tagtrak_users
    WHERE           user_id = p_user_id
    FOR UPDATE OF retired;
        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Deny a user access to the tagtrak system                *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Find the tagtrak user             Cursor     Local      *
        * Retire the tagtrak user                       Query      Local      *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    OPEN retired_user(p_user_id);
    l_process := 'update tagtrak_users : ';

    LOOP
            FETCH   retired_user INTO v_retired;
        IF retired_user%ROWCOUNT = 0 THEN
                l_error_number := -102;   -- unknown user
                SELECT error_description INTO l_error_message FROM error WHERE error_id = -102;
                CLOSE retired_user;
                RAISE abort;
        ELSIF v_retired = 'Y' THEN
                l_error_number := -1;
                l_error_message := 'user_id '
                            || p_user_id ||
                                 ' is already retired';
                CLOSE retired_user;
                RAISE abort;
        ELSE
                UPDATE  tagtrak_users
                SET             retired = 'Y'
                ,           retire_date = SYSDATE
                ,           last_user       = p_last_user
                WHERE   CURRENT OF retired_user;
        END IF;
        EXIT; -- only expect one row at most
    END LOOP;

    CLOSE retired_user;

    p_error_number := 0;
    p_error_message := 'retire_user successful for user_id '
                        || p_user_id;

    COMMIT;

        EXCEPTION
    WHEN abort THEN
      p_error_number := l_error_number;
      p_error_message := l_process || l_error_message;
      ROLLBACK;
    WHEN OTHERS THEN
            p_error_number := SQLCODE;
            p_error_message := 'retire_user : ' || SQLERRM;
            raise_application_error(-20000,l_process || SQLERRM);
        END retire_user;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        l_process         VARCHAR2(30);
        l_error_number NUMBER DEFAULT 0;
        l_error_message VARCHAR2(512);

        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Allow a retired user access to the tagtrak system                   *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Find the tagtrak user             Cursor     Local      *
        * Unretire the tagtrak user                     Query      Local      *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    l_process := 'unretire_user : ';
    UPDATE  tagtrak_users
            SET             retired = 'N'
            ,           retire_date = NULL
            ,           last_user       = p_last_user
            WHERE   user_id = p_user_id;

    IF SQL%NOTFOUND THEN
            l_error_number := -102;
            SELECT error_description INTO l_error_message FROM error WHERE error_id = -102;
            RAISE abort;
    END IF;

    p_error_number := 0;
    p_error_message := 'unretire_user successful for user_id '
                        || p_user_id;
    COMMIT;

        EXCEPTION
    WHEN abort THEN
      p_error_number := l_error_number;
      p_error_message := l_process || l_error_message;
      ROLLBACK;
    WHEN OTHERS THEN
            p_error_number := SQLCODE;
            p_error_message := 'unretire_user : ' || SQLERRM;
      ROLLBACK;
            raise_application_error(-20000,l_process || SQLERRM);
        END unretire_user;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        l_process         VARCHAR2(30);
        l_error_number NUMBER DEFAULT 0;
        l_error_message VARCHAR2(512);

        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Allow a locked out user access to the tagtrak system                *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Reset the log flag to unlocked                Query      Local      *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    l_process := 'unlock_user : ';
    UPDATE  tagtrak_users
            SET             lock_flag = 0
            ,           last_user       = p_last_user
            WHERE   user_id = p_user_id;

    IF SQL%NOTFOUND THEN
            p_error_number := -102;
            SELECT error_description INTO l_error_message FROM error WHERE error_id = -102;
            RAISE abort;
    END IF;

    p_error_number := 0;
    p_error_message := 'unlock_user successful for user_id '
                        || p_user_id;
    COMMIT;

        EXCEPTION
    WHEN abort THEN
      p_error_number := l_error_number;
      p_error_message := l_process || l_error_message;
      ROLLBACK;
    WHEN OTHERS THEN
            p_error_number := SQLCODE;
            p_error_message := 'unlock_user : ' || SQLERRM;
      ROLLBACK;
            raise_application_error(-20000,l_process || SQLERRM);
        END unlock_user;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
  ) AS
  l_process         VARCHAR2(30);
  l_error_number NUMBER DEFAULT 0;
  l_error_message VARCHAR2(512);

  BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Change the name of a tagtrak user group                 *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        * Update the name of the group_name with a new name         Query      Local      *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    l_process := 'change_group_name : ';
    UPDATE  tagtrak_users
      SET   group_name = p_group_name
      ,     last_user = p_last_user
      WHERE user_id = p_user_id;

    IF SQL%NOTFOUND THEN
      l_error_number := -102;
      SELECT error_description INTO l_error_message FROM error WHERE error_id = -102;
            RAISE abort;
    END IF;

    l_process := 'delete tagtrak_users_role : ';

    DELETE FROM tagtrak_users_role WHERE user_id = p_user_id;

    l_process := 'insert tagtrak_users_role : ';

    INSERT INTO tagtrak_users_role (user_id, user_role_id)
    SELECT tu.user_id, ur.user_role_id
    FROM   tagtrak_users tu, user_role ur
    WHERE  tu.user_id           = p_user_id
    AND    tu.agent_code        = ur.agent_code
    AND    TRIM (tu.group_name) = ur.user_role_name;

    IF SQL%ROWCOUNT = 0 THEN
      RAISE integrity_constraint;
    END IF;

    p_error_number := 0;
    p_error_message := 'change_group_name successful for user_id '
            || p_user_id;
    COMMIT;

  EXCEPTION
    WHEN abort THEN
      p_error_number := l_error_number;
      p_error_message := l_process || l_error_message;
      ROLLBACK;
    WHEN integrity_constraint THEN
      p_error_number := SQLCODE;
      p_error_message := 'change_group_name : invalid user_group_name for '
            || p_group_name;
      ROLLBACK;
    WHEN OTHERS THEN
      p_error_number := SQLCODE;
      p_error_message := 'change_group_name : ' || SQLERRM;
      ROLLBACK;
            raise_application_error(-20000,l_process || SQLERRM);
  END change_group_name;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        l_process         VARCHAR2(30);
        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Create a new tagtrak user group                         *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        * Create a user group with UserGroup Setup (j) and manager (m) privs    Query      Local      *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    l_process := 'change_group_name : ';
    l_process := 'insert user_group : ';
    INSERT INTO user_group
            (group_name
            ,priv1
            ,priv2
            ,last_user
            ,agent_code
            ) VALUES
            (p_group_name
            ,'m'
            ,'j'
            ,p_last_user
            ,p_agent_code);

    p_error_number := 0;
    p_error_message := 'add_user_group successful for '
                        || RTRIM(p_group_name);
    COMMIT;

  EXCEPTION
    WHEN integrity_constraint THEN
      p_error_number := SQLCODE;
      p_error_message := 'add_user_group : invalid agent_code for '
            || p_agent_code;
      ROLLBACK;
    WHEN DUP_VAL_ON_INDEX THEN
            p_error_number := -20027;
            SELECT CONCAT('add_user_group : ', error_description)
            INTO p_error_message FROM error WHERE error_id = -20027;
      ROLLBACK;
    WHEN OTHERS THEN
      p_error_number := SQLCODE;
      p_error_message := 'change_group_name : ' || SQLERRM;
      ROLLBACK;
      raise_application_error(-20000,l_process || SQLERRM);

END add_user_group;

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

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
  ) IS

  l_flight_id   flight.flight_id%TYPE;

BEGIN

  add_a_flight_2(p_flight_no,
               p_flight_date,
               p_flight_sch_time,
               p_agent_code,
               p_flight_destination,
               p_user_id,
               p_serial_no,
               p_error_message,
               p_error_number,
               l_flight_id);

END add_a_flight;

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

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
  ) AS

  l_normalised_flight_no  flight.flight_no%TYPE;
  l_dcs_flight_no         flight.dcs_flight_no%TYPE;
  l_flight_sch_time       flight.flight_sch_time%TYPE := p_flight_sch_time;
  l_departure_time        VARCHAR2(20);
  l_dept_airport           site_info.airport_code%TYPE;
  l_process               VARCHAR2(30);

BEGIN

  --Check validity of passed in flight_no and format it.
  IF valid_flight_digits(p_flight_no) THEN

    l_process := 'normalise_flight_no : ';
    l_normalised_flight_no := normalise_flight_no(p_flight_no);
    -- remove any space for dcs_flight_no
    l_dcs_flight_no := RTRIM(SUBSTR(l_normalised_flight_no, 1, 3))
                       || RTRIM(SUBSTR(l_normalised_flight_no,4,5));

  ELSE

    g_error_number := -5;
    g_error_message := 'flight '|| RTRIM(p_flight_no) ||' is an invalid flight number';
    RAISE abort;

  END IF;

  l_process := 'valid_flight_prefix : ';

  IF NOT valid_flight_prefix(SUBSTR(l_normalised_flight_no,1,3)) THEN

    g_error_number := -2;
    g_error_message := 'flight '|| RTRIM(l_normalised_flight_no) ||' is a restricted flight';
    RAISE abort;

  END IF;

  l_process := 'valid_agent_code : ';

  IF NOT valid_agent_code(p_agent_code, l_normalised_flight_no) THEN

    g_error_number := -3;
    g_error_message := 'agent_code '|| RTRIM(p_agent_code) ||' is an invalid agent_code for flight '
                       || l_normalised_flight_no;
    RAISE abort;

  ELSE

    SELECT airport_code
    INTO l_dept_airport
    FROM agents a, site_info si
    WHERE agent_code = p_agent_code
    AND a.site_id  = si.site_id ;

    l_process := 'add_flight : ';
    l_departure_time := departure_time(p_flight_date, l_flight_sch_time);

    add_flight(l_normalised_flight_no,
               LPAD(p_flight_date,5,'0'),
               l_dept_airport,
               l_flight_sch_time,
               'FO',     -- flight_status
               'DUMMY',  -- process_name
               '1',      -- unit_name
               'DUMMY',  -- dcs_id
               0,        -- subunit_name
               l_departure_time,
               l_dcs_flight_no,
               p_flight_id,
               NULL -- bvis subheader
               );

    l_process := 'add_flight_dests : ';
    add_flight_dests(p_flight_id, p_flight_destination);

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id,
                                      p_serial_no,
                                      p_flight_id,
                                      NULL,
                                      0,
                                      NULL,
                                      0,
                                      0);

  END IF;

  p_error_number := 0;
  p_error_message := 'add_a_flight successful for '
                     || RTRIM(l_normalised_flight_no) ||
                     ' on '
                     || LPAD(p_flight_date,5,'0');
  COMMIT;

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    p_error_number := -138;
    SELECT CONCAT('add_flight : ', error_description)
    INTO p_error_message FROM error WHERE error_id = -138;
    ROLLBACK;

  WHEN abort THEN
    p_error_number := g_error_number;
    p_error_message := l_process || g_error_message;
    ROLLBACK;

  WHEN OTHERS THEN
    ROLLBACK;
    raise_application_error(-20000,l_process || SQLERRM);

END add_a_flight_2;

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

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
  ) AS
  
  l_flight_sch_time  flight.flight_sch_time%TYPE := p_flight_sch_time;
  l_departure_time   flight.departure_time%TYPE;
  l_flight_id        flight.flight_id%TYPE;
  l_flight_no        flight.flight_no%TYPE;
  l_process          VARCHAR2(30);
  
BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                            *
  * PURPOSE                                                                                    *
  *                                                                                            *
  * Change the departure time of a flight                                                      *
  *                                                                                            *
  * PROCESS                                                              METHOD     TYPE       *
  *                                                                                            *
  * Validate the flight no using valid_flight_digits                     Function   Internal   *
  * Put the flight no into the correct format using normalise_flight_no  Function   External   *
  * Validate the flight prefix using valid_flight_prifix                 Function   Internal   *
  * Validate the agent code using valid_agent_code                       Function   Internal   *
  * Change the departure time and scheduled departure time               Query      Local      *
  * Determine the correct departure time using departure_time            Function   Internal   *
  *                                                                                            *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
  l_departure_time := TO_DATE(departure_time(p_flight_date,
                                             l_flight_sch_time),'DD-MON-YYYY HH24:MI');

  IF valid_flight_digits(p_flight_no) THEN
  
    l_process := 'normalise_flight_no : ';
    l_flight_no := normalise_flight_no(p_flight_no);
    
  ELSE
   
    g_error_number := -5;
    g_error_message := 'flight '|| RTRIM(p_flight_no) ||' is an invalid flight number';
    RAISE abort;
    
  END IF;
  
  l_process := 'f_flight_id : ';
  l_flight_id := f_flight_id(l_flight_no, 
                             LPAD(p_flight_date,5,'0'),
                             multi_site.get_agent_siteid(p_agent_code));
  l_process := 'valid_flight_prefix : ';
  
  IF NOT valid_flight_prefix(SUBSTR(l_flight_no,1,3)) THEN
    
    g_error_number := -2;
    g_error_message := 'flight '|| RTRIM(l_flight_no) ||' is a restricted flight';
    RAISE abort;
    
  END IF;
  
  l_process := 'valid_agent_code : ';
  
  IF NOT valid_agent_code(p_agent_code, l_flight_no) THEN
    
    g_error_number := -3;
    g_error_message := 'agent_code '|| RTRIM(p_agent_code) ||
                        ' is an invalid agent_code for flight '|| l_flight_no;
    RAISE abort;

  ELSE

    l_process := 'update flight : ';
    UPDATE  flight
    SET departure_time = l_departure_time,
        flight_sch_time = p_flight_sch_time
    WHERE flight_id = l_flight_id;
    
    IF SQL%NOTFOUND THEN
    
      g_error_number := -139;
      SELECT error_description INTO g_error_message FROM error WHERE error_id = -139;
      RAISE abort;
      
    END IF;
    
    l_process := 'delete flight_dests : ';
    DELETE FROM flight_dests
    WHERE flight_id = l_flight_id;

    l_process := 'add_flight_dests : ';
    add_flight_dests(l_flight_id, p_flight_destination);

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, 
                                      p_serial_no, 
                                      l_flight_id, 
                                      NULL, 
                                      0, 
                                      NULL, 
                                      0, 
                                      0);

  END IF;

  p_error_number := 0;
  p_error_message := 'update_a_flight successful for '|| RTRIM(l_flight_no) ||
                     ' on '|| LPAD(p_flight_date,5,'0');

  COMMIT;

EXCEPTION
  WHEN abort THEN
    p_error_number := g_error_number;
    p_error_message := l_process || g_error_message;
    ROLLBACK;
  WHEN OTHERS THEN
    ROLLBACK;
    raise_application_error(-20000,l_process || SQLERRM);

END update_a_flight;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

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
  ) AS
        v_flight_id                     flight.flight_id%TYPE;
  v_flight_no           flight.flight_no%TYPE;
  psiteid               flight.site_id%type;
  l_process             VARCHAR2(30);
        FUNCTION loaded_bags
    (p_flight_id                IN          flight.flight_id%TYPE
    ) RETURN BOOLEAN IS
    CURSOR c_bag_tag(p_flight_id flight.flight_id%TYPE) IS
            SELECT  bag_tag
            FROM            bag_v2
            WHERE           flight_id = p_flight_id
            AND         (delete_marker = 0
            OR          loaded_status = 'Y')
            FOR UPDATE OF bag_tag;

            v_bag_tag               bag.bag_tag%TYPE;
    BEGIN
            l_process := 'loaded_bags : ';
            OPEN c_bag_tag(p_flight_id);
            FETCH c_bag_tag INTO v_bag_tag;
            IF c_bag_tag%NOTFOUND THEN
        RAISE NO_DATA_FOUND;
            END IF;
            CLOSE c_bag_tag;
            RETURN TRUE;
    EXCEPTION
            WHEN NO_DATA_FOUND THEN
        CLOSE c_bag_tag;
        RETURN FALSE;
    END loaded_bags;

  BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Remove a flight                             *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Validate the flight no using valid_flight_digits          Function   Internal   *
        * Put the flight no into the correct format using normalise_flight_no   Function   External   *
        * Validate the flight prefix using valid_flight_prifix              Function   Internal   *
        * Validate the agent code using valid_agent_code            Function   Internal   *
        * Validate there are no loaded bags on the flight using loaded bags     Function   Local      *
        * Delete a flight                   Query      Local      *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    IF valid_flight_digits(p_flight_no) THEN
            l_process := 'normalise_flight_no : ';
            v_flight_no := normalise_flight_no(p_flight_no);
    ELSE
            g_error_number := -5;
            g_error_message :=        'flight '
                        || RTRIM(p_flight_no) ||
                        ' is an invalid flight number';
            RAISE abort;
    END IF;
    psiteid := multi_site.get_agent_siteid(p_agent_code);
    l_process := 'f_flight_id : ';
    v_flight_id := f_flight_id(v_flight_no, LPAD(p_flight_date,5,'0'),psiteid);
    l_process := 'valid_flight_prefix : ';
    IF NOT valid_flight_prefix(SUBSTR(v_flight_no,1,3)) THEN
            g_error_number := -2;
            g_error_message :=        'flight '
                        || RTRIM(v_flight_no) ||
                        ' is a restricted flight';
            RAISE abort;
    END IF;
    l_process := 'valid_agent_code : ';
    IF NOT valid_agent_code(p_agent_code, v_flight_no) THEN
            g_error_number := -3;
            g_error_message :=        'agent_code '
                        || RTRIM(p_agent_code) ||
                        ' is an invalid agent_code for flight '
                        || v_flight_no;
            RAISE abort;
    ELSIF loaded_bags(v_flight_id) THEN
            g_error_number := -4;
            g_error_message :=        'flight '
                        || RTRIM(v_flight_no) ||
                        ' has loaded bags on board';
            RAISE abort;
    ELSE
            l_process := 'delete_flight : ';
            DELETE FROM flight WHERE flight_id = v_flight_id;
            IF SQL%NOTFOUND THEN
        g_error_number := -139;
        SELECT error_description INTO g_error_message FROM error WHERE error_id = -139;
        RAISE abort;
            END IF;

    END IF;

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, p_serial_no, v_flight_id, '', 0, '', 0, 0);

    p_error_number := 0;
    p_error_message :=      'delete_a_flight successful for '
                        || RTRIM(v_flight_no) ||
                        ' on '
                        || LPAD(p_flight_date,5,'0');
    COMMIT;

        EXCEPTION
    WHEN abort THEN
      p_error_number := g_error_number;
      p_error_message := l_process || g_error_message;
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,l_process || SQLERRM);
  END delete_a_flight;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        v_error_number          NUMBER;
        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Find the loaded bag counts for a flight split between Local (l) and Transfer (t) and then   *
        * divided by class: first (f), business (j) and economy (y).          *
        * tf is first class transfer bags                         *
        * ty is economy class local bags and so on ...            *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Find out what the loading progress is                   *
  * using flts_ldg_prog_by_time_pack.flts_ldg_prog_by_time    Procedure  External   *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    flts_ldg_prog_by_time_pack.flts_ldg_prog_by_time
            (p_flight_id
            ,p_flight_no_in
            ,p_time_1
            ,p_time_2
            ,p_agent_code
            ,v_error_number
            ,p_flight_no_out
            ,p_flight_status
            ,p_flight_destination
            ,p_flight_sch_time
            ,p_description
            ,tf
            ,tj
            ,ty
            ,tr
            ,lf
            ,lj
            ,ly
            ,lr
            );
        END loading_progress;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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 IS
  l_flight_no_out          flts_ldg_prog_by_time_pack.char8;
  l_flight_status          flts_ldg_prog_by_time_pack.char2;
  l_flight_destination     flts_ldg_prog_by_time_pack.char3;
  l_flight_sch_time        flts_ldg_prog_by_time_pack.char4;
  l_description            flts_ldg_prog_by_time_pack.char7;
  tf                       flts_ldg_prog_by_time_pack.tnumber;
  tj                       flts_ldg_prog_by_time_pack.tnumber;
  ty                       flts_ldg_prog_by_time_pack.tnumber;
  tr                       flts_ldg_prog_by_time_pack.tnumber;
  lf                       flts_ldg_prog_by_time_pack.tnumber;
  lj                       flts_ldg_prog_by_time_pack.tnumber;
  ly                       flts_ldg_prog_by_time_pack.tnumber;
  lr                       flts_ldg_prog_by_time_pack.tnumber;
  l_results_tab           loading_prog_by_time_tab := loading_prog_by_time_tab();
  l_cursor           SYS_REFCURSOR;
  v_error_number          NUMBER;

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                            *
  * PURPOSE                                                                                    *
  *                                                                                            *
  * Find the loaded bag counts for a flight split between Local (l) and Transfer (t) and then  *
  * divided by class: first (f), business (j) and economy (y).                                 *
  * tf is first class transfer bags                                                            *
  * ty is economy class local bags and so on ...                                               *
  * This function returns the data in a ref cursor.                                            *
  *                                                                                            *
  * PROCESS                                                   METHOD     TYPE                  *
  *                                                                                            *
  * Find out what the loading progress is                                                      *
  * using flts_ldg_prog_by_time_pack.flts_ldg_prog_by_time    Function  External               *
  *                                                                                            *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *  */

BEGIN

  flts_ldg_prog_by_time_pack.flts_ldg_prog_by_time(p_flight_id
                                                  ,p_flight_no_in
                                                  ,p_time_1
                                                  ,p_time_2
                                                  ,p_agent_code
                                                  ,v_error_number
                                                  ,l_flight_no_out
                                                  ,l_flight_status
                                                  ,l_flight_destination
                                                  ,l_flight_sch_time
                                                  ,l_description
                                                  ,tf
                                                  ,tj
                                                  ,ty
                                                  ,tr
                                                  ,lf
                                                  ,lj
                                                  ,ly
                                                  ,lr);

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

      BEGIN

        --make room in sql table.
        l_results_tab.extend(1);
        --use constructor to populate.
        l_results_tab(n) := loading_prog_by_time_rec(
                                            l_flight_no_out(n),
                                            l_flight_status(n),
                                            l_flight_destination(n),
                                            l_flight_sch_time(n),
                                            l_description(n),
                                            tf(n),
                                            tj(n),
                                            ty(n),
                                            tr(n),
                                            lf(n),
                                            lj(n),
                                            ly(n),
                                            lr(n)
                                            );

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

      END;

   END LOOP;

   OPEN l_cursor FOR
   SELECT *
   FROM TABLE(CAST(l_results_tab AS loading_prog_by_time_tab));

   RETURN l_cursor;

END loading_progress_2;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
  ) AS

  v_uld_no              ulds.uld_no%TYPE;
  v_source_uld_no       ulds.uld_no%TYPE;
  v_destination_uld_no  ulds.uld_no%TYPE;
  v_flight_id           flight.flight_id%TYPE;
  v_pax_id              pax.pax_id%TYPE;
  v_bag_tag             bag.bag_tag%TYPE;
  v_bag_tag1            bag.bag_tag1%TYPE;
  v_bag_tag2a           bag.bag_tag2a%TYPE;
  v_bag_tag2b           bag.bag_tag2b%TYPE;
  v_bag_tag3            bag.bag_tag3%TYPE;
  v_security_clearance_status bag.security_clearance_status%TYPE;
  v_location_code       location.location_code%TYPE;
  v_user_id             tagtrak_users.user_id%TYPE;
  v_bpmid               bpm_data.bpmid%TYPE DEFAULT 0;
  l_tracking_err_event         tracking_events.event_no%type;
  l_tracking_event_description tracking_events.description%type;
  l_event_count                number;
  l_no_of_errs                 number;
  l_bag_group                  bag_group.group_no%type;
  n_bag_tag             bag.bag_tag%TYPE;                                                          -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)

BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                             *
  * PURPOSE                                                                                     *
  *                                                                                             *
  * Handle a duplicate bag tag by either loading, offloading or transferring it as requested    *
  *                                                                                             *
  * PROCESS                                                   METHOD     TYPE                   *
  *                                                                                             *
  * Validate parameters                                       Code       Local                  *
  * Get data                                                  Query      Local                  *
  * Either:                                                                                     *
  * Load a bag onto a ULD using load_bag                      Procedure  External               *
  * Maintain traceability using add_bag_trace_record          Procedure  External               *
  * Send Bag Processed Message using store_bpm1               Procedure  Internal               *
  * Or:                                                                                         *
  * Transfer a bag from one uld to another using xfer_bag     Procedure  External               *
  * Maintain traceability using add_bag_trace_record          Procedure  External               *
  * Send Bag Processed Message using store_bpm1               Procedure  Internal               *
  * Or:                                                                                         *
  * Unload a bag from a ULD using offload_bag                 Procedure  External               *
  * Maintain traceability using add_bag_trace_record          Procedure  External               *
  * Generate a BPM id using generate_bpmid                    Procedure  External               *
  * Send Bag Processed Message using store_bpm2               Procedure  Internal               *
  *                                                                                             *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

  process := 'validate parameters : ';

  IF p_function_type NOT IN ('XFER', 'LOAD', 'LOAD WHERE', 'MWS_LOAD', 'OFFLOAD') THEN      -- UAS/FALCP/CA/00699

    g_error_number := -1;
    g_error_message := 'function type '|| RTRIM(p_function_type) ||' is invalid';
    RAISE abort;

  END IF;

  -- Create a trace entry (HANDLER_TRACE) for this flight
  handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

  -- Ensure bag tag is in numeric format
  process := 'convert bag tag : ';                -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
  n_bag_tag := get_numeric_bag_tag(p_bag_tag);    -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)

  BEGIN

    process := 'Query Select : ';

    SELECT b.pax_id,
           b.bag_tag1,
           NVL(b.bag_tag2a,'-'),
           b.bag_tag2b,
           b.bag_tag3,
           b.uld_no
    INTO v_pax_id,
         v_bag_tag1,
         v_bag_tag2a,
         v_bag_tag2b,
         v_bag_tag3,
         v_uld_no
    FROM bag_v2 b
    WHERE substr(b.bag_tag, 2) = substr(n_bag_tag, 2)    -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
    AND b.flight_id = p_flight_id
    AND b.pax_id = p_pax_id
    AND ((b.authority_to_load_dcs = 'Y'
          AND b.authority_to_load_brs = 'Y'
          AND b.delete_marker = 0
          AND ((b.loaded_status IN ('N','U') AND p_function_type IN ('LOAD', 'LOAD WHERE', 'MWS_LOAD'))       -- UAS/FALCP/CA/00699
               OR (b.loaded_status = 'Y' AND p_function_type = 'XFER')))
         OR (b.loaded_status = 'Y' AND p_function_type = 'OFFLOAD'));

  EXCEPTION
    WHEN NO_DATA_FOUND THEN

      g_error_message := 'bag '
                         || n_bag_tag ||
                         ' not found on flight '
                         || p_flight_id ||
                         ' for passenger '
                         || p_pax_id;    -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
      g_error_number := SQLCODE;
      RAISE abort;

    WHEN TOO_MANY_ROWS THEN

      g_error_number := -1003;
      SELECT error_description INTO g_error_message FROM error WHERE error_id = -1003;

      IF p_function_type IN ('LOAD', 'LOAD WHERE', 'MWS_LOAD') THEN -- UAS/FALCP/CA/00699

        SELECT b.pax_id,
               b.bag_tag1,
               NVL(b.bag_tag2a,'-'),
               b.bag_tag2b,
               b.bag_tag3,
               b.uld_no
        INTO v_pax_id,
             v_bag_tag1,
             v_bag_tag2a,
             v_bag_tag2b,
             v_bag_tag3,
             v_uld_no
        FROM bag_v2 b
        WHERE substr(b.bag_tag, 2) = substr(n_bag_tag, 2)       -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
        AND b.flight_id = p_flight_id
        AND b.pax_id = p_pax_id
        AND b.authority_to_load_dcs = 'Y'
        AND b.authority_to_load_brs = 'Y'
        AND b.delete_marker = 0
        AND b.loaded_status IN ('N','U')
        AND ROWNUM = 1;

        -- If bagflo interface set up then store a BPM for the error
        -- and abandon further processing but commit the store of the BPM
        IF store_bpm1(p_location_code,
                      p_user_id,
                      p_flight_id,
                      v_pax_id,
                      n_bag_tag,                                   -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                      f_pax_sequence_no(p_pax_id, p_flight_id),
                      TO_CHAR(g_error_number)) THEN

          RAISE abandon;

        END IF;

      END IF;

      g_error_number := -1003;
      SELECT error_description INTO g_error_message FROM error WHERE error_id = -1003;
      RAISE abort;

    WHEN OTHERS THEN
      RAISE;

  END;

  IF p_function_type IN ('LOAD', 'LOAD WHERE', 'MWS_LOAD') THEN     -- UAS/FALCP/CA/00699

    SELECT security_clearance_status
    INTO v_security_clearance_status
    FROM bag b
    WHERE b.pax_id = v_pax_id
    AND b.bag_tag2b = v_bag_tag2b
    AND b.bag_tag3 = v_bag_tag3;

    IF v_security_clearance_status = 'N' THEN

      g_error_number := -1068;
      g_error_message := 'error for bag '|| n_bag_tag ||' - ';   -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
      RAISE abort;

    END IF;

    -- Fire any automatic tracking events for this bag (ignore errors, as these should not affect the operation itself)

    tracking.auto_events(n_bag_tag,
                         p_flight_id,
                         NULL,
                         v_pax_id,
                         0,
                         p_user_id,  -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                         p_location_code,
                         NULL,
                         l_event_count,
                         l_no_of_errs);

    -- Check that all mandatory tracking events have been cleared

    tracking.check_events_clear(p_user_id,
                                n_bag_tag,
                                v_pax_id,
                                p_flight_id,            -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                                l_tracking_err_event,
                                l_tracking_event_description,
                                g_error_number,
                                l_bag_group);

    IF g_error_number < 0 THEN

      g_error_message := 'Event ' || l_tracking_err_event || ' failed: ' ||l_tracking_event_description;
      RAISE abort;

    END IF;

    process := 'load_bag : ';

    load_bag(v_bag_tag2a,
             v_bag_tag2b,
             v_bag_tag3,
             p_pax_id,
             p_source_uld_no,
             p_location_code,
             0,      -- scanner_id
             p_flight_id,
             g_error_number);

    IF g_error_number <> 0 THEN

      g_error_message := 'error for bag '|| n_bag_tag ||' in ULD '
                         || p_source_uld_no ||' - ';     -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
      RAISE abort;

    END IF;

    v_user_id := p_user_id;
    process := 'add_bag_trace : ';

    add_bag_trace_record(v_user_id,
                         p_location_code,
                         0,      -- error_code
                         n_bag_tag,        -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                         v_bag_tag1,
                         v_bag_tag2a,
                         v_bag_tag2b,
                         v_bag_tag3,
                         p_function_type, -- trace_action
                         0, -- scanner_id
                         p_flight_id,
                         p_pax_id,
                         p_destination_uld_no);    -- pass ULD number (Nigel, 01/04/04, CA598)

    IF store_bpm1(p_location_code,
                  p_user_id,
                  p_flight_id,
                  p_pax_id,
                  n_bag_tag,                                   -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                  f_pax_sequence_no(p_pax_id, p_flight_id),
                  p_function_type) THEN

      NULL;

    END IF;

  ELSIF p_function_type = 'OFFLOAD' THEN

    process := 'offload_bag : ';

    v_location_code         := p_location_code;
    v_flight_id             := p_flight_id;
    v_pax_id    := p_pax_id;

    -- Fire any automatic tracking events for this bag (ignore errors, as these
    -- should not affect the operation itself)
    tracking.auto_events(n_bag_tag,
                         p_flight_id,
                         NULL,
                         v_pax_id,
                         0,
                         p_user_id,                   -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                         p_location_code,
                         NULL,
                         l_event_count,
                         l_no_of_errs);

    offload_bag(v_bag_tag2a,
                v_bag_tag2b,
                v_bag_tag3,
                v_pax_id,
                v_uld_no,
                v_location_code,
                0,      -- scanner_id
                v_flight_id,
                g_error_number);

    IF g_error_number <> 0 THEN
      g_error_message :=  'error for bag '|| n_bag_tag ||' in ULD '
                          || p_source_uld_no ||' - ';             -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
      RAISE abort;

    END IF;

    v_user_id := p_user_id;

    process := 'add_bag_trace : ';

    add_bag_trace_record(v_user_id,
                         p_location_code,
                         0,      -- error_code
                         n_bag_tag,   -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                         v_bag_tag1,
                         v_bag_tag2a,
                         v_bag_tag2b,
                         v_bag_tag3,
                         p_function_type, -- trace_action
                         0, -- scanner_id
                         p_flight_id,
                         p_pax_id,
                         v_uld_no);    -- pass ULD number (Nigel, 01/04/04, CA598)

    IF store_bpm1(p_location_code,
                  p_user_id,
                  p_flight_id,
                  p_pax_id,
                  n_bag_tag,                                    -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                  f_pax_sequence_no(p_pax_id, p_flight_id),
                  p_function_type) THEN

      NULL;

    END IF;

  ELSIF p_function_type = 'XFER' THEN

    -- Fire any automatic tracking events for this bag (ignore errors, as these
    -- should not affect the operation itself)

    tracking.auto_events(n_bag_tag,
                         p_flight_id,
                         NULL,
                         v_pax_id,
                         0,
                         p_user_id,                  -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                         p_location_code,
                         NULL,
                         l_event_count,
                         l_no_of_errs);

    process := 'generate_bpmid : ';

    IF bpm_mode THEN
      IF bpm2_required(p_flight_id,f_bpm_type(p_function_type,p_uld_no => p_destination_uld_no)) THEN
        generate_bpmid(v_bpmid);
      ELSE
        v_bpmid := 0;
      END IF;
    ELSE
      v_bpmid := 0;
    END IF;

    v_bag_tag := n_bag_tag;       -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
    v_source_uld_no := p_source_uld_no;
    v_destination_uld_no := p_destination_uld_no;
    v_location_code := p_location_code;
    v_flight_id := p_flight_id;

    process := 'xfer_bag : ';

    xfer_bag(v_bag_tag,
             v_bag_tag2a,
             v_bag_tag2b,
             v_bag_tag3,
             v_pax_id,
             v_source_uld_no,
             v_destination_uld_no,
             v_location_code,
             0,
             NVL(v_bpmid,0),
             v_flight_id,
             g_error_number);

    IF g_error_number <> 0 THEN

      g_error_message := 'error for bag '|| n_bag_tag ||' from ULD '|| p_source_uld_no
                         ||' to ULD '|| p_destination_uld_no ||' - ';        -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
      RAISE abort;

    END IF;

    v_user_id := p_user_id;

    process := 'add_bag_trace : ';

    add_bag_trace_record(v_user_id,
                         p_location_code,
                         0,      -- error_code
                         n_bag_tag,          -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                         v_bag_tag1,
                         v_bag_tag2a,
                         v_bag_tag2b,
                         v_bag_tag3,
                         p_function_type, -- trace_action
                         0, -- scanner_id
                         p_flight_id,
                         p_pax_id,
                         p_destination_uld_no);    -- pass ULD number (Nigel, 01/04/04, CA598)

    IF bpm_mode and bpm2_required(p_flight_id,f_bpm_type(p_function_type,p_uld_no => p_destination_uld_no)) THEN

      process := 'store_bpm2 : ';
      store_bpm2(p_location_code,
                 p_user_id,
                 p_flight_id,
                 p_function_type,
                 p_source_uld_no,
                 p_destination_uld_no,
                 v_bpmid,
                 '0', -- scanner_id
                 p_pax_id);
    END IF;

  END IF;

  p_error_number := 0;
  p_error_message := 'action_duplicate_tag successful for bag '
                     || n_bag_tag ||                              -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                     ' on flight '|| p_flight_id ||' for passenger '|| p_pax_id;

  COMMIT;

EXCEPTION
  WHEN abort THEN
    p_error_number := g_error_number;
    p_error_message := process || g_error_message;
    ROLLBACK;

  WHEN abandon THEN
    p_error_number := g_error_number;
    p_error_message := process || g_error_message;
    COMMIT;

  WHEN bag_issuer_not_known THEN
    p_error_number := SQLCODE;
    p_error_message := process || ' Baggage Tag Issuer Code not known';
    ROLLBACK;

  WHEN invalid_bag_tag_format THEN
    p_error_number := SQLCODE;
    p_error_message := process || ' Invalid Baggage Tag format';
    ROLLBACK;

  WHEN OTHERS THEN
    ROLLBACK;
    raise_application_error(-20000,process || SQLERRM);

END action_duplicate_tag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
            ) AS
        v_flight_status          flts_px_bg_cnt_by_tim_mws_pack.char2;
        v_error_number           NUMBER DEFAULT 0;
BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Find the pax and bag counts for a flight by time split between Local (l) and Transfer (t)   *
        * and then divided by class: first (f), business (j) and economy (y).             *
        * tf is first class transfer bags                         *
        * ty is economy class local bags and so on ...            *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Find out what the loading progress is                   *
  * using flts_px_bg_cnt_by_tim_mws_pack.flts_pax_bag_count_by_time_mws   Procedure  External   *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
            flts_px_bg_cnt_by_tim_mws_pack.flts_pax_bag_count_by_time_mws
        (p_flight_id
        ,p_flight_no_in
        ,p_time_1
        ,p_time_2
        ,p_agent_code
        ,v_error_number
        ,p_flight_no_out
        ,v_flight_status
        ,p_flight_destination
        ,p_flight_sch_time
        ,p_type
        ,tf
        ,tj
        ,ty
        ,lf
        ,lj
        ,ly
        ,tot
        ,tr
        ,lr
        );
        p_flight_no_out(p_flight_no_out.COUNT) := '        ';
END pax_bag_count;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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 IS

         l_flight_no_out          flts_px_bg_cnt_by_tim_mws_pack.char8;
         l_flight_destination     flts_px_bg_cnt_by_tim_mws_pack.char3;
         l_flight_sch_time        flts_px_bg_cnt_by_tim_mws_pack.char4;
         l_type                   flts_px_bg_cnt_by_tim_mws_pack.char5;
         tf                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         lf                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         tj                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         lj                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         ty                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         ly                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         tr                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         lr                       flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         tot                      flts_px_bg_cnt_by_tim_mws_pack.tnumber;
         v_flight_status          flts_px_bg_cnt_by_tim_mws_pack.char2;
         v_error_number           NUMBER DEFAULT 0;
         l_results_tab            flts_pax_bag_count_by_time_tab := flts_pax_bag_count_by_time_tab();
         l_cursor                 SYS_REFCURSOR;

 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                             *
  * PURPOSE                                                                                     *
  *                                                                                             *
  * Find the pax and bag counts for a flight by time split between Local (l) and Transfer (t)   *
  * and then divided by class: first (f), business (j) and economy (y).                         *
  * tf is first class transfer bags                                                             *
  * ty is economy class local bags and so on ...                                                *
  * This function returns the data in a ref cursor                                              *
  *                                                                                             *
  * PROCESS                                                               METHOD     TYPE       *
  *                                                                                             *
  * Find out what the loading progress is                                                       *
  * using flts_px_bg_cnt_by_tim_mws_pack.flts_pax_bag_count_by_time_mws   Procedure  External   *
  *                                                                                             *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

BEGIN
flts_px_bg_cnt_by_tim_mws_pack.flts_pax_bag_count_by_time_mws
        (p_flight_id
        ,p_flight_no_in
        ,p_time_1
        ,p_time_2
        ,p_agent_code
        ,v_error_number
        ,l_flight_no_out
        ,v_flight_status
        ,l_flight_destination
        ,l_flight_sch_time
        ,l_type
        ,tf
        ,tj
        ,ty
        ,lf
        ,lj
        ,ly
        ,tot
        ,tr
        ,lr
        );
        l_flight_no_out(l_flight_no_out.COUNT) := '        ';

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

      BEGIN

        --make room in sql table.
        l_results_tab.extend(1);
        --use constructor to populate.
        l_results_tab(n) := flts_pax_bag_count_by_time_rec(
                l_flight_no_out(n),
                l_flight_destination(n),
                l_flight_sch_time(n),
                l_type(n),
                tf(n),
                lf(n),
                tj(n),
                lj(n),
                ty(n),
                ly(n),
                tr(n),
                lr(n),
                tot(n)
                );

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

      END;

 END LOOP;

   OPEN l_cursor FOR
   SELECT *
   FROM TABLE(CAST(l_results_tab AS flts_pax_bag_count_by_time_tab));
   RETURN l_cursor;
END pax_bag_count_2;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        v_flight_id             flight.flight_id%TYPE;
        v_pax_id            pax.pax_id%TYPE;
        v_pax_status                pax.pax_status%TYPE;
        v_delete_marker     pax.delete_marker%TYPE;
        l_process         VARCHAR2(30);
        l_error_number NUMBER DEFAULT 0;
        l_error_message VARCHAR2(512);

        CURSOR c_pax (p_flight_id flight.flight_id%TYPE
                ,p_pax_id_1 pax.pax_id%TYPE
                ,p_pax_id_2 pax.pax_id%TYPE
                ,p_pax_id_3 pax.pax_id%TYPE
                ,p_pax_id_4 pax.pax_id%TYPE
                ,p_pax_id_5 pax.pax_id%TYPE) IS
    SELECT flight_id, pax_id, pax_status, delete_marker
    FROM pax_v1
    WHERE flight_id = p_flight_id
    AND             pax_id IN (p_pax_id_1
                    ,p_pax_id_2
                    ,p_pax_id_3
                    ,p_pax_id_4
                    ,p_pax_id_5)
    FOR UPDATE OF pax_status;

        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Handle a duplicate bag tag by either loading, offloading or transferring it as requested    *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Obtain passenger details                      Cursor     Local      *
  * Either:                         *
        * Board all passengers for a flight             Query      Local      *
  * Or:                             *
        * Board up to 5 listed passengers who are checked in and not deleted    Query      Local      *
  * Or:                             *
        * Board all passengers except up to 5 listed passengers     Query      Local      *
  * Or:                             *
        * Unboard all passengers for a flight           Query      Local      *
  * Or:                             *
        * Unboard up to 5 listed passengers who are boarded and not deleted     Query      Local      *
  * Or:                             *
        * Unboard all passengers except up to 5 listed passengers               Query      Local      *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

    IF p_type = 1 THEN
            l_process := 'board all passengers : ';
            UPDATE pax_v1
        SET     pax_status = 'B'
        WHERE   flight_id = p_flight_id
        AND             pax_status = 'C'
        AND             delete_marker = 0;
    ELSIF p_type = 2 THEN
            l_process := 'board listed passengers : ';
            OPEN c_pax      (p_flight_id
                        ,p_pax_id_1
                        ,p_pax_id_2
                        ,p_pax_id_3
                        ,p_pax_id_4
                        ,p_pax_id_5);
            LOOP
        FETCH c_pax
        INTO v_flight_id
        ,v_pax_id
        ,v_pax_status
        ,v_delete_marker;
        EXIT WHEN c_pax%NOTFOUND;
        EXIT WHEN v_delete_marker = 1;
        EXIT WHEN v_pax_status <> 'C';
        UPDATE pax_v1
        SET     pax_status = 'B'
        WHERE   CURRENT OF c_pax;
            END LOOP;
            IF c_pax%ROWCOUNT = 0 THEN
        CLOSE c_pax;
        l_error_number := -1;
        l_error_message := 'no passengers found';
        RAISE abort;
            ELSIF v_delete_marker = 1 THEN
        CLOSE c_pax;
        l_error_number := -1;
        l_error_message := 'passenger ' || v_pax_id || ' has been deleted';
        RAISE abort;
            ELSIF v_pax_status <> 'C' THEN
        CLOSE c_pax;
        l_error_number := -1;
        l_error_message := 'passenger ' || v_pax_id || ' is not checked in';
        RAISE abort;
            ELSE
        CLOSE c_pax;
            END IF;
    ELSIF p_type = 3 THEN
            l_process := 'board all unlisted pax : ';
            UPDATE pax_v1
        SET     pax_status = 'B'
        WHERE   flight_id = p_flight_id
        AND             pax_status = 'C'
        AND             delete_marker = 0
        AND             pax_id NOT IN (p_pax_id_1
                            ,p_pax_id_2
                            ,p_pax_id_3
                            ,p_pax_id_4
                            ,p_pax_id_5);
    ELSIF p_type = 4 THEN
            l_process := 'unboard all passengers : ';
            UPDATE pax_v1
        SET     pax_status = 'C'
        WHERE   flight_id = p_flight_id
        AND             pax_status = 'B';
    ELSIF p_type = 5 THEN
            l_process := 'unboard listed passengers : ';
            OPEN c_pax      (p_flight_id
                        ,p_pax_id_1
                        ,p_pax_id_2
                        ,p_pax_id_3
                        ,p_pax_id_4
                        ,p_pax_id_5);
            LOOP
        FETCH c_pax
        INTO v_flight_id
        ,v_pax_id
        ,v_pax_status
        ,v_delete_marker;
        EXIT WHEN c_pax%NOTFOUND;
        EXIT WHEN v_delete_marker = 1;
        EXIT WHEN v_pax_status <> 'B';
        UPDATE pax_v1
        SET     pax_status = 'C'
        WHERE   CURRENT OF c_pax;
            END LOOP;
            IF c_pax%ROWCOUNT = 0 THEN
        CLOSE c_pax;
        l_error_number := -1;
        l_error_message := 'no passengers found';
        RAISE abort;
            ELSIF v_delete_marker = 1 THEN
        CLOSE c_pax;
        l_error_number := -1;
        l_error_message := 'passenger ' || v_pax_id || ' has been deleted';
        RAISE abort;
            ELSIF v_pax_status <> 'B' THEN
        CLOSE c_pax;
        l_error_number := -1;
        l_error_message := 'passenger ' || v_pax_id || ' is not boarded';
        RAISE abort;
            ELSE
        CLOSE c_pax;
            END IF;
    ELSIF p_type = 6 THEN
            l_process := 'unboard all unlisted pax : ';
            UPDATE pax_v1
        SET     pax_status = 'C'
        WHERE   flight_id = p_flight_id
        AND             pax_status = 'B'
        AND             pax_id NOT IN (p_pax_id_1
                            ,p_pax_id_2
                            ,p_pax_id_3
                            ,p_pax_id_4
                            ,p_pax_id_5);
    ELSE
            l_process := 'unknown process : ';
            l_error_number := -1;
            l_error_message := 'unknown p_type of ' || p_type || ' should be between 1 and 6';
            RAISE abort;
    END IF;

    p_error_number := 0;
    p_error_message := l_process || 'completed successfully';

    COMMIT;
        EXCEPTION
    WHEN abort THEN
      p_error_number := l_error_number;
      p_error_message := l_process || l_error_message;
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,l_process || SQLERRM);
        END board_passenger;

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

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) AS

  v_bags_remaining      NUMBER;
  v_user_id             tagtrak_users.user_id%TYPE;
  v_bag_tag2a           bag.bag_tag2a%TYPE DEFAULT '-';
  v_bag_tag2b           bag.bag_tag2b%TYPE;

  c_action              CONSTANT bag_trace.trace_action%TYPE := 'CLEAR BAG';

  n_bag_tag             bag.bag_tag%TYPE;

  -- Tracking variables
  l_event_count                number;
  l_no_of_errs                 number;

BEGIN

  p_error_description   :=  ' ';

  IF post_departure(p_flight_id) THEN

    g_error_message := 'flight id '|| p_flight_id ||' ';
    g_error_number := -208;
    SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
    RAISE abort;

  END IF;

  -- Create a trace entry (HANDLER_TRACE) for this flight
  handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

  -- Ensure bag tag is in numeric format
  process := 'convert bag tag : ';
  n_bag_tag := get_numeric_bag_tag(p_bag_tag);

  -- Fire any automatic tracking events for this bag (ignore errors, as these
  -- should not affect the operation itself). Note: g_error_number is the pax
  -- id associated with the gate bag just created

  tracking.auto_events(n_bag_tag,
                       p_flight_id,
                       NULL,
                       p_pax_id,
                       0,
                       p_user_id,
                       p_location_code,
                       NULL,
                       l_event_count,
                       l_no_of_errs);

  process := 'clear_bag : ';

  clear_bag(p_user_id,
            p_flight_id,
            p_pax_id,
            n_bag_tag,
            0,
            p_flight_id,
            v_bags_remaining,
            g_error_number);

  IF g_error_number < 0 THEN

    g_error_message := 'error for bag '||n_bag_tag||' on flight '||p_flight_id||' - ';
    SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
    RAISE abort;

  END IF;

  process := 'add_bag_trace : ';

  v_user_id := p_user_id;
  v_bag_tag2b := SUBSTR(n_bag_tag,2,3);

  add_bag_trace_record(v_user_id,
                       p_location_code,
                       0,
                       n_bag_tag,
                       SUBSTR(n_bag_tag,1,1),
                       v_bag_tag2a,
                       v_bag_tag2b,
                       SUBSTR(n_bag_tag,5,6),
                       c_action, -- trace_action
                       0, -- scanner_id
                       p_flight_id,
                       p_pax_id,
                       uld_no_for_bag (n_bag_tag, p_pax_id, 'GATE'));

  p_error_number := 0;
  p_error_message :=  'clear_a_bag successful for bag '
            || n_bag_tag ||
            ' on flight '
            || p_flight_id;

  COMMIT;

EXCEPTION
  WHEN abort THEN
    p_error_number := g_error_number;
    p_error_message := process || g_error_message;
    p_error_description   :=  get_error_description(p_error_number);
    ROLLBACK;

  WHEN bag_issuer_not_known THEN
    p_error_number := SQLCODE;
    p_error_message := process || ' Baggage Tag Issuer Code not known';
    ROLLBACK;

  WHEN invalid_bag_tag_format THEN
    p_error_number := SQLCODE;
    p_error_message := process || ' Invalid Baggage Tag format';
    ROLLBACK;

  WHEN OTHERS THEN
    ROLLBACK;
    raise_application_error(-20000,process || SQLERRM);

END clear_a_bag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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) AS

BEGIN

  clear_a_bag(p_location_code,
              p_user_id,
              p_flight_id,
              p_bag_tag,
              p_serial_no,
              0,
              p_error_message,
              p_error_number,
              p_error_description);

END clear_a_bag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
  ) AS

  v_allow_tagged_bags    CHAR(1);
  v_bags_remaining       NUMBER;

 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                 *
  * PURPOSE                         *
  *                                 *
  * Remove a crew bag from a flight                         *
  *                                 *
  * PROCESS                           METHOD     TYPE       *
  *                                 *
  * Check flight_status using post_departure                  Function   Internal   *
  * Obtain the crew bag credentials using get_crew_bag_creds              Procedure  External   *
  * Either:                         *
  * Obtain the pax id for gate crew using gate_crew_pax_id    Function   Internal   *
  * Clear a tagged crew bag using clear_bag                   Procedure  External   *
  * Maintain traceability using add_bag_trace_record          Procedure  External   *
  * Or:                             *
  * Clear an untagged crew bag using clear_bag                Procedure  External   *
  *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
        BEGIN

      p_error_description   :=  ' ';
    IF post_departure(p_flight_id) THEN
      g_error_message := 'flight id '
           || p_flight_id ||
           ' ';
      g_error_number := -208;
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
      RAISE abort;
    END IF;

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

    process := 'get_crew_bag_creds : ';

                carrier_details.GET_TAGGED_CREW_BAG( p_flight_id
                            ,v_allow_tagged_bags
                            ,g_error_number
                            );

    process := 'validate parameters : ';

    IF g_error_number < 0 THEN
        p_error_message :=  'Invalid parm errors ';
    ELSIF v_allow_tagged_bags = 'Y' THEN
        g_error_number := -3;
       p_error_message :=  'unable to clear an untagged crew bag as flight has tagged crew_bag ';
    ELSIF v_allow_tagged_bags = 'N' THEN
            IF p_no_of_bags < 1 THEN
                  g_error_number := -3;
                  g_error_message :=        'tagged bags not allowed so number of bags MUST be supplied. '
                                || p_no_of_bags ||
                                ' is an invalid number of bags';
                  RAISE abort;
            ELSE
              process := 'clear_bag : ';

              clear_bag       (p_user_id
                               ,p_flight_id
                               ,0
                               ,'-'
                               ,p_no_of_bags
                               ,p_flight_id
                               ,v_bags_remaining
                               ,g_error_number
                               );
              IF g_error_number < 0 THEN
                        g_error_message :=  'error for flight '
                            || p_flight_id ||
                            ' no of bags '
                            || p_no_of_bags ||
                          ' - ';
                       SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
                       RAISE abort;
              END IF;
         END IF;
    END IF;

    p_error_number := 0;
    p_error_message :=  'clear_an_untagged_crew_bag successful for flight '
            || p_flight_id;

    COMMIT;

  EXCEPTION
    WHEN abort THEN
      p_error_number := g_error_number;
      p_error_message := process || g_error_message;
      p_error_description   :=  get_error_description(p_error_number);
      ROLLBACK;
    WHEN bag_issuer_not_known THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Baggage Tag Issuer Code not known';
      ROLLBACK;
    WHEN invalid_bag_tag_format THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Invalid Baggage Tag format';
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,process || SQLERRM);

END clear_an_untagged_crew_bag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        SUBTYPE                     app_priv_name       IS app_priv.app_priv_name%TYPE;
        TYPE                    app_priv_name_table IS TABLE OF app_priv_name
                                            INDEX BY BINARY_INTEGER;
        app_priv_names              app_priv_name_table;
        update_user_group_required                          BOOLEAN DEFAULT FALSE;
        l_rowcount              PLS_INTEGER  DEFAULT 0;
        l_process               VARCHAR2(30);
        FUNCTION tabularize (p_string IN parameter_string, p_delimiter IN CHAR DEFAULT ',') RETURN app_priv_name_table IS
    parameters app_priv_name_table;
    i                   BINARY_INTEGER  DEFAULT 1;
    pointer PLS_INTEGER             DEFAULT 1;
    BEGIN
            WHILE INSTR(p_string,p_delimiter,pointer) > 0 LOOP
        parameters(i) := SUBSTR(p_string,pointer,INSTR(p_string,p_delimiter,INSTR(p_string,p_delimiter,pointer)) - pointer);
        i := i + 1;
        pointer := INSTR(p_string,p_delimiter,pointer) + 1;
            END LOOP;
            parameters(i) := SUBSTR(p_string,pointer);
            RETURN parameters;
    END tabularize;

        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                 *
  * PURPOSE                         *
  *                                 *
  * Change the privileges associated with a role            *
  *                                 *
  * PROCESS                           METHOD     TYPE       *
  *                                 *
  * Load a parameter array from a comma separated parameter list of                 *
  * granted privileges using tabularize           Procedure  Local      *
  * Grant privileges in the array by inserting into user_role_priv table  Query      Local      *
  * Load a parameter array from a comma separated parameter list of                 *
  * revoked privileges using tabularize           Procedure  Local      *
  * Revoke privileges in the array by deleting from user_role_priv table  Query      Local      *
  * Maintain equivalence in user_group table for scanner privileges       Procedure  Internal   *
  *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    l_process := 'tabularize : ';
    IF p_granted_privilege IS NOT NULL THEN
            app_priv_names := tabularize(p_granted_privilege);
    ELSE
            app_priv_names.DELETE;
    END IF;

    l_process := 'insert into user_role_priv : ';
    l_rowcount := 0;

    FOR i IN 1 .. app_priv_names.COUNT LOOP
            INSERT INTO user_role_priv (user_role_id,app_priv_id)
        (SELECT user_role_id, app_priv_id
        FROM user_role, app_priv
        WHERE user_role_name = p_user_role_name
        AND     agent_code = p_agent_code
        AND app_priv_name = app_priv_names(i)
        AND (user_role_id, app_priv_id) NOT IN (SELECT user_role_id, app_priv_id FROM user_role_priv));

            IF scanner_privilege(app_priv_names(i)) THEN
        update_user_group_required := TRUE;
            END IF;

            l_rowcount := l_rowcount + SQL%ROWCOUNT;
    END LOOP;

    g_error_message := l_rowcount || ' privileges granted and ';

    l_process := 'tabularize : ';
    IF p_revoked_privilege IS NOT NULL THEN
            app_priv_names := tabularize(p_revoked_privilege);
    ELSE
            app_priv_names.DELETE;
    END IF;

    l_process := 'delete from user_role_priv : ';
    l_rowcount := 0;

    FOR i IN 1 .. app_priv_names.COUNT LOOP
            DELETE FROM user_role_priv
        WHERE (user_role_id, app_priv_id) =
        (SELECT user_role_id, app_priv_id
        FROM user_role, app_priv
        WHERE user_role_name = p_user_role_name
        AND     agent_code = p_agent_code
        AND app_priv_name = app_priv_names(i));

            IF scanner_privilege(app_priv_names(i)) THEN
        update_user_group_required := TRUE;
            END IF;

            l_rowcount := l_rowcount + SQL%ROWCOUNT;
    END LOOP;

    g_error_message := g_error_message
                    || l_rowcount ||
                    ' privileges revoked for '
                    || p_user_role_name ||
                    ' with agent '
                    || p_agent_code;

    IF update_user_group_required THEN
            l_process := 'update user group : ';
            update_user_group(p_user_role_name, p_agent_code);
    END IF;

    p_error_number := 0;
    p_error_message := 'update_role successful - '
                    || g_error_message;

    COMMIT;
  EXCEPTION
    WHEN abort THEN
      p_error_number := g_error_number;
      p_error_message := l_process || g_error_message;
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,l_process || SQLERRM);
        END update_role;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
          l_process  VARCHAR2(30);
          l_rowcount PLS_INTEGER DEFAULT 0;

  PROCEDURE insert_user_role
    (p_user_role_name   IN  user_role.user_role_name%TYPE
    ,p_agent_code       IN  agents.agent_code%TYPE
    ) AS
    BEGIN
      INSERT INTO user_role
        (user_role_id
        ,user_role_name
        ,agent_code
        ) VALUES
        (NULL
        ,p_user_role_name
        ,p_agent_code
        );
    EXCEPTION
            WHEN integrity_constraint THEN
        g_error_number := SQLCODE;
        g_error_message := 'agent code '
                                || p_agent_code ||
                                ' does not exist';
        RAISE abort;
      WHEN DUP_VAL_ON_INDEX THEN
        NULL;
      WHEN OTHERS THEN
        RAISE;
    END insert_user_role;
  BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                 *
  * PURPOSE                         *
  *                                 *
  * Add all the privileges associated with a particular role to another role        *
  *                                 *
  * PROCESS                           METHOD     TYPE       *
  *                                 *
  * Create the new role if it does not exist                  Procedure  Local      *
  * Grant privileges belonging to one role to another role by inserting             *
  * into user_role_priv table                     Query      Local      *
  *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    l_process := 'insert user_role : ';
    insert_user_role(p_user_role_name_to, p_agent_code);

    l_process := 'insert user_role_priv : ';
    INSERT INTO user_role_priv (user_role_id,app_priv_id)
            (SELECT role.user_role_id,  priv.app_priv_id
            FROM user_role role, user_role_priv priv
            WHERE role.user_role_id =
        (SELECT user_role_id
        FROM user_role
        WHERE user_role_name = p_user_role_name_to
        AND agent_code = p_agent_code)
            AND     priv.user_role_id =
             (SELECT user_role_id
        FROM user_role
        WHERE user_role_name =  p_user_role_name_from
        AND agent_code = p_agent_code)
            AND (role.user_role_id, priv.app_priv_id) NOT IN
        (SELECT user_role_id, app_priv_id
        FROM user_role_priv)
            );

    l_rowcount := SQL%ROWCOUNT;
    IF l_rowcount = 0 THEN
            g_error_number := -1;
            g_error_message := 'copy_role failed. '
                            || l_rowcount ||
                            ' changes made for role name '
                            || p_user_role_name_to ||
                            ' with agent '
                            || p_agent_code;
            RAISE abort;
    END IF;

    p_error_number := 0;
    p_error_message := 'copy_role successful. '
                            || l_rowcount ||
                            ' changes made for role name '
                            || p_user_role_name_to ||
                            ' with agent '
                            || p_agent_code;

    l_process := 'update user group : ';
    update_user_group(p_user_role_name_to, p_agent_code);

    COMMIT;
  EXCEPTION
    WHEN abort THEN
      p_error_number := g_error_number;
      p_error_message := l_process || g_error_message;
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,l_process || SQLERRM);
        END copy_role;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        l_process         VARCHAR2(30);
        access_denied  exception;
        ret            number;
        p_function_id  number;
        l_locked       tagtrak_users.lock_flag%type;
        l_retired      tagtrak_users.retired%type;
        l_pwd_expired  tagtrak_users.pwd_expired_flag%type;
        FUNCTION valid_privilege
        (p_user_id                  IN    tagtrak_users.user_id%TYPE
        ,p_app_priv_name        IN    app_priv.app_priv_name%TYPE
        ) RETURN BOOLEAN IS
        CURSOR c_app_priv_name(p_user_id            tagtrak_users.user_id%TYPE
        ,p_app_priv_name    app_priv.app_priv_name%TYPE) IS
            SELECT ap.app_priv_name
            FROM tagtrak_users tu
            ,tagtrak_users_role tur
            ,user_role ur
            ,user_role_priv urp
            ,app_priv ap
            WHERE tu.user_id = p_user_id
            AND   ap.app_priv_name = p_app_priv_name
            AND             tu.user_id = tur.user_id
            AND             tur.user_role_id = ur.user_role_id
            AND             ur.user_role_id = urp.user_role_id
            AND             urp.app_priv_id = ap.app_priv_id
            AND             tu.retired = 'N'
            AND             tu.lock_flag = 0
            AND             tu.pwd_expired_flag = 0
        FOR UPDATE OF app_priv_name;
        v_app_priv_name        app_priv.app_priv_name%TYPE;
        BEGIN
        l_process := 'valid_privilege : ';
        OPEN c_app_priv_name(p_user_id, p_app_priv_name);
        FETCH c_app_priv_name
        INTO  v_app_priv_name;
        IF c_app_priv_name%NOTFOUND THEN
        RAISE NO_DATA_FOUND;
        END IF;
        CLOSE c_app_priv_name;
        RETURN TRUE;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        CLOSE c_app_priv_name;
        RETURN FALSE;
    END valid_privilege;
        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                 *
  * PURPOSE                         *
  * First check if user have access to perform the function required                *
  * Check whether the user has the privilege allocated to at least one of his roles             *
  *                                 *
  * PROCESS                           METHOD     TYPE       *
  * Validate the privilege using valid_privilege              Function   Local      *
  *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    IF valid_privilege(p_user_id, p_app_priv_name) THEN
            p_valid_privilege := 'Y';
    ELSE
            -- Not a valid privilege. Try to determine why this is.

            BEGIN
               SELECT lock_flag, retired, pwd_expired_flag INTO
                      l_locked, l_retired, l_pwd_expired FROM tagtrak_users WHERE

                  user_id = p_user_id;

               IF l_locked = 1 THEN
                  p_valid_privilege := 'L';
               ELSIF l_retired = 'Y' THEN
                  p_valid_privilege := 'R';
               ELSIF l_pwd_expired = 1 THEN
                  p_valid_privilege := 'P';
               ELSE
                  -- User must not have access to the privilege
                  p_valid_privilege := 'N';
               END IF;
            EXCEPTION
               -- Should never happen as only valid users should be passed to this procedure
               WHEN OTHERS THEN
                  p_valid_privilege := 'O';
                  p_error_number := SQLCODE;
            END;
    END IF;
    p_error_number := 0;
    p_error_message := 'check privilege successful for user id '
                        || p_user_id ||
                        ' for privilege name '
                        || p_app_priv_name;
               IF p_error_number = 0 THEN
      validate_action.check_access(p_user_id,p_flight_id,p_function_id,p_app_priv_name, ret);
      IF( ret < 0 ) THEN
        raise access_denied;
      END IF;
               END IF;
     EXCEPTION
     WHEN access_denied THEN
         p_valid_privilege := 'D';
         p_error_number  := 0;
         p_error_message := 'No access to function   '||' '|| p_app_priv_name;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,l_process || SQLERRM);
        END check_privilege;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        l_process         VARCHAR2(30);
        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                 *
  * PURPOSE                         *
  *                                 *
  * Allow an agent to view a flight                         *
  *                                 *
  * PROCESS                           METHOD     TYPE       *
  * Add the flight to an agent by inserting into flight_privileges table  Query      Local      *
  *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    l_process := 'insert flight_privileges : ';

    INSERT INTO flight_privileges
    (id
    ,originating_agent
    ,airline_alpha_code
    ,agent_code
    ,flight_no
    ,flight_date
    ,last_user
    ) VALUES
    (p_id
    ,p_originating_agent
    ,p_airline_alpha_code
    ,p_agent_code
    ,p_flight_no
    ,p_flight_date
    ,p_last_user
    );

    p_error_number := 0;
    p_error_message := 'add_flight_privileges : successful for airline code '
                        || p_airline_alpha_code ||
                        ' for agent code '
                        || p_agent_code ||
                        ' for flight no '
                        || p_flight_no ||
                        ' on '
                        || p_flight_date;
    COMMIT;

        EXCEPTION
    WHEN integrity_constraint THEN
      p_error_number := SQLCODE;
      p_error_message := 'add_flight_privileges : invalid originating agent code / airline alpha code / agent code combination for '
            || p_originating_agent ||
                        ' and '
            || p_airline_alpha_code ||
                        ' and '
                        || p_agent_code;
      ROLLBACK;
    WHEN DUP_VAL_ON_INDEX THEN
            p_error_number := -20027;
            SELECT CONCAT('add_flight_privileges : ', error_description)
            INTO p_error_message FROM error WHERE error_id = -20027;
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,l_process || SQLERRM);
        END add_flight_privileges;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        l_error_number NUMBER DEFAULT 0;
        l_error_message VARCHAR2(512);

        l_process         VARCHAR2(30);
        l_rowcount PLS_INTEGER DEFAULT 0;
        SUBTYPE                     dcs_name        IS system_cfg.dcs_name%TYPE;
        TYPE                    dcs_name_table  IS TABLE OF dcs_name
                                        INDEX BY BINARY_INTEGER;
        dcs_names                   dcs_name_table;

        FUNCTION tabularize (p_string IN parameter_string, p_delimiter IN CHAR DEFAULT ',') RETURN dcs_name_table IS
    parameters dcs_name_table;
    i                   BINARY_INTEGER  DEFAULT 1;
    pointer PLS_INTEGER             DEFAULT 1;
    BEGIN
            WHILE INSTR(p_string,p_delimiter,pointer) > 0 LOOP
        parameters(i) := SUBSTR(p_string,pointer,INSTR(p_string,p_delimiter,INSTR(p_string,p_delimiter,pointer)) - pointer);
        i := i + 1;
        pointer := INSTR(p_string,p_delimiter,pointer) + 1;
            END LOOP;
            parameters(i) := SUBSTR(p_string,pointer);
            RETURN parameters;
    END tabularize;

        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                 *
  * PURPOSE                         *
  *                                 *
  * Set BPM status off (N) or on (Y) for a particular list of dcs names             *
  *                                 *
  * PROCESS                           METHOD     TYPE       *
  *                                 *
  * Load a parameter array from a comma separated parameter list of                 *
  * dcs names that will have BPMs sent            Procedure  Local      *
  * Update the system_cfg table and set dcs_status to Y for the dcs names Query      Local      *
  * Load a parameter array from a comma separated parameter list of                 *
  * dcs names that will not have any BPMs sent                Procedure  Local      *
  * Update the system_cfg table and set dcs_status to N for the dcs names Query      Local      *
  *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    l_process := 'tabularize : ';
    IF p_granted_bpm_status IS NOT NULL THEN
            dcs_names := tabularize(p_granted_bpm_status);
    ELSE
            dcs_names.DELETE;
    END IF;

    l_process := 'set bpm status to Y : ';
    l_rowcount := 0;

    FOR i IN 1 .. dcs_names.COUNT LOOP
            UPDATE system_cfg
            SET bpm_status = 'Y'
            WHERE dcs_name = dcs_names(i);
            l_rowcount := l_rowcount + SQL%ROWCOUNT;
    END LOOP;

    l_error_message := l_rowcount
                    || ' bpm statuses set to Y for ' ||
                    p_granted_bpm_status
                    || ' and ';

    l_process := 'tabularize : ';
    IF p_revoked_bpm_status IS NOT NULL THEN
            dcs_names := tabularize(p_revoked_bpm_status);
    ELSE
            dcs_names.DELETE;
    END IF;

    l_process := 'set bpm status to N : ';
    l_rowcount := 0;

    FOR i IN 1 .. dcs_names.COUNT LOOP
            UPDATE system_cfg
            SET bpm_status = 'N'
            WHERE dcs_name = dcs_names(i);
            l_rowcount := l_rowcount + SQL%ROWCOUNT;
    END LOOP;

    l_error_message := l_error_message
                    || l_rowcount ||
                    ' and bpm statuses set to N for '
                    || p_revoked_bpm_status;

    p_error_number := 0;
    p_error_message := 'update_bpm_status successful - '
                    || l_error_message;

    COMMIT;
  EXCEPTION
    WHEN abort THEN
      p_error_number := l_error_number;
      p_error_message := l_process || l_error_message;
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,l_process || SQLERRM);
        END update_bpm_status;

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

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) IS

BEGIN

  IF p_month_day = 'M' THEN

    p_new_date := TO_CHAR(ADD_MONTHS(TO_DATE(p_old_date,'MON YYYY'),p_increment),'MON YYYY');

  ELSIF p_month_day = 'E' THEN

    p_new_date := to_char(ltr.month_end(TO_DATE(p_old_date,'MON YYYY')), 'DD MON YYYY');

  ELSE

    p_new_date := TO_CHAR(TO_DATE(p_old_date,'DD MON YYYY') + p_increment,'DD MON YYYY');

  END IF;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    raise_application_error(-20000,'get new date : ' || SQLERRM);

END get_new_date;

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

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
        ) AS
        v_pax_id                    pax.pax_id%TYPE;
        v_pax_sequence_no           pax.pax_sequence_no%TYPE;
        v_bag_tag1                      bag.bag_tag1%TYPE;
        v_bag_tag2a                     bag.bag_tag2a%TYPE;
        v_bag_tag2b                     bag.bag_tag2b%TYPE;
        v_bag_tag3                      bag.bag_tag3%TYPE;

        v_user_id                   tagtrak_users.user_id%TYPE;

        v_action                    bag_trace.trace_action%TYPE;

        n_bag_tag                   bag.bag_tag%TYPE;                                              -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)

        -- CA/920 - Convert Bag query to use tag components, not full tag
        l_bt1                       bag.bag_tag1%TYPE;
        l_bt2a                      bag.bag_tag2a%TYPE;
        l_bt2b                      bag.bag_tag2b%TYPE;
        l_bt3                       bag.bag_tag3%TYPE;
        l_tag_num                   bag.bag_tag%TYPE;
        l_tag_type                  NUMBER;
        -- End of CA/920

        -- Tracking variables
        l_event_count                number;
        l_no_of_errs                 number;
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Set security clearance status to Not cleared            *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Check flight_status using post_departure                  Function   Internal   *
        * Get the bag details for trace record          Query      Local      *
        * Set the security clearance status             Query      Local      *
        * Maintain traceability using add_bag_trace_record          Procedure  External   *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
        BEGIN

           -- Create a trace entry (HANDLER_TRACE) for this flight
           handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

           -- Ensure bag tag is in numeric format
           process := 'convert bag tag : ';                                                        -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
           n_bag_tag := get_numeric_bag_tag(p_bag_tag);                                            -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)

           split_tags(n_bag_tag, l_bt1, l_bt2a, l_bt2b, l_bt3, l_tag_num, l_tag_type, 0);          -- CA/920

 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * CR 15683                        *
        *       set the message for a trace record depending on the security_clearance_status               *
        * if the bag has been through xray (status = 'C') then set the message to 'CLEAR XRAY'        *
        * if the bag has not been through xray (status = 'N') then set the message to 'NOT XRAY'      *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
    process := 'set trace action : ';

    SELECT
    DECODE(p_security_clearance_status
            ,'C'    ,'CLEAR XRAY'
            ,'NOT XRAY')
    INTO
    v_action
    FROM
    dual;

    IF post_departure(p_flight_id) THEN
      g_error_message := 'flight id '
           || p_flight_id ||
           ' ';
            g_error_number := -208;
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
      RAISE abort;
    END IF;
    BEGIN

            process := 'Query Select : ';
            SELECT p.pax_id
        ,p.pax_sequence_no
        ,b.bag_tag1
        ,b.bag_tag2a
        ,NVL(b.bag_tag2b,'-')
        ,b.bag_tag3
            INTO v_pax_id
        ,v_pax_sequence_no
        ,v_bag_tag1
        ,v_bag_tag2a
        ,v_bag_tag2b
        ,v_bag_tag3
            FROM pax p
        ,bag b
        ,flight f
            WHERE p.flight_id = f.flight_id
            AND p.pax_id            = b.pax_id
--          AND b.bag_tag           = n_bag_tag                                                    -- CA/920
            AND b.bag_tag2b         = l_bt2b                                                       -- CA/920
            AND b.bag_tag3          = l_bt3                                                        -- CA/920
            AND f.flight_id = p_flight_id
            AND b.delete_marker = 0
            AND b.security_clearance_status <> p_security_clearance_status
            FOR UPDATE OF b.security_clearance_status;
    EXCEPTION
            WHEN NO_DATA_FOUND THEN
        g_error_message := 'bag '
                                || n_bag_tag ||
                                ' not found for flight '
                                || p_flight_id;                                                    -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
        g_error_number := SQLCODE;
        RAISE abort;
            WHEN TOO_MANY_ROWS THEN
        g_error_number := -1003;
        SELECT error_description INTO g_error_message FROM error WHERE error_id = -1003;
        RAISE abort;
            WHEN OTHERS THEN
        RAISE;
    END;

    -- Fire any automatic tracking events for this bag (ignore errors, as these
    -- should not affect the operation itself)

    tracking.auto_events(n_bag_tag, p_flight_id, NULL, v_pax_id, 0, p_user_id,                     -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                         p_location_code, NULL, l_event_count, l_no_of_errs);

    process := 'set clearance status : ';

    BEGIN

                IF v_action = 'CLEAR XRAY' THEN

                    UPDATE bag
                       SET security_clearance_status = p_security_clearance_status
                            , sc_user_id  = p_user_id
                            , sc_location = p_location_code
                            , sc_time     = SYSDATE
                           WHERE bag_tag     = n_bag_tag
                           AND pax_id = v_pax_id;

                ELSE -- 'NOT XRAY' 'N' remove security certificate

                    UPDATE bag
                       SET security_clearance_status = p_security_clearance_status
                         , sc_user_id  = NULL
                         , sc_location = NULL
                         , sc_time     = NULL
                     WHERE bag_tag = n_bag_tag
                       AND pax_id = v_pax_id;
                END IF;

    EXCEPTION
          WHEN check_constraint THEN
              g_error_message := SQLERRM;
              g_error_number := SQLCODE;
              RAISE abort;
    END;

    v_user_id := p_user_id;

    process := 'add_bag_trace : ';

    add_bag_trace_record(v_user_id
                        ,p_location_code
                        ,0      -- error_code
                        ,n_bag_tag                                                                 -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                        ,v_bag_tag1
                        ,v_bag_tag2a
                        ,v_bag_tag2b
                        ,v_bag_tag3
                        ,v_action -- trace_action
                        ,0 -- scanner_id
                        ,p_flight_id
                        ,v_pax_id
                        ,uld_no_for_bag (n_bag_tag, v_pax_id));    -- pass ULD number (Nigel, 01/04/04, CA598)

    p_error_number := 0;
    p_error_message :=      'set_a_security_bag successful for bag '
                        || n_bag_tag ||
                        ' on flight '
                        || p_flight_id;             -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)


    process := 'process bpm : ';

    IF p_security_clearance_status  = 'C' THEN

       IF store_bpm1(  p_location_code
                                     ,v_user_id
                                     ,p_flight_id
                                     ,v_pax_id
                                     ,n_bag_tag
                                     ,v_pax_sequence_no
          ,V_action) then
     null;
       END IF;

    END IF;

    COMMIT;

        EXCEPTION
    WHEN abort THEN
            p_error_number := g_error_number;
            p_error_message := process || g_error_message;
            ROLLBACK;
    WHEN bag_issuer_not_known THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Baggage Tag Issuer Code not known';
      ROLLBACK;
    WHEN invalid_bag_tag_format THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Invalid Baggage Tag format';
      ROLLBACK;
    WHEN OTHERS THEN
            ROLLBACK;
            raise_application_error(-20000,process || SQLERRM);
        END set_a_security_bag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
  )
  AS

  v_flight_no           flight.flight_no%TYPE;
  v_depcode             site_info.airport_code%TYPE :=null;
  v_status              airport_configuration.SECURITY_CLEARANCE%TYPE := null;
  l_process             VARCHAR2(30);

  BEGIN

 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                 *
  * PURPOSE                         *
  *                                 *
  * Set or unset  flight clearance status within a station              *
  *                                 *
  * PROCESS                           METHOD     TYPE       *
  *                                 *
  * Validate the flight no using valid_flight_digits          Function   Internal   *
  * Put the flight no into the correct format using normalise_flight_no   Function   External   *
  * Validate the flight prefix using valid_flight_prifix      Function   Internal   *
  * Validate the agent code using valid_agent_code            Function   Internal   *
  *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
  IF valid_flight_digits(p_flight_no) THEN
      l_process := 'normalise_flight_no : ';
      v_flight_no := normalise_flight_no(p_flight_no);
  ELSE
      g_error_number := -5;
      g_error_message :=  'flight '
            || RTRIM(p_flight_no) ||
            ' is an invalid flight number';
      RAISE abort;
  END IF;
  l_process := 'valid_flight_prefix : ';
  IF NOT valid_flight_prefix(SUBSTR(v_flight_no,1,3)) THEN
      g_error_number := -2;
      g_error_message :=  'flight '
            || RTRIM(v_flight_no) ||
            ' is a restricted flight';
      RAISE abort;
  ELSE
  l_process := 'set_flight_clearance_status : ';
      SELECT air.airport_code, security_clearance
        INTO v_depcode, v_status
        FROM agents, airport_configuration air, site_info si
       WHERE agent_code       = p_agent_code
         and agents.site_id   = si.site_id
         and si.airport_code  = air.airport_code;

      l_process := 'update flight_configuration : ';

      IF (v_status = 'Y') THEN
         IF(action = 'ADD') THEN
           insert into flight_configuration (flight_no, airport_code, security_clearance) values(v_flight_no, v_depcode, 'N');
         ELSE
           delete from flight_configuration where flight_no = v_flight_no;
         END IF;
      ELSIF (v_status = 'N') THEN
         IF(action = 'ADD') THEN
           insert into flight_configuration (flight_no, airport_code, security_clearance) values(v_flight_no, v_depcode, 'Y');
         ELSE
           delete from flight_configuration where flight_no = v_flight_no;
         END IF;
      ELSE
        RAISE abort;
      END IF;
  END IF;

  p_error_number := 0;
  p_error_message :=  'set_flight_clearance_status successful for '
           || RTRIM(v_flight_no);
  COMMIT;

  EXCEPTION
    WHEN abort THEN
      p_error_number := g_error_number;
      p_error_message := l_process || g_error_message;
      ROLLBACK;
    WHEN dup_val_on_index THEN
      p_error_number := SQLCODE;
      p_error_message := l_process || 'row already exists for ' || v_flight_no;
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,l_process || SQLERRM);


  END set_flight_clearance_status;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
        ) AS
        v_pax_id                    pax.pax_id%TYPE;
        v_pax_sequence_no           pax.pax_sequence_no%TYPE;
        v_bag_tag1                      bag.bag_tag1%TYPE;
        v_bag_tag2a                     bag.bag_tag2a%TYPE;
        v_bag_tag2b                     bag.bag_tag2b%TYPE;
        v_bag_tag3                      bag.bag_tag3%TYPE;

        v_user_id                   tagtrak_users.user_id%TYPE;

        c_action                    bag_trace.trace_action%TYPE;

        n_bag_tag                   bag.bag_tag%TYPE;                                              -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)

        -- Tracking variables
        l_event_count                number;
        l_no_of_errs                 number;
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Set security clearance status to Not cleared for a duplicate bag tag            *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Check flight_status using post_departure                  Function   Internal   *
        * Get the bag details for trace record          Query      Local      *
        * Set the security clearance status             Query      Local      *
        * Maintain traceability using add_bag_trace_record          Procedure  External   *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
BEGIN

process := 'set trace action : ';

    SELECT
    DECODE(p_security_clearance_status
            ,'C'    ,'CLEAR XRAY'
            ,'NOT XRAY')
    INTO
    c_action
    FROM
    dual;

    IF post_departure(p_flight_id) THEN
      g_error_message := 'flight id '
           || p_flight_id ||
           ' ';
            g_error_number := -208;
      SELECT CONCAT(g_error_message,error_description) INTO g_error_message FROM error WHERE error_id = g_error_number;
      RAISE abort;
    END IF;

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

    -- Ensure bag tag is in numeric format
    process := 'convert bag tag : ';                                                               -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
    n_bag_tag := get_numeric_bag_tag(p_bag_tag);                                                   -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)

    BEGIN

            process := 'Query Select : ';
            SELECT p.pax_id
        ,p.pax_sequence_no
        ,b.bag_tag1
        ,b.bag_tag2a
        ,NVL(b.bag_tag2b,'-')
        ,b.bag_tag3
            INTO v_pax_id
        ,v_pax_sequence_no
        ,v_bag_tag1
        ,v_bag_tag2a
        ,v_bag_tag2b
        ,v_bag_tag3
            FROM pax p
        ,bag b
        ,flight f
            WHERE p.flight_id = f.flight_id
            AND p.pax_id            = b.pax_id
            AND b.bag_tag           = n_bag_tag                                                    -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
            AND f.flight_id = p_flight_id
            AND p.pax_id = p_pax_id
            AND b.delete_marker = 0
            FOR UPDATE OF b.security_clearance_status;
    EXCEPTION
            WHEN NO_DATA_FOUND THEN
        g_error_message := 'bag '
                                || n_bag_tag ||
                                ' not found for flight '
                                || p_flight_id;                                                    -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
        g_error_number := SQLCODE;
        RAISE abort;
            WHEN TOO_MANY_ROWS THEN
        g_error_number := -1003;
        SELECT error_description INTO g_error_message FROM error WHERE error_id = -1003;
        RAISE abort;
            WHEN OTHERS THEN
        RAISE;
    END;

    -- Fire any automatic tracking events for this bag (ignore errors, as these
    -- should not affect the operation itself)

    tracking.auto_events(n_bag_tag, p_flight_id, NULL, v_pax_id, 0, p_user_id,                     -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                         p_location_code, NULL, l_event_count, l_no_of_errs);

    process := 'set clearance status : ';

    BEGIN

        IF p_security_clearance_status = 'C' THEN

            UPDATE bag
            SET    security_clearance_status = p_security_clearance_status
                 , sc_user_id  = p_user_id
                                                           , sc_location = p_location_code
                                                                 , sc_time     = SYSDATE
                                          WHERE  bag_tag     = n_bag_tag
                                          AND    pax_id      = p_pax_id;

        ELSE -- 'N' remove security certificate

                                                UPDATE bag
                                                SET    security_clearance_status = p_security_clearance_status
                                                                 , sc_user_id  = NULL
                                                         , sc_location = NULL
                                                                 , sc_time     = NULL
                                          WHERE  bag_tag     = n_bag_tag
                                          AND    pax_id      = p_pax_id;

                          END IF;

    EXCEPTION
            WHEN check_constraint THEN
        g_error_message := SQLERRM;
        g_error_number := SQLCODE;
        RAISE abort;
    END;

    v_user_id := p_user_id;

    process := 'add_bag_trace : ';

    add_bag_trace_record(v_user_id
                        ,p_location_code
                        ,0      -- error_code
                        ,n_bag_tag                                                                 -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                        ,v_bag_tag1
                        ,v_bag_tag2a
                        ,v_bag_tag2b
                        ,v_bag_tag3
                        ,c_action -- trace_action
                        ,0 -- scanner_id
                        ,p_flight_id
                        ,v_pax_id
                        ,uld_no_for_bag (n_bag_tag, v_pax_id));    -- pass ULD number (Nigel, 01/04/04, CA598)

    p_error_number := 0;
    p_error_message :=      'set_a_security_bag successful for bag '
                        || n_bag_tag ||
                        ' on flight '
                        || p_flight_id;                  -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)


    process := 'process bpm : ';

    IF p_security_clearance_status  = 'C' THEN

       IF store_bpm1(  p_location_code
                                     ,v_user_id
                                     ,p_flight_id
                                     ,v_pax_id
                                     ,n_bag_tag
                                     ,v_pax_sequence_no
          ,c_action) then
     null;
       END IF;

   END IF;

    COMMIT;

        EXCEPTION
    WHEN abort THEN
            p_error_number := g_error_number;
            p_error_message := process || g_error_message;
            ROLLBACK;
    WHEN bag_issuer_not_known THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Baggage Tag Issuer Code not known';
      ROLLBACK;
    WHEN invalid_bag_tag_format THEN
      p_error_number := SQLCODE;
      p_error_message := process || ' Invalid Baggage Tag format';
      ROLLBACK;
    WHEN OTHERS THEN
            ROLLBACK;
            raise_application_error(-20000,process || SQLERRM);
        END set_a_duplicate_security_bag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
  ) AS
  l_error_number NUMBER DEFAULT 0;
  l_error_message VARCHAR2(512);
  l_process         VARCHAR2(30);

  BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                         *
  * PURPOSE                 *
  *                         *
  * Restrict access to a flight                     *
  *                         *
  * PROCESS                   METHOD     TYPE       *
  * Delete row(s) from flight_access table            Query      Local      *
  * Insert row into flight_access table               Query      Local      *
  *                         *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);
    l_process := 'delete_flight_access : ';

    DELETE FROM flight_access
    WHERE flight_id = p_flight_id
    AND EXISTS (SELECT 1 FROM flight_access
    WHERE flight_id = p_flight_id
    AND agent_code = p_agent_code);

    IF SQL%NOTFOUND THEN
      l_error_number := -1031;
      SELECT error_description
      INTO l_error_message FROM error WHERE error_id = l_error_number;
      RAISE abort;
    END IF;

    l_process := 'insert_flight_access : ';

    BEGIN
      INSERT INTO flight_access
      (flight_id
      ,flight_priv_id
      ,agent_code
      ,last_user
      ) VALUES
      (p_flight_id
      ,NULL
      ,p_restricted_agent
      ,NULL
      );
    EXCEPTION
      WHEN integrity_constraint THEN
        l_error_number := SQLCODE;
        l_error_message := 'invalid flight_id / agent_code combination for '
             || p_flight_id ||
             ' and '
             || p_agent_code;
        RAISE abort;
    END;

    p_error_number := 0;
    p_error_message :=   'flight_restriction successful for '
            || p_flight_id;
    COMMIT;

  EXCEPTION
    WHEN abort THEN
      p_error_number := l_error_number;
      p_error_message := l_process || l_error_message;
      ROLLBACK;
    WHEN OTHERS THEN
      ROLLBACK;
      raise_application_error(-20000,l_process || SQLERRM);
  END flight_restriction;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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)
AS
   l_comma_pos   NUMBER  (5);
   l_next_pos    NUMBER  (5);
   l_occurrence  NUMBER  (5);
   l_position    VARCHAR2(3);
   l_agent_code  VARCHAR2(5);
BEGIN

   l_comma_pos  := 1;
   l_next_pos   := 1;
   l_occurrence := 1;

   SELECT agent_code
   INTO l_agent_code
   FROM tagtrak_users
   WHERE user_id = p_user_id;

   LOOP
      /** extract a position from the string **/

      l_comma_pos := instr(p_position_string, ',', 1, l_occurrence);
      dbms_output.put_line ('l_comma_pos : '||l_comma_pos);

      IF l_comma_pos = 0
      THEN
         /** no more positions **/

         dbms_output.put_line ('l_occurrence : '||l_occurrence);
         IF l_occurrence = 1
         THEN
            /** this is the only position **/

            l_position := p_position_string;

         ELSE
            /** get the last position from the string **/

            l_position := substr (p_position_string, l_next_pos);
         END IF;
      ELSE
         /** extract position and set pointer to look at next **/

         l_position := substr (p_position_string, l_next_pos, l_comma_pos-l_next_pos);
         l_next_pos := l_comma_pos + 1;
         l_occurrence := l_occurrence + 1;
      END IF;
      dbms_output.put_line ('l_position : '||l_position);

      /** store the position **/
      BEGIN
         INSERT INTO hold_config
         (agent_code,
          aircraft_subtype,
          carrier_alpha_code,
          position,
          description)
         VALUES
         (l_agent_code,
          p_aircraft_subtype,
          p_carrier_alpha_code,
          l_position,
          p_description);

      EXCEPTION
         WHEN others
         THEN
            NULL;
      END;

      /** If this was the last one exit **/
      IF l_comma_pos = 0
      THEN
         EXIT;
      END IF;
   END LOOP;

   p_error_number := 0;
   p_error_message :=   'add_hold_config successful for carrier : '
            || p_carrier_alpha_code
            || ' subtype '
            || p_aircraft_subtype;

  COMMIT;

EXCEPTION
 WHEN OTHERS THEN
   p_error_number := SQLCODE;
   p_error_message := SQLERRM;
   ROLLBACK;

END;

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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)
AS
   l_agent_code  VARCHAR2(5);
BEGIN

   SELECT agent_code
   INTO l_agent_code
   FROM tagtrak_users
   WHERE user_id = p_user_id;

   DELETE hold_config
   WHERE agent_code         = l_agent_code
   AND   aircraft_subtype   = p_aircraft_subtype
   AND   carrier_alpha_code = p_carrier_alpha_code;

   p_error_number := 0;
   p_error_message :=   'delete_hold_config successful for carrier : '
            || p_carrier_alpha_code
            || ' subtype '
            || p_aircraft_subtype;

  COMMIT;

EXCEPTION
 WHEN OTHERS THEN
   p_error_number := SQLCODE;
   p_error_message := SQLERRM;
   ROLLBACK;

END;

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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)
AS

BEGIN

   /** First delete the hold configuration for the airline/carrier/agent **/

   delete_hold_config (p_user_id, p_aircraft_subtype, p_carrier_alpha_code,p_error_message,p_error_number);

   /** Now insert the configurations. **/

   IF p_error_number = 0 THEN
     add_hold_config (p_user_id, p_aircraft_subtype, p_carrier_alpha_code, p_position_string,p_description,p_error_message,p_error_number);
     IF p_error_number = 0 THEN
       p_error_number := 0;
       p_error_message :=   'update_hold_config successful for : '
                || p_carrier_alpha_code
                || ' subtype '
                || p_aircraft_subtype;
     END IF;
   END IF;

EXCEPTION
 WHEN OTHERS THEN
   p_error_number := SQLCODE;
   p_error_message := SQLERRM;
   ROLLBACK;

END;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------
-- UAS/FALCP/CA/00740
--
-- Function:    MWS_GET_USER_BAG_TAG
-- Parameters:  P_TAG            - Tag to be converted
--              P_USER_PREF      - New format required (A/N)
--              P_BAG_MUST_EXIST - Bag Tag must exist in database (Default = TRUE)
-- Returns:     P_TAG converted to requested format or P_TAG on error
----------------------------------------------------------------------------------

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
AS

  tmptag BAG.BAG_TAG%TYPE;

BEGIN

  --
  -- Purpose:
  --
  -- This function uses the existing GET_USER_BAG_TAG database
  -- function to convert the given TAG to the requested format.
  -- Unlike the standard function, this function will NOT raise any
  -- exceptions.
  -- If the conversion raises an error (e.g. ORA-20041 BAG_ISSUER_NOT_KNOWN
  -- or ORA-20042 BAG_FORMAT_INVALID) then the original data is returned.
  --

  tmptag := get_user_bag_tag(p_tag, p_user_pref, p_bag_must_exist);

  RETURN NVL(tmptag, p_tag);

EXCEPTION

  -- On ANY error - return the original tag
  WHEN OTHERS THEN

    RETURN p_tag;

END;


----------------------------------------------------------------------------------
-- UAS/FALCP/CA/00740
--
-- Function:    MWS_BUILD_USER_BAG_TAG
-- Parameters:  P_T1             - First character of tag (customer defined)
--              P_T2A            - Alpha BTIC code
--              P_T2B            - Numeric BTIC code
--              P_T3             - Numeric part of bag tag (final 6 digits)
--              P_USER_PREF      - New format required (A/N)
--              P_BAG_MUST_EXIST - Bag Tag must exist in database (Default = TRUE)
-- Returns:     P_TAG converted to requested format or NULL on error
----------------------------------------------------------------------------------

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
AS

  tmptag BAG.BAG_TAG%TYPE;

BEGIN

  --
  -- Purpose:
  --
  -- This function uses the existing BUILD_USER_BAG_TAG database
  -- function to convert the given components in to a TAG of the requested
  -- format.
  -- Unlike the standard function, this function will NOT raise any
  -- exceptions.
  -- If the conversion raises an error (e.g. ORA-20041 BAG_ISSUER_NOT_KNOWN
  -- or ORA-20042 BAG_FORMAT_INVALID) then NULL is returned.
  --

  tmptag := build_user_bag_tag(p_t1, p_t2a, p_t2b, p_t3, p_user_pref, p_bag_must_exist);

  RETURN tmptag;

EXCEPTION

  -- On ANY error - return NULL
  WHEN OTHERS THEN
     RETURN NULL;

END;


----------------------------------------------------------------------------------
-- UAS/FALCP/CA/00740
--
-- Function:    MWS_GET_USER_BAG_TAG
-- Parameters:  P_TAG            - Tag to be converted
--              P_BAG_MUST_EXIST - Bag Tag must exist in database (Default = TRUE)
-- Returns:     P_TAG converted to requested format or P_TAG on error
----------------------------------------------------------------------------------

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

  tmptag BAG.BAG_TAG%TYPE;

BEGIN

  --
  -- Purpose:
  --
  -- This function uses the existing GET_NUMERIC_BAG_TAG database
  -- function to convert the given TAG to numeric format.
  -- Unlike the standard function, this function will NOT raise any
  -- exceptions.
  -- If the conversion raises an error (e.g. ORA-20041 BAG_ISSUER_NOT_KNOWN
  -- or ORA-20042 BAG_FORMAT_INVALID) then the original data is returned.
  --

  tmptag := get_numeric_bag_tag(p_tag, p_bag_must_exist);

  RETURN NVL(tmptag, p_tag);

EXCEPTION

  -- On ANY error - return the original tag
  WHEN OTHERS THEN

    RETURN p_tag;

END;


----------------------------------------------------------------------------------
-- UAS/FALCP/CA/00740
--
-- Function:    MWS_VALIDATE_BAG_TAG
-- Parameters:  P_TAG            - Tag to be converted
--              A_BAG_TAG        - Alphanumeric form of bag tag
--              N_BAG_TAG        - Numeric form of bag tag
--              RETVAL           - Return Status code (0=Success)
-- Returns:     P_TAG converted to both formats.  Both will be set to P_TAG on
--              error.
----------------------------------------------------------------------------------

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

BEGIN

  --
  -- Purpose:
  --
  -- This function uses the existing VALIDATE_BAG_TAG database
  -- procedure to convert the given TAG to both alpha and numeric formats.
  -- Unlike the standard function, this function will NOT raise any
  -- exceptions.
  -- If the conversion raises an error (e.g. ORA-20041 BAG_ISSUER_NOT_KNOWN
  -- or ORA-20042 BAG_FORMAT_INVALID) then the original data is returned for
  -- both formats.
  --

  validate_bag_tag(p_tag, a_bag_tag, n_bag_tag, retval);

  IF (retval <> 0) THEN
    a_bag_tag := p_tag;
    n_bag_tag := p_tag;
  END IF;

EXCEPTION

  -- On ANY error - return the original tag
  WHEN OTHERS THEN

    a_bag_tag := p_tag;
    n_bag_tag := p_tag;
    RETURN;

END;


----------------------------------------------------------------------------------
-- UAS/FALCP/CA/00870
--
-- Procedure:   VALIDATE_PARTIAL_BAG_TAG
-- Parameters:  P_PARTIAL_TAG    IN  - Section of a bag tag in alpha or numeric form
--              P_NUMERIC_TAG    OUT - Tag converted to numeric format with wildcards
--                                     for any missing characters.
----------------------------------------------------------------------------------

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

  tag_invalid    EXCEPTION;
  l_partial_tag  VARCHAR2(10);
  l_numeric_tag  VARCHAR2(10);
  l_tag_len      NUMBER;

BEGIN

  -- Initialise
  l_partial_tag := UPPER(LTRIM(p_partial_tag));
  p_numeric_tag := p_partial_tag;

  -- Check somthing entered
  IF (l_partial_tag IS NULL) THEN

    RAISE tag_invalid;

  END IF;

  Dbms_Output.PUT_LINE('Input Tag - ' || l_partial_tag);

  -- Does the tag contain any non-numeric characters?
  l_tag_len := LENGTH(l_partial_tag);
  IF (LENGTH(TRANSLATE(l_partial_tag, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789','ABCDEFGHIJKLMNOPQRSTUVWXYZ')) > 0) THEN

    -- Alpha characters found - pad to length 8 for alphanumeric bag tag
    Dbms_Output.PUT_LINE('Performing Alpha PAD');
    l_partial_tag := rpad(l_partial_tag, 8, '9');

  ELSE

    -- No alpha characters found - pad to length 10 for numeric bag tag
    Dbms_Output.PUT_LINE('Performing Numeric PAD');
    l_partial_tag := rpad(l_partial_tag, 10, '9');

  END IF;

  Dbms_Output.PUT_LINE('Padded Tag - ' || l_partial_tag);

  -- Convert the bag tag to numeric
  l_numeric_tag := mws_get_numeric_bag_tag(l_partial_tag, 0);

  Dbms_Output.PUT_LINE('Converted Tag - ' || l_numeric_tag);

  -- Remove any padding
  IF (l_tag_len < LENGTH(l_partial_tag)) THEN

    Dbms_Output.PUT_LINE('Performing SUBSTR');

    l_numeric_tag := SUBSTR(l_numeric_tag, 1, LENGTH(l_numeric_tag) - (LENGTH(l_partial_tag) - l_tag_len));
    l_numeric_tag := l_numeric_tag || '%';

  END IF;

  -- Convert any ? to a wildcard
  p_numeric_tag := REPLACE(l_numeric_tag, '?', '_');

EXCEPTION

  WHEN tag_invalid THEN

    p_numeric_tag := NULL;

END validate_partial_bag_tag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
IS
   -- ref cursor type
   TYPE PrivCurTyp IS REF CURSOR;

   -- cursor that will hold the concatenated privs
   priv_cv   PrivCurTyp;

   -- a record that will hold a row from the priv_cv cursor
   priv_rec  VARCHAR2(2000);

   -- a variable that will hold the statement to be executed
   sql_stmt VARCHAR2(2000);

   -- a cursor that will select the privXX columns from the user_tab_column table
   CURSOR colNames is SELECT COLUMN_NAME from USER_TAB_COLUMNS WHERE TABLE_NAME='USER_GROUP' AND COLUMN_NAME LIKE 'PRIV%';

   -- loop counter
   l_rowcount PLS_INTEGER DEFAULT 0;

BEGIN
   -- for all the privXX columns in the USER_GROUP table
   -- build up a statement to select the values concatenated together
   sql_stmt := 'SELECT ';
   FOR aRow IN colNames LOOP
      IF l_rowcount > 0 THEN
             sql_stmt := sql_stmt||'||';
          END IF;

      sql_stmt := sql_stmt||aRow.COLUMN_NAME;

          l_rowcount := l_rowcount + 1;
   END LOOP;
   sql_stmt := sql_stmt||' FROM USER_GROUP WHERE GROUP_NAME='''||p_group_name||''' AND AGENT_CODE='''||p_agent_code||'''';

   -- execute the statement created above to get all the privs back in one string
   OPEN priv_cv FOR sql_stmt;
   FETCH priv_cv INTO priv_rec;
   CLOSE priv_cv;

   RETURN TRIM(priv_rec);
END;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
IS
   -- variable to hold the privs for group 1
   l_privs1 VARCHAR2(1000);

   -- variable to hold the privs for group 2
   l_privs2 VARCHAR2(1000);

   -- loop counter
   l_privs2_loop INTEGER;

   -- return value
   l_return INTEGER := 1;

   -- number of rows in user_role_priv that do not exist in p_group_name1, p_agent_code1
   l_count INTEGER;

BEGIN
   -- get the first set of privs
   SELECT get_privs_as_varchar(p_group_name1, p_agent_code1) INTO l_privs1 FROM DUAL;

   -- get the second set of privs
   SELECT get_privs_as_varchar(p_group_name2, p_agent_code2) INTO l_privs2 FROM DUAL;

   -- check that some privs exist for p_group_name2
   IF LENGTH(l_privs2) > 0 THEN
      -- loop through each char in l_privs2 and try to find it in l_privs1
      -- exit if not found
      FOR l_privs2_loop IN 1..LENGTH(l_privs2) LOOP
         IF INSTR(l_privs1,SUBSTR(l_privs2,l_privs2_loop,1)) = 0 THEN
            -- not found, set return value and exit loop
            l_return := 0;
            EXIT;
         END IF;
      END LOOP;
   END IF;

   -- select the number of privs that exist in group2 that aren't in group1
   SELECT count(*) into l_count from user_role_priv, user_role where user_role_priv.user_role_id = user_role.user_role_id
      and user_role_name=p_group_name2 and agent_code=p_agent_code2 and app_priv_id not in (
      SELECT urp2.app_priv_id from user_role_priv urp2, user_role ur2 where
         urp2.user_role_id = ur2.user_role_id
         and ur2.user_role_name=p_group_name1 and ur2.agent_code=p_agent_code1);

   IF l_count > 0 THEN
      l_return := 0;
   END IF;

   RETURN l_return;
END;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

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   --  NULL or 0 if named pax NOT to be created
        ,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  -- '-' if no surname supplied
        ,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   --  0   or >0 if a named pax to be re-used
        ,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
        ) AS


  -- Tracking variables
  v_pax_cred                   carrier_lookup.pax_cred%type;

  l_additional_bags    number  ;
  l_pax_id             pax.pax_id%TYPE ;
  l_credential_value   NUMBER;
  i                    NUMBER DEFAULT 0;

  l_proportional_weight        bag.kg_weight%TYPE;
  l_undistributed_weight       bag.kg_weight%TYPE;

  l_bt1                        bag.bag_tag1%TYPE;
  l_bt2a                       bag.bag_tag2a%TYPE;
  l_bt2b                       bag.bag_tag2b%TYPE;
  l_bt3                        bag.bag_tag3%TYPE;
  l_tag_num                    bag.bag_tag%TYPE;
  l_tag_type                   NUMBER;
  l_bag_tag                    bag.bag_tag%TYPE;


  BEGIN

  l_additional_bags     := p_additional_bags ;
  l_pax_id              := p_pax_id;
  l_credential_value    := p_credential_value;
  p_error_description   :=  ' ';

   -- Create a trace entry (HANDLER_TRACE) for this flight
  handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

  -- divide the weight as evenly as possible amongst the number of bags
  l_proportional_weight := TRUNC(p_kg_weight / p_no_of_bags);

  -- add the remainder to the first bag
  l_undistributed_weight := p_kg_weight - (l_proportional_weight * p_no_of_bags);

  l_bag_tag := get_numeric_bag_tag(p_bag_tag);

  -- isolate bag_tag1, bag_tag2b, bag_tag3 and cast bag_tag3 to a number
  split_tags(l_bag_tag, l_bt1, l_bt2a, l_bt2b, l_bt3, l_tag_num, l_tag_type, 0);

  carrier_details.get_pax_identifier (p_flight_id, v_pax_cred);

  -- call once for the first bag

  exception_bags.add_pax_and_excep_bag (
         p_location_code
        ,p_user_id
        ,p_serial_no
        ,p_flight_id
        ,l_bag_tag
        ,v_pax_cred
        ,l_credential_value
        ,p_exception_code
        ,p_pax_destination
        ,p_pax_initials
        ,p_pax_surname
        ,p_booked_flight_id
        ,p_conn_type
        ,p_embassy
        ,p_seat_number
        ,p_delivery_instr
        ,l_additional_bags
        ,p_wt_ref
        ,l_pax_id
        ,l_proportional_weight + l_undistributed_weight
        ,NULL -- BVIS Sub-header
        ,p_error_message
        ,p_error_number
        ) ;

  -- call for the subsequent bags
  -- repeat from 2 to p_no_of_bags
  -- or until p_error_number <> 0

  FOR i IN 2 .. p_no_of_bags LOOP

    IF p_error_number <> 0 THEN
      EXIT;
    END IF;


    l_bt3 := l_bt3 + 1; -- create a new bag tag by incrementing the numeric part

    DBMS_OUTPUT.PUT_LINE('Next call to exception_bags.add_pax_and_excep_bag');

    exception_bags.add_pax_and_excep_bag (
           p_location_code
          ,p_user_id
          ,p_serial_no
          ,p_flight_id
          ,get_numeric_bag_tag(l_bt1 || l_bt2a || LPAD(TO_CHAR(l_bt3),6,'0')) -- reconstituted new bag tag
          ,v_pax_cred
          ,l_credential_value
          ,p_exception_code
          ,p_pax_destination
          ,p_pax_initials
          ,p_pax_surname
          ,p_booked_flight_id
          ,p_conn_type
          ,p_embassy
          ,p_seat_number
          ,p_delivery_instr
          ,l_additional_bags
          ,p_wt_ref
          ,l_pax_id
          ,l_proportional_weight
          ,NULL -- BVIS sub-header
          ,p_error_message
          ,p_error_number
          ) ;

  END LOOP;

  p_error_description   :=  get_error_description(p_error_number);

  IF p_error_number <> 0 THEN
    IF p_error_number = -1003 THEN -- duplicate bag
      p_error_message := 'Duplicate bag : '
            || get_numeric_bag_tag(l_bt1 || l_bt2a || TO_CHAR(l_bt3));
    END IF;
    ROLLBACK;
  ELSE
    COMMIT;
  END IF;

EXCEPTION
    WHEN numeric_or_value_error THEN -- 6502
      p_error_number := SQLCODE;
      p_error_message := SQLERRM || ': weight may be too large';
      ROLLBACK;
    WHEN bag_issuer_not_known THEN -- -20041
      p_error_number := SQLCODE;
      p_error_message := process || ' Baggage Tag Issuer Code not known';
      ROLLBACK;
    WHEN invalid_bag_tag_format THEN -- -20042
      p_error_number := SQLCODE;
      p_error_message := process || ' Invalid Baggage Tag format';
      ROLLBACK;
   WHEN OTHERS THEN
     ROLLBACK;
     p_error_number := -502;
     add_app_msg.log_oracle_error ('mws_add exception_bags ',SQLERRM,SQLCODE);
     p_error_description   :=  get_error_description(p_error_number);


END add_a_pax_and_bag;
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
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
                       ) AS

  uflt      number(10);
  bag_alert exception;
  ret       number(10);
  depair    pax.departure_airport_code%type := null;

begin

        /*
         * - Assign variables
         * - Select the flight_id from the ulds table where the uld_no is
         *    equal to the p_uld_no parameter and the pd_marker is equal to
         *    'N'.  If no data is found, then assign -1 to uflt.
         * - IF uflt is equal to -1 (ie. no records found) then
         *   Assign -1001 to the return value
         * - ELSE
         *   IF the flight_id's are different then
         *     Assign -200 to the return value
         *   ELSE
         *     Update the ulds table setting the appropriate columns where the
         *      the uld_no is equal to the p_uld_no parameter and the flight_id
         *      is equal to the uflt variable.
         */

  depair := get_offset.get_user_location(p_user_id);

        uflt   := -1;
        retval := 0;
        /* try and get the uld_details */
        begin
                select
                        flight_id
                into
                        uflt
                from
                        ulds
                where
                        uld_no    =  p_uld_no AND
                        (   force_pd_load = 'Y'
                         OR pd_marker = 'N'
                        );

                exception
                        when NO_DATA_FOUND then
                                uflt := -1;
        end;

        if (uflt = -1) then
                /* no uld records found */
                retval := -1001;
        else
                if (uflt != fltid) then
                        /* uld not for this flight */
                        retval := -200;
                else
                        /* now its alright to update the columns in the ulds table */

                  check_uld_alert(uflt, p_uld_no,ret);
                        if ret = -1082 then
                                 raise bag_alert;
                  end if;
                  IF uld_loaded_status = 'Y' THEN
                    IF (posn IS NOT NULL) THEN
                       validate_uld_posn (uflt, p_uld_no, posn, p_user_id, retval);
                    END IF;
                  END IF;

                  if retval >= 0
                  OR retval = -1212 /* Full Validation not performed warning only */
                  OR (retval = -1213 AND force_pd_load = 'Y')
                  then

                    IF uld_loaded_status = 'N' THEN
                        UPDATE ulds
                           SET  uld_hold_no       = NULL
                               ,uld_loaded_status = 'N'
                               ,user_id           = p_user_id
                               ,location_code     = RTRIM(locn)
                               ,uld_load_time     = NULL
                         WHERE uld_no    = p_uld_no
                           AND flight_id = fltid;
                     ELSE

                        UPDATE ulds
                           SET uld_hold_no       = RTRIM(posn),
                               uld_loaded_status = 'Y',
                               user_id           = p_user_id,
                               location_code     = RTRIM(locn),
                               uld_load_time     = SYSDATE + get_offset.airport_code(depair)
                         WHERE uld_no    = p_uld_no
                           AND flight_id = fltid;
                     END IF;
                 end if;
                end if;
        end if;
exception

         when bag_alert then
                        retval := ret;

end rec_uld_posn;

-- UAS/FALCP/CA/02658: Stop all of the bags belonging to a list of passengers
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
        ) AS
BEGIN

   -- Initialise error message/number list OUT parameters
   p_error_message_list := error_msg_list_type(NULL);
   p_error_number_list  := error_num_list_type(NULL);

   -- Extend the error message/number lists as necessary
   IF p_pax_list.COUNT > 1 THEN
      p_error_message_list.extend(p_pax_list.COUNT - 1);
      p_error_number_list.extend(p_pax_list.COUNT - 1);
   END IF;

   -- Attempt to stop the bags for each passenger in turn
   FOR i IN 1 .. p_pax_list.COUNT LOOP
      stop_bags_2(p_location_code, p_user_id, p_flight_id, p_pax_list(i),
      p_serial_no, p_reason, p_error_message_list(i), p_error_number_list(i));
   END LOOP;

END stop_multiple_passengers;

-- UAS/FALCP/CA/02658: Clear all of the bags belonging to a list of passengers
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
        ) AS
BEGIN

   -- Initialise error message/number list OUT parameters
   p_error_message_list := error_msg_list_type(NULL);
   p_error_number_list  := error_num_list_type(NULL);

   -- Extend the error message/number lists as necessary
   IF p_pax_list.COUNT > 1 THEN
      p_error_message_list.extend(p_pax_list.COUNT - 1);
      p_error_number_list.extend(p_pax_list.COUNT - 1);
   END IF;

   -- Attempt to stop the bags for each passenger in turn
   FOR i IN 1 .. p_pax_list.COUNT LOOP
      clear_bags(p_location_code, p_user_id, p_flight_id, p_pax_list(i),
                 p_serial_no, p_error_message_list(i), p_error_number_list(i));
   END LOOP;

END clear_multiple_passengers;

-- UAS/FALCP/CA/02658: Count how many stopped bags a given passenger has
FUNCTION pax_stopped_bag_count(p_pax_id IN pax.pax_id%TYPE)
      RETURN INTEGER AS
   l_bag_count INTEGER;
BEGIN
   SELECT COUNT(*) INTO l_bag_count
   FROM bag
   WHERE bag.pax_id = p_pax_id
   AND bag.delete_marker = 0
   AND bag.authority_to_load_brs = 'N';

   RETURN l_bag_count;
END pax_stopped_bag_count;

-- UAS/FALCP/CA/02658: Count how many non-stopped bags a given passenger has
FUNCTION pax_unstopped_bag_count(p_pax_id IN pax.pax_id%TYPE)
      RETURN INTEGER AS
   l_bag_count INTEGER;
BEGIN
   SELECT COUNT(*) INTO l_bag_count
   FROM bag
   WHERE bag.pax_id = p_pax_id
   AND bag.delete_marker = 0
   AND bag.authority_to_load_brs = 'Y';

   RETURN l_bag_count;
END pax_unstopped_bag_count;

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
        ) AS
        v_bag_tag2a                     bag.bag_tag2a%TYPE;
        v_bag_tag2b                     bag.bag_tag2b%TYPE;
        v_user_id                   tagtrak_users.user_id%TYPE;

        c_action                    CONSTANT bag_trace.trace_action%TYPE := 'STOP';
        -- Tracking variables
        l_event_count                number;
        l_no_of_errs                 number;
        l_process         VARCHAR2(30);
        l_error_number NUMBER DEFAULT 0;
        l_error_message VARCHAR2(512);

        BEGIN
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
        *                                 *
        * PURPOSE                         *
        *                                 *
        * Stop all the bags for a passenger                       *
        *                                 *
        * PROCESS                           METHOD     TYPE       *
        *                                 *
        * Deny authority to load to the passenger                   Query      Local      *
        * Select all the bags for the passenger         Cursor     Internal   *
        * Maintain traceability for each bag using add_bag_trace_record         Procedure  External   *
        *                                 *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

    -- Create a trace entry (HANDLER_TRACE) for this flight
    handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

    -- Fire any automatic tracking events for this bag (ignore errors, as these
    -- should not affect the operation itself)

    tracking.auto_events(0, p_flight_id, NULL, p_pax_id, 0, p_user_id,
                         p_location_code, NULL, l_event_count, l_no_of_errs);

    v_user_id       := p_user_id;

    l_process := 'update bag : ';

    UPDATE  bag
    SET         authority_to_load_brs = 'N'
    WHERE           authority_to_load_brs <> 'N'
    AND         pax_id  =       p_pax_id;

    IF SQL%NOTFOUND THEN
            l_error_number := -1;
            l_error_message :=        'passenger '
                        || p_pax_id ||
                        ' has no unstopped bags';
            RAISE abort;
    END IF;

    v_user_id := p_user_id;

    l_process := 'add_bag_trace : ';

    FOR bag IN bags(p_pax_id) LOOP
            v_bag_tag2a     := bag.bag_tag2a;
            v_bag_tag2b     := bag.bag_tag2b;


            add_bag_trace_with_reasons(v_user_id
                                ,p_location_code
                                ,0      -- error_code
                                ,bag.bag_tag
                                ,bag.bag_tag1
                                ,v_bag_tag2a
                                ,v_bag_tag2b
                                ,bag.bag_tag3
                                ,c_action -- trace_action
                                ,0 -- scanner_id
                                ,p_flight_id
                                ,p_pax_id
                                ,bag.uld_no
                                ,sysdate
                                ,p_reason);
    END LOOP;

    p_error_number := 0;
    p_error_message :=      'stop_bags successful for passenger '
                        || p_pax_id;
    COMMIT;

        EXCEPTION
    WHEN abort THEN
            p_error_number := l_error_number;
            p_error_message := l_process || l_error_message;
            ROLLBACK;
    WHEN OTHERS THEN
            ROLLBACK;
            raise_application_error(-20000,l_process || SQLERRM);
        END stop_bags_2;

END mws_web;
/
