--UPDATE FROM 8.9.0.4 TO 8.9.0.5


DROP TABLE VERSION_8_9_0_4;



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

UPDATE XCAC SET XCAC_XGCA_ID = 0 WHERE XCAC_XGCA_ID IS NULL;

ALTER TABLE XCAC MODIFY(XCAC_XGCA_ID  NOT NULL);

ALTER TABLE XCAC MODIFY(XCAC_XGCA_ID  DEFAULT 0);

UPDATE YRCH SET YRCH_XGCA_ID = 0 WHERE YRCH_XGCA_ID IS NULL;

ALTER TABLE YRCH MODIFY(YRCH_XGCA_ID  NOT NULL);

ALTER TABLE YRCH MODIFY(YRCH_XGCA_ID  DEFAULT 0);

ALTER TABLE MWSV MODIFY MWSV_LASTREQUEST TIMESTAMP(6);

ALTER TABLE MURL MODIFY MURL_LASTREQUEST TIMESTAMP(6);

ALTER TABLE XPHN ADD XPHN_WPCA_ID NUMBER(30);

ALTER TABLE XPHN ADD XPHN_XCMT_ID NUMBER(30);

UPDATE SHIS SET SHIS_IMPORTED=0 WHERE SHIS_IMPORTED IS NULL;

ALTER TABLE SHIS MODIFY(SHIS_IMPORTED DEFAULT 0);

ALTER TABLE SHIS MODIFY(SHIS_IMPORTED  NOT NULL);

UPDATE SFOR SET SFOR_IMPORTED=0 WHERE SFOR_IMPORTED IS NULL;

ALTER TABLE SFOR MODIFY(SFOR_IMPORTED DEFAULT 0);

ALTER TABLE SFOR MODIFY(SFOR_IMPORTED  NOT NULL);

UPDATE SMAN SET SMAN_IMPORTED=0 WHERE SMAN_IMPORTED IS NULL;

ALTER TABLE SMAN MODIFY(SMAN_IMPORTED DEFAULT 0);

ALTER TABLE SMAN MODIFY(SMAN_IMPORTED  NOT NULL);

UPDATE SRPD SET SRPD_IMPORTED=0 WHERE SRPD_IMPORTED IS NULL;

ALTER TABLE SRPD MODIFY(SRPD_IMPORTED DEFAULT 0);

ALTER TABLE SRPD MODIFY(SRPD_IMPORTED  NOT NULL);

UPDATE SAAR SET SAAR_IMPORTED=0 WHERE SAAR_IMPORTED IS NULL;

ALTER TABLE SAAR MODIFY(SAAR_IMPORTED DEFAULT 0);

ALTER TABLE SAAR MODIFY(SAAR_IMPORTED  NOT NULL);

UPDATE SBUD SET SBUD_IMPORTED=0 WHERE SBUD_IMPORTED IS NULL;

ALTER TABLE SBUD MODIFY(SBUD_IMPORTED DEFAULT 0);

ALTER TABLE SBUD MODIFY(SBUD_IMPORTED  NOT NULL);

UPDATE SMBD SET SMBD_IMPORTED=0 WHERE SMBD_IMPORTED IS NULL;

ALTER TABLE SMBD MODIFY(SMBD_IMPORTED DEFAULT 0);

ALTER TABLE SMBD MODIFY(SMBD_IMPORTED  NOT NULL);

CREATE TABLE YRDE (
  YRDE_ID NUMBER(30) NOT NULL
  ,YRDE_YRDD_ID NUMBER(30) NOT NULL
  ,YRDE_STARTDATE DATE 
  ,YRDE_ENDDATE DATE 
  ,CONSTRAINT YRDE_PK PRIMARY KEY (YRDE_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;
  
CREATE SEQUENCE SEQ_YRDE INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE YRDE IS 'Day type schema date range details';

COMMENT ON COLUMN YRDE.YRDE_STARTDATE IS 'Daytype detail start date, if empty valid for the past';

COMMENT ON COLUMN YRDE.YRDE_ENDDATE IS 'Daytype detail end date, if empty valid for the future';

ALTER TABLE YRDY ADD YRDY_YRDE_ID NUMBER(30);
  
INSERT INTO YRDE (YRDE_ID, YRDE_YRDD_ID) (SELECT YRDD_ID, YRDD_ID FROM YRDD);

UPDATE YRDY SET YRDY_YRDE_ID =YRDY_YRDD_ID;

COMMENT ON COLUMN YRDY.YRDY_YRDD_ID IS 'Obsolete column, not used anymore';  

CREATE TABLE WSET(
WSET_ID NUMBER(30) NOT NULL
,WSET_NAME VARCHAR2(60) 
,WSET_SERIALNUMBER VARCHAR2(20) 
,WSET_PORT NUMBER(1)
,WSET_PARITY NUMBER(1)
,WSET_BAUDRATE NUMBER(30)
,WSET_TYPE VARCHAR2(1)
,WSET_TIMEOUT NUMBER(4)
,WSET_MINDELAY NUMBER(4)
,WSET_MAXDELAY NUMBER(4)
,WSET_SWVERSION VARCHAR2(10)
,CONSTRAINT WSET_PK PRIMARY KEY (WSET_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE WSET IS 'Swedish E Tax Control Units';

COMMENT ON COLUMN WSET.WSET_NAME IS 'Unique Name';

COMMENT ON COLUMN WSET.WSET_SERIALNUMBER IS 'Control Unit Serial Number';

COMMENT ON COLUMN WSET.WSET_BAUDRATE IS 'Baud Rate ';

COMMENT ON COLUMN WSET.WSET_PARITY IS 'Parity 0.None, 1.ODD, 2.EVEN';

COMMENT ON COLUMN WSET.WSET_TYPE IS 'TYPE A or TYPE B';

COMMENT ON COLUMN WSET.WSET_TIMEOUT IS 'Time out in Seconds';

COMMENT ON COLUMN WSET.WSET_SWVERSION IS 'ETax Software version';

COMMENT ON COLUMN WSET.WSET_MINDELAY IS 'Minimum Delay (seconds) between two consecutive Bytes';

COMMENT ON COLUMN WSET.WSET_MINDELAY IS 'Maximum Delay (seconds) between two consecutive Bytes';

ALTER TABLE WPCA ADD WPCA_WSET_ID NUMBER(30);

COMMENT ON COLUMN WPCA.WPCA_WSET_ID IS 'linked Swedish E Tax Control Unit' ;

ALTER TABLE WPCA ADD WPCA_ETAX_REGISTERID VARCHAR2(16);

COMMENT ON COLUMN WPCA.WPCA_ETAX_REGISTERID IS 'ETAX cash register id of the terminal' ;

ALTER TABLE ZINV MODIFY ZINV_FISCALNUMBER VARCHAR2(100);

ALTER TABLE ZDCO ADD ZDCO_DISABLE_FISCALPRINTER NUMBER(1);

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 ... to be continued for special numbers';

CREATE TABLE CORD
(
  CORD_ID                 NUMBER(30)            NOT NULL,
  CORD_ORDERNAME          VARCHAR2(100),  
  CORD_DESCRIPTION        VARCHAR2(4000),
  CORD_TEXT               VARCHAR2(1000),
  CORD_ISSUE              DATE,
  CORD_VALIDFROM          DATE,
  CORD_VALIDUNTIL         DATE,
  CORD_XCMS_ID            NUMBER(30),
  CORD_QUANTITY           NUMBER(30)          NOT NULL,
  CORD_CART_ID            NUMBER(30),
  CORD_ZFAC_ID            NUMBER(30),
  CORD_FINALIZED          NUMBER(1),
  CORD_DEDICATION         VARCHAR2(4000),
  CORD_CONSUMING_XCMS_ID  NUMBER(30),
  CORD_INTERNAL_INFO      VARCHAR2(1000),
  CORD_ORDER_REASON       VARCHAR2(100)
  ,CONSTRAINT CORD_PK PRIMARY KEY (CORD_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE CORD IS 'Voucher orders';

COMMENT ON COLUMN CORD.CORD_CONSUMING_XCMS_ID IS 'Consuming Guest';

COMMENT ON COLUMN CORD.CORD_VALIDUNTIL IS 'End of date where the gift certificate is valid ';

COMMENT ON COLUMN CORD.CORD_XCMS_ID IS 'Ordering Profile';

COMMENT ON COLUMN CORD.CORD_QUANTITY IS 'Number of vouchers';

COMMENT ON COLUMN CORD.CORD_CART_ID IS 'Article Link';

COMMENT ON COLUMN CORD.CORD_DESCRIPTION IS 'gift certificate description';

COMMENT ON COLUMN CORD.CORD_TEXT IS 'Text for the gift certificate ';

COMMENT ON COLUMN CORD.CORD_ISSUE IS 'Date of issue of the gift certificate ';

COMMENT ON COLUMN CORD.CORD_VALIDFROM IS 'Start of date where the gift certificate is valid ';

ALTER TABLE YPAC ADD YPAC_BOARDTYPE NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YPAC.YPAC_BOARDTYPE IS '0=NO BOARD 	1=BREAKFAST 	2=BREAKFAST+LUNCH 	3=BREAKFAST+DINNER 	4=FULL BOARD 	5=ALL INCLUSIVE (same as yrch_boardtype so far) 6=only lunch 7=only dinner';

CREATE TABLE YBLS (
  YBLS_ID NUMBER(30) NOT NULL
  ,YBLS_YBLD_ID NUMBER(30) NOT NULL
  ,YBLS_OCCUPANCYSPLIT NUMBER(5) NOT NULL
  ,YBLS_ADULTNO NUMBER(5)
  ,CONSTRAINT YBLS_PK PRIMARY KEY (YBLS_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;
  
CREATE SEQUENCE SEQ_YBLS INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE YBLS IS 'Manual occupancy split per block detail';

ALTER TABLE COUP ADD COUP_CORD_ID NUMBER(30);

COMMENT ON COLUMN COUP.COUP_CORD_ID IS 'Link to the Voucher Order';

ALTER TABLE CORD ADD CORD_YRES_ID NUMBER(30);

COMMENT ON COLUMN CORD.CORD_YRES_ID IS 'Reservation link for Arrangement vouchers';

ALTER TABLE CORD ADD CORD_AMOUNT NUMBER(30, 4);

ALTER TABLE ZINV ADD ZINV_CITYLEDGERNUMBER VARCHAR2(60 CHAR);

COMMENT ON COLUMN ZINV.ZINV_CITYLEDGERNUMBER IS 'The city ledger folio number' ;

CREATE TABLE YBSS (
   YBSS_ID NUMBER(30)
  ,YBSS_YBLH_ID NUMBER(30)
  ,YBSS_YBLD_ID NUMBER(30)
  ,YBSS_YBLS_ID NUMBER(30)
  ,YBSS_YCAT_ID NUMBER(30) 
  ,YBSS_DATE DATE
  ,YBSS_ZDCO_ID NUMBER(30)
  ,YBSS_UNITPRICE NUMBER(30,4)
  ,YBSS_QUANTITY NUMBER(10)
  ,YBSS_TAXLINK_ID NUMBER(30)
  ,YBSS_INTERNALSPLIT_ID NUMBER(30)
  ,YBSS_DT NUMBER(1)  -- 1 = DEBIT     2 = TAX
  ,YBSS_TYPE NUMBER(1) --0=NORMAL POSTINGS 1=FIXED CHARGES(NOT USED FOR BLOCKS ONLY FOR YPOS) 2=PACKAGE RELATED POSTINGS
  ,YBSS_ADULTNO NUMBER(3)
  ,YBSS_OCCUPANCYSPLIT NUMBER(3)  
  ,CONSTRAINT YBSS_PK PRIMARY KEY (YBSS_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE YBSS IS 'Block split forecast';
