/*****************************************************************************
*
* Filename         - $Workfile:   get_pax_bags.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, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   get_pax_bags.sql  $  $Revision:   1.5  $
*
* 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_bags.sqv  $
* 
*    Rev 1.5   16 Mar 1999 10:24:04   tagtrak
* OR:
* CR: 13686
* REV:
* Changes made as a result of System Testing.
* 
* Neil Bishop.
* 
*    Rev 1.4   02 Apr 1998 19:36:02   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.3   15 Jan 1998 12:12:56   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.2   24 Oct 1997 11:11:52   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT create or replace package get_pax_bags_pkg

create or replace package get_pax_bags_pkg
as

	/*
	 * - Define the records and procedures to be used
	 */

	type cur_return_type is record (
		tno                        char(10),
		atld                       char(1),
		atlb                       char(1),
		bsi                        char(1),
		lsts                       char(1),
		del                        integer,
		uno                        char(10),
		ltime                      char(5),
		bseq                       smallint,
		sc_status                  char(1),
		sc_user_id                 integer,              
		sc_loc                     char(6),
		sc_time                    char(15), /* LENGTH(TO_CHAR(SC_TIME,'DD-MON-YY HH:MI')) */
                bag_excep                  char(4)
	);

	type cur_type is ref cursor return cur_return_type;

	procedure get_pax_bags (
		pid in           number,
		cursor_in in out cur_type
	);

end get_pax_bags_pkg;
/

PROMPT create or replace package body get_pax_bags_pkg

create or replace package body get_pax_bags_pkg
as 
	procedure get_pax_bags (
		pid       in     number,
		cursor_in in out cur_type
	)
	is

	begin

		/*
		 * - Open the cursor and select the relevant columns from the bag
		 *   table where the pax_id is equal to the pid parameter.
		 */


		/*
		 * Changed the cursor below to convert the time loaded into
		 * the format HH:MI.
		 * Neil Bishop.  09/03/99.  CR 13686.
		 */
		open cursor_in for
			select
				bag_tag,
				authority_to_load_dcs,
				authority_to_load_brs,
				bag_source_indicator,
				loaded_status,
				delete_marker,
				uld_no,
				TO_CHAR(time_loaded, 'HH24:MI'),
				bag_sequence_no,
				security_clearance_status,
				sc_user_id,              
				sc_location,
				TO_CHAR(sc_time,'DD.MM.YY HH24:MI'),
                                bag_exception
			from
				bag
			where
				pax_id = pid;
	end get_pax_bags;
end get_pax_bags_pkg;
/
