rem ***************************************************************************
rem *                                                                         *
rem *             Copyright (c) Ultra Electronics 2006.                       *
rem *                           All rights reserved.                          *
rem *                                                                         *
rem ***************************************************************************
rem *                                                                         *
rem * Filename:              WT_TRANSACTIONS_trig_extras                      *
rem * Module Name:           Triggers for WT_TRANSACTIONS table               *
rem * Subsystem Name:        trigs                                            *
rem * Originating Author:    K. Oborn                                         *
rem * Design Reference:      N/A                                              *
rem *                                                                         *
rem ***************************************************************************
rem *                                                                         *
rem * Description:                                                            *
rem *                                                                         *
rem * Triggers for WT_TRANSACTIONS table. Processing is required to process   *
rem * incoming messages.                                                      *
rem *                                                                         *
rem ***************************************************************************
rem *                                                                         *
rem * Program Identification Array:                                           *
rem *                                                                         *
rem * "@(#)(WT_TRANSACTIONS_trig_extras 1.8 10:27:28 07/03/15)"
rem *                                                                         *
rem ***************************************************************************
rem *                                                                         *
rem * Modification History:                                                   *
rem *                                                                         *
rem Rel Date/Time      Proj/Id  CA Ref/Details Of Change                      *
rem --- -------------- -------- --------------------------------------------- *
rem 1.1 30.06.06/11:41 BARFL/KO  File WT_TRANSACTIONS_trig_extras (subsystem trigs) initially checked in
rem 1.2 19.09.06/10:59 BARFL/KO  CA/128 if a response is received to a BDO message then call procedure p19_process_bdo
rem 1.3 03.10.06/14:05 BARFL/CJA CA186 - added coding and decoding of IATA HTH Header
rem 1.4 04.10.06/15:33 BARFL/KO  CA/190 Capture any Oracle error returns during processing of response
rem 1.5 05.10.06/14:48 BARFL/KO  CA/195 Replace chr(30)with null in all responses
rem 1.6 10.10.06/15:59 BARFL/KO  CA/202 Modify aas send dah request flags
rem 1.7 09.11.06/16:06 BARFL/KO  CA/157 Process responses to alternative forward messages
rem 1.8 15.03.07/10:27 BARFL/KO  CA/296 Correct detection of error response to alternative forward messages
rem ***************************************************************************/

CREATE OR REPLACE TRIGGER ti_wt_transactions
BEFORE INSERT ON wt_transactions
FOR EACH ROW
BEGIN

   -- The MATIP HTH protocol requires that the WT message is encapsulated with
   -- the IATA HTH protocol. We use a (almost) static header for all transactions,
   -- which is defined in the WT_CONFIG table.
   -- We postfix this value to the message and add a chr(3) ETX to the end
   -- The value in the WT_CONFIG must end with a chr(2) STX so that the message
   -- is wrapped correctly.
   -- The header in WT_CONFIG contains a <carrier> token, which we need to
   -- replace with the appropriate carrier value

   DECLARE
      l_iata_hth wt_config.iata_hth_header%TYPE; -- IATA HTH value from WT_CONFIG

   BEGIN

      BEGIN
         SELECT iata_hth_header INTO l_iata_hth FROM WT_CONFIG WHERE site = :new.site;

      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            l_iata_hth := '';

      END;

      IF l_iata_hth IS NOT NULL THEN
         :new.wt_request :=  REPLACE(l_iata_hth,'<carrier>',RTRIM(:new.carrier))||:new.wt_request||chr(3);
      END IF;

   END;


END;
/

CREATE OR REPLACE TRIGGER tu_wt_transactions
BEFORE UPDATE ON wt_transactions
FOR EACH ROW
BEGIN

   -- OK the response from WorldTracer should have the message encapsulated in the IATA
   -- HTH protocol. We just want the body of the response which is surrounded by STX (chr(2))
   -- and ETX (chr(3)).

   DECLARE
      l_stx_pos INTEGER; -- Char pos of STX
      l_etx_pos INTEGER; -- Char pos of ETX

   BEGIN
      l_stx_pos := INSTR(:new.wt_response,chr(2));

      -- did we find an STX?
      IF l_stx_pos > 0 THEN

         l_etx_pos := INSTR(:new.wt_response,chr(3));

         -- did we find an ETX?
         IF l_etx_pos > 0 THEN

            -- STX and ETX found, extract the message body
            :new.wt_response := SUBSTR(:new.wt_response,l_stx_pos + 1, l_etx_pos - (l_stx_pos + 1));

         END IF;

      END IF;

   END;
   
   -- process incoming messages
   IF NVL(:new.wt_response, '?')!= NVL(:old.wt_response, '?') AND
      :new.wt_response IS NOT NULL THEN
      -- remove any chr(30) characters from the message
      :new.wt_response := REPLACE(:new.wt_response, CHR(30), NULL);

      BEGIN
         -- process individual message types
         IF :new.wt_code = 'FWD' THEN
            reflighting.process_fwd(:new.wt_response, :new.response_error);
         END IF;
      EXCEPTION
         WHEN OTHERS THEN
            -- an error has occurred processing the messages so store it
            -- in the response error field
            :new.response_error := SUBSTR(:new.response_error||' '||sqlerrm,1,250);
      END;
   END IF;

END;
/

CREATE OR REPLACE TRIGGER tia_wt_transactions
AFTER UPDATE ON wt_transactions
FOR EACH ROW
BEGIN

   IF ( :new.wt_response is not NULL and :old.bag_appn_row_id is not null) THEN
     update reflight_bag
        set WORLDTRACER_REF = :new.wt_response
        where APPN_ROW_ID = :old.bag_appn_row_id;
   END IF;

END;
/
