/*****************************************************************************
*
* Filename         - $Workfile:   grant_flt_access.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         - grant_flt_access stored procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   grant_flt_access.sql  $  $Revision:   1.2  $
*
* 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:   /pvcsarch/db_ora/db/source/live/procedures/grant_flt_access.svl  $
* 
*    Rev 1.2   20 Sep 1999 14:41:24   tagtrak
*  
* 
*    Rev 1.1   02 Apr 1998 19:36:34   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.0   26 Feb 1998 10:21:50   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
*
****************************************************************************/

/*
 * grant_flt_access - add access records for a given privilege record.
 * This procedure will select flights appropriately depending upon the
 * specified privilege record, and add flight_access and stats_access
 * records for them.
 */
PROMPT create or replace procedure grant_flt_access
	
create or replace procedure grant_flt_access (
	p_id          in  flight_privileges.id%type,
	p_alpha       in  flight_privileges.airline_alpha_code%type,
	p_fno         in  flight_privileges.flight_no%type,
	p_fdate       in  flight_privileges.flight_date%type,
	p_agent       in  flight_privileges.agent_code%type,
	p_orig_agent  in  flight_privileges.originating_agent%type
)
as

	counter number;
	fltid	  flight.flight_id%type;
	fltno   flight.flight_no%type;
	fltdt   flight.flight_date%type;

	type fid_curs_type is ref cursor;
	flight_cur fid_curs_type;

begin

	/*
	 * Open a cursor to return the flights which are relevant
	 * to this flight_privilege record.
	 */

	if ( p_fno is not null and p_fdate is not null ) then
		
		/*
		 * 1. If a flight number and date are specified, the
		 *    flight must match both. (plus the airline code)
		 */
		open flight_cur for
			select
				flight_id,
				flight_no,
				flight_date
			from
				flight
			where
				rtrim(carrier_alpha_code) = rtrim(p_alpha) and
				flight_no = p_fno and
				flight_date = p_fdate;
				
	elsif ( p_fno is not null ) then

		/*
		 * 2. If flight number only is specified, the
		 *    flight must match that. (plus the airline code)
		 */
		open flight_cur for
			select
				flight_id,
				flight_no,
				flight_date
			from
				flight
			where
				rtrim(carrier_alpha_code) = rtrim(p_alpha) and
				flight_no = p_fno;

	elsif ( p_fdate is not null ) then

		/*
		 * 3. If flight date only is specified, the
		 *    flight must match that. (plus the airline code)
		 */
		open flight_cur for
			select
				flight_id,
				flight_no,
				flight_date
			from
				flight
			where
				rtrim(carrier_alpha_code) = rtrim(p_alpha) and
				flight_date = p_fdate;

	else

		/*
		 * 4. If neither flight number nor date are specified
		 *    flight need only match the airline code.
		 */
		open flight_cur for
			select
				flight_id,
				flight_no,
				flight_date
			from
				flight
			where
				rtrim(carrier_alpha_code) = rtrim(p_alpha);

	end if;
		

	/*
	 * For each selected flight, add flight_access and
	 * stats_access records.
	 */
	loop

		fetch
			flight_cur
		into
			fltid,
			fltno,
			fltdt;

		exit when flight_cur%NOTFOUND;

		add_flt_access( fltid, p_agent, p_id );

		add_stats_access( p_orig_agent, p_agent, fltno, fltdt );

	end loop;


end grant_flt_access;
/
