/*****************************************************************************
*
* Filename         - $Workfile:   delete_duplicates.sql  $
*
*                     Name         Date       Version     CR Number
*                     Chris Penny  20/11/2003		
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 
*
* Design Spec
* Section          - 
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE delete_duplicates

CREATE OR REPLACE PROCEDURE delete_duplicates (
     user_id       IN tagtrak_users.user_id%TYPE
   , location      IN location.location_code%TYPE
   , tagno         IN bag.bag_tag%TYPE
   , tag1          IN bag.bag_tag1%TYPE
   , tag2a         IN bag.bag_tag2a%TYPE
   , tag2b         IN bag.bag_tag2b%TYPE
   , tag3          IN bag.bag_tag3%TYPE
   , scanner_id    IN kit.scanner_id%TYPE
   , flt_id        IN flight.flight_id%TYPE
   , handle_dupes  IN CHAR
   , retval        IN OUT NUMBER
) 
AS 
 
    depair             pax.departure_airport_code%type := null;
    l_max_time_stored  bag.time_bag_stored%TYPE;
    l_pax_bag_count    pax.no_of_bags%TYPE;

    CURSOR cu_del_res_dups (max_time_stored IN DATE) IS
	SELECT b.rowid
	     , p.pax_id
	     , p.flight_id
	FROM   bag b
	     , pax p
	WHERE  p.pax_id  = b.pax_id
	AND    bag_tag3 = tag3
	AND  ( 
	       bag_tag2b = NVL(tag2b,'-')
	 OR    bag_tag2a = NVL(tag2a,'-') 
	     )
	AND    b.delete_marker != 1
	AND    time_bag_stored != l_max_time_stored;


    CURSOR cu_del_unres_dups (max_time_stored IN DATE) IS
	SELECT b.rowid
	     , p.pax_id
	FROM   bag b
	     , pax p
	WHERE  p.pax_id  = b.pax_id
	AND    bag_tag3 = tag3
	AND  ( 
	       bag_tag2b = NVL(tag2b,'-')
	 OR    bag_tag2a = NVL(tag2a,'-') 
	     )
	AND    b.delete_marker != 1
	AND    p.flight_id = flt_id
	AND    time_bag_stored != l_max_time_stored;
	

BEGIN

    depair := get_offset.get_user_location(user_id);

    IF handle_dupes = 'R' THEN

        SELECT MAX(time_bag_stored)
	INTO   l_max_time_stored
	FROM   bag
	WHERE  bag_tag3 = tag3
	AND  ( 
	       bag_tag2b = NVL(tag2b,'-')
	 OR    bag_tag2a = NVL(tag2a,'-') 
	     )
	AND    bag.delete_marker != 1;

        FOR r_del_res_dups IN cu_del_res_dups (l_max_time_stored) LOOP

            UPDATE bag
            SET    delete_marker = 1	    
            WHERE  rowid = r_del_res_dups.rowid;

            SELECT no_of_bags
            INTO   l_pax_bag_count
            FROM   pax
            WHERE  pax_id = r_del_res_dups.pax_id;

            UPDATE pax
            SET    no_of_bags = l_pax_bag_count - 1
            WHERE  pax_id = r_del_res_dups.pax_id;
        
            IF (l_pax_bag_count - 1 ) < 1 THEN

                UPDATE pax
                SET    delete_marker = 1
                WHERE  pax_id = r_del_res_dups.pax_id;

            END IF;

	    INSERT INTO bag_trace (
		bag_trace_time,
		user_id,
		location_code,
		error_id,
		bag_tag,
		bag_tag1,
		bag_tag2a,
		bag_tag2b,
		bag_tag3,
		trace_action,
		scanner_id,
		flight_id_scan,
		pax_id
	    ) 
	    VALUES (
                SYSDATE + get_offset.airport_code(depair),
                user_id,
                location,
                -1003,
                tagno,
                tag1,
                tag2a,
                tag2b,
                tag3,
                'DELETE',
                NVL(scanner_id,'0'),
                r_del_res_dups.flight_id,
                r_del_res_dups.pax_id
            ); 

        END LOOP;

    ELSIF handle_dupes = 'U' THEN

        SELECT MAX(time_bag_stored)
	INTO   l_max_time_stored
	FROM   bag
	     , pax
	WHERE  pax.pax_id  = bag.pax_id
	AND    bag_tag3 = tag3
	AND  ( 
	       bag_tag2b = NVL(tag2b,'-')
	 OR    bag_tag2a = NVL(tag2a,'-') 
	     )
	AND    bag.delete_marker != 1
	AND    pax.flight_id = flt_id;


        FOR r_del_unres_dups IN cu_del_unres_dups (l_max_time_stored) LOOP

            UPDATE bag
            SET    delete_marker = 1	    
            WHERE  rowid = r_del_unres_dups.rowid;

            SELECT no_of_bags
            INTO   l_pax_bag_count
            FROM   pax
            WHERE  pax_id = r_del_unres_dups.pax_id;

            UPDATE pax
            SET    no_of_bags = l_pax_bag_count - 1
            WHERE  pax_id = r_del_unres_dups.pax_id;
        
            IF (l_pax_bag_count - 1 ) < 1 THEN

                UPDATE pax
                SET    delete_marker = 1
                WHERE  pax_id = r_del_unres_dups.pax_id;

            END IF;

	    INSERT INTO bag_trace (
		bag_trace_time,
		user_id,
		location_code,
		error_id,
		bag_tag,
		bag_tag1,
		bag_tag2a,
		bag_tag2b,
		bag_tag3,
		trace_action,
		scanner_id,
		flight_id_scan,
		pax_id
	    ) 
	    VALUES (
                SYSDATE + get_offset.airport_code(depair),
                user_id,
                location,
                -1021,
                tagno,
                tag1,
                tag2a,
                tag2b,
                tag3,
                'DELETE',
                NVL(scanner_id,'0'),
                flt_id,
                r_del_unres_dups.pax_id
            ); 

        END LOOP;

    END IF;

EXCEPTION

    WHEN NO_DATA_FOUND THEN
        retval := -502;   /* Database Error: FAIL */

    WHEN OTHERS THEN
        retval := -502;   /* Database Error: FAIL */

END delete_duplicates;
/
