/*****************************************************************************
*
* Filename         - $Workfile:   check_uld_alert.sql  $
*
*                   Name Yomi Kogbe  Date 6-Jun-2001 Version 1.0  CR Number 15437
* 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 1998
*
*****************************************************************************/

/**********************************************
 This stored procedure check a uld for bag alert.
 returns -1082 for found bag alert
**********************************************/

 
PROMPT CREATE OR REPLACE PROCEDURE check_uld_alert
 
CREATE OR REPLACE PROCEDURE check_uld_alert
(
	fid     IN     flight.flight_id%TYPE,
	uld     IN     ulds.uld_no%TYPE,
	retval  OUT    NUMBER
)
IS

   ret         number(10);
   bag_alert   exception;


CURSOR c1 is
      select    b.bag_tag1        tag1,
                b.bag_tag2a       tag2a,
                b.bag_tag2b       tag2b,
                b.bag_tag3        tag3,
                b.pax_id          pax_id
        from    bag b, pax p
       where    p.pax_id    = b.pax_id
         and    b.loaded_status = 'Y'
         and    b.uld_no    = uld 
         and    p.flight_id = fid; 


BEGIN

        FOR c1_rec IN c1 LOOP
      
         check_alert( c1_rec.tag2a, c1_rec.tag2b, c1_rec.tag3, c1_rec.pax_id, ret); 

         IF ret < 0 then
            raise bag_alert;
         END IF;

       END LOOP;

EXCEPTION


      when bag_alert then

        retval := -1082;

END;

/ 
            
