/*****************************************************************************
*
* Filename         - $Workfile:   exception_bags.sql  $
*
*                     Name              Date      Version     CR Number
*
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live Schema Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/

PROMPT CREATE OR REPLACE PACKAGE exception_bags

CREATE OR REPLACE PACKAGE exception_bags AS
error_message           VARCHAR2(512)       DEFAULT NULL;
error_number            NUMBER              DEFAULT 0;

FUNCTION is_permitted_exception
(exception_type   IN  bag.bag_exception%TYPE
) RETURN BOOLEAN ;

PROCEDURE add_pax_and_excep_bag
  (p_location_code      IN      location.location_code%TYPE
  ,p_user_id            IN      tagtrak_users.user_id%TYPE
  ,p_serial_no          IN      kit.serial_number%TYPE
  ,p_flight_id          IN      flight.flight_id%TYPE
  ,p_bag_tag            IN      bag.bag_tag%TYPE
  ,p_credential         IN      carrier_lookup.pax_cred%type   -- PAX_SEQUENCE_NO  or PAX_SECURITY_NO   or (PAX_SEAT_NO)
  ,p_credential_value   IN      CHAR
  ,p_exception          in      bag.bag_exception%TYPE
  ,p_pax_destination    IN      pax.pax_destination%TYPE
  ,p_pax_initials       IN      pax.pax_initials%TYPE
  ,p_pax_surname        IN      pax.pax_surname%TYPE
  ,p_booked_flight_id   IN      pax.booked_flight_id%TYPE
  ,p_conn_type          IN      pax.connection_type%TYPE
  ,p_embassy            in      pax.embassy%TYPE
  ,p_seat_no            in      pax.pax_seat_no%TYPE
  ,p_deliv_instr        in      bag.delivery_instr%TYPE
  ,p_additional_bags    in      number
  ,p_wt_ref             IN      bag.worldtracer_ref%TYPE
  ,p_pax_id             IN OUT  pax.pax_id%TYPE
  ,p_kg_weight          IN      bag.kg_weight%TYPE
  ,p_bvis_subhead       IN      bag.bvis_subheader%TYPE
  ,p_error_message         OUT  error_message%TYPE
  ,p_error_number          OUT  error_number%TYPE
  ) ;

PROCEDURE add_unnamed_pax_and_excep_bag (
  fltid             IN OUT NUMBER,
  p_bag_exception   IN     bag.bag_exception%TYPE ,  --  eg  'RUSH'
  tag               IN     CHAR,
  tag1              IN     CHAR,
  tag2a             IN     CHAR,
  tag2b             IN     CHAR,
  tag3              IN     NUMBER,
  bag_count         IN     NUMBER,
  atldcs            IN     CHAR,
  dest              IN     CHAR,
  source_indicator  IN     CHAR,
  proc              IN OUT CHAR,
  unit              IN OUT CHAR,
  dcsid             IN OUT CHAR,
  subunit           IN OUT CHAR,
  dcstagno          IN     CHAR,
  conn_type         IN     CHAR,
  btype             IN     CHAR,
  location          IN     bag_trace.location_code%TYPE,
  user_id           IN     bag_trace.user_id%TYPE,
  scs               IN     bag.security_clearance_status%TYPE default NULL,
  deliv_instr       IN     bag.delivery_instr%TYPE,
  wt_ref            IN     bag.worldtracer_ref%TYPE,
  p_kg_weight       IN     bag.kg_weight%TYPE,
  p_bvis_subhead    IN     bag.bvis_subheader%TYPE,
  rpid                 OUT NUMBER
);

PROCEDURE get_bag_count_for_crewcrew (
  p_fltid          IN     flight.flight_id%TYPE,
  p_crew_bag_count    OUT NUMBER
);

FUNCTION get_bag_exception_type_id (p_bag_exception IN bag.bag_exception%TYPE)
RETURN bag_exception_types.BAG_EXCEP_ID%TYPE;

PROCEDURE get_pax_credentials
(cred_value IN     credentials_lookup.pax_cred_scanner_values%TYPE,
 pax_cred      OUT credentials_lookup.pax_cred%TYPE
);

PROCEDURE update_bag (
        p_bag_tag                 IN      bag.bag_tag%TYPE,
        p_pax_id                  IN      pax.pax_id%TYPE,
        p_flight_id               IN      flight.flight_id%TYPE,
        p_embassy                 IN      pax.embassy%TYPE,
        p_delivery_instructions   IN      bag.delivery_instr%TYPE,
        p_seat_no                 IN      pax.pax_seat_no%TYPE,
        p_surname                 IN      pax.pax_surname%TYPE,
        p_initials                IN      pax.pax_initials%TYPE,
        p_destination             IN      bag.bag_destination%TYPE,
        p_worldtracer_ref         IN      bag.worldtracer_ref%TYPE,
        p_user_id                 IN      tagtrak_users.user_id%TYPE,
        p_location                IN      location.location_code%TYPE,
        p_kg_weight               IN      bag.kg_weight%TYPE,
        p_error_no                   OUT  error.error_id%TYPE,
        p_error_message              OUT  error_message%TYPE );

END exception_bags;
/

/*********************************************************************************
*                                                                                *
*                                                                                *
*********************************************************************************/

PROMPT CREATE OR REPLACE PACKAGE BODY exception_bags

CREATE OR REPLACE PACKAGE BODY exception_bags AS

  oracle_error_message    error_message%TYPE DEFAULT NULL;
  oracle_error_number     error_number%TYPE  DEFAULT 0;
  process                 VARCHAR2(50)       DEFAULT NULL;
  --rowcount                PLS_INTEGER             DEFAULT 0;

  abort                   EXCEPTION;
  abandon                 EXCEPTION;
  integrity_constraint    EXCEPTION;
  check_constraint        EXCEPTION;
  invalid_bag_tag_format  EXCEPTION;
  PRAGMA EXCEPTION_INIT(check_constraint, -2290);
  PRAGMA EXCEPTION_INIT(integrity_constraint, -2291);
  PRAGMA EXCEPTION_INIT(invalid_bag_tag_format, -20042);

/*********************************************************************************
*********************************************************************************/
procedure Set_Misc_Exceptions(p_tag2a     in bag.bag_tag2a%type,
			      p_tag2b     in bag.bag_tag2b%type,
			      p_tag3      in bag.bag_tag3%type,
			      p_pax_id    in pax.pax_id%type,
			      p_exception in bag.bag_exception%type)
is
    l_bag_id bag.bag_id%type;
begin
    
    select bag_id into l_bag_id from bag
    	where (bag_tag2a = p_tag2a or bag_tag2b = p_tag2b) and
		bag_tag3 = p_tag3 and pax_id = p_pax_id;

    delete from misc_bag_exceptions
    	where bag_id = l_bag_id and exception_source = 'BRS';

    insert into misc_bag_exceptions (bag_id, exception_source, exception_type)
    	values (l_bag_id, 'BRS', p_exception);

end Set_Misc_Exceptions;

/*********************************************************************************
*********************************************************************************/
FUNCTION post_departure (p_flight_id IN flight.flight_id%TYPE) 
RETURN BOOLEAN AS

  v_flight_status       flight.flight_status%TYPE;

BEGIN

  process := 'post_departure : ';

  SELECT flight_status
  INTO v_flight_status
  FROM flight
  WHERE flight_id = p_flight_id;

  RETURN v_flight_status = 'PD';

END post_departure;
/*********************************************************************************
*********************************************************************************/
FUNCTION convert_dash_to_null (credential_value in pax.pax_security_no%TYPE)
RETURN CHAR AS

BEGIN

  IF credential_value = '-' THEN
    RETURN NULL;

  ELSE

    RETURN credential_value;
  
  END IF;

END convert_dash_to_null;
/*********************************************************************************
*********************************************************************************/
FUNCTION credentials_ok(p_flight_id        IN flight.flight_id%TYPE,
                        v_pax_cred         IN credentials_lookup.pax_cred%TYPE,
                        p_credential_value IN CHAR,
                        p_pax_surname      IN pax.pax_surname%TYPE)       
RETURN BOOLEAN IS

  v_total         INTEGER DEFAULT 0;

BEGIN

  process := 'credentials_ok : ';

  IF v_pax_cred = 'SECURITY_NO' THEN
  
    SELECT COUNT(*)
    INTO v_total
    FROM pax
    WHERE flight_id = p_flight_id
    AND pax_security_no = p_credential_value;
        
    IF v_total = 0 THEN
    
      RETURN TRUE;
    
    ELSE
    
      SELECT COUNT(*)
      INTO v_total
      FROM pax
      WHERE flight_id = p_flight_id
      AND     pax_security_no = p_credential_value
      AND             nvl(pax_surname,'-') = nvl(p_pax_surname,'-');
      
      IF v_total = 0 THEN
        RETURN FALSE;
      ELSE
        RETURN TRUE;
      END IF;
     
    END IF;
  
  ELSIF v_pax_cred = 'SEQUENCE_NO' THEN
    
    SELECT COUNT(*)
    INTO v_total
    FROM pax
    WHERE flight_id = p_flight_id
    AND     pax_sequence_no = p_credential_value;
    
    IF v_total = 0 THEN
            
      RETURN TRUE;
            
    ELSE
          
      SELECT COUNT(*)
      INTO v_total
      FROM pax
      WHERE flight_id = p_flight_id
      AND pax_sequence_no = p_credential_value
      AND nvl(pax_surname,'-') = nvl(p_pax_surname,'-');
                
      IF v_total = 0 THEN
        RETURN FALSE;
      ELSE
        RETURN TRUE;
      END IF;
            
    END IF;

  ELSIF v_pax_cred = 'SEAT_NO' THEN

    SELECT COUNT(*)
    INTO v_total
    FROM pax
    WHERE flight_id = p_flight_id
    AND     pax_seat_no = p_credential_value;
    
    IF v_total = 0 THEN
    
      RETURN TRUE;
    
    ELSE
    
      SELECT COUNT(*)
      INTO v_total
      FROM pax
      WHERE flight_id = p_flight_id
      AND pax_seat_no = TO_NUMBER(p_credential_value)
      AND nvl(pax_surname,'-') = nvl(p_pax_surname,'-');
      
      IF v_total = 0 THEN
        RETURN FALSE;
      ELSE
        RETURN TRUE;
      END IF;
      
    END IF;
    
  ELSE
  
    error_number := -1045;
    error_message := 'passenger credential '
                     || v_pax_cred 
                     || ' is unknown ';
    RAISE abort;

  END IF;

END credentials_ok;

/*********************************************************************************
*********************************************************************************/
FUNCTION valid_flight_destination (
  p_flight_id          IN flight.flight_id%TYPE,
  p_flight_destination IN flight_dests.flight_destination%TYPE )
RETURN BOOLEAN IS

  CURSOR c_flight_destination (p_flight_id           flight.flight_id%TYPE,
                               p_flight_destination  flight_dests.flight_destination%TYPE) IS
  SELECT flight_destination
  FROM flight_dests
  WHERE flight_id = p_flight_id
  AND flight_destination = p_flight_destination
  FOR UPDATE OF flight_destination;

  v_flight_destination        flight_dests.flight_destination%TYPE;

BEGIN
      
  process := 'valid_flight_destination : ';

  OPEN c_flight_destination(p_flight_id, p_flight_destination);
  FETCH c_flight_destination
  INTO  v_flight_destination;
  IF c_flight_destination%NOTFOUND THEN

    RAISE NO_DATA_FOUND;

  END IF;
  CLOSE c_flight_destination;

  RETURN TRUE;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    CLOSE c_flight_destination;
    RETURN FALSE;
    
END valid_flight_destination;

/*********************************************************************************
*********************************************************************************/

FUNCTION get_bag_exception_type_id (
        p_bag_exception   IN   bag.bag_exception%TYPE )
        return bag_exception_types.BAG_EXCEP_ID%TYPE
IS
    type_id bag_exception_types.BAG_EXCEP_ID%TYPE;
BEGIN

    -- CA/2427
    --
    -- This is a purposefully restricted set of possible values as this is
    -- just the set of exception types that want to be displayed in the
    -- passenger's details on the MWS.  In the future this table may be used
    -- (or mis-used even) for other exception types.
    --
    -- For instance, if GATE was added as an exception type then this exception
    -- code should not be stored against the PAX, just the BAG.
    --
    begin
	select bag_excep_id into type_id
	    from bag_exception_types
	    	where bag_excep_code = p_bag_exception and
			create_pax_with_excep_id = 'Y';
    exception
    	when NO_DATA_FOUND then
	    type_id := null;
    end;

    return type_id;

END get_bag_exception_type_id;

/*********************************************************************************
********************************************************************************/
FUNCTION is_permitted_exception(exception_type   IN  bag.bag_exception%TYPE) 
RETURN BOOLEAN IS

  exception_code NUMBER(1);

BEGIN

  -- Check if supplied baggage exception exists in bag_exception_types table
  SELECT bag_excep_id
  INTO exception_code
  FROM bag_exception_types
  WHERE bag_excep_code = exception_type;

  RETURN TRUE;

EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;

END is_permitted_exception;

/*********************************************************************************
*********************************************************************************/
FUNCTION is_exception_pax
( v_pax_id              IN  pax.pax_id%TYPE,
  new_exception_code    IN  bag_exception_types.bag_excep_code%TYPE
) RETURN BOOLEAN IS

    status boolean := true;
    cnt number;

BEGIN

    -- CA/02760
    --
    -- We no longer worry about the PAX record itself having the exception 
    -- code -- we just check to see if it is a possible exception code to
    -- test against, then query the misc exceptions to check that the PAX has
    -- the exception type against them.
    --
    if get_bag_exception_type_id(new_exception_code) is not null then

    	select count(*) into cnt from misc_bag_exceptions
	    where exception_type = new_exception_code and
		    bag_id in (select bag_id from bag where pax_id = v_pax_id);

	status := (cnt > 0);

    end if;

    return status;

END is_exception_pax;

/*********************************************************************************
*********************************************************************************/
PROCEDURE get_exception_consts (
    p_flight_id        IN  flight.flight_id%type,
    p_bag_exception    IN  bag.bag_exception%TYPE,
    p_pax_initials     OUT pax.pax_initials%TYPE,
    p_surname          OUT pax.pax_surname%TYPE,
    p_sequence_no      OUT pax.pax_sequence_no%TYPE,
    p_class            OUT pax.pax_class%type) AS

BEGIN

  p_class := '*';

  IF p_bag_exception = 'CREW' THEN

    p_pax_initials         := 'CREW';
    p_surname              := 'CREWCREW';
    p_sequence_no          := 997;

  ELSIF p_bag_exception = 'DIPL' THEN

    p_pax_initials         := 'DIPL';
    p_surname              := 'DIPLDIPL';
    p_sequence_no          := 996;

  ELSIF p_bag_exception = 'MAIL' THEN
    p_pax_initials         := 'MAIL';
    p_surname              := 'MAILMAIL';
    p_sequence_no          := 995;

  ELSE
    -- remaining "permitted" exceptions will be assumed to be RUSH
    p_pax_initials         := 'RUSH';
    p_surname              := 'RUSHRUSH';
    p_sequence_no          := 999;

    begin
    	select substr(c.db_value,1,1) into p_class
	    from site_config c, flight f
	    where f.flight_id = p_flight_id and
	          f.site_id = c.site_id and
		  db_item = 'RUSHRUSH_CLASS';
    exception
	when NO_DATA_FOUND then
	    null;
    end;

  END IF;

END get_exception_consts;

/*********************************************************************************
*********************************************************************************/
PROCEDURE  get_flight_dest (p_flight_id    IN     flight.flight_id%TYPE,
                            p_flight_dests    OUT flight_dests.flight_destination%TYPE,
                            retval            OUT  NUMBER)
AS

  dest_not_found number;

BEGIN

  /* Get the flight destination for this flight_id */
  dest_not_found := 0;
  
  BEGIN
    /*
     * Get the flight destination, firstly where source_code = 'F'
     * failing that, where the source_code = 'B' and then 'M'
     */
    SELECT flight_destination
    INTO p_flight_dests
    FROM flight_dests
    WHERE flight_id    = p_flight_id 
    AND dest_order   = 1           
    AND dest_source  = 'F';

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
  
      dest_not_found := 1;
  
  END;

  IF (dest_not_found = 1) THEN
    /* Get the flight_destination from the flight_dests where the */
    /* dest_source is equal to 'B'.                               */
    BEGIN
      /*
       * Get the flight destination, firstly where source_code = 'F'
       * failing that, where the source_code = 'B'
       */
      SELECT flight_destination
      INTO p_flight_dests
      FROM flight_dests
      WHERE flight_id    = p_flight_id 
      AND dest_order   = 1           
      AND dest_source  = 'B';

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        
        dest_not_found := 2;
        
    END;
  
  END IF;

  IF (dest_not_found = 2) THEN
        /* Get the flight_destination from the flight_dests where the */
        /* dest_source is equal to 'M'.                               */
    BEGIN
      
      SELECT flight_destination
      INTO p_flight_dests
      FROM flight_dests
      WHERE flight_id    = p_flight_id 
      AND dest_order   = 1           
      AND dest_source  = 'M';

    EXCEPTION 
      WHEN NO_DATA_FOUND THEN
        /* WRONG DESTINATION */
        retval := -1009;
        
    END;
      
  END IF;

END get_flight_dest;

/*********************************************************************************
*********************************************************************************/
procedure add_named_bag (
  flt_id          IN     flight.flight_id%TYPE,
  p_pax_id        IN     pax.pax_id%TYPE,
  p_exception     IN     bag.bag_exception%TYPE,
  p_bag_tag       IN     bag.bag_tag%TYPE,
  p_tag1          IN     bag.bag_tag1%TYPE,
  p_tag2a         IN     bag.bag_tag2a%TYPE,
  p_tag2b         IN     bag.bag_tag2b%TYPE,
  p_tag3          IN     bag.bag_tag3%TYPE,
  bag_dest        IN     bag.bag_destination%TYPE,
  proc_name       IN     bag.process_name%TYPE,
  un_name         IN     bag.unit_name%TYPE,
  dcsid           IN     bag.dcsid%TYPE,
  subu_name       IN     bag.subunit_name%TYPE,
  p_conn_type     IN     pax.connection_type%type,
  p_deliv_instr   IN     bag.delivery_instr%TYPE,
  p_location_code IN     location.location_code%TYPE,
  p_user_id       IN     tagtrak_users.user_id%TYPE,
  p_wt_ref        IN     bag.worldtracer_ref%TYPE,
  p_kg_weight     IN     bag.kg_weight%TYPE,
  p_bvis_subhead  IN     bag.bvis_subheader%TYPE,
  retval             out number
) AS

  pax_pax_id        pax.pax_id%TYPE;
  tag_type          NUMBER;
  rv                NUMBER;
  dm                NUMBER;
  reuse_bag_flag    NUMBER;

  dcstagno          bag.dcs_bag_tag%TYPE;
  dcs_bag_tag       bag.dcs_bag_tag%TYPE;
  v_fid             flight.flight_id%TYPE;
  v_fno             flight.flight_no%TYPE;
  v_fda             flight.flight_date%TYPE;
  v_tag1            bag.bag_tag1%TYPE;
  v_tag2a           bag.bag_tag2a%TYPE;
  v_tag2b           bag.bag_tag2b%TYPE;
  v_tag3            bag.bag_tag3%TYPE;

  depair            pax.departure_airport_code%TYPE := NULL;
  /* reuse_bag_flag Parameters */
  source_indicator  bag.bag_source_indicator%TYPE := 'L';

  CURSOR get_bag_pax_details_csr (b2b     CHAR, 
                                  b3      NUMBER, 
                                  depcode CHAR) IS
  SELECT p.pax_id,
         b.delete_marker,
         b.dcs_bag_tag
  FROM bag b,
       pax p
  WHERE b.bag_tag2b = b2b 
  AND b.bag_tag3  = b3  
  AND b.bag_source_location = depcode 
  AND b.pax_id    = p.pax_id;

BEGIN

  /* Initialise reuse_bag_flag to 0 */
  reuse_bag_flag := 0;

  depair := get_offset.find_airport_code(flt_id, p_pax_id);

  --Check for a duplicate bag

  BEGIN
  
    OPEN get_bag_pax_details_csr (p_tag2b, 
                                  p_tag3, 
                                  depair);
    LOOP
    
      FETCH get_bag_pax_details_csr INTO pax_pax_id, dm, dcstagno;
      EXIT WHEN get_bag_pax_details_csr%NOTFOUND;

      /* If the pax_id is the same as the pax_id of the bag
         then return UNRESOLVABLE DUPLICATE                 */
      IF pax_pax_id = p_pax_id AND dm = 0 THEN
        
        /* UNRESOLVABLE DUPLICATE */
        rv := -1021;
        EXIT;
        
      ELSIF pax_pax_id = p_pax_id AND dm != 0 THEN
        /* Need to reuse bag */
        reuse_bag_flag   := 1;
        dcs_bag_tag := dcstagno;
        
      END IF;
    END LOOP;
    CLOSE get_bag_pax_details_csr;
    
  END;

  dbms_output.put_line( 'add_named_bag  reuse_bag_flag='||reuse_bag_flag||'  rv='||rv);

  IF (rv != 0) THEN
  
    retval := rv;
    RETURN;
    
  ELSIF reuse_bag_flag = 1 THEN
    /* need to reuse_exception_bag */

    v_tag1  := p_tag1;
    v_tag2a := p_tag2a;
    v_tag2b := p_tag2b;
    v_tag3  := p_tag3;

    dbms_output.put_line('reuse_bag');

    reuse_bag(p_bag_tag,
              v_tag1,
              v_tag2a,
              v_tag2b,
              v_tag3,
              p_pax_id,
              'Y',
              bag_dest,
              source_indicator ,
              p_exception,
              proc_name,
              un_name,
              dcsid,
              subu_name,
              dcs_bag_tag,
              p_conn_type,
              NULL,
              NULL,              /* Ensure security clearance is recalculated */
              p_deliv_instr,
              p_wt_ref,
              rv,
              p_kg_weight,
              p_bvis_subhead);

    IF rv <= 0 THEN
      /* No pax id returned so error on reuse_bag_flag */
      retval := rv;
      RETURN;
    END IF;
    
  ELSE
  
    /* Insert the bag */
    dbms_output.put_line('add_named_bag  Insert the bag');

    add_bag (p_bag_tag,
             p_tag1,
             p_tag2a,
             p_tag2b,
             p_tag3,
             p_pax_id,
             'Y',
             bag_dest,
             source_indicator,
             p_exception,
             proc_name,
             un_name,
             dcsid,
             subu_name,
             p_bag_tag,
             NULL,
             p_conn_type,
             NULL,          /* Bag type */
             p_location_code,
             p_user_id,
             '-',          /* CA 134 Add Security Clearance Status */
             p_deliv_instr,  /* Delivery instructions */
             p_wt_ref,
             p_kg_weight,
             p_bvis_subhead);

  END IF;

  Set_Misc_Exceptions(p_tag2a, p_tag2b, p_tag3, p_pax_id, p_exception);

  /* Increment the pax.no_of_bags field */
  UPDATE pax
  SET no_of_bags = no_of_bags + 1
  WHERE pax_id     = p_pax_id 
  AND flight_id  = flt_id;

  retval := 0;

END add_named_bag;

/*********************************************************************************
*********************************************************************************/

PROCEDURE add_named_pax
(
   p_flight_id       IN    flight.flight_id%TYPE,
   p_pax_initials    IN    pax.pax_initials%TYPE,
   p_pax_surname     IN    pax.pax_surname%TYPE,
   p_pax_credential  IN    carrier_lookup.pax_cred%type,  -- PAX_SEQUENCE_NO  or PAX_SECURITY_NO or (PAX_SEAT_NO)
   p_pax_qualifier   IN    CHAR,
   p_exception       IN    bag.bag_exception%TYPE,
   p_embassy         IN    pax.embassy%TYPE,
   p_bag_dest        IN    bag.bag_destination%TYPE,
   p_seat_no         IN    pax.pax_seat_no%TYPE,
   p_codeshare_fid   IN    NUMBER,
   p_conn_type       IN    CHAR,
   p_retval             OUT NUMBER
)
AS

  pax_id_no     pax.pax_id%TYPE;
  pax_seq_no    pax.pax_sequence_no%TYPE;
  retcode       NUMBER;
  v_pid         pax.pax_id%type;
  depair        pax.departure_airport_code%type := null;
  surname       pax.pax_surname%TYPE;
  pcv_onwd      onward_flight_defs.onwd_flt_t;

  l_seat_no     pax.PAX_SEAT_NO%TYPE;
  l_seqno       pax.PAX_SEQUENCE_NO%TYPE;
  l_dcs_seqno   pax.PAX_DCS_SEQUENCE_NO%TYPE;
  l_secno       pax.PAX_SECURITY_NO%TYPE;

BEGIN

  depair := get_offset.find_airport_code(p_flight_id,  v_pid);

  --Firstly, generate a new pax_sequence_no value.
  make_sequence_no (p_codeshare_fid, pax_seq_no);

  --Add the new Pax.
  IF (UPPER(p_pax_credential) = 'SEQUENCE_NO') THEN
   
    l_seat_no   :=  convert_dash_to_null (p_seat_no);
    l_seqno     :=  pax_seq_no;
    l_dcs_seqno :=  convert_dash_to_null (p_pax_qualifier);
    l_secno     :=  NULL;
    
  ELSIF (UPPER(p_pax_credential) = 'SECURITY_NO') THEN

    l_seat_no   :=  convert_dash_to_null (p_seat_no);
    l_seqno     :=  pax_seq_no;
    l_dcs_seqno :=  NULL ;
    l_secno     :=  convert_dash_to_null (p_pax_qualifier);

  ELSE
    /* It must be seat_no */
    l_seat_no   :=  convert_dash_to_null (p_pax_qualifier);
    l_seqno     :=  pax_seq_no;
    l_dcs_seqno :=  NULL ;
    l_secno     :=  NULL;

  END IF;

  add_pax (p_flight_id,
           p_codeshare_fid,
           p_pax_initials,
           p_pax_surname,
           'C',
           0,
           1,
           p_bag_dest,
           l_seat_no,
           '*',
           l_seqno,
           l_dcs_seqno,
           l_secno,
           NULL,
           NULL,
           NULL,
           NULL,
           NULL,
           NULL,
           'Y',
           p_pax_initials,
           0,
           p_conn_type,
           pcv_onwd,
           NULL,
           p_exception,
           p_embassy,
           pax_id_no);

  --Return the pax_id_no
  p_retval := pax_id_no;

END add_named_pax;

/*********************************************************************************
*********************************************************************************/

PROCEDURE add_unnamed_pax_and_excep_bag (
  fltid             IN OUT NUMBER,
  p_bag_exception   IN     bag.bag_exception%TYPE ,  --  eg  'RUSH'
  tag               IN     CHAR,
  tag1              IN     CHAR,
  tag2a             IN     CHAR,
  tag2b             IN     CHAR,
  tag3              IN     NUMBER,
  bag_count         IN     NUMBER,
  atldcs            IN     CHAR,
  dest              IN     CHAR,
  source_indicator  IN     CHAR,
  proc              IN OUT CHAR,
  unit              IN OUT CHAR,
  dcsid             IN OUT CHAR,
  subunit           IN OUT CHAR,
  dcstagno          IN     CHAR,
  conn_type         IN     CHAR,
  btype             IN     CHAR,
  location          IN     bag_trace.location_code%TYPE,
  user_id           IN     bag_trace.user_id%TYPE,
  scs               IN     bag.security_clearance_status%TYPE default NULL,
  deliv_instr       IN     bag.delivery_instr%TYPE,
  wt_ref            IN     bag.worldtracer_ref%TYPE,
  p_kg_weight       IN     bag.kg_weight%TYPE,
  p_bvis_subhead    IN     bag.bvis_subheader%TYPE,
  rpid                 OUT NUMBER
)
AS

  -- variables dependent ob exception type
  v_pax_initials       pax.pax_initials%TYPE     ;  --      := 'RUSH';
  v_surname            pax.pax_surname%TYPE      ;  --      := 'RUSHRUSH';
  v_sequence_no        pax.pax_sequence_no%TYPE  ;  --      := 999;
  v_dcs_sequence_no    pax.pax_dcs_sequence_no%TYPE  ;  --  := '999';
  v_full_initials      pax.pax_full_inits%TYPE       ;  --  := 'RUSH';
  
  added_pax            NUMBER;
  dm                   NUMBER;
  tagged_bags          CHAR := 'Y';
  v_tag_error          NUMBER := 0;

  -- pax_id parameters
  v_rpid               NUMBER;
  l_dest               flight_dests.FLIGHT_DESTINATION%TYPE;

  -- add_pax parameters
  status               pax.pax_status%TYPE             := 'C';
  nobags               pax.no_of_bags%TYPE             := 1;
  no_same_surname      pax.no_same_surname%TYPE        := 1;
  no_value             varchar2(1)                     := '';
  class                pax.pax_class%TYPE              := '*';
  pax_atl_dcs          pax.authority_to_load_dcs%TYPE  := 'Y';
  onward_transfer      pax.onward_transfer%TYPE        := 0;
  v_onward_flight      onward_flight_defs.onwd_flt_t ;

  -- add_bag parameters
  uldno                bag.uld_no%type;

  CURSOR c1 IS
  SELECT b.delete_marker
  FROM bag b,
       pax p,
       bag_exception_types e
  WHERE b.pax_id = p.pax_id        
  AND p.flight_id = fltid           
  AND b.bag_tag2b = tag2b           
  AND b.bag_tag3 = tag3            
  AND b.bag_exception = p_bag_exception 
  AND e.bag_excep_code = b.bag_exception 
  AND e.bag_excep_pax_seq = p.pax_sequence_no;

  CURSOR c2 IS
  SELECT b.delete_marker
  FROM bag b,
       pax p,
       bag_exception_types e
  WHERE b.pax_id = p.pax_id        
  AND p.flight_id = fltid           
  AND b.bag_tag2a = tag2a           
  AND b.bag_tag3 = tag3            
  AND b.bag_exception = p_bag_exception 
  AND e.bag_excep_code = b.bag_exception 
  AND e.bag_excep_pax_seq = p.pax_sequence_no;

  l_location      bag_trace.location_code%TYPE;  /* local variables to use as OUT parameters */
  l_user_id       bag_trace.user_id%TYPE;
  l_sqlerr        error.error_id%TYPE;
  depair          pax.departure_airport_code%TYPE := NULL;

BEGIN

  /* Copy the fields into local variables to use as OUT parameters */

  l_location := location;
  l_user_id  := user_id;

  /*
   * - Initialise variables
   * - call the pax_id function
   * - if tag2b != '-' then
   *   loop around c1_rec cursor
   *     dm = c1_rec.delete_marker
   *     if dm = 0 then
   *       error = -1021
   * - else
   *   loop around c2_rec cursor
   *     dm = c2_rec.delete_marker
   *     if dm = 0 then
   *       error = -1021
   * - if error = 0 then
   *   call the add_pax procedure
   * - if error > 0 then
   *   if proc is null or equal to "-" then
   *     select details from the flight table
   *   call the add_bag procedure
   *   if added_pax is not equal to 1 then
   *     increment the bag_count
   */

  v_rpid    := 0;
  added_pax := 0;

  -- set appropriate constant values for pax
  get_exception_consts (fltid,
                        p_bag_exception,
			v_pax_initials,
			v_surname,
			v_sequence_no,
			class);

  v_dcs_sequence_no := v_sequence_no;
  v_full_initials := v_pax_initials ;

  -- find the pax_Id for the existing  un-named exception pax if it exists
  pax_id(fltid,v_sequence_no, v_rpid);

  -- if pax does not exist check or get flight dest
  IF dest IS NULL OR dest  = '-' THEN
    get_flight_dest (fltid,l_dest,rpid);
  ELSE
    l_dest := dest;
  END IF;

  -- If a named tag is to be used the dest MUST be valid
  IF NOT valid_flight_destination (fltid, l_dest) THEN
    
    dbms_output.put_line ( 'flight_destination ' || l_dest || ' is invalid') ;
    rpid := pkg_constants.ERROR_WRONG_DESTINATION;
    RETURN;

  END IF;

  /* Before processing PAX data check if there is CREW exceptions
   * to support untagged or tagged bags. */

  IF p_bag_exception = 'CREW' THEN
  
    /* Check if untagged or tagged crew bag options are valid.
     * If bags are untagged: tagged_bags = 'N', else tagged tagged_bags = 'Y'
     * and a non-zero value in v_tag_error indicates an error to be returned. */
    carrier_details.get_tagged_crew_bag (fltid, tagged_bags, v_tag_error);
    
    IF v_tag_error != 0 THEN
    
      --Base error on tagged_crew_bags value
      v_rpid := v_tag_error;
      
    ELSE
    
      --Work out if the right parameter is added
      IF (tagged_bags = 'N' AND tag != '-') THEN
      
        v_rpid := -1024;
        
      ELSIF (tagged_bags = 'Y' AND tag = '-') THEN
      
        v_rpid := -1026;
        
      END IF;

      IF ( bag_count != 0 ) THEN
        nobags := bag_count;
        
      END IF;
      
    END IF;
    
  END IF;

  IF tagged_bags = 'Y' THEN
  
    IF tag2b != '-' THEN
    
       FOR c1_rec IN c1 LOOP
       
         dm := c1_rec.delete_marker;
         IF (dm = 0) THEN
           /*
            * A Bag exists and is undeleted. This error code
            * will prevent the scanner from adding it again.
            */
           v_rpid := -1021;
         END IF;
         
       END LOOP;
       
    ELSE
    
      FOR c2_rec IN c2 LOOP
       
        dm := c2_rec.delete_marker;
      
        IF (dm = 0) THEN
         
          /* A Bag exists and is undeleted. This error code
           * will prevent the scanner from adding it again.*/
          v_rpid := -1021;

        END IF;
    
      END LOOP;
    
    END IF;
    
  END IF;

  BEGIN

    IF v_rpid = 0 THEN
    
      --No unnamed exception Pax, so add one.
      added_pax := 1;

      add_pax (
              fltid           =>    fltid,
              codeshare_fid   =>    NULL,
              init            =>    v_pax_initials,
              surn            =>    v_surname,
              sts             =>    status,
              nobags          =>    nobags,
              nosurn          =>    no_same_surname,
              dest            =>    l_dest,
              seat            =>    no_value,
              class           =>    class,
              seqno           =>    v_sequence_no,
              dcsseqno        =>    v_dcs_sequence_no,
              secno           =>    no_value,
              pnr             =>    no_value,
              secsts          =>    no_value,
              depair          =>    no_value,
              ifltno          =>    no_value,
              ifltdate        =>    no_value,
              ibdg            =>    no_value,
              atl             =>    pax_atl_dcs,
              pax_full_inits  =>    v_full_initials,
              onward_transfer =>    onward_transfer,
              con_type        =>    conn_type,
              onwd_flt        =>    v_onward_flight,
              freq_flyer      =>    no_value,
              BAG_EXCEPTION   =>    p_bag_exception,
              EMBASSY         =>    NULL,
              paxid           =>    v_rpid
              );

    END IF;

  EXCEPTION
    WHEN OTHERS THEN

      l_sqlerr := SQLCODE;

      /* This record is really the same as an existing one, update it. */
      /* -20028 error means that the pax will be marked for deletion */
      IF l_sqlerr =  -20028 THEN
     
        UPDATE pax
        SET delete_marker = 0
        WHERE flight_id = fltid
        AND pax_sequence_no = v_sequence_no
        RETURNING pax_id INTO v_rpid;

        l_sqlerr := 0; --reset handled error
        added_pax := 0; --A pax was not added

      ELSE
        RAISE;
      END IF;

  END;

  IF (v_rpid > 0) THEN

    BEGIN

      --Only add tagged bags
      IF tagged_bags = 'Y' THEN

        add_bag (
                tag,
                tag1,
                tag2a,
                tag2b,
                tag3,
                v_rpid,
                atldcs,
                l_dest,
                source_indicator,
                p_bag_exception,
                proc,
                unit,
                dcsid,
                subunit,
                dcstagno,
                uldno,
                conn_type,
                btype,
                l_location,
                l_user_id,
                scs,  /* CA 134 Add Security Clearance Status */
                deliv_instr,  /* Delivery instructions */
                wt_ref,
                p_kg_weight,
                p_bvis_subhead
               );

	  Set_Misc_Exceptions(tag2a, tag2b, tag3, v_rpid, p_bag_exception);

      END IF;

    EXCEPTION
      WHEN OTHERS THEN
        /* catch application error -20028 (bag already exists so update) 
           raised from TI_BAG trigger */

        l_sqlerr := SQLCODE;

        IF l_sqlerr =  -20028 THEN

          depair := get_offset.find_airport_code(fltid, v_rpid);

          UPDATE bag
          SET delete_marker = 0,
              unassociated_bag      = 'N',
              authority_to_load_dcs = atldcs,
              bag_destination       = l_dest,
              bag_exception         = p_bag_exception,
              bag_source_indicator  = source_indicator,
              time_bag_stored       = SYSDATE + get_offset.airport_code(depair),
              process_name          = proc,
              unit_name             = unit,
              dcsid                 = dcsid,
              subunit_name          = subunit,
              dcs_bag_tag           = dcstagno,
              connection_type       = conn_type,
              delivery_instr        = deliv_instr,
              worldtracer_ref       = wt_ref,
              kg_weight             = p_kg_weight,
              bvis_subheader        = p_bvis_subhead
          WHERE  bag_tag3 = tag3
          AND  ( bag_tag2a = tag2a OR bag_tag2b = tag2b )
          AND    pax_id = v_rpid
          AND    bag_exception = p_bag_exception ;

        ELSE  -- Propagate the exception
          RAISE;
        END IF;

    END;

    IF added_pax != 1 THEN
      --Need to increment bag count.
      UPDATE pax
      SET no_of_bags = no_of_bags + nobags
      WHERE pax_id = v_rpid;

    END IF;

  END IF;

  rpid := v_rpid;

END add_unnamed_pax_and_excep_bag;

/*********************************************************************************
*********************************************************************************/
PROCEDURE get_bag_count_for_crewcrew (
  p_fltid           IN     flight.flight_id%TYPE,
  p_crew_bag_count     OUT number
)
AS

BEGIN

  p_crew_bag_count := 0;

  --Now get the count of crew bags
  BEGIN

    SELECT nvl(sum(no_of_bags), 0)
    INTO p_crew_bag_count
    FROM pax
    WHERE flight_id = p_fltid     
    AND pax_sequence_no = '997'       
    AND delete_marker = 0;

  EXCEPTION
      WHEN NO_DATA_FOUND THEN
        p_crew_bag_count := 0;
  END;

END get_bag_count_for_crewcrew;

/*********************************************************************************
*********************************************************************************/
PROCEDURE add_pax_and_excep_bag
  (p_location_code      IN      location.location_code%TYPE
  ,p_user_id            IN      tagtrak_users.user_id%TYPE
  ,p_serial_no          IN      kit.serial_number%TYPE
  ,p_flight_id          IN      flight.flight_id%TYPE
  ,p_bag_tag            IN      bag.bag_tag%TYPE
  ,p_credential         IN      carrier_lookup.pax_cred%type   -- PAX_SEQUENCE_NO  or PAX_SECURITY_NO   or (PAX_SEAT_NO)
  ,p_credential_value   IN      CHAR
  ,p_exception          in      bag.bag_exception%TYPE
  ,p_pax_destination    IN      pax.pax_destination%TYPE
  ,p_pax_initials       IN      pax.pax_initials%TYPE
  ,p_pax_surname        IN      pax.pax_surname%TYPE
  ,p_booked_flight_id   IN      pax.booked_flight_id%TYPE
  ,p_conn_type          IN      pax.connection_type%TYPE
  ,p_embassy            in      pax.embassy%TYPE
  ,p_seat_no            in      pax.pax_seat_no%TYPE
  ,p_deliv_instr        in      bag.delivery_instr%TYPE
  ,p_additional_bags    in      number
  ,p_wt_ref             IN      bag.worldtracer_ref%TYPE
  ,p_pax_id             IN OUT  pax.pax_id%TYPE
  ,p_kg_weight          IN      bag.kg_weight%TYPE
  ,p_bvis_subhead       IN      bag.bvis_subheader%TYPE
  ,p_error_message      OUT     error_message%TYPE
  ,p_error_number       OUT     error_number%TYPE
  ) AS

  v_user_id             tagtrak_users.user_id%TYPE;
  v_flight_id           flight.flight_id%TYPE;
  v_pax_surname         pax.pax_surname%TYPE;
  l_pax_id              pax.pax_id%TYPE;

  n_bag_tag             bag.bag_tag%TYPE;
  l_bag_tag             bag.bag_tag%TYPE;
  v_bag_tag1            bag.bag_tag1%TYPE ;
  v_bag_tag2a           bag.bag_tag2a%TYPE DEFAULT '-';
  v_bag_tag2b           bag.bag_tag2b%TYPE;
  v_bag_tag3            bag.bag_tag3%TYPE;

  v_proc_name           bag.process_name%TYPE;
  v_un_name             bag.unit_name%TYPE;
  v_dcsid               bag.dcsid%TYPE;
  v_subu_name           bag.subunit_name%TYPE;

  v_credential          carrier_lookup.pax_cred%TYPE;

  v_mapped              connection_configuration.mapping%TYPE;
  l_tag_type            NUMBER;

  c_action              CONSTANT bag_trace.trace_action%TYPE := 'ADD GATE';

  pax_seq_no            pax.pax_sequence_no%TYPE;

  v_retval              NUMBER;
  -- Tracking variables
  l_event_count         NUMBER;
  l_no_of_errs          NUMBER;
  support_tag_crew_bags CHAR(1);

  v_dcs_bag_tag         bag.dcs_bag_tag%TYPE;
  v_dm                  bag.delete_marker%TYPE;
  v_bag_exception       bag.bag_exception%TYPE;
  source_indicator      bag.bag_source_indicator%TYPE   := 'L';   --  add_excep_bags only gets called from MWS and Scanner so must be Local

  CURSOR get_bag_flt_details_csr (fid     NUMBER, 
                                  b2b     CHAR, 
                                  b3      NUMBER, 
                                  depcode CHAR) IS
  SELECT b.delete_marker,
         b.dcs_bag_tag,
         b.bag_exception
  FROM bag b, pax p, flight f
  WHERE f.flight_id = fid
  AND f.flight_id = p.flight_id
  AND p.pax_id = b.pax_id
  AND b.bag_tag2b = b2b
  AND b.bag_tag3  = b3
  AND b.bag_source_location = depcode;

  depair      pax.departure_airport_code%TYPE := NULL;
 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  *                                                                                   *
  * PURPOSE                                                                           *
  *                                                                                    *
  * Add a bag to a flight                                                              *
  *                                                                                    *
  * PROCESS                           METHOD     TYPE                                  *
  *                                                                                       *
  * Check flight_status using post_departure                  Function   Internal         *
  * Validate the bag tag using valid_bag_tag                  Function   Internal          *
  * Validate the flight destination using valid_flight_destination   Function   Internal   *
  * Obtain the passenger credentials using get_gate_pax_creds        Procedure  External   *
  * Validate the passenger credentials using credentials_ok          Function   Local      *
  * Add a gate bag using mws_add_gate_pax_and_bag             Procedure  External         *
  * Maintain traceability using add_bag_trace_record          Procedure  External         *
  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
BEGIN

  v_flight_id := p_flight_id;
  v_pax_surname := p_pax_surname;
  p_error_number := 0;

  BEGIN

    v_credential := TRIM(p_credential);

    --Change surname to NULL if input from MWS or Scanner is a '-' (Dash)
    IF v_pax_surname = '-' THEN

      v_pax_surname := NULL;

    END IF;

    IF post_departure(p_flight_id) THEN

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

    END IF;

    /*Originally this took the DCS from the flight table.  Functionally this
      may be dubious as the bag is almost more unknown (in that a DCS has not
      told us about it).  Hence this was changed during the BNS design to use
      the SYM DCS (BAGFLO), which is reserved for unknown bags.  It also has
      the happy side-effect that we can determine all MWS created bags, as
      manually created bags also use BAGFLO.*/
     
    SELECT process_name, unit_name, subunit_name, dcsid
	  INTO v_proc_name, v_un_name, v_subu_name, v_dcsid
	  FROM system_cfg 
    WHERE subunit_name = 'SYM' 
    AND process_name = 'DCS';

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      /* UNKNOWN FLIGHT */
      error_number := -139;
      error_message := 'unknown flight specified';
      RAISE abort;
  END;

  IF p_conn_type IS NOT NULL
  AND NOT is_connection_valid (p_conn_type, v_mapped) THEN
  
    error_number := -1069;
    error_message := 'Connection type is invalid';
    RAISE abort;
  
  END IF;
  
  process := 'get_excep_pax_creds : ';

  /* check if a  cred_value and surname are given that the pax in db for 
     that cred value has the the same surname */
  IF nvl(p_pax_id,0)  > 0 AND NOT credentials_ok (p_flight_id,
                                                  v_credential,
                                                  p_credential_value,
                                                  v_pax_surname) THEN
  
    error_number := -1045;
    error_message := 'credential value  does not belong to the passenger ' 
                     ||v_pax_surname||'; ';
    RAISE abort;

  END IF;

  --Split the tags to get tag1, tag2a, tag2b, tag3, tagno and tag_type
  process := 'check  tags : ';

  --IF NOT valid_bag_tag(l_bag_tag) THEN    from MWS_WEB
  validate_bag_tag (p_bag_tag,l_bag_tag,n_bag_tag,error_number);

  IF error_number <> 0 THEN

    error_message := 'bag_tag ' || p_bag_tag ||' is invalid';
    RAISE invalid_bag_tag_format;

  END IF;

  split_tags (n_bag_tag, v_bag_tag1, v_bag_tag2a, v_bag_tag2b, v_bag_tag3, l_bag_tag, l_tag_type, 0);

  dbms_output.put_line('n_bag_tag '||n_bag_tag);
  dbms_output.put_line('l_bag_tag '||l_bag_tag);
  dbms_output.put_line('bag_tag1 '||v_bag_tag1);
  dbms_output.put_line('bag_tag2a '||v_bag_tag2a);
  dbms_output.put_line('bag_tag2b '||v_bag_tag2b);
  dbms_output.put_line('bag_tag3 '||v_bag_tag3);

  process := 'check bag type : ';

  IF l_tag_type < 0 THEN

    error_number := pkg_constants.ERROR_INVALID_BAG_TAG_FORMAT ;
    error_message := 'bag_tag '|| n_bag_tag ||' is invalid';
    RAISE invalid_bag_tag_format;

  END IF;

  depair := get_offset.find_airport_code(p_flight_id, NULL);

/*
If determine_security_status is going to set the status to C due to batch_log entries 
then generate a 'CLEAR XRAY' trace beforehand. The WHERE clause of the statement 
below must fetch identical result to the query on batch_log in 
determine_security_status(). The query is not multi site aware. This is because it 
should exactly match the query used by determine_security_status(). At a later stage 
if the query in determine_security_status() is made multi site aware then this query 
will need to be amended as well. A direct insert is made to bag_trace table rather 
than using add_bag_trace_record() because it populates current timestamp in 
both bag_trace_time and action_time fields. The intention is to populate action_time 
field with the time when the bag was pre-cleared rather than current timestamp. This 
is consistent with the way trace records for unknown bags are processed when the bags 
become known (see trigger tu_unknown_bag in unknown_bag_triggers.sql). A better 
solution can be implemented in future if core_batch_update.pc is migrated to PL/SQL.
This will ensure that bags added via MWS handle batch_log entries in a manner identical 
to bags created via BSMs. This will also make the batch_log query in 
determine_security_status redundant.
*/
  INSERT INTO bag_trace
    (pax_id,
     bag_trace_time,
     action_time,
     user_id,
     location_code,
     error_id,
     trace_action,
     scanner_id,
     bag_tag,
     bag_tag1,
     bag_tag2a,
     bag_tag2b,
     bag_tag3,
     flight_id_scan,
     uld_no,
     airport_code)
    (SELECT p_pax_id,
            SYSDATE + get_offset.airport_code(depair),
            b.scan_time,
            b.user_id,
            b.location_code,
            0,
            'CLEAR XRAY',
            b.scanner_id,
            n_bag_tag,
            v_bag_tag1,
            v_bag_tag2a,
            v_bag_tag2b,
            v_bag_tag3,
            b.flight_id,
            NULL,
            multi_site.get_user_loc(b.user_id)
     FROM batch_log b
     WHERE b.FUNCTION = 'CLEAR XRAY'
     AND b.tag3 = v_bag_tag3
     AND (b.tag2b = NVL (NVL (v_bag_tag2b, alpha_to_numeric(trim(v_bag_tag2a))),'-'))
     AND NOT EXISTS (SELECT 'x'
                     FROM bag, pax
                     WHERE bag_tag3 = v_bag_tag3
                     AND (bag_tag2b = NVL(v_bag_tag2b, '-')
                          OR bag_tag2a  = NVL(v_bag_tag2a, '-'))
                     AND  bag.pax_id = pax.pax_id
                     AND  pax.flight_id = p_flight_id));

  --Check for a duplicate bag
  dbms_output.put_line('check dup tag start ');

  OPEN get_bag_flt_details_csr (p_flight_id, v_bag_tag2b, v_bag_tag3, depair);
  LOOP
    
    FETCH get_bag_flt_details_csr INTO v_dm, v_dcs_bag_tag, v_bag_exception;
    EXIT when get_bag_flt_details_csr%NOTFOUND;

    dbms_output.put_line('In loop - tag is <'||v_dcs_bag_tag||'>');
    dbms_output.put_line('dm is <'||v_dm||'>');
    dbms_output.put_line('exception is <'||v_bag_exception||'>');

    /*If bag is not marked as deleted and an exception bag already exists, then 
      fail with a duplicate error. */
    IF p_exception = v_bag_exception AND v_dm = 0 THEN
      
      error_number := pkg_constants.ERROR_UNRESOLVABLE_DUP_TAG;
      error_message := 'exception bag tag ' || v_dcs_bag_tag || ' already exists';
      RAISE abort;
      
    END IF;

  END LOOP;

  dbms_output.put_line('check dup tag end ');
  CLOSE get_bag_flt_details_csr;

  IF error_number <> 0 THEN

    dbms_output.put_line('error_number ='||error_number);
    RAISE abort;

  END IF;

  dbms_output.put_line('pax surname='||trim(nvl(v_pax_surname,'')) ||';');
  dbms_output.put_line('  11='|| nvl(p_pax_id,0)  ||';');
  dbms_output.put_line('  21='||  nvl(P_CREDENTIAL_VALUE,'-') ||';');

  IF (nvl(p_pax_id, 0) = 0 
  AND nvl(P_CREDENTIAL_VALUE, '-') = '-' 
  AND v_pax_surname is null ) THEN

    /******************************************************************************/
    /*************                 Un - Named  PAX                    *************/
    /******************************************************************************/

    process := 'add_unnamed_pax_and_excep_bag  : ';
    dbms_output.put_line('process  '||process);

    add_unnamed_pax_and_excep_bag (v_flight_id,
                                   p_exception,
                                   l_bag_tag,
                                   v_bag_tag1,
                                   v_bag_tag2a,
                                   v_bag_tag2b,
                                   v_bag_tag3,
                                   p_additional_bags,
                                   'Y',
                                   p_pax_destination,
                                   source_indicator,
                                   v_proc_name,
                                   v_un_name,
                                   v_dcsid,
                                   v_subu_name,
                                   l_bag_tag,            -- dcs_tagno
                                   p_conn_type,
                                   NULL,
                                   p_location_code,
                                   p_user_id,
                                   '-',
                                   p_deliv_instr,
                                   p_wt_ref,
                                   p_kg_weight,
                                   p_bvis_subhead,
                                   l_pax_id);
                                   
    IF l_pax_id < 0 THEN

      error_number := l_pax_id;
      SELECT error_description 
      INTO error_message
      FROM error 
      WHERE error_id = error_number;
      RAISE abort;

    ELSE

      p_pax_id := l_pax_id;

    END IF;

  ELSE

    /******************************************************************************/
    /*************                      Named  PAX                    *************/
    /******************************************************************************/

    /* if CREW Exception and  untagged crew bags supported
        always call add_unnamed_pax_and_excep_bag*/

    IF ( p_exception = 'CREW' ) THEN
        
      /******************************************************************************/
      /*************            CREW      Named  PAX                    *************/
      /******************************************************************************/
      process := ' CREW bag exception   : ';
      dbms_output.put_line('process  '||process);
      -- Check whether we are allowed to use tagged bags or not
      -- Obtain tagged crew bags value for flight id.
      carrier_details.GET_TAGGED_CREW_BAG (p_flight_id ,support_tag_crew_bags,v_retval);
       
      IF v_retval < 0 THEN
        
        error_number := v_retval;
        error_message := 'credentials do not belong to this passenger';
        RAISE abort;
          
      END IF;
       
    END IF;

    IF   p_exception = 'CREW' AND support_tag_crew_bags = 'N' THEN
      /******************************************************************************/
      /*************          UNTAGGED   CREW   BAGS                    *************/
      /******************************************************************************/
      process := 'add_unnamed_pax_and_excep_bag for CREW  ';
      dbms_output.put_line('process  '||process);

      add_unnamed_pax_and_excep_bag (v_flight_id,
                                     p_exception,
                                     l_bag_tag,
                                     v_bag_tag1,
                                     v_bag_tag2a,
                                     v_bag_tag2b,
                                     v_bag_tag3,
                                     p_additional_bags,
                                     'Y',
                                     p_pax_destination,
                                     source_indicator,
                                     v_proc_name,
                                     v_un_name,
                                     v_dcsid,
                                     v_subu_name,
                                     l_bag_tag,          -- dcs_tagno
                                     p_conn_type,
                                     NULL,
                                     p_location_code,
                                     p_user_id,
                                     '-',
                                     p_deliv_instr,
                                     p_wt_ref,
                                     p_kg_weight,
                                     p_bvis_subhead,
                                     l_pax_id);
                                     
      IF l_pax_id < 0 THEN

        error_number := l_pax_id;
        SELECT error_description 
        INTO error_message
        FROM error 
        WHERE error_id = error_number;
        RAISE abort;

      ELSE

        p_pax_id := l_pax_id;

      END IF;
                                       
    ELSE
      
      /******************************************************************************/
      /*************           ADD NAMED  EXCEPTION   PAX  and  BAG     *************/
      /******************************************************************************/

      process := ' add as named exception    : ';
      dbms_output.put_line('process  '||process);
      dbms_output.put_line('flight_id  '||p_flight_id);
      dbms_output.put_line('dest  '||p_pax_destination);

      IF NOT valid_flight_destination (p_flight_id, p_pax_destination) THEN
        
        error_number := pkg_constants.ERROR_WRONG_DESTINATION;
        error_message :=  'flight_destination '
                          || p_pax_destination ||
                          ' is invalid';
        RAISE abort;
          
      END IF;

      /* If the pax already exists add the gate bags to this pax, otherwise 
         create this pax to add the gate bag to. If the pax_id = 0
         means that the pax is uknown. */
      IF nvl(p_pax_id,'0')  = '0' THEN

        process := 'add_named_pax : ';
        dbms_output.put_line('process  '||process);

        add_named_pax (p_flight_id,
                       p_pax_initials,
                       v_pax_surname,
                       v_credential,
                       p_credential_value,
                       p_exception,
                       p_embassy,
                       p_pax_destination,
                       p_seat_no,
                       p_flight_id,
                       p_conn_type,
                       error_number);

        dbms_output.put_line(' add_named_pax pax_id = '||p_pax_id);
        IF error_number > 0 THEN
          p_pax_id := error_number;
          error_number := 0;
        ELSE
          error_message :=  ' unable to add_named_pax for '||p_credential_value ;
          RAISE abort;
        END IF;

      ELSE
        /* PAX already exists. Now ensure exception bag is not attached to a 
           normal bag. */
        dbms_output.put_line('pax already exists');
        dbms_output.put_line('pax is '||p_pax_id);

        IF is_exception_pax ( p_pax_id, p_exception ) = FALSE THEN
                   
          error_number := pkg_constants.ERROR_EXCEPTION_BAG;
          error_message := 'cannot add exception bag to normal passenger';
          RAISE abort;
            
        END IF;
          
      END IF;

      --pax_id will now exists
      process := 'add_named_bag : ';
      dbms_output.put_line('process  '||process);

      add_named_bag (p_flight_id,
                     p_pax_id,
                     p_exception,
                     n_bag_tag,
                     v_bag_tag1,
                     v_bag_tag2a,
                     v_bag_tag2b,
                     v_bag_tag3,
                     p_pax_destination,
                     v_proc_name,
                     v_un_name,
                     v_dcsid,
                     v_subu_name,
                     p_conn_type,
                     p_deliv_instr,
                     p_location_code,
                     p_user_id,
                     p_wt_ref,
                     p_kg_weight,
                     p_bvis_subhead,
                     error_number);
                       
      dbms_output.put_line(' add_named_bag error_number = '||error_number);
       
    END IF;  --   end named pax NOT untagged crew bags

  END IF;   --   end named pax

  process := 'start add_bag_trace : ';
  dbms_output.put_line('process  '||process);

  IF error_number < 0 THEN

    error_message :=  'error for flight '   || p_flight_id ||    ' - ';
    SELECT CONCAT(error_message,error_description) INTO error_message 
    FROM error WHERE error_id = error_number;
    RAISE abort;

  ELSE

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

    -- Note: 1st 0 - pax_sequence_no and 2nd 0 is group no
    tracking.auto_events(n_bag_tag, p_flight_id, NULL, p_pax_id, 0, p_user_id,
                         p_location_code, 0, l_event_count, l_no_of_errs);
  END IF;


  -- dont write a bag tarce for untagged CREW bags
  IF n_bag_tag IS NOT NULL THEN
  
    process := 'add_bag_trace : '||n_bag_tag||';';
    dbms_output.put_line('process  '||process);

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

    add_bag_trace_record(v_user_id
                        ,p_location_code
                        ,0
                        ,n_bag_tag
                        ,SUBSTR(n_bag_tag,1,1)
                        ,v_bag_tag2a
                        ,v_bag_tag2b
                        ,SUBSTR(n_bag_tag,5,6)
                        ,'ADD '||p_exception -- trace_action
                        ,0 -- scanner_id
                        ,p_flight_id
                        ,p_pax_id  -- pax_id  passed in  or been added
                        ,NULL); -- mws_web.uld_no_for_bag (n_bag_tag, p_pax_id, p_exception
  END IF;

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

EXCEPTION
  WHEN abort THEN
    -- if exception raised without setting error_number
    IF error_number >= 0 THEN
      p_error_number := -1322;
    ELSE
      p_error_number := error_number;
    END IF;
    
    p_error_message := process || error_message;
    dbms_output.put_line('exception   '||p_error_message);
    ROLLBACK;
    add_app_msg.log_msg (0,p_error_number,'exception_bags',process,p_error_message);
    
  WHEN invalid_bag_tag_format THEN
  
    p_error_number := SQLCODE;
    p_error_message := process || ' Invalid Baggage Tag format';
    dbms_output.put_line('exception   '||p_error_message);
    ROLLBACK;
    add_app_msg.log_msg (0,p_error_number,'exception_bags',process,p_error_message);
  
  WHEN OTHERS THEN
  
    dbms_output.put_line('FATAL exception   '||process||SQLCODE||SQLERRM);
    p_error_number := -502;
    p_error_message := process || error_message || '  '||SQLCODE ||SQLERRM;
    ROLLBACK;
    add_app_msg.log_oracle_error ('exception_bags.add_pax_and_excep_bag',SQLERRM,SQLCODE);
    
END add_pax_and_excep_bag;

/*********************************************************************************
*                                                                                *
*                        get_pax_credentials                                     *
*         used by tcmt                                                           *
*                                                                                *
*********************************************************************************/
PROCEDURE get_pax_credentials
(cred_value IN     credentials_lookup.pax_cred_scanner_values%TYPE,
 pax_cred      OUT credentials_lookup.pax_cred%TYPE
) AS

  v_pax_cred              carrier_lookup.pax_cred%TYPE;
  WRONG_CREDENTIAL_VALUE  EXCEPTION;
  v_cred_seat_no          NUMBER := 2;
  v_cred_sequence_no      NUMBER := 8;
  
BEGIN

  -- Obtain pax credentials from supplied scanner id value
  SELECT  TRIM(pax_cred)
  INTO     v_pax_cred
  FROM     credentials_lookup
  WHERE   pax_cred_scanner_values = cred_value;

  IF ( cred_value < v_cred_seat_no OR cred_value > v_cred_sequence_no ) THEN
    RAISE WRONG_CREDENTIAL_VALUE;
  END IF;

  pax_cred := v_pax_cred;

EXCEPTION
  WHEN NO_DATA_FOUND OR WRONG_CREDENTIAL_VALUE THEN
    oracle_error_number := SQLCODE;
    oracle_error_message := SQLERRM;

END get_pax_credentials;

/*********************************************************************************
*                                                                                *
*                          get_error_desc                                        *
*         used internally                                                        *
*                                                                                *
*********************************************************************************/
FUNCTION get_error_desc (p_error_code IN error.error_id%TYPE)
RETURN error.error_description%TYPE IS

  l_error_desc  error.error_description%TYPE;

BEGIN

  SELECT e.error_description
  INTO l_error_desc
  FROM error e
  WHERE e.error_id = p_error_code;

  RETURN l_error_desc;

EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;

END get_error_desc;

/*********************************************************************************
*                                                                                *
*                          update_bag                                            *
*         used by MWS                                                            *
*                                                                                *
*********************************************************************************/

PROCEDURE update_bag (
        p_bag_tag                 IN      bag.bag_tag%TYPE,
        p_pax_id                  IN      pax.pax_id%TYPE,
        p_flight_id               IN      flight.flight_id%TYPE,
        p_embassy                 IN      pax.embassy%TYPE,
        p_delivery_instructions   IN      bag.delivery_instr%TYPE,
        p_seat_no                 IN      pax.pax_seat_no%TYPE,
        p_surname                 IN      pax.pax_surname%TYPE,
        p_initials                IN      pax.pax_initials%TYPE,
        p_destination             IN      bag.bag_destination%TYPE,
        p_worldtracer_ref         IN      bag.worldtracer_ref%TYPE,
        p_user_id                 IN      tagtrak_users.user_id%TYPE,
        p_location                IN      location.location_code%TYPE,
        p_kg_weight               IN      bag.kg_weight%TYPE,
        p_error_no                   OUT  error.error_id%TYPE,
        p_error_message              OUT  error_message%TYPE ) IS


  internal_error    EXCEPTION;

  l_tag1            bag.bag_tag1%TYPE;
  l_tag2a           bag.bag_tag2a%TYPE;
  l_tag2b           bag.bag_tag2b%TYPE;
  l_tag3s           VARCHAR2(6);
  l_tagno           bag.bag_tag%TYPE;
  l_tag_type        NUMBER;
  l_exception_type  bag.bag_exception%TYPE;
  l_generic_pax     NUMBER;
  l_pax_bag_count   NUMBER;
  l_user_id         tagtrak_users.user_id%TYPE;
  l_action          bag_trace.trace_action%TYPE;

BEGIN

  -- Updates some details against a specific pax and bag
  -- Used to update Exception Bag details via MWS
  --
  -- Restrictions:
  --      Embassy               - Diplomatic bags only
  --      Delivery Instructions - Rush bags only
  --      Seat Number           - Diplomatic bags only
  --      Surname               - Named Paxes only
  --      Initials              - Names Paxes only
  --      Destination           - Only updates Bag destination
  --      World Tracer Ref      - Rush bags only

  -- Initialise
  p_error_no          := pkg_constants.SUCCESS;
  p_error_message := NULL;

  -- Split the Bag Tag
  split_tags(p_bag_tag, l_tag1, l_tag2a, l_tag2b, l_tag3s, l_tagno, l_tag_type, 0);
  IF (l_tag_type < 2 OR l_tag_type > 5) THEN

    p_error_no := pkg_constants.ERROR_INVALID_BAG_TAG_FORMAT;
    RAISE internal_error;

  END IF;

  -- Validate given details
  BEGIN

    SELECT COUNT(*)
    INTO l_pax_bag_count
    FROM bag b,
         pax p,
         flight f
    WHERE b.pax_id     = p.pax_id
    AND p.flight_id  = f.flight_id
    AND (b.bag_tag2a = l_tag2a OR b.bag_tag2b = l_tag2b)
    AND b.bag_tag3   = TO_NUMBER(l_tag3s)
    AND p.pax_id     = p_pax_id
    AND f.flight_id  = p_flight_id;

    IF (l_pax_bag_count > 1) THEN

      p_error_no := pkg_constants.ERROR_UNRESOLVABLE_DUP_TAG;
      RAISE internal_error;

    END IF;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN

      p_error_no := pkg_constants.ERROR_UNKNOWN_BAG;
      RAISE internal_error;

  END;

  -- Determine Exception Type
  SELECT b.bag_exception
  INTO   l_exception_type
  FROM   bag b
  WHERE  (b.bag_tag2a = l_tag2a OR b.bag_tag2b = l_tag2b)
  AND  b.bag_tag3   = TO_NUMBER(l_tag3s)
  AND  b.pax_id     = p_pax_id;

  -- Is this a Generic Pax?
  SELECT DECODE(p.pax_surname, 'RUSHRUSH', 1, 'CREWCREW', 1, 'MAILMAIL', 1, 0)
  INTO   l_generic_pax
  FROM   pax p
  WHERE  p.pax_id    = p_pax_id
  AND  p.flight_id = p_flight_id;

  -- Update Bag Table
  UPDATE bag b
  SET b.delivery_instr = DECODE(l_exception_type, 'RUSH', p_delivery_instructions, b.delivery_instr),
      b.bag_destination = p_destination,
      b.worldtracer_ref = p_worldtracer_ref,
      b.kg_weight = p_kg_weight
  WHERE (b.bag_tag2a = l_tag2a OR b.bag_tag2b = l_tag2b)
  AND b.bag_tag3 = TO_NUMBER(l_tag3s)
  AND b.pax_id = p_pax_id;

  -- Update Pax Table
  UPDATE pax p
  SET p.embassy      = DECODE(l_exception_type, 'DIPL', p_embassy, p.embassy),
      p.pax_seat_no  = DECODE(l_exception_type, 'DIPL', p_seat_no, p.pax_seat_no),
      p.pax_surname  = DECODE(l_generic_pax, 0, p_surname, p.pax_surname),
      p.pax_initials = DECODE(l_generic_pax, 0, p_initials, p.pax_initials)
  WHERE p.pax_id    = p_pax_id
  AND p.flight_id = p_flight_id;

  -- Add Trace Record
  l_user_id := p_user_id;
  l_action  := 'UPD ' || TRIM(l_exception_type);
  add_bag_trace_record(l_user_id, p_location, pkg_constants.SUCCESS, p_bag_tag, l_tag1, l_tag2a, l_tag2b,
                       TO_NUMBER(l_tag3s), l_action, 0, p_flight_id, p_pax_id, NULL);

  -- Done
  COMMIT;

  RETURN;

EXCEPTION
  WHEN internal_error THEN

    p_error_message := get_error_desc(p_error_no);
    ROLLBACK;
    RETURN;

  WHEN OTHERS THEN

    p_error_no          := SQLCODE;
    p_error_message := SQLERRM;
    ROLLBACK;

END update_bag;


END exception_bags;
/

