--UPDATE FROM 8.9.4.0 TO 8.9.5.0


DROP TABLE VERSION_8_9_4_0;


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

ALTER TABLE MMOD ADD MMOD_USE_SELL_LIMIT NUMBER (1);

COMMENT ON COLUMN MMOD.MMOD_USE_SELL_LIMIT IS 'Indicates whether the mapper uses sell limts instaed of standard inventory handling';

ALTER TABLE want ADD WANT_PREVIOUS_ID  number(30);

COMMENT ON COLUMN WANT.WANT_PREVIOUS_ID IS 'Previous  Want_id ';

ALTER TABLE want ADD WANT_CREATEDON   DATE;

COMMENT ON COLUMN WANT.WANT_CREATEDON IS 'Record insert datetime ';

ALTER TABLE want ADD WANT_CREATEDBY_XCED_ID  number(30);

COMMENT ON COLUMN WANT.WANT_CREATEDBY_XCED_ID IS 'Employee Id Who inserted record ';

CREATE TABLE MYRES (
    MYRES_ID NUMBER(30,0) NOT NULL
   ,MYRES_MIRROR_YRES_ID NUMBER(30,0) NOT NULL
   ,MYRES_WPHS_ID NUMBER(30,0) NOT NULL
   ,MYRES_XCMS_GLOBALID NUMBER(30,0) 
   ,MYRES_XCMS_GUESTNAME VARCHAR2(100) NOT NULL
   ,MYRES_EXPARRTIME DATE NOT NULL
   ,MYRES_EXPDEPTIME DATE NOT NULL
   ,MYRES_ACTARRTIME DATE
   ,MYRES_ACTDEPTIME DATE
   ,MYRES_RESSTATUS NUMBER(1) NOT NULL
   ,MYRES_NOAVAILREASON NUMBER(1) NOT NULL
   ,CONSTRAINT MYRES_PK PRIMARY KEY (MYRES_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

CREATE UNIQUE INDEX MYRES_UNIQUE ON MYRES (MYRES_MIRROR_YRES_ID,MYRES_WPHS_ID) TABLESPACE V8INDEX; 

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

ALTER TABLE YRCO ADD YRCO_REPORTNAME VARCHAR2(60);

COMMENT ON COLUMN YRCO.YRCO_REPORTNAME IS 'CRS does not sync reports. This stores report name and gets synchronized';

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

ALTER TABLE ZFAC ADD ZFAC_NOBONUSPOINT NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE ZOTA ADD ZOTA_USE_RR_FROM_RC NUMBER(1) NULL;

COMMENT ON COLUMN ZOTA.ZOTA_USE_RR_FROM_RC IS 'Indicates whether we upload minlos/maxlos from rate code if ravl indicates open';

ALTER TABLE MMOD ADD MMOD_USE_RR_FROM_RC NUMBER(1) NULL;

COMMENT ON COLUMN mmod.mmod_USE_RR_FROM_RC IS 'Indicates whether we upload minlos/maxlos from rate code if ravl indicates open';

UPDATE YRCH SET YRCH_INACTIVE=0 WHERE YRCH_INACTIVE IS NULL; 

ALTER TABLE YRDT MODIFY YRDT_SHORTDESC VARCHAR2(6);

ALTER TABLE YRDT ADD YRDT_ROLE NUMBER (1);

COMMENT ON COLUMN YRDT.YRDT_ROLE IS 'null, 0 = Normal Daytype;  1=BAR Daytype';

ALTER TABLE YRDT ADD YRDT_BARCODE VARCHAR2 (30);

COMMENT ON COLUMN YRDT.YRDT_BARCODE IS 'BAR Interface Bar Code Linkage';

ALTER TABLE WLOG ADD WLOG_WPHS_ID NUMBER(30);

COMMENT ON COLUMN WLOG.WLOG_WPHS_ID IS 'In central db this shows which hotel made changes using xml interface';

ALTER TABLE YRCF ADD YRCF_BOOKINGDATE DATE;

ALTER TABLE WPSS ADD WPSS_COMPANYRULES NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN WPSS.WPSS_COMPANYRULES IS '0=All profiles use the same rule set. 1=Companies use separate ruleset';

ALTER TABLE WPSD ADD WPSD_COMPANY NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE WPSF ADD WPSF_COMPANY NUMBER(1) DEFAULT 0 NOT NULL;

CREATE TABLE YCRG (
   YCRG_ID NUMBER(30) NOT NULL
  ,YCRG_CRSID VARCHAR2(60) NOT NULL
  ,YCRG_GLOBALID NUMBER(30) NOT NULL
  ,YCRG_TIMESTAMP DATE NOT NULL
  ,YCRG_LASTTRY DATE
  ,CONSTRAINT YCRG_PK PRIMARY KEY (YCRG_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;
  
COMMENT ON TABLE YCRG IS 'CRS needs to transfer xcms globalids listed here';

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

ALTER TABLE XCID MODIFY XCID_FAM_NAME VARCHAR2(100);

COMMENT ON COLUMN ZINN.ZINN_YEARLY IS '1 - it will be reset every year on the month/day set in the resetdate field;  0 - will be reset once, only on the date marked in resetdate; 2 - it will be reset every month on the day set in the resetdate field';

ALTER TABLE ZINS ADD ZINS_MANUALNUMBER NUMBER(1) DEFAULT 0;

UPDATE YRCH SET YRCH_AVAIL_YRCH_ID = NULL WHERE YRCH_AVAIL_YRCH_ID = 0;

ALTER TABLE XMNR ADD XMNR_TAX_OFFICE VARCHAR2(30);

COMMENT ON COLUMN XMNR.XMNR_TAX_OFFICE IS 'Tax Office';

ALTER TABLE XCID MODIFY XCID_BIRTHPLACE VARCHAR2(50);

ALTER TABLE XPDC MODIFY XPDC_BIRTHPLACE VARCHAR2(50);

ALTER TABLE W8HP ADD W8HP_DEPOSIT_ZDCO_ID NUMBER(30);

CREATE TABLE WSAL (
    WSAL_ID NUMBER(30) NOT NULL
   ,WSAL_APP VARCHAR2(30) NOT NULL
   ,WSAL_SERVERACTIVITY VARCHAR2(30) NOT NULL
   ,WSAL_TIMESTAMP DATE NOT NULL
   ,WSAL_COMPLETIONTIMESTAMP DATE
   ,WSAL_WPCA_ID NUMBER(30)
   ,WSAL_XCED_ID NUMBER(30)
   ,WSAL_FIDELIODATE DATE NOT NULL
   ,WSAL_AUTOMATIC NUMBER(1) NOT NULL
   ,WSAL_TEXT VARCHAR2(2000)
   ,WSAL_FULLLOG CLOB
   ,CONSTRAINT WSAL_PK PRIMARY KEY (WSAL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;
  
COMMENT ON TABLE WSAL IS 'Server activity log';

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

ALTER TABLE XMTY ADD XMTY_LASTREQUESTVALUE VARCHAR2(100);

ALTER TABLE ZFST ADD ZFST_SIMPLIFIEDINVOICE NUMBER(1) DEFAULT 0;

COMMENT ON COLUMN ZFST.ZFST_SIMPLIFIEDINVOICE IS '1 = Portugal Simplified Invoice';

COMMENT ON COLUMN ZINV.ZINV_ROLE IS ' 0 or null = regular invoice | 1 = Deposit Folio | 2 = City Ledger Folio (not yet implemented) | 3 = Credit Note, 31=Faktura Korekta (polish credit note)| 10 = Fattura Riepilogativa (used for Italy)| 4 - Egyptian A/R Invoice | 5 - AR Ricevuta Fiscale | 6 - Stored Info/Proforma Invoice ';

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 Folio, 6 = Information 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';

ALTER TABLE ZINV ADD ZINV_FISCALINVOICE NUMBER(1) DEFAULT 0;

COMMENT ON COLUMN ZINV.ZINV_FISCALINVOICE IS '1 = Turkey e-invoice';

ALTER TABLE WERR ADD WERR_USER VARCHAR2(60);

ALTER TABLE ZFST DROP COLUMN ZFST_SIMPLIFIEDINVOICE CASCADE CONSTRAINTS; 

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 Folio, 6 = Information Folio, 7 = Simplified Invoice (Portugal),  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';

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 Folio, 6 = Information Folio, 7 = Simplified Invoice (Portugal),  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';

ALTER TABLE ZINS DROP COLUMN ZINS_KEYVERSION;

ALTER TABLE ZINS ADD ZINS_KEYVERSION varchar2(40 char);

UPDATE ZINS SET ZINS_KEYVERSION = '1';

ALTER TABLE YRES MODIFY YRES_CRSRESNR VARCHAR2(50);

CREATE TABLE ZPI2
(
  ZPI2_ID       NUMBER(30)  NOT NULL,
  ZPI2_ZINV_ID  NUMBER(30)  NOT NULL,
  ZPI2_ZPOS_ID  NUMBER(30)  ,
  ZPI2_ZPO2_ID  NUMBER(30)  ,
  CONSTRAINT ZPI2_PK PRIMARY KEY (ZPI2_ID) USING INDEX TABLESPACE V8INDEX 
) TABLESPACE V8MAIN;

COMMENT ON TABLE ZPI2 IS ' link between postings and saved information/proforma invoices';

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

CREATE TABLE YBAS (
    YBAS_ID NUMBER(30,0) NOT NULL
   ,YBAS_TABLE_ID NUMBER(30,0) NOT NULL
   ,YBAS_TABLENAME VARCHAR2(4) NOT NULL
   ,YBAS_BASKETID VARCHAR2(20) NOT NULL
   ,CONSTRAINT YBAS_PK PRIMARY KEY (YBAS_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

COMMENT ON TABLE YBAS  IS 'Homepage booking Basket links to the reservation, table reservation, vouchers etc';

ALTER TABLE W8HP ADD (W8HP_MAXPAX NUMBER(2), W8HP_MAXADULT NUMBER(2), W8HP_MAXCHILDREN NUMBER(2));

ALTER TABLE W8HP ADD W8HP_MYSTAYCHECKIN NUMBER(1);

ALTER TABLE W8HP ADD W8HP_PAYDIRECT NUMBER(1);

COMMENT ON COLUMN W8HP.W8HP_PAYDIRECT IS 'if 1, it is not neccessary to enter a credit card and and the customer is routed to the defined pay system before reservation is confirmed.' ;

ALTER TABLE W8HP ADD W8HP_LOGIN_WITH_RESID NUMBER(1);

COMMENT ON COLUMN WDBF.WDBF_TYPE IS '1=HTML 2=RTF';

COMMENT ON COLUMN XUDS.XUDS_DISPLAYTYPE IS '1=Single 2=Double 3=Quadruple';

CREATE TABLE ZQUE (
    ZQUE_ID NUMBER(30) NOT NULL
   ,ZQUE_ZOTA_ID NUMBER(30) NOT NULL
   ,ZQUE_MESSAGE CLOB
   ,ZQUE_RESEND NUMBER(2) NOT NULL
   ,CONSTRAINT ZQUE_PK PRIMARY KEY (ZQUE_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE ZQUE IS 'queue for OTA XML interface messages not sent as of issues with URL';

ALTER TABLE ZOTA ADD ZOTA_RESEND NUMBER(2,0);

COMMENT ON COLUMN ZOTA.ZOTA_RESEND IS 'number of resend tries. 0 = infinity (til successfull)';

ALTER TABLE WATY ADD WATY_DUEDAYS NUMBER(3);

ALTER TABLE WATY ADD WATY_WEBINFO varchar2(1000);

ALTER TABLE WATY ADD WATY_LONGWEBINFO varchar2(2000);

COMMENT ON COLUMN WATY.WATY_DUEDAYS is 'Due days to resolve this task';

COMMENT ON COLUMN WATY.WATY_WEBINFO is 'Web Short Information';

COMMENT ON COLUMN WATY.WATY_LONGWEBINFO is 'Web Long Information';

ALTER TABLE XSCD MODIFY XSCD_FTP VARCHAR2(2000);

ALTER TABLE MMOD ADD MMOD_UPLOAD_DERIVED_RATE NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_UPLOAD_DERIVED_RATE IS 'indicates whether rates of derived rate code (BaseRate in Suite8) will be uploaded';

ALTER TABLE W8HP ADD W8HP_PROFILEPICTURE NUMBER(1);

ALTER TABLE W8HP ADD W8HP_MAXIMGHEIGHT NUMBER(6);

ALTER TABLE W8HP ADD W8HP_MAXIMGWIDTH NUMBER(6);

CREATE TABLE NEAT (
    NEAT_ID NUMBER(30) NOT NULL
   ,NEAT_CODE VARCHAR2(5) NOT NULL
   ,NEAT_PRODUCTNAME VARCHAR2(256) 
   ,NEAT_DEFAULT NUMBER(1) DEFAULT 0 NOT NULL 
   ,NEAT_DISPLAYORDER NUMBER(2)
   ,NEAT_ZDCO_ID NUMBER(30) NOT NULL
   ,CONSTRAINT NEAT_PK PRIMARY KEY (NEAT_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;
   
CREATE SEQUENCE SEQ_NEAT INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE NEAT IS 'Configuration table for Products that are used with the Europaeische.at Web Services';

COMMENT ON COLUMN NEAT.NEAT_CODE IS 'Code of the product, IN611 for example';

COMMENT ON COLUMN NEAT.NEAT_PRODUCTNAME IS 'Name of the product, Hotelstorno Plus (Vermittlerinkasso) for example, to show in the selection';

COMMENT ON COLUMN NEAT.NEAT_DEFAULT IS '1 = default, 0 = not default';

COMMENT ON COLUMN NEAT.NEAT_ZDCO_ID IS 'Link to the department code where the booked insurance fee should be posted';

ALTER TABLE MMAP ADD MMAP_CONV_TYPE NUMBER(2);

COMMENT ON COLUMN MMAP.MMAP_CONV_TYPE IS 'indicates converion type of the custom field 0 = no conversion 1 = source code 2 = channel code 3 = market code'; 

ALTER TABLE WRPT ADD WRPT_EMAILSENDPREF NUMBER(1);

COMMENT ON COLUMN WRPT.WRPT_EMAILSENDPREF IS 'Email sending preference; 0- attachment(defaulrt); 1-body';

ALTER TABLE XPAC ADD XPAC_APPLCOMMENTS NUMBER(1);

COMMENT ON COLUMN XPAC.XPAC_APPLCOMMENTS IS ' Defines if attribute category applies for comments (1) or not(0)';

ALTER TABLE YCMG ADD YCMG_TYPESTATUS NUMBER(1);
  
ALTER TABLE YCMG ADD YCMG_YCMO_ID NUMBER(30);

ALTER TABLE YCMG ADD YCMG_INACTIVE NUMBER(1);

COMMENT ON COLUMN YCMG.YCMG_TYPESTATUS IS '0=Neutral, 1=Positive, 2=Negative';

COMMENT ON COLUMN YCMG.YCMG_YCMO_ID IS 'Linked Guest Comment Origin Id';

COMMENT ON COLUMN YCMG.YCMG_INACTIVE IS ' 1 = Inactive';

CREATE TABLE YCMO
(
  YCMO_ID          NUMBER(30)                   NOT NULL,
  YCMO_SHORTDESC   VARCHAR2(3 BYTE)             NOT NULL,
  YCMO_LONGDESC    VARCHAR2(30 BYTE)            NOT NULL,
  YCMO_DISPLORDER  NUMBER(3),
  YCMO_INACTIVE    NUMBER(1),
  CONSTRAINT YCMO_PK PRIMARY KEY (YCMO_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE YCMO IS ' Comment Type Origin ';

COMMENT ON COLUMN YCMO.YCMO_ID IS ' YCMO unique Id';

COMMENT ON COLUMN YCMO.YCMO_SHORTDESC IS 'Comment Type Origin Code';

COMMENT ON COLUMN YCMO.YCMO_LONGDESC IS 'Comment Type Origin Description';

COMMENT ON COLUMN YCMO.YCMO_DISPLORDER IS 'Display Order';

COMMENT ON COLUMN YCMO.YCMO_INACTIVE IS ' 1 = Inactive';

CREATE SEQUENCE SEQ_YCMO MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1000 INCREMENT BY 1 CACHE 20;

CREATE TABLE YCMT
(
  YCMT_ID            NUMBER(30)                 NOT NULL,
  YCMT_SHORTDESC     VARCHAR2(3 BYTE)           NOT NULL,
  YCMT_LONGDESC      VARCHAR2(30 BYTE)          NOT NULL,
  YCMT_DISPLORDER    NUMBER(3),
  YCMT_NUMERICVALUE  NUMBER(2),
  YCMT_INACTIVE      NUMBER(1),
  CONSTRAINT YCMT_PK PRIMARY KEY (YCMT_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE YCMT IS 'Track Guest Temper for Complaints in YCMT ';

COMMENT ON COLUMN YCMT.YCMT_ID IS ' YCMT Unique Id';

COMMENT ON COLUMN YCMT.YCMT_SHORTDESC IS 'Guest Temper Code';

COMMENT ON COLUMN YCMT.YCMT_LONGDESC IS ' Guest Temper Description';

COMMENT ON COLUMN YCMT.YCMT_DISPLORDER IS 'Display Order';

COMMENT ON COLUMN YCMT.YCMT_INACTIVE IS ' 1 = Inactive';

CREATE SEQUENCE SEQ_YCMT MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1000 INCREMENT BY 1 CACHE 20;

CREATE TABLE YCAL
(
  YCAL_ID       NUMBER(30) NOT NULL,
  YCAL_YCMG_ID  NUMBER(30),
  YCAL_XPDA_ID  NUMBER(30),
  YCAL_XPAC_ID  NUMBER(30),
  CONSTRAINT YCAL_PK PRIMARY KEY (YCAL_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE YCAL IS 'Guest Comment Attribute Links';

COMMENT ON COLUMN YCAL.YCAL_YCMG_ID IS 'Linked Guest Comment Type Id';

COMMENT ON COLUMN YCAL.YCAL_XPDA_ID IS 'Linked Guest Comment Attribute Item Id';

COMMENT ON COLUMN YCAL.YCAL_XPAC_ID IS 'Linked Guest Comment  Attribute Categories Id';

CREATE SEQUENCE SEQ_YCAL MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1000 INCREMENT BY 1 CACHE 20;

ALTER TABLE YCMP ADD YCMP_REPLYNOTE CLOB;
 
ALTER TABLE YCMP ADD YCMP_YCMT_BEGINING_ID NUMBER(30);
 
ALTER TABLE YCMP ADD YCMP_YCMT_ENDING_ID NUMBER(30);
 
COMMENT ON COLUMN YCMP.YCMP_REPLYNOTE IS 'Hotel response to guest';

COMMENT ON COLUMN YCMP.YCMP_YCMT_BEGINING_ID IS 'Initial Linked Guest Temper Id';

COMMENT ON COLUMN YCMP.YCMP_YCMT_ENDING_ID IS  'Final Linked Guest Temper Id';

ALTER TABLE XPAC ADD XPAC_APPLMAINTENANCE NUMBER(1);

COMMENT ON COLUMN  XPAC.XPAC_APPLMAINTENANCE IS 'Applies to Maintenance Attributes';

CREATE TABLE WAAL
(
  WAAL_ID       NUMBER(30)                      NOT NULL,
  WAAL_WACT_ID  NUMBER(30),
  WAAL_XPDA_ID  NUMBER(30),
  WAAL_XPAC_ID  NUMBER(30),
  CONSTRAINT WAAL_PK PRIMARY KEY (WAAL_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE WAAL IS 'Maintenance Attribute Links';

COMMENT ON COLUMN WAAL.WAAL_WACT_ID IS 'Linked  Maintenance Type Id';

COMMENT ON COLUMN WAAL.WAAL_XPDA_ID IS 'Linked Maintenance Attribute Item Id';

COMMENT ON COLUMN WAAL.WAAL_XPAC_ID IS 'Linked Maintenance  Attribute Categories Id';

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

ALTER TABLE YCMP ADD YCMP_CREDITS NUMBER(4);

COMMENT ON COLUMN YCMP.YCMP_CREDITS  IS 'Complaint cost amount';

ALTER TABLE ZCAS ADD ZCAS_INACTIVE NUMBER(1) DEFAULT 0;

COMMENT ON COLUMN ZCAS.ZCAS_INACTIVE is '0 - active, 1 - inactive';

UPDATE ZDCO SET ZDCO_DISABLED_PREARR = 1 WHERE ZDCO_DISABLED_BILLING = 1;

ALTER TABLE XCAC MODIFY XCAC_XGCA_ID NUMBER(30) DEFAULT 0; 

ALTER TABLE YRCH MODIFY YRCH_XGCA_ID NUMBER(30) DEFAULT 0; 

ALTER TABLE YCEV ADD YCEV_NOPARTICIPANTLIMIT NUMBER(1);

ALTER TABLE YCEV ADD YCEV_SET_WEB_SELLLIMIT NUMBER(1);

ALTER TABLE YCEV ADD YCEV_SET_GUEST_WEB_SELLLIMIT NUMBER(1);

ALTER TABLE YCEV ADD YCEV_INACTIVE NUMBER(1);

COMMENT ON COLUMN YCEV.YCEV_NOPARTICIPANTLIMIT IS 'This event does not have any limit for registerd attendees';

COMMENT ON COLUMN YCEV.YCEV_SET_WEB_SELLLIMIT IS '1. Indicates this event have a limit for Online registration';

COMMENT ON COLUMN YCEV.YCEV_SET_GUEST_WEB_SELLLIMIT IS '1. Indicates this event have a limit per Guest for Online registration';

ALTER TABLE W8HP ADD W8HP_REG_SHOWPREFERENCES NUMBER(1);

ALTER TABLE W8HP ADD W8HP_REG_SHOWOTHERPREFERENCES NUMBER(1);

ALTER TABLE W8HP ADD W8HP_REG_SHOWTABLERESERVATIONS NUMBER(1);

ALTER TABLE W8HP ADD W8HP_REG_SHOWBONUSPOINTS NUMBER(1);

ALTER TABLE W8HP ADD W8HP_REG_SHOWREGISTEREDEVENTS NUMBER(1);

ALTER TABLE W8HP ADD W8HP_REG_SHOWRESERVATIONS NUMBER(1);

ALTER TABLE W8HP ADD W8HP_REG_SHOWALLOTTMENTS NUMBER(1);

ALTER TABLE W8HP ADD W8HP_CALENDAREVENT_FILTERDAYS NUMBER(3);

ALTER TABLE W8HP ADD W8HP_SHOWEVENTLOCATIONS NUMBER(1);

ALTER TABLE XMLS MODIFY XMLS_RECIPIENTADDRESS VARCHAR2(4000);

ALTER TABLE YPAC ADD YPAC_FORECASTTYPE NUMBER(1) DEFAULT 0;

COMMENT ON COLUMN YPAC.YPAC_FORECASTTYPE IS '0=once per room 1=adults 2=adults and children';

ALTER TABLE YSLM ADD YSLM_TYPE2 NUMBER(1);

ALTER TABLE YSLM ADD YSLM_RECID2 NUMBER(30);

ALTER TABLE ZITR ADD ZITR_TNAME1 VARCHAR2(4); 

ALTER TABLE ZITR ADD ZITR_TNAME2 VARCHAR2(4);

CREATE TABLE YRPG (
    YRPG_ID NUMBER(30,0) NOT NULL
   ,YRPG_SHORTDESC VARCHAR2(10) NOT NULL
   ,YRPG_LONGDESC VARCHAR2(40) NOT NULL
   ,YRPG_WEBENABLED NUMBER(1)
   ,YRPG_DISPLORDER NUMBER(30,0) 
   ,CONSTRAINT YRPG_PK PRIMARY KEY (YRPG_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE YRPG IS 'Rate Plan Group';

COMMENT ON COLUMN YRPG.YRPG_SHORTDESC IS 'Group Code';
                        
COMMENT ON COLUMN YRPG.YRPG_LONGDESC IS 'Group Name';

COMMENT ON COLUMN YRPG.YRPG_WEBENABLED IS 'Web enabled flag';

ALTER TABLE YRPG ADD YRPG_INTERFACECODE VARCHAR2(1);

COMMENT ON COLUMN YRPG.YRPG_INTERFACECODE IS 'Best Western level code';

CREATE TABLE YGRL (
	YGRL_ID NUMBER (30, 0) NOT NULL
	,YGRL_YRPG_ID NUMBER(30, 0) NOT NULL
	,YGRL_YRCH_ID NUMBER(30, 0) NOT NULL
	,CONSTRAINT YGRL_PK PRIMARY KEY (YGRL_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;	

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

COMMENT ON TABLE YGRL IS 'Rate Plan Group and Rate Code Link table';

COMMENT ON COLUMN YGRL.YGRL_YRPG_ID IS 'Rate Plan Group ID';

COMMENT ON COLUMN YGRL.YGRL_YRCH_ID IS 'Rate Code';

ALTER TABLE ZIPT ADD ZIPT_TNAME1 VARCHAR2(4);

ALTER TABLE ZIPT ADD ZIPT_TNAME2 VARCHAR2(4);

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

COMMENT ON COLUMN YRES.YRES_SELLINGLIMITSCOPE IS '0=General limit 1=Web limit';

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

COMMENT ON COLUMN YRES.YRES_FROMCRS IS '1=crs system created reservation 0=pms uploaded reservation to crs'; 

ALTER TABLE YGCO ADD YGCO_LIMITEDRESEDIT NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YGCO.YGCO_LIMITEDRESEDIT IS 'Some edit features are disabled for reservations with this guarantee code'; 

ALTER TABLE YGCO ADD YGCO_CRSONLY NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YGCO.YGCO_CRSONLY IS 'Only crs can select';

ALTER TABLE XCED ADD XCED_INCENTIVECODE VARCHAR2(5);

COMMENT ON COLUMN XCED.XCED_INCENTIVECODE IS 'User incentive code';

CREATE TABLE XPRG (
   XPRG_ID NUMBER(30) NOT NULL
   ,XPRG_XCMS_ID NUMBER(30) NOT NULL
   ,XPRG_NIGHTSTONEXTTIER NUMBER(30)
   ,XPRG_POINTSBALANCE NUMBER(30)
   ,XPRG_PROGRAMCODE VARCHAR2(10)
   ,XPRG_TIERSINCEDATE DATE
   ,XPRG_TIEREXPIRYDATE DATE
   ,XPRG_NIGHTSTOMAINTAINSTATUS NUMBER(30)
   ,XPRG_NIGHTSSTAYEDINYEAR NUMBER(30)
   ,XPRG_POINTSTOMAINTAINSTATUS NUMBER(30)
   ,XPRG_STAYSTOMAINTAINSTATUS NUMBER(30)
   ,XPRG_NEXTLOYALLEVEL VARCHAR2(10)
   ,XPRG_POINTSTONEXTTIER NUMBER(30)
   ,XPRG_STAYSTONEXTTIER NUMBER(30)
   ,XPRG_ENROLLMENTSOURCE VARCHAR2(30)
   ,XPRG_ENROLLMENTDATE DATE   
  ,CONSTRAINT XPRG_PK PRIMARY KEY (XPRG_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE XPRG IS 'BW points snapshot';

COMMENT ON COLUMN XPRG.XPRG_XCMS_ID IS 'Linked profile';

COMMENT ON COLUMN XPRG.XPRG_NIGHTSTONEXTTIER IS 'Nights to next tier';

COMMENT ON COLUMN XPRG.XPRG_POINTSBALANCE IS 'Points balance';

COMMENT ON COLUMN XPRG.XPRG_PROGRAMCODE IS 'Program code';

COMMENT ON COLUMN XPRG.XPRG_TIERSINCEDATE IS 'Tier since date';

COMMENT ON COLUMN XPRG.XPRG_TIEREXPIRYDATE IS 'Tier expiry date';

COMMENT ON COLUMN XPRG.XPRG_NIGHTSTOMAINTAINSTATUS IS 'Nights to maintain status';

COMMENT ON COLUMN XPRG.XPRG_POINTSTOMAINTAINSTATUS IS 'Points to maintain status';

COMMENT ON COLUMN XPRG.XPRG_STAYSTOMAINTAINSTATUS IS 'Stays to maintain status';

COMMENT ON COLUMN XPRG.XPRG_NEXTLOYALLEVEL IS 'Next loyal level';

COMMENT ON COLUMN XPRG.XPRG_POINTSTONEXTTIER IS 'Points to next tier';

COMMENT ON COLUMN XPRG.XPRG_STAYSTONEXTTIER IS 'Stays to next tier';

COMMENT ON COLUMN XPRG.XPRG_ENROLLMENTSOURCE IS 'Enrollment source  AAATW, CAATW, HARTW, NASTW';

COMMENT ON COLUMN XPRG.XPRG_ENROLLMENTDATE IS 'Date of enrollment';

ALTER TABLE YRCH ADD YRCH_ONLINEINTERFACEENABLED NUMBER(1);

COMMENT ON COLUMN YRCH.YRCH_ONLINEINTERFACEENABLED IS  'CRS Rate Code ';

UPDATE YRCH SET YRCH.YRCH_ONLINEINTERFACEENABLED=1 WHERE EXISTS (SELECT 1 FROM MAVL WHERE MAVL.MAVL_CODE_ID=YRCH.YRCH_ID AND MAVL.MAVL_TYPE='RC');

UPDATE YRCH SET YRCH.YRCH_ONLINEINTERFACEENABLED=0 WHERE YRCH.YRCH_ONLINEINTERFACEENABLED IS NULL;

ALTER TABLE YRCH ADD YRCH_AUTOEXTENDDAYS NUMBER (4);

COMMENT ON COLUMN YRCH.YRCH_AUTOEXTENDDAYS IS 'if greater than zero, at night audit rate details ending on this day will be extended with confugured number of days, if confilct, log is written in wilg';

ALTER TABLE YRAT ADD YRAT_RATEAVAILCAT NUMBER( 1)  DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YRAT.YRAT_RATEAVAILCAT IS '0=Rate; 1=RatePlanGroup'; 

ALTER TABLE YRAV ADD YRAV_RATEAVAILCAT NUMBER( 1)  DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YRAV.YRAV_RATEAVAILCAT IS '0=Rate; 1=RatePlanGroup'; 

ALTER TABLE YRAT ADD YRAT_YRPG_ID NUMBER (30);

COMMENT ON COLUMN YRAT.YRAT_YRPG_ID IS 'Rate Plan Group link'; 

ALTER TABLE YRAV ADD YRAV_YRPG_ID NUMBER (30);

COMMENT ON COLUMN YRAV.YRAV_YRPG_ID IS 'Rate Plan Group link'; 

ALTER TABLE YRAT ADD YRAT_SCOPE NUMBER( 1)  DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YRAT.YRAT_SCOPE IS '0=All; 1=CRS; 2=Web; 3=Local'; 

ALTER TABLE YRAV ADD YRAV_SCOPE NUMBER( 1)  DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YRAV.YRAV_SCOPE IS '0=All; 1=CRS; 2=Web; 3=Local';

CREATE TABLE YRPI
(
  YRPI_ID NUMBER(30) NOT NULL,
  YRPI_YCDP_ID NUMBER(30) NOT NULL,
  YRPI_YGCO_ID NUMBER(30) NOT NULL,
  YRPI_LINKTABLE VARCHAR2 (4),
  YRPI_LINKID NUMBER(30),
  YRPI_STARTDATE DATE,
  YRPI_ENDDATE DATE,
  YRPI_MON NUMBER(1), 
  YRPI_TUE NUMBER(1), 
  YRPI_WED NUMBER(1), 
  YRPI_THU NUMBER(1), 
  YRPI_FRI NUMBER(1), 
  YRPI_SAT NUMBER(1), 
  YRPI_SUN NUMBER(1), 
  CONSTRAINT YRPI_PK PRIMARY KEY (YRPI_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE YRPI IS 'Best Western Policy and guarantee code config';

ALTER TABLE ZITR ADD ZITR_INFO VARCHAR2(50);

ALTER TABLE ZIPT ADD ZIPT_INFO VARCHAR2(50);

COMMENT ON COLUMN XMTY.XMTY_ROLE IS '1 = A/R Number     2 = IATA Number     3 = VAT Number  4 = Frequent Flyer Cards and Loyalty Cards and Bonus point cards (differentiation will not ba handled on this level)     5 = Corporate ID (also used for V8Online IFC)     6 = Unique 3rd party Profile ID for Property Interface     7 = Rebate cards     8 = Contract number    9 = Fiscal Code (Legge Bersani, needed in Italy)    10 = MVZV Membership  11 = Best Western ... to be continued for special numbers';

COMMENT ON COLUMN XMTY.XMTY_ROLE IS '1 = A/R Number     2 = IATA Number     3 = VAT Number  4 = Frequent Flyer Cards and Loyalty Cards and Bonus point cards (differentiation will not ba handled on this level)     5 = Corporate ID (also used for V8Online IFC)     6 = Unique 3rd party Profile ID for Property Interface     7 = Rebate cards     8 = Contract number    9 = Fiscal Code (Legge Bersani, needed in Italy)    10 = MVZV Membership  11 = Best Western  12 = Best Western Affiliate Programs ... to be continued for special numbers';

ALTER TABLE W8HP ADD (W8HP_MINLOS NUMBER(3), W8HP_MAXLOS NUMBER(3), W8HP_BASKET_WRPT_ID NUMBER(30));

UPDATE WRPT SET WRPT_EMAILSENDPREF=1 WHERE WRPT_ID IN (SELECT WRPT_ID FROM WRPT, WRPB WHERE WRPT_WRPB_ID=WRPB_ID AND WRPB_REPORTTYPE=5);

ALTER TABLE YCAT ADD YCAT_MINNOOFPERSONS NUMBER(3);

COMMENT ON COLUMN YCAT.YCAT_MINNOOFPERSONS IS 'Miniumum number of persons (Including children) that can be booked for this room type';

ALTER TABLE YRMS ADD YRMS_MINNOOFPERSONS NUMBER(3);

COMMENT ON COLUMN YRMS.YRMS_MINNOOFPERSONS IS 'Miniumum number of persons (Including children) that can be booked for this room';

ALTER TABLE YRES ADD YRES_SUITE_YRMS_ID NUMBER(30);

ALTER TABLE YRCH ADD YRCH_BASE_DETAILS NUMBER (1);

COMMENT ON COLUMN YRCH.YRCH_BASE_DETAILS IS 'setting to deide if base rate detail setup is used or only header configuration';

ALTER TABLE  YRCD ADD YRCD_BASE_TYPE NUMBER(1);

ALTER TABLE  YRCD ADD YRCD_BASE_ROUNDING NUMBER(1);

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

COMMENT ON COLUMN YRCD.YRCD_BASE_ROUNDING IS '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 IS 'Base rate type is 0=percentage, 1= discount amount';

COMMENT ON COLUMN YRCD.YRCD_BASE_VALUE IS 'Base rate value';  

ALTER TABLE W8HP ADD W8HP_REG_SHOWCUSTOMPAGE NUMBER(1);

ALTER TABLE YCEV ADD YCEV_GUEST_MAX_WEB_SELL NUMBER(5);

COMMENT ON COLUMN YCEV.YCEV_GUEST_MAX_WEB_SELL IS 'Maximum Number of particpants per Guest';

ALTER TABLE YCET ADD YCET_SHORTWEBDESC VARCHAR2(1000);

COMMENT ON COLUMN YCET.YCET_SHORTWEBDESC IS 'Short Web Description';

ALTER TABLE YCEP ADD YCEP_CANCELLED NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE MMOD ADD MMOD_ALTERNATE_PROFILE_DATA NUMBER(1,0);

COMMENT ON COLUMN MMOD.MMOD_ALTERNATE_PROFILE_DATA  IS 'indicates the use of alternate profile data for names and address';

ALTER TABLE XMNR ADD XMNR_SIGNUPPROPERTY VARCHAR2(100 CHAR);

COMMENT ON COLUMN XMNR.XMNR_SIGNUPPROPERTY IS 'The property''s (hotel''s) name, where the membership enrollment happened ';

ALTER TABLE YRCH ADD YRCH_OTA_RULENOTIFRQ NUMBER (1);

COMMENT ON COLUMN YRCH.YRCH_OTA_RULENOTIFRQ IS 'If online interface rate code than depending on this flag, policy information will be uploaded or not';

ALTER TABLE W8HP ADD W8HP_CONFIRMATIONASPDF NUMBER(1);

CREATE TABLE GHAG (
   GHAG_ID NUMBER(30) NOT NULL
  ,GHAG_WLAN_ID NUMBER(30)
  ,GHAG_NAME1 VARCHAR2(35)
  ,GHAG_NAME2 VARCHAR2(35)
  ,GHAG_NAME3 VARCHAR2(35)
  ,LASTMODIFIED DATE
  ,CONSTRAINT GHAG_PK PRIMARY KEY (GHAG_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE GHAG IS 'ALTERNATE STORAGE FOR PROFILE XCMS TABLE';

CREATE TABLE GHAA (
   GHAA_ID NUMBER(30) NOT NULL
  ,GHAA_STREET1 VARCHAR2(60)
  ,GHAA_STREET2 VARCHAR2(60)
  ,GHAA_STREET3 VARCHAR2(60)
  ,GHAA_ZIP VARCHAR2(15)
  ,GHAA_CITY VARCHAR2(60)
  ,LASTMODIFIED DATE
  ,CONSTRAINT GHAA_PK PRIMARY KEY (GHAA_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE GHAA IS 'ALTERNATE STORAGE FOR PROFILE ADRESSES XADR TABLE';

CREATE TABLE GHAD (
   GHAD_ID NUMBER(30) NOT NULL
  ,GHAD_TITLE VARCHAR2(30)
  ,GHAD_ADDRGREET VARCHAR2(30)
  ,GHAD_LETTGREET VARCHAR2(60)
  ,LASTMODIFIED DATE
  ,CONSTRAINT GHAD_PK PRIMARY KEY (GHAD_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE GHAD IS 'Alternate storage for individul profiles xcid table';

UPDATE YRCH SET YRCH.YRCH_ONLINEINTERFACEENABLED = 1 WHERE YRCH.YRCH_CALCMETHOD = 3;

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