/*
*
* Filename         - $Workfile:   is_flight_batch.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:   is_flight_batch.sql  $  $Revision:   1.1  $
*
* 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:   
* 
****************************************************************************/

PROMPT create or replace procedure is_flight_batch 

create or replace procedure is_flight_batch (
	fltno       in   char,
	fltdate     in   char,
        siteId     in   number,
	fltstatus        out  char
) 

as  
begin

	/*
	 *	if any scanners for a flight have been taken out of RF 
	 *	and data for ALL scanners has not been updated fltstatus= Y
	 *			else fltstatus = N
	 */

	/*  do any scanners exist for this flight and not updated */
	select 'Y' 
	into fltstatus
	from dual
	where exists (
	select r.FLIGHT_ID 
	from batch_scanner r,
			flight_outbound_v1 f 
	where  
				f.flight_no = fltno
				and f.flight_date = fltdate
        and f.SITE_ID = siteId
				and  f.FLIGHT_ID  = r.FLIGHT_ID 
				and  r.UPDATE_STATUS <> 'U' ) ;

	

    EXCEPTION
         WHEN NO_DATA_FOUND THEN
            fltstatus := 'N';
 

end is_flight_batch;
/
