/*****************************************************************************
*
* Filename         - $Workfile:   reuse_exception_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, 1998
*
*****************************************************************************/

/*
 * Stored procedure reuse_exception_bag will be called in the following
 * situations:-
 * 1. To re-use a bag record which has the delete marker set.
 * 2. To modify the first character of the bag-tag (e.g. if a CHG BSM
 * 	  is received with a different first character in the bag tag.
 */

PROMPT create or replace procedure reuse_exception_bag

create or replace procedure reuse_exception_bag (
	fltid      in out  number,
	tag        in out  char,
	tag1       in out  char,
	tag2a      in out  char,
	tag2b      in out  char,
	tag3       in out  number,
	atldcs     in      char,
	dest       in out  char,
	proc       in out  char,
	unit       in out  char,
	dcsid      in out  char,
	subunit    in out  char,
	dcstagno   in      char,
	excep_code in      char,
	paxid      in      number,
        conn_type  in     char,
	rpid          out  number
) 
as 

	dm number(10);

	/* reuse_bag Parameters */
	source_indicator bag.bag_source_indicator%TYPE:='L';
	v_btype					 bag.bag_type%TYPE;
	v_del_instr			 bag.delivery_instr%TYPE;

begin

	/*
	 * - Initialise Variable
	 * - IF tag2a is not used then
	 *   Select the delete marker from the bag table where the paxid,
	 *    bag_tag3 and bag_tag2b are the same as the relevant parameters.
	 * - ELSE IF tag2b is not used then
	 *   Select the delete marker from the bag table where the paxid,
	 *    bag_tag3 and bag_tag2a are the same as the relevant parameters.
	 * - ELSE
	 *   Select the delete marker from the bag table where the paxid,
	 *    bag_tag3, bag_tag2a and bag_tag2b are the same as the relevant
	 *    parameters.
	 * - IF proc is not used then
	 *   Select process_name, unit_name, dcsid and subunit name from the
	 *    flight table where the flight_id is equal to the fltid
	 *    parameter.  If no data is found, then assign NULL values to the
	 *    proc, unit, dcsid and subunit variables.
	 *   Now call the reuse_bag procedure.
	 * - IF delete_marker is equal to 1 then
	 *   Update the pax table, incrementing the number of bags for this
	 *    pax.
	 *   Assign v_rpid to rpid
	 */

	/* we must have a pax since the bag has been added previously */

	dm := 0;
 
	if (tag2a = '-' or tag2a IS NULL) then 
		select
			delete_marker, bag_type, delivery_instr 
		into
			dm, v_btype, v_del_instr 
		from
			bag
		where
			pax_id    =  paxid and
			bag_tag3  =  tag3  and
			bag_tag2b =  tag2b;

	elsif (tag2b = '-' or tag2b IS NULL) then 
		select
			delete_marker, bag_type, delivery_instr 
		into
			dm, v_btype, v_del_instr 
		from
			bag
		where
			pax_id    =  paxid and
			bag_tag3  =  tag3  and
			bag_tag2a =  tag2a;

	else 
		select
			delete_marker, bag_type, delivery_instr 
		into
			dm, v_btype, v_del_instr 
		from
			bag
		where
			pax_id    =  paxid and
			bag_tag3  =  tag3  and
			bag_tag2a =  tag2a and
			bag_tag2b =  tag2b;
	end if;


	/*
	 * if we have no DCS address, use that of the flight.
	 * this is mainly for the TC and MWS
	 */

  if (proc is null or proc = '-') then
	begin
		select
			process_name,
			unit_name,
			dcsid,
			subunit_name
		into
			proc,
			unit,
			dcsid,
			subunit
		from
			flight
		where
			flight_id = fltid;

		exception
			when NO_DATA_FOUND then
				proc    := NULL;
				unit    := NULL;
				dcsid   := NULL;
				subunit := NULL;
		end;
	end if;

	reuse_bag (
		tag,
		tag1,
		tag2a,
		tag2b,
		tag3,
		paxid,
		atldcs,
		dest,
		source_indicator,
		excep_code,
		proc,
		unit,
		dcsid,
		subunit,
		dcstagno,
		conn_type,
		v_btype,
		NULL,									/* Ensure security clearance is re-calculated */
		v_del_instr,
		NULL, /* World Tracer Reference */
		rpid,
		0,
		NULL --  bvis subheader
	);

	rpid := paxid;

end reuse_exception_bag;
/
