/*****************************************************************************
*
* Filename         - $Workfile:   TSS_OFFLOAD_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_OFFLOAD_BAG 

CREATE OR REPLACE PROCEDURE TSS_OFFLOAD_BAG ( p_emp_num       IN  tagtrak_users.loginname%TYPE
                           ,p_scanner_id    IN  VARCHAR2
                           ,p_location_code IN  location.location_code%TYPE
                           ,p_bag_tag       IN  bag.bag_tag%TYPE
                           ,p_result        OUT VARCHAR2)
AS
/*******************************************************************************
 *
 * Procedure            - tss_offload_bag
 *
 * Description          - This procedure records whether the bag has been
 *                        offloaded or not.
 * CA History:
 * Date         Name        CA Number           CA Description
 ******************************************************************************/

   -- Decalrations
   l_process_name   oracle_error.process_name%TYPE := 'TSS_OFFLOAD_BAG';
   l_error_msg      oracle_error.errror_msg%TYPE   := NULL;
   l_error_code     oracle_error.error_code%TYPE   := NULL;
   l_error_time     oracle_error.error_time%TYPE   := NULL;

   -- Local constants
   YES                  CONSTANT NUMBER(1)   DEFAULT 1;
   DEBUG                CONSTANT VARCHAR2(1) DEFAULT 'Y';
   NO                   CONSTANT NUMBER(1)   DEFAULT 0;
   VALID                CONSTANT VARCHAR2(1) DEFAULT 'Y';
   DENIED               CONSTANT VARCHAR2(1) DEFAULT 'N';
   TRACE_ACTION         CONSTANT bag_trace.trace_action%TYPE := 'OFFLOAD';

   -- General declarations
   l_location_code      location.location_code%TYPE  := p_location_code;
   l_emp_num            tagtrak_users.loginname%TYPE := p_emp_num;
   l_scanner_id         kit.scanner_id%TYPE          := NULL;
   l_result             VARCHAR2(10)                 := pkg_constants.OFFLOAD_SUCCESS;
   l_error_message      VARCHAR2(512)                := NULL;
   l_error_number       NUMBER                       := NULL;

   -- Bag varibales
   l_bag_bag_tag        bag.bag_tag%TYPE             := p_bag_tag;
   l_bag_bag_tag1       bag.bag_tag1%TYPE            := 0;
   l_bag_bag_tag2a      bag.bag_tag2a%TYPE           := 0;
   l_bag_bag_tag2b      bag.bag_tag2b%TYPE           := 0;
   l_bag_bag_tag3       bag.bag_tag3%TYPE            := 0;
   l_bag_pax_id         bag.pax_id%TYPE              := NULL;
   l_bag_bag_exception  bag.bag_exception%TYPE       := NULL;
   l_bag_loaded_status  bag.loaded_status%TYPE       := NULL;
   l_bag_location_code  bag.location_code%TYPE       := NULL;
   l_bag_scanner_id     bag.scanner_id%TYPE          := NULL;
   l_bag_dcs_bag_tag    bag.dcs_bag_tag%TYPE         := NULL;
   l_bag_uld_num        bag.uld_no%TYPE              := NULL;
   l_bag_flight_id      pax.flight_id%TYPE           := 0;
   l_bag_uld_flight_id  bag.uld_flight_id%TYPE       := 0;

   -- Output variables for check_bag_for_offload
   l_tbag_uld_num       bag.uld_no%TYPE              := NULL;
   l_tbag_pax_id        bag.pax_id%TYPE              := NULL;
   l_tflight_id         pax.flight_id%TYPE           := NULL;

   -- Pax variables
   l_pax_sno            pax.pax_sequence_no%TYPE     := 0;

   -- Tag variables
   l_tag1               CHAR(1)                      := NULL;
   l_tag2a              CHAR(3)                      := NULL;
   l_tag2b              CHAR(3)                      := NULL;
   l_tag3               CHAR(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;
   l_ret		            NUMBER                       := 0;

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

   -- Flight variables
   l_flight_num         flight.flight_no%TYPE        := NULL;
   l_flight_id          flight.flight_id%TYPE        := 0;
   l_flight_date        flight.flight_date%TYPE      := NULL;
   l_flight_status      flight.flight_status%TYPE    := NULL;
   l_site_id            flight.site_id%TYPE          := NULL;

   -- app_priv variables
   l_offload_bag_priv  VARCHAR2(10)                 := NULL;
   l_app_priv_name_offload_bag     app_priv.app_priv_name%TYPE := 'TETHER_Offload_bag';

   -- Type declarations
   TYPE bag_tag_tt       IS TABLE OF bag.bag_tag%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_tag1_tt      IS TABLE OF bag.bag_tag1%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_tag2a_tt     IS TABLE OF bag.bag_tag2a%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_tag2b_tt     IS TABLE OF bag.bag_tag2b%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_tag3_tt      IS TABLE OF bag.bag_tag3%TYPE INDEX BY BINARY_INTEGER;
   TYPE pax_id_tt        IS TABLE OF bag.pax_id%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_exception_tt IS TABLE OF bag.bag_exception%TYPE INDEX BY BINARY_INTEGER;
   TYPE loaded_status_tt IS TABLE OF bag.loaded_status%TYPE INDEX BY BINARY_INTEGER;
   TYPE location_code_tt IS TABLE OF bag.location_code%TYPE INDEX BY BINARY_INTEGER;
   TYPE scanner_id_tt    IS TABLE OF bag.scanner_id%TYPE INDEX BY BINARY_INTEGER;
   TYPE dcs_bag_tag_tt   IS TABLE OF bag.dcs_bag_tag%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_exception_t  IS TABLE OF bag.bag_tag%TYPE INDEX BY BINARY_INTEGER;
   TYPE pax_flight_id_tt IS TABLE OF pax.flight_id%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_atl_brs_tt   IS TABLE OF bag.authority_to_load_brs%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_atl_dcs_tt   IS TABLE OF bag.authority_to_load_dcs%TYPE INDEX BY BINARY_INTEGER;
   TYPE number_tt        IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

   -- Index counters
   indx                  BINARY_INTEGER              := 0;
   dup_indx              BINARY_INTEGER              := 0;

   -- PL/SQL table declarations
   l_dup_bag_tag_tab            bag_tag_tt;
   l_dup_bag_tag1_tab           bag_tag1_tt;
   l_dup_bag_tag2a_tab          bag_tag2a_tt;
   l_dup_bag_tag2b_tab          bag_tag2b_tt;
   l_dup_bag_tag3_tab           bag_tag3_tt;
   l_dup_bag_pax_id_tab         pax_id_tt;
   l_dup_bag_exception_tab      bag_exception_tt;
   l_dup_bag_loaded_status_tab  loaded_status_tt;
   l_dup_bag_location_code_tab  location_code_tt;
   l_dup_bag_scanner_id_tab     scanner_id_tt;
   l_dup_bag_dcs_bag_tag_tab    dcs_bag_tag_tt;
   l_dup_bag_exception_log_tab  bag_exception_tt;
   l_dup_pax_flight_id_tab      pax_flight_id_tt;

   l_duplicate_bag_tab number_tt;

   -- duplicate if set to 1 otherwise unresolveable dup (0)
   l_duplicate_bag      NUMBER                       := 0;


   -- Exceptions
   error_not_authorised       EXCEPTION;
   error_unknown_user         EXCEPTION;
   error_duplicate_bag        EXCEPTION;
   error_unres_dup_bag        EXCEPTION;
   error_general_exception    EXCEPTION;
   error_bag_not_loaded       EXCEPTION;
   error_unknown_bag          EXCEPTION;
   error_flight_access        EXCEPTION;

   -- Cursor declarations ( ALL not offloaded )
   CURSOR c_get_bag_details (  p_bag_tag2a IN bag.bag_tag2a%TYPE
                              ,p_bag_tag2b IN bag.bag_tag2b%TYPE
                              ,p_bag_tag3  IN bag.bag_tag3%TYPE )
   IS     SELECT  bag.bag_tag
                 ,bag.pax_id
                 ,bag.bag_exception
                 ,bag.loaded_status
                 ,bag.bag_tag1
                 ,bag.bag_tag2a
                 ,nvl(bag.bag_tag2b,'-')
                 ,bag.bag_tag3
                 ,bag.location_code
                 ,bag.scanner_id
                 ,bag.uld_no
                 ,pax.flight_id
                 ,bag.uld_flight_id
                 ,f.flight_id
                 ,f.flight_no
                 ,f.flight_date
          FROM    bag, pax, flight_outbound_v1 f
          WHERE ( bag.bag_tag2a = p_bag_tag2a
               OR bag.bag_tag2b = p_bag_tag2b )
          AND    bag.bag_tag3  = p_bag_tag3
          AND    pax.pax_id    = bag.pax_id
          AND    f.flight_id = pax.flight_id;

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

   -- count the number of bags
   CURSOR c_count_bag_tags  (  p_bag_tag2a IN bag.bag_tag2a%TYPE
                              ,p_bag_tag2b IN bag.bag_tag2b%TYPE
                              ,p_bag_tag3  IN bag.bag_tag3%TYPE )
   IS     SELECT count(bag.bag_tag)
          FROM   bag
          WHERE ( bag.bag_tag2a = p_bag_tag2a
               OR bag.bag_tag2b = p_bag_tag2b )
          AND    bag.bag_tag3  = p_bag_tag3;

   -- Cursor to get duplicate data
   CURSOR c_check_duplicate_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  )
   IS     SELECT  bag.bag_tag
                 ,bag.pax_id
                 ,bag.bag_exception
                 ,bag.loaded_status
                 ,bag.bag_tag1
                 ,bag.bag_tag2a
                 ,bag.bag_tag2b
                 ,bag.bag_tag3
                 ,decode(bag.location_code,'-',p_location_code,p_location_code)
                 ,bag.scanner_id
                 ,pax.flight_id
          FROM    bag, pax
          WHERE ( bag.bag_tag2a = p_bag_tag2a
               OR bag.bag_tag2b = p_bag_tag2b )
          AND     bag.bag_tag3  = p_bag_tag3
          AND     pax.pax_id = bag.pax_id;

 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);
    ELSIF ( length(p_scanner_id ) > 8  OR length(p_scanner_id) < 8 )THEN
         RAISE error_general_exception;
    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 OR
         c_get_user_details%ROWCOUNT = 0 )THEN
       RAISE error_general_exception;
    END IF;
    CLOSE c_get_user_details;

   /* Check whether this user has the authority to
    * register ULDS ie. TETHER_PreClear_Bag.
    */

    tss_is_user_authorised ( p_emp_num        => l_emp_num
                            ,p_app_priv_name  => l_app_priv_name_offload_bag
                            ,p_result         => l_offload_bag_priv
                           );

    /* Some debug information */
    IF ( DEBUG = VALID ) THEN
       dbms_output.put_line('tss_is_user_authorised ');
       dbms_output.put_line('--------------------------');
       dbms_output.put_line('l_emp_num          :'||l_emp_num);
       dbms_output.put_line('l_app_priv_name_offload_bag :'||l_app_priv_name_offload_bag);
       dbms_output.put_line('l_valid_priv       :'||l_offload_bag_priv);
    END IF;

    /* If user doesn't have the permission to register_ulds them error */
    IF ( l_offload_bag_priv != pkg_constants.AUTHORISE_SUCCESS ) THEN
       RAISE error_not_authorised;
    END IF;


    /* Firstly split the bag_tag to use bits of it.
     */
    IF (p_bag_tag IS NOT NULL) then
        split_tags (  tagno     => p_bag_tag
                     ,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_bag_Tag);
       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;

    /* Need to trap duplicate tags also need to trap
     * whether this bag is in the bag table
     */
     OPEN c_count_bag_tags( l_tag2a
                           ,l_tag2b
                           ,l_tag3 );
     FETCH c_count_bag_tags
     INTO l_total_bags;

     IF ( c_count_bag_tags%NOTFOUND OR
          c_count_bag_tags%ROWCOUNT = 0 ) THEN
         NULL;
     END IF;

     CLOSE c_count_bag_tags;

     /* Error if there is a duplicate tag */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('l_total_bags :'||l_total_bags);
     END IF;

     IF ( l_total_bags > 1) THEN
        /* Check whether this bag is a resolvable one or
         * unresolvable duplicate
         */
        OPEN c_check_duplicate_type ( l_tag2a
                                     ,l_tag2b
                                     ,l_tag3 );

        LOOP
           FETCH c_check_duplicate_type
           INTO  l_dup_bag_tag_tab(indx)
                ,l_dup_bag_pax_id_tab(indx)
                ,l_dup_bag_exception_tab(indx)
                ,l_dup_bag_loaded_status_tab(indx)
                ,l_dup_bag_tag1_tab(indx)
                ,l_dup_bag_tag2a_tab(indx)
                ,l_dup_bag_tag2b_tab(indx)
                ,l_dup_bag_tag3_tab(indx)
                ,l_dup_bag_location_code_tab(indx)
                ,l_dup_bag_scanner_id_tab(indx)
                ,l_dup_pax_flight_id_tab(indx);
           EXIT WHEN c_check_duplicate_type%NOTFOUND OR
                     c_check_duplicate_type%ROWCOUNT = 0;
           IF (DEBUG = VALID ) THEN
              dbms_output.put_line('----------------------------------------');
              dbms_output.put_line('Checking for duplicates');
              dbms_output.put_line('----------------------------------------');
              dbms_output.put_line('l_dup_bag_tag       :'||l_dup_bag_tag_tab(indx));
              dbms_output.put_line('l_dup_bag_pax_id    :'||l_dup_bag_pax_id_tab(indx));
              dbms_output.put_line('l_dup_pax_flight_id :'||l_dup_pax_flight_id_tab(indx));
              dbms_output.put_line('----------------------------------------');
           END IF;

           IF l_dup_pax_flight_id_tab(0) != l_dup_pax_flight_id_tab(indx) THEN
              l_duplicate_bag_tab(indx) := 1; -- resolvable duplicate
           END IF;
           indx := indx + 1;
        END LOOP;
        CLOSE c_check_duplicate_type;

        IF l_dup_bag_pax_id_tab.COUNT > 0 THEN
           /* Need to set one value */
           IF l_duplicate_bag_tab.COUNT > 0 THEN
           FOR dup_indx IN l_duplicate_bag_tab.FIRST..l_duplicate_bag_tab.LAST LOOP
               IF l_duplicate_bag_tab(dup_indx) = 1 THEN
                  l_duplicate_bag := 1;
               END IF;

           END LOOP;
           END IF;



             /* Un-resolvable bags:l_res_unresolve = 0,  duplicate bag = 1 */
           IF (l_duplicate_bag = 1 ) THEN
               l_result := pkg_constants.ERROR_DUPLICATE_BAG;

           ELSE
               l_result := pkg_constants.ERROR_UNRESOLVABLE_DUP_TAG;
           END IF;

           /* Create the trace file */
            add_bag_trace_record( l_user_id
                                 ,l_location_code
                                 ,l_result -- error_code
                                 ,p_bag_tag
                                 ,l_tag1
                                 ,l_tag2a
                                 ,l_tag2b
                                 ,l_tag3
                                 ,TRACE_ACTION  -- trace_action
                                 ,l_scanner_id
                                 ,l_dup_pax_flight_id_tab(0) -- one of the flights
                                 ,l_dup_bag_pax_id_tab(0) -- one of the pax's
                                 );    -- don't pass ULD number here as we don't have it yet! (Nigel, 01/04/04, CA598)
            IF  ( l_result =  pkg_constants.ERROR_DUPLICATE_BAG ) THEN
               RAISE error_duplicate_bag;
            ELSIF ( l_result = pkg_constants.ERROR_UNRESOLVABLE_DUP_TAG ) THEN
               RAISE error_unres_dup_bag;
            END IF;

           /* Create a bag_trace file for the duplicate bag */

         END IF;


     END IF ;

    /* Check to see whether this bag exists in the system, If it
     * doesn't need to error this as pre_clear_not_authorised
     */
     OPEN c_get_bag_details ( l_tag2a
                             ,l_tag2b
                             ,l_tag3);
     FETCH c_get_bag_details
     INTO    l_bag_bag_tag
            ,l_bag_pax_id
            ,l_bag_bag_exception
            ,l_bag_loaded_status
            ,l_bag_bag_tag1
            ,l_bag_bag_tag2a
            ,l_bag_bag_tag2b
            ,l_bag_bag_tag3
            ,l_bag_location_code
            ,l_bag_scanner_id
            ,l_bag_uld_num
            ,l_bag_flight_id
            ,l_bag_uld_flight_id
            ,l_flight_id
            ,l_flight_num
            ,l_flight_date;
     IF ( c_get_bag_details%NOTFOUND OR
          c_get_bag_details%ROWCOUNT = 0 ) THEN
          l_result := pkg_constants.ERROR_UNKNOWN_BAG;
     END IF;

     CLOSE c_get_bag_details;

         /* Check to see whether this is an unknown bag */
         IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('l_result :'||l_result);
         END IF;

    /* Call check_bag_for_unload to trap erros like duplicates and
     * bag not loaded.
     */

     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('---------------------------------');
        dbms_output.put_line('Before check_bag_for_unload...');
        dbms_output.put_line('---------------------------------');
        dbms_output.put_line('l_bag_bag_tag       :'||l_bag_bag_tag);
        dbms_output.put_line('l_bag_pax_id        :'||l_bag_pax_id);
        dbms_output.put_line('l_bag_bag_exception :'||l_bag_bag_exception);
        dbms_output.put_line('l_bag_loaded_status :'||l_bag_loaded_status);
        dbms_output.put_line('l_bag_bag_tag2b     :'||l_bag_bag_tag2b);
        dbms_output.put_line('l_bag_bag_tag3      :'||l_bag_bag_tag3);
        dbms_output.put_line('l_bag_location_code :'||l_bag_location_code);
        dbms_output.put_line('l_bag_scanner_id    :'||l_bag_scanner_id);
        dbms_output.put_line('l_scanner_id        :'||l_scanner_id);
        dbms_output.put_line('l_flight_id         :'||l_flight_id);
        dbms_output.put_line('l_flight_num        :'||l_flight_num);
        dbms_output.put_line('l_flight_date       :'||l_flight_date);
        dbms_output.put_line('l_bag_flight_id     :'||l_bag_flight_id);
        dbms_output.put_line('l_bag_uld_flight_id :'||l_bag_uld_flight_id);
        dbms_output.put_line('l_pax_sno           :'||l_pax_sno);
        dbms_output.put_line('---------------------------------');
        dbms_output.put_line('l_tbag_uld_num      :'||l_tbag_uld_num);
        dbms_output.put_line('l_tbag_pax_id       :'||l_tbag_pax_id);
        dbms_output.put_line('l_tflight_id        :'||l_tflight_id);
        dbms_output.put_line('l_result            :'||l_result);
        dbms_output.put_line('---------------------------------');
     END IF;

     check_bag_for_unload (  tag1          => l_tag1
                            ,tag2a         => l_tag2a
                            ,tag2b         => l_tag2b
                            ,tag3          => l_tag3
                            ,sno           => l_pax_sno
                            ,fid           => l_flight_id
                            ,ufid          => l_bag_uld_flight_id
                            ,codeshare_fid => 0
                            ,retval        => l_result
                            ,tuld_no       => l_tbag_uld_num
                            ,tpax_id       => l_tbag_pax_id
                            ,tflight_id    => l_tflight_id );

     /* Some debug information for checking */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('---------------------------------');
        dbms_output.put_line('After check_bag_for_unload...');
        dbms_output.put_line('---------------------------------');
        dbms_output.put_line('l_bag_bag_tag       :'||l_bag_bag_tag);
        dbms_output.put_line('l_bag_pax_id        :'||l_bag_pax_id);
        dbms_output.put_line('l_bag_bag_exception :'||l_bag_bag_exception);
        dbms_output.put_line('l_bag_loaded_status :'||l_bag_loaded_status);
        dbms_output.put_line('l_bag_bag_tag2b     :'||l_bag_bag_tag2b);
        dbms_output.put_line('l_bag_bag_tag3      :'||l_bag_bag_tag3);
        dbms_output.put_line('l_bag_location_code :'||l_bag_location_code);
        dbms_output.put_line('l_bag_scanner_id    :'||l_bag_scanner_id);
        dbms_output.put_line('l_scanner_id        :'||l_scanner_id);
        dbms_output.put_line('l_flight_id         :'||l_flight_id);
        dbms_output.put_line('l_flight_num        :'||l_flight_num);
        dbms_output.put_line('l_flight_date       :'||l_flight_date);
        dbms_output.put_line('l_bag_flight_id     :'||l_bag_flight_id);
        dbms_output.put_line('l_bag_uld_flight_id :'||l_bag_uld_flight_id);
        dbms_output.put_line('l_pax_sno           :'||l_pax_sno);
        dbms_output.put_line('----------------------------------');
        dbms_output.put_line('l_tbag_uld_num      :'||l_tbag_uld_num);
        dbms_output.put_line('l_tbag_pax_id       :'||l_tbag_pax_id);
        dbms_output.put_line('l_tflight_id        :'||l_tflight_id);
        dbms_output.put_line('l_result            :'||l_result);
        dbms_output.put_line('---------------------------------');
     END IF;

     /* Logg the error */
     IF ( l_result != pkg_constants.OFFLOAD_SUCCESS ) THEN
        /* Create a trace record if eror occurred during offloaded */
        add_bag_trace_record( l_user_id
                             ,l_location_code
                             ,l_result -- error_code
                             ,p_bag_tag
                             ,l_tag1
                             ,l_tag2a
                             ,l_tag2b
                             ,l_tag3
                             ,TRACE_ACTION  -- trace_action
                             ,l_scanner_id
                             ,l_flight_id
                             ,l_tbag_pax_id
                             ,l_bag_uld_num    -- pass ULD number (Nigel, 01/04/04, CA598)
                             );
     END IF;

     /* Trap the following errors */
     IF ( l_result = pkg_constants.ERROR_UNKNOWN_BAG ) THEN
        RAISE error_unknown_bag;
     ELSIF ( l_result = pkg_constants.ERROR_DUPLICATE_BAG ) THEN
        RAISE error_duplicate_bag;
     ELSIF ( l_result = pkg_constants.ERROR_UNRESOLVABLE_DUP_TAG ) THEN
        RAISE error_unres_dup_bag;
     ELSIF ( l_result = pkg_constants.ERROR_BAG_NOT_LOADED ) THEN
        RAISE error_bag_not_loaded;
         ELSIF ( l_result != pkg_constants.OFFLOAD_SUCCESS) OR
               ( l_result > pkg_constants.OFFLOAD_SUCCESS ) THEN
            RAISE error_general_exception;
     END IF;

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

     user_authentication.validate_access
       (l_user_id, l_flight_id, NULL, NULL, l_ret);
     IF( l_ret < 0 ) THEN
         l_result:= pkg_constants.ERROR_FLIGHT_ACCESS_DENIED; --(-1031);
         RAISE error_flight_access;
     END IF;

     /* Now try to offload the bag and get it recorded as OFFLOAD */
     offload_bag( tag2a            => l_tag2a
                   ,tag2b            => l_tag2b
                       ,tag3             => l_tag3
                       ,pid              => l_tbag_pax_id
                 ,uldno            => l_bag_uld_num
                       ,p_location_code  => l_location_code
                       ,p_scanner_id     => l_scanner_id
                       ,fltid            => l_flight_id
                 ,retval           => l_retcode );

     /* Some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('l_retcode ....'||l_retcode);
     END IF;

     /* Error if unsuccessful, i.e. -1013 error */
     IF ( l_retcode !=  pkg_constants.OFFLOAD_SUCCESS )  THEN
         l_result := pkg_constants.ERROR_GENERAL_EXCEPTION;
     END IF;

     /* Create a trace record if successfully offloaded */
     add_bag_trace_record( l_user_id
                          ,l_location_code
                          ,pkg_constants.OFFLOAD_SUCCESS -- error_code
                          ,p_bag_tag
                          ,l_tag1
                          ,l_tag2a
                          ,l_tag2b
                          ,l_tag3
                          ,TRACE_ACTION  -- trace_action
                          ,l_scanner_id
                          ,l_flight_id
                          ,l_tbag_pax_id
                          ,l_bag_uld_num    -- pass ULD number (Nigel, 01/04/04, CA598)
                          );


     /* Generate BPM for the Offloaded Bag */
     IF mws_web.store_bpm1(l_location_code
                          ,l_user_id
                          ,l_flight_id
                          ,l_tbag_pax_id
                          ,get_numeric_bag_tag(p_bag_tag)
                          ,mws_web.f_pax_sequence_no(l_tbag_pax_id, l_flight_id)
                          ,TRACE_ACTION) THEN -- OFFLOAD
     	NULL;
     END IF;

     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('---------------------------------');
        dbms_output.put_line('After offload_bag...');
        dbms_output.put_line('---------------------------------');
        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_bag_pax_id        :'||l_bag_pax_id);
        dbms_output.put_line('l_tbag_pax_id       :'||l_tbag_pax_id);
        dbms_output.put_line('l_uld_num           :'||l_bag_uld_num);
        dbms_output.put_line('l_location_code     :'||l_location_code);
        dbms_output.put_line('l_scanner_id        :'||l_scanner_id);
        dbms_output.put_line('l_flight_id         :'||l_flight_id);
        dbms_output.put_line('l_flight_num        :'||l_flight_num);
        dbms_output.put_line('l_flight_date       :'||l_flight_date);
        dbms_output.put_line('l_retcode           :'||l_retcode);
        dbms_output.put_line('---------------------------------');
     END IF;

    /* Return result */
    p_result := l_result;

 EXCEPTION
    WHEN error_unknown_bag THEN
     /* Bag details */
     IF ( c_get_bag_details%ISOPEN ) THEN
        CLOSE c_get_bag_details;
     END IF;
      /* Some debug information */
     IF ( DEBUG = VALID ) THEN
          dbms_output.put_line('Error unknown bag');
     END IF;

     l_result  := pkg_constants.ERROR_UNKNOWN_BAG;
     p_result  := l_result;

    WHEN error_general_exception THEN
     /* Close all cursors if they are opened */
     /* User details */
     IF ( c_get_user_details%ISOPEN ) THEN
         CLOSE c_get_user_details;
     END IF;

     /* Some debug info */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('Error General Exception error');
     END IF;

     l_result := pkg_constants.ERROR_GENERAL_EXCEPTION;
     p_result := l_result;

    WHEN error_unres_dup_bag THEN
     l_result  := pkg_constants.ERROR_UNRESOLVABLE_DUP_TAG;
     p_result  := l_result;

     /* Do some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('--------------------------------------');
        dbms_output.put_line('Error Unresolvable Duplicate Tag');
     END IF;

    WHEN error_duplicate_bag THEN
     l_result  := pkg_constants.ERROR_DUPLICATE_BAG;
     p_result  := l_result;

     /* Do some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('--------------------------------------');
        dbms_output.put_line('Error Duplicate Tag');
     END IF;

    WHEN error_unknown_user THEN
     /* Close cursor if opened */
     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, need to error this ');
     END IF;

     l_result  := pkg_constants.ERROR_USER_UNKNOWN;
     p_result  := l_result;

   WHEN error_bag_not_loaded THEN
     /* Create a trace record for information purposes */
     l_result  := pkg_constants.ERROR_BAG_NOT_LOADED;
     p_result  := l_result;

     /* Some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('------------------------------------------');
        dbms_output.put_line('Error bag not loaded');
     END IF;

   WHEN error_not_authorised THEN
     /* Some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('------------------------------------------');
        dbms_output.put_line('Error user not authorised to offload bags');
     END IF;
     l_result  := pkg_constants.ERROR_USER_NOT_AUTHORISED;
     p_result  := l_result;

   WHEN error_flight_access THEN
     l_result  := pkg_constants.ERROR_FLIGHT_ACCESS_DENIED;
     p_result  := l_result;

     /* Some debug information */
     IF ( DEBUG = VALID ) THEN
        dbms_output.put_line('------------------------------------------');
        dbms_output.put_line('User Does not have access to Flight');
     END IF;

   WHEN OTHERS THEN
      /* Assign general error */
      l_result := pkg_constants.ERROR_GENERAL_EXCEPTION;
      p_result := l_result;

      /* 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;

 END; -- tss_offload_bag
/
