/*****************************************************************************
*
* Filename         - $Workfile:   carrier_name.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         - Stored function carrier_name
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   carrier_name.sql  $  $Revision:   1.0  $
*
* 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.
*
*
****************************************************************************/

/*
 * carrier_name
 given a flight_number wil obtain the carrier name this is required
 *   as some alpha codes have sub groups of carriers 
 *    depending on the numeric part of the flight no
 */

PROMPT create or replace function carrier_name

create or replace function carrier_name (
   flt_no in varchar2
)
   return carrier_lookup.carrier%TYPE
is

   car_name carrier_lookup.carrier%TYPE;

begin


   select carrier 
   into car_name
   from (
      select CARRIER        
      from carrier_subgroup 
      where ALPHA_CODE = substr(flt_no,1,3) 
            and MIN_FLIGHT_NO <= to_number(substr(flt_no,4,4))
            and MAX_FLIGHT_NO >= to_number(substr(flt_no,4,4))
      union
      select CARRIER
      from carrier_lookup 
      where ALPHA_CODE = substr(flt_no,1,3)  and
      not exists 
         (
         select 1 from carrier_subgroup
            where ALPHA_CODE = substr(flt_no,1,3)
            and MIN_FLIGHT_NO <= to_number(substr(flt_no,4,4))
            and MAX_FLIGHT_NO >= to_number(substr(flt_no,4,4))
         )
      ) ;



   return car_name;

end carrier_name;
/
