/*****************************************************************************
*
* Filename         - $Workfile:   get_carrier_cred.sql  $
*
*                  Name Yomi Kogbe  Date 6-Jun-2001   Version 1.0 CR Number 15432
* 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
*
*****************************************************************************/


/**************************************************
   This Function return the carrier credential  
    It uses the flight_id to lookup the
   airline alpha code credential.
************************************/


PROMPT CREATE OR REPLACE FUNCTION get_carrier_cred

CREATE OR REPLACE FUNCTION get_carrier_cred
(
  p_flight_id  in flight.flight_id%type 
)
RETURN NUMBER
IS
		 cred   carrier_lookup.pax_cred%type;
		 retval    number(10);

BEGIN

    retval := 0;

		SELECT selectee_pax
      INTO cred
			FROM carrier_lookup
     WHERE alpha_code = (SELECT SUBSTR(flight_no,1,3)
													 FROM flight
													WHERE flight_id = p_flight_id
                        );

      IF cred = 'Y' then
				 retval := 1;
      END IF;


		 RETURN retval;

EXCEPTION

	 when no_data_found then

			 RETURN retval;

   when others then

			 RETURN -502;


END;

/
