--UPDATE FROM 8.9.3.0 TO 8.9.4.0


DROP TABLE VERSION_8_9_3_0;


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


ALTER TABLE YRDA ADD YRDA_MANUAL NUMBER(1) DEFAULT 0 NOT NULL;

UPDATE WATY SET WATY_XCED_ID = NULL WHERE WATY_WEBGUESTENABLED = 1;

CREATE TABLE WDBF
(
  WDBF_ID            NUMBER(30)        NOT NULL,
  WDBF_FILENAME      VARCHAR2(200)     NOT NULL,
  WDBF_TYPE          NUMBER(1)         NOT NULL,
  WDBF_DISPLORDER    NUMBER(3),
CONSTRAINT WDBF_PK PRIMARY KEY (WDBF_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE WDBF IS 'User Dashboard files';

COMMENT ON COLUMN WDBF.WDBF_FILENAME IS 'File Name';

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

COMMENT ON COLUMN WDBF.WDBF_DISPLORDER IS 'Display Order';

CREATE TABLE XUDS
(
  XUDS_ID            NUMBER(30)        NOT NULL,
  XUDS_NAME          VARCHAR2(200)     NOT NULL,
  XUDS_WEGR_ID       NUMBER(30)        ,
  XUDS_XCED_ID       NUMBER(30),
  XUDS_DISPLAYTYPE   NUMBER(1)         NOT NULL,
  XUDS_WDBF_ID_1     NUMBER(30),
  XUDS_WDBF_ID_2     NUMBER(30),
  XUDS_WDBF_ID_3     NUMBER(30),
  XUDS_WDBF_ID_4     NUMBER(30),  
  XUDS_DEFAULT       NUMBER(1),  
  XUDS_DISPLORDER    NUMBER(3),
CONSTRAINT XUDS_PK PRIMARY KEY (XUDS_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE XUDS IS 'User Dashboard files';

COMMENT ON COLUMN XUDS.XUDS_WEGR_ID IS 'User Group';

COMMENT ON COLUMN XUDS.XUDS_XCED_ID IS 'User id';

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

COMMENT ON COLUMN XUDS.XUDS_WDBF_ID_1 IS 'wdbf_id of the file, shown in container 1';

COMMENT ON COLUMN XUDS.XUDS_WDBF_ID_2 IS 'wdbf_id of the file, shown in container 2';

COMMENT ON COLUMN XUDS.XUDS_WDBF_ID_3 IS 'wdbf_id of the file, shown in container 3';

COMMENT ON COLUMN XUDS.XUDS_WDBF_ID_4 IS 'wdbf_id of the file, shown in container 4';

COMMENT ON COLUMN XUDS.XUDS_DEFAULT IS 'Default display setup';

CREATE TABLE XMDY ( 
 XMDY_ID NUMBER(30) NOT NULL
,XMDY_XMTY_ID NUMBER(30) NOT NULL
,XMDY_FROMDATE DATE
,XMDY_UNTILDATE DATE
,CONSTRAINT XMDY_PK PRIMARY KEY (XMDY_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE XMDY IS 'Membership type rate  dateranges link ';

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

CREATE TABLE XMRL ( 
 XMRL_ID NUMBER(30) NOT NULL
,XMRL_XMDY_ID NUMBER(30) NOT NULL
,XMRL_YRCH_ID NUMBER(30) NOT NULL 
,CONSTRAINT XMRL_PK PRIMARY KEY (XMRL_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE XMRL IS 'Membership type rate link to dateranges (xmdy)';

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

CREATE TABLE XMBY ( 
 XMBY_ID NUMBER(30) NOT NULL
,XMBY_XMTL_ID NUMBER(30) NOT NULL
,XMBY_FROMDATE DATE 
,XMBY_UNTILDATE DATE  
,CONSTRAINT XMBY_PK PRIMARY KEY (XMBY_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE XMBY IS 'Bonus Level dateranges link for linked rates';

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

CREATE TABLE XMBL (
 XMBL_ID NUMBER(30) NOT NULL
,XMBL_XMBY_ID NUMBER(30) NOT NULL
,XMBL_YRCH_ID NUMBER(30) NOT NULL 
,CONSTRAINT XMBL_PK PRIMARY KEY (XMBL_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE XMBL IS 'Bonus Level rate link to dateranges';

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

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

ALTER TABLE ZFAG ADD ZFAG_IGNOREINVOICE NUMBER(1) DEFAULT 0;

COMMENT ON COLUMN ZFAG.ZFAG_IGNOREINVOICE IS '1 - Ignore invoice creation';

ALTER TABLE WQUE ADD WQUE_ANSWERMAXLENGTH NUMBER(4);

COMMENT ON COLUMN WQUE.WQUE_ANSWERMAXLENGTH IS 'maximum number of characters allowed in answer text';

ALTER TABLE YRSC ADD YRSC_RETURN_YHKS_ID NUMBER(30);

COMMENT ON COLUMN YRSC.YRSC_RETURN_YHKS_ID IS 'Default return Status';

CREATE TABLE YBCN
(
  YBCN_ID  NUMBER(30)  NOT NULL,
  YBCN_CONTRACTNUMBER NUMBER(3)  NOT NULL,
  YBCN_TIMESTAMP  DATE  NOT NULL,
  YBCN_YBOM_ID  NUMBER(30),
  CONSTRAINT YBCN_PK PRIMARY KEY  (YBCN_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE YBCN IS 'Contract number history';

COMMENT ON COLUMN YBCN.YBCN_ID IS 'Contract number history id';

COMMENT ON COLUMN YBCN.YBCN_CONTRACTNUMBER IS 'Contract number';

COMMENT ON COLUMN YBCN.YBCN_TIMESTAMP IS 'When contract number has been created';

COMMENT ON COLUMN YBCN.YBCN_YBOM_ID IS 'Booking id link';

CREATE TABLE WRWM (
    WRWM_ID NUMBER(30) NOT NULL
   ,WRWM_WRPT_ID NUMBER(30) NOT NULL
   ,WRWM_PART_WRPT_ID NUMBER(30) NOT NULL
   ,WRWM_FILENAME VARCHAR2(60) NOT NULL
   ,WRWM_EXECUTIONSTEP NUMBER(3) NOT NULL
   ,CONSTRAINT WRWM_PK PRIMARY KEY (WRWM_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE WRWM IS 'Word Merge components table';

COMMENT ON COLUMN WRWM.WRWM_ID IS 'Id';

COMMENT ON COLUMN WRWM.WRWM_WRPT_ID IS 'Id of main report';

COMMENT ON COLUMN WRWM.WRWM_FILENAME IS 'Subreport filename, as specified in word merge document';

COMMENT ON COLUMN WRWM.WRWM_EXECUTIONSTEP IS 'Execution Step';

ALTER TABLE XMSR ADD XMSR_URL VARCHAR2(255);

COMMENT ON COLUMN XMSR.XMSR_URL IS 'Source URL';

ALTER TABLE XMSR ADD XMSR_USER VARCHAR2(60);

COMMENT ON COLUMN XMSR.XMSR_USER IS 'Source login user';

ALTER TABLE XMSR ADD XMSR_PASSWORD VARCHAR2(60);



CREATE TABLE XMLA ( 
  XMLA_ID NUMBER(30) NOT NULL,
  XMLA_XMLS_ID NUMBER(30) NOT NULL,
  XMLA_WTXT_ID NUMBER(30) NOT NULL,
  XMLA_FILENAME VARCHAR2(120) NOT NULL,
  CONSTRAINT XMLA_PK PRIMARY KEY (XMLA_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN; 

COMMENT ON TABLE XMLA IS 'Incoming E-Mail attachments';

COMMENT ON COLUMN XMLA.XMLA_XMLS_ID IS 'Email attachment Link to email';

COMMENT ON COLUMN XMLA.XMLA_WTXT_ID IS 'Body of attachment';

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

ALTER TABLE XMLD ADD XMLD_BACKUP_XMLD_ID NUMBER(30);

COMMENT ON COLUMN XMLD.XMLD_BACKUP_XMLD_ID IS 'Backup folder';

CREATE TABLE XMLB ( 
  XMLB_ID NUMBER(30) NOT NULL,
  XMLB_XMLS_ID NUMBER(30) NOT NULL,
  XMLB_YRES_ID NUMBER(30),
  XMLB_WPHS_ID NUMBER(30),
  CONSTRAINT XMLB_PK PRIMARY KEY (XMLB_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN; 

COMMENT ON TABLE XMLB IS 'Linked bookings';

COMMENT ON COLUMN XMLB.XMLB_XMLS_ID IS 'Email ID';

COMMENT ON COLUMN XMLB.XMLB_YRES_ID IS 'Linked reservation id';

COMMENT ON COLUMN XMLB.XMLB_WPHS_ID IS 'Partner id';

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

ALTER TABLE YRCO ADD YRCO_XMLS_ID NUMBER(30);

COMMENT ON COLUMN YRCO.YRCO_XMLS_ID IS 'Linked to incoming email id';

ALTER TABLE XMLS ADD XMLS_YRCO_ID NUMBER(30);

COMMENT ON COLUMN XMLS.XMLS_YRCO_ID IS 'Link of incoming email to profile';

CREATE TABLE XMBC ( 
  XMBC_ID NUMBER(30) NOT NULL,
  XMBC_YRCO_ID NUMBER(30) NOT NULL,
  XMBC_XMLB_ID NUMBER(30) NOT NULL,
  CONSTRAINT XMBC_PK PRIMARY KEY (XMBC_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN; 

COMMENT ON TABLE XMBC IS 'For emails confirmation links group of attached reservations to incomming email, to an outgoing correspondence';

COMMENT ON COLUMN XMBC.XMBC_XMLB_ID IS 'Linked reservation to incoming email';

COMMENT ON COLUMN XMBC.XMBC_YRCO_ID IS 'Outgoing correspondence';

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

ALTER TABLE YDET ADD YDET_FINALAMOUNT NUMBER(30,4);

ALTER TABLE YDET ADD YDET_FINALFOREIGNAMOUNT NUMBER(30,4);

COMMENT ON COLUMN YDET.YDET_FINALAMOUNT IS 'Total amount with discounts applied'; 

COMMENT ON COLUMN YDET.YDET_FINALFOREIGNAMOUNT IS 'Total foreign amount with discounts applied';

ALTER TABLE WCIP ADD WCIP_OOO NUMBER (1);

COMMENT ON COLUMN WCIP.WCIP_OOO IS 'Kiosk is out of order';

ALTER TABLE YPCF ADD YPCF_CREATEDINHOTEL VARCHAR2(100);

COMMENT ON COLUMN YPCF.YPCF_CREATEDINHOTEL IS 'DO NOT REMOVE FROM TABLE';

ALTER TABLE WSES ADD WSES_INSTANCEID NUMBER(3);

ALTER TABLE YRCH ADD YRCH_IGNORECHANGE NUMBER (1);

COMMENT ON COLUMN YRCH.YRCH_IGNORECHANGE IS 'On rate selection the rate price on arrival date will be used for the whole stay';

ALTER TABLE MCFG MODIFY MCFG_API_LOGFILE VARCHAR2(100) NULL;

ALTER TABLE MCFG MODIFY MCFG_MAP_LOGFILE VARCHAR2(100) NULL;

ALTER TABLE ZELL ADD ZELL_HOURS NUMBER(2);

ALTER TABLE YRCH ADD YRCH_BASERATECALC_ADDTORATE NUMBER (1);

COMMENT ON COLUMN YRCH.YRCH_BASERATECALC_ADDTORATE IS 'calculating base rate price before or after addtoratepackages. 0 = before, 1= after'; 

ALTER TABLE YRCH ADD YRCH_BASERATECALC_DAYTYPE NUMBER (1);

COMMENT ON COLUMN YRCH.YRCH_BASERATECALC_DAYTYPE IS 'calculating base rate price before or after Daytypes. 0 = before, 1= after'; 

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

UPDATE YRCH SET YRCH_AVAIL_YRCH_ID = NULL WHERE YRCH_AVAIL_YRCH_ID = 0;
