/*****************************************************************************
*
* Filename         - $Workfile$
*
*                     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          - FALCP\SS\01940
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PACKAGE Process_Message
CREATE OR REPLACE PACKAGE process_message
as

    -- Decodes an incoming BMM message
    --
    -- Arguments:
    --         msg_id        - The message ID to parse.
    --         ok        - True is returned in this if the process worked.
    --
    procedure Decode_BMM
    (
            msg_id in  messages.message_id%type,
            ok     out boolean
    );


    -- Encodes an outgoing BMM message
    --
    -- Arguments:
    --         id        - The Flight ID to create a BMM for.
    --
    function Build_BMM
    (
            id  in  flight.flight_id%type
    ) return messages.message_id%TYPE;

    -- Check if an object has the correct format and/or value
    --
    -- Arguments:
    --        p_validation_object - Object string from BMM field to validate
    --        p_object_type   	  - Type of object to validate. Values are:
	--                              DATE      - Check if date is valid and has format DDMON
	--                              BAG_TYPE  - Check if type is one of: 'M', 'T', 'S' or 'X'.
	--                              FLIGHT    - Check if flight number has format AA(A)NNN(N)(A),
	--                                          wher airline code AA(A) is valid and flight number
	--                                          has at least 3 digits.
    --

	function is_object_valid
	(
	    p_validation_object IN  CHAR,
	    p_object_type       IN  VARCHAR2
    ) return BOOLEAN;

    -- Creates an entry in the MESSAGES_TRACE table.
    --
    -- Arguments:
    --        id        - The message ID to log an error against.
    --        part_id   - The message part ID to log an error against (can be null).
    --        action    - Either 'MESSAGE_CREATE', 'MESSAGE_DELETE', 'MESSAGE_SEND',
    --                    'MESSAGE_SEND_FAIL', 'MESSAGE_RECEIVE_ERROR' or null.
    --        dest      - Message destination (can be null).
    --        route     - Message routine info (can be null).
    --
    procedure Add_Message_Trace
    (
            p_id      in        messages.message_id%type,
            p_part_id in        message_parts.message_part_id%type,
            p_action  in        messages_trace.trace_action%type,
            p_dest    in        messages_trace.message_destination%type,
            p_route   in        messages_trace.message_route_id%type,
            p_user_id in        tagtrak_users.user_id%type
    );


    --
    -- ****** Imported from previous PROCESS_MESSAGE
    --

    -- Exceptions
    INVALID_MESSAGE      EXCEPTION;

    -- Types
    TYPE t_field_list   IS TABLE OF VARCHAR2(64)       INDEX BY BINARY_INTEGER;
    TYPE bag_id_type    IS TABLE OF bag.bag_id%TYPE    INDEX BY BINARY_INTEGER;
	  TYPE bag_tag_type   IS TABLE OF bag.bag_tag%TYPE   INDEX BY BINARY_INTEGER;
    TYPE bag_tag3_type  IS TABLE OF bag.bag_tag3%TYPE  INDEX BY BINARY_INTEGER;
    TYPE bag_tag2b_type IS TABLE OF bag.bag_tag2b%TYPE INDEX BY BINARY_INTEGER;

    MAX_BAG_ROWS NUMBER := 1000;

    -- Functions

    -- Procedures
    PROCEDURE split_message_line
    (
            p_line       IN     VARCHAR2,
            p_fields     IN OUT t_field_list,
            p_element       OUT VARCHAR2
    );

    FUNCTION find_record
    (
            p_part1      IN     VARCHAR2,
            p_part2      IN     VARCHAR2,
            p_element    IN     VARCHAR2,
            p_occurrence IN     NUMBER   DEFAULT 1
    ) RETURN VARCHAR2;

    FUNCTION get_bmm_identity
    ( p_uld_type       IN ulds.uld_type%TYPE
     ,p_flight_id      IN uld_dests.flight_id%TYPE
     ,p_uld_no         IN uld_dests.uld_no%TYPE
     ,p_next_flt_dest  IN flight_dests.flight_destination%TYPE
     ,p_uld_dest_count IN NUMBER) RETURN VARCHAR2;

    PROCEDURE derive_bmm_identity
    ( p_identity     IN  VARCHAR2
     ,p_uld_type     OUT ulds.uld_type%TYPE
     ,p_uld_category OUT ulds.uld_category%TYPE);

    PROCEDURE screen_bsm_bmm_bag
    ( p_pax_id      IN  pax.pax_id%TYPE
     ,p_flight_id   IN  flight.flight_id%TYPE);

end Process_Message;
/
CREATE OR REPLACE package body process_message
is

    --
    -- ******************** PRIVATE ********************
    --


    --
    -- ********** Constants
    --
    ENCODED_MAXLEN              constant number := 4080;
    ENCODED_PART1_MAXLEN        constant number := 4000;
    EOL                         constant char := chr(10);


    --
    -- ********** Data Types
    --

    -- Used as a general string.
    --
    subtype String is varchar2(256);

    -- This is used to represent a list of strings.
    --
    type String_List is table of String index by binary_integer;

    -- This is used to store a list of bags for the current PAX
    --
    type Bag_List is table of inbound_bag.inbound_bag_id%type
                     index by binary_integer;

    -- These are used to store a list of destinations for the current PAX
    --
    type Onward_Flight_No_List is table of
            inbound_pax_onward_dests.onward_flight_no%type
            index by binary_integer;

    type Onward_Flight_Date_List is table of
            inbound_pax_onward_dests.onward_flight_date%type
            index by binary_integer;

    type Onward_Dest_List is table of
            inbound_pax_onward_dests.onward_destination%type
            index by binary_integer;

    type Onward_Class_List is table of
            inbound_pax_onward_dests.onward_class%type
            index by binary_integer;

    type t_dest_list is table of site_info.airport_code%TYPE;

    -- This is a local exception used for abort conditions.
    --
    BMM_Exception exception;

    -- This is used to store the flight ID we are working on.
    --
    subtype Flight_ID_Type is flight.flight_id%type;

    -- This is used to store the ULD ID we are working with.
    --
    subtype ULD_ID_Type is inbound_uld.inbound_uld_id%type;

    -- This is used to store the PAX ID we are working with.
    --
    subtype PAX_ID_Type is inbound_pax.inbound_pax_id%type;

    -- Stores state on a flight
    --
    type Flight_State is record
    (
            id        Flight_ID_Type default null,
            fltdate   flight.flight_date%type default null,
            fltno     flight.flight_no%type default null,
            airport   site_info.airport_code%type default null
    );

    -- This is used to store state on the BMM as a whole while processing it.
    --
    type Message_State is record
    (
        msg_id                messages.message_id%type,

        version               char(1) default null,
        baggage_source        char(1) default null,
        airport_code          char(3) default null,
        site_id               number default null,

        msgref                char(5) default null,

        inbound               Flight_State,
        outbound              Flight_State,

        current_bags          Bag_List,

        current_uld           ULD_ID_Type default null,
        current_pax           PAX_ID_Type default null,

        -- These fields are to store state to be flushed out on a per-PAX basis.
        --
        onward_fno            Onward_Flight_No_List,
        onward_fdate          Onward_Flight_Date_List,
        onward_dest           Onward_Dest_List,
        onward_class          Onward_Class_List,
        pax_samename          number default 1,
        pax_surname           String default null,
        pax_initials          String default null,
        pax_full_inits        String default null,
        pax_frequent          String default null,
        pax_destination       char(3) default null,

        -- Flags for sanity checks
        --
        set_pax_called        boolean,
        is_existing_pax       boolean
    );


    -- These types are used to store per-item data for the encode routines.
    -- This is as long as possible as it used to store multiple lines in some
    -- instances, ie. each Encoded_Data either starts with .V, .I, .U, .F or .N.
    --
    subtype Encoded_Data is varchar2(4000);

    type Encoded_List is table of Encoded_Data index by binary_integer;


    -- Represents a bag in the database
    --
    subtype Bag_Record is bag%rowtype;


    --
    -- ********** Decode Cursors
    --
    cursor Message_Cursor(id messages.message_id%type) is
            select   message_parts.message_text1,
                     message_parts.message_text2
            from     message_parts
            where    message_parts.message_id = id
            order by message_parts.part_no asc;

    cursor Previous_Message_Cursor(vers messages.version%type,
                                   fno messages.flight_number%type,
                                   fdate messages.flight_date%type,
                                   thisid messages.message_id%type) is
            select   messages.message_id,
                     messages.flight_id
            from     messages
            where    messages.version = vers and
                     messages.flight_number = fno and
                     messages.flight_date = fdate and
                     messages.processed_state = 'P' and
                     messages.message_id <> thisid
            for update;

    cursor Find_Untransfer_ULD_Cursor (id messages.message_id%type) is
            select   inbound_uld.result_uld_no,
                     inbound_uld.result_uld_flight_id
            from     inbound_uld
            where    inbound_uld.source_message_id = id  and
                     inbound_uld.inbound_uld_status = 'TRANSFER' and
                     inbound_uld.result_uld_no is not null and
                     inbound_uld.result_uld_flight_id is not null and
                     inbound_uld.result_uld_status in ('CREATED', 'AMENDED');

    --
    -- ********** Encode Cursors
    --
    cursor Encode_Find_Bulk_ULDs_Cursor(id Flight_ID_Type) is
            select   *
            from     ulds
            where    ulds.flight_id = id and
                     ulds.uld_no in (select hold_no from holds);

    cursor Encode_Find_ULDs_Cursor(id Flight_ID_Type) is
            select   DISTINCT ulds.*
            from     ulds
            where    ulds.flight_id = id and
                     ulds.uld_no not in (select hold_no from holds);

    cursor Encode_Bags_Without_Onward(uld ulds.uld_no%type,
                                      id Flight_ID_Type,
                                      next_airport site_info.airport_code%type) is
            select   b.*
            from     bag b, pax p
            where    b.uld_no = uld and
                     b.uld_flight_id = id and
                     b.loaded_status = 'Y' and
                     b.pax_id = p.pax_id and
                     p.pax_destination = next_airport and
                     not exists
                        (select 1
                         from   onward_flight o
                         where  o.pax_id = b.pax_id);

    cursor Encode_Find_Onward_Flights(uld ulds.uld_no%type,
                                      id Flight_ID_Type,
                                      next_airport site_info.airport_code%type) is
            select   distinct f.flight_no no,
                              f.flight_date odate,
                              p1.pax_class class,
                              p1.pax_destination dest
            from     flight f, pax p1
            where    f.flight_id = p1.flight_id and
                     p1.pax_destination <> next_airport and
                     p1.pax_id in
                        (select b1.pax_id
                         from   bag b1
                         where  b1.uld_no = uld and
                                b1.uld_flight_id = id)
          union
            select   distinct o.norm_fno no,
                              o.odate,
                              o.class,
                              o.dest
            from     onward_flight o, pax p2
            where    o.dest_order = 1 and
                     p2.pax_id = o.pax_id and
                     p2.pax_destination = next_airport and
                     o.pax_id in
                        (select b2.pax_id
                         from   bag b2
                         where  b2.uld_no = uld and
                                b2.uld_flight_id = id);

    cursor Encode_Find_Bags_For_Onward(fno onward_flight.no%type,
                                       fdate onward_flight.odate%type,
                                       class onward_flight.class%type,
                                       id Flight_ID_Type,
                                       uld ulds.uld_no%type,
                                       next_airport site_info.airport_code%type) is
            select   b.*
            from     bag b, pax p1, flight_outbound_v1 f
            where    b.loaded_status = 'Y' and
                     b.uld_flight_id = p1.flight_id and
                     b.uld_no = uld and
                     b.pax_id = p1.pax_id and
                     p1.flight_id = id and
                     p1.flight_id = f.flight_id and
                     ((p1.pax_destination = next_airport and
                     b.pax_id in
                        (select o.pax_id
                         from   onward_flight o
                         where  o.dest_order = 1 and
                                o.norm_fno = fno and
                                o.odate = fdate))
                     or
                     (p1.pax_destination <> next_airport and
                      f.flight_no = fno and
                      f.flight_date = fdate));

    cursor Encode_Onward_Flights_For_PAX(id pax.pax_id%type) is
            select   o.no,
                     o.odate,
                     o.class,
                     o.dest
            from     onward_flight o,
                     pax p
            where    (o.dest_order > 1 or (o.dest_order=1 and o.dest <> p.pax_destination))
              and    o.pax_id = p.pax_id
              and    p.pax_id = id
            order by o.dest_order asc;


    --
    -- ********** General Routines
    --

    -- Logs a decode error.
    --
    -- Arguments:
    --         id       - The message causing the error.
    --         e        - The error string.
    --
    procedure Decode_BMM_Error(id in messages.message_id%type, e in VARCHAR, p_airport IN site_info.airport_code%type)
    is
        pragma AUTONOMOUS_TRANSACTION;
    begin
        Add_Message_Trace(id,null,'RECEIVE_ERROR',null,null, multi_site.get_auto_user(p_airport));
        insert into oracle_error
	    (
		process_name,
		errror_msg,
		error_code,
		error_time
	    )
	    values
            (
                'Process_Message.Decode_BMM',
                'Message ID ' || id || ' -- ' || e,
                null,
                sysdate
            );
        commit;
    end Decode_BMM_Error;


    -- Log an encode error.
    --
    -- Arguments:
    --         e        - The error string.
    --
    procedure Encode_BMM_Error(e in varchar)
    is
        pragma AUTONOMOUS_TRANSACTION;
    begin
        insert into oracle_error
            (
                process_name,
                errror_msg,
                error_code,
                error_time
            )
	values
            (
                'Process_Message.Build_BMM',
                e,
                null,
                sysdate
            );
        commit;
    end Encode_BMM_Error;


    -- Flags a message as being in error.
    --
    -- Arguments:
    --         id        - The message ID.
    --
    procedure Set_Message_Error_State(id in messages.message_id%type)
    is
        pragma AUTONOMOUS_TRANSACTION;
    begin
        update messages set   messages.processed_state = 'E'
                        where messages.message_id = id;
        commit;
    end Set_Message_Error_State;


    -- Returns the element if the element exists in the String_List, otherwise
    -- null.  This is used to extract optional elements from IATA lines.
    --
    -- Arguments:
    --         i        - The element index
    --         list     - The String_List
    --
    function Get_Optional(i in number, list in String_List) return varchar2
    is
    begin
        if list.count >= i then
            return list(i);
        else
            return null;
        end if;
    end Get_Optional;


    -- Debug routine to dump a string list.
    --
    -- Arguments:
    --         prompt        - The description.
    --         list        - The string list to dump.
    --
    procedure Dump_List(prompt varchar2, list in String_List)
    is
    begin
        for i in 1 .. list.count loop
            dbms_output.put_line(prompt || '(' || i || ')="' || list(i) || '"');
        end loop;
    end Dump_List;


    -- Debug routine to dump a message.
    --
    -- Arguments:
    --         msg        - The message to dump.
    --
    procedure Dump_Message(msg in String_List)
    is
    begin
        Dump_List('MESSAGE',msg);
    end Dump_Message;



    -- Tokenise a string
    --
    -- Arguments:
    --         data       - The string to tokenise
    --         dest       - The next token, if any.
    --         sep        - The token seperator.
    --         pos        - Used to store state.  Set to 1 before the initial call.
    --
    -- Returns:
    --        true        - A token found and returned in dest.
    --        false       - Tokens exhausted.
    --
    function Tokenise(data       in        varchar,
                      dest       out       varchar,
                      sep        in        char,
                      pos        in out    number) return boolean
    is
            nextpos        number;
    begin
        -- Check for termination
        --
        if pos = -1 then
            return false;
        end if;

        -- Find the next seperator
        --
        nextpos := instr(data,sep,pos);

        -- If no more seperators, return the tail and note the termination for
        -- next iteration
        --
        if nextpos = 0 then
            dest := substr(data,pos);
            pos := -1;
        else
            dest := substr(data,pos,nextpos-pos);
            pos := nextpos + 1;
        end if;

        return true;
    end Tokenise;


    -- Split an IATA line into tokens.
    --
    -- Arguments:
    --         data        - The line to tokenise
    --         list        - The tokens.
    --
    -- Returns:
    --        The IATA line token (V, I, N, etc) or null if none.
    --
    function Tokenise_Line(data        in                varchar,
                           list        in out nocopy     String_List) return varchar2
    is
        tokind        number := 1;
        tokpos        number := 1;
        token        String;
    begin

        -- Fetch the tokens
        --
        while Tokenise(data,token,'/',tokpos) loop
            list(tokind) := token;
            tokind := tokind + 1;
        end loop;

        -- Check for a .<char> start
        --
        if list.count > 0 and length(list(1)) > 1 and
                                substr(list(1),1,1) = '.' then
            return substr(list(1),2);
        else
            return null;
        end if;
    end Tokenise_Line;


    -- Derive the ULD Type and ULD Category from the
    -- Incoming BMMS .U identity i.e.  Type of Baggage in Container/Location
    procedure derive_bmm_identity ( p_identity     IN  VARCHAR2
                                   ,p_uld_type     OUT ulds.uld_type%TYPE
                                   ,p_uld_category OUT ulds.uld_category%TYPE)
    is
    begin
        -- mixed identity
        if (p_identity = 'M') THEN
            p_uld_type     := 'M';
            p_uld_category := 'S';
        -- terminating identity
        elsif (p_identity = 'X') THEN
           -- this does not matter since the uld will not be transferred to the outbound flight
           -- so no mapping is required.
           p_uld_type     := 'L';
           p_uld_category  := 'B';
        -- transfer identity
        elsif (p_identity = 'T') THEN
           p_uld_type     := 'T';
           p_uld_category := 'T';
        -- 'S' identity
        elsif (p_identity = 'S') THEN
           p_uld_type     := 'T';
           p_uld_category := 'T';
        end if;
    end derive_bmm_identity;

    -- Get the BMM indentity and uses the uld_type amd uld_category
    ---to be able to derive this value
    ---
    function get_bmm_identity ( p_uld_type       IN ulds.uld_type%TYPE
                               ,p_flight_id      IN uld_dests.flight_id%TYPE
                               ,p_uld_no         IN uld_dests.uld_no%TYPE
                               ,p_next_flt_dest  IN flight_dests.flight_destination%TYPE
                               ,p_uld_dest_count IN NUMBER) RETURN VARCHAR2
    is
     l_uld_dest uld_dests.uld_destination%TYPE;
	 identity varchar2(1) := NULL;
    begin
      if ( p_uld_dest_count = 1 )
	  then
	      SELECT uld_destination
		  INTO   l_uld_dest
		  FROM   uld_dests u
		  WHERE  u.flight_id = p_flight_id
		  AND    u.uld_no = p_uld_no;
	  end if;

	  -- Type of Baggage Container/Location (identity), which can be one of the
      -- following:  M = Mixed; T = Transfer; S = Short Connection ; X = Terminating
      if  (p_uld_type = 'L')  THEN
        if  (l_uld_dest = p_next_flt_dest) THEN
          identity := 'X';
        else
          identity := 'T'; -- still a through uld
        end if;
      elsif (p_uld_type = 'M') THEN
          identity := 'M';
      elsif (p_uld_type = 'T') THEN
          if (p_uld_dest_count = 1) THEN
             if (l_uld_dest = p_next_flt_dest) THEN
                identity := 'X';
             else
                identity := 'T';
             end if;
          else
             identity := 'T';
          end if;
      end if;

      return(identity);
    exception
       when others then
          Encode_BMM_Error('get_bmm_identity :'||SQLCODE);
    end get_bmm_identity;

    -- count the number of uld_dests records for the uld
    --
    function count_uld_dests ( p_uld_no    IN ulds.uld_no%TYPE
                              ,p_flight_id IN flight.flight_id%TYPE) RETURN NUMBER
    is
      l_tot_uld_dests NUMBER := 0;
    begin
      select count(*)
      into   l_tot_uld_dests
      from   uld_dests
      where  uld_dests.uld_no    = p_uld_no
      and    uld_dests.flight_id = p_flight_id;

      return(l_tot_uld_dests);
    end count_uld_dests;


    -- Fetches a message into a String_List type.
    --
    -- If the fetched message does not follow the basic rules of formatting
    -- a BMM then an exception is thrown.
    --
    -- Arguments:
    --         id        - The message ID.
    --         msg       - The resulting message lines.
    --
    procedure Fetch_Message(id  in                messages.message_id%type,
                            msg in out nocopy     String_List)
    is
        line_number number;
        line String;
        in_line boolean;
        c char(1);
        parts number;
        last_line_checked number;
    begin

        parts := 0;
        in_line := false;
        line_number := 1;

        for msgpart in Message_Cursor(id) loop

            -- Record the number of message parts
            --
            parts := parts + 1;

            -- Simple state machine to split up the message
            --
            if msgpart.message_text1 is not null then
                for i in 1 .. length(msgpart.message_text1) loop
                    c := substr(msgpart.message_text1,i,1);

                    -- If we hit a line end character, store the line if we've
                    -- read one.  Otherwise add the character to the current
                    -- line.
                    --
                    if c = chr(10) or c = chr(13) then
                        if in_line then
                            msg(line_number) := line;
                            line := '';
                            line_number := line_number + 1;
                            in_line := false;
                        end if;
                    else
                        line := concat(line,c);
                        in_line := true;
                    end if;
                end loop;
            end if;

            -- Rinse and repeat
            --
            if msgpart.message_text2 is not null then
                for i in 1 .. length(msgpart.message_text2) loop
                    c := substr(msgpart.message_text2,i,1);

                    if c = chr(10) or c = chr(13) then
                        if in_line then
                            msg(line_number) := line;
                            line := '';
                            line_number := line_number + 1;
                            in_line := false;
                        end if;
                    else
                        line := concat(line,c);
                        in_line := true;
                    end if;
                end loop;
            end if;

        end loop;

        -- Validate that the message at least looks like a BMM.
        --
        if msg.count < 2 then
            Decode_BMM_Error(id,'Too short', NULL);
            raise BMM_Exception;
        end if;

        if msg(1) <> 'BMM' then
            if msg(1) <> 'BCM' or msg(2) <> 'BMM' then
                Decode_BMM_Error(id,'Does not start with BMM/BCM', NULL);
                raise BMM_Exception;
            end if;
        end if;

        -- Check for ENDPARTs if appropriate.
        -- There's probably some proper SQL way of doing this...
        --
        if parts > 1 then
            last_line_checked := 1;
            for i in 1 .. parts-1 loop
                declare
                    found boolean := false;
                    expected String := 'ENDPART' || i;
                begin
                    for j in last_line_checked .. msg.count loop
                        if msg(j) = expected then
                            found := true;
                            last_line_checked := j;
                            exit;
                        end if;
                    end loop;

                    if not found then
                        Decode_BMM_Error(id,expected || ' is missing or incorrectly placed.', NULL);
                        raise BMM_Exception;
                    end if;
                end;
            end loop;
        end if;

    end Fetch_Message;


    -- Adds an inbound ULD destination.
    --
    -- Arguments:
    --         id        - The incoming ULD ID.
    --         fno       - The flight number.
    --         fdate     - The flight date.
    --         dest      - The destination.
    --         class     - The class.
    --
    procedure Add_ULD_Dest(id    in inbound_uld.inbound_uld_id%type,
                           fno   in inbound_uld_dests.onward_flight_no%type,
                           fdate in inbound_uld_dests.onward_flight_date%type,
                           dest  in inbound_uld_dests.destination%type,
                           class in inbound_uld_dests.class_list%type)
    is
        destseq inbound_uld_dests.destination_sequence%type;
    begin

        -- Find the current sequence number and increment it (set to one if
        -- none defined).
        --
        begin
            select max(inbound_uld_dests.destination_sequence) into destseq
                from inbound_uld_dests
                where inbound_uld_dests.inbound_uld_id = id;

            if destseq is null then
                    destseq := 1;
            else
                destseq := destseq + 1;
            end if;
        exception
            when NO_DATA_FOUND then
                    destseq := 1;
        end;

        -- Save the ULD destination
        --
        insert into inbound_uld_dests
            (
                inbound_uld_dests.inbound_uld_id,
                inbound_uld_dests.destination_sequence,
                inbound_uld_dests.destination,
                inbound_uld_dests.class_list,
                inbound_uld_dests.onward_flight_no,
                inbound_uld_dests.onward_flight_date
            )
        values
            (
                id,
                destseq,
                dest,
                class,
                fno,
                fdate
            );
    end Add_ULD_Dest;


    -- Resets the state for a new PAX
    --
    -- Arguments:
    --         state        - The message state
    --
    procedure Reset_For_New_PAX(state in out nocopy Message_State)
    is
    begin
        state.current_pax := null;

        state.current_bags.delete(1,state.current_bags.last);

        -- mustn't delete the first record, since we need this for the
        -- the first onward flight when decoding .O fields

        state.onward_fno.delete(2,state.onward_fno.last);

        state.onward_fdate.delete(2,state.onward_fdate.last);

        state.onward_dest.delete(2,state.onward_dest.last);

        state.onward_class.delete(2,state.onward_class.last);

        state.pax_samename := 1;
        state.pax_surname := null;
        state.pax_initials := null;
        state.pax_full_inits := null;
        state.pax_frequent := null;

        state.set_pax_called := false;
        state.is_existing_pax := false;
    end Reset_For_New_PAX;


    -- If a PAX has been found to already exist, then this is used to delete
    -- the newly created PAX and set currently defined data to use the new one.
    --
    -- Arguments:
    --         pax_id       - The PAX Id
    --         state        - The message state
    --
    procedure Set_State_For_PAX(pax_id        in                PAX_ID_Type,
                                state         in out nocopy     Message_State)
    is
    begin
        -- This should only be called the once per PAX to remove the newly
        -- created one and use a current one instead.  This check prevents us
        -- removing a current PAX accidentally.
        --
        if state.set_pax_called then
            Decode_BMM_Error(state.msg_id,
                 'Serious logic error --  Set_State_For_PAX repeated.', state.airport_code);
            raise BMM_Exception;
        end if;

        -- Update the message ID
        --
        update inbound_pax
            set inbound_pax.source_message_id = state.msg_id
                    where inbound_pax.inbound_pax_id = pax_id;

        -- Update the bags
        --
        forall i in 1 .. state.current_bags.count
            update inbound_bag
                    set inbound_bag.inbound_pax_id = pax_id
                    where inbound_bag.inbound_bag_id = state.current_bags(i);

        -- Remove the previous PAX and remember the new one
        --
        delete from inbound_pax where
                        inbound_pax.inbound_pax_id = state.current_pax;
        state.current_pax := pax_id;
        state.set_pax_called := true;

    end Set_State_For_PAX;


    -- Checks for a PAX match.
    --
    -- Arguments:
    --         state        - The message state
    --
    procedure Check_PAX_Match(state in out nocopy Message_State)
    is
        pax_id PAX_ID_Type := null;
        flightlist pm_onward_flight_list := pm_onward_flight_list();
        baglist pm_bag_list := pm_bag_list();
        pmrec pm_pax_record;
        dest String;
        bag_except bag.bag_exception%type;
        tag1 bag.bag_tag1%type;
        tag2a bag.bag_tag2a%type;
        tag2b bag.bag_tag2b%type;
        tag3 bag.bag_tag3%type;
        tagnum bag.bag_tag%type;
        tagtype number;
    begin
        -- Don't do anything if no passenger or if already done
        --
        if state.current_pax is null or state.set_pax_called then
            return;
        end if;

        -- Load up the PAX_MATCH structure
        --
        flightlist.extend(state.onward_fdate.count);
        baglist.extend(state.current_bags.count);

        dest := null;

        for i in 1 .. state.onward_fno.count loop
            flightlist(i) := pm_onward_flight_record(state.onward_fno(i),
                                                     state.onward_fdate(i),
                                                     state.onward_dest(i),
                                                     i,
                                                     state.onward_class(i));

            dest := state.onward_dest(i);
        end loop;

        for i in 1 .. state.current_bags.count loop
            declare
                tag inbound_bag.bag_tag%type;
            begin
                select inbound_bag.bag_tag,
                       inbound_bag.bag_exception_type
                into   tag,
                       bag_except
                from   inbound_bag
                where  inbound_bag.inbound_bag_id = state.current_bags(i);

                split_tags(tag, tag1, tag2a, tag2b, tag3, tagnum, tagtype, 0);

                baglist(i) := pm_bag_record(tag, tag2b, tag3,
                                            null, null, bag_except, null,
                                            null, null, null, null);
            end;
        end loop;

        pmrec := pm_pax_record(state.pax_initials,
                               state.pax_surname,
                               state.pax_samename,
                               dest,
                               null,                        -- Seat No
                               null,                        -- Class
                               null,                        -- PAX Seq No
                               null,                        -- PAX Security No
                               null,                        -- Name Record
                               null,                        -- Dep Airport Code
                               state.inbound.fltno,
                               state.inbound.fltdate,
                               state.inbound.airport,       -- Boarding point
                               state.pax_full_inits,        -- PAX Full Inits
                               null,                        -- DCS Seq No
                               state.pax_frequent,
                               flightlist,
                               baglist);

        -- Call PAX_MATCH
        --
        pax_id := pax_match.perform_match(config.get_value('BMM_INBOUND_PAX_MATCHING'),
                                          pmrec,
                                          state.inbound.id);

        if pax_id is not null and pax_id > 0 then
            Set_State_For_PAX(pax_id,state);
            state.is_existing_pax := true;
        else -- no match ie pax_id is 0
            update inbound_pax set result_pax_status = 'UNPROCESSED'
            where inbound_pax_id = state.current_pax;
        end if;

    end Check_PAX_Match;


    -- Flushes state on starting a new PAX.
    --
    -- Arguments:
    --         state        - The message state
    --
    procedure Flush_State(state in out nocopy Message_State)
    is
            destseq number;
    begin
        -- Don't do anything if no passenger
        --
        if state.current_pax is null then
            return;
        end if;

        -- Check for a PAX match
        --
        Check_PAX_Match(state);

        -- Update PAX name
        --
        if state.pax_surname is not null then
            -- Trim names
            --
            if length(state.pax_surname) > 27 then
                state.pax_surname := substr(state.pax_surname,1,27);
            end if;

            /* Pax full initials */
            if state.pax_initials is not null and
                            length(state.pax_initials) > 4 then
                state.pax_initials := substr(state.pax_initials,1,4);
            end if;

            update inbound_pax
                set inbound_pax.pax_surname = state.pax_surname,
                    inbound_pax.pax_initials = state.pax_initials,
                    inbound_pax.pax_full_inits = state.pax_full_inits,
                    inbound_pax.pax_number_same_surname = state.pax_samename
                where inbound_pax.inbound_pax_id = state.current_pax;
        end if;

        -- Update frequent flyer info
        --
        if state.pax_frequent is not null then
            update inbound_pax
                set inbound_pax.frequent_flyer = state.pax_frequent
                    where inbound_pax.inbound_pax_id = state.current_pax;
        end if;

        -- Update pax_destination
        --
        if state.pax_destination is not null then
           update inbound_pax
               set inbound_pax.pax_destination = state.pax_destination
                   where inbound_pax.inbound_pax_id = state.current_pax;
        end if;

        -- Get the current onward destination sequence.
        --
        begin
            select max(inbound_pax_onward_dests.destination_sequence)
                    into destseq
                    from inbound_pax_onward_dests
                        where inbound_pax_onward_dests.inbound_pax_id =
                                                            state.current_pax;

            if destseq is null then
                destseq := 1;
            else
                destseq := destseq + 1;
            end if;
        exception
            when NO_DATA_FOUND then
                    destseq := 1;
        end;

        -- Add the buffered ownard destinations if they don't already exist
        --
        for i in 1 .. state.onward_fno.count loop
            declare
                    num number;
            begin
                select count(*) into num from inbound_pax_onward_dests
                where inbound_pax_onward_dests.inbound_pax_id =
                                                    state.current_pax and
                      inbound_pax_onward_dests.onward_flight_no =
                                                    state.onward_fno(i) and
                      inbound_pax_onward_dests.onward_flight_date =
                                                    state.onward_fdate(i) and
                      inbound_pax_onward_dests.onward_destination =
                                                    state.onward_dest(i) and
                      inbound_pax_onward_dests.onward_class =
                                                    state.onward_class(i);

                if num = 0 then
                   insert into inbound_pax_onward_dests
                        (
                            inbound_pax_onward_dests.inbound_pax_id,
                            inbound_pax_onward_dests.destination_sequence,
                            inbound_pax_onward_dests.onward_flight_no,
                            inbound_pax_onward_dests.onward_flight_date,
                            inbound_pax_onward_dests.onward_destination,
                            inbound_pax_onward_dests.onward_class
                        )
                    values
                        (
                            state.current_pax,
                            destseq,
                            state.onward_fno(i),
                            state.onward_fdate(i),
                            state.onward_dest(i),
                            state.onward_class(i)
                        );

                    destseq := destseq + 1;
                end if;
            end;
        end loop;

    end Flush_State;


    --
    -- ********** Encode Routines
    --


    -- Adds the passed string onto part1, until part1 is 4000 characters long,
    -- in which case it adds it to part2.
    --
    -- If adding this string would make the total length greater than 4080
    -- characters long then the string is not added and result is set to false.
    -- If the string is added, then result is set to true.
    --
    -- Arguments:
    --         data        - The data to add.
    --         part1       - Part 1 of the message part.
    --         part2       - Part 1 of the message part.
    --         result      - Whether the string was added or not.
    --
    procedure Add_To_Message_Parts(data   in                Encoded_Data,
                                   part1  in out nocopy     varchar2,
                                   part2  in out nocopy     varchar2,
                                   result out               boolean)
    is
    begin
        -- Check if too long
        --
        if length(part1) + length(part2) + length(data) > ENCODED_MAXLEN then
            result := false;
        else
            result := true;

            -- Check to see if there's enough room left in part1.  If not, add
            -- it to part2.
            --
            if length(part1) = ENCODED_PART1_MAXLEN then
                    part2 := part2 || data;
            elsif length(part1) + length(data) > ENCODED_PART1_MAXLEN then
                declare
                    remaining number;
                begin
                    remaining := ENCODED_PART1_MAXLEN - length(part1);
                    part1 := part1 || substr(data,1,remaining);
                    part2 := part2 || substr(data,remaining + 1);
                end;
            else
                    part1 := part1 || data;
            end if;
        end if;
    end Add_To_Message_Parts;


    -- Acts like Add_To_Message_Parts, but doesn't do the length check.  This
    -- version is used to terminate the message part, where the check is
    -- not needed.
    --
    -- Arguments:
    --         data        - The data to add.
    --         part1       - Part 1 of the message part.
    --         part2       - Part 1 of the message part.
    --
    procedure Terminate_Message_Parts(data   in                   Encoded_Data,
                                      part1  in out nocopy        varchar2,
                                      part2  in out nocopy        varchar2)
    is
    begin
        -- Check to see if there's enough room left in part1.  If not, add
        -- it to part2.
        --
        if length(part1) = ENCODED_PART1_MAXLEN then
            part2 := part2 || data;
        elsif length(part1) + length(data) > ENCODED_PART1_MAXLEN then
            declare
                remaining number;
            begin
                remaining := ENCODED_PART1_MAXLEN - length(part1);
                part1 := part1 || substr(data,1,remaining);
                part2 := part2 || substr(data,remaining + 1);
            end;
        else
            part1 := part1 || data;
        end if;
    end Terminate_Message_Parts;


    -- Stores the data for an encoded BMM, partioning as required
    --
    -- Arguments:
    --         id        - The message ID to relate this data to.
    --         data      - The data for the message.
    --
    procedure Store_Encoded_Message(id   in messages.message_id%type,
                                    data in Encoded_List)
    is
        current_v Encoded_Data := null;
        current_i Encoded_Data := null;
        current_u Encoded_Data := null;
        current_f Encoded_Data := null;
        part_num number := 1;
        part1 message_parts.message_text1%type := '';
        part2 message_parts.message_text2%type := '';
        command char(1);
        buffer Encoded_Data := '';
        added boolean;
    begin
        -- The data must start with BMM.
        --
        part1 := 'BMM' || EOL;

        -- Go through all the BMM parts
        --
		for i in 1 .. data.count loop

            -- Get the command for this block
            --
            if length(data(i)) > 1 then
                    command := substr(data(i),2,1);
            else
                    command := null;
            end if;

            -- Check for message lines that need to be saved for partitioning
            -- purposes.  Also we buffer data until we get a .N entry.
            --
            if command = 'V' then
				current_v := data(i);
                buffer := buffer || data(i) || '/PART' || part_num || EOL;

            elsif command = 'I' then
				current_i := data(i);
                buffer := buffer || data(i);

            elsif command = 'U' then
				current_u := data(i);
                current_f := null;
                buffer := buffer || data(i);

            elsif command = 'F' then
				current_f := data(i);
                buffer := buffer || data(i);

            elsif command ='N' then

                -- Sanity check
                --
		    if current_v is null or current_i is null then
                    Encode_BMM_Error('Bad BMM (missing a .V or .I before .N)');
                    raise BMM_Exception;
                end if;

                -- Add the message part, partioning if appropriate.  Feasibly
                -- this routine could go hidedously wrong if a .N entry is too
                -- big for a part of the BMM, but that shouldn't be possible.
                --
                buffer := buffer || data(i);

                if length(buffer) > ENCODED_MAXLEN then
                    Encode_BMM_Error('A single .N is too big (' ||
                                            length(data(i)) || ' chars)');
                    raise BMM_Exception;
                end if;

                -- Add the buffered data on
                --
                Add_To_Message_Parts(buffer,part1,part2,added);


		    -- If this can't be added, create a new message part
                --
                if not added then
					Terminate_Message_Parts
                            ('ENDPART' || part_num || EOL,part1,part2);

                    insert into message_parts
                        (
                            message_parts.message_text1,
                            message_parts.message_text2,
                            message_parts.part_no,
                            message_parts.timestamp,
                            message_parts.message_id
                        )
                    values
                        (
                            part1,
                            part2,
                            part_num,
                            sysdate,
                            id
                        );

                    -- Now create the start of the next partition with this
                    -- bag.  The V, I and U items must always be set.
                    --
                    part_num := part_num + 1;

                    buffer := 'BMM' || EOL;
                    buffer := buffer || current_v || '/PART' || part_num || EOL;
                    buffer := buffer || current_i;

                    if current_u is not null then
                        buffer := buffer || current_u;
                    end if;

                    if current_f is not null then
                        buffer := buffer || current_f;
                    end if;

                    buffer := buffer || data(i);

                    part1 := '';
                    part2 := '';

                    Add_To_Message_Parts(buffer,part1,part2,added);

                    if not added then
                        Encode_BMM_Error('Failed to initialise new BMM part');
                        raise BMM_Exception;
                    end if;
                end if;

                -- Reset the buffer
                --
                buffer := '';
            else
                Encode_BMM_Error('Segment is not V,I,U,F or N -- starts with "'
                                        || substr(data(i),1,10) || '"');
                raise BMM_Exception;
            end if;
        end loop;

        -- Save the last bit of message
        --
        if length(part1) > 0 then
            Terminate_Message_Parts('ENDBMM' || EOL,part1,part2);

            insert into message_parts
                (
                    message_parts.message_text1,
                    message_parts.message_text2,
                    message_parts.part_no,
                    message_parts.timestamp,
                    message_parts.message_id
                )
            values
                (
                    part1,
                    part2,
                    part_num,
                    sysdate,
                    id
                );
        end if;

        -- Save the eventual number of parts
        --
        update messages set messages.parts = part_num,
                            messages.parts_received = part_num
                                            where messages.message_id = id;
    end Store_Encoded_Message;


    -- Check that the given destination exists for the flight
    --
    -- Arguments:
    --         dest      - The destination to be found
    --         dest_list - A list of destinations for the flight
    --
    -- Returns:
    --        TRUE if the destination is found in the list.
    --
    function check_flight_dest(dest in site_info.airport_code%TYPE,
                               dest_list in t_dest_list) return boolean is

      l_found boolean := false;
      l_count number(10);

    begin

      if (dest_list is not null) then

        if (dest_list.count > 0) then

          l_count := 1;
          loop

            if (dest_list(l_count) = dest) then

              l_found := true;

            end if;

            l_count := l_count + 1;

            exit when (l_count > dest_list.count or l_found);

          end loop;

        end if;

      end if;

      return l_found;

    end check_flight_dest;

    -- Get the destination and class for a ULD.
    --
    -- Arguments:
    --         id        - The flight the ULD is associated with.
    --         no        - The ULD number.
    --
    -- Returns:
    --        The IATA formated string '/[<Class>]/<Dest>'
    --
    function Get_ULD_Final_Dest(id in Flight_ID_Type,
                                no in ulds.uld_no%TYPE,
                                ac IN site_info.airport_code%type) return String
    is
        class String;
        dest String;
        seq number;
        id_code ulds.uld_id_code%type;
        l_dest_list t_dest_list;

        cursor c_flight_dests (fid Flight_ID_Type) is
               select f.flight_destination
               from   flight_dests f
               where  f.flight_id = fid;

        cursor c_final_dest (fid Flight_ID_Type, uno ulds.uld_no%type) is
               (
                 select fd.flight_destination dest,
                        fd.dest_order seq,
                        ud.uld_class_list class
                 from   uld_dests ud,
                        flight_dests fd
                 where  ud.flight_id = fid
                   and  fd.flight_id = ud.flight_id
                   and  fd.flight_destination = ud.uld_destination
                   and  ud.uld_no = uno
               union
                 select ac dest,
                        0 seq,
                        NULL class
                 from   dual
               )
               order by seq desc;
    begin
        -- See if there is an entry in uld_recorded_id_codes for this ULD
        --
        select ulds.uld_id_code into id_code
            from ulds where ulds.uld_no = no and flight_id = id;

        if id_code is null then

            -- If not defined, find the last destination in common
            -- between the flight and the uld.  If none, then use the
            -- current airport
            --
            open c_final_dest (id, no);
            fetch c_final_dest into dest, seq, class;
            close c_final_dest;

        else
            -- Read in the destinations for the flight
            --
            open c_flight_dests(id);
            fetch c_flight_dests bulk collect into l_dest_list;
            close c_flight_dests;

            -- Otherwise, get it from uld_recorded_id_codes
            --
            declare
                    dest1 uld_recorded_id_codes.dest1%type;
                    dest2 uld_recorded_id_codes.dest2%type;
                    dest3 uld_recorded_id_codes.dest3%type;
                    dest4 uld_recorded_id_codes.dest4%type;
                    dest5 uld_recorded_id_codes.dest5%type;
                    dest6 uld_recorded_id_codes.dest6%type;
                    class1 uld_recorded_id_codes.class1%type;
                    class2 uld_recorded_id_codes.class2%type;
                    class3 uld_recorded_id_codes.class3%type;
                    class4 uld_recorded_id_codes.class4%type;
                    class5 uld_recorded_id_codes.class5%type;
                    class6 uld_recorded_id_codes.class6%type;
            begin
                select uld_recorded_id_codes.dest1,
                       uld_recorded_id_codes.dest2,
                       uld_recorded_id_codes.dest3,
                       uld_recorded_id_codes.dest4,
                       uld_recorded_id_codes.dest5,
                       uld_recorded_id_codes.dest6,
                       uld_recorded_id_codes.class1,
                       uld_recorded_id_codes.class2,
                       uld_recorded_id_codes.class3,
                       uld_recorded_id_codes.class4,
                       uld_recorded_id_codes.class5,
                       uld_recorded_id_codes.class6
                into
                       dest1, dest2, dest3, dest4, dest5, dest6,
                       class1, class2, class3, class4, class5, class6
                from   uld_recorded_id_codes
                where  uld_recorded_id_codes.uld_id_code = id_code;

                -- Find last destination
                --
                if (dest6 is not null and check_flight_dest(dest6, l_dest_list)) then
                    dest := dest6;
                    class := class6;
                elsif (dest5 is not null and check_flight_dest(dest5, l_dest_list)) then
                    dest := dest5;
                    class := class5;
                elsif (dest4 is not null and check_flight_dest(dest4, l_dest_list)) then
                    dest := dest4;
                    class := class4;
                elsif (dest3 is not null and check_flight_dest(dest3, l_dest_list)) then
                    dest := dest3;
                    class := class3;
                elsif (dest2 is not null and check_flight_dest(dest2, l_dest_list)) then
                    dest := dest2;
                    class := class2;
                elsif (dest1 is not null and check_flight_dest(dest1, l_dest_list)) then
                    dest := dest1;
                    class := class1;
                else
                    dest := ac;
                    class := NULL;
                end if;
            end;
        end if;

        return (nvl(substr(class,1,1),'') || '/' || dest);
    end Get_ULD_Final_Dest;


    -- Get the transfer destination for a ULD.
    --
    -- Arguments:
    --         id        - The flight the ULD is associated with.
    --         no        - The ULD number.
    --
    -- Returns:
    --        The IATA formated string '/<Number>/<Date>/<Dest>'
    --
    function Get_ULD_Transfer_Dest(id in Flight_ID_Type,
                                   no in ulds.uld_no%TYPE,
                                   ac IN site_info.airport_code%type) return String
    is
        fno String    := NULL;
        fdate String  := NULL;
        dest String   := NULL;
        id_code ulds.uld_id_code%type;

    begin
            -- See if there is an entry in uld_recorded_id_codes for this ULD
        --
        select ulds.uld_id_code into id_code
            from ulds where ulds.uld_no = no and ulds.flight_id = id;

        if id_code is not null then

            -- Get the onward flight info from uld_recorded_id_codes
            --
            select uld_recorded_id_codes.dest1,
                   uld_recorded_id_codes.onward_flight_no1,
                   uld_recorded_id_codes.onward_flight_date1
            into   fno, fdate, dest
            from   uld_recorded_id_codes
            where  uld_recorded_id_codes.uld_id_code = id_code;

        end if;

        if (fno is not null and fdate is not null) then

          return ('/' || fno ||
                  '/' || fdate ||
                  '/' || dest);

        else

          return null;

        end if;

    end Get_ULD_Transfer_Dest;


    -- Encodes the information for a bag
    --
    -- Arguments:
    --         bag        - The bag to encode.
    --
    -- Returns:
    --        The IATA formated strings making up this bag.
    --
    function Encode_Bag(bag in Bag_Record) return Encoded_Data
    is
        has_passenger boolean := true;
        passenger pax%rowtype;
        result Encoded_Data;
        exception_allowed char;
    begin

        -- Get the passenger for this bag
        --
        declare
        begin
            select * into passenger from pax where pax.pax_id = bag.pax_id;
        exception
            when NO_DATA_FOUND then
                has_passenger := false;
        end;

            -- Encode the bag tag
        --
        result := '.N/' || bag.bag_tag || '001' || EOL;

        -- Add the weight if defined
        --
        if bag.kg_weight is not null and bag.kg_weight > 0 then
            result := result || '.W/K/1/' || trim(bag.kg_weight) || EOL;
        end if;

        -- Add the sequence number if defined
        --
        if bag.bag_sequence_no is not null then
            result := result||
                      '.Q/' ||
                      trim(to_char(bag.bag_sequence_no,'009')) ||
                      EOL;
        end if;

        -- Only do these if we have a passenger
        --
        if has_passenger then

            -- Add the onward flights
            --
            for onward in Encode_Onward_Flights_For_PAX(bag.pax_id) loop
                if onward.class is not null then
                    result := result ||
                        '.O/' || replace(onward.no,' ') ||
                        '/' || onward.odate ||
                        '/' || onward.dest ||
                        '/' || onward.class ||
                        EOL;
                else
                    result := result ||
                        '.O/' || replace(onward.no,' ') ||
                        '/' || onward.odate ||
                        '/' || onward.dest ||
                        EOL;
                end if;
            end loop;

            -- Add the passenger name
            --
            if passenger.pax_surname is not null then
                result := result ||
                          '.P/' ||
                          nvl(passenger.no_same_surname,'') ||
                          trim(passenger.pax_surname);

                if passenger.pax_full_inits is not null then
                    result := result || '/' || trim(passenger.pax_full_inits);
                end if;

                result := result || EOL;
            end if;

            -- Add the frequent flyer info.  This is stored in the database
            -- with the .Y intact!
            --
            if passenger.freq_flyer is not null then
                result := result || trim(passenger.freq_flyer) || EOL;
            end if;

        end if;

        -- Add the exception data
        --
        if bag.bag_exception is not null then
            exception_allowed := 'Y';
            if (bag.bag_exception = 'MAIL') then
                select NVL(csc.config_value, 'N')
                into   exception_allowed
                from   flight f,
                       pax p,
                       carrier_site_config csc
                where  f.flight_id = p.flight_id
                  and  csc.site_id(+) = f.site_id
                  and  csc.alpha_code(+) = substr(f.flight_no, 1, 3)
                  and  csc.config_item(+) = 'ALLOW_MAIL_IN_BMM'
                  and  p.pax_id = bag.pax_id;
            end if;

            if (exception_allowed = 'Y') then
                result := result || '.E/' || trim(bag.bag_exception) || EOL;
            end if;
        end if;

        -- Add the security clearance
        --
        if bag.security_clearance_status = 'C' then
            result := result || '.X/CLR' || EOL;
        elsif bag.security_clearance_status = 'N' then
            result := result || '.X/SEL' || EOL;
        end if;

        return result;

    end Encode_Bag;


    --
    -- ********** Decode Routines
    --
    -- All these routines take the following arguments:
    --         fields        - The fields making up the line.
    --         state        - The state of the current BMM message.
    --

    -- Decode a .I line
    --
    procedure Decode_I_BMM(fields in                String_List,
                           state  in out nocopy        Message_State)
    is
        num number;
        prevmsg Previous_Message_Cursor%rowtype;
        l_full_version char(5) := NULL;
    begin

        -- This may be a repeated .I due to partitioning
        --
        if state.inbound.id is not null then

            -- This is not the primary .I - probably due to partitioning
            -- Check that the details are the same as before
            IF (state.inbound.fltno <> normalise_flight_no(fields(2))
              OR state.inbound.fltdate <> fields(3)) THEN
                 Decode_BMM_Error(state.msg_id, 'Subsequent .I contains different information: ' || fields(2) || ' - ' || fields(3),
                                                 state.airport_code);
                 raise BMM_Exception;
            END IF;
            return;
        end if;

        state.inbound.id := null;
        state.inbound.fltno := normalise_flight_no(fields(2));

		-- Check if flight and associated number are valid
		if ( is_object_valid (state.inbound.fltno, 'FLIGHT') = FALSE )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid Flight in .I field <' || state.inbound.fltno || '>', state.airport_code);
            raise BMM_Exception;
		end if;

        state.inbound.fltdate := fields(3);

		-- Check date is valid and has the correct format 'DDMON'
		if ( is_object_valid (state.inbound.fltdate, 'DATE') = FALSE )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid Date in .I field <' || state.inbound.fltdate || '>', state.airport_code);
            raise BMM_Exception;
		end if;

        state.inbound.airport := fields(4);

        -- need to build up the version for getting the correct message
        l_full_version := state.version||state.baggage_source||state.airport_code;

        -- Have we processed a similar message before?
        --

        begin
            open Previous_Message_Cursor(l_full_version,
                                         state.inbound.fltno,
                                         state.inbound.fltdate,
                                         state.msg_id);

            fetch Previous_Message_Cursor into prevmsg;
            if (Previous_Message_Cursor%FOUND) then

                -- This is the flight ID -- abort if it's null.
                --
                if prevmsg.flight_id is not null then

                    -- The previous message's flight ID is the one to use for this
                    -- current message.
                    --
                    state.inbound.id := prevmsg.flight_id;

                    -- Unload any applicable ULDs from the previous message
                    --
                    for untrans_uld in Find_Untransfer_ULD_Cursor
                                                    (prevmsg.message_id) loop
                        declare
                            retval number := 0;
                        begin
                            Untransfer_ULD(state.inbound.id,
                                           untrans_uld.result_uld_flight_id,
                                           untrans_uld.result_uld_no,
                                           multi_site.get_auto_user(state.airport_code),
                                           multi_site.get_auto_location(state.airport_code),
                                           retval);

                            if retval <> 0 then
                                    Decode_BMM_Error(state.msg_id,
                                                 'Failed to untransfer ULD ' || untrans_uld.result_uld_no,
                                                 state.airport_code);
                                raise BMM_Exception;
                            end if;
                        end;
                    end loop;

                    -- Call unthrough for an applicable flight.
                    --
                    declare
                        outbound_id flight.next_leg_id%type;
                    begin

                        outbound_id := inbound_flight_utils.match_inbound_outbound_flts(state.inbound.id);

                        if (NVL(outbound_id, 0) > 0) then
                            Unthrough_Flight(state.inbound.id,outbound_id);
                        end if;

                    end;
                 end if;
                    -- Reverse any Inbound records created by previous BMM
                    --
                    UPDATE inbound_bag ib
                    SET    ib.result_bag_status = 'REVERSED'
                    WHERE  ib.result_bag_status IN ('CREATED', 'AMENDED', 'UNPROCESSED')
                      AND  ib.inbound_pax_id IN (SELECT ip.inbound_pax_id
                                                 FROM   inbound_pax ip
                                                 WHERE  ip.inbound_flight_id = state.inbound.id);

                    UPDATE inbound_pax ip
                    SET    ip.result_pax_status = 'REVERSED'
                    WHERE  ip.result_pax_status IN ('CREATED', 'AMENDED', 'UNPROCESSED')
                      AND  ip.inbound_flight_id = state.inbound.id;

                    UPDATE inbound_uld iu
                    SET    iu.result_uld_status = 'REVERSED'
                    WHERE  iu.result_uld_status IN ('CREATED', 'AMENDED', 'UNPROCESSED')
                      AND  iu.inbound_flight_id = state.inbound.id;



                update messages set processed_state = 'R' where current of Previous_Message_Cursor;

            end if;

            close Previous_Message_Cursor;

        end;

        -- If we didn't find previous messages, try and locate the flight if
        -- it already exists. If not (we get NO_DATA_FOUND) then create it.
        --
        if state.inbound.id is null then
            begin
                select f.flight_id into state.inbound.id
                    from flight_inbound_v1 f
                    where f.flight_no = state.inbound.fltno and
                          f.flight_date = state.inbound.fltdate and
                          f.site_id = state.site_id;
            exception
                when NO_DATA_FOUND then
                    declare
                        PROCESS constant system_cfg.process_name%type
                                                                := 'DUMMY';

                        dcsid system_cfg.dcsid%type;
                        unit system_cfg.unit_name%type;
                        subunit system_cfg.subunit_name%type;
                        depsecs String;
                    begin

                        -- Select data for dummy source
                        --
                        select dcsid,
                               unit_name,
                               subunit_name
                        into   dcsid,
                               unit,
                               subunit
                        from   system_cfg
                        where  process_name = PROCESS;

                        -- Create a dummy departure time
                        --
                        depsecs := to_char(inbound_flight_utils.flt_date_to_date(state.inbound.fltdate), 'DD-MON-YYYY "00:00"');

                        -- Add the flight
                        --
                        Add_Flight(state.inbound.fltno,
                                   state.inbound.fltdate,
                                   state.airport_code,        -- Depcode
                                   '-',
                                   'IB',
                                   PROCESS,
                                   unit,
                                   dcsid,
                                   subunit,
                                   depsecs,
                                   null,        -- Dcsfno
                                   state.inbound.id,
                                   null);        -- BVIS Subheader
                     end;
                   when others then
                         raise;
                   end;

        end if;

        -- Update the information in the message table
        --
        update messages
        set    messages.version = state.msgref,
               messages.flight_number = state.inbound.fltno,
               messages.flight_date = state.inbound.fltdate,
               messages.flight_id = state.inbound.id
        where  messages.message_id = state.msg_id;
        -- Check the flight has not been populated using the Flight
        -- Departed Processing mechanism.
        --
        select count(*)
        into   num
        from   inbound_uld
        where  inbound_uld.inbound_flight_id = state.inbound.id and
               inbound_uld.source_uld_no is not null;

        if num > 0 then
            Decode_BMM_Error(state.msg_id,
                             'Flight ID ' || state.inbound.id || ' has been Flight Departure Processed',
                             state.airport_code);
            raise BMM_Exception;
        end if;

    end;


    -- Decode a .U line
    --
    procedure Decode_U_BMM(fields in                String_List,
                           state  in out nocopy        Message_State)
    is
        uld_number      inbound_uld.uld_number%type;
        uld_type        inbound_uld.uld_type%type;
        uld_category    inbound_uld.uld_category%type;
        uld_class       inbound_uld_dests.class_list%type;
        uld_position    inbound_uld.uld_position%type;
        uld_dest        inbound_uld_dests.destination%type;
        onward_dest     inbound_uld_dests.destination%type;
        onward_fno      inbound_uld_dests.onward_flight_no%type;
        onward_fdate    inbound_uld_dests.onward_flight_date%type;
        l_identity      CHAR(1);
    begin
        -- Check appropriate preceding data
        --
        if state.inbound.id is null then
            Decode_BMM_Error(state.msg_id,'Bad ' || fields(1) || ' ordering', state.airport_code);
            raise BMM_Exception;
        end if;

        uld_number      := nvl(Get_Optional(2,fields),'*');
        uld_position    := Get_Optional(3,fields);

		-- Check bag type is valid
    l_identity := NVL(Get_Optional(4,fields), 'X');
		if ( is_object_valid (l_identity, 'BAG_TYPE') = FALSE )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid Bag Type .U field <' || l_identity || '>', state.airport_code);
            raise BMM_Exception;
		end if;

        -- Use the identity ( i.e. Type of Baggage to derive the uld_category and uld_type
        derive_bmm_identity ( p_identity     => l_identity
                             ,p_uld_type     => uld_type
                             ,p_uld_category => uld_category);


        uld_class       := NVL(map_class(extract_carrier(state.inbound.fltno), Get_Optional(5,fields)), '*');

        uld_dest        := Get_Optional(6,fields);

		-- Check destination irport code is valid
		if ( multi_site.get_site_id (uld_dest) = -502 )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid Airport Destination <' || uld_dest || '>', state.airport_code);
            raise BMM_Exception;
		end if;

        onward_fno      := Get_Optional(8,fields);

		-- Check flight and associated flight number have the correct format.
		if ( onward_fno IS NOT NULL AND is_object_valid (onward_fno, 'FLIGHT') = FALSE )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid Flight in .U field <' || onward_fno|| '>', state.airport_code);
            raise BMM_Exception;
		end if;

		onward_fdate    := Get_Optional(9,fields);

		-- Check date is valid and has the correct format 'DDMON'
		if ( onward_fdate IS NOT NULL AND is_object_valid (onward_fdate, 'DATE') = FALSE )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid Date in .U field <' || onward_fdate || '>', state.airport_code);
            raise BMM_Exception;
		end if;

        onward_dest     := Get_Optional(10,fields);

		-- Check destination of transfer airport code of the container is valid
		if ( onward_dest IS NOT NULL AND multi_site.get_site_id (onward_dest) = -502 )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid Destination Transfer Airport Code of Container <' || onward_dest || '>', state.airport_code);
            raise BMM_Exception;
		end if;

        -- See if this ULD is the same as the last one (due to partitioning)
        --
        if state.current_uld is not null then
            declare
                    curno inbound_uld.uld_number%type;
            begin
                select inbound_uld.uld_number
                into   curno
                from   inbound_uld
                where  inbound_uld.inbound_uld_id = state.current_uld;

                if curno = uld_number then
                    return;
                end if;
            exception
                when no_data_found then
                    null;
            end;
        end if;


        -- Create the incoming ULD
        --
        insert into inbound_uld
            (
                inbound_uld.inbound_flight_id,
                inbound_uld.source_message_id,
                inbound_uld.inbound_uld_status,
                inbound_uld.uld_number,
                inbound_uld.uld_type,
                inbound_uld.uld_category,
                inbound_uld.uld_position
            )
        values
            (
                state.inbound.id,
                state.msg_id,
                'TERMINATING',
                uld_number,
                uld_type,
                uld_category,
                uld_position
            ) return inbound_uld.inbound_uld_id into state.current_uld;

        -- Add the destination for the ULD
        --
        if uld_dest is not null and uld_class is not null then
            Add_ULD_Dest(state.current_uld,
                         state.inbound.fltno,
                         state.inbound.fltdate,
                         uld_dest,
                         uld_class);
        end if;

        -- Add the onward destination
        --
        if onward_fno is not null and
                onward_fdate is not null and onward_dest is not null then
            Add_ULD_Dest(state.current_uld,
                         onward_fno,
                         onward_fdate,
                         onward_dest,
                         uld_class);
        end if;
    end;


    -- Decode a .F line
    --
    procedure Decode_F_BMM(fields in                String_List,
                           state  in out nocopy     Message_State)
    is
            i number;
    begin
        -- Buffer the onward information
        -- A .F record will be the first onward flight
        --
        state.onward_fno.DELETE;
        state.onward_fdate.DELETE;
        state.onward_dest.DELETE;
        state.onward_class.DELETE;
        i := 1;

        state.onward_fno(i)        := normalise_flight_no(fields(2));

		-- Check flight number has a valid format
		if ( is_object_valid (state.onward_fno(i), 'FLIGHT') = FALSE )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid Flight Number in .F field <' || state.onward_fno(i) || '>', state.airport_code);
            raise BMM_Exception;
		end if;

		state.onward_fdate(i)      := fields(3);

		-- Check date is valid and has the correct format 'DDMON'
		if ( is_object_valid (state.onward_fdate(i), 'DATE') = FALSE )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid Date in .F field <' || state.onward_fdate(i) || '>', state.airport_code);
            raise BMM_Exception;
		end if;

        state.onward_dest(i)       := fields(4);

         -- assign the .F destination to the pax destination
         -- this would override any default current airport which
         -- had been assigned to the pax_destination.

        if state.onward_dest(1) is not null then
           state.pax_destination      := state.onward_dest(1);
		    -- Check destination is valid
		    if ( multi_site.get_site_id (state.pax_destination) = -502 )
		    then
		            Decode_BMM_Error(state.msg_id,'Invalid .F Airport Destination <' || state.pax_destination || '>', state.airport_code);
                    raise BMM_Exception;
		    end if;
        end if;

        state.onward_class(i)      := map_class(extract_carrier(state.onward_fno(i)), Get_Optional(5,fields));
    end;


    -- Decode a .N line
    --
    procedure Decode_N_BMM(fields in                String_List,
                           state  in out nocopy     Message_State)
    is
        tag inbound_bag.bag_tag%type;
        tagnum number;
        num number;
        offset number;
        l_hold inbound_uld.uld_number%TYPE;
    begin
        -- Check appropriate preceding data
        --
        if state.inbound.id is null then
            Decode_BMM_Error(state.msg_id,'Bad ' || fields(1) || ' ordering', state.airport_code);
            raise BMM_Exception;
        end if;

        -- Extract tags
        --
        tag := substr(fields(2),1,10);
        num := substr(fields(2),11,3);

        -- If we've no current PAX, create one.  This PAX may eventually be
        -- deleted if we discover that this data is indeed for a previously
        -- defined PAX.
        --
        if state.current_pax is null then
            insert into inbound_pax
                (
                    inbound_pax.inbound_flight_id,
                    inbound_pax.source_message_id,
                    inbound_pax.result_pax_status
                )
            values
                (
                    state.inbound.id,
                    state.msg_id,
                    'REVERSED'
                ) return inbound_pax.inbound_pax_id
                  into   state.current_pax;
        end if;

        -- Create Default Hold (if necessary)
        --
        if state.current_uld is null then

            l_hold := trim(config.get_value('UNCONTAINED_BMM_BAGS_HOLD'));
            insert into inbound_uld
                (
                    inbound_flight_id,
                    source_message_id,
                    inbound_uld_status,
                    uld_number,
                    uld_type,
                    uld_category
                )
            values
                (
                    state.inbound.id,
                    state.msg_id,
                    'TERMINATING',
                    l_hold,
                    '*',
                    '*'
                ) return inbound_uld_id
                  into   state.current_uld;

        end if;

        -- Create bag entries
        --
        offset := state.current_bags.count;

        for i in 1 .. num loop
            insert into inbound_bag
                (
                    inbound_bag.inbound_pax_id,
                    inbound_bag.source_message_id,
                    inbound_bag.inbound_uld_id,
                    inbound_bag.bag_tag
                )
            values
                (
                    state.current_pax,
                    state.msg_id,
                    state.current_uld,
                    tag
                ) return inbound_bag.inbound_bag_id
                  into   state.current_bags(offset+i);

            tagnum := to_number(substr(tag, 5));
            tagnum := mod(tagnum + 1, 1000000);
            tag    := substr(tag, 1, 4) || trim(to_char(tagnum, '000000'));
        end loop;
    end;


    -- Decode a .W line
    --
    procedure Decode_W_BMM(fields in                String_List,
                           state  in out nocopy     Message_State)
    is
            weight number;
    begin
        -- Check appropriate preceding data
        --
        if state.inbound.id is null or state.current_bags.count = 0 then
            Decode_BMM_Error(state.msg_id,'Bad ' || fields(1) || ' ordering', state.airport_code);
            raise BMM_Exception;
        end if;

        -- Check to make sure that all the required data is there
        --
        if fields(2) not in ('K', 'L') or fields(3) is null
                                        or fields(4) is null then
            return;
        end if;

        -- Get weight/count and convert to kilos if needbe.
        --
        weight := fields(4) / fields(3);

        if fields(2) = 'L' then
            weight := weight * 0.4536;
        end if;

        -- Update the bags
        --
        forall i in 1 .. state.current_bags.count
            update inbound_bag
            set    bag_weight = weight
            where  inbound_bag_id = state.current_bags(i);
    end;


    -- Decode a .Q line
    --
    procedure Decode_Q_BMM(fields in                String_List,
                           state  in out nocopy     Message_State)
    is
    begin
        -- Check appropriate preceding data
        --
        if state.current_bags is null then
            Decode_BMM_Error(state.msg_id,'Bad ' || fields(1) || ' ordering', state.airport_code);
            raise BMM_Exception;
        end if;

        -- Update the bags
        --
        forall i in 1 .. state.current_bags.count
            update inbound_bag
            set    inbound_bag.bag_load_sequence = fields(2)
            where  inbound_bag.inbound_bag_id = state.current_bags(i);
    end;


    -- Decode a .O line
    --
    procedure Decode_O_BMM(fields in                String_List,
                           state  in out nocopy     Message_State)
    is
        i number;
    begin
        -- To have an Onward Flight, the passenger MUST have an Outbound Flight (.F)
        --
        i := state.onward_fno.count + 1;
        if (i < 2) then
            Decode_BMM_Error(state.msg_id, '.O without .F is invalid', state.airport_code);
            raise BMM_Exception;
        end if;

        -- Buffer the onward information
        --
        state.onward_fno(i)        := normalise_flight_no(fields(2));

		-- Check for valid flight number format
		if ( is_object_valid (state.onward_fno(i), 'FLIGHT') = FALSE )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid Flight Number in .O field <' || state.onward_fno(i) || '>', state.airport_code);
            raise BMM_Exception;
		end if;

        state.onward_fdate(i)      := fields(3);

		-- Check date is valid and has the correct format 'DDMON'
		if ( is_object_valid (state.onward_fdate(i), 'DATE') = FALSE )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid Date in .O field <' || state.onward_fdate(i) || '>', state.airport_code);
            raise BMM_Exception;
		end if;

        state.onward_dest(i)       := fields(4);

		-- Check destination irport code is valid
		if ( multi_site.get_site_id (state.onward_dest(i)) = -502 )
		then
		    Decode_BMM_Error(state.msg_id,'Invalid .O Airport Destination <' || state.onward_dest(i) || '>', state.airport_code);
            raise BMM_Exception;
		end if;

        state.onward_class(i)      := map_class(extract_carrier(state.onward_fno(i)), Get_Optional(5,fields));

    end;


    -- Decode a .P line
    --
    procedure Decode_P_BMM(fields in                String_List,
                           state  in out nocopy     Message_State)
    is
        c char(1);
        name_start number;
    begin

        -- Check appropriate preceding data
        --
        if state.current_pax is null then
            Decode_BMM_Error(state.msg_id,'Bad ' || fields(1) || ' ordering', state.airport_code);
            raise BMM_Exception;
        end if;

        -- Extract counts
        --
        c := substr(fields(2),1,1);

        if c between '0' and '9' then
            state.pax_samename := c;

            c := substr(fields(2),2,1);

            if c between '0' and '9' then
                state.pax_samename := state.pax_samename * 10 + c;
                name_start := 3;
            else
                name_start := 2;
            end if;
        else
            name_start := 1;
        end if;

        -- Extract and store names
        --

        state.pax_surname := substr(fields(2),name_start);

        if fields.count > 2 then
            state.pax_initials := substr(fields(3),1,1);
            state.pax_full_inits := fields(3);
        end if;


    end;


    -- Decode a .Y line
    --
    procedure Decode_Y_BMM(fields in                String_List,
                           state  in out nocopy     Message_State)
    is
    begin
        -- Check appropriate preceding data
        --
        if state.current_pax is null then
            Decode_BMM_Error(state.msg_id,'Bad ' || fields(1) || ' ordering', state.airport_code);
            raise BMM_Exception;
        end if;

        -- Extract info
        --
        state.pax_frequent := fields(2);
    end;


    -- Decode a .E line
    --
    procedure Decode_E_BMM(fields in                String_List,
                           state  in out nocopy     Message_State)
    is
    begin
        -- Check appropriate preceding data
        --
        if state.current_bags is null then
            Decode_BMM_Error(state.msg_id,'Bad ' || fields(1) || ' ordering', state.airport_code);
            raise BMM_Exception;
        end if;

        -- Update the bags
        --
        forall i in 1 .. state.current_bags.count
            update inbound_bag
            set    inbound_bag.bag_exception_type = fields(2)
            where  inbound_bag.inbound_bag_id = state.current_bags(i);

    end;


    -- Decode a .X line
    --
    procedure Decode_X_BMM(fields in                String_List,
                           state  in out nocopy     Message_State)
    is
        status inbound_bag.bag_security_status%type;
    begin
        -- Check appropriate preceding data
        --
        if state.current_bags is null then
            Decode_BMM_Error(state.msg_id,'Bad ' || fields(1) || ' ordering', state.airport_code);
            raise BMM_Exception;
        end if;

        if fields(2) = 'CLR' then
            status := 'C';
        elsif fields(2) = 'SEL' then
            status := 'N';
        else
            status := 'U';
        end if;

        -- Update the bags
        --
        forall i in 1 .. state.current_bags.count
            update inbound_bag
            set    inbound_bag.bag_security_status = status
            where  inbound_bag.inbound_bag_id = state.current_bags(i);
    end;

    --
    -- ******************** PUBLIC ********************
    --

    -- Decode a BMM
    --
    procedure Decode_BMM
    (
        msg_id in  messages.message_id%type,
        ok     out boolean
    )
    is

        parts_total        number;
        parts_count        number;

        msg                String_List;
        state              Message_State;
        v_line             String_List;
        start_index        number;

        last_command       String;
        l_uld_status       inbound_uld.inbound_uld_status%TYPE;
        l_pax_status       inbound_pax.inbound_pax_status%TYPE;
        l_agent_count      number;

        l_flight_number    messages.flight_number%TYPE;
        l_flight_date      messages.flight_date%TYPE;
        l_flight_id        messages.flight_id%TYPE;

        CURSOR c_ulds (p_msg_id messages.message_id%TYPE) IS
               SELECT inbound_flight_id,
                      uld_no
               FROM   inbound_uld_v1
               WHERE  source_message_id = p_msg_id
                 AND  result_uld_status IN ('UNPROCESSED', 'CREATED', 'AMENDED')
               FOR UPDATE OF inbound_uld_status;

        CURSOR c_paxes (p_msg_id messages.message_id%TYPE) IS
               SELECT inbound_flight_id,
                      inbound_pax_id
               FROM   inbound_pax
               WHERE  source_message_id = p_msg_id
                 AND  result_pax_status IN ('UNPROCESSED', 'CREATED', 'AMENDED')
               FOR UPDATE OF inbound_pax_status;
    begin

        ok := false;

        -- Update the status.  This will be rolled back on error
        --
        update messages
        set    messages.processed_state = 'P'
        where  messages.message_id = msg_id;

        -- First, validate the message is complete.
        --
        state.msg_id := msg_id;

        select messages.parts
        into   parts_total
        from   messages
        where  messages.message_id = msg_id;

        select count(*)
        into   parts_count
        from   message_parts
        where  message_parts.message_id = msg_id;

        select msg.flight_number, msg.flight_date, msg.flight_id
        into   l_flight_number, l_flight_date, l_flight_id
        from   messages msg
        where  msg.message_id = msg_id;


        if parts_count < 1 or parts_count <> parts_total then
            Decode_BMM_Error(msg_id,'Message not complete', state.airport_code);
            raise BMM_Exception;
        end if;

        -- Grab the message from the database and do a cursory structure
        -- validation.
        --
        Fetch_Message(msg_id,msg);

        -- Extract the .V line and validate it
        --
        start_index := null;

        for i in 1 .. msg.count loop
            if Tokenise_Line(msg(i),v_line) = 'V' then
                -- Validate message verison
                --
                state.version := substr(v_line(2),1,1);

                if state.version <> '1' then
                    Decode_BMM_Error(msg_id,'Unsupported version ' || state.version, state.airport_code);
                    raise BMM_Exception;
                end if;

                -- Extract baggage source and airport
                --
                state.baggage_source := substr(v_line(2),2,1);
                state.airport_code   := substr(v_line(2),3,3);

                -- Set the pax_destination to default initially to airport_code
                -- if any .F fields exist then the pax_destination should default to
                -- the .F dest when decoding the .F field.
                state.pax_destination := state.airport_code;

                select site_id
                into   state.site_id
                from   site_info
                where  airport_code = state.airport_code;

                -- Check that this airport is a supported site in this database
                --
                SELECT COUNT(*)
                INTO   l_agent_count
                FROM   agents
                WHERE  site_id = state.site_id;

                IF (l_agent_count = 0) THEN
                    Decode_BMM_Error(msg_id,'Unsupported Airport: ' || state.airport_code, state.airport_code);
                    raise BMM_Exception;
                END IF;

                -- Extract the reference check (simply all the above)
                --
                state.msgref := v_line(2);

                -- We've now found the start of the message proper
                --
                start_index := i+1;
                exit;
            end if;
        end loop;

        if start_index is null then
            Decode_BMM_Error(msg_id,'Message does not begin with .V', state.airport_code);
            raise BMM_Exception;
        end if;

        -- We're now entering the main loop where we syphon off the rest
        -- and process the results.
        --
        for i in start_index .. msg.count loop
            declare
                line String_List;
                command String;
            begin
                command := Tokenise_Line(msg(i),line);

                if command = 'I' then                -- Inbound flight

                    Decode_I_BMM(line,state);

                elsif command = 'U' then        -- Loading Data

                    Decode_U_BMM(line,state);

                elsif command = 'F' then        -- Outbound flight

                    -- An F starts a new PAX
                    --
                    Flush_State(state);
                    Reset_For_New_PAX(state);
                    Decode_F_BMM(line,state);

                elsif command = 'N' then        -- Bag tag

                    -- Check is this is a new passenger
                    --
                    if last_command <> 'F' and last_command <> 'H' then
                        Flush_State(state);
                        Reset_For_New_PAX(state);
                    end if;

                    Decode_N_BMM(line,state);

                elsif command = 'W' then        -- Baggage weight

                    Decode_W_BMM(line,state);

                elsif command = 'Q' then        -- Load sequence number

                    Decode_Q_BMM(line,state);

                elsif command = 'O' then        -- Onward flight info

                    Decode_O_BMM(line,state);

                elsif command = 'P' then        -- PAX name

                    Decode_P_BMM(line,state);

                elsif command = 'Y' then        -- PAX Frequent flyer info

                    Decode_Y_BMM(line,state);

                elsif command = 'E' then        -- Baggage exception

                    Decode_E_BMM(line,state);

                elsif command = 'X' then        -- Security status

                    Decode_X_BMM(line,state);

                end if;

                last_command := command;
            end;
        end loop;

        -- Finish off the last passenger (if any)
        --
        Flush_State(state);

        -- Determine the object type for each created object
        FOR r_uld IN c_ulds(state.msg_id) LOOP

          l_uld_status := inbound_flight_utils.get_inbound_status('ULD', r_uld.inbound_flight_id, NULL, NULL, r_uld.uld_no);

          UPDATE inbound_uld
          SET    inbound_uld_status = l_uld_status
          WHERE CURRENT OF c_ulds;

        END LOOP;

        FOR r_pax IN c_paxes(state.msg_id) LOOP

          l_pax_status := inbound_flight_utils.get_inbound_status('PAX', r_pax.inbound_flight_id, r_pax.inbound_pax_id, NULL, NULL);

          UPDATE inbound_pax
          SET    inbound_pax_status = l_pax_status
          WHERE CURRENT OF c_paxes;

        END LOOP;

        -- Call through flight processing
        --
        declare
            out_flight flight.flight_id%TYPE;
        begin
            out_flight := inbound_flight_utils.match_inbound_outbound_flts(state.inbound.id);
            IF (NVL(out_flight, 0) > 0) THEN
                Auto_Copy_Through(state.inbound.id, out_flight);
            END IF;
        end;

         --
         -- Setting the outbound bag for security screening or not
         -- when the BSM is received prior to the incoming BMM
         --
         DECLARE
             -- For Transfer Flight processing
             CURSOR c_trans_bags (p_in_flight_no   messages.flight_number%TYPE
                                 ,p_in_flight_date messages.flight_date%TYPE
                                 ,p_in_flight_id   messages.flight_id%TYPE) IS
             SELECT p.*
             FROM   pax p, flight f, flight f2
             WHERE  p.inbound_flight_no = p_in_flight_no    -- from .I
             AND    p.inbound_date      = p_in_flight_date  -- from .I
             AND    f.flight_id         = p.flight_id
             AND    f2.flight_id        = p_in_flight_id    -- inbound_flight_id
             AND    nvl(f2.next_leg_id,-1) <> f.flight_id;

             -- Other declarations
             type bag_ref IS REF CURSOR ;
             through_bag_csr      bag_ref;
             check_bag_match_csr  bag_ref;
             t_bag_id             bag_id_type;
             t_bag_tag            bag_tag_type;
             t_bag_tag3           bag_tag3_type;
             t_bag_tag2b          bag_tag2b_type;
             pax_structure        pm_pax_record;
             l_pax_id             pax.pax_id%TYPE := 0;

         BEGIN
             -- Deal with through flight processing scenario first
             OPEN through_bag_csr FOR

             -- Find all the bags which exist as created via the BSM for the specific .I
             -- flight which have yet to be created or exist on the incoming BMM
             SELECT bag.bag_id
             FROM   bag, pax, flight
             WHERE  pax.inbound_flight_no = l_flight_number
             AND    flight.flight_id      = state.inbound.id --i/b flight_id
             AND    pax.flight_id         = flight.next_leg_id
             AND    pax.inbound_date      = l_flight_date
             AND    pax.pax_id            = bag.pax_id
             AND    bag.bag_id NOT IN ( SELECT ib.result_bag_id
                                        FROM   inbound_pax ip, inbound_bag ib
                                        WHERE  ip.inbound_flight_id = state.inbound.id
                                        AND    ip.inbound_pax_id    = ib.inbound_pax_id
                                        AND    ip.result_pax_status <> 'REVERSED'
                                        AND    ib.result_bag_status <> 'REVERSED'
                                        AND    ip.result_pax_id = pax.pax_id);
             --
             FETCH through_bag_csr BULK COLLECT INTO
                   t_bag_id
             LIMIT MAX_BAG_ROWS;
             --
             CLOSE through_bag_csr;

             -- determine which details to use
             FORALL i in 1..t_bag_id.COUNT
                   UPDATE bag set bag.security_clearance_status = 'N'
                   WHERE  bag.bag_id = t_bag_id(i);


            -- Now deal with the transfer flight processing scenario

            FOR r_pax IN c_trans_bags(l_flight_number, l_flight_date, state.inbound.id) LOOP


            -- load the outbound paxes details into the pax_structure
            pax_match.load_pax_structure( pax_structure
                                         ,r_pax.flight_id  --outbound flight id
                                         ,r_pax.pax_id);   --outbound pax_id

            -- compare the outbound pax with that on the inbound flight to see
            -- whether there is a match or not. The returned pax_id is the inbound_pax_id
            l_pax_id := pax_match.perform_match(config.get_value('BMM_INBOUND_PAX_MATCHING'),
                                                pax_structure,
                                                state.inbound.id);


            -- No pax match then need to update ALL the bsm bags on the outbound flight
            -- to need security screaning
            IF l_pax_id = 0 THEN
               UPDATE bag
               SET    security_clearance_status = 'N'
               WHERE  bag.pax_id = r_pax.pax_id;
            ELSE
            -- Found PAX match, need to check the bags on the outbound pax/outbound flight having
            -- the same .I flight details as that of the incoming BMM.
            -- If there are bags different on the outbound pax compared to the incoming BMM
            -- then all these bags need to be screened. The comparison is done using
            -- the bag_tags of the outbound pax against the incoming BMM pax.

               OPEN check_bag_match_csr FOR
                    SELECT bag.bag_tag, bag.bag_id, bag.bag_tag3, bag.bag_tag2b
                    FROM   bag, pax
                    WHERE  pax.pax_id            = bag.pax_id
                    AND    pax.flight_id         = r_pax.flight_id
                    AND    pax.pax_id            = r_pax.pax_id
                    AND    pax.inbound_flight_no = r_pax.inbound_flight_no
                    AND    pax.inbound_date      = r_pax.inbound_date
                    AND    (bag.bag_tag3,bag.bag_tag2b) NOT IN
                           ( SELECT NVL(ib.bag_tag3, TO_NUMBER(SUBSTR(ib.bag_tag,-6)))
                                   ,NVL(ib.bag_tag2b, SUBSTR(ib.bag_tag,-9,3))
                             FROM   inbound_bag_v1 ib,
                                    inbound_pax_v1 ip
                             WHERE  ib.inbound_pax_id    = ip.inbound_pax_id
                             AND    ip.inbound_flight_id = state.inbound.id
                             AND    ib.result_bag_status <> 'REVERSED'
                             AND    ip.result_pax_status <> 'REVERSED'
                             AND    ip.inbound_pax_id    = l_pax_id);

                    --
                    FETCH check_bag_match_csr BULK COLLECT INTO
                          t_bag_tag, t_bag_id, t_bag_tag3, t_bag_tag2b

                    LIMIT MAX_BAG_ROWS;
                    --
               CLOSE check_bag_match_csr;

               -- for any bags which don't exist on the incoming BMM
               -- need to set these for screening.
               IF t_bag_tag.COUNT > 0 THEN
                  FORALL i in 1..t_bag_tag.COUNT
                         UPDATE bag set bag.security_clearance_status = 'N'
                         WHERE  bag.bag_id = t_bag_id(i);
               END IF;
            END IF; -- end of pax match
            END LOOP;
         END;

        -- If we get to here, all is well.
        --
        ok := true;

    exception

        when BMM_Exception then
            null;

        when others then
            if length(SQLERRM) < 200 then
                Decode_BMM_Error(msg_id,'Exception in Decode_BMM -- ' || SUBSTR(SQLERRM,1,200), state.airport_code);
            else
                Decode_BMM_Error(msg_id,'Exception in Decode_BMM -- SQLCODE=' || SQLCODE, state.airport_code);
            end if;

    end Decode_BMM;


	FUNCTION is_object_valid ( p_validation_object IN  CHAR,
			         p_object_type       IN  VARCHAR2
                         ) RETURN BOOLEAN
    IS
        -- Local Variables

        l_object_type   VARCHAR2(20);
        l_return	    BOOLEAN := FALSE;
        l_temp_date     DATE;
        l_temp_carrier  VARCHAR2(20);
        l_flight_number CHAR(8);


    BEGIN

       -- Initialise
       l_object_type := TRIM(UPPER(p_object_type));

       -- Validate Parameters
       IF (l_object_type NOT IN ('DATE','BAG_TYPE','FLIGHT'))
       THEN
           raise_application_error(-20000,'FUNCTION: is_object_valid ERROR: invalid object type'
                                  ||' REASON: p_object_type must be either DATE, BAG_TYPE or FLIGHT');
       END IF;

       -- Check that the date is the correct format
       IF (l_object_type = 'DATE')
       THEN
           BEGIN
               SELECT TO_DATE (p_validation_object,'DDMON')
			   INTO   l_temp_date
			   FROM   dual;

               l_return := TRUE;

           EXCEPTION
		       WHEN OTHERS
			   THEN
                   l_return := FALSE;
	       END;
       END IF;

       -- Check that the bag tag type is valid
       IF (l_object_type = 'BAG_TYPE')
       THEN
           IF ( p_validation_object IN ('M','T','S','X'))
	       THEN
               l_return := TRUE;
	       ELSE
               l_return := FALSE;
	       END IF;
       END IF;

       -- Check flight number consist of a valid airport code numeric flight number

       IF (l_object_type = 'FLIGHT')
       THEN
   	       l_flight_number := normalise_flight_no (p_validation_object);
	       BEGIN
	           SELECT alpha_code||TO_NUMBER(substr(l_flight_number,4,3))
			   INTO   l_temp_carrier
			   FROM   carrier_lookup
			   WHERE  UPPER(carrier_lookup.alpha_code) = UPPER(substr (l_flight_number,1,3));

			   l_return := TRUE;

           EXCEPTION
		       WHEN OTHERS
			   THEN
                   l_return := FALSE;
	       END;

       END IF;

       RETURN l_return;

    END is_object_valid;

    -- Creates an entry in the MESSAGES_TRACE table.
    --
    procedure Add_Message_Trace
    (
        p_id      in        messages.message_id%type,
        p_part_id in        message_parts.message_part_id%type,
        p_action  in        messages_trace.trace_action%type,
        p_dest    in        messages_trace.message_destination%type,
        p_route   in        messages_trace.message_route_id%type,
        p_user_id in    tagtrak_users.user_id%type
    )
    is
    begin
        insert into messages_trace
            (
                messages_trace.message_id,
                messages_trace.message_part_id,
                messages_trace.creator_id,
                messages_trace.creator_location_code,
                messages_trace.creator_agent_code,
                messages_trace.method,
                messages_trace.trace_action,
                messages_trace.message_destination,
                messages_trace.message_route_id
            )
            ( select
                p_id,
                p_part_id,
                tu.user_id,
                multi_site.get_auto_location(si.airport_code),
                a.agent_code,
                'A',
                p_action,
                p_dest,
                p_route
             from
                tagtrak_users tu,
                agents a,
                site_info si
             where
                tu.user_id = p_user_id
             and
                tu.agent_code = a.agent_code
             and
                a.site_id = si.site_id
            );
    end Add_Message_Trace;


    -- Encodes an outgoing BMM message
    --
    -- Arguments:
    --         id        - Flight ID
    --
    function Build_BMM
    (
        id  in  flight.flight_id%type
    ) return messages.message_id%type
    is
        msg_id          messages.message_id%type;
        data            Encoded_List;
        loc             site_info.airport_code%type;
        iloc            site_info.airport_code%type;
        l_identity        VARCHAR2(1);
        l_transfer_dest varchar2(100);
    begin

	-- CA/02296 - Prevent an empty BMM being generated
	--
	declare
	    loaded_count number;
	begin
	    select count(*) into loaded_count
	    	from bag
		where bag.loaded_status = 'Y' and
		      bag.pax_id in (select pax_id from pax where
		      			pax.flight_id = id);

	    if loaded_count = 0 then
	    	return null;
	    end if;
	end;

        -- Construct the base .V line.  We DO NOT terminate this with EOL as
        -- the storage routines put part numbers on this.
        --
        select fd.flight_destination
        into   loc
        from   flight_dests fd
        where  fd.flight_id = id
          and  fd.dest_order = 1;

        data(data.count + 1) := '.V/1T' || loc;

        -- Construct the .I line
        --
        declare
            orig_fno flight.flight_no%type;
            fno String;
            fdate flight.flight_date%type;
            iloc site_info.airport_code%type;
        begin
            select replace(flight.flight_no,' '),
                   flight.flight_date,
                   flight.flight_no
            into   fno,
                   fdate,
                   orig_fno
            from   flight
            where  flight.flight_id = id;

			select site_info.airport_code
            into   iloc
            from   site_info, flight
            where  site_info.site_id = flight.site_id
              and  flight.flight_id = id;

			-- Create a message for this (we need the flight no and date)
            --
            insert into messages
                (
                    messages.message_type,
                    messages.message_direction,
                    messages.flight_id,
                    messages.flight_number,
                    messages.flight_date,
                    messages.processed_state,
                    messages.version,
                    messages.originating_airport
                )
            values
                (
                    'BMM',
                    'O',
                    id,
                    orig_fno,
                    fdate,
                    'U',
                    '1T' || loc,
                    iloc
                ) return messages.message_id
                  into   msg_id;


			data(data.count + 1) :=
                    '.I/' || fno || '/' || fdate || '/' || iloc || EOL;
        end;

        -- Process the bulkhead baggage with no onward flights
        --
        for uld in Encode_Find_Bulk_ULDs_Cursor(id) loop

            for bag in Encode_Bags_Without_Onward(uld.uld_no,id, loc) loop
                data(data.count + 1) := Encode_Bag(bag);
            end loop;

        end loop;

		-- Process the bulkhead baggage with onward flights
        --
        for uld in Encode_Find_Bulk_ULDs_Cursor(id) loop

            for onward in Encode_Find_Onward_Flights(uld.uld_no,id, loc) loop

                -- Add the .F line
                --
                declare
                    fno String := replace(onward.no,' ');
                begin
                    if onward.class is not null then
                        data(data.count + 1) :=
                            '.F/' || fno ||
                            '/' || replace(onward.odate,' ') ||
                            '/' || onward.dest ||
                            '/' || onward.class ||
                            EOL;
                    else
                        data(data.count + 1) :=
                            '.F/' || fno ||
                            '/' || onward.odate ||
                            '/' || onward.dest ||
                            EOL;
                    end if;
                end;

                -- Add the bags matching this
                --

				for bag in Encode_Find_Bags_For_Onward(onward.no,
                                                       onward.odate,
                                                       onward.class,
                                                       id,
                                                       uld.uld_no,
                                                       loc) loop
                    data(data.count + 1) := Encode_Bag(bag);
                end loop;

            end loop;
        end loop;


        -- Process the 'real' ULDs
        --

		for uld in Encode_Find_ULDs_Cursor(id) loop
            declare
                u_line String := '.U';
            begin
                -- The ID
                --
                u_line := u_line || '/' || trim(uld.uld_no);

                -- The compartment
                --
                u_line := u_line || '/' || nvl(trim(uld.uld_hold_no),'');


                -- derive the .U Type of baggage in Container/Location
                l_identity :=  get_bmm_identity ( p_uld_type       => uld.uld_type
                                                 ,p_flight_id      => uld.flight_id
                                                 ,p_uld_no         => uld.uld_no
                                                 ,p_next_flt_dest  => loc -- first/next flight dest
                                                 ,p_uld_dest_count => count_uld_dests(uld.uld_no,id));

                -- set the correctly mapped type of baggage in container/location
                u_line := u_line || '/' || l_identity;

                -- Class
                -- Note: The BMM standard allows for only a single class, which is
                --       insufficient for UltraTrak, so don't send anything
                u_line := u_line || '/';

                -- The final destination
                --
                u_line := u_line || Get_ULD_Final_Dest(id,uld.uld_no, iloc);

                -- The transfer location
                --
                if l_identity = 'T' then
                    l_transfer_dest := Get_ULD_Transfer_Dest(id,uld.uld_no, iloc);
                    if (l_transfer_dest is not null) then
                        -- Add NULL entry for Sealed Container Indicator
                        u_line := u_line || '/';
                        u_line := u_line || l_transfer_dest;
                    end if;
                end if;

                -- Terminate line
                --
                u_line := u_line || EOL;

                -- Add the .U line to the data
                --
                data(data.count + 1) := u_line;

                -- Get bags with no onward flight
                --

				for bag in Encode_Bags_Without_Onward(uld.uld_no,id, loc) loop
                    data(data.count + 1) := Encode_Bag(bag);
                end loop;


				-- Get bags with onward flights
                --
                for onward in Encode_Find_Onward_Flights(uld.uld_no,id, loc) loop

                    -- Add the .F line
                    --

					declare
                        fno String := replace(onward.no,' ');
                    begin
                        if onward.class is not null then
                            data(data.count + 1) :=
                                '.F/' || fno ||
                                '/' || onward.odate ||
                                '/' || onward.dest ||
                                '/' || onward.class ||
                                EOL;
                        else
                            data(data.count + 1) :=
                                '.F/' || fno ||
                                '/' || onward.odate ||
                                '/' || onward.dest ||
                                EOL;
                        end if;
                    end;

                    -- Add the bags matching this
                    --

					for bag in Encode_Find_Bags_For_Onward(onward.no,
                                                           onward.odate,
                                                           onward.class,
                                                           id,
                                                           uld.uld_no,
                                                           loc) loop

						data(data.count + 1) := Encode_Bag(bag);
                    end loop;

                end loop;

            end;
        end loop;

        -- Store the BMM in the database
        --

		Store_Encoded_Message(msg_id,data);

		-- If we get to here, all is well.
        --
        return msg_id;

    exception

        when BMM_Exception then
            return NULL;

        when others then
            if length(SQLERRM) < 200 then
                Encode_BMM_Error('Exception in Build_BMM -- ' || SUBSTR(SQLERRM,1,200));
            else
                Encode_BMM_Error('Exception in Build_BMM -- SQLCODE=' ||
                                                                SQLCODE);
            end if;
            return NULL;

    end Build_BMM;

    --
    -- ****** Imported from previous PROCESS_MESSAGE
    --

    --
    -- split_message_line
    --
    PROCEDURE split_message_line
    (
      p_line      IN     VARCHAR2,
      p_fields    IN OUT t_field_list,
      p_element      OUT VARCHAR2
    ) IS

      l_line          varchar2(64);
      l_field_num     NUMBER;
      l_found_slash   BOOLEAN;
      l_current_slash NUMBER;
      l_next_slash    NUMBER;

    BEGIN

      -- Initialise
      l_line    := TRIM(p_line);
      p_element := NULL;
      p_fields.DELETE;

      -- Verify a valid line
      IF (l_line IS NULL                -- Must contain something!
       OR SUBSTR(l_line, 1, 1) <> '.'   -- First character is a period
       OR LENGTH(l_line) < 3            -- Must be at least 3 characters long
       OR INSTR(l_line, '/') = 0 ) THEN -- Must contain at least one forward slash

        -- Invalid message line
        RETURN;

      END IF;

      -- Extract the element
      p_element := SUBSTR(l_line, 2, 1);

      -- Extract the fields
      l_field_num     := 1;
      l_found_slash   := TRUE;
      l_current_slash := INSTR(l_line, '/');
      WHILE (l_found_slash = TRUE) LOOP

        -- Does another slash exist?
        l_next_slash := INSTR(l_line, '/', 1, l_field_num + 1);
        IF (l_next_slash = 0) THEN

          -- Extract the data to the end of the line
          p_fields(l_field_num) := SUBSTR(l_line, l_current_slash + 1, LENGTH(l_line) - l_current_slash);

          -- No more slashes - finish
          l_found_slash := FALSE;

        ELSE

          -- Extract the data between the slashes
          p_fields(l_field_num) := SUBSTR(l_line, l_current_slash + 1, l_next_slash - l_current_slash - 1);

          -- Move to the next field
          l_field_num := l_field_num + 1;
          l_current_slash := l_next_slash;

        END IF;

      END LOOP;

    END split_message_line;


    --
    -- find_record
    --
    FUNCTION find_record
    (
      p_part1      IN     VARCHAR2,
      p_part2      IN     VARCHAR2,
      p_element    IN     VARCHAR2,
      p_occurrence IN     NUMBER   DEFAULT 1
    ) RETURN VARCHAR2 IS

      l_header       VARCHAR2(64);
      l_start        NUMBER;
      l_end          NUMBER;
      l_end1         NUMBER;
      l_end2         NUMBER;
      l_missed_count NUMBER;
      l_part2        VARCHAR2(4000);

    BEGIN

     -- Validate Parameters
     IF (TRIM(p_part1) IS NULL
      OR TRIM(p_element) IS NULL
      OR p_occurrence < 1) THEN

       RETURN NULL;

     END IF;

     -- Construct Record Header
     l_header := '.' || UPPER(p_element) || '/';

     l_start := INSTR(p_part1, l_header, 1, p_occurrence);
     IF (l_start > 0) THEN

       l_end1 := INSTR(p_part1, CHR(13), l_start);
       l_end2 := INSTR(p_part1, CHR(10), l_start);
       IF (l_end1 = 0 AND l_end2 = 0) THEN

         l_end := LENGTH(p_part1) + 1;

       ELSIF (l_end1 > 0 AND l_end2 > 0) THEN

         l_end := LEAST(l_end1, l_end2);

       ELSE

         l_end := GREATEST(l_end1, l_end2);

       END IF;

       RETURN SUBSTR(p_part1, l_start, l_end - l_start);

     ELSIF (p_part2 IS NOT NULL) THEN

       -- Count number of occurrences in first 2048 characters
       l_missed_count := (LENGTH(SUBSTR(p_part1, 1, 2048)) - LENGTH(REPLACE(SUBSTR(p_part1, 1, 2048), l_header))) / LENGTH(l_header);

       -- Build new string containing remainder of message
       l_part2 := SUBSTR(p_part1, 2049) || p_part2;

       -- Search remainder of message
       l_start := INSTR(l_part2, l_header, 1, p_occurrence - l_missed_count);
       IF (l_start > 0) THEN

         l_end1 := INSTR(l_part2, CHR(13), l_start);
         l_end2 := INSTR(l_part2, CHR(10), l_start);
         IF (l_end1 = 0 AND l_end2 = 0) THEN

           l_end := LENGTH(l_part2) + 1;

         ELSIF (l_end1 > 0 AND l_end2 > 0) THEN

           l_end := LEAST(l_end1, l_end2);

         ELSE

           l_end := GREATEST(l_end1, l_end2);

         END IF;

         RETURN SUBSTR(l_part2, l_start, l_end - l_start);

       END IF;

     END IF;

     RETURN NULL;

    END find_record;

PROCEDURE screen_bsm_bmm_bag ( p_pax_id    IN pax.pax_id%TYPE
                              ,p_flight_id IN flight.flight_id%TYPE)

IS
  -- inbound flight
  l_flight_id          flight.flight_id%type       := NULL;
  l_site_id            flight.site_id%TYPE         := NULL;

  -- pax and flight
  l_ib_flight_no       pax.inbound_flight_no%TYPE  := NULL;
  l_ib_flight_date     pax.inbound_date%TYPE       := NULL;
  l_msg_msg_id         messages.message_id%TYPE    := NULL;
  l_msg_flight_id      messages.flight_id%TYPE     := NULL;
  l_msg_flight_number  messages.flight_number%TYPE := NULL;
  l_msg_flight_date    messages.flight_date%TYPE   := NULL;
  l_seq_no             pax.pax_sequence_no%TYPE    := NULL;

  -- type declaration
  type bag_ref IS         REF CURSOR ;
  check_bag_match_csr     bag_ref;
  t_bag_id                bag_id_type;
  t_bag_tag               bag_tag_type;
  t_bag_tag3              bag_tag3_type;
  t_bag_tag2b             bag_tag2b_type;
  pax_structure           pm_pax_record;

  -- general declarations
  l_pax_exists            pax.pax_id%TYPE            := 0;
  pax_id_match            pax.pax_id%TYPE            := 0;
  l_bmm_type              messages.message_type%TYPE := 'BMM';
BEGIN
      -- Get the site and inbound flight details of the
      -- outbound pax.
      BEGIN
         SELECT  pax.flight_id
                ,pax.inbound_flight_no
                ,pax.inbound_date
                ,flight.site_id
                ,pax.pax_sequence_no
         INTO   l_flight_id
               ,l_ib_flight_no
               ,l_ib_flight_date
               ,l_site_id
               ,l_seq_no
         FROM   pax, flight
         WHERE  pax.pax_id       = p_pax_id
         AND    pax.flight_id    = flight.flight_id;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
              l_flight_id     := NULL;
      END;

     -- Sanity Check - If this is the LOADALL pax, don't bother
     IF (l_seq_no = -1) THEN
       RETURN;
     END IF;

     -- check the message table to see whether an incoming BMM has been
     -- received.
     IF ( l_ib_flight_no IS NOT NULL AND l_ib_flight_date IS NOT NULL ) THEN

        BEGIN
           SELECT msg.message_id
                 ,msg.flight_id
                 ,msg.flight_number
                 ,msg.flight_date
           INTO   l_msg_msg_id
                 ,l_msg_flight_id
                 ,l_msg_flight_number
                 ,l_msg_flight_date
           FROM   messages msg
           WHERE  msg.flight_number = l_ib_flight_no   --bsm i/b flight no
           AND    msg.flight_date   = l_ib_flight_date --bsm i/b flight date
           AND    multi_site.get_site_id(substr(msg.version,-3,3)) = l_site_id
           AND    msg.message_type     = l_bmm_type
           AND    msg.processed_state  = 'P';
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
                return; -- no need to continue processing
         END;

       -- Using the current BSM pax/bag matching uses the pax_id and bag_ids
       -- to check against the i/b pax and i/b bag tables.
        BEGIN

        SELECT ip.inbound_pax_id
        INTO   l_pax_exists
        FROM   inbound_pax ip
        WHERE  ip.result_pax_id     = p_pax_id
        AND    ip.inbound_flight_id = l_msg_flight_id  -- i/b flight id
        AND    ip.result_pax_status <> 'REVERSED';

        EXCEPTION
            WHEN NO_DATA_FOUND THEN NULL;
            WHEN TOO_MANY_ROWS THEN
                 add_app_msg.log_oracle_error('screen_bsm_bmm_bag '||l_msg_flight_id||','||p_pax_id,SQLERRM,SQLCODE);
          RAISE;
        END;


        -- if no entry found on the inbound_pax table then further processing is needed
        -- to determine the pax details for checking the bags against.
        IF l_pax_exists = 0 THEN
        -- load the outbound paxes details into the pax_structure
            pax_match.load_pax_structure( pax_structure
                                         ,l_flight_id  --outbound flight id
                                         ,p_pax_id);   --outbound pax_id

            -- compare the outbound pax with that on the inbound flight to see
            -- whether there is a match or not.
            pax_id_match := pax_match.perform_match(config.get_value('BMM_INBOUND_PAX_MATCHING'),
                                              pax_structure,
                                              l_msg_flight_id);   -- i/b flight id

           -- if there is still no match need to update the bag.security_clearance_status = 'N'
           -- for the outbound pax for ALL bags, since haven't received a BMM.
           IF pax_id_match = 0 THEN
              UPDATE bag
              SET    security_clearance_status = 'N'
              WHERE  bag.pax_id = p_pax_id;
           END IF;
        END IF;

        -- If pax found either via the manual pax_match or the pax already exists
        -- get all the bags which don't exist on the bmm so that their
        -- security_clearance status change be set to 'N'
         IF  pax_id_match > 0 OR l_pax_exists > 0 THEN

            -- need to use the inbound pax id as determined from the manual pax matching
            -- or if it already exists.
            OPEN check_bag_match_csr FOR
                    SELECT bag.bag_tag, bag.bag_id, bag.bag_tag3, bag.bag_tag2b
                    FROM   bag, pax
                    WHERE  pax.pax_id            = bag.pax_id
                    AND    pax.flight_id         = l_flight_id -- outbound flight id of pax
                    AND    pax.pax_id            = p_pax_id    -- outbound pax id
                    AND    pax.inbound_flight_no = l_ib_flight_no   -- inbound flight_no from messages
                    AND    pax.inbound_date      = l_ib_flight_date -- inbound flight_date from messages
                    AND    (bag.bag_tag3,bag.bag_tag2b) NOT IN
                           ( SELECT NVL(ib.bag_tag3, TO_NUMBER(SUBSTR(ib.bag_tag,-6)))
                                   ,NVL(ib.bag_tag2b, SUBSTR(ib.bag_tag,-9,3))
                             FROM   inbound_bag_v1 ib,
                                    inbound_pax_v1 ip
                             WHERE  ib.inbound_pax_id    = ip.inbound_pax_id
                             AND    ip.inbound_flight_id = l_msg_flight_id  -- i/b flight_id
                             AND    ib.result_bag_status <> 'REVERSED'
                             AND    ip.result_pax_status <> 'REVERSED'
                             AND    ip.inbound_pax_id  =  decode(pax_id_match,0,l_pax_exists,pax_id_match)); -- from pax matching

                    --
               FETCH check_bag_match_csr BULK COLLECT INTO
                     t_bag_tag, t_bag_id, t_bag_tag3, t_bag_tag2b
               LIMIT MAX_BAG_ROWS;
                    --
               CLOSE check_bag_match_csr;

               -- for any bags which don't exist on the incoming BMM
                -- need to set these for screening.
               IF t_bag_tag.COUNT > 0 THEN
                FORALL i in 1..t_bag_tag.COUNT
                   UPDATE bag
                   SET    bag.security_clearance_status = 'N'
                   WHERE  bag.bag_id = t_bag_id(i);
              END IF;
         END IF;
     END IF;

EXCEPTION
     WHEN OTHERS THEN
          add_app_msg.log_oracle_error('screen_bsm_bmm_bag ',SQLERRM,SQLCODE);
          RAISE;
END screen_bsm_bmm_bag;


end Process_Message;
/
