/*****************************************************************************
*
* Filename         - $Workfile:   xfer_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:   xfer_bag.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/aisarch/db_ora/db/source/live/procedures/xfer_bag.sqv  $
* 
*    Rev 1.8   01 Aug 2002 13:58:48   yomi
* DESC: Timezone change
* CR:   15508
* 
*    Rev 1.7   25 Sep 1998 10:05:42   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.6.2.0   18 Aug 1998 09:36:40   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merging Vienna (0.3.1E) and Core Product (0.2.5E).
* Andrew Walker
* 
*    Rev 1.6.1.0   14 Jul 1998 13:34:30   tagtrak
* OR: 12309
* CR: 12309
* REV:
* Corrected problem with exception being generated when transferring to Hold.
* Andrew
* 
*    Rev 1.6   02 Apr 1998 19:38:36   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.5   26 Feb 1998 10:16:38   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
* 
*    Rev 1.4   15 Jan 1998 12:14:14   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.3   24 Oct 1997 11:21:44   tagtrak
* Added PVCS header information
*
****************************************************************************/
PROMPT create or replace procedure xfer_bag 

create or replace procedure xfer_bag (tag in out  char,
	tag2a           in out  char,
	tag2b           in out  char,
	tag3            in out  number,
	pid             in out  number,
	from_uld_no     in out  char,
	to_uld_no       in out  char,
	p_location_code in out  char,
	scanner_id      in      char,
	bpmid           in      number,
	fltid           in out  number ,
	retval          in out  number
) 
as

	bsn                  number(6); 
	int_retval           number(10); 
	found_ulds           number;
	pd_ulds              number;
	no_uld_recs          number;

	to_uld_flight_id     ulds.flight_id%TYPE;
  v_fid    flight.flight_id%type;
  depair      pax.departure_airport_code%type := null;

        l_hold_no            HOLDS.HOLD_NO%TYPE;
begin
    depair := get_offset.find_airport_code(fltid,pid);

	/*
	 * - Initialise Variables
	 * - Call the add_bag_to_uld procedure
	 * - IF bsn (obtained from the call to add_bag_to_uld) is > 0 THEN
	 *   IF tag2b is entered then
	 *     Update the bag table, setting the various fields where the
	 *      bag_tag3, bag_tag2b and pax_id are the same as the relevant
	 *      parameters.
	 *   ELSE
	 *     Update the bag table, setting the various fields where the
	 *      bag_tag3, bag_tag2a and pax_id are the same as the relevant
	 *      parameters.
	 *   Call the remove_bag_from_uld procedure
	 * - ELSE
	 *   Assign -1013 to the return value
	 * - IF the return value from the remove_bag_from_uld procedure is less
	 *    than 0, then assign -1013 to the return value.
	 * - ELSE
	 *   IF bpmid is greater than 0 then
	 *     Insert a record into the bpm_data table
	 */

	bsn             := 0;
	int_retval      := 0;
	retval          := 0;
	found_ulds      := 0;
	pd_ulds         := 0;
	no_uld_recs     := 0;


	/*
	 * We need to check if the destination ULD is acceptable.
	 * Count ULDS which match the to_uld_no and flight ID and are
	 * not PD. (This will give either 0 or 1).
	 */
	select
		count(*)
	into
		found_ulds
	from
		ulds
	where
		uld_no = to_uld_no and
		flight_id = fltid and
		pd_marker != 'Y';

	/*
	 * If we didn't find it, we'll decide which error code to return.
	 */
	if ( found_ulds = 0 ) then

		/*
		 * See if there is a Uld for this Flight ID which is PD.
		 */
		select
			count(*)
		into
			pd_ulds
		from
			ulds
		where
			uld_no = to_uld_no and
			flight_id = fltid and
			pd_marker = 'Y';

		/*
		 * And see how many Uld records have this number.
		 */
		select
			count(*)
		into
			no_uld_recs
		from
			ulds
		where
			uld_no = to_uld_no
                        and
                        to_uld_no not in (select hold_no from holds);

		if ( pd_ulds > 0 ) then
			retval := -208; /* FLT_STS_PD */
		elsif ( no_uld_recs > 0 ) then
			retval := -1006; /* WRONG_FLIGHT */
		else
                        -- select to see if the to ULD is a hold_no
                        -- if so, create the hold no and continue
     
                        BEGIN
                           SELECT hold_no INTO l_hold_no FROM holds WHERE hold_no = to_uld_no; 
                           EXCEPTION when NO_DATA_FOUND then
			      retval := -211; /* TO_ULD_UNKNOWN */
                        END;
                        -- if retval is still zero then the the to_uld_no is a
                        -- hold so create the hold uld and continue
                        IF retval = 0 THEN
                           add_hold_uld(fltid,to_uld_no,bsn);
                        END IF;
		end if;

                -- only return if we have a none zero return value (we may have create a hold uld)
                IF retval != 0 THEN
		   return;
                END IF;

	end if;

  /* CRs 15561, 15605
   * If the bag has not already been loaded then lock the row to
   * prevent another load of the same bag by a different transaction.
   * If the bag has been loaded then terminate processing and return
   * to the calling unit with an already loaded error (-1011).
   */
  BEGIN
    SELECT
      0
    INTO
      retval
    FROM
      bag
    WHERE
      bag_tag3 = tag3
    AND
      (bag_tag2a = NVL(tag2a,'-')
      OR
      bag_tag2b = NVL(tag2b,'-'))
    AND
      pax_id = pid
    AND
      delete_marker = 0
    AND
      uld_no = from_uld_no
    AND
      loaded_status = 'Y'
    FOR UPDATE OF
      uld_no
      ,loaded_status;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      retval := -1011;
      RETURN;
  END;

	/* lets add the from bag to its destination */	    		 
	add_bag_to_uld (
		fltid,
		to_uld_no,
		bsn
	);

	/* now lets load the bag */
	if (bsn >= 0) then 
		if (tag2b != '-') then 
			/* use bar code representation */
			update
				bag
			set 
				uld_no          = to_uld_no,
				loaded_status   = 'Y',
				bag_sequence_no = bsn,
				time_loaded     = SYSDATE + get_offset.airport_code(depair),
				location_code   = p_location_code,
				scanner_id      = scanner_id,
				uld_flight_id   = fltid
			where
				bag_tag3        = tag3  and
				bag_tag2b       = tag2b and
				pax_id          =  pid;
		else 
			/* use alpha */
			update
				bag
			set 
				uld_no          = to_uld_no,
				loaded_status   = 'Y',
				bag_sequence_no = bsn,
				time_loaded     = SYSDATE + get_offset.airport_code(depair),
				location_code   = p_location_code,
				scanner_id      = scanner_id,
				uld_flight_id   = fltid
			where
				bag_tag3        = tag3   and
				bag_tag2a       =  tag2a and
				pax_id          =  pid;
		end if;


		/* now lets remove the bag in uld 2 */		    			 				  
		remove_bag_from_uld (
			fltid,
			from_uld_no,
			int_retval
		);

	else
		retval := -1013;
	end if;

		 /* put into BPM temp table */			     				   			

	if (int_retval < 0) then 
		/* uld error */
		retval := -1013;
	else 
		if (bpmid > 0) then 
			insert into
				bpm_data (
					bpmid,
					pax_id,
					bag_tag
				) values (
					bpmid,
					pid,
					tag
				);
		end if;
	end if;
	
end xfer_bag;
/
