/*****************************************************************************
*
* Filename         - $Workfile:   get_pax_cred.sql  $
*
*                   Name							Date				CR Number		CR Description
*										Yomi Kogbe				06/06/01		15435
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle PL/SQL
*
* Classification   - Commercial In Confidence
*
* (c) Ultra Electronics Limited 2001
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   get_pax_cred.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.
*
* $Log:   /pvcsarch/db_ora/db/source/live/procedures/get_pax_cred.svl  $
* 
*    Rev 1.0   18 Jun 2001 11:45:20   tagtrak
* OR:
* CR: 15435
* REV: 1.0
* DESC: User Group Changes 2001
*
*****************************************************************************/

/****************************************************************************
*
* Function				- get_pax_cred
*
* Description			- This Function return the pax credential for carriers.
*										It uses the flight_id to lookup the airline alpha code
*										credential.
*
*****************************************************************************/
PROMPT CREATE OR REPLACE FUNCTION get_pax_cred

CREATE OR REPLACE FUNCTION
get_pax_cred
(
	fid  IN flight.flight_id%type
)
RETURN NUMBER IS

	cred   carrier_lookup.pax_cred%type;
	ret    number(10);

BEGIN
	SELECT
		pax_cred
	INTO
		cred
	FROM
		carrier_lookup
	WHERE
		alpha_code =
		(
			SELECT
				substr(flight_no,1,3)
			FROM
				flight
			WHERE
				flight_id = fid
		);

	IF cred = 'SEQUENCE_NO' THEN
		ret := 1;
	END IF;

	IF cred = 'SECURITY_NO' THEN
		ret := 2;
	END IF;

	RETURN ret;

EXCEPTION

	 WHEN no_data_found THEN
			 RETURN -502;

   WHEN OTHERS THEN
			 RETURN -502;

END;
/
