--UPDATE FROM 8.9.2.0 TO 8.9.3.0


DROP TABLE VERSION_8_9_2_0;


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


CREATE TABLE WUNO
(
  WUNO_ID           	NUMBER(30)      NOT NULL,
  WUNO_NOTES			VARCHAR2(2000)	NOT NULL,
  WUNO_XCED_ID			NUMBER(30) 	NOT NULL,
  WUNO_DATE				DATE,
  CONSTRAINT WUNO_PK  	PRIMARY KEY  (WUNO_ID)  USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE WUNO IS 'User Notes';

ALTER TABLE WATY ADD WATY_WAGR_ID NUMBER(30); 

COMMENT ON COLUMN WATY.WATY_WAGR_ID IS 'Linked to Maintenance Group';

CREATE TABLE WAGR (
 WAGR_ID NUMBER(30) NOT NULL
,WAGR_WHDE_ID NUMBER(30) NOT NULL 
,WAGR_LONGDESC VARCHAR2(30)
,WAGR_SHORTDESC VARCHAR2(3)
,WAGR_DISPLORDER NUMBER(3)
,WAGR_INACTIVE NUMBER(1)
,WAGR_DEFAULT NUMBER(1)
,CONSTRAINT WAGR_PK PRIMARY KEY (WAGR_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE WAGR IS 'Maintenance Group';

COMMENT ON COLUMN WAGR.WAGR_WHDE_ID IS 'Linked to Resp. Departments';

COMMENT ON COLUMN WAGR.WAGR_INACTIVE IS '0=active, 1=inactive';

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

INSERT INTO WHDE (WHDE_ID,WHDE_SHORTDESC,WHDE_LONGDESC,WHDE_DISPLORDER,WHDE_INACTIVE,WHDE_ROLE) SELECT SEQ_WHDE.NEXTVAL, 'GEN', 'General',(SELECT NVL(MAX(WHDE_DISPLORDER),0)+1 FROM WHDE), 0, 0 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM WHDE WHERE UPPER(WHDE_SHORTDESC) = upper('GEN') );

INSERT INTO WAGR (WAGR_ID, WAGR_WHDE_ID, WAGR_LONGDESC, WAGR_SHORTDESC, WAGR_DISPLORDER, WAGR_INACTIVE, WAGR_DEFAULT) SELECT SEQ_WAGR.NEXTVAL, (SELECT WHDE_ID FROM WHDE WHERE UPPER(WHDE_SHORTDESC)=UPPER('GEN')), 'General', 'GEN', '1',0,1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM WAGR WHERE UPPER(WAGR_SHORTDESC) = upper('GEN') ); 

UPDATE WATY SET WATY_WAGR_ID = (SELECT WAGR_ID FROM WAGR WHERE UPPER(WAGR_SHORTDESC) = UPPER('GEN')) WHERE  WATY_WATG_ID = (SELECT WATG_ID FROM WATG WHERE WATG_ROLE = 6);

ALTER TABLE WUSR MODIFY WUSR_NAME VARCHAR2(200);

ALTER TABLE WACT ADD WACT_REQUESTLINKED_ID NUMBER(30);

COMMENT ON COLUMN WACT.WACT_REQUESTLINKED_ID IS 'Request Employee';

ALTER TABLE ZINV ADD ZINV_MANUALNUMBER VARCHAR(60);

UPDATE YRMS SET YRMS_WALKORDER = YRMS_DISPLORDER;

ALTER TABLE WAMT ADD WAMT_WORJ_ID NUMBER(30);

COMMENT ON COLUMN WAMT.WAMT_WORJ_ID IS 'Maintenance Origin';

COMMENT ON COLUMN WORJ.WORJ_ROLE IS 'where role = 1 means guest generated and makes wact_xcms_id mandatory when entering the maintenance task; 2 = Automatic ';

ALTER TABLE YBOM ADD YBOM_YCAD_ID NUMBER(30);

COMMENT ON COLUMN YBOM.YBOM_YCAD_ID IS 'Linked segment id';

ALTER TABLE YEMN MODIFY YEMN_QUANTITY NUMBER(5);

ALTER TABLE WPHS ADD WPHS_MULTIHOTELCONFIG NUMBER(1) DEFAULT 0 NOT NULL;

UPDATE WUSS SET WUSS.WUSS_VALUE=4 WHERE WUSS.WUSS_VALUE=2 AND WUSS.WUSS_NAME LIKE 'confratefilter' AND WUSS.WUSS_XCMS_ID=0;

UPDATE WUSS SET WUSS.WUSS_VALUE=3 WHERE WUSS.WUSS_VALUE=1 AND WUSS.WUSS_NAME LIKE 'confratefilter' AND WUSS.WUSS_XCMS_ID=0;

ALTER TABLE QNRA ADD QNRA_TEXT VARCHAR2(1000);

COMMENT ON COLUMN QNRA.QNRA_TEXT IS 'Guest entered answers/comments';

ALTER TABLE WATY MODIFY WATY_WAGR_ID NUMBER(30) DEFAULT 0;

UPDATE WATY SET WATY_WAGR_ID = 0 WHERE WATY_WAGR_ID IS NULL;

ALTER TABLE MMOD ADD MMOD_USE_SALEDATE NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_USE_SALEDATE IS 'indicates whether the crs booking date is stored in yres_saledate';

ALTER TABLE WATG ADD WATG_TABRES NUMBER(1);  

ALTER TABLE WACT ADD WACT_RRES_ID NUMBER(30);

ALTER TABLE WAVL ADD (
    WAVL_WCOL1 NUMBER(30) 
   ,WAVL_WCOL2 NUMBER(30) 
   ,WAVL_WCOL3 NUMBER(30)
   ,WAVL_WCOL4 NUMBER(30)
   ,WAVL_FULLYOCCUPIED_WCOL NUMBER(30));

ALTER TABLE CART ADD CART_OVERRIDEREDEEMAMOUNT NUMBER(1); 

COMMENT ON COLUMN CART.CART_OVERRIDEREDEEMAMOUNT IS 'if 1, redemption amount will be requested';

ALTER TABLE COUP ADD COUP_REDEMPTIONNOTE VARCHAR2(1000);

ALTER TABLE YSEC ADD YSEC_DISPLORDER NUMBER(3);

COMMENT ON COLUMN YSEC.YSEC_DISPLORDER IS 'Display Order';

COMMENT ON COLUMN WPHS.WPHS_CRS_ROLE IS '1=central 2=mirror 3=partner 4=direct partner null=no crs functionality';

ALTER TABLE XCCS MODIFY XCCS_ISSUE_DATE VARCHAR2(6);

ALTER TABLE MMOD ADD MMOD_NOGUESTS_ALLOWED NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_NOGUESTS_ALLOWED IS 'indicates whether a reservation can be created with number of guests 0. Default = No';

ALTER TABLE MMOD ADD MMOD_USE_FA NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_USE_FA IS 'indicates whether a finacial account will be created for a new group. Default = Yes';

ALTER TABLE SHIS ADD SHIS_YDET_YCAT_ID_RATE NUMBER(30);

ALTER TABLE SHIS ADD SHIS_YCAT_SHORTDESC_RATE Varchar2(30);

CREATE TABLE YFBK ( 
 YFBK_OBJECT_ID VARCHAR2(30) NOT NULL 
,YFBK_NAME VARCHAR2(100) 
,YFBK_ACCESSTOKEN VARCHAR2(200) 
,YFBK_TYPE NUMBER (1) 
,YFBK_LASTUSED NUMBER (1) 
,YFBK_LASTLOGIN DATE
,YFBK_DISPLORDER NUMBER (2) 
,CONSTRAINT YFBK_PK PRIMARY KEY (YFBK_OBJECT_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

COMMENT ON TABLE YFBK IS 'Facebook accounts table';

ALTER TABLE WGBS ADD WGBS_ACCESSDENY NUMBER(1); 

ALTER TABLE W8HP ADD W8HP_USERATEMARKET NUMBER(1);

COMMENT ON COLUMN W8HP.W8HP_USERATEMARKET IS 'S8HP should user rate market code if it is defined';

ALTER TABLE TCCR MODIFY TCCR_VALUE NUMBER(30,4);

ALTER TABLE ZDCL ADD ZDCL_POSTONLYIFMANUAL NUMBER(1);

COMMENT ON COLUMN ZDCL.ZDCL_POSTONLYIFMANUAL IS 'Post Citytax only for manual postings';

ALTER TABLE YEVM ADD YEVM_MANUALDISPLORDER NUMBER(1);

COMMENT ON COLUMN YEVM.YEVM_MANUALDISPLORDER IS 'Display order manually changed by user';

ALTER TABLE SHIB MODIFY SHIB_YBOM_NOOFATTENDEES NUMBER(5);

ALTER TABLE SHIE MODIFY SHIE_YEVM_NOOFATTENDEES NUMBER(5);

ALTER TABLE MCNV ADD MCNV_PMS_PROF_ID Number(30);

COMMENT ON COLUMN MCNV.MCNV_PMS_PROF_ID IS 'we can link a profile of a specific type to a specifc code. i.e a source profile to a source code';

UPDATE YRES SET YRES_YACC_ID=NULL WHERE YRES_YACC_ID=-1;

ALTER TABLE YPRT ADD YPRT_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE XPDC MODIFY XPDC_NAME VARCHAR2(70);

ALTER TABLE YCAT ADD YCAT_KEYIFC_ICFG_ID NUMBER(30);

ALTER TABLE ZFAG ADD ZFAG_KEYIFC_ICFG_ID NUMBER(30);

COMMENT ON COLUMN YCAT.YCAT_KEYIFC_ICFG_ID IS 'linked Key Interface';

COMMENT ON COLUMN ZFAG.ZFAG_KEYIFC_ICFG_ID IS 'linked Key Interface';

UPDATE YCAT SET YCAT_KEYIFC_ICFG_ID = (SELECT ICFG_ID FROM ICFG WHERE ICFG_CMD_KEY <> 0 AND ICFG_ACTIVE = 1 AND ROWNUM= 1) WHERE NVL(YCAT_KEYIFC_ICFG_ID, 0) = 0;

UPDATE ZFAG SET ZFAG_KEYIFC_ICFG_ID = (SELECT ICFG_ID FROM ICFG WHERE ICFG_CMD_KEY <> 0 AND ICFG_ACTIVE = 1 AND ROWNUM= 1) WHERE ZFAG_SEND_IFC > 0 AND NVL(ZFAG_KEYIFC_ICFG_ID, 0) = 0;

UPDATE XPDA SET XPDA_ICFG_ID = (SELECT ICFG_ID FROM ICFG WHERE  ICFG_CMD_KEY <> 0 AND ICFG_ACTIVE = 1 AND ROWNUM= 1) WHERE XPDA_XPAC_ID = 25 AND NVL(XPDA_ICFG_ID, 0) = 0;

ALTER TABLE WDBG DROP COLUMN WDBG_BIGTEXT;

ALTER TABLE WDBG ADD WDBG_BIGTEXT CLOB;

ALTER TABLE WLOR ADD WLOR_COUP_ID NUMBER(30);

ALTER TABLE WLOR MODIFY WLOR_YRES_ID NULL;

INSERT INTO WLOR (WLOR_ID, WLOR_WLOP_ID, WLOR_COUP_ID) (SELECT SEQ_WLOR.NEXTVAL, COUP_WLOP_ID, COUP_ID FROM COUP CI WHERE COUP_WLOP_ID IS NOT NULL AND NOT EXISTS (SELECT 1 FROM WLOR WHERE WLOR_COUP_ID=CI.COUP_ID));

COMMENT ON COLUMN COUP.COUP_WLOP_ID IS 'Obsolete. Please use WLOR_COUP_ID' ;

ALTER TABLE W8HP ADD W8HP_QNR_SENDNOTIFICATION NUMBER(1) ;

ALTER TABLE W8HP ADD W8HP_QNR_WRPT_ID NUMBER(30);

ALTER TABLE W8HP ADD W8HP_QNR_EMAILID VARCHAR2(30);

ALTER TABLE W8HP ADD W8HP_QNR_SUBJECT_WRPT_ID NUMBER(30);

COMMENT ON COLUMN W8HP.W8HP_QNR_SENDNOTIFICATION IS 'Send email notification, when guest fills out questionnaire';

COMMENT ON COLUMN W8HP.W8HP_QNR_WRPT_ID IS 'Email Template id';

COMMENT ON COLUMN W8HP.W8HP_QNR_EMAILID IS 'Email id of the receiver';

COMMENT ON COLUMN W8HP.W8HP_QNR_WRPT_ID IS 'Email Subject Report id';

COMMENT ON COLUMN RRST.RRST_THRESHOLD IS 'Number of persons closed for web booking.';

ALTER TABLE YCDP ADD YCDP_ONLINEPAYMENT NUMBER(1);

COMMENT ON COLUMN YCDP.YCDP_ONLINEPAYMENT IS '1=collect online payment directly on homepage';

ALTER TABLE W8HP ADD W8HP_PAYDIRECT NUMBER(1);

COMMENT ON COLUMN W8HP.W8HP_PAYDIRECT IS 'if 1, it is not neccessary to enter a credit card and and the customer is routed to the defined pay system before reservation is confirmed.' ;

ALTER TABLE YCEV ADD YCEV_REQUESTCC NUMBER(1);

COMMENT ON COLUMN YCEV.YCEV_REQUESTCC IS '1=Reservations request cc in this timerange';

ALTER TABLE YCDP ADD YCDP_MANDATORYCC NUMBER(1);

COMMENT ON COLUMN YCDP.YCDP_MANDATORYCC IS '1=Reservations request cc in this timerange';

ALTER TABLE WATY ADD WATY_ISRESOLVEDNOTE NUMBER(1);

COMMENT ON COLUMN WATY.WATY_ISRESOLVEDNOTE IS 'Resolved Note';

ALTER TABLE WACT ADD WACT_RESOLVEDNOTE_WANT_ID NUMBER(30);

COMMENT ON COLUMN WACT.WACT_RESOLVEDNOTE_WANT_ID IS 'Resolved note id';  

ALTER TABLE WMSL ADD WMSL_DEFAULTFORSOLVE NUMBER(1);

COMMENT ON COLUMN WMSL.WMSL_DEFAULTFORSOLVE IS 'Default for solved'; 

ALTER TABLE WAGR ADD WAGR_NOTIFYHKSCREEN NUMBER(1) default 0;

COMMENT ON COLUMN WAGR.WAGR_NOTIFYHKSCREEN IS 'Notify in HK Screen'; 

ALTER TABLE WATY ADD WATY_ISRESOLVEDNOTE NUMBER(1);

COMMENT ON COLUMN WATY.WATY_ISRESOLVEDNOTE IS 'Resolved Note';

ALTER TABLE WACT ADD WACT_RESOLVEDNOTE_WANT_ID NUMBER(30);

COMMENT ON COLUMN WACT.WACT_RESOLVEDNOTE_WANT_ID IS 'Resolved note id';  

ALTER TABLE WMSL ADD WMSL_DEFAULTFORSOLVE NUMBER(1);

COMMENT ON COLUMN WMSL.WMSL_DEFAULTFORSOLVE IS 'Default for solved'; 

ALTER TABLE WAGR ADD WAGR_NOTIFYHKSCREEN NUMBER(1) default 0;

COMMENT ON COLUMN WAGR.WAGR_NOTIFYHKSCREEN IS 'Notify in HK Screen'; 

ALTER TABLE MMOD ADD MMOD_USE_RT_CATEGORY NUMBER(1,0);

ALTER TABLE MMOD ADD MMOD_CODE_TO_PROFILE NUMBER(1,0);

COMMENT ON COLUMN MMOD.MMOD_USE_RT_CATEGORY IS 'Indicates the use of a room type list to simulate a rate category';

COMMENT ON COLUMN MMOD.MMOD_CODE_TO_PROFILE IS 'Indicates the conversion of a code like source code or channel also to a profile category liek Source Profile';

DROP TRIGGER ZPOS_ZARR_ID;

ALTER TABLE ZCNV MODIFY(ZCNV_SHORTDESC VARCHAR2(5));

ALTER TABLE YRCH ADD YRCH_AVAIL_YRCH_ID NUMBER (30);

COMMENT ON COLUMN YRCH.YRCH_AVAIL_YRCH_ID IS 'Follow availability of linked rate';

DROP TABLE YTRR CASCADE CONSTRAINTS;

DROP PROCEDURE V8_SYS_YTRR;

CREATE TABLE XDEV (
    XDEV_ID NUMBER(30) NOT NULL
    ,XDEV_TYPE NUMBER(1)
    ,XDEV_ENABLEFASTSCANNING NUMBER(1)
    ,XDEV_IDCARD_XPDG_ID NUMBER(30)
    ,XDEV_PASSPORT_XPDG_ID NUMBER(30)
    ,XDEV_AUTOATTACHPICTURE NUMBER(1)
    ,XDEV_PICTURE_WDCG_ID NUMBER(30)
    ,XDEV_PROMPTWHENMISSINGFIELDS NUMBER(1)
    ,XDEV_QUESTIONIFSECONDSIDE NUMBER(1)
    ,XDEV_AUTOATTACHFULLPICTURE NUMBER(1)
    ,XDEV_FULLPICTURE_WDCG_ID NUMBER(30)
    ,XDEV_USEWATERMARK NUMBER(1)
    ,XDEV_WATERMARK VARCHAR2(200)
    ,XDEV_USESCANNINGCENTER NUMBER(1)
   ,CONSTRAINT XDEV_PK PRIMARY KEY (XDEV_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE XDEV IS 'Document scanning devices';

COMMENT ON COLUMN XDEV.XDEV_ID IS 'Id of scanning device';

COMMENT ON COLUMN XDEV.XDEV_TYPE IS '0 - Fujitsu; 1 - Belgian Eid reader; 2 - Samsotech';

COMMENT ON COLUMN XDEV.XDEV_ENABLEFASTSCANNING IS 'Scanning with no form interaction';

COMMENT ON COLUMN XDEV.XDEV_IDCARD_XPDG_ID IS 'Document group for IDCards';

COMMENT ON COLUMN XDEV.XDEV_PASSPORT_XPDG_ID IS 'Document group for Passports';

COMMENT ON COLUMN XDEV.XDEV_AUTOATTACHPICTURE IS 'Attach picture to profile if exists';

COMMENT ON COLUMN XDEV.XDEV_PICTURE_WDCG_ID IS 'Picture group';

COMMENT ON COLUMN XDEV.XDEV_PROMPTWHENMISSINGFIELDS IS 'Warning if missing fields';

COMMENT ON COLUMN XDEV.XDEV_QUESTIONIFSECONDSIDE IS 'Question if second side needs to be scanned';

COMMENT ON COLUMN XDEV.XDEV_AUTOATTACHFULLPICTURE IS 'Attach full picture to profile if exists';

COMMENT ON COLUMN XDEV.XDEV_FULLPICTURE_WDCG_ID IS 'Full picture group';

COMMENT ON COLUMN XDEV.XDEV_USEWATERMARK IS 'Wattermark scanned pictures';

COMMENT ON COLUMN XDEV.XDEV_WATERMARK IS 'Picture used as wattermark';

COMMENT ON COLUMN XDEV.XDEV_USESCANNINGCENTER IS 'Use this device in scanning center';

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

CREATE TABLE XDLT (
    XDLT_ID NUMBER(30) NOT NULL
    ,XDLT_WPCA_ID NUMBER(30) NOT NULL
    ,XDLT_XDEV_ID NUMBER(30) NOT NULL
   ,CONSTRAINT XDLT_PK PRIMARY KEY (XDLT_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE XDLT IS 'Devices linked to terminals';

COMMENT ON COLUMN XDLT.XDLT_ID IS 'Id';

COMMENT ON COLUMN XDLT.XDLT_WPCA_ID IS 'Terminal id';

COMMENT ON COLUMN XDLT.XDLT_XDEV_ID IS 'Device id';

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

COMMENT ON COLUMN WPCA.WPCA_PASSSCANNER IS 'Obsolete using table linked devices to terminal';

COMMENT ON COLUMN WPCA.WPCA_HASEIDREADER IS 'Obsolete using table linked devices to terminal';

ALTER TABLE XDEV ADD XDEV_UPLOADFOLDER VARCHAR2(200);

ALTER TABLE XDEV ADD XDEV_UPLOADUSER VARCHAR2(60);

ALTER TABLE XDEV ADD XDEV_UPLOADPASSWORD VARCHAR2(60);

COMMENT ON COLUMN XDEV.XDEV_UPLOADFOLDER IS 'Folder where scanned document will be uploaded for further processings/uploadings';

COMMENT ON COLUMN XDEV.XDEV_UPLOADUSER IS 'User that logs on network resource; othewise blank';

COMMENT ON COLUMN XDEV.XDEV_UPLOADPASSWORD IS 'User password';

ALTER TABLE XDEV DROP COLUMN XDEV_IDCARD_XPDG_ID;

ALTER TABLE XDEV DROP COLUMN XDEV_PASSPORT_XPDG_ID;

ALTER TABLE XDEV ADD XDEV_XPDG_ID NUMBER(30);

COMMENT ON COLUMN XDEV.XDEV_XPDG_ID IS 'Default document type if not recognized as passport or Id card';

ALTER TABLE WSFH ADD WSFH_XPDG_ID NUMBER(30);

COMMENT ON COLUMN WSFH.WSFH_XPDG_ID IS 'Document type as recognized for later assigning to profile';

ALTER TABLE XDLT ADD XDLT_DISPLORDER NUMBER(3);

COMMENT ON COLUMN XDLT.XDLT_DISPLORDER IS 'Display order';

ALTER TABLE XSCD ADD (XSCD_TRIGGERCONDITION VARCHAR2(1000));

ALTER TABLE WSFH MODIFY WSFH_FILENAME NULL;

COMMENT ON COLUMN YRES.YRES_CALCSTATUS IS '0=expected(future) 1=expected today 2=checked in today 3=in house 4=exp dep today 5=departed today 6=history 7=day use exp 8=day use in house 9=day use history 10=waitlist 11=offer 12=cancelled 13=noshow 14=voucher template 15=day use exp today 17=day use departed today';

COMMENT ON COLUMN CBCC.CBCC_ROLE IS '1 = Voucher Handling 2 = Rebate 3=Loyalty Services Voucher 4=Bonus Card 5= Rebate Cards';

UPDATE WACT SET WACT.WACT_REMINDERSTATE = 4 WHERE EXISTS (SELECT 1 FROM WATY,WATG WHERE WACT.WACT_WATY_ID=WATY.WATY_ID AND WATY.WATY_WATG_ID=WATG.WATG_ID AND WATG.WATG_ROLE=6) AND NVL(WACT.WACT_REMINDERSTATE,0)=0 AND TRUNC(WACT.WACT_ENDTIME) < SYSDATE;

ALTER TABLE W8HP MODIFY W8HP_QNR_EMAILID VARCHAR2(300);

ALTER TABLE YCRS MODIFY YCRS_MODIFIEDWITHOUTOWNED DEFAULT 0;

ALTER TABLE WSFH ADD WSFH_XDEV_ID NUMBER(30);

COMMENT ON COLUMN WSFH.WSFH_XDEV_ID IS 'Link to device that scanned the document';

UPDATE WSFH SET WSFH_ISPROCESSED = 1 WHERE NVL(WSFH_ISPROCESSED,0) <> 1;

ALTER TABLE YPRD ADD YPRD_PRIORTOARRIVAL NUMBER (1);

COMMENT ON COLUMN YPRD.YPRD_PRIORTOARRIVAL IS 'if yprd_staydaterange and this field set: only if res is prior or equal to arrival to prom startdate will be applied, if staydaterange not set: only for overlapping dates prm will be set';

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 RRST ADD RRST_AutoExtend NUMBER(1);

COMMENT ON COLUMN RRST.RRST_AutoExtend IS 'Auto extend seated reservations';

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';

ALTER TABLE WERR ADD WERR_SYSINFO CLOB;
