/*****************************************************************************
*
* Filename         - $Workfile:   add_pax_creds.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 Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   add_pax_creds.sql  $  $Revision:   1.3  $
*
* 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/add_pax_creds.svl  $
* 
*    Rev 1.3   21 Jun 2001 14:38:18   tagtrak
* OR:
* CR:
* Re-baseline version 4.0.0 to include patches.
* 
*    Rev 1.2   20 Sep 1999 14:40:04   tagtrak
*  
* 
*    Rev 1.1   02 Apr 1998 19:31:38   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.0   26 Feb 1998 10:18:48   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
*
****************************************************************************/
 
PROMPT create or replace procedure add_pax_creds
 
create or replace procedure add_pax_creds (
	p_fltid					in     flight.flight_id%TYPE,
	p_pax_cred         out credentials_lookup.pax_cred%TYPE
)
as
	flt_no            flight.flight_no%TYPE;
	found       			boolean := TRUE;
	airline_code			carrier_lookup.alpha_code%TYPE;

begin

	/*
	 * Get the flight_no from the flight_id
	 */
	begin
		select
			flight_no
		into
			flt_no
		from
			flight
		where
			flight_id = p_fltid;

		exception
			when NO_DATA_FOUND then
				found := false;
	end;

	if (found) then
		/*
		 * Parse the flight number to extract the airline code for the lookup
		 */

		/* Get the first 3 characters of the p_fltno variable */
		airline_code := substr(flt_no, 1, 3);

		begin
			select
				pax_cred
			into
				p_pax_cred
			from
				carrier_lookup
			where
				rtrim(alpha_code) = rtrim(airline_code);

			exception
				when NO_DATA_FOUND then
					found := FALSE;
		end;
	end if;

	if ( not found ) then
		begin
			/* Get the default entry from the db_config table */
			select
				substr(db_value, 1, 15)
			into
				p_pax_cred
			from
				db_config
			where
				db_item = 'default_gate_paxcred';

			exception
				when NO_DATA_FOUND then
					p_pax_cred := 'SEQUENCE_NO';
		end;
	end if;

end add_pax_creds;
/
