--UPDATE FROM 8.12.4.0 TO 8.13.0.0

DROP TABLE VERSION_8_12_4_0;

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

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

create table SETT (
    SETT_ID NUMBER(30) not null
   ,SETT_SHORTDESC VARCHAR2(3) not null
   ,SETT_LONGDESC VARCHAR2(60) not null
   ,SETT_DISPLORDER NUMBER(3,0)
   ,SETT_INACTIVE NUMBER(1,0) 
   ,SETT_REPEATING NUMBER(1,0)
   ,SETT_WCOL_ID NUMBER(30,0) 
   ,constraint SETT_PK primary key (SETT_ID) using index tablespace v8index) tablespace v8conf;


create table SEDE (
    SEDE_ID NUMBER(30) not null
   ,SEDE_SETT_ID NUMBER(30) not null
   ,SEDE_FROMDATE DATE
   ,SEDE_TODATE DATE
   ,SEDE_DESCRIPTION VARCHAR2(60)
   ,SEDE_ACTIVE NUMBER(1)
   ,SEDE_CLOSED NUMBER(1)
   ,constraint SEDE_PK primary key (SEDE_ID) using index tablespace v8index) tablespace v8conf;

create table xchg (
   xchg_id number(30) not null
  ,xchg_globalid number(30) unique
  ,xchg_SetOrClear number(1) not null
  ,xchg_timestamp date not null
  ,constraint xchg_pk primary key (xchg_id) using index tablespace v8index
) tablespace v8main;


create table xrsn (
    xrsn_xcms_globalid number(30) not null
   ,xrsn_timestamp date not null
   ,constraint xrsn_pk primary key (xrsn_xcms_globalid) using index tablespace v8index
) tablespace v8main;


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


alter table ybef add ybef_revenuetype number(1);

alter table ypkf add ypkf_revenuetype number(1);

alter table ybla add ybla_yres_id number(30);

alter table zche modify zche_guestcheck clob;

Alter table xjet add xjet_companycode VARCHAR2(10);

ALTER TABLE W8HP ADD W8HP_CCM_SHOWPACKAGEDETAIL NUMBER(1) DEFAULT 1;

alter table wses add wses_LastActive date;

alter table shis add shis_bed_noshow number(1) default 0 not null;

alter table yhsp add yhsp_ybed_id number(30);

alter table w8hp add w8hp_ccm_deposit_zdco_id number(30);

alter table w8hp add w8hp_google_adwords number(1);

alter table w8hp add w8hp_google_conversion_id  varchar2(60);

alter table w8hp add w8hp_google_conversion_label  varchar2(60);

alter table zdco add zdco_disabled_chargeit number(1,0) default 1 not null;

alter table wscs modify wscs_xced_id null;

ALTER INDEX zche_pk REBUILD ;

ALTER TABLE zche ADD PRIMARY KEY (zche_id);

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


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

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

create sequence seq_xchg increment by 1 start with 1000 nomaxvalue;




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


comment on column ybef.ybef_revenuetype is 'Revenue type = 0 - flat amount / 1 - per hour / 2 - per person';

comment on column ypkf.ypkf_revenuetype is 'Revenue type = 0 - flat amount / 1 - per hour / 2 - per person';

comment on column ybla.ybla_yres_id is 'Link to imported rooming list reservation';

COMMENT ON TABLE SETT IS 'Statistical Season Typ Table';

COMMENT ON TABLE SEDE  IS 'Statistical Season Details';

COMMENT ON COLUMN W8HP.W8HP_CCM_SHOWPACKAGEDETAIL IS '1=show package details, 0=hide package details';

comment on table xchg is 'Profile subscriptions to be transferred to central';

comment on column xchg.xchg_SetOrClear is '1=set 0=clear';

comment on column wses.wses_LastActive is 'Last time an offline session had database reconnect/disconnect. Used for lock removal for idle or stuck sessions after process terminated without logout';

comment on column w8hp.w8hp_ccm_deposit_zdco_id is 'Department code for ccm deposit';

comment on column w8hp.w8hp_google_adwords is 'Turns on/off use of ad words';

comment on column w8hp.w8hp_google_conversion_id is 'hotel specific';

comment on column w8hp.w8hp_google_conversion_label is 'hotel specific';

comment on table xrsn is 'Profile global id subscription resend log';



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


update zdco set zdco_disabled_chargeit = 0 where (zdco_cdt = 5) and (zdco_paym_type in (0,3,4));
