/******************************************************************************
*                        (c) Ultra Electronics Limited, 2006
*                       All rights reserved
*
*******************************************************************************
*
* Filename:                 inbound_flight.sql
* Originating Author:       Peter Charalambous
* Design Reference:         UAS/FALCP/SS/1940
* Process:                  Inbound Flight Processing
*
*******************************************************************************
* Description
*               This package contains the main inbound flight processing which
*               includes the following functions: COPY_PAX; COPY_ULD; COPY_BAG
*               and COPY_NON_ULD_BAGS
*
*******************************************************************************/

prompt create or replace package INBOUND_FLIGHT

CREATE OR REPLACE package INBOUND_FLIGHT is
  /*********************************************************************/
  /* Variable Declarations                                             */
  /*********************************************************************/
  v_blank_hold      VARCHAR2(6) := '      ';  -- value to use when the hold posn is unknown
  v_blank_conn_type VARCHAR2(6) :='      ';
  v_match_window    NUMBER      := (15/60/24);-- time difference allowed when matching inbound
                                              -- and outbound flight information


  /* Exception Handling */
  uld_already_exists  EXCEPTION;


  /*********************************************************************/
  /* TYPE Declarations                                                 */
  /*********************************************************************/

   TYPE inbound_pax_onward_dests_r IS RECORD
   (
      inbound_pax_id         inbound_pax_onward_dests.inbound_pax_id%TYPE,
      destination_sequence   inbound_pax_onward_dests.destination_sequence%TYPE,
      onward_flight_no       inbound_pax_onward_dests.onward_flight_no%TYPE,
      onward_flight_date     inbound_pax_onward_dests.onward_flight_date%TYPE,
      onward_destination     inbound_pax_onward_dests.onward_destination%TYPE,
      onward_class           inbound_pax_onward_dests.onward_class%TYPE
   );

   TYPE inbound_pax_onward_dests_t IS TABLE OF inbound_pax_onward_dests_r                         INDEX BY BINARY_INTEGER;
   TYPE inbound_pax_id_t           IS TABLE OF inbound_pax_onward_dests.inbound_pax_id%TYPE 	    INDEX BY BINARY_INTEGER;
   TYPE destination_sequence_t     IS TABLE OF inbound_pax_onward_dests.destination_sequence%TYPE INDEX BY BINARY_INTEGER;
   TYPE onward_flight_no_t         IS TABLE OF inbound_pax_onward_dests.onward_flight_no%TYPE 	  INDEX BY BINARY_INTEGER;
   TYPE onward_flight_date_t       IS TABLE OF inbound_pax_onward_dests.onward_flight_date%TYPE 	INDEX BY BINARY_INTEGER;
   TYPE onward_destination_t       IS TABLE OF inbound_pax_onward_dests.onward_destination%TYPE 	INDEX BY BINARY_INTEGER;
   TYPE onward_class_t             IS TABLE OF inbound_pax_onward_dests.onward_class%TYPE 	      INDEX BY BINARY_INTEGER;
   TYPE bags_t                     IS TABLE OF bag%ROWTYPE;

  /*********************************************************************/
  /* COPY_BAGS                                                         */
  /*********************************************************************/
  /* Description:                                                      */
  /* Procedure to copy all bags from an inbound flight to an outbound  */
  /* through flight, provided that the bag has no exception, the bag   */
  /* has not already been transferred and the passenger exists on the  */
  /* outbound flight. The aim of this procedure is to copy the bags    */
  /* which have not already been copied as part of a through ULD.      */
  /*********************************************************************/
  PROCEDURE copy_bags
  (p_in_flightid  IN NUMBER
  ,p_out_flightid IN NUMBER
  ,p_uld_no       IN VARCHAR2
  ,p_user_id      IN bag_trace.user_id%TYPE
  ,p_process_type IN VARCHAR2);

  /*********************************************************************/
  /* COPY_PAX                                                          */
  /*********************************************************************/
  /* Description:                                                      */
  /* Procedure to copy passengers from one flight to another           */
  /*********************************************************************/
  PROCEDURE copy_pax
  ( p_in_flightid  IN NUMBER
   ,p_out_flightid IN NUMBER
   ,p_uld_no       IN inbound_uld.result_uld_no %TYPE DEFAULT NULL
   ,p_process_type IN VARCHAR2);

  /*********************************************************************/
  /* COPY_ULD                                                          */
  /*********************************************************************/
  /* Description:                                                      */
  /* Procedure to copy the details of a ULD and its baggage from one   */
  /* flight to another.                                                */
  /*********************************************************************/
  PROCEDURE copy_uld (
      p_in_flightid     IN NUMBER
     ,p_out_flightid    IN NUMBER
     ,p_uld_no          IN ulds.uld_no%TYPE
     ,p_hold_no         IN VARCHAR2
     ,p_user_id         IN VARCHAR2
     ,p_loc             IN VARCHAR2
     ,p_reallocate_flag IN VARCHAR2
     ,p_process_type    IN VARCHAR2
     ,p_return_code     OUT NUMBER
     ,p_posn_result     OUT NUMBER);

  /*********************************************************************/
  /* COPY_NON_ULD_BAGS                                                 */
  /*********************************************************************/
  /* Description:                                                      */
  /* Procedure to copy all bags from an inbound flight to an outbound  */
  /* through flight, provided that the bag has no exception, the bag   */
  /* has not already been transferred and the passenger exists on the  */
  /* outbound flight. The aim of this procedure is to copy the bags    */
  /* which have not already been copied as part of a through ULD.      */
  /*********************************************************************/
  PROCEDURE copy_non_uld_bags (
    p_in_flightid  IN NUMBER
   ,p_out_flightid IN NUMBER
   ,p_process_type IN VARCHAR2);


  /*********************************************************************/
  /* CREATE_ONWARD_FLIGHT_DETAILS                                      */
  /*********************************************************************/
  /* Description:                                                      */
  /* Procedure to create onward_flight details for a specific pax      */
  /*********************************************************************/
  PROCEDURE create_onward_flight_details (
      p_inbound_pax_id  IN pax.pax_id%TYPE
     ,p_outbound_pax_id IN pax.pax_id%TYPE);

  /*********************************************************************/
  /* CHECK_PAX_SEQUENCE_NO                                             */
  /*********************************************************************/
  /* Description:                                                      */
  /* This checks to ensure a valid pax_sequenec_no exists. If the      */
  /* pax_sequence_no is not in the range of 1 to 999, then a new       */
  /* pax_sequenec_no is to be generated.                               */
  /*********************************************************************/
 FUNCTION check_pax_sequence_no
  (p_in_flight_id IN flight.flight_id%TYPE
  ,p_seqno        IN pax.pax_sequence_no%TYPE) RETURN pax.pax_sequence_no%TYPE;

 /*********************************************************************/
 /* CHECK_LOADALL_PAX                                                 */
 /*********************************************************************/
 /* Description:                                                      */
 /* Checks for the existence of a loadall pax, returns the loadall pax*/
 /* otherwise returns the pax_id = 0                                  */
 /*********************************************************************/
  FUNCTION check_loadall_pax
   (p_flight_id IN flight.flight_id%TYPE ) RETURN pax.pax_id%TYPE;

 /*********************************************************************/
 /* GET_CARRIER_SITE_CONFIG                                           */
 /*********************************************************************/
 /* Description:                                                      */
 /* This gets the specific carrier_site_config value                  */
 /*********************************************************************/
  FUNCTION get_carrier_site_config
   ( p_site_id IN carrier_site_config.site_id%TYPE
    ,p_carrier IN carrier_site_config.alpha_code%TYPE
    ,p_item    IN carrier_site_config.config_item%TYPE ) RETURN carrier_site_config.config_value%TYPE;

 /*********************************************************************/
 /* CREATE_BAG_ALERT                                                  */
 /*********************************************************************/
 /* Description:                                                      */
 /* This routine creates a bag alert, checks initially to see whether */
 /* it exists prior to creation. If the bag alert already exist then  */
 /* timing information is updated and also an UNLOAD bag alert is     */
 /* override by an UNKNOWN bag alert, which has higher precedence.    */
 /*********************************************************************/

 PROCEDURE create_bag_alert ( p_flight_id           IN NUMBER
                             ,p_bag_tag             IN bag.bag_tag%TYPE
                             ,p_bag_tag1            IN bag.bag_tag1%TYPE
                             ,p_bag_tag2a           IN bag.bag_tag2a%TYPE
                             ,p_bag_tag2b           IN bag.bag_tag2b%TYPE
                             ,p_bag_tag3            IN bag.bag_tag3%TYPE
                             ,p_pax_id              IN pax.pax_id%TYPE
                             ,p_bag_alert_to_create IN VARCHAR2
                             ,p_airport_code        IN site_info.airport_code%TYPE);


 /*********************************************************************/
 /* CHECK_ADD_ULD_DESTS_RECORD                                        */
 /* Description:                                                      */
 /* This routine checks whether a uld_dests record has already been   */
 /* created and adds it if it hasn't                                  */
 /*********************************************************************/
 PROCEDURE check_add_uld_dests_record ( p_uld_no        IN CHAR
	                                     ,p_dest          IN CHAR
	                                     ,p_class         IN CHAR
	                                     ,p_flt_id        IN NUMBER
                                       ,p_onwd_flt_no   IN VARCHAR2
                                       ,p_onwd_flt_date IN VARCHAR2);

end INBOUND_FLIGHT;
/

CREATE OR REPLACE package body INBOUND_FLIGHT AS

/*********************************************************************/
/* CHECK_FOR_UNAPPROVED_DUPLICATES                                   */
/*********************************************************************/
/* Description:                                                      */
/* This routine checks whether a given bag tag is a duplicate for a  */
/* flight and sets security screening required if duplicates are     */
/* found and any of them are inbound from an unapproved airport      */
/*********************************************************************/
PROCEDURE check_for_unapproved_duplicate ( p_out_flightid flight.flight_id%TYPE,
                                            p_bag_tag2b    bag.bag_tag2b%TYPE,
                                            p_bag_tag3     bag.bag_tag3%TYPE
)
AS

  l_dup_vals        NUMBER;
  l_origin_approved CHAR;
  l_bags_insecure   BOOLEAN := FALSE;

  CURSOR c_dup_tags IS
    SELECT p.inbound_boarding_point
    FROM   bag b,
           pax p
    WHERE  p.pax_id        = b.pax_id
      AND  p.flight_id     = p_out_flightid
      AND  b.bag_tag3      = p_bag_tag3
      AND  b.bag_tag2b     = p_bag_tag2b
      AND  b.delete_marker = 0;

BEGIN

  /***********************************************/
  /* Is this bag tag a duplicate on this flight? */
  /***********************************************/
  SELECT COUNT(*)
  INTO   l_dup_vals
  FROM   bag b,
         pax p
  WHERE  p.pax_id        = b.pax_id
    AND  p.flight_id     = p_out_flightid
    AND  b.bag_tag3      = p_bag_tag3
    AND  b.bag_tag2b     = p_bag_tag2b
    AND  b.delete_marker = 0;

  IF (l_dup_vals > 1) THEN

    /**************************************************************/
    /* This is a duplicate bag tag - check whether any are from a */
    /* non-approved airport                                       */
    /**************************************************************/
    FOR r_bag IN c_dup_tags LOOP

      /*********************************************/
      /* Is this pax inbound from another airport? */
      /*********************************************/
      IF (r_bag.inbound_boarding_point IS NOT NULL) THEN

        /***********************************************************************/
        /* This bag is inbound - check the originating airport security status */
        /***********************************************************************/
        BEGIN

          SELECT si.approved_security_status
          INTO   l_origin_approved
          FROM   site_info si
          WHERE  si.airport_code = r_bag.inbound_boarding_point;

          /*****************************/
          /* Is this airport insecure? */
          /*****************************/
          IF (l_origin_approved = 'N') THEN

            l_bags_insecure := TRUE;

          END IF;

        EXCEPTION
          WHEN no_data_found THEN
            l_origin_approved := 'N';
            l_bags_insecure   := TRUE;
        END;

      END IF;

    END LOOP;

    /******************************************************************/
    /* Did we find a duplicate bag inbound from an unapproved airport */
    /******************************************************************/
    IF (l_bags_insecure = TRUE) THEN

      /*********************************/
      /* Update all the duplicate tags */
      /*********************************/
      UPDATE bag b
      SET    b.security_clearance_status = 'N'
      WHERE  b.bag_tag3   = p_bag_tag3
        AND  b.bag_tag2b  = p_bag_tag2b
        AND  b.security_clearance_status = 'U'
        AND  b.delete_marker = 0
        AND  b.pax_id IN (SELECT p.pax_id
                          FROM   pax p
                          WHERE  p.flight_id = p_out_flightid);

    END IF;

  END IF;

END check_for_unapproved_duplicate;


/*********************************************************************/
/* CHECK_ADD_ULD_DESTS_RECORD                                        */
/*********************************************************************/
/* Description:                                                      */
/* This routine checks whether a uld_dests record has already been   */
/* created and adds it if it hasn't                                  */
/*********************************************************************/
PROCEDURE check_add_uld_dests_record ( p_uld_no        IN CHAR
	                                    ,p_dest          IN CHAR
	                                    ,p_class         IN CHAR
	                                    ,p_flt_id        IN NUMBER
                                      ,p_onwd_flt_no   IN VARCHAR2
                                      ,p_onwd_flt_date IN VARCHAR2
)
AS

  l_exists NUMBER                                   := 0;
  l_uld_no uld_dests.uld_no%TYPE                    := p_uld_no;
  l_dest   uld_dests.uld_destination%TYPE           := p_dest;
  l_class  uld_dests.uld_class_list%TYPE            := p_class;
  l_flt_id uld_dests.flight_id%TYPE                 := p_flt_id;
  l_onwd_flt_no uld_dests.onward_flight_no%TYPE     := p_onwd_flt_no;
  l_onwd_flt_date uld_dests.onward_flight_date%TYPE := p_onwd_flt_date;
BEGIN

  /************************************************************************/
  /* Before creating the record check to see whether the uld_dests record */
  /* Already exists or not. If it does then do nothing                    */
  /************************************************************************/

  SELECT count(uld_no)
  INTO   l_exists
  FROM   uld_dests ud
  WHERE  ud.uld_no             = l_uld_no
  AND    ud.uld_destination    = l_dest
  AND    ud.uld_class_list     = l_class
  AND    ud.flight_id                 = l_flt_id
  AND    nvl(ud.onward_flight_no,0)   = rtrim(nvl(l_onwd_flt_no,0))
  AND    nvl(ud.onward_flight_date,0) = rtrim(nvl(l_onwd_flt_date,0));

  /* Create the uld_dests only if it does exist */
  IF (l_exists = 0) THEN
	   /* insert record into uld_dests */
	  add_uld_dests_record (l_uld_no
                         ,l_dest
                         ,l_class
                         ,l_flt_id
                         ,l_onwd_flt_no
                         ,l_onwd_flt_date);
  END IF;
  EXCEPTION
     WHEN OTHERS THEN
        add_app_msg.log_oracle_error('INBOUND_FLIGHT.CHECK_ADD_ULD_DESTS_RECORD',SQLERRM,SQLCODE);
        RAISE;

END check_add_uld_dests_record;

/*********************************************************************/
/* CREATE_BAG_ALERT                                                  */
/*********************************************************************/
/* Description:                                                      */
/* This routine creates a bag alert, checks initially to see whether */
/* it exists prior to creation. If the bag alert already exist then  */
/* timing information is updated and also an UNLOAD bag alert is     */
/* override by an UNKNOWN bag alert, which has higher precedence.    */
/*********************************************************************/
 PROCEDURE create_bag_alert ( p_flight_id           IN NUMBER
                             ,p_bag_tag             IN bag.bag_tag%TYPE
                             ,p_bag_tag1            IN bag.bag_tag1%TYPE
                             ,p_bag_tag2a           IN bag.bag_tag2a%TYPE
                             ,p_bag_tag2b           IN bag.bag_tag2b%TYPE
                             ,p_bag_tag3            IN bag.bag_tag3%TYPE
                             ,p_pax_id              IN pax.pax_id%TYPE
                             ,p_bag_alert_to_create IN VARCHAR2
                             ,p_airport_code        IN site_info.airport_code%TYPE)
 IS
  l_unknown_bag_alert   NUMBER := 0;
  l_unload_bag_alert    NUMBER := 0;
  l_bag_alert_exists    bag_alert.alert_on_action%TYPE := NULL;
  l_bag_alert_to_create bag_alert.alert_on_action%TYPE := p_bag_alert_to_create;

  p_out_flightid        NUMBER                         := p_flight_id;
  l_home_airport        site_info.airport_code%TYPE    := p_airport_code;

 BEGIN

    /***************************************************************************/
    /* Check to see whether a bag alert has been created. If it's an UNKNOWN   */
    /* bag alert this takes precedence over any UNLOAD bag alerts.             */
    /* Update the existing bag_alert with timing information.                  */
    /* If bag alert doesn't already exist, then create it. Again UNKNOWN bag   */
    /* alerts take precedence over UNLOAD ones.                                */
    /***************************************************************************/

    -- check whether bag alert exists or not, should only return one record if exists
    -- one per bag, pre flight per pax
    BEGIN
       SELECT alert_on_action
       INTO   l_bag_alert_exists
       FROM   bag_alert
       WHERE (bag_tag2a       = p_bag_tag2a OR bag_tag2b = p_bag_tag2b)
       AND    bag_tag3        = p_bag_tag3
       AND    pax_id          = p_pax_id
       AND    flight_id       = p_out_flightid;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
       l_bag_alert_exists := NULL;
    END;

    /* Update and override the bag alert if an UNKNOWN bag alert is to be created */
    IF l_bag_alert_exists = 'UNLOAD' AND l_bag_alert_to_create = 'UNKNOWN' THEN
       -- update the bag alert
       UPDATE bag_alert
       SET    alert_on_action = 'UNKNOWN', last_time_stamp = SYSDATE
       WHERE (bag_tag2a       = p_bag_tag2a OR bag_tag2b = p_bag_tag2b)
       AND    bag_tag3        = p_bag_tag3
       AND    pax_id          = p_pax_id
       AND    flight_id       = p_out_flightid;
    ELSIF l_bag_alert_exists = 'UNKNOWN'  AND (l_bag_alert_to_create = 'UNKNOWN' OR
                                               l_bag_alert_to_create = 'UNLOAD')
          OR
          l_bag_alert_exists = 'UNLOAD' AND l_bag_alert_to_create = 'UNLOAD'  THEN
     -- update the bag alert with timing information
       UPDATE bag_alert
       SET    last_time_stamp = SYSDATE
       WHERE (bag_tag2a       = p_bag_tag2a OR bag_tag2b = p_bag_tag2b)
       AND    bag_tag3        = p_bag_tag3
       AND    pax_id          = p_pax_id
       AND    flight_id       = p_out_flightid;
    ELSIF  l_bag_alert_to_create IS NOT NULL THEN  -- create the bag alert as new, since doesn't already exist
       INSERT INTO bag_alert
                  (pax_id,
                   no_warnings,
                   bag_tag2b,
                   bag_tag3,
                   last_time_stamp,
                   alert_on_action,
                   bag_tag,
                   bag_tag2a,
                   bag_tag1,
                   flight_id,
                   airport_code)
       VALUES     (p_pax_id,
                  '1',
                   p_bag_tag2b,
                   p_bag_tag3,
                   sysdate,
                   l_bag_alert_to_create,
                   p_bag_tag,
                   p_bag_tag2a,
                   p_bag_tag1,
                   p_out_flightid,
                   l_home_airport);
    END IF;

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

/*********************************************************************/
/* GET_CARRIER_SITE_CONFIG                                           */
/*********************************************************************/
/* Description:                                                      */
/* This gets the specific carrier_site_config value                  */
/*********************************************************************/
  FUNCTION get_carrier_site_config
   ( p_site_id IN carrier_site_config.site_id%TYPE
    ,p_carrier IN carrier_site_config.alpha_code%TYPE
    ,p_item    IN carrier_site_config.config_item%TYPE ) RETURN carrier_site_config.config_value%TYPE
   IS
   l_config_value carrier_site_config.config_value%TYPE := 0;
  BEGIN
     SELECT config_value
     INTO   l_config_value
     FROM   carrier_site_config
     WHERE  site_id = p_site_id
     AND    alpha_code = p_carrier
     AND    config_item = p_item;

     RETURN(l_config_value);
  EXCEPTION

    WHEN No_Data_Found THEN
      RETURN NULL;

  END get_carrier_site_config;
/*********************************************************************/
/* CREATE_ONWARD_FLIGHT_DETAILS                                      */
/*********************************************************************/
/* Description:                                                      */
/* Procedure to create onward_flight details for a specific pax      */
/*********************************************************************/

  PROCEDURE create_onward_flight_details (
      p_inbound_pax_id  IN pax.pax_id%TYPE
     ,p_outbound_pax_id IN pax.pax_id%TYPE)
  IS
     l_inbound_pax_id_t         inbound_pax_id_t   ;
     l_destination_sequence_t   destination_sequence_t;
     l_onward_flight_no_t       onward_flight_no_t;
     l_onward_flight_date_t     onward_flight_date_t;
     l_onward_destination_t     onward_destination_t;
     l_onward_class_t           onward_class_t;
     v_onward_flight            onward_flight_defs.onwd_flt_t;
  BEGIN

     SELECT  inbound_pax_id
           ,destination_sequence -1
           ,onward_flight_no
           ,onward_flight_date
           ,onward_destination
           ,onward_class
     BULK COLLECT INTO
            l_inbound_pax_id_t
           ,l_destination_sequence_t
           ,l_onward_flight_no_t
           ,l_onward_flight_date_t
           ,l_onward_destination_t
           ,l_onward_class_t
     FROM inbound_pax_onward_dests onw
     WHERE inbound_pax_id = p_inbound_pax_id
     AND   onw.destination_sequence > 1;

     /************************************************************/
     /* Onward Flight Process                                    */
     /************************************************************/
     /* set up values for adding onward flights for outbound pax */
     /************************************************************/
     FOR v_index IN 1..l_inbound_pax_id_t.count
     LOOP
         v_onward_flight(v_index).no          := l_onward_flight_no_t(v_index);
         v_onward_flight(v_index).odate       := l_onward_flight_date_t(v_index);
         v_onward_flight(v_index).dest        := l_onward_destination_t(v_index);
         v_onward_flight(v_index).dest_order  := l_destination_sequence_t(v_index);
         v_onward_flight(v_index).class       := l_onward_class_t(v_index);
     END LOOP;

     /* Call procedure to add it */
     add_onward_flight(p_outbound_pax_id,v_onward_flight);
  EXCEPTION
     WHEN OTHERS THEN
          add_app_msg.log_oracle_error('INBOUND_FLIGHT.CREATE_ONWARD_FLIGHT_DETAILS(1)',SQLERRM,SQLCODE);
          RAISE;
  END create_onward_flight_details;

/*********************************************************************/
/* CHECK_LOADALL_PAX                                           */
/*********************************************************************/
/* Description:                                                      */
/* This checks to ensure a valid pax_sequenec_no exists. If the      */
/* pax_sequence_no is not in the range of 1 to 999, then a new       */
/* pax_sequenec_no is to be generated.                               */
/*********************************************************************/
  FUNCTION check_loadall_pax
   (p_flight_id IN flight.flight_id%TYPE ) RETURN pax.pax_id%TYPE
   IS
   l_loadall_pax_id pax.pax_id%TYPE := 0;
  BEGIN
     SELECT pax_id
     INTO   l_loadall_pax_id
     FROM   pax
     WHERE  flight_id = p_flight_id
     AND    pax_sequence_no = -1;

     RETURN(l_loadall_pax_id);
  EXCEPTION
     WHEN OTHERS THEN
        RETURN(l_loadall_pax_id);
  END check_loadall_pax;

/*********************************************************************/
/* CHECK_PAX_SEQUENCE_NO                                             */
/*********************************************************************/
/* Description:                                                      */
/* This checks to ensure a valid pax_sequenec_no exists. If the      */
/* pax_sequence_no is not in the range of 1 to 999, then a new       */
/* pax_sequenec_no is to be generated.                               */
/*********************************************************************/
 FUNCTION check_pax_sequence_no
  (p_in_flight_id IN flight.flight_id%TYPE
  ,p_seqno        IN pax.pax_sequence_no%TYPE) RETURN pax.pax_sequence_no%TYPE
  IS
  l_seqno pax.pax_sequence_no%TYPE;
  BEGIN

      /* check to ensure it is in the range of 1 to 999*/
      IF (p_seqno BETWEEN 1 and 999) THEN
          RETURN(p_seqno);
      ELSE
          make_sequence_no(booked_fid =>p_in_flight_id ,seqno=>l_seqno);
          RETURN(l_seqno);
      END IF;
  END check_pax_sequence_no;

/********************************************************************
* Module Name:          copy_bags                                   *
* Subsystem Name:       stored_procs                                *
*********************************************************************
*                                                                   *
* Description:                                                      *
*  Procedure to copy all of the bags in a specified ULD from one    *
*     flight to another, recording a trace for each bag             *
*********************************************************************/

PROCEDURE copy_bags
  ( p_in_flightid  IN NUMBER
   ,p_out_flightid IN NUMBER
   ,p_uld_no       IN VARCHAR2
   ,p_user_id      IN bag_trace.user_id%TYPE
   ,p_process_type IN VARCHAR2)
IS
  l_out_flight_no     flight.flight_no%TYPE;
  l_out_flight_date   flight.flight_date%TYPE;
  l_in_flight_no      flight.flight_no%TYPE;
  l_in_flight_date    flight.flight_date%TYPE;
  l_site_id           site_info.site_id%TYPE;
  l_home_airport      site_info.airport_code%TYPE;
  l_origin_airport    site_info.airport_code%TYPE;
  l_user_id           bag_trace.user_id%TYPE;
  l_return_code       NUMBER;    --result returned by the bag_load function
  l_in_flightid       NUMBER;
  l_out_flightid      NUMBER;
  l_uld_no            ulds.uld_no%TYPE;
  l_class             pax.pax_class%TYPE;
  l_error_message     mws_web.error_msg_type;
  l_pax_next_dest     onward_flight.dest%TYPE;
  l_is_through_flight BOOLEAN := FALSE;
  l_bag               bag%ROWTYPE := NULL;
  l_process_action    bag_trace.trace_action%TYPE;

  l_config_value      carrier_site_config.config_value%TYPE;
  l_authorise_through_bag carrier_site_config.config_item%TYPE := NULL;

  l_out_carrier       carrier_site_config.alpha_code%TYPE;
  l_security_screening VARCHAR2(4);
  l_security_status   bag.security_clearance_status%TYPE;
  l_loadall_pax_id    pax.pax_id%TYPE := 0;

  l_hbmd_value        VARCHAR2(1) := 'Y'; -- default hbmd_value to 'Y'. Through Flight set to 'N'
  l_result_bag_status inbound_bag.result_bag_status%TYPE := 'CREATED';
  l_loadall_pax       VARCHAR2(1); --are we loading under the LOADALL pax?
  l_retcode           NUMBER := 0;
  l_process_type      inbound_pax.inbound_pax_status%TYPE := upper(p_process_type); -- THROUGH OR TRANSFER


  -- alert types
  l_unload_bag_alert  NUMBER;

  l_abandon BOOLEAN;

  /* Get the inbound_bag and inbound_pax details of bags which have been loaded */
  CURSOR b1 IS
    SELECT inbound_bag.*
          ,nvl(inbound_pax.result_pax_id,0) o_pax_id
          ,inbound_pax.pax_source    o_pax_source
          ,inbound_pax.pax_status    o_pax_status
    FROM   inbound_bag_v1 inbound_bag
          ,inbound_pax_v1 inbound_pax
          ,inbound_uld_v1 inbound_uld
    WHERE  inbound_pax.inbound_flight_id = p_in_flightid
    AND    inbound_uld.inbound_uld_id    = inbound_bag.inbound_uld_id
    AND    inbound_uld.inbound_flight_id = inbound_pax.inbound_flight_id
    AND    inbound_bag.inbound_pax_id    = inbound_pax.inbound_pax_id
    AND    inbound_bag.result_bag_status = 'UNPROCESSED'
    AND    inbound_bag.uld_no            = p_uld_no;



  /***********************************************************************************/
  /* Cursor to check whether the bag already exists to determine whether to add_bag  */
  /* or reuse_bag to modifuy and update existing details.                            */
  /***********************************************************************************/
  CURSOR c_check_bag_exists ( p_tag2b     IN bag.bag_tag2b%TYPE
                             ,p_tag3      IN bag.bag_tag3%TYPE
                             ,p_pax_id    IN bag.pax_id%TYPE
                             ,p_flight_id IN pax.flight_id%TYPE) IS
   SELECT bag.*
   FROM   bag, pax
        WHERE  bag.bag_tag3      = p_tag3
        AND    bag.bag_tag2b     = p_tag2b
        AND    bag.pax_id        = p_pax_id
		    AND    pax.flight_id     = p_flight_id
        AND    pax.pax_id        = bag.pax_id
        AND    bag.delete_marker = 0;
BEGIN

  /***********************************************************/
  /* Copy the parameters into local variables so that they   */
  /* can be used as IN/OUT parameters in existing procedures */
  /***********************************************************/
  l_in_flightid  := p_in_flightid;
  l_out_flightid := p_out_flightid;
  l_uld_no       := p_uld_no;
  l_user_id      := p_user_id;



  /***********************************************************/
  /* Get the airport the outward flight will depart from,    */
  /* and the flight no and date                              */
  /***********************************************************/
  SELECT site_info.airport_code, flight.site_id, flight.flight_no, flight.flight_date
  INTO l_home_airport, l_site_id, l_out_flight_no, l_out_flight_date
  FROM site_info, flight
  WHERE flight.site_id = site_info.site_id
   AND  flight_id = l_out_flightid;

  /***********************************************************/
  /* Get the flight no and date of the inbound flight and    */
  /* get the originating airport and its approval state      */
  /***********************************************************/
  SELECT flight.flight_no, flight.flight_date
  INTO l_in_flight_no, l_in_flight_date
  FROM flight
  WHERE flight_id = l_in_flightid;

  SELECT si.airport_code
  INTO l_origin_airport
  FROM site_info si
  WHERE si.site_id = inbound_flight_utils.get_inbound_orig_site(l_in_flightid);

  /* Set the outbound carrier  */
  l_out_carrier := substr(l_out_flight_no,1,3);
  /* Is this a THROUGH flight? */
  l_is_through_flight := inbound_flight_utils.is_through(p_object_type => 'FLIGHT'
                                                        ,p_flight_id   => l_in_flightid);

  /**************************************************************************/
  /* if this is not a through flight set the hbmd processing to 'N'         */
  /* through bags in a through ULD will not be included in HBMD processing  */
  /**************************************************************************/
  IF (l_is_through_flight) THEN
     l_hbmd_value:='N';
  END IF;

  /* remember. cannot load duplicate bags onto loadall pax */
  /* need to check for this */

  FOR c_inbound_bag in b1 LOOP
  BEGIN
     /* Initialise alert flags */
     l_unload_bag_alert    := 0;
     l_loadall_pax         := 'N';

     /* build the config item value, for through flight processing ONLY */
     l_authorise_through_bag := 'AUTH_'||c_inbound_bag.o_pax_source||'_THROUGH';
     /* get carrier site config */
     l_config_value := Nvl(get_carrier_site_config(l_site_id      -- outbound site_id
                                              ,l_out_carrier  -- outbound carrier
                                              ,l_authorise_through_bag), 'N'); -- whether COPY/BMM
     /* determine security screening */
     determine_security_status(p_flt_no               => l_out_flight_no      -- outbound flight no
                              ,p_flt_date             => l_out_flight_date    -- outbound flight date
                              ,p_bag_tag              => c_inbound_bag.bag_tag
                              ,p_bsi                  => 'T'                  -- transfer/through flights
                              ,p_airport_code         => l_home_airport
                              ,p_carrier              => l_out_carrier        -- outbound carrier from the outbound flight
                              ,p_bag_exception        => c_inbound_bag.bag_exception
                              ,p_inbound_airport_code => l_origin_airport     -- inbound airport_code from the inbound_flight
                              ,p_security_screening   => l_security_screening -- default is NULL, from BSM .X
                              ,p_security_status      => l_security_status    -- returned security_status
                              ,p_retcode              => l_retcode);          -- error codes

     /* check to see whether o_pax_id is LOADALL PAX or NOT , l_loadall_pax = 'Y' for loadall pax */
      BEGIN
          SELECT 'Y', pax_class,pax_id
          INTO   l_loadall_pax, l_class, l_loadall_pax_id
          FROM   pax
          WHERE  flight_id = p_out_flightid
          AND    pax_id = c_inbound_bag.o_pax_id
          AND    pax_sequence_no = -1;
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
            IF (c_inbound_bag.o_pax_id = 0) THEN
               l_loadall_pax := 'Y';
            END IF;
            -- no loadall pax yet, add_loaded_bag will create one, hence need to set flag
            -- to create loadall pax bag alert
          WHEN OTHERS THEN
            add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_BAGS - loadall pax',SQLERRM,SQLCODE);
            raise;
      END;

	  /* If a loadall pax exists, assign this to the o_pax_id */
      IF (l_loadall_pax_id != 0 AND c_inbound_bag.o_pax_id = 0) THEN
         c_inbound_bag.o_pax_id := l_loadall_pax_id;
        /********************************************************************************/
        /* Update the inbound_pax table with the new_pax_id -> result_pax_id            */
        /********************************************************************************/

		BEGIN
        UPDATE inbound_pax
        SET    result_pax_id     = c_inbound_bag.o_pax_id -- needed for new loadall pax created
              ,error_code        = 0                      -- error status OK
              ,result_pax_status  = 'AMENDED'             -- new loadall pax always AMENDED
        WHERE  inbound_pax_id    = c_inbound_bag.inbound_pax_id
        AND    inbound_flight_id = p_in_flightid;
        EXCEPTION
              WHEN OTHERS THEN
              raise;
        END;
      END IF;

      /* Update the pax table set the inbound_boarding_point of the outbound flight */
	  UPDATE pax
      SET   inbound_flight_no      = l_in_flight_no,
            inbound_date           = l_in_flight_date,
            inbound_boarding_point = l_origin_airport
      WHERE flight_id = p_out_flightid
      AND   pax_id    = c_inbound_bag.o_pax_id ;

      /*
       * Set pax_status to cleared if AUTH_BMM_THROUGH or AUTH_COPY_THROUGH
       * configuration set to 'Y'
       */
      IF ( l_config_value = 'Y' AND l_process_type = 'THROUGH')
      THEN
          UPDATE pax
          SET    pax_status = 'C'
          WHERE  flight_id = p_out_flightid
          AND    pax_id    = c_inbound_bag.o_pax_id ;
      END IF;


    /*************************************************************************/
    /* Before adding the bag, check to see whether it already exists. If it  */
    /* does then resuse it, otherwise create a new bag.                      */
    /* Duplicates?                                                           */
    /*************************************************************************/
    l_bag := NULL;
    OPEN  c_check_bag_exists( c_inbound_bag.bag_tag2b
                             ,c_inbound_bag.bag_tag3
                             ,c_inbound_bag.o_pax_id
                             ,p_out_flightid);
    FETCH c_check_bag_exists INTO l_bag; -- get the existing bag details
    CLOSE c_check_bag_exists;

    /*************************************************************************/
    /* if this is a loadall pax or the bag does not exist yet                */
    /* get the next destination of the pax, if they do not have one then     */
    /* use the old destination ( ie. the home airport since its a terminating*/
    /*************************************************************************/

	IF  (l_loadall_pax = 'Y' OR l_bag.bag_tag IS NULL) THEN
        IF c_inbound_bag.bag_destination != l_home_airport THEN
          -- through passenger, leave the destination as it is
          l_pax_next_dest := c_inbound_bag.bag_destination;
        ELSE
          -- get the next destination of the passenger, if they don't have one, (a terminating bag)
          -- then use the old destination (i.e. the home airport)
          BEGIN
            SELECT dest
            INTO l_pax_next_dest
            FROM onward_flight
            WHERE pax_id     = c_inbound_bag.pax_id AND
                  dest_order = 1;
          EXCEPTION
            WHEN OTHERS THEN
             l_pax_next_dest := c_inbound_bag.bag_destination;
          END;
        END IF;
    END IF;

    /*************************************************************************/
    /* If the bag exists and it is authority_to_load_dcs = 'Y'; load the bag */
    /*************************************************************************/
    /* OR if the bag exists and it's authority_to_load_dcs = 'N'; load the   */
    /* bag; raise an unload bag alert type.                                  */
    /*************************************************************************/
    IF (l_bag.bag_tag IS NOT NULL) THEN

        /* Set the bag status to amended, since it already exists */
        l_result_bag_status := 'AMENDED';

      BEGIN
		mws_web.mws_load_a_bag ( c_inbound_bag.location_code,
                             l_user_id,
                             p_out_flightid, -- load the bag onto the outbound flight
                             l_uld_no,
                             c_inbound_bag.bag_tag,
                             0,
                             l_error_message,
                             l_return_code);
          l_abandon := FALSE;
      EXCEPTION
        WHEN OTHERS THEN
          l_abandon := TRUE;
      END;

        /* This is a duplicate tag, doesn't matter load it anyway */
		  IF l_return_code = '-1003' OR l_abandon THEN
                     load_bag ( c_inbound_bag.bag_tag2a,
                                c_inbound_bag.bag_tag2b,
                                c_inbound_bag.bag_tag3,
                                c_inbound_bag.o_pax_id,
                                l_uld_no,
                                c_inbound_bag.location_code,
                                '0',
                                p_out_flightid,
                                l_return_code);
        END IF;

        -- problem when trying to auto-load. Raise UNLOAD alert
        IF l_return_code < 0 OR l_abandon THEN
           l_unload_bag_alert := 1;
        END IF;

        IF (l_bag.authority_to_load_dcs = 'N') then
             unauthorised_bags.add_unauthorised_bag
                                   (p_bag_tag     => c_inbound_bag.bag_tag
                                   ,p_bag_tag1    => c_inbound_bag.bag_tag1
                                   ,p_bag_tag2a   => c_inbound_bag.bag_tag2a
                                   ,p_bag_tag2b   => c_inbound_bag.bag_tag2b
                                   ,p_bag_tag3    => c_inbound_bag.bag_tag3
                                   ,p_flight_id   => p_out_flightid
                                   ,p_pax_id      => c_inbound_bag.o_pax_id
                                   ,p_error_id    => 0
                                   ,p_user_id     => l_user_id
					     ,p_location	  => c_inbound_bag.location_code);

             /************************************************************************************/
             /* Raise an UNLOAD Bag Alert Type since for THROUGH/TRANSFER ULD Processing         */
             /* the ATL/config = 'N' and so the bag would need offloaded/Unloaded since its an   */
             /* unknown bag.                                                                     */
             /************************************************************************************/
           l_unload_bag_alert := 1;
        END IF;

		/* Create an inbound transfer bag trace record */
        add_bag_trace_record (l_user_id,
                              c_inbound_bag.location_code,
                              '0',
                              c_inbound_bag.bag_tag,
                              c_inbound_bag.bag_tag1,
                              c_inbound_bag.bag_tag2a,
                              c_inbound_bag.bag_tag2b,
                              c_inbound_bag.bag_tag3,
                              'CIN_TR_BAG',
                              l_user_id,
                              p_out_flightid,
                              c_inbound_bag.o_pax_id,
                              l_uld_no);
    /*******************************************************************************/
    /* else if the outbound bag does not exist, then if transfer processing        */
    /* generated an unknown bag; if carrier_site_config.authorise_bmm_through_bags */
    /* set to 'Y' then add bag as loaded with authority_to_load_dcs set to 'Y'     */
    /* otherwise generated unauthorised_bag; add bag as loaded; raise an unload bag*/
    /* alert.                                                                      */
    /*******************************************************************************/
    ELSIF (l_bag.bag_tag IS NULL)  THEN

          /* Set the bag status to created, since it doesn't exist */
          l_result_bag_status := 'CREATED';

          /***********************************************************/
          /* Only for TRANSFER ULD Processing, create an unknown bag */
          /***********************************************************/
		  IF (l_process_type = 'TRANSFER') THEN
             /* Create an unknown bag */
             unauthorised_bags.add_unknown_bag( c_inbound_bag.bag_tag
                                               ,c_inbound_bag.bag_tag1
                                               ,c_inbound_bag.bag_tag2a
                                               ,c_inbound_bag.bag_tag2b
                                               ,c_inbound_bag.bag_tag3
                                               ,p_out_flightid, 0
                                               ,0, l_user_id, null, null);
             /************************************************************************************/
             /* Raise an UNLOAD Bag Alert Type since for THROUGH/TRANSFER ULD Processing         */
             /* the ATL/config = 'N' and so the bag would need offloaded/Unloaded since its an   */
             /* unknown bag.                                                                     */
             /************************************************************************************/
             l_unload_bag_alert := 1;
          END IF;
          /***************************************************************/
          /* Generate an unknown bag ONLY for TRANSFER ULD processing    */
          /* If AUTHORISE_<COPY/BMM>_THROUGH_BAGS for the carrier = 'Y'  */
          /* then create the new bag with ATL = 'Y'                      */
          /***************************************************************/
		  IF (l_process_type = 'THROUGH' AND l_config_value = 'Y') THEN

          /* For Through flight processing, add the bag as loaded, ATL = 'Y' */
           add_loaded_bag ( l_uld_no,
                         c_inbound_bag.bag_tag,
                         c_inbound_bag.bag_tag1,
                         c_inbound_bag.bag_tag2a,
                         c_inbound_bag.bag_tag2b,
                         c_inbound_bag.bag_tag3,
                         c_inbound_bag.o_pax_id,
                         p_out_flightid,
                         l_pax_next_dest,
                         l_class,
                         c_inbound_bag.location_code,
                         c_inbound_bag.scanner_id,
                         l_return_code);

           /* Create an inbound through bag trace record */

			add_bag_trace_record (l_user_id,
                                  c_inbound_bag.location_code,
                                  '0',
                                  c_inbound_bag.bag_tag,
                                  c_inbound_bag.bag_tag1,
                                  c_inbound_bag.bag_tag2a,
                                  c_inbound_bag.bag_tag2b,
                                  c_inbound_bag.bag_tag3,
                                  'CIN_TH_BAG',
                                  l_user_id,
                                  p_out_flightid,
                                  c_inbound_bag.o_pax_id,
                                  l_uld_no);

            /***********************************************************************/
            /* Update the bag security_status                                      */
            /***********************************************************************/
            UPDATE bag
              SET    authority_to_load_dcs = 'Y'
              WHERE  pax_id    = c_inbound_bag.o_pax_id
              AND    bag_tag   = c_inbound_bag.bag_tag
              AND    bag_tag2a = c_inbound_bag.bag_tag2a
              AND    bag_tag2b = c_inbound_bag.bag_tag2b
              AND    Nvl(authority_to_load_dcs, 'N') = 'N';

            UPDATE bag
              SET    security_clearance_status = l_security_status
              WHERE  pax_id    = c_inbound_bag.o_pax_id
              AND    bag_tag   = c_inbound_bag.bag_tag
              AND    bag_tag2a = c_inbound_bag.bag_tag2a
              AND    bag_tag2b = c_inbound_bag.bag_tag2b;

          END IF; -- through and l_config = 'Y'

          /*************************************************************************/
          /* If AUTHORISE_<COPY/BMM>_THROUGH_BAGS for the carrier = 'N' then create*/
          /* the new bag with ATL = 'N', raise a BAG ALERT                         */
          /*************************************************************************/
          /* Transfer/Through Bags which do not have authority to load or require  */
          /* screening will generate an unauthorised bag record.                   */
          /*************************************************************************/
		   IF (l_process_type = 'THROUGH' AND l_config_value = 'N') OR
             (l_process_type = 'TRANSFER') THEN

              /*****************************************************************/
              /* Generate an unauthorised_bag (for Tranfer/Through)            */
              /* Unauthorised bags are generated for bags that do not have     */
              /* authority to load OR that require screening. An UNLOAD bag    */
              /* alert type is also created. These unauthorised bags are       */
              /* cleared when the authority is received via a BSM OR the bag is*/
              /* screened or the bag is offloaded.                             */
              /*****************************************************************/
              IF (l_process_type = 'THROUGH') then
			  unauthorised_bags.add_unauthorised_bag
                                   (p_bag_tag     => c_inbound_bag.bag_tag
                                   ,p_bag_tag1    => c_inbound_bag.bag_tag1
                                   ,p_bag_tag2a   => c_inbound_bag.bag_tag2a
                                   ,p_bag_tag2b   => c_inbound_bag.bag_tag2b
                                   ,p_bag_tag3    => c_inbound_bag.bag_tag3
                                   ,p_flight_id   => p_out_flightid
                                   ,p_pax_id      => c_inbound_bag.o_pax_id
                                   ,p_error_id    => 0
                                   ,p_user_id     => l_user_id
                                   ,p_location    => null);

                 /************************************************************************************/
                 /* Raise an UNLOAD Bag Alert Type since for THROUGH/TRANSFER ULD Processing         */
                 /* the ATL/config = 'N' and so the bag would need offloaded/Unloaded since its an   */
                 /* unknown bag.                                                                     */
                 /************************************************************************************/
                 l_unload_bag_alert := 1;
              END IF;


             /* Add the bag as loaded initially, this sets the ATL intially to 'Y' */
			 add_loaded_bag ( l_uld_no,
                         c_inbound_bag.bag_tag,
                         c_inbound_bag.bag_tag1,
                         c_inbound_bag.bag_tag2a,
                         c_inbound_bag.bag_tag2b,
                         c_inbound_bag.bag_tag3,
                         c_inbound_bag.o_pax_id,
                         p_out_flightid,
                         l_pax_next_dest,
                         nvl(l_class,'*'),
                         c_inbound_bag.location_code,
                         c_inbound_bag.scanner_id,
                         l_return_code);

             /* Create an inbound through or transfer bag trace record */

			 IF ( l_process_type = 'THROUGH' )
             THEN
                 l_process_action := 'CIN_TH_BAG';
             ELSE
                 l_process_action := 'CIN_TR_BAG';
             END IF;

             add_bag_trace_record (l_user_id,
                                   c_inbound_bag.location_code,
                                   '0',
                                   c_inbound_bag.bag_tag,
                                   c_inbound_bag.bag_tag1,
                                   c_inbound_bag.bag_tag2a,
                                   c_inbound_bag.bag_tag2b,
                                   c_inbound_bag.bag_tag3,
                                   l_process_action,
                                   l_user_id,
                                   p_out_flightid,
                                   c_inbound_bag.o_pax_id,
                                   l_uld_no);

              /* Reset the authority to load dcs to 'N' and the bag's security */
              UPDATE bag
              SET    authority_to_load_dcs = 'N'
              WHERE  pax_id    = c_inbound_bag.o_pax_id
              AND    bag_tag   = c_inbound_bag.bag_tag
              AND    bag_tag2a = c_inbound_bag.bag_tag2a
              AND    bag_tag2b = c_inbound_bag.bag_tag2b
              AND    authority_to_load_dcs = 'Y';

              UPDATE bag
              SET    security_clearance_status = l_security_status
              WHERE  pax_id    = c_inbound_bag.o_pax_id
              AND    bag_tag   = c_inbound_bag.bag_tag
              AND    bag_tag2a = c_inbound_bag.bag_tag2a
              AND    bag_tag2b = c_inbound_bag.bag_tag2b;

           END IF;

    END IF;

    /*************************************************************************/
    /* This is a loadall pax then create an UNLOAD bag alert, create a trace*/
    /*************************************************************************/

	IF (l_loadall_pax = 'Y') THEN
     l_unload_bag_alert := 1;
     add_bag_trace_record (l_user_id,
                           c_inbound_bag.location_code,
                           '-1010',    -- unknown passenger
                           c_inbound_bag.bag_tag,
                           c_inbound_bag.bag_tag1,
                           c_inbound_bag.bag_tag2a,
                           c_inbound_bag.bag_tag2b,
                           c_inbound_bag.bag_tag3,
                           'UNKNOWN',
                           l_user_id,
                           p_out_flightid,
                           c_inbound_bag.o_pax_id,
                           l_uld_no);
    END IF;

    /***************************************************************/
    /* Check whether this bag and its duplicates require screening */
    /***************************************************************/
    check_for_unapproved_duplicate(p_out_flightid, c_inbound_bag.bag_tag2b, c_inbound_bag.bag_tag3);

    /*************************************************************************/
    /* Need to get the existing or newly created bag.bag_id to update the    */
    /* inbound_bag table with the new fields.                                */
    /*************************************************************************/
    l_bag := NULL;
    OPEN  c_check_bag_exists( c_inbound_bag.bag_tag2b
                             ,c_inbound_bag.bag_tag3
                             ,c_inbound_bag.o_pax_id
                             ,p_out_flightid);
    FETCH c_check_bag_exists INTO l_bag;
    CLOSE c_check_bag_exists;

    -- transferred bags are included in the HBMD
    UPDATE bag
    SET
        hbmd_flag     = l_hbmd_value -- Y/N
    WHERE
        bag_tag2b     = c_inbound_bag.bag_tag2b AND
        bag_tag3      = c_inbound_bag.bag_tag3 AND
        pax_id        = c_inbound_bag.o_pax_id AND
        delete_marker = 0;

    -- set the next flight details of the inbound bags
    UPDATE bag
    SET
        next_flight_no=l_out_flight_no,
        next_flight_date=l_out_flight_date
    WHERE
        bag_tag2b     = c_inbound_bag.bag_tag2b AND
        bag_tag3      = c_inbound_bag.bag_tag3 AND
        pax_id        = c_inbound_bag.pax_id AND
        delete_marker = 0;

    /* Update the inbound_bag table with the new status fields */
    UPDATE inbound_bag
    SET
           result_bag_id     = l_bag.bag_id        -- Either NEW or EXISTING bag_id
          ,result_bag_status = l_result_bag_status -- set to CREATED (new bag, no match) AMENDED (existing bag/loadall)
          ,error_code        = 0
    WHERE  inbound_bag.inbound_bag_id = c_inbound_bag.inbound_bag_id;

    /***********************************************************/
    /* Generate Bag Alerts - first check to see whether the    */
    /* bag alert being created already exist, if it does then  */
    /* update it with timing information, otherwise create it  */
    /* alert should be raised.                                 */
    /***********************************************************/
    IF (l_unload_bag_alert = 1) THEN
       inbound_flight.create_bag_alert ( p_flight_id           => p_out_flightid
                                        ,p_bag_tag             => c_inbound_bag.bag_tag
                                        ,p_bag_tag1            => c_inbound_bag.bag_tag1
                                        ,p_bag_tag2a           => c_inbound_bag.bag_tag2a
                                        ,p_bag_tag2b           => c_inbound_bag.bag_tag2b
                                        ,p_bag_tag3            => c_inbound_bag.bag_tag3
                                        ,p_pax_id              => c_inbound_bag.o_pax_id
                                        ,p_bag_alert_to_create => 'UNLOAD'
                                        ,p_airport_code        => l_home_airport);
    END IF;

    EXCEPTION
      WHEN OTHERS THEN
        add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_BAGS - general',SQLERRM,SQLCODE);
        RAISE;
    END;  -- catch errors in this loop
  END LOOP;

END copy_bags;


/********************************************************************
* Module Name:          copy_pax                                    *
* Subsystem Name:       stored_procs                                *
*********************************************************************
*                                                                   *
* Description:                                                      *
*  Procedure to copy passengers from one flight to another          *
********************************************************************/
PROCEDURE copy_pax ( p_in_flightid  IN NUMBER
                    ,p_out_flightid IN NUMBER
                    ,p_uld_no       IN inbound_uld.result_uld_no %TYPE DEFAULT NULL
                    ,p_process_type IN VARCHAR2)
IS
     l_home_airport             site_info.airport_code%TYPE;
     l_matched_pax              pax.pax_id%TYPE := 0;
     out_pax_id                 NUMBER;
     pax_structure              pm_pax_record;
     dummy_onwd_flt_t           ONWARD_FLIGHT_DEFS.ONWD_FLT_T;

     l_in_flight_no             flight.flight_no%TYPE;
     l_in_flight_date           flight.flight_date%TYPE;
     l_origin_airport           site_info.airport_code%TYPE;
     l_pax_sequence_no          pax.pax_sequence_no%TYPE;

     l_pax_through              inbound_pax_v1.result_pax_status%TYPE;
     l_pax_dest                 inbound_pax_v1.pax_destination%TYPE;
     l_pax_class                inbound_pax_v1.pax_class%TYPE;
     l_flight_dest              flight_dests.flight_destination%TYPE;
     l_authority_to_load_dcs    pax.authority_to_load_dcs%TYPE;

     /* get db_config values */
     l_transfer_pax_processing  db_config.db_value%TYPE := config.get_value('TRANSFER_PAX_PROCESSING');
     l_bmm_inbound_pax_matching db_config.db_value%TYPE := config.get_value('BMM_INBOUND_PAX_MATCHING');
     l_transfer_pax_matching db_config.db_value%TYPE := config.get_value('TRANSFER_PAX_MATCHING');
     l_pax_matching db_config.db_value%TYPE;
     l_process_type    inbound_pax.inbound_pax_status%TYPE := upper(p_process_type); -- THROUGH OR TRANSFER

     /* initialise values */
     l_result_status   inbound_pax_v1.result_pax_status%TYPE := 'CREATED';
     l_loadall_pax     VARCHAR2(1) := 'N';
     l_wild_class      VARCHAR2(1) := '*';

     /* get the pax details regardless whether it's a through/not pax */
     /* this should get all the inbound_pax details for the inbound flight */
     CURSOR p1 IS
     SELECT inbound_pax_v1.*
     FROM  inbound_pax_v1
     WHERE ((inbound_pax_v1.inbound_flight_id = p_in_flightid
           AND   inbound_pax_v1.delete_marker = 0
           AND   inbound_pax_v1.inbound_pax_status = 'THROUGH'               -- for through flight processing
          )
     OR   ( inbound_pax_v1.inbound_pax_id IN ( SELECT ib.inbound_pax_id -- for transfer uld processing
                                              FROM  inbound_bag_v1 ib
                                                   ,inbound_uld_v1 iu
                                              WHERE ib.inbound_uld_id = iu.inbound_uld_id
                                              AND  iu.uld_no = p_uld_no
                                              AND iu.inbound_flight_id = p_in_flightid
                                              AND iu.result_uld_status IN ('CREATED','AMENDED','UNPROCESSED')
                                             )))
     AND   inbound_pax_v1.result_pax_status  = 'UNPROCESSED';

BEGIN

  /* Get the airport the outward flight will depart from */
  SELECT site_info.airport_code
  INTO l_home_airport
  FROM site_info, flight
  WHERE site_info.site_id = flight.site_id
   AND  flight.flight_id  = p_out_flightid;

  SELECT flight.flight_no, flight.flight_date
  INTO  l_in_flight_no, l_in_flight_date
  FROM  flight
  WHERE flight.flight_id  = p_in_flightid;

  SELECT site_info.airport_code
  INTO l_origin_airport
  FROM site_info
  WHERE site_info.site_id = inbound_flight_utils.get_inbound_orig_site(p_in_flightid);

  SELECT flight_dests.flight_destination
  INTO   l_flight_dest
  FROM   flight_dests
  WHERE  flight_dests.flight_id = p_out_flightid
  AND    flight_dests.dest_order = 1;


   FOR c_inbound_pax IN p1 LOOP

   /**************************************************************/
   /* Match the Inbound Paxes that have bags in a Through ULD    */
   /* or are marked as THROUGH with paxes in the Outbound Flight */
   /**************************************************************/
    IF (c_inbound_pax.inbound_pax_status <> 'TERMINATING') THEN
         pax_match.load_pax_structure( pax_structure
                                      ,p_in_flightid
                                      ,c_inbound_pax.inbound_pax_id);

         l_pax_matching := l_transfer_pax_matching;
         IF (c_inbound_pax.pax_source = 'BMM') THEN

           l_pax_matching := l_bmm_inbound_pax_matching;

         END IF;

         l_matched_pax := pax_match.perform_match( l_pax_matching
                                                  ,pax_structure
                                                  ,p_out_flightid);
    END IF;

    /**************************************************************/
    /* if there is a pax match set the result status to amended   */
    /**************************************************************/
    l_result_status := 'CREATED';
    IF (l_matched_pax > 0) THEN
       l_result_status := 'AMENDED';
       out_pax_id := l_matched_pax;
    END IF;

    /************************************************************************************/
    /* ALL Cases: If the pax did not match                                              */
    /************************************************************************************/
    /* Case 1: <CREATE PAX SCENARIO> if the pax did not match AND pax is THROUGH pax AND*/
    /*         transder_pax_processing is CREATE                                        */
    /* Result: CREATE the PAX, if there is insufficient                                 */
    /*         information or creation fails then Raise error.                          */
    /* Later:  If successful creation, in COPY_BAGS add bags to this pax and load them  */
    /************************************************************************************/
    /* Case 2: <CREATE PAX or LOADALL PAX SCENARIO> if the pax did not match AND pax is */
    /*         THROUGH/TRANSFER pax AND transfer_pax_processing is ATTEMPT              */
    /* Result: CREATE the PAX, if there is                                              */
    /*         insufficient information or creation fails                               */
    /*         then this is a LOADALL pax scenario to Add the bag to the loadall PAX    */
    /*         Set the RESULT_PAX_STATUS = AMENDED if LOADALL PAX scenario is needed.   */
    /* Later:  If successful creation, in COPY_BAGS load the bags, otherwise add the bag*/
    /*         to the LOADALL PAX, LOAD the bag and then RAISE bag alert.               */
    /************************************************************************************/
    /* Case 3: <LOADLL PAX SCENAIO> if the pax did not match AND the pax is either not  */
    /*         a THROUGH/TRANSFER PAX OR the transfer_pax_processing is set to LOADALL. */
    /* Result: This is a LOADALL PAX scenario.                                          */
    /* Later:  Add the bags to the LOADALL PAX if one exists, otherwise create it.      */
    /*         Load the BAGS and then RAISE a BAG ALERT.                                */
    /************************************************************************************/

    /* if pax did not match and the pax is a through/transfer pax and create or attempt */
    IF ((l_matched_pax = 0) AND c_inbound_pax.inbound_pax_status IN( 'THROUGH','TRANSFER') )
                            AND (l_transfer_pax_processing       = 'CREATE'
                            OR   l_transfer_pax_processing       = 'ATTEMPT') THEN

        BEGIN
        /*************************************************************************************/
        /* Creating PAX:                                                                     */
        /*************************************************************************************/
        /* 1) Check the sequence number is between the range of 1 and 999, otherwise need to */
        /*    generate a new sequence number using the make_sequence_no() routine.           */
        /*************************************************************************************/
        /* 2) Create the PAX and UPDATE the INBOUND_PAX table with the appropriate status    */
        /*    changes.                                                                       */
        /*************************************************************************************/
        /* 3) For each successful pax transfer, increment the leg number                     */
        /*************************************************************************************/
        /* 4) For unsuccessul pax creation/transfer then RAISE invalid_pax error. Update the */
        /*    inbound_pax.error_code and inbound_pax.result_pax_status = 'ERROR'.            */
        /*************************************************************************************/
        /* 5) Create any Onward Flight details of the next flight onwards i.e dest_order> 1  */
        /*************************************************************************************/

        /* Keep some values for the loadall pax, if needed */
         l_pax_through := c_inbound_pax.inbound_pax_status;
         l_pax_dest    := c_inbound_pax.pax_destination;
         l_pax_class   := c_inbound_pax.pax_class;

         /***********************************************************************/
         /* or just set a loadall_pax flag, if pax processing is set to attempt */
         /* we already know that this is not a match and its a through pax      */
         /***********************************************************************/
         IF l_transfer_pax_processing = 'ATTEMPT' THEN
            l_loadall_pax := 'Y';
         END IF;

        /* check the pax_sequence_no */
        l_pax_sequence_no := check_pax_sequence_no(p_in_flight_id =>p_out_flightid
                                                  ,p_seqno => c_inbound_pax.pax_sequence_no);

        /* Create the pax record */
        add_pax (p_out_flightid,
             p_out_flightid,
             c_inbound_pax.pax_initials,
             c_inbound_pax.pax_surname,
             c_inbound_pax.pax_status,
             0,
             c_inbound_pax.no_same_surname,
             c_inbound_pax.pax_destination,
             c_inbound_pax.pax_seat_no,
             c_inbound_pax.pax_class,
             l_pax_sequence_no,
             c_inbound_pax.pax_dcs_sequence_no,
             c_inbound_pax.pax_security_no,
             c_inbound_pax.pax_name_record,
             c_inbound_pax.pax_security_status,
             l_home_airport,
             l_in_flight_no,
             l_in_flight_date,
             l_origin_airport,
             c_inbound_pax.authority_to_load_dcs, -- for transfer ULD processing this is 'N'
             c_inbound_pax.pax_full_inits,
             c_inbound_pax.onward_flight_count,
             c_inbound_pax.connection_type,
             dummy_onwd_flt_t, /* add these later */
             c_inbound_pax.freq_flyer,
             c_inbound_pax.pax_bags_class,
             c_inbound_pax.embassy,
             out_pax_id);

	/* Now create a TRACE entries for Create Inbound Through or Transfer PAX */

        add_log_rec( 'CREATE_INBOUND_'||c_inbound_pax.inbound_pax_status||'_PAX',
                'create: PAX ID: '||out_pax_id||' on FLIGHT: '||
                p_out_flightid||' for PAX: '||c_inbound_pax.pax_initials||''||
                c_inbound_pax.pax_surname,
                NULL);

        EXCEPTION
            WHEN OTHERS THEN
                /******************************************************************************/
                /* Deal with any errors in creating the pax                                   */
                /* 1) Update the inbound_pax table with the invalid pax error                 */
                /*    for the inbound_flight.                                                 */
                /* 2) Create a LOADALL PAX if this is a loadall pax scenario                  */
                /******************************************************************************/
                 UPDATE inbound_pax
                 SET    error_code = PKG_CONSTANTS.ERROR_INVALID_PAX_ID
                       ,result_pax_status = 'ERROR'
                 WHERE  inbound_flight_id = p_in_flightid
                 AND    inbound_pax_id = c_inbound_pax.inbound_pax_id;

                 /****************************************************/
                 /* Still need to carry on processing ie. leg update */
                 /****************************************************/
                 IF (l_loadall_pax = 'Y') THEN
                     /* since loadall pax set the result_pax_status to amended */
                     l_result_status := 'AMENDED';

                     /* Check for loadall pax, assign one if found */
                     out_pax_id := check_loadall_pax(p_out_flightid);

                     /* only create the loadall pax if it does not exist */
                     IF (out_pax_id = 0) THEN
                        add_loadall_pax(fid   => p_out_flightid
                                       ,nbags => 0 -- add the bags later
                                       ,pdest => l_flight_dest -- flight_dest > 1
                                       ,pcls  => l_wild_class
                                       ,pid   => out_pax_id); --new loadall pax
                     END IF;

                 ELSE
                    add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_PAX(1)',SQLERRM,SQLCODE);
                    RAISE;
                 END IF;
        END; --end exception
    ELSIF (l_matched_pax = 0) AND ( c_inbound_pax.inbound_pax_status NOT IN ('THROUGH','TRANSFER')  -- catches TERMIINATING
                              OR l_transfer_pax_processing       = 'LOADALL') THEN
        /* set the result_status to amended, for loadedall */
        IF (l_transfer_pax_processing = 'LOADALL') THEN
            l_result_status := 'AMENDED';
        END IF;

        /* Check for loadall pax, assign one if found */
        out_pax_id := check_loadall_pax(p_out_flightid);

        /* only create the loadall pax if it does not exist */
        IF (out_pax_id = 0) THEN
           /* Create the loadall pax */
           add_loadall_pax(fid   => p_out_flightid
                          ,nbags => 0  -- add the bags later
                          ,pdest => l_flight_dest
                          ,pcls  => l_wild_class
                          ,pid   => out_pax_id);
         END IF;

    END IF;

    /********************************************************************************/
    /* If this is a transfer ULD type then set the PAX's ATL to  'N'                */
    /********************************************************************************/
    IF (inbound_flight_utils.is_transfer(p_object_type => 'PAX' -- or ULD
                                        ,p_flight_id => p_out_flightid
                                        ,p_pax_id    => out_pax_id
                                        ,p_bag_id    => NULL         -- may remove this
                                        ,p_uld_no    => p_uld_no)) THEN  -- may remove this
           l_authority_to_load_dcs := 'N';
    ELSE
           l_authority_to_load_dcs := c_inbound_pax.authority_to_load_dcs;
    END IF;

    /********************************************************************************/
    /* Now update the pax's next leg details and the inbound_pax table              */
    /********************************************************************************/
    BEGIN
    UPDATE pax
    SET    pax.leg_number = (nvl(c_inbound_pax.leg_number,0) + 1)
          ,pax.authority_to_load_dcs = l_authority_to_load_dcs
    WHERE  pax_id       = out_pax_id ;  -- could be loadall pax/normal pax
    EXCEPTION
    WHEN OTHERS THEN
      add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_PAX - update pax',SQLERRM,SQLCODE);
      raise;
    END;


    /********************************************************************************/
    /* Update the inbound_pax table with the new_pax_id -> result_pax_id            */
    /********************************************************************************/
    BEGIN
    UPDATE inbound_pax
    SET    result_pax_id     = out_pax_id      -- could be loadall pax/normal pax OR the matched pax
          ,result_pax_status = l_result_status -- CREATED for new pax/NOT MATCHED, AMENDED for LOADALL/MATCHED PAX
          ,error_code        = 0               -- error status OK
    WHERE  inbound_pax_id    = c_inbound_pax.inbound_pax_id
    AND    inbound_flight_id = p_in_flightid
    AND    result_pax_status = 'UNPROCESSED';
    EXCEPTION
      WHEN OTHERS THEN
      add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_PAX - update inbound_pax',SQLERRM,SQLCODE);
      raise;
    END;


    /************************************************************************************/
    /* Create onward flight/pax details for the outbound pax                            */
    /************************************************************************************/
    inbound_flight.create_onward_flight_details( p_inbound_pax_id => c_inbound_pax.inbound_pax_id
                                                ,p_outbound_pax_id => out_pax_id);

    END LOOP;
EXCEPTION
   WHEN OTHERS THEN
      add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_PAX - general',SQLERRM,SQLCODE);
      raise;
end COPY_PAX;


/********************************************************************
* Module Name:          copy_uld                                    *
* Subsystem Name:       stored_procs                                *
*********************************************************************
*                                                                   *
* Description:                                                      *
*  Procedure to copy the details of a ULD and its baggage from one  *
* flight to another.                                                *
********************************************************************/

PROCEDURE copy_uld (
      p_in_flightid     IN NUMBER
     ,p_out_flightid    IN NUMBER
     ,p_uld_no          IN ulds.uld_no%TYPE
     ,p_hold_no         IN VARCHAR2
     ,p_user_id         IN VARCHAR2
     ,p_loc             IN VARCHAR2
     ,p_reallocate_flag IN VARCHAR2
     ,p_process_type    IN VARCHAR2
     ,p_return_code     OUT NUMBER
     ,p_posn_result     OUT NUMBER)
IS
  l_inbound_uld      inbound_uld_v1%ROWTYPE       := NULL;
  l_connection_type  ulds.connection_type%TYPE    := NULL;
  l_out_flight_no    flight.flight_no%TYPE        := NULL;
  l_out_flight_date  flight.flight_date%TYPE      := NULL;
  l_trtime           uld_trace.flight_date%TYPE;
  l_process_action   uld_trace.trace_action%TYPE;
  l_flight_dest      flight_dests.flight_destination%TYPE := NULL;

  l_in_flightid      flight.flight_id%TYPE        := p_in_flightid;
  l_out_flightid     flight.flight_id%TYPE        := p_out_flightid;
  l_uld_no           ulds.uld_no%TYPE             := RPAD(p_uld_no,10);
  l_hold_no          ulds.uld_hold_no%TYPE        := p_hold_no;
  l_user_id          bag_trace.user_id%TYPE       := p_user_id;
  l_loc              bag_trace.location_code%TYPE := p_loc;
  l_uld_exists       NUMBER                       := 0;
  l_hold_exists      NUMBER                       := 0;
  l_create_uld       VARCHAR2(1)                  := 'N';

  l_current_airport   site_info.airport_code%TYPE;


  /* Local declarations */
  l_process_type    inbound_uld.inbound_uld_status%TYPE := upper(p_process_type); -- THROUGH OR TRANSFER
  l_dest_added      BOOLEAN := FALSE;

  /* Get the details for the outbound flight and ignore details of the first destination */
  CURSOR ud1 IS
    SELECT inbound_uld_dests.*
          ,iuv.uld_type  uld_type
    FROM   inbound_uld_dests
          ,inbound_uld_v1 iuv
    WHERE iuv.uld_no            = l_uld_no
      AND iuv.inbound_flight_id = p_in_flightid
      AND iuv.inbound_uld_id    = inbound_uld_dests.inbound_uld_id
      AND iuv.result_uld_status = 'UNPROCESSED';



BEGIN

  p_return_code := 0;

  /****************************************************************/
  /* Check to ensure that the uld exists on the INBOUND_ULD table */
  /****************************************************************/
  BEGIN
     SELECT *
     INTO l_inbound_uld
     FROM inbound_uld_v1 iuv
     WHERE iuv.uld_no             = l_uld_no
     AND   iuv.inbound_flight_id  = l_in_flightid
     AND   iuv.result_uld_status IN( 'UNPROCESSED');

  EXCEPTION
     WHEN NO_DATA_FOUND THEN
        p_return_code := -1100;
        add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_ULD(1)- Select inbound_uld',SQLERRM,SQLCODE);
        RETURN;
  END;

  /**************************************************************************/
  /* We can't transfer a hold, so only do the rest if this is a genuine ULD */
  /**************************************************************************/

  /* Check to see whether this is a HOLD */
  SELECT count(*)
  INTO l_hold_exists
  FROM holds
  WHERE hold_no = l_uld_no ;

  /* Check to see whether the ULD already exists on the Outbound Flight */
  SELECT count(*)
  INTO   l_uld_exists
  FROM   ulds
  WHERE  ulds.flight_id = l_out_flightid
  AND    ulds.uld_no = l_uld_no;

  /*****************************************************************************/
  /* Deal with HOLDs and ULDs existing on the outbound flight.                 */
  /* Create the uld for the Hold only or for just the ULD                      */
  /* The existence of HOLDs means that these would need to be checked and      */
  /* created if they exist and not yet on the Outbound Flight.                 */
  /*****************************************************************************/
  BEGIN
     IF ((l_hold_exists > 0 AND l_uld_exists = 0)  OR    -- Create ULD.HOLD or
        ( l_hold_exists = 0 AND l_uld_exists = 0)) THEN  -- Create ULD
         l_create_uld := 'Y';
     ELSIF -- this is not a hold and the uld already exists, then error.
        ( l_hold_exists = 0 AND l_uld_exists > 0) THEN

        IF (l_process_type = 'THROUGH') then
          /*************************************************************************/
          /* If a through uld already exists then need to error this and abort     */
          /*************************************************************************/
           UPDATE inbound_uld iu
           SET    iu.error_code        = pkg_constants.ERROR_ULD_ALREADY_EXISTS
                 ,iu.result_uld_status= 'ERROR'
           WHERE  iu.source_uld_no     = l_uld_no
           AND    iu.inbound_flight_id = l_in_flightid;

        END IF;

        RAISE uld_already_exists;

     END IF;

  EXCEPTION
     WHEN uld_already_exists THEN
          add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_ULD(2) - ULD EXISTS',SQLERRM,SQLCODE);
          RAISE;

     WHEN OTHERS THEN
          add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_ULD(3)',SQLERRM,SQLCODE);
          RAISE;
  END;

  /* Get the current airport code of the inbound flight */
  SELECT site_info.airport_code
  INTO l_current_airport
  FROM site_info, flight
  WHERE flight.site_id = site_info.site_id
  AND   flight_id      = l_in_flightid;

  /* Get the first flight destination of the outbound flight */
  SELECT flight_dests.flight_destination
  INTO   l_flight_dest
  FROM   flight_dests
  WHERE  flight_dests.flight_id = p_out_flightid
  AND    flight_dests.dest_order = 1;

  /***********************************************************************/
  /* Create the ULD for the HOLD or for the outbound ULD                 */
  /***********************************************************************/
  IF p_return_code = 0 AND l_create_uld = 'Y' THEN
        add_uld_record (l_uld_no,
                        l_out_flightid,
                        l_inbound_uld.uld_category,
                        l_inbound_uld.uld_type,
                        l_user_id,
                        l_loc,
                        l_connection_type, -- leave this as null since cannot determine this at the next airport
                        l_inbound_uld.uld_id_code );

        /* Create Inbound Through/Transfer ULD Trace Record */

	IF ( l_process_type = 'THROUGH' )
        THEN
            l_process_action := 'CIN_TH_ULD';
        ELSE
            l_process_action := 'CIN_TR_ULD';
        END IF;

        add_uld_trace_record (l_uld_no,
		                      l_out_flightid,
                              l_user_id,
                              '0',
                              l_loc,
                              l_trtime,
                              l_process_action,
                              'Y');

        /* Set the position if it's known */
        IF ltrim(p_hold_no) IS NOT NULL THEN
          rec_uld_posn(l_uld_no, l_out_flightid, l_hold_no, l_user_id, l_loc, p_posn_result,0);
        ELSE
          p_posn_result := 0;
        END IF;


        /******************************************************************/
        /* Create the uld_dests records.                                  */
        /* The first destination should be excluded if it matches the     */
        /* current airport of the inbound flight. If no destination exist */
        /* then a default first destination of the new uld which matches  */
        /* the first destination of the outbound flight is to be used.    */
        /******************************************************************/
        BEGIN -- add_uld_dest_record
        l_dest_added := FALSE;

        FOR inbound_uld_dests IN ud1 LOOP
          IF ( inbound_uld_dests.destination_sequence = 1 AND
               rtrim(inbound_uld_dests.destination) = rtrim(l_current_airport) ) THEN
              NULL; -- don't add any uld records;
          ELSE
              check_add_uld_dests_record (l_uld_no
                                   ,inbound_uld_dests.destination
                                   ,inbound_uld_dests.class_list
                                   ,l_out_flightid
                                   ,inbound_uld_dests.onward_flight_no
                                   ,inbound_uld_dests.onward_flight_date);
             l_dest_added := TRUE;
          END IF;

        END LOOP;

        IF (l_dest_added != TRUE) THEN
           check_add_uld_dests_record (l_uld_no
                                 ,l_flight_dest
                                 ,'*'
                                 ,l_out_flightid
                                 ,NULL
                                 ,NULL);
        END IF;

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

        /*******************************************************/
        /* Update the NEXT... details of the inbound uld,      */
        /* but only if transferring, rather than reallocating  */
        /*******************************************************/
        flight_no (p_out_flightid, l_out_flight_no, l_out_flight_date);

        IF NVL(p_reallocate_flag, '??') != 'MERGE' THEN
          UPDATE ulds
            SET next_flight_no  =l_out_flight_no,
                next_flight_date=l_out_flight_date
          WHERE
            uld_no    = l_uld_no AND
            flight_id = l_in_flightid AND
            pd_marker = 'Y';
        END IF;
  END IF;

  /********************************************************/
  /* Update inbound_uld table with values                 */
  /********************************************************/
  UPDATE inbound_uld
  SET    result_uld_no         = l_uld_no
        ,result_uld_flight_id  = l_out_flightid
        ,result_uld_status     = 'CREATED'
        ,error_code            = 0
  WHERE inbound_uld_id         = l_inbound_uld.inbound_uld_id
  AND   inbound_flight_id      = l_in_flightid;

EXCEPTION
   WHEN OTHERS THEN
      p_return_code := -3; -- some other error
      add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_ULD(4)',SQLERRM,SQLCODE);
      raise;
END copy_uld;


/********************************************************************
* Module Name:          copy_non_uld_bags                           *
* Subsystem Name:       stored_procs                                *
*********************************************************************
*                                                                   *
* Description:                                                      *
*  Procedure to copy all bags from an inbound flight to an outbound *
* through flight, provided that the bag has no exception, the bag   *
* has not already been transferred and the passenger exists on the  *
* outbound flight. The aim of this procedure is to copy the bags    *
* which have not already been copied as part of a through ULD.      *
********************************************************************/

PROCEDURE copy_non_uld_bags (
    p_in_flightid  IN NUMBER
   ,p_out_flightid IN NUMBER
   ,p_process_type IN VARCHAR2)
IS
  /* Local declarations */
  l_process_type    inbound_pax.inbound_pax_status%TYPE := upper(p_process_type); -- THROUGH OR TRANSFER

  /* Configuration parameters */
  l_pax_processing db_config.db_value%TYPE;

  /* Flight declarations */
  l_in_flight_no    flight.flight_no%TYPE;
  l_in_flight_date  flight.flight_date%TYPE;
  l_out_flight_no   flight.flight_no%TYPE;
  l_out_flight_date flight.flight_date%TYPE;

  l_location        bag_trace.location_code%TYPE;
  l_user_id         bag_trace.user_id%TYPE;
  l_home_airport    site_info.airport_code%TYPE;
  l_origin_airport  site_info.airport_code%TYPE;

  /* Rowtypes and Types */
  l_bag             bag%ROWTYPE := NULL;
  dummy_onwd_flt_t  ONWARD_FLIGHT_DEFS.ONWD_FLT_T;

  /* pax declarations */
  l_pax_sequence_no    pax.pax_sequence_no%TYPE            := 0;

  /* security clearance */
  l_retcode            NUMBER                              := 0;
  l_security_status    bag.security_clearance_status%TYPE  := NULL;

  /* Inbound specific table details */
  l_result_bag_status inbound_bag.result_bag_status%TYPE := NULL;

  /***********************************************************************************/
  /* Cursor to check whether the bag already exists to determine whether to add_bag  */
  /* or reuse_bag to modifuy and update existing details.                            */
  /***********************************************************************************/
  CURSOR c_check_bag_exists ( p_tag2b     IN bag.bag_tag2b%TYPE
                             ,p_tag3      IN bag.bag_tag3%TYPE
                             ,p_pax_id    IN bag.pax_id%TYPE
                             ,p_flight_id IN pax.flight_id%TYPE) IS
   SELECT bag.*
   FROM   bag, pax
        WHERE  bag.bag_tag3      = p_tag3
        AND    bag.bag_tag2b     = p_tag2b
        AND    bag.pax_id        = p_pax_id
		    AND    pax.flight_id     = p_flight_id
        AND    pax.pax_id        = bag.pax_id
        AND    bag.delete_marker = 0;

  /***********************************************************************************/
  /* Process only through bags which have yet to be processed                        */
  /***********************************************************************************/
  CURSOR tb1 IS
    SELECT  inbound_bag.*
           ,inbound_pax.result_pax_id                          o_pax_id
           ,inbound_pax.pax_destination                        o_pax_destination
           ,inbound_pax.pax_id                                 o_pax_id_fred
           ,inbound_pax.inbound_pax_id                         o_inbound_pax_id
           ,inbound_pax.flight_id                              o_flight_id
           ,inbound_pax.inbound_flight_id                      o_inbound_flight_id
           ,inbound_pax.booked_flight_id                       o_booked_flight_id
           ,inbound_pax.pax_initials                           o_pax_initials
           ,inbound_pax.pax_surname                            o_pax_surname
           ,inbound_pax.pax_status                             o_pax_status
           ,inbound_pax.time_pax_stored                        o_time_pax_stored
           ,inbound_pax.no_of_bags                             o_no_of_bags
           ,inbound_pax.no_same_surname                        o_no_same_surname
           ,inbound_pax.pax_sequence_no                        o_pax_sequence_no
           ,inbound_pax.pax_seat_no                            o_pax_seat_no
           ,inbound_pax.pax_class                              o_pax_class
           ,inbound_pax.pax_security_no                        o_pax_security_no
           ,inbound_pax.pax_name_record                        o_pax_name_record
           ,inbound_pax.exception_type_id                      o_exception_type_id
           ,inbound_pax.departure_airport_code                 o_departure_airport_code
           ,inbound_pax.authority_to_load_dcs                  o_authority_to_load_dcs
           ,inbound_pax.pax_full_inits                         o_pax_full_inits
           ,inbound_pax.onward_transfer                        o_onward_transfer
           ,inbound_pax.pax_dcs_sequence_no                    o_pax_dcs_sequence_no
           ,inbound_pax.embassy                                o_embassy
           ,inbound_pax.pax_security_status                    o_pax_security_status
           ,inbound_pax.source_message_id                      o_source_message_id
           ,inbound_pax.error_code                             o_error_code
           ,inbound_pax.inbound_pax_status                     o_inbound_pax_status
           ,inbound_pax.freq_flyer                             o_freq_flyer
           ,inbound_pax.pax_bags_class                         o_pax_bags_class
           ,inbound_pax.connection_type                        o_connection_type
           ,inbound_pax.onward_flight_count                    o_onward_flight_count
           ,inbound_pax.result_pax_status                      o_result_pax_status
    FROM   inbound_bag_v1 inbound_bag
          ,inbound_pax_v1 inbound_pax
    WHERE  inbound_bag.result_bag_id IS NULL
    AND    inbound_pax.inbound_pax_status = l_process_type -- 'THROUGH' or TRANSFER
    AND    nvl(inbound_bag.error_code,0) = 0
    AND    nvl(inbound_pax.error_code,0) = 0
    AND    inbound_bag.inbound_pax_id     = inbound_pax.inbound_pax_id
    AND    inbound_pax.inbound_flight_id = p_in_flightid
    AND    inbound_bag.result_bag_status = 'UNPROCESSED';       -- for the inbound flight

BEGIN

  /* Get the airport the outward flight will depart from */
  SELECT site_info.airport_code
  INTO   l_home_airport
  FROM   site_info, flight
  WHERE  flight.flight_id = p_out_flightid AND
         site_info.site_id = flight.site_id;

  SELECT site_info.airport_code
  INTO l_origin_airport
  FROM site_info
  WHERE site_info.site_id = inbound_flight_utils.get_inbound_orig_site(p_in_flightid);

  /* Get the 'automatic' location for this site */
  l_location := multi_site.get_auto_location(l_home_airport);

  /* Get the 'automatic' user_id for this site */
  l_user_id := multi_site.get_auto_user(l_home_airport);

  /* Get the TRANSFER_PAX_PROCESSING parameter from DB_CONFIG */
  l_pax_processing := config.get_value('TRANSFER_PAX_PROCESSING');

  FOR c_inbound_bag IN tb1 LOOP

    /* get the FLIGHT_NO and FLIGHT_DATE for the outbound flight */
    flight_no(p_out_flightid, l_out_flight_no, l_out_flight_date);
    flight_no(p_in_flightid,  l_in_flight_no,  l_in_flight_date);

    /*************************************************************/
    /* Check to see whether the result_pax_id exists for the     */
    /* outbound flight. If it doesn't a new pax needs to be      */
    /* created and the inbound_pax table needs to be updated with*/
    /* the new changes. Need to also set the inbound_pax status  */
    /* fields to AMENDED.                                        */
    /*************************************************************/

    IF (c_inbound_bag.o_pax_id IS NULL AND l_pax_processing <> 'LOADALL') THEN

        /* check the pax_sequence_no, to ensure its in range */
        l_pax_sequence_no := check_pax_sequence_no(p_in_flight_id => p_out_flightid
                                                  ,p_seqno        => c_inbound_bag.o_pax_sequence_no);

        /* create the pax, since the inbound_pax.result_pax_id is NULL, we need a value for later */
        add_pax (p_out_flightid,
                 p_out_flightid,
                 c_inbound_bag.o_pax_initials,
                 c_inbound_bag.o_pax_surname,
                 c_inbound_bag.o_pax_status,
                 0,
                 c_inbound_bag.o_no_same_surname,
                 c_inbound_bag.o_pax_destination,
                 c_inbound_bag.o_pax_seat_no,
                 c_inbound_bag.o_pax_class,
                 l_pax_sequence_no,
                 c_inbound_bag.o_pax_dcs_sequence_no,
                 c_inbound_bag.o_pax_security_no,
                 c_inbound_bag.o_pax_name_record,
                 c_inbound_bag.o_pax_security_status,
                 l_home_airport,
                 l_in_flight_no,
                 l_in_flight_date,
                 l_origin_airport,
                 c_inbound_bag.o_authority_to_load_dcs,
                 c_inbound_bag.o_pax_full_inits,
                 c_inbound_bag.o_onward_flight_count,
                 c_inbound_bag.o_connection_type,
                 dummy_onwd_flt_t, /* add these later */
                 c_inbound_bag.o_freq_flyer,
                 c_inbound_bag.o_pax_bags_class,
                 c_inbound_bag.o_embassy,
                 c_inbound_bag.o_pax_id); -- return new pax_id

       /***********************************************************************/
       /* Create any onward flight details for the newly created outbound pax */
       /* for the next destination onwards i.e. destination_sequence > 1      */
       /***********************************************************************/
       inbound_flight.create_onward_flight_details( p_inbound_pax_id  => c_inbound_bag.o_inbound_pax_id
                                                   ,p_outbound_pax_id => c_inbound_bag.o_pax_id);

       /*************************************************************************/
       /* Now update the inbound_pax table with the existing outbound pax       */
       /* and set the inbound_pax_status to CREATED/AMENDED, since              */
       /* inbound_pax.result_pax_is has already been populated and the          */
       /* outbound pax already exists in the PAX table                          */
       /*************************************************************************/

       BEGIN
       UPDATE inbound_pax
       SET    result_pax_id      = c_inbound_bag.o_pax_id
             ,result_pax_status  = 'CREATED'
             ,error_code         = 0
       WHERE  inbound_pax_id     = c_inbound_bag.inbound_pax_id
       AND    inbound_flight_id  = p_in_flightid;
       EXCEPTION
         WHEN OTHERS THEN
          add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_NON_ULD_BAGS - update inbound_pax',SQLERRM,SQLCODE);
       END;

    END IF;

    /* Check we managed to get an outbound pax id and it is not a LoadAll pax */
    BEGIN
      SELECT p.pax_sequence_no
      INTO   l_pax_sequence_no
      FROM   pax p
      WHERE  p.pax_id = c_inbound_bag.o_pax_id;
    EXCEPTION
      WHEN OTHERS THEN
        l_pax_sequence_no := 0;
    END;

    IF (c_inbound_bag.o_pax_id > 0 AND l_pax_sequence_no > 0) THEN

      /*************************************************************************/
      /* Before adding the bag, check to see whether it already exists. If it  */
      /* does then resuse it, otherwise create a new bag.                      */
      /*************************************************************************/
      l_bag := NULL;
      OPEN  c_check_bag_exists( c_inbound_bag.bag_tag2b
                               ,c_inbound_bag.bag_tag3
                               ,c_inbound_bag.o_pax_id
                               ,p_out_flightid);
      FETCH c_check_bag_exists INTO l_bag;
      CLOSE c_check_bag_exists;

      IF l_bag.bag_tag IS NULL THEN
        /* add the bag, but don't load it */
        add_bag ( c_inbound_bag.bag_tag
                 ,c_inbound_bag.bag_tag1
                 ,c_inbound_bag.bag_tag2a
                 ,c_inbound_bag.bag_tag2b
                 ,c_inbound_bag.bag_tag3
                 ,c_inbound_bag.o_pax_id
                 ,c_inbound_bag.authority_to_load_dcs -- already derived by inbound_bag_v1
                 ,c_inbound_bag.o_pax_destination
                 ,c_inbound_bag.bag_source_indicator
                 ,c_inbound_bag.bag_exception
                 ,c_inbound_bag.process_name
                 ,c_inbound_bag.unit_name
                 ,c_inbound_bag.dcsid
                 ,c_inbound_bag.subunit_name
                 ,c_inbound_bag.dcs_bag_tag
                 ,''                         -- ULD is blank
                 ,c_inbound_bag.connection_type
                 ,c_inbound_bag.bag_type
                 ,l_location
                 ,l_user_id
                 ,l_security_status          -- need to be derived for the outbound flight bag
                 ,c_inbound_bag.delivery_instr
                 ,NULL
                 ,0
                 ,NULL);

        /********************************************************************************/
        /* Need to get the newly generated bag.bag_id needed for the inbound_bag update */
        /* query the bag and pax tables using the outbound pax_id of the inbound flight */
        /********************************************************************************/
        l_bag := NULL;
        OPEN  c_check_bag_exists( c_inbound_bag.bag_tag2b
                                 ,c_inbound_bag.bag_tag3
                                 ,c_inbound_bag.o_pax_id
                                 ,p_out_flightid);
        FETCH c_check_bag_exists INTO l_bag;
        CLOSE c_check_bag_exists;

        /************************************************************************/
        /* Now update the inbound_bag and set the result_bag_status to 'CREATED */
        /* and to update the result_bag_id with the newly created bag           */
        /* which had been created for the outbound flight.                      */
        /************************************************************************/
        l_result_bag_status := 'CREATED';

      ELSE /* call resuse_bag */
        reuse_bag( tagno         => c_inbound_bag.bag_tag
                  ,tag1          => c_inbound_bag.bag_tag1
                  ,tag2a         => c_inbound_bag.bag_tag2a
                  ,tag2b         => c_inbound_bag.bag_tag2b
                  ,tag3          => c_inbound_bag.bag_tag3
                  ,pid           => c_inbound_bag.o_pax_id
                  ,atldcs        => c_inbound_bag.authority_to_load_dcs
                  ,dest          => c_inbound_bag.o_pax_destination
                  ,bsi           => c_inbound_bag.bag_source_indicator
                  ,excep         => c_inbound_bag.bag_exception
                  ,proc          => c_inbound_bag.process_name
                  ,unit          => c_inbound_bag.unit_name
                  ,p_dcsid       => c_inbound_bag.dcsid
                  ,subunit       => c_inbound_bag.subunit_name
                  ,dcstagno      => c_inbound_bag.dcs_bag_tag
                  ,conn_type     => c_inbound_bag.connection_type
                  ,btype         => c_inbound_bag.bag_type
                  ,scs           => l_security_status
                  ,p_deliv_instr => c_inbound_bag.delivery_instr
                  ,wt_ref        => c_inbound_bag.worldtracer_ref
                  ,rv            => l_retcode
                  ,p_kg_weight   => c_inbound_bag.kg_weight
                  ,bvis_subhead  => c_inbound_bag.bvis_subheader
                  );

        /************************************************************************/
        /* Now set the result_bag_status to AMENDED since the bag already exists*/
        /* and so the inbound_bag table is being updated to reflect this.       */
        /************************************************************************/
        l_result_bag_status := 'AMENDED';

      END IF;

      /*******************************************************************************************************/
      /* Unfortunately, ADD_BAG and REUSE_BAG update the RESULT_PAX_STATUS column of INBOUND_PAX to AMENDED. */
      /* If the pax was actually CREATED before this process started, we need to reset it                    */
      /*******************************************************************************************************/
      IF (c_inbound_bag.o_result_pax_status = 'CREATED') THEN

        BEGIN
          UPDATE inbound_pax ip
          SET    ip.result_pax_status = 'CREATED'
          WHERE  ip.inbound_pax_id = c_inbound_bag.o_inbound_pax_id;
        EXCEPTION
          WHEN OTHERS THEN
            NULL;
        END;

      END IF;

      /***************************************************************/
      /* Check whether this bag and its duplicates require screening */
      /***************************************************************/
      check_for_unapproved_duplicate(p_out_flightid, c_inbound_bag.bag_tag2b, c_inbound_bag.bag_tag3);

      /************************************************************************/
      /* Now updated the inbound_bag and set the result_bag_id to the existing*/
      /* bag.bag_id which has already been created for the outbound flight. OR*/
      /* set thre result_bag_id to the new bag if being created for the       */
      /* outbound flight. Also set the result_bag_status depending on whether */
      /* the bag existed prior hence result_bag_status = AMENDED otherwise    */
      /* for NEW bags it's CREATED.                                           */
      /************************************************************************/
      BEGIN
        UPDATE inbound_bag
        SET    result_bag_id     = l_bag.bag_id        -- Either NEW or EXISTING bag_id
              ,result_bag_status = l_result_bag_status -- set to CREATED (new bag) AMENDED (existing bag)
              ,error_code        = 0
        WHERE  inbound_bag.inbound_bag_id = c_inbound_bag.inbound_bag_id;
      EXCEPTION
        WHEN OTHERS THEN
          add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_NON_ULD_BAGS - update inbound_bag',SQLERRM,SQLCODE);
      END;

      /* Through Bags not in a Through ULD will be included in the HBMD processing */
      BEGIN
        UPDATE bag
        SET hbmd_flag='Y',
            next_flight_no  = l_out_flight_no,
            next_flight_date= l_out_flight_date
        WHERE
            bag.bag_tag3  = c_inbound_bag.bag_tag3
        AND bag.bag_tag2a = c_inbound_bag.bag_tag2a
        AND pax_id        = c_inbound_bag.o_pax_id
        AND loaded_status = 'Y';
      EXCEPTION
        WHEN OTHERS THEN
          add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_NON_ULD_BAGS - update bag',SQLERRM,SQLCODE);
      END;

    END IF;  -- End of check c_inbound_bag.o_pax_id > 0

  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    add_app_msg.log_oracle_error('INBOUND_FLIGHT.COPY_NON_ULD_BAGS(1)',SQLERRM,SQLCODE);
    RAISE;
END copy_non_uld_bags;

end INBOUND_FLIGHT;
/



