db/oracle/02_create_DB_1.0.0.sql

CREATE SEQUENCE HTBL_RESP_ID_SEQ
  INCREMENT BY 1;

CREATE SEQUENCE REF_BRANCH_ID_SEQ
  INCREMENT BY 1;

CREATE SEQUENCE REF_GRID_TERRITORY_ID_SEQ
  INCREMENT BY 1;

CREATE SEQUENCE REF_NOTIF_STATUS_ID_SEQ
  INCREMENT BY 1;

CREATE SEQUENCE TBL_NOTIF_ID_SEQ
  INCREMENT BY 1;

CREATE SEQUENCE TBL_NOTIF_INCIDENT_SEQ
  INCREMENT BY 1;

CREATE SEQUENCE TBL_RESP_ID_SEQ
  INCREMENT BY 1;


CREATE TABLE REF_BRANCH
(
  "ID" NUMBER NOT NULL,
  "NAME" VARCHAR2(50) NOT NULL,
  "DESCRIPTION" VARCHAR2(255),
  CONSTRAINT REF_BRANCH_PKEY PRIMARY KEY ("ID")
);


CREATE OR REPLACE TRIGGER "REF_BRANCH_BIR"
BEFORE INSERT ON REF_BRANCH
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT REF_BRANCH_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "REF_BRANCH_BIR" ENABLE;

-- TABLE: PUBLIC.REF_GRID_TERRITORY
CREATE TABLE REF_GRID_TERRITORY
(
  "ID" INTEGER NOT NULL,
  "NAME" VARCHAR2(50) NOT NULL,
  "DESCRIPTION" VARCHAR2(255),
  "FK_REF_MASTER" INTEGER NOT NULL,
  CONSTRAINT REF_GRID_TERRITORY_PKEY PRIMARY KEY ("ID"),
  CONSTRAINT FK_REF_GRID_TERRITORY_SELF FOREIGN KEY ("FK_REF_MASTER")
      REFERENCES REF_GRID_TERRITORY ("ID")
      ON DELETE CASCADE
);

CREATE OR REPLACE TRIGGER "REF_GRID_TERRITORY_BIR"
BEFORE INSERT ON "REF_GRID_TERRITORY"
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT REF_GRID_TERRITORY_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "REF_GRID_TERRITORY_BIR" ENABLE;

-- INDEX: PUBLIC.FKI_FK_GRID_TERRITORY_SELF
CREATE INDEX FKI_FK_GRID_TERRITORY_SELF
  ON "REF_GRID_TERRITORY"
  ("FK_REF_MASTER");

-- INDEX: REF_GRID_TERR_DESCR_UNIQUE
CREATE UNIQUE INDEX REF_GRID_TERR_DESCR_UNIQUE
  ON "REF_GRID_TERRITORY" ("DESCRIPTION");


CREATE TABLE REF_NOTIFICATION_STATUS
(
  "ID" INTEGER NOT NULL,
  "NAME" VARCHAR2(50) NOT NULL,
  CONSTRAINT REF_NOTIFICATION_STATUS_PKEY PRIMARY KEY ("ID")
);


CREATE OR REPLACE TRIGGER "REF_NOTIFICATION_STATUS_BIR"
BEFORE INSERT ON "REF_NOTIFICATION_STATUS"
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT REF_NOTIF_STATUS_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "REF_NOTIFICATION_STATUS_BIR" ENABLE;

-- TABLE: PUBLIC.REF_VERSION
CREATE TABLE REF_VERSION
(
  "ID" INTEGER NOT NULL,
  "VERSION" VARCHAR2(100) NOT NULL,
  CONSTRAINT REF_VERSION_PKEY PRIMARY KEY ("ID")
);


-- TABLE: PUBLIC.TBL_NOTIFICATION



CREATE TABLE "TBL_NOTIFICATION"
(
  "ID" INTEGER NOT NULL,
  "INCIDENT_ID" INTEGER,
  "VERSION" INTEGER DEFAULT 0 NOT NULL,
  "FK_REF_BRANCH" INTEGER,
  "NOTIFICATION_TEXT" VARCHAR2(200) NOT NULL,
  "FREE_TEXT" VARCHAR2(1000),
  "FREE_TEXT_EXTENDED" VARCHAR2(1000),
  "FK_REF_NOTIFICATION_STATUS" INTEGER NOT NULL,
  "RESPONSIBILITY_FORWARDING" VARCHAR2(100),
  "REMINDER_DATE" TIMESTAMP,
  "EXPECTED_FINISHED_DATE" TIMESTAMP,
  "RESPONSIBILITY_CONTROL_POINT" VARCHAR2(100),
  "BEGIN_DATE" TIMESTAMP NOT NULL,
  "FINISHED_DATE" TIMESTAMP,
  "CREATE_USER" VARCHAR2(100) NOT NULL,
  "CREATE_DATE" TIMESTAMP NOT NULL,
  "MOD_USER" VARCHAR2(100),
  "MOD_DATE" TIMESTAMP,
  "FK_REF_GRID_TERRITORY" INTEGER,
  "ADMIN_FLAG" NUMBER DEFAULT 0 NOT NULL,

  CONSTRAINT TBL_NOTIFICATION_PKEY PRIMARY KEY ("ID"),
  CONSTRAINT FK_NOTIFICATION_FK_BRANCH FOREIGN KEY ("FK_REF_BRANCH")
      REFERENCES REF_BRANCH ("ID"),
  CONSTRAINT FK_NOTIF_FK_GRID_TERR FOREIGN KEY ("FK_REF_GRID_TERRITORY")
      REFERENCES REF_GRID_TERRITORY ("ID"),
  CONSTRAINT FK_NOTIF_FK_STATUS FOREIGN KEY ("FK_REF_NOTIFICATION_STATUS")
      REFERENCES REF_NOTIFICATION_STATUS ("ID")
);

CREATE OR REPLACE TRIGGER "TBL_NOTIFICATION_BIR"
BEFORE INSERT ON "TBL_NOTIFICATION"
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT TBL_NOTIF_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "TBL_NOTIFICATION_BIR" ENABLE;


CREATE OR REPLACE TRIGGER "TBL_NOTIF_INCIDENT_BIR"
BEFORE INSERT ON "TBL_NOTIFICATION"
FOR EACH ROW
 WHEN (NEW."INCIDENT_ID" IS NULL) BEGIN
  SELECT TBL_NOTIF_INCIDENT_SEQ.NEXTVAL
  INTO   :NEW."INCIDENT_ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "TBL_NOTIF_INCIDENT_BIR" ENABLE;

-- INDEX: PUBLIC.CREATE_DATE_IDX

CREATE INDEX CREATE_DATE_IDX
  ON TBL_NOTIFICATION( "CREATE_DATE" );


-- INDEX: PUBLIC.FKI_NOTIFICATION_FK_BRANCH
CREATE INDEX FKI_NOTIFICATION_FK_BRANCH
  ON TBL_NOTIFICATION("FK_REF_BRANCH");


-- INDEX: PUBLIC.FKI_NOTIFICATION_FK_GRID_TERRITORY
CREATE INDEX FKI_NOTIF_FK_GRID_TERR
  ON TBL_NOTIFICATION ("FK_REF_GRID_TERRITORY");


-- INDEX: PUBLIC.FKI_NOTIFICATION_FK_STATUS
CREATE INDEX FKI_NOTIFICATION_FK_STATUS
  ON TBL_NOTIFICATION("FK_REF_NOTIFICATION_STATUS");


-- INDEX: PUBLIC.MOD_DATE_IDX
CREATE INDEX MOD_DATE_IDX
  ON TBL_NOTIFICATION ("MOD_DATE");


-- INDEX: PUBLIC.RESPONSIBILITY_FORWARDING_IDX
CREATE INDEX RESPONSIBILITY_FORWARDING_IDX
  ON TBL_NOTIFICATION( "RESPONSIBILITY_FORWARDING");


-- INDEX: PUBLIC.TBL_NOTIFICATION_INCIDENT_VERSION_UNIQUE
CREATE UNIQUE INDEX TBL_NOTIF_INCID_VERSION_UNIQUE
  ON TBL_NOTIFICATION ("INCIDENT_ID", "VERSION");

-- TABLE: PUBLIC.TBL_RESPONSIBILITY
CREATE TABLE TBL_RESPONSIBILITY
(
  "ID" NUMBER NOT NULL,
  "FK_REF_GRID_TERRITORY" NUMBER NOT NULL,
  "FK_REF_BRANCH" NUMBER NOT NULL,
  "RESPONSIBLE_USER" VARCHAR2(100) NOT NULL,
  "NEW_RESPONSIBLE_USER" VARCHAR2(100),
  "CREATE_USER" VARCHAR2(100) NOT NULL,
  "CREATE_DATE" TIMESTAMP NOT NULL,
  "MOD_USER" VARCHAR2(100),
  "MOD_DATE" TIMESTAMP,
  CONSTRAINT TBL_RESPONSIBILITY_PKEY PRIMARY KEY ("ID"),
  CONSTRAINT FK_BRANCH FOREIGN KEY ("FK_REF_BRANCH")
      REFERENCES REF_BRANCH ("ID"),
  CONSTRAINT FK_GRID_TERRITORY FOREIGN KEY ("FK_REF_GRID_TERRITORY")
      REFERENCES REF_GRID_TERRITORY ("ID")
);

CREATE OR REPLACE TRIGGER "TBL_RESPONSIBILITY_BIR"
BEFORE INSERT ON "TBL_RESPONSIBILITY"
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT TBL_RESP_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "TBL_RESPONSIBILITY_BIR" ENABLE;


-- INDEX: PUBLIC.TBL_RESPONSIBILITY_TERRITORY_BRANCH_USER_UNIQUE
CREATE UNIQUE INDEX TBL_RESP_TERR_BRANCH_UNIQUE
  ON TBL_RESPONSIBILITY ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH");

-- TABLE: PUBLIC.HTBL_RESPONSIBILITY
CREATE TABLE HTBL_RESPONSIBILITY
(
  "ID" NUMBER NOT NULL,
  "FK_REF_GRID_TERRITORY" NUMBER NOT NULL,
  "FK_REF_BRANCH" NUMBER NOT NULL,
  "TRANSFER_DATE" TIMESTAMP NOT NULL,
  "TRANSACTION_ID" NUMBER NOT NULL,
  "RESPONSIBLE_USER" VARCHAR2(100) NOT NULL,
  "FORMER_RESPONSIBLE_USER" VARCHAR2(100),
  "CREATE_USER" VARCHAR2(100) NOT NULL,
  "CREATE_DATE" TIMESTAMP NOT NULL,
  "MOD_USER" VARCHAR2(100),
  "MOD_DATE" TIMESTAMP,
  CONSTRAINT HTBL_RESPONSIBILITY_PKEY PRIMARY KEY ("ID"),
  CONSTRAINT HTBL_RESP_FK_BRANCH FOREIGN KEY ("FK_REF_BRANCH")
      REFERENCES REF_BRANCH ("ID"),
  CONSTRAINT HTBL_RESP_FK_GRID_TERRITORY FOREIGN KEY ("FK_REF_GRID_TERRITORY")
      REFERENCES REF_GRID_TERRITORY ("ID")
);

CREATE OR REPLACE TRIGGER "HTBL_RESPONSIBILITY_BIR"
BEFORE INSERT ON "HTBL_RESPONSIBILITY"
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT HTBL_RESP_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "HTBL_RESPONSIBILITY_BIR" ENABLE;

-- INDEX: PUBLIC.HTBL_RESP_TRANS_ID_UNIQUE
CREATE UNIQUE INDEX HTBL_RESP_TRANS_ID_UNIQUE
  ON HTBL_RESPONSIBILITY  ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "TRANSACTION_ID");


CREATE OR REPLACE VIEW VIEW_ACTIVE_NOTIFICATION AS
 SELECT S."ID",
    S."INCIDENT_ID",
    S."VERSION",
    S."FK_REF_BRANCH",
    S."NOTIFICATION_TEXT",
    S."FREE_TEXT",
    S."FREE_TEXT_EXTENDED",
    S."FK_REF_NOTIFICATION_STATUS",
    S."RESPONSIBILITY_FORWARDING",
    S."REMINDER_DATE",
    S."EXPECTED_FINISHED_DATE",
    S."RESPONSIBILITY_CONTROL_POINT",
    S."BEGIN_DATE",
    S."FINISHED_DATE",
    S."CREATE_USER",
    S."CREATE_DATE",
    S."MOD_USER",
    S."MOD_DATE",
    S."FK_REF_GRID_TERRITORY",
    S."ADMIN_FLAG"
   FROM ( SELECT TBL_NOTIFICATION."ID",
            TBL_NOTIFICATION."INCIDENT_ID",
            TBL_NOTIFICATION."VERSION",
            TBL_NOTIFICATION."FK_REF_BRANCH",
            TBL_NOTIFICATION."NOTIFICATION_TEXT",
            TBL_NOTIFICATION."FREE_TEXT",
            TBL_NOTIFICATION."FREE_TEXT_EXTENDED",
            TBL_NOTIFICATION."FK_REF_NOTIFICATION_STATUS",
            TBL_NOTIFICATION."RESPONSIBILITY_FORWARDING",
            TBL_NOTIFICATION."REMINDER_DATE",
            TBL_NOTIFICATION."EXPECTED_FINISHED_DATE",
            TBL_NOTIFICATION."RESPONSIBILITY_CONTROL_POINT",
            TBL_NOTIFICATION."BEGIN_DATE",
            TBL_NOTIFICATION."FINISHED_DATE",
            TBL_NOTIFICATION."CREATE_USER",
            TBL_NOTIFICATION."CREATE_DATE",
            TBL_NOTIFICATION."MOD_USER",
            TBL_NOTIFICATION."MOD_DATE",
            TBL_NOTIFICATION."FK_REF_GRID_TERRITORY",
            TBL_NOTIFICATION."ADMIN_FLAG",
            RANK() OVER (PARTITION BY TBL_NOTIFICATION."INCIDENT_ID" ORDER BY "TBL_NOTIFICATION"."VERSION" DESC) AS RANK
           FROM TBL_NOTIFICATION) S
  WHERE S.RANK = 1;


INSERT INTO "REF_NOTIFICATION_STATUS" ( "ID", "NAME" ) VALUES ( 1, 'Offen' );
INSERT INTO "REF_NOTIFICATION_STATUS" ( "ID", "NAME" ) VALUES ( 2, 'In arbeit' );
INSERT INTO "REF_NOTIFICATION_STATUS" ( "ID", "NAME" ) VALUES ( 3, 'Erledigt' );
INSERT INTO "REF_NOTIFICATION_STATUS" ( "ID", "NAME" ) VALUES ( 4, 'Geschlossen' );

INSERT INTO "REF_BRANCH" ("ID", "NAME", "DESCRIPTION" ) VALUES ( 1, 'S', 'Strom' );
INSERT INTO "REF_BRANCH" ("ID", "NAME", "DESCRIPTION" ) VALUES ( 2, 'G', 'Gas' );
INSERT INTO "REF_BRANCH" ("ID", "NAME", "DESCRIPTION" ) VALUES ( 3, 'F', 'Fernwärme' );
INSERT INTO "REF_BRANCH" ("ID", "NAME", "DESCRIPTION" ) VALUES ( 4, 'W', 'Wasser' );

INSERT INTO "REF_GRID_TERRITORY" ("ID", "NAME", "DESCRIPTION", "FK_REF_MASTER") VALUES ( 1, 'MA', 'Mannheim', 1);
INSERT INTO "REF_GRID_TERRITORY" ("ID", "NAME", "DESCRIPTION", "FK_REF_MASTER") VALUES ( 2, 'OF', 'Offenbach', 2);

INSERT INTO REF_VERSION VALUES (1, '1.0.0_ORA');

INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (1, 1, 'Admin','Admin', CURRENT_TIMESTAMP);
INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (1, 2, 'Admin','Admin', CURRENT_TIMESTAMP);
INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (1, 3, 'Admin','Admin', CURRENT_TIMESTAMP);
INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (1, 4, 'Admin','Admin', CURRENT_TIMESTAMP);
INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (2, 1, 'Admin','Admin', CURRENT_TIMESTAMP);
INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (2, 2, 'Admin','Admin', CURRENT_TIMESTAMP);
INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (2, 3, 'Admin','Admin', CURRENT_TIMESTAMP);
INSERT INTO "TBL_RESPONSIBILITY" ("FK_REF_GRID_TERRITORY", "FK_REF_BRANCH", "RESPONSIBLE_USER", "CREATE_USER", "CREATE_DATE") VALUES (2, 4, 'Admin','Admin', CURRENT_TIMESTAMP);