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


/************************************************************************************************************************
*
*  tables
*
*  1117 tables were found in VERSION_8_12_0@V8
*  1114 tables were found in VERSION_8_11_1@V8
*
************************************************************************************************************************/


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


DROP TABLE VERSION_8_11_3_0;


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


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


create table WRCS (
WRCS_ID number(30) not null
,WRCS_LONGDESC varchar2(200) not null
,WRCS_CODE varchar2(30)
,WRCS_INACTIVE number(1) default 0
,WRCS_TYPE number(1)
,constraint WRCS_PK primary key (WRCS_ID) using index tablespace v8index
) tablespace v8conf;


CREATE TABLE WRCD
(
WRCD_ID NUMBER(30) NOT NULL,
WRCD_WRCS_ID NUMBER(30) NOT NULL,
WRCD_OOO NUMBER(1) default 0,
WRCD_PAYMENT NUMBER(1) default 0,
WRCD_POSTCHARGES NUMBER(1) default 0,
WRCD_CHECKINFORPAIDONLY NUMBER(1) default 0,
WRCD_CHECKINWITHPAYONLY NUMBER(1) default 0,
WRCD_USE_LOGIN NUMBER(1) default 0,
WRCD_USE_TOKEN NUMBER(1) default 0,
WRCD_USE_ID_LN NUMBER(1) default 0,
WRCD_USE_EXRES_LN NUMBER(1) default 0,
WRCD_USE_EXRES_OR_ID_LN NUMBER(1) default 0,
WRCD_TIME DATE NULL,
WRCD_USE_UPSELL NUMBER(1) default 0,
WRCD_REG_GEN NUMBER(1) default 0,
WRCD_REG_WRPT_ID NUMBER(1) NULL,
WRCD_REG_SCAN NUMBER(1) default 0,
WRCD_FOL_GEN NUMBER(1) default 0,
WRCD_FOL_WRPT_ID NUMBER(1) NULL,
WRCD_FOL_SCAN NUMBER(1) default 0,
constraint WRCD_PK primary key (WRCD_ID) using index tablespace v8index
) tablespace v8main ;


CREATE TABLE WRCC
(
WRCC_ID NUMBER(30) NOT NULL,
WRCC_WRCS_ID NUMBER(30) NOT NULL,
WRCC_DATE DATE NULL,
WRCC_OOO NUMBER default 0,
WRCC_ONLYSELECTEDROOMS NUMBER(1) default 0,
constraint WRCC_PK primary key (WRCC_ID) using index tablespace v8index
) tablespace v8main ;


CREATE TABLE WRCR
(
WRCR_ID NUMBER(30) NOT NULL,
WRCR_WRCC_ID NUMBER(30) NOT NULL,
WRCR_YRMS_ID NUMBER(30) NULL,
WRCR_DISPLORDER NUMBER(4) NULL,
constraint WRCR_PK primary key (WRCR_ID) using index tablespace v8index
) tablespace v8main ;


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


alter table yres add yres_preCheckIn number(1) default 0 not null;

alter table yres add yres_PaymentAtPreCheckIn number(1) default 0 not null;

alter table smrt add smrt_room_blocked_by_bed_d number(5);

alter table smrt add smrt_room_blocked_by_bed_m number(5);

alter table smrt add smrt_room_blocked_by_bed_y number(5);

alter table smrt add smrt_bed_blocked_by_room_d number(5);

alter table smrt add smrt_bed_blocked_by_room_m number(5);

alter table smrt add smrt_bed_blocked_by_room_y number(5);

ALTER TABLE W8HP ADD W8HP_REMOTE_CI NUMBER(1) DEFAULT 0 NULL;

ALTER TABLE W8HP ADD W8HP_WRCS_ID NUMBER(30) NULL;

ALTER TABLE ZDCF ADD PRIMARY KEY (ZDCF_ID);

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


create sequence seq_cctokenserver_request increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;

create sequence seq_wrcs increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;

create sequence seq_wrcd increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;

create sequence seq_wrcc increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;

create sequence seq_wrcr increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;



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


COMMENT ON COLUMN XCCS.XCCS_CARD_ID_TYPE IS 'Source of Token 1 = local interface, 2 = CRS, 3 = Token Server';

comment on column W8HP.W8HP_REMOTE_CI is '0=not active, 1=active';

comment on column W8HP.W8HP_WRCS_ID is 'Link to WRCS RemoteCheckInSystem';

comment on table WRCD is 'Suite 8 Remote Check Detail Configuration';

comment on column WRCD.WRCD_WRCS_ID is 'Link to RemoteCheckInSystem';

comment on column WRCD.WRCD_OOO is 'Web Check In is out of order';

comment on column WRCD.WRCD_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 WRCD.WRCD_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';

comment on column WRCD.WRCD_CHECKINFORPAIDONLY is '(1=true, 0=false):Allow web Check In only for pre paid reservation';

comment on column WRCD.WRCD_CHECKINWITHPAYONLY is '(1=true, 0=false):Allow web Check In only with payment plugin process';

comment on column WRCD.WRCD_USE_LOGIN is '(1=true, 0=false): Allow Web Check in with login with User name and password for the webConnect';

comment on column WRCD.WRCD_USE_TOKEN is '(1=true, 0=false): Allow Web Check in with suite8 generated token and send out by suite8';

comment on column WRCD.WRCD_USE_ID_LN is '(1=true, 0=false): Allow Web Check in with Reservation Number (YRES_ID) and Lastname';

comment on column WRCD.WRCD_USE_EXRES_LN is '(1=true, 0=false): Allow Web Check in with external reservation number and Lastname';

comment on column WRCD.WRCD_USE_EXRES_OR_ID_LN is '(1=true, 0=false): Allow Web Check in with Reservation Number (YRES_ID) or external reservation number and Lastname';

comment on column WRCD.WRCD_TIME is 'to set Web Check in time';

comment on column WRCD.WRCD_USE_UPSELL is 'Offer upsell functionality during Web Check in, phase 1 grey out this feature, we will add in future releases';

comment on column WRCD.WRCD_REG_GEN is 'Future purpose, Registration Card activation';

comment on column WRCD.WRCD_REG_WRPT_ID is 'Future purpose, Registration card Report file';

comment on column WRCD.WRCD_REG_SCAN is 'Future purpose, Registration Card Signature Scanning';

comment on column WRCD.WRCD_FOL_GEN is 'Future purpose, Folio generation';

comment on column WRCD.WRCD_FOL_WRPT_ID is 'Future purpose, Folio report file';

comment on column WRCD.WRCD_FOL_SCAN is 'Future purpose, Folio Signature Scanning';

comment on table WRCS is 'Suite 8 Remote Check Configuration';

comment on column WRCS.WRCS_ID is 'Unique internal ID';

comment on column WRCS.WRCS_LONGDESC is 'Description';

comment on column WRCS.WRCS_CODE is 'Code to be used in XML Interface';

comment on column WRCS.WRCS_ID is '0=active, 1=inactive';

comment on column WRCS.WRCS_TYPE is '1=Web Connect, 2=Kiosk (future purpose)';

comment on table WRCC is 'Suite 8 Web Check In Date config';

comment on column WRCC.WRCC_WRCS_ID is 'Link to RemoteCheckInSystem';

comment on column WRCC.WRCC_DATE is 'Date Setting';

comment on column WRCC.WRCC_OOO is 'To set Check In ooo for defined dates, overwrite setting WRCD';

comment on column WRCC.WRCC_ONLYSELECTEDROOMS is 'if 1, only rooms configured in WRCR can be assigned on the WRCC_DATE';

comment on table WRCR is 'Suite 8 RemoteCheckIn Link to Rooms';

comment on column smrt.smrt_room_blocked_by_bed_d is 'Number of rooms blocked by bed reservations. Meaningful only for mixed purpose rooms: ycat_type=2';

comment on column yres.yres_preCheckIn  is '1=Pre Check In (Web check in) was completed';

comment on column yres.yres_PaymentAtPreCheckIn is '0=Default behavior from configuration 1=Ask for payment 0=Do not ask for payment (During pre check in/web check in)';

comment on column smrt.smrt_bed_blocked_by_room_d is 'Number of beds blocked by room reservations. Meaningful only for bed types that have beds in mixed purpose rooms';



/************************************************************************************************************************
*
*
*  The run the following statement
*
************************************************************************************************************************/


update xcms set xcms_active=6 where xcms_active=5 and exists (select 1 from xccm where xccm_xcms_id=xcms_id);

delete from xcms where xcms_active=6;

delete from xccm where not exists (select 1 from xcms where xcms_id=xccm_xcms_id);

commit;