/*****************************************************************************
*
* Filename         - $Workfile:   get_gate_apron.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, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   get_gate_apron.sql  $  $Revision:   1.4  $
*
* 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/get_gate_apron.svl  $
* 
*    Rev 1.4   21 Jun 2001 10:06:24   tagtrak
* OR:
* CR:
* Re-baseline version 4.0.0 to include patches.
* 
* 
*    Rev 1.3   20 Sep 1999 14:41:06   tagtrak
*  
* 
*    Rev 1.2   08 Apr 1999 15:22:38   tagtrak
* OR: 
* CR: 13385
* REV:
* Modifications to provide interface for Ferranti (FASL) Flight
* Information at San Francisco.
* -------------
* Andrew Walker
* (branch build revision copied to trunk.)
* Neil Bishop
* 
*    Rev 1.1.1.0   25 Feb 1999 15:24:18   tagtrak
* OR: 
* CR: 13385
* REV:
* Modifications to provide interface for Ferranti (FASL) Flight
* Information at San Francisco.
* -------------
* Andrew Walker
* 
*    Rev 1.1   25 Sep 1998 10:03:58   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merged Vienna (0.3.1E) and Core Product (0.2.5E) -
* branch ATS revision moved to trunk.
* Andrew Walker
* 
*    Rev 1.0.1.0   18 Aug 1998 12:23:54   tagtrak
* OR: 12410
* CR: 12410
* REV:
* Corrected multiple fetch of both I and O records
* Andrew Walker
* 
*    Rev 1.0   03 Jun 1998 12:20:00   tagtrak
* Initial Check-In to PVCS
*
****************************************************************************/

PROMPT create or replace procedure get_gate_apron 

create or replace procedure get_gate_apron ( 
  flt_id_in    in          number,
  bag_tag     in          char,
  pax_seq_no  in           number,
  p_agent_code  in          char,
  gate             out      char,
  apron            out     char
)
as
num_bags  number  ;
flt_id    number  ;
  depcode site_info.airport_code%type;
begin
  depcode := multi_site.get_agent_loc(p_agent_code);


  flt_id := flt_id_in ;

  /* if flight_id  then find  gate directly */
  /* otherwise derive from bag and or pax */

  if  flt_id = 0    then   /* { */

    if  substr( bag_tag,1,1) <>  '-'    then   /* { */

     /*  Pax and  bag_tag may be unique */
     if  pax_seq_no > 0  then   /* { */

        begin
          select  count (distinct p.FLIGHT_ID )
          into    num_bags
          from    bag b,pax p
          where   b.bag_tag         = bag_tag and
                  b.pax_id          = p.pax_id and
                  p.pax_sequence_no = pax_seq_no and
                  b.bag_source_location = depcode;
          exception
                  when NO_DATA_FOUND then
                  gate := '-' ;
                  apron := '-' ;
        end ;


      if (num_bags = 1)    then   /* { */
          begin 
            select  distinct p.FLIGHT_ID 
            into    flt_id
            from    bag b,pax p
            where   b.bag_tag         = bag_tag and
                    b.pax_id          = p.pax_id and
                    p.pax_sequence_no = pax_seq_no and
                    b.bag_source_location = depcode ;
            exception
                    when NO_DATA_FOUND then
                    gate := '-' ;
                    apron := '-' ;
            end ;
      
      else 
                  gate := '-' ;
                   apron := '-' ;
      end if;  /*  } */


    /*  Bag Tag only  */
    /*  This is sufficient if only one instance of this
         bag_tag on this flight */


    else  /*   } { */
        flt_id := 0;
        begin
          select count (distinct p.flight_id)
          into num_bags
          from bag b,pax p
            where  p.pax_id = b.pax_id and 
                b.bag_tag    = bag_tag and
                b.bag_source_location = depcode ;
          exception
                 when NO_DATA_FOUND then
                 gate := '-' ;
                 apron := '-' ;
          end ;
      

        if (num_bags = 1 ) then   /* { */
            begin 
                select distinct p.FLIGHT_ID 
              into flt_id
              from bag b, pax p
              where   b.pax_id     = p.pax_id    and
                      b.bag_tag    = bag_tag and
                      b.bag_source_location = depcode ;
              exception
                      when NO_DATA_FOUND then
                      gate := '-' ;
                       apron := '-' ;
            end ;
      
        else 
                  gate := '-' ;
                   apron := '-' ;
        end if;  /*  } */
    end if;  /*  } */

  end if;  /*  } */
  end if;  /*  } */


  /*  ALL  options resolve to a unique flight id */
  if (flt_id <> 0) then   /* { */

    begin
        select i.gate, i.apron
        into  gate,  apron
        from  flight_info i, flight f
        where  f.flight_id = flt_id  and
               i.flight_no = f.flight_no and
               i.flight_date = f.flight_date and
               i.airport_code = depcode and
               i.direction = 'O';
        exception
          when NO_DATA_FOUND then
            gate := '-' ;
            apron := '-' ;
    end ;

  end if;  /*  } */


end  get_gate_apron ;
/
