/*****************************************************************************
*
* Filename         - $Workfile:   get_bag_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 Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   get_bag_data.sql  $  $Revision:   1.7  $
*
* 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_bag_data.sqv  $
* 
*    Rev 1.7   16 Oct 2001 09:14:32   tagtrak
* OR:15514
* CR:15514
* REV:
* DESC: BAG SEARCH FIX
* 
*    Rev 1.6   20 Sep 1999 14:40:56   tagtrak
*  
* 
*    Rev 1.5   04 Nov 1998 16:25:48   tagtrak
* OR: 12686
* CR: 12686
* REV:
* Show ULD hold position on bag_search report
* Andrew Walker
* (branch ATS revision copied to trunk.)
* Andrew Walker
* 
*    Rev 1.4.1.0   22 Oct 1998 14:04:30   tagtrak
* OR: 12686
* CR: 12686
* REV:
* Show ULD hold position on bag_search report
* Andrew Walker
* 
*    Rev 1.4   02 Apr 1998 19:34:48   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.3   15 Jan 1998 12:12:30   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:08:30   tagtrak
* Added PVCS header information
*
****************************************************************************/
PROMPT create or replace package get_bag_data_pack

create or replace package get_bag_data_pack
as

	/*
	 * - Declare record types and procedures
	 */

	type GetBagDataRecType 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,
		stime       char(5),
		paxid       integer,
		uld_pos     char(6),
		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 GetBagDataCurType is ref cursor return GetBagDataRecType;

	procedure get_bag_data (
		fid           in     number,
		pid           in     number,
		tag           in     char,
    userid       in     number,
		GetBagData_cv in out GetBagDataCurType
	);

end get_bag_data_pack;
/


PROMPT create or replace package body get_bag_data_pack

create or replace package body get_bag_data_pack
as
	procedure get_bag_data 
  (
		fid           in     number,
		pid           in     number,
		tag           in     char,
    userid        in     number, 
		GetBagData_cv in out GetBagDataCurType
	)
	is
	
  	v_mode     smallint;
    tag3       bag.bag_tag3%type;
    tag2b      bag.bag_tag2b%type;
    depcode site_info.airport_code%type;

		begin
    
    depcode := multi_site.get_user_loc(userid);
      

      /************************************************************* 
      *  Fix for bag_search timeout problem in sfo
      *  fetching for the select statement below is taking         
      *  25 seconds on average to return because of the like     
      *  operator. Use tag3 and tag2b instead takes 2 sec on average     
      *
      **************************************************************/
      
      tag3  := to_number(rtrim(substr(tag, rtrim(length(tag))-5)));
      tag2b := rtrim(substr(tag, rtrim(length(tag))-8,3));

      

      

			/*
			 * - Initialise Variable
			 * - IF flight_id and pax id are both equal to 0 then
			 *   Assign 1 to v_mode (use tag only)
			 * - IF pid is not equal to 0 then
			 *   Assign 2 to v_mode (Use tag and pid)
			 * - IF pid equals 0 and flight_id in not equal to 0 then
			 *   Assign 3 to v_mode (Use tag and flight)
			 * - IF tag only (v_mode = 1) then
			 *   IF tab2b is specified then
			 *     Open cursor for select rows from the bag table where the bag
			 *      tag2a match
			 *   ELSEIF tag2a is specified then
			 *     Open cursor for select rows from the bag table where the bag
			 *      tag2b match
			 *   ELSE
			 *     Open cursor for select rows from the bag table where the bag
			 *      tag match matches
			 * - ELSEIF tag and pid (v_mode = 2) then
			 *   IF tab2b is specified then
			 *     Open cursor for select rows from the bag table where the bag
			 *      tag2a match and the pid
			 *   ELSEIF tag2a is specified then
			 *     Open cursor for select rows from the bag table where the bag
			 *      tag2b match and the pid
			 *   ELSE
			 *     Open cursor for select rows from the bag table where the bag
			 *      tag match matches and the pid
			 * - ELSEIF tag and flight (v_mode = 3) then
			 *   IF tab2b is specified then
			 *     Open cursor for select rows from the bag table where the bag
			 *      tag2a match and the flight and pax
			 *   ELSEIF tag2a is specified then
			 *     Open cursor for select rows from the bag table where the bag
			 *      tag2b match and the flight and pax
			 *   ELSE
			 *     Open cursor for select rows from the bag table where the bag
			 *      tag match matches and the flight and pax
			 * - ELSE
			 *   Open a cursor with NULL values
			 */

			v_mode := -1;

			if (fid = 0 and pid = 0) then
				v_mode := 1;        /* use tag only */
			end if;

			if (pid != 0) then
				v_mode := 2;        /* use tag + pid */
			end if;

			if (pid = 0 and fid != 0) then
				v_mode := 3;        /* use tag + flight */
			end if;

			if (v_mode = 1) then

				/* use the barcode representation */
				open GetBagData_cv 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, 
					TO_CHAR(time_bag_stored, 'HH24:MI'),
					pax_id,
					get_uld_pos(uld_no),
					security_clearance_status,
					sc_user_id,              
					sc_location,
					TO_CHAR(sc_time,'DD-MON-YY HH:MI'),
                                        bag_exception
				from
					bag b
				where
              bag_tag3  = tag3
          and bag_tag2b = tag2b
          and bag_source_location = depcode;


			elsif (v_mode = 2) then
 
				/* use the barcode representation */
				open GetBagData_cv 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, 
					TO_CHAR(time_bag_stored, 'HH24:MI'),
					pax_id,
					get_uld_pos(uld_no),
					security_clearance_status,
					sc_user_id,              
					sc_location,
					TO_CHAR(sc_time,'DD-MON-YY HH:MI'),
                                        bag_exception
				from
					bag
				where
					bag_tag3 = tag3   and
          bag_tag2b = tag2b and
					pax_id  = pid;

			elsif (v_mode = 3) then

				/* use the barcode representation */
				open GetBagData_cv for     
				select
					b.bag_tag,
					b.authority_to_load_dcs,
					b.authority_to_load_brs,
					b.bag_source_indicator,
					b.loaded_status, 
					b.delete_marker,
					b.uld_no, 
					TO_CHAR(b.time_loaded, 'HH24:MI'), 
					b.bag_sequence_no, 
					TO_CHAR(b.time_bag_stored, 'HH24:MI'),
					b.pax_id,
					get_uld_pos(uld_no),
					security_clearance_status,
					sc_user_id,              
					sc_location,
					TO_CHAR(sc_time,'DD-MON-YY HH:MI'),
                                        b.bag_exception
				from
					bag b,
					pax p
				where
					b.bag_tag3  = tag3  and
          bag_tag2b   = tag2b and
					p.flight_id = fid   and
					p.pax_id    = b.pax_id;

			else
				/* unknown mode */
				open GetBagData_cv for     
				select
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					-1016,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
                                        NULL
				from
					dual;

			end if;
    
	end get_bag_data;
end get_bag_data_pack;
/ 
