/*****************************************************************************
*
* Filename         - $Workfile:   bag_alert_triggers.sql  $
*
*                     Name              Date      Version     CR Number
* 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_alert table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   bag_alert_triggers.sql  $  $Revision:   1.4  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/triggers/bag_alert_triggers.svl  $
* 
*    Rev 1.4   21 Jun 2001 15:03:42   tagtrak
* OR:
* CR:
* Re-baseline version 4.0.0 to include patches.
* 
*    Rev 1.3   02 Apr 1998 19:45:50   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.2   26 Feb 1998 11:52:04   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
* 
*    Rev 1.1   15 Jan 1998 13:38:46   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical issues.
* 
* 
*    Rev 1.0   22 Oct 1997 16:38:20   tagtrak
* OR:
* CR:
* REV:
* Initial check-in
*
****************************************************************************/



create or replace trigger ti_bag_alert
before insert on bag_alert
for each row

declare 
dummy           number;
l_user_id       tagtrak_users.user_id%type;
p_flight_id     flight.flight_id%type;
l_bag_tag2a     bag.bag_tag2a%type;
l_bag_tag2b     bag.bag_tag2b%type;
l_trace_created number := 0;
begin

   :new.airport_code := get_offset.find_airport_code(:new.flight_id, :new.pax_id);

	/*
	 * Trigger added by Richard Jacobs (Oracle) to overcome
	 * a constraining table error when a referential integrity
	 * constraint existed between bag and bag_alert.
	 */

	/*
	 * Check that the flight being referenced exists.
	 * Raise an exception if it doesn't.
	 */
        begin

	select
		1
	into
		dummy
	from
		flight
	where
		flight_id = :new.flight_id;

	exception
		when no_data_found then
			raise_application_error(-20029,'Invalid Flight No.');
        end;

        -- get the user_id of the 'auto' system user

        l_user_id := multi_site.get_auto_user(:new.airport_code);

        -- Now we will create a bag trace entry for this alert if it has not
        -- already been created. The alert will already have been created if
        -- if it was raised due to the update of the bag table. This is 
        -- because calling add_bag_trace_record (which selects from bag) in 
        -- this trigger would cause a mutating trigger error

        select count(*) into l_trace_created from bag_trace where
           pax_id = :new.pax_id and
           (bag_tag2a = :new.bag_tag2a OR bag_tag2b = :new.bag_tag2b) and
           bag_tag3 = :new.bag_tag3 and
           trace_action = 'ALERT' and
           bag_trace_time > (SYSDATE + get_offset.airport_code(:new.airport_code) - 2/86400);

        if l_trace_created = 0 then

            -- Now we have all the information required to create the bag trace entry

            l_bag_tag2a := :new.bag_tag2a;
            l_bag_tag2b := :new.bag_tag2b;

            add_bag_trace_record (l_user_id,
                    multi_site.get_auto_location(:new.airport_code), -- location
                    0,    -- error code
                    :new.bag_tag,
                    :new.bag_tag1,
                    l_bag_tag2a,
                    l_bag_tag2b,
                    :new.bag_tag3,
                    'ALERT',
                    0, -- scanner id
                    :new.flight_id,
                    :new.pax_id
                    );

        end if;
end;
/

create or replace trigger td_bag_alert
before delete on bag_alert
for each row
declare
l_user_id       tagtrak_users.user_id%type;
l_bag_tag2a     bag.bag_tag2a%type;
l_bag_tag2b     bag.bag_tag2b%type;
l_trace_created number := 0;
begin

        -- Create a bag trace entry for this alert

        -- get the user_id of the 'auto' system user

        l_user_id := multi_site.get_auto_user(:old.airport_code);
 
        -- already been created. The alert will already have been created if
        -- if it was raised due to the update of the bag table. This is
        -- because calling add_bag_trace_record (which selects from bag) in
        -- this trigger would cause a mutating trigger error

        select count(*) into l_trace_created from bag_trace where
           pax_id = :old.pax_id and
           (bag_tag2a = :old.bag_tag2a OR bag_tag2b = :old.bag_tag2b) and
           bag_tag3 = :old.bag_tag3 and
           trace_action = 'CLR ALERT' and
           bag_trace_time > SYSDATE + get_offset.airport_code(:old.airport_code) - 2/86400;

        if l_trace_created = 0 then

           -- Now we have all the information required to create the bag trace entry

           l_bag_tag2a := :old.bag_tag2a;
           l_bag_tag2b := :old.bag_tag2b;

           add_bag_trace_record (l_user_id,
                    multi_site.get_auto_location(:old.airport_code), -- location
                    0,    -- error code
                    :old.bag_tag,
                    :old.bag_tag1,
                    l_bag_tag2a,
                    l_bag_tag2b,
                    :old.bag_tag3,
                    'CLR ALERT',
                    0, -- scanner id
                    :old.flight_id,
                    :old.pax_id
                   );
        end if;
end;
/
