/*****************************************************************************
*
* Filename         - $Workfile:   get_pax_details.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:   get_pax_details.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_pax_details.svl  $
* 
*    Rev 1.4   20 Sep 1999 14:41:14   tagtrak
*  
* 
*    Rev 1.3   25 Sep 1998 10:04:12   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.2.2.0   18 Aug 1998 09:35:42   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merging Vienna (0.3.1E) and Core Product (0.2.5E).
* Andrew Walker
* 
*    Rev 1.2.1.0   04 Jun 1998 15:31:24   tagtrak
* OR:
* CR: 12098
* REV:
* Vienna Modifications
* Andrew Walker
* 
*    Rev 1.2   02 Apr 1998 19:36:06   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.1   10 Mar 1998 15:42:10   tagtrak
* OR:
* CR:
* REV:
* The pax sequence number where the credential used
* is a sequence number could thought that a sequence 
* number of 045 was differnet to a sequence number of 45.
* It now treats them as the same PAX. Neil Bishop.
* 
*    Rev 1.0   26 Feb 1998 10:20:48   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
*
****************************************************************************/
 
PROMPT create or replace procedure get_pax_details
 
create or replace procedure get_pax_details (
	p_flight_id				in     flight.flight_id%TYPE,
	p_pax_qualifier		in     credentials_lookup.pax_cred%TYPE,
	p_pax_credential	in     char,
	codeshare_fid     in     number,
	p_pax_id 					   out pax.pax_id%TYPE,
	p_pax_surname        out pax.pax_surname%TYPE,
	p_retcode            out number
)
as
begin
	/* Initialise p_retcode */
	p_retcode := 0;

	/* Check what credential is passed in and then perform the relevant select */
	if (upper(rtrim(p_pax_qualifier)) = 'SEQUENCE_NO') then
		begin
			select
				pax_id,
				pax_surname
			into
				p_pax_id,
				p_pax_surname
			from
				pax
			where
				flight_id       = p_flight_id and
				pax_sequence_no = TO_NUMBER(p_pax_credential) and
				(codeshare_fid = 0 or codeshare_fid = booked_flight_id );

			exception
				when NO_DATA_FOUND then
					/* NO_PAX_RECORD_FOUND */
					p_retcode := -601;
				when TOO_MANY_ROWS then
					if ( codeshare_fid = 0 ) then
						p_retcode := -1039; /* Duplicate Pax sequence No. */
					else
						p_pax_id := -602; /* Too Many Pax ! */
					end if;
		end;
	elsif (upper(rtrim(p_pax_qualifier)) = 'SECURITY_NO') then
		begin
			select
				pax_id,
				pax_surname
			into
				p_pax_id,
				p_pax_surname
			from
				pax
			where
				flight_id       = p_flight_id and
				pax_security_no = p_pax_credential and
				(codeshare_fid = 0 or codeshare_fid = booked_flight_id );

			exception
				when NO_DATA_FOUND then
					/* NO_PAX_RECORD_FOUND */
					p_retcode := -601;
				when TOO_MANY_ROWS then
					if ( codeshare_fid = 0 ) then
						p_retcode := -1039; /* Duplicate Pax sequence No. */
					else
						p_pax_id := -602; /* Too Many Pax ! */
					end if;
		end;
	else
		begin
			select
				pax_id,
				pax_surname
			into
				p_pax_id,
				p_pax_surname
			from
				pax
			where
				flight_id   = p_flight_id and
				pax_seat_no = p_pax_credential and
				(codeshare_fid = 0 or codeshare_fid = booked_flight_id );

			exception
				when NO_DATA_FOUND then
					/* NO_PAX_RECORD_FOUND */
					p_retcode := -601;
				when TOO_MANY_ROWS then
					if ( codeshare_fid = 0 ) then
						p_retcode := -1039; /* Duplicate Pax sequence No. */
					else
						p_pax_id := -602; /* Too Many Pax ! */
					end if;
		end;
	end if;

end get_pax_details;
/
