/*****************************************************************************
*
* Filename         - $Workfile:   get_alpha_code.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 get_alpha_code
*                    (Phase 2C addition)
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   get_alpha_code.sql  $  $Revision:   1.5  $
*
* 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/get_alpha_code.sqv  $
* 
*    Rev 1.5   12 Oct 1998 16:42:56   tagtrak
* OR: 12682 12683 12678 12654 12419 12265 12805 12285
* CR: 12682 12683 12678 12654 12419 12265 12805
* REV:
* Vienna enhancements/fixes for database V0.4.1E
* branch ATS revision moved to trunk.
* Andrew Walker
* 
*    Rev 1.4.1.0   25 Sep 1998 12:33:40   tagtrak
* OR: 12682 12683 12678 12654 12419 12265 12285
* CR: 12682 12683 12678 12654 12419 12265
* REV:
* Vienna enhancements for Database 0.4.1
* Andrew Walker
* 
*    Rev 1.4   02 Apr 1998 19:34:42   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.3   15 Jan 1998 12:12:26   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.2   24 Oct 1997 11:08:08   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT create or replace procedure get_alpha_code

create or replace procedure get_alpha_code (
	p_bar_code     in  char,
	ret_alpha_code out char,
	used_default   out number
)
as

	found    boolean := TRUE;
	numrows  number;

begin

	/*
	 * - Initialise variable
	 * - count how many matching records there are.
	 * - if we found exactly one record then
	 * - Select the alpha_code from the carrier_lookup table where the
	 *   bar_code is equal to the p_bar_code parameter.  If no data is
	 *   found then set the found variable to FALSE.
	 * - else set found to FALSE
	 * - If found is FALSE then
	 *   Initialise used_default variable
	 *   Select the db_value from the db_config table where the db_item is
	 *   equal to 'def_bar_des'.  If no data found then initialise the
	 *   variables.
	 */
 
	used_default := 0;

  /*
	 * Attempt to get the carrier alpha code.
	 */
	select
		count(1)
	into
		numrows
	from
		carrier_lookup
	where
		bar_code = p_bar_code;

	if ( numrows = 1 ) then

		begin

			select
				alpha_code
			into
				ret_alpha_code
			from
				carrier_lookup
			where
				bar_code = p_bar_code;

			exception
				when NO_DATA_FOUND then
					found := FALSE;
		end;

	else

		found := FALSE;

	end if;
 

  /*
	 * If we didn't find exactly one record, use the default value.
	 */

  if ( not found ) then
    used_default := 1;

		begin
			select
				rpad(db_value,3)
			into
				ret_alpha_code
			from
				db_config
			where
				db_item = 'def_bar_des';

			exception
				when NO_DATA_FOUND then
					ret_alpha_code := '';
					used_default := 0;
		end;

	end if;

end get_alpha_code;
/
