--UPDATE FROM 8.8.2.0 TO 8.8.3.0


DROP TABLE VERSION_8_8_2_0;



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


ALTER TABLE ROTM ADD ROTM_DISPLAYSTARTTIME DATE;

ALTER TABLE RRES ADD RRES_SHARABLE NUMBER (1);

COMMENT ON COLUMN RRES.RRES_SHARABLE IS 'Allow this reservation to be shared';

CREATE TABLE WEDA (
   WEDA_ID NUMBER(30) NOT NULL
  ,WEDA_ROLE NUMBER(1) NOT NULL
  ,WEDA_FILEPATH VARCHAR2(400) NOT NULL
  ,CONSTRAINT WEDA_PK PRIMARY KEY (WEDA_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE WEDA IS 'Email Default attachments per role';

COMMENT ON COLUMN WEDA.WEDA_ROLE IS '1=Profiles|2=Tasks+Activities|3=PMS|4=CCM'; 

ALTER TABLE WUSS MODIFY WUSS_NAME VARCHAR2(200);

ALTER TABLE ZPOS ADD ZPOS_DEPOSIT_ZINV_ID NUMBER(30);

COMMENT ON COLUMN ZPOS.ZPOS_DEPOSIT_ZINV_ID IS 'Reference to ZINV_ID of the deposit invoice (using enhanced deposit handling)';

ALTER TABLE YBLH ADD YBLH_AUTOGROUP NUMBER(1);

ALTER TABLE YGRP ADD YGRP_BLOCK_DEFAULT NUMBER(1) DEFAULT 0;

COMMENT ON COLUMN YGRP.YGRP_BLOCK_DEFAULT IS '1 - default group of linked block in case yblh_autogroup = 1';

COMMENT ON COLUMN WEDA.WEDA_ROLE IS '1=Profiles|2=Tasks+Activities|3=Reservation|4=CCM|5=A/R|6=Maintenance|7=Table Reservation';

ALTER TABLE XSCD ADD XSCD_FILENAMEMASK VARCHAR2(200);

ALTER TABLE YBOM ADD YBOM_CHANNEL_YRAS_ID NUMBER(30);

COMMENT ON COLUMN YBOM.YBOM_CHANNEL_YRAS_ID IS 'Channel code link';

CREATE TABLE ZYTT (
    ZYTT_ID NUMBER(30) NOT NULL
   ,ZYTT_SHORTDESC VARCHAR2(3 CHAR) NOT NULL
   ,ZYTT_LONGDESC VARCHAR2(30 CHAR) 
   ,ZYTT_DISPLORDER NUMBER(3) 
   ,ZYTT_INACTIVE NUMBER(1) 
   ,ZYTT_DEFAULT NUMBER(1) 
   ,ZYTT_FROMNUMBER VARCHAR2(6 CHAR) 
   ,ZYTT_TONUMBER VARCHAR2(6 CHAR) 
   ,CONSTRAINT ZYTT_PK PRIMARY KEY (ZYTT_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE ZYTT IS 'Config of TI yearly tickets';

COMMENT ON COLUMN ZYTT.ZYTT_FROMNUMBER IS 'start of the card number range';

COMMENT ON COLUMN ZYTT.ZYTT_TONUMBER IS 'end of the card number range';

CREATE TABLE ZYPC (
    ZYPC_ID NUMBER(30) NOT NULL
   ,ZYPC_SHORTDESC VARCHAR2(3 CHAR) NOT NULL
   ,ZYPC_LONGDESC VARCHAR2(30 CHAR) 
   ,ZYPC_DISPLORDER NUMBER(3) 
   ,ZYPC_INACTIVE NUMBER(1) 
   ,ZYPC_DEFAULT NUMBER(1) 
   ,CONSTRAINT ZYPC_PK PRIMARY KEY (ZYPC_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE ZYPC IS 'Config of TI yearly tickets payment cycles';

CREATE TABLE ZYTX (
    ZYTX_ID NUMBER(30) NOT NULL
   ,ZYTX_ZYTT_ID NUMBER(30) NOT NULL
   ,ZYTX_ZYPC_ID NUMBER(30) NOT NULL
   ,ZYTX_ZTRA_ID NUMBER(30) NOT NULL
   ,ZYTX_VALIDFROM DATE
   ,ZYTX_VALIDUNTIL DATE
   ,ZYTX_GUEST_XCMS_ID NUMBER(30) NOT NULL
   ,ZYTX_CONTRACTPARTNER_XCMS_ID NUMBER(30) NOT NULL
   ,ZYTX_SALESCONTACT_XCMS_ID NUMBER(30) NOT NULL
   ,ZYTX_INACTIVE NUMBER(1) 
   ,ZYTX_INACTIVE_REASON VARCHAR2(200 CHAR)
   ,ZYTX_CANCELDATE DATE
   ,ZYTX_CANCEL_REASON VARCHAR2(200 CHAR)
   ,CONSTRAINT ZYTX_PK PRIMARY KEY (ZYTX_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE ZYTX IS 'TI yearly tickets';

COMMENT ON COLUMN ZYTX.ZYTX_GUEST_XCMS_ID IS 'link to guest profile';

COMMENT ON COLUMN ZYTX.ZYTX_CONTRACTPARTNER_XCMS_ID IS 'link to contract partner profile';

COMMENT ON COLUMN ZYTX.ZYTX_SALESCONTACT_XCMS_ID IS 'link to sales contact profile';

CREATE TABLE ZYZF (
    ZYZF_ID NUMBER(30) NOT NULL
   ,ZYZF_ZYTX_ID NUMBER(30) NOT NULL
   ,ZYZF_ZFAC_ID NUMBER(30) NOT NULL
   ,CONSTRAINT ZYZF_PK PRIMARY KEY (ZYZF_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE ZYZF IS 'Link table between ZYTX and ZFAC to identify transponders used with one yearly ticket';

CREATE TABLE ZYTG (
    ZYTG_ID NUMBER(30) NOT NULL
   ,ZYTG_SHORTDESC VARCHAR2(3 CHAR) NOT NULL
   ,ZYTG_LONGDESC VARCHAR2(30 CHAR) 
   ,ZYTG_DISPLORDER NUMBER(3) 
   ,ZYTG_ROLE NUMBER(3) 
   ,CONSTRAINT ZYTG_PK PRIMARY KEY (ZYTG_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE ZYTG IS 'Yearly Ticket Groups for Tropical Islands';

COMMENT ON COLUMN ZYTG.ZYTG_ROLE IS '0 - No role, 1 - Yearly Tickets'; 

ALTER TABLE PTIK ADD PTIK_ZYTG_ID NUMBER(30);

COMMENT ON COLUMN PTIK.PTIK_ZYTG_ID IS 'Link to Ticket groups'; 

ALTER TABLE YRAS ADD YRAS_DEFAULT_CCM NUMBER(1) DEFAULT 0 ;

COMMENT ON COLUMN YRAS.YRAS_DEFAULT_CCM IS 'Default for new CCM bookings can be set for one source and once channel code';

CREATE TABLE YTRX
(
   YTRX_ID           NUMBER (30)
  ,YTRX_CALCDATE     DATE 
  ,YTRX_CALCFORDATE  DATE
  ,YTRX_RESTYPE      NUMBER (1)
  ,YTRX_YCAT_ID      NUMBER (30)
  ,YTRX_XCMA_ID      NUMBER (30)
  ,YTRX_GROSS_TOTAL  NUMBER (30,4)
  ,YTRX_NET_TOTAL    NUMBER (30,4)
  ,YTRX_GROSS_ROOM   NUMBER (30,4)
  ,YTRX_NET_ROOM     NUMBER (30,4) 
  ,YTRX_NOOFROOMS    NUMBER(3)          
  ,YTRX_DEFROOMS     NUMBER(3)          
  ,YTRX_ADULTNO      NUMBER(10)         
  ,YTRX_DEFADULTNO   NUMBER(10)         
  ,YTRX_CHILDNO      NUMBER(10)         
  ,YTRX_DEFCHILDNO   NUMBER(10)         
  ,YTRX_CHILDNODOMESTIC    NUMBER(10)         
  ,YTRX_DEFCHILDNODOMESTIC NUMBER(10)         
  ,CONSTRAINT YTRX_PK PRIMARY KEY (YTRX_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE YTRX IS 'Trend analyisis Market Code per Roomtype per reservationtype Forecast';

COMMENT ON COLUMN YTRX.YTRX_RESTYPE IS '1=Individual reservation, 2= Group, 3=Block ';

CREATE TABLE XGCA (
    XGCA_ID NUMBER(30) NOT NULL
   ,XGCA_SHORTDESC VARCHAR2(5) NOT NULL 
   ,XGCA_LONGDESC VARCHAR2(30) NOT NULL
   ,XGCA_INACTIVE NUMBER(1)
   ,XGCA_DISPLORDER NUMBER(3)
   ,CONSTRAINT XGCA_PK PRIMARY KEY (XGCA_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;
   
CREATE SEQUENCE SEQ_XGCA INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE XGCA IS 'childage category groups';

ALTER TABLE YRCH ADD YRCH_XGCA_ID NUMBER(30);

COMMENT ON COLUMN YRCH.YRCH_XGCA_ID IS 'childage category group';

COMMENT ON COLUMN XCAC.XCAC_XGCA_ID IS 'childage category group';

CREATE TABLE RRYB (
    RRYB_ID NUMBER(30) NOT NULL,
    RRYB_RRES_ID NUMBER(30) NOT NULL,
    RRYB_YBOM_ID NUMBER(30),
    RRYB_YEVM_ID NUMBER(30),
    RRYB_YEBS_ID NUMBER(30),
    CONSTRAINT RRYB_PK PRIMARY KEY (RRYB_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE RRYB IS 'Table reservations linked to CCM';

COMMENT ON COLUMN RRYB.RRYB_RRES_ID IS 'Table Reservation ID';

COMMENT ON COLUMN RRYB.RRYB_YBOM_ID IS 'Booking ID';

COMMENT ON COLUMN RRYB.RRYB_YEVM_ID IS 'Event ID';

COMMENT ON COLUMN RRYB.RRYB_YEBS_ID IS 'Function Space Booked ID';

ALTER TABLE YPKR ADD YPKR_NOOFPAX NUMBER(1);

COMMENT ON COLUMN YPKR.YPKR_NOOFPAX IS '1=yes';

ALTER TABLE YETR ADD YETR_NOOFPAX NUMBER(1);

COMMENT ON COLUMN YETR.YETR_NOOFPAX IS '1=yes';

ALTER TABLE YBOM MODIFY YBOM_NOOFATTENDEES NUMBER(5);

ALTER TABLE YGRP MODIFY YGRP_COMMENT VARCHAR2(1000);

ALTER TABLE WBCF ADD WBCF_COMMITCOUNT NUMBER (4);

COMMENT ON COLUMN WBCF.WBCF_COMMITCOUNT IS 'db commit after number of processed records';

ALTER TABLE WBCF ADD WBCF_DELAYMSEC NUMBER (4);

COMMENT ON COLUMN WBCF.WBCF_DELAYMSEC IS 'break after db commit';

UPDATE RRES SET RRES_YEBS_ID=NULL;

ALTER TABLE RRES DROP COLUMN RRES_YEBS_ID;

ALTER TABLE RRST ADD RRST_YFSC_ID NUMBER(30);

CREATE TABLE YFAL (
    YFAL_ID NUMBER(30) NOT NULL
   ,YFAL_YRES_ID NUMBER(30) NOT NULL
   ,YFAL_ZFAC_ID NUMBER(30) NOT NULL
   ,YFAL_ROLE NUMBER(1) NOT NULL
   ,CONSTRAINT YFAL_PK PRIMARY KEY (YFAL_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE YFAL IS 'reservation - financial account link';

COMMENT ON COLUMN YFAL.YFAL_ROLE IS '0=prearrival 1=postdeparture';

ALTER TABLE PTIK ADD (PTIK_ZYTT_ID NUMBER(30));

ALTER TABLE YPAC ADD YPAC_FOLIOTEXT VARCHAR2(60);

COMMENT ON COLUMN YPAC.YPAC_FOLIOTEXT IS 'text printed on folio if package is print separate';

ALTER TABLE WSCT ADD WSCT_DESCRIPTION VARCHAR(30);

ALTER TABLE YBLH ADD YBLH_WEBPROMOTION VARCHAR2(30);

COMMENT ON COLUMN YBLH.YBLH_WEBPROMOTION IS 'Web promotion codes';

CREATE TABLE MRAT
(
  MRAT_ID                NUMBER(30)             NOT NULL,
  MRAT_ZCNV_ID           NUMBER(30)             NOT NULL,
  MRAT_XPAC_ID           NUMBER(30)             NOT NULL,
  MRAT_TYPE              VARCHAR2(2),
  CONSTRAINT MRAT_PK PRIMARY KEY (MRAT_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

COMMENT ON COLUMN MRAT.MRAT_ID IS 'unique id';

COMMENT ON COLUMN MRAT.MRAT_ZCNV_ID IS 'link to zcnv and mmod';

COMMENT ON COLUMN MRAT.MRAT_XPAC_ID IS 'xpac id';

COMMENT ON COLUMN MRAT.MRAT_TYPE IS 'conversion type. possible values are X0..X9';

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

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

COMMENT ON COLUMN MMOD.MMOD_USE_RES_ATTR IS 'Indicates whether userdefine reservation attributes are used';

ALTER TABLE MCOP ADD MCOP_DESC VARCHAR2(40);

COMMENT ON COLUMN MCOP.MCOP_DESC IS 'description of user defined conversion type';

ALTER TABLE MRAT ADD MRAT_RESORDET NUMBER(1,0);

COMMENT ON COLUMN MRAT.MRAT_RESORDET IS 'Indicates whether attribute is linked to yres or ydet';

ALTER TABLE SMAN MODIFY SMAN_BEDSINHOTEL_M NUMBER(7);

ALTER TABLE SMAN MODIFY SMAN_MAXBEDSINHOTEL_M NUMBER(7);

ALTER TABLE YRCH ADD YRCH_WRPT_ID NUMBER (30);

COMMENT ON COLUMN YRCH.YRCH_WRPT_ID IS 'default confirmation letter of rate';

ALTER TABLE YPRR ADD YPRR_EB_CALCMETHOD NUMBER(1);

COMMENT ON COLUMN YPRR.YPRR_EB_CALCMETHOD IS '1: Percentage, 2:Night  3: Stay ';

ALTER TABLE YPRR ADD YPRR_EB_POSTDAY NUMBER(1);

COMMENT ON COLUMN YPRR.YPRR_EB_POSTDAY IS '0: option disabled(percentage calculation), 1: Average, 2:First Day  3: Last Day ';

UPDATE YPRR SET YPRR_EB_CALCMETHOD=1, YPRR_EB_POSTDAY=0 WHERE NVL(YPRR_FIXAMOUNTPERNIGHT,0)=0;

UPDATE YPRR SET YPRR_EB_CALCMETHOD=2 ,YPRR_EB_POSTDAY=2 WHERE NVL(YPRR_FIXAMOUNTPERNIGHT,0)>0;

COMMENT ON COLUMN YPRR.YPRR_FIXAMOUNTPERNIGHT IS 'Obsolete column, not supported anymore';

ALTER TABLE ZADR MODIFY ZADR_CITY VARCHAR2(60);

UPDATE YFSR SET YFSR_VALUE='0' WHERE YFSR_VALUE='-1';

ALTER TABLE WMAI ADD WMAI_DRAFT NUMBER(1);

COMMENT ON COLUMN WMAI.WMAI_DRAFT IS 'The email saved for future editing';

ALTER TABLE WMAI MODIFY WMAI_TO NULL;

ALTER TABLE WMAI MODIFY WMAI_SUBJECT NULL;
