--THIS FILE IS NEEDED ONLY FOR THE ONLINE UPDATE!!!

--UPDATE FROM 8.6.1.3 TO 8.6.2.0


DROP TABLE VERSION_8_6_1_3;



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


CREATE TABLE PSAC (
  psac_id NUMBER(30)  NOT NULL ,
  psac_in_psec_id NUMBER(30)  NULL,
  psac_out_psec_id NUMBER(30) NOT NULL,
  psac_shortdesc VARCHAR2(3) NULL,
  psac_longdesc VARCHAR2(100) NULL,
  psac_externalid VARCHAR2(200) NULL,
  psac_displorder NUMBER(3)  NULL,
  CONSTRAINT PSAC_PK PRIMARY KEY (PSAC_ID) USING INDEX TABLESPACE V8INDEX  ) TABLESPACE V8CONF ; 

CREATE INDEX PSAC_FKINDEX1 ON PSAC(PSAC_IN_PSEC_ID) TABLESPACE V8INDEX;

CREATE INDEX PSAC_FKINDEX2 ON PSAC(PSAC_OUT_PSEC_ID) TABLESPACE V8INDEX;

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

ALTER TABLE YRES ADD YRES_SUITE_YRES_ID NUMBER(30);

CREATE INDEX YRES_SUITE_YRES_ID ON YRES(YRES_SUITE_YRES_ID) TABLESPACE V8INDEX;

ALTER TABLE YDET ADD YDET_SUITE_YCAT_ID NUMBER(30);

CREATE INDEX YDET_SUITE_YCAT_ID ON YDET(YDET_SUITE_YCAT_ID) TABLESPACE V8INDEX;

ALTER TABLE YDET ADD YDET_SUITE_YRMS_ID NUMBER(30);

CREATE INDEX YDET_SUITE_YRMS_ID ON YDET(YDET_SUITE_YRMS_ID) TABLESPACE V8INDEX;

ALTER TABLE YCAT ADD YCAT_SUITE NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE YCAT ADD YCAT_SUITE_PRIMARY_YCAT_ID NUMBER(30);

ALTER TABLE YRMS ADD YRMS_SUITE NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE YRMS ADD YRMS_SUITE_PRIMARY_YRMS_ID NUMBER(30);

CREATE TABLE YCAS (
    ycas_id NUMBER(30) NOT NULL
   ,ycas_suite_ycat_id NUMBER(30) NOT NULL
   ,ycas_room_ycat_id NUMBER(30) NOT NULL
   ,ycas_number NUMBER(3) NOT NULL,
   CONSTRAINT YCAS_PK PRIMARY KEY (YCAS_ID) USING INDEX TABLESPACE V8INDEX  ) TABLESPACE V8CONF ;

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

CREATE INDEX YCAS_SUITE_YCAT_ID ON YCAS(YCAS_SUITE_YCAT_ID) TABLESPACE V8INDEX;

CREATE INDEX YCAS_ROOM_YCAT_ID ON YCAS(YCAS_ROOM_YCAT_ID) TABLESPACE V8INDEX;

CREATE TABLE YRSS (
    yrss_id NUMBER(30) NOT NULL
   ,yrss_suite_yrms_id NUMBER(30) NOT NULL
   ,yrss_room_yrms_id NUMBER(30) NOT NULL,
   CONSTRAINT YRSS_PK PRIMARY KEY (YRSS_ID) USING INDEX TABLESPACE V8INDEX  ) TABLESPACE V8CONF ;

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

CREATE INDEX YRSS_SUITE_YRMS_ID ON YRSS(YRSS_SUITE_YRMS_ID) TABLESPACE V8INDEX;

CREATE INDEX YRSS_ROOM_YRMS_ID ON YRSS(YRSS_ROOM_YRMS_ID) TABLESPACE V8INDEX;

CREATE TABLE YSLR (
     yslr_id NUMBER(30) NOT NULL
    ,yslr_one_yrms_id NUMBER(30) NOT NULL
    ,yslr_other_yrms_id NUMBER(30) NOT NULL ,
    CONSTRAINT YSLR_PK PRIMARY KEY (YSLR_ID) USING INDEX TABLESPACE V8INDEX  ) TABLESPACE V8CONF ;

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

CREATE INDEX YSLR_ONE_YRMS_ID ON YSLR(YSLR_ONE_YRMS_ID) TABLESPACE V8INDEX;

CREATE INDEX YSLR_OTHER_YRMS_ID ON YSLR(YSLR_OTHER_YRMS_ID) TABLESPACE V8INDEX;

ALTER TABLE YCAT ADD YCAT_NOTINAVL NUMBER(1) DEFAULT 0 NOT NULL ;

ALTER TABLE YCAT ADD YCAT_DONTSELLALONE NUMBER(1) DEFAULT 0 NOT NULL ;

ALTER TABLE YRES ADD YRES_NOTCLOSED_ZFAC_ID NUMBER(30);

CREATE INDEX YRES_NOTCLOSED_ZFAC_ID ON YRES(YRES_NOTCLOSED_ZFAC_ID) TABLESPACE V8INDEX;

CREATE TABLE YADB
(
  yadb_id                  NUMBER(30)     NOT NULL,
  yadb_yres_id     		   NUMBER(30)     NULL,
  yadb_zfac_id    		   NUMBER(30)     NULL,
  yadb_xced_id    		   NUMBER(30) 	  NOT NULL,
  yadb_comment			   VARCHAR2(100)  NULL,
  yadb_role				   NUMBER(1)	  NOT NULL,
  CONSTRAINT YADB_PK PRIMARY KEY (YADB_ID) USING INDEX TABLESPACE V8INDEX  ) TABLESPACE V8MAIN ;

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

COMMENT ON TABLE YADB IS 'City Ledger Authorization log';

COMMENT ON COLUMN YADB.YADB_ROLE IS '0=Direct Bill/City Ledger Auth';

CREATE INDEX YADB_YRES_ID ON YADB(YADB_YRES_ID) TABLESPACE V8INDEX;

CREATE INDEX YADB_ZFAC_ID ON YADB(YADB_ZFAC_ID) TABLESPACE V8INDEX;

CREATE INDEX YADB_XCED_ID ON YADB(YADB_XCED_ID) TABLESPACE V8INDEX;

CREATE TABLE ZBLI
(
  zbli_id                  NUMBER(30)     NOT NULL,
  zbli_from_yres_id        NUMBER(30)     NULL,
  zbli_from_zfac_id    	   NUMBER(30)     NULL,
  zbli_to_yres_id     	   NUMBER(30)     NULL,
  zbli_to_zfac_id    	   NUMBER(30)     NULL,
  CONSTRAINT ZBLI_PK PRIMARY KEY (ZBLI_ID) USING INDEX TABLESPACE V8INDEX  ) TABLESPACE V8MAIN ;

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

COMMENT ON TABLE ZBLI IS 'Balance link. One of the FROM and one of the TO fields must be filled';

COMMENT ON COLUMN ZBLI.ZBLI_FROM_YRES_ID IS 'ID of the source reservation from where the balance is linked';

COMMENT ON COLUMN ZBLI.ZBLI_TO_YRES_ID IS 'ID of the target reservation where the balance is linked';

CREATE INDEX ZBLI_FROM_YRES_ID ON ZBLI(ZBLI_FROM_YRES_ID) TABLESPACE V8INDEX;

CREATE INDEX ZBLI_FROM_ZFAC_ID ON ZBLI(ZBLI_FROM_ZFAC_ID) TABLESPACE V8INDEX;

CREATE INDEX ZBLI_TO_YRES_ID ON ZBLI(ZBLI_TO_YRES_ID) TABLESPACE V8INDEX;

CREATE INDEX ZBLI_TO_ZFAC_ID ON ZBLI(ZBLI_TO_ZFAC_ID) TABLESPACE V8INDEX;

ALTER TABLE YBFO MODIFY ( YBFO_B NUMBER(5)  );

ALTER TABLE YBFO MODIFY ( YBFO_L NUMBER(5)  );

ALTER TABLE YBFO MODIFY ( YBFO_D NUMBER(5)  );
 
CREATE TABLE ZBTL
(
  zbtl_id              number(30) not null,
  zbtl_from_zwin_id    number(30) not null,
  zbtl_to_zwin_id      number(30) not null,
  zbtl_from_zpos_id    number(30) not null,
  zbtl_to_zpos_id      number(30) not null,
  zbtl_date			   date,
  CONSTRAINT ZBTL_PK PRIMARY KEY (ZBTL_ID) USING INDEX TABLESPACE V8INDEX  ) TABLESPACE V8MAIN ; 

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

CREATE INDEX ZBTL_FROM_ZWIN_ID ON ZBTL(ZBTL_FROM_ZWIN_ID) TABLESPACE V8INDEX;

CREATE INDEX ZBTL_TO_ZWIN_ID ON ZBTL(ZBTL_TO_ZWIN_ID) TABLESPACE V8INDEX;

CREATE INDEX ZBTL_FROM_ZPOS_ID ON ZBTL(ZBTL_FROM_ZPOS_ID) TABLESPACE V8INDEX;

CREATE INDEX ZBTL_TO_ZPOS_ID ON ZBTL(ZBTL_TO_ZPOS_ID) TABLESPACE V8INDEX;

ALTER TABLE ZFAG ADD (ZFAG_DEPDATETYPE NUMBER(1));

COMMENT ON COLUMN ZFAG.ZFAG_TYPE IS '0 = Financial Account (Paymaster) | 1 = AR Revenue Charges | 2 = AR Payments | 3 = Foreign Currency Exchange | 4 = for Booth telephone charges paid cash | 5 = Internal Interface Type for POS Accounts or Lost Posting Account, these financial accounts are getting checked in automatically after check out. | 6 = Petty Cash accounts | 7 = Transponder reading | 8 = Deposits, Cancellation and NoShow | 9 = Personal (Staff) Accounts (TI) | 10 = type for ''Don''t close folio'' ';

ALTER TABLE ZFAG MODIFY ZFAG_TYPE NUMBER(2);

ALTER TABLE WSQP DROP UNIQUE(WSQP_SHORTDESC);

ALTER TABLE ZCCA ADD ZCCA_AUTH_VALID_DAYS NUMBER (3,0);	

COMMENT ON COLUMN ZCCA.ZCCA_AUTH_VALID_DAYS IS 'Validity Time Period (number of Days) of an old active Authorisation, 0 value indicate no timeframe is applicable ';

ALTER TABLE XCAP ADD XCAP_VALIDUNTIL DATE;

COMMENT ON COLUMN XCAP.XCAP_VALIDUNTIL IS 'The Validity End date';

CREATE TABLE TAPR
(
  TAPR_ID 		NUMBER (30,0) 	NOT NULL,
  TAPR_PRC_SYSDATE 	DATE 		NOT NULL,
  TAPR_PRC_DATETIME  	DATE 		NULL, 
  TAPR_BILLING NUMBER (1),
  CONSTRAINT TAPR_PK PRIMARY KEY (TAPR_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;  

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

COMMENT ON COLUMN TAPR.TAPR_PRC_SYSDATE IS 'Process systemdate';

COMMENT ON COLUMN TAPR.TAPR_PRC_DATETIME IS 'Process date and time';

COMMENT ON COLUMN TAPR.TAPR_BILLING IS 'Process is created by Billing Screen TAP Posting';

COMMENT ON COLUMN TACD.TACD_COMMTYPE IS 'null,0 = per department code, 1= per night, 2= per stay, 3=manual,  4=Prepaid';

CREATE TABLE TAPD
(
  TAPD_ID 		NUMBER (30,0) 	NOT NULL,
  TAPD_TAPR_ID 		NUMBER (30,0) 	NULL,
  TAPD_TACD_ID 		NUMBER (30,0) 	NULL,
  TAPD_VOID 		NUMBER (1)	NULL,
  TAPD_VOID_SYSDATE 	DATE 		NULL,
  TAPD_VOID_DATETIME 	DATE 		NULL,
  CONSTRAINT TAPD_PK PRIMARY KEY (TAPD_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;  

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

COMMENT ON TABLE TAPD IS 'TAP PROCESS LINK TABLE TO TAP COMMISSION DETAILS TO PROCESS TABLE';

COMMENT ON COLUMN TAPD.TAPD_VOID IS 'Rollback flag';

COMMENT ON COLUMN TAPD.TAPD_VOID_SYSDATE IS 'Rollback systemdate';

COMMENT ON COLUMN TAPD.TAPD_VOID_DATETIME IS 'Rollback date and time';

ALTER TABLE TACD ADD TACD_YRCH_ID NUMBER (30,0);

ALTER TABLE TACD ADD TACD_NOTE  VARCHAR (200);

COMMENT ON COLUMN TACD.TACD_NOTE IS 'REASON OF MANUAL COMMISSION AMOUNT';
 
ALTER TABLE TACC ADD TACC_WRPT_ID NUMBER(30,0);

COMMENT ON COLUMN TACC.TACC_WRPT_ID IS 'ACCOUNT CHEQUE REPORT';

ALTER TABLE TACO ADD TACO_ZFAC_ID NUMBER (30); 

COMMENT ON COLUMN TACO.TACO_ZFAC_ID IS 'Commissions calculated for FA';

ALTER TABLE TACD DROP COLUMN TACD_PAIDOFF_DATE;

ALTER TABLE TACD ADD TACD_DELETEDATE DATE;

COMMENT ON COLUMN TACD.TACD_DELETEDATE IS 'Datetime of TACD deletion flag';

ALTER TABLE TACD ADD TACD_CALCDATE DATE; 

COMMENT ON COLUMN TACD.TACD_CALCDATE IS 'Datetime of TACD record insertion';

COMMENT ON COLUMN TACD.TACD_PROCESSEDATCHECK IS 'Obsolete column, not used anymore';

CREATE INDEX TAPD_TAPR_ID ON TAPD(TAPD_TAPR_ID) TABLESPACE V8INDEX; 

CREATE INDEX TAPD_TACD_ID ON TAPD(TAPD_TACD_ID) TABLESPACE V8INDEX; 

CREATE INDEX TACD_YRCH_ID ON TACD(TACD_YRCH_ID) TABLESPACE V8INDEX;

CREATE INDEX TACC_WRPT_ID ON TACC(TACC_WRPT_ID) TABLESPACE V8INDEX;

CREATE INDEX TACO_ZFAC_ID ON TACO(TACO_ZFAC_ID) TABLESPACE V8INDEX; 

ALTER TABLE WCOL ADD WCOL_FONTCOLOR NUMBER(30); 

COMMENT ON COLUMN XCMS.XCMS_AUTOMERGEERRORID IS '0= error 1=merged to other profile remotely 2=profile was not uploaded because it has a match point higher than configured max match point 3=unique xmnr conflict 4=manual merge needed';

CREATE TABLE WPTS (
    WPTS_ID NUMBER(30) NOT NULL
   ,WPTS_TIMESTAMP DATE NOT NULL
   ,WPTS_GLOBALID NUMBER(30) NULL
   ,CONSTRAINT WPTS_PK PRIMARY KEY (WPTS_ID) USING INDEX TABLESPACE V8INDEX  ) TABLESPACE V8MAIN ;   

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

CREATE TABLE WDBG (
    wdbg_id number(30) NOT NULL
   ,wdbg_timestamp date NOT NULL
   ,wdbg_wpca_id number(30) NULL
   ,wdbg_smalltext varchar2(200) NULL
   ,wdbg_bigtext long NULL
   ,CONSTRAINT WDBG_PK PRIMARY KEY (WDBG_ID) USING INDEX TABLESPACE V8INDEX  ) TABLESPACE V8MAIN ;   

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

ALTER TABLE ZTRA ADD ZTRA_LOCKERNUMBER VARCHAR2(30);

ALTER TABLE XPMR ADD XPMR_DUPLICATE NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN XPMR.XPMR_DUPLICATE IS '0=ignore  1=copy';
   
ALTER TABLE XCMS ADD XCMS_NO_HC_TRANSFER NUMBER (1);

COMMENT ON COLUMN XCMS.XCMS_NO_HC_TRANSFER IS 'Dont Upload and Synchronize this in HC installation';

ALTER TABLE ZDGT ADD (ZDGT_BESTPRICEROLE NUMBER(3)); 

COMMENT ON COLUMN ZDGT.ZDGT_BESTPRICEROLE IS 'defines a grouping for best price finding';

ALTER TABLE ZFAC ADD (ZFAC_CURRENT_PTIK_ID NUMBER(30));

ALTER TABLE ZTRM DROP COLUMN ZTRM_DEF_POSTICKETTYPE;

ALTER TABLE ZTRM DROP COLUMN ZTRM_DEF_ZDGT_ID;

ALTER TABLE ZTRM ADD (ZTRM_DEF_PTIK_ID NUMBER(30));

ALTER TABLE ZTRC DROP COLUMN ZTRC_DEF_POSTICKETTYPE;

ALTER TABLE ZTRC DROP COLUMN ZTRC_DEF_ZDGT_ID;

ALTER TABLE ZTRC ADD (ZTRC_DEF_PTIK_ID NUMBER(30));

ALTER TABLE ZTAK ADD (ZTAK_PTIK_ID NUMBER(30));

ALTER TABLE ZTAA ADD (ZTAA_PTIK_ID NUMBER(30));

CREATE TABLE PTLG (
  ptlg_id number(30) not null ,
  ptlg_timestamp date null,
  ptlg_user_xcms_id number(30),
  ptlg_zfac_id number(30),
  ptlg_ptik_id number(30),
  ptlg_ppro_id number(30), 
  ptlg_pric_id number(30),
  ptlg_psac_id number(30),
  ptlg_pcad_id number(30),
  ptlg_postedamount number(30,4),
  ptlg_postingdate date,
  ptlg_zpos_id number(30),
  ptlg_type number(1),
  CONSTRAINT PTLG_PK PRIMARY KEY (PTLG_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

COMMENT ON COLUMN PTLG.PTLG_TYPE IS '1-checkin, 2-Movement, 3-checkout';

CREATE INDEX IPTLG_ID ON PTLG(PTLG_ID) TABLESPACE V8INDEX; 

CREATE INDEX IPTLG_ZFAC_ID ON PTLG(PTLG_ZFAC_ID) TABLESPACE V8INDEX; 

CREATE INDEX IPTLG_PTIK_ID ON PTLG(PTLG_PTIK_ID) TABLESPACE V8INDEX; 

CREATE INDEX IPTLG_PPRO_ID ON PTLG(PTLG_PPRO_ID) TABLESPACE V8INDEX; 

CREATE INDEX IPTLG_PRIC_ID ON PTLG(PTLG_PRIC_ID) TABLESPACE V8INDEX; 

CREATE INDEX IPTLG_PSAC_ID ON PTLG(PTLG_PSAC_ID) TABLESPACE V8INDEX; 

CREATE INDEX IPTLG_PCAD_ID ON PTLG(PTLG_PCAD_ID) TABLESPACE V8INDEX; 

CREATE INDEX IPTLG_ZPOS_ID ON PTLG(PTLG_ZPOS_ID) TABLESPACE V8INDEX;

CREATE INDEX IPTLG_TIMESTAMP ON PTLG(PTLG_TIMESTAMP) TABLESPACE V8INDEX; 
 
CREATE TABLE PTIK (
  ptik_id number(30) not null ,
  ptik_shortdesc varchar2(10) null,
  ptik_longdesc varchar2(60) null,
  ptik_inactive number(1),
  ptik_wcol_id number(30),
  ptik_displorder number(5),
  ptik_zdgt_id number(30) not null,
  CONSTRAINT PTIK_PK PRIMARY KEY (PTIK_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

COMMENT ON TABLE PTIK IS 'Ticket Configuration Header';

CREATE INDEX IPTIK_WCOL_ID ON PTIK(PTIK_WCOL_ID) TABLESPACE V8INDEX;

CREATE TABLE PTPL (
  ptpl_id number(30) not null ,
  ptpl_fromdate date not null,
  ptpl_todate date not null,
  ptpl_ppro_id number(30),
  ptpl_ptik_id number(30),
  CONSTRAINT PTPL_PK PRIMARY KEY (PTPL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

COMMENT ON TABLE PTPL IS 'Ticket Header - Ticket Detail Link';

CREATE INDEX IPTPL_PPRO_ID ON PTPL(PTPL_PPRO_ID) TABLESPACE V8INDEX;

CREATE INDEX IPTPL_PTIK_ID ON PTPL(PTPL_PTIK_ID) TABLESPACE V8INDEX;

CREATE TABLE PTSQ (
  ptsq_id number(30) not null ,
  ptsq_card varchar2(6) null,
  ptsq_psac_id number(30) not null,
  ptsq_zfac_id number(30) not null,
  ptsq_xcms_id number(30) null,
  ptsq_thirdpartydate date not null,
  ptsq_received date not null,
  ptsq_processed date null,
  CONSTRAINT PTSQ_PK PRIMARY KEY (PTSQ_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

COMMENT ON TABLE PTSQ IS 'Processing queue for TI IFC';

CREATE INDEX IPTSQ_PSAC_ID ON PTSQ(PTSQ_PSAC_ID) TABLESPACE V8INDEX;

CREATE INDEX IPTSQ_ZFAC_ID ON PTSQ(PTSQ_ZFAC_ID) TABLESPACE V8INDEX;

CREATE INDEX IPTSQ_PROCESSED ON PTSQ(PTSQ_PROCESSED) TABLESPACE V8INDEX;

CREATE TABLE PHOL (
  phol_id number(30)  not null ,
  phol_longdesc varchar2(200) null,
  phol_day number(2),
  phol_month number(2),
  constraint pholcheck_day   check (phol_day between 1 and 31),
  constraint pholcheck_month check (phol_month between 1 and 12),
  CONSTRAINT PHOL_PK PRIMARY KEY (PHOL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

CREATE TABLE PSEC (
  psec_id number(30)  not null ,
  psec_shortdesc varchar2(3) null,
  psec_longdesc varchar2(30) null,
  psec_globalarea number(1),
  psec_inactive number(1),
  psec_displorder number(5) null,
  psec_autoout number(1),
  psec_autooutminutes number(4),
  CONSTRAINT PSEC_PK PRIMARY KEY (PSEC_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

CREATE TABLE PPRO (
  ppro_id number(30)  not null ,
  ppro_shortdesc varchar2(3) null,
  ppro_longdesc varchar2(200) null,
  ppro_displorder number(5) null,
  ppro_inactive number(1) null,
  ppro_validfrom date null,
  ppro_validuntil date null,
  ppro_wcol_id number(30) null,
  CONSTRAINT PPRO_PK PRIMARY KEY (PPRO_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

CREATE INDEX IPPRO_WCOL_ID ON PPRO(PPRO_WCOL_ID) TABLESPACE V8INDEX;

CREATE TABLE PDSC (
  pdsc_id number(30)  not null ,
  pdsc_shortdesc varchar2(3) null,
  pdsc_longdesc varchar2(30) null,
  pdsc_inactive number(1) null,
  CONSTRAINT PDSC_PK PRIMARY KEY (PDSC_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

CREATE TABLE PDSP (
  pdsp_id number(30)  not null ,
  pdsp_percentage number(30)  null,
  pdsp_psec_id number(30)  null,
  pdsp_flatamount number(30,4) null,
  pdsp_type number(1) null,
  CONSTRAINT PDSP_PK PRIMARY KEY (PDSP_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

CREATE TABLE PRIC (
  pric_id number(30)  not null ,
  pric_shortdesc varchar2(3) null,
  pric_longdesc varchar2(30) null,
  pric_foliotext varchar2(30) null,
  pric_psec_id number(30)  null,
  pric_immediatepost number(1) null,
  pric_graceminutes number(3) null,
  pric_displorder number(5) null,
  CONSTRAINT PRIC_PK PRIMARY KEY (PRIC_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

CREATE INDEX PRIC_FKINDEX1 ON PRIC(PRIC_PSEC_ID) TABLESPACE V8INDEX;

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

CREATE TABLE PSAC (
  psac_id number(30)  not null ,
  psac_in_psec_id number(30)  null,
  psac_out_psec_id number(30) not null,
  psac_shortdesc varchar2(3) null,
  psac_longdesc varchar2(100) null,
  psac_externalid varchar2(200) null,
  psac_displorder number(5)  null,
  CONSTRAINT PSAC_PK PRIMARY KEY (PSAC_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

CREATE INDEX PSAC_FKINDEX1 ON PSAC(PSAC_IN_PSEC_ID) TABLESPACE V8INDEX;

CREATE INDEX PSAC_FKINDEX2 ON PSAC(PSAC_OUT_PSEC_ID) TABLESPACE V8INDEX;

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

CREATE TABLE PDLP (
  pdlp_id number(30)  not null ,
  pdlp_pdsc_id number(30)  null,
  pdlp_pdsp_id number(30)  null,
  pdlp_fromdate date null,
  pdlp_todate date null,
  CONSTRAINT PDLP_PK PRIMARY KEY (PDLP_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

CREATE INDEX PDLP_FKINDEX1 ON PDLP(PDLP_PDSC_ID) TABLESPACE V8INDEX;

CREATE INDEX PDLP_FKINDEX2 ON PDLP(PDLP_PDSP_ID) TABLESPACE V8INDEX;

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

CREATE TABLE PPRL (
  pprl_id number(30)  not null ,
  pprl_ppro_id number(30)  null,
  pprl_pric_id number(30)  null,
  CONSTRAINT PPRL_PK PRIMARY KEY (PPRL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

CREATE INDEX PPRL_FKINDEX1 ON PPRL(PPRL_PPRO_ID) TABLESPACE V8INDEX;

CREATE INDEX PPRL_FKINDEX2 ON PPRL(PPRL_PRIC_ID) TABLESPACE V8INDEX;

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

CREATE TABLE PCAL (
  pcal_id number(30)  not null ,
  pcal_pric_id number(30)  null,
  pcal_daytype number(2) null,
  pcal_timefrom date null,
  pcal_timeto date null,
  CONSTRAINT PCAL_PK PRIMARY KEY (PCAL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

CREATE INDEX PCAL_FKINDEX1 ON PCAL(PCAL_PRIC_ID) TABLESPACE V8INDEX;

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

CREATE TABLE PCAD (
  pcad_id number(30)  not null ,
  pcad_pcal_id number(30)  null,
  pcad_price number(30,4) null,
  pcad_minutes number(30)  null,
  pcad_zdco_id number(30)  null,
  CONSTRAINT PCAD_PK PRIMARY KEY (PCAD_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

CREATE INDEX PCAD_FKINDEX1 ON PCAD(PCAD_PCAL_ID) TABLESPACE V8INDEX;

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

CREATE INDEX PCAD_FKINDEX2 ON PCAD(PCAD_ZDCO_ID) TABLESPACE V8INDEX;

CREATE TABLE PMKL (
  pmkl_id number(30)  not null ,
  pmkl_zfac_id number(30)  null,
  pmkl_timestamp date  null,
  CONSTRAINT PMKL_PK PRIMARY KEY (PMKL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

CREATE INDEX PMKL_FKINDEX2 ON PMKL(PMKL_ZFAC_ID) TABLESPACE V8INDEX;

DELETE FROM XPMR WHERE XPMR_TYPE<>1;

ALTER TABLE XPMR DROP COLUMN XPMR_TYPE;

ALTER TABLE YCAT ADD YCAT_DISABLEMAKEKEY NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YCAT.YCAT_DISABLEMAKEKEY IS '1=no make key screen comes up when check-in';

ALTER TABLE PTLG ADD PTLG_TRIGGERGENERATED NUMBER(1);

ALTER TABLE XPRM MODIFY XPRM_DISPLORDER NUMBER(5);

ALTER TABLE SMBD MODIFY SMBD_ADULTS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_CHILDS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_ROOMS_OCC_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_ROOMS_OOO_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_WALKIN_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_ARR_ROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_DEP_ROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_ARR_ADULTS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_ARR_CHILDS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_DEP_ADULTS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_DEP_CHILDS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_COMPROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_HOUSEUSEROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_REPEATGUESTARRROOM_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_RESMADETODAY_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_RESCANCELEDTODAY_Y	NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_COMPANY_ROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_AGENT_ROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_IND_ROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_VIPADULTS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_CANCFORTODAY_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_DEPNOTPAID_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_NOSHOWROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_DAYUSEROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_MAXBEDSINHOTEL_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_SOURCE_ROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_BOOKER_ROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_VENDOR_ROOMS_Y NUMBER(7);

ALTER TABLE SMBD MODIFY SMBD_MPLANER_ROOMS_Y NUMBER(7);

DELETE FROM WDTC;

ALTER TABLE PSEC ADD PSEC_AUTO_ERRORENTRY NUMBER(2);

ALTER TABLE MCFG ADD (MCFG_HTTP_UPLOAD_INTERVAL Number(5) Null);

COMMENT ON COLUMN MCFG.MCFG_HTTP_UPLOAD_INTERVAL IS 'Time interval for trigger upload';

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

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

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

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

COMMENT ON COLUMN MMOD.MMOD_DL_REQ_BACKUP IS 'Indicates whether download requests should be backed up';

COMMENT ON COLUMN MMOD.MMOD_DL_RESP_BACKUP IS 'Indicates whether download responses should be backed up. If yes only those indicating not success';

COMMENT ON COLUMN MMOD.MMOD_UL_REQ_BACKUP IS 'Indicates whether the V8 trigger xml should be backed up';

COMMENT ON COLUMN MMOD.MMOD_UL_RESP_BACKUP IS 'Indicates whether upload messages should be backed up';

CREATE TABLE RPRF
(
  RPRF_ID       NUMBER(30)                      NOT NULL, CONSTRAINT RPRF_PK PRIMARY KEY (RPRF_ID) USING INDEX TABLESPACE V8INDEX,
  RPRF_RRES_ID  NUMBER(30)                      NOT NULL,
  RPRF_XCMS_ID  NUMBER(30)                      NOT NULL
)
TABLESPACE V8MAIN;

COMMENT ON TABLE RPRF IS 'Profiles linked to the table reservations.';

COMMENT ON COLUMN RPRF.RPRF_ID IS 'Link ID';

COMMENT ON COLUMN RPRF.RPRF_RRES_ID IS 'Table reservation ID';

COMMENT ON COLUMN RPRF.RPRF_XCMS_ID IS 'Profile ID';

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

CREATE UNIQUE INDEX RPRF_U01 ON RPRF (RPRF_RRES_ID, RPRF_XCMS_ID) TABLESPACE V8INDEX;

INSERT INTO RPRF (RPRF_ID, RPRF_RRES_ID, RPRF_XCMS_ID) (SELECT SEQ_RPRF.NEXTVAL, RRES_ID, RRES_XCMS_ID FROM RRES);

ALTER TABLE RRES DROP COLUMN RRES_XCMS_ID;

CREATE TABLE WDES
(
  WDES_ID           NUMBER(30)                  NOT NULL, CONSTRAINT WDES_PK PRIMARY KEY (WDES_ID) USING INDEX TABLESPACE V8INDEX,
  WDES_REPORT_ROLE  NUMBER(2)                   NOT NULL, CONSTRAINT WDES_REPORT_ROLE UNIQUE (WDES_REPORT_ROLE) USING INDEX TABLESPACE V8INDEX,
  WDES_WRPT_ID      NUMBER(30)                  NOT NULL
)
TABLESPACE V8MAIN;

COMMENT ON TABLE WDES IS 'Default email subjects per report type';

COMMENT ON COLUMN WDES.WDES_REPORT_ROLE IS 'Report role';

COMMENT ON COLUMN WDES.WDES_WRPT_ID IS 'Reference to the report';

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

ALTER TABLE ZRDF MODIFY ZRDF_SHORTDESC VARCHAR2(10);

ALTER TABLE ZRDF ADD ZRDF_WCOL_ID NUMBER(30);

ALTER TABLE XCOU ADD CONSTRAINT XCOU_CUSTSHORTDESC UNIQUE (XCOU_CUSTSHORTDESC) USING INDEX TABLESPACE V8INDEX;

UPDATE XCOU SET XCOU_CUSTSHORTDESC=XCOU_ISO2 WHERE XCOU_CUSTSHORTDESC IS NULL;

ALTER TABLE XCOU MODIFY XCOU_CUSTSHORTDESC NOT NULL;

ALTER TABLE YHRD ADD (YHRD_DELTA NUMBER(30,4));

COMMENT ON COLUMN YHRD.YHRD_DELTA IS 'variation of the hurdle rate each time a reservation is made';

ALTER TABLE YHRD ADD (YHRD_MAXHURDLEAMOUNT NUMBER(30,4));

COMMENT ON COLUMN YHRD.YHRD_MAXHURDLEAMOUNT IS 'maximal value of the hurdle rate';

ALTER TABLE YHRD ADD (YHRD_ORIGIN NUMBER(1) DEFAULT 0);

COMMENT ON COLUMN YHRD.YHRD_ORIGIN IS 'System originated this record. 0- TLP 1-Hurdle rates XML Interface';

COMMENT ON COLUMN YOVB.YOVB_ORIGIN IS ' Overbooking source: 0=V8 User, 1=TLP  2=XML Interface';

ALTER TABLE YFVT ADD YFVT_WATY_ID NUMBER(30);

ALTER TABLE YFVT ADD YFVT_WATY_ACTION NUMBER(1);

COMMENT ON COLUMN YFVT.YFVT_WATY_ACTION IS '0.New Task  1. Task resolved';

ALTER TABLE WNAL ADD WNAL_FIDELIODATE DATE;

ALTER TABLE WLCK ADD WLCK_WSES_ID NUMBER(30);

ALTER TABLE PTLG ADD PTLG_FIDELIODATE DATE NULL;

UPDATE ZPOS SET ZPOS_ZFAC_ID=NULL WHERE ZPOS_ZFAC_ID=0;

UPDATE ZPOS SET ZPOS_YRES_ID=NULL WHERE ZPOS_YRES_ID=0;

CREATE TABLE XRPC (
   xrpc_id NUMBER(30) NOT NULL
  ,xrpc_xcms_id NUMBER(30) NOT NULL
  ,xrpc_time DATE NOT NULL
  ,xrpc_hasdifference NUMBER(1) NOT NULL 
  ,xrpc_log VARCHAR2(2000) 
  ,CONSTRAINT XRPC_PK PRIMARY KEY (XRPC_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE v8main;

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

COMMENT ON COLUMN XRPC.XRPC_HASDIFFERENCE IS '0=no   1=yes';

CREATE INDEX XRPC_XCMS_ID ON XRPC(XRPC_XCMS_ID) TABLESPACE V8INDEX; 

CREATE INDEX XRPC_TIME ON XRPC(XRPC_TIME) TABLESPACE V8INDEX; 
