/*****************************************************************************
*
* Filename         - $Workfile:   get_pax_data.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 Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   get_pax_data.sql  $  $Revision:   1.8  $
*
* 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:   /shared/pvcs/tagtarch/db_ora/db/source/live/procedures/get_pax_data.sqv  $
* 
*    Rev 1.8   25 Sep 1998 10:04:04   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.7.3.0   18 Aug 1998 09:35:36   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merging Vienna (0.3.1E) and Core Product (0.2.5E).
* Andrew Walker
* 
*    Rev 1.7.1.0   04 Jun 1998 15:32:34   tagtrak
* OR:
* CR: 12098
* REV:
* Vienna Modifications
* Andrew Walker
* 
*    Rev 1.7   02 Apr 1998 19:36:04   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.6   15 Jan 1998 12:12:58   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.5   10 Nov 1997 13:55:36   tagtrak
* OR:
* CR:
* REV:Seat now set to '-' if null. Nick. 10/11/97.
* 
*    Rev 1.4   24 Oct 1997 11:12:08   tagtrak
* Added PVCS header information
*
****************************************************************************/

PROMPT create or replace procedure get_pax_data

create or replace procedure get_pax_data (
	ipid                  in     number,
	fid                   in     number,
	sno                   in     number,
	codeshare_fid         in     number,
	init                  out    char,
	surn                  out    char,
	dest                  out    char,
	cls                   out    char,
	sts                   out    char,
	del                   out    number,
	seat                  out    char,
	ctime                 out    char,
	nbags                 out    number,
	npax                  out    number,
	seq                   in out number,
	pid                   out    number,
	fltid                 out    number,
	p_flight_no_in        OUT    CHAR,
	p_airport_code_in     OUT    CHAR,
	p_airport_security_in OUT    CHAR
) 
as

	seat_cpy  pax.pax_seat_no%type;
  
begin

	/*
	 * - IF the ipid parameter is equal to 0 then
	 *   select relevant columns from the pax table where the flight_id
	 *   is equal to the fid parameter and the pax_sequence_no is equal
	 *   to the sno parameter.
	 * - ELSE
	 *   select relevant columns from the pax table where the pax_id
	 *   is equal to the ipid parameter.
	 * - IF seat_cpy is NULL then
	 *   Assign '-' to the seat variable
	 * - ELSE
	 *   Assign the contents of the seat_cpy variable to seat
	 * - IF no data found then
	 *   reset all of the variables to indicate NULL values.
	 */

	/*  SS1062 BARNZ Hold Baggage Screening:                             *
	 *  For transfer bags, the Pax/Bag Search report will also show the  *
	 *  inbound flight number, incoming airport_code and airport         *
	 *  security approval.  The pax inbound columns should always be     *
	 *  populated for transfer passengers.                               */


	if (ipid = 0) then
		select
			pax_initials,
			pax_surname,
			pax_destination,
			pax_class,
			pax_status,
			inbound_flight_no,
			inbound_boarding_point,
			delete_marker,
			pax_seat_no,
			TO_CHAR(time_pax_stored, 'HH24:MI'),
			no_of_bags,
			no_same_surname,
			TO_NUMBER(pax_dcs_sequence_no, '999'),
			pax_id,
			flight_id 
		into
			init,
			surn,
			dest,
			cls,
			sts,
			p_flight_no_in,
			p_airport_code_in,
			del,
			seat_cpy,
			ctime,
			nbags,
			npax,
			seq,
			pid,
			fltid 
		from
			pax
		where
			flight_id       = fid and
			pax_sequence_no = sno and
			(codeshare_fid = 0 or booked_flight_id = codeshare_fid);

	else 

		select
			pax_initials,
			pax_surname,
			pax_destination,
			pax_class,
			pax_status,
			inbound_flight_no,
			inbound_boarding_point,
			delete_marker,
			pax_seat_no,
			TO_CHAR(time_pax_stored, 'HH24:MI'),
			no_of_bags,
			no_same_surname,
			TO_NUMBER(pax_dcs_sequence_no, '999'),
			pax_id,
			flight_id 
		into
			init,
			surn,
			dest,
			cls,
			sts,
			p_flight_no_in,
			p_airport_code_in,
			del,
			seat_cpy,
			ctime,
			nbags,
			npax,
			seq,
			pid,
			fltid 
		from
			pax
		where
			pax_id = ipid;

	end if;

	if (seq IS NULL) then
		seq := 0;
	end if;

	if (seat_cpy IS NULL) then
		seat := '-';
	else
		seat := seat_cpy;
	end if;

	IF p_airport_code_in IS NOT NULL THEN

		BEGIN
			SELECT approved_security_status
			INTO   p_airport_security_in
			FROM   site_info
			WHERE  airport_code = p_airport_code_in;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				p_airport_security_in := 'Y';           /* BARNZ RQ 8.10 */
		END;
        ELSE

                p_airport_security_in := 'N';                           /* BARNZ RQ 8.9 and RQ 8.10 */

	END IF;

	exception
		when NO_DATA_FOUND then
			init                  := '-';
			surn                  := '-';
			dest                  := '-';
			cls                   := '-';
			sts                   := '-';
			del                   := 0;
			seat                  := '';
			ctime                 := '-';
			nbags                 := 0;
			npax                  := 0;
			seq                   := 0;
			pid                   := 0;
			fltid                 := 0;
			p_flight_no_in        := '-';
			p_airport_code_in     := '-';
			p_airport_security_in := '-';

		when TOO_MANY_ROWS then
			init                  := '-';
			surn                  := '-';
			dest                  := '-';
			cls                   := '-';
			sts                   := '-';
			del                   := 0;
			seat                  := '';
			ctime                 := '-';
			nbags                 := 0;
			npax                  := 0;
			seq                   := 0;
			pid                   := -1039;
			fltid                 := 0;
			p_flight_no_in        := '-';
			p_airport_code_in     := '-';
			p_airport_security_in := '-';
end get_pax_data;
/
