/*****************************************************************************
*
* Filename         - $Workfile:   modify_flight_dests.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 Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

PROMPT create or replace procedure modify_flight_dests

create or replace procedure modify_flight_dests (
	fid  in out  number,
	dest in out  char
)
as

	got_dest  number(6);
	max_order number(6);
	tdest     char(3);
	tsrc      char(1);

	cursor c1 is
		select
			flight_destination,
			dest_source,
			dest_order
		from
			flight_dests
		where
			flight_id = fid
		order by
			dest_source desc,
			dest_order  asc;

begin

	/*
	 * - Initialise the variables
	 * - Loop around the cursor
	 *   Assign variables to the contents of the cursor variables
	 *   If the destinations are the same then
	 *     Assign 1 to the got_dest variable.
	 * - End Loop
	 * - If got_dest = 0 (ie. destination not found) then
	 *   If tsrc is equal to 'F' then
	 *     Assign 1 to max_order
	 *   ELSE
	 *     Increment max_order
	 *   Insert a record into the flight_dests table
	 */

	/* this procedure will be called if a BSM requires it
	   it will not be used for FOM */

	/* the idea is that we get the data returned in the order;
		MAN 1 F
		LHR 2 F
		GLA 3 F
		HKG 1 B
		CDG 2 B  etc

		If the dest is matched from the FOM (F) return,
		OR if the dest is matched from the BSM (B) return
		OR if the dest is not matched add it as the next
		"in order" BSM record */

	/* if we had no dests of type B then start at 1,
		else increment order */
	got_dest  := 0;
	max_order := 0;
	tdest     := 'F';

	/* For Statement */
	for c1_rec in c1 loop
		/*
		 * The original logic depended upon these variables being set by
		 * the each fetch from the cursor. This should emulate that effect.
		 */
		tdest     := c1_rec.flight_destination;
		tsrc      := c1_rec.dest_source;
		max_order := c1_rec.dest_order;

		if (dest = c1_rec.flight_destination) then
			got_dest := 1;
		end if;
	end loop;

	if (got_dest = 0) then

  	max_order := max_order + 1;

		insert into
			flight_dests (
				flight_id,
				flight_destination,
				dest_source,
				dest_order
			) values (
				fid,
				dest,
				'B',
				max_order
			);

	end if;

end modify_flight_dests;
/
