/*******************************************************************************
*
* Filename         -  $Workfile: rename_uld.sql  $
*
*                     Name         Date       Version     CR Number
* Author              Chris Penny  20/12/2003
*
* Project Name     -  Ultratrak Database
*
* Source Code
* Identifier       -  FAS/FALCP
*
* Description      -  Live Schema Stored Procedure to rename uld:
*                     1. Registration details of original ULD are retrieved.
*                     2. New ULD is registered with same details as original ULD.
*                     3. Original ULD is disassociated from the ULD card (if any).
*                     4. New ULD is associated with the original ULD card (if any).
*                     5. Create ULD_TRACE entry for old ULD name (and this also creates BAG_TRACE entries)
*                     6. Any loaded bags are transferred from original ULD to new ULD.
*                     7. Create ULD_TRACE entry for new ULD name (and this also creates BAG_TRACE entries)
*                     8. Original ULD is removed 
*
* Language         -  Oracle PL/SQL
*
* File History     -  For development history run Show History report in PVCS
*
* Copyright Ultra Electronics Airport Systems Limited, 2003
*
*******************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE rename_uld

CREATE OR REPLACE PROCEDURE rename_uld (
    p_scanner_id    IN  VARCHAR2 DEFAULT NULL
  , p_flight_id     IN  ulds.flight_id%TYPE
  , p_from_uld_no   IN  ulds.uld_no%TYPE
  , p_to_uld_no     IN  ulds.uld_no%TYPE
  , p_uld_id_code   IN  ulds.uld_id_code%TYPE
  , p_user_id       IN  tagtrak_users.user_id%TYPE
  , p_location      IN  location.location_code%TYPE
  , p_retcode       OUT NUMBER
)
AS

    -- Local constants
    DEBUG                CONSTANT VARCHAR2(1)  DEFAULT 'N';
    VALID                CONSTANT VARCHAR2(1)  DEFAULT 'Y';
    ASSOCIATE_ULD        CONSTANT VARCHAR2(1)  DEFAULT 'A';
    DISASSOCIATE_ULD     CONSTANT VARCHAR2(1)  DEFAULT 'D';
    ULD_RENAME_OLD       CONSTANT VARCHAR2(10) DEFAULT 'RENAME OLD';
    ULD_RENAME_NEW       CONSTANT VARCHAR2(10) DEFAULT 'RENAME NEW';
    L_SCAN_ID            CONSTANT VARCHAR2(1)  DEFAULT '0';		 /*  For adding bag_trace record  */

    -- local variables for ULDs
    l_from_uld_no   ulds.uld_no%TYPE := p_from_uld_no;
    l_to_uld_no     ulds.uld_no%TYPE := p_to_uld_no;
    l_uld_id_code   ulds.uld_id_code%TYPE := p_uld_id_code;
    l_uld_cat       uld_recorded_id_codes.uld_category%TYPE;
    l_uld_type      uld_recorded_id_codes.uld_type%TYPE;
    l_conn_type     ulds.connection_type%TYPE;
    l_dest1         uld_recorded_id_codes.dest1%TYPE;
    l_class1        uld_recorded_id_codes.class1%TYPE;
    l_dest2         uld_recorded_id_codes.dest2%TYPE;
    l_class2        uld_recorded_id_codes.class2%TYPE;
    l_dest3         uld_recorded_id_codes.dest3%TYPE;
    l_class3        uld_recorded_id_codes.class3%TYPE;
    l_dest4         uld_recorded_id_codes.dest4%TYPE;
    l_class4        uld_recorded_id_codes.class4%TYPE;
    l_dest5         uld_recorded_id_codes.dest5%TYPE;
    l_class5        uld_recorded_id_codes.class5%TYPE;
    l_dest6         uld_recorded_id_codes.dest6%TYPE;
    l_class6        uld_recorded_id_codes.class6%TYPE;
    l_onward_flight_no1           uld_recorded_id_codes.onward_flight_no1%TYPE;
    l_onward_flight_date1         uld_recorded_id_codes.onward_flight_date1%TYPE;
    l_onward_flight_no2           uld_recorded_id_codes.onward_flight_no2%TYPE;
    l_onward_flight_date2         uld_recorded_id_codes.onward_flight_date2%TYPE;
    l_onward_flight_no3           uld_recorded_id_codes.onward_flight_no3%TYPE;
    l_onward_flight_date3         uld_recorded_id_codes.onward_flight_date3%TYPE;
    l_onward_flight_no4           uld_recorded_id_codes.onward_flight_no4%TYPE;
    l_onward_flight_date4         uld_recorded_id_codes.onward_flight_date4%TYPE;
    l_onward_flight_no5           uld_recorded_id_codes.onward_flight_no5%TYPE;
    l_onward_flight_date5         uld_recorded_id_codes.onward_flight_date5%TYPE;
    l_onward_flight_no6           uld_recorded_id_codes.onward_flight_no6%TYPE;
    l_onward_flight_date6         uld_recorded_id_codes.onward_flight_date6%TYPE;


    l_uld_status    NUMBER;
    l_uld_exists    CHAR(1);

    -- local variables for USER
    l_location      location.location_code%TYPE := p_location;
    l_scanner_id    kit.scanner_id%TYPE := p_scanner_id;
    l_user_id       ulds.user_id%TYPE := p_user_id;

    -- local variables for FLIGHT
    l_flt_id        ulds.flight_id%TYPE := p_flight_id;
    l_flt_no        flight.flight_no%TYPE;
    l_flt_date      uld_recorded_id_codes.flight_date%TYPE;
    l_site_id       flight.site_id%TYPE := NULL;

    -- local variables for GENERAL
    l_pax_id        pax.pax_id%TYPE;
    l_ret_date      CHAR(20) := RTRIM(TO_CHAR(SYSDATE, 'HH24:MI'));
    l_bag_count     NUMBER   := 0;

    -- local variables for BPMs
    l_bpmid         NUMBER;
    l_bpmsts        CHAR;
    l_bpmtyp        CHAR := '5';
    retcode         NUMBER := 0;

    -- local variables for ERROR HANDLING
    l_process_name  oracle_error.process_name%TYPE := 'RENAME_ULD';
    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_result        VARCHAR2(10)                   := NULL;
    l_error_message VARCHAR2(512)                  := NULL;
    l_error_number  NUMBER                         := NULL;

    trace_time      CHAR(5);

    -- Exceptions
    error_bpm_mail                 EXCEPTION;
    error_unknown_user             EXCEPTION;
    error_unknown_uld              EXCEPTION;
    error_unknown_flight           EXCEPTION;
    error_uld_already_associated   EXCEPTION;
    error_general_exception        EXCEPTION;
    error_uld_diff_flight          EXCEPTION;
    error_uld_already_exists       EXCEPTION;
    error_flight_access_denied     EXCEPTION;
    error_uld_not_associated       EXCEPTION;
    error_uld_disassociation       EXCEPTION;
    error_uld_association          EXCEPTION;
    error_uld_id_code              EXCEPTION;

    /*
     *  CA821
     *  Parameterised cursor so that it can be reuse for different ULD.
     *  Also select more columns for bag_trace purpose.
     *
     */

    CURSOR c_bag_cursor(uldnum IN ulds.uld_no%type) IS
       SELECT bag_tag
            , pax_id
            , bag_tag1
            , bag_tag2a
            , bag_tag2b
            , bag_tag3
       FROM   bag
       WHERE  loaded_status = 'Y'
         AND  uld_no        = uldnum
         AND  uld_flight_id = l_flt_id;

    /* Cursor to derive ULD destinations and classes */
    CURSOR c_uld_dests IS
       SELECT uld_destination
             ,uld_class_list
             ,onward_flight_no
             ,onward_flight_date
       FROM   uld_dests
       WHERE  uld_no = p_from_uld_no
       AND    flight_id = l_flt_id;

BEGIN

    /***********************************************************************************
    *  1. Registration details of original ULD are retrieved.
    *     Check if user has access to the flight ( user_authentication.validate_access )
    *   RETURN codes:
    *    0 = OK
    *   -102 = Unknown User
    *   -139 = Unknown Flight
    *   -210 = Source ULD Unknown
    *   -1031 = Access Denied
    *   -1300 = ULD Not Associated
    *************************************************************************************/

    IF l_flt_id IS NULL THEN

       BEGIN

          IF l_user_id IS NOT NULL THEN
              l_site_id := multi_site.get_user_siteid(l_user_id);
          END IF;

          dbms_output.put_line('multi site id from user id: ' || l_site_id );

          IF l_site_id IS NULL AND l_location IS NOT NULL THEN
              SELECT site_id
              INTO   l_site_id
              FROM   location
              WHERE  location_code = l_location; -- pK location_code
          END IF;

          dbms_output.put_line('site id from location: ' || l_site_id );

          /* There can only ever be one uldno for non-departed flights (including multisite) */
          SELECT flight_id
          INTO   l_flt_id
          FROM   ulds
          WHERE  uld_no = l_from_uld_no
          AND    pd_marker != 'Y';

       EXCEPTION
          WHEN NO_DATA_FOUND THEN
                  dbms_output.put_line('FLT ID: ' || l_flt_id || ' SITE ID: ' || l_site_id );
		  RAISE error_unknown_flight;
       END;

    END IF;

    /* if ULD to rename TO already exists for non-departed flight raise ULD already exists error */
    BEGIN
        -- for all flights
        SELECT 'Y'
        INTO   l_uld_exists
        FROM   ulds
        WHERE  uld_no = p_to_uld_no
	AND    pd_marker != 'Y';

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            l_uld_exists := '';
    END;

    IF l_uld_exists = 'Y' THEN
        RAISE error_uld_already_exists;
    END IF;

    /* If the ULD to be renamed does not exist (or if it exists with pd_marker 'Y') raise unknown ULD error */
    BEGIN

        SELECT 'Y'
        INTO   l_uld_exists
        FROM   ulds
        WHERE  uld_no = l_from_uld_no
        AND  ( uld_id_code = l_uld_id_code
            OR uld_id_code IS NULL
             )
        AND    pd_marker != 'Y'
        AND    flight_id = l_flt_id;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            l_uld_exists := 'N';
    END;

    IF l_uld_exists = 'Y' THEN
        BEGIN
            -- Get details from the ULDS table
            SELECT uld_category
                 , uld_type
            INTO   l_uld_cat
                 , l_uld_type
            FROM   ulds
            WHERE  uld_no = l_from_uld_no
            AND  ( uld_id_code = l_uld_id_code
               OR  uld_id_code IS NULL
	         )
            AND    pd_marker != 'Y'
            AND    flight_id = l_flt_id;

            FOR uld_dests_rec IN c_uld_dests LOOP
                -- Set up ULD dests and classes
                IF l_dest1 IS NULL THEN
                    l_dest1 := uld_dests_rec.uld_destination;
                    l_class1 := uld_dests_rec.uld_class_list;
                    l_onward_flight_no1          := uld_dests_rec.onward_flight_no;
                    l_onward_flight_date1        := uld_dests_rec.onward_flight_date;
                ELSIF l_dest2 IS NULL THEN
                    l_dest2 := uld_dests_rec.uld_destination;
                    l_class2 := uld_dests_rec.uld_class_list;
                    l_onward_flight_no2          := uld_dests_rec.onward_flight_no;
                    l_onward_flight_date2        := uld_dests_rec.onward_flight_date;
                ELSIF l_dest3 IS NULL THEN
                    l_dest3 := uld_dests_rec.uld_destination;
                    l_class3 := uld_dests_rec.uld_class_list;
                    l_onward_flight_no3          := uld_dests_rec.onward_flight_no;
                    l_onward_flight_date3        := uld_dests_rec.onward_flight_date;
                ELSIF l_dest4 IS NULL THEN
                    l_dest4 := uld_dests_rec.uld_destination;
                    l_class4 := uld_dests_rec.uld_class_list;
                    l_onward_flight_no4          := uld_dests_rec.onward_flight_no;
                    l_onward_flight_date4        := uld_dests_rec.onward_flight_date;
                ELSIF l_dest5 IS NULL THEN
                    l_dest5 := uld_dests_rec.uld_destination;
                    l_class5 := uld_dests_rec.uld_class_list;
                    l_onward_flight_no5          := uld_dests_rec.onward_flight_no;
                    l_onward_flight_date5        := uld_dests_rec.onward_flight_date;
                ELSIF l_dest6 IS NULL THEN
                    l_dest6 := uld_dests_rec.uld_destination;
                    l_class6 := uld_dests_rec.uld_class_list;
                    l_onward_flight_no6          := uld_dests_rec.onward_flight_no;
                    l_onward_flight_date6        := uld_dests_rec.onward_flight_date;
                END IF; -- l_dest1 IS NULL
            END LOOP;

            -- Get the flight details
            SELECT flight_no
                 , flight_date
            INTO   l_flt_no
                 , l_flt_date
            FROM   flight
            WHERE  flight_id = l_flt_id;

        EXCEPTION
	    WHEN OTHERS THEN
                NULL;
        END;

    ELSE

        /* If the source ULD does not exist then we are not interested in *
         * getting information from uld_recorded_id_codes for a uld card  *
         * with no associated uld (like get_uld_info)                     */

        RAISE error_unknown_uld;

    END IF;

    user_authentication.validate_access(l_user_id, NVL(l_flt_id, '0'), l_flt_no, l_flt_date, retcode);

    IF retcode = pkg_constants.error_user_unknown THEN -- -102
        RAISE error_unknown_user;
    END IF;

    IF retcode = pkg_constants.error_unknown_flight THEN -- -139
        RAISE error_unknown_flight;
    END IF;

    IF retcode = pkg_constants.error_flight_access_denied THEN -- -1031
        RAISE error_flight_access_denied;
    END IF;

    /************************************************************
    *  2. New ULD is registered with same details as original ULD.
    *
    *     add_uld_record.sql
    *     add_uld_dests_record.sql
    *
    ************************************************************/

     /* We need connection type if not null */
     SELECT NVL(connection_type,'')
     INTO   l_conn_type
     FROM   ulds
     WHERE  uld_no = l_from_uld_no
     AND    flight_id = l_flt_id;

     IF retcode = 0 THEN

        add_uld_record (
                          l_to_uld_no
                        , l_flt_id
                        , l_uld_cat
                        , l_uld_type
                        , l_user_id
                        , l_location
                        , l_conn_type
                        , l_uld_id_code
                       );

	-- CA02015 - Copy the next sequence number across.
	--
	UPDATE ulds
	SET next_bag_seq_no = (SELECT next_bag_seq_no
			       FROM   ulds
			       WHERE  uld_no = p_from_uld_no
			       AND    flight_id = l_flt_id)
	WHERE uld_no = l_to_uld_no AND flight_id = l_flt_id;

        -- ULD_TRACE table populated by add_uld_record with trace_action 'ASSIGN'

	IF ( DEBUG = VALID ) THEN
             dbms_output.put_line('------------------------------------');
             dbms_output.put_line('Calling add_uld_dests_record ');
             dbms_output.put_line('------------------------------------');
             dbms_output.put_line( 'DESTINATIONS: ' || l_dest2 || ',' || l_dest3 || ',' || l_dest4 || ',' || l_dest5 || ','|| l_dest6);
             dbms_output.put_line('------------------------------------');
        END IF;

        add_uld_dests_record (
                                l_to_uld_no
                              , l_dest1
                              , l_class1
                              , l_flt_id
                              , l_onward_flight_no1
                              , l_onward_flight_date1
                             );

        IF l_dest2 IS NOT NULL THEN

	    add_uld_dests_record (
                                    l_to_uld_no
                                  , l_dest2
                                  , l_class2
                                  , l_flt_id
                                  , l_onward_flight_no2
                                  , l_onward_flight_date2
                                 );

            IF l_dest3 IS NOT NULL THEN

                add_uld_dests_record (
                                        l_to_uld_no
                                      , l_dest3
                                      , l_class3
                                      , l_flt_id
                                      , l_onward_flight_no3
                                      , l_onward_flight_date3
                                     );

                IF l_dest4 IS NOT NULL THEN

                    add_uld_dests_record (
                                            l_to_uld_no
                                          , l_dest4
                                          , l_class4
                                          , l_flt_id
                                          , l_onward_flight_no4
                                          , l_onward_flight_date4
                                         );

                    IF l_dest5 IS NOT NULL THEN

                        add_uld_dests_record (
                                                l_to_uld_no
                                              , l_dest5
                                              , l_class5
                                              , l_flt_id
                                              , l_onward_flight_no5
                                              , l_onward_flight_date5
                                             );

                        IF l_dest6 IS NOT NULL THEN

                            add_uld_dests_record (
                                                l_to_uld_no
                                              , l_dest6
                                              , l_class6
                                              , l_flt_id
                                              , l_onward_flight_no6
                                              , l_onward_flight_date6
                                             );
                        END IF;

                    END IF;

                END IF;

            END IF;

        END IF;

    ELSE

        IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('------------------------------------');
            dbms_output.put_line('RETCODE: ' || retcode );
            dbms_output.put_line('------------------------------------');
        ELSE
	    NULL;
	END IF;

    END IF;

    /******************************************************************
    *
    *  3. Original ULD is disassociated from the ULD card (if any).
    *
    *******************************************************************/
     DBMS_OUTPUT.PUT_LINE('disassociate_recorded_uld');

     associate_recorded_uld (
                               l_from_uld_no
                             , l_uld_id_code
                             , 'D' -- disassociate
                             , retcode
                            );
     IF ( DEBUG = VALID ) THEN
         dbms_output.put_line('------------------------------------');
         dbms_output.put_line('After dis-association ');
         dbms_output.put_line('------------------------------------');
         dbms_output.put_line('Old ULD: '|| l_from_uld_no);
         dbms_output.put_line('ULD ID CODE: '||l_uld_id_code);
         dbms_output.put_line('retcode: ' || retcode);
         dbms_output.put_line('------------------------------------');
      END IF;

     IF retcode = -1307 THEN
         RAISE error_uld_disassociation;
     END IF;

    /******************************************************************
    *
    *  4. New ULD is associated with the original ULD card (if any).
    *
    *******************************************************************/

     DBMS_OUTPUT.PUT_LINE('associate_recorded_uld');

     associate_recorded_uld (
                               l_to_uld_no
                             , l_uld_id_code
                             , 'A' -- associate
                             , retcode
                            );

     IF ( DEBUG = VALID ) THEN
         dbms_output.put_line('------------------------------------');
         dbms_output.put_line('After new association ');
         dbms_output.put_line('------------------------------------');
         dbms_output.put_line('New ULD :'|| l_to_uld_no);
         dbms_output.put_line('ULD ID CODE   :'||l_uld_id_code);
         dbms_output.put_line('RETCODE: ' || retcode);
         dbms_output.put_line('------------------------------------');
     END IF;

    IF retcode = pkg_constants.error_uld_already_associated THEN -- -1304
        RAISE error_uld_already_associated;
    END IF;

    IF retcode = pkg_constants.error_uld_id THEN -- -1305
        RAISE error_uld_id_code;
    END IF;

    IF retcode = pkg_constants.error_uld_association THEN -- -1306
        RAISE error_uld_association;
    END IF;

     /* Update the ulds table with the uld_id_code value */
     UPDATE ulds
     SET    uld_id_code = l_uld_id_code
     WHERE  uld_no      = l_to_uld_no;

     dbms_output.put_line('ULDS updated with uld_id_code.');

    /******************************************************************
     * Count the number of bags in the uld to be renamed. If there are
     * no bags then it is not necessary to create a BPM since they
     * should only be generated for bags.
     ******************************************************************/
     BEGIN
       SELECT Count(*)
       INTO   l_bag_count
       FROM   bag
       WHERE  loaded_status = 'Y'
       AND    uld_no        = l_from_uld_no
       AND    uld_flight_id = l_flt_id;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          l_bag_count := 0;
    END;

    /******************************************************************
    *  5. Create uld trace entry for the 'old' ULD name and this also 
    *     creates bag_trace entries
    *******************************************************************/
    add_uld_trace_record(l_from_uld_no, l_flt_id, l_user_id, NVL(l_scanner_id,'0'), l_location, trace_time, ULD_RENAME_OLD, 'Y');

    /******************************************************************
    *  6. Any loaded bags are transferred from original ULD to new ULD.
    *     First check for BPM requirement.
    *******************************************************************/

    /* check if we should do BPMs */
    DECLARE
    	l_process      flight.process_name%type;
    	l_unit         flight.unit_name%type;
    	l_dcsid        flight.dcsid%type;
    	l_subunit_name 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_name
	FROM flight f
	WHERE f.flight_id = l_flt_id;

	get_bpm_status(l_process,
		       l_unit,
		       l_dcsid,
		       l_subunit_name,
		       null,
		       l_flt_id,
		       l_bpmtyp,
		       l_bpmsts);
    EXCEPTION
         WHEN NO_DATA_FOUND THEN
             l_bpmsts := '';
    END;

    /* Create a BPM only if there are bags in the uld */
    IF (l_bpmsts = 'Y' AND l_bag_count > 0) THEN

        generate_bpmid ( l_bpmid );

        dbms_output.put_line('Start BPM processing ...');
      	dbms_output.put_line('FLT ID: ' || l_flt_id);
        dbms_output.put_line('FROM ULD NO: ' || l_from_uld_no);
        dbms_output.put_line('TO ULD NO: ' || l_to_uld_no);
        dbms_output.put_line('BPM TYPE: ' || l_bpmtyp);
        dbms_output.put_line('BPM ID: ' || l_bpmid);
        dbms_output.put_line('SCANNER ID: ' || l_scanner_id);
        dbms_output.put_line('PAX ID: ' || l_pax_id);
        dbms_output.put_line('RETCODE: ' || retcode);

        mail_bpm2 (
                     l_flt_id
                   , l_to_uld_no
                   , l_from_uld_no
		   , l_bpmtyp
                   , l_bpmid
                   , l_scanner_id
                   , l_pax_id
                   , retcode
                  );

        IF retcode = pkg_constants.error_bpm_mail THEN -- -1015
            RAISE error_bpm_mail;
        END IF;

	dbms_output.put_line('mail_indiv record created by mail_bpm2.');




        store_bpm_user (
	                  l_bpmid
                        , l_user_id
                        , l_location
                       );



	dbms_output.put_line('store_bpm_user bpm_data record created with pax_id of Zero.');

        IF (l_bpmid > 0) THEN
            dbms_output.put_line( 'Opening Bag Cursor ... ');
            FOR r_bag_cursor IN c_bag_cursor(l_from_uld_no) LOOP
                INSERT INTO bpm_data (
                                        bpmid
                                      , pax_id
                                      , bag_tag
                                      , user_id
                                      , scan_time
                                     )
                              VALUES (
                                        l_bpmid
                                      , r_bag_cursor.pax_id
                                      , r_bag_cursor.bag_tag
                                      , l_user_id
                                      , SYSDATE
                                     );
                dbms_output.put_line( 'bpm_data populated for pax: ' || r_bag_cursor.pax_id || ', bag: ' || r_bag_cursor.bag_tag );
            END LOOP;
        END IF;

    END IF;

    UPDATE bag
    SET    uld_no = l_to_uld_no
    WHERE  uld_no = l_from_uld_no
    AND    pax_id IN (
                       SELECT pax_id
                       FROM   pax
                       WHERE  flight_id = l_flt_id
                     )
    AND    uld_no IS NOT NULL;

    dbms_output.put_line('ULD_NO updated in bag table.');

    UPDATE ulds
    SET uld_no_of_bags = (
                           SELECT COUNT(b.bag_tag)
                           FROM   bag b
                                , pax p
                                , flight f
                           WHERE  b.pax_id = p.pax_id
                           AND    p.flight_id = f.flight_id
			                     AND    f.flight_id = l_flt_id
			                     AND    b.uld_no = l_to_uld_no
			                     AND    b.loaded_status = 'Y'
			                     AND    f.flight_status != 'PD'
			                   )
    WHERE uld_no = l_to_uld_no
    AND   flight_id = l_flt_id;

    dbms_output.put_line('uld_no_of_bags updated in ULDS table.');

    /******************************************************************
    *  7. Create uld trace entry for the 'new' ULD name and this also 
    *     creates BAG_TRACE entries
    *******************************************************************/
    add_uld_trace_record(l_to_uld_no, l_flt_id, l_user_id, NVL(l_scanner_id,'0'), l_location, trace_time, ULD_RENAME_NEW, 'Y');


    /******************************************************************
    *  8. Original ULD is removed
    *******************************************************************/

    DELETE FROM uld_dests
    WHERE uld_no = p_from_uld_no
    AND   flight_id = l_flt_id;

    dbms_output.put_line('ULD: ' || p_from_uld_no || ' Deleted from ULD_DESTS table for flight: ' || l_flt_id);


    DELETE FROM ulds
    WHERE uld_no = p_from_uld_no
    AND   flight_id = l_flt_id;

    dbms_output.put_line('ULD: ' || p_from_uld_no || ' Deleted from ULDs table for flight: ' || l_flt_id);


    p_retcode := NVL(p_retcode,0);

EXCEPTION

    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No Data Found' || retcode);

    WHEN error_unknown_uld THEN

	IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('--------------------------------------');
            dbms_output.put_line('Error Unknown ULD');
        END IF;

	l_result := pkg_constants.ERROR_UNKNOWN_ULD;
        p_retcode := l_result;

    WHEN error_uld_already_exists THEN

	IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('--------------------------------------');
            dbms_output.put_line('Error ULD already exists.');
        END IF;

	l_result := pkg_constants.ERROR_ULD_ALREADY_EXISTS;
        p_retcode := l_result;

    WHEN error_unknown_user THEN

        IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('--------------------------------------');
            dbms_output.put_line('Error Unknown User.');
        END IF;

	l_result := pkg_constants.error_user_unknown;
        p_retcode := l_result;

    WHEN error_unknown_flight THEN

        IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('--------------------------------------');
            dbms_output.put_line('Error Unknown Flight.');
        END IF;

	l_result := pkg_constants.error_unknown_flight;
        p_retcode := l_result;

    WHEN error_flight_access_denied THEN

        IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('--------------------------------------');
            dbms_output.put_line('Error Flight Access Denied.');
        END IF;

	l_result := pkg_constants.error_flight_access_denied;
        p_retcode := l_result;

    WHEN error_uld_not_associated THEN

        IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('--------------------------------------');
            dbms_output.put_line('Error ULD Not Associated.');
        END IF;

	l_result := pkg_constants.error_uld_not_associated;
        p_retcode := l_result;

    WHEN error_uld_already_associated THEN

        IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('--------------------------------------');
            dbms_output.put_line('Error ULD Already Associated.');
        END IF;

	l_result := pkg_constants.error_uld_already_associated;
        p_retcode := l_result;

    WHEN error_uld_disassociation THEN

        IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('--------------------------------------');
            dbms_output.put_line('Neither ULD_NO nor ULD_ID_CODE set.');
        END IF;

	l_result := pkg_constants.error_uld_disassociation;
        p_retcode := l_result;

    WHEN error_uld_id_code THEN

        IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('--------------------------------------');
            dbms_output.put_line('ULD_ID_CODE not set.');
        END IF;

	l_result := pkg_constants.error_uld_id;
        p_retcode := l_result;

    WHEN error_uld_association THEN

        IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('--------------------------------------');
            dbms_output.put_line('Neither ULD_NO nor ULD_ID_CODE set.');
        END IF;

	l_result := pkg_constants.error_uld_association;
        p_retcode := l_result;

    WHEN error_bpm_mail THEN

        IF ( DEBUG = VALID ) THEN
            dbms_output.put_line('--------------------------------------');
            dbms_output.put_line('Mail_bpm2: No Data Found.');
        END IF;

	l_result := pkg_constants.error_bpm_mail;
        p_retcode := l_result;

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

        /* Create oracle_error record */
        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 rename_uld;
/
show errors
