db/oracle/create_db.sql

DROP SEQUENCE htbl_responsibility_id_seq;
DROP SEQUENCE ref_branch_id_seq;
DROP SEQUENCE ref_grid_territory_id_seq;
DROP SEQUENCE ref_notification_status_id_seq;
DROP SEQUENCE tbl_notification_id_seq;
DROP SEQUENCE tbl_notification_incident_seq;
DROP SEQUENCE tbl_responsibility_id_seq;

DROP TABLE TBL_RESPONSIBILITY;
DROP TABLE HTBL_RESPONSIBILITY;
DROP TABLE TBL_NOTIFICATION;
DROP TABLE REF_BRANCH;
DROP TABLE REF_GRID_TERRITORY;
DROP TABLE REF_NOTIFICATION_STATUS;
DROP TABLE REF_VERSION;


CREATE SEQUENCE htbl_responsibility_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_notification_status_id_seq
  INCREMENT BY 1;  
  
CREATE SEQUENCE tbl_notification_id_seq
  INCREMENT BY 1;
  
CREATE SEQUENCE tbl_notification_incident_seq
  INCREMENT BY 1;
  
CREATE SEQUENCE tbl_responsibility_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_NOTIFICATION_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_NOTIFICATION_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_NOTIFICATION_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_RESPONSIBILITY_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_RESPONSIBILITY_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, 'FW', '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, 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, 3, 'otto','admin', CURRENT_TIMESTAMP);
INSERT INTO "TBL_RESPONSIBILITY" ("fk_ref_grid_territory", "fk_ref_branch", "responsible_user", "create_user", "create_date") VALUES (2, 4, 'otto','admin', CURRENT_TIMESTAMP);