
SET ServerOutput ON 

DECLARE
dbversion_neu NUMBER(5) := 2061;
dbversion_alt NUMBER(5);


BEGIN

Select MAX(dbversion) INTO dbversion_alt FROM WgVers;
IF dbversion_neu -1 <> dbversion_alt THEN
   dbms_output.put_line('Für diese Datenbankversion ' || dbversion_alt || ' ist das Skript nicht zulässig!');
ELSE
   dbms_output.put_line('Das Datenbankupdate für die Version ' || dbversion_neu || ' wird ausgeführt!');
  	

-- #### Anfang SQL-Skript ####


execute immediate 'ALTER TABLE ORTE
ADD
(
	AGS			varchar2(12) NULL
)';

execute immediate 'CREATE TABLE SYSTEM_FU_KI
(
	LFD_FU_KI		NUMBER(11) NOT NULL,
	FU_KI_BEZ		varchar2(100) NULL,
 	PRIMARY KEY (LFD_FU_KI)
)';

execute immediate 'ALTER TABLE SYSTEM_FEHL_UNTERLAGEN
ADD
(
	Unterlagen_KI_LFD	NUMBER(11) NULL
)';

execute immediate 'ALTER TABLE DOKUMENTE_VORLAGEN
ADD
(
	V_DMS_DOKTYP		NUMBER(5) NULL,
	V_Langer_Text		NUMBER(5) NULL
)';

execute immediate 'CREATE TABLE SYSTEM_DMS_TYP
(
	TYP_ID			NUMBER(5) NOT NULL,
	TYP_BEZ			varchar2(50) NULL,
	KAT_ID			NUMBER(5) NULL,
	PRIMARY KEY (TYP_ID)
)';

execute immediate 'CREATE TABLE ZUSATZDOLLARVAR_LANG
(
	lfd			NUMBER(11) NOT NULL,
	Beschreibung		varchar2(255) NULL,
	DollarWert		varchar2(1200) NULL,
	Art			NUMBER(5) NULL,
 	PRIMARY KEY(lfd)
)';



-- #### CMIS ####
  
execute immediate 'CREATE TABLE CMIS_MAP
(
	MAP_ID			NUMBER(11) NOT NULL,	
        Parameter		Varchar2(255) NOT NULL,
	Beschreibung		Varchar2(255) NULL,
        PRIMARY KEY (MAP_ID)
)';

execute immediate 'CREATE TABLE CMIS_OBJ_ID
(
	ID			NUMBER(11) NOT NULL,
	FV_KZN			varchar2(8) NULL,
	AGS			varchar2(8) NULL,
	AKTENZN			varchar2(10) NULL,
	CMIS_ID			varchar2(10) NULL,
	CREATE_AKTE		varchar2(8) NULL,
	DELETE_AKTE		varchar2(14) NULL,
	PRIMARY KEY (ID)
)';

execute immediate 'CREATE SEQUENCE CMIS_OBJ_ID_SEQ
  START WITH 1
  NOMAXVALUE';

execute immediate 'CREATE TRIGGER CMIS_OBJ_ID_TRG BEFORE INSERT ON CMIS_OBJ_ID 
  FOR EACH ROW
  BEGIN
    SELECT CMIS_OBJ_ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
  END;';



DELETE FROM WGVERS;
INSERT INTO WGVERS (WGVERSNR, dbversion) VALUES(1, dbversion_neu);


COMMIT;
-- Ende SQL-Skript

END IF; 
END;

