/*****************************************************************************
*
* Filename         - $Workfile:   pax_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 pax table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   pax_triggers.sql  $  $Revision:   1.6  $
*
* 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/pax_triggers.svl  $
* 
*    Rev 1.6   06 Jun 2000 09:56:34   tagtrak
*  
* 
*    Rev 1.5.1.1   22 Jun 1999 15:41:06   tagtrak
* OR:
* CR: 14043
* REV:
* Change to Archive and Backup so that when the eraseLive function is
* called, it deletes the child records first, parent records last, so
* not to take out any table locks.
* 
* Neil Bishop.
* 
*    Rev 1.5.1.0   17 May 1999 15:00:14   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.
* 
*    Rev 1.5   08 Apr 1999 15:24:36   tagtrak
* OR: 13206
* CR:  
* REV:
* Andrew Walker
* (branch build revision copied to trunk.)
* Neil Bishop
* 
*    Rev 1.4.1.0   25 Feb 1999 15:21:56   tagtrak
* OR: 13206
* CR:  
* REV:
* Andrew Walker
* 
*    Rev 1.4   25 Sep 1998 10:07:10   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merged Vienna (0.3.1E) and Core Product (0.2.5E) -
* branch ATS revision moved to trunk.
* Andrew Walker
* 
*    Rev 1.3.3.0   18 Aug 1998 12:07:50   tagtrak
* OR: 12453
* CR: 12453
* REV:
* Disallow changing the pax status to 'N'.
* Andrew Walker
* 
* 
*    Rev 1.3.1.0   04 Jun 1998 15:31:18   tagtrak
* OR:
* CR: 12098
* REV:
* Vienna ModIFications
* Andrew Walker
* 
*    Rev 1.3   02 Apr 1998 19:46:26   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.2   26 Feb 1998 11:42:48   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
* 
*    Rev 1.1   15 Jan 1998 13:39:12   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical issues.
* 
* 
*    Rev 1.0   22 Oct 1997 16:38:40   tagtrak
* OR:
* CR:
* REV:
* Initial check-in
*
****************************************************************************/

CREATE OR REPLACE TRIGGER ti_pax
BEFORE INSERT ON pax
FOR EACH ROW
DECLARE
   numrows INTEGER;
   updrows INTEGER;
   duprows INTEGER;
   dm      INTEGER;

   CURSOR pax_delete_marker ( fid number, booked_fid number, pax_seq number ) IS
      SELECT delete_marker
        FROM pax
       WHERE flight_id = fid AND
             booked_flight_id = booked_fid AND
             pax_sequence_no = pax_seq;
BEGIN
   IF :new.departure_airport_code is null THEN
     :new.departure_airport_code := multi_site.get_flight_loc(:new.flight_id);
   END IF;

   /*
    * Before each insert on the pax table :
    * - Initialise variables
    * - Open Cursor
    * - Loop around cursor and count the number of records
    * - Close Cursor
    * - Check for a duplicate key (unique)
    * - Get the next pax_id
    * - Insert record into fs_events
    */
         
   numrows := 0;
   duprows := 0;
   updrows := 0;

   IF (:new.booked_flight_id IS NULL) then
      :new.booked_flight_id := :new.flight_id;
   END IF;
      
   OPEN pax_delete_marker
           (:new.flight_id,
            :new.booked_flight_id,
            :new.pax_sequence_no);
   
   LOOP
      FETCH pax_delete_marker INTO dm;
      EXIT WHEN pax_delete_marker%NOTFOUND;
      numrows := numrows + 1;
      IF ( dm = 0 ) THEN
         duprows := duprows + 1;
      ELSE
         updrows := updrows + 1;
      END IF;
      
   END LOOP;
   CLOSE pax_delete_marker;
   
   IF (duprows > 0) THEN
      raise_application_error( -20027, 'Attempt to insert a Duplicate Value into Unique Key');
   END IF;
   IF (numrows > 1) THEN
      raise_application_error( -20027, 'Attempt to insert a Duplicate Value into Unique Key');
   END IF;
   IF (updrows > 0) THEN
      raise_application_error( -20028, 'This record is really the same as an existing one, update it.');
   END IF;
   
   SELECT paxid_gen.nextval
     INTO :new.pax_id
     FROM dual;
   
   IF (:new.pax_status = 'B') THEN
      INSERT
        INTO fs_events
             (flight_id, delta_chk_pax, delta_bdd_pax)
      VALUES (:new.flight_id, 1, 1);
   ELSE
      INSERT
        INTO fs_events
             (flight_id, delta_chk_pax, delta_sht_pax)
      VALUES (:new.flight_id, 1, 1);
   END IF;
END;
/
CREATE OR REPLACE TRIGGER tu_pax
BEFORE UPDATE ON pax
FOR EACH ROW
DECLARE

   numrows INTEGER;
   
   depair      pax.departure_airport_code%type := null;
   i           BINARY_INTEGER := 0;
   
   /* For Bulk Bind */
   TYPE l_scanner_id    IS TABLE OF bag.scanner_id%TYPE;
   TYPE l_bag_tag1      IS TABLE OF bag.bag_tag1%TYPE;
   TYPE l_bag_tag2a     IS TABLE OF bag.bag_tag2a%TYPE;
   TYPE l_bag_tag2b     IS TABLE OF bag.bag_tag2b%TYPE;
   TYPE l_bag_tag3      IS TABLE OF bag.bag_tag3%TYPE;
   TYPE l_loaded_status IS TABLE OF bag.loaded_status%TYPE;
   TYPE l_pax_id        IS TABLE OF bag.pax_id%TYPE;
   TYPE l_uld_no        IS TABLE OF bag.uld_no%TYPE;
   TYPE l_bag_tag       IS TABLE OF bag.bag_tag%TYPE;
   
   t_scanner_id    l_scanner_id;
   t_bag_tag1      l_bag_tag1;
   t_bag_tag2a     l_bag_tag2a;
   t_bag_tag2b     l_bag_tag2b;
   t_bag_tag3      l_bag_tag3;
   t_loaded_status l_loaded_status;
   t_pax_id        l_pax_id;
   t_uld_no        l_uld_no;
   t_bag_tag       l_bag_tag;
    
BEGIN

   /*
    * OR. 12453
    * IF the old.pax_status = 'C' and new.pax_status = 'N' then
    *  new_pax_status := 'C'
    *
    * IF the old.pax_status = 'B' and new.pax_status = 'N' then
    *  new_pax_status := 'B'
    */

/*
   Section removed to allow non-through-checked pax to exist...

   IF (:old.pax_status = 'C' and :new.pax_status = 'N') THEN
      :new.pax_status := 'C';
   END IF;

   IF (:old.pax_status = 'B' and :new.pax_status = 'N') THEN
      :new.pax_status := 'B';
   END IF;
*/

   /*
    * Before each update on the pax table :
    * - Insert the relevant details into the fs_events table
    */

   IF (:old.delete_marker = 0 AND :new.delete_marker = 1) THEN
      
      IF (:old.pax_status = 'B') THEN
         INSERT
           INTO fs_events
                (flight_id, delta_chk_pax)
         VALUES (:old.flight_id, -1);
      ELSE
         INSERT
           INTO fs_events
                (flight_id, delta_chk_pax, delta_sht_pax)
         VALUES (:old.flight_id, -1, -1);
      END IF;

   ELSIF (:old.delete_marker = 1 AND :new.delete_marker = 0) THEN
      
      IF (:new.pax_status = 'B') THEN
         INSERT
           INTO fs_events
                (flight_id, delta_chk_pax)
         VALUES (:old.flight_id, 1);
      ELSE
         INSERT
           INTO fs_events
                (flight_id, delta_chk_pax, delta_sht_pax)
         VALUES (:old.flight_id, 1, 1);
      END IF;
   ELSIF (:new.pax_status != :old.pax_status) THEN
      IF (:new.pax_status = 'B') THEN
         INSERT
           INTO fs_events
                (flight_id, delta_bdd_pax, delta_sht_pax)
         VALUES (:old.flight_id, 1, -1);
      ELSIF (:old.pax_status = 'B') THEN
         IF (:old.delete_marker = 0) THEN
            INSERT
              INTO fs_events
                   (flight_id, delta_bdd_pax, delta_sht_pax)
            VALUES (:old.flight_id, -1, 1);
         ELSE
            INSERT
              INTO fs_events
                   (flight_id, delta_bdd_pax)
            VALUES (:old.flight_id, -1);
         END IF;
      END IF;
   END IF;
   
   
   /* check if the pax_status is changed */
   IF (:old.pax_status <> :new.pax_status) THEN
   
   depair := get_offset.find_airport_code(:old.flight_id,  :old.pax_id);
   
   SELECT nvl(scanner_id,0),
          bag_tag,
          bag_tag1,
          bag_tag2a,
          bag_tag2b,
          bag_tag3,
          loaded_status,
          pax_id,
          uld_no
   BULK COLLECT INTO t_scanner_id,
                     t_bag_tag,
                     t_bag_tag1,
                     t_bag_tag2a,
                     t_bag_tag2b,
                     t_bag_tag3,
                     t_loaded_status,
                     t_pax_id,
                     t_uld_no
   FROM bag
   WHERE bag.pax_id        = :new.pax_id
   AND   bag.loaded_status = 'Y'
   AND   bag.delete_marker = 0;
   
   /* If there any loaded bags */
   IF  (t_pax_id.COUNT > 0) THEN
       
       FOR i in t_pax_id.FIRST..t_pax_id.LAST LOOP
       IF (:new.pax_status = 'S') THEN
       
       /* Set alert on all loaded bags */
           
          add_bag_alert_trace_record (
                                      null, -- user id
                                      null, -- location
                                      0, -- error id
                                      t_bag_tag(i),
                                      t_bag_tag1(i),
                                      t_bag_tag2a(i),
                                      t_bag_tag2b(i),
                                      t_bag_tag3(i),
                                      'ALERT',
                                      t_scanner_id(i),
                                      :new.flight_id,
                                      t_pax_id(i),
                                      t_uld_no(i));

        	INSERT INTO bag_alert (
          	pax_id,
          	flight_id,
          	bag_tag,
          	bag_tag1,
          	bag_tag2a,
          	bag_tag2b,
          	bag_tag3,
          	no_warnings,
          	last_time_stamp,
          	alert_on_action
        	) VALUES (
          	t_pax_id(i),
          	:new.flight_id,
          	t_bag_tag(i),
          	t_bag_tag1(i),
          	t_bag_tag2a(i),
          	t_bag_tag2b(i),
          	t_bag_tag3(i),
          	1,
          	SYSDATE + get_offset.airport_code(depair),
          	'UNLOAD'
        	);
         
       ELSE
      
       /* Clear Alert */
          add_bag_alert_trace_record ( null, -- user id
                                       null, -- location
                                       0, -- error id
                                       t_bag_tag(i),
                                       t_bag_tag1(i),
                                       t_bag_tag2a(i),
                                       t_bag_tag2b(i),
                                       t_bag_tag3(i),
                                       'CLR ALERT',
                                        t_scanner_id(i),
                                        :old.flight_id,
                                        :old.pax_id,
                                        t_uld_no(i));

				delete from
					bag_alert
				where
					bag_tag = t_bag_tag(i) and
					pax_id = :old.pax_id and
					alert_on_action = 'LOAD';
           /* Clear Alert on all loaded bags */
    
       END IF;
       END LOOP;
   END IF;
  END IF;
   
END;
/
