/************************************************************************************************************************
*
*
*  Date: March 03, 2016  09:29:50
*
*  This upgrade script was automatically generated by DBDiff for Oracle (www.dbdiff.com)
*
*  SOURCE DATABASE:  VERSION_8_10_0@V8TEST2
*  TARGET DATABASE:  VERSION_8_9_6@V8TEST2
*
*  This script should be executed as VERSION_8_9_6 in database VERSION_8_9_6@V8TEST2 except where otherwise noted.
*
*  IMPORTANT:  As with any script, you should always perform a sanity check before executing it in your target database.
*
*
************************************************************************************************************************/


/************************************************************************************************************************
*
*  tables
*
*  1070 tables were found in VERSION_8_10_0@V8TEST2
*  1066 tables were found in VERSION_8_9_6@V8TEST2
*
************************************************************************************************************************/




/************************************************************************************************************************
*
*  The following statements will drop tables that DO NOT exist in VERSION_8_10_0@V8TEST2
*
************************************************************************************************************************/


DROP TABLE VERSION_8_9_6_10;


/************************************************************************************************************************
*
*  The following statements will create tables that DO NOT exist in VERSION_8_9_6@V8TEST2
*
************************************************************************************************************************/


/*  VERSION_8_10_0_0  */
CREATE TABLE VERSION_8_10_0_0 (
	 DUMMY	NUMBER	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50
	FREELISTS  1 FREELIST GROUPS 1
        )
;


/*  WPRT  */
CREATE TABLE WPRT (
	 WPRT_ID	NUMBER(30)	NOT NULL
	,WPRT_NAME	VARCHAR2(60)	    NULL
	,WPRT_VALUE	VARCHAR2(1000)	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WPRT ADD (
	CONSTRAINT WPRT_PK
	PRIMARY KEY
	            (
	             WPRT_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

ALTER TABLE WPRT ADD (
	CONSTRAINT SYS_C00260672
	UNIQUE
	       (
	        WPRT_NAME
	       )
	USING INDEX
	TABLESPACE V8MAIN
)
;

/*  YBBW  */
CREATE TABLE YBBW (
	 YBBW_ID	NUMBER(30)	NOT NULL
	,YBBW_YBLH_ID	NUMBER(30)	NOT NULL
	,YBBW_YRCH_ID	NUMBER(30)	    NULL
	,YBBW_BLOCK_TYPE	VARCHAR2(20)	    NULL
	,YBBW_BLOCK_LEAD_ID	VARCHAR2(50)	    NULL
	,YBBW_BOOKING_CHANNEL	NUMBER(1)	    NULL
	,YBBW_PROMISED_NON	NUMBER(4)	    NULL
	,YBBW_PROMISED_LOW_RATE	NUMBER(30,4)	    NULL
	,YBBW_PROMISED_HIGH_RATE	NUMBER(30,4)	    NULL
	,YBBW_ONLINE_URL	VARCHAR2(200)	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE YBBW ADD (
	CONSTRAINT YBBW_PK
	PRIMARY KEY
	            (
	             YBBW_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YBCR  */
CREATE TABLE YBCR (
	 YBCR_ID	NUMBER(30)	NOT NULL
	,YBCR_YBLH_ID	NUMBER(30)	NOT NULL
	,YBCR_CRSSYSTEM	VARCHAR2(30)	NOT NULL
	,YBCR_CRSBLOCKID	VARCHAR2(50)	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE YBCR ADD (
	CONSTRAINT YBCR_PK
	PRIMARY KEY
	            (
	             YBCR_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YRRR  */
CREATE TABLE YRRR (
	 YRRR_ID	NUMBER(30)	NOT NULL
	,YRRR_RESA_YRES_ID	NUMBER(30)	NOT NULL
	,YRRR_RESB_YRES_ID	NUMBER(30)	NOT NULL
	,YRRR_RELATION	NUMBER(1)	NOT NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50
	FREELISTS  1 FREELIST GROUPS 1
        )
;

ALTER TABLE YRRR ADD (
	CONSTRAINT YRRR_PK
	PRIMARY KEY
	            (
	             YRRR_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;




/************************************************************************************************************************
*
*  The following statements will upgrade the COMMON tables
*
************************************************************************************************************************/

CREATE TABLE ZTXL(
    ZTXL_ID NUMBER(30) NOT NULL
   ,ZTXL_ztxx_id NUMBER(30) NOT NULL
   ,ZTXL_ztxc_id NUMBER(30) NOT NULL
   ,CONSTRAINT ZTXL_PK PRIMARY KEY (ZTXL_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE ZTXL IS 'Link between tax exemptions and exempted tax types for cases where several taxes are linked to a department code and not all should be comitted';

ALTER TABLE MMOD ADD ( MMOD_USE_NEW_BLOCK_HANDLING NUMBER(1) NULL  );

ALTER TABLE MMOD ADD ( MMOD_ZDCO_ID NUMBER(30) NULL  );

ALTER TABLE MMOD ADD ( MMOD_BLOCK_INC_CANC NUMBER(1) NULL  );

ALTER TABLE MMOD ADD ( MMOD_RATE_IGN_PCK NUMBER(1) NULL  );

ALTER TABLE WFTP MODIFY ( WFTP_PASSWORD VARCHAR2(60));

ALTER TABLE WPHS MODIFY ( WPHS_MULTIV8PW VARCHAR2(60));

ALTER TABLE WPHS MODIFY ( WPHS_DBPW VARCHAR2(60));

ALTER TABLE WSPS MODIFY ( WSPS_PASSWORD VARCHAR2(60));

ALTER TABLE XCED MODIFY ( XCED_LEISUREPASSWORD VARCHAR2(60));

ALTER TABLE XJET MODIFY ( XJET_PASSWORD VARCHAR2(60));

ALTER TABLE XMTY ADD ( XMTY_WRXP_ID NUMBER(30) NULL  );

ALTER TABLE YCAD ADD ( YCAD_WPHS_ID NUMBER(30) NULL  );

ALTER TABLE YCAT ADD ( YCAT_TYPE NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE YCEV ADD ( YCEV_MIN_ADVANCE_BOOKING_DAYS NUMBER(3) NULL  );

ALTER TABLE YDOR MODIFY ( YDOR_LONGDESC VARCHAR2(30));

ALTER TABLE YDOR ADD ( YDOR_SHORTDESC VARCHAR2(30) NULL  );

ALTER TABLE YDOR ADD ( YDOR_RELATION NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE YDOR ADD ( YDOR_STATUS NUMBER(1) NULL  );

ALTER TABLE YGRP ADD ( YGRP_STATUS NUMBER(1) DEFAULT 0 NULL  );

ALTER TABLE YHAT ADD ( YHAT_MAXDEPCREDITS NUMBER(4) NULL  );

ALTER TABLE YRCH ADD ( YRCH_BWBLOCKRATE NUMBER(1) NULL  );

ALTER TABLE YRES ADD ( YRES_STATUS NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE YRKP MODIFY ( YRKP_PIN VARCHAR2(50));

ALTER TABLE YRMS ADD ( YRMS_BED_YCAT_ID NUMBER(30) NULL  );

ALTER TABLE YRMS ADD ( YRMS_HSKFLAG NUMBER(1) NULL  );

ALTER TABLE YRSC ADD ( YRSC_ROLE NUMBER(1) NULL  );

ALTER TABLE YRSC ADD ( YRSC_DEFAULT_YRSR_ID NUMBER(10) NULL  );

ALTER TABLE YRST ADD ( YRST_YRES_ID NUMBER(30) NULL  );

ALTER TABLE ZDCO ADD (ZDCO_POSTTAXONLY number(1));

ALTER TABLE ZDCO ADD (ZDCO_REBATE_ZDCO_ID number(30));

ALTER TABLE ZINV ADD (ZINV_NUMBER2 varchar2(60 char), ZINV_EMAIL varchar2(100 char));

ALTER TABLE ZFAG ADD (ZFAG_NOEARCHIVE number(1));

ALTER TABLE SFOR MODIFY SFOR_GUESTNAME VARCHAR2(60 CHAR);

ALTER TABLE WATY ADD WATY_ROOMCLEANING number(1);

DELETE FROM XREM WHERE XREM_LEVEL = 'GOLD ELITE';

UPDATE XCED SET XCED_MAXSESSIONS=0 WHERE XCED_MAXSESSIONS IS NULL;

COMMENT ON COLUMN YCAT.YCAT_TYPE IS '0=Room Only Room 1=Bed Only Room 2=Mixed: Room and Bed Room 3=Bed (not room)';

COMMENT ON COLUMN YRMS.YRMS_BED_YCAT_ID IS 'Bed type: Links to a ycat_type=3 Bed type if hotel does not identify beds individually in ybed. Otherwise not used.';

COMMENT ON COLUMN YCAD.YCAD_WPHS_ID IS 'Non Operated Rooms Management: link hotel segment to partner hotel'; 

COMMENT ON COLUMN YGRP.YGRP_STATUS IS 'to indicate group is closed down for any future modification, 0 = open , 1 = closed';

COMMENT ON COLUMN YRCH.YRCH_BWBLOCKRATE IS 'flag to indicate internal rate used only by Best western group functionality. These rates are not visible in the pms system for normal rate operations';

COMMENT ON COLUMN YRMS.YRMS_HSKFLAG IS '0 - Default | 1 - Room is dirty+vacant after previous departure (c/o in the past, not today) |  2 - Room is dirty+vacant after OOO/OOS status change';

COMMENT ON COLUMN ZINV.ZINV_NUMBER2 IS 'Storage for a secondary invoice number, introduced for the Turkish E-Archive ETTN number';

COMMENT ON COLUMN ZINV.ZINV_EMAIL IS 'The email address for electronically delivered invoices';

COMMENT ON COLUMN ZINV.ZINV_FISCALINVOICE IS '1 = Turkey e-invoice, 4 = Turkey E-Archive not processed, 5 = Turkey E-Archive processed';

COMMENT ON COLUMN ZDCO.ZDCO_REBATE_ZDCO_ID IS 'Turkish rebate ZDCO_ID for voiding E-Archive';

COMMENT ON COLUMN ZFAG.ZFAG_NOEARCHIVE IS 'Don''t allow Turkish E-Archive for this account type';

COMMENT ON COLUMN MMOD.MMOD_BLOCK_INC_CANC IS 'indicates whether also blocks not affecting availability will be uploaded';

COMMENT ON COLUMN MMOD.MMOD_RATE_IGN_PCK IS 'indicates whether packages are add to rate upload xml';

COMMENT ON COLUMN ZDCO.ZDCO_POSTTAXONLY IS '1 = Indicates that this department code represents a technical department code, where all of the entered amount goes to the linked tax code';

COMMENT ON COLUMN XCLR.XCLR_LEVEL IS '0 - parents/childs; 1 - grandparents/grandchilds';

COMMENT ON COLUMN XCLR.XCLR_TYPE IS '1-Spouse, 2-next geneneration (descendant), 3-prev gen (predecessor), 4-siblings, 5-partners, 6-Relative, 7-Distant Relative | for parents should be 3, for children 2';

COMMENT ON COLUMN MMOD.MMOD_USE_NEW_BLOCK_HANDLING IS 'indicates for Best Western whether the new group handling is in place';

COMMENT ON TABLE YBCR IS 'Third party CRS id for block';

COMMENT ON TABLE YBBW IS 'Best Western specifc block data';

CREATE TABLE WBWC (
    WBWC_ID NUMBER(30) NOT NULL
   ,WBWC_LONGDESC VARCHAR2(30) NOT NULL
   ,WBWC_NAME_SEP VARCHAR2(1)
   ,WBWC_ENDOFTRACK_SEP varchar2(1)
   ,WBWC_TRACK2START_SEP varchar2(1)
   ,WBWC_SWIPESPEED number(4)
   ,CONSTRAINT WBWC_PK PRIMARY KEY (WBWC_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8cONF;

COMMENT ON TABLE WBWC IS 'Swiper config for Best Western Membership Cards';

COMMENT ON COLUMN WBWC.WBWC_ID IS 'Id of bw swiper';

COMMENT ON COLUMN WBWC.WBWC_LONGDESC IS 'Name of bw swiper';

COMMENT ON COLUMN WBWC.WBWC_NAME_SEP IS 'Name separator';

COMMENT ON COLUMN WBWC.WBWC_ENDOFTRACK_SEP IS 'End of track separator';

COMMENT ON COLUMN WBWC.WBWC_TRACK2START_SEP IS 'Track 2 start separator';

COMMENT ON COLUMN WBWC.WBWC_SWIPESPEED IS 'Swipe speed';

ALTER TABLE WPCA ADD WPCA_WBWC_ID NUMBER(30);

COMMENT ON TABLE WPRT IS 'Holds the protected data';

ALTER TABLE YFAL ADD YFAL_FROM_ZFAC_ID NUMBER(30);

COMMENT ON COLUMN YFAL.YFAL_FROM_ZFAC_ID IS 'to be able to link zfac to zfac. This will be the originator accounts id';

ALTER TABLE XPRG MODIFY XPRG_NEXTLOYALLEVEL VARCHAR2(15);


/************************************************************************************************************************
*
*  The following statements will upgrade the indexes for the COMMON tables
*
************************************************************************************************************************/


/*  RRER  */

CREATE UNIQUE INDEX RRER_YRES_RRES ON RRER
	(
	  RRER_RRES_ID
	 ,RRER_YRES_ID
	)
	PCTFREE    10
	INITRANS   2
	MAXTRANS   255
	TABLESPACE V8INDEX
;



/*  XCMG  */

CREATE UNIQUE INDEX XCMG_XCMS_GLOBALID ON XCMG
	(
	  XCMG_CRSTYPE
	 ,XCMG_GLOBALID
	)
	PCTFREE    10
	INITRANS   2
	MAXTRANS   255
	TABLESPACE V8INDEX
;


/************************************************************************************************************************
*
*  sequences
*
*  The following statements will upgrade the sequences
*
************************************************************************************************************************/


CREATE SEQUENCE SEQ_WPRT INCREMENT BY 1 START WITH 1001 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_YBBW INCREMENT BY 1 START WITH 1001 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_YBCR INCREMENT BY 1 START WITH 1001 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_YRRR INCREMENT BY 1 START WITH 1001 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WBWC INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;






--- End of DBDiff for Oracle --- Elapsed Time: 00:26:59 ---

