-- Stand 2015-02-16 (TC)
-- "QDAS_USER" -> "USERGUID" "GUSERNO"

CREATE TABLE QDAS_AREA
( 
Q_KEY 	NUMBER(10),
Q_NO  	VARCHAR2(40),
Q_NAME 	VARCHAR2(80)
);
CREATE UNIQUE INDEX QDAS_AREA ON QDAS_AREA(Q_KEY);

CREATE TABLE QDAS_COST
( 
Q_KEY 	NUMBER(10),
Q_NO  	VARCHAR2(40),
Q_NAME 	VARCHAR2(80)
);
CREATE UNIQUE INDEX QDAS_COST ON QDAS_COST(Q_KEY);

CREATE TABLE QDAS_DEPT
( 
Q_KEY 	NUMBER(10),
Q_NO  	VARCHAR2(40),
Q_NAME 	VARCHAR2(80)
);
CREATE UNIQUE INDEX QDAS_DEPT ON QDAS_DEPT(Q_KEY);

CREATE TABLE QDAS_FILTERS 
( 
Q_USERKEY 	NUMBER(10),
Q_GROUPKEY  	NUMBER(10),
Q_MODUL 	NUMBER(5),
Q_KEY 		NUMBER(5),
Q_SUBKEY 	NUMBER(5),
Q_CONTEXT 	NUMBER(10),
Q_SUBCONTEXT 	NUMBER(5),
Q_HISTORYKEY  	NUMBER(5),
Q_ENTRY 	VARCHAR2(50),
Q_VALUE 	LONG
);
CREATE UNIQUE INDEX QDAS_FILTERS ON QDAS_FILTERS(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY);

CREATE TABLE QDAS_GRAPHICS 
( 
Q_USERKEY 	NUMBER(10),
Q_GROUPKEY  	NUMBER(10),
Q_MODUL 	NUMBER(5),
Q_KEY 		NUMBER(5),
Q_SUBKEY 	NUMBER(5),
Q_CONTEXT 	NUMBER(10),
Q_SUBCONTEXT 	NUMBER(5),
Q_HISTORYKEY  	NUMBER(5),
Q_ENTRY 	VARCHAR2(50),
Q_VALUE 	LONG
);
CREATE UNIQUE INDEX QDAS_GRAPHICS ON QDAS_GRAPHICS(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY);

CREATE TABLE QDAS_QUERIES 
( 
Q_USERKEY 	NUMBER(10),
Q_GROUPKEY  	NUMBER(10),
Q_MODUL 	NUMBER(5),
Q_KEY 		NUMBER(5),
Q_SUBKEY 	NUMBER(5),
Q_CONTEXT 	NUMBER(10),
Q_SUBCONTEXT 	NUMBER(5),
Q_HISTORYKEY  	NUMBER(5),
Q_ENTRY 	VARCHAR2(50),
Q_VALUE 	LONG
);
CREATE UNIQUE INDEX QDAS_QUERIES ON QDAS_QUERIES(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY);

CREATE TABLE QDAS_QUICKFILTERS
( 
Q_USERKEY 	NUMBER(10),
Q_GROUPKEY  	NUMBER(10),
Q_MODUL 	NUMBER(5),
Q_KEY 		NUMBER(5),
Q_SUBKEY 	NUMBER(5),
Q_CONTEXT 	NUMBER(10),
Q_SUBCONTEXT 	NUMBER(5),
Q_HISTORYKEY  	NUMBER(5),
Q_ENTRY 	VARCHAR2(50),
Q_VALUE 	LONG
);
CREATE UNIQUE INDEX QDAS_QUICKFILTERS ON QDAS_QUICKFILTERS(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY);

CREATE TABLE QDAS_STRATEGY
(
COMPANYKEY 		NUMBER(5),
MODUL 			NUMBER(5), 
SUBTYPE 		NUMBER(5),
ACTNO 			NUMBER(5),
COMPANYNAME 		VARCHAR2(100), 
STANDARDSTRATEGY 	NUMBER(5),
STRATEGYVISIBLE 	NUMBER(5),
STRATEGYSTATUS 		NUMBER(10),
STRATEGYCHANGED 	NUMBER(5),
CHANGEDATE 		DATE,
CHANGEUSER 		VARCHAR2(50)
);
CREATE UNIQUE INDEX QDAS_STRATEGY ON QDAS_STRATEGY(COMPANYKEY,MODUL,SUBTYPE,ACTNO);
  
CREATE TABLE QDAS_STRATEGYKEYS
(
USERKEY 	NUMBER(5),
COMPANYKEY 	NUMBER(5),
MODUL 		NUMBER(5), 
SUBTYPE 	NUMBER(5),
ACTNO 		NUMBER(5),
ENTRY 		VARCHAR2(50),
VALUE 		LONG
);
CREATE UNIQUE INDEX QDAS_STRATEGYKEYS ON QDAS_STRATEGYKEYS(USERKEY,COMPANYKEY,MODUL,SUBTYPE,ACTNO,ENTRY);

CREATE TABLE QDAS_STRATEGYKEYS_NEW
(
USERKEY 	NUMBER(5),
COMPANYKEY 	NUMBER(5),
MODUL 		NUMBER(5), 
SUBTYPE 	NUMBER(5),
ACTNO 		NUMBER(5),
ENTRY 		VARCHAR2(50),
VALUE 		LONG
);
CREATE UNIQUE INDEX QDAS_STRATEGYKEYS_NEW ON QDAS_STRATEGYKEYS_NEW(USERKEY,COMPANYKEY,MODUL,SUBTYPE,ACTNO,ENTRY);

CREATE TABLE QDAS_SYSTEM 
( 
Q_USERKEY 	NUMBER(10),
Q_GROUPKEY  	NUMBER(10),
Q_MODUL 	NUMBER(5),
Q_KEY 		NUMBER(5),
Q_SUBKEY 	NUMBER(5),
Q_CONTEXT 	NUMBER(10),
Q_SUBCONTEXT 	NUMBER(5),
Q_HISTORYKEY  	NUMBER(5),
Q_ENTRY 	VARCHAR2(50),
Q_VALUE 	LONG
);
CREATE UNIQUE INDEX QDAS_SYSTEM ON QDAS_SYSTEM(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY);

CREATE TABLE QDAS_SYSTEM_OPTIONS 
( 
Q_KEY 		NUMBER(10),
Q_OPTION 	VARCHAR2(255)
);
CREATE UNIQUE INDEX QDAS_SYSTEM_OPTIONS ON QDAS_SYSTEM_OPTIONS(Q_KEY);

CREATE TABLE QDAS_UPLOAD 
( 
Q_USERKEY 	NUMBER(10),
Q_GROUPKEY  	NUMBER(10),
Q_MODUL 	NUMBER(5),
Q_KEY 		NUMBER(5),
Q_SUBKEY 	NUMBER(5),
Q_CONTEXT 	NUMBER(10),
Q_SUBCONTEXT 	NUMBER(5),
Q_HISTORYKEY  	NUMBER(5),
Q_ENTRY 	VARCHAR2(50),
Q_VALUE 	LONG
);
CREATE UNIQUE INDEX QDAS_UPLOAD ON QDAS_UPLOAD(Q_USERKEY,Q_GROUPKEY,Q_MODUL,Q_KEY,Q_SUBKEY,Q_CONTEXT,Q_SUBCONTEXT,Q_HISTORYKEY,Q_ENTRY);

CREATE TABLE QDAS_USER
(
USERNO		NUMBER(5),
USERGROUP	NUMBER(5),
USERNAME	VARCHAR2(50),
PASSWORD	VARCHAR2(255),
USINIDAT	VARCHAR2(50),
USHIERA		NUMBER(5),
USBEREICH	NUMBER(10),
USABT		NUMBER(10),		
USWERKSTATT	NUMBER(10),
USNAME		VARCHAR2(50),
USFIRMA		VARCHAR2(50),
USABTNAME	VARCHAR2(50),
USFON		VARCHAR2(50),
USFAX		VARCHAR2(50),
USMAIL		VARCHAR2(50),
USZUS		NUMBER(10)
);
CREATE UNIQUE INDEX QDAS_USER ON QDAS_USER(USERNO);

ALTER TABLE QDAS_USER ADD(
USLOGIN		DATE,
USLOGINPC	VARCHAR2(50),
USLOGINIP	VARCHAR2(15),
USLOGOUT	DATE,
USLOGOUTPC	VARCHAR2(50),
USLOGOUTIP	VARCHAR2(15),
USERGUID	VARCHAR2(50),
GUSERNO		NUMBER(5)
);

ALTER TABLE QDAS_USER MODIFY (PASSWORD	VARCHAR2(255));

CREATE TABLE QDAS_USERGROUP
(
GROUPNO	NUMBER(5),
GROUPNAME	VARCHAR2(50)
);
CREATE UNIQUE INDEX QDAS_USERGROUP ON QDAS_USERGROUP(GROUPNO);

CREATE TABLE QDAS_USERRIGHTS
(
ZRUSERNO			NUMBER(10),
ZRUSERGROUP			NUMBER(5),
ZRMESSWEIN			NUMBER(5),
ZRMESSWAEND			NUMBER(5),
ZRPRPLEIN			NUMBER(5),
ZRPRPLAEND			NUMBER(5),
ZRUSERQUERIES			NUMBER(5),
ZRKATEDIT			NUMBER(5),
ZRDBSAVE			NUMBER(5),
ZREVALCHANGE			NUMBER(5),
ZRSYSKONF			NUMBER(5),
ZROPTIONAL			VARCHAR2(50),
ZRPROTECT_AUSWAHLSTRATEGY	NUMBER(5),
ZRPROTECT_DELETE_TEILAUSDB	NUMBER(5),
ZRPROTECT_SELEKTION_INGRAFIK	NUMBER(5),
ZRPROTECT_AUSREISSER_ENTFERNEN	NUMBER(5),
ZRPROTECT_VERTEILUNG_AENDERN	NUMBER(5),
ZRPROTECT_ANALYSE_QRK_AENDERN	NUMBER(5),
ZRPROTECT_SPC_QRK_AENDERN	NUMBER(5),
ZRPROTECT_AUSGABEPUNKT_AENDERN	NUMBER(5)
);

CREATE UNIQUE INDEX QDAS_USERRIGHTS ON QDAS_USERRIGHTS(ZRUSERNO,ZRUSERGROUP);

ALTER TABLE QDAS_USERRIGHTS ADD(ZRDBADMIN			NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_SIGNATUR_VIEW		NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_SIGNATUR_CREATE	NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_PRUEFERNAME_EINGABE 	NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZRUSERADMIN			NUMBER(5) );

ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_SEEALLPARTS		NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_SEE_CHANGE_HISTORY	NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZREXTENDED_MMSELECT		NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZR_EDIT_SUBCAT			NUMBER(5) );


CREATE TABLE QDAS_WORKSHOP
( 
Q_KEY 	NUMBER(10),
Q_NO  	VARCHAR2(40),
Q_NAME 	VARCHAR2(80)
);
CREATE UNIQUE INDEX QDAS_WORKSHOP ON QDAS_WORKSHOP(Q_KEY);

CREATE TABLE SAVE_RESULTS
( 
CONF_NAME 	VARCHAR2(50),
MODUL  		NUMBER(10),
Q_VALUE 	LONG
);
CREATE UNIQUE INDEX SAVE_RESULTS ON SAVE_RESULTS(CONF_NAME,MODUL);

CREATE TABLE SR_MERKMAL_REPORT
( 
METEIL		NUMBER(10),
MEMERKMAL	NUMBER(5),
MEREPORT	VARCHAR2(255),
MODUL		NUMBER(5),
EMAIL		VARCHAR2(255),
PRINTER		VARCHAR2(255),
CONFNAME	VARCHAR2(50),
GRUPPE		NUMBER(10),
TEMPLATE	VARCHAR2(255),
DBID		VARCHAR2(50)
);
CREATE INDEX CONFNAME ON SR_MERKMAL_REPORT(METEIL,MEMERKMAL,CONFNAME,MEREPORT,MODUL);
CREATE INDEX DBID ON SR_MERKMAL_REPORT(DBID);

CREATE TABLE SR_REPORTS
( 
REPORTNO	NUMBER(5),
REPORTNAME	VARCHAR2(255),
REPORTTYPE	NUMBER(5),
MODUL		NUMBER(5),
CONFNAME	VARCHAR2(50),
REPORTTYPETM	NUMBER(5),
DBID		VARCHAR2(50)
);
CREATE INDEX SRR_DBID ON SR_REPORTS(DBID);

CREATE TABLE QDAS_LIC
( 
Q_USERKEY 	NUMBER(10),
Q_MODUL 	NUMBER(5),
Q_KEY 		NUMBER(5),
Q_ENTRY 	NUMBER(10)
);
ALTER TABLE QDAS_LIC ADD(Q_CONTEXT		NUMBER(5) );

CREATE TABLE QDAS_FDA_PROT
( 
USER_GROUP_NO 	NUMBER(10),
TYPE 		NUMBER(5),
Q_KEY 		VARCHAR2(50),
CHANGEDVALUE 	VARCHAR2(255),
CHANGEDATETIME	DATE,
CHANGEUSERNO	NUMBER(10)
);

CREATE TABLE TMPNODE
( 
NODE NUMBER(5),
INDEXNO NUMBER(10),
TMG VARCHAR2(40),
INDEXNEW NUMBER(10)
);

ALTER TABLE TMPNODE ADD(INDEXNEW NUMBER(10));

CREATE TABLE QDAS_OFFLINE
( 
PC_GUID 	VARCHAR2(50),
PC_NAME  	VARCHAR2(50),
PROD_KEY 	NUMBER(5),
LANG		VARCHAR2(50),
Q_CONTEXT	NUMBER(5),
ANZAHL		NUMBER(5),
ABLAUF		DATE
);
CREATE UNIQUE INDEX QDAS_OFFLINE ON SAVE_RESULTS(PC_GUID,PC_Name,Prod_Key,Lang,Q_CONTEXT);

ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_VERTEILUNG_BERECHNEN	NUMBER(5) );

CREATE TABLE AENDERUNGSSTAND
(
TABELLE			VARCHAR2(50),
FELD			VARCHAR2(50),
TYP			VARCHAR2(50),
BEMERKUNG		VARCHAR2(100),
STATUS			VARCHAR2(50),
DATUM			DATE,
VERSION			VARCHAR2(50),
BEMERKUNG_AENDERUNG	VARCHAR2(100),
AENDERUNGSGRUND		LONG
);

ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_CHANGE_DBCONN	NUMBER(5) );

ALTER TABLE QDAS_USERRIGHTS ADD(ZR_EDIT_AUXDATA			NUMBER(5) );

ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_IMPORT		NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_EXPORT		NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_CONFIG_WS		NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_MANAGE_REP		NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_EXEMPTCHARS		NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_MODIFYDISTNO		NUMBER(5) );
ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_CREATEMAINNODE	NUMBER(5) );

ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_AUSWAHLGCSUBSTRATEGY	NUMBER(5) );

ALTER TABLE QDAS_USERRIGHTS ADD(ZR_CLOSE_PDCA			NUMBER(5) );

CREATE TABLE QDAS_USER_ACCESS
( 
Q_USERNO 	NUMBER(10),
Q_LAST_ACCESS	DATE
);
CREATE UNIQUE INDEX QDAS_USER_ACCESS ON QDAS_USER_ACCESS(Q_USERNO);

create table MERKMAL_REPORT
(
	METEIL		NUMBER(10,0),
	MEMERKMAL	NUMBER(5,0),
	MEREPORT	VARCHAR2(255),
	MODUL		NUMBER(5,0),
	EMAIL		VARCHAR2(255),
	PRINTER		VARCHAR2(255),
	CONFNAME	VARCHAR2(50),
	GRUPPE		NUMBER(10,0),
	TEMPLATE	VARCHAR2(255),
	MEREPORTALARM	VARCHAR2(255),
	PDFPATH		VARCHAR2(255),
	WITHOUT_AL_TXT	VARCHAR2(255),
	ALTPDFDNERZ	VARCHAR2(255),
	PDF_URL		VARCHAR2(255),
	DBID		NUMBER(10,0)
)
TABLESPACE QDAS_DEFAULT;

create index MERKMAL_REPORT_TEIL on MERKMAL_REPORT(METEIL);

CREATE TABLE MERKMAL_REPORT_NO_SELECT
(
	METEIL                        	NUMBER(10, 0) ,
	MEMERKMAL                     	NUMBER(5, 0) ,
	MEREPORT                      	VARCHAR2(255) ,
	MODUL                         	NUMBER(5, 0) ,
	CONFNAME                      	VARCHAR2(50),
	DBID		NUMBER(10,0)
)
TABLESPACE QDAS_DEFAULT;
create index MERKMAL_REPORT_NST on MERKMAL_REPORT_NO_SELECT(METEIL);




create table Reports
(
	ReportNo	NUMBER(5,0),
	ReportName	VARCHAR2(255),
	ReportType	NUMBER(5,0),
	Modul		NUMBER(5,0),
	CONFNAME	VARCHAR2(50),
	DBID		NUMBER(10,0)
)
TABLESPACE QDAS_DEFAULT;

create table QDAS_RSJOBS
(
	RSSTATION	VARCHAR2(38),
	RSSECTION	VARCHAR2(255),
	Q_ENTRY		VARCHAR2(255),
	Q_VALUE		LONG
)
TABLESPACE QDAS_DEFAULT;
create index QDAS_RSJOBS on QDAS_RSJOBS(RSSTATION,RSSECTION,Q_ENTRY);

ALTER TABLE QDAS_USERRIGHTS ADD(ZR_DENIED_LOADSAVE_USER_CFG NUMBER(5) );

ALTER TABLE QDAS_USERRIGHTS ADD(ZRPROTECT_LOCK_TESTPLAN	NUMBER(5) );

ALTER TABLE QDAS_USER ADD(
USSIGNATURE       LONG RAW,
USSIGNATUREFILEXT VARCHAR2(10)
);

create table QDAS_TEMPLATE
(
	TEMPLATE_ID		VARCHAR2(255),
	TEMPLATE_BESCHREIBUNG	VARCHAR2(255),
	TEMPLATE_BILD		LONG RAW,
	TEMPLATE_KONFIG		VARCHAR2(1000)
);