/*****************************************************************************
*
* Filename         - $Workfile:   bag_trace_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_trace table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   bag_trace_triggers.sql  $  $Revision:   1.3  $
*
* 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:   /shared/pvcs/tagtarch/db_ora/db/source/live/triggers/bag_trace_triggers.svl  $
* 
*    Rev 1.3   02 Apr 1998 19:45:52   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.2   26 Feb 1998 11:52:06   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
*
****************************************************************************/

-- File bag_trace_triggers2.sql merged into this file - Dave Banks - FALCP/CA/1771 - 8th March 2006

/*
 * Trigger ti_bag_trace will Generate a unique ID
 * for the inserted record, and will also place a
 * record in the fs_events table in the event
 * of an error condition being logged, to indicate
 * a rejected bag.
 */

create or replace trigger ti_bag_trace
before insert on bag_trace
for each row
declare
   depcode site_info.airport_code%type;

   l_fno          flight.flight_no%TYPE;
   l_date         flight.flight_date%TYPE;
   l_error        error.error_description%TYPE;
   l_loc          location.location_description%TYPE;
   l_logname      tagtrak_users.loginname%TYPE;
   l_alpha_code   carrier_lookup.alpha_code%TYPE;

begin

    :new.airport_code := multi_site.get_user_loc(:new.user_id);

	select
		bag_trace_id.nextval
	into
		:new.record_id
	from
		dual;
	
	if (:new.flight_id_scan > 0 and :new.error_id < 0) then
		insert into fs_events
			(flight_id, delta_rej_bag)
		values
			(:new.flight_id_scan, 1);
	end if;

    if :new.action_time is null then

       -- Default action time to be the same value as the bag trace time.
       -- The action time should only ever be supplied when an unknown
       -- bag not associated with a flight does become associated

       :new.action_time := :new.bag_trace_time;
    end if;

    -- Populate the extra columns required for the BAG_TRACE_DATA view

    -- Get the FLIGHT_NO and FLIGHT_DATE from FLIGHT using FLIGHT_ID_SCAN. If the 
    -- FLIGHT_ID_SCAN is 0 (unknown) or the flight cannot be found then set 
    -- them to NULL
    IF :new.flight_id_scan > 0 THEN
       BEGIN
          SELECT flight_no, flight_date INTO :new.flight_no, :new.flight_date
            FROM FLIGHT
            WHERE flight_id = :new.flight_id_scan;
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
            :new.flight_no  := NULL;
            :new.flight_date:=NULL;
       END;
    ELSE
       :new.flight_no  :=NULL;
       :new.flight_date:=NULL;
    END IF;

    -- Get the ERROR_DESCRIPTION matching ERROR_CODE from ERROR.
    -- If not found simply set it to NULL

    BEGIN
      SELECT error_description INTO :new.error_description
        FROM error
        WHERE error_id = :new.error_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        :new.error_description := NULL;
    END;

    -- Get the LOCATION_DESCRIPTION matching LOCATION_CODE from LOCATION
    -- If not found simply set it to NULL

    BEGIN
      SELECT location_description INTO :new.location_description
        FROM location
        WHERE location_code = :new.location_code;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        :new.location_description := NULL;
    END;

    -- Get the LOGINNAME matching USER_ID from TAGTRAK_USERS.
    -- If not found simply set it to NULL

    BEGIN
      SELECT loginname INTO :new.loginname
        FROM tagtrak_users
        WHERE user_id = :new.user_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        :new.loginname := NULL;
    END;

    -- Get the ALPHA_CODE from CARRIER_LOOKUP

    BEGIN
      SELECT alpha_code INTO :new.alpha_code
        FROM carrier_lookup
        WHERE (DECODE(:new.bag_tag2b,'000',NULL,:new.bag_tag2b) = bar_code OR (:new.bag_tag2b = bar_code AND alpha_code='XH'))
        AND ROWNUM = 1; -- take the first one if not unique

    EXCEPTION
      WHEN OTHERS THEN
        :new.alpha_code := NULL;
    END;

end;
/

