PROMPT CREATE OR REPLACE PACKAGE hel_fids

CREATE OR REPLACE PACKAGE hel_fids
is

	/*
	 * Accept Helsinki FIDS data, convert it to the generic format
	 * and store it in the flight_info table, and then call the
	 * generic maintain_flight procedure.
	 */
	procedure handle_flight_info (
		interface_id   in   sys_interface.id%type,
		incoming       in   hel_fids_flight_info%rowtype
	);

	/*
	 * Perform any required actions to 'normalize' the flight
	 * number so it is compatible with those used in the
	 * flight table.
	 */
	function normalise_flight (
		p_hel_flight_carrier in hel_fids_flight_info.airline_code%type,
		p_hel_flight_number  in hel_fids_flight_info.flight_no%type,
		p_hel_op_suffix      in hel_fids_flight_info.op_suffix%type
	) return
		flight.flight_no%type;

	/*
	 * Get departure time will accept an in_time as a parameter and convert
	 * this to a departure time for flight_info.
	 */
	procedure get_departure_time (
		in_time					in	hel_fids_flight_info.departure_time%type,
		in_flight_date	in	flight_info.flight_date%type,
		departure_time	out	flight_info.departure_time%type
	);

end hel_fids;

/

PROMPT CREATE OR REPLACE PACKAGE BODY hel_fids

CREATE OR REPLACE PACKAGE BODY hel_fids
as

function normalise_flight (
	p_hel_flight_carrier in hel_fids_flight_info.airline_code%type,
	p_hel_flight_number  in hel_fids_flight_info.flight_no%type,
	p_hel_op_suffix      in hel_fids_flight_info.op_suffix%type
) return
	flight.flight_no%type
is
	result flight.flight_no%type;
begin

	/*
	 * FASL pad the carrier to 3 chars and add leading zeroes to
	 * the number. Ditch the suffix if it is a blank.
	 */
	result := rpad( p_hel_flight_carrier, 3, ' ') || lpad(ltrim(rtrim(p_hel_flight_number)), 4, '0') || rtrim(p_hel_op_suffix);

	return result;

end normalise_flight;

procedure handle_flight_info (
	interface_id   in   sys_interface.id%type,
	incoming       in   hel_fids_flight_info%rowtype
)
is

	norm_flight_no flight.flight_no%type;
	std_conv       char(25);
	departure_time date;
	generic_dir    flight_info.direction%type;
	effective_std  flight.flight_sch_time%type;
	force_mapping  boolean;
	apron          flight_info.apron%type;
	block_off			 flight_info.block_off%type;
  depcode        site_info.airport_code%type DEFAULT NULL;

begin
  depcode := trim (config.get_value ('FIDS_IF_' || interface_id));

/*
	if ( config.get_value( 'FORCE_OPDB_MAPPING' ) = 'Y' ) then
		force_mapping := TRUE;
	else
		force_mapping := FALSE;
	end if;
*/
	force_mapping := FALSE;

	apron := NULL;

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

	if (incoming.arrival_departure = 'D') then
		generic_dir := 'O';
	elsif (incoming.arrival_departure = 'A') then
		generic_dir := 'I';
	else
		generic_dir := NULL;
	end if;

	/*
	 * Work out the off-block character.
	 */
	if (ltrim(rtrim(incoming.last_message_type)) = 'ATD') then
		block_off     := 'A';
		effective_std := rtrim(incoming.actual_dept_time);
		get_departure_time (
			incoming.actual_dept_time,
			incoming.flight_date,
			departure_time
		);
	elsif (ltrim(rtrim(incoming.last_message_type)) = 'AD') then
		if (incoming.actual_dept_time != '    ') then
			/* We have a actual_dept_time, so use it */
			block_off     := 'A';
			effective_std := rtrim(incoming.actual_dept_time);
			get_departure_time (
				incoming.actual_dept_time,
				incoming.flight_date,
				departure_time
			);
		elsif (incoming.off_block_time != '    ') then
			/* We have an off-block time, so use that */
			block_off     := 'B';
			effective_std := rtrim(incoming.off_block_time);
			get_departure_time (
				incoming.off_block_time,
				incoming.flight_date,
				departure_time
			);
		else
			return;
		end if;
	elsif (ltrim(rtrim(incoming.last_message_type)) = 'OFF') then
		block_off     := 'B';
		effective_std := rtrim(incoming.off_block_time);
		get_departure_time (
			incoming.off_block_time,
			incoming.flight_date,
			departure_time
		);
	elsif (ltrim(rtrim(incoming.last_message_type)) = 'PRK') then
		block_off     := NULL;
		/*
		 * We don't know what the departure time is, so use the actual_dept_time
		 * if supplied, otherwise, the departure_time
		 */
		effective_std := nvl(rtrim(incoming.actual_dept_time), rtrim(incoming.departure_time));
		get_departure_time (
			nvl(incoming.actual_dept_time, incoming.departure_time),
			incoming.flight_date,
			departure_time
		);
	else
		block_off     := NULL;
		effective_std := rtrim(incoming.departure_time);
		get_departure_time (
			incoming.departure_time,
			incoming.flight_date,
			departure_time
		);
	end if;

	/*
	 * Add the generic flight_info record.
	 */
	flightinfo.incoming_record (
		norm_flight_no,
		departure_time,
		incoming.gate1,
		incoming.stand,
		effective_std,
		block_off,
		generic_dir,
		interface_id,
		incoming.id,
		force_mapping,
    depcode
	);

	/*
	 * Call the generic flightinfo routine to apply the
	 * information as appropriate.
	 */
	flightinfo.maintain_flight (
		norm_flight_no,
		departure_time,
		effective_std,
		incoming.airline_code,
		incoming.arrival,
		'-',
		force_mapping,
    depcode,
    interface_id
	);

end handle_flight_info;

/*
 * Get departure time will accept an in_time as a parameter and convert
 * this to a departure time for flight_info.
 */
procedure get_departure_time (
	in_time					in	hel_fids_flight_info.departure_time%type,
	in_flight_date	in	flight_info.flight_date%type,
	departure_time	out	flight_info.departure_time%type
)
is
	digits					char(10) := '0123456789';
	temp_dept_time	char(25);
begin
	/*
	 * Produce a date/time value from the text flight date and
	 * STD if this is numeric.
	 */
	temp_dept_time := in_flight_date;

	if ( instr(digits,substr(in_time,1,1)) != 0 and
		instr(digits,substr(in_time,2,1)) != 0 and
		instr(digits,substr(in_time,3,1)) != 0 and
		instr(digits,substr(in_time,4,1)) != 0
	) then
		temp_dept_time := rtrim(temp_dept_time) || ' ' || rtrim(in_time);
	else
		temp_dept_time := rtrim(temp_dept_time) || ' 0000';
	end if;

	departure_time := to_date( temp_dept_time, 'YYYYMMDD HH24MI' );

end get_departure_time;

end hel_fids;

/


