V0_23__CREATE_GFI_DB.sql 31.8 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
-----------------------------------------------------------------------------------
-- *******************************************************************************
-- * 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.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_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;

-- ---------------------------------------------
-- TABLE VERSION
-- ---------------------------------------------
CREATE TABLE public.VERSION
(
  ID integer NOT NULL,
  VERSION character varying(50) NOT NULL,
  CONSTRAINT REF_VERSION_PKEY PRIMARY KEY (id)
);

ALTER TABLE public.VERSION
  OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.VERSION TO GFI_SERVICE;

INSERT INTO public.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,
  IS_INTERNAL boolean NOT NULL,
  IS_EXTERNAL boolean 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, IS_INTERNAL, IS_EXTERNAL) VALUES (1, 'acabc8f6-2cf3-485a-a4f8-68d178c7df45', 'neu', TRUE, FALSE );
INSERT INTO public.REF_STATUS (ID, UUID, STATUS, IS_INTERNAL, IS_EXTERNAL) VALUES (2, 'f2e44dc5-d30c-4128-86bb-d3d5fc766b61', 'geplant', TRUE, TRUE );
INSERT INTO public.REF_STATUS (ID, UUID, STATUS, IS_INTERNAL, IS_EXTERNAL) VALUES (3, '23fc0254-cc3d-4371-97ad-54ef733008ae', 'angelegt', TRUE, FALSE );
INSERT INTO public.REF_STATUS (ID, UUID, STATUS, IS_INTERNAL, IS_EXTERNAL) VALUES (4, '74a4ca78-7268-11ea-bc55-0242ac130003', 'storniert', TRUE, FALSE );
INSERT INTO public.REF_STATUS (ID, UUID, STATUS, IS_INTERNAL, IS_EXTERNAL) VALUES (5, '7264e572-eae9-4cca-be05-af6b0d081247', 'qualifiziert', TRUE, FALSE );
INSERT INTO public.REF_STATUS (ID, UUID, STATUS, IS_INTERNAL, IS_EXTERNAL) VALUES (6, '9374219a-7419-4b72-899d-cd0576d85cdb', 'aktualisiert', TRUE, TRUE );
INSERT INTO public.REF_STATUS (ID, UUID, STATUS, IS_INTERNAL, IS_EXTERNAL) VALUES (7, '8c333345-5c3c-41ed-9de4-323045f64259', 'abgeschlossen', TRUE, TRUE );


-- ---------------------------------------------
-- 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_FAILURE_TYPE
-- ---------------------------------------------
CREATE SEQUENCE public.ref_failure_type_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.ref_failure_type_id_seq
  OWNER TO GFI_SERVICE;

CREATE TABLE public.REF_FAILURE_TYPE
(
  ID integer NOT NULL DEFAULT nextval('REF_FAILURE_TYPE_ID_SEQ'::regclass),
  UUID uuid NOT NULL,
  TYPE character varying(50) NOT NULL,
  DESCRIPTION character varying(255) NULL,
  CONSTRAINT REF_FAILURE_TYPE_PKEY PRIMARY KEY (id)
);

ALTER TABLE public.REF_FAILURE_TYPE
  OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.REF_FAILURE_TYPE TO GFI_SERVICE;


INSERT INTO public.REF_FAILURE_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '44a2aaed-8910-4116-b0c4-0855f8d3c28d', 'Information', NULL );
INSERT INTO public.REF_FAILURE_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '94e880c4-3127-47d5-aaee-5f778462ab0c', 'zu veröffentlichende Meldung', 'Standardwert' );
INSERT INTO public.REF_FAILURE_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '658245bd-bdc4-47dd-bc90-0336f9471410', 'unterlagerte Störung', 'nicht zu veröffentlichen' );

-- ---------------------------------------------
-- TABLE REF_BRANCH
-- ---------------------------------------------
CREATE SEQUENCE public.ref_branch_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.ref_branch_id_seq
  OWNER TO GFI_SERVICE;

CREATE TABLE public.REF_BRANCH
(
  ID integer NOT NULL DEFAULT nextval('REF_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_BRANCH_PKEY PRIMARY KEY (id)
);

ALTER TABLE public.REF_BRANCH
  OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.REF_BRANCH TO GFI_SERVICE;

INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('535b4beb-9b17-4247-bb8b-26bd01b48f9a', 'S', 'Strom', '#fc6042');
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('d41f54e5-c4df-440e-b334-40e8f3a6854a', 'G', 'Gas', '#fdea64');
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('62c6d361-96a0-41cc-bda1-4e58ad16f21a', 'F', 'Fernwärme', '#2cc990');
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('d8d93e0e-5c8c-4ab8-9625-f820de55ee7c', 'W', 'Wasser', '#2c82c9');
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('7d4907fb-cb3f-4a4f-93e9-839052e76894', 'TK', 'Telekommunikation', '#ff33cc');
INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('014c4a2a-3cf1-4d28-af70-4573722bceb0', 'ST', 'Sekundärtechnik', '#9933ff');
INSERT INTO public.ref_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,
  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) VALUES ('27a07918-6aa0-11ea-bc55-0242ac130003', 'Defekt technische Anlage', 'Dies ist die Beschreibung für: Defekt technische Anlage');
INSERT INTO public.ref_expected_reason(uuid, text, description) VALUES ('27a07c42-6aa0-11ea-bc55-0242ac130003', 'Kabelfehler Mittelspannung', 'Dies ist die Beschreibung für: Kabelfehler Mittelspannung');
INSERT INTO public.ref_expected_reason(uuid, text, description) VALUES ('27a07d50-6aa0-11ea-bc55-0242ac130003', 'Kabelfehler Niederspannung', 'Dies ist die Beschreibung für: Kabelfehler Niederspannung');
INSERT INTO public.ref_expected_reason(uuid, text, description) VALUES ('27a07e22-6aa0-11ea-bc55-0242ac130003', 'Leitung beschädigt', 'Dies ist die Beschreibung für: Leitung beschädigt');
INSERT INTO public.ref_expected_reason(uuid, text, description) VALUES ('27a07eea-6aa0-11ea-bc55-0242ac130003', 'noch nicht bekannt', 'Dies ist die Beschreibung für: noch nicht bekannt');
INSERT INTO public.ref_expected_reason(uuid, text, description) VALUES ('27a07fbc-6aa0-11ea-bc55-0242ac130003', 'Wasserrohrbruch', 'Dies ist die Beschreibung für: Wasserrohrbruch');
INSERT INTO public.ref_expected_reason(uuid, text, description) VALUES ('27a08160-6aa0-11ea-bc55-0242ac130003', 'Überregionale Störung', 'Dies ist die Beschreibung für: Überregionale Störung');

-- ---------------------------------------------
-- 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 ('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,
  FK_REF_FAILURE_TYPE integer,
  TITLE character varying(255) NULL,
  DESCRIPTION character varying(255) NULL,
  RESPONSIBILITY character varying(255),
  INTERN_EXTERN character varying(1),
  FK_REF_STATUS_INTERN integer NOT NULL,
  FK_REF_STATUS_EXTERN 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 NOT NULL,
  LONGITUDE numeric(9,6),
  LATITUDE numeric(9,6),
  FK_TBL_FAILURE_INFORMATION_CONDENSED integer,
  CONDENSED boolean,
  CONDENSED_COUNT integer,
  OBJECT_REFERENCE_EXTERNAL_SYSTEM character varying (255),
  PUBLICATION_STATUS character varying (40),
  PUBLICATION_FREETEXT character varying (1024),
  FK_REF_EXPECTED_REASON integer,
  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_FTYPE_FKEY FOREIGN KEY (FK_REF_FAILURE_TYPE)
           REFERENCES public.REF_FAILURE_TYPE (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__STATUSEXT_FKEY FOREIGN KEY (FK_REF_STATUS_EXTERN)
           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_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;

INSERT INTO public.tbl_failure_information(UUID, VERSION_NUMBER, FK_REF_FAILURE_CLASSIFICATION, FK_REF_FAILURE_TYPE, TITLE, DESCRIPTION,  RESPONSIBILITY, INTERN_EXTERN, FK_REF_STATUS_INTERN, FK_REF_STATUS_EXTERN, 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, OBJECT_REFERENCE_EXTERNAL_SYSTEM, PUBLICATION_STATUS, PUBLICATION_FREETEXT, FK_REF_EXPECTED_REASON, CONDENSED, CONDENSED_COUNT, FK_TBL_FAILURE_INFORMATION_CONDENSED, CREATE_DATE, CREATE_USER, MOD_DATE, MOD_USER)
VALUES ('6432a9c9-0384-44af-9bb8-34f2878d7b49',3, 1, 1, 'Stromausfall Murr', 'Es gibt einen Stromausfall im Bereich Murr/Westbezirk', 'Rolf Rudis', 'I', 1, 1, 1, 'NS', null, '2021-01-19 00:00:00', '2021-01-22 00:00:00', '2021-01-22 12:00:00',  'Der Bagger grub zu tief', '71711', 'Murr', 'Westbezirk', 'Ferdinand-Porsche-Straße', '2', '52863a', 'Trafo 1', '124,2323', 2, 9.247952, 48.955700, 'Link - extenes System', 'veröffentlicht', 'Freitext für die Veröffentlichung: Defekt an Trafostation', 1, false, 0, null, '2020-01-22 15:32:15', 'SCRIPT', '2020-01-24 08:02:44', 'SCRIPT');

INSERT INTO public.tbl_failure_information(UUID, VERSION_NUMBER, FK_REF_FAILURE_CLASSIFICATION, FK_REF_FAILURE_TYPE, TITLE, DESCRIPTION,  RESPONSIBILITY, INTERN_EXTERN, FK_REF_STATUS_INTERN, FK_REF_STATUS_EXTERN, 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, OBJECT_REFERENCE_EXTERNAL_SYSTEM, PUBLICATION_STATUS, PUBLICATION_FREETEXT, FK_REF_EXPECTED_REASON, CONDENSED, CONDENSED_COUNT, FK_TBL_FAILURE_INFORMATION_CONDENSED, CREATE_DATE, CREATE_USER, MOD_DATE, MOD_USER)
VALUES ('37aef635-d0d4-4c47-ac25-c0d16c29e35c', 1, 2, 2, 'Gasleck Oldenburg', 'Es gibt ein Gasleck in Oldenburg', 'Bernd Britzel', 'E', 2, 2, 3, 'NS', 'HD', '2021-05-19 00:00:00', '2021-05-22 00:00:00', '2021-05-22 12:00:00', 'HD betroffen', '26133', 'Oldenburg', null, 'Cloppenburger Str.', '302', '1234863-b234', 'ertf', '124,2323', 4, 8.210150, 53.111820, 'Link - extenes System', 'nicht veröffentlicht', 'Freitext: Eine Gasleitung wurde beschädigt', 4, false, 0, null, '2020-02-03 15:15:15', 'SCRIPT', '2020-02-24 20:08:41', 'SCRIPT');


-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- HISTORY-TABLES
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------


-- PUBLIC.HTBL_FAILURE_INFORMATION Automatic generanted History Table DDL --
-- <GENERATED CODE!>

DROP TABLE IF EXISTS PUBLIC.HTBL_FAILURE_INFORMATION;
DROP SEQUENCE IF EXISTS PUBLIC.HTBL_FAILURE_INFORMATION_ID_SEQ;

CREATE SEQUENCE PUBLIC.HTBL_FAILURE_INFORMATION_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

ALTER TABLE PUBLIC.HTBL_FAILURE_INFORMATION_ID_SEQ
  OWNER TO gfi_service;

CREATE TABLE PUBLIC.HTBL_FAILURE_INFORMATION
(
  HID integer NOT NULL DEFAULT nextval('HTBL_FAILURE_INFORMATION_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,
  FK_REF_FAILURE_TYPE integer,
  TITLE character varying(255) NULL,
  DESCRIPTION character varying(255) NULL,
  RESPONSIBILITY character varying (255),
  INTERN_EXTERN character varying (1),
  FK_REF_STATUS_INTERN integer,
  FK_REF_STATUS_EXTERN 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_FAILURE_INFORMATION_CONDENSED integer,
  CONDENSED boolean,
  CONDENSED_COUNT integer,
  OBJECT_REFERENCE_EXTERNAL_SYSTEM character varying (255),
  PUBLICATION_STATUS character varying (40),
  PUBLICATION_FREETEXT character varying (1024),
  FK_REF_EXPECTED_REASON integer,
  CREATE_USER character varying(100),
  CREATE_DATE timestamp without time zone,
  MOD_USER character varying(100),
  MOD_DATE timestamp without time zone,
  CONSTRAINT HTBL_FAILURE_INFORMATION_PKEY PRIMARY KEY (HID)
)
WITH (
  OIDS=FALSE
);

ALTER TABLE PUBLIC.HTBL_FAILURE_INFORMATION
  OWNER TO gfi_service;
GRANT ALL ON TABLE PUBLIC.HTBL_FAILURE_INFORMATION TO gfi_service;

INSERT INTO public.htbl_failure_information(HACTION, HDATE, HUSER, ID, UUID, VERSION_NUMBER, FK_REF_FAILURE_CLASSIFICATION, FK_REF_FAILURE_TYPE, TITLE, DESCRIPTION, RESPONSIBILITY, INTERN_EXTERN, FK_REF_STATUS_INTERN, FK_REF_STATUS_EXTERN, 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, OBJECT_REFERENCE_EXTERNAL_SYSTEM, PUBLICATION_STATUS, PUBLICATION_FREETEXT, FK_REF_EXPECTED_REASON, FK_TBL_FAILURE_INFORMATION_CONDENSED, CONDENSED, CONDENSED_COUNT, CREATE_DATE, CREATE_USER, MOD_DATE, MOD_USER)
VALUES (1, '2020-03-13 00:00:00', 'SCRIPT', 1, '6432a9c9-0384-44af-9bb8-34f2878d7b49', 1, 1, 1, 'Stromausfall Murr', 'Es gibt einen Stromausfall im Bereich Murr/Westbezirk', 'Rolf Rudis', 'I', 1, 1, 1, 'NS', null, '2021-01-19 00:00:00', '2021-01-22 00:00:00', '2021-01-22 12:00:00', 'Der Bagger grub zu tief', '71711', 'Murr', 'Westbezirk', 'Ferdinand-Porsche-Straße', '2', '52863a', 'Trafo 1', '124,2323', 2, 9.247952, 48.955700, 'Link - extenes System', 'veröffentlicht', 'Freitext für die Veröffentlichung: Defekt an Trafostation', 1, null, false, 0, '2020-01-22 15:32:15', 'SCRIPT', '2020-01-24 08:02:44', 'SCRIPT');

INSERT INTO public.htbl_failure_information(HACTION, HDATE, HUSER, ID, UUID, VERSION_NUMBER, FK_REF_FAILURE_CLASSIFICATION, FK_REF_FAILURE_TYPE, TITLE, DESCRIPTION, RESPONSIBILITY, INTERN_EXTERN, FK_REF_STATUS_INTERN, FK_REF_STATUS_EXTERN, 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, OBJECT_REFERENCE_EXTERNAL_SYSTEM, PUBLICATION_STATUS, PUBLICATION_FREETEXT, FK_REF_EXPECTED_REASON, FK_TBL_FAILURE_INFORMATION_CONDENSED, CONDENSED, CONDENSED_COUNT, CREATE_DATE, CREATE_USER, MOD_DATE, MOD_USER)
VALUES (2, '2020-03-13 00:00:00', 'SCRIPT', 2, '37aef635-d0d4-4c47-ac25-c0d16c29e35c', 1, 2, 2, 'Gasleck Oldenburg', 'Es gibt ein Gasleck in Oldenburg', 'Bernd Britzel', 'E', 2, 2, 3, 'NS', 'HD', '2021-05-19 00:00:00', '2021-05-22 00:00:00', '2021-05-22 12:00:00', 'HD betroffen', '26133', 'Oldenburg', null, 'Cloppenburger Str.', '302', '1234863-b234', 'ertf', '124,2323', 4, 8.210150, 53.111820, 'Link - extenes System', 'nicht veröffentlicht', 'Freitext: Eine Gasleitung wurde beschädigt', 4, null, false, 0, '2020-02-03 15:15:15', 'SCRIPT', '2020-02-24 20:08:41', 'SCRIPT');


-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- Tables ADDRESSIMPORT
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------

/*DROP TABLE IF EXISTS public.TBL_ADDRESS CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_ADDRESS_ID_SEQ;*/

-- ---------------------------------------------
-- TABLE TBL_ADDRESS
-- ---------------------------------------------
/*CREATE SEQUENCE public.TBL_ADDRESS_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.TBL_ADDRESS_ID_SEQ
  OWNER TO GFI_SERVICE;

CREATE TABLE public.TBL_ADDRESS
(
  ID integer NOT NULL DEFAULT nextval('TBL_ADDRESS_ID_SEQ'::regclass),
  UUID uuid NOT NULL,
  SDOX1 numeric(9,2) NOT NULL,
  SDOY1 numeric(10,2) NOT NULL,
  G3EFID numeric,
  POSTCODE character varying(30),
  COMMUNITY character varying(255),
  DISTRICT character varying(255),
  STREET character varying(255),
  HOUSENUMBER character varying(30),
  WATER_CONNECTION boolean,
  WATER_GROUP character varying(255),
  GAS_CONNECTION boolean,
  GAS_GROUP character varying(255),
  POWER_CONNECTION boolean,
  STATION_ID character varying(30),
  LONGITUDE numeric(9,6),
  LATITUDE numeric(9,6),
  CONSTRAINT TBL_ADDRESS_PKEY PRIMARY KEY (id)
);

ALTER TABLE public.TBL_ADDRESS
  OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.TBL_ADDRESS TO GFI_SERVICE;

CREATE INDEX idx_tbl_address_g3efid ON public.TBL_ADDRESS ( G3EFID );
CREATE INDEX idx_tbl_address_postcode ON public.TBL_ADDRESS ( POSTCODE );
CREATE INDEX idx_tbl_address_community ON public.TBL_ADDRESS ( COMMUNITY );
CREATE INDEX idx_tbl_address_district ON public.TBL_ADDRESS ( DISTRICT );
CREATE INDEX idx_tbl_address_street ON public.TBL_ADDRESS ( STREET );
CREATE INDEX idx_tbl_address_station_id ON public.TBL_ADDRESS ( STATION_ID );*/


-- ---------------------------------------------
-- TABLE TBL_STATION
-- ---------------------------------------------

/*DROP TABLE IF EXISTS public.TBL_STATION CASCADE;
DROP SEQUENCE IF EXISTS public.TBL_STATION_ID_SEQ;

CREATE SEQUENCE public.TBL_STATION_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.TBL_STATION_ID_SEQ
  OWNER TO GFI_SERVICE;

CREATE TABLE public.TBL_STATION
(
  ID integer NOT NULL DEFAULT nextval('TBL_STATION_ID_SEQ'::regclass),
  UUID uuid NOT NULL,
  SDOX1 numeric(9,2) NOT NULL,
  SDOY1 numeric(10,2) NOT NULL,
  G3EFID numeric,
  STATION_ID character varying(30),
  STATION_NAME character varying(255),
  LONGITUDE numeric(9,6),
  LATITUDE numeric(9,6),
  CONSTRAINT TBL_STATION_PKEY PRIMARY KEY (id)
);

ALTER TABLE public.TBL_STATION
  OWNER TO GFI_SERVICE;
GRANT ALL ON TABLE public.TBL_STATION TO GFI_SERVICE;

CREATE INDEX idx_tbl_station_g3efid ON public.TBL_STATION ( G3EFID );
CREATE INDEX idx_tbl_station_station_id ON public.TBL_STATION ( STATION_ID );
*/

-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- 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,FK_REF_FAILURE_TYPE,TITLE,DESCRIPTION,RESPONSIBILITY,INTERN_EXTERN,FK_REF_STATUS_INTERN,FK_REF_STATUS_EXTERN,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_FAILURE_INFORMATION_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REFERENCE_EXTERNAL_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON,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.FK_REF_FAILURE_TYPE,NEW.TITLE,NEW.DESCRIPTION,NEW.RESPONSIBILITY,NEW.INTERN_EXTERN,NEW.FK_REF_STATUS_INTERN,NEW.FK_REF_STATUS_EXTERN,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_FAILURE_INFORMATION_CONDENSED,NEW.CONDENSED,NEW.CONDENSED_COUNT,NEW.OBJECT_REFERENCE_EXTERNAL_SYSTEM,NEW.PUBLICATION_STATUS,NEW.PUBLICATION_FREETEXT,NEW.FK_REF_EXPECTED_REASON,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,FK_REF_FAILURE_TYPE,TITLE,DESCRIPTION,RESPONSIBILITY,INTERN_EXTERN,FK_REF_STATUS_INTERN,FK_REF_STATUS_EXTERN,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_FAILURE_INFORMATION_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REFERENCE_EXTERNAL_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON,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.FK_REF_FAILURE_TYPE,NEW.TITLE,NEW.DESCRIPTION,NEW.RESPONSIBILITY,NEW.INTERN_EXTERN,NEW.FK_REF_STATUS_INTERN,NEW.FK_REF_STATUS_EXTERN,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_FAILURE_INFORMATION_CONDENSED,NEW.CONDENSED,NEW.CONDENSED_COUNT,NEW.OBJECT_REFERENCE_EXTERNAL_SYSTEM,NEW.PUBLICATION_STATUS,NEW.PUBLICATION_FREETEXT,NEW.FK_REF_EXPECTED_REASON,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,FK_REF_FAILURE_TYPE,TITLE,DESCRIPTION,RESPONSIBILITY,INTERN_EXTERN,FK_REF_STATUS_INTERN,FK_REF_STATUS_EXTERN,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_FAILURE_INFORMATION_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REFERENCE_EXTERNAL_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON,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.FK_REF_FAILURE_TYPE,OLD.TITLE,OLD.DESCRIPTION,OLD.RESPONSIBILITY,OLD.INTERN_EXTERN,OLD.FK_REF_STATUS_INTERN,OLD.FK_REF_STATUS_EXTERN,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_FAILURE_INFORMATION_CONDENSED,OLD.CONDENSED,OLD.CONDENSED_COUNT,OLD.OBJECT_REFERENCE_EXTERNAL_SYSTEM,OLD.PUBLICATION_STATUS,OLD.PUBLICATION_FREETEXT,OLD.FK_REF_EXPECTED_REASON,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();