/*****************************************************************************
*
* Filename         - $Workfile:   check_flight_access.sql  $
*
*                     Name Yomi Kogbe  Date 8/03/00      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 Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/


PROMPT create or replace procedure check_flight_access

create or replace procedure check_flight_access
( 
   v_flight_id  in  number
 , v_agent_id   in  char
 , v_ret_code   out number
) is

  l_num        number(10) := 0 ;
  l_flight_id  number(10);
  l_agent      char(5);     
  

cursor c1 is
  select   flight_id
         , agent_code
    from   flight_access
   where   flight_id  = v_flight_id
     and   agent_code = v_agent_id;


begin
    
    open c1;
    loop
    fetch c1 into l_flight_id, l_agent;
        if c1%found then
       
          l_num := l_num + 1;
       
         end if;
        exit when c1%notfound;
          
    End loop;
    close c1;
    
      
       if  l_num > 0  then 

        v_ret_code := 0;
        
       else   
          
          v_ret_code := -1;

        end if;

exception

    when others then
        raise_application_error(-2000, 'Error in verifying agent access to flight');

end check_flight_access;   
       
/     
      
          
