--UPDATE FROM 8.9.5.1 TO 8.9.5.2


DROP TABLE VERSION_8_9_5_1;



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

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