/*****************************************************************************
*
* Filename         - $Workfile:   flight_id2.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 flight_id2 

create or replace procedure flight_id2 (
   fltno         in   char,
   fltdate       in   char,
   psiteid       in   number,
   fltid         out  number,
   pflight_type  in   flight.flight_type%TYPE DEFAULT 'O') 
as 

   counter number(10);

   cursor c1 is
      select
         flight_id 
      from
         flight
      where
         flight_no =  fltno
     and
      site_id   =  psiteid
     and
      flight_type = pflight_type;

begin

   /*
    * - Initialise variable
    * - IF fltdate is not equal to '-' (ie. we have a date) then
    *   Select flight_id from the flight table where the flight number is
    *   equal to the fltno parameter and the flight_date is equal to the
    *   fltdate parameter.  If no data is found, then fltid = -1.
    * - else
    *   Initialise counter
    *   Loop around cursor
    *     Increment counter
    *     fltid = cursor flight_id
    *   end loop
    *   if counter = 0 then
    *     fltid = -1
    *   else
    *     if counter is greater than 1 then
    *       fltid = -2
    */

   fltid := -1;
 
   if (fltdate != '-') then 
  begin
    select
         flight_id
    into
         fltid
    from
         flight
      where
         flight_no   =  fltno and
         flight_date =  fltdate and
         site_id     =  psiteid and
         flight_type =  pflight_type;
    exception
      when NO_DATA_FOUND then
        fltid := -1;
      end;

   else 
      counter := 0;
 
      /* For Statement */
      for c1_rec in c1 loop 
         counter := counter + 1;
         fltid   := c1_rec.flight_id;
      end loop;

      if (counter = 0) then 
         fltid := -1;
      else 
         if (counter > 1) then 
            fltid := -2;
         end if;
      end if;
   end if;

end flight_id2;
/
