/************************************************************************************************************************
*
*
*  Date: December 17, 2014  09:22:45
*
*  This upgrade script was automatically generated by DBDiff for Oracle (www.dbdiff.com)
*
*  SOURCE DATABASE:  VERSION_8_9_6@V8TEST2
*  TARGET DATABASE:  VERSION_8_9_5@V8TEST2
*
*  This script should be executed as VERSION_8_9_5 in database VERSION_8_9_5@V8TEST2 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 upgrade from 8_9_5_0 to 8_9_5_1
*
*
************************************************************************************************************************/

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';


/************************************************************************************************************************
*
*  The following statements will upgrade from 8_9_5_1 to 8_9_5_2
*
************************************************************************************************************************/

ALTER TABLE XCID ADD XCID_WTWN_ID NUMBER(30);

COMMENT ON COLUMN XCID.XCID_WTWN_ID IS 'Link to birth town';

ALTER TABLE xcid ADD XCID_BIRTHCOUNTRY_XCOU_ID NUMBER(30);

COMMENT ON COLUMN XCID.XCID_BIRTHCOUNTRY_XCOU_ID IS 'Birth country of profile';

ALTER TABLE xcid ADD XCID_BIRTHSTATE_XSTA_ID NUMBER(30);

COMMENT ON COLUMN XCID.XCID_BIRTHSTATE_XSTA_ID IS 'Birth State of profile';

ALTER TABLE WTWN ADD WTWN_XCOU_ID NUMBER(30);

COMMENT ON COLUMN WTWN.WTWN_XCOU_ID IS 'Link to Country id';

ALTER TABLE WTWN ADD WTWN_XSTA_ID NUMBER(30);

COMMENT ON COLUMN WTWN.WTWN_XSTA_ID IS 'Link to State id';

CREATE TABLE WFTP
(
  WFTP_ID                      NUMBER(30)       NOT NULL,
  WFTP_SERVER                  VARCHAR2(100 BYTE),
  WFTP_USERNAME                VARCHAR2(30 BYTE),
  WFTP_PASSWORD                VARCHAR2(30 BYTE),
  WFTP_PORT                    NUMBER(5),
  WFTP_MODE                    NUMBER(1),
  WFTP_PROTOCOL                NUMBER(1),
  WFTP_ENCRYPTION              NUMBER(1),
  WFTP_LOGON_TYPE              NUMBER(1),
  WFTP_CERTIFICATE             VARCHAR2(100 BYTE),
  WFTP_CERTIFICATE_PWD         VARCHAR2(1024 BYTE),
  WFTP_COMPRESSION             NUMBER(1),
  WFTP_ENCRYPT_DATACHANNEL     NUMBER(1),
  WFTP_CERTIFICATE_FORMAT      NUMBER(1),
  WFTP_USE_SSL                 NUMBER(1),
  WFTP_SSL_SESSION_RESUMPTION  NUMBER(1),
  WFTP_DESCRIPTION             VARCHAR2(100 BYTE),
  WFTP_DEBUG                   NUMBER(1),
  CONSTRAINT WFTP_PK  PRIMARY KEY  (WFTP_ID)  USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON COLUMN WFTP.WFTP_ID IS 'Unique Id';

COMMENT ON COLUMN WFTP.WFTP_SERVER IS 'ftp url';

COMMENT ON COLUMN WFTP.WFTP_USERNAME IS 'username of ftp account';

COMMENT ON COLUMN WFTP.WFTP_PASSWORD IS 'password of ftp account';

COMMENT ON COLUMN WFTP.WFTP_PORT IS 'port, standard = 21';

COMMENT ON COLUMN WFTP.WFTP_MODE IS '0 = Default of used protocol, 1 = Active, 2 = Passive';

COMMENT ON COLUMN WFTP.WFTP_PROTOCOL IS '0 = FTP/FTPS, 1 SFTP';

COMMENT ON COLUMN WFTP.WFTP_ENCRYPTION IS '0 = explicit FTP over TLS, 1 =  implicit FTP over TLS, 2 = Plain FTP (no encryption)';

COMMENT ON COLUMN WFTP.WFTP_LOGON_TYPE IS '0 = Anonymous, 1 = Normal';

COMMENT ON COLUMN WFTP.WFTP_CERTIFICATE IS 'file name and path of certificate file';

COMMENT ON COLUMN WFTP.WFTP_CERTIFICATE_PWD IS 'certificate password';

COMMENT ON COLUMN WFTP.WFTP_COMPRESSION IS '0 = No, 1 = Yes';

COMMENT ON COLUMN WFTP.WFTP_ENCRYPT_DATACHANNEL IS '0 = No, 1 = Yes';

COMMENT ON COLUMN WFTP.WFTP_CERTIFICATE_FORMAT IS '0 = DER, 1 = PEM, 2 = PFX, 3 = SPC';

COMMENT ON COLUMN WFTP.WFTP_USE_SSL IS '0 = No, 1 = Yes';

COMMENT ON COLUMN WFTP.WFTP_SSL_SESSION_RESUMPTION IS '0 = No, 1 = Yes';

COMMENT ON COLUMN WFTP.WFTP_DESCRIPTION IS 'Name of the FTP Account';

COMMENT ON COLUMN WFTP.WFTP_DEBUG IS 'Debug level for FTP 0 = No Logging, 1 = Command Logging';

ALTER TABLE MMOD ADD MMOD_WFTP_ID NUMBER(30);

COMMENT ON COLUMN MMOD.MMOD_WFTP_ID IS 'Link to the FTP account tabel';

INSERT INTO WUSS (WUSS_ID, WUSS_XCMS_ID, WUSS_NAME, WUSS_VALUE) VALUES (SEQ_WUSS.NEXTVAL, 0, 'ExeUpdateFTPAccountId', 0);

INSERT INTO WUSS (WUSS_ID, WUSS_XCMS_ID, WUSS_NAME, WUSS_VALUE) VALUES (SEQ_WUSS.NEXTVAL, 0, 'ExeUpdateFTPAccountDesc', '');

INSERT INTO WUSS (WUSS_ID, WUSS_XCMS_ID, WUSS_NAME, WUSS_VALUE) VALUES (SEQ_WUSS.NEXTVAL, 0, 'ExeUpdateFTPFolder', '');

INSERT INTO WUSS (WUSS_ID, WUSS_XCMS_ID, WUSS_NAME, WUSS_VALUE) VALUES (SEQ_WUSS.NEXTVAL, 0, 'ExeUpdateFTPFolder', '');

ALTER TABLE WATI ADD WATI_WFTP_ID NUMBER(30);

ALTER TABLE WATI ADD WATI_ftpfolder VARCHAR2(100);

COMMENT ON COLUMN WATI.WATI_WFTP_ID IS 'link to ftp account used for download';

COMMENT ON COLUMN WATI.WATI_FTPFOLDER IS 'remote ftp folder, where files are stored for download';

ALTER TABLE XSCD ADD XSCD_WFTP_ID NUMBER(30);

ALTER TABLE XSCD ADD XSCD_FTP_REMOTEDIR VARCHAR2(100);

COMMENT ON COLUMN XSCD.XSCD_WFTP_ID IS 'Linked FTP Account';

COMMENT ON COLUMN XSCD.XSCD_FTP_REMOTEDIR IS 'Remote directory on FTP Server';

ALTER TABLE WFTP ADD WFTP_USE_MLIS NUMBER(1);

ALTER TABLE WFTP ADD WFTP_USE_TLS NUMBER(1);

ALTER TABLE WFTP ADD WFTP_TRANSFER_TYPE NUMBER(1);

COMMENT ON COLUMN WFTP.WFTP_USE_MLIS IS 'indicates the use of MLST/MLSD to lISt directories';

COMMENT ON COLUMN WFTP.WFTP_USE_TLS IS 'indicates use of TLS 0 = No, 1 = Explicit TLS, 2 = Implicit TLS, 3 = Require TLS';

COMMENT ON COLUMN WFTP.WFTP_TRANSFER_TYPE IS 'ftp transfer type 0 = ASCII, 1 = Binary (default)';

ALTER TABLE YBLP modify YBLP_RESOURCE_TYPE NUMBER(2); 

COMMENT ON COLUMN YBLP.YBLP_RESOURCE_TYPE IS 'resource type of booking which has posted amount - 1=package events | 2=function spaces | 3=mISc. items  |5=menu items | 8= Standard Menu Split |9 = package header| 10- standard menu';

ALTER TABLE WIMG ADD WIMG_PRIMARY NUMBER(1);

COMMENT ON COLUMN WIMG.WIMG_PRIMARY IS 'Primary flag, used on profile html, to identify the picture to dISplay';

ALTER TABLE XDEV ADD XDEV_SAVEPICTURESAUTOMATICALLY NUMBER(1);

COMMENT ON COLUMN XDEV.XDEV_SAVEPICTURESAUTOMATICALLY IS 'Auto adjust pictures to document group settings';

COMMENT ON COLUMN XPDG.XPDG_ROLE IS ' passports = 1, id card=2, drivers licence=3, vISa=4    .. to be continued  ';

ALTER TABLE XPDC ADD XPDC_PERSONALNUMBER VARCHAR2(60);

COMMENT ON COLUMN XPDC.XPDC_PERSONALNUMBER IS 'Individual personal NUMBER';

ALTER TABLE MMOD ADD MMOD_DOWNLOAD_RES NUMBER(1) DEFAULT 1;

COMMENT ON COLUMN MMOD.MMOD_DOWNLOAD_RES IS 'indicates whether CRS downloads reservation. Not configurable on config screen';

ALTER TABLE zota ADD ZOTA_DOWNLOAD_RES NUMBER(1) DEFAULT 1;

COMMENT ON COLUMN ZOTA.ZOTA_DOWNLOAD_RES IS 'indicates whether XML OTA downloads reservation. Configurable on config screen';

ALTER TABLE XPAC ADD XPAC_MANDATORYSERVICEPLAN NUMBER(1);

ALTER TABLE XPAC ADD XPAC_MANDATORYSERVICEBOOKING NUMBER(1);

ALTER TABLE XPAC ADD XPAC_MANDATORYSERVICESCHEDULED NUMBER(1);

ALTER TABLE XPAC ADD XPAC_MANDATORYSERVICEALLOCAT NUMBER(1);

COMMENT ON COLUMN xpac.xpac_MANDATORYSERVICEPLAN IS 'Mandatory for Service Plan in Service Module';

COMMENT ON COLUMN xpac.xpac_MANDATORYSERVICEBOOKING IS 'Mandatory for Service Booking in Service Module';

COMMENT ON COLUMN xpac.xpac_MANDATORYSERVICESCHEDULED IS 'Mandatory for Scheduled Service in Service Module';

COMMENT ON COLUMN xpac.xpac_MANDATORYSERVICEALLOCAT IS 'Mandatory for Service Allocation in Service Module';

ALTER TABLE ZDCO ADD ZDCO_BAVEL_TYPE  NUMBER(1) ;

COMMENT ON COLUMN ZDCO. ZDCO_BAVEL_TYPE IS '0 =  ZDCO_BAVEL_TYPE  EarlyPayment, 1  =  ZDCO_BAVEL_TYPE  EarlyBooking, 2  =  ZDCO_BAVEL_TYPE  Commercial, 3  =  ZDCO_BAVEL_TYPE  Other, 4  =  ZDCO_BAVEL_TYPE  Room, -1  =  ZDCO_BAVEL_TYPE  None  ';

UPDATE MMOD SET MMOD_DOWNLOAD_RES = 1 where not (MMOD_vendor_id in ('CAEDA', 'FERAT', 'WEBRE'));

ALTER TABLE YPRM ADD YPRM_WEBENABLED NUMBER (1);

ALTER TABLE YPRM ADD YPRM_WEBDESC VARCHAR2 (100);

COMMENT ON COLUMN YPRM.YPRM_WEBDESC IS 'Web description';

ALTER TABLE MMOD ADD MMOD_MAX_ADULT_RATES NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_MAX_ADULT_RATES IS 'maximal NUMBER of adult rates supported by the CRS';

UPDATE MMOD SET MMOD_MAX_ADULT_RATES = 5 where (MMOD_vendor_id in ('ALEAN', 'GHA', 'ORS', 'SYNX'));

UPDATE MMOD SET MMOD_MAX_ADULT_RATES = 6 WHERE MMOD_VENDOR_ID = 'HWS';

UPDATE MMOD SET MMOD_MAX_ADULT_RATES = 1 WHERE MMOD_VENDOR_ID = 'RIT';

UPDATE MMOD SET MMOD_MAX_ADULT_RATES = 4 WHERE MMOD_VENDOR_ID = 'SMHOT';

UPDATE MMOD SET MMOD_MAX_ADULT_RATES = 3 WHERE MMOD_VENDOR_ID = 'TCLPC';

UPDATE MMOD SET MMOD_MAX_ADULT_RATES = 0 WHERE NOT (MMOD_VENDOR_ID IN ('ALEAN', 'GHA', 'HWS', 'ORS', 'SMHOT', 'SYNX', 'RIT', 'TCLPC'));

ALTER TABLE YRDC ADD YRDC_YCAD_ID NUMBER(30)  DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YRDC.YRDC_YCAD_ID  IS 'Day type calendar value link to Hotel Segment';

CREATE table YRDM (
   YRDM_ID NUMBER(30) NOT NULL
   ,YRDM_YRDB_ID NUMBER(30) NOT NULL
   ,YRDM_YCAD_ID NUMBER(30) NOT NULL
   ,CONSTRAINT YRDM_PK PRIMARY KEY (YRDM_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE YRDM IS 'auto daytype rule to hotelsegment linktable';

COMMENT ON COLUMN WRPT.WRPT_SAVEREPORT IS 'Obsolete';

ALTER TABLE WCIP ADD WCIP_SHOWBANNER NUMBER(1);

ALTER TABLE WCIP ADD WCIP_PACKAGES VARCHAR2(100);

ALTER TABLE WADV ADD WADV_KIOSKENABLED NUMBER(1);

COMMENT ON COLUMN WCIP.WCIP_SHOWBANNER IS 'flag to show/hide Banner';

COMMENT ON COLUMN WCIP.WCIP_PACKAGES IS 'List of Kiosk enabled packages';

CREATE TABLE WREX (
    WREX_ID NUMBER(30) NOT NULL
   ,WREX_ROLE NUMBER(3) NOT NULL
   ,WREX_TYPE NUMBER(1) NOT NULL 
   ,WREX_PATH VARCHAR2(500) NOT NULL
   ,WREX_FILENAME VARCHAR2(30)
   ,WREX_FORMAT NUMBER(2)
   ,WREX_INACTIVE NUMBER(1)
   ,CONSTRAINT WREX_PK PRIMARY KEY (WREX_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;
 
CREATE sequence seq_WREX increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;

COMMENT ON TABLE WREX IS 'Configuration for report exports';

COMMENT ON COLUMN WREX.WREX_ROLE IS 'Role of report = wrps_role';

COMMENT ON COLUMN WREX.WREX_TYPE IS 'Type of report = wrps_type';

COMMENT ON COLUMN WREX.WREX_PATH IS 'Path where reports will be saved';

COMMENT ON COLUMN WREX.WREX_FILENAME IS 'Filename to which _timestamp will be appended';

COMMENT ON COLUMN WREX.WREX_FORMAT IS 'Export format, thIS should be correlated with wrps_role';

COMMENT ON COLUMN WREX.WREX_INACTIVE IS '0 - execute; 1- do not execute';

ALTER TABLE YCET ADD YCET_WEB_DELETE_PARTICIPANTS NUMBER(1);

COMMENT ON COLUMN YCET.YCET_WEB_DELETE_PARTICIPANTS IS '1. Allow Participant deletion in Webconnect';

ALTER TABLE ZDCO ADD ZDCO_SAFT_PAYMENTmECHANISM VARCHAR2(2 char);

COMMENT ON COLUMN ZINN.ZINN_TYPE IS 
' 1 = invoices, 2 = user definable number cycles, 3 = currency exchange (zfag_type 3), 4 = autonumber for booth telephone charges paid cash, 
  5 = City Ledger Number for ZINV_CITYLEDGERNUMBER, 6 = Information Folio, 7 = Simplified Invoice (Portugal), 8 = City Ledger Folio, 11 = autonumber zfag_type 1 (ar postings), 
  12 = autonumber zfag_type 2 (ar payments), 13 = void invoice, 14 = fattura (for italy), 15 = nota di credito (for Italy or credit note for Greece, Portugal or Spain), 
  16 = proforma, 17 = fattura and nota di credito (for italy), 18 = nota korekta (for poland), 19 = Egyptian AR Invoice, 20 = AR Payment Receipt, 
  21 = registration cards, 22 = Ticket BarCode, 23 = Contract Numbers, 31 = automatic fa numbers (zfac_autonumber), 32 = lost and found, 33 = police export, 
  34 = tourist export, 35 = jetweb interface, 36 = Voucher, 37 = membership numbers, 38 = AR Compressions 
  40 = Portugal Manual Invoice, 41 = Portugal Manual Credit Note, 42 = Portugal Manual Void Invoice, 43 = Portugal Manual Simple Invoice
  44 = Manual City Ledger Invoice ';


/************************************************************************************************************************
*
*  The following statements will upgrade from 8_9_5_2 to 8_9_5_3
*
************************************************************************************************************************/


CREATE TABLE YCRN (
   YCRN_ID NUMBER(30) NOT NULL
  ,YCRN_YRES_ID NUMBER(30) NOT NULL
  ,YCRN_CRSSYSTEM VARCHAR2(30) NOT NULL
  ,YCRN_CRSRESNR VARCHAR2(50) NULL  
  ,CONSTRAINT YCRN_PK PRIMARY KEY (YCRN_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE YCRN IS 'Third party CRS id for reservation';

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

ALTER TABLE YCRN MODIFY YCRN_CRSRESNR NULL;

ALTER TABLE YRES ADD YRES_CRSMAIN NUMBER(1);

COMMENT ON COLUMN YRES.YRES_CRSMAIN IS 'Main reservation of sharers when uploading them as a single reservation to crs systems'; 

ALTER TABLE ZITR MODIFY ZITR_INFO VARCHAR2(100);

ALTER TABLE ZIPT MODIFY ZIPT_INFO VARCHAR2(100);

ALTER TABLE YRDB ADD YRDB_TENTATIVERES NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE YPRM ADD YPRM_WEBLONG VARCHAR2 (1000);

COMMENT ON COLUMN YPRM.YPRM_WEBLONG IS 'web long description';

CREATE TABLE WLIB (
   WLIB_ID NUMBER(30) NOT NULL
  ,WLIB_TIMESTAMP DATE NOT NULL
  ,WLIB_HASH VARCHAR2(32)
  ,WLIB_DATA CLOB NOT NULL
  ,CONSTRAINT WLIB_PK PRIMARY KEY (WLIB_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;   

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


/************************************************************************************************************************
*
*  The following statements will upgrade from 8_9_5_3 to 8_9_5_4
*
************************************************************************************************************************/

CREATE TABLE VSTG (
   VSTG_ID NUMBER(30) NOT NULL
  ,VSTG_SHORTDESC VARCHAR2(12) NOT NULL UNIQUE
  ,VSTG_LONGDESC VARCHAR2(30) NOT NULL UNIQUE
  ,VSTG_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTG_DISPLORDER NUMBER(3) 
  ,CONSTRAINT VSTG_PK PRIMARY KEY (VSTG_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSTG is 'Service type group';

CREATE TABLE VSTS (
   VSTS_ID NUMBER(30) NOT NULL
  ,VSTS_SHORTDESC VARCHAR2(12) NOT NULL
  ,VSTS_LONGDESC VARCHAR2(30) NOT NULL
  ,VSTS_DISPLORDER NUMBER(3) 
  ,VSTS_VSTG_ID NUMBER(30) NOT NULL
  ,VSTS_PROVIDER_XCMS_ID NUMBER(30)
  ,VSTS_THIRDPARTY NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_ALLOCATIONS NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_MAXNUMBER NUMBER(3)
  ,VSTS_CHARGETYPE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_WCOL_ID NUMBER(30)
  ,VSTS_SCHEDULED NUMBER(1) DEFAULT 1 NOT NULL
  ,VSTS_FROMTIME DATE 
  ,VSTS_UNTILTIME DATE
  ,VSTS_PICKUPNEEDED NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_MINPERSON NUMBER(3)
  ,VSTS_MAXPERSON NUMBER(3)
  ,VSTS_TODATEALLOWED NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_TOTIMEALLOWED NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_DESCRIPTIONALLOWED NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_SIMPLEPRICESETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_PRICE NUMBER(30,4)
  ,VSTS_PRICE_ZCUR_ID NUMBER(30)
  ,VSTS_PRICE_ZDCO_ID NUMBER(30)
  ,VSTS_PRICEFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_PRICEPERCENTAGE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_PRICE_YRCH_ID NUMBER(30)
  ,VSTS_SIMPLECOSTSETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_COST NUMBER(30,4)
  ,VSTS_COST_ZCUR_ID NUMBER(30)
  ,VSTS_COST_ZDCO_ID NUMBER(30)
  ,VSTS_COSTFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_COST_YRCH_ID NUMBER(30)
  ,VSTS_SIMPLETAXSETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_TAX NUMBER(30,4)
  ,VSTS_TAX_ZCUR_ID NUMBER(30)
  ,VSTS_TAX_ZDCO_ID NUMBER(30)
  ,VSTS_TAX_YRCH_ID NUMBER(30)
  ,VSTS_PRICEDESCALLOWED NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_TAXFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_ALLOWWITHOUTCLASS NUMBER(1) DEFAULT 0 NOT NULL
  ,CONSTRAINT VSTS_PK PRIMARY KEY (VSTS_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

COMMENT ON TABLE VSTS is 'Service type';

COMMENT ON COLUMN VSTS.VSTS_MAXNUMBER is 'For example: for hotel owned vehicles have a maximum NUMBER';

COMMENT ON COLUMN VSTS.VSTS_CHARGETYPE is '0=once 1=per hour 2=per day'; 

COMMENT ON COLUMN VSTS.VSTS_SCHEDULED is '0=service is available on demand. ie: taxi 1=available at prescheduled times';

COMMENT ON COLUMN VSTS.VSTS_FROMTIME is 'For non scheduled services this defines availability of service during the day';

COMMENT ON COLUMN VSTS.VSTS_UNTILTIME is 'For non scheduled services this defines availability of service during the day';

COMMENT ON COLUMN VSTS.VSTS_DESCRIPTIONALLOWED is '0=disabled 1=enabled 2=mandatory';

COMMENT ON COLUMN VSTS.VSTS_PRICEDESCALLOWED is '0=disabled 1=enabled 2=mandatory';

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

CREATE TABLE VSCL (
   VSCL_ID NUMBER(30) NOT NULL
  ,VSCL_SHORTDESC VARCHAR2(12) NOT NULL
  ,VSCL_LONGDESC VARCHAR2(30) NOT NULL
  ,VSCL_DISPLORDER NUMBER(3) 
  ,VSCL_VSTS_ID NUMBER(30) NOT NULL
  ,VSCL_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_SIMPLEPRICESETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_PRICE NUMBER(30,4)
  ,VSCL_PRICE_ZCUR_ID NUMBER(30)
  ,VSCL_PRICE_ZDCO_ID NUMBER(30)
  ,VSCL_PRICEFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_PRICEPERCENTAGE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_PRICE_YRCH_ID NUMBER(30)
  ,VSCL_SIMPLECOSTSETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_COST NUMBER(30,4)
  ,VSCL_COST_ZCUR_ID NUMBER(30)
  ,VSCL_COST_ZDCO_ID NUMBER(30)
  ,VSCL_COSTFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_COST_YRCH_ID NUMBER(30)
  ,VSCL_SIMPLETAXSETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_TAX NUMBER(30,4)
  ,VSCL_TAX_ZCUR_ID NUMBER(30)
  ,VSCL_TAX_ZDCO_ID NUMBER(30)
  ,VSCL_TAXFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_TAX_YRCH_ID NUMBER(30)
  ,CONSTRAINT VSCL_PK PRIMARY KEY (VSCL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSCL is 'Service class';

CREATE TABLE VSPL (
   VSPL_ID NUMBER(30) NOT NULL
  ,VSPL_VSTS_ID NUMBER(30) NOT NULL
  ,VSPL_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSPL_PK PRIMARY KEY (VSPL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSPL is 'Service - PRICE Package link';

CREATE TABLE VSPC (
   VSPC_ID NUMBER(30) NOT NULL
  ,VSPC_VSTS_ID NUMBER(30) NOT NULL
  ,VSPC_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSPC_PK PRIMARY KEY (VSPC_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSPC is 'Service - Cost Package link';

CREATE TABLE VSPT (
   VSPT_ID NUMBER(30) NOT NULL
  ,VSPT_VSTS_ID NUMBER(30) NOT NULL
  ,VSPT_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSPT_PK PRIMARY KEY (VSPT_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSPT is 'Service - Tax Package link';

CREATE TABLE VSCP (
   VSCP_ID NUMBER(30) NOT NULL
  ,VSCP_VSCL_ID NUMBER(30) NOT NULL
  ,VSCP_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSCP_PK PRIMARY KEY (VSCP_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSCP is 'Service Class - PRICE Package link';

CREATE TABLE VSCC (
   VSCC_ID NUMBER(30) NOT NULL
  ,VSCC_VSCL_ID NUMBER(30) NOT NULL
  ,VSCC_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSCC_PK PRIMARY KEY (VSCC_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSCC is 'Service Class - Cost Package link';

CREATE TABLE VSCT (
   VSCT_ID NUMBER(30) NOT NULL
  ,VSCT_VSCL_ID NUMBER(30) NOT NULL
  ,VSCT_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSCT_PK PRIMARY KEY (VSCT_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSCT is 'Service Class - Cost Package link';

CREATE TABLE VSAV (
   VSAV_ID NUMBER(30) NOT NULL
  ,VSAV_VSTS_ID NUMBER(30) NOT NULL
  ,VSAV_FROMDATE DATE NOT NULL
  ,VSAV_TODATE DATE NOT NULL
  ,VSAV_FROMHOUR DATE
  ,VSAV_TOHOUR DATE
  ,VSAV_MON NUMBER(1) 
  ,VSAV_TUE NUMBER(1) 
  ,VSAV_WED NUMBER(1) 
  ,VSAV_THU NUMBER(1) 
  ,VSAV_FRI NUMBER(1) 
  ,VSAV_SAT NUMBER(1) 
  ,VSAV_SUN NUMBER(1)
  ,VSAV_STATUS NUMBER(1) DEFAULT 1 NOT NULL 
  ,CONSTRAINT VSAV_PK PRIMARY KEY (VSAV_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSAV is 'Service availability';

COMMENT ON COLUMN VSAV.VSAV_STATUS is '0=not available 1=available';

CREATE TABLE VCTY (
   VCTY_ID NUMBER(30) NOT NULL
  ,VCTY_SHORTDESC VARCHAR2(12) NOT NULL
  ,VCTY_LONGDESC VARCHAR2(30) NOT NULL
  ,VCTY_DISPLORDER NUMBER(3) 
  ,VCTY_XCOU_ID VARCHAR2(30)
  ,VCTY_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
  ,CONSTRAINT VCTY_PK PRIMARY KEY (VCTY_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VCTY is 'City';

CREATE TABLE VCAS (
    VCAS_ID NUMBER(30) NOT NULL
   ,VCAS_VCTY_ID NUMBER(30) NOT NULL 
   ,VCAS_VSTS_ID NUMBER(30) NOT NULL
   ,VCAS_NUMBER NUMBER(3) 
   ,CONSTRAINT VCAS_PK PRIMARY KEY (VCAS_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VCAS is 'Available service list for hotels';

CREATE TABLE VLOC (
   VLOC_ID NUMBER(30) NOT NULL
  ,VLOC_SHORTDESC VARCHAR2(12) NOT NULL
  ,VLOC_LONGDESC VARCHAR2(30) NOT NULL
  ,VLOC_DISPLORDER NUMBER(3) 
  ,VLOC_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
  ,VLOC_VCTY_ID VARCHAR2(30)
  ,VLOC_WPHS_ID VARCHAR2(30)
  ,VLOC_WPHSREPR NUMBER(1) DEFAULT 0 NOT NULL
  ,VLOC_GPS VARCHAR2(50)
  ,CONSTRAINT VLOC_PK PRIMARY KEY (VLOC_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VLOC is 'Location';

COMMENT ON COLUMN VLOC.VLOC_WPHSREPR is 'Hotel representation type: 0=hotel 1=location inside hotel 2=location near hotel';

ALTER TABLE WPHS ADD WPHS_SYSTEMTYPE NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN WPHS.WPHS_SYSTEMTYPE is '0=fidelio pms 1=third party system'; 

ALTER TABLE WPHS ADD WPHS_PROPERTYTYPE NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN WPHS.WPHS_PROPERTYTYPE is '0=hotel 1=restaurant 2=spa'; 

ALTER TABLE WPHS ADD WPHS_VLOC_ID NUMBER(30);

CREATE TABLE VLAS (
    VLAS_ID NUMBER(30) NOT NULL
   ,VLAS_VLOC_ID NUMBER(30) NOT NULL 
   ,VLAS_VSTS_ID NUMBER(30) NOT NULL
   ,VLAS_NUMBER NUMBER(3) 
   ,CONSTRAINT VLAS_PK PRIMARY KEY (VLAS_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VLAS is 'Available service list for locations';

CREATE TABLE VRST (
    VRST_ID NUMBER(30) NOT NULL
   ,VRST_SHORTDESC VARCHAR2(10)
   ,VRST_LONGDESC VARCHAR2(50)
   ,VRST_VSTS_ID NUMBER(30) NOT NULL
   ,VRST_FROMDATE DATE NOT NULL
   ,VRST_TODATE DATE NOT NULL
   ,VRST_STARTTIME DATE
   ,VRST_ENDTIME DATE
   ,VRST_FROM_VLOC_ID NUMBER(30)
   ,VRST_TO_VLOC_ID NUMBER(30)
   ,VRST_WEEKLYSCHEDULE CHAR(14) DEFAULT '11111111111111' NOT NULL
   ,VRST_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
   ,VRST_WCOL_ID NUMBER(30)
   ,CONSTRAINT VRST_PK PRIMARY KEY (VRST_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE VRST is 'Regular service timetable (transport timetable. ie: flights)';

CREATE TABLE VSSV (
    VSSV_ID NUMBER(30) NOT NULL
   ,VSSV_SHORTDESC VARCHAR2(10)
   ,VSSV_LONGDESC VARCHAR2(50)
   ,VSSV_VSTG_ID NUMBER(30) NOT NULL
   ,VSSV_VSTS_ID NUMBER(30)
   ,VSSV_VRST_ID NUMBER(30)
   ,VSSV_FROM_VLOC_ID NUMBER(30)
   ,VSSV_TO_VLOC_ID NUMBER(30)
   ,VSSV_FROM DATE NOT NULL
   ,VSSV_TO DATE
   ,VSSV_FROMTIME DATE
   ,VSSV_TOTIME DATE
   ,VSSV_ORIGINALFROMTIME DATE
   ,VSSV_ORIGINALTOTIME DATE
   ,VSSV_DURATIONMINUTES NUMBER(4)
   ,VSSV_WCOL_ID NUMBER(30)
   ,VSSV_VALIDATIONTIME DATE
   ,VSSV_ORGANISEDTIME DATE
   ,VSSV_STATUS NUMBER(1) DEFAULT 0 NOT NULL
   ,VSSV_CLOSEDFORINDEPENDENT DATE
   ,CONSTRAINT VSSV_PK PRIMARY KEY (VSSV_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;   
   
CREATE SEQUENCE SEQ_VSSV INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE VSSV is 'Scheduled service (ie: taxi or flight)';

COMMENT ON COLUMN VSSV.VSSV_VSTG_ID is 'for drafts without VSTS id';

COMMENT ON COLUMN VSSV.VSSV_VALIDATIONTIME is '1=Resquest is valiDATEd for availability 0=Request/promise only';

COMMENT ON COLUMN VSSV.VSSV_ORGANISEDTIME is '1=Service is organised and booked to happen';

COMMENT ON COLUMN VSSV.VSSV_STATUS is '0=active 1=cancelled';

COMMENT ON COLUMN VSSV.VSSV_CLOSEDFORINDEPENDENT is '1=closed for independent booking. Allocation only';

CREATE TABLE VSAH (
    VSAH_ID NUMBER(30) NOT NULL
   ,VSAH_SHORTDESC VARCHAR2(10)
   ,VSAH_LONGDESC VARCHAR2(60)
   ,VSAH_STATUS NUMBER(1) DEFAULT 0 NOT NULL
   ,VSAH_FROMDATE DATE
   ,VSAH_TODATE DATE
   ,VSAH_CUTOFFDATE DATE
   ,VSAH_CUTOFFDAYS NUMBER(3)
   ,VSAH_PRICE NUMBER(30,4)
   ,VSAH_COST NUMBER(30,4)
   ,VSAH_TAX NUMBER(30,4)
   ,VSAH_PRICE_MANUAL NUMBER(30,4)
   ,VSAH_COST_MANUAL NUMBER(30,4)
   ,VSAH_TAX_MANUAL NUMBER(30,4)
   ,VSAH_PRICE_FOREIGN NUMBER(30,4)
   ,VSAH_COST_FOREIGN NUMBER(30,4)
   ,VSAH_TAX_FOREIGN NUMBER(30,4)
   ,VSAH_PRICE_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAH_COST_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAH_TAX_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAH_PRICE_ZDCO_ID NUMBER(30)
   ,VSAH_COST_ZDCO_ID NUMBER(30)
   ,VSAH_TAX_ZDCO_ID NUMBER(30)
   ,VSAH_PRICE_ZCUR_ID NUMBER(30)
   ,VSAH_COST_ZCUR_ID NUMBER(30)
   ,VSAH_TAX_ZCUR_ID NUMBER(30)
   ,VSAH_PRICE_EXCHRATE NUMBER(38,18)
   ,VSAH_COST_EXCHRATE NUMBER(38,18)
   ,VSAH_TAX_EXCHRATE NUMBER(38,18)
   ,VSAH_PRICE_REVERSEEXCHRATE NUMBER(1)
   ,VSAH_COST_REVERSEEXCHRATE NUMBER(1)
   ,VSAH_TAX_REVERSEEXCHRATE NUMBER(1)
   ,VSAH_PRICE_FINALAMOUNT NUMBER(30,4)
   ,VSAH_PRICE_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAH_COST_FINALAMOUNT NUMBER(30,4)
   ,VSAH_COST_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAH_TAX_FINALAMOUNT NUMBER(30,4)
   ,VSAH_TAX_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAH_PRICE_PERCENTAGE NUMBER(1) DEFAULT 0 NOT NULL
   ,VSAH_PRICE_PERCENTAGEVALUE NUMBER(30,4)
,CONSTRAINT VSAH_PK PRIMARY KEY (VSAH_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE VSAH is 'Service Allocation header';

COMMENT ON COLUMN VSAH.VSAH_STATUS is '0=definive 1=tentative 2=cancelled';

CREATE TABLE VSAD (
    VSAD_ID NUMBER(30) NOT NULL
   ,VSAD_VSAH_ID NUMBER(30) NOT NULL
   ,VSAD_VSSV_ID NUMBER(30)
   ,VSAD_VSCL_ID NUMBER(30)
   ,VSAD_NOOFPERSONS NUMBER(30) NOT NULL
   ,VSAD_PRICE NUMBER(30,4)
   ,VSAD_COST NUMBER(30,4)
   ,VSAD_TAX NUMBER(30,4)
   ,VSAD_PRICE_MANUAL NUMBER(30,4)
   ,VSAD_COST_MANUAL NUMBER(30,4)
   ,VSAD_TAX_MANUAL NUMBER(30,4)
   ,VSAD_PRICE_FOREIGN NUMBER(30,4)
   ,VSAD_COST_FOREIGN NUMBER(30,4)
   ,VSAD_TAX_FOREIGN NUMBER(30,4)
   ,VSAD_PRICE_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAD_COST_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAD_TAX_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAD_PRICE_ZDCO_ID NUMBER(30)
   ,VSAD_COST_ZDCO_ID NUMBER(30)
   ,VSAD_TAX_ZDCO_ID NUMBER(30)
   ,VSAD_PRICE_ZCUR_ID NUMBER(30)
   ,VSAD_COST_ZCUR_ID NUMBER(30)
   ,VSAD_TAX_ZCUR_ID NUMBER(30)
   ,VSAD_PRICE_EXCHRATE NUMBER(38,18)
   ,VSAD_COST_EXCHRATE NUMBER(38,18)
   ,VSAD_TAX_EXCHRATE NUMBER(38,18)
   ,VSAD_PRICE_REVERSEEXCHRATE NUMBER(1)
   ,VSAD_COST_REVERSEEXCHRATE NUMBER(1)
   ,VSAD_TAX_REVERSEEXCHRATE NUMBER(1)
   ,VSAD_PRICE_FINALAMOUNT NUMBER(30,4)
   ,VSAD_PRICE_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAD_COST_FINALAMOUNT NUMBER(30,4)
   ,VSAD_COST_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAD_TAX_FINALAMOUNT NUMBER(30,4)
   ,VSAD_TAX_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAD_PRICE_PERCENTAGE NUMBER(1) DEFAULT 0 NOT NULL
   ,VSAD_PRICE_PERCENTAGEVALUE NUMBER(30,4)
   ,CONSTRAINT VSAD_PK PRIMARY KEY (VSAD_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;
     
CREATE SEQUENCE SEQ_VSAD INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;
     
COMMENT ON TABLE VSAD is 'Service allocation detail';

CREATE TABLE VBOK (
    VBOK_ID NUMBER(30) NOT NULL
   ,VBOK_XCMS_ID NUMBER(30) NOT NULL
   ,VBOK_YGCO_ID NUMBER(30)
   ,VBOK_STATUS NUMBER(1) NOT NULL
   ,VBOK_WCOL_ID NUMBER(30)
   ,VBOK_ZCUR_ID NUMBER(30)
   ,VBOK_YCDP_ID NUMBER(30)
   ,VBOK_BONUS_XMNR_ID NUMBER(30)
   ,VBOK_CONFIRMATIONNO VARCHAR2(10) NOT NULL UNIQUE
   ,VBOK_NOOFPERSONS NUMBER(4) NOT NULL
   ,VBOK_STARTDATE DATE
   ,VBOK_ENDDATE DATE
   ,VBOK_FREETEXT1 VARCHAR2(100)
   ,VBOK_FREETEXT2 VARCHAR2(100)
   ,VBOK_XCMA_ID NUMBER(30)
   ,VBOK_CHANNEL_YRAS_ID NUMBER(30)
   ,VBOK_SOURCE_YRAS_ID NUMBER(30)
   ,VBOK_READONLY DATE
   ,VBOK_OPTIONDATE DATE
   ,CONSTRAINT VBOK_PK PRIMARY KEY (VBOK_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;
 
COMMENT ON TABLE VBOK is 'Booking. Collects room reservations and service reservations';

COMMENT ON COLUMN VBOK.VBOK_STATUS is '0=tentative 1=definitive 2=offer 3=cancelled';

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

CREATE TABLE VBOP (
    VBOP_ID NUMBER(30) NOT NULL
   ,VBOP_VBOK_ID NUMBER(30) NOT NULL
   ,VBOP_XCAC_ID NUMBER(30) NOT NULL
   ,VBOP_NUMBER NUMBER(3) NOT NULL
   ,CONSTRAINT VBOP_PK PRIMARY KEY (VBOP_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VBOP is 'Service Plan - Person NUMBER';

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

CREATE TABLE VBPL (
    VBPL_ID NUMBER(30) NOT NULL
   ,VBPL_VBOK_ID NUMBER(30) NOT NULL
   ,VBPL_XCMS_ID NUMBER(30)
   ,CONSTRAINT VBPL_PK PRIMARY KEY (VBPL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VBPL is 'Booking - Profile link (guests, travel agent, booker etc)';

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

CREATE TABLE VBGL (
    VBGL_ID NUMBER(30) NOT NULL
   ,VBGL_VBOK_ID NUMBER(30) NOT NULL
   ,VBGL_XCMS_ID NUMBER(30)
   ,VBGL_XCAC_ID NUMBER(30)
   ,VBGL_XCAC_index NUMBER(2) 
   ,CONSTRAINT VBGL_PK PRIMARY KEY (VBGL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VBGL is 'Booking - Guest link';

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

CREATE TABLE VSRV (
    VSRV_ID NUMBER(30) NOT NULL
   ,VSRV_type NUMBER(1) NOT NULL 
   ,VSRV_NOOFPERSONS NUMBER(30)
   ,VSRV_VBOK_ID NUMBER(30)
   ,VSRV_NUMBER VARCHAR2(30)
   ,VSRV_INVOICEDINCENTRAL NUMBER(1) DEFAULT 1 NOT NULL
   ,VSRV_SYSTEMNAME VARCHAR2(10)
   ,VSRV_LINKID VARCHAR2(50)
   ,VSRV_START_WPHS_ID NUMBER(30)
   ,VSRV_END_WPHS_ID NUMBER(30)
   ,VSRV_INVOICE_WPHS_ID NUMBER(30)
   ,VSRV_FROMDATE DATE
   ,VSRV_TODATE DATE
   ,VSRV_FROMTIME DATE
   ,VSRV_TOTIME DATE
   ,VSRV_CONFIRMATIONNO VARCHAR2(30) UNIQUE
   ,VSRV_VSCL_ID NUMBER(30)
   ,VSRV_PRICE_ZCUR_ID NUMBER(30)
   ,VSRV_PRICE NUMBER(30,4)
   ,VSRV_PRICE_FOREIGN NUMBER(30,4)
   ,VSRV_PRICE_MANUAL NUMBER(30,4)
   ,VSRV_PRICE_FOREIGN_MANUAL NUMBER(30,4)
   ,VSRV_PRICE_EXCHRATE NUMBER(38,18)
   ,VSRV_PRICE_REVERSEEXCHRATE NUMBER(1)
   ,VSRV_PRICE_FINALAMOUNT NUMBER(30,4)
   ,VSRV_PRICE_FOREIGNFinalAmount NUMBER(30,4)
   ,VSRV_PRICE_PERCENTAGE NUMBER(1) DEFAULT 0 NOT NULL
   ,VSRV_PRICE_PERCENTAGEVALUE NUMBER(30,4)
   ,VSRV_COST_ZCUR_ID NUMBER(30)
   ,VSRV_COST NUMBER(30,4)
   ,VSRV_COST_FOREIGN NUMBER(30,4)
   ,VSRV_COST_MANUAL NUMBER(30,4)
   ,VSRV_COST_FOREIGN_MANUAL NUMBER(30,4)
   ,VSRV_COST_EXCHRATE NUMBER(38,18)
   ,VSRV_COST_REVERSEEXCHRATE NUMBER(1)
   ,VSRV_COST_FINALAMOUNT NUMBER(30,4)
   ,VSRV_COST_FOREIGNFinalAmount NUMBER(30,4)
   ,VSRV_TAX_ZCUR_ID NUMBER(30)
   ,VSRV_TAX NUMBER(30,4)
   ,VSRV_TAX_FOREIGN NUMBER(30,4)
   ,VSRV_TAX_MANUAL NUMBER(30,4)
   ,VSRV_TAX_FOREIGN_MANUAL NUMBER(30,4)
   ,VSRV_TAX_EXCHRATE NUMBER(38,18)
   ,VSRV_TAX_REVERSEEXCHRATE NUMBER(1)
   ,VSRV_TAX_FINALAMOUNT NUMBER(30,4)
   ,VSRV_TAX_FOREIGNFinalAmount NUMBER(30,4)
   ,VSRV_STATUS NUMBER(1) DEFAULT 0 NOT NULL
   ,VSRV_POSTTIME DATE
   ,VSRV_DESC VARCHAR2(200)
   ,VSRV_FROM_VLOC_ID NUMBER(30)
   ,VSRV_TO_VLOC_ID NUMBER(30)
   ,VSRV_VSAH_ID NUMBER(30)
   ,VSRV_AFFECTSAVAILABILITY NUMBER(1) NOT NULL
   ,CONSTRAINT VSRV_PK PRIMARY KEY (VSRV_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSRV is 'Service reservation (leg)';

COMMENT ON COLUMN VSRV.VSRV_type is '0=service 1=room reservation 2=third party service';

COMMENT ON COLUMN VSRV.VSRV_SYSTEMNAME is 'Third party related service. Empty=fidelio';

COMMENT ON COLUMN VSRV.VSRV_LINKID is 'Id in third party system';

COMMENT ON COLUMN VSRV.VSRV_STATUS is '0=tentative 1=definitive 2=cancelled';

COMMENT ON COLUMN VSRV.VSRV_FROM_VLOC_ID is 'Location info for non scheduled services (VSSV) ie reservations and third party services';

COMMENT ON COLUMN VSRV.VSRV_TO_VLOC_ID is 'Location info for non scheduled services (VSSV) ie reservations and third party services';

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

CREATE TABLE VSRP (
    VSRP_ID NUMBER(30) NOT NULL
   ,VSRP_VSRV_ID NUMBER(30) NOT NULL
   ,VSRP_XCAC_ID NUMBER(30) NOT NULL
   ,VSRP_NUMBER NUMBER(3) NOT NULL
   ,VSRP_VSCL_ID NUMBER(30)
   ,VSRP_VSAD_ID NUMBER(30) 
   ,CONSTRAINT VSRP_PK PRIMARY KEY (VSRP_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSRP is 'Service reservation - Person NUMBER';

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

CREATE TABLE VSRE (
    VSRE_ID NUMBER(30) NOT NULL
   ,VSRE_VSRV_ID NUMBER(30) NOT NULL
   ,VSRE_XCED_ID NUMBER(30) NOT NULL 
   ,CONSTRAINT VSRE_PK PRIMARY KEY (VSRE_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSRE is 'Service reservation - Employee link (ie: drivers)';

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

CREATE TABLE VSRG (
    VSRG_ID NUMBER(30) NOT NULL
   ,VSRG_VSRV_ID NUMBER(30) NOT NULL
   ,VSRG_ROLE NUMBER(1) NOT NULL 
   ,VSRG_XCMS_ID NUMBER(30)
   ,VSRG_XCAC_ID NUMBER(30)
   ,VSRG_XCAC_INDEX NUMBER(2) 
   ,CONSTRAINT VSRG_PK PRIMARY KEY (VSRG_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;
   
COMMENT ON TABLE VSRG is 'Service reservation - Guest link';

COMMENT ON COLUMN VSRG.VSRG_ROLE is '0=guest 1=linked for internalcategory';

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

CREATE TABLE VPOS (
    VPOS_ID NUMBER(30) NOT NULL
   ,VPOS_VSRV_ID NUMBER(30) NOT NULL
   ,VPOS_XCMS_ID NUMBER(30) NOT NULL
   ,VPOS_DATE DATE NOT NULL
   ,VPOS_POSTINGDATE DATE NOT NULL
   ,VPOS_ZDCO_ID NUMBER(30) NOT NULL
   ,VPOS_UNITPRICE NUMBER(30,4) NOT NULL
   ,VPOS_QUANTITY NUMBER(10) NOT NULL
   ,VPOS_TAXLINK_ID NUMBER(30) NOT NULL
   ,VPOS_INERNALSPLIT_ID NUMBER(30) NOT NULL
   ,VPOS_DT NUMBER(1) NOT NULL
   ,VPOS_GROSSUNITPRICE NUMBER(30) NOT NULL
   ,VPOS_TRANSFERRED NUMBER(1) NOT NULL
   ,VPOS_POSTED NUMBER(1) NOT NULL
   ,VPOS_ZPOS_ID NUMBER(30)
   ,VPOS_WPHS_ID NUMBER(30)
   ,VPOS_FOREIGNUnitPRICE NUMBER(30,4)
   ,VPOS_FOREIGNGrossUnitPRICE NUMBER(30,4)
   ,VPOS_ZCUR_ID NUMBER(30) NOT NULL
   ,VPOS_TYPE NUMBER(1) NOT NULL
   ,VPOS_YPOS_type NUMBER(1)
   ,VPOS_M_ZCUR_ID NUMBER(30)
   ,VPOS_M_UNITPRICE NUMBER(30)
   ,VPOS_M_GROSSUNITPRICE NUMBER(30) 
   ,CONSTRAINT VPOS_PK PRIMARY KEY (VPOS_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VPOS is 'Service charges (later transferred to zpos in central, or to partner, or to third party system)';

COMMENT ON COLUMN VPOS.VPOS_TRANSFERRED is '0=not transferred 1=transferred';

COMMENT ON COLUMN VPOS.VPOS_ZPOS_ID is 'In central postings only. Empty if transferred to partner. (Partner stores VPOS_ID for reference)';

COMMENT ON COLUMN VPOS.VPOS_WPHS_ID is 'Empty for central postings. Filled for items to be transferred';

COMMENT ON COLUMN VPOS.VPOS_TYPE is '0=PRICE 1=cost 2=tax';

COMMENT ON COLUMN VPOS.VPOS_YPOS_TYPE is 'Same as ypos_type. Data is copied from ypos';

COMMENT ON COLUMN VPOS.VPOS_M_ZCUR_ID is 'Mirror home currency';

COMMENT ON COLUMN VPOS.VPOS_M_UNITPRICE is 'Mirror unit PRICE in mirror home currency';

COMMENT ON COLUMN VPOS.VPOS_M_GROSSUNITPRICE is 'Mirror gross unit PRICE in mirror home currency'; 

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

CREATE TABLE VBOA (
    VBOA_ID NUMBER(30) NOT NULL
   ,VBOA_VBOK_ID NUMBER(30) NOT NULL  
   ,VBOA_XPDA_ID NUMBER(30) NOT NULL 
   ,CONSTRAINT VBOA_PK PRIMARY KEY (VBOA_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VBOA is 'Attribute link to Service Plan';

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

CREATE TABLE VSRA (
    VSRA_ID NUMBER(30) NOT NULL
   ,VSRA_VSRV_ID NUMBER(30) NOT NULL  
   ,VSRA_xpda_ID NUMBER(30) NOT NULL 
   ,CONSTRAINT VSRA_PK PRIMARY KEY (VSRA_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSRA is 'Attribute link to Service Booking';

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

CREATE TABLE VSSA (
    VSSA_ID NUMBER(30) NOT NULL
   ,VSSA_VSSV_ID NUMBER(30) NOT NULL  
   ,VSSA_XPDA_ID NUMBER(30) NOT NULL 
   ,CONSTRAINT VSSA_PK PRIMARY KEY (VSSA_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSSA is 'Attribute link to Scheduled Service';

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

CREATE TABLE VSAA (
    VSAA_ID NUMBER(30) NOT NULL
   ,VSAA_VSAH_ID NUMBER(30) NOT NULL  
   ,VSAA_XPDA_ID NUMBER(30) NOT NULL 
   ,CONSTRAINT VSAA_PK PRIMARY KEY (VSAA_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSAA is 'Attribute link to Service Allocation';

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

ALTER TABLE ZDCO ADD ZDCO_CCCOMMISSION_ZDCO_ID NUMBER(30);

ALTER TABLE ZDCO ADD ZDCO_CCCOMMISSION_PERCENT NUMBER(4, 2);

COMMENT ON COLUMN ZDCO.ZDCO_CCCOMMISSION_ZDCO_ID IS 'Department code for Credit Card Commission posting';

COMMENT ON COLUMN ZDCO.ZDCO_CCCOMMISSION_PERCENT IS 'Percentage of Credit Card Commission';

ALTER TABLE ZDCL ADD(
    ZDCL_CALCINCLUDED NUMBER(1),
    ZDCL_UDF VARCHAR2(200 char)
);

COMMENT ON COLUMN ZDCL.ZDCL_CALCINCLUDED is '1 - indicates that the City Tax has to be calculated like it was an included tax'; 

COMMENT ON COLUMN ZDCL.ZDCL_UDF is 'User defined formula for the City Tax calculation';


/************************************************************************************************************************
*
*  The following statements will upgrade from 8_9_5_4 to 8_9_5_5
*
************************************************************************************************************************/


ALTER TABLE W8HP ADD W8HP_MYHOTELSHOPTRACKINGPIXEL NUMBER(1);

ALTER TABLE W8HP ADD W8HP_MYHOTELSHOPID varchar2(20);

ALTER TABLE YHKS ADD YHKS_GREENSTATUS NUMBER( 1);

COMMENT ON COLUMN YHKS.YHKS_GREENSTATUS IS 'One record in YHKS to be identified for Green Status (not used too long and review needed)';

ALTER TABLE YHKS ADD YHKS_GREENSTATUSDAYS NUMBER( 3);

COMMENT ON COLUMN YHKS.YHKS_GREENSTATUSDAYS IS 'Number of days for Green Status (not used too long and review needed)';

ALTER TABLE MMOD ADD MMOD_INCL_ADD_TO_RATE NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_INCL_ADD_TO_RATE IS 'indicates whether add to rate packages should be uploaded within the reservation rate';

ALTER TABLE MAVL ADD MAVL_UPLOAD_RATE NUMBER(1);

ALTER TABLE MAVL ADD MAVL_UPLOAD_RAVL NUMBER(1);

COMMENT ON COLUMN MAVL.MAVL_UPLOAD_RATE IS 'indicates whether the rate code is used for rate upload';

COMMENT ON COLUMN MAVL.MAVL_UPLOAD_RAVL IS 'indicates whether the rate code is used for ravl upload';

UPDATE MAVL SET MAVL_UPLOAD_RATE = 1, MAVL_UPLOAD_RAVL = 1;

ALTER TABLE XCCS ADD XCCS_CARD_ID VARCHAR2(100 CHAR);

ALTER TABLE XCCS ADD XCCS_CARD_ID_TYPE NUMBER(1);

COMMENT ON COLUMN XCCS.XCCS_CARD_ID IS 'CC token used by Best Western';

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

ALTER TABLE MMOD ADD MMOD_ALLOW_ZERO_PRICE_PACKAGE NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_ALLOW_ZERO_PRICE_PACKAGE IS 'indicates whether an interface accepts zero rates for packages. If not the price from Suite8 will be used';

ALTER TABLE WPCA MODIFY WPCA_BARCODEREADERPORT NUMBER(2);

ALTER TABLE WPCA ADD WPCA_KIOSK_ZFST_ID NUMBER(30);

COMMENT ON COLUMN WPCA.WPCA_KIOSK_ZFST_ID IS 'Custom folio styles per Kiosk terminal';

UPDATE MMOD SET MMOD_INCL_ADD_TO_RATE = 1;

INSERT INTO WUSS(WUSS_ID, WUSS_XCMS_ID, WUSS_NAME, WUSS_VALUE)
SELECT SEQ_WUSS.NEXTVAL, 0, 'MenuCourseSeparator', '***' 
FROM DUAL
WHERE NOT EXISTS (SELECT WUSS_ID FROM WUSS WHERE WUSS_NAME='MenuCourseSeparator');

INSERT INTO WUSS(WUSS_ID, WUSS_XCMS_ID, WUSS_NAME, WUSS_VALUE)
SELECT SEQ_WUSS.NEXTVAL, 0, 'TranslationMethod', '0' 
FROM DUAL
WHERE NOT EXISTS (SELECT WUSS_ID FROM WUSS WHERE WUSS_NAME='TranslationMethod');

/************************************************************************************************************************
*
*  The following statements will upgrade from 8_9_5_5 to 8_9_5_6
*
************************************************************************************************************************/


CREATE TABLE WSPD
(
   WSPD_ID         NUMBER (30) NOT NULL,
   WSPD_DATE       DATE,
   WSPD_DOWNLOAD   VARCHAR2 (60),
   WSPD_UPLOAD     VARCHAR2 (60),
   WSPD_XCED_ID    NUMBER (30),
   CONSTRAINT WSPD_PK PRIMARY KEY (WSPD_ID) USING INDEX TABLESPACE V8INDEX
)
TABLESPACE V8MAIN;

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

COMMENT ON TABLE WSPD IS 'Table to store the results for speed test functionality in webSuite';

COMMENT ON COLUMN WSPD.WSPD_ID IS 'unique id';

COMMENT ON COLUMN WSPD.WSPD_DATE IS 'Date and time of speed test start';

COMMENT ON COLUMN WSPD.WSPD_DOWNLOAD IS 'Result of download speed';

COMMENT ON COLUMN WSPD.WSPD_UPLOAD IS 'Result of upload speed';

COMMENT ON COLUMN WSPD.WSPD_XCED_ID IS 'User ID of user who started speed test';








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

CREATE SEQUENCE SEQ_CSCO INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_CSHC INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_CSHP INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WCCS INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WECF INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WLHC INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WMWC INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WMWD INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WMWE INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WMWF INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WMWG INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WMWL INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WMWM INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WMWP INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WSIG INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WSMC INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WSML INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_WSQP INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_XPAD INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_YFOR INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE SEQ_YRDM INCREMENT BY 1 START WITH 1000 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER;


/************************************************************************************************************************
*

/************************************************************************************************************************
*
*  tables
*
*  1046 tables were found in VERSION_8_9_6@V8TEST2
*  1030 tables were found in VERSION_8_9_5@V8TEST2
*
************************************************************************************************************************/




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


DROP TABLE VERSION_8_9_5_0;


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


/*  CSCO  */
CREATE TABLE CSCO (
	 CSCO_ID	NUMBER(30)	NOT NULL
	,CSCO_XCOU_ID	NUMBER(30)	NOT NULL
	,CSCO_CSHP_ID	NUMBER(30)	NOT NULL
)
TABLESPACE V8CONF
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE CSCO ADD (
	CONSTRAINT CSCO_PK
	PRIMARY KEY
	            (
	             CSCO_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  CSHC  */
CREATE TABLE CSHC (
	 CSHC_ID	NUMBER(30)	NOT NULL
	,CSHC_CSHP_ID	NUMBER(30)	NOT NULL
	,CSHC_ZDCO_ID	NUMBER(30)	NOT NULL
	,CSHC_PRICE	NUMBER(30,4)	    NULL
)
TABLESPACE V8CONF
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE CSHC ADD (
	CONSTRAINT CSHC_PK
	PRIMARY KEY
	            (
	             CSHC_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  CSHP  */
CREATE TABLE CSHP (
	 CSHP_ID	NUMBER(30)	NOT NULL
	,CSHP_NAME	VARCHAR2(300)	NOT NULL
	,CSHP_WEBENABLED	NUMBER(1)	    NULL
	,CSHP_WEBDESCRIPTION	VARCHAR2(300)	    NULL
	,CSHP_FOLIODESCRIPTION	VARCHAR2(300)	    NULL
	,CSHP_FREEWHENTOTALMORE	NUMBER(30,4)	    NULL
	,CSHP_ROLE	NUMBER(2)	    NULL
	,CSHP_INSTANTPAYMENT	NUMBER(1)	    NULL
	,CSHP_DISPLORDER	NUMBER(4)	    NULL
)
TABLESPACE V8CONF
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE CSHP ADD (
	CONSTRAINT CSHP_PK
	PRIMARY KEY
	            (
	             CSHP_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

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


/*  WCCS  */
CREATE TABLE WCCS (
	 WCCS_ID	NUMBER(30)	NOT NULL
	,WCCS_TABLENAME	VARCHAR2(4)	NOT NULL
	,WCCS_TABLEID	NUMBER(30)	NOT NULL
	,WCCS_WPHS_ID	NUMBER(30)	NOT NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WCCS ADD (
	CONSTRAINT WCCS_PK
	PRIMARY KEY
	            (
	             WCCS_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;
ALTER TABLE WCCS ADD (
	CONSTRAINT WCCS_UNIQUE
	UNIQUE
	       (
	        WCCS_TABLENAME
	       ,WCCS_TABLEID
	       ,WCCS_WPHS_ID
	       )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WECF  */
CREATE TABLE WECF (
	 WECF_ID	NUMBER(30)	NOT NULL
	,WECF_AREA	NUMBER(3)	NOT NULL
	,WECF_CC	VARCHAR2(60)	    NULL
	,WECF_BCC	VARCHAR2(60)	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WECF ADD (
	CONSTRAINT WECF_PK
	PRIMARY KEY
	            (
	             WECF_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WLHC  */
CREATE TABLE WLHC (
	 WLHC_ID	NUMBER(30)	NOT NULL
	,WLHC_DATE	DATE	NOT NULL
	,WLHC_YRES_ID	NUMBER(30)	NOT NULL
	,WLHC_REVIEW	VARCHAR2(4000)	NOT NULL
	,WLHC_REPLY	VARCHAR2(2000)	    NULL
	,WLHC_SUCCESS	NUMBER(1)	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WLHC ADD (
	CONSTRAINT WLHC_PK
	PRIMARY KEY
	            (
	             WLHC_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WMWC  */
CREATE TABLE WMWC (
	 WMWC_ID	NUMBER(30)	NOT NULL
	,WMWC_SHORTDESC	VARCHAR2(3)	    NULL
	,WMWC_LONGDESC	VARCHAR2(100)	    NULL
	,WMWC_WSQG_ID	NUMBER(30)	    NULL
	,WMWC_WSQL_ID	NUMBER(30)	    NULL
	,WMWC_CREATED_XCED_ID	NUMBER(30)	    NULL
	,WMWC_TIME_CREATED	DATE	    NULL
	,WMWC_FIXED	NUMBER(1)	    NULL
	,WMWC_DISPLORDER	NUMBER(4)	    NULL
	,WMWC_REMARKS	VARCHAR2(2000)	    NULL
	,WMWC_TECHNICAL_INFO	VARCHAR2(2000)	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WMWC ADD (
	CONSTRAINT WMWC_PK
	PRIMARY KEY
	            (
	             WMWC_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WMWD  */
CREATE TABLE WMWD (
	 WMWD_ID	NUMBER(30)	NOT NULL
	,WMWD_SHORTDESC	VARCHAR2(3)	    NULL
	,WMWD_LONGDESC	VARCHAR2(60)	    NULL
	,WMWD_WMWM_ID	NUMBER(30)	NOT NULL
	,WMWD_SQL_SETUP	LONG	    NULL
	,WMWD_DISPLORDER	NUMBER(4)	    NULL
	,WMWD_REMARKS	VARCHAR2(2000)	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WMWD ADD (
	CONSTRAINT WMWD_PK
	PRIMARY KEY
	            (
	             WMWD_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WMWE  */
CREATE TABLE WMWE (
	 WMWE_ID	NUMBER(30)	NOT NULL
	,WMWE_SHORTDESC	VARCHAR2(3)	    NULL
	,WMWE_LONGDESC	VARCHAR2(100)	    NULL
	,WMWE_WMWC_ID	NUMBER(30)	    NULL
	,WMWE_TIME	DATE	    NULL
	,WMWE_XCED_ID	NUMBER(30)	NOT NULL
	,WMWE_WSQG_ID	NUMBER(30)	    NULL
	,WMWE_WSQL_ID	NUMBER(30)	    NULL
	,WMWE_RESULTCOUNT	NUMBER(10)	    NULL
	,WMWE_RESULTXCMS	LONG	    NULL
	,WMWE_REMARKS	VARCHAR2(2000)	    NULL
	,WMWE_TECHNICAL_INFO	VARCHAR2(2000)	    NULL
	,WMWE_WACT_TYPE	NUMBER(1)	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WMWE ADD (
	CONSTRAINT WMWE_PK
	PRIMARY KEY
	            (
	             WMWE_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WMWF  */
CREATE TABLE WMWF (
	 WMWF_ID	NUMBER(30)	NOT NULL
	,WMWF_SHORTDESC	VARCHAR2(3)	    NULL
	,WMWF_LONGDESC	VARCHAR2(100)	    NULL
	,WMWF_TYPE	NUMBER(1)	    NULL
	,WMWF_TABLE_NAME	VARCHAR2(4)	    NULL
	,WMWF_TABLE_LINK_FIELD	VARCHAR2(30)	    NULL
	,WMWF_DISPLORDER	NUMBER(4)	    NULL
	,WMWF_REMARKS	VARCHAR2(2000)	    NULL
	,WMWF_SQL_SETUP	LONG	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WMWF ADD (
	CONSTRAINT WMWF_PK
	PRIMARY KEY
	            (
	             WMWF_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WMWG  */
CREATE TABLE WMWG (
	 WMWG_ID	NUMBER(30)	NOT NULL
	,WMWG_SHORTDESC	VARCHAR2(3)	    NULL
	,WMWG_LONGDESC	VARCHAR2(100)	    NULL
	,WMWG_DISPLORDER	NUMBER(4)	    NULL
	,WMWG_REMARKS	VARCHAR2(2000)	    NULL
	,WMWG_SQL_SETUP	LONG	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WMWG ADD (
	CONSTRAINT WMWG_PK
	PRIMARY KEY
	            (
	             WMWG_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WMWL  */
CREATE TABLE WMWL (
	 WMWL_ID	NUMBER(30)	NOT NULL
	,WMWL_WMWD_ID	NUMBER(30)	    NULL
	,WMWL_WMWD_EXCL	NUMBER(1)	    NULL
	,WMWL_WMWF_ID	NUMBER(30)	    NULL
	,WMWL_WMWG_ID	NUMBER(30)	    NULL
	,WMWL_WSQL_ID	NUMBER(30)	    NULL
	,WMWL_TIMESTAMP	DATE	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WMWL ADD (
	CONSTRAINT WMWL_PK
	PRIMARY KEY
	            (
	             WMWL_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WMWM  */
CREATE TABLE WMWM (
	 WMWM_ID	NUMBER(30)	NOT NULL
	,WMWM_SHORTDESC	VARCHAR2(3)	    NULL
	,WMWM_LONGDESC	VARCHAR2(60)	    NULL
	,WMWM_DISPLORDER	NUMBER(4)	    NULL
	,WMWM_REMARKS	VARCHAR2(2000)	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WMWM ADD (
	CONSTRAINT WMWM_PK
	PRIMARY KEY
	            (
	             WMWM_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WMWP  */
CREATE TABLE WMWP (
	 WMWP_ID	NUMBER(30)	NOT NULL
	,WMWP_TYP	NUMBER(1)	    NULL
	,WMWP_WMWD_ID	NUMBER(30)	NOT NULL
	,WMWP_MULTIPLE	NUMBER(1)	    NULL
	,WMWP_TABLE_NAME	VARCHAR2(4)	    NULL
	,WMWP_TABLE_FIELD	VARCHAR2(30)	    NULL
	,WMWP_PARAMETERNAME	VARCHAR2(30)	    NULL
	,WMWP_INTERNAL_SQL	VARCHAR2(2000)	    NULL
	,WMWP_FIELD1	VARCHAR2(30)	    NULL
	,WMWP_FIELD2	VARCHAR2(30)	    NULL
	,WMWP_FIELD3	VARCHAR2(30)	    NULL
	,WMWP_DISPLORDER	NUMBER(4)	    NULL
	,WMWP_REMARKS	VARCHAR2(2000)	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WMWP ADD (
	CONSTRAINT WMWP_PK
	PRIMARY KEY
	            (
	             WMWP_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WSIG  */
CREATE TABLE WSIG (
	 WSIG_ID	NUMBER(30)	NOT NULL
	,WSIG_YRES_ID	NUMBER(30)	    NULL
	,WSIG_XCMS_ID	NUMBER(30)	    NULL
	,WSIG_DOCUMENT	LONG RAW	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WSIG ADD (
	CONSTRAINT WSIG_PK
	PRIMARY KEY
	            (
	             WSIG_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WSMC  */
CREATE TABLE WSMC (
	 WSMC_ID	NUMBER(30)	NOT NULL
	,WSMC_WPCA_ID	NUMBER(30)	NOT NULL
	,WSMC_TIMESTAMP	DATE	NOT NULL
	,WSMC_CONFIGXML	CLOB	NOT NULL
)
TABLESPACE V8CONF
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WSMC ADD (
	CONSTRAINT WSMC_PK
	PRIMARY KEY
	            (
	             WSMC_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WSML  */
CREATE TABLE WSML (
	 WSML_ID	NUMBER(30)	NOT NULL
	,WSML_WPCA_ID	NUMBER(30)	    NULL
	,WSML_TERMINALNAME	VARCHAR2(60)	    NULL
	,WSML_TIMESTAMP	DATE	NOT NULL
	,WSML_TEXT	CLOB	NOT NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE WSML ADD (
	CONSTRAINT WSML_PK
	PRIMARY KEY
	            (
	             WSML_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  XPAD  */
CREATE TABLE XPAD (
	 XPAD_ID	NUMBER(30)	NOT NULL
	,XPAD_TYPE	NUMBER(3)	NOT NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE XPAD ADD (
	CONSTRAINT XPAD_PK
	PRIMARY KEY
	            (
	             XPAD_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YFOR  */
CREATE TABLE YFOR (
	 YFOR_ID	NUMBER(30)	NOT NULL
	,YFOR_SHORTDESC	VARCHAR2(10)	NOT NULL
	,YFOR_LONGDESC	VARCHAR2(30)	NOT NULL
	,YFOR_FORMULA	VARCHAR2(2000)	NOT NULL
	,YFOR_INACTIVE	NUMBER(1) DEFAULT 0
   	    NULL
	,YFOR_DISPLORDER	VARCHAR2(3)	    NULL
)
TABLESPACE V8CONF
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE YFOR ADD (
	CONSTRAINT YFOR_PK
	PRIMARY KEY
	            (
	             YFOR_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YRDM  */
CREATE TABLE YRDM (
	 YRDM_ID	NUMBER(30)	NOT NULL
	,YRDM_YRDB_ID	NUMBER(30)	NOT NULL
	,YRDM_YCAD_ID	NUMBER(30)	NOT NULL
)
TABLESPACE V8CONF
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE YRDM ADD (
	CONSTRAINT YRDM_PK
	PRIMARY KEY
	            (
	             YRDM_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  ZURL  */
CREATE TABLE ZURL (
	 ZURL_ID	NUMBER(30)	NOT NULL
	,ZURL_ZOTA_ID	NUMBER(30)	NOT NULL
	,ZURL_MSG_NAME	VARCHAR2(100)	    NULL
	,ZURL_URL	VARCHAR2(150)	    NULL
	,ZURL_INACTIVE	NUMBER(1)	    NULL
	,ZURL_MSG_TYPE	NUMBER(1)	    NULL
)
TABLESPACE V8MAIN
    STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS 2147483645
	PCTINCREASE 50 
	FREELISTS 1 FREELIST GROUPS 1
        )
;

ALTER TABLE ZURL ADD (
	CONSTRAINT ZURL_PK
	PRIMARY KEY
	            (
	             ZURL_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;



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


ALTER TABLE CORD ADD ( CORD_VOID_ZFAC_ID NUMBER(30) NULL  );

ALTER TABLE CORD ADD ( CORD_CSHP_ID NUMBER(30) NULL  );

ALTER TABLE CORD ADD ( CORD_SENDBYMAIL NUMBER(1) NULL  );

ALTER TABLE CORD ADD ( CORD_SHIP_MULTIPLY NUMBER(1) NULL  );

ALTER TABLE COUP ADD ( COUP_CSHP_ID NUMBER(30) NULL  );

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

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

ALTER TABLE MMOD ADD ( MMOD_FTP_BACKUPPATH VARCHAR2(100) NULL  );

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

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

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

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

ALTER TABLE MMOD ADD ( MMOD_FTP_CERTIFICATE VARCHAR2(100) NULL  );

ALTER TABLE MMOD ADD ( MMOD_FTP_CERT_PWD VARCHAR2(1024) NULL  );

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

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

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

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

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

ALTER TABLE SMAN ADD ( SMAN_EARLY_DEP_ROOMS_D NUMBER(5) NULL  );

ALTER TABLE SMAN ADD ( SMAN_EARLY_DEP_ROOMS_M NUMBER(5) NULL  );

ALTER TABLE SMAN ADD ( SMAN_EARLY_DEP_ROOMS_Y NUMBER(7) NULL  );

ALTER TABLE SMAN ADD ( SMAN_EARLY_DEP_ADULTS_D NUMBER(5) NULL  );

ALTER TABLE SMAN ADD ( SMAN_EARLY_DEP_ADULTS_M NUMBER(5) NULL  );

ALTER TABLE SMAN ADD ( SMAN_EARLY_DEP_ADULTS_Y NUMBER(7) NULL  );

ALTER TABLE SMAN ADD ( SMAN_EARLY_DEP_CHILDS_D NUMBER(5) NULL  );

ALTER TABLE SMAN ADD ( SMAN_EARLY_DEP_CHILDS_M NUMBER(5) NULL  );

ALTER TABLE SMAN ADD ( SMAN_EARLY_DEP_CHILDS_Y NUMBER(7) NULL  );

ALTER TABLE WACT ADD ( WACT_YDEP_ID NUMBER(30) NULL  );

ALTER TABLE WATG ADD ( WATG_ZFAC NUMBER(1) NULL  );

ALTER TABLE WFPP ADD ( WFPP_PICTURE_C_CLOB CLOB NULL  );

ALTER TABLE WPCA ADD ( WPCA_IS_S8_KIOSK NUMBER(1) NULL  );

ALTER TABLE WPCA ADD ( WPCA_KIOSK_ZFST_ID NUMBER(30) NULL  );

ALTER TABLE WPHS MODIFY ( WPHS_VPDID VARCHAR2(30) );

ALTER TABLE WPPP MODIFY ( WPPP_LONGDESC VARCHAR2(60) );

ALTER TABLE WQNR ADD ( WQNR_HOLIDAYCHECK NUMBER(1) NULL  );

ALTER TABLE WQUE MODIFY ( WQUE_QUESTIONTEXT VARCHAR2(2000) );

ALTER TABLE WQUE MODIFY ( WQUE_DESCRIPTION VARCHAR2(2000) );

ALTER TABLE WRPB ADD ( WRPB_NONBINARYREPORTID NUMBER(30) NULL  );

ALTER TABLE WSPS MODIFY ( WSPS_VPDID VARCHAR2(30) );

ALTER TABLE WSQP ADD ( WSQP_INCLUDEPARAM NUMBER(1) NULL  );

ALTER TABLE WSQP ADD ( WSQP_MULTISELECT NUMBER(1) DEFAULT 1 NOT NULL  );

ALTER TABLE XCED ADD ( XCED_WAITER_ID CHAR(12) NULL  );

ALTER TABLE XCMG MODIFY ( XCMG_GLOBALID VARCHAR2(100) );

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

ALTER TABLE XCNC ADD ( XCNC_POPUP_TABLERESERVATION NUMBER(1) NULL  );

ALTER TABLE XDLT ADD ( XDLT_XPAD_ID NUMBER(30) NULL  );

ALTER TABLE XMTY ADD ( XMTY_CENTRAL NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE YBOS ADD ( YBOS_GROSSUNITPRICE NUMBER(30,4) NULL  );

ALTER TABLE YBSS ADD ( YBSS_GROSSUNITPRICE NUMBER(30,4) NULL  );

ALTER TABLE YCEV ADD ( YCEV_REQUESTCC NUMBER(1) NULL  );

ALTER TABLE YFBK MODIFY ( YFBK_ACCESSTOKEN VARCHAR2(400) );

ALTER TABLE YGRP ADD ( YGRP_FIXEXCHRATE NUMBER(1) NULL  );

ALTER TABLE YHKS ADD ( YHKS_GREENSTATUS NUMBER(1) NULL  );

ALTER TABLE YHKS ADD ( YHKS_GREENSTATUSDAYS NUMBER(1) NULL  );

ALTER TABLE YRCO ADD ( YRCO_TYPE NUMBER(1) NULL  );

ALTER TABLE YRDC ADD ( YRDC_YCAD_ID NUMBER(30) DEFAULT 0 NOT NULL  );

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

ALTER TABLE ZALL ADD ( ZALL_PROFORMA NUMBER(1) DEFAULT 0 NULL  );

ALTER TABLE ZARR ADD ( ZARR_CENTRAL NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE ZDCG ADD ( ZDCG_CENTRAL NUMBER(1) DEFAULT 0 NOT NULL  );

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

ALTER TABLE ZDMG ADD ( ZDMG_CENTRAL NUMBER(1) DEFAULT 0 NOT NULL  );

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

ALTER TABLE WFMC ADD WFMC_ANCESTOR_VERSIONID VARCHAR2(30);

CREATE TABLE YCWI (
    YCWI_ID NUMBER(30) NOT NULL
   ,YCWI_WEBENABLED NUMBER(1)
   ,YCWI_SHORTWEBINFO VARCHAR2(2000)
   ,YCWI_LONGWEBINFO VARCHAR2(2000)
   ,CONSTRAINT YCWI_PK PRIMARY KEY (YCWI_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;
   
CREATE SEQUENCE SEQ_YCWI INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE YCWI is 'Web info';

COMMENT ON COLUMN YCWI.YCWI_WEBENABLED IS '0 - not enabled for web; 1 - enabled for web';

COMMENT ON COLUMN YCWI.YCWI_SHORTWEBINFO IS 'Short web info';

COMMENT ON COLUMN YCWI.YCWI_LONGWEBINFO IS 'Long web info';

ALTER TABLE YFSC ADD YFSC_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YFSC.YFSC_YCWI_ID IS 'Extends function space definition with a config for web';

ALTER TABLE YSSL ADD YSSL_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YSSL.YSSL_YCWI_ID IS 'Extends function space seating with a config for web';

ALTER TABLE YFSR ADD YFSR_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YFSR.YFSR_YCWI_ID IS 'Extends function space rate with a config for web';

ALTER TABLE YCIT ADD YCIT_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YCIT.YCIT_YCWI_ID IS 'Extends Catering item definition with a config for web';

ALTER TABLE YCIR ADD YCIR_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YCIR.YCIR_YCWI_ID IS 'Extends Catering item rate with a config for web';

ALTER TABLE YSME ADD YSME_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YSME.YSME_YCWI_ID IS 'Extends Standard menue with a config for web';

ALTER TABLE YSMR ADD YSMR_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YSMR.YSMR_YCWI_ID IS 'Extends Standard menue rates with a config for web';

ALTER TABLE YCMI ADD YCMI_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YCMI.YCMI_YCWI_ID IS 'Extends Menu item with a config for web';

ALTER TABLE YCMR ADD YCMR_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YCMR.YCMR_YCWI_ID IS 'Extends Menu item rates with a config for web';

CREATE TABLE YWRA (
    YWRA_ID NUMBER(30) NOT NULL
   ,YWRA_YFSC_ID NUMBER(30) NOT NULL
   ,YWRA_YETY_ID NUMBER(30) NOT NULL
   ,CONSTRAINT YWRA_PK PRIMARY KEY (YWRA_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE YWRA IS 'Web restriction Event type - Function Spaces';

COMMENT ON COLUMN YWRA.YWRA_YFSC_ID IS 'Function Space restricted';

COMMENT ON COLUMN YWRA.YWRA_YETY_ID IS 'Event type ID';

CREATE TABLE YWRB (
    YWRB_ID NUMBER(30) NOT NULL
   ,YWRB_YFSC_ID NUMBER(30) NOT NULL
   ,YWRB_YSSC_ID NUMBER(30) NOT NULL
   ,CONSTRAINT YWRB_PK PRIMARY KEY (YWRB_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE YWRB IS 'Web restriction Seattings - Function Spaces';

COMMENT ON COLUMN YWRB.YWRB_YFSC_ID IS 'Function Space restricted';

COMMENT ON COLUMN YWRB.YWRB_YSSC_ID IS 'Seating ID';

CREATE TABLE YWRC (
    YWRC_ID NUMBER(30) NOT NULL
   ,YWRC_YFSC_ID NUMBER(30) NOT NULL
   ,YWRC_YCAD_ID NUMBER(30) NOT NULL
   ,CONSTRAINT YWRC_PK PRIMARY KEY (YWRC_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE YWRC IS 'Web restriction Hotel Segment - Function Spaces';

COMMENT ON COLUMN YWRC.YWRC_YFSC_ID IS 'Function Space restricted';

COMMENT ON COLUMN YWRC.YWRC_YCAD_ID IS 'Hotel Segment ID';

CREATE TABLE YWRD (
    YWRD_ID NUMBER(30) NOT NULL
   ,YWRD_YFSC_ID NUMBER(30) NOT NULL
   ,YWRD_YPKG_ID NUMBER(30) NOT NULL
   ,CONSTRAINT YWRD_PK PRIMARY KEY (YWRD_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE YWRD IS 'Web restriction Packages - Function Spaces';

COMMENT ON COLUMN YWRD.YWRD_YFSC_ID IS 'Function Space restricted';

COMMENT ON COLUMN YWRD.YWRD_YPKG_ID IS 'Package ID';

ALTER TABLE YPKG ADD YPKG_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YPKG.YPKG_YCWI_ID IS 'Linked to web config';

ALTER TABLE YETY ADD YETY_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YETY.YETY_YCWI_ID IS 'Linked to web config';

ALTER TABLE MDEF MODIFY MDEF_TYPE VARCHAR2(3);

ALTER TABLE YFTY ADD YFTY_YCWI_ID NUMBER(30);

COMMENT ON COLUMN YFTY.YFTY_YCWI_ID IS 'Linked to web info';



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


/*  WSQP  */

CREATE INDEX WSQP_DISPLORDER ON WSQP
	(
	  WSQP_DISPLORDER
	)
	TABLESPACE V8INDEX
;
CREATE INDEX WSQP_WSQL_ID ON WSQP
	(
	  WSQP_WSQL_ID
	)
	TABLESPACE V8INDEX
;



/*  XCMG  */
CREATE INDEX XCMG_XCMS_ID ON XCMG
	(
	  XCMG_XCMS_ID
	)
	TABLESPACE V8INDEX
;



/*  YRAV  */

CREATE INDEX YRAV_MAXVALUE ON YRAV
	(
	  YRAV_MAXVALUE
	)
	TABLESPACE V8INDEX
;
CREATE INDEX YRAV_TYPE ON YRAV
	(
	  YRAV_TYPE
	)
	TABLESPACE V8INDEX
;
CREATE INDEX YRAV_VALUE ON YRAV
	(
	  YRAV_VALUE
	)
	TABLESPACE V8INDEX
;


/*  YRPI  */

CREATE INDEX YRPI_ENDDATE ON YRPI
	(
	  YRPI_ENDDATE
	)
	TABLESPACE V8INDEX
;
CREATE INDEX YRPI_STARTDATE ON YRPI
	(
	  YRPI_STARTDATE
	)
	TABLESPACE V8INDEX
;





/************************************************************************************************************************
*
*  The following statements will upgrade constraints for table WSQP
*
************************************************************************************************************************/


ALTER TABLE WSQP ADD (
	CONSTRAINT WSQP_PK
	PRIMARY KEY
	            (
	             WSQP_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;



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

INSERT INTO YCRN (YCRN_ID,YCRN_YRES_ID,YCRN_CRSSYSTEM,YCRN_CRSRESNR) SELECT SEQ_YCRN.NEXTVAL,YRES_ID,YRES_CRSSYSTEM,YRES_CRSRESNR FROM YRES WHERE YRES_CRSSYSTEM IS NOT NULL AND NOT EXISTS (SELECT 1 FROM YCRN WHERE YCRN_CRSSYSTEM=YRES_CRSSYSTEM AND YCRN_YRES_ID=YRES_ID);

insert into wusr 
select seq_wusr.nextval,wusr_xcms_id,wusr_wegr_id,'ConfigurationGlobal settings',wusr_value 
from wusr w 
where wusr_name='ConfigurationMiscellaneous' 
and not exists (
   select 1 
   from wusr ww 
   where (ww.wusr_xcms_id=w.wusr_xcms_id or ww.wusr_wegr_id=w.wusr_wegr_id)
   and ww.wusr_name='ConfigurationGlobal settings'
   );  

commit;



--- End of DBDiff for Oracle --- Elapsed Time: 00:29:16 ---




