/*****************************************************************************
*
* Filename         - $Workfile:   TSS_LOAD_BAG.sql  $
*
*                     Name         Date       Version     CR Number
*                     Peter Charalambous 01/12/2003
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       -
*
* Design Spec
* Section          -  FAS/DUBRS/SD/245
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PROCEDURE tss_load_bag

CREATE OR REPLACE PROCEDURE tss_load_bag (  p_emp_num       IN  tagtrak_users.loginname%TYPE
                         ,p_scanner_id    IN  VARCHAR2
                         ,p_location_code IN  location.location_code%TYPE
                         ,p_uld           IN  ulds.uld_no%TYPE
                         ,p_bagtag        IN  bag.bag_tag%TYPE
                         ,p_load_all      IN  VARCHAR2 DEFAULT 'FALSE'
                         ,p_force_load    IN  VARCHAR2 DEFAULT 'FALSE'
                         ,p_result        OUT VARCHAR2)
AS
/*******************************************************************************
 *
 * Procedure            - tss_load_bag
 *
 * Description          - This procedure loads a bag into the tagtrak system.
 *                        The bag is loaded into the container that is also
 *                        supplied. In addition, this procedure can be run
 *                        in load_all mode (i.e. pass value as TRUE) or as force
 *                        load ( i.e. pass value as TRUE ). LOADALL includes
 *                        whether it runs with a loading strategy of 'L' Loadll
 *                        'C' CHECKEDIN or 'B' BOOKED. Also, the lockout status
 *                        of the user is determind.
 *
 *                        Further functionality inclued :
 *                        1) The authority of the user is checked to see
 *                           whether this user can load bags.
 *                        2) The ULD is checked to ensure that it exists
 *                           and is valid.
 *                        3) The bag is checked to see whether it is authorised
 *                           to load and is not already loaded.
 *                        4) The bag and ULD flight details are compaired to
 *                           ensure that they match.
 *                        5) The bag and ULD destinations are compaired to
 *                           ensure that they match.
 *                        6) The ULD flight is not closed.
 *                        7) The bag and ULD Types match.
 *                        8) The bag and ULD Classes match.
 *
 * CA History:
 * Date         Name        CA Number           CA Description
 ******************************************************************************/

   -- Local constants
   DEBUG                CONSTANT VARCHAR2(1) DEFAULT 'Y';
   VALID                CONSTANT VARCHAR2(1) DEFAULT 'Y';
   DENIED               CONSTANT VARCHAR2(1) DEFAULT 'N';
   LOADALL              CONSTANT ldg_strat.ldg_strat_code%TYPE DEFAULT 'L';
   CHECKEDIN            CONSTANT ldg_strat.ldg_strat_code%TYPE DEFAULT 'C';
   DATA_NOT_FOUND       CONSTANT NUMBER := 100;

   -- General declarations
   l_process_name       oracle_error.process_name%TYPE := 'TSS_LOAD_BAG';
   l_location_code      location.location_code%TYPE  := p_location_code;
   l_emp_num            tagtrak_users.loginname%TYPE := p_emp_num;
   l_load_all           VARCHAR2(5)                  := p_load_all;
   l_force_load         VARCHAR2(5)                  := p_force_load;
   l_scanner_id         bag_trace.scanner_id%TYPE    := NULL;

   l_result             VARCHAR2(10)                 := pkg_constants.LOAD_BAG_SUCCESS;
   l_log_error          l_result%TYPE                := NULL;
   l_error_result       l_result%TYPE                := NULL;
   l_error_message      VARCHAR2(512)                := NULL;
   l_error_msg          oracle_error.errror_msg%TYPE := NULL;
   l_error_code         oracle_error.error_code%TYPE := NULL;
   l_error_number       NUMBER                       := NULL;
   l_error_time         oracle_error.error_time%TYPE := NULL;
   l_duplicate_count    NUMBER                       := 0;

   -- Pax variables
   l_pax_id             pax.pax_id%TYPE              := NULL;
   l_pax_seq_no         pax.pax_sequence_no%TYPE     := 0;
   l_retval             NUMBER                       := 0;
   l_pax_bags_class     pax.pax_bags_class%TYPE      := NULL;
   l_pax_class          pax.pax_class%TYPE           := NULL;
   l_pax_freq_flyer     pax.freq_flyer%TYPE          := NULL;

   -- Bag variables
   l_bag_tag            bag.bag_tag%TYPE             := p_bagtag;
   l_loading_strategy   ldg_strat.ldg_strat_code%TYPE:= 'L';
   l_bag_tag1           bag.bag_tag1%TYPE            := NULL;
   l_bag_tag2a          bag.bag_tag2a%TYPE           := 0;
   l_bag_tag2b          bag.bag_tag2b%TYPE           := 0;
   l_bag_tag3           bag.bag_tag3%TYPE            := NULL;
   l_bsm_bag            bag.bag_tag%TYPE             := NULL;
   l_bag_pax_id         bag.pax_id%TYPE              := 0;
   l_trace_action       bag_trace.trace_action%TYPE  := NULL;
   l_security_result    bag.security_result%type;
   l_security_reason    bag.security_reason%type;

   -- Uld variables
   l_uld_num            ulds.uld_no%TYPE             := p_uld;
   l_uld_id_code        ulds.uld_id_code%TYPE        := NULL;
   l_flight_id          ulds.flight_id%TYPE          := NULL;
   l_uld_loaded_status  ulds.uld_loaded_status%TYPE  := NULL;
   l_uld_category       ulds.uld_category%TYPE       := NULL;
   l_uld_type           ulds.uld_type%TYPE           := NULL;
   l_connection_type    ulds.connection_type%TYPE    := NULL;
   l_uld_hold_no        ulds.uld_hold_no%TYPE        := NULL;
   l_uld_no_of_bags     ulds.uld_no_of_bags%TYPE     := NULL;
   l_pd_marker          ulds.pd_marker%TYPE          := NULL;
   l_tuld_destination   uld_dests.uld_destination%TYPE := NULL;
   l_tuld_class_list    uld_dests.uld_class_list%TYPE  := NULL;

   -- Tag variables
   l_tag1               CHAR(1)                      := NULL;
   l_tag2a              CHAR(3)                      := NULL;
   l_tag2b              CHAR(3)                      := NULL;
   l_tag3               NUMBER(6)                    := NULL;
   l_tagno_num          CHAR(12)                     := NULL;
   l_tag_type           NUMBER                       := NULL;
   l_allow_default      NUMBER                       := 0;
   l_total_bags         NUMBER                       := 0;
   l_retcode            NUMBER                       := NULL;

   -- Tagtrak_users variables
   l_user_id            tagtrak_users.user_id%TYPE   := NULL;
   l_loginname          tagtrak_users.loginname%TYPE := NULL;

   -- Flight variables
   l_flight_no          flight.flight_no%TYPE        := NULL;
   l_flight_date        flight.flight_date%TYPE      := NULL;
   l_flight_status      flight.flight_status%TYPE    := NULL;

   /* Load all variables */
   l_pax_id_loadall     pax.pax_id%TYPE              := 0;
   l_pax_dest           pax.pax_destination%TYPE     := NULL;
   l_pax_class_loadall  pax.pax_class%TYPE           := '*';
   l_retval_loadall     pax.pax_id%TYPE              := NULL;

   -- app_priv variables
   l_app_priv_name_load_bag     app_priv.app_priv_name%TYPE := 'TETHER_Load_bag';
   l_app_priv_name_scanner_lock app_priv.app_priv_name%TYPE := 'TETHER_Scanner_Locking_Disable';
   l_loadbag_priv               VARCHAR2(1)                 := NULL;
   l_scanner_lock_priv          VARCHAR2(1)                 := NULL;
   l_scanner_locking_disabled   VARCHAR2(5)                 := 'FALSE';
	 handle_dupes  char(1) := '';
   
   -- reload set to 'Y' if TSS_RELOAD_ALLOWED exists and set to 'Y' in db_config
   l_tss_reload                 VARCHAR2(1) := nvl(UPPER(SUBSTR(config.get_value('TSS_RELOAD_ALLOWED'), 1, 1)),'N');
   
   


   -- Cursor declarations
   -- User details cursor
   CURSOR c_get_user_details (p_emp_num IN tagtrak_users.loginname%TYPE)
       IS
          SELECT nvl ( user_id,0 ),
                     loginname
              FROM tagtrak_users
              WHERE trim(loginname) = trim(p_emp_num);

   -- Uld details cursor
   CURSOR c_get_uld_details (p_uld_num IN ulds.uld_no%TYPE)
       IS
          SELECT  ulds.uld_id_code
                 ,ulds.flight_id
                 ,ulds.uld_loaded_status
                 ,ulds.uld_category
                 ,ulds.uld_type
                 ,ulds.connection_type
                 ,ulds.uld_hold_no
                 ,nvl(ulds.uld_no_of_bags,0)
                 ,ulds.pd_marker
                 ,f.flight_no
                 ,f.flight_date
                 ,f.flight_status
          FROM  ulds, flight_outbound_v1 f
          WHERE ulds.uld_no     = p_uld_num
          AND   ulds.flight_id  = f.flight_id
          AND   ulds.pd_marker != 'Y';    -- UAS/FALCP/CA/00603  added this line for Rohan Alexander (Nigel, 01/04/04)


   /* cursor needed to check bag details */
   CURSOR c_get_bag_details ( p_bag_tag2b IN bag.bag_tag2b%TYPE
                             ,p_bag_tag3  IN bag.bag_tag3%TYPE )
       IS
       SELECT p.pax_id
        ,b.bag_tag1
        ,b.bag_tag2a
        ,NVL(b.bag_tag2b,'-')
        ,b.bag_tag3
        ,p.pax_class
        ,p.pax_bags_class
        ,p.freq_flyer
        ,b.bag_tag
       FROM pax p
        ,bag b
        ,flight_outbound_v1 f
      WHERE p.flight_id     = f.flight_id
      AND   p.pax_id        = b.pax_id
      AND   b.bag_tag2b     = p_bag_tag2b
      AND   b.bag_tag3      = p_bag_tag3
	  order by time_bag_stored desc;   -- For same bags with delete markers set/unset

    -- Exceptions
    unknown_user             EXCEPTION;
    error_duplicate_bag      EXCEPTION;
    no_uld_details           EXCEPTION;
    upd_bag_trace_err        EXCEPTION;
    error_results            EXCEPTION;
    error_general_exception  EXCEPTION;
    no_bag_details           EXCEPTION;
    error_flight_closed      EXCEPTION;
    error_pd_flight          EXCEPTION;
    error_flight_access      EXCEPTION;

BEGIN

    /* Strip the scanner_id to 6 characters. Scanner id comes in
     * as 8 characters.
     */
     IF ( length(p_scanner_id) = 8 )  THEN
          l_scanner_id := substr(p_scanner_id,3,6);
     END IF;

     /* Firstly get the user_id */
     OPEN c_get_user_details (l_emp_num);
     FETCH c_get_user_details INTO l_user_id
                                  ,l_loginname;
     IF c_get_user_details%NOTFOUND THEN
        RAISE unknown_user;
     END IF;
     CLOSE c_get_user_details;

       /* Firstly split the bag_tag to use bits of it.
     */
    IF (p_bagtag IS NOT NULL ) THEN
        split_tags (  tagno     => p_bagtag
                     ,tag1      => l_tag1
                     ,tag2a     => l_tag2a
                     ,tag2b     => l_tag2b
                     ,tag3      => l_tag3
                     ,tagno_num => l_tagno_num
                     ,tag_type  => l_tag_type
			   ,allow_default => l_allow_default
                );
    END IF;

    /* Some debug information */
    IF ( DEBUG = VALID ) THEN
       dbms_output.put_line('Results from Split Tag....');
       dbms_output.put_line('tagno      :'||p_bagtag);
       dbms_output.put_line('tag1       :'||l_tag1);
       dbms_output.put_line('tag2a      :'||l_tag2a);
       dbms_output.put_line('tag2b      :'||l_tag2b);
       dbms_output.put_line('tag3       :'||l_tag3);
       dbms_output.put_line('tagno_num  :'||l_tagno_num);
       dbms_output.put_line('tag_type   :'||l_tag_type);
       dbms_output.put_line('l_user_id  :'||l_user_id);
    END IF;

     /* Assign the appropriate trace action */
     IF p_force_load = 'TRUE' THEN
        l_trace_action := 'FORCED';
     ELSE
        l_trace_action := 'LOAD';
     END IF;

     /* Get the flight id needed */
     OPEN c_get_uld_details (l_uld_num);
     FETCH c_get_uld_details
     INTO  l_uld_id_code
          ,l_flight_id
          ,l_uld_loaded_status
          ,l_uld_category
          ,l_uld_type
          ,l_connection_type
          ,l_uld_hold_no
          ,l_uld_no_of_bags
          ,l_pd_marker
          ,l_flight_no
          ,l_flight_date
          ,l_flight_status;
     IF c_get_uld_details%NOTFOUND THEN
        /* Create a log for this */
        add_bag_trace_record( l_user_id
                             ,l_location_code
                             ,pkg_constants.ERROR_UNKNOWN_ULD    -- error_code
                             ,l_bag_tag
                             ,l_tag1
                             ,l_tag2a
                             ,l_tag2b
                             ,l_tag3
                             ,l_trace_action    -- trace_action
                             ,l_scanner_id
                             ,l_flight_id
                             ,l_bag_pax_id
                             ,l_uld_num    -- pass ULD number (Nigel, 01/04/04, CA598)
                             );
        RAISE no_uld_details;
     END IF;
     CLOSE c_get_uld_details;

     --Validate user access to flight
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('-----------------------------------');
        dbms_output.put_line('Validating user access to flight..');
        dbms_output.put_line('-----------------------------------');
     END IF;

     -- CA697 Flight Access Changes
     user_authentication.validate_access
          (l_user_id, l_flight_id, null, null, l_retcode);

     IF( l_retcode < 0 ) THEN
         l_result:= pkg_constants.ERROR_FLIGHT_ACCESS_DENIED; --(-1031);
         RAISE error_flight_access;
     END IF;

     /* Do some debug, can be switched off */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('---------------------------------------');
        dbms_output.put_line('Parameters passed into TSS_LOAD_BAG...');
        dbms_output.put_line('---------------------------------------');
        dbms_output.put_line('l_emp_num       :'||l_emp_num);
        dbms_output.put_line('p_scanner_id    :'||p_scanner_id);
        dbms_output.put_line('l_location      :'||l_location_code);
        dbms_output.put_line('l_uld_num       :'||l_uld_num);
        dbms_output.put_line('l_bag_tag       :'||l_bag_tag);
        dbms_output.put_line('l_load_all      :'||l_load_all);
        dbms_output.put_line('l_force_load    :'||l_force_load);
        dbms_output.put_line('---------------------------------------');
        dbms_output.put_line('l_user_id       :'||l_user_id);
        dbms_output.put_line('l_flight_id     :'||l_flight_id);
        dbms_output.put_line('---------------------------------------');
        dbms_output.put_line('Flight details.');
        dbms_output.put_line('---------------------------------------');
        dbms_output.put_line('l_pd_marker     :'||l_pd_marker);
        dbms_output.put_line('l_flight_id     :'||l_flight_id);
        dbms_output.put_line('l_flight_date   :'||l_flight_date);
        dbms_output.put_line('l_flight_status :'||l_flight_status);
        dbms_output.put_line('---------------------------------------');
     END IF;


     /* Check whether this user has the authority to
      * load bags ie. TETHER_Load_bag exists.
      */

      mws_web.check_privilege (  p_user_id         => l_user_id
                                ,p_app_priv_name   => l_app_priv_name_load_bag
                                ,p_flight_id       => l_flight_id
                                ,p_valid_privilege => l_loadbag_priv
                                ,p_error_message   => l_error_message
                                ,p_error_number    => l_error_number);


     /* Do some debug, can be switched off */
     IF ( DEBUG = VALID) THEN
        dbms_output.put_line('---------------------------------------');
        dbms_output.put_line('Checked load bag priv');
        dbms_output.put_line('---------------------------------------');
        dbms_output.put_line('l_user_id           :'||l_user_id);
        dbms_output.put_line('l_app_name_load_bag :'||l_app_priv_name_load_bag);
        dbms_output.put_line('l_flight_id         :'||l_flight_id);
        dbms_output.put_line('l_loadbag_priv      :'||l_loadbag_priv);
        dbms_output.put_line('l_error_message     :'||l_error_message);
        dbms_output.put_line('l_error_number      :'||l_error_number);
        dbms_output.put_line('----------------------------------------');
     END IF;

     /* If user is not allowed to load error and create a log file */
     IF l_loadbag_priv = 'N' THEN
      /* Create a log for this */
      add_bag_trace_record( l_user_id
                             ,l_location_code
                             ,pkg_constants.ERROR_USER_NOT_AUTHORISED    -- error_code
                             ,l_bag_tag
                             ,l_tag1
                             ,l_tag2a
                             ,l_tag2b
                             ,l_tag3
                             ,l_trace_action    -- trace_action
                             ,l_scanner_id
                             ,l_flight_id
                             ,l_bag_pax_id
                             ,l_uld_num    -- pass ULD number (Nigel, 01/04/04, CA598)
                             );
     END IF;
     /* Also check whether user has scanner locking enabled.
      * If scanner locking exists for this user then user
      * has scanner locking enabled i.e. set to TRUE.
      */
     mws_web.check_privilege ( p_user_id         => l_user_id
                              ,p_app_priv_name   => l_app_priv_name_scanner_lock
                              ,p_flight_id       => l_flight_id
                              ,p_valid_privilege => l_scanner_lock_priv
                              ,p_error_message   => l_error_message
                              ,p_error_number    => l_error_number);

     /* Assume initially that all users don't have scanner locking priv
      * and only enable scanning locking, once the privilege has actually
      * been checked.
      */
     IF ( l_scanner_lock_priv = VALID ) THEN
          l_scanner_locking_disabled := 'TRUE';
     END IF;


     /* Do some debug, can be switched off */
     IF ( DEBUG = VALID) THEN
       dbms_output.put_line('----------------------------------------');
       dbms_output.put_line('Checked for scanner locking');
       dbms_output.put_line('----------------------------------------');
       dbms_output.put_line('l_user_id                     :'||l_user_id);
       dbms_output.put_line('l_app_priv_name_scanner_lock  :'||
                           l_app_priv_name_scanner_lock);
       dbms_output.put_line('l_flight_id                   :'||l_flight_id);
       dbms_output.put_line('l_scanner_lock_priv           :'||l_scanner_lock_priv);
       dbms_output.put_line('l_error_message               :'||l_error_message);
       dbms_output.put_line('l_error_number                :'||l_error_number);
       dbms_output.put_line('----------------------------------------');
     END IF;

     /* Get some data needed like pax_sequence_no and pax.flight_id */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('Open cursor and fetching data');
     END IF;

     /* If no bags are found and this is a loadall situation
      * a LoadAll Pax would be created if it doesn't exist
      * and these bags assigned to this load all to be loaded
      * on as a LoadAll Pax passenger. Otherwise unknown tag error.
      */

     /* Get the data - use the split tag bag_tag2b and bag_tag3 */
     OPEN c_get_bag_details( l_tag2b, l_tag3);
     FETCH c_get_bag_details INTO  l_bag_pax_id
                                  ,l_bag_tag1
                                  ,l_bag_tag2a
                                  ,l_bag_tag2b
                                  ,l_bag_tag3
                                  ,l_pax_class
                                  ,l_pax_bags_class
                                  ,l_pax_freq_flyer
                                  ,l_bsm_bag;

     /* Don't trap notfound or too many rows */
     IF ( SQL%NOTFOUND OR SQL%ROWCOUNT = 0) OR
        ( SQL%ROWCOUNT > 1 ) THEN
         NULL;
     END IF;

     /* Close cursor */
     CLOSE c_get_bag_details;

     /* Assign the correct loading strategy */
     IF ( l_load_all = 'TRUE' AND l_bsm_bag  IS NOT NULL) OR
        ( l_load_all = 'FALSE' ) THEN
          l_loading_strategy := CHECKEDIN; -- Normal rules apply
     ELSIF ( l_load_all = 'TRUE' AND l_bsm_bag IS NULL ) THEN
             l_loading_strategy := LOADALL;    -- Load all
     END IF;



     /* Force a flight closed status if got this far and the
      * flight_status is 'CLOSED'. No point to even try load
      * if the flight status doesn't allow the bag to load.
      */

      dbms_output.put_line(l_pd_marker);
      dbms_output.put_line(l_force_load);

      -- UAS/FALCP/CA/00802
      -- Add check of flight status using LOADING_PROHIBITED
      -- This will allow loading to PD and FC flights, if configured
      IF ( l_flight_status = pkg_constants.FLIGHT_CLOSED ) AND
         ( loading_prohibited(l_flight_id) ) THEN
          /* Assign Flight_Closed */
          l_result := pkg_constants.ERROR_FLIGHT_CLOSED;
      ELSIF (l_flight_status = pkg_constants.POST_DEPARTURE AND  -- we already trap this for force load
             l_pd_marker = 'Y' AND l_force_load = 'FALSE' AND
             loading_prohibited(l_flight_id) ) THEN
          l_result := pkg_constants.ERROR_FLIGHT_STATUS_PD;
      END IF;
      -- End of UAS/FALCP/CA/00802 changes

      /* Log if flight_status is CLOSED or PD */
      IF l_result < 0 THEN
          /* Some debug infor */
          IF ( DEBUG = VALID ) THEN
             dbms_output.put_line('Error due to flight_status');
          END IF;
         /* Create a trace record of any of the above errored */
          add_bag_trace_record( l_user_id
                               ,l_location_code
                               ,l_result    -- error_code
                               ,l_bag_tag
                               ,l_tag1
                               ,l_tag2a
                               ,l_tag2b
                               ,l_tag3
                               ,l_trace_action    -- trace_action
                               ,l_scanner_id
                               ,l_flight_id
                               ,l_bag_pax_id
                               ,l_uld_num    -- pass ULD number (Nigel, 01/04/04, CA598)
                               );

          RAISE error_results;
      END IF;

      -- ------------------------------------------
      -- UAS/FALCP/CA/00878
      -- Determine whether RELOAD is a valid option
      dbms_output.put_line('TSS_RELOAD_ALLOWED :'|| l_tss_reload);
      
      IF (l_tss_reload = 'Y') THEN
         --------------------------------
         -- UAS/FALCP/CA/00835 - start...
         --------------------------------
         DECLARE
             CURSOR c_check_bag_exists IS
               SELECT b.bag_tag
               FROM   bag_v2 b
               WHERE  b.bag_tag2b = l_tag2b
               AND    b.bag_tag3  = l_tag3;
                
             CURSOR c_bag_loaded IS
               SELECT b.loaded_status,
                      b.uld_no,
                      b.flight_id,
                      b.location_code,
                      b.pax_id,
                      b.pax_surname,
                      b.bag_exception
               FROM   bag_v2 b
               WHERE  b.bag_tag2b = l_tag2b
               AND    b.bag_tag3  = l_tag3
               AND    b.flight_id = l_flight_id -- use the uld flight to get the correct bag
               ORDER  by b.time_bag_stored desc;
            
            
            l_tag_exists    bag_v3.bag_tag%TYPE := NULL;
            l_loaded_status bag.loaded_status%TYPE;
            l_old_uld_no    bag.uld_no%TYPE;
            l_old_flight_id bag.uld_flight_id%TYPE;
            l_old_location_code bag.location_code%TYPE;
            l_action_1      bag_trace.trace_action%TYPE := 'RELOAD';
            l_action_2      bag_trace.trace_action%TYPE := 'OFFLOAD';
            l_error_id      bag_trace.error_id%TYPE     := 1011;
            l_offload_ret   NUMBER;
            indx            BINARY_INTEGER              := 0;
            
            -- rowtypes
            rec_bag_loaded  c_bag_loaded%ROWTYPE;
            TYPE loaded_bags_tt   IS TABLE OF bag_v2%ROWTYPE INDEX BY BINARY_INTEGER;
            
            TYPE loaded_status_tt IS TABLE OF bag_v2.loaded_status%TYPE INDEX BY BINARY_INTEGER;
            TYPE location_code_tt IS TABLE OF bag_v2.location_code%TYPE INDEX BY BINARY_INTEGER;
            TYPE flight_id_tt     IS TABLE OF bag_v2.flight_id%TYPE INDEX BY BINARY_INTEGER;
            TYPE uld_no_tt        IS TABLE OF bag_v2.uld_no%TYPE INDEX BY BINARY_INTEGER;
            TYPE pax_id_tt        IS TABLE OF bag_v2.pax_id%TYPE INDEX BY BINARY_INTEGER;
            TYPE pax_surname_tt   IS TABLE OF bag_v2.pax_surname%TYPE INDEX BY BINARY_INTEGER;
            TYPE bag_exception_tt IS TABLE OF bag_v2.bag_exception%TYPE INDEX BY BINARY_INTEGER;
            TYPE wrong_flight_tt  IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
             
            -- PL/SQL table declarations
            l_flight_id_tab      flight_id_tt;
            l_pax_id_tab         pax_id_tt;
            l_loaded_status_tab  loaded_status_tt;
            l_location_code_tab  location_code_tt;
            l_uld_no_tab         uld_no_tt;
            l_pax_surname_tab    pax_surname_tt;
            l_bag_exception_tab  bag_exception_tt;
            l_wrong_flight_tab   wrong_flight_tt;
            
            l_old_flight_id_tab      flight_id_tt;
            l_old_pax_id_tab         pax_id_tt;
            l_old_loaded_status_tab  loaded_status_tt;
            l_old_location_code_tab  location_code_tt;
            l_old_uld_no_tab         uld_no_tt;
            l_old_pax_surname_tab    pax_surname_tt;
            l_old_bag_exception_tab  bag_exception_tt;
            
         BEGIN
            /* Check for the existance of the bag */
            /* In normal load circumstances this would be found via */
            /* check_bag_for_load. But for RELOAD functionality, we still need
            /* to check for the existance of bags which may have differing flight
            /* details due to the time sequence scenarios*/
            
            OPEN c_check_bag_exists;
            FETCH c_check_bag_exists
            INTO  l_tag_exists;
         
            -- Carry on as normal if found a match. 
            IF (c_check_bag_exists%ROWCOUNT = 1 OR c_check_bag_exists%ROWCOUNT > 1) THEN
                NULL;
            END IF;
            
           -- If no bags exists then error this as an unknown tag. No need to carry
           -- on doing further checks.
            IF (c_check_bag_exists%ROWCOUNT = 0) THEN
                  l_result := pkg_constants.error_unknown_bag;
                  l_error_id := l_result;
                  add_bag_trace_record (l_user_id,
                                        l_location_code,
                                        l_error_id,         -- unknown tag
                                        l_bag_tag,
                                        l_tag1,
                                        l_tag2a,
                                        l_tag2b,
                                        l_tag3,
                                        l_trace_action,     -- trace action of 'LOAD' or 'FORCED'
                                        l_scanner_id,
                                        l_flight_id,        -- new Flight ID
                                        0,                  -- no bag, therefore no pax
                                        l_uld_num);         -- new ULD number
                  /* Must close the cursor before erroring */
                  CLOSE c_check_bag_exists;
                  RAISE error_results;          
            END IF;  
            CLOSE c_check_bag_exists;
          
            
            /* Get the bags. They could be loaded or not */
            OPEN c_bag_loaded;
            FETCH c_bag_loaded
            BULK COLLECT INTO  l_loaded_status_tab,
                      l_uld_no_tab,
                      l_flight_id_tab,
                      l_location_code_tab,
                      l_pax_id_tab,
                      l_pax_surname_tab,
                      l_bag_exception_tab;
            /*****************************************************************************/
            /* For duplicate bags, one of the tags may not match the uld.flight_id       */
            /* however, we do not want to error this tag, with the wrong flight error    */
            /* since we want to load the tag with the correct flight_id onto the correct */
            /* uld. Due to this issue, need to derive the wrong flight with a no match   */
            /* Since we know the tag exists and we are not interested in its loaded      */
            /* status, the failure to find the tag is a result of the wrong flight being */
            /* used. 
            /*****************************************************************************/          
            IF (c_bag_loaded%ROWCOUNT = 0)  THEN
             l_result := pkg_constants.error_wrong_flight;
             l_error_id := l_result;
             add_bag_trace_record (l_user_id,
                                   l_location_code,
                                   l_error_id,         -- wrong flight error
                                   l_bag_tag,
                                   l_tag1,
                                   l_tag2a,
                                   l_tag2b,
                                   l_tag3,
                                   l_trace_action,     -- trace action of 'LOAD'
                                   l_scanner_id,
                                   l_flight_id,        -- new Flight ID
                                   0,                  -- no match
                                   l_uld_num);         -- new ULD number
 
             /* Must close prior to erroring */
             CLOSE c_bag_loaded;
             RAISE error_results;
            END IF;
            CLOSE c_bag_loaded;
 
            -- if some data is found
            IF l_pax_id_tab.COUNT > 0 THEN
                       
            FOR indx IN l_pax_id_tab.FIRST..l_pax_id_tab.LAST LOOP    
            dbms_output.put_line('----------------------------------------');
            dbms_output.put_line('LOADED_STATUS :'||l_loaded_status_tab(indx));
            dbms_output.put_line('ULD_NO        :'||l_uld_no_tab(indx));
            dbms_output.put_line('FLIGHT_ID     :'||l_flight_id_tab(indx));  --bag flight id
            dbms_output.put_line('LOCATION_CODE :'||l_location_code_tab(indx));
            dbms_output.put_line('PAX_ID        :'||l_pax_id_tab(indx));
            dbms_output.put_line('PAX_SURNAME   :'||l_pax_surname_tab(indx));
            dbms_output.put_line('BAG_EXCEPTION :'||l_bag_exception_tab(indx));
            dbms_output.put_line('----------------------------------------');
                    
            IF l_loaded_status_tab(indx) = 'Y' THEN 
            -- this becomes the old and should be offloaded 
             l_old_uld_no_tab(indx)        := l_uld_no_tab(indx);
             l_old_flight_id_tab(indx)     := l_flight_id_tab(indx);
             l_old_location_code_tab(indx) := l_location_code_tab(indx);
             l_old_pax_id_tab(indx)        := l_pax_id_tab(indx);
            
               
            
            /* Get the Old uld_no, old_flight, old_pax, old_bag etc i.e. */
            /* the old one is the one which has been loaded              */
            dbms_output.put_line('----NEW BAG DETAILS -----');
            dbms_output.put_line('l_location_code :'||l_location_code);
            dbms_output.put_line('l_location_code :'||l_location_code_tab(indx));
            dbms_output.put_line('l_erorr_id      :'||l_error_id);
            dbms_output.put_line('l_bag_tag       :'||l_bag_tag);
            dbms_output.put_line('l_action_1      :'||l_action_1);
            dbms_output.put_line('l_flight_id     :'||l_flight_id);  --uld flight id
            dbms_output.put_line('l_uld_num       :'||l_uld_num);
            dbms_output.put_line('l_pax_id        :'||l_pax_id_tab(indx));
            
            
            dbms_output.put_line('----OLD BAG DETAILS -----');
            dbms_output.put_line('l_old_location_code :'||l_old_location_code_tab(indx));
            dbms_output.put_line('l_old_flight_id     :'||l_old_flight_id_tab(indx));
            dbms_output.put_line('l_old_uld_no        :'||l_old_uld_no_tab(indx));
            dbms_output.put_line('l_old_pax_id        :'||l_old_pax_id_tab(indx));
            dbms_output.put_line('l_erorr_id          :'||l_error_id);
            dbms_output.put_line('l_bag_tag           :'||l_bag_tag);
            dbms_output.put_line('l_action_2          :'||l_action_2);
            
               -- Flight Access Checks
               user_authentication.validate_access(l_user_id,l_flight_id,null,null,l_retcode);
               IF ( l_retcode < 0 ) THEN
                  l_result:= pkg_constants.ERROR_FLIGHT_ACCESS_DENIED; --(-1031);
                  RAISE error_flight_access;
               END IF;

               /*******************************************************************/
               /* The following are possible scenarios to occur:                  */
               /* single bag does not exist                                       */
               /* single bag to be loaded onto correct uld                        */
               /* single bag to be loaded onto the wrong uld                      */
               /* same bag tag, same flights - UNRESOLVABLE erors, load via MWS   */
               /* same bag tag, different flights - RESOLVABLE and LOADED by TSS  */
               /*******************************************************************/
               /* The following errors should be returned.                        */
               /* UNKNOWN_TAG  - the bag does not exist in the bag table, a bag   */
               /*                trace record should be created for the unkonw tag*/
               /* WRONG_FLIGHT - the bag cannot be loaded into a ULD where the    */
               /*                flights do not match. A bag trace record should  */
               /*                be created for the wrong flight                  */
               /* WRONG_CLASS  - the bag and uld class do no match                */
               /* WRONG_TYPE   - the uld types are different                      */
               /* UNRESOLVABLE - Same tags, same flight when loading get this     */
               /* RELOAD       - Get this in bag trace when do a double scan of   */
               /*                the same tag onto the uld.                       */
               /*******************************************************************/
               IF l_flight_id = l_flight_id_tab(indx) THEN 
               add_bag_trace_record (l_user_id,
                                     l_location_code,
                                     l_error_id,         -- new positive error ID
                                     l_bag_tag,
                                     l_tag1,
                                     l_tag2a,
                                     l_tag2b,
                                     l_tag3,
                                     l_action_1,         -- new trace action of 'RELOAD'
                                     l_scanner_id,
                                     l_flight_id,        -- new Flight ID
                                     l_bag_pax_id,
                                     l_uld_num);         -- new ULD number
               offload_bag          (l_tag2a,
                                     l_tag2b,
                                     l_tag3,
                                     l_old_pax_id_tab(indx),
                                     l_old_uld_no_tab(indx),       -- old ULD number
                                     l_location_code,
                                     l_scanner_id,
                                     l_old_flight_id_tab(indx),    -- old Flight ID
                                     l_offload_ret);
               add_bag_trace_record (l_user_id,
                                     l_old_location_code_tab(indx),-- old Location Code
                                     l_offload_ret,
                                     l_bag_tag,
                                     l_tag1,
                                     l_tag2a,
                                     l_tag2b,
                                     l_tag3,
                                     l_action_2,         -- trace action of 'OFFLOAD'
                                     l_scanner_id,
                                     l_old_flight_id_tab(indx),    -- old Flight ID
                                     l_old_pax_id_tab(indx),
                                     l_old_uld_no_tab(indx));      -- old ULD number
               END IF;
               
               IF mws_web.store_bpm1( l_old_location_code_tab(indx) 
                              ,l_user_id
                              ,l_old_flight_id_tab(indx)
                              ,l_old_pax_id_tab(indx)
                              ,l_bag_tag
                              ,mws_web.f_pax_sequence_no(l_old_pax_id_tab(indx), l_old_flight_id_tab(indx))
                              ,l_action_2
                             ) then
		 null;
               END IF;

               IF l_offload_ret != pkg_constants.success THEN
                  l_result := pkg_constants.error_general_exception;
                  RAISE error_results;
               END IF;
            
             END IF;
             END LOOP;
           END IF;
         END;
         -------------------------------
         -- UAS/FALCP/CA/00835 - ... end
         -------------------------------

      END IF;
      -- End of UAS/FALCP/CA/00878
      -- ------------------------------------------
     /* Force Loading uses current mws_web functionality (load_a_bag)
      * However, when loading startegy is LOADALL cannot be catered for
      * since this is a very basic checking mechanism.
      */
     IF ( l_force_load = 'TRUE' AND l_loadbag_priv = VALID ) AND
        ( l_load_all   = 'FALSE') THEN

         IF ( DEBUG = VALID ) THEN
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('Force Loading ...................');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('l_uld_num          :'||l_uld_num);
               dbms_output.put_line('l_loading_strategy :'||l_loading_strategy);
               dbms_output.put_line('l_tag1             :'||l_tag1);
               dbms_output.put_line('l_tag2a            :'||l_tag2a);
               dbms_output.put_line('l_tag2b            :'||l_tag2b);
               dbms_output.put_line('l_tag3             :'||l_tag3);
               dbms_output.put_line('l_pax_seq_no       :'||l_pax_seq_no);
               dbms_output.put_line('l_retval           :'||l_retval);
               dbms_output.put_line('l_tuld_destination :'||l_tuld_destination);
               dbms_output.put_line('l_tuld_class_list  :'||l_tuld_class_list);
               dbms_output.put_line('l_pax_id           :'||l_pax_id);
               dbms_output.put_line('l_bag_pax_id       :'||l_bag_pax_id);
               dbms_output.put_line('l_flight_id (ULD)  :'||l_flight_id);
               dbms_output.put_line('l_bsm_bag          :'||l_bsm_bag);
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('CLP Specific....................');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('l_pax_class        :'||l_pax_class);
               dbms_output.put_line('l_pax_bags_class   :'||l_pax_bags_class);
               dbms_output.put_line('l_pax_freq_flyer   :'||l_pax_freq_flyer);
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('Flight_status      :'||l_flight_status);
               dbms_output.put_line('l_trace_action     :'||l_trace_action);
           END IF;

          /* Call check_bag_for_load */
           /* now do the main processing to check bag for loadall*/
           check_bag_for_load ( uldno           => l_uld_num
                               ,ldg_strat       => l_loading_strategy
                               ,tag1            => l_tag1
                               ,tag2a           => l_tag2a
                               ,tag2b           => l_tag2b
                               ,tag3            => l_tag3
                               ,sno             => l_pax_seq_no
                               ,fid             => l_flight_id
                               ,ufid            => 0 --l_flight_id
                               ,retval          => l_retval
                               ,tuld_destination=> l_tuld_destination
                               ,tuld_class_list => l_tuld_class_list
                               ,tpax_id         => l_pax_id
                               ,t2flight_id     => l_flight_id
                               ,paxseqno        => l_pax_seq_no
			       ,p_handle_dupes  => handle_dupes
			       ,p_security_result  => l_security_result
			       ,p_security_reason  => l_security_reason);

            IF ( l_retval = pkg_constants.ERROR_DUPLICATE_BAG ) THEN
            /* Add bag trace record first and then recall check_bag_for_load */
              /* Assign a pax for the trace file */
                 IF l_pax_id = 0 THEN
                    l_pax_id := l_bag_pax_id;
                 END IF;
                 
                 check_bag_for_load ( uldno       => l_uld_num
                                 ,ldg_strat       => l_loading_strategy
                                 ,tag1            => l_tag1
                                 ,tag2a           => l_tag2a
                                 ,tag2b           => l_tag2b
                                 ,tag3            => l_tag3
                                 ,sno             => 0 --l_pax_seq_no
                                 ,fid             => l_flight_id
                                 ,ufid            => l_flight_id
                                 ,retval          => l_retval
                                 ,tuld_destination=> l_tuld_destination
                                 ,tuld_class_list => l_tuld_class_list
                                 ,tpax_id         => l_pax_id
                                 ,t2flight_id     => l_flight_id
                                 ,paxseqno        => l_pax_seq_no
				 ,p_handle_dupes  => handle_dupes
			         ,p_security_result  => l_security_result
			         ,p_security_reason  => l_security_reason);
            END IF;

            IF ( DEBUG = VALID ) THEN
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('After call to check_bag_for_load');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('In parameters:..................');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('l_uld_num          :'||l_uld_num);
               dbms_output.put_line('l_loading_strategy :'||l_loading_strategy);
               dbms_output.put_line('l_tag1             :'||l_tag1);
               dbms_output.put_line('l_tag2a            :'||l_tag2a);
               dbms_output.put_line('l_tag2b            :'||l_tag2b);
               dbms_output.put_line('l_tag3             :'||l_tag3);
               dbms_output.put_line('l_pax_seq_no       :'||l_pax_seq_no);
               dbms_output.put_line('l_flight_id (ULD)  :'||l_flight_id);

               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('Out parameters..................');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('l_retval           :'||l_retval);
               dbms_output.put_line('l_tuld_destination :'||l_tuld_destination);
               dbms_output.put_line('l_tuld_class_list  :'||l_tuld_class_list);
               dbms_output.put_line('l_pax_id           :'||l_pax_id);
               dbms_output.put_line('l_flight_id        :'||l_flight_id);
               dbms_output.put_line('l_bsm_bag          :'||l_bsm_bag);
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('CLP Specific....................');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('l_pax_class        :'||l_pax_class);
               dbms_output.put_line('l_pax_bags_class   :'||l_pax_bags_class);
               dbms_output.put_line('l_pax_freq_flyer   :'||l_pax_freq_flyer);
               dbms_output.put_line('----------------------------------------');
            END IF;

            /* Now deal with the error returned back from check_bag_for_load */
            IF ( DEBUG = VALID ) THEN
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('Check whether this bag had a clp upgrade');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('l_pax_class       :'||l_pax_class);
               dbms_output.put_line('l_pax_bags_class  :'||l_pax_bags_class);
               dbms_output.put_line('l_tuld_class_list :'||l_tuld_class_list);
               dbms_output.put_line('instr freq_flyer  :'||INSTR(UPPER(l_pax_freq_flyer),'.Y'));
               dbms_output.put_line('instr tuld_class  :'||INSTR(trim(l_tuld_class_list),l_pax_bags_class));
               dbms_output.put_line('l_retval          :'||l_retval);
               dbms_output.put_line('----------------------------------------');
            END IF;




           /* Now deal with the error returned back from check_bag_for_load */
            IF ( l_retval = pkg_constants.ERROR_FLIGHT_STATUS_PD OR
                 l_retval = pkg_constants.ERROR_UNRESOLVABLE_DUP_TAG OR
                 l_retval = pkg_constants.ERROR_BAG_NOT_ATL_DCS  OR
                 l_retval = pkg_constants.ERROR_BAG_NOT_ATL_BRS  OR
                 l_retval = pkg_constants.ERROR_WRONG_FLIGHT     OR
                 l_retval = pkg_constants.ERROR_DELETED_BAGS     OR
                 l_retval = pkg_constants.ERROR_ALREADY_LOADED   OR
                 l_retval = pkg_constants.ERROR_UNKNOWN_BAG      ) THEN
                 l_result := l_retval;

                  /* Assign a pax for the trace file */
                 IF l_pax_id = 0 THEN
                    l_pax_id := l_bag_pax_id;
                 END IF;

                 
                 /* Create a trace record of any of the above errored */
                 add_bag_trace_record( l_user_id
                                      ,l_location_code
                                      ,l_retval    -- error_code
                                      ,l_bag_tag
                                      ,l_tag1
                                      ,l_tag2a
                                      ,l_tag2b
                                      ,l_tag3
                                      ,l_trace_action -- trace_action
                                      ,l_scanner_id
                                      ,l_flight_id
                                      ,l_pax_id
                                      ,l_uld_num    -- pass ULD number (Nigel, 01/04/04, CA598)
                                      );
                 /* Error this out */
                 RAISE error_results;
            END IF;

        IF ( DEBUG = VALID) THEN
           dbms_output.put_line('----------------------------------------');
           dbms_output.put_line('Before call to mws_web.load_a_bag..');
           dbms_output.put_line('----------------------------------------');
           dbms_output.put_line('l_uld_no  :'||l_uld_num);
           dbms_output.put_line('l_bag_tag :'||l_bag_tag);
           dbms_output.put_line('----------------------------------------');
        END IF;

        /* Force Load the bag regardless of error except those above */
        mws_web.load_a_bag ( p_location_code  =>  l_location_code
                            ,p_user_id        =>  l_user_id
                            ,p_flight_id      =>  l_flight_id
                            ,p_uld_no         =>  l_uld_num
                            ,p_bag_tag        =>  l_bag_tag
                            ,p_serial_no     =>  '0'
                            ,p_error_message  =>  l_error_message
                            ,p_error_number   =>  l_error_number);


        /* Do some debug, can be switched off */
        /* Errors returned from mws_web.load_a_bag are: -208, -1003, */
        IF ( DEBUG = VALID) THEN
           dbms_output.put_line('----------------------------------------');
           dbms_output.put_line('After call to mws_web.load_a_bag..');
           dbms_output.put_line('----------------------------------------');
           dbms_output.put_line('l_error_number  :'||l_error_number);
           dbms_output.put_line('l_error_message :'||l_error_message);
           dbms_output.put_line('----------------------------------------');
        END IF;

        /* Update bag_trace setting trace action to 'FORCED' */


        UPDATE bag_trace bt
        SET    bt.trace_action   = 'FORCED',
               bt.scanner_id     = l_scanner_id
        WHERE  bt.user_id        = l_user_id
        AND    bt.bag_tag2b      = l_tag2b
        AND    bt.bag_tag3       = l_tag3
        AND    bt.location_code  = l_location_code
        AND    bt.trace_action   = 'MWS_LOAD'
        AND    bt.error_id       = 0
        AND    bt.scanner_id     = '0'
        AND    bt.flight_id_scan = l_flight_id;

        IF SQL%NOTFOUND THEN
           NULL;
        END IF;

        /* Now deal with the error returned back from mws.load_bag */
        IF ( l_error_number < 0 ) OR
           ( l_error_number = pkg_constants.ERROR_FLIGHT_STATUS_PD OR
             l_error_number = pkg_constants.ERROR_DUPLICATE_BAG    OR
             l_error_number = pkg_constants.ERROR_UNRESOLVABLE_DUP_TAG OR
             l_error_number = pkg_constants.ERROR_BAG_NOT_ATL_DCS  OR
             l_error_number = pkg_constants.ERROR_BAG_NOT_ATL_BRS  OR
             l_error_number = pkg_constants.ERROR_WRONG_FLIGHT     OR
             l_error_number = pkg_constants.ERROR_DELETED_BAGS     OR
             l_error_number = pkg_constants.ERROR_ALREADY_LOADED OR
             l_error_number = pkg_constants.ERROR_UNKNOWN_BAG) THEN

             /* Assign a pax for the trace file */
             IF l_pax_id = 0 THEN
                l_pax_id := l_bag_pax_id;
             END IF;

             IF (l_tss_reload = 'Y') AND (l_retval != pkg_constants.ERROR_DUPLICATE_BAG) OR
                (l_tss_reload = 'N') THEN
             /* Create a trace record of any of the above errored */
             add_bag_trace_record( l_user_id
                                  ,l_location_code
                                  ,l_error_number    -- error_code
                                  ,l_bag_tag
                                  ,l_tag1
                                  ,l_tag2a
                                  ,l_tag2b
                                  ,l_tag3
                                  ,l_trace_action    -- trace_action
                                  ,l_scanner_id
                                  ,l_flight_id
                                  ,l_pax_id
                                  ,l_uld_num    -- pass ULD number (Nigel, 01/04/04, CA598)
                                  );
             END IF;

             l_result := l_error_number;
             RAISE error_results;
        ELSIF ( l_error_number = pkg_constants.LOAD_BAG_SUCCESS ) THEN
                l_result := pkg_constants.LOAD_BAG_SUCCESS;
        ELSIF ( l_error_number = DATA_NOT_FOUND ) THEN  -- no bag found
                RAISE no_bag_details;
        ELSIF ( l_error_number > DATA_NOT_FOUND ) THEN
                RAISE error_general_exception;
        END IF;


     ELSIF ( l_force_load = 'FALSE' OR l_load_all = 'TRUE' ) AND
           ( l_loadbag_priv = VALID ) THEN /* If loadall then assume force_load */


            /* Some debug information */
            IF ( DEBUG = VALID ) THEN
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('Before call to check_bag_for_load');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('In parameters:..................');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('l_uld_num          :'||l_uld_num);
               dbms_output.put_line('l_loading_strategy :'||l_loading_strategy);
               dbms_output.put_line('l_tag1             :'||l_tag1);
               dbms_output.put_line('l_tag2a            :'||l_tag2a);
               dbms_output.put_line('l_tag2b            :'||l_tag2b);
               dbms_output.put_line('l_tag3             :'||l_tag3);
               dbms_output.put_line('l_pax_seq_no       :'||l_pax_seq_no);
               dbms_output.put_line('l_tuld_destination :'||l_tuld_destination);
               dbms_output.put_line('l_tuld_class_list  :'||l_tuld_class_list);
               dbms_output.put_line('l_pax_id           :'||l_pax_id);
               dbms_output.put_line('l_flight_id (ULD)  :'||l_flight_id);
               dbms_output.put_line('l_bsm_bag          :'||l_bsm_bag);
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('CLP Specific.....................');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('l_pax_class        :'||l_pax_class);
               dbms_output.put_line('l_pax_bags_class   :'||l_pax_bags_class);
               dbms_output.put_line('l_pax_freq_flyer   :'||l_pax_freq_flyer);
               dbms_output.put_line('----------------------------------------');
            END IF;


           /* now do the main processing to check bag for loadall*/
           check_bag_for_load ( uldno           => l_uld_num
                                   ,ldg_strat       => l_loading_strategy
                                     ,tag1            => l_tag1
                                     ,tag2a           => l_tag2a
                                     ,tag2b           => l_tag2b
                                     ,tag3            => l_tag3
                                     ,sno             => l_pax_seq_no
                                     ,fid             => l_flight_id
                                     ,ufid            => 0 --l_flight_id
                                     ,retval          => l_retval
                                     ,tuld_destination=> l_tuld_destination
                                     ,tuld_class_list => l_tuld_class_list
                                     ,tpax_id         => l_pax_id
                                     ,t2flight_id     => l_flight_id
                                     ,paxseqno        => l_pax_seq_no
				     ,p_handle_dupes  => handle_dupes
				     ,p_security_result  => l_security_result
				     ,p_security_reason  => l_security_reason);

            IF ( l_retval = pkg_constants.ERROR_DUPLICATE_BAG ) THEN
                 IF l_pax_id = 0 THEN
                    l_pax_id := l_bag_pax_id;
                 END IF;

                 check_bag_for_load ( uldno           => l_uld_num
                                   ,ldg_strat       => l_loading_strategy
                                     ,tag1            => l_tag1
                                     ,tag2a           => l_tag2a
                                     ,tag2b           => l_tag2b
                                     ,tag3            => l_tag3
                                     ,sno             => 0 --l_pax_seq_no
                                     ,fid             => l_flight_id
                                     ,ufid            => l_flight_id
                                     ,retval          => l_retval
                                     ,tuld_destination=> l_tuld_destination
                                     ,tuld_class_list => l_tuld_class_list
                                     ,tpax_id         => l_pax_id
                                     ,t2flight_id     => l_flight_id
                                     ,paxseqno        => l_pax_seq_no
				     ,p_handle_dupes  => handle_dupes
				     ,p_security_result  => l_security_result
				     ,p_security_reason  => l_security_reason);


            END IF;

            IF ( DEBUG = VALID ) THEN
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('After call to check_bag_for_load');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('In parameters:..................');
               dbms_output.put_line('l_uld_num          :'||l_uld_num);
               dbms_output.put_line('l_loading_strategy :'||l_loading_strategy);
               dbms_output.put_line('l_tag1             :'||l_tag1);
               dbms_output.put_line('l_tag2a            :'||l_tag2a);
               dbms_output.put_line('l_tag2b            :'||l_tag2b);
               dbms_output.put_line('l_tag3             :'||l_bag_tag3);
               dbms_output.put_line('l_pax_seq_no       :'||l_pax_seq_no);
               dbms_output.put_line('l_flight_id(ULD)   :'||l_flight_id);
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('Out parameters..................');
               dbms_output.put_line('l_retval           :'||l_retval);
               dbms_output.put_line('l_tuld_destination :'||l_tuld_destination);
               dbms_output.put_line('l_tuld_class_list  :'||l_tuld_class_list);
               dbms_output.put_line('l_pax_id           :'||l_pax_id);
               dbms_output.put_line('l_flight_id        :'||l_flight_id);
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('CLP Specific....................');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('l_pax_class        :'||l_pax_class);
               dbms_output.put_line('l_pax_bags_class   :'||l_pax_bags_class);
               dbms_output.put_line('l_pax_freq_flyer   :'||l_pax_freq_flyer);
               dbms_output.put_line('----------------------------------------');
            END IF;

            /* Now deal with the error returned back from check_bag_for_load */
            IF ( DEBUG = VALID ) THEN
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('Check whether this bag had a clp upgrade');
               dbms_output.put_line('----------------------------------------');
               dbms_output.put_line('l_pax_class       :'||l_pax_class);
               dbms_output.put_line('l_pax_bags_class  :'||l_pax_bags_class);
               dbms_output.put_line('l_tuld_class_list :'||l_tuld_class_list);
               dbms_output.put_line('instr freq_flyer  :'||INSTR(UPPER(l_pax_freq_flyer),'.Y'));
               dbms_output.put_line('instr tuld_class  :'||INSTR(trim(l_tuld_class_list),l_pax_bags_class));
               dbms_output.put_line('l_retval          :'||l_retval);
               dbms_output.put_line('----------------------------------------');
            END IF;

            /* Need to trap loading of bags by loadall pax. Initially
             * pax_id = 0
             */

           -- UAS/FALCP/CA/00802
           -- CHECK_BAG_FOR_LOAD will return ERROR_UNKNOWN_ULD if flight status is PD
           -- Since the ULD has already been checked and is known to be valid, reset this error
           -- Also, CHECK_BAG_FOR_LOAD will fail to assign the PAX_ID, so use the PAX_ID from this bag
           /* Deal with a ERROR_UNKNOWN_ULD error that will be returned if attempting to load
              a bag into a PD or FC flight */
           IF (l_retval = pkg_constants.ERROR_UNKNOWN_ULD) THEN
             l_retval := pkg_constants.LOAD_BAG_SUCCESS;
             dbms_output.put_line('l_bag_pax_id  :'||l_bag_pax_id);
             IF NVL(l_pax_id,0) = 0 THEN
                l_pax_id := l_bag_pax_id;
             END IF;
             dbms_output.put_line('l_pax_id      :'||l_pax_id);
           END IF;
           -- End of UAS/FALCP/CA/00802



           /* Now deal with the error returned back from check_bag_for_load */
            IF ( l_retval < 0) AND l_retval != pkg_constants.ERROR_DUPLICATE_BAG THEN
                 /* Assign a pax for the trace file */
                 IF l_pax_id = 0 THEN
                    l_pax_id := l_bag_pax_id;
                 END IF;

                 IF (l_tss_reload = 'Y') AND (l_retval != pkg_constants.ERROR_DUPLICATE_BAG) OR
                     (l_tss_reload = 'N') THEN
                      add_bag_trace_record( l_user_id
                                ,l_location_code
                                ,l_retval    -- error_code
                                ,l_bag_tag
                                ,l_tag1
                                ,l_tag2a
                                ,l_tag2b
                                ,l_tag3
                                ,l_trace_action -- trace_action
                                ,l_scanner_id
                                ,l_flight_id
                                ,l_pax_id
                                ,l_uld_num    -- pass ULD number (Nigel, 01/04/04, CA598)
                                 );
                 END IF;
                 l_result := l_retval;
                 RAISE error_results;
            ELSIF ( l_retval = 1 AND  l_loading_strategy = LOADALL)  THEN
                  IF ( DEBUG = VALID ) THEN
                     dbms_output.put_line('----------------------------------------');
                     dbms_output.put_line('Doing LoadALL functionality');
                     dbms_output.put_line('----------------------------------------');
                  END IF;

                  /* Assign the pax id */
                  l_pax_id_loadall := l_pax_id;

                  
                  /* Load bag functionality */
                  add_loaded_bag ( l_uld_num
                                  ,l_bag_tag
                                  ,l_tag1
                                  ,l_tag2a
                                  ,l_tag2b
                                  ,l_tag3
                                  ,l_pax_id_loadall
                                  ,l_flight_id
                                  ,l_tuld_destination
                                  ,l_pax_class_loadall
                                  ,l_location_code
                                  ,l_scanner_id
                                  ,l_retval_loadall -- Returned values are: -1 (unknown uld),
                                  );                -- -2 (no loadall pax),  > 0 for the pax_id
                   
                   IF (l_tss_reload = 'Y') AND (l_retval != pkg_constants.ERROR_DUPLICATE_BAG) OR
                  (l_tss_reload = 'N') THEN
                   /* Create a bag_trace record for the pax loadall */
                   add_bag_trace_record( l_user_id
                                ,l_location_code
                                ,pkg_constants.LOAD_BAG_SUCCESS    -- error_code
                                ,l_bag_tag
                                ,l_tag1
                                ,l_tag2a
                                ,l_tag2b
                                ,l_tag3
                                ,l_trace_action       -- trace_action
                                ,l_scanner_id
                                ,l_flight_id
                                ,l_pax_id_loadall
                                ,l_uld_num    -- pass ULD number (Nigel, 01/04/04, CA598)
                                 );
                  END IF;
                  /* Generate BPM for loaded bag */
                  IF mws_web.store_bpm1(l_location_code
                                       ,l_user_id
                                       ,l_flight_id
                                       ,l_pax_id_loadall
                                       ,get_numeric_bag_tag(l_bag_tag)
                                       ,l_pax_seq_no
                                       ,'LOAD') then
		    null;
                  END IF;

                  /* If there is an error i.e. no loadall pax then error */
                  IF ( DEBUG = VALID ) THEN
                     dbms_output.put_line('----------------------------------------');
                     dbms_output.put_line('Loadall Pax         :'||l_pax_id_loadall);
                     dbms_output.put_line('----------------------------------------');
                     dbms_output.put_line('l_uld_num           :'||l_uld_num);
                     dbms_output.put_line('l_bag_tag           :'||l_bag_tag);
                     dbms_output.put_line('l_flight_id (ULD)   :'||l_flight_id);
                     dbms_output.put_line('l_retval_loadall    :'||l_retval_loadall);
                     dbms_output.put_line('l_pax_id            :'||l_pax_id);
                     dbms_output.put_line('l_pax_class_loadall :'||l_pax_class_loadall);
                     dbms_output.put_line('l_tuld_destination  :'||l_tuld_destination);
                     dbms_output.put_line('----------------------------------------');
                  END IF;

                  /* Deal with any errors */
                  IF l_retval_loadall < 0 THEN
                     l_result := pkg_constants.ERROR_GENERAL_EXCEPTION;
                  ELSIF (l_retval_loadall >  pkg_constants.LOAD_BAG_SUCCESS) THEN
                     l_result := pkg_constants.LOAD_BAG_SUCCESS;
                  END IF;


            ELSIF ( l_retval = pkg_constants.LOAD_BAG_SUCCESS ) AND
                  ( l_loading_strategy = CHECKEDIN ) THEN
                  /* Some debug information */
                  IF ( DEBUG = VALID ) THEN
                      dbms_output.put_line('----------------------------------------');
                      dbms_output.put_line('Before load_bag..');
                      dbms_output.put_line('----------------------------------------');
                      dbms_output.put_line('l_retval        :'||l_retval);
                      dbms_output.put_line('l_pax_id        :'||l_pax_id);
                      dbms_output.put_line('l_tag2a         :'||l_tag2a);
                      dbms_output.put_line('l_tag2b         :'||l_tag2b);
                      dbms_output.put_line('l_tag3          :'||l_tag3);
                      dbms_output.put_line('l_uld_num       :'||l_uld_num);
                      dbms_output.put_line('l_location_code :'||l_location_code);
                      dbms_output.put_line('----------------------------------------');
                 END IF;


                 /* Call load all */
                 load_bag( l_tag2a
                          ,l_tag2b
                          ,l_tag3
                          ,l_pax_id
                          ,l_uld_num
                          ,l_location_code
                          ,l_scanner_id
                          ,l_flight_id
                          ,l_retval
                         );

               /***************************************************************/
               /* Only create bag_tace records of ALL bag load error cases    */
               /* including successful load cases for NON-RELOAD processing.  */
               /* If the processing is RELOAD, no need to show duplicate bag  */
               /* errors since this increases the count of the flight status  */
               /* rejects screen and is not a true reflection the bag status. */
               /***************************************************************/
               IF (l_tss_reload = 'Y') AND (l_retval != pkg_constants.ERROR_DUPLICATE_BAG) OR
                  (l_tss_reload = 'N') THEN
                 add_bag_trace_record( l_user_id
                                ,l_location_code
                                ,l_retval    -- error_code
                                ,l_bag_tag
                                ,l_tag1
                                ,l_tag2a
                                ,l_tag2b
                                ,l_tag3
                                ,l_trace_action -- trace_action
                                ,l_scanner_id
                                ,l_flight_id
                                ,l_pax_id
                                ,l_uld_num    -- pass ULD number (Nigel, 01/04/04, CA598)
                                 );
                END IF;
                
                /* Generate BPM for loaded bag */
                IF mws_web.store_bpm1(l_location_code
                                     ,l_user_id
                                     ,l_flight_id
                                     ,l_pax_id
                                     ,get_numeric_bag_tag(l_bag_tag)
                                     ,l_pax_seq_no
                                     ,'LOAD') THEN
		    null;
                END IF;

                /* Some more debug information */
                IF ( DEBUG = VALID) THEN
                     dbms_output.put_line('----------------------------------------');
                     dbms_output.put_line('After load_bag...');
                     dbms_output.put_line('----------------------------------------');
                     dbms_output.put_line('l_retval :'||l_retval);
                     dbms_output.put_line('----------------------------------------');
                END IF;

                /* Now deal with the error returned back from load_bag */
                IF ( l_retval < 0 ) THEN
                     l_result := l_retval;
                     RAISE error_results;
                ELSIF ( l_retval = pkg_constants.LOAD_BAG_SUCCESS ) THEN
                        l_result := pkg_constants.LOAD_BAG_SUCCESS;
                ELSIF ( l_retval = DATA_NOT_FOUND ) THEN  -- no_bag_details found
                     RAISE no_bag_details;
                ELSIF ( l_retval > DATA_NOT_FOUND ) THEN
                     RAISE error_general_exception;
                END IF;

            END IF;

     ELSIF  ( l_loadbag_priv = DENIED ) THEN
              l_result := pkg_constants.ERROR_USER_NOT_AUTHORISED;
     END IF;

     /* Do some debug, can be switched off */
     IF ( DEBUG = VALID) THEN
        dbms_output.put_line('---------------------------------------');
        dbms_output.put_line('Final errors........');
        dbms_output.put_line('---------------------------------------');
        dbms_output.put_line('l_error_message :'||l_error_message);
        dbms_output.put_line('l_error_number  :'||l_error_number);
        dbms_output.put_line('l_result        :'||l_result);
        dbms_output.put_line('----------------------------------------');
     END IF;

     /* Return result */
     p_result := l_result||','||l_scanner_locking_disabled;

EXCEPTION
    WHEN error_flight_closed THEN
     /* Some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('Error flight is closed..');
     END IF;

     -- Flight Status is Closed
     l_result  := pkg_constants.ERROR_FLIGHT_CLOSED;
     p_result  := l_result||','||l_scanner_locking_disabled;

     WHEN error_pd_flight THEN
     /* Some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('Error flight is post departure..');
     END IF;

     -- Flight Status Post Departure
     l_result  := pkg_constants.ERROR_FLIGHT_STATUS_PD;
     p_result  := l_result||','||l_scanner_locking_disabled;

    WHEN error_duplicate_bag THEN
     /* Some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('Error duplicate bag found..');
     END IF;

     -- Duplicate Bag
     l_result  := pkg_constants.ERROR_DUPLICATE_BAG;
     p_result  := l_result||','||l_scanner_locking_disabled;

    WHEN error_general_exception THEN
     /* Some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('Error general exception ');
     END IF;

     -- General error
     l_result  := pkg_constants.ERROR_GENERAL_EXCEPTION;
     p_result  := l_result||','||l_scanner_locking_disabled;

    WHEN unknown_user  THEN
     IF c_get_user_details%ISOPEN THEN
        CLOSE c_get_user_details;
     END IF;

     /* Do some debug, can be switched off */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('-------------------------------------');
        dbms_output.put_line('No Such User, may need to error this ');
     END IF;

     -- General error
     l_result  := pkg_constants.ERROR_GENERAL_EXCEPTION;
     p_result := l_result||','||l_scanner_locking_disabled;

    WHEN no_uld_details THEN
     IF c_get_uld_details%ISOPEN THEN
        CLOSE c_get_uld_details;
     END IF;

     /* Do some debug, can be switched off */
     IF ( DEBUG = VALID) THEN
        dbms_output.put_line('-------------------------------');
        dbms_output.put_line('No Such ULD ');
     END IF;

     -- ULD not known
     l_result  := pkg_constants.ERROR_UNKNOWN_ULD;
     p_result := l_result||','||l_scanner_locking_disabled;

     WHEN no_bag_details THEN
     /* Close cursors */
     IF c_get_bag_details%ISOPEN THEN
        CLOSE c_get_bag_details;
     END IF;

     /* Do some debug, can be switched off */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('---------------------');
        dbms_output.put_line('No bag details found ');
     END IF;

     -- Bag is not known
     l_result  := pkg_constants.ERROR_UNKNOWN_BAG;
     p_result := l_result||','||l_scanner_locking_disabled;

    WHEN upd_bag_trace_err THEN
     /* Do some debug, can be switched off */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('---------------------');
        dbms_output.put_line('Error updating bag trace');
     END IF;

    WHEN error_flight_access THEN
     /* Some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('User does not have access to flight ');
     END IF;

     -- Flight Access Error
     l_result  := pkg_constants.ERROR_FLIGHT_ACCESS_DENIED;
     p_result  := l_result;

    WHEN error_results THEN
     /* Do some debug, can be switched off */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('---------------------');
        dbms_output.put_line('Error results');
     END IF;

     -- Error pre-set
     p_result  := l_result||','||l_scanner_locking_disabled;

     WHEN OTHERS THEN
      /* General catch all error, inserts into Oracle_error table */
      l_error_msg  := SQLERRM;
      l_error_code := SQLCODE;
      l_error_time := SYSDATE;
      INSERT INTO oracle_error  ( process_name
                                 ,errror_msg
                                 ,error_code
                                 ,error_time)
                          VALUES( l_process_name
                                 ,l_error_msg
                                 ,l_error_code
                                 ,l_error_time);
      IF ( DEBUG = VALID ) THEN
         dbms_output.put_line('------------------------------------');
         dbms_output.put_line('When others error: '||l_error_code||'-'||l_error_msg);
      END IF;

      -- General error
      l_result  := pkg_constants.ERROR_GENERAL_EXCEPTION;
      p_result  := l_result||','||l_scanner_locking_disabled;

end TSS_LOAD_BAG;
/

