--UPDATE FROM 8.7.0.1 TO 8.7.0.2


DROP TABLE VERSION_8_7_0_1;



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


ALTER TABLE SHIB DROP COLUMN SHIB_FORECAST_GROSSREV;

ALTER TABLE SHIB DROP COLUMN SHIB_FORECAST_NETREV;

ALTER TABLE SHIB DROP COLUMN SHIB_EXPECTED_GROSSREV;

ALTER TABLE SHIB DROP COLUMN SHIB_EXPECTED_NETREV;

ALTER TABLE SHIE DROP COLUMN SHIE_FORECAST_GROSSREV;

ALTER TABLE SHIE DROP COLUMN SHIE_FORECAST_NETREV;

ALTER TABLE SHIE DROP COLUMN SHIE_EXPECTED_GROSSREV;

ALTER TABLE SHIE DROP COLUMN SHIE_EXPECTED_NETREV;

DROP VIEW V8_STAT_CCM_FORREV_BYRES;

DELETE FROM WUSS WHERE WUSS_NAME IN ('AvlTopCol_Date','AvlBottomCol_Date');

DELETE FROM WUSS WHERE WUSS_NAME = 'ContactPreferenceValue';

COMMENT ON COLUMN XCMS.XCMS_CONTACTPREF IS 'Contact Preference  value >0 = company has been selected';

ALTER TABLE SHIB ADD SHIB_SHIS_XCMS_ID NUMBER(30);

COMMENT ON COLUMN SHIB.SHIB_SHIS_XCMS_ID IS 'xcms taken from shis';

ALTER TABLE SHIB ADD SHIB_SHIS_GUESTNAME VARCHAR2(35);

COMMENT ON COLUMN SHIB.SHIB_SHIS_GUESTNAME IS 'guestname from shis';

CREATE TABLE ZITI (
    ZITI_ID NUMBER(30) NOT NULL
   ,ZITI_TYPE NUMBER(2) NOT NULL
   ,ZITI_INTERFACEID NUMBER(30)
   ,CONSTRAINT ZITI_PK PRIMARY KEY (ZITI_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON COLUMN ZITI.ZITI_TYPE IS 'see constantsandtypes.ttriggerinterfacetypes. in example 0=mmod interfaces';

COMMENT ON COLUMN ZITI.ZITI_INTERFACEID IS 'for mmod interfaces this is mmod_id.';

CREATE TABLE ZITSBACKUP AS SELECT * FROM ZITS;

ALTER TABLE ZITS ADD ZITS_ZITI_ID NUMBER(30);

INSERT INTO ZITI (SELECT SEQ_ZITI.NEXTVAL,0,MMOD_ID FROM MMOD WHERE MMOD_TWOWAY=1 AND NOT EXISTS (SELECT 1 FROM ZITI WHERE MMOD.MMOD_TWOWAY=1 AND MMOD.MMOD_ID=ZITI.ZITI_INTERFACEID));

UPDATE ZITS SET ZITS_ZITI_ID=(SELECT ZITI_ID FROM ZITI WHERE ZITI_INTERFACEID=ZITS_MMOD_ID);

ALTER TABLE ZITS MODIFY ZITS_ZITI_ID NOT NULL;

ALTER TABLE ZITS DROP COLUMN ZITS_MMOD_ID;

DROP TABLE ZITSBACKUP;

ALTER TABLE ZIPF ADD ZIPF_ZITI_ID NUMBER(30);

UPDATE ZIPF SET ZIPF_ZITI_ID=(SELECT ZITI_ID FROM ZITI WHERE ZITI_INTERFACEID=ZIPF_MMOD_ID);

ALTER TABLE ZIPF MODIFY ZIPF_ZITI_ID NOT NULL;

ALTER TABLE ZIPF DROP COLUMN ZIPF_MMOD_ID;

ALTER TABLE ZIPH ADD ZIPH_ZITI_ID NUMBER(30);

UPDATE ZIPH SET ZIPH_ZITI_ID=(SELECT ZITI_ID FROM ZITI WHERE ZITI_INTERFACEID=ZIPH_MMOD_ID);

ALTER TABLE ZIPH MODIFY ZIPH_ZITI_ID NOT NULL;

ALTER TABLE ZIPH DROP COLUMN ZIPH_MMOD_ID;

ALTER TABLE ZITR ADD ZITR_TABLEID2 NUMBER(30);

ALTER TABLE ZIPT ADD ZIPT_TABLEID2 NUMBER(30);

ALTER TABLE XCAP ADD (XCAP_AUDIT_TRAILNUM VARCHAR2(15));

ALTER TABLE MMOD ADD (MMOD_ADD_OVERBOOK NUMBER(1,0) NULL);

ALTER TABLE MMOD ADD (MMOD_HOTEL_KEY VARCHAR2(128) NULL);

COMMENT ON COLUMN MMOD.MMOD_ADD_OVERBOOK IS 'indicates whether overbooking values are used for rtav';

COMMENT ON COLUMN MMOD.MMOD_HOTEL_KEY IS 'extra hotel key for WebRes';

ALTER TABLE MMOD ADD (MMOD_WEBS_WSDL VARCHAR2(200) NULL);

ALTER TABLE MMOD ADD (MMOD_WEBS_SERVICE VARCHAR2(50) NULL);

ALTER TABLE MMOD ADD (MMOD_WEBS_PORT VARCHAR2(50) NULL);

COMMENT ON COLUMN MMOD.MMOD_WEBS_WSDL IS 'WSDL for WebService used by the mapper';

COMMENT ON COLUMN MMOD.MMOD_WEBS_SERVICE IS 'Service for WebService used by the mapper';

COMMENT ON COLUMN MMOD.MMOD_WEBS_PORT IS 'Port for WebService used by the mapper';

CREATE TABLE ZEMS (
	ZEMS_ID NUMBER(30,0) NOT NULL,
	ZEMS_YRES_ID NUMBER(30,0) NOT NULL,
	ZEMS_ON_NOTIFICATION_TIME DATE,
	ZEMS_OFF_NOTIFICATION_TIME DATE,
	ZEMS_YRMS_ID NUMBER(30,0) NULL,
	CONSTRAINT ZEMS_PK PRIMARY KEY (ZEMS_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

CREATE INDEX ZEMS_YRES_ID ON ZEMS(ZEMS_YRES_ID) TABLESPACE V8INDEX; 

COMMENT ON TABLE ZEMS  IS 'Energy management interface notification';

COMMENT ON COLUMN ZEMS.ZEMS_ID IS 'WMAA PK';

COMMENT ON COLUMN ZEMS.ZEMS_YRES_ID IS 'Reservation link';

COMMENT ON COLUMN ZEMS.ZEMS_ON_NOTIFICATION_TIME IS 'Time of the switching ON';

COMMENT ON COLUMN ZEMS.ZEMS_OFF_NOTIFICATION_TIME IS 'Time of the switching OFF';

COMMENT ON COLUMN ZEMS.ZEMS_YRMS_ID IS 'Room number link';

CREATE TABLE RRSC (
	RRSC_ID NUMBER(30,0) NOT NULL,
	RRSC_RRST_ID NUMBER(30) NOT NULL,
	RRSC_STARTDATE DATE NOT NULL,
	RRSC_ENDDATE DATE NOT NULL,
	CONSTRAINT RRSC_PK PRIMARY KEY (RRSC_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN ;

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

CREATE INDEX RRSC_RRST_ID ON RRSC(RRSC_RRST_ID) TABLESPACE V8INDEX; 

COMMENT ON TABLE RRSC IS 'Restaurant closing times';

COMMENT ON COLUMN RRSC.RRSC_ID IS 'RRSC PK';

COMMENT ON COLUMN RRSC.RRSC_RRST_ID IS 'Restaurant link';

COMMENT ON COLUMN RRSC.RRSC_STARTDATE IS 'Restaurant closed from..';

COMMENT ON COLUMN RRSC.RRSC_ENDDATE IS 'Restaurant closed to..';

ALTER TABLE RRST ADD (RRST_OPEN_MON NUMBER(1) DEFAULT 1,  
                      RRST_OPEN_TUE NUMBER(1) DEFAULT 1, 
                      RRST_OPEN_WED NUMBER(1) DEFAULT 1, 
                      RRST_OPEN_THU NUMBER(1) DEFAULT 1, 
                      RRST_OPEN_FRI NUMBER(1) DEFAULT 1, 
                      RRST_OPEN_SAT NUMBER(1) DEFAULT 1, 
                      RRST_OPEN_SUN NUMBER(1) DEFAULT 1);

ALTER TABLE YCAT ADD (YCAT_MAXNOOFPERSONS NUMBER(3));

COMMENT ON COLUMN YCAT.YCAT_MAXNOOFPERSONS IS 'Max No of persons (Including children) which can be booked for this room type';

CREATE TABLE WIDT (
    WIDT_ID NUMBER(30) NOT NULL
   ,WIDT_DESCRIPTION VARCHAR2(30) NOT NULL
   ,WIDT_PRECONFIG_TEXT VARCHAR2(30)
   ,WIDT_TYPE NUMBER(1)
   ,WIDT_DATATYPE VARCHAR2(1)
   ,WIDT_LINKID NUMBER(30)
   ,WIDT_LEVEL NUMBER(2)	
   ,CONSTRAINT WIDT_PK PRIMARY KEY (WIDT_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE WIDT IS ' IFC Data through commands';

COMMENT ON COLUMN WIDT.WIDT_DATATYPE IS '0 - pre configured text , 1 - user input value';

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

ALTER TABLE SHIB ADD SHIB_SHIS_XCMS_ID NUMBER(30);

COMMENT ON COLUMN SHIB.SHIB_SHIS_XCMS_ID IS 'XCMS_ID from SHIS';

ALTER TABLE SHIB ADD SHIB_SHIS_GUESTNAME VARCHAR2(35);

COMMENT ON COLUMN SHIB.SHIB_SHIS_GUESTNAME IS 'GUESTNAME from SHIS';

ALTER TABLE SHIB ADD SHIB_YRES_ID NUMBER(30);

COMMENT ON COLUMN SHIB.SHIB_YRES_ID IS 'YRES_ID from SHIS';

