--UPDATE FROM 8.7.2.0 TO 8.7.3.0


DROP TABLE VERSION_8_7_2_0;


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


CREATE TABLE WMAA (
	WMAA_id NUMBER(30,0) NOT NULL,
	WMAA_WMAI_id NUMBER(30,0) NOT NULL,
	WMAA_FILENAME VARCHAR2(128) NOT NULL,
	WMAA_ATTACHMENT_WTXT_ID NUMBER(30,0) NOT NULL,
	CONSTRAINT WMAA_PK PRIMARY KEY (WMAA_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN ;

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

CREATE INDEX WMAA_WMAI_ID ON WMAA(WMAA_WMAI_ID) TABLESPACE V8INDEX; 

COMMENT ON TABLE WMAA IS 'Email attachments';

COMMENT ON COLUMN WMAA.WMAA_ID IS 'WMAA PK';

COMMENT ON COLUMN WMAA.WMAA_WMAI_ID IS 'Attachment link to the email';

COMMENT ON COLUMN WMAA.WMAA_FILENAME IS 'Attachment file name';

COMMENT ON COLUMN WMAA.WMAA_ATTACHMENT_WTXT_ID IS 'link to the binary storage';

COMMENT ON COLUMN WTXT.WTXT_DOCFORMAT IS ' 0/null - Ms Word Format; 1-Text; 2-Acrobat PDF; 3-HTML; 4-RTF; 5-MHTML; 6-Unknown. Currently used to store any email attachment ';

ALTER TABLE XMTY ADD XMTY_RATEQUERY NUMBER (1);

COMMENT ON COLUMN XMTY.XMTY_RATEQUERY IS 'Allow for searching on rate query screen ';

ALTER TABLE YDET ADD YDET_BLOCKPRICE NUMBER(1);

COMMENT ON COLUMN YDET.YDET_BLOCKPRICE IS '1= manual price comes from block price config';

ALTER TABLE MCFG ADD MCFG_READ_TW_INTERVAL NUMBER (5,0);

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

ALTER TABLE MMOD ADD MMOD_FTP_SERVER VARCHAR2(100);

ALTER TABLE MMOD ADD MMOD_FTP_USER VARCHAR2(30);

ALTER TABLE MMOD ADD MMOD_FTP_PWD VARCHAR2(30);

ALTER TABLE MMOD ADD MMOD_FTP_PORT NUMBER (5,0);

COMMENT ON COLUMN MCFG.MCFG_READ_TW_INTERVAL IS 'interval of uploading files by filereader';

COMMENT ON COLUMN MMOD.MMOD_USEFTP IS 'Indicates use of FTP within FileReader';

COMMENT ON COLUMN MMOD.MMOD_FTP_SERVER IS 'server address of FTP server';

COMMENT ON COLUMN MMOD.MMOD_FTP_USER IS 'user name of FTP account';

COMMENT ON COLUMN MMOD.MMOD_FTP_PWD IS 'password of FTP account';

COMMENT ON COLUMN MMOD.MMOD_FTP_PORT IS 'port of FTP default is 21';

UPDATE ZPOS SET ZPOS.ZPOS_XCMS_ID = (SELECT YRES.YRES_XCMS_ID FROM YRES WHERE YRES.YRES_ID=ZPOS.ZPOS_YRES_ID)
WHERE ZPOS.ZPOS_YRES_ID IS NOT NULL AND EXISTS (SELECT 1 FROM YRES WHERE YRES.YRES_ID=ZPOS.ZPOS_YRES_ID);

UPDATE ZPOS SET ZPOS.ZPOS_XCMS_ID = (SELECT ZFAC.ZFAC_XCMS_ID FROM ZFAC WHERE ZFAC.ZFAC_ID=ZPOS.ZPOS_ZFAC_ID)
WHERE ZPOS.ZPOS_ZFAC_ID IS NOT NULL AND EXISTS (SELECT 1 FROM ZFAC WHERE ZFAC.ZFAC_ID=ZPOS.ZPOS_ZFAC_ID);

ALTER TABLE WTXT ADD WTXT_COMPRESSED NUMBER (1);

CREATE INDEX WTXT_COMPRESSED ON WTXT(WTXT_COMPRESSED) TABLESPACE V8INDEX;

UPDATE WTXT SET WTXT_COMPRESSED=0 WHERE WTXT_COMPRESSED IS NULL;

ALTER TABLE XJET DROP COLUMN XJET_USE_COUNTRY_CODE;

ALTER TABLE XJET ADD XJET_LAND_COUNTRY_CODE NUMBER(1);

ALTER TABLE XJET ADD XJET_STAAT_COUNTRY_CODE NUMBER(1);

ALTER TABLE XJET ADD XJET_NATION_COUNTRY_CODE NUMBER(1);

ALTER TABLE MAVL ADD MAVL_CUR_ID NUMBER (30,0);

COMMENT ON COLUMN MAVL.MAVL_CUR_ID IS 'currency id for of rate code';

BEGIN
DECLARE 
   ID NUMBER(30);
   BEGIN
   SELECT NVL(MAX(YPCF_ID), 1) INTO ID FROM YPCF;
   EXECUTE IMMEDIATE 'drop sequence SEQ_YPCF';
   EXECUTE IMMEDIATE 'create sequence SEQ_YPCF increment by 1 start with ' || to_char(ID) || 'nomaxvalue nocycle nocache noorder';
   END;
END;
/

ALTER TABLE XCOU ADD XCOU_PSCANSTRING VARCHAR2(60);

COMMENT ON COLUMN XCOU.XCOU_PSCANSTRING IS 'string to store the passport scanner''s equivalent for this country';

ALTER TABLE XREG ADD XREG_PSCANSTRING VARCHAR2(60);

COMMENT ON COLUMN XREG.XREG_PSCANSTRING IS 'string to store the passport scanner''s equivalent for this region';

ALTER TABLE XSTA ADD (XSTA_PSCANSTRING VARCHAR2(60), XSTA_PSCANDEFAULT NUMBER(1));

COMMENT ON COLUMN XSTA.XSTA_PSCANSTRING IS 'string to store the passport scanner''s equivalent for this state';

COMMENT ON COLUMN XSTA.XSTA_PSCANDEFAULT IS '1 = if this state should be used as default for fast scanning';

COMMENT ON COLUMN XPDG.XPDG_ROLE IS ' passports = 1, id card=2, drivers licence=3    .. to be continued  ';

ALTER TABLE WPCA ADD (WPCA_PASSSCANNER NUMBER(3));

COMMENT ON COLUMN WPCA.WPCA_PASSSCANNER IS '0 - no pass scanner, 1 - Fuji fi-60F'; 

ALTER TABLE YRGP ADD YRGP_LIMIT NUMBER(30,4);

COMMENT ON COLUMN YRGP.YRGP_LIMIT IS 'limit value for allowance packages';

ALTER TABLE YRGP ADD YRGP_FLIMIT NUMBER(30,4);

COMMENT ON COLUMN YRGP.YRGP_FLIMIT IS 'foreign currency limit value for allowance packages';

ALTER TABLE YRAV ADD YRAV_MAXVALUE NUMBER (10,0);

CREATE TABLE YRDV (
YRDV_ID NUMBER (30,0) NOT NULL,
YRDV_YRDY_ID NUMBER (30),
YRDV_ADULTCOUNT NUMBER (2),
YRDV_VALUE NUMBER(10,4), CONSTRAINT YRDV_PK PRIMARY KEY (YRDV_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE YRDV IS 'Day type adult values';

TRUNCATE TABLE YRDD;

ALTER TABLE YRDD ADD YRDD_AVERAGE NUMBER (1);

ALTER TABLE YRDD ADD YRDD_FIRSTDAY NUMBER (1);

ALTER TABLE YRDD DROP COLUMN YRDD_VALUE;

CREATE TABLE YRDY (
YRDY_ID NUMBER (30) NOT NULL,
YRDY_YRDD_ID NUMBER (30),
YRDY_YCAT_ID NUMBER (30),CONSTRAINT YRDY_PK PRIMARY KEY (YRDY_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE YRDY IS 'Day type schema/daytype value configuration per room type';

ALTER TABLE YRDD ADD YRDD_TYPE NUMBER (1);

ALTER TABLE YRDD DROP COLUMN YRDD_YCAT_ID;

DROP INDEX YRDD_YCAT_ID;

ALTER TABLE YRDD DROP COLUMN YRDD_FLAT;

ALTER TABLE YRDX ADD YRDX_YRDV_ID NUMBER (30);

CREATE TABLE XPMS (
   XPMS_ID NUMBER(30) NOT NULL
  ,XPMS_NAME VARCHAR2(60) NOT NULL
  ,XPMS_XPMR_SET NUMBER(30) NOT NULL 
  ,CONSTRAINT XPMS_PK PRIMARY KEY (XPMS_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

CREATE UNIQUE INDEX XPMS_NAME ON XPMS (XPMS_NAME) TABLESPACE V8INDEX;

ALTER TABLE XPMS ADD CONSTRAINT XPMS_NAME UNIQUE (XPMS_NAME);

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

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

ALTER TABLE XPMR MODIFY XPMR_SET NUMBER(30);

ALTER TABLE WMSO ADD WMSO_MIICS NUMBER(1);

COMMENT ON COLUMN WMSO.WMSO_MIICS IS 'Matched individuals if company selected';

