/*****************************************************************************
*
* Filename         - $Workfile:   unknown_bag_triggers.sql  $
*
*                     Name              Date      Version     CA Number
*                     Gordon Clark    23/7/2004     1.0      FALCP/CA/858
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - ULTRATRAK(Oracle Database)
*
* Source Code
* Identifier       - 
*
* Design Spec
* Section          - 
*
* Contents         -  Live Schema: triggers for unknown_bag table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2004
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   unknown_bag_triggers.sql  $  $Revision:   1.0  $
*
* Revision History
*
****************************************************************************/

create or replace trigger tu_unknown_bag
before update on unknown_bag
for each row
declare
    depair        pax.departure_airport_code%type := null;
begin

    depair := get_offset.get_user_location(:new.user_id);   


    IF (:new.pax_id != 0 and :old.pax_id = 0) 
    THEN

        -- This was an unknown bag but is now has a passenger (and flight), so update 
        -- previously created tracking/trace entries for this bag

        UPDATE tracking_log
           SET tracking_log.pax_id = :new.pax_id
         WHERE tracking_log.pax_id = 0 AND
              (tracking_log.bag_tag2a = :new.bag_tag2a OR tracking_log.bag_tag2b = :new.bag_tag2b) AND
               tracking_log.bag_tag3 = :new.bag_tag3 AND
               tracking_log.flight_id = :new.flight_id;

        -- Update tracking log entries if this was previously an unknown bag that
        -- was not associated with a flight

        UPDATE tracking_log
           SET tracking_log.pax_id = :new.pax_id,
               tracking_log.flight_id = :new.flight_id
         WHERE tracking_log.pax_id = 0 AND tracking_log.flight_id = 0 AND
              (tracking_log.bag_tag2a = :new.bag_tag2a OR tracking_log.bag_tag2b = :new.bag_tag2b) AND
               tracking_log.bag_tag3 = :new.bag_tag3;

    ELSE

       IF (:new.flight_id != 0 and :old.flight_id = 0) and (:new.pax_id = 0) THEN
 
             -- This unknown bag has just been associated with a flight (e.g. via the load bag
             -- screen). However, there is still no passenger for this bag.
   
             UPDATE tracking_log
                SET tracking_log.flight_id = :new.flight_id
               WHERE tracking_log.pax_id = 0 AND tracking_log.flight_id = 0 AND
                    (tracking_log.bag_tag2a = :new.bag_tag2a OR tracking_log.bag_tag2b = :new.bag_tag2b) AND
                     tracking_log.bag_tag3 = :new.bag_tag3;

       END IF;

    END IF;

    IF (:new.flight_id != 0 and :old.flight_id = 0) and  
       trim(config.get_value('ALLOW_BAG_WITH_NOFLT')) = 'Y' 
    THEN
   
       -- Recreate any bag trace entries which were not associated with flights

       INSERT INTO bag_trace 
          (pax_id, bag_trace_time, action_time, user_id, location_code, error_id, trace_action, 
           scanner_id, bag_tag2b, bag_tag3, flight_id_scan, bag_tag1, bag_tag, bag_tag2a, 
           record_id, uld_no, airport_code) 
           SELECT :new.pax_id, sysdate + get_offset.airport_code(depair), 
                  bag_trace_time, user_id, location_code, error_id, trace_action,
                  scanner_id, bag_tag2b, bag_tag3, :new.flight_id, bag_tag1, bag_tag, bag_tag2a,
                  record_id, uld_no, airport_code FROM bag_trace 
              WHERE
                  bag_trace.flight_id_scan = 0 AND
                  (bag_trace.bag_tag2a = :new.bag_tag2a OR bag_trace.bag_tag2b = :new.bag_tag2b) AND
                   bag_trace.bag_tag3 = :new.bag_tag3;
    END IF;

end;
/


create or replace trigger ti_unknown_bag
before insert on unknown_bag
for each row
begin

    if (:new.group_no is not null) then

       -- the bag has been added to the group

       begin
          update bag_group set no_of_bags = no_of_bags + 1 where
             group_no = :new.group_no;

       exception
          when others then
             null;
       end;

    end if;

end;
/

create or replace trigger td_unknown_bag
before delete on unknown_bag
for each row
begin
        -- Delete any corresponding entries in the Tracking Log table
        delete from tracking_log where 
            (:old.bag_tag2a = tracking_log.bag_tag2a OR :old.bag_tag2b = tracking_log.bag_tag2b) AND
             :old.bag_tag3 = tracking_log.bag_tag3 AND
             tracking_log.flight_id = 0;

        -- Decrement bag group count if bag was in group
        if (:old.group_no is not null AND :OLD.ok_to_fly != 1) then

           begin

              update bag_group set no_of_bags = no_of_bags - 1 where
                 group_no = :old.group_no;

           exception
              when others then
                 null;
           end;

        end if;

end;
/




