--UPDATE FROM 8.9.0.2 TO 8.9.0.3


DROP TABLE VERSION_8_9_0_2;



/*  VERSION_8_9_0_3  */
CREATE TABLE VERSION_8_9_0_3 (
	 DUMMY	NUMBER	    NULL
)
;

CREATE TABLE WINT (
  WINT_ID NUMBER(30) NOT NULL
  ,WINT_INTERVAL NUMBER(3) NOT NULL
  ,WINT_INTERVALTYPE NUMBER(1) NOT NULL
  ,WINT_SNAP NUMBER(1) NOT NULL
  ,CONSTRAINT WINT_PK PRIMARY KEY (WINT_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

CREATE SEQUENCE SEQ_WINT INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE WINT IS 'interval table for table reservations';

COMMENT ON COLUMN WINT.WINT_ID IS 'ID';

COMMENT ON COLUMN WINT.WINT_INTERVAL IS 'interval';

COMMENT ON COLUMN WINT.WINT_INTERVALTYPE IS 'interval type; 0 - minutes; 1 - hours; 2 - days; 3 - weeks; 4 - months';

COMMENT ON COLUMN WINT.WINT_SNAP IS 'used as snap also';

INSERT INTO WINT(WINT_ID, WINT_INTERVAL, WINT_INTERVALTYPE, WINT_SNAP) (SELECT SEQ_WINT.NEXTVAL, 30, 0, 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM WINT WHERE WINT_INTERVAL = 30 AND WINT_INTERVALTYPE = 0 AND WINT_SNAP = 1));

INSERT INTO WINT(WINT_ID, WINT_INTERVAL, WINT_INTERVALTYPE, WINT_SNAP) (SELECT SEQ_WINT.NEXTVAL, 1, 1, 0 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM WINT WHERE WINT_INTERVAL = 1 AND WINT_INTERVALTYPE = 1 AND WINT_SNAP = 0));

INSERT INTO WINT(WINT_ID, WINT_INTERVAL, WINT_INTERVALTYPE, WINT_SNAP) (SELECT SEQ_WINT.NEXTVAL, 4, 1, 0 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM WINT WHERE WINT_INTERVAL = 4 AND WINT_INTERVALTYPE = 1 AND WINT_SNAP = 0));

INSERT INTO WINT(WINT_ID, WINT_INTERVAL, WINT_INTERVALTYPE, WINT_SNAP) (SELECT SEQ_WINT.NEXTVAL, 1, 3, 0 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM WINT WHERE WINT_INTERVAL = 1 AND WINT_INTERVALTYPE = 3 AND WINT_SNAP = 0));

INSERT INTO WINT(WINT_ID, WINT_INTERVAL, WINT_INTERVALTYPE, WINT_SNAP) (SELECT SEQ_WINT.NEXTVAL, 2, 3, 0 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM WINT WHERE WINT_INTERVAL = 2 AND WINT_INTERVALTYPE = 3 AND WINT_SNAP = 0));

INSERT INTO WINT(WINT_ID, WINT_INTERVAL, WINT_INTERVALTYPE, WINT_SNAP) (SELECT SEQ_WINT.NEXTVAL, 1, 4, 0 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM WINT WHERE WINT_INTERVAL = 1 AND WINT_INTERVALTYPE = 4 AND WINT_SNAP = 0));

UPDATE WUSS SET WUSS_VALUE=(SELECT MIN(WINT_ID) FROM WINT WHERE NVL(WINT_SNAP, 0) =1 ) WHERE WUSS_NAME='TableReservationSnap';

ALTER TABLE WPCA ADD WPCA_HASEIDREADER NUMBER(1);

COMMENT ON COLUMN WPCA.WPCA_HASEIDREADER IS 'Terminal has eID reader';

ALTER TABLE MQU1 ADD MQU1_MMOD_ID NUMBER(30); 

ALTER TABLE MQU2 ADD MQU2_MMOD_ID NUMBER(30);

ALTER TABLE MQU3 ADD MQU3_MMOD_ID NUMBER(30);

ALTER TABLE MQU4 ADD MQU4_MMOD_ID NUMBER(30);

ALTER TABLE MMOD ADD MMOD_ERR_NOTIFY_EMAIL VARCHAR2(2000);

COMMENT ON COLUMN MMOD.MMOD_ERR_NOTIFY_EMAIL IS 'email address of receiver of error email'; 

CREATE TABLE MCRT
(
  MCRT_ID               NUMBER(30)              NOT NULL,
  MCRT_MCNV_ID          NUMBER(30)              NOT NULL,
  MCRT_CRS_CODE         VARCHAR2(30),
  MCRT_MIN_NUM_PERSONS  NUMBER(2),
  MCRT_MAX_NUM_PERSONS  NUMBER(2),
  CONSTRAINT MCRT_PK PRIMARY KEY (MCRT_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;
  
CREATE SEQUENCE SEQ_MCRT INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON COLUMN MCRT.MCRT_ID IS 'unique id';

COMMENT ON COLUMN MCRT.MCRT_MCNV_ID IS 'linkage to the master room type in mcnv';

COMMENT ON COLUMN MCRT.MCRT_CRS_CODE IS 'additional crs code';

COMMENT ON COLUMN MCRT.MCRT_MIN_NUM_PERSONS IS 'indicates the min. number of persons of the room type';

COMMENT ON COLUMN MCRT.MCRT_MAX_NUM_PERSONS IS 'indicates the max. number of persons of the room type';

ALTER TABLE MCNV ADD MCNV_CRS_CATEGORY VARCHAR2(30);

COMMENT ON COLUMN MCNV.MCNV_CRS_CATEGORY IS 'crs room type category';

UPDATE YEST SET YEST.YEST_POSTREVENUE=1;

ALTER TABLE WEDA MODIFY(WEDA_ROLE NUMBER(2));

ALTER TABLE XCEE MODIFY(XCEE_MODULE NUMBER(2));

ALTER TABLE WSCM MODIFY(WSCM_MODULE NUMBER(2));

ALTER TABLE WEDA ADD WEDA_WTXT_ID NUMBER(30);

ALTER TABLE XSCD ADD XSCD_ADDDEFAULTATTACHMENTS NUMBER(1);

INSERT INTO WUSS(WUSS_ID, WUSS_XCMS_ID, WUSS_NAME, WUSS_VALUE) SELECT SEQ_WUSS.NEXTVAL, 0, 'SupervisorUserGroup', (SELECT WEGR_ID FROM WEGR WHERE WEGR_SHORTDESC='SUP' AND ROWNUM = 1) FROM DUAL WHERE ( (SELECT COUNT(WUSS_ID) FROM WUSS WHERE WUSS_NAME = 'SupervisorUserGroup') = 0 ) AND ( (SELECT COUNT(WEGR_ID) FROM WEGR WHERE WEGR_SHORTDESC='SUP') > 0);

ALTER TABLE YRCO ADD YRCO_RRES_ID NUMBER(30);

COMMENT ON COLUMN YRCO.YRCO_RRES_ID IS 'Linked to table reservation id';

ALTER TABLE XPAC ADD XPAC_APPLRESOURCE NUMBER(1);

COMMENT ON COLUMN XPAC.XPAC_APPLRESOURCE IS 'Applied to function spaces, catering items, standard menues';

ALTER TABLE RRES ADD RRES_PRICE NUMBER(30,4);

COMMENT ON COLUMN RRES.RRES_PRICE IS 'Price of the resource reservation. Not available for tables';

ALTER TABLE XPAC ADD XPAC_ADDRIND NUMBER(1);

COMMENT ON COLUMN XPAC.XPAC_ADDRIND IS 'Individual address';

ALTER TABLE XPAC ADD XPAC_ADDRCOMP NUMBER(1);

COMMENT ON COLUMN XPAC.XPAC_ADDRCOMP IS 'Company address';

CREATE TABLE XAAL (
  XAAL_ID NUMBER(30) NOT NULL
  ,XAAL_XADR_ID NUMBER(30) NOT NULL
  ,XAAL_XPDA_ID NUMBER(30) NOT NULL
  ,XAAL_XPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT XAAL_PK PRIMARY KEY (XAAL_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;
  
CREATE SEQUENCE SEQ_XAAL INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE XAAL IS 'Linked Attributes to Addresses Table';

COMMENT ON COLUMN XAAL.XAAL_ID IS 'Id of xaal';

COMMENT ON COLUMN XAAL.XAAL_XADR_ID IS 'Address id';

COMMENT ON COLUMN XAAL.XAAL_XPDA_ID IS 'Attribute value id';

COMMENT ON COLUMN XAAL.XAAL_XPAC_ID IS 'Linked Attribute Category Id';

UPDATE YBIS SET YBIS_FROM_YRES_ID = NULL WHERE  YBIS_FROM_YRES_ID = 0;

UPDATE YBIS SET YBIS_FROM_ZFAC_ID = NULL WHERE  YBIS_FROM_ZFAC_ID = 0;

UPDATE YBIS SET YBIS_TO_ZFAC_ID = NULL WHERE  YBIS_TO_ZFAC_ID = 0;

UPDATE YBIS SET YBIS_TO_YRES_ID = NULL WHERE  YBIS_TO_YRES_ID = 0;

ALTER TABLE YEBS ADD YEBS_DISPLORDER NUMBER(3);

COMMENT ON COLUMN YEBS.YEBS_DISPLORDER IS 'Display order';

ALTER TABLE YPUB ADD (YPUB_DEFAULTCREDITS NUMBER(3));

ALTER TABLE YFSC ADD (YFSC_DEFAULTCREDITS NUMBER(3), YFSC_DEPARTURECREDITS NUMBER(3), YFSC_CLEANCREDITS NUMBER(3));

ALTER TABLE YHRL ADD (YHRL_YPUB_ID NUMBER(30), YHRL_YFSC_ID NUMBER(30));

COMMENT ON COLUMN YPUB.YPUB_DEFAULTCREDITS IS ' Housekeeping credits (credits are inidicating how many units as for instance minutes the maid needs to clean the public place)';

COMMENT ON COLUMN YFSC.YFSC_DEFAULTCREDITS IS ' Housekeeping credits (credits are inidicating how many units as for instance minutes the maid needs to clean the function space)';

COMMENT ON COLUMN YFSC.YFSC_DEPARTURECREDITS IS ' Housekeeping credits on departure';

COMMENT ON COLUMN YFSC.YFSC_CLEANCREDITS IS 'Housekeeping credits for clean function space';

COMMENT ON COLUMN YHRL.YHRL_YPUB_ID IS 'Link to Public places (can only be filled if yrms_id and yfsc_id are null)';

COMMENT ON COLUMN YHRL.YHRL_YFSC_ID IS 'Link to Function spaces (can only be filled if yrms_id and ypub_id are null)';

ALTER TABLE YPUB ADD (YPUB_COUNTINATTENDANTPLAN NUMBER(1));

COMMENT ON COLUMN YPUB.YPUB_COUNTINATTENDANTPLAN IS '1 - The public place should be taken into consideration when calculating attendant plan';

ALTER TABLE XCTL MODIFY XCTL_XMNR_ID NULL;

ALTER TABLE YCMC ADD YCMC_FMGR_ID NUMBER(30);

ALTER TABLE YCMC ADD YCMC_ROLE NUMBER(2);

UPDATE YCMC SET YCMC_ROLE = 1;

ALTER TABLE YOUT ADD YOUT_XADR_ID NUMBER(30,0);

ALTER TABLE YOUT ADD YOUT_ADVERTISEMENT1 VARCHAR2(120);

ALTER TABLE YOUT ADD YOUT_ADVERTISEMENT2 VARCHAR2(120);

ALTER TABLE YOUT ADD YOUT_ONECHECKPERTABLE NUMBER(1);

ALTER TABLE YOUT ADD YOUT_ASKCOURSEORDER NUMBER(1);

ALTER TABLE YOUT ADD YOUT_ROLE NUMBER(3);

ALTER TABLE YOUT ADD YOUT_RRST_ID NUMBER(30,0);
