/*****************************************************************************
*
* Filename         - $Workfile:   check_bag_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_bag_alert

create or replace procedure check_bag_alert
(
   v_agent_code   in  char
 , v_amount       out number
 , retval         out char
) is


  v_count   number(10);
  v_counter number(10) :=0;

cursor check_bag is
select count(ba.alert_on_action)
       from flight_access fa, bag_alert ba, bag b, pax p
       where b.pax_id  =  ba.pax_id
       and b.bag_tag = ba.bag_tag
       and b.pax_id = p.pax_id
       and p.flight_id = fa.flight_id
       and fa.agent_code = v_agent_code
       and ba.alert_on_action in ('UNLOAD', 'MISSING');


begin
      
      

      open check_bag;
      Loop 
      fetch check_bag into v_count;
      exit when check_bag%notfound;
      end loop; 
      close check_bag;

          
          
      If  v_count > 0 then
             retval := 'Y';
            
         else
             retval := 'N';

      end if;

       v_amount := v_count;



exception

      when others then

         retval := -1052;

end check_bag_alert;

/
