--UPDATE FROM 8.9.0.3 TO 8.9.0.4


DROP TABLE VERSION_8_9_0_3;



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

ALTER TABLE YRES ADD YRES_NO_EFT_AUTHORIZATION NUMBER(1);

ALTER TABLE ZWIN ADD ZWIN_NOBONUSPOINTS NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN XCMS.XCMS_INACTTIME IS ' Date and time of inactivation';

COMMENT ON COLUMN XCMS.XCMS_INACTUSER IS ' Link to Employee who inactivated customer Profile';

COMMENT ON COLUMN XCMS.XCMS_CUSTNUM IS ' Manually entered unique identifier  (must be unique)';

COMMENT ON COLUMN XCMS.XCMS_CREDITSTAT IS ' Credit status (empty=normal C=cash only B=blacklist A=City ledger authorization)';

COMMENT ON COLUMN XCMS.XCMS_CREDREASON IS ' Credit Status Change Reason';

COMMENT ON COLUMN XCMS.XCMS_CREDTIME IS ' Date/time of Credit Status change';

COMMENT ON COLUMN XCMS.XCMS_CREDUSER IS ' User Name that changed the credit status';

COMMENT ON COLUMN XCMS.XCMS_REVPOTCAT IS ' Revenue Potential Catering';

COMMENT ON COLUMN XCMS.XCMS_REVPOTRMS IS ' Revenue Potential Rooms';

COMMENT ON COLUMN XCMS.XCMS_VIEWFO IS ' Customer profile visible in FO(1) or not(0)';

COMMENT ON COLUMN XCMS.XCMS_VIEWSC IS ' Customer Profile visible in SC(1) or not(0)';

COMMENT ON COLUMN XCMS.XCMS_PREF_ZCUR_ID IS ' Preferred Currency';

COMMENT ON COLUMN XCMS.XCMS_CANDELETE IS ' Profile can be deleted (1) or not(0 or null)';

COMMENT ON COLUMN XCMS.XCMS_WLAN_ID IS ' language of guest';

COMMENT ON COLUMN XCMS.XCMS_XCOU_ID_NATION IS ' XCMS_XCOU_ID_NATION is of XCMS';

COMMENT ON COLUMN XCMS.XCMS_NOMAILING IS ' No mailing. 1 = True, anything else = false';

COMMENT ON COLUMN XCMS.XCMS_TRACEFREQ IS ' Trace frequency for ccm automated traces';

COMMENT ON COLUMN XCMS.XCMS_SAVEGUEST IS ' if 1, profile should be automatically deleted after a period of time.';

COMMENT ON COLUMN XCMS.XCMS_YCDP_ID IS ' Policy used for this guest';

COMMENT ON COLUMN XCMS.XCMS_WEBLOGIN IS ' Username for web bookings';

COMMENT ON COLUMN XCMS.XCMS_WEBPASSWORD IS ' Password for web bookings';

COMMENT ON COLUMN XCMS.XCMS_GLOBALID IS ' Global ID if Hotel Connector is installed';

COMMENT ON COLUMN XCMS.XCMS_CENTRALSYNCTIMESTAMP IS ' XCMS_CENTRALSYNCTIMESTAMP is of XCMS';

COMMENT ON COLUMN XCMS.XCMS_CENTRALSYNCTIMELOCAL IS ' XCMS_CENTRALSYNCTIMELOCAL is of XCMS';

COMMENT ON COLUMN XCMS.XCMS_LINKEDPROFILESYNCDONE IS ' 1 = indicates that linked profiles have been synchronized with the server during initalization process (Hotel Connector)';

COMMENT ON COLUMN XCMS.XCMS_AUTOMERGEERROR IS ' Auto profile match error flag';

COMMENT ON COLUMN XCMS.XCMS_ORG_WPHS_ID IS ' Original Partner Hotel ID (Hotel Connector)';

COMMENT ON COLUMN XCMS.XCMS_ZTXX_ID IS ' Tax exemption link';

COMMENT ON COLUMN XCMS.XCMS_MERGEDTO_XCMS_ID IS ' System profile merge log - ID of merged profile';

COMMENT ON COLUMN XCMS.XCMS_MERGETIME IS ' System profile merge log - date/time of profile merge';

COMMENT ON COLUMN XCMS.XCMS_AUTOMERGEERRORID IS ' 0=error 1=merged to other profile remotely 2=profile was not uploaded because it has a match point higher than configured max match point 3=unique xmnr conflict 4=manual merge needed';

COMMENT ON COLUMN XCMS.XCMS_EXTERNALID IS ' External ID in case profile has been inserted from external program';

COMMENT ON COLUMN XCMS.XCMS_NO_HC_TRANSFER IS ' 0=profile can be transferred  1=profile cannot be transferred'; 

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

COMMENT ON COLUMN XCMS.XCMS_HC_TOUCHED IS ' Timestamp so that hotel connector knows an activity or something got unlinked from a certain profile';

COMMENT ON COLUMN XCMS.XCMS_NOEMAILING IS ' No E-Mail Mailing | 1= True';

ALTER TABLE QNRF ADD QNRF_FILLOUTDATE DATE;

COMMENT ON COLUMN QNRF.QNRF_FILLOUTDATE IS 'Questionaire Fillout date';

ALTER TABLE ZWIN ADD ZWIN_NOBONUSPOINTS NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE WQNR ADD WQNR_COLUMNCOUNT NUMBER(3);

COMMENT ON COLUMN WQNR.WQNR_COLUMNCOUNT IS 'Default Answer options Column Count';

ALTER TABLE WQNL ADD WQNL_COLUMNCOUNT NUMBER(3);

COMMENT ON COLUMN WQNL.WQNL_COLUMNCOUNT IS 'Answer options Column Count';

ALTER TABLE SMAN ADD SMAN_ROOMS_OCC_E1P_D NUMBER(5);

ALTER TABLE SMAN ADD SMAN_ROOMS_OCC_E1P_M NUMBER(5);

ALTER TABLE SMAN ADD SMAN_ROOMS_OCC_E1P_Y NUMBER(7);

ALTER TABLE SMAN ADD SMAN_ROOMS_OCC_M1P_D NUMBER(5);

ALTER TABLE SMAN ADD SMAN_ROOMS_OCC_M1P_M NUMBER(5);

ALTER TABLE SMAN ADD SMAN_ROOMS_OCC_M1P_Y NUMBER(7);

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

ALTER TABLE W8HP ADD W8HP_BOOKWITHOUTREGISTRATION NUMBER(1);

ALTER TABLE YHRI ADD YHRI_SOLD NUMBER (10);

ALTER TABLE XCMS ADD XCMS_BATCHID NUMBER(30);

COMMENT ON COLUMN XCMS.XCMS_BATCHID IS 'used for grouping profiles for batch processing. ie for duplicate search';

CREATE TABLE WCSP (
   WCSP_ID NUMBER(30) NOT NULL
  ,WCSP_ROLE NUMBER(2) NOT NULL
  ,WCSP_SENDING NUMBER(1) NOT NULL
  ,WCSP_LETTER_XADR_ID NUMBER(30)
  ,WCSP_EMAIL_XCOM_ID NUMBER(30)
  ,WCSP_XMNR_ID NUMBER(30)
  ,CONSTRAINT WCSP_PK PRIMARY KEY (WCSP_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

CREATE SEQUENCE SEQ_WCSP INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;
  
COMMENT ON TABLE WCSP IS 'Correspondence Sending Preferences for Bonus Points etc.'; 

COMMENT ON COLUMN WCSP.WCSP_ROLE IS '1 - Bonus Points';

COMMENT ON COLUMN WCSP.WCSP_SENDING IS '0 = dont send, 1 = Letter, 2 = EMAIL';

ALTER TABLE ACMP ADD ACMP_NUMBER VARCHAR2(60);

ALTER TABLE WQUE ADD WQUE_QUESTIONKIND NUMBER(3);

COMMENT ON COLUMN WQUE.WQUE_QUESTIONKIND IS 'Type Question. 0=Undefined, 1=YesNo, 2=Multiple, 3=Rating';

UPDATE YRES SET YRES.YRES_ORGDEPTIME=(YRES.YRES_ORGDEPTIME)+1 WHERE YRES.YRES_ID IN (SELECT YRES2.YRES_ID FROM YRES YRES2, WLOG WHERE WLOG.WLOG_TYPE=4 AND WLOG.WLOG_TABLE_ID=YRES2.YRES_ID AND WLOG.WLOG_TABLENAME LIKE 'YRES' AND EXISTS 
(SELECT 1 FROM WLOG WLOG2 WHERE WLOG2.WLOG_TYPE=2 AND WLOG2.WLOG_TABLE_ID=YRES2.YRES_ID AND WLOG2.WLOG_TABLENAME LIKE 'YRES' AND TRUNC(WLOG2.WLOG_TIME,'MI')=TRUNC(WLOG.WLOG_TIME,'MI'))) AND TRUNC(YRES.YRES_ACTDEPTIME) = TRUNC(YRES.YRES_ORGDEPTIME);

CREATE TABLE YHFS (
    YHFS_ID NUMBER(30) NOT NULL
   ,YHFS_NUMBEROFATTENDANTS NUMBER(3) 
   ,YHFS_DEFAULTCREDITS NUMBER(3) 
   ,YHFS_ALLOWAUTOEXTEND NUMBER(1)
   ,YHFS_AUTOREDUCE NUMBER(1)
   ,YHFS_AUTOMAIDNAME VARCHAR2(50 CHAR)
   ,YHFS_USEDEFINED NUMBER(1)
   ,YHFS_USEATTENDANTCONFIG NUMBER(1)
   ,YHFS_ATTENDANTTYPE NUMBER(30)
   ,YHFS_METHOD NUMBER(1) DEFAULT 0 NOT NULL
   ,YHFS_INCLUDEPUBLICPLACES NUMBER(1)
   ,YHFS_INCLUDEFUNCTIONSPACES NUMBER(1)
   ,YHFS_INCLUDECLEANROOMS NUMBER(1)
   ,YHFS_INCLUDEONLYFORARRIVALS NUMBER(1)
   ,YHFS_INCLUDEOOOROOMS NUMBER(1)
   ,YHFS_LISTCLEANASNOTASSIGNED NUMBER(1)
   ,CONSTRAINT YHFS_PK PRIMARY KEY (YHFS_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE YHFS IS 'Attendant calculation settings for saved attendant plans';

COMMENT ON COLUMN YHFS.YHFS_METHOD IS '0=Fill to maxcredits 1=Balance equally 2=Attendant per section';

ALTER TABLE YHRL ADD YHRL_YHFS_ID NUMBER(30);

COMMENT ON COLUMN YHRL.YHRL_YHFS_ID IS 'Link to the filter settings';

ALTER TABLE WPCA ADD WPCA_REFRESHLEVEL NUMBER(3);

COMMENT ON COLUMN WPCA.WPCA_REFRESHLEVEL IS 'Controls depth at which SetWindowPos it is ignored by windows systems due to kernel stack size; default = 7 after testings';

ALTER TABLE MWSV ADD MWSV_LASTREQUEST DATE;

ALTER TABLE MURL ADD MURL_LASTREQUEST DATE;

COMMENT ON COLUMN MWSV.MWSV_LASTREQUEST IS 'timestamp of last successful booking request';

COMMENT ON COLUMN MURL.MURL_LASTREQUEST IS 'timestamp of last successful booking request';

ALTER TABLE YEBS ADD YEBS_PRICE_NET NUMBER(30,4);

COMMENT ON COLUMN YEBS.YEBS_PRICE_NET IS 'Function Space net value';

ALTER TABLE YEBS ADD YEBS_PRICE_VAT NUMBER(30,4);

COMMENT ON COLUMN YEBS.YEBS_PRICE_VAT IS 'Function Space tax value';

ALTER TABLE YEBR ADD YEBR_PRICE_NET NUMBER(30,4);

COMMENT ON COLUMN YEBR.YEBR_PRICE_NET IS 'Catering Item net value';

ALTER TABLE YEBR ADD YEBR_PRICE_VAT NUMBER(30,4);

COMMENT ON COLUMN YEBR.YEBR_PRICE_VAT IS 'Catering Item tax value';

ALTER TABLE YMRD ADD YMRD_PRICE_NET NUMBER(30,4);

COMMENT ON COLUMN YMRD.YMRD_PRICE_NET IS 'Standard menu split net value';

ALTER TABLE YMRD ADD YMRD_PRICE_VAT NUMBER(30,4);

COMMENT ON COLUMN YMRD.YMRD_PRICE_VAT IS 'Standard menu split tax value';

ALTER TABLE YEMI ADD YEMI_PRICE_NET NUMBER(30,4);

COMMENT ON COLUMN YEMI.YEMI_PRICE_NET IS 'Menu item net value';

ALTER TABLE YEMI ADD YEMI_PRICE_VAT NUMBER(30,4);

COMMENT ON COLUMN YEMI.YEMI_PRICE_VAT IS 'Menu item tax value';

ALTER TABLE YBEF ADD YBEF_PRICE_NET NUMBER(30,4);

COMMENT ON COLUMN YBEF.YBEF_PRICE_NET IS 'Revenue net value';

ALTER TABLE YBEF ADD YBEF_PRICE_VAT NUMBER(30,4);

COMMENT ON COLUMN YBEF.YBEF_PRICE_VAT IS 'Revenue tax value';

UPDATE XCAC SET XCAC_XGCA_ID = 0 WHERE XCAC_XGCA_ID IS NULL;

ALTER TABLE XCAC MODIFY (XCAC_XGCA_ID NOT NULL);

ALTER TABLE XCAC MODIFY (XCAC_XGCA_ID DEFAULT 0);

UPDATE YRCH SET YRCH_XGCA_ID = 0 WHERE YRCH_XGCA_ID IS NULL;

ALTER TABLE YRCH MODIFY (YRCH_XGCA_ID NOT NULL);

ALTER TABLE YRCH MODIFY (YRCH_XGCA_ID DEFAULT 0);
