--UPDATE FROM 8.7.0.2 TO 8.7.1.0


DROP TABLE VERSION_8_7_0_2;



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



ALTER TABLE XPDC ADD (XPDC_ISSUE_XSTA_ID NUMBER(30), XPDC_BORN_XSTA_ID NUMBER(30));

COMMENT ON COLUMN XPDC.XPDC_ISSUE_XSTA_ID IS 'State of issue'; 

COMMENT ON COLUMN XPDC.XPDC_BORN_XSTA_ID IS 'State of birth';

ALTER TABLE WMAI ADD (WMAI_READONLY NUMBER(1) DEFAULT 0); 

CREATE TABLE WTWN
(
  WTWN_ID    NUMBER(30) NOT NULL,
  WTWN_CODE  VARCHAR2(9) NOT NULL,
  WTWN_TOWN  VARCHAR2(40) NOT NULL,
  CONSTRAINT WTWN_PK PRIMARY KEY (WTWN_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE WTWN IS 'contains town and code for italian police export';

COMMENT ON COLUMN WTWN.WTWN_ID IS 'wtwn_id identifies wtwn';

COMMENT ON COLUMN WTWN.WTWN_CODE IS 'wtwn_code is the standard police code';

COMMENT ON COLUMN WTWN.WTWN_TOWN IS 'wtwn_town is the name of the town';

ALTER TABLE XPDC ADD (XPDC_ISSUE_WTWN_ID NUMBER(30), XPDC_BORN_WTWN_ID NUMBER(30));

ALTER TABLE WPHS ADD (WPHS_ALTERNATEURL VARCHAR(200));

COMMENT ON COLUMN WPHS.WPHS_ALTERNATEURL IS 'Alternate hotel URL'; 

CREATE TABLE ZLOG (
    ZLOG_ID NUMBER(30) NOT NULL
   ,ZLOG_ZPOS_ID NUMBER(30) NOT NULL 
   ,ZLOG_WPCA_ID NUMBER(30) NOT NULL 
   ,ZLOG_XCED_ID NUMBER(30) NOT NULL 
   ,ZLOG_PCDATE DATE NOT NULL 
   ,ZLOG_DATE DATE NOT NULL 
   ,ZLOG_TEXT VARCHAR2(200) 
   ,ZLOG_TYPE NUMBER(2)
   ,ZLOG_FROM_ZWIN_ID NUMBER(30)
   ,ZLOG_TO_ZWIN_ID NUMBER(30)
   ,ZLOG_2_ZPOS_ID NUMBER(30)
   ,CONSTRAINT ZLOG_PK PRIMARY KEY (ZLOG_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN ;
   
CREATE SEQUENCE SEQ_ZLOG INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE ZLOG IS 'ZPOS Log';

COMMENT ON COLUMN ZLOG.ZLOG_FROM_ZWIN_ID IS 'Needed for transfers: The source window';

COMMENT ON COLUMN ZLOG.ZLOG_TO_ZWIN_ID IS 'Needed for transfers: The target window';

COMMENT ON COLUMN ZLOG.ZLOG_TYPE IS '0-transfer, 1-negative posting, 2-posting split, 3-posting delete, 4-posting correction, 5-rebate posting, 6-posting adjustment';

COMMENT ON COLUMN ZLOG.ZLOG_2_ZPOS_ID IS 'used for rebates or adjustments';

CREATE INDEX ZLOG_ZPOS_ID ON ZLOG(ZLOG_ZPOS_ID) TABLESPACE V8INDEX; 

CREATE INDEX ZLOG_FROM_ZWIN_ID ON ZLOG(ZLOG_FROM_ZWIN_ID) TABLESPACE V8INDEX; 

CREATE INDEX ZLOG_TO_ZWIN_ID ON ZLOG(ZLOG_TO_ZWIN_ID) TABLESPACE V8INDEX; 

CREATE INDEX ZLOG_2_ZPOS_ID ON ZLOG(ZLOG_2_ZPOS_ID) TABLESPACE V8INDEX; 

ALTER TABLE XADR ADD (XADR_WTWN_ID NUMBER(30));

UPDATE WTWN SET WTWN_TOWN=TRIM(WTWN_TOWN);

ALTER TABLE YRPL ADD YRPL_AGE NUMBER(3);

UPDATE XCID SET XCID_FAMILYMAILING=0;

UPDATE XCID SET XCID_FAMILYMAILING=1 
WHERE XCID_ID IN 
(SELECT XIIL_FROM_XCMS_ID 
 FROM XIIL, XCLR
 WHERE XIIL_XCLR_ID=XCLR_ID AND XCLR_TYPE IN (1,2,3,4, 5) 
 AND XIIL_FROM_XCMS_ID NOT IN (SELECT XIIL_TO_XCMS_ID FROM XIIL));

ALTER TABLE WFTR MODIFY WFTR_VALUE VARCHAR2(2000);

ALTER TABLE YCIT MODIFY (YCIT_DISPLORDER NUMBER(4));

ALTER TABLE YCMI MODIFY (YCMI_DISPLORDER NUMBER(4));

CREATE TABLE RRER (
    RRER_ID NUMBER(30) NOT NULL
   ,RRER_RRES_ID NUMBER(30) NOT NULL
   ,RRER_YRES_ID VARCHAR(30) NOT NULL
   ,CONSTRAINT RRER_PK PRIMARY KEY (RRER_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

COMMENT ON TABLE RRER IS 'Link table reservations to the PMS reservations';

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

COMMENT ON COLUMN RRER.RRER_ID IS 'Primary key';

COMMENT ON COLUMN RRER.RRER_RRES_ID IS 'Link to the table reservation';

COMMENT ON COLUMN RRER.RRER_YRES_ID IS 'Link to the PMS reservation';

CREATE INDEX RRER_RRES_ID ON RRER(RRER_RRES_ID) TABLESPACE V8INDEX;

CREATE INDEX RRER_YRES_ID ON RRER(RRER_YRES_ID) TABLESPACE V8INDEX;

CREATE UNIQUE INDEX RRER_YRES_RRES ON RRER(RRER_RRES_ID, RRER_YRES_ID) TABLESPACE V8INDEX;

INSERT INTO RRER
            (RRER_ID, RRER_RRES_ID, RRER_YRES_ID)
   (SELECT  SEQ_RRER.NEXTVAL, T.*
      FROM (SELECT RRES_ID AS R,
                   (SELECT YRES_ID
                      FROM YRES, YRPL, RPRF
                     WHERE YRPL_YRES_ID = YRES_ID
                       AND YRPL_XCMS_ID = RPRF_XCMS_ID
                       AND RRES_DATE BETWEEN YRES_EXPARRTIME AND YRES_EXPDEPTIME
                       AND RPRF_RRES_ID = RRES_ID
                       AND ROWNUM = 1) YRES_ID
              FROM RRES) T
     WHERE YRES_ID IS NOT NULL);

ALTER TABLE MMOD ADD (MMOD_HTTP_USER VARCHAR2(30) NULL);

ALTER TABLE MMOD ADD (MMOD_HTTP_PWD VARCHAR2(30) NULL);

ALTER TABLE MMOD ADD (MMOD_HTTP_URL1 VARCHAR2(100) NULL);

COMMENT ON COLUMN MMOD.MMOD_HTTP_USER IS 'User name for http request';

COMMENT ON COLUMN MMOD.MMOD_HTTP_PWD IS 'User password for http request';

COMMENT ON COLUMN MMOD.MMOD_HTTP_URL1 IS 'Second URL to split download and upload';

