/*****************************************************************************
*
* Filename         - $Workfile:   increment_warn_count.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell 2/8/2        1.5        15639
* 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:   increment_warn_count.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/aisarch/db_ora/db/source/live/procedures/increment_warn_count.sqv  $
* 
*    Rev 1.5   02 Aug 2002 14:33:52   yomi
* DESC: Null terminate char  variable
* CR:   15639
* 
*    Rev 1.4   02 Apr 1998 19:36:38   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.3   15 Jan 1998 12:13:16   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:14:10   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT create or replace procedure increment_warn_count

create or replace procedure increment_warn_count (
	ltime in out  char,
	winal in out  number
) 
as

	pid  number(10); 
	tg   char(10); 
	tg1  char(1); 
	tg2a char(3); 
	tg2b char(3); 
	tg3  number(10); 
	nw   number(10); 
	ta   char(10); 

begin

	/*
	 * - declare a cursor which will select rows from the bag_alert table
	 *   where the last_time_stamp is less than or equal to the ltime
	 *   parameter which is for the update of the bag_alert table.
	 * - Loop around the cursor
	 *   assign variables
	 *   update the bag_alert table by incrementing the number of warnings
	 *   and setting the last_time_stamp to the system date for the current
	 *   record.
	 *   Check if we need an ALARM or WARNING
	 *   Insert a record into the bag_trace table
	 */

	declare
	cursor csr_0 is
		select
			pax_id pid,
			bag_tag tg,
			bag_tag1 tg1,
			bag_tag2a tg2a,
			bag_tag2b tg2b,
			bag_tag3 tg3,
			no_warnings nw
		from
			bag_alert
		where
			last_time_stamp <= to_date(ltime)
		for update of no_warnings;

	begin
		for alert_record in csr_0 loop
			pid  := alert_record.pid;
			tg   := alert_record.tg;
			tg1  := alert_record.tg1;
			tg2a := alert_record.tg2a;
			tg2b := alert_record.tg2b;
			tg3  := alert_record.tg3;
			nw   := alert_record.nw;

			update
				bag_alert
			set 
				no_warnings     = nw + 1,
				last_time_stamp = SYSDATE
			where
				current of csr_0;

			if (nw + 1 >= winal) then 
				ta := 'ALARM';
			else
				ta := 'WARNING';
			end if;

			insert into bag_trace (
				pax_id,
				bag_trace_time,
				user_id,
				location_code,
				error_id,
				trace_action,
				scanner_id,
				bag_tag2b,
				bag_tag3,
				flight_id_scan,
				bag_tag1,
				bag_tag,
				bag_tag2a
			) values (
				pid,
				SYSDATE,
				1,
				'MBA',
				0,
				ta,
				'0',
				tg2b,
				tg3,
				0,
				tg1,
				tg,
				tg2a
			);
		end loop;
	end;
end increment_warn_count;
/
