/************************************************************************************************************************
*
*
*  Date: November 07, 2017  18:04:22
*
*  This upgrade script was automatically generated by DBDiff for Oracle (www.dbdiff.com)
*
*  SOURCE DATABASE:  VERSION_8_11_0@V8
*  TARGET DATABASE:  VERSION_8_10_2@V8
*
*  This script should be executed as VERSION_8_10_2 in database VERSION_8_10_2@V8 except where otherwise noted.
*
*  IMPORTANT:  As with any script, you should always perform a sanity check before executing it in your target database.
*
*
************************************************************************************************************************/


/************************************************************************************************************************
*
*  The following statements will drop tables that DO NOT exist in VERSION_8_11_0@V8
*
************************************************************************************************************************/


DROP TABLE VERSION_8_10_2_40;


/************************************************************************************************************************
*
*  The following statements will create tables that DO NOT exist in VERSION_8_10_2@V8
*
************************************************************************************************************************/


/*  WLOS  */
CREATE TABLE WLOS (
	 WLOS_ID	NUMBER(30)	NOT NULL
	,WLOS_TIME	DATE	NOT NULL
	,WLOS_XCED_ID	NUMBER(30)	NOT NULL
	,WLOS_TYPE	NUMBER(3)	NOT NULL
	,WLOS_TABLE_ID	NUMBER(30)	NOT NULL
	,WLOS_TEXT	VARCHAR2(400)	NOT NULL
	,WLOS_WPCA_ID	NUMBER(30)	NOT NULL
	,WLOS_SYSDAT	DATE	NOT NULL
	,WLOS_TABLENAME	CHAR(4)	NOT NULL
	,WLOS_TERMINAL	VARCHAR2(60)	    NULL
	,WLOS_WPHS_ID	NUMBER(30)	    NULL
)
TABLESPACE V8MAIN;

ALTER TABLE WLOS ADD (
	CONSTRAINT WLOS_PK
	PRIMARY KEY
	            (
	             WLOS_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX);


/*  WDBO  */
CREATE TABLE WDBO (
	 WDBO_ID	NUMBER(30)	NOT NULL
	,WDBO_NAME	VARCHAR2(100)	NOT NULL
	,WDBO_TYPE	VARCHAR2(20)	NOT NULL
	,WDBO_LAST_DDL_TIME	DATE	NOT NULL
	,WDBO_TIMESTAMP	VARCHAR2(30)	    NULL
	,WDBO_STATUS	VARCHAR2(10)	NOT NULL
	,WDBO_HASH	VARCHAR2(100)	NOT NULL
	,WDBO_SIGNED_EMPLOYEE_XCMS_ID	NUMBER(30)	NOT NULL
	,WDBO_SIGNED_TIMESTAMP	DATE	NOT NULL
	,WDBO_TEXT	CLOB	    NULL
	,WDBO_TEXTHASH	VARCHAR2(100)	    NULL
)
TABLESPACE V8LARGE;

ALTER TABLE WDBO ADD (
	CONSTRAINT WDBO_PK
	PRIMARY KEY
	            (
	             WDBO_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX);


/*  WCCL  */
CREATE TABLE WCCL (
	 WCCL_ID	NUMBER(30)	NOT NULL
	,WCCL_XCED_ID	NUMBER(30)	    NULL
	,WCCL_PURPOSE	NUMBER(1)	NOT NULL
	,WCCL_NAME	VARCHAR2(60)	    NULL
	,WCCL_DATA	CLOB	    NULL
)
TABLESPACE V8MAIN;

ALTER TABLE WCCL ADD (
	CONSTRAINT WCCL_PK
	PRIMARY KEY
	            (
	             WCCL_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX);


/*  WAIL  */
CREATE TABLE WAIL (
	 WAIL_ID	NUMBER(30)	NOT NULL
	,WAIL_NAME	VARCHAR2(30)	NOT NULL
)
TABLESPACE V8MAIN;

ALTER TABLE WAIL ADD (
	CONSTRAINT WAIL_PK
	PRIMARY KEY
	            (
	             WAIL_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX);

ALTER TABLE WAIL ADD (
	CONSTRAINT WAIL_NAME_UNIQUE
	UNIQUE
	       (
	        WAIL_NAME
	       )
	USING INDEX
		TABLESPACE V8INDEX);


/*  VERSION_8_11_0_0  */
CREATE TABLE VERSION_8_11_0_0 (
	 DUMMY	NUMBER	    NULL
)
TABLESPACE V8MAIN;


create table ywrf (
    ywrf_id number(30) not null
    ,ywrf_ypke_id number(30) not null
    ,ywrf_yfsc_id number(30) not null
    ,ywrf_displorder number(3) not null
    ,constraint ywrf_pk primary key (ywrf_id) using index tablespace v8index
);




create table ywrs (
    ywrs_id number(30) not null
    ,ywrs_ywrf_id number(30) not null
    ,ywrs_yssl_id number(30) not null
    ,ywrs_displorder number(3) not null
    ,constraint ywrs_pk primary key (ywrs_id) using index tablespace v8index
);




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



ALTER TABLE ZAML ADD ( ZAML_YBED_ID NUMBER(30) NULL  );

ALTER TABLE YHRL ADD ( YHRL_YBED_ID NUMBER(30) NULL  );

ALTER TABLE YCIM ADD ( YCIM_DISPLORDER NUMBER(3) NULL  );

ALTER TABLE YCAT ADD ( YCAT_WCOL_ID NUMBER(30) NULL  );

ALTER TABLE YBED ADD ( YBED_YHKS_TIMESTAMP DATE NULL  );

ALTER TABLE YBED ADD ( YBED_DEFAULTCREDITS NUMBER(3) NULL  );

ALTER TABLE YBED ADD ( YBED_DEPARTURECREDITS NUMBER(3) NULL  );

ALTER TABLE YBED ADD ( YBED_CLEANCREDITS NUMBER(3) NULL  );

ALTER TABLE YBED ADD ( YBED_GUESTSERVICE_STATUS NUMBER(1) NULL  );

ALTER TABLE YBED ADD ( YBED_HSKFLAG NUMBER(1) NULL  );

ALTER TABLE XCMS ADD ( XCMS_STOREPRIVATEDATA NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE XCMS ADD ( XCMS_DELETEPRIVATEDATA DATE NULL  );

ALTER TABLE XCMS ADD ( XCMS_DELETEPRIVATEDATABATCH NUMBER NULL  );

ALTER TABLE XCED ADD ( XCED_WUSRHASH VARCHAR2(60) NULL  );

ALTER TABLE WEGR ADD ( WEGR_WUSRHASH VARCHAR2(60) NULL  );

ALTER TABLE WEDA ADD ( WEDA_DISPLORDER NUMBER(3) NULL  );

ALTER TABLE W8HP ADD ( W8HP_CCM_CANCEL_YEST_ID NUMBER(30) NULL  );

ALTER TABLE W8HP ADD ( W8HP_REG_SHOWBOOKINGS NUMBER(1) NULL  );

ALTER TABLE MMOD ADD ( MMOD_DEF_CANCEL_TIME DATE NULL  );

ALTER TABLE XCED MODIFY XCED_PASSWORD VARCHAR2(2000);  

ALTER TABLE XPWH MODIFY XPWH_PASSWORD VARCHAR2(2000);

ALTER TABLE YPRL ADD YPRL_SOURCE NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE YPKG ADD YPKG_LENGTH NUMBER(3);

ALTER TABLE YPKS ADD ypks_default NUMBER(1);

ALTER TABLE W8HP ADD W8HP_CCM_YEST_ID NUMBER(30);

ALTER TABLE W8HP ADD W8HP_CCM_OVERRIDEWO NUMBER(1);

ALTER TABLE SMRT ADD SMRT_NOSHOWPERSON_D NUMBER(5);

ALTER TABLE SMRT ADD SMRT_NOSHOWPERSON_M NUMBER(5);

ALTER TABLE SMRT ADD SMRT_NOSHOWPERSON_Y NUMBER(5);

COMMENT ON TABLE WCCL IS 'Custom color config';

COMMENT ON TABLE WLOS IS 'Security log';

COMMENT ON TABLE WAIL IS 'GPDR anonymization ignore list: field names and table names';

COMMENT ON TABLE YWRS IS 'Link package event/function space seatings for web purpose';

COMMENT ON TABLE YWRF IS 'Link package event/function spaces for web purpose';

COMMENT ON COLUMN WCCL.WCCL_PURPOSE IS '0=User interface colors 1=roomtype colors';

COMMENT ON COLUMN YPRL.YPRL_SOURCE IS '0=Manual 1=Rate 2=Membership 3=Upsell';

COMMENT ON COLUMN WCCL.WCCL_PRUPOSE IS '0=User interface colors 1=roomtype colors';

COMMENT ON COLUMN MMOD.MMOD_DEF_CANCEL_TIME IS 'Default cancellation time for reservation, in case linked guarantee code of the policy code linked to the rate code of the reservation has no release time';

COMMENT ON COLUMN XCMS.XCMS_STOREPRIVATEDATA IS '0=Normal (anonymize after configured period), 1=Permanent storage, 2= Anonymize as soon as possible';

COMMENT ON COLUMN YBED.YBED_GUESTSERVICE_STATUS IS '1=Make up Rooom 2=Do not Disturb 3=Reset';

COMMENT ON COLUMN W8HP.W8HP_CCM_CANCEL_YEST_ID IS 'Booking cancel status used.';

COMMENT ON COLUMN W8HP.W8HP_REG_SHOWBOOKINGS IS 'Show bookings in registration page';

COMMENT ON COLUMN W8HP.W8HP_CCM_OVERRIDEWO IS 'Override web only flag for package events/resources if package is web only';

COMMENT ON COLUMN W8HP.W8HP_CCM_YEST_ID IS 'Booking Status';

COMMENT ON COLUMN YPKS.YPKS_DEFAULT IS 'Default';

COMMENT ON COLUMN YPKG.YPKG_LENGTH IS 'Package length';

COMMENT ON COLUMN XCMS.XCMS_ACTIVE is '1 = Yes | 0 = No | 3 = Terminated (emplyoees) | 4 = Merged | 5 = Anonymized';

UPDATE WEDA SET WEDA_DISPLORDER=(SELECT MAX(NVL(WEDA_DISPLORDER,0)) FROM WEDA)+ROWNUM WHERE WEDA_DISPLORDER IS NULL;

DELETE FROM ZITS WHERE ZITS.ZITS_TABLENAME='zfac';

UPDATE YPRL SET YPRL_SOURCE=1 WHERE YPRL_FROMRATE=1;


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

CREATE SEQUENCE SEQ_WCCL INCREMENT BY 1 START WITH 1001 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WDBO INCREMENT BY 1 START WITH 1001 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WLOS INCREMENT BY 1 START WITH 1001 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_YWRF INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_YWRS INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WAIL INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;







