/*****************************************************************************
*
* Filename         - $Workfile:   check_alert.sql  $
*
*                     Name Yomi Kogbe     Date 03/05/00  Version     CR Number 14994
* 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
*
*****************************************************************************/



PROMPT create or replace procedure check_alert 

create or replace procedure check_alert 
(
   v_bag_tag2a   in  bag_alert.bag_tag2a%type
 , v_bag_tag2b   in  bag_alert.bag_tag2b%type
 , v_bag_tag3    in  bag_alert.bag_tag3%type
 , v_pax_id      in  bag_alert.bag_tag%type
 , retval        out number
)  is

  counter   number(10) := 0;

  cursor c1 is
  select bag_tag, 
         alert_on_action,
         pax_id
   from  bag_alert
  where  (bag_tag2a = v_bag_tag2a or bag_tag2b = v_bag_tag2b)
    and  bag_tag3 = v_bag_tag3
    and  pax_id  = v_pax_id
    and  alert_on_action = 'UNLOAD';

begin

     For c1_rec IN c1
     Loop


       counter := counter + 1;

     End Loop;

 
     IF counter > 0 then
          retval := -1;
     else
          retval := 0;
     End If;


end;


/
