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

create or replace trigger ti_mail_indiv
before insert on mail_indiv
for each row
declare
  first_comma NUMBER;
  second_comma NUMBER;
  as_length NUMBER;
begin

	/*
	 * For each row inserted :
	 *  - Generate the next sequential indiv_msg_id
	 *  - Set the msg_time to today's date.
	 *
	 * For BPM and BMM messages
	 *  - Derive address stamp from message text
	 */

	select
		imsgid_gen.nextval
	into
		:new.indiv_msg_id
	from
		dual;

	:new.msg_time := SYSDATE;

	IF :new.msg_type IN ('BPM','BMM') THEN
		first_comma := InStr(:new.msg_text, ',', 1, 1);
		second_comma := InStr(:new.msg_text, ',', 1, 2);
		as_length := second_comma - first_comma - 1;
		:new.address_stamp := SubStr(:new.msg_text, first_comma+1, as_length);
	END IF;

	IF :new.msg_type = 'BNS' THEN
		first_comma := instr(:new.msg_text, ',');
		as_length := length(:new.msg_text) - first_comma;
		:new.address_stamp :=
			substr(:new.msg_text, first_comma+1, as_length);
	END IF;

	dbms_alert.signal( :new.msg_address, 'before insert' );

end;
/

CREATE OR REPLACE TRIGGER td_mail_indiv
BEFORE DELETE ON mail_indiv
FOR EACH ROW
DECLARE

  first_comma       NUMBER;
  second_comma      NUMBER;
  field_length      NUMBER;
  l_message_id      messages.message_id%TYPE;
  l_message_part_id message_parts.message_part_id%TYPE;
  l_dest            bmm_routes.destination%TYPE;
  l_route           bmm_routes.route_id%TYPE;
  l_trace_action    messages_trace.trace_action%TYPE;
  l_flight_id       flight.flight_id%TYPE;

BEGIN

  -- Only for BMM's
  IF (:OLD.msg_type = 'BMM') THEN

    -- Find the commas in the message text
    first_comma := InStr(:OLD.msg_text, ',', 1, 1);
    second_comma := InStr(:OLD.msg_text, ',', 1, 2);

    -- Only continue if we found them
    IF (first_comma <> 0 AND second_comma <> 0) THEN

      -- Get the Message Part ID
      field_length := first_comma - 1;
      l_message_part_id := TO_NUMBER(SUBSTR(:OLD.msg_text, 1, field_length));

      -- Get the Route ID
      l_route := TO_NUMBER(SUBSTR(:OLD.msg_text, second_comma + 1));

      -- Check we found the information we need
      IF (l_message_part_id > 0 AND l_route > 0) THEN

        IF (:OLD.send_status = 'Y') THEN

          l_trace_action := 'MESSAGE_SEND';

        ELSE

          l_trace_action := 'MESSAGE_SEND_FAIL';

        END IF;

        -- Get the rest of the information
        BEGIN

          SELECT m.message_id,
                 m.flight_id
          INTO   l_message_id,
                 l_flight_id
          FROM   messages m,
                 message_parts p
          WHERE  m.message_id = p.message_id
            AND  p.message_part_id = l_message_part_id;

          SELECT r.destination
          INTO   l_dest
          FROM   bmm_routes r
          WHERE  r.route_id = l_route;

          process_message.add_message_trace(l_message_id, l_message_part_id, l_trace_action,
                                            l_dest, l_route, multi_site.get_auto_user(multi_site.get_flight_loc(l_flight_id)));

        EXCEPTION
          WHEN no_data_found THEN
            NULL;
        END;

      END IF;

    END IF;

  END IF;

END;
/

