-- File renamed from hel_triggers.sql - Dave Banks - FALCP/CA/1771 - 8th March 2006


create or replace trigger ti_hel_fids_flight_info
before insert on hel_fids_flight_info
for each row
begin

	:new.timestamp := sysdate;

	if (:new.id is null or :new.id = 0 ) then
		select
			flight_info_id.nextval
		into
			:new.id
		from
			dual;
	end if;

end;
/

create or replace trigger tu_hel_fids_flight_info
before update on hel_fids_flight_info
for each row
begin

	:new.timestamp := sysdate;

end;
/



create or replace trigger tia_hel_fids_flight_info
after insert on hel_fids_flight_info
for each row
declare
	incoming hel_fids_flight_info%rowtype;

	actual_fltno flight.flight_no%type;
	actual_fltdt flight.flight_date%type;
	actual_fltid flight.flight_id%type;
	norm_flight_no flight.flight_no%type;
  depcode        site_info.airport_code%type DEFAULT NULL;
  p_interface_id  flight_info.interface_id%type;
	rc			 number;

begin

  p_interface_id := flightinfo.hel_interface;

	/*
	 * Don't create flights for ED and AD messages where the departure_station
	 * is not equal to 'HEL'
	 */
	if ((:new.last_message_type in ('ED', 'AD')) and
			(:new.departure != 'HEL')) then
				return;
	end if;

	/*
	 * If the last_message type is OFF, ATD or PRK and they don't already
	 * exist in the database, don't allow them access
	 */
	if (:new.last_message_type in ('OFF', 'ATD', 'PRK')) then
		return;
	end if;

	if (:new.last_message_type = 'PRK' and :new.arrival_departure = 'A') then
		return;
	end if;

	/*
	 * Now check for either a ZR message or a message without an indicator.
	 * This means the message was received from the FTP script, ran each
	 * night.
	 * If we receive a leg for a message that doesn't depart from HEL
	 * and we don't have that flight in the database, ignore it.
	 */
	if (:new.last_message_type = 'ZR' or
		nvl(:new.last_message_type, '   ') = '   ') then

		/*
		 * We have the message type, so check what the departure station is.
		 */
		if (:new.departure != 'HEL') then
			/*
			 * If this flight exists, this will be an onward destination, so
			 * add it, otherwise its a leg of a flight that isn't of interest
			 * to Helsinki.
			 */

			norm_flight_no := hel_fids.normalise_flight(
				:new.airline_code,
				:new.flight_no,
				:new.op_suffix
			);

			flightinfo.identify_flight (
				norm_flight_no,
				to_char(to_date(:new.flight_date, 'YYYYMMDD'),'DDMON'),
				FALSE,
				actual_fltno,
				actual_fltdt,
				actual_fltid,
				rc,
        depcode,
        p_interface_id
			);

			if (rc != 0) then
				/* Flight doesn`t exist, so don`t allow message. */
				return;
			elsif (rc = 0 and nvl(:new.last_message_type, '   ') = '   ') then
				/*
				 * Don't allow this, because Stored Proc add_hel_fids_data will
				 * cope with this.
				 */
				return;
			end if;
		end if;
	end if;

	/*
	 * Everything is OK, so allow the message.
	 */

	incoming.id                      := :new.id;
	incoming.timestamp               := :new.timestamp;
	incoming.airline_code            := :new.airline_code;
	incoming.flight_no               := :new.flight_no;
	incoming.flight_date             := :new.flight_date;
	incoming.op_suffix               := :new.op_suffix;
	incoming.departure               := :new.departure;
	incoming.arrival                 := :new.arrival;
	incoming.dept_overnight_ind      := :new.dept_overnight_ind;
	incoming.departure_time          := :new.departure_time;
	incoming.arr_overnight_ind       := :new.arr_overnight_ind;
	incoming.arrival_time            := :new.arrival_time;
	incoming.serv_type_code          := :new.serv_type_code;
	incoming.aircraft_type_code      := :new.aircraft_type_code;
	incoming.aircraft_reg            := :new.aircraft_reg;
	incoming.first_delay             := :new.first_delay;
	incoming.second_delay            := :new.second_delay;
	incoming.first_delay_time        := :new.first_delay_time;
	incoming.second_delay_time       := :new.second_delay_time;
	incoming.no_of_pax               := :new.no_of_pax;
	incoming.text_dep_ind            := :new.text_dep_ind;
	incoming.sched_dept_date         := :new.sched_dept_date;
	incoming.off_block_time          := :new.off_block_time;
	incoming.stand                   := :new.stand;
	incoming.gate1                   := :new.gate1;
	incoming.gate2                   := :new.gate2;
	incoming.actual_dept_date        := :new.actual_dept_date;
	incoming.actual_dept_time        := :new.actual_dept_time;
	incoming.sched_arr_date          := :new.sched_arr_date;
	incoming.takeoff_overnight_ind   := :new.takeoff_overnight_ind;
	incoming.takeoff_time            := :new.takeoff_time;
	incoming.off_block_overnight_ind := :new.off_block_overnight_ind;
	incoming.arrival_departure       := :new.arrival_departure;
	incoming.last_message_type       := :new.last_message_type;
 
	hel_fids.handle_flight_info( flightinfo.hel_interface, incoming);
end;
/
show errors
create or replace trigger tua_hel_fids_flight_info
after update on hel_fids_flight_info
for each row
declare
	incoming hel_fids_flight_info%rowtype;
	actual_fltno flight.flight_no%type;
	actual_fltdt flight.flight_date%type;
	actual_fltid flight.flight_id%type;
	norm_flight_no flight.flight_no%type;
	rc			 number;
  depcode        site_info.airport_code%type DEFAULT NULL;
  p_interface_id  flight_info.interface_id%type;

begin
  p_interface_id := flightinfo.hel_interface;

	/*
	 * Don't create flights for ED and AD messages where the departure_station
	 * is not equal to 'HEL'
	 */
	if ((:new.last_message_type in ('ED', 'AD')) and
			(:new.departure != 'HEL')) then
				return;
	end if;

	/*
	 * If we get a PRK message for a flight arrival, ignore this as well.
	 */
	if (:new.last_message_type = 'PRK' and :new.arrival_departure = 'A') then
		return;
	end if;

	/*
	 * If the last_message type is OFF, ATD or PRK and they don't already
	 * exist in the database, don't allow them access
	 */
	if (:new.last_message_type in ('OFF', 'ATD', 'PRK')) then

		/*
		 * Check to see if the flight already exists.  If not, don't allow it.
		 */

		norm_flight_no := hel_fids.normalise_flight(
			:new.airline_code,
			:new.flight_no,
			:new.op_suffix
		);

		flightinfo.identify_flight (
			norm_flight_no,
			to_char(to_date(:new.flight_date, 'YYYYMMDD'),'DDMON'),
			FALSE,
			actual_fltno,
			actual_fltdt,
			actual_fltid,
			rc,
      depcode,
      p_interface_id
		);

		if (rc != 0) then
			/* Flight doesn`t exist, so don`t allow message. */
			return;
		end if;
	end if;

	/*
	 * Now check for either a ZR message or a message without an indicator.
	 * This means the message was received from the FTP script, ran each
	 * night.
	 * If we receive a leg for a message that doesn't depart from HEL
	 * and we don't have that flight in the database, ignore it.
	 */
	if (:new.last_message_type = 'ZR' or
		nvl(:new.last_message_type, '   ') = '   ') then

		/*
		 * We have the message type, so check what the departure station is.
		 */
		if (:new.departure != 'HEL') then
			/*
			 * If this flight exists, this will be an onward destination, so
			 * add it, otherwise its a leg of a flight that isn't of interest
			 * to Helsinki.
			 */

			norm_flight_no := hel_fids.normalise_flight(
				:new.airline_code,
				:new.flight_no,
				:new.op_suffix
			);

			flightinfo.identify_flight (
				norm_flight_no,
				to_char(to_date(:new.flight_date, 'YYYYMMDD'),'DDMON'),
				FALSE,
				actual_fltno,
				actual_fltdt,
				actual_fltid,
				rc,
        depcode,
        p_interface_id
			);

			if (rc != 0) then
				/* Flight doesn`t exist, so don`t allow message. */
				return;
			elsif (rc = 0 and nvl(:new.last_message_type, '   ') = '   ') then
				/*
				 * Don't allow this, because Stored Proc add_hel_fids_data will
				 * cope with this.
				 */
				return;
			end if;
		end if;
	end if;

	/*
	 * Everything is OK, so add the flight data.
	 */
	incoming.id                      := :new.id;
	incoming.timestamp               := :new.timestamp;
	incoming.airline_code            := :new.airline_code;
	incoming.flight_no               := :new.flight_no;
	incoming.flight_date             := :new.flight_date;
	incoming.op_suffix               := :new.op_suffix;
	incoming.departure               := :new.departure;
	incoming.arrival                 := :new.arrival;
	incoming.dept_overnight_ind      := :new.dept_overnight_ind;
	incoming.departure_time          := :new.departure_time;
	incoming.arr_overnight_ind       := :new.arr_overnight_ind;
	incoming.arrival_time            := :new.arrival_time;
	incoming.serv_type_code          := :new.serv_type_code;
	incoming.aircraft_type_code      := :new.aircraft_type_code;
	incoming.aircraft_reg            := :new.aircraft_reg;
	incoming.first_delay             := :new.first_delay;
	incoming.second_delay            := :new.second_delay;
	incoming.first_delay_time        := :new.first_delay_time;
	incoming.second_delay_time       := :new.second_delay_time;
	incoming.no_of_pax               := :new.no_of_pax;
	incoming.text_dep_ind            := :new.text_dep_ind;
	incoming.sched_dept_date         := :new.sched_dept_date;
	incoming.off_block_time          := :new.off_block_time;
	incoming.stand                   := :new.stand;
	incoming.gate1                   := :new.gate1;
	incoming.gate2                   := :new.gate2;
	incoming.actual_dept_date        := :new.actual_dept_date;
	incoming.actual_dept_time        := :new.actual_dept_time;
	incoming.sched_arr_date          := :new.sched_arr_date;
	incoming.takeoff_overnight_ind   := :new.takeoff_overnight_ind;
	incoming.takeoff_time            := :new.takeoff_time;
	incoming.off_block_overnight_ind := :new.off_block_overnight_ind;
	incoming.arrival_departure       := :new.arrival_departure;
	incoming.last_message_type       := :new.last_message_type;
 
	hel_fids.handle_flight_info( flightinfo.hel_interface, incoming);
end;
/
