/*****************************************************************************
*
* Filename         - $Workfile:   flight_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 flight table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*****************************************************************************/


/*
 * This trigger will perform the following tasks:-
 * - Return appropriate error codes when certain not-null
 *   constraints are violated.
 * - Generate a sequential ID for the new flight record.
 * - Supply the default loading strategy if none is provided.
 * - Extract and check the carrier alpha code if not supplied.
 */
create or replace trigger ti_flight
before insert on flight
for each row
DECLARE
  numrows       INTEGER;
  ldgstrat      CHAR(12);
  alpha_carrier carrier_lookup.alpha_code%TYPE;
  FOUND         NUMBER;
BEGIN

        /*
         * These three checks are already covered by NOT NULL constraints,
         * this code is retained for consistency of the error codes between
         * the Interbase and oracle versions.
         */
  IF (:new.flight_no IS NULL) THEN
                raise_application_error( -20029, 'Invalid flight no or date');
                :new.flight_no := '-';
  END IF;

  IF (:new.flight_date IS NULL) THEN
                raise_application_error( -20029, 'Invalid flight no or date');
                :new.flight_date := '-';
  END IF;

  IF (:new.departure_time IS NULL) THEN
                raise_application_error( -20030, 'Invalid flight dep time');
                :new.departure_time := SYSDATE;
  END IF;


        /*
         * Assign next sequential flight ID.
         */
  SELECT fltid_gen.NEXTVAL INTO :new.flight_id FROM dual;

  IF (:new.flight_strategy IS NULL) THEN
    SELECT rtrim(db_value)
      INTO ldgstrat
      FROM db_config
     WHERE db_item = 'ldg_strat_def';
                :new.flight_strategy := ldgstrat;
  END IF;

        /*
         * Extract the carrier code, check it is recognised, and
         * assign it to the field in the new record.
         */
  IF (:new.carrier_alpha_code IS NULL) THEN
                alpha_carrier := extract_carrier( :new.flight_no );

    SELECT COUNT(1)
      INTO FOUND
      FROM carrier_lookup
     WHERE alpha_code = alpha_carrier;

    IF (FOUND != 0) THEN
                        :new.carrier_alpha_code := alpha_carrier;
    END IF;

  END IF;
END;
/
/*
 * This trigger will place the newly created flight ID in an
 * interim table to be used in the creation of flight_status
 * records in the after statement trigger tias_flight.
 * This mechanism avoids a mutating/constraining table error.
 * The mechanism is also used for granting access to the flight.
 */
create or replace trigger tia_flight
after insert on flight
for each row
BEGIN
        /*
         * Save the new flight ID temporarily.
         */
  INSERT INTO interim (id, NUMERIC) VALUES (1, :new.flight_id);
  maintain_report_config.update_timed_report(:new.flight_id,
                                             :new.flight_no,
                                             :new.site_id);
END;
/
show errors
/*
 * Take any flight ID's inserted by the insert statement (stored
 * temporarily in the interim table) and create corresponding
 * flight_status records.
 * Also, grant access to the newly added flight(s) by calling
 * appropriate procedures.
 */
create or replace trigger tias_flight
after insert on flight
DECLARE

  CURSOR interim_flight_records IS
    SELECT NUMERIC FROM interim WHERE id = 1;

  flt_no   flight.flight_no%TYPE := NULL;
  flt_date flight.flight_date%TYPE := NULL;
  carrier  flight.carrier_alpha_code%TYPE := NULL;
  psiteid  flight.site_id%TYPE;

BEGIN

        /*
         * Create flight_status record.
         */
  INSERT INTO flight_status
    (flight_id)
    SELECT NUMERIC FROM interim WHERE id = 1;

        /*
         * We need to retrieve some of the flight's details
         * in order to grant appropriate access to it.
         */
  FOR interim_rec IN interim_flight_records LOOP

    SELECT flight_no, flight_date, carrier_alpha_code, site_id
      INTO flt_no, flt_date, carrier, psiteid
      FROM flight
     WHERE flight_id = interim_rec.NUMERIC;

                /*
                 * Grant flight_access and stats_access for the newly inserted flight.
                 * Firstly for contracted agents
                 */
    grant_contract_access(interim_rec.NUMERIC,
                          flt_no,
                          flt_date,
                          psiteid,
                          carrier);

                /*
                 * and secondly for delegated agents.
                 */
    grant_by_flight(interim_rec.NUMERIC,
                        flt_no,
                        flt_date,
      psiteid,
                    carrier);


   /* grant for super agents */
    add_flt_access2(interim_rec.NUMERIC);

  END LOOP;

        /*
         * remove the interim record.
         */
  DELETE FROM interim WHERE id = 1;

END;
/
show errors
/*
 * This trigger will perform the following tasks:-
 * - Cascade any update of the archive flag to the mba_flights table.
 * - Set the PD marker of any (non-hold) ULDs on this flight if the
 *   flight status changes to PD.
 * - Call procedure clear_pd_marker when the flight status changes from
 *   PD, to reset the ULDs' PD markers.
 * - Prevent updates to the flight_id.
 * - Update the carrier_alpha_code if the flight_no has changed.
 */
create or replace trigger tu_flight
before update on flight
for each row
DECLARE
  found         NUMBER;
  alpha_carrier carrier_lookup.alpha_code%TYPE;
  ignore        BOOLEAN := FALSE;
  lAutoLoadUlds site_config.db_value%TYPE;
  old_bpm_status system_cfg.bpm_status%TYPE;
  new_bpm_status system_cfg.bpm_status%TYPE;
BEGIN
  IF :OLD.process_name != :NEW.process_name AND :NEW.process_name = 'DCS' THEN
    /*A BSM has been received from a flight created via MWS or FIDS
      and flight is being modified*/
    SELECT bpm_status
      INTO old_bpm_status
      FROM system_cfg
     WHERE process_name = :OLD.process_name
       AND unit_name = :OLD.unit_name
       AND subunit_name = :OLD.subunit_name
       AND dcsid = :OLD.dcsid;
  
    SELECT bpm_status
      INTO new_bpm_status
      FROM system_cfg
     WHERE process_name = :NEW.process_name
       AND unit_name = :NEW.unit_name
       AND subunit_name = :NEW.subunit_name
       AND dcsid = :NEW.dcsid;
    /*If BPMs were enabled for the old interface (MWS or FIDS) then 
      mail_indiv entries would have accumulated which need to be 
      corrected so that gateway can process them.But this should be 
      done only if BPMs are enabled on gateway interface*/
    IF old_bpm_status = 'Y' AND new_bpm_status = 'Y' THEN
      mail_pack.correct_mail_indiv_bpms(p_flight_id        => :OLD.flight_id,
                                        p_old_process_name => :OLD.process_name,
                                        p_old_unit_name    => :OLD.unit_name,
                                        p_new_process_name => :NEW.process_name,
                                        p_new_unit_name    => :NEW.unit_name,
                                        p_new_dcsid        => :NEW.dcsid,
                                        p_new_subunit_name => :NEW.subunit_name);
    END IF;
  END IF;

  IF (:old.archive_flag != :new.archive_flag) THEN
      UPDATE mba_flights
      SET delete_marker = :new.archive_flag
      WHERE flight_id = :old.flight_id;
   END IF;

   IF (:old.flight_status != :new.flight_status)
   THEN
      IF (:new.flight_status = 'PD')
      THEN
         UPDATE ulds
         SET pd_marker = 'Y'
         WHERE flight_id = :old.flight_id
         AND   uld_no NOT IN (SELECT hold_no
                              FROM holds);
      END IF;

      IF (:old.flight_status = 'PD') THEN
                        clear_pd_marker(:old.flight_id);

         /**
         Reflighting module. When the flight status is changed back from PD
         Delete all of the data held in the reflighting tables for the flight
         **/

         BEGIN
            DELETE reflight_flight
            WHERE flight_id = :old.flight_id;
         EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
         END;

    END IF;

    IF (:new.flight_status = 'FX') THEN

      -- Flight has been cancelled. Carry out action depending
      -- on db_config CANCEL_ACTION setting
      -- Note: cancel_flight may insert INTERIM record

      cancel_flight(:new.flight_id, :new.site_id, ignore);

      IF (ignore = TRUE) THEN
        -- Procedure indicated that this change should
        -- be ignored.
        :new.flight_status := :old.flight_status;
      END IF;

    END IF;


  END IF;

        /*
         * We forbid updates to flight_id as flight_access
         * could not practically be maintained in this case.
         */
  IF (:new.flight_id != :old.flight_id) THEN
    raise_application_error(-20018,
                            'Non modifiable column cannot be modified.');
  END IF;

        /*
         * If the flight number is changing, we should update the
         * carrier code accordingly.
         */
   IF ( :new.flight_no != :old.flight_no )
   THEN
                /*
                 * Extract the carrier code, check it is recognised, and
                 * assign it to the field in the new record.
                 */
                alpha_carrier := extract_carrier( :new.flight_no );

      SELECT count(1)
      INTO found
      FROM carrier_lookup
      WHERE alpha_code = alpha_carrier;

    IF (FOUND != 0) THEN
                        :new.carrier_alpha_code := alpha_carrier;
    ELSE
                        :new.carrier_alpha_code := NULL;
    END IF;
  END IF;

  IF :new.flight_status != :old.flight_status THEN
           -- Flight status has changed

    IF (:old.flight_status IN ('FF', 'PD') AND
       :new.flight_status NOT IN ('FF', 'PD')) THEN
              -- Flight status was rolled back to a state prior to FF or PD,
              -- so invalidate all the valid hbmd's for this flight
         UPDATE hbmd
         SET status = 'INVALID'
         WHERE flight_id = :new.flight_id
         AND status != 'INVALID';
    END IF;

           -- Flight status has changed, so calculate the new open/close counts

    IF :new.flight_status = 'FO' AND :old.flight_status != '-' THEN

              -- Flight now open, so increment open count
              :new.open_count := :old.open_count + 1;

    END IF;

    IF :new.flight_status = 'FC' THEN

              -- Flight now closed, so increment closed count
              :new.close_count := :old.close_count + 1;

               -- Flight is closed, store data so that the HBMD can be created in an after trigger
               INSERT INTO hbmd_interim
               (flight_id, flight_no, flight_date, flight_sch_time)
               VALUES
        (:new.flight_id,
         :new.flight_no,
         :new.flight_date,
         :new.flight_sch_time);

           end if;

      IF (:new.flight_status = 'PD')
      THEN
         /**
            Reflighting Module.
            If the flight status has changed to PD the after statement trigger
            will copy any non loaded bags/pax to the reflighting tables.
         **/
         INSERT INTO interim_flight
         (flight_id,
          flight_no,
          flight_date,
          scheduled_time,
          carrier_alpha_code,
          site_id,
          departure_time)
         VALUES
         (:old.flight_id,
          :old.flight_no,
          :old.flight_date,
          :old.flight_sch_time,
          :old.carrier_alpha_code,
          :old.site_id,
          :old.departure_time);

      END IF;

  END IF;
END;
/
show errors
/*
 * After update trigger for flight table.
 * This trigger will check whether the Flight number, date, or carrier
 * have been changed, and if so it will maintain the flight's access by
 * adding an interim record which will be used by the after-statement
 * trigger tuas_flight to update the access record. This mechanism
 * is used to avoid mutating/constraining table errors.
 * The same mechanism is used to cascade flight status changes to related
 * flights.
 */
create or replace trigger tua_flight
after update on flight
for each row
DECLARE
  l_action      db_config.db_value%TYPE;

BEGIN

  /*
    * Set ready flag for timed_initited_report
    *
    ********************************************/
        IF( :new.flight_status IN ('PD', 'FC')) THEN
    maintain_report_config.check_timed_report(:old.flight_id,
                                              :new.flight_status);
        END IF;

        /*
         * If any of flight_no, flight_date, or carrier have changed...
         */
  IF (:new.flight_no != :old.flight_no OR
     :new.flight_date != :old.flight_date OR
     :new.carrier_alpha_code != :old.carrier_alpha_code) THEN

    INSERT INTO interim (id, NUMERIC) VALUES (4, :new.flight_id);

  END IF;

        /*
         * record flight status changes so they can be cascaded to related flights.
         */
  IF (:old.flight_status != :new.flight_status) THEN

    INSERT INTO interim
      (id, NUMERIC, text)
    VALUES
                (3, :new.flight_id, :new.flight_status);

          /*
           * Through flights that have been (un)departed need to be (un)transferred
           */
    IF (:new.flight_status = 'PD') THEN
               /*
                * Flight has been departed, if it is a through flight, transfer it
                */
      INSERT INTO interim
        (id, NUMERIC, text)
      VALUES
                  (5, :new.flight_id, :new.site_id);

    ELSIF (:old.flight_status = 'PD') THEN
               /*
                * Flight has been undeparted, if it is a through flight, untransfer it
                */
      INSERT INTO interim
        (id, NUMERIC, text)
      VALUES
                  (6, :new.flight_id, :new.site_id);

    END IF;

  END IF;

  IF (:new.flight_status = 'FO') AND (:old.flight_status = 'FX') THEN

     /**************************************************************************
      * Depending on the db_config value of CANCEL_ACTION we can determine     *
      * which course of action is appropriate:                                 *
      * If cancel action is REMOVE_ACCESS (and the flight is already cancelled *
      * then we must reverse this by re-creating the flight_access record(s).  *
      *************************************************************************/

      l_action := trim(config.get_value('CANCEL_ACTION'));


      IF l_action = 'REMOVE_ACCESS' THEN

         INSERT INTO interim
            (id, numeric, text)
         VALUES
            (8, :new.flight_id, :new.site_id);

      END IF;

  END IF;

END;
/
show errors
/*
 * After update statement trigger - this will read any interim
 * table entries which indicate updated flights, and maintain the
 * flight_access table by removing ALL access records for the old
 * flight, and recalculating access rights based on the modified data.
 * The same mechanism is used to cascade flight_status changes.
 */
create or replace trigger tuas_flight
after update on flight
DECLARE

  CURSOR interim_updated_flight_records IS
    SELECT NUMERIC FROM interim WHERE id = 4;

  CURSOR interim_updated_flight_status IS
    SELECT NUMERIC, text FROM interim WHERE id = 3;

  CURSOR interim_departed_flight IS
    SELECT NUMERIC, text FROM interim WHERE id = 5;

  CURSOR interim_undeparted_flight IS
    SELECT NUMERIC, text FROM interim WHERE id = 6;

  CURSOR interim_cancelled_flight IS
    SELECT NUMERIC FROM interim WHERE id = 7;

  CURSOR interim_reopened_canc_flight IS
    SELECT numeric, text FROM interim WHERE id = 8;

  CURSOR interim_hbmd IS
    SELECT flight_id, flight_no, flight_date, flight_sch_time
    FROM hbmd_interim;

   CURSOR lcurInterimFlight
   IS
      SELECT flight_id,
             flight_no,
             flight_date,
             scheduled_time,
             site_id,
             carrier_alpha_code,
             departure_time
      FROM interim_flight;

  CURSOR c_stopped_bags(p_flight_id IN flight.flight_id%TYPE) IS
    SELECT b.bag_tag, b.bag_tag2a, b.bag_tag2b, b.bag_tag3, p.pax_id
      FROM bag b, pax p
     WHERE authority_to_load_brs != 'N' AND b.pax_id = p.pax_id AND
           p.flight_id = p_flight_id;

  CURSOR c_routes(p_site_id IN site_info.site_id%TYPE,
                  p_alpha_code IN flight.carrier_alpha_code%TYPE,
                  p_dest IN flight_dests.flight_destination%TYPE) IS
    SELECT br.route_id
    FROM   bmm_routes br
    WHERE  br.site_id = p_site_id
      AND  br.carrier = trim(p_alpha_code)
      AND  br.destination = p_dest;

  CURSOR c_transferred_ulds(p_out_flight_id flight.flight_id%TYPE) IS
    SELECT   iu.result_uld_no,
             iu.result_uld_flight_id,
             iu.inbound_flight_id,
             si.airport_code
    FROM     inbound_uld iu,
             flight f,
             site_info si
    WHERE    f.flight_id = p_out_flight_id
      AND    iu.inbound_flight_id = f.next_leg_id
      AND    f.site_id = si.site_id
      AND    iu.inbound_uld_status = 'TRANSFER'
      AND    iu.result_uld_no IS NOT NULL
      AND    iu.result_uld_flight_id IS NOT NULL
      AND    iu.result_uld_status IN ('CREATED', 'AMENDED');

  TYPE flight_id_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE text_tab IS TABLE OF VARCHAR2(250);

  t_flight_id      flight_id_tab;
  t_text           text_tab;
  flt_no           flight.flight_no%TYPE := NULL;
  flt_date         flight.flight_date%TYPE := NULL;
  carrier          flight.carrier_alpha_code%TYPE := NULL;
  psiteid             flight.site_id%type;
  l_in_flt_id      flight.flight_id%TYPE := 0;
  l_through_flt_id flight.flight_id%TYPE := 0;
  l_user_id        tagtrak_users.user_id%TYPE;
  l_airport_code   site_info.airport_code%TYPE;
  l_location       location.location_code%TYPE;
  l_carrier_config carrier_site_config.config_value%TYPE;
  l_site_id        site_info.site_id%TYPE;
  l_alpha_code     flight.carrier_alpha_code%TYPE;
  l_dest           flight_dests.flight_destination%TYPE;
  l_flight_id      flight.flight_id%TYPE;
  l_msg_id         messages.message_id%TYPE;
  l_ret            NUMBER;
  l_auto_transfer_done BOOLEAN;
  l_route_count    NUMBER;
  l_count          NUMBER;
  i                NUMBER;

BEGIN

        /*
         * We need to select some flight details in order to grant
         * appropriate access.
         */
  FOR interim_rec IN interim_updated_flight_records LOOP

    SELECT flight_no, flight_date, carrier_alpha_code, site_id
      INTO flt_no, flt_date, carrier, psiteid
      FROM flight
      WHERE flight_id = interim_rec.numeric;

                /*
                 * Remove entirely any access records for the old flight.
                 */
    revoke_all_by_flight(interim_rec.NUMERIC);
                /*
                 * Grant appropriate access to the updated flight,
                 * for contracted agents
                 */
      grant_contract_access(interim_rec.numeric,
                            flt_no,
                            flt_date,
                            psiteid,
                            carrier);

                /*
                 * and for delegated agents.
                 */
                grant_by_flight(
                        interim_rec.numeric,
                        flt_no,
                        flt_date,
      psiteid,
                        carrier
                );

     /* grant to super_agents */
    add_flt_access2(interim_rec.NUMERIC);

  END LOOP;

        /*
         * Remove the interim records.
         */
  DELETE FROM interim WHERE id = 4;

        /*
         * Cascade flight status changes to related flights.
         */
  FOR i_rec IN interim_updated_flight_status LOOP

    DELETE FROM interim WHERE id = 3 AND NUMERIC = i_rec.NUMERIC;

      UPDATE flight
      SET flight_status = rtrim(i_rec.text)
      WHERE op_flight_id = i_rec.numeric;

      UPDATE flight
      SET flight_status = rtrim(i_rec.text)
     WHERE flight_id =
           (SELECT op_flight_id FROM flight WHERE flight_id = i_rec.NUMERIC);

  END LOOP;

  <<interim_record_loop5>>
  -- This section of processing can make changes to the FLIGHT table.
  -- This can cause this after statement trigger to be executed more than once.
  -- Any existing records will be processed more than once if a cursor is open
  -- on the INTERIM table (since the cursor is a snapshot).  To prevent this
  -- the data will be BULK COLLECTED into a table and the records removed from
  -- the INTERIM table before they are processed.
  IF (t_flight_id IS NOT NULL) THEN
    t_flight_id.DELETE;
  END IF;
  IF (t_text IS NOT NULL) THEN
    t_text.DELETE;
  END IF;

  OPEN interim_departed_flight;
  FETCH interim_departed_flight BULK COLLECT into t_flight_id, t_text;
  CLOSE interim_departed_flight;

  /* remove records */
  DELETE FROM interim WHERE id = 5;

  FOR i IN 1 .. t_flight_id.COUNT LOOP
    dbms_output.put_line('numeric value in after stmt' ||t_flight_id(i));

    l_auto_transfer_done := FALSE;
    IF (insert_inbound_data.is_configured_for_inbound) THEN

      /****************************************************************/
      /* Flight Departed Processing                                   */
      /****************************************************************/
      /* If this flight is departing to another airport on the system */
      /* then transfer all the Outbound flight details to an Inbound  */
      /* flight at the next station.                                  */
      /****************************************************************/
      IF insert_inbound_data.is_transit_airport(t_flight_id(i)) THEN
        insert_inbound_data.insert_all(t_flight_id(i));
      END IF;

      /****************************************************************/
      /* Through Flight Processing                                    */
      /****************************************************************/
      /* Identify the Outbound FlightId. If not found and no error    */
      /* raised then this is NOT a Through Flight. Hence no further   */
      /* processing is needed. If error then abort processing         */
      /****************************************************************/
      l_in_flt_id := inbound_flight_utils.match_inbound_outbound_flts(p_flight_id => t_flight_id(i));
      IF (NVL(l_in_flt_id, 0) > 0) THEN
        l_through_flt_id := inbound_flight_utils.match_inbound_outbound_flts(p_flight_id => l_in_flt_id);
        IF (NVL(l_through_flt_id, 0) > 0) THEN
           l_auto_transfer_done := TRUE;
           dbms_output.put_line('auto_copy_through: From ' ||l_in_flt_id || ' to ' ||l_through_flt_id);
           auto_copy_through(l_in_flt_id, l_through_flt_id);
        END IF;
      END IF;

    END IF;

    IF (config.get_value('BMM_CONFIG') = 'Y' AND NOT l_auto_transfer_done) THEN

      /****************************************************************/
      /* BMM Processing                                               */
      /****************************************************************/
      /* Create a BMM for this flight (reverse any existing BMM for   */
      /* this flight).  If this carrier is configured for AUTOMATIC   */
      /* send, then create a BMM mail record.                         */
      /****************************************************************/
      BEGIN

        SELECT csc.config_value,
               f.site_id,
               f.carrier_alpha_code,
               fd.flight_destination,
               f.flight_id
        INTO   l_carrier_config,
               l_site_id,
               l_alpha_code,
               l_dest,
               l_flight_id
        FROM   carrier_site_config csc,
               flight f,
               flight_dests fd
        WHERE  f.flight_id = t_flight_id(i)
          AND  f.flight_id = fd.flight_id
          AND  fd.dest_order = 1
          AND  csc.site_id = f.site_id
          AND  csc.alpha_code = f.carrier_alpha_code
          AND  csc.config_item = 'SEND_BMM';

      EXCEPTION
        WHEN no_data_found THEN
          l_carrier_config := 'DISABLED';
      END;

      -- Is Sending of BMMs valid for this carrier?
      IF (l_carrier_config <> 'DISABLED') THEN

        -- Reverse any previous BMMs
        UPDATE messages m
        SET    m.processed_state = 'R'
        WHERE  m.flight_id = l_flight_id
          AND  m.message_type = 'BMM'
          AND  m.message_direction = 'O'
          AND  m.processed_state = 'P';

        -- Construct and store the outgoing BMM
        l_msg_id := process_message.build_bmm(t_flight_id(i));

        -- Check the message was constructed
        IF (l_msg_id IS NOT NULL) THEN

          -- Count the number of routes for this BMM
          SELECT COUNT(*)
          INTO   l_route_count
          FROM   bmm_routes br
          WHERE  br.site_id = l_site_id
            AND  br.carrier = trim(l_alpha_code)
            AND  br.destination = l_dest;

          IF (l_route_count = 0) THEN

            -- Add Trace Message since no routes are defined
            process_message.add_message_trace(l_msg_id, NULL, 'MESSAGE_NO_ROUTE', l_dest, NULL, multi_site.get_auto_user( multi_site.get_airport_code(l_site_id)));

          ELSE

            -- If the carrier has automated sends create the outgoing mail messages
            IF (l_carrier_config = 'AUTOMATIC') THEN

              FOR r_route IN c_routes(l_site_id, l_alpha_code, l_dest) LOOP

                l_ret := store_bmm_mail(l_msg_id, r_route.route_id);

              END LOOP;

            END IF;

          END IF;

        END IF;

      END IF;

    END IF;

    -- Send BNS?
    --
    if Bag_Not_Seen.Send_For_Flight(t_flight_id(i)) and
	    not l_auto_transfer_done then

      -- Construct and store the outgoing BNS
      --
      Bag_Not_Seen.Build_Message(t_flight_id(i));

    end if;

    /****************************************************************/
    /* Do we need to error/abort processing if error returned       */
    /****************************************************************/

  END LOOP;

  <<interim_record_loop6>>
  -- This section of processing can make changes to the FLIGHT table.
  -- This can cause this after statement trigger to be executed more than once.
  -- Any existing records will be processed more than once if a cursor is open
  -- on the INTERIM table (since the cursor is a snapshot).  To prevent this
  -- the data will be BULK COLLECTED into a table and the records removed from
  -- the INTERIM table before they are processed.
  IF (t_flight_id IS NOT NULL) THEN
    t_flight_id.DELETE;
  END IF;
  IF (t_text IS NOT NULL) THEN
    t_text.DELETE;
  END IF;

  OPEN interim_undeparted_flight;
  FETCH interim_undeparted_flight BULK COLLECT into t_flight_id, t_text;
  CLOSE interim_undeparted_flight;

  /* remove record */
  DELETE FROM interim WHERE id = 6;

  FOR i IN 1 .. t_flight_id.COUNT LOOP
    dbms_output.put_line('numeric value in after stmt' ||t_flight_id(i));

    IF (insert_inbound_data.is_configured_for_inbound) THEN

      /****************************************************************/
      /* Reverse Transfer ULD Processing                              */
      /****************************************************************/
      /* Identify any transferred ULDs and reverse the operation      */
      /****************************************************************/
      FOR r_uld IN c_transferred_ulds(t_flight_id(i)) LOOP
        DECLARE
          retval NUMBER := 0;
        BEGIN
          Untransfer_ULD(r_uld.inbound_flight_id,
                         r_uld.result_uld_flight_id,
                         r_uld.result_uld_no,
                         multi_site.get_auto_user(r_uld.airport_code),
                         multi_site.get_auto_location(r_uld.airport_code),
                         retval);

        END;
      END LOOP;

      /****************************************************************/
      /* Reverse Through Flight Processing                            */
      /****************************************************************/
      /* Identify the Outbound FlightId. If not found and no error    */
      /* raised then this is NOT a Through Flight. Hence no further   */
      /* processing is needed. If error then abort processing         */
      /****************************************************************/
      l_in_flt_id := inbound_flight_utils.match_inbound_outbound_flts(p_flight_id => t_flight_id(i));
      IF (NVL(l_in_flt_id, 0) > 0) THEN
        l_through_flt_id := inbound_flight_utils.match_inbound_outbound_flts(p_flight_id => l_in_flt_id);
        IF (NVL(l_through_flt_id, 0) > 0) THEN
         dbms_output.put_line('unthrough_flight ' ||l_in_flt_id || ', ' ||l_through_flt_id);
         unthrough_flight(l_in_flt_id, l_through_flt_id);
        END IF;
      END IF;
      --The call to clear_pd_marker in TU_FLIGHT wouldn't have worked if 
      --ULDs were copied/transferred to next leg hence make a second call
      clear_pd_marker(t_flight_id(i));

      /****************************************************************/
      /* Reverse Inbound Records                                      */
      /****************************************************************/
      /* Set the status of any Inbound records created for this flight*/
      /* to REVERSED.                                                 */
      /****************************************************************/
      UPDATE inbound_bag ib
      SET    ib.result_bag_status = 'REVERSED'
      WHERE  ib.result_bag_status IN ('CREATED', 'AMENDED', 'UNPROCESSED')
        AND  ib.inbound_pax_id IN (SELECT ip.inbound_pax_id
                                   FROM   inbound_pax ip,
                                          flight f
                                   WHERE  ip.inbound_flight_id = f.next_leg_id
                                     AND  f.flight_id = t_flight_id(i));

      UPDATE inbound_pax ip
      SET    ip.result_pax_status = 'REVERSED'
      WHERE  ip.result_pax_status IN ('CREATED', 'AMENDED', 'UNPROCESSED')
        AND  ip.inbound_flight_id = (SELECT f.next_leg_id
                                     FROM   flight f
                                     WHERE  f.flight_id = t_flight_id(i));

      UPDATE inbound_uld iu
      SET    iu.result_uld_status = 'REVERSED'
      WHERE  iu.result_uld_status IN ('CREATED', 'AMENDED', 'UNPROCESSED')
        AND  iu.inbound_flight_id = (SELECT f.next_leg_id
                                     FROM   flight f
                                     WHERE  f.flight_id = t_flight_id(i));

    END IF;

    -- Remove the next leg id
    SELECT COUNT(*)
    INTO   l_count
    FROM   flight f
    WHERE  f.flight_id = t_flight_id(i)
      AND  f.next_leg_id IS NOT NULL;

    IF (l_count > 0) THEN

      UPDATE flight f
      SET    f.next_leg_id = NULL
      WHERE  f.flight_id = t_flight_id(i);

    END IF;

    -- Reverse any current BMMs
    UPDATE messages m
    SET    m.processed_state = 'R'
    WHERE  m.flight_id = t_flight_id(i)
      AND  m.message_type = 'BMM'
      AND  m.message_direction = 'O'
      AND  m.processed_state <> 'R';

  END LOOP;

  <<interim_record_loop7>>
  FOR cancelled_flight IN interim_cancelled_flight LOOP

    SELECT s.airport_code
      INTO l_airport_code
      FROM site_info s, flight f
     WHERE f.flight_id = cancelled_flight.NUMERIC AND s.site_id = f.site_id;

    l_user_id := multi_site.get_auto_user(l_airport_code);


    FOR l_bag_to_stop IN c_stopped_bags(cancelled_flight.NUMERIC) LOOP

      BEGIN

        add_bag_trace_record(l_user_id,
                             multi_site.get_auto_location(TRIM(l_airport_code)),
                             0, -- error_id, no error
                             l_bag_to_stop.bag_tag,
                             NULL,
                             l_bag_to_stop.bag_tag2a,
                             l_bag_to_stop.bag_tag2b,
                             l_bag_to_stop.bag_tag3,
                             'CANCEL STP',
                             '0', -- scanner_id, dummy value
                             cancelled_flight.NUMERIC,
                             l_bag_to_stop.pax_id);
      EXCEPTION
        WHEN OTHERS THEN
          -- Ignore any errors that occur when attempting to log
          NULL;
      END;
   END LOOP;



    UPDATE bag
       SET authority_to_load_brs = 'N'
     WHERE authority_to_load_brs != 'N' AND
           pax_id IN
           (SELECT pax_id
              FROM pax
             WHERE flight_id = cancelled_flight.NUMERIC);

    DELETE FROM interim
     WHERE id = 7 AND NUMERIC = cancelled_flight.NUMERIC;

  END LOOP interim_record_loop7;

  <<interim_record_loop8>>
     FOR reopened_canc_flight IN interim_reopened_canc_flight LOOP

        /*******************************************************
         * Grant flight_access and stats_access for the flight. *
         * Firstly for contracted agents.                       *
         ********************************************************/

         SELECT
            flight_no,
            flight_date,
            carrier_alpha_code,
            site_id
         INTO
            flt_no,
            flt_date,
            carrier,
            psiteid
         FROM
            flight
         WHERE
            flight_id = reopened_canc_flight.numeric;

         grant_contract_access( reopened_canc_flight.numeric, flt_no, flt_date, psiteid, carrier );

         /*******************************************************
          * and secondly for delegated agents.                  *
          *******************************************************/

         grant_by_flight(
                         reopened_canc_flight.numeric,
                         flt_no,
                         flt_date,
                         psiteid,
                         carrier
                        );

         /*******************************************************
          * grant to super_agents                               *
          *******************************************************/

         add_flt_access2(reopened_canc_flight.numeric);

     END LOOP interim_record_loop8;

     /*******************************************************
      * Remove the interim records.                         *
      *******************************************************/

     DELETE FROM interim
     WHERE id = 8;

  FOR hbmd IN interim_hbmd LOOP

    hbmd_create(hbmd.flight_id,
                hbmd.flight_no,
                hbmd.flight_date,
                hbmd.flight_sch_time);

    DELETE FROM hbmd_interim WHERE flight_id = hbmd.flight_id;

  END LOOP hbmd_interim;

  /**
      Reflighting Module
   **/

   FOR i IN lcurInterimFlight
   LOOP

     /**
        The flight status has changed to 'PD' so need to copy any missed bags and their assoiated
        flight/pax/uld/bag data to the re-flighting tables.
     **/
     reflighting.tu_flight_trig(i.site_id,
                                i.flight_id,
                                i.flight_no,
                                i.flight_date,
                                i.scheduled_time,
                                i.carrier_alpha_code,
                                i.departure_time);

   END LOOP;

   DELETE interim_flight;

END;
/
show errors
/*
 * Before delete row trigger - this will remove any
 * related records in the bag_trace table.
 */
create or replace trigger td_flight
before delete on flight
for each row
BEGIN

        /*
         * Delete all children in "bag_trace"
         */
  DELETE FROM bag_trace WHERE flight_id_scan = :old.flight_id;
END;
/
show errors

-- vim: sw=2
