PROMPT CREATE OR REPLACE PACKAGE mach_fids

CREATE OR REPLACE PACKAGE mach_fids
is

	/*
	 * Accept MACH 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   mach_flight_info%rowtype
	);

end mach_fids;

/

PROMPT CREATE OR REPLACE PACKAGE BODY mach_fids

CREATE OR REPLACE PACKAGE BODY mach_fids
as

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

	norm_flight_no flight.flight_no%type;
	effective_std  mach_flight_info.schedule%type;
	std_conv       char(25);
	digits         char(10) := '0123456789';
  depcode        site_info.airport_code%type;

begin

	/*
	 * Only process outgoing flights.
	 */
	if ( incoming.direction != 'O' ) then
		return;
	end if;

	/*
	 * Convert incoming MACH data to the format
	 * required for the generic flight_info table.
	 */

	/*
	 * Work out the value to use for STD.
	 * If block off time is given, use it.
	 * otherwise use schedule if provided,
	 * or else '-' for unknown.
	 */
	if ( rtrim( incoming.blo_time ) is not null ) then
		effective_std := incoming.blo_time;
	elsif ( rtrim( incoming.schedule ) is not null ) then
		effective_std := incoming.schedule;
	else
		effective_std := '-';
	end if;

	/*
	 * Codeshare package has a convenient routine to make
	 * a flight number from mach components.
	 */
	codeshare.compose_flight_no(
		incoming.airline,
		incoming.flight_no,
		norm_flight_no
	);


	/*
	 * Make a date+time value from the text date, and STD if numeric.
	 */
	std_conv := rtrim(to_char(incoming.plan_date,'YYYYMMDD'));

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

	/*
	 * Add the generic flight_info record.
	 */
	flightinfo.incoming_record(
		norm_flight_no,
		to_date(std_conv,'YYYYMMDD HH24MI'),
		incoming.gate,
		incoming.position,
		effective_std,
		substr(incoming.blo_id,1,1),
		incoming.direction,
		interface_id,
		incoming.id,
		FALSE, 
    depcode
	);

	/*
	 * Call the generic flightinfo routine to apply the
	 * information as appropriate.
	 */
	flightinfo.maintain_flight (
		norm_flight_no,
		to_date(std_conv,'YYYYMMDD HH24MI'),
		effective_std,
		incoming.airline,
		substr(incoming.destination, 1, 3),
		'-',
		FALSE,
    depcode,
    interface_id
	);

end handle_flight_info;

end mach_fids;

/


