--UPDATE FROM 8.9.2.3 TO 8.9.2.4


DROP TABLE VERSION_8_9_2_3;



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

ALTER TABLE WATY ADD WATY_ISRESOLVEDNOTE NUMBER(1);

COMMENT ON COLUMN WATY.WATY_ISRESOLVEDNOTE IS 'Resolved Note';

ALTER TABLE WACT ADD WACT_RESOLVEDNOTE_WANT_ID NUMBER(30);

COMMENT ON COLUMN WACT.WACT_RESOLVEDNOTE_WANT_ID IS 'Resolved note id';  

ALTER TABLE WMSL ADD WMSL_DEFAULTFORSOLVE NUMBER(1);

COMMENT ON COLUMN WMSL.WMSL_DEFAULTFORSOLVE IS 'Default for solved'; 

ALTER TABLE WAGR ADD WAGR_NOTIFYHKSCREEN NUMBER(1) default 0;

COMMENT ON COLUMN WAGR.WAGR_NOTIFYHKSCREEN IS 'Notify in HK Screen'; 

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

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

COMMENT ON COLUMN MMOD.MMOD_USE_RT_CATEGORY IS 'Indicates the use of a room type list to simulate a rate category';

COMMENT ON COLUMN MMOD.MMOD_CODE_TO_PROFILE IS 'Indicates the conversion of a code like source code or channel also to a profile category liek Source Profile';

DROP TRIGGER ZPOS_ZARR_ID;

ALTER TABLE ZCNV MODIFY(ZCNV_SHORTDESC VARCHAR2(5));

ALTER TABLE YRCH ADD YRCH_AVAIL_YRCH_ID NUMBER (30);

COMMENT ON COLUMN YRCH.YRCH_AVAIL_YRCH_ID IS 'Follow availability of linked rate';

DROP TABLE YTRR CASCADE CONSTRAINTS;

DROP PROCEDURE V8_SYS_YTRR;

CREATE TABLE XDEV (
    XDEV_ID NUMBER(30) NOT NULL
    ,XDEV_TYPE NUMBER(1)
    ,XDEV_ENABLEFASTSCANNING NUMBER(1)
    ,XDEV_IDCARD_XPDG_ID NUMBER(30)
    ,XDEV_PASSPORT_XPDG_ID NUMBER(30)
    ,XDEV_AUTOATTACHPICTURE NUMBER(1)
    ,XDEV_PICTURE_WDCG_ID NUMBER(30)
    ,XDEV_PROMPTWHENMISSINGFIELDS NUMBER(1)
    ,XDEV_QUESTIONIFSECONDSIDE NUMBER(1)
    ,XDEV_AUTOATTACHFULLPICTURE NUMBER(1)
    ,XDEV_FULLPICTURE_WDCG_ID NUMBER(30)
    ,XDEV_USEWATERMARK NUMBER(1)
    ,XDEV_WATERMARK VARCHAR2(200)
    ,XDEV_USESCANNINGCENTER NUMBER(1)
   ,CONSTRAINT XDEV_PK PRIMARY KEY (XDEV_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE XDEV IS 'Document scanning devices';

COMMENT ON COLUMN XDEV.XDEV_ID IS 'Id of scanning device';

COMMENT ON COLUMN XDEV.XDEV_TYPE IS '0 - Fujitsu; 1 - Belgian Eid reader; 2 - Samsotech';

COMMENT ON COLUMN XDEV.XDEV_ENABLEFASTSCANNING IS 'Scanning with no form interaction';

COMMENT ON COLUMN XDEV.XDEV_IDCARD_XPDG_ID IS 'Document group for IDCards';

COMMENT ON COLUMN XDEV.XDEV_PASSPORT_XPDG_ID IS 'Document group for Passports';

COMMENT ON COLUMN XDEV.XDEV_AUTOATTACHPICTURE IS 'Attach picture to profile if exists';

COMMENT ON COLUMN XDEV.XDEV_PICTURE_WDCG_ID IS 'Picture group';

COMMENT ON COLUMN XDEV.XDEV_PROMPTWHENMISSINGFIELDS IS 'Warning if missing fields';

COMMENT ON COLUMN XDEV.XDEV_QUESTIONIFSECONDSIDE IS 'Question if second side needs to be scanned';

COMMENT ON COLUMN XDEV.XDEV_AUTOATTACHFULLPICTURE IS 'Attach full picture to profile if exists';

COMMENT ON COLUMN XDEV.XDEV_FULLPICTURE_WDCG_ID IS 'Full picture group';

COMMENT ON COLUMN XDEV.XDEV_USEWATERMARK IS 'Wattermark scanned pictures';

COMMENT ON COLUMN XDEV.XDEV_WATERMARK IS 'Picture used as wattermark';

COMMENT ON COLUMN XDEV.XDEV_USESCANNINGCENTER IS 'Use this device in scanning center';

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

CREATE TABLE XDLT (
    XDLT_ID NUMBER(30) NOT NULL
    ,XDLT_WPCA_ID NUMBER(30) NOT NULL
    ,XDLT_XDEV_ID NUMBER(30) NOT NULL
   ,CONSTRAINT XDLT_PK PRIMARY KEY (XDLT_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE XDLT IS 'Devices linked to terminals';

COMMENT ON COLUMN XDLT.XDLT_ID IS 'Id';

COMMENT ON COLUMN XDLT.XDLT_WPCA_ID IS 'Terminal id';

COMMENT ON COLUMN XDLT.XDLT_XDEV_ID IS 'Device id';

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

COMMENT ON COLUMN WPCA.WPCA_PASSSCANNER IS 'Obsolete using table linked devices to terminal';

COMMENT ON COLUMN WPCA.WPCA_HASEIDREADER IS 'Obsolete using table linked devices to terminal';

ALTER TABLE XDEV ADD XDEV_UPLOADFOLDER VARCHAR2(200);

ALTER TABLE XDEV ADD XDEV_UPLOADUSER VARCHAR2(60);

ALTER TABLE XDEV ADD XDEV_UPLOADPASSWORD VARCHAR2(60);

COMMENT ON COLUMN XDEV.XDEV_UPLOADFOLDER IS 'Folder where scanned document will be uploaded for further processings/uploadings';

COMMENT ON COLUMN XDEV.XDEV_UPLOADUSER IS 'User that logs on network resource; othewise blank';



ALTER TABLE XDEV DROP COLUMN XDEV_IDCARD_XPDG_ID;

ALTER TABLE XDEV DROP COLUMN XDEV_PASSPORT_XPDG_ID;

ALTER TABLE XDEV ADD XDEV_XPDG_ID NUMBER(30);

COMMENT ON COLUMN XDEV.XDEV_XPDG_ID IS 'Default document type if not recognized as passport or Id card';

ALTER TABLE WSFH ADD WSFH_XPDG_ID NUMBER(30);

COMMENT ON COLUMN WSFH.WSFH_XPDG_ID IS 'Document type as recognized for later assigning to profile';

ALTER TABLE XDLT ADD XDLT_DISPLORDER NUMBER(3);

COMMENT ON COLUMN XDLT.XDLT_DISPLORDER IS 'Display order';

ALTER TABLE XSCD ADD (XSCD_TRIGGERCONDITION VARCHAR2(1000));

ALTER TABLE WSFH MODIFY WSFH_FILENAME NULL;

COMMENT ON COLUMN YRES.YRES_CALCSTATUS IS '0=expected(future) 1=expected today 2=checked in today 3=in house 4=exp dep today 5=departed today 6=history 7=day use exp 8=day use in house 9=day use history 10=waitlist 11=offer 12=cancelled 13=noshow 14=voucher template 15=day use exp today 17=day use departed today';

COMMENT ON COLUMN CBCC.CBCC_ROLE IS '1 = Voucher Handling 2 = Rebate 3=Loyalty Services Voucher 4=Bonus Card 5= Rebate Cards';

UPDATE WACT SET WACT.WACT_REMINDERSTATE = 4 WHERE EXISTS (SELECT 1 FROM WATY,WATG WHERE WACT.WACT_WATY_ID=WATY.WATY_ID AND WATY.WATY_WATG_ID=WATG.WATG_ID AND WATG.WATG_ROLE=6) AND NVL(WACT.WACT_REMINDERSTATE,0)=0 AND TRUNC(WACT.WACT_ENDTIME) < SYSDATE;

ALTER TABLE W8HP MODIFY W8HP_QNR_EMAILID VARCHAR2(300);

ALTER TABLE YCRS MODIFY YCRS_MODIFIEDWITHOUTOWNED DEFAULT 0;

ALTER TABLE WSFH ADD WSFH_XDEV_ID NUMBER(30);

COMMENT ON COLUMN WSFH.WSFH_XDEV_ID IS 'Link to device that scanned the document';

UPDATE WSFH SET WSFH_ISPROCESSED = 1 WHERE NVL(WSFH_ISPROCESSED,0) <> 1;

ALTER TABLE YPRD ADD YPRD_PRIORTOARRIVAL NUMBER (1);

COMMENT ON COLUMN YPRD.YPRD_PRIORTOARRIVAL IS 'if yprd_staydaterange and this field set: only if res is prior or equal to arrival to prom startdate will be applied, if staydaterange not set: only for overlapping dates prm will be set';

UPDATE YRCH SET YRCH_AVAIL_YRCH_ID = NULL WHERE YRCH_AVAIL_YRCH_ID = 0;
