--UPDATE FROM 8.9.5.0 TO 8.9.5.1


DROP TABLE VERSION_8_9_5_0;



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

ALTER TABLE RTAB MODIFY (RTAB_NUMBER NUMBER(5), RTAB_MAX_PAX NUMBER(3), RTAB_STD_PAX NUMBER(3), RTAB_DISPLORDER NUMBER(5) );

ALTER TABLE ZDCL ADD ZDCL_INCLUDEINGROUP NUMBER(1);

COMMENT ON COLUMN ZDCL.ZDCL_INCLUDEINGROUP IS '1 = Include citytax postings in the orginial posting''s internalgroup ';

CREATE TABLE WPER (
    WPER_ID NUMBER(30,0) NOT NULL
   ,WPER_USER_IDENTIFIER VARCHAR2(60) NOT NULL
   ,WPER_DATA CLOB
   ,WPER_TIMESTAMP DATE
   ,WPER_EXPIRED DATE
   ,CONSTRAINT WPER_PK PRIMARY KEY (WPER_ID) USING INDEX TABLESPACE V8INDEX
   ,CONSTRAINT WPER_IDENTIFIER UNIQUE (WPER_USER_IDENTIFIER) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8LARGE; 
   
CREATE SEQUENCE SEQ_WPER INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE WPER  IS 'Homepage Persistent storage';

UPDATE WUSR SET WUSR_NAME = REPLACE(WUSR_NAME,'Rate ','BAR ') WHERE WUSR.WUSR_NAME LIKE '%Rate Availability Level%';

COMMENT ON COLUMN YSLM.YSLM_TYPE IS 'Rates - 0, Packages - 1, Rate Levels - 2 (currently only used for Best Western)';

ALTER TABLE XCMG ADD XCMG_INACTIVE NUMBER(1) DEFAULT 0;

ALTER TABLE XCMG ADD XCMG_INACTIVE_TIMESTAMP DATE;

COMMENT ON COLUMN ZPOS.ZPOS_NOREPRINT IS '0-Posting is not on a valid folio | 1-The posting is printed on an invoice | 2-Posting is on a void invoice | 3-Posting has been voided';

ALTER TABLE MMOD ADD MMOD_RATE_DAYS_IN_ADVANCE NUMBER(4,0);

COMMENT ON COLUMN MMOD.MMOD_RATE_DAYS_IN_ADVANCE IS 'Number of days in advance for rate and rate restriction upload. CRS determines this value';

ALTER TABLE MMOD ADD MMOD_INV_DAYS_IN_ADVANCE NUMBER(4,0);

COMMENT ON COLUMN MMOD.MMOD_INV_DAYS_IN_ADVANCE IS 'Number of days in advance for inventory upload. CRS determines this value';

ALTER TABLE ZOTA ADD ZOTA_RATE_DAYS_IN_ADVANCE NUMBER(4,0);

COMMENT ON COLUMN ZOTA.ZOTA_RATE_DAYS_IN_ADVANCE IS 'Number of days in advance for rate and rate restriction upload. CRS determines this value';

ALTER TABLE ZOTA ADD ZOTA_INV_DAYS_IN_ADVANCE NUMBER(4,0);

COMMENT ON COLUMN ZOTA.ZOTA_INV_DAYS_IN_ADVANCE IS 'Number of days in advance for inventory upload. CRS determines this value';

ALTER TABLE XCMG ADD XCMG_DOWNLOADTIMESTAMP DATE;

COMMENT ON COLUMN XCMG.XCMG_DOWNLOADTIMESTAMP IS 'Time when profile was downloaded in Suite8';

ALTER TABLE YSEA ADD YSEA_ONLYQROOMS NUMBER(1) default 0;

COMMENT ON COLUMN YSEA.YSEA_ONLYQROOMS IS '1=true';

ALTER TABLE YCAD ADD YCAD_THIRDPARTYID VARCHAR2(30);

ALTER TABLE WNAE ADD WNAE_PARAMETERS VARCHAR2(1000);

ALTER TABLE YBLH ADD YBLH_INACTIVE NUMBER(1);

ALTER TABLE YRCO ADD YRCO_VBOK_ID NUMBER(30);

ALTER TABLE MYRES ADD MYRES_YRCH_ID NUMBER(30);

ALTER TABLE MYRES ADD MYRES_YCAT_ID NUMBER(30);

ALTER TABLE YPOS ADD YPOS_ZCUR_ID NUMBER(30);

UPDATE YPOS SET YPOS_ZCUR_ID=(SELECT WUSS_VALUE FROM WUSS WHERE WUSS_NAME='BaseCurrency') WHERE YPOS_ZCUR_ID IS NULL; 

COMMIT;

ALTER TABLE YPOS MODIFY YPOS_ZCUR_ID NOT NULL;

ALTER TABLE YPOS ADD YPOS_FOREIGNUNITPRICE NUMBER(30,4);

ALTER TABLE YPOS ADD YPOS_FOREIGNGROSSUNITPRICE NUMBER(30,4);

ALTER TABLE XPAC ADD XPAC_APPLSERVICEPLAN NUMBER(1);

ALTER TABLE XPAC ADD XPAC_APPLSERVICEBOOKING NUMBER(1);

ALTER TABLE XPAC ADD XPAC_APPLSERVICESCHEDULED NUMBER(1);

COMMENT ON COLUMN XPAC.XPAC_APPLSERVICEPLAN IS 'Applies to Service Plan in Service Module';

COMMENT ON COLUMN XPAC.XPAC_APPLSERVICEBOOKING IS 'Applies to Service Booking in Service Module';

COMMENT ON COLUMN XPAC.XPAC_APPLSERVICESCHEDULED IS 'Applies to Scheduled Service in Service Module';

ALTER TABLE WLCK ADD WLCK_TABLENAME VARCHAR2(4);

ALTER TABLE MMOD ADD MMOD_UPLOAD_WEEKEND_RATES NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_UPLOAD_WEEKEND_RATES IS 'indicates whether weekend rates will be uploaded';

ALTER TABLE MMOD ADD MMOD_UPLOAD_CHILDREN_RATES NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_UPLOAD_CHILDREN_RATES IS 'indicates whether children rates will be uploaded';

ALTER TABLE XCMG ADD XCMG_DOWNLOADTIMESTAMP DATE;

COMMENT ON COLUMN XCMG.XCMG_DOWNLOADTIMESTAMP IS 'Time when profile was downloaded in Suite8';

ALTER TABLE WLOG MODIFY WLOG_TEXT VARCHAR2(400);

ALTER TABLE XPAC ADD XPAC_APPLSERVICEALLOCATION NUMBER(1);

COMMENT ON COLUMN XPAC.XPAC_APPLSERVICEALLOCATION IS 'Applies to Service Allocation in Service Module';

ALTER TABLE ZDCO ADD ZDCO_GENERATETHAIVAT NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE WPCA ADD WPCA_THAIRDNUMBER VARCHAR2(100 CHAR);

CREATE TABLE ZTTL (
   ZTTL_ID NUMBER(30) NOT NULL
  ,ZTTL_TYPE VARCHAR2(2) NOT NULL
  ,ZTTL_PAYM_ZPOS_ID NUMBER(30) NOT NULL
  ,ZTTL_ZINV_ID NUMBER(30) NOT NULL
  ,ZTTL_TAXNUMBER VARCHAR2(14) NOT NULL
  ,ZTTL_TAXINVOICEDATE DATE
  ,ZTTL_TIMESTAMP DATE
  ,CONSTRAINT ZTTL_PK PRIMARY KEY (ZTTL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE ZTTL IS 'Thai tax invoice table ';

CREATE TABLE ZTTP (
   ZTTP_ID NUMBER(30) NOT NULL
  ,ZTTP_ZTTL_ID NUMBER(30) NOT NULL
  ,ZTTP_ZPOS_ID NUMBER(30) NOT NULL
  ,CONSTRAINT ZTTP_PK PRIMARY KEY (ZTTP_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE ZTTP IS 'Thai tax invoice payment to postings link table';

CREATE TABLE ZTTX (
   ZTTX_ID NUMBER(30) NOT NULL
  ,ZTTX_ZTTL_ID NUMBER(30) NOT NULL
  ,ZTTX_CN_ZTTL_ID NUMBER(30) NOT NULL
  ,ZTTX_TIMESTAMP DATE
  ,ZTTX_USER_XCMS_ID NUMBER(30) NOT NULL
  ,CONSTRAINT ZTTX_PK PRIMARY KEY (ZTTX_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE ZTTX IS 'Thai tax credit note link table';

COMMENT ON COLUMN ZTTX.ZTTX_CN_ZTTL_ID IS 'The tax invoice''s ID which is negated by the zttx_zttl_id tax invoice';

ALTER TABLE YRCH ADD YRCH_SUPRESSOLDDETAILS NUMBER (1) default 0 NOT NULL;

COMMENT ON COLUMN YRCH.YRCH_SUPRESSOLDDETAILS IS 'hide rate details on edit screen ending in the past (system date) ';

ALTER TABLE ZTTL MODIFY ZTTL_ZINV_ID NUMBER(30) NULL;

ALTER TABLE  YRCH ADD YRCH_BASE_TYPE_WE NUMBER(1);

ALTER TABLE  YRCH ADD YRCH_BASE_ROUNDING_WE NUMBER(1);

ALTER TABLE  YRCH ADD YRCH_BASE_VALUE_WE NUMBER(10,4);

ALTER TABLE  YRCD ADD YRCD_BASE_TYPE_WE NUMBER(1);

ALTER TABLE  YRCD ADD YRCD_BASE_ROUNDING_WE NUMBER(1);

ALTER TABLE  YRCD ADD YRCD_BASE_VALUE_WE NUMBER(10,4);

COMMENT ON COLUMN YRCH.YRCH_BASE_ROUNDING_WE IS 'Weekend  rounding of the rate values, 0=no rounding, 1=round up,  2=round down, 3=round decimal up, 4= round decimal down';

COMMENT ON COLUMN YRCH.YRCH_BASE_TYPE_WE IS 'Weekend  Base rate type is 0=percentage, 1= discount amount';

COMMENT ON COLUMN YRCH.YRCH_BASE_VALUE_WE IS 'Weekend Base rate value';  

COMMENT ON COLUMN YRCd.YRCD_BASE_ROUNDING_WE IS 'Weekend  rounding of the rate values, 0=no rounding, 1=round up,  2=round down, 3=round decimal up, 4= round decimal down';

COMMENT ON COLUMN YRCD.YRCD_BASE_TYPE_WE IS 'Weekend  Base rate type is 0=percentage, 1= discount amount';

COMMENT ON COLUMN YRCD.YRCD_BASE_VALUE_WE IS 'Weekend Base rate value';  

ALTER TABLE YRAV ADD YRAV_YCAT_ID NUMBER(30) default 0;

COMMENT ON COLUMN YRAV.YRAV_YCAT_ID IS '0 = all roomtype, or  ycat_id filled. In this case the records with ycat_ids has higher priority for the given day ';

ALTER TABLE ZTTL ADD (
    ZTTL_TAXALLOCATIONVAT NUMBER(30, 4), 
    ZTTL_TAXALLOCATIONPROV NUMBER(30, 4), 
    ZTTL_VATABLEAMOUNT NUMBER(30, 4), 
    ZTTL_NONVATAMOUNT NUMBER(30, 4),
    ZTTL_AMOUNTBEFOREVAT NUMBER(30, 4));

ALTER TABLE WSQP ADD WSQP_DEF_VALUE VARCHAR2(2000);

COMMENT ON COLUMN WSQP.WSQP_DEF_VALUE IS 'Used in Mailing Wizard to store the last default value';

ALTER TABLE WSQP ADD
  ( WSQP_TABLE_NAME VARCHAR2(4)
   ,WSQP_PARAMFIELD VARCHAR2(30)
   ,WSQP_SHOWFIELD1 VARCHAR2(30)
   ,WSQP_SHOWFIELD2 VARCHAR2(30)
   ,WSQP_INTERNAL_SQL VARCHAR2(2000));

ALTER TABLE YRCH ADD YRCH_TYPE NUMBER (1) default 0;

COMMENT ON COLUMN YRCH.YRCH_TYPE IS '0=Normal Rate , 1= Service Rate';

COMMENT ON COLUMN YPAP.YPAP_TYPE IS  ' 0=per YPAP_XCAC_ID , 1=per adult,  2=flat';

ALTER TABLE ypac ADD YPAC_ISFORMULA NUMBER (1) DEFAULT 0 NOT NULL ;

COMMENT ON COLUMN YPAC.YPAC_ISFORMULA IS  'Special package type with formula price configuration';

ALTER TABLE YPAT ADD YPAT_YFOR_ID NUMBER (30) DEFAULT 0 ;

COMMENT ON COLUMN YPAT.YPAT_YFOR_ID IS  ' Formula link to package Detail';

CREATE TABLE ZDSG
(
  ZDSG_ID          NUMBER(30)                   NOT NULL,
  ZDSG_SHORTDESC   VARCHAR2(3 BYTE)             NOT NULL,
  ZDSG_LONGDESC    VARCHAR2(30 BYTE)            NOT NULL,
  ZDSG_DISPLORDER  NUMBER(3)                        NULL,
  ZDSG_INACTIVE    NUMBER(1)                        NULL,
  ZDSG_CENTRAL     NUMBER(1)                    DEFAULT 0                     NOT NULL,
  CONSTRAINT ZDSG_PK PRIMARY KEY (ZDSG_ID) USING INDEX TABLESPACE V8INDEX )  TABLESPACE V8CONF ; 
  
CREATE SEQUENCE SEQ_ZDSG INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE ZDSG IS 'department code group CCM - Sales and Catering';

COMMENT ON COLUMN ZDSG.ZDSG_INACTIVE IS ' ZDSG_INACTIVE is of ZDSG  ';

COMMENT ON COLUMN ZDSG.ZDSG_DISPLORDER IS ' ZDSG_DISPLORDER is of ZDSG';

COMMENT ON COLUMN ZDSG.ZDSG_LONGDESC IS ' ZDSG_LONGDESC is of ZDSG';

COMMENT ON COLUMN ZDSG.ZDSG_SHORTDESC IS ' ZDSG_SHORTDESC is of ZDSG';

COMMENT ON COLUMN ZDSG.ZDSG_ID IS ' ZDSG_ID identifies ZDSG';

COMMENT ON COLUMN ZDSG.ZDSG_CENTRAL IS ' ZDSG_CENTRAL used for CRS or HCN';

ALTER TABLE ZDCO ADD ZDCO_ZDSG_ID NUMBER(30);

COMMENT ON COLUMN ZDCO.ZDCO_ZDSG_ID IS ' Department code group CCM ID';

CREATE TABLE ZFCF
  (
    ZFCF_ID NUMBER(30) NOT NULL,
    ZFCF_ZINV_ID NUMBER(30) NOT NULL,
    ZFCF_GENERATION_DATE DATE,
    ZFCF_RESPONSE_XML XMLTYPE,
    ZFCF_STATUS         VARCHAR2(30),
    CONSTRAINT ZFCF_PK PRIMARY KEY (ZFCF_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE ZFCF IS 'Invoice custom fileds. Usually used with Fiscal Printer Interface';

ALTER TABLE WPER MODIFY WPER_USER_IDENTIFIER VARCHAR2(300 char);

ALTER TABLE W8HP ADD W8HP_HOTELSEGMENTCOMBO NUMBER(1);

ALTER TABLE WNAR ADD WNAR_TYPE NUMBER (2) default 0 NOT NULL;

ALTER TABLE WNAR ADD WNAR_NAME VARCHAR (200) ;

COMMENT ON COLUMN WNAR.WNAR_TYPE IS '0 - Night Audit report, 1 - Text Exports, 2 - Remote printing reports ';

COMMENT ON COLUMN WNAR.WNAR_NAME IS 'used if the report is not specified (for example for exports) ';

ALTER TABLE WNAR MODIFY WNAR_WRPT_ID NULL;

ALTER TABLE YCEP ADD YCEP_ENTEREDDATE DATE;

COMMENT ON COLUMN YCEP.YCEP_ENTEREDDATE IS 'Entered Date';

ALTER TABLE W8HP ADD W8HP_CALENDAREVENT_CONF_WRPTID NUMBER(30);

ALTER TABLE ZOTA ADD ZOTA_USE_RATELEVEL_RESTRICTION NUMBER(1);

ALTER TABLE ZOTA ADD ZOTA_UPLOAD_WEEKEND_RATES NUMBER(1);

ALTER TABLE ZOTA ADD ZOTA_UPLOAD_CHILDREN_RATES NUMBER(1);

COMMENT ON COLUMN ZOTA.ZOTA_USE_RATELEVEL_RESTRICTION IS 'indicates whether rate level restrictions are used';

COMMENT ON COLUMN ZOTA.ZOTA_UPLOAD_WEEKEND_RATES IS 'indicates whether we also upload weekend rates';

COMMENT ON COLUMN ZOTA.ZOTA_UPLOAD_CHILDREN_RATES IS 'indicates whether we also upload children rates';

ALTER TABLE YPAC ADD YPAC_REDUCE NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE ZPCF MODIFY ZPCF_CUSTOMORIGINATORNAME VARCHAR2(121);

ALTER TABLE ZOTA ADD ZOTA_UPLOAD_ALL_RES NUMBER(1);

COMMENT ON COLUMN ZOTA.ZOTA_UPLOAD_ALL_RES IS 'Indicates whether all reservations or only the one of the OTA XML will be uploaded';

ALTER TABLE WNAR ADD (WNAR_HANDLED NUMBER(1) DEFAULT 0, WNAR_WPJC_ID NUMBER(30));

ALTER TABLE WNAR ADD (WNAR_WPCA_ID NUMBER(30), WNAR_PRINTERNAME VARCHAR2(300));

INSERT INTO ZORG SELECT SEQ_ZORG.NEXTVAL ZORG_ID, ZPOS_ID ZORG_ZPOS_ID, 4 ZORG_ROLE, ZPOS_ZDCO_ID ZORG_TABLE_ID FROM ZPOS WHERE NOT EXISTS (SELECT 1 FROM ZORG WHERE ZORG_ZPOS_ID=ZPOS_ID) AND ZPOS.ZPOS_CDT <> 2;

alter table YPAL ADD YPAL_OVALUE NUMBER(30,4);

ALTER TABLE YPAL ADD YPAL_OLIMIT NUMBER(30,4);

ALTER TABLE YPAL ADD YPAL_OFOREIGNVALUE NUMBER(30,4);

ALTER TABLE YPAL ADD YPAL_OFOREIGNLIMIT NUMBER(30,4);

ALTER TABLE YPRL ADD YPRL_OVALUE NUMBER(30,4);

ALTER TABLE YPRL ADD YPRL_OLIMIT NUMBER(30,4);

ALTER TABLE YPRL ADD YPRL_OFOREIGNVALUE NUMBER(30,4);

ALTER TABLE YPRL ADD YPRL_OFOREIGNLIMIT NUMBER(30,4);

ALTER TABLE YRGP ADD YRGP_OVALUE NUMBER(30,4);

ALTER TABLE YRGP ADD YRGP_OLIMIT NUMBER(30,4);

ALTER TABLE YRGP ADD YRGP_OFVALUE NUMBER(30,4);

ALTER TABLE YRGP ADD YRGP_OFLIMIT NUMBER(30,4);

UPDATE YPAL SET YPAL_OVALUE=YPAL_VALUE WHERE YPAL_OVALUE IS NULL AND YPAL_VALUE IS NOT NULL; 

UPDATE YPAL SET YPAL_OLIMIT=YPAL_LIMIT WHERE YPAL_OLIMIT IS NULL AND YPAL_LIMIT IS NOT NULL;

UPDATE YPAL SET YPAL_OFOREIGNVALUE=YPAL_FOREIGNVALUE WHERE YPAL_OFOREIGNVALUE IS NULL AND YPAL_FOREIGNVALUE IS NOT NULL; 

UPDATE YPAL SET YPAL_OFOREIGNLIMIT=YPAL_FOREIGNLIMIT WHERE YPAL_OFOREIGNLIMIT IS NULL AND YPAL_FOREIGNLIMIT IS NOT NULL;

UPDATE YPRL SET YPRL_OVALUE=YPRL_VALUE WHERE YPRL_OVALUE IS NULL AND YPRL_VALUE IS NOT NULL; 

UPDATE YPRL SET YPRL_OLIMIT=YPRL_LIMIT WHERE YPRL_OLIMIT IS NULL AND YPRL_LIMIT IS NOT NULL;

UPDATE YPRL SET YPRL_OFOREIGNVALUE=YPRL_FOREIGNVALUE WHERE YPRL_OFOREIGNVALUE IS NULL AND YPRL_FOREIGNVALUE IS NOT NULL; 

UPDATE YPRL SET YPRL_OFOREIGNLIMIT=YPRL_FOREIGNLIMIT WHERE YPRL_OFOREIGNLIMIT IS NULL AND YPRL_FOREIGNLIMIT IS NOT NULL;

UPDATE YRGP SET YRGP_OVALUE=YRGP_VALUE WHERE YRGP_OVALUE IS NULL AND YRGP_VALUE IS NOT NULL; 

UPDATE YRGP SET YRGP_OLIMIT=YRGP_LIMIT WHERE YRGP_OLIMIT IS NULL AND YRGP_LIMIT IS NOT NULL;

UPDATE YRGP SET YRGP_OFVALUE=YRGP_FVALUE WHERE YRGP_OFVALUE IS NULL AND YRGP_FVALUE IS NOT NULL; 

UPDATE YRGP SET YRGP_OFLIMIT=YRGP_FLIMIT WHERE YRGP_OFLIMIT IS NULL AND YRGP_FLIMIT IS NOT NULL;

ALTER TABLE MMOD ADD MMOD_UPLOAD_ONLY_DEFAULT_RATE NUMBER(1) DEFAULT 0;

COMMENT ON COLUMN MMOD.MMOD_UPLOAD_ONLY_DEFAULT_RATE IS 'indicates whether only the default rate will be loaded or all rates';

ALTER TABLE ZOTA ADD ZOTA_UPLOAD_ONLY_DEFAULT_RATE NUMBER(1) DEFAULT 0;

COMMENT ON COLUMN ZOTA.ZOTA_UPLOAD_ONLY_DEFAULT_RATE IS 'indicates only the default rate will be loaded or all rates';

ALTER TABLE  YRCH ADD YRCH_YRAS_ID NUMBER(30);

COMMENT ON COLUMN YRCH.YRCH_YRAS_ID IS 'Customer Source Code';

ALTER TABLE  YRCD ADD YRCD_YRAS_ID NUMBER(30);

COMMENT ON COLUMN YRCD.YRCD_YRAS_ID IS 'Customer Source Code';