PROMPT CREATE OR REPLACE PROCEDURE TSS_MERGE_ULDS

CREATE OR REPLACE PROCEDURE TSS_MERGE_ULDS ( p_emp_num          IN  tagtrak_users.loginname%TYPE,
                           p_scanner_id       IN  VARCHAR2,
                           p_location_code    IN  location.location_code%type,
                           p_uld_id_code_src  IN  uld_recorded_id_codes.uld_id_code%TYPE,
                           p_uld_id_code_dest IN  uld_recorded_id_codes.uld_id_code%TYPE,
                           p_result           OUT VARCHAR2)
AS
/*******************************************************************************
 *
 * Procedure            - tss_merge_ulds
 *
 * Description          - This procedure allows emptying the contents of one
 *                        ULD ( Source ) into another ULD ( Destination ). Both
 *                        the source and destination ULDS should already have
 *                        container sheets associated with them.
 * CA History:
 * Date         Name        CA Number           CA Description
 ******************************************************************************/

   -- Declarations
   l_process_name   oracle_error.process_name%TYPE  := 'TSS_MERGE_ULDS';
   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;
   l_retcode        NUMBER                          := 0;

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

   -- 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          := 0;
   l_result             VARCHAR2(10)                 := pkg_constants.MERGE_ULDS_SUCCESS;
   l_error_message      VARCHAR2(512)                := NULL;
   l_error_number       NUMBER                       := NULL;
   l_calling_location   VARCHAR2(512)                := NULL;

   -- Pax variables
   l_pax_id             pax.pax_id%TYPE              := NULL;
   l_sno                pax.pax_sequence_no%TYPE     := NULL;
   l_pax_seq_no         pax.pax_sequence_no%TYPE     := NULL;
   l_retval             NUMBER                       := 0;
   l_tpax_id            pax.pax_id%TYPE              := NULL;
   l_t2flight_id        pax.flight_id%TYPE           := NULL;
   l_paxseqno           pax.pax_sequence_no%TYPE     := NULL;

   -- Source ULD declarations
   l_uld_num_src              ulds.uld_no%TYPE                := NULL;
   l_uld_flight_id_src        ulds.flight_id%TYPE             := NULL;
   l_uld_no_of_bags_src       ulds.uld_no_of_bags%TYPE        := 0;
   l_uld_loaded_status_src    ulds.uld_loaded_status%TYPE     := NULL;
   l_uld_connection_type_src  ulds.connection_type%TYPE       := NULL;

   -- Destination ULD declarations
   l_uld_num_dest             ulds.uld_no%TYPE                := NULL;
   l_uld_flight_id_dest       ulds.flight_id%TYPE             := NULL;
   l_uld_no_of_bags_dest      ulds.uld_no_of_bags%TYPE        := 0;
   l_uld_loaded_status_dest   ulds.uld_loaded_status%TYPE     := NULL;
   l_uld_connection_type_dest ulds.connection_type%TYPE       := NULL;

   -- Card details
   l_uld_id_code_src   uld_recorded_id_codes.uld_id_code%TYPE := NULL;
   l_flight_num_src    uld_recorded_id_codes.flight_no%TYPE   := NULL;
   l_flight_date_src   uld_recorded_id_codes.flight_date%TYPE := NULL;
   l_site_id_src       uld_recorded_id_codes.site_id%TYPE     := NULL;
   l_uld_category_src  uld_recorded_id_codes.uld_category%TYPE:= NULL;
   l_uld_type_src      uld_recorded_id_codes.uld_type%TYPE    := NULL;
   l_uld_dest1_src     uld_recorded_id_codes.dest1%TYPE       := NULL;
   l_uld_class1_src    uld_recorded_id_codes.class1%TYPE      := NULL;
   l_err_uld_id_code   uld_recorded_id_codes.uld_id_code%TYPE := NULL;
   l_valid_card            uld_recorded_id_codes.uld_id_code%TYPE := NULL;

   -- Source Card details
   l_uld_id_source      uld_recorded_id_codes.uld_id_code%TYPE := NULL;

   -- Destination Card details
   l_uld_id_dest        uld_recorded_id_codes.uld_id_code%TYPE := NULL;
   l_flight_num_dest    uld_recorded_id_codes.flight_no%TYPE   := NULL;
   l_flight_date_dest   uld_recorded_id_codes.flight_date%TYPE := NULL;
   l_site_id_dest       uld_recorded_id_codes.site_id%TYPE     := NULL;
   l_uld_category_dest  uld_recorded_id_codes.uld_category%TYPE:= NULL;
   l_uld_type_dest      uld_recorded_id_codes.uld_type%TYPE    := NULL;
   l_uld_dest1_dest     uld_recorded_id_codes.dest1%TYPE       := NULL;
   l_uld_class1_dest    uld_recorded_id_codes.class1%TYPE      := NULL;

   -- Bag varibales
   l_bag_tag            bag.bag_tag%TYPE             := NULL;
   l_bag_tag1           bag.bag_tag1%TYPE            := NULL;
   l_bag_tag2a          bag.bag_tag2a%TYPE           := NULL;
   l_bag_tag2b          bag.bag_tag2b%TYPE           := NULL;
   l_bag_tag3           bag.bag_tag3%TYPE            := NULL;
   l_bag_pax_id         bag.pax_id%TYPE              := NULL;
   l_bag_dcs_bag_tag    bag.dcs_bag_tag%TYPE         := NULL;

   -- 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_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_register_uld_priv  VARCHAR2(10)                 := NULL;
   l_bpm_status         system_cfg.bpm_status%TYPE   := NULL;
   l_bpm_id             NUMBER                       := NULL;
   l_bpm_type           VARCHAR2(1)                  := NULL;

   -- holds variables
   l_hold_src           NUMBER                       := 0;
   l_hold_dest          NUMBER                       := 0;

   -- Various counts
   l_registered                 NUMBER                                           := 0;

   -- Exceptions
   error_unknown_uld        EXCEPTION;
   error_unknown_user       EXCEPTION;
   error_unknown_uld_dest   EXCEPTION;
   error_no_bags            EXCEPTION;
   error_uld_wrong_flight   EXCEPTION;
   error_no_bpm_status      EXCEPTION;
   error_general_exception  EXCEPTION;
   error_flight_status_pd   EXCEPTION;
   error_bag_already_loaded EXCEPTION;
   error_src_uld_not_reg    EXCEPTION;
   error_dest_uld_not_reg   EXCEPTION;
   error_flight_access      EXCEPTION;


   -- Cursor declarations

   -- Gets user details
   CURSOR c_get_user_id ( p_emp_num IN tagtrak_users.loginname%TYPE )
    IS    SELECT nvl ( user_id,0 )
              FROM   tagtrak_users
              WHERE  trim(loginname) = trim(p_emp_num);

   -- Gets the uld_no for the container to do transfers.
   CURSOR c_get_uld_num_details ( p_uld_id_code IN uld_recorded_id_codes.uld_id_code%TYPE )
    IS    SELECT urc.uld_no
                ,urc.flight_no
                ,urc.flight_date
                ,urc.site_id
                ,urc.uld_category
                ,urc.uld_type
                ,urc.dest1
                ,urc.class1
                ,ulds.flight_id
                ,nvl(ulds.uld_no_of_bags,0)
                ,ulds.uld_loaded_status
                ,ulds.connection_type
          FROM   uld_recorded_id_codes urc, ulds
          WHERE  urc.uld_id_code = p_uld_id_code
          AND    ulds.uld_no     = urc.uld_no
          AND    ulds.uld_loaded_status = 'N';

   CURSOR c_confirm_valid_card ( p_uld_id_code IN uld_recorded_id_codes.uld_id_code%TYPE )
    IS    SELECT urc.uld_id_code
          FROM   uld_recorded_id_codes urc
          WHERE  urc.uld_id_code = p_uld_id_code;

    -- Bag Cursor, also need to lock this for updating later.
    CURSOR c_get_bag_details ( p_uld_num       IN ulds.uld_no%TYPE
                              ,p_uld_flight_id IN ulds.flight_id%TYPE)
     IS   SELECT bag.bag_tag
                ,bag.bag_tag1
                ,bag.bag_tag2a
                ,bag.bag_tag2b
                ,bag.bag_tag3
                ,bag.pax_id
                ,bag.dcs_bag_tag
          FROM   bag
          WHERE  bag.delete_marker = 0
          AND    bag.loaded_status = 'Y'
          AND    bag.uld_no        = p_uld_num
          AND    bag.uld_flight_id = p_uld_flight_id
          FOR UPDATE;

   -- BPM Cursor
   CURSOR c_get_bpm_status ( p_flight_id_dest IN ulds.flight_id%TYPE )
    IS    SELECT  sf.bpm_status
          FROM    system_cfg sf
                 ,flight f
          WHERE   sf.process_name = f.process_name
          AND     sf.unit_name    = f.unit_name
          AND     sf.dcsid        = f.dcsid
          AND     sf.subunit_name = f.subunit_name
          AND     f.flight_id     = p_flight_id_dest;

   -- Hold Cursor
   CURSOR c_check_uld_is_hold ( p_uld_num IN ulds.uld_no%TYPE )
    IS    SELECT count(hold_no)
          FROM   holds
          WHERE  hold_no = p_uld_num;

   -- ULD Registered Cursor
   CURSOR c_check_registered ( p_uld_id_code IN uld_recorded_id_codes.uld_id_code%TYPE )
    IS    SELECT count(*)
          FROM   uld_recorded_id_codes
          WHERE  uld_id_code = p_uld_id_code
                  AND    uld_no IS NOT NULL;


   -- Record structure declarations
   r_bag_details      c_get_bag_details%ROWTYPE;


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;

    /* Get the user_id */
    OPEN c_get_user_id (p_emp_num);
    FETCH c_get_user_id
    INTO  l_user_id;
    IF ( c_get_user_id%NOTFOUND OR
         c_get_user_id%ROWCOUNT = 0 ) THEN
         RAISE error_general_exception;
    END IF;
    CLOSE c_get_user_id;

   /* Check to ensure that the Card scanned in is a card and not a container */
   /* Check Whether the ULD Card is a valid card i.e. that it exists */
    IF ( p_uld_id_code_src  IS NOT NULL ) AND
           ( p_uld_id_code_dest IS NOT NULL ) THEN

           /* Check the Source Card */
           OPEN c_confirm_valid_card ( p_uld_id_code_src );
           FETCH c_confirm_valid_card
           INTO  l_valid_card;

           IF ( c_confirm_valid_card%NOTFOUND OR
                c_confirm_valid_card%ROWCOUNT = 0 ) THEN
                        /* Error this out */
                        IF ( DEBUG = VALID ) THEN
                l_err_uld_id_code := p_uld_id_code_src;
                    END IF;
                        RAISE error_unknown_uld;

           END IF;
           CLOSE c_confirm_valid_card;

           /* Now check the Destination Card */
           OPEN c_confirm_valid_card ( p_uld_id_code_dest );
           FETCH c_confirm_valid_card
           INTO  l_valid_card;

           IF ( c_confirm_valid_card%NOTFOUND OR
                c_confirm_valid_card%ROWCOUNT = 0 ) THEN
                        RAISE error_unknown_uld_dest;

           END IF;
           CLOSE c_confirm_valid_card;

          /* Now need to check to see whether these cards are registered */
      /* Check Source ULD Card is registered */
           OPEN c_check_registered ( p_uld_id_code_src );
           FETCH c_check_registered
           INTO  l_registered;
           /* Error if not registered */
           IF ( c_check_registered%NOTFOUND OR
                c_check_registered%ROWCOUNT = 0) THEN
                        /* error this out */
                        IF ( DEBUG = VALID ) THEN
               l_err_uld_id_code := p_uld_id_code_src;
                        END IF;
                        RAISE error_src_uld_not_reg;
           END IF;
           /* Close cursor */
           CLOSE c_check_registered;

       /* Check Destination ULD Card is registered */
           OPEN c_check_registered ( p_uld_id_code_dest ); -- could be redundant
           FETCH c_check_registered
           INTO  l_registered;
           /* Error if not registered */
           IF ( c_check_registered%NOTFOUND OR
                c_check_registered%ROWCOUNT = 0) THEN
                        /* error this out */
                        IF ( DEBUG = VALID ) THEN
               l_err_uld_id_code := p_uld_id_code_dest;
                        END IF;
                        RAISE error_dest_uld_not_reg;
           END IF;
           /* Close Cursor */
           CLOSE c_check_registered;

     /* Get the Container numbers i.e. ULD_NOs of the source and the
      * destination ULD containers.
      */
     /* Get source container ( ULD Number ) */
       OPEN c_get_uld_num_details ( p_uld_id_code_src );
       FETCH c_get_uld_num_details
       INTO  l_uld_num_src
            ,l_flight_num_src
            ,l_flight_date_src
            ,l_site_id_src
            ,l_uld_category_src
            ,l_uld_type_src
            ,l_uld_dest1_src
            ,l_uld_class1_src
            ,l_uld_flight_id_src
            ,l_uld_no_of_bags_src
            ,l_uld_loaded_status_src
            ,l_uld_connection_type_src;

       IF ( c_get_uld_num_details%NOTFOUND OR
            c_get_uld_num_details%ROWCOUNT = 0 ) THEN
            /* error this out */
                        IF ( DEBUG = VALID ) THEN
                l_err_uld_id_code := p_uld_id_code_src;
                    END IF;
            RAISE error_src_uld_not_reg;  -- This should really never occur

       END IF;
       CLOSE c_get_uld_num_details;

       -- CA709 Flight Access Changes. RA
       IF ( DEBUG = VALID ) THEN
          dbms_output.put_line('----------------------------------');
          dbms_output.put_line('Validating user access to flight..');
          dbms_output.put_line('----------------------------------');
          dbms_output.put_line('User ID = '||l_user_id);
          dbms_output.put_line('Flight Number = '||l_flight_num_src);
          dbms_output.put_line('Flight Date = '||l_flight_date_src);
          
       END IF;

       user_authentication.validate_access
         (l_user_id, 0, l_flight_num_src, l_flight_date_src, l_retcode);
       
       IF( l_retcode < 0 ) THEN
         IF ( DEBUG = VALID ) THEN
           dbms_output.put_line('User not authorised to access flight '||l_flight_num_src);
         END IF;

         l_result := pkg_constants.ERROR_FLIGHT_ACCESS_DENIED; --(-1031);
         RAISE error_flight_access;
       END IF;
       -- End of Flight Access Changes

       /* Get destination container ( ULD Number ) */
       OPEN c_get_uld_num_details ( p_uld_id_code_dest );
       FETCH c_get_uld_num_details
       INTO  l_uld_num_dest
            ,l_flight_num_dest
            ,l_flight_date_dest
            ,l_site_id_dest
            ,l_uld_category_dest
            ,l_uld_type_dest
            ,l_uld_dest1_dest
            ,l_uld_class1_dest
            ,l_uld_flight_id_dest
            ,l_uld_no_of_bags_dest
            ,l_uld_loaded_status_dest
            ,l_uld_connection_type_dest;

       IF ( c_get_uld_num_details%NOTFOUND OR
            c_get_uld_num_details%ROWCOUNT = 0 ) THEN
             /* Error this out for debug purposes only */
            IF ( DEBUG = VALID ) THEN
               l_err_uld_id_code := p_uld_id_code_dest;
            END IF;
            RAISE error_dest_uld_not_reg;  -- This should really never occur

       END IF;
       CLOSE c_get_uld_num_details;
    ELSE
       /* Error this if either of the uld_id_codes is null */
       IF ( DEBUG = VALID ) THEN
           l_err_uld_id_code := p_uld_id_code_src;
       END IF;
       RAISE error_unknown_uld;
    END IF;

    /* Some debug information */
    IF ( DEBUG = VALID ) THEN
       dbms_output.put_line('Source ULD Container :'||l_uld_num_src);
       dbms_output.put_line('Dest   ULD Container :'||l_uld_num_dest);
    END IF;

    /* Confirm that the destination ULD is a valid ULD containter
     * this is assumed if found in the uld_recorded_id_codes table
     * then this is a valid destination uld card.
     */
    IF ( l_uld_flight_id_dest != l_uld_flight_id_src ) THEN
       RAISE error_uld_wrong_flight;
    END IF;

    /* Ensure that there are bags to do a transfer.
     * otherwise need to error if the container
     * is empty.
     */
    IF  ( l_uld_no_of_bags_src = 0 ) THEN
          RAISE error_no_bags;
    END IF;


    /* Check to see whether this is a hold uld.
     * This would be needed to decide what to send mail_bpm2 as the
     * type.
     */

    OPEN  c_check_uld_is_hold ( l_uld_num_dest );
    FETCH c_check_uld_is_hold
    INTO  l_hold_dest;        -- 0, 1 or > 1
    IF ( c_check_uld_is_hold%NOTFOUND OR
         c_check_uld_is_hold%ROWCOUNT = 0 ) THEN
        NULL; --ignore this and carry on. l_hold_dest = 0
    END IF;
    CLOSE c_check_uld_is_hold;

    IF ( DEBUG = VALID ) THEN
       dbms_output.put_line('Checking whether this is a hold..');
       dbms_output.put_line('l_hold_dest :'||l_hold_dest);
    END IF;


    /* Now transfer all bags from the Source Container (ULD) to the
     * Destination Container (ULD), but get the bag details first.
     */
    OPEN c_get_bag_details ( l_uld_num_src
                            ,l_uld_flight_id_src );

    /* Process each bag */
    LOOP
       FETCH c_get_bag_details
       INTO  r_bag_details;

       IF ( DEBUG = VALID  ) THEN
         dbms_output.put_line('------------------------------------------');
         dbms_output.put_line('Bag details.....'||r_bag_details.bag_tag);
         dbms_output.put_line('------------------------------------------');
         dbms_output.put_line('dcs_bg_tag       :'||r_bag_details.dcs_bag_tag);
         dbms_output.put_line('bag_tag2a        :'||r_bag_details.bag_tag2a);
         dbms_output.put_line('bag_tag2b        :'||r_bag_details.bag_tag2b);
         dbms_output.put_line('bag_tag3         :'||r_bag_details.bag_tag3);
         dbms_output.put_line('pax_id           :'||r_bag_details.pax_id);
         dbms_output.put_line('l_uld_num_src    :'||l_uld_num_src);
         dbms_output.put_line('l_uld_num_dest   :'||l_uld_num_dest);
         dbms_output.put_line('l_location_code  :'||l_location_code);
         dbms_output.put_line('l_scanner_id     :'||l_scanner_id);
         dbms_output.put_line('bpmid            :'||0);
         dbms_output.put_line('fltid            :'||l_uld_flight_id_dest);
         dbms_output.put_line('l_retcode        :'||l_retcode );
         dbms_output.put_line('l_user_id        :'||l_user_id);

       END IF;
       /* Get out of loop if no records found */
       EXIT WHEN ( c_get_bag_details%NOTFOUND OR
                   c_get_bag_details%ROWCOUNT = 0);

       /* Process and transfer these bags i.e. update ulds */
       xfer_bag (  tag             => r_bag_details.dcs_bag_tag
                  ,tag2a           => r_bag_details.bag_tag2a
                  ,tag2b           => r_bag_details.bag_tag2b
                  ,tag3            => r_bag_details.bag_tag3
                  ,pid             => r_bag_details.pax_id
                  ,from_uld_no     => l_uld_num_src
                  ,to_uld_no       => l_uld_num_dest
                  ,p_location_code => l_location_code
                  ,scanner_id      => l_scanner_id
                  ,bpmid           => 0
                  ,fltid           => l_uld_flight_id_dest
                  ,retval          => l_retcode );

      /* Need to trap this error to trap any errors
       * that we are interested for merging.
       *
       * i.e : Flight Status set to PD
       *     : Wrong Flight
       *     : Destination ULD Not Found
       *     : Tag/Bag Already Loaded
       *     : General ULD ERROR
       *     : Unknown ULD
       */

       /* Create another bag_trace record for checking errors */
       add_bag_trace_record (p_user_id        => l_user_id
                            ,p_location_code  => l_location_code
                            ,p_error_id       => l_retcode
                            ,p_bagtag         => r_bag_details.bag_tag
                            ,p_tag1           => r_bag_details.bag_tag1
                            ,p_tag2a          => r_bag_details.bag_tag2a
                            ,p_tag2b          => r_bag_details.bag_tag2b
                            ,p_tag3           => r_bag_details.bag_tag3
                            ,p_trace_action   => 'XFER'
                            ,p_scanner_id     => l_scanner_id
                            ,p_flight_id_scan => l_uld_flight_id_dest
                            ,p_paxid          => r_bag_details.pax_id
                            ,p_uld_no         => l_uld_num_dest  );    -- pass ULD number (Nigel, 01/04/04, CA598)

      /* Set up the result to be returned */
      IF ( l_retcode = pkg_constants.ERROR_WRONG_FLIGHT ) THEN
              RAISE error_uld_wrong_flight;
      ELSIF ( l_retcode = pkg_constants.ERROR_DEST_ULD_NOT_FOUND ) THEN
              RAISE error_unknown_uld_dest;
      ELSIF ( l_retcode = pkg_constants.ERROR_FLIGHT_STATUS_PD ) THEN
              RAISE error_flight_status_pd;
      ELSIF ( l_retcode = pkg_constants.ERROR_ALREADY_LOADED ) THEN
              RAISE error_bag_already_loaded;
      ELSIF ( l_retcode = pkg_constants.ERROR_UNKNOWN_ULD ) THEN
              RAISE error_unknown_uld;
      ELSIF ( l_retcode != pkg_constants.MERGE_ULDS_SUCCESS OR
              l_retcode > 0 ) THEN                            -- for 100 cases
              RAISE error_general_exception;
      END IF;

      /* Some debug information */
      IF ( DEBUG = VALID  ) THEN
         dbms_output.put_line('------------------------------------------');
         dbms_output.put_line('Bag details.....'||r_bag_details.bag_tag);
         dbms_output.put_line('------------------------------------------');
         dbms_output.put_line('dcs_bg_tag       :'||r_bag_details.dcs_bag_tag);
         dbms_output.put_line('bag_tag2a        :'||r_bag_details.bag_tag2a);
         dbms_output.put_line('bag_tag2b        :'||r_bag_details.bag_tag2b);
         dbms_output.put_line('bag_tag3         :'||r_bag_details.bag_tag3);
         dbms_output.put_line('pax_id           :'||r_bag_details.pax_id);
         dbms_output.put_line('l_uld_num_src    :'||l_uld_num_src);
         dbms_output.put_line('l_uld_num_dest   :'||l_uld_num_dest);
         dbms_output.put_line('l_location_code  :'||l_location_code);
         dbms_output.put_line('l_scanner_id     :'||l_scanner_id);
         dbms_output.put_line('bpmid            :'||0);
         dbms_output.put_line('fltid            :'||l_uld_flight_id_dest);
         dbms_output.put_line('l_retcode        :'||l_retcode );
      END IF;


    END LOOP; -- c_get_bag_details

    /* Check to see whether any rows found */
    IF  ( c_get_bag_details%ROWCOUNT = 0 ) THEN
         RAISE error_general_exception;
    END IF;

    /* Close this cursor */
    CLOSE c_get_bag_details;

    /* Create a trace file and then delete the source uld,
     * since this is no longer needed.
     */
    IF ( l_retcode = pkg_constants.MERGE_ULDS_SUCCESS ) THEN
        /* Delete from uld_dests */
        DELETE FROM uld_dests
                WHERE uld_no = l_uld_num_src;
                /* Dont error if not found */
        IF SQL%NOTFOUND THEN
           NULL;
        END IF;

        /* Insert into uld_trace and delete from ulds */
        deassign_uld ( p_uld_no   => l_uld_num_src
                      ,userid     => l_user_id
                      ,loc        => l_location_code
                      ,retval     => l_retcode );

        /* Now delete the source card */
        DELETE FROM uld_recorded_id_codes
        WHERE uld_id_code = p_uld_id_code_src;
                /* Dont error if not found */
            IF SQL%NOTFOUND THEN
                   NULL;
                END IF;


       /* Trap all errors, since we need to keep a reference of
        * source ULD in uld_trace.
        */
       IF ( l_retcode != pkg_constants.MERGE_ULDS_SUCCESS ) THEN
            dbms_output.put_line('Error creating trace record');
            RAISE error_general_exception;
       END IF;
       /* Some debug information */
       IF ( DEBUG = VALID ) THEN
          dbms_output.put_line('ULD :'||l_uld_num_src||' having flight_id '||l_uld_flight_id_src);
          dbms_output.put_line('has now been deleted ');
       END IF;
    END IF; -- to insert into uld_trace and delete source uld

    /***********************************************************
     * Process BPM functionality and create the BPMS
     ***********************************************************
     */

    IF ( l_hold_dest = 1 ) THEN
	l_bpm_type := '7';
    ELSE
	l_bpm_type := '5';
    END IF;

    DECLARE
    	l_process flight.process_name%type;
	l_unit    flight.unit_name%type;
    	l_dcsid   flight.dcsid%type;
	l_subunit flight.subunit_name%type;
    BEGIN
    	SELECT f.process_name, f.unit_name, f.dcsid, f.subunit_name
	INTO l_process, l_unit, l_dcsid, l_subunit
	FROM flight f
	WHERE f.flight_id = l_uld_flight_id_dest;

	get_bpm_status(l_process,
		       l_unit,
		       l_dcsid,
		       l_subunit,
		       null,
		       l_uld_flight_id_dest,
		       l_bpm_type,
		       l_bpm_status);
    EXCEPTION
    	WHEN NO_DATA_FOUND THEN
	    l_bpm_status := '';
    END;

    /* Check to see whether it is bpm_status */
    IF ( l_bpm_status = 'Y' ) THEN
        generate_bpmid ( l_bpm_id );

        /* Create a mail record */
       mail_bpm2 ( fid        => l_uld_flight_id_dest
                   ,tuld       => l_uld_num_dest
                   ,fuld       => l_uld_num_src
                   ,btype      => l_bpm_type
                   ,bpmid      => l_bpm_id
                   ,scanner_id => l_scanner_id
									 ,pax_id => l_pax_id
                   ,retval     => l_retcode
                   );

        IF ( l_retcode < 0 ) THEN           -- note that retval can be < 0 and > 0
             RAISE error_general_exception; -- if > 0 then this is a success retval
        END IF;                             -- need to trap any errors from this

        /* Now create a bpm_data record */
        store_bpm_user ( bpmid  => l_bpm_id
                        ,uid    => l_user_id
                        ,locn   => l_location_code
                        );
    END IF;



    p_result  := l_result;

EXCEPTION
    WHEN error_src_uld_not_reg THEN
             /* Close cursor */
                 IF ( c_check_registered%ISOPEN ) THEN
                    CLOSE c_check_registered;
             END IF;

                 /* Close Cursor */
                 IF ( c_get_uld_num_details%ISOPEN ) THEN
                    CLOSE c_get_uld_num_details;
             END IF;

                 /* Some debug information */
         IF ( DEBUG = VALID ) THEN
           dbms_output.put_line('Error Source ULD Not registered :'||l_err_uld_id_code);
         END IF;

         l_result := pkg_constants.ERROR_SRC_ULD_NOT_REG;
         p_result := l_result;

    WHEN error_dest_uld_not_reg THEN
             /* Close cursor */
                 IF ( c_check_registered%ISOPEN ) THEN
                    CLOSE c_check_registered;
             END IF;

                 /* Close Cursor */
                 IF ( c_get_uld_num_details%ISOPEN ) THEN
                    CLOSE c_get_uld_num_details;
             END IF;

                 /* Some debug information */
         IF ( DEBUG = VALID ) THEN
           dbms_output.put_line('Error Dest ULD Not registered :'||l_err_uld_id_code);
         END IF;

         l_result := pkg_constants.ERROR_DEST_ULD_NOT_REG;
         p_result := l_result;


    WHEN error_no_bpm_status THEN
         /* Close cursor */
         IF ( c_get_bpm_status%ISOPEN ) THEN
              CLOSE c_get_bpm_status;
         END IF;
         /* Some debug information */
         IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('Error no bpm status...');
         END IF;

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

    WHEN error_flight_status_pd THEN
         /* Close cursor */
         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 flight status set to PD...');
         END IF;
         l_result := pkg_constants.ERROR_FLIGHT_STATUS_PD;
         p_result := l_result;

    WHEN error_bag_already_loaded THEN
         /* Close cursor */
         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 bag already loaded...');
         END IF;

         l_result := pkg_constants.ERROR_ALREADY_LOADED;
         p_result := l_result;

    WHEN error_general_exception THEN
        /* Close the cursor, if opened */
        IF ( c_get_bag_details%ISOPEN ) THEN
           CLOSE c_get_bag_details;
        END IF;

        /* Close cursor if opened */
        IF ( c_get_user_id%ISOPEN ) THEN
           CLOSE c_get_user_id;
        END IF;

        /* Some debug information */
        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_unknown_user THEN
         /* Close cursor if opened */
         IF ( c_get_user_id%ISOPEN ) THEN
            CLOSE c_get_user_id;
         END IF;

         /* Some debug information  */
         IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('Error unknown user :'||l_emp_num );
         END IF;
         l_result := pkg_constants.ERROR_USER_UNKNOWN;
         p_result := l_result;

    WHEN error_uld_wrong_flight THEN
         /* Close the cursor, if opened */
         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 Wrong Flights (src) :'||
                                  l_uld_flight_id_src||'- (dest)'||
                                  l_uld_flight_id_dest);
         END IF;
         l_result := pkg_constants.ERROR_WRONG_FLIGHT;
         p_result := l_result;

    WHEN error_no_bags THEN
         /* Close cursor */
         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 No Bags for Source Containter: ');
         END IF;

         l_result  := pkg_constants.ERROR_SOURCE_ULD_NO_BAGS;
         p_result  := l_result;

    WHEN error_unknown_uld THEN
            /* Close cursor */
                IF ( c_confirm_valid_card%ISOPEN ) THEN
                   CLOSE c_confirm_valid_card;
                END IF;

        /* Close cursor */
        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 ULD Card Number :'||l_err_uld_id_code);
        END IF;
        l_result  := pkg_constants.ERROR_UNKNOWN_ULD;
        p_result  := l_result;

    WHEN error_unknown_uld_dest THEN
            /* Close cursor */
                IF ( c_confirm_valid_card%ISOPEN ) THEN
                   CLOSE c_confirm_valid_card;
                END IF;

       /* Close the cursor, if opened */
        IF ( c_get_bag_details%ISOPEN ) THEN
           CLOSE c_get_bag_details;
        END IF;

        l_result := pkg_constants.ERROR_DEST_ULD_NOT_FOUND;
        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_merge_uld
/
