/*****************************************************************************
*
* Filename         - $Workfile:   add_flt_access2.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         - add_flt_access stored procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
PROMPT create or replace procedure add_flt_access2 

create or replace procedure add_flt_access2 
(
	flt_id     in   flight.flight_id%type
)
as

  cursor get_super_agents is
  select agent_code
    from super_agents;

	counter number;

begin

	counter := 0;

	/*
	 * We won't attempt to insert a record if an
	 * identical one exists.
	 */

  For get_super_agents_rec IN get_super_agents Loop

	select
		count(1)
	into
		counter
	from
		flight_access
	where
		flight_id  = flt_id and
		agent_code = get_super_agents_rec.agent_code;

	if ( counter = 0 ) then
		insert into flight_access
			(flight_id, agent_code )
		values
			(flt_id, get_super_agents_rec.agent_code );
	end if;
  
  counter := 0;

 End Loop;

end add_flt_access2;
/
