--UID:           1.4.0_201805015
--NAME:          1.4.0_kfoqsdb-update.sql
--VERSION:       1.04.00.00
--DESCRIPTION:   UPDATE



CREATE TABLE GE1001(
  "LOGCODE" VARCHAR(1) NOT NULL,
  "PROJEKT" VARCHAR(2) NOT NULL,
  "LOGID" VARCHAR(6) NOT NULL,
  "VERSIONPROTOKOLL" VARCHAR(4) NOT NULL,
  "SYSTEMMODUS" VARCHAR(1) NOT NULL,
  "VERARBEITUNGSMODUS" VARCHAR(1) NOT NULL,
  "TRAEGERID" VARCHAR(2) NOT NULL,
  "ORGEINHEITID" VARCHAR(10),
  "BENUTZERID" VARCHAR(128) NOT NULL,
  "APPID" VARCHAR(8) NOT NULL,
  "BEARBGRUND" VARCHAR(50),
  "ZIELTRAEGERID" VARCHAR(2) NOT NULL,
  "AUSWAHLKRITERIUM1" VARCHAR(20) NOT NULL,
  "AUSWAHLKRITERIUM2" VARCHAR(20),
  "AUSWAHLKRITERIUM3" VARCHAR(20),
  "DATUMPROTOKOLL" VARCHAR(8) NOT NULL,
  "ZEITPROTOKOLL" VARCHAR(12) NOT NULL,
  "TRANSACTIONID" VARCHAR(13) NOT NULL,
  "FEHLERCODE" VARCHAR(6),
  "ZUSATZDATEN" VARCHAR(255)

);


ALTER TABLE GE1001 ADD CONSTRAINT "GE1001_PK" PRIMARY KEY("DATUMPROTOKOLL", "ZEITPROTOKOLL", "TRANSACTIONID");


CREATE TABLE ReportType(
  "ID" bigint NOT NULL DEFAULT nextval('kfoqsdb_seq'::regclass),
  "sql" character varying,
  version bigint NOT NULL DEFAULT 0,
  "Erstelldatum" timestamp without time zone NOT NULL

);


ALTER TABLE ReportType ADD CONSTRAINT "ReportType_PK" PRIMARY KEY ("ID");


ALTER TABLE ReportType ADD COLUMN role character varying DEFAULT 'KFOQSDB.BEARB';
ALTER TABLE ReportType ADD COLUMN type character varying;
ALTER TABLE ReportType ADD COLUMN name character varying;

ALTER TABLE "Benachrichtigung" ALTER COLUMN "ID" SET DEFAULT nextval('kfoqsdb_seq'::regclass);
ALTER TABLE "Auswertung" ALTER COLUMN "ID" SET DEFAULT nextval('kfoqsdb_seq'::regclass);


ALTER TABLE "Benachrichtigung" DROP  CONSTRAINT "FK_Benachrichtigung_Traeger";
ALTER TABLE "Auswertung" DROP  CONSTRAINT "FK_Auswertung_Traeger";


ALTER TABLE "Traeger" ADD COLUMN bundesland CHARACTER VARYING;


DELETE FROM "Traeger";

INSERT INTO "Traeger"("Code","QSBerechtigt","Sanktionsberechtigt","version","bundesland") values(11,true,true,1,'W');
INSERT INTO "Traeger"("Code","QSBerechtigt","Sanktionsberechtigt","version","bundesland") values(12,true,true,1,'NOE');
INSERT INTO "Traeger"("Code","QSBerechtigt","Sanktionsberechtigt","version","bundesland") values(13,true,true,1,'BGLD');
INSERT INTO "Traeger"("Code","QSBerechtigt","Sanktionsberechtigt","version","bundesland") values(14,true,true,1,'OOE');
INSERT INTO "Traeger"("Code","QSBerechtigt","Sanktionsberechtigt","version","bundesland") values(15,true,true,1,'STMK');
INSERT INTO "Traeger"("Code","QSBerechtigt","Sanktionsberechtigt","version","bundesland") values(16,true,true,1,'KTN');
INSERT INTO "Traeger"("Code","QSBerechtigt","Sanktionsberechtigt","version","bundesland") values(17,true,true,1,'SBG');
INSERT INTO "Traeger"("Code","QSBerechtigt","Sanktionsberechtigt","version","bundesland") values(18,true,true,1,'T');
INSERT INTO "Traeger"("Code","QSBerechtigt","Sanktionsberechtigt","version","bundesland") values(19,true,true,1,'VBG');
