/*****************************************************************************
*
* Filename         - $Workfile:   get_alert_counts.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:   get_alert_counts.sql  $  $Revision:   1.4  $
*
* 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_alert_counts.sqv  $
* 
*    Rev 1.4   02 Apr 1998 19:34:40   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.3   15 Jan 1998 12:12:24   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:07:58   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT create or replace procedure get_alert_counts

create or replace procedure get_alert_counts (
	no_warns_in_alarm in out  number,
	no_alarms         in out  number,
	no_warns          in out  number,
	no_unloads        in out  number,
  p_agent_code      in out  char
) 

as
	act     char(10); 
	wno     number(10); 
  depcode site_info.airport_code%type;

begin

  depcode := multi_site.get_agent_loc(p_agent_code);

	/*
	 * - Initialise Variables
	 * - Declare a cursor for the table bag_alert
	 * - Loop around cursor
	 *   Assign variables
	 *   IF act variable = 'UNLOAD' then
	 *     Increment the number of unloads
	 *   else
	 *     Increment the number of alarms
	 * - End loop
	 */

	no_alarms  := 0;
	no_warns   := 0;
	no_unloads := 0;

	declare
		cursor csr_0 is
		select
			alert_on_action act,
			no_warnings wno
		from
			bag_alert
    where airport_code = depcode;

	begin
		for alert_record in csr_0 loop
			act := alert_record.act;
			wno := alert_record.wno;
   
			if (act = 'UNLOAD') then 
				no_unloads := no_unloads + 1;
			else 
				if (wno < no_warns_in_alarm) then 
					no_warns := no_warns + 1;
				else
					no_alarms := no_alarms + 1;
				end if;
			end if;
		end loop;
	end;
        
end get_alert_counts;
/
