/*****************************************************************************
*
* Filename         - $Workfile:   add_hold_uld.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:   add_hold_uld.sql  $  $Revision:   1.7  $
*
* 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/add_hold_uld.sqv  $
*
*    Rev 1.7   01 Aug 2002 14:04:54   yomi
* DESC: Timezone change
* CR:   15508
*
*    Rev 1.6   02 Apr 1998 19:31:24   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
*
*    Rev 1.5   15 Jan 1998 12:10:50   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
*
*    Rev 1.4   24 Oct 1997 10:59:08   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT create or replace procedure add_hold_uld

create or replace procedure add_hold_uld (
	fid    in out  number,
	uld_no in out  char,
	rv     out     number
)

as
	rv_param number;
	string1  char(1);
	string2  char(1);
  v_pid    pax.pax_id%type;
  depair   pax.departure_airport_code%type := null;

begin

	/*
	 * - Call the check_ulds stored procedure
	 * - if rv_param = 7 then
	 *   insert a record into the ulds table
	 *   call the add_uld_dests_record procedure
	 */

  depair := get_offset.find_airport_code(fid,v_pid);

	string1 := '*';
	string2 := '*';

	check_ulds ( uld_no, fid, string1, rv_param);

	if (rv_param = 7) then

		/*
		 * Not yet assigned.
		 */
		insert into ulds (
			uld_no,
			flight_id,
      uld_category,
			uld_type,
			pd_marker,
			uld_no_of_bags,
			next_bag_seq_no,
			uld_loaded_status,
			uld_hold_no,
			uld_load_time
		) VALUES (
			uld_no,
			fid,
      '*',
			string1,    -- uld_type
			'N',
			0,
			1,
			'Y',
			rtrim(uld_no),
			SYSDATE + get_offset.airport_code(depair)
		);

		add_uld_dests_record ( uld_no, string2, string2, fid, NULL, NULL);

		rv_param := 8; /* Assigned to this flight. */

	end if;

	rv := rv_param;

end add_hold_uld;
/
