/*****************************************************************************
*
* Filename         - $Workfile:   unload_bag.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:   unload_bag.sql  $  $Revision:   1.6  $
*
* 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/unload_bag.sqv  $
* 
*    Rev 1.6   04 Dec 1997 15:44:00   tagtrak
* OR:
* CR:
* REV:
* Removed code to send mail to MWS - redundant.
* 
*    Rev 1.5   24 Oct 1997 11:21:20   tagtrak
* Added PVCS header information
*
****************************************************************************/

/*
 * NOTE: This procedure will NOT raise an unload alert when called
 * from delete_bag - this is because the bag will have already been
 * deleted and hence will be ignored. In this case it is the bag table
 * update trigger which will raise the unload alert when a loaded bag
 * is deleted.
 */
PROMPT create or replace procedure unload_bag

create or replace procedure unload_bag (
	paxid    in out  number,
	bagtag   in out  char,
	atl      in      char,
	nobags      out  number
) as 

	tatl CHAR(1); 
	tuld CHAR(10); 
	tsts CHAR(1); 
	bagtag1 CHAR(1); 
	bagtag2a CHAR(3); 
	bagtag2b CHAR(3); 
	bagtag3 NUMBER(10); 
	tseq NUMBER(10); 
	chg NUMBER(10); 
	ldd NUMBER(10); 
	fid NUMBER(10);

BEGIN

	tsts := '-';
	nobags := -1; /* found no bags */
 
	begin
		SELECT
			uld_no,
			loaded_status,
			authority_to_load_dcs,
			bag_sequence_no,
			bag_tag1,
			bag_tag2a,
			bag_tag2b,
			bag_tag3 
		INTO
			tuld,
			tsts,
			tatl,
			tseq,
			bagtag1,
			bagtag2a,
			bagtag2b,
			bagtag3 
		FROM
			bag
		WHERE
			pax_id =  paxid AND
			bag_tag =  bagtag AND
			delete_marker != 1; 

		exception
			when NO_DATA_FOUND then
				tsts := '-';
			
	end;

	IF (tsts != '-') THEN 
		nobags := 0; /* found a bag */
	 
		IF (tsts = 'Y') THEN 
			/* it was loaded */
			ldd := 1;
			nobags := 1; /* needs unloading */
		END IF;
 
		IF (atl != '-' AND atl != tatl) THEN 
			/* atl needs to change */
			chg := 1;
		END IF;
 
		IF (ldd = 1) THEN 

			/* add to bag_alert so a warning is raised */
			SELECT
				flight_id 
			INTO
				fid 
			FROM
				pax
			WHERE
				pax_id =  paxid; 

			INSERT INTO bag_alert (
				bag_tag,
				bag_tag1,
				bag_tag2a,
				bag_tag2b,
				bag_tag3,
				pax_id,
				flight_id,
				no_warnings,
				last_time_stamp,
				alert_on_action
			) VALUES ( 
				bagtag,
				bagtag1,
				bagtag2a,
				bagtag2b,
				bagtag3,
				paxid,
				fid,
				1,
				SYSDATE,
				'UNLOAD'
			);

		END IF;
 
		IF (chg = 1) THEN 

			/* modify record */
			UPDATE
				bag
			SET 
				authority_to_load_dcs =  atl
			WHERE
				pax_id =  paxid AND
				bag_tag =  bagtag;

		END IF;

	END IF;
 
end unload_bag;
/
