/*****************************************************************************
*
* Filename         - $Workfile:   ulds_triggers.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: triggers for ulds table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1999
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   ulds_triggers.sql  $  $Revision:   1.0  $
*
* 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:   /pvcsarch/db_ora/db/source/live/triggers/ulds_triggers.svl  $
* 
*    Rev 1.0   17 May 1999 15:00:44   tagtrak
* OR: 13936
* CR: 13936
* REV:
* Changes added so the cascade delete constraints on the
* LIVE schema is removed and replaced with trigger based
* deletions.
* 
* Neil Bishop.
****************************************************************************/


---------------------------------------------------------------
-- UAS/FALCP/CA/00674
-- Ian Byass
-- 06/05/2004
--
-- Removed the TD_ULDS before delete trigger.
-- The trigger performed 2 functions:
--   a) Delete ULD info from ULD_DESTS
--   b) Delete ULD info from ULD_TRACE
--
-- Item (a) is automatically actioned by the ON DELETE CASCADE
-- clause of the ULD foreign key.
-- Item (b) is not required, since trace information should
-- remain.
---------------------------------------------------------------

/**
   Before Update trigger on the ULDS table for the Reflighting Module
   It will fire if the ULD LOAD TIME is changed and set a flag and store data in an interim
   table to indicate if the ULD has been loaded or removed from the aircraft. This data is
   then used by and after statement trigger.
**/

CREATE OR REPLACE TRIGGER tu_ulds
BEFORE UPDATE ON ulds
FOR EACH ROW
DECLARE
   lAction    VARCHAR2(10);
BEGIN

   IF UPDATING ('ULD_LOAD_TIME')
   THEN
      /**
         The load time has changed if the PD_marker is 'Y' we need to ajust the reflight data
      **/

      IF :old.PD_marker = 'Y'
      THEN

         IF :new.uld_load_time IS NULL
         THEN
            /** 
               ULD has been unloaded from the aircraft so it and all of its bags/pax
               will need to be added to the reflighting data.
            **/
            lAction   := 'ADD';

         ELSIF :old.uld_load_time IS NULL
         THEN
            
            /**
               ULD has been loaded, so it and its bags/pax will need to be removed from the reflighting
               data
            **/

            lAction   := 'REMOVE';

         END IF;

         INSERT INTO interim_ulds
         (flight_id,
          uld_no,
          uld_no_of_bags,
          action)
         VALUES
         (:new.flight_id,
          :new.uld_no,
          :new.uld_no_of_bags,
          lAction);

      END IF;
   END IF;

END;
/

/**
   Reflighting Module Addition.
   If the ULD is being loaded thane the bags will be removed from the reflighting tables as 
   the bags no longer missed the flight. If the ULD is unloaded, then the bags will be added
   to the reflighting tables as they are now missing from the flight.
**/

CREATE OR REPLACE TRIGGER tuas_ulds
AFTER UPDATE ON ulds
DECLARE
   lSiteId       flight.site_id%TYPE;

   CURSOR lcurInterimULDS
   IS
      SELECT flight_id,
             uld_no,
             uld_no_of_bags,
             action
      FROM interim_ulds;

BEGIN
   FOR i IN lcurInterimULDS
   LOOP

      SELECT site_id
      INTO lSiteId
      FROM flight
      WHERE flight_id = i.flight_id;

      IF i.action = 'ADD'
      THEN 
         /**
            ULD has been unloaded, so add it and its bags/pax to the reflighting
            data
         **/

         reflighting.uld_unloaded(lSiteId, i.flight_id, i.uld_no, i.uld_no_of_bags);

      ELSIF i.action = 'REMOVE'
      THEN
         /**
            ULD has been loaded, so removed it and its bags/pax from the reflighting
            data
         **/

         reflighting.uld_loaded(lSiteId, i.flight_id, i.uld_no);

      END IF;
   END LOOP;

   DELETE interim_ulds;
END;   
/
