/*****************************************************************************
*
* Filename         - $Workfile:   get_uld_pos.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 function get_uld_pos
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   get_uld_pos.sql  $  $Revision:   1.2  $
*
* 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_uld_pos.svl  $
* 
*    Rev 1.2   20 Sep 1999 14:41:16   tagtrak
*  
* 
*    Rev 1.1   02 Nov 1998 14:09:52   tagtrak
* OR:
* CR: 12983
* REV:
* Corrected to ignore ULDS which are actually holds!!
* Andrew
* 
*    Rev 1.0   22 Oct 1998 15:05:14   tagtrak
* OR: 12686
* CR: 12686
* REV:
* Initial Check-in to PVCS
* Show ULD hold position on bag_search report
* Andrew Walker
* 
*
****************************************************************************/


PROMPT create or replace function get_uld_pos

create or replace function get_uld_pos (
	p_uld_no     in    ulds.uld_no%type
) return char

is

	result ulds.uld_hold_no%type;

begin

	begin
		select
			uld_hold_no
		into
			result
		from
			ulds
		where
			uld_no = p_uld_no and
			pd_marker != 'Y' and
			not exists (
				select
					1
				from
					holds
				where
					hold_no = p_uld_no
			);

		exception
			when NO_DATA_FOUND then
				result := NULL;
	end;

	return result;

end get_uld_pos;
/
