/*****************************************************************************
*
* Filename         - $Workfile:   bag_triggers.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell 25/9/02     1.10         15682
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         -  Live Schema: triggers for bag table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/



create or replace trigger tu_bag
before update on bag
for each row
declare
  
	numrows number;
	fid number;
	pid number;
	rv number;
	tag char( 10 );
  depair      pax.departure_airport_code%type := null;
  l_pax_status pax.pax_status%type := null;
  
begin

/*
 * This trigger performs the following actions:-
 *  
 * if deleted (delete_marker -> 1) then
 *   if loaded,
 *     decrement flight_status.delta_chk_bag AND raise UNLOAD alert
 *   if not loaded,
 *     decrement flight_status.delta_chk_bag AND flight_status.delta_sht_bag
 *   if in bag group
 *     decrement bag_group.no_of_bags
 * 
 * else if not deleted (delete_marker -> 0) then
 *   if loaded,
 *     increment flight_status.delta_chk_bag AND possibly clear UNLOAD alert
 *   if not loaded,
 *     increment flight_status.delta_chk_bag AND flight_status.delta_sht_bag
 * 
 * else if loaded (loaded_status -> Y) then
 *   increment flight_status.delta_ldd_bag AND
 *     decrement flight_status.delta_sht_bag AND
 *   clear any LOAD alert
 * 
 * else if unloaded (loaded_status -> !Y) then
 *   if not deleted,
 *     decrement flight_status.delta_ldd_bag AND
 *       increment flight_status.delta_sht_bag AND
 *     clear any UNLOAD alert
 *   if deleted,
 *     decrement flight_status.delta_ldd_bag AND clear any UNLOAD alert
 * 
 * else if not ATL_dcs (authority_to_load_dcs -> !Y) then
 *   if loaded,
 *     raise an UNLOAD alert
 *   if not loaded,
 *     clear a LOAD alert
 * 
 * else if ATL_dcs (authority_to_load_dcs -> Y) then
 *   if loaded,
 *     possibly clear any UNLOAD alerts
 * 
 * else if not ATL_brs (authority_to_load_brs -> !Y) then
 *   if loaded,
 *     raise an UNLOAD alert
 *   if not loaded,
 *     clear a LOAD alert
 * 
 * else if ATL_brs (authority_to_load_brs -> Y) then
 *   if loaded,
 *     possibly clear any UNLOAD alerts
 * 
 * if destination (bag_destination) has changed then
 *   if loaded,
 *     check that the new destination matches one of the ULD destinations OR 
 *     the ULD destination is "*"
 *     if not,
 *       raise an UNLOAD alert
 *     if so,
 *       possibly clear UNLOAD alert.
 * 
 */

 /* if Standby Pax */ 
  if (:old.loaded_status <> :new.loaded_status) then
  BEGIN 
  
    /* get pax_status */
  	select pax_status,flight_id
		into   l_pax_status, fid
		from   pax
		where  pax_id = :old.pax_id; 
   
    if (l_pax_status = 'S') then
       if (:new.loaded_status = 'Y') then
          /* Set Alert */
          	select count(bag_tag)
				    into   numrows
	          from   bag_alert
				    where  bag_tag = :old.bag_tag and
					         pax_id = :old.pax_id and
					         alert_on_action = 'UNLOAD';
          
				    if (numrows = 0) then
                add_bag_alert_trace_record (null, -- user id
                                            null, -- location
                                            0, -- error id
                                           :old.bag_tag,
                                           :old.bag_tag1,
                                           :old.bag_tag2a,
                                           :old.bag_tag2b,
                                           :old.bag_tag3,
                                           'ALERT',
                                           0, -- scanner_id
                                           fid,
                                           :old.pax_id,
                                           :new.uld_no);

   					insert into bag_alert (
	   				   	pax_id,
						    flight_id,
						    bag_tag,
						    bag_tag1,
						    bag_tag2a,
						    bag_tag2b,
						    bag_tag3,
						    no_warnings,
						    last_time_stamp,
						    alert_on_action
					    ) values (
						    :old.pax_id,
						    fid,
						    :old.bag_tag,
						    :old.bag_tag1,
						    :old.bag_tag2a,
						    :old.bag_tag2b,
						    :old.bag_tag3,
						    1,
						    SYSDATE + get_offset.airport_code(depair),
						   'UNLOAD'
					   );
		    		end if;
       else 
         
          /* Clear Alert */
          add_bag_alert_trace_record ( null, -- user id
                                       null, -- location
                                       0, -- error id
                                       :old.bag_tag,
                                       :old.bag_tag1,
                                       :old.bag_tag2a,
                                       :old.bag_tag2b,
                                       :old.bag_tag3,
                                       'CLR ALERT',
                                        0, -- scanner_id
                                        fid,
                                        :old.pax_id,
                                        :new.uld_no);

				delete from
					bag_alert
				where
					bag_tag = :old.bag_tag and
					pax_id = :old.pax_id and
					alert_on_action = 'LOAD';
       end if;
    end if;
  END; -- Standby Passenger Check
 end if;
  
 /*
	 * Note: The select to get the flight id from pax was moved inside the
	 * relevant blocks in the Oracle port because it caused a mutating table
	 * error previously, due to a bag update being done by the pax update
	 * trigger.
	 * In some cases the uld_flight_id has been used instead of the pax's
	 * flight ID (but only when the bag is already loaded) to get around the
	 * mutating error and because it's more efficient.
	 */
  
	if (:old.delete_marker = 0 and :new.delete_marker = 1) then

		/* get the pax's fltid */
		select
			flight_id
		into
			fid
		from
			pax
		where
			pax_id = :old.pax_id;

		if (:old.loaded_status = 'Y') then
			/* It was loaded, so alert to unload it */
			insert into fs_events
				(flight_id, delta_chk_bag)
			values
				(fid, -1);

			select
				count(bag_tag)
			into
				numrows
			from
				bag_alert
			where
				bag_tag = :old.bag_tag and
				pax_id = :old.pax_id and
				alert_on_action = 'UNLOAD';





        /* get departing airport code */
           depair := get_offset.find_airport_code(fid, :old.pax_id); 
           
        

			if (numrows = 0) then

                                add_bag_alert_trace_record (
                                        null, -- user id
                                        null, -- location
                                        0, -- error id
                                        :old.bag_tag,
                                        :old.bag_tag1,
                                        :old.bag_tag2a,
                                        :old.bag_tag2b,
                                        :old.bag_tag3,
                                        'ALERT',
                                        0, -- scanner_id
                                        fid,
                                        :old.pax_id,
                                        :new.uld_no);

				insert into bag_alert (
					pax_id,
					flight_id,
					bag_tag,
					bag_tag1,
					bag_tag2a,
					bag_tag2b,
					bag_tag3,
					no_warnings,
					last_time_stamp,
					alert_on_action)
				values
					(:old.pax_id,
					fid,
					:old.bag_tag,
					:old.bag_tag1,
					:old.bag_tag2a,
					:old.bag_tag2b,
					:old.bag_tag3,
					1,
					SYSDATE + get_offset.airport_code(depair),
					'UNLOAD');
			end if;
		else
			insert into fs_events
				( flight_id, delta_chk_bag, delta_sht_bag )
			values
				( fid, -1, -1 );

                        add_bag_alert_trace_record (
                                null, -- user id
                                null, -- location
                                0, -- error id
                                :old.bag_tag,
                                :old.bag_tag1,
                                :old.bag_tag2a,
                                :old.bag_tag2b,
                                :old.bag_tag3,
                                'CLR ALERT',
                                0, -- scanner_id
                                fid,
                                :old.pax_id,
                                :new.uld_no);

			delete from
				bag_alert
			where
				bag_tag = :old.bag_tag and
				pax_id = :old.pax_id and
				alert_on_action = 'LOAD';

		end if;

	elsif (:old.delete_marker = 1 and :new.delete_marker = 0) then

		/* bag is being un-deleted */

		/* get the pax's fltid */
		select
			flight_id
		into
			fid
		from
			pax
		where
			pax_id = :old.pax_id;

		if (:new.loaded_status = 'Y') then
			insert into fs_events
				( flight_id, delta_chk_bag )
			values
				( fid, 1 );

			check_unload_alert (
				:new.delete_marker,
				:new.authority_to_load_dcs,
				:new.authority_to_load_brs,
				:new.bag_destination,
				:new.uld_no,
				:new.uld_flight_id,
				:new.loaded_status,
				rv
			);

			if (rv = 1) then
				/* clear any UNLOAD alert */

                                add_bag_alert_trace_record (
                                        null, -- user id
                                        null, -- location
                                        0, -- error id
                                        :old.bag_tag,
                                        :old.bag_tag1,
                                        :old.bag_tag2a,
                                        :old.bag_tag2b,
                                        :old.bag_tag3,
                                        'CLR ALERT',
                                        0, -- scanner_id
                                        fid,
                                        :old.pax_id,
                                        :new.uld_no);

				delete from
					bag_alert
				where
					bag_tag = :old.bag_tag and
					pax_id = :old.pax_id and
					alert_on_action = 'UNLOAD';
			end if;

		else

			insert into fs_events
				( flight_id, delta_chk_bag, delta_sht_bag )
			values
				( fid, 1, 1 );

		end if;
		
		-- We may have tracked an unknown bag between the bag being deleted and now recreated.
                -- If there are any unknown_bags then update them.

                -- Set pax id if this bag was previously unknown (and the bag was associated with a flight)
                UPDATE unknown_bag
                   SET unknown_bag.pax_id = :new.pax_id
                 WHERE unknown_bag.pax_id = 0 AND
                       (unknown_bag.bag_tag2a = :new.bag_tag2a OR unknown_bag.bag_tag2b = :new.bag_tag2b) AND
                       unknown_bag.bag_tag3 = :new.bag_tag3 AND
                       unknown_bag.flight_id = (SELECT flight_id FROM pax WHERE pax_id = :new.pax_id);

                -- Set pax id and flight_id if this bag was previously unknown (and the bag
                -- was not associated with a flight)
                UPDATE unknown_bag
                   SET unknown_bag.pax_id = :new.pax_id,
                       unknown_bag.flight_id = (SELECT flight_id FROM pax WHERE pax_id = :new.pax_id)
                 WHERE unknown_bag.pax_id = 0 AND
                       (unknown_bag.bag_tag2a = :new.bag_tag2a OR unknown_bag.bag_tag2b = :new.bag_tag2b) AND
                        unknown_bag.bag_tag3 = :new.bag_tag3 AND
                        unknown_bag.flight_id = 0;

                if (:new.group_no is null) then
                     begin
                          select rtrim(ub.group_no)
                            into :new.group_no
                            from UNKNOWN_BAG ub, PAX p
                           where p.flight_id = ub.flight_id
                             and p.pax_id = :new.pax_id
                             and (ub.bag_tag2a = :new.bag_tag2a or ub.bag_tag2b = :new.bag_tag2b)
                             and ub.bag_tag3 = :new.bag_tag3;
                     exception
                        when NO_DATA_FOUND then
                             :new.group_no := NULL;
                     end;
                end if;

	elsif (:new.loaded_status != :old.loaded_status) then

		/* get the pax's fltid */
		select
			flight_id
		into
			fid
		from
			pax
		where
			pax_id = :old.pax_id;

		if (:new.loaded_status = 'Y') then
			/* it has been loaded */
			insert into fs_events
				( flight_id, delta_ldd_bag, delta_sht_bag )
			values
				( fid, 1, -1 );

                        add_bag_alert_trace_record (
                                null, -- user id
                                null, -- location
                                0, -- error id
                                :old.bag_tag,
                                :old.bag_tag1,
                                :old.bag_tag2a,
                                :old.bag_tag2b,
                                :old.bag_tag3,
                                'CLR ALERT',
                                0, -- scanner_id
                                fid,
                                :old.pax_id,
                                :new.uld_no);

			delete from
				bag_alert ba
			where
				ba.bag_tag = :old.bag_tag and
				ba.pax_id = :old.pax_id and
				alert_on_action = 'LOAD';

		elsif (:old.loaded_status = 'Y') then

			/* It has been unloaded */	
			if (:old.delete_marker = 0) then
				/* It was not a deleted bag */	
				insert into fs_events
					( flight_id, delta_ldd_bag, delta_sht_bag )
				values
					( fid, -1, 1 );
			else
				/* it was a deleted bag */
				insert into fs_events
					( flight_id, delta_ldd_bag )
				values
					( fid, -1 );
			end if;

                        add_bag_alert_trace_record (
                                null, -- user id
                                null, -- location
                                0, -- error id
                                :old.bag_tag,
                                :old.bag_tag1,
                                :old.bag_tag2a,
                                :old.bag_tag2b,
                                :old.bag_tag3,
                                'CLR ALERT',
                                0, -- scanner_id
                                fid,
                                :old.pax_id,
                                :new.uld_no);

			delete from
				bag_alert ba
			where
				ba.bag_tag = :old.bag_tag and
				ba.pax_id = :old.pax_id and
				alert_on_action IN ('UNLOAD', 'UNKNOWN');

		end if;

	elsif (:new.authority_to_load_dcs != :old.authority_to_load_dcs) then

		if (:new.authority_to_load_dcs = 'Y') then
			if (:old.loaded_status = 'Y') then
				check_unload_alert (
					:new.delete_marker,
					:new.authority_to_load_dcs,
					:new.authority_to_load_brs,
					:new.bag_destination,
					:new.uld_no,
					:new.uld_flight_id,
					:new.loaded_status,
					rv
				);
				-- BABRS CR 15682
				if (rv = 1 and :new.security_clearance_status <> 'N') then
					/* it was loaded before ATL went to 'N' - clear any UNLOAD alert */

                                        add_bag_alert_trace_record (
                                                null, -- user id
                                                null, -- location
                                                0, -- error id
                                                :old.bag_tag,
                                                :old.bag_tag1,
                                                :old.bag_tag2a,
                                                :old.bag_tag2b,
                                                :old.bag_tag3,
                                                'CLR ALERT',
                                                0, -- scanner_id
                                                fid,
                                                :old.pax_id,
                                                :new.uld_no);

					delete from
						bag_alert
					where
						bag_tag = :old.bag_tag and
						pax_id = :old.pax_id and
						alert_on_action = 'UNLOAD';
				end if;
			end if;
		else
			/* no longer ATL from DCS */
			if (:old.loaded_status = 'Y') then
				/* but it is loaded - alert to unload it */
				select
					count(bag_tag)
				into
					numrows
				from
					bag_alert
				where
					bag_tag = :old.bag_tag and
					pax_id = :old.pax_id and
					alert_on_action = 'UNLOAD';

				/*
				 * Can't query pax table to get pax's flight ID in this
				 * block as it would cause a mutating table error (This code
				 * is triggered by the pax update trigger!). Instead we will
				 * use the uld_flight_id which will be set when the bag is
				 * loaded - this is also more efficient..
				 */

				if (numrows = 0) then

                                        add_bag_alert_trace_record (
                                                null, -- user id
                                                null, -- location
                                                0, -- error id
                                                :old.bag_tag,
                                                :old.bag_tag1,
                                                :old.bag_tag2a,
                                                :old.bag_tag2b,
                                                :old.bag_tag3,
                                                'ALERT',
                                                0, -- scanner_id
                                                fid,
                                                :old.pax_id,
                                                :new.uld_no);

					insert into bag_alert (
						pax_id,
						flight_id,
						bag_tag,
						bag_tag1,
						bag_tag2a,
						bag_tag2b,
						bag_tag3,
						no_warnings,
						last_time_stamp,
						alert_on_action
					) values (
						:old.pax_id,
						:old.uld_flight_id,
						:old.bag_tag,
						:old.bag_tag1,
						:old.bag_tag2a,
						:old.bag_tag2b,
						:old.bag_tag3,
						1,
						SYSDATE + get_offset.airport_code(depair),
						'UNLOAD'
					);
				end if;

			else

                                add_bag_alert_trace_record (
                                        null, -- user id
                                        null, -- location
                                        0, -- error id
                                        :old.bag_tag,
                                        :old.bag_tag1,
                                        :old.bag_tag2a,
                                        :old.bag_tag2b,
                                        :old.bag_tag3,
                                        'CLR ALERT',
                                        0, -- scanner_id
                                        fid,
                                        :old.pax_id,
                                        :new.uld_no);

				delete from
					bag_alert
				where
					bag_tag = :old.bag_tag and
					pax_id = :old.pax_id and
					alert_on_action = 'LOAD';

			end if;
		end if;

	elsif (:new.authority_to_load_brs != :old.authority_to_load_brs) then

		if (:new.authority_to_load_brs = 'Y') then

			if (:old.loaded_status = 'Y') then
				check_unload_alert (
					:new.delete_marker,
					:new.authority_to_load_dcs,
					:new.authority_to_load_brs,
					:new.bag_destination,
					:new.uld_no,
					:new.uld_flight_id,
					:new.loaded_status,
					rv
				);

				-- BABRS CR 15682
				if (rv = 1 and :new.security_clearance_status <> 'N') then
					/*
					 * It was loaded before ATL went to 'N' -
					 * clear any UNLOAD alert.
					 */

                                add_bag_alert_trace_record (
                                        null, -- user id
                                        null, -- location
                                        0, -- error id
                                        :old.bag_tag,
                                        :old.bag_tag1,
                                        :old.bag_tag2a,
                                        :old.bag_tag2b,
                                        :old.bag_tag3,
                                        'CLR ALERT',
                                        0, -- scanner_id
                                        fid,
                                        :old.pax_id,
                                        :new.uld_no);

				delete from
					bag_alert
				where
					bag_tag = :old.bag_tag and
					pax_id = :old.pax_id and
					alert_on_action = 'UNLOAD';

				end if;
			end if;

		else

			/* No longer ATL from BRS. */

			if (:old.loaded_status = 'Y') then

				select
					count(bag_tag)
				into
					numrows
				from
					bag_alert
				where
					bag_tag = :old.bag_tag and
					pax_id = :old.pax_id and
					alert_on_action = 'UNLOAD';

				/*
				 * We'll use uld_flight_id here instead of finding
				 * the pax's flight ID (as per interbase s/ware) as
				 * it's more efficient.
				 */

				if (numrows = 0) then

          depair := get_offset.find_airport_code(:old.uld_flight_id,  :old.pax_id);

                                        add_bag_alert_trace_record (
                                                null, -- user id
                                                null, -- location
                                                0, -- error id
                                                :old.bag_tag,
                                                :old.bag_tag1,
                                                :old.bag_tag2a,
                                                        :old.bag_tag2b,
                                                :old.bag_tag3,
                                                'ALERT',
                                                0, -- scanner_id
                                                fid,
                                                :old.pax_id,
                                                :new.uld_no);

					insert into bag_alert (
						pax_id,
						flight_id,
						bag_tag, 
						bag_tag1, 
						bag_tag2a, 
						bag_tag2b, 
						bag_tag3,
						no_warnings, 
						last_time_stamp, 
						alert_on_action
					) values (
						:old.pax_id, 
						:old.uld_flight_id,
						:old.bag_tag, 
						:old.bag_tag1, 
						:old.bag_tag2a, 
						:old.bag_tag2b, 
						:old.bag_tag3,
						1, 
						SYSDATE + get_offset.airport_code(depair), 
						'UNLOAD'
					);


				end if;

			else 
				/*
				 * not loaded - clear any missing bag (ie LOAD) alert
				 */

                                add_bag_alert_trace_record (
                                        null, -- user id
                                        null, -- location
                                        0, -- error id
                                        :old.bag_tag,
                                        :old.bag_tag1,
                                        :old.bag_tag2a,
                                        :old.bag_tag2b,
                                        :old.bag_tag3,
                                        'CLR ALERT',
                                        0, -- scanner_id
                                        fid,
                                        :old.pax_id,
                                        :new.uld_no);

				delete from
					bag_alert
				where
					bag_tag = :old.bag_tag and
					pax_id = :old.pax_id and
					alert_on_action = 'LOAD';

			end if;
		end if;
	end if;



	/*
	 * See if the destination has changed
	 */
	if (:new.bag_destination != :old.bag_destination
		and :old.loaded_status = 'Y') then
		/*
		 * The bag dest has changed and it was loaded.
		 */

		/* Is the uld for this new destination ? */
		select
			count(uld_no)
		into
			numrows
		from
			uld_dests
		where
			uld_no = :old.uld_no and
			flight_id = :old.uld_flight_id and
			(uld_destination = :new.bag_destination or
				uld_destination = '*');

		if (numrows = 0) then
			/*
			 * The uld that the bag is loaded into is not for
			 * the new destination - alert to unload it
			 */
			select
				count(bag_tag)
			into
				numrows
			from
				bag_alert
			where
				bag_tag = :old.bag_tag and
				pax_id = :old.pax_id and
				alert_on_action = 'UNLOAD';

			/*
			 * We'll use uld_flight_id here instead of finding
			 * the pax's flight ID (as per interbase s/ware) as
			 * it's more efficient.
			 */
			if (numrows = 0) then

                                add_bag_alert_trace_record (
                                        null, -- user id
                                        null, -- location
                                        0, -- error id
                                        :old.bag_tag,
                                        :old.bag_tag1,
                                        :old.bag_tag2a,
                                        :old.bag_tag2b,
                                        :old.bag_tag3,
                                        'ALERT',
                                        0, -- scanner_id
                                        fid,
                                        :old.pax_id,
                                        :new.uld_no);

				insert into bag_alert (
					pax_id,
					flight_id,
					bag_tag, 
					bag_tag1, 
					bag_tag2a, 
					bag_tag2b, 
					bag_tag3,
					no_warnings, 
					last_time_stamp, 
					alert_on_action
				) values (
					:old.pax_id,
					:old.uld_flight_id,
					:old.bag_tag,
					:old.bag_tag1, 
					:old.bag_tag2a, 
					:old.bag_tag2b,
					:old.bag_tag3,
					1, 
					SYSDATE + get_offset.airport_code(depair), 
					'UNLOAD'
				);
			end if;

		else 

			check_unload_alert (
				:new.delete_marker,
				:new.authority_to_load_dcs, 
				:new.authority_to_load_brs, 
				:new.bag_destination,
				:new.uld_no, 
				:new.uld_flight_id,
				:new.loaded_status,
				rv
			);
			-- BABRS CR 15682
			if (rv = 1 and :new.security_clearance_status <> 'N') then
				/*
				 * Can still stay in this ULD so delete any alert.
				 */

                                add_bag_alert_trace_record (
                                        null, -- user id
                                        null, -- location
                                        0, -- error id
                                        :old.bag_tag,
                                        :old.bag_tag1,
                                        :old.bag_tag2a,
                                        :old.bag_tag2b,
                                        :old.bag_tag3,
                                        'CLR ALERT',
                                        0, -- scanner_id
                                        fid,
                                        :old.pax_id,
                                        :new.uld_no);

				delete from
					bag_alert
				where
					bag_tag = :old.bag_tag and
					pax_id = :old.pax_id and
					alert_on_action = 'UNLOAD';
			end if;

		end if;
	end if; 

	-- BABRS CR 15682
	-- IF there is a change in security clearance status on a bag to 'Cleared' and
	-- if the bag is loaded then delete the bag alerts that pass check_unload_alert.
	-- IF there is a change in security clearance status on a bag to 'Not Xray'
	-- and if the bag is loaded then raise an UNLOAD alert if one does not exist.
	IF :old.security_clearance_status <> :new.security_clearance_status THEN
		IF :new.security_clearance_status = 'C' THEN
			IF :old.loaded_status = 'Y' THEN
				-- check for existing UNLOAD alerts
     				check_unload_alert (
			        	:new.delete_marker,
        				:new.authority_to_load_dcs,
			        	:new.authority_to_load_brs,
        				:new.bag_destination,
			        	:new.uld_no,
        				:new.uld_flight_id,
			        	:new.loaded_status,
        				rv
      					);

    		IF (rv = 1) THEN

                                        add_bag_alert_trace_record (
                                                null, -- user id
                                                null, -- location
                                                0, -- error id
                                                :old.bag_tag,
                                                :old.bag_tag1,
                                                :old.bag_tag2a,
                                                :old.bag_tag2b,
                                                :old.bag_tag3,
                                                'CLR ALERT',
                                                0, -- scanner_id
                                                fid,
                                                :old.pax_id,
                                                :new.uld_no);

        	DELETE FROM
          	bag_alert
        	WHERE
          	bag_tag = :old.bag_tag and
          	pax_id = :old.pax_id and
          	alert_on_action = 'UNLOAD';
      	END IF;
			END IF;
		ELSE -- security status is 'N' for NOT XRAY
			IF :old.loaded_status = 'Y' THEN
				-- count UNLOAD alerts
			        SELECT
          				COUNT(bag_tag)
        			INTO
          				numrows
        			FROM
          				bag_alert
        			WHERE
          				bag_tag = :old.bag_tag AND
				        pax_id = :old.pax_id AND
          				alert_on_action = 'UNLOAD';

				IF numrows = 0 THEN -- create an UNLOAD alert
          depair := get_offset.find_airport_code(:old.uld_flight_id,  :old.pax_id);

                                        add_bag_alert_trace_record (
                                                null, -- user id
                                                null, -- location
                                                0, -- error id
                                                :old.bag_tag,
                                                :old.bag_tag1,
                                                :old.bag_tag2a,
                                                :old.bag_tag2b,
                                                :old.bag_tag3,
                                                'ALERT',
                                                0, -- scanner_id
                                                fid,
                                                :old.pax_id,
                                                :new.uld_no);

        	INSERT INTO bag_alert (
          	pax_id,
          	flight_id,
          	bag_tag,
          	bag_tag1,
          	bag_tag2a,
          	bag_tag2b,
          	bag_tag3,
          	no_warnings,
          	last_time_stamp,
          	alert_on_action
        	) VALUES (
          	:old.pax_id,
          	:old.uld_flight_id,
          	:old.bag_tag,
          	:old.bag_tag1,
          	:old.bag_tag2a,
          	:old.bag_tag2b,
          	:old.bag_tag3,
          	1,
          	SYSDATE + get_offset.airport_code(depair),
          	'UNLOAD'
        	);
				END IF;
			END IF;
		END IF;
	END IF;

        unauthorised_bags.maintain_unauthorised_bags
        (:old.pax_id
	,:old.bag_tag2b
  	,:old.bag_tag3
	,:new.authority_to_load_dcs
  	,:new.delete_marker
	);

	unauthorised_bags.maintain_unknown_bags
	    (:new.pax_id
	    ,:new.bag_tag2b
	    ,:new.bag_tag3
	    ,:new.authority_to_load_dcs
	    ,:new.delete_marker
	    ,:new.group_no
	    );

  	IF :old.loaded_status in ('N', 'U') and :new.loaded_status = 'Y' THEN
    		unauthorised_bags.remove_unauthorised_bag
		      (:old.pax_id
		      ,:old.bag_tag2b
		      ,:old.bag_tag3
      			);
  	END IF;

end;
/
create or replace trigger ti_bag
before insert on bag
for each row
declare
	numrows integer;
	updrows integer;
	duprows integer;
	fid number( 9 );
	t2 char(3);
	t1 char( 1 );
	dm integer;
	default_flag number := 0;
  p_flight_id  flight.flight_id%type;

	cursor tag2a_cursor( p_pax_id number, p_bag_tag3 char,
		p_bag_tag2b char) is
		select
			bag_tag1,
			delete_marker
		from
			bag
		where
			pax_id = p_pax_id and
			bag_tag3 = p_bag_tag3 and
			bag_tag2b = p_bag_tag2b;
	cursor tag2b_cursor( p_pax_id number, p_bag_tag3 char,
		p_bag_tag2a char) is
		select
			bag_tag1,
			delete_marker
		from
			bag
		where
			pax_id = p_pax_id and
			bag_tag3 = p_bag_tag3 and
			bag_tag2a = p_bag_tag2a;
	cursor tag_all_cursor( p_pax_id number, p_bag_tag3 char,
		p_bag_tag2a char, p_bag_tag2b char) is
		select
			bag_tag1,
			delete_marker
		from
			bag
		where
			pax_id = p_pax_id and
			bag_tag3 = p_bag_tag3 and
			bag_tag2a = p_bag_tag2a and
			bag_tag2b = p_bag_tag2b;
begin

    :new.bag_source_location := get_offset.find_airport_code(p_flight_id, :new.pax_id);

	/*
	 * Before each insert on the bag table :
	 * - Initialise Variables
	 * - IF it is not a alpha tag then
	 *   Open tag2a_cursor
	 *   LOOP
	 *     Fetch Value
	 *     increment numrows
	 *     increment the relevant variable
	 *   END LOOP
	 *   Close tag2a_cursor
	 * - Else if it is an alpha bag then
	 *   Open tag2b_cursor
	 *   LOOP
	 *     Fetch Value
	 *     increment numrows
	 *     increment the relevant variable
	 *   END LOOP
	 *   Close tag2b_cursor
	 * - ELSE
	 *   Open tag_all_cursor
	 *   LOOP
	 *     Fetch Value
	 *     increment numrows
	 *     increment the relevant variable
	 *   END LOOP
	 *   Close tag_all_cursor
	 * - Check for Duplicate key, and if it is a duplicate, error
	 * - Check to see if it is the same as an existing record, and if it
	 *   is, inform the user to change the record.
	 * - If it is a numeric code, then get the alpha code
	 * - If it is a alpha code, then get the numeric code
	 * - Select the flight_id from the pax table for the given pax_id.
	 * - Insert increments into fs_events table using the flight_id.
	 */
    
	
	numrows := 0;
	duprows := 0;
	updrows := 0;
  
  SELECT bag_id_seq.NEXTVAL INTO :NEW.bag_id FROM dual;
  
	if (:new.bag_tag2a = '-' or :new.bag_tag2a is null) then
		open tag2a_cursor(:new.pax_id, :new.bag_tag3, :new.bag_tag2b);
		loop
			fetch tag2a_cursor into t1, dm;
			exit when tag2a_cursor%NOTFOUND;
			numrows := numrows + 1;
			if (t1 = :new.bag_tag1 and dm = 0) then
				duprows := duprows + 1;
			elsif (t1 != :new.bag_tag1) then
				updrows := updrows + 1;
			elsif (dm = 1) then
				updrows := updrows + 1;
			end if;
		end loop;
		close tag2a_cursor;
	elsif (:new.bag_tag2b = '-' or :new.bag_tag2b is null) then
		open tag2b_cursor(:new.pax_id, :new.bag_tag3, :new.bag_tag2a);
		loop
			fetch tag2b_cursor into t1, dm;
			exit when tag2b_cursor%NOTFOUND;
			numrows := numrows + 1;
			if (t1 = :new.bag_tag1 and dm = 0) then
				duprows := duprows + 1;
			elsif (t1 != :new.bag_tag1) then
				updrows := updrows + 1;
			elsif (dm = 1) then
				updrows := updrows + 1;
			end if;
			
		end loop;
		close tag2b_cursor;
	else
		open tag_all_cursor(:new.pax_id, :new.bag_tag3, :new.bag_tag2a,
			:new.bag_tag2b);
		loop
			fetch tag_all_cursor into t1, dm;
			exit when tag_all_cursor%NOTFOUND;
			numrows := numrows + 1;
			if (t1 = :new.bag_tag1 and dm = 0) then
				duprows := duprows + 1;
			elsif (t1 != :new.bag_tag1) then
				updrows := updrows + 1;
			elsif (dm = 1) then
				updrows := updrows + 1;
			end if;
			
		end loop;
		close tag_all_cursor;
	end if;
	
	if (duprows > 0 or numrows > 1) then
		raise_application_error( -20027, 'Attempt to insert a Duplicate Value into Unique Key' );
	end if;
	if (updrows > 0) then
		raise_application_error( -20028, 'This record is really the same as an existing one, update it.');
	end if;
	
	if (:new.bag_tag2b is not null and :new.bag_tag2a is null) then

		/*
		 * We have a numeric code but no alpha code.
		 * Procedure get_alpha_code obtains the correct alpha
		 * code. If it doesn't exist it will supply a default code
		 * but this is not currently used in the system so we will
		 * retain the NULL value in this case.
		 */
		t2 := NULL;
		get_alpha_code( :new.bag_tag2b, t2, default_flag );
		if ( default_flag = 0 ) then
			:new.bag_tag2a := t2;
		end if;

	end if;

	if (:new.bag_tag2b is null and :new.bag_tag2a is not null) then

		/*
		 * We have alpha code but not numeric code.
		 * Procedure get_carrier_code obtains either the correct
		 * code or if it doesn't exist gets the default code instead.
		 */
		t2 := NULL;
		get_carrier_code( :new.bag_tag2a, t2 );
		:new.bag_tag2b := t2;

	end if;
	
	/*
	 * Get flight_id from pax table and then update fs_events.
	 */

	select
		flight_id
	into
		fid
	from
		pax
	where
		pax_id = :new.pax_id;

	if (:new.loaded_status = 'Y') then
		insert into fs_events
			(flight_id, delta_chk_bag, delta_ldd_bag)
		values
			(fid, 1, 1);
	else
		insert into fs_events
			(flight_id, delta_chk_bag, delta_sht_bag)
		values
			(fid, 1, 1);
	end if;
	
        -- Set pax id if this bag was previously unknown (and the bag was associated with a flight)

        UPDATE unknown_bag
           SET unknown_bag.pax_id = :new.pax_id
         WHERE unknown_bag.pax_id = 0 AND
              (unknown_bag.bag_tag2a = :new.bag_tag2a OR unknown_bag.bag_tag2b = :new.bag_tag2b) AND
               unknown_bag.bag_tag3 = :new.bag_tag3 AND
               unknown_bag.flight_id = (SELECT flight_id FROM pax WHERE pax_id = :new.pax_id);

        -- Set pax id and flight_id if this bag was previously unknown (and the bag 
        -- was not associated with a flight)

        UPDATE unknown_bag
           SET unknown_bag.pax_id = :new.pax_id,
               unknown_bag.flight_id = (SELECT flight_id FROM pax WHERE pax_id = :new.pax_id)
         WHERE unknown_bag.pax_id = 0 AND
              (unknown_bag.bag_tag2a = :new.bag_tag2a OR unknown_bag.bag_tag2b = :new.bag_tag2b) AND
               unknown_bag.bag_tag3 = :new.bag_tag3 AND
               unknown_bag.flight_id = 0;

        if (:new.group_no is null) then
             begin
                  select rtrim(ub.group_no)
                    into :new.group_no
                    from UNKNOWN_BAG ub, PAX p 
                   where p.flight_id = ub.flight_id
                     and p.pax_id = :new.pax_id
                     and (ub.bag_tag2a = :new.bag_tag2a or ub.bag_tag2b = :new.bag_tag2b)
                     and ub.bag_tag3 = :new.bag_tag3;
             exception
                when NO_DATA_FOUND then
                     :new.group_no := NULL;
             end;
        end if;

  unauthorised_bags.maintain_unauthorised_bags
    (:new.pax_id
    ,:new.bag_tag2b
    ,:new.bag_tag3
    ,:new.authority_to_load_dcs
    ,:new.delete_marker
    );
    
  unauthorised_bags.maintain_unknown_bags
    (:new.pax_id
    ,:new.bag_tag2b
    ,:new.bag_tag3
    ,:new.authority_to_load_dcs
    ,:new.delete_marker
    ,:new.group_no
    );

end;
/
create or replace trigger td_bag
before delete on bag
for each row
begin

	/*
	 * Before each delete on table bag :
	 * - delete from the bag_alert table where the pax_id = the old pax_id
	 *   and the bag tags are equal
	 */

        add_bag_alert_trace_record (
                null, -- user id
                null, -- location
                0, -- error id
                :old.bag_tag,
                :old.bag_tag1,
                :old.bag_tag2a,
                :old.bag_tag2b,
                :old.bag_tag3,
                'CLR ALERT',
                0, -- scanner_id
                null,
                :old.pax_id,
                :old.uld_no);

	delete from
		bag_alert
	where
		pax_id = :old.pax_id and
		bag_tag2b = :old.bag_tag2b and
		bag_tag3 = :old.bag_tag3;

        -- Decrement bag group count if bag was in group

        if (:old.group_no is not null) then

           begin

              update bag_group set no_of_bags = no_of_bags - 1 where
                 group_no = :old.group_no;

           exception
              when others then
                 null;
           end;

        end if;


end;
/
