CREATE OR REPLACE TRIGGER ARCHIVE_SUB_RULES_ABS
BEFORE INSERT OR UPDATE OR DELETE ON ARCHIVE_SUB_RULES
BEGIN
   -- Trigger generated by make_trigs (Tue Jan 16 11:17:54 NFT 2007 on zrhsup by tagtrak)

   -- Perform before statement checks
   pkg73_notify.p73_before_statement_brs;
END;
/
CREATE OR REPLACE TRIGGER ARCHIVE_SUB_RULES_IBR
BEFORE INSERT ON ARCHIVE_SUB_RULES
FOR EACH ROW
BEGIN
   -- Trigger generated by make_trigs (Tue Jan 16 11:17:54 NFT 2007 on zrhsup by tagtrak)

   IF pkg73_row_header.v_source IS NULL AND
      ( :new.appn_row_id IS NOT NULL OR :new.row_create_time IS NOT NULL OR
        :new.row_date_time IS NOT NULL ) THEN
      -- Database shell can only set APPN_ROW_ID, ROW_CREATE_TIME, ROW_DATE_TIME
      p73_raise_appn_error(735901,
         'Cannot set field', 'APPN_ROW_ID, ROW_CREATE_TIME, ROW_DATE_TIME',
         FALSE, :new.system_id);
   END IF;
   IF :new.row_create_time IS NULL THEN
      -- Set up the the creation timestamp if not already set
      :new.row_create_time := pkg73_get_timedate.f73_get_utc_time;
   END IF;
   IF :new.row_date_time IS NULL OR pkg73_notify.v_timestamp_flag THEN
      -- Set up modification timestamp if not already set
      :new.row_date_time := :new.row_create_time;
   END IF;

   IF pkg73_notify.v_format_flag THEN
      -- Field formatting/knock-ons

      IF pkg73_row_header.v_source IS NOT NULL THEN
         -- Insert from database shell, check if CUSER, MUSER, MTERM being inserted
         IF :new.muser IS NULL THEN
            :new.muser := pkg73_row_header.v_muser;
         END IF;
         IF :new.cuser IS NULL THEN
            :new.cuser := :new.muser;
         END IF;
         IF :new.mterm IS NULL THEN
            :new.mterm := pkg73_row_header.v_mterm;
         END IF;
      ELSE
         -- Insert NOT from database shell, force CUSER, MUSER and MTERM
         :new.muser := pkg73_row_header.v_muser;
         :new.cuser := :new.muser;
         :new.mterm := pkg73_row_header.v_mterm;
      END IF;

      IF :new.appn_row_id IS NULL THEN
         -- Set up the unique value for APPN_ROW_ID
         SELECT global_ari_seq.nextval INTO :new.appn_row_id FROM dual;
      END IF;

      -- Set source to indicate whether knock on insert or not
      :new.source := NVL(pkg73_row_header.v_knock_on,pkg73_row_header.v_source);

      IF :new.to_be_archived IS NOT NULL AND NVL(:new.source,'?') != 'ARCHIVE' THEN
         -- Archive can only set TO_BE_ARCHIVED
         p73_raise_appn_error(735901, 'Cannot set field', 'TO_BE_ARCHIVED',
            FALSE, :new.system_id);
      END IF;

      -- Date range checks
      pkg73_data_validation.p73_check_date(:new.last_archived, 'LAST_ARCHIVED', :new.system_id);

   END IF; -- pkg73_notify.v_format_flag

   -- Ensure that the system_id value is set to 0
   :new.system_id := 0;

   IF pkg73_notify.v_notify_flag THEN
      -- Notification is required so assemble
      pkg73_notify.v_message := 'TL INSERT ARCHIVE_SUB_RULES'
         ||' ROW_CREATE_TIME!'''||TO_CHAR(:new.row_create_time,'YYYYMMDD HH24MISS')||''''
         ||' ROW_DATE_TIME!'''||TO_CHAR(:new.row_date_time,'YYYYMMDD HH24MISS')||''''
         ||' CUSER!'''||REPLACE(:new.cuser,chr(39),chr(39)||chr(39))||''''
         ||' MUSER!'''||REPLACE(:new.muser,chr(39),chr(39)||chr(39))||''''
         ||' MTERM!'''||REPLACE(:new.mterm,chr(39),chr(39)||chr(39))||''''
         ||' SOURCE!'''||REPLACE(:new.source,chr(39),chr(39)||chr(39))||''''
         ||' TO_BE_ARCHIVED!'''||REPLACE(:new.to_be_archived,chr(39),chr(39)||chr(39))||''''
         ||' APPN_ROW_ID!'''||:new.appn_row_id||''''
         ||' SYSTEM_ID!'''||:new.system_id||''''
         ||' ARCHIVE_SEQUENCE!'''||:new.archive_sequence||''''
         ||' ARCHIVE_TABLE!'''||REPLACE(:new.archive_table,chr(39),chr(39)||chr(39))||''''
         ||' ARCHIVE_SUB_TABLE!'''||REPLACE(:new.archive_sub_table,chr(39),chr(39)||chr(39))||''''
         ||' ARCHIVE_SUB_COLUMN!'''||REPLACE(:new.archive_sub_column,chr(39),chr(39)||chr(39))||''''
         ||' ARCHIVE_DEST_TABLE!'''||REPLACE(:new.archive_dest_table,chr(39),chr(39)||chr(39))||''''
         ||' ARCHIVE_SUB_QUERY!'''||REPLACE(:new.archive_sub_query,chr(39),chr(39)||chr(39))||''''
         ||' ARCHIVE_PROCEDURE!'''||REPLACE(:new.archive_procedure,chr(39),chr(39)||chr(39))||'''';
      pkg73_notify.p73_sendmessage; -- Send notification
   END IF;
   IF pkg73_notify.v_timestamp_flag THEN
      -- Save row_date_time for full standby update in After Statement Trigger
      pkg73_row_header.v_row_date_time := :new.row_date_time;
   END IF;
END;
/
CREATE OR REPLACE TRIGGER ARCHIVE_SUB_RULES_UBR
BEFORE UPDATE ON ARCHIVE_SUB_RULES
FOR EACH ROW
DECLARE
l_muser VARCHAR2(7) := ' MUSER ';
l_mterm VARCHAR2(7) := ' MTERM ';
l_source VARCHAR2(8) := ' SOURCE ';
l_to_be_archived VARCHAR2(16) := ' TO_BE_ARCHIVED ';
l_archive_sequence VARCHAR2(18) := ' ARCHIVE_SEQUENCE ';
l_archive_table VARCHAR2(15) := ' ARCHIVE_TABLE ';
l_archive_sub_table VARCHAR2(19) := ' ARCHIVE_SUB_TABLE ';
l_archive_sub_column VARCHAR2(20) := ' ARCHIVE_SUB_COLUMN ';
l_archive_dest_table VARCHAR2(20) := ' ARCHIVE_DEST_TABLE ';
l_archive_sub_query VARCHAR2(19) := ' ARCHIVE_SUB_QUERY ';
l_archive_procedure VARCHAR2(19) := ' ARCHIVE_PROCEDURE ';
BEGIN
   -- Trigger generated by make_trigs (Tue Jan 16 11:17:55 NFT 2007 on zrhsup by tagtrak)

   IF UPDATING('APPN_ROW_ID') OR UPDATING('CUSER') OR
      UPDATING('ROW_CREATE_TIME') OR UPDATING('SYSTEM_ID') THEN
      -- Reject standard read only column updates
      p73_raise_appn_error(735902,
         'Cannot update field', 'APPN_ROW_ID, CUSER, ROW_CREATE_TIME, SYSTEM_ID',
         FALSE, :new.system_id);
   END IF;
   IF UPDATING('ROW_DATE_TIME') AND pkg73_row_header.v_source IS NULL THEN
      -- Reject ROW_DATE_TIME non database shell updates
      p73_raise_appn_error(735902,
         'Cannot update field', 'ROW_DATE_TIME',
         FALSE, :new.system_id);
   END IF;
   IF :new.row_date_time IS NULL OR pkg73_notify.v_timestamp_flag THEN
      -- Set up timestamp
      :new.row_date_time := pkg73_get_timedate.f73_get_utc_time;
   END IF;

   IF pkg73_notify.v_format_flag THEN
      -- Field formatting/knock-ons

      IF pkg73_row_header.v_source IS NOT NULL THEN
         -- Update from database shell, check if MUSER, MTERM being updated
         IF NOT UPDATING('MUSER') OR :new.muser IS NULL THEN
            -- Set up modified user if not set
            :new.muser := pkg73_row_header.v_muser;
         END IF;
         IF NOT UPDATING('MTERM') OR :new.mterm IS NULL THEN
            -- Set up modified terminal if not set
            :new.mterm := pkg73_row_header.v_mterm;
         END IF;
      ELSE
         -- Update NOT from database shell, force MUSER and MTERM
         :new.muser := pkg73_row_header.v_muser;
         :new.mterm := pkg73_row_header.v_mterm;
      END IF;

      -- Set source to indicate whether knock on update or not
      :new.source := NVL(pkg73_row_header.v_knock_on,pkg73_row_header.v_source);

      IF UPDATING('TO_BE_ARCHIVED') AND NVL(:new.source,'?') != 'ARCHIVE' THEN
         -- Archive can only set TO_BE_ARCHIVED
         p73_raise_appn_error(735902,
            'Cannot update field', 'TO_BE_ARCHIVED',
            FALSE, :new.system_id);
      END IF;

      -- Set up notification column changes

      IF NVL(:new.muser,'?') != NVL(:old.muser,'?') THEN
         l_muser := ' MUSER!';
      END IF;
      IF NVL(:new.mterm,'?') != NVL(:old.mterm,'?') THEN
         l_mterm := ' MTERM!';
      END IF;
      IF NVL(:new.source,'?') != NVL(:old.source,'?') THEN
         l_source := ' SOURCE!';
      END IF;
      IF NVL(:new.to_be_archived,'?') != NVL(:old.to_be_archived,'?') THEN
         l_to_be_archived := ' TO_BE_ARCHIVED!';
      END IF;
      IF :new.archive_sequence != :old.archive_sequence THEN
         l_archive_sequence := ' ARCHIVE_SEQUENCE!';
      END IF;
      IF :new.archive_table != :old.archive_table THEN
         l_archive_table := ' ARCHIVE_TABLE!';
      END IF;
      IF :new.archive_sub_table != :old.archive_sub_table THEN
         l_archive_sub_table := ' ARCHIVE_SUB_TABLE!';
      END IF;
      IF :new.archive_sub_column != :old.archive_sub_column THEN
         l_archive_sub_column := ' ARCHIVE_SUB_COLUMN!';
      END IF;
      IF :new.archive_dest_table != :old.archive_dest_table THEN
         l_archive_dest_table := ' ARCHIVE_DEST_TABLE!';
      END IF;
      IF NVL(:new.archive_sub_query,'?') != NVL(:old.archive_sub_query,'?') THEN
         l_archive_sub_query := ' ARCHIVE_SUB_QUERY!';
      END IF;
      IF :new.archive_procedure != :old.archive_procedure THEN
         l_archive_procedure := ' ARCHIVE_PROCEDURE!';
      END IF;
   END IF; -- pkg73_notify.v_format_flag

   IF pkg73_notify.v_notify_flag THEN
      -- Notification is required so assemble
      pkg73_notify.v_message := 'TL UPDATE ARCHIVE_SUB_RULES'
         ||' ROW_CREATE_TIME '||''''||TO_CHAR(:new.row_create_time,'YYYYMMDD HH24MISS')||''''
         ||' ROW_DATE_TIME!'||''''||TO_CHAR(:new.row_date_time,'YYYYMMDD HH24MISS')||''''
         ||' CUSER '||''''||REPLACE(:new.cuser,chr(39),chr(39)||chr(39))||''''
         ||l_muser||''''||REPLACE(:new.muser,chr(39),chr(39)||chr(39))||''''
         ||l_mterm||''''||REPLACE(:new.mterm,chr(39),chr(39)||chr(39))||''''
         ||l_source||''''||REPLACE(:new.source,chr(39),chr(39)||chr(39))||''''
         ||l_to_be_archived||''''||REPLACE(:new.to_be_archived,chr(39),chr(39)||chr(39))||''''
         ||' APPN_ROW_ID '||''''||:new.appn_row_id||''''
         ||' SYSTEM_ID '||''''||:new.system_id||''''
         ||l_archive_sequence||''''||:new.archive_sequence||''''
         ||l_archive_table||''''||REPLACE(:new.archive_table,chr(39),chr(39)||chr(39))||''''
         ||l_archive_sub_table||''''||REPLACE(:new.archive_sub_table,chr(39),chr(39)||chr(39))||''''
         ||l_archive_sub_column||''''||REPLACE(:new.archive_sub_column,chr(39),chr(39)||chr(39))||''''
         ||l_archive_dest_table||''''||REPLACE(:new.archive_dest_table,chr(39),chr(39)||chr(39))||''''
         ||l_archive_sub_query||''''||REPLACE(:new.archive_sub_query,chr(39),chr(39)||chr(39))||''''
         ||l_archive_procedure||''''||REPLACE(:new.archive_procedure,chr(39),chr(39)||chr(39))||'''';
      pkg73_notify.p73_sendmessage; -- Send notification
   END IF;
   IF pkg73_notify.v_timestamp_flag THEN
      -- Save row_date_time for full standby update in After Statement Trigger
      pkg73_row_header.v_row_date_time := :new.row_date_time;
   END IF;
END;
/
CREATE OR REPLACE TRIGGER ARCHIVE_SUB_RULES_DBR
BEFORE DELETE ON ARCHIVE_SUB_RULES
FOR EACH ROW
BEGIN
   -- Trigger generated by make_trigs (Tue Jan 16 11:17:56 NFT 2007 on zrhsup by tagtrak)

   IF pkg73_notify.v_notify_flag THEN
      -- Notification is required so assemble
      pkg73_notify.v_message := 'TL DELETE ARCHIVE_SUB_RULES'
         ||' ROW_CREATE_TIME '''||TO_CHAR(:old.row_create_time,'YYYYMMDD HH24MISS')||''''
         ||' ROW_DATE_TIME!'''||TO_CHAR(pkg73_get_timedate.f73_get_utc_time,'YYYYMMDD HH24MISS')||''''
         ||' CUSER '''||REPLACE(:old.cuser,chr(39),chr(39)||chr(39))||''''
         ||' MUSER!'''||REPLACE(pkg73_row_header.v_muser,chr(39),chr(39)||chr(39))||''''
         ||' MTERM!'''||REPLACE(pkg73_row_header.v_mterm,chr(39),chr(39)||chr(39))||''''
         ||' SOURCE!'''||REPLACE(NVL(pkg73_row_header.v_knock_on,pkg73_row_header.v_source),chr(39),chr(39)||chr(39))||''''
         ||' TO_BE_ARCHIVED '''||REPLACE(:old.to_be_archived,chr(39),chr(39)||chr(39))||''''
         ||' APPN_ROW_ID '''||:old.appn_row_id||''''
         ||' SYSTEM_ID '''||:old.system_id||''''
         ||' ARCHIVE_SEQUENCE '''||:old.archive_sequence||''''
         ||' ARCHIVE_TABLE '''||REPLACE(:old.archive_table,chr(39),chr(39)||chr(39))||''''
         ||' ARCHIVE_SUB_TABLE '''||REPLACE(:old.archive_sub_table,chr(39),chr(39)||chr(39))||''''
         ||' ARCHIVE_SUB_COLUMN '''||REPLACE(:old.archive_sub_column,chr(39),chr(39)||chr(39))||''''
         ||' ARCHIVE_DEST_TABLE '''||REPLACE(:old.archive_dest_table,chr(39),chr(39)||chr(39))||''''
         ||' ARCHIVE_SUB_QUERY '''||REPLACE(:old.archive_sub_query,chr(39),chr(39)||chr(39))||''''
         ||' ARCHIVE_PROCEDURE '''||REPLACE(:old.archive_procedure,chr(39),chr(39)||chr(39))||''''
         ||' ARCHIVE_STATUS '''||REPLACE(:old.archive_status,chr(39),chr(39)||chr(39))||''''
         ||' LAST_ARCHIVED '''||TO_CHAR(:old.last_archived,'YYYYMMDD HH24MISS')||'''';
      pkg73_notify.p73_sendmessage; -- Send notification
   END IF;
   IF pkg73_notify.v_timestamp_flag THEN
      -- Save row_date_time for full standby update in After Statement Trigger
      pkg73_row_header.v_row_date_time := pkg73_get_timedate.f73_get_utc_time;
   END IF;
END;
/
CREATE OR REPLACE TRIGGER ARCHIVE_SUB_RULES_AAS
AFTER INSERT OR UPDATE OR DELETE ON ARCHIVE_SUB_RULES
BEGIN
   -- Trigger generated by make_trigs (Tue Jan 16 11:17:57 NFT 2007 on zrhsup by tagtrak)

   IF pkg73_notify.v_timestamp_flag AND
      pkg73_row_header.v_row_date_time IS NOT NULL THEN
      -- Only update timestamp (with last row amendment) if something updated
      UPDATE fsu_tabs
         SET timestamp = pkg73_row_header.v_row_date_time
            WHERE table_name = 'ARCHIVE_SUB_RULES';
   END IF;
END;
/
