Merge branch 'DEVELOP' of ssh://git.eclipse.org:29418/openk-usermodules/org.eclipse.openk-usermodules.gridFailureInformation.backend into DEVELOP
diff --git a/gfsBackendService/deploy/db/oracle/CREATE_GFI_DB_ORACLE.sql b/gfsBackendService/deploy/db/oracle/CREATE_GFI_DB_ORACLE.sql
new file mode 100644
index 0000000..52d0226
--- /dev/null
+++ b/gfsBackendService/deploy/db/oracle/CREATE_GFI_DB_ORACLE.sql
@@ -0,0 +1,950 @@
+
+-----------------------------------------------------------------------------------
+-- *******************************************************************************
+-- * Copyright (c) 2019 Contributors to the Eclipse Foundation
+-- *
+-- * See the NOTICE file(s) distributed with this work for additional
+-- * information regarding copyright ownership.
+-- *
+-- * This program and the accompanying materials are made available under the
+-- * terms of the Eclipse Public License v. 2.0 which is available at
+-- * http://www.eclipse.org/legal/epl-2.0.
+-- *
+-- * SPDX-License-Identifier: EPL-2.0
+-- *******************************************************************************
+-----------------------------------------------------------------------------------
+
+
+-- ---------------------------------------------
+-- TABLE VERSION
+-- ---------------------------------------------
+CREATE TABLE GFI_VERSION
+(
+ ID integer NOT NULL,
+ VERSION VARCHAR2(50) NOT NULL,
+ CONSTRAINT GFI_VERSION_PKEY PRIMARY KEY (id)
+);
+
+INSERT INTO GFI_VERSION (ID, VERSION) VALUES ( 1, '01.00-ORACLE' );
+
+-- ---------------------------------------------
+-- TABLE REF_STATUS
+-- ---------------------------------------------
+CREATE SEQUENCE REF_STATUS_ID_SEQ
+ INCREMENT BY 1;
+
+CREATE TABLE REF_STATUS
+(
+ ID integer,
+ UUID RAW(16) NOT NULL,
+ STATUS VARCHAR2(50) NOT NULL,
+ CONSTRAINT REF_STATUS_PKEY PRIMARY KEY (id)
+);
+
+CREATE OR REPLACE TRIGGER "REF_STATUS_BIR"
+BEFORE INSERT ON REF_STATUS
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT REF_STATUS_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "REF_STATUS_BIR" ENABLE;
+
+INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (1, hextoraw(replace('acabc8f6-2cf3-485a-a4f8-68d178c7df45','-','')), 'neu');
+INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (2, hextoraw(replace('f2e44dc5-d30c-4128-86bb-d3d5fc766b61','-','')), 'geplant');
+INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (3, hextoraw(replace('23fc0254-cc3d-4371-97ad-54ef733008ae','-','')), 'angelegt');
+INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (4, hextoraw(replace('74a4ca78-7268-11ea-bc55-0242ac130003','-','')), 'storniert');
+INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (5, hextoraw(replace('7264e572-eae9-4cca-be05-af6b0d081247','-','')), 'qualifiziert');
+INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (6, hextoraw(replace('9374219a-7419-4b72-899d-cd0576d85cdb','-','')), 'aktualisiert');
+INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (7, hextoraw(replace('8c333345-5c3c-41ed-9de4-323045f64259','-','')), 'abgeschlossen');
+
+
+-- ---------------------------------------------
+-- TABLE REF_FAILURE_CLASSIFICATION
+-- ---------------------------------------------
+CREATE SEQUENCE REF_FAILURE_CLASS_ID_SEQ
+ INCREMENT BY 1;
+
+CREATE TABLE REF_FAILURE_CLASSIFICATION
+(
+ ID integer NOT NULL,
+ UUID RAW(16) NOT NULL,
+ CLASSIFICATION VARCHAR2(50) NOT NULL,
+ DESCRIPTION VARCHAR2(255) NULL,
+ CONSTRAINT REF_FAILURE_CLASS_PKEY PRIMARY KEY (id)
+);
+
+CREATE OR REPLACE TRIGGER "REF_FAILURE_CLASS_BIR"
+BEFORE INSERT ON REF_FAILURE_CLASSIFICATION
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT REF_FAILURE_CLASS_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "REF_FAILURE_CLASS_BIR" ENABLE;
+
+INSERT INTO REF_FAILURE_CLASSIFICATION (UUID, CLASSIFICATION, DESCRIPTION) VALUES ( hextoraw(replace('9255fb79-c57a-4448-a69c-5d57994f0c91','-','')), 'Störung', NULL );
+INSERT INTO REF_FAILURE_CLASSIFICATION (UUID, CLASSIFICATION, DESCRIPTION) VALUES ( hextoraw(replace('8ec1e144-5230-4d43-a3df-f62dd64bb855','-','')), 'geplante Maßnahme', NULL );
+
+-- ---------------------------------------------
+-- TABLE REF_BRANCH
+-- ---------------------------------------------
+CREATE SEQUENCE REF_GFI_BRANCH_ID_SEQ
+ INCREMENT BY 1;
+
+CREATE TABLE REF_GFI_BRANCH
+(
+ ID integer NOT NULL,
+ UUID RAW(16) NOT NULL,
+ NAME VARCHAR2(50) NOT NULL,
+ DESCRIPTION VARCHAR2(255),
+ COLOR_CODE VARCHAR2(20),
+ CONSTRAINT REF_GFI_BRANCH_PKEY PRIMARY KEY (id)
+);
+
+CREATE OR REPLACE TRIGGER "REF_GFI_BRANCH_BIR"
+BEFORE INSERT ON REF_GFI_BRANCH
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT REF_GFI_BRANCH_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "REF_GFI_BRANCH_BIR" ENABLE;
+
+INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('535b4beb-9b17-4247-bb8b-26bd01b48f9a','-','')), 'S', 'Strom', '#fc6042');
+INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('d41f54e5-c4df-440e-b334-40e8f3a6854a','-','')), 'G', 'Gas', '#fdea64');
+INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('62c6d361-96a0-41cc-bda1-4e58ad16f21a','-','')), 'F', 'Fernwärme', '#2cc990');
+INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('d8d93e0e-5c8c-4ab8-9625-f820de55ee7c','-','')), 'W', 'Wasser', '#2c82c9');
+INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('7d4907fb-cb3f-4a4f-93e9-839052e76894','-','')), 'TK', 'Telekommunikation', '#ff33cc');
+INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('014c4a2a-3cf1-4d28-af70-4573722bceb0','-','')), 'ST', 'Sekundärtechnik', '#9933ff');
+INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('8fb3c764-8fb5-11ea-bc55-0242ac130003','-','')), 'OS', 'ohne Sparte', '#ffffff');
+
+
+-- ---------------------------------------------
+-- TABLE REF_EXPECTED_REASON
+-- ---------------------------------------------
+CREATE SEQUENCE REF_EXPECTED_REASON_ID_SEQ
+ INCREMENT BY 1;
+
+CREATE TABLE REF_EXPECTED_REASON
+(
+ ID integer NOT NULL,
+ UUID RAW(16) NOT NULL,
+ TEXT VARCHAR2(100) NOT NULL,
+ DESCRIPTION VARCHAR2(255) NULL,
+ BRANCHES VARCHAR2(100) NOT NULL,
+ CONSTRAINT REF_EXPECTED_REASON_PKEY PRIMARY KEY (id)
+);
+
+CREATE OR REPLACE TRIGGER "REF_EXPECTED_REASON_BIR"
+BEFORE INSERT ON REF_EXPECTED_REASON
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT REF_EXPECTED_REASON_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "REF_EXPECTED_REASON_BIR" ENABLE;
+
+INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('27a07918-6aa0-11ea-bc55-0242ac130003','-','')), 'Defekt technische Anlage', 'Dies ist die Beschreibung für: Defekt technische Anlage', 'S,G,W,F,TK,ST,OS');
+INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('27a07c42-6aa0-11ea-bc55-0242ac130003','-','')), 'Kabelfehler Mittelspannung', 'Dies ist die Beschreibung für: Kabelfehler Mittelspannung', 'S');
+INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('27a07d50-6aa0-11ea-bc55-0242ac130003','-','')), 'Kabelfehler Niederspannung', 'Dies ist die Beschreibung für: Kabelfehler Niederspannung', 'S');
+INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('27a07e22-6aa0-11ea-bc55-0242ac130003','-','')), 'Leitung beschädigt', 'Dies ist die Beschreibung für: Leitung beschädigt', 'S,G,W,F,TK,ST');
+INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('27a07eea-6aa0-11ea-bc55-0242ac130003','-','')), 'noch nicht bekannt', 'Dies ist die Beschreibung für: noch nicht bekannt', 'S,G,W,F,TK,ST,OS');
+INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('27a07fbc-6aa0-11ea-bc55-0242ac130003','-','')), 'Wasserrohrbruch', 'Dies ist die Beschreibung für: Wasserrohrbruch', 'W');
+INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('27a08160-6aa0-11ea-bc55-0242ac130003','-','')), 'Überregionale Störung', 'Dies ist die Beschreibung für: Überregionale Störung', 'S,G,W,F,TK,ST');
+INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('343bd0e9-b958-4a94-950f-52223b58f62d','-','')), 'Sonstiges', 'Dies ist die Beschreibung für: sonstiges', 'S,G,W,F,TK,ST,OS');
+
+-- ---------------------------------------------
+-- TABLE REF_RADIUS
+-- ---------------------------------------------
+CREATE SEQUENCE REF_RADIUS_ID_SEQ
+ INCREMENT BY 1;
+
+CREATE TABLE REF_RADIUS
+(
+ ID integer NOT NULL,
+ UUID RAW(16) NOT NULL,
+ RADIUS integer NOT NULL,
+ CONSTRAINT REF_RADIUS_PKEY PRIMARY KEY (id)
+);
+
+CREATE OR REPLACE TRIGGER "REF_RADIUS_BIR"
+BEFORE INSERT ON REF_RADIUS
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT REF_RADIUS_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "REF_RADIUS_BIR" ENABLE;
+
+INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('e6439b49-ce5a-486e-a437-b362ec73dc44','-','')), 0);
+INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('36671000-6aa6-11ea-bc55-0242ac130003','-','')), 50);
+INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('366712a8-6aa6-11ea-bc55-0242ac130003','-','')), 100);
+INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('366713c0-6aa6-11ea-bc55-0242ac130003','-','')), 250);
+INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('3667150a-6aa6-11ea-bc55-0242ac130003','-','')), 500);
+INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('36671780-6aa6-11ea-bc55-0242ac130003','-','')), 1000);
+INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('3667187a-6aa6-11ea-bc55-0242ac130003','-','')), 1500);
+INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('36671ad2-6aa6-11ea-bc55-0242ac130003','-','')), 2000);
+
+-- ---------------------------------------------
+-- TABLE TBL_FAILURE_INFORMATION
+-- ---------------------------------------------
+CREATE SEQUENCE TBL_FAILURE_INFORMATION_ID_SEQ
+ INCREMENT BY 1;
+
+CREATE TABLE TBL_FAILURE_INFORMATION
+(
+ ID integer NOT NULL,
+ UUID RAW(16) NOT NULL,
+ VERSION_NUMBER integer NOT NULL,
+ FK_REF_FAILURE_CLASSIFICATION integer,
+ TITLE VARCHAR2(255) NULL,
+ DESCRIPTION VARCHAR2(255) NULL,
+ RESPONSIBILITY VARCHAR2(255),
+ FK_REF_STATUS_INTERN integer NOT NULL,
+ FK_REF_BRANCH integer NOT NULL,
+ VOLTAGE_LEVEL VARCHAR2(2),
+ PRESSURE_LEVEL VARCHAR2(2),
+ FAILURE_BEGIN timestamp NOT NULL,
+ FAILURE_END_PLANNED timestamp,
+ FAILURE_END_RESUPPLIED timestamp,
+ INTERNAL_REMARK VARCHAR2(1024),
+ POSTCODE VARCHAR2(30),
+ CITY VARCHAR2(255),
+ DISTRICT VARCHAR2(255),
+ STREET VARCHAR2(255),
+ HOUSENUMBER VARCHAR2(30),
+ STATION_ID VARCHAR2 (30),
+ STATION_DESCRIPTION VARCHAR2 (255),
+ STATION_COORDS VARCHAR2 (255),
+ FK_REF_RADIUS integer,
+ LONGITUDE numeric(9,6),
+ LATITUDE numeric(9,6),
+ FK_TBL_FAILINFO_CONDENSED integer,
+ CONDENSED NUMBER,
+ CONDENSED_COUNT integer,
+ OBJECT_REF_EXT_SYSTEM VARCHAR2 (255),
+ PUBLICATION_STATUS VARCHAR2 (40),
+ PUBLICATION_FREETEXT VARCHAR2 (1024),
+ FK_REF_EXPECTED_REASON integer,
+ ADDRESS_TYPE varchar(10),
+ fault_location_area varchar(20),
+ CREATE_DATE timestamp NOT NULL,
+ CREATE_USER VARCHAR2(100) NOT NULL,
+ MOD_DATE timestamp NOT NULL,
+ MOD_USER VARCHAR2(100),
+ CONSTRAINT TBL_FAILURE_INFORMATION_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_FAIL_INF__FCLASS_FKEY FOREIGN KEY (FK_REF_FAILURE_CLASSIFICATION)
+ REFERENCES REF_FAILURE_CLASSIFICATION (ID),
+ CONSTRAINT TBL_FAIL_INF_STATUSINT_FKEY FOREIGN KEY (FK_REF_STATUS_INTERN)
+ REFERENCES REF_STATUS (ID),
+ CONSTRAINT TBL_FAIL_INF__BRANCH_ID_FKEY FOREIGN KEY (FK_REF_BRANCH)
+ REFERENCES REF_GFI_BRANCH (ID),
+ CONSTRAINT TBL_FAIL_INF__EXPREASON_FKEY FOREIGN KEY (FK_REF_EXPECTED_REASON)
+ REFERENCES REF_EXPECTED_REASON (ID)
+);
+
+CREATE OR REPLACE TRIGGER "TBL_FAILURE_INFORMATION_BIR"
+BEFORE INSERT ON TBL_FAILURE_INFORMATION
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT TBL_FAILURE_INFORMATION_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "TBL_FAILURE_INFORMATION_BIR" ENABLE;
+
+
+-- ---------------------------------------------
+-- TABLE TBL_DISTRIBUTION_GROUP
+-- ---------------------------------------------
+CREATE SEQUENCE TBL_DISTRIBUTION_GROUP_ID_SEQ
+ INCREMENT BY 1;
+
+
+CREATE TABLE TBL_DISTRIBUTION_GROUP
+(
+ ID integer NOT NULL,
+ UUID RAW(16) NOT NULL,
+ NAME VARCHAR2(255),
+ DISTRIBUTION_TEXT_PUBLISH VARCHAR2(2048),
+ EMAIL_SUBJECT_PUBLISH varchar(255),
+ DISTRIBUTION_TEXT_COMPLETE VARCHAR2(2048),
+ EMAIL_SUBJECT_COMPLETE varchar(255),
+ DISTRIBUTION_TEXT_UPDATE VARCHAR2(2048),
+ EMAIL_SUBJECT_UPDATE varchar(255),
+ CONSTRAINT TBL_DISTRIBUTION_GROUP_PKEY PRIMARY KEY (id)
+);
+
+CREATE OR REPLACE TRIGGER "TBL_DISTRIBUTION_GROUP_BIR"
+BEFORE INSERT ON TBL_DISTRIBUTION_GROUP
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT TBL_DISTRIBUTION_GROUP_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "TBL_DISTRIBUTION_GROUP_BIR" ENABLE;
+
+-- ---------------------------------------------
+-- TABLE TBL_DISTRIBUTION_GROUP_MEMBER
+-- ---------------------------------------------
+CREATE SEQUENCE TBL_DISTR_GROUP_MEMB_ID_SEQ
+ INCREMENT BY 1;
+
+CREATE TABLE TBL_DISTRIBUTION_GROUP_MEMBER
+(
+ ID integer NOT NULL,
+ UUID RAW(16) NOT NULL,
+ FK_TBL_DISTRIBUTION_GROUP integer NOT NULL,
+ CONTACT_ID RAW(16) NOT NULL,
+ POSTCODES varchar(1024),
+ CONSTRAINT TBL_DISTR_GROUP_MEMBER_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_DIS_GRP_MEM__DIS_GRP_FKEY FOREIGN KEY (FK_TBL_DISTRIBUTION_GROUP)
+ REFERENCES TBL_DISTRIBUTION_GROUP (ID)
+);
+
+CREATE OR REPLACE TRIGGER "TBL_DISTR_GROUP_MEMB_BIR"
+BEFORE INSERT ON TBL_DISTRIBUTION_GROUP_MEMBER
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT TBL_DISTR_GROUP_MEMB_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "TBL_DISTR_GROUP_MEMB_BIR" ENABLE;
+
+-- ---------------------------------------------
+-- TABLE TBL_FAILURE_INFORMATION_DISTRIBUTION_GROUP
+-- ---------------------------------------------
+CREATE SEQUENCE TBL_FAILINFO_DISTGROUP_ID_SEQ
+ INCREMENT BY 1;
+
+CREATE TABLE tbl_failinfo_distgroup
+(
+ id integer NOT NULL,
+ fk_tbl_failure_information integer NOT NULL,
+ fk_tbl_distribution_group integer NOT NULL,
+ CONSTRAINT tbl_fi_dist_group_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_fi_dist_group_fi_id_fkey FOREIGN KEY (fk_tbl_failure_information)
+ REFERENCES tbl_failure_information (id),
+ CONSTRAINT tbl_fi_dist_group__digrp_fkey FOREIGN KEY (fk_tbl_distribution_group)
+ REFERENCES tbl_distribution_group (id)
+);
+
+CREATE OR REPLACE TRIGGER "TBL_FAILINFO_DISTGROUP_BIR"
+BEFORE INSERT ON TBL_FAILINFO_DISTGROUP
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT TBL_FAILINFO_DISTGROUP_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "TBL_FAILINFO_DISTGROUP_BIR" ENABLE;
+
+CREATE UNIQUE INDEX idx_tbl_fi_d_group_uq ON tbl_failinfo_distgroup ( fk_tbl_failure_information, fk_tbl_distribution_group );
+
+INSERT INTO tbl_distribution_group(UUID, NAME, EMAIL_SUBJECT_PUBLISH, DISTRIBUTION_TEXT_PUBLISH)
+VALUES(hextoraw(replace('f71660e2-aee1-11ea-b3de-0242ac130004','-','')), 'Veröffentlicher', 'Bitte Anpassen - Das geplannte Ende der Störung (Sparte: \$Sparte\$) wird demnächst erreicht. Der Statuswechsel erfolgt automatisch.',
+'Sehr geehrte Damen und Herren,
+
+die im Betreff genannte Meldung ist über folgenden Link erreichbar: \$Direkter_Link_zur_Störung\$');
+
+
+-- ---------------------------------------------
+-- TABLE TBL_FAILURE_INFORMATION_STATION
+-- ---------------------------------------------
+CREATE SEQUENCE tbl_fi_station_id_seq
+ INCREMENT BY 1;
+
+CREATE TABLE tbl_failinfo_station
+(
+ id integer,
+ fk_tbl_failure_information integer NOT NULL,
+ station_station_id VARCHAR2(30) NOT NULL,
+ CONSTRAINT tbl_fi_station_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_fi_station__fi_id_fkey FOREIGN KEY (fk_tbl_failure_information)
+ REFERENCES tbl_failure_information (id)
+);
+
+
+CREATE OR REPLACE TRIGGER "tbl_fi_station_BIR"
+BEFORE INSERT ON tbl_failinfo_station
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT tbl_fi_station_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "tbl_fi_station_BIR" ENABLE;
+
+-- ---------------------------------------------
+-- TABLE TBL_FAILURE_INFORMATION_PUBLICATION_CHANNEL
+-- ---------------------------------------------
+
+CREATE SEQUENCE tbl_fi_pub_channel_seq
+ INCREMENT BY 1;
+
+
+CREATE TABLE tbl_failinfo_pub_channel
+(
+ id integer NOT NULL,
+ fk_tbl_failure_information integer NOT NULL,
+ publication_channel VARCHAR2(50) NOT NULL,
+ published NUMBER NOT NULL,
+ CONSTRAINT tbl_fi_pubchannel_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_fi_id_fkey FOREIGN KEY (fk_tbl_failure_information)
+ REFERENCES tbl_failure_information (id)
+);
+
+CREATE OR REPLACE TRIGGER "tbl_fi_pub_channel_bir"
+BEFORE INSERT ON TBL_FAILINFO_PUB_CHANNEL
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT tbl_fi_pub_channel_seq.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "tbl_fi_pub_channel_bir" ENABLE;
+
+
+-- ---------------------------------------------
+-- TABLE TBL_FAILURE_INFORMATION_REMINDER_MAIL_SENT
+-- ---------------------------------------------
+CREATE SEQUENCE tbl_failinfo_reminsent_id_seq
+ INCREMENT BY 1;
+
+CREATE TABLE tbl_failinfo_remind_mail_sent
+(
+ id integer NOT NULL,
+ fk_tbl_failure_information integer NOT NULL,
+ mail_sent NUMBER NOT NULL,
+ date_mail_sent timestamp,
+ CONSTRAINT tbl_fi_remmail_sent_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_fi_remmail_sent__fi_fkey FOREIGN KEY (fk_tbl_failure_information)
+ REFERENCES tbl_failure_information (id)
+);
+
+CREATE OR REPLACE TRIGGER "tbl_failinfo_reminsent_BIR"
+BEFORE INSERT ON tbl_failinfo_remind_mail_sent
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT tbl_failinfo_reminsent_id_seq.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "tbl_failinfo_reminsent_BIR" ENABLE;
+
+create unique index idx_ri_reminder_sent_uq on tbl_failinfo_remind_mail_sent ( fk_tbl_failure_information );
+
+
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+-- HISTORY-TABLES
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+
+
+
+CREATE SEQUENCE HTBL_FAILUREINFORMATION_ID_SEQ
+ INCREMENT BY 1;
+
+CREATE TABLE HTBL_FAILURE_INFORMATION
+(
+ HID integer NOT NULL,
+ HACTION integer NOT NULL,
+ HDATE timestamp NOT NULL,
+ HUSER VARCHAR2(100),
+
+ ID integer,
+ UUID RAW(16),
+ VERSION_NUMBER integer,
+ FK_REF_FAILURE_CLASSIFICATION integer,
+ TITLE VARCHAR2(255) NULL,
+ DESCRIPTION VARCHAR2(255) NULL,
+ RESPONSIBILITY VARCHAR2 (255),
+ FK_REF_STATUS_INTERN integer,
+ FK_REF_BRANCH integer,
+ VOLTAGE_LEVEL VARCHAR2 (2),
+ PRESSURE_LEVEL VARCHAR2 (2),
+ FAILURE_BEGIN timestamp,
+ FAILURE_END_PLANNED timestamp,
+ FAILURE_END_RESUPPLIED timestamp,
+ INTERNAL_REMARK VARCHAR2(1024),
+ POSTCODE VARCHAR2 (30),
+ CITY VARCHAR2 (255),
+ DISTRICT VARCHAR2 (255),
+ STREET VARCHAR2 (255),
+ HOUSENUMBER VARCHAR2 (30),
+ STATION_ID VARCHAR2 (30),
+ STATION_DESCRIPTION VARCHAR2 (255),
+ STATION_COORDS VARCHAR2 (255),
+ FK_REF_RADIUS integer,
+ LONGITUDE numeric(9,6),
+ LATITUDE numeric(9,6),
+ FK_TBL_FAILINFO_CONDENSED integer,
+ CONDENSED NUMBER,
+ CONDENSED_COUNT integer,
+ OBJECT_REF_EXT_SYSTEM VARCHAR2 (255),
+ PUBLICATION_STATUS VARCHAR2 (40),
+ PUBLICATION_FREETEXT VARCHAR2 (1024),
+ FK_REF_EXPECTED_REASON integer,
+ ADDRESS_TYPE varchar(10),
+ fault_location_area varchar(20),
+ CREATE_USER VARCHAR2(100),
+ CREATE_DATE timestamp,
+ MOD_USER VARCHAR2(100),
+ MOD_DATE timestamp,
+ CONSTRAINT HTBL_FAILURE_INFORMATION_PKEY PRIMARY KEY (HID)
+);
+
+CREATE OR REPLACE TRIGGER "HTBL_FAILUREINFORMATION_BIR"
+BEFORE INSERT ON HTBL_FAILURE_INFORMATION
+FOR EACH ROW
+ WHEN (NEW."HID" IS NULL) BEGIN
+ SELECT HTBL_FAILUREINFORMATION_ID_SEQ.NEXTVAL
+ INTO :NEW."HID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "HTBL_FAILUREINFORMATION_BIR" ENABLE;
+
+-- ----------------------------------------------------------------------------
+-- HTBL_FAILURE_INFORMATION_STATION
+-- ----------------------------------------------------------------------------
+
+
+CREATE SEQUENCE HTBL_FAILINFO_STATION_ID_SEQ
+ INCREMENT BY 1;
+CREATE TABLE HTBL_FAILINFO_STATION
+(
+ HID integer NOT NULL,
+ HDATE timestamp NOT NULL,
+
+ FK_TBL_FAILURE_INFORMATION integer,
+ VERSION_NUMBER integer,
+ STATION_STATION_ID VARCHAR2 (30),
+ CONSTRAINT HTBL_FAILINFO_STATION_PKEY PRIMARY KEY (HID)
+);
+
+CREATE OR REPLACE TRIGGER "HTBL_FAILINFO_STATION_BIR"
+BEFORE INSERT ON HTBL_FAILINFO_STATION
+FOR EACH ROW
+ WHEN (NEW."HID" IS NULL) BEGIN
+ SELECT HTBL_FAILINFO_STATION_ID_SEQ.NEXTVAL
+ INTO :NEW."HID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "HTBL_FAILINFO_STATION_BIR" ENABLE;
+
+
+-- ---------------------------------------------
+-- TABLE TBL_ADDRESS
+-- ---------------------------------------------
+CREATE SEQUENCE TBL_GFI_ADDRESS_ID_SEQ
+ INCREMENT BY 1;
+
+CREATE TABLE TBL_GFI_ADDRESS
+(
+ ID integer NOT NULL,
+ UUID RAW(16) NOT NULL,
+ SDOX1 numeric(9,2) NOT NULL,
+ SDOY1 numeric(10,2) NOT NULL,
+ G3EFID numeric,
+ POSTCODE VARCHAR2(30),
+ COMMUNITY VARCHAR2(255),
+ DISTRICT VARCHAR2(255),
+ STREET VARCHAR2(255),
+ HOUSENUMBER VARCHAR2(30),
+ WATER_CONNECTION NUMBER,
+ WATER_GROUP VARCHAR2(255),
+ GAS_CONNECTION NUMBER,
+ GAS_GROUP VARCHAR2(255),
+ POWER_CONNECTION NUMBER,
+ DISTRICTHEATING_CONNECTION NUMBER,
+ TELECOMM_CONNECTION NUMBER,
+ STATION_ID VARCHAR2(30),
+ LONGITUDE numeric(9,6),
+ LATITUDE numeric(9,6),
+ CONSTRAINT TBL_GFI_ADDRESS_PKEY PRIMARY KEY (id)
+);
+
+CREATE OR REPLACE TRIGGER "TBL_GFI_ADDRESS_BIR"
+BEFORE INSERT ON TBL_GFI_ADDRESS
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT TBL_GFI_ADDRESS_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "TBL_GFI_ADDRESS_BIR" ENABLE;
+
+CREATE INDEX idx_tbl_gfiaddress_g3efid ON TBL_GFI_ADDRESS ( G3EFID );
+CREATE INDEX idx_tbl_gfiaddress_postcode ON TBL_GFI_ADDRESS ( POSTCODE );
+CREATE INDEX idx_tbl_gfiaddress_community ON TBL_GFI_ADDRESS ( COMMUNITY );
+CREATE INDEX idx_tbl_gfiaddress_district ON TBL_GFI_ADDRESS ( DISTRICT );
+CREATE INDEX idx_tbl_gfiaddress_street ON TBL_GFI_ADDRESS ( STREET );
+CREATE INDEX idx_tbl_gfiaddress_station_id ON TBL_GFI_ADDRESS ( STATION_ID );
+
+
+-- ---------------------------------------------
+-- TABLE TBL_STATION
+-- ---------------------------------------------
+
+CREATE SEQUENCE TBL_STATION_ID_SEQ
+ INCREMENT BY 1;
+
+CREATE TABLE TBL_STATION
+(
+ ID integer NOT NULL,
+ UUID RAW(16) NOT NULL,
+ SDOX1 numeric(9,2) NOT NULL,
+ SDOY1 numeric(10,2) NOT NULL,
+ G3EFID numeric,
+ STATION_ID VARCHAR2(30),
+ STATION_NAME VARCHAR2(255),
+ LONGITUDE numeric(9,6),
+ LATITUDE numeric(9,6),
+ CONSTRAINT TBL_STATION_PKEY PRIMARY KEY (id)
+);
+
+CREATE OR REPLACE TRIGGER "TBL_STATION_BIR"
+BEFORE INSERT ON TBL_STATION
+FOR EACH ROW
+ WHEN (NEW."ID" IS NULL) BEGIN
+ SELECT TBL_STATION_ID_SEQ.NEXTVAL
+ INTO :NEW."ID"
+ FROM DUAL;
+END;
+/
+ALTER TRIGGER "TBL_STATION_BIR" ENABLE;
+
+CREATE INDEX idx_tbl_station_g3efid ON TBL_STATION ( G3EFID );
+CREATE INDEX idx_tbl_station_station_id ON TBL_STATION ( STATION_ID );
+CREATE INDEX idx_tbl_station_uuid ON TBL_STATION ( UUID );
+
+
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+-- TRIGGER
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+CREATE OR REPLACE TRIGGER TBL_FAIINFO_INSERT_TRG BEFORE
+ INSERT ON TBL_FAILURE_INFORMATION REFERENCING OLD as oldValue NEW AS newValue
+ FOR EACH ROW
+ BEGIN
+ INSERT INTO HTBL_FAILURE_INFORMATION (
+ ID,
+ UUID,
+ VERSION_NUMBER,
+ FK_REF_FAILURE_CLASSIFICATION,
+ TITLE,
+ "DESCRIPTION",
+ RESPONSIBILITY,
+ FK_REF_STATUS_INTERN,
+ FK_REF_BRANCH,
+ VOLTAGE_LEVEL,
+ PRESSURE_LEVEL,
+ FAILURE_BEGIN,
+ FAILURE_END_PLANNED,
+ FAILURE_END_RESUPPLIED,
+ INTERNAL_REMARK,
+ POSTCODE,
+ CITY,
+ DISTRICT,
+ STREET,
+ HOUSENUMBER,
+ STATION_ID,
+ STATION_DESCRIPTION,
+ STATION_COORDS,
+ FK_REF_RADIUS,
+ LONGITUDE,
+ LATITUDE,
+ FK_TBL_FAILINFO_CONDENSED,
+ CONDENSED,
+ CONDENSED_COUNT,
+ OBJECT_REF_EXT_SYSTEM,
+ PUBLICATION_STATUS,
+ PUBLICATION_FREETEXT,
+ FK_REF_EXPECTED_REASON,
+ ADDRESS_TYPE,
+ FAULT_LOCATION_AREA,
+ CREATE_DATE,
+ CREATE_USER,
+ MOD_DATE,
+ MOD_USER,
+ HACTION,
+ HDATE,
+ HUSER
+ )
+ VALUES (
+ :newValue.ID,
+ :newValue.UUID,
+ :newValue.VERSION_NUMBER,
+ :newValue.FK_REF_FAILURE_CLASSIFICATION,
+ :newValue.TITLE,
+ :newValue."DESCRIPTION",
+ :newValue.RESPONSIBILITY,
+ :newValue.FK_REF_STATUS_INTERN,
+ :newValue.FK_REF_BRANCH,
+ :newValue.VOLTAGE_LEVEL,
+ :newValue.PRESSURE_LEVEL,
+ :newValue.FAILURE_BEGIN,
+ :newValue.FAILURE_END_PLANNED,
+ :newValue.FAILURE_END_RESUPPLIED,
+ :newValue.INTERNAL_REMARK,
+ :newValue.POSTCODE,
+ :newValue.CITY,
+ :newValue.DISTRICT,
+ :newValue.STREET,
+ :newValue.HOUSENUMBER,
+ :newValue.STATION_ID,
+ :newValue.STATION_DESCRIPTION,
+ :newValue.STATION_COORDS,
+ :newValue.FK_REF_RADIUS,
+ :newValue.LONGITUDE,
+ :newValue.LATITUDE,
+ :newValue.FK_TBL_FAILINFO_CONDENSED,
+ :newValue.CONDENSED,
+ :newValue.CONDENSED_COUNT,
+ :newValue.OBJECT_REF_EXT_SYSTEM,
+ :newValue.PUBLICATION_STATUS,
+ :newValue.PUBLICATION_FREETEXT,
+ :newValue.FK_REF_EXPECTED_REASON,
+ :newValue.ADDRESS_TYPE,
+ :newValue.FAULT_LOCATION_AREA,
+ :newValue.CREATE_DATE,
+ :newValue.CREATE_USER ,
+ :newValue.MOD_DATE,
+ :newValue.MOD_USER,
+ 1,
+ CURRENT_TIMESTAMP,
+ :newValue.CREATE_USER );
+
+ END;
+/
+
+
+CREATE OR REPLACE TRIGGER TBL_FAIINFO_UPDATE_TRG BEFORE
+ UPDATE ON TBL_FAILURE_INFORMATION REFERENCING OLD as oldValue NEW AS newValue
+ FOR EACH ROW
+ BEGIN
+ INSERT INTO HTBL_FAILURE_INFORMATION (
+ ID,
+ UUID,
+ VERSION_NUMBER,
+ FK_REF_FAILURE_CLASSIFICATION,
+ TITLE,
+ "DESCRIPTION",
+ RESPONSIBILITY,
+ FK_REF_STATUS_INTERN,
+ FK_REF_BRANCH,
+ VOLTAGE_LEVEL,
+ PRESSURE_LEVEL,
+ FAILURE_BEGIN,
+ FAILURE_END_PLANNED,
+ FAILURE_END_RESUPPLIED,
+ INTERNAL_REMARK,
+ POSTCODE,
+ CITY,
+ DISTRICT,
+ STREET,
+ HOUSENUMBER,
+ STATION_ID,
+ STATION_DESCRIPTION,
+ STATION_COORDS,
+ FK_REF_RADIUS,
+ LONGITUDE,
+ LATITUDE,
+ FK_TBL_FAILINFO_CONDENSED,
+ CONDENSED,
+ CONDENSED_COUNT,
+ OBJECT_REF_EXT_SYSTEM,
+ PUBLICATION_STATUS,
+ PUBLICATION_FREETEXT,
+ FK_REF_EXPECTED_REASON,
+ ADDRESS_TYPE,
+ FAULT_LOCATION_AREA,
+ CREATE_DATE,
+ CREATE_USER,
+ MOD_DATE,
+ MOD_USER,
+ HACTION,
+ HDATE,
+ HUSER
+ )
+ VALUES (
+ :newValue.ID,
+ :newValue.UUID,
+ :newValue.VERSION_NUMBER,
+ :newValue.FK_REF_FAILURE_CLASSIFICATION,
+ :newValue.TITLE,
+ :newValue."DESCRIPTION",
+ :newValue.RESPONSIBILITY,
+ :newValue.FK_REF_STATUS_INTERN,
+ :newValue.FK_REF_BRANCH,
+ :newValue.VOLTAGE_LEVEL,
+ :newValue.PRESSURE_LEVEL,
+ :newValue.FAILURE_BEGIN,
+ :newValue.FAILURE_END_PLANNED,
+ :newValue.FAILURE_END_RESUPPLIED,
+ :newValue.INTERNAL_REMARK,
+ :newValue.POSTCODE,
+ :newValue.CITY,
+ :newValue.DISTRICT,
+ :newValue.STREET,
+ :newValue.HOUSENUMBER,
+ :newValue.STATION_ID,
+ :newValue.STATION_DESCRIPTION,
+ :newValue.STATION_COORDS,
+ :newValue.FK_REF_RADIUS,
+ :newValue.LONGITUDE,
+ :newValue.LATITUDE,
+ :newValue.FK_TBL_FAILINFO_CONDENSED,
+ :newValue.CONDENSED,
+ :newValue.CONDENSED_COUNT,
+ :newValue.OBJECT_REF_EXT_SYSTEM,
+ :newValue.PUBLICATION_STATUS,
+ :newValue.PUBLICATION_FREETEXT,
+ :newValue.FK_REF_EXPECTED_REASON,
+ :newValue.ADDRESS_TYPE,
+ :newValue.FAULT_LOCATION_AREA,
+ :newValue.CREATE_DATE,
+ :newValue.CREATE_USER ,
+ :newValue.MOD_DATE,
+ :newValue.MOD_USER,
+ 2,
+ CURRENT_TIMESTAMP,
+ :newValue.CREATE_USER );
+
+ END;
+/
+
+
+
+CREATE OR REPLACE TRIGGER TBL_FAIINFO_DELETE_TRG
+ BEFORE DELETE ON TBL_FAILURE_INFORMATION REFERENCING OLD as oldValue
+ FOR EACH ROW
+ BEGIN
+ INSERT INTO HTBL_FAILURE_INFORMATION (
+ ID,
+ UUID,
+ VERSION_NUMBER,
+ FK_REF_FAILURE_CLASSIFICATION,
+ TITLE,
+ "DESCRIPTION",
+ RESPONSIBILITY,
+ FK_REF_STATUS_INTERN,
+ FK_REF_BRANCH,
+ VOLTAGE_LEVEL,
+ PRESSURE_LEVEL,
+ FAILURE_BEGIN,
+ FAILURE_END_PLANNED,
+ FAILURE_END_RESUPPLIED,
+ INTERNAL_REMARK,
+ POSTCODE,
+ CITY,
+ DISTRICT,
+ STREET,
+ HOUSENUMBER,
+ STATION_ID,
+ STATION_DESCRIPTION,
+ STATION_COORDS,
+ FK_REF_RADIUS,
+ LONGITUDE,
+ LATITUDE,
+ FK_TBL_FAILINFO_CONDENSED,
+ CONDENSED,
+ CONDENSED_COUNT,
+ OBJECT_REF_EXT_SYSTEM,
+ PUBLICATION_STATUS,
+ PUBLICATION_FREETEXT,
+ FK_REF_EXPECTED_REASON,
+ ADDRESS_TYPE,
+ FAULT_LOCATION_AREA,
+ CREATE_DATE,
+ CREATE_USER,
+ MOD_DATE,
+ MOD_USER,
+ HACTION,
+ HDATE,
+ HUSER
+ )
+ VALUES (
+ :oldValue.ID,
+ :oldValue.UUID,
+ :oldValue.VERSION_NUMBER,
+ :oldValue.FK_REF_FAILURE_CLASSIFICATION,
+ :oldValue.TITLE,
+ :oldValue."DESCRIPTION",
+ :oldValue.RESPONSIBILITY,
+ :oldValue.FK_REF_STATUS_INTERN,
+ :oldValue.FK_REF_BRANCH,
+ :oldValue.VOLTAGE_LEVEL,
+ :oldValue.PRESSURE_LEVEL,
+ :oldValue.FAILURE_BEGIN,
+ :oldValue.FAILURE_END_PLANNED,
+ :oldValue.FAILURE_END_RESUPPLIED,
+ :oldValue.INTERNAL_REMARK,
+ :oldValue.POSTCODE,
+ :oldValue.CITY,
+ :oldValue.DISTRICT,
+ :oldValue.STREET,
+ :oldValue.HOUSENUMBER,
+ :oldValue.STATION_ID,
+ :oldValue.STATION_DESCRIPTION,
+ :oldValue.STATION_COORDS,
+ :oldValue.FK_REF_RADIUS,
+ :oldValue.LONGITUDE,
+ :oldValue.LATITUDE,
+ :oldValue.FK_TBL_FAILINFO_CONDENSED,
+ :oldValue.CONDENSED,
+ :oldValue.CONDENSED_COUNT,
+ :oldValue.OBJECT_REF_EXT_SYSTEM,
+ :oldValue.PUBLICATION_STATUS,
+ :oldValue.PUBLICATION_FREETEXT,
+ :oldValue.FK_REF_EXPECTED_REASON,
+ :oldValue.ADDRESS_TYPE,
+ :oldValue.FAULT_LOCATION_AREA,
+ :oldValue.CREATE_DATE,
+ :oldValue.CREATE_USER ,
+ :oldValue.MOD_DATE,
+ :oldValue.MOD_USER,
+ 3,
+ CURRENT_TIMESTAMP,
+ :oldValue.CREATE_USER );
+
+ END;
+/
+
+
+
+
+-- ----------------------------------------------------------------
+-- Insert initial values Distribution group
+--
+-- TODO
+-- ----------------------------------------------------------------
+
+UPDATE VERSION SET VERSION = '1.0.0_ORACLE' WHERE ID = 1;
+
+
diff --git a/gfsBackendService/deploy/db/oracle/DELETE_GFI_DB_ORACLE.sql b/gfsBackendService/deploy/db/oracle/DELETE_GFI_DB_ORACLE.sql
new file mode 100644
index 0000000..aa2d086
--- /dev/null
+++ b/gfsBackendService/deploy/db/oracle/DELETE_GFI_DB_ORACLE.sql
@@ -0,0 +1,65 @@
+-----------------------------------------------------------------------------------
+-- *******************************************************************************
+-- * Copyright (c) 2019 Contributors to the Eclipse Foundation
+-- *
+-- * See the NOTICE file(s) distributed with this work for additional
+-- * information regarding copyright ownership.
+-- *
+-- * This program and the accompanying materials are made available under the
+-- * terms of the Eclipse Public License v. 2.0 which is available at
+-- * http://www.eclipse.org/legal/epl-2.0.
+-- *
+-- * SPDX-License-Identifier: EPL-2.0
+-- *******************************************************************************
+-----------------------------------------------------------------------------------
+
+
+DROP TABLE TBL_STATION;
+DROP SEQUENCE TBL_STATION_ID_SEQ;
+
+DROP TABLE TBL_GFI_ADDRESS;
+DROP SEQUENCE TBL_GFI_ADDRESS_ID_SEQ;
+
+DROP TABLE HTBL_FAILINFO_STATION;
+DROP SEQUENCE HTBL_FAILINFO_STATION_ID_SEQ;
+
+DROP TABLE HTBL_FAILURE_INFORMATION;
+DROP SEQUENCE HTBL_FAILUREINFORMATION_ID_SEQ;
+
+DROP TABLE tbl_failinfo_remind_mail_sent;
+DROP SEQUENCE tbl_failinfo_reminsent_id_seq;
+
+DROP TABLE tbl_failinfo_pub_channel;
+DROP SEQUENCE tbl_fi_pub_channel_seq;
+
+DROP TABLE tbl_failinfo_station;
+DROP SEQUENCE tbl_fi_station_id_seq;
+
+DROP TABLE tbl_failinfo_distgroup;
+DROP SEQUENCE TBL_FAILINFO_DISTGROUP_ID_SEQ;
+
+DROP TABLE TBL_DISTRIBUTION_GROUP_MEMBER;
+DROP SEQUENCE TBL_DISTR_GROUP_MEMB_ID_SEQ;
+
+DROP TABLE TBL_DISTRIBUTION_GROUP;
+DROP SEQUENCE TBL_DISTRIBUTION_GROUP_ID_SEQ;
+
+DROP TABLE TBL_FAILURE_INFORMATION;
+DROP SEQUENCE TBL_FAILURE_INFORMATION_ID_SEQ;
+
+DROP TABLE REF_RADIUS;
+DROP SEQUENCE REF_RADIUS_ID_SEQ;
+
+DROP TABLE REF_EXPECTED_REASON;
+DROP SEQUENCE REF_EXPECTED_REASON_ID_SEQ;
+
+DROP TABLE REF_GFI_BRANCH;
+DROP SEQUENCE REF_GFI_BRANCH_ID_SEQ;
+
+DROP TABLE REF_FAILURE_CLASSIFICATION;
+DROP SEQUENCE REF_FAILURE_CLASS_ID_SEQ;
+
+DROP TABLE REF_STATUS;
+DROP SEQUENCE REF_STATUS_ID_SEQ;
+
+DROP TABLE GFI_VERSION;
diff --git a/gfsBackendService/src/main/resources/db/migration/V0_63__CREATE_GFI_DB_Complete_V_1.0.0.sql b/gfsBackendService/src/main/resources/db/migration/V0_63__CREATE_GFI_DB_Complete_V_1.0.0.sql
new file mode 100644
index 0000000..40c303f
--- /dev/null
+++ b/gfsBackendService/src/main/resources/db/migration/V0_63__CREATE_GFI_DB_Complete_V_1.0.0.sql
@@ -0,0 +1,785 @@
+-----------------------------------------------------------------------------------
+-- *******************************************************************************
+-- * Copyright (c) 2019 Contributors to the Eclipse Foundation
+-- *
+-- * See the NOTICE file(s) distributed with this work for additional
+-- * information regarding copyright ownership.
+-- *
+-- * This program and the accompanying materials are made available under the
+-- * terms of the Eclipse Public License v. 2.0 which is available at
+-- * http://www.eclipse.org/legal/epl-2.0.
+-- *
+-- * SPDX-License-Identifier: EPL-2.0
+-- *******************************************************************************
+-----------------------------------------------------------------------------------
+-- CREATE ROLE GFI_SERVICE LOGIN
+-- NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
+-- ALTER ROLE GFI_SERVICE with password 'gfi_service';
+DROP TABLE IF EXISTS public.VERSION CASCADE;
+DROP TABLE IF EXISTS public.GFI_VERSION CASCADE;
+DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_STATUS CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_STATUS_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_GFI_BRANCH CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_GFI_BRANCH_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_BRANCH CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_BRANCH_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_FAILURE_CLASSIFICATION CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_FAILURE_CLASSIFICATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_FAILURE_TYPE CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_FAILURE_TYPE_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_RADIUS CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_RADIUS_ID_SEQ;
+
+DROP TABLE IF EXISTS public.REF_EXPECTED_REASON CASCADE;
+DROP SEQUENCE IF EXISTS public.REF_EXPECTED_REASON_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_IMPORT_DATA CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_IMPORT_DATA_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_DISTRIBUTION_GROUP_MEMBER CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_DISTRIBUTION_GROUP_MEMBER_ID_SEQ;
+DROP SEQUENCE IF EXISTS public.TBL_DISTR_GROUP_MEMB_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_DISTRIBUTION_GROUP CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_DISTRIBUTION_GROUP_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_STATION CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_STATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_FAILINFO_STATION CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_FAILINFO_STATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.HTBL_FAILURE_INFORMATION CASCADE;
+DROP SEQUENCE IF EXISTS public.HTBL_FAILURE_INFORMATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_FAILINFO_STATION CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_FAILINFO_STATION_ID_SEQ;
+DROP SEQUENCE IF EXISTS public.TBL_FI_STATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.HTBL_FAILURE_INFORMATION_STATION CASCADE;
+DROP SEQUENCE IF EXISTS public.HTBL_FAILURE_INFORMATION_STATION_ID_SEQ;
+DROP SEQUENCE IF EXISTS public.HTBL_FAILUREINFORMATION_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_DISTRIBUTION_GROUP CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_DISTRIBUTION_GROUP_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_FAILINFO_DISTGROUP CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_FAILINFO_DISTGROUP_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_PUBLICATION_CHANNEL;
+DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_PUBLICATION_CHANNEL_SEQ;
+
+
+DROP TABLE IF EXISTS public.TBL_FAILINFO_PUB_CHANNEL;
+DROP SEQUENCE IF EXISTS public.TBL_FAILINFO_PUB_CHANNEL_SEQ;
+DROP SEQUENCE IF EXISTS public.TBL_FI_PUB_CHANNEL_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_REMINDER_MAIL_SENT CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_REMINDER_MAIL_SENT_ID_SEQ;
+
+DROP TABLE IF EXISTS public.TBL_FAILINFO_REMIND_MAIL_SENT CASCADE;
+DROP SEQUENCE IF EXISTS public.TBL_FAILINFO_REMINSENT_ID_SEQ;
+
+
+-- ---------------------------------------------
+-- TABLE VERSION
+-- ---------------------------------------------
+CREATE TABLE public.GFI_VERSION
+(
+ ID integer NOT NULL,
+ VERSION character varying(50) NOT NULL,
+ CONSTRAINT REF_GFI_VERSION_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.GFI_VERSION
+ OWNER TO GFI_SERVICE;
+GRANT ALL ON TABLE public.GFI_VERSION TO GFI_SERVICE;
+
+INSERT INTO public.GFI_VERSION (ID, VERSION) VALUES ( 1, '00-DEV' );
+
+-- ---------------------------------------------
+-- TABLE REF_STATUS
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_status_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_status_id_seq
+ OWNER TO GFI_SERVICE;
+
+CREATE TABLE public.REF_STATUS
+(
+ ID integer NOT NULL DEFAULT nextval('REF_STATUS_ID_SEQ'::regclass),
+ UUID uuid NOT NULL,
+ STATUS character varying(50) NOT NULL,
+ CONSTRAINT REF_STATUS_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.REF_STATUS
+ OWNER TO GFI_SERVICE;
+GRANT ALL ON TABLE public.REF_STATUS TO GFI_SERVICE;
+
+INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (1, 'acabc8f6-2cf3-485a-a4f8-68d178c7df45', 'neu');
+INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (2, 'f2e44dc5-d30c-4128-86bb-d3d5fc766b61', 'geplant');
+INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (3, '23fc0254-cc3d-4371-97ad-54ef733008ae', 'angelegt');
+INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (4, '74a4ca78-7268-11ea-bc55-0242ac130003', 'storniert');
+INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (5, '7264e572-eae9-4cca-be05-af6b0d081247', 'qualifiziert');
+INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (6, '9374219a-7419-4b72-899d-cd0576d85cdb', 'aktualisiert');
+INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (7, '8c333345-5c3c-41ed-9de4-323045f64259', 'abgeschlossen');
+
+
+-- ---------------------------------------------
+-- TABLE REF_FAILURE_CLASSIFICATION
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_failure_classification_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_failure_classification_id_seq
+ OWNER TO GFI_SERVICE;
+
+CREATE TABLE public.REF_FAILURE_CLASSIFICATION
+(
+ ID integer NOT NULL DEFAULT nextval('REF_FAILURE_CLASSIFICATION_ID_SEQ'::regclass),
+ UUID uuid NOT NULL,
+ CLASSIFICATION character varying(50) NOT NULL,
+ DESCRIPTION character varying(255) NULL,
+ CONSTRAINT REF_FAILURE_CLASSIFICATION_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.REF_FAILURE_CLASSIFICATION
+ OWNER TO GFI_SERVICE;
+GRANT ALL ON TABLE public.REF_FAILURE_CLASSIFICATION TO GFI_SERVICE;
+
+INSERT INTO public.REF_FAILURE_CLASSIFICATION (UUID, CLASSIFICATION, DESCRIPTION) VALUES ( '9255fb79-c57a-4448-a69c-5d57994f0c91', 'Störung', NULL );
+INSERT INTO public.REF_FAILURE_CLASSIFICATION (UUID, CLASSIFICATION, DESCRIPTION) VALUES ( '8ec1e144-5230-4d43-a3df-f62dd64bb855', 'geplante Maßnahme', NULL );
+
+-- ---------------------------------------------
+-- TABLE REF_BRANCH
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_gfi_branch_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_gfi_branch_id_seq
+ OWNER TO GFI_SERVICE;
+
+CREATE TABLE public.REF_GFI_BRANCH
+(
+ ID integer NOT NULL DEFAULT nextval('REF_GFI_BRANCH_ID_SEQ'::regclass),
+ UUID uuid NOT NULL,
+ NAME character varying(50) NOT NULL,
+ DESCRIPTION character varying(255),
+ COLOR_CODE character varying(20),
+ CONSTRAINT REF_GFI_BRANCH_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.REF_GFI_BRANCH
+ OWNER TO GFI_SERVICE;
+GRANT ALL ON TABLE public.REF_GFI_BRANCH TO GFI_SERVICE;
+
+INSERT INTO public.ref_gfi_branch(uuid, name, description, color_code) VALUES ('535b4beb-9b17-4247-bb8b-26bd01b48f9a', 'S', 'Strom', '#fc6042');
+INSERT INTO public.ref_gfi_branch(uuid, name, description, color_code) VALUES ('d41f54e5-c4df-440e-b334-40e8f3a6854a', 'G', 'Gas', '#fdea64');
+INSERT INTO public.ref_gfi_branch(uuid, name, description, color_code) VALUES ('62c6d361-96a0-41cc-bda1-4e58ad16f21a', 'F', 'Fernwärme', '#2cc990');
+INSERT INTO public.ref_gfi_branch(uuid, name, description, color_code) VALUES ('d8d93e0e-5c8c-4ab8-9625-f820de55ee7c', 'W', 'Wasser', '#2c82c9');
+INSERT INTO public.ref_gfi_branch(uuid, name, description, color_code) VALUES ('7d4907fb-cb3f-4a4f-93e9-839052e76894', 'TK', 'Telekommunikation', '#ff33cc');
+INSERT INTO public.ref_gfi_branch(uuid, name, description, color_code) VALUES ('014c4a2a-3cf1-4d28-af70-4573722bceb0', 'ST', 'Sekundärtechnik', '#9933ff');
+INSERT INTO public.ref_gfi_branch(uuid, name, description, color_code) VALUES ('8fb3c764-8fb5-11ea-bc55-0242ac130003', 'OS', 'ohne Sparte', '#ffffff');
+
+
+-- ---------------------------------------------
+-- TABLE REF_EXPECTED_REASON
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_expected_reason_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_expected_reason_id_seq
+ OWNER TO GFI_SERVICE;
+
+CREATE TABLE public.REF_EXPECTED_REASON
+(
+ ID integer NOT NULL DEFAULT nextval('REF_EXPECTED_REASON_ID_SEQ'::regclass),
+ UUID uuid NOT NULL,
+ TEXT character varying(100) NOT NULL,
+ DESCRIPTION character varying(255) NULL,
+ BRANCHES character varying(100) NOT NULL,
+ CONSTRAINT REF_EXPECTED_REASON_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.REF_EXPECTED_REASON
+ OWNER TO GFI_SERVICE;
+GRANT ALL ON TABLE public.REF_EXPECTED_REASON TO GFI_SERVICE;
+
+INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07918-6aa0-11ea-bc55-0242ac130003', 'Defekt technische Anlage', 'Dies ist die Beschreibung für: Defekt technische Anlage', 'S,G,W,F,TK,ST,OS');
+INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07c42-6aa0-11ea-bc55-0242ac130003', 'Kabelfehler Mittelspannung', 'Dies ist die Beschreibung für: Kabelfehler Mittelspannung', 'S');
+INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07d50-6aa0-11ea-bc55-0242ac130003', 'Kabelfehler Niederspannung', 'Dies ist die Beschreibung für: Kabelfehler Niederspannung', 'S');
+INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07e22-6aa0-11ea-bc55-0242ac130003', 'Leitung beschädigt', 'Dies ist die Beschreibung für: Leitung beschädigt', 'S,G,W,F,TK,ST');
+INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07eea-6aa0-11ea-bc55-0242ac130003', 'noch nicht bekannt', 'Dies ist die Beschreibung für: noch nicht bekannt', 'S,G,W,F,TK,ST,OS');
+INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07fbc-6aa0-11ea-bc55-0242ac130003', 'Wasserrohrbruch', 'Dies ist die Beschreibung für: Wasserrohrbruch', 'W');
+INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a08160-6aa0-11ea-bc55-0242ac130003', 'Überregionale Störung', 'Dies ist die Beschreibung für: Überregionale Störung', 'S,G,W,F,TK,ST');
+INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('343bd0e9-b958-4a94-950f-52223b58f62d', 'Sonstiges', 'Dies ist die Beschreibung für: sonstiges', 'S,G,W,F,TK,ST,OS');
+
+-- ---------------------------------------------
+-- TABLE REF_RADIUS
+-- ---------------------------------------------
+CREATE SEQUENCE public.ref_radius_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.ref_radius_id_seq
+ OWNER TO GFI_SERVICE;
+
+CREATE TABLE public.REF_RADIUS
+(
+ ID integer NOT NULL DEFAULT nextval('REF_RADIUS_ID_SEQ'::regclass),
+ UUID uuid NOT NULL,
+ RADIUS integer NOT NULL,
+ CONSTRAINT REF_RADIUS_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.REF_RADIUS
+ OWNER TO GFI_SERVICE;
+GRANT ALL ON TABLE public.REF_RADIUS TO GFI_SERVICE;
+
+INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('e6439b49-ce5a-486e-a437-b362ec73dc44', 0);
+INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('36671000-6aa6-11ea-bc55-0242ac130003', 50);
+INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('366712a8-6aa6-11ea-bc55-0242ac130003', 100);
+INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('366713c0-6aa6-11ea-bc55-0242ac130003', 250);
+INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('3667150a-6aa6-11ea-bc55-0242ac130003', 500);
+INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('36671780-6aa6-11ea-bc55-0242ac130003', 1000);
+INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('3667187a-6aa6-11ea-bc55-0242ac130003', 1500);
+INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('36671ad2-6aa6-11ea-bc55-0242ac130003', 2000);
+
+-- ---------------------------------------------
+-- TABLE TBL_FAILURE_INFORMATION
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_failure_information_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_failure_information_id_seq
+ OWNER TO GFI_SERVICE;
+
+CREATE TABLE public.TBL_FAILURE_INFORMATION
+(
+ ID integer NOT NULL DEFAULT nextval('TBL_FAILURE_INFORMATION_ID_SEQ'::regclass),
+ UUID uuid NOT NULL,
+ VERSION_NUMBER integer NOT NULL,
+ FK_REF_FAILURE_CLASSIFICATION integer,
+ TITLE character varying(255) NULL,
+ DESCRIPTION character varying(255) NULL,
+ RESPONSIBILITY character varying(255),
+ FK_REF_STATUS_INTERN integer NOT NULL,
+ FK_REF_BRANCH integer NOT NULL,
+ VOLTAGE_LEVEL character varying(2),
+ PRESSURE_LEVEL character varying(2),
+ FAILURE_BEGIN timestamp NOT NULL,
+ FAILURE_END_PLANNED timestamp,
+ FAILURE_END_RESUPPLIED timestamp,
+ INTERNAL_REMARK character varying(1024),
+ POSTCODE character varying(30),
+ CITY character varying(255),
+ DISTRICT character varying(255),
+ STREET character varying(255),
+ HOUSENUMBER character varying(30),
+ STATION_ID character varying (30),
+ STATION_DESCRIPTION character varying (255),
+ STATION_COORDS character varying (255),
+ FK_REF_RADIUS integer,
+ LONGITUDE numeric(9,6),
+ LATITUDE numeric(9,6),
+ FK_TBL_FAILINFO_CONDENSED integer,
+ CONDENSED boolean,
+ CONDENSED_COUNT integer,
+ OBJECT_REF_EXT_SYSTEM character varying (255),
+ PUBLICATION_STATUS character varying (40),
+ PUBLICATION_FREETEXT character varying (1024),
+ FK_REF_EXPECTED_REASON integer,
+ ADDRESS_TYPE varchar(10),
+ fault_location_area varchar(20),
+ CREATE_DATE timestamp without time zone NOT NULL,
+ CREATE_USER character varying(100) NOT NULL,
+ MOD_DATE timestamp without time zone NOT NULL,
+ MOD_USER character varying(100),
+ CONSTRAINT TBL_FAILURE_INFORMATION_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_FAIL_INF__FCLASS_FKEY FOREIGN KEY (FK_REF_FAILURE_CLASSIFICATION)
+ REFERENCES public.REF_FAILURE_CLASSIFICATION (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_FAIL_INF_STATUSINT_FKEY FOREIGN KEY (FK_REF_STATUS_INTERN)
+ REFERENCES public.REF_STATUS (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_FAIL_INF__BRANCH_ID_FKEY FOREIGN KEY (FK_REF_BRANCH)
+ REFERENCES public.REF_GFI_BRANCH (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT TBL_FAIL_INF__EXPREASON_FKEY FOREIGN KEY (FK_REF_EXPECTED_REASON)
+ REFERENCES public.REF_EXPECTED_REASON (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+
+ALTER TABLE public.TBL_FAILURE_INFORMATION
+ OWNER TO GFI_SERVICE;
+GRANT ALL ON TABLE public.TBL_FAILURE_INFORMATION TO GFI_SERVICE;
+
+-- ---------------------------------------------
+-- TABLE TBL_DISTRIBUTION_GROUP
+-- ---------------------------------------------
+CREATE SEQUENCE public.TBL_DISTRIBUTION_GROUP_ID_SEQ
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.TBL_DISTRIBUTION_GROUP_ID_SEQ
+ OWNER TO GFI_SERVICE;
+
+
+CREATE TABLE public.TBL_DISTRIBUTION_GROUP
+(
+ ID integer NOT NULL DEFAULT nextval('TBL_DISTRIBUTION_GROUP_ID_SEQ'::regclass),
+ UUID uuid NOT NULL,
+ NAME character varying(255),
+ DISTRIBUTION_TEXT_PUBLISH character varying(2048),
+ EMAIL_SUBJECT_PUBLISH varchar(255),
+ DISTRIBUTION_TEXT_COMPLETE character varying(2048),
+ EMAIL_SUBJECT_COMPLETE varchar(255),
+ DISTRIBUTION_TEXT_UPDATE character varying(2048),
+ EMAIL_SUBJECT_UPDATE varchar(255),
+ CONSTRAINT TBL_DISTRIBUTION_GROUP_PKEY PRIMARY KEY (id)
+);
+
+ALTER TABLE public.TBL_DISTRIBUTION_GROUP
+ OWNER TO GFI_SERVICE;
+GRANT ALL ON TABLE public.TBL_DISTRIBUTION_GROUP TO GFI_SERVICE;
+
+INSERT INTO public.tbl_distribution_group(UUID, NAME, EMAIL_SUBJECT_PUBLISH, DISTRIBUTION_TEXT_PUBLISH)
+VALUES('f71660e2-aee1-11ea-b3de-0242ac130004', 'Veröffentlicher', 'Bitte Anpassen - Das geplannte Ende der Störung (Sparte: $Sparte$) wird demnächst erreicht. Der Statuswechsel erfolgt automatisch.',
+'<b>Veröffentlichen</b><br>
+<p>Sehr geehrte Damen und Herren,</p>
+<p>die im Betreff genannte Meldung ist über folgenden Link erreichbar:</p>
+$Direkter_Link_zur_Störung$
+<p>Mit freundlichen Grüßen</p>
+<p>Ihr Admin-Meister-Team der PTA GmbH</p>');
+
+-- ---------------------------------------------
+-- TABLE TBL_DISTRIBUTION_GROUP_MEMBER
+-- ---------------------------------------------
+CREATE SEQUENCE public.TBL_DISTR_GROUP_MEMB_ID_SEQ
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.TBL_DISTR_GROUP_MEMB_ID_SEQ
+ OWNER TO GFI_SERVICE;
+
+CREATE TABLE public.TBL_DISTRIBUTION_GROUP_MEMBER
+(
+ ID integer NOT NULL DEFAULT nextval('TBL_DISTR_GROUP_MEMB_ID_SEQ'::regclass),
+ UUID uuid NOT NULL,
+ FK_TBL_DISTRIBUTION_GROUP integer NOT NULL,
+ CONTACT_ID uuid NOT NULL,
+ POSTCODES varchar(1024),
+ CONSTRAINT TBL_DISTRIBUTION_GROUP_MEMBER_PKEY PRIMARY KEY (id),
+ CONSTRAINT TBL_DIS_GRP_MEM__DIS_GRP_FKEY FOREIGN KEY (FK_TBL_DISTRIBUTION_GROUP)
+ REFERENCES public.TBL_DISTRIBUTION_GROUP (ID) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+ );
+
+ALTER TABLE public.TBL_DISTRIBUTION_GROUP_MEMBER
+ OWNER TO GFI_SERVICE;
+GRANT ALL ON TABLE public.TBL_DISTRIBUTION_GROUP_MEMBER TO GFI_SERVICE;
+
+-- ---------------------------------------------
+-- TABLE TBL_FAILURE_INFORMATION_DISTRIBUTION_GROUP
+-- ---------------------------------------------
+CREATE SEQUENCE public.TBL_FAILINFO_DISTGROUP_ID_SEQ
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.TBL_FAILINFO_DISTGROUP_ID_SEQ
+ OWNER TO gfi_service;
+
+CREATE TABLE public.tbl_failinfo_distgroup
+(
+ id integer NOT NULL DEFAULT nextval('TBL_FAILINFO_DISTGROUP_ID_SEQ'::regclass),
+ fk_tbl_failure_information integer NOT NULL,
+ fk_tbl_distribution_group integer NOT NULL,
+ CONSTRAINT tbl_failure_information_distribution_group_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_fail_inf_dist_grp__failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information)
+ REFERENCES public.tbl_failure_information (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION,
+ CONSTRAINT tbl_fail_inf_dist_grp__distribution_group_fkey FOREIGN KEY (fk_tbl_distribution_group)
+ REFERENCES public.tbl_distribution_group (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+)
+WITH (
+ OIDS=FALSE
+);
+ALTER TABLE public.tbl_failinfo_distgroup
+ OWNER TO gfi_service;
+GRANT ALL ON TABLE public.tbl_failinfo_distgroup TO gfi_service;
+
+CREATE UNIQUE INDEX idx_tbl_fi_distribution_group_uq ON public.tbl_failinfo_distgroup ( fk_tbl_failure_information, fk_tbl_distribution_group );
+
+-- ---------------------------------------------
+-- TABLE TBL_FAILURE_INFORMATION_STATION
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_fi_station_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_fi_station_id_seq
+ OWNER TO gfi_service;
+
+CREATE TABLE public.tbl_failinfo_station
+(
+ id integer NOT NULL DEFAULT nextval('tbl_fi_station_id_seq'::regclass),
+ fk_tbl_failure_information integer NOT NULL,
+ station_station_id character varying(30) NOT NULL,
+ CONSTRAINT tbl_failure_information_station_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_fail_inf_station__failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information)
+ REFERENCES public.tbl_failure_information (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+)
+WITH (
+ OIDS=FALSE
+);
+ALTER TABLE public.tbl_failinfo_station
+ OWNER TO gfi_service;
+GRANT ALL ON TABLE public.tbl_failinfo_station TO gfi_service;
+
+-- ---------------------------------------------
+-- TABLE TBL_FAILURE_INFORMATION_PUBLICATION_CHANNEL
+-- ---------------------------------------------
+
+CREATE SEQUENCE public.tbl_fi_pub_channel_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_fi_pub_channel_seq
+ OWNER TO gfi_service;
+
+
+CREATE TABLE public.tbl_failinfo_pub_channel
+(
+ id integer NOT NULL DEFAULT nextval('tbl_fi_pub_channel_seq'::regclass),
+ fk_tbl_failure_information integer NOT NULL,
+ publication_channel character varying(50) NOT NULL,
+ published boolean NOT NULL,
+ CONSTRAINT tbl_failure_information_publication_channel_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information)
+ REFERENCES public.tbl_failure_information (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+
+ALTER TABLE public.tbl_failinfo_pub_channel
+ OWNER TO gfi_service;
+GRANT ALL ON TABLE public.tbl_failinfo_pub_channel TO gfi_service;
+
+
+-- ---------------------------------------------
+-- TABLE TBL_FAILURE_INFORMATION_REMINDER_MAIL_SENT
+-- ---------------------------------------------
+CREATE SEQUENCE public.tbl_failinfo_reminsent_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+ALTER TABLE public.tbl_failinfo_reminsent_id_seq
+ OWNER TO gfi_service;
+
+CREATE TABLE public.tbl_failinfo_remind_mail_sent
+(
+ id integer NOT NULL DEFAULT nextval('tbl_failinfo_reminsent_id_seq'::regclass),
+ fk_tbl_failure_information integer NOT NULL,
+ mail_sent boolean NOT NULL,
+ date_mail_sent timestamp,
+ CONSTRAINT tbl_failure_information_reminder_mail_sent_pkey PRIMARY KEY (id),
+ CONSTRAINT tbl_fail_inf_rem_mail_sent__failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information)
+ REFERENCES public.tbl_failure_information (id) MATCH SIMPLE
+ ON UPDATE NO ACTION ON DELETE NO ACTION
+)
+WITH (
+ OIDS=FALSE
+);
+ALTER TABLE public.tbl_failinfo_remind_mail_sent
+ OWNER TO gfi_service;
+GRANT ALL ON TABLE public.tbl_failinfo_remind_mail_sent TO gfi_service;
+
+create unique index idx_ri_reminder_sent_uq on public.tbl_failinfo_remind_mail_sent ( fk_tbl_failure_information );
+
+
+
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+-- HISTORY-TABLES
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+
+
+-- PUBLIC.HTBL_FAILURE_INFORMATION Automatic generanted History Table DDL --
+-- <GENERATED CODE!>
+
+CREATE SEQUENCE PUBLIC.HTBL_FAILUREINFORMATION_ID_SEQ
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 9223372036854775807
+ START 1
+ CACHE 1;
+
+ALTER TABLE PUBLIC.HTBL_FAILUREINFORMATION_ID_SEQ
+ OWNER TO gfi_service;
+
+CREATE TABLE PUBLIC.HTBL_FAILURE_INFORMATION
+(
+ HID integer NOT NULL DEFAULT nextval('HTBL_FAILUREINFORMATION_ID_SEQ'::regclass),
+ HACTION integer NOT NULL,
+ HDATE timestamp without time zone NOT NULL,
+ HUSER character varying(100),
+
+ ID integer,
+ UUID uuid,
+ VERSION_NUMBER integer,
+ FK_REF_FAILURE_CLASSIFICATION integer,
+ TITLE character varying(255) NULL,
+ DESCRIPTION character varying(255) NULL,
+ RESPONSIBILITY character varying (255),
+ FK_REF_STATUS_INTERN integer,
+ FK_REF_BRANCH integer,
+ VOLTAGE_LEVEL character varying (2),
+ PRESSURE_LEVEL character varying (2),
+ FAILURE_BEGIN timestamp without time zone,
+ FAILURE_END_PLANNED timestamp without time zone,
+ FAILURE_END_RESUPPLIED timestamp without time zone,
+ INTERNAL_REMARK character varying(1024),
+ POSTCODE character varying (30),
+ CITY character varying (255),
+ DISTRICT character varying (255),
+ STREET character varying (255),
+ HOUSENUMBER character varying (30),
+ STATION_ID character varying (30),
+ STATION_DESCRIPTION character varying (255),
+ STATION_COORDS character varying (255),
+ FK_REF_RADIUS integer,
+ LONGITUDE numeric(9,6),
+ LATITUDE numeric(9,6),
+ FK_TBL_FAILINFO_CONDENSED integer,
+ CONDENSED boolean,
+ CONDENSED_COUNT integer,
+ OBJECT_REF_EXT_SYSTEM character varying (255),
+ PUBLICATION_STATUS character varying (40),
+ PUBLICATION_FREETEXT character varying (1024),
+ FK_REF_EXPECTED_REASON integer,
+ ADDRESS_TYPE varchar(10),
+ fault_location_area varchar(20),
+ CREATE_USER character varying(100),
+ CREATE_DATE timestamp without time zone,
+ MOD_USER character varying(100),
+ MOD_DATE timestamp without time zone,
+ CONSTRAINT HTBL_FI_PKEY PRIMARY KEY (HID)
+);
+
+ALTER TABLE public.HTBL_FAILURE_INFORMATION
+ OWNER TO gfi_service;
+GRANT ALL ON TABLE public.HTBL_FAILURE_INFORMATION TO gfi_service;
+-- ----------------------------------------------------------------------------
+-- HTBL_FAILURE_INFORMATION_STATION
+-- ----------------------------------------------------------------------------
+
+DROP TABLE IF EXISTS HTBL_FAILINFO_STATION CASCADE;
+DROP SEQUENCE IF EXISTS HTBL_FAILINFO_STATION_ID_SEQ CASCADE;
+
+CREATE SEQUENCE PUBLIC.HTBL_FAILINFO_STATION_ID_SEQ
+ INCREMENT BY 1;
+
+ALTER TABLE PUBLIC.HTBL_FAILINFO_STATION_ID_SEQ
+ OWNER TO gfi_service;
+
+CREATE TABLE PUBLIC.HTBL_FAILINFO_STATION
+(
+ HID integer NOT NULL DEFAULT nextval('HTBL_FAILINFO_STATION_ID_SEQ'::regclass),
+ HDATE timestamp without time zone NOT NULL,
+
+ FK_TBL_FAILURE_INFORMATION integer,
+ VERSION_NUMBER integer,
+ STATION_STATION_ID character varying (30),
+ CONSTRAINT HTBL_FAILURE_INFORMATION_STATION_PKEY PRIMARY KEY (HID)
+);
+ALTER TABLE public.HTBL_FAILINFO_STATION
+ OWNER TO gfi_service;
+GRANT ALL ON TABLE public.HTBL_FAILINFO_STATION TO gfi_service;
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+-- Tables ADDRESSIMPORT
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+-- ---------------------------------------------
+-- TABLE TBL_ADDRESS
+-- ---------------------------------------------
+-- Das SQL Skript für den Addressimport wird im Modul "addressImport" verwaltet
+-- ---------------------------------------------
+-- TABLE TBL_STATION
+-- ---------------------------------------------
+-- Das SQL Skript für den Stationsimport wird im Modul "addressImport" verwaltet
+
+
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+-- TRIGGER
+-- ----------------------------------------------------------------------------
+-- ----------------------------------------------------------------------------
+
+-- PUBLIC.TBL_FAILURE_INFORMATION INSERT TRIGGER --
+-- <GENERATED CODE!>
+CREATE OR REPLACE FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_INSERT_TRG()
+ RETURNS trigger AS
+$BODY$
+ BEGIN
+ INSERT INTO HTBL_FAILURE_INFORMATION (
+ ID,UUID,VERSION_NUMBER,FK_REF_FAILURE_CLASSIFICATION,TITLE,DESCRIPTION,RESPONSIBILITY,FK_REF_STATUS_INTERN,FK_REF_BRANCH,VOLTAGE_LEVEL,PRESSURE_LEVEL,FAILURE_BEGIN,FAILURE_END_PLANNED,FAILURE_END_RESUPPLIED,INTERNAL_REMARK,POSTCODE,CITY,DISTRICT,STREET,HOUSENUMBER,STATION_ID,STATION_DESCRIPTION,STATION_COORDS,FK_REF_RADIUS,LONGITUDE,LATITUDE,FK_TBL_FAILINFO_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REF_EXT_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON,ADDRESS_TYPE,FAULT_LOCATION_AREA,CREATE_DATE,CREATE_USER,MOD_DATE,MOD_USER,
+
+ HACTION,
+ HDATE,
+ HUSER
+ )
+ VALUES (
+
+ NEW.ID,NEW.UUID,NEW.VERSION_NUMBER,NEW.FK_REF_FAILURE_CLASSIFICATION,NEW.TITLE,NEW.DESCRIPTION,NEW.RESPONSIBILITY,NEW.FK_REF_STATUS_INTERN,NEW.FK_REF_BRANCH,NEW.VOLTAGE_LEVEL,NEW.PRESSURE_LEVEL,NEW.FAILURE_BEGIN,NEW.FAILURE_END_PLANNED,NEW.FAILURE_END_RESUPPLIED,NEW.INTERNAL_REMARK,NEW.POSTCODE,NEW.CITY,NEW.DISTRICT,NEW.STREET,NEW.HOUSENUMBER,NEW.STATION_ID,NEW.STATION_DESCRIPTION,NEW.STATION_COORDS,NEW.FK_REF_RADIUS,NEW.LONGITUDE,NEW.LATITUDE,NEW.FK_TBL_FAILINFO_CONDENSED,NEW.CONDENSED,NEW.CONDENSED_COUNT,NEW.OBJECT_REF_EXT_SYSTEM,NEW.PUBLICATION_STATUS,NEW.PUBLICATION_FREETEXT,NEW.FK_REF_EXPECTED_REASON,NEW.ADDRESS_TYPE,NEW.FAULT_LOCATION_AREA,NEW.CREATE_DATE,NEW.CREATE_USER,NEW.MOD_DATE,NEW.MOD_USER,
+
+ 1,
+ current_timestamp,
+ NEW.CREATE_USER );
+
+ RETURN NEW;
+ END;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+ALTER FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_INSERT_TRG()
+ OWNER TO gfi_service;
+
+
+DROP TRIGGER IF EXISTS TBL_FAILURE_INFORMATION_INSERT_TRG ON PUBLIC.TBL_FAILURE_INFORMATION;
+
+CREATE TRIGGER TBL_FAILURE_INFORMATION_INSERT_TRG
+ BEFORE INSERT
+ ON PUBLIC.TBL_FAILURE_INFORMATION
+ FOR EACH ROW
+ EXECUTE PROCEDURE PUBLIC.TBL_FAILURE_INFORMATION_INSERT_TRG();
+
+
+
+-- PUBLIC.TBL_FAILURE_INFORMATION UPDATE TRIGGER --
+-- <GENERATED CODE!>
+
+CREATE OR REPLACE FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_UPDATE_TRG()
+ RETURNS trigger AS
+$BODY$
+ BEGIN
+ INSERT INTO HTBL_FAILURE_INFORMATION (
+ ID,UUID,VERSION_NUMBER,FK_REF_FAILURE_CLASSIFICATION,TITLE,DESCRIPTION,RESPONSIBILITY,FK_REF_STATUS_INTERN,FK_REF_BRANCH,VOLTAGE_LEVEL,PRESSURE_LEVEL,FAILURE_BEGIN,FAILURE_END_PLANNED,FAILURE_END_RESUPPLIED,INTERNAL_REMARK,POSTCODE,CITY,DISTRICT,STREET,HOUSENUMBER,STATION_ID,STATION_DESCRIPTION,STATION_COORDS,FK_REF_RADIUS,LONGITUDE,LATITUDE,FK_TBL_FAILINFO_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REF_EXT_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON,ADDRESS_TYPE,FAULT_LOCATION_AREA,CREATE_DATE,CREATE_USER,MOD_DATE,MOD_USER,
+
+ HACTION,
+ HDATE,
+ HUSER
+ )
+ VALUES (
+ NEW.ID,NEW.UUID,NEW.VERSION_NUMBER,NEW.FK_REF_FAILURE_CLASSIFICATION,NEW.TITLE,NEW.DESCRIPTION,NEW.RESPONSIBILITY,NEW.FK_REF_STATUS_INTERN,NEW.FK_REF_BRANCH,NEW.VOLTAGE_LEVEL,NEW.PRESSURE_LEVEL,NEW.FAILURE_BEGIN,NEW.FAILURE_END_PLANNED,NEW.FAILURE_END_RESUPPLIED,NEW.INTERNAL_REMARK,NEW.POSTCODE,NEW.CITY,NEW.DISTRICT,NEW.STREET,NEW.HOUSENUMBER,NEW.STATION_ID,NEW.STATION_DESCRIPTION,NEW.STATION_COORDS,NEW.FK_REF_RADIUS,NEW.LONGITUDE,NEW.LATITUDE,NEW.FK_TBL_FAILINFO_CONDENSED,NEW.CONDENSED,NEW.CONDENSED_COUNT,NEW.OBJECT_REF_EXT_SYSTEM,NEW.PUBLICATION_STATUS,NEW.PUBLICATION_FREETEXT,NEW.FK_REF_EXPECTED_REASON,NEW.ADDRESS_TYPE,NEW.FAULT_LOCATION_AREA,NEW.CREATE_DATE,NEW.CREATE_USER,NEW.MOD_DATE,NEW.MOD_USER,
+ 2,
+ current_timestamp,
+ NEW.MOD_USER
+ );
+
+ RETURN NEW;
+ END;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+ALTER FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_UPDATE_TRG()
+ OWNER TO gfi_service;
+
+DROP TRIGGER IF EXISTS TBL_FAILURE_INFORMATION_UPDATE_TRG ON PUBLIC.TBL_FAILURE_INFORMATION;
+
+CREATE TRIGGER TBL_FAILURE_INFORMATION_UPDATE_TRG
+ BEFORE UPDATE
+ ON PUBLIC.TBL_FAILURE_INFORMATION
+ FOR EACH ROW
+ EXECUTE PROCEDURE PUBLIC.TBL_FAILURE_INFORMATION_UPDATE_TRG();
+
+
+
+-- PUBLIC.TBL_FAILURE_INFORMATION DELETE TRIGGER --
+-- <GENERATED CODE!>
+
+CREATE OR REPLACE FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_DELETE_TRG()
+ RETURNS trigger AS
+$BODY$
+ BEGIN
+ IF TG_OP = 'DELETE' THEN
+ INSERT INTO HTBL_FAILURE_INFORMATION (
+
+ ID,UUID,VERSION_NUMBER,FK_REF_FAILURE_CLASSIFICATION,TITLE,DESCRIPTION,RESPONSIBILITY,FK_REF_STATUS_INTERN,FK_REF_BRANCH,VOLTAGE_LEVEL,PRESSURE_LEVEL,FAILURE_BEGIN,FAILURE_END_PLANNED,FAILURE_END_RESUPPLIED,INTERNAL_REMARK,POSTCODE,CITY,DISTRICT,STREET,HOUSENUMBER,STATION_ID,STATION_DESCRIPTION,STATION_COORDS,FK_REF_RADIUS,LONGITUDE,LATITUDE,FK_TBL_FAILINFO_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REF_EXT_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON,ADDRESS_TYPE,FAULT_LOCATION_AREA,CREATE_DATE,CREATE_USER,MOD_DATE,MOD_USER,
+
+ HACTION,
+ HDATE,
+ HUSER
+ )
+ VALUES (
+ OLD.ID,OLD.UUID,OLD.VERSION_NUMBER,OLD.FK_REF_FAILURE_CLASSIFICATION,OLD.TITLE,OLD.DESCRIPTION,OLD.RESPONSIBILITY,OLD.FK_REF_STATUS_INTERN,OLD.FK_REF_BRANCH,OLD.VOLTAGE_LEVEL,OLD.PRESSURE_LEVEL,OLD.FAILURE_BEGIN,OLD.FAILURE_END_PLANNED,OLD.FAILURE_END_RESUPPLIED,OLD.INTERNAL_REMARK,OLD.POSTCODE,OLD.CITY,OLD.DISTRICT,OLD.STREET,OLD.HOUSENUMBER,OLD.STATION_ID,OLD.STATION_DESCRIPTION,OLD.STATION_COORDS,OLD.FK_REF_RADIUS,OLD.LONGITUDE,OLD.LATITUDE,OLD.FK_TBL_FAILINFO_CONDENSED,OLD.CONDENSED,OLD.CONDENSED_COUNT,OLD.OBJECT_REF_EXT_SYSTEM,OLD.PUBLICATION_STATUS,OLD.PUBLICATION_FREETEXT,OLD.FK_REF_EXPECTED_REASON,OLD.ADDRESS_TYPE,OLD.FAULT_LOCATION_AREA,OLD.CREATE_DATE,OLD.CREATE_USER,OLD.MOD_DATE,OLD.MOD_USER,
+
+ 3,
+ current_timestamp,
+ OLD.MOD_USER );
+ END IF;
+
+ RETURN OLD;
+ END;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+ALTER FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_DELETE_TRG()
+ OWNER TO gfi_service;
+
+DROP TRIGGER IF EXISTS TBL_FAILURE_INFORMATION_DELETE_TRG ON PUBLIC.TBL_FAILURE_INFORMATION;
+
+CREATE TRIGGER TBL_FAILURE_INFORMATION_DELETE_TRG
+ BEFORE DELETE
+ ON PUBLIC.TBL_FAILURE_INFORMATION
+ FOR EACH ROW
+ EXECUTE PROCEDURE PUBLIC.TBL_FAILURE_INFORMATION_DELETE_TRG();
+
+
+
+
+-- ---------------------------------------------
+-- INSERT TEST DATA
+-- ---------------------------------------------
+UPDATE public.GFI_VERSION SET VERSION = '1.0.0' WHERE ID = 1;
