--THIS FILE IS NEEDED ONLY FOR THE ONLINE UPDATE OR WHEN UPGRADING FROM VERSION 8.9.2.6!!!


DROP TABLE VERSION_8_9_2_6;


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


COMMENT ON COLUMN MMOD.MMOD_FTP_ENCRYPTION IS '0 = Explicit FTP over TLS, 1 = Implicit FTP over TLS, 2 = plain FTP';

DELETE FROM WUSR W WHERE EXISTS (SELECT 1 FROM WUSR WW WHERE W.WUSR_ID<WW.WUSR_ID AND W.WUSR_NAME=WW.WUSR_NAME AND W.WUSR_XCMS_ID=WW.WUSR_XCMS_ID);

DELETE FROM WUSR W WHERE EXISTS (SELECT 1 FROM WUSR WW WHERE W.WUSR_ID<WW.WUSR_ID AND W.WUSR_NAME=WW.WUSR_NAME AND W.WUSR_WEGR_ID=WW.WUSR_WEGR_ID);

ALTER TABLE WUSR ADD CONSTRAINT WUSR_UNIQUE UNIQUE (WUSR_NAME,WUSR_XCMS_ID,WUSR_WEGR_ID) USING INDEX TABLESPACE V8INDEX;

COMMENT ON COLUMN ZINV.ZINV_CORRECTING_ZINV_ID IS 'Invoice has been corrected with a correction invoice, which id stored here. Used for Polish Faktura Korrekta und Italian Fattura';

ALTER TABLE XPAC ADD XPAC_COPY_FROM_RES_TO_GUEST NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE XPAC ADD XPAC_COPY_FROMRES_TOMULTIGUEST NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE ZOTA ADD ZOTA_HOTELCODE VARCHAR2(30) NULL;

ALTER TABLE ZOTA ADD ZOTA_WPSS_ID NUMBER(30) NULL;

ALTER TABLE ZOTA ADD ZOTA_VENDOR_ID VARCHAR2(20) NULL;

ALTER TABLE ZOTA ADD ZOTA_REINSTATE NUMBER(1) NULL;

COMMENT ON COLUMN ZOTA.ZOTA_HOTELCODE IS 'Hotel Code to identify correct hotel';

COMMENT ON COLUMN ZOTA.ZOTA_WPSS_ID IS 'Id of used profile match settings';

COMMENT ON COLUMN ZOTA.ZOTA_VENDOR_ID IS 'Vendor name used for identification';

COMMENT ON COLUMN ZOTA.ZOTA_REINSTATE IS 'Indicates whether reinstate functionality is supported';

ALTER TABLE YRCO MODIFY YRCO_WRPT_ID NULL;

ALTER TABLE YCRF MODIFY YCRF_CROSSREF_1 VARCHAR2(3000);

CREATE TABLE WCIP
(
  WCIP_ID                  NUMBER(30)           NOT NULL,
  WCIP_MAXNIGHTS             NUMBER(3),
  WCIP_PAYMENT            NUMBER(1),
  WCIP_POSTCHARGES            NUMBER(1),
  WCIP_CHECKINFORPAIDONLY NUMBER(1),
CONSTRAINT WCIP_PK PRIMARY KEY (WCIP_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

COMMENT ON TABLE WCIP IS 'Check In Point configuration';

COMMENT ON COLUMN WCIP.WCIP_MAXNIGHTS IS 'Maximum number of nights wich can be booked from the Check In Point';

COMMENT ON COLUMN WCIP.WCIP_PAYMENT IS 'Advanced payment rules for the Check In Point. 0 - nothing has to be paid in advance / 1 - First night price / 2 - All has to be paid';

COMMENT ON COLUMN WCIP.WCIP_POSTCHARGES IS 'Advanced posting rules for the Check In Point. 0 - nothing has to be posted / 1 - First night price / 2 - All stay has to be posted';

CREATE TABLE WCIR
(
  WCIR_ID              NUMBER(30)           NOT NULL,
  WCIR_DATE            DATE,
  WCIR_YRMS_ID         Number(30),
  WCIR_DISPLORDER      Number(4),
CONSTRAINT WCIR_PK PRIMARY KEY (WCIR_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

COMMENT ON TABLE WCIP IS 'Check In Point room assigment config per day';

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

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

CREATE TABLE WCID
(
  WCID_ID                 NUMBER(30)           NOT NULL,
  WCID_DATE               DATE,
  WCID_OnlySelectedRooms  Number(1),
CONSTRAINT WCID_PK PRIMARY KEY (WCID_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE WCID IS 'Suite 8 Kiosk Date config';

COMMENT ON COLUMN WCID.WCID_OnlySelectedRooms IS 'if 1, only rooms configured in WCIR can be assigned on the WCID_DATE';

COMMENT ON COLUMN YCET.YCET_TYPE IS ' 0 = miscellaneous event  1 = availability event  2 = public holiday event     3= conference booking  4 = Day Rebates ';

CREATE TABLE ZRDS
(
  ZRDS_ID                      NUMBER(30) NOT NULL,
  ZRDS_YCEV_ID                 NUMBER(30) NOT NULL,
  ZRDS_FROMDATE                DATE,
  ZRDS_TODATE                  DATE,
  CONSTRAINT ZRDS_PK  PRIMARY KEY  (ZRDS_ID)  USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON COLUMN ZRDS.ZRDS_ID IS 'Unique Id';

COMMENT ON COLUMN ZRDS.ZRDS_YCEV_ID IS 'Link to the calendar event';

ALTER TABLE ZRDS ADD ZRDS_ZRDF_ID NUMBER(30) NOT NULL;

ALTER TABLE ZRDP ADD ZRDP_REBATEORIGIN NUMBER(1) DEFAULT 0;

COMMENT ON COLUMN ZRDP.ZRDP_REBATEORIGIN IS '0 = not defined, 1 = day rebate, 2 = account rebate, 3 = window rebate';

ALTER TABLE RRES ADD (RRES_RESSTATUS NUMBER(1), RRES_NOAVAILREASON NUMBER (1), RRES_INTERNALINFO VARCHAR2(1000));

COMMENT ON COLUMN RRES.RRES_RESSTATUS IS '1=definitive 3=not effecting availability, as an example waitlist /canceled entries will have a RRES_RESSTATUS=3';

COMMENT ON COLUMN RRES.RRES_NOAVAILREASON IS 'Reason why not affecting availability.  1 = Waitlist  3 = Canceled';

COMMENT ON COLUMN RRES.RRES_CANCELED IS '1 - if reservation was canceled. Maintained only for comaptibility. Please refer to rres_resstatus/RRES_NOAVAILREASON';

UPDATE RRES SET RRES_RESSTATUS = 1, RRES_NOAVAILREASON=0 WHERE RRES_RESSTATUS IS NULL AND NVL(RRES_CANCELED,0)= 0;

UPDATE RRES SET RRES_RESSTATUS = 3,  RRES_NOAVAILREASON=3 WHERE RRES_RESSTATUS IS NULL AND RRES_CANCELED = 1;

ALTER TABLE WCIP ADD WCIP_WALKINONLY NUMBER (1);

CREATE TABLE YRKP (
	 YRKP_ID NUMBER(30) NOT NULL
	,YRKP_YRES_ID NUMBER(30)
	,YRKP_ZFAC_ID NUMBER(30)
	,YRKP_PIN VARCHAR2(30)
	,CONSTRAINT YRKP_PK PRIMARY KEY (YRKP_ID) USING INDEX TABLESPACE V8INDEX
	) TABLESPACE V8MAIN;
	
CREATE SEQUENCE SEQ_YRKP INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;	

COMMENT ON TABLE YRKP IS 'Stores Room Key PIN ';

COMMENT ON COLUMN ZCIF.ZCIF_INTERFACECODE IS 'Interface code. 1- TopVital Interface  2.Kiosk  3.Video Checkout';

CREATE TABLE ZURL
(
  ZURL_ID                NUMBER(30)             NOT NULL,
  ZURL_ZOTA_ID           NUMBER(30)             NOT NULL,
  ZURL_MSG_NAME          VARCHAR2(100 BYTE),
  ZURL_URL               VARCHAR2(150 BYTE),
  ZURL_INACTIVE          NUMBER(1),
  CONSTRAINT ZURL_PK PRIMARY KEY (ZURL_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

COMMENT ON COLUMN ZURL.ZURL_ID IS 'unique id';

COMMENT ON COLUMN ZURL.ZURL_ZOTA_ID IS 'link to mmod';

COMMENT ON COLUMN ZURL.ZURL_MSG_NAME IS 'name of xml message';

COMMENT ON COLUMN ZURL.ZURL_URL IS 'URL used to send message';

COMMENT ON COLUMN ZURL.ZURL_INACTIVE IS 'Indicates which setup URL is really used';

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

ALTER TABLE ZOTA ADD ZOTA_THRESHOLD NUMBER(1);

ALTER TABLE ZOTA ADD ZOTA_USE_RTFORRCAVAIL NUMBER(1);

ALTER TABLE ZOTA ADD ZOTA_HTTP_USER VARCHAR2(30);

ALTER TABLE ZOTA ADD ZOTA_HTTP_PWD VARCHAR2(30);

ALTER TABLE ZOTA ADD ZOTA_RESOLVE_TIMEOUT NUMBER(6);

ALTER TABLE ZOTA ADD ZOTA_CONNECT_TIMEOUT NUMBER(6);

ALTER TABLE ZOTA ADD ZOTA_SEND_TIMEOUT NUMBER(6);

ALTER TABLE ZOTA ADD ZOTA_RECEIVE_TIMEOUT NUMBER(6);

COMMENT ON COLUMN ZOTA.ZOTA_THRESHOLD IS 'indicates whether room type threshild should be considered for inventory upload';

COMMENT ON COLUMN ZOTA.ZOTA_USE_RTFORRCAVAIL IS 'indicates whether room types should be sent also for rate availability';

COMMENT ON COLUMN ZOTA.ZOTA_HTTP_USER IS 'http authentication user';

COMMENT ON COLUMN ZOTA.ZOTA_HTTP_PWD IS 'http authentication password';

COMMENT ON COLUMN ZOTA.ZOTA_RESOLVE_TIMEOUT IS 'timeout setting for URL resolve';

COMMENT ON COLUMN ZOTA.ZOTA_CONNECT_TIMEOUT IS 'timeout setting for URL connect';

COMMENT ON COLUMN ZOTA.ZOTA_SEND_TIMEOUT IS 'timeout setting for http send';

COMMENT ON COLUMN ZOTA.ZOTA_RECEIVE_TIMEOUT IS 'timeout setting for http receive';

ALTER TABLE ZOTA MODIFY ZOTA_URL VARCHAR2(120) NULL;

ALTER TABLE ZURL ADD ZURL_MSG_TYPE NUMBER(1);

COMMENT ON COLUMN ZURL.ZURL_MSG_TYPE IS 'Indicates the type of message 0 = Room Type Inventory, 1 = Rate Availability, 2 = Rate Code, 3 = Reservation';

ALTER TABLE ZOTA ADD ZOTA_USE_NETRATES NUMBER(1);

COMMENT ON COLUMN ZOTA.ZOTA_USE_NETRATES IS 'Indicates whether net or gross rates are used';

ALTER TABLE ZOTA ADD ZOTA_USE_TABLERESERVATION NUMBER(1) NULL;

ALTER TABLE WACT MODIFY WACT_REMINDERSTATE NUMBER(3);

ALTER TABLE WSKL DROP ( WSKL_DUEMINL1, WSKL_DUEMINL2, WSKL_REMINDERMIN, WSKL_USEDEFAULTMIN);

COMMENT ON COLUMN WACT.WACT_REMINDERSTATE IS '1-Reminder, 2-Level1, 3-Reminder+Level1, 4-Level2, 5-Reminder+Level2, 6-Level1+Level2, 7-Reminder+Level1+Level2, 15-Not Processed, Old records';

ALTER TABLE W8HP ADD W8HP_ONLIENEPAYMENTIFRAME NUMBER(1);

CREATE TABLE XSCA
(
  XSCA_ID                  NUMBER(30)           NOT NULL,
  XSCA_XSCD_ID             NUMBER(30)           NOT NULL,
  XSCA_FILENAME            VARCHAR2(128 BYTE)   NOT NULL,
  XSCA_ATTACHMENT_WTXT_ID  NUMBER(30)           NOT NULL,
 CONSTRAINT XSCA_PK PRIMARY KEY (XSCA_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

COMMENT ON TABLE XSCA IS 'Scheduled tasks attachments';

COMMENT ON COLUMN XSCA.XSCA_ATTACHMENT_WTXT_ID IS 'link to the binary storage';

COMMENT ON COLUMN XSCA.XSCA_FILENAME IS 'Attachment file name';

COMMENT ON COLUMN XSCA.XSCA_XSCD_ID IS 'Attachment link to the scheduled task';

COMMENT ON COLUMN XSCA.XSCA_ID IS 'WMAA PK';

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

CREATE TABLE MMSG
(
  MMSG_ID          NUMBER(30) NOT NULL,
  MMSG_TYPE        NUMBER(1) NULL,
  MMSG_TIME        DATE NULL,
  MMSG_LEVEL       NUMBER(1) NULL,
  MMSG_FROM        NUMBER(1) NULL,
  MMSG_LOCATION    VARCHAR2(100 BYTE) NULL,
  MMSG_TEXT        VARCHAR2(500 BYTE) NULL,
  MMSG_BACKUP_FILE VARCHAR2(50 BYTE) NULL,
  CONSTRAINT MMSG_PK PRIMARY KEY (MMSG_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

COMMENT ON COLUMN MMSG.MMSG_ID IS 'unique id';

COMMENT ON COLUMN MMSG.MMSG_TYPE IS 'Indicates who made the entry 1 = API, 2 = Mapper';

COMMENT ON COLUMN MMSG.MMSG_TIME IS 'timestamp of entry';

COMMENT ON COLUMN MMSG.MMSG_LEVEL IS 'message level 0 = debug, 1 = trace, 2 = message, 3 = warning, 4 = error, 5 = system ';

COMMENT ON COLUMN MMSG.MMSG_FROM IS 'direction 0 = 1way, 1 = 2way, 2 = system';

COMMENT ON COLUMN MMSG.MMSG_LOCATION IS 'name of the function im which the message were created';

COMMENT ON COLUMN MMSG.MMSG_TEXT IS 'log message';

COMMENT ON COLUMN MMSG.MMSG_BACKUP_FILE IS 'CRS message file processed, when log message were created';

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

ALTER TABLE MCFG MODIFY MCFG_API_LOGFILE VARCHAR2(100);

ALTER TABLE MCFG MODIFY MCFG_MAP_LOGFILE VARCHAR2(100);

ALTER TABLE RRST ADD RRST_WEBONLYSTDTIMES NUMBER(1);

COMMENT ON COLUMN RRST.RRST_WebOnlyStdTimes IS 'In Suite 8 Homepage, only standard rerservation times will be used for this restaurant';

ALTER TABLE RSTF ADD RSTF_WEBENABLED NUMBER(1);

ALTER TABLE XJET ADD XJET_CONFIG VARCHAR2(4000);

ALTER TABLE YCDR MODIFY YCDR_HOURS NUMBER(2);

ALTER TABLE YCRS ADD YCRS_PROBLEMEXEVERSION VARCHAR2(10);

COMMENT ON COLUMN YCRS.YCRS_PROBLEMEXEVERSION IS 'Exe version when error or hash mismatch happened. Automatic retry is based on this information after exe update.';

ALTER TABLE XCED ADD XCED_CENTRAL NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN XCED.XCED_CENTRAL IS '0=normal user 1=central user';

CREATE TABLE SFFD ( 
  SFFD_ID 				NUMBER(30) NOT NULL,
  SFFD_ROLE 			NUMBER(2) NOT NULL,
  SFFD_FIELD_NAME 		VARCHAR2(30),
  SFFD_FIELD_LABEL		VARCHAR2(100),  
  SFFD_FIELD_TYPE 		NUMBER(2),
  SFFD_MAXLENGTH		NUMBER(5),	
  SFFD_MANDATORY_FIELD 	NUMBER(1),
  SFFD_CUSTOM_FIELD 	NUMBER(1),
  SFFD_PICKLIST_FIELD	NUMBER(1),	
  SFFD_REFERENCE_FIELD  NUMBER(1),
  SFFD_REFERENCE_TABLE  VARCHAR2(20),
  SFFD_EXTENDED_FIELD   NUMBER(1),
  SFFD_READONLY			NUMBER(1),	
  SFFD_INACTIVE			NUMBER(1),	
  CONSTRAINT SFFD_PK PRIMARY KEY (SFFD_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF; 

COMMENT ON TABLE SFFD IS 'Sales force field details';

COMMENT ON COLUMN SFFD.SFFD_ROLE IS 'Salesforce object type 1.Account , 2.Contact, 3.User, 4.Oppertunity';

COMMENT ON COLUMN SFFD.SFFD_FIELD_NAME IS 'Sales force Field Name';

COMMENT ON COLUMN SFFD.SFFD_FIELD_TYPE IS 'Sales force Field Type  1.string, 2. number. 3.currency ';

COMMENT ON COLUMN SFFD.SFFD_MAXLENGTH IS 'Maximum Field Length';

COMMENT ON COLUMN SFFD.SFFD_MANDATORY_FIELD IS 'Mandatory flag' ;

COMMENT ON COLUMN SFFD.SFFD_CUSTOM_FIELD IS 'Custom field' ;

COMMENT ON COLUMN SFFD.SFFD_REFERENCE_TABLE IS 'Linked type' ;

COMMENT ON COLUMN SFFD.SFFD_REFERENCE_FIELD IS 'Linked field' ;

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

CREATE TABLE SFPV (
	SFPV_ID 			NUMBER(30) NOT NULL,	
	SFPV_SFFD_ID		NUMBER(30),
	SFPV_DESCRIPTION 	VARCHAR2(100),
	SFPV_VALUE 			VARCHAR2(100),
	CONSTRAINT SFPV_PK PRIMARY KEY (SFPV_ID) USING INDEX TABLESPACE V8INDEX ) TABLESPACE V8CONF;
	
COMMENT ON TABLE SFPV is 'Salesforce Pickup Values' ;

COMMENT on COLUMN SFPV.SFPV_SFFD_ID is 'linked SFFD_id' ;

COMMENT on COLUMN SFPV.SFPV_DESCRIPTION is 'Pick up option Description';

COMMENT on COLUMN SFPV.SFPV_VALUE is 'Pickup value';
	
CREATE SEQUENCE SEQ_SFPV INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;	

CREATE TABLE SFFV (
	SFFV_ID 		NUMBER(30) not null,
	SFFV_TABLENAME VARCHAR2(10),
	SFFV_RECID 	NUMBER(30),
	SFFV_SFFD_ID 	NUMBER(30),
	SFFV_VALUE 		VARCHAR2(1000),
	CONSTRAINT SFFV_PK PRIMARY KEY (SFFV_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;
	 
CREATE SEQUENCE SEQ_SFFV INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;	

COMMENT ON TABLE SFFV is 'Salesforce field values linked Suite8 fields';

COMMENT on COLUMN SFFV.SFFV_SFFD_ID is 'linked SFFD id' ;

COMMENT on COLUMN SFFV.SFFV_TABLENAME is 'Suite 8 Table Name';

COMMENT on COLUMN SFFV.SFFV_RECID is 'Suite8 Table Record id';

COMMENT on COLUMN SFFV.SFFV_VALUE is 'Field Value';
