/*****************************************************************************
*
* Filename         - $Workfile:   rec_uld_posn.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
*
*****************************************************************************/

PROMPT create or replace procedure rec_uld_posn
create or replace procedure rec_uld_posn (
	p_uld_no   in out  char,
	fltid      in out  number,
	posn       in out  char,
	p_user_id  in out  number,
	locn       in out  char ,
	retval     in out  number,
	scanner_id in      char
) 
as
	uflt      number(10);
	bag_alert exception;
	ret       number(10);

begin

	/*
	 * - Assign variables
	 * - Select the flight_id from the ulds table where the uld_no is
	 *    equal to the p_uld_no parameter and the pd_marker is equal to
	 *    'N'.  If no data is found, then assign -1 to uflt.
	 * - IF uflt is equal to -1 (ie. no records found) then
	 *   Assign -1001 to the return value
	 * - ELSE
	 *   IF the flight_id's are different then
	 *     Assign -200 to the return value
	 *   ELSE
	 *     Update the ulds table setting the appropriate columns where the
	 *      the uld_no is equal to the p_uld_no parameter and the flight_id
	 *      is equal to the uflt variable.
	 */

	uflt   := -1;
	retval := 0;

	/* try and get the uld_details */	    	  	   	      	 
	begin
		select
			flight_id 
		into
			uflt 
		from
			ulds
		where
			uld_no    =  p_uld_no and
			pd_marker = 'N';

		exception
			when NO_DATA_FOUND then
				uflt := -1;
	end;


	if (uflt = -1) then 
		/* no uld records found */
		retval := -1001;
	else 
		if (uflt != fltid) then 
			/* uld not for this flight */
			retval := -200;
		else 
			/* now its alright to update the columns in the ulds table */


                  check_uld_alert(uflt, p_uld_no,ret);

			if ret = -1082 then
				 raise bag_alert;
                  end if;

                  validate_uld_posn (uflt, p_uld_no, posn, p_user_id, retval);
                  if retval >= 0
                  or retval = -1212 /* Full Validation not performed warning only */
                  then 
                      store_uld_posn (p_uld_no, uflt, posn, p_user_id, locn, retval, scanner_id);
                  end if;
		end if;
	end if;

exception

	 when bag_alert then
			retval := ret;
	
end rec_uld_posn;
/


