--UPDATE FROM 8.9.2.0 TO 8.9.2.1


DROP TABLE VERSION_8_9_2_0;



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

CREATE TABLE WUNO
(
  WUNO_ID           	NUMBER(30)      NOT NULL,
  WUNO_NOTES			VARCHAR2(2000)	NOT NULL,
  WUNO_XCED_ID			NUMBER(30) 	NOT NULL,
  WUNO_DATE				DATE,
  CONSTRAINT WUNO_PK  	PRIMARY KEY  (WUNO_ID)  USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE WUNO IS 'User Notes';

ALTER TABLE WATY ADD WATY_WAGR_ID NUMBER(30); 

COMMENT ON COLUMN WATY.WATY_WAGR_ID IS 'Linked to Maintenance Group';

CREATE TABLE WAGR (
 WAGR_ID NUMBER(30) NOT NULL
,WAGR_WHDE_ID NUMBER(30) NOT NULL 
,WAGR_LONGDESC VARCHAR2(30)
,WAGR_SHORTDESC VARCHAR2(3)
,WAGR_DISPLORDER NUMBER(3)
,WAGR_INACTIVE NUMBER(1)
,WAGR_DEFAULT NUMBER(1)
,CONSTRAINT WAGR_PK PRIMARY KEY (WAGR_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

COMMENT ON TABLE WAGR IS 'Maintenance Group';

COMMENT ON COLUMN WAGR.WAGR_WHDE_ID IS 'Linked to Resp. Departments';

COMMENT ON COLUMN WAGR.WAGR_INACTIVE IS '0=active, 1=inactive';

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

INSERT INTO WHDE (WHDE_ID,WHDE_SHORTDESC,WHDE_LONGDESC,WHDE_DISPLORDER,WHDE_INACTIVE,WHDE_ROLE) SELECT SEQ_WHDE.NEXTVAL, 'GEN', 'General',(SELECT NVL(MAX(WHDE_DISPLORDER),0)+1 FROM WHDE), 0, 0 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM WHDE WHERE UPPER(WHDE_SHORTDESC) = upper('GEN') );

INSERT INTO WAGR (WAGR_ID, WAGR_WHDE_ID, WAGR_LONGDESC, WAGR_SHORTDESC, WAGR_DISPLORDER, WAGR_INACTIVE, WAGR_DEFAULT) SELECT SEQ_WAGR.NEXTVAL, (SELECT WHDE_ID FROM WHDE WHERE UPPER(WHDE_SHORTDESC)=UPPER('GEN')), 'General', 'GEN', '1',0,1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM WAGR WHERE UPPER(WAGR_SHORTDESC) = upper('GEN') ); 

UPDATE WATY SET WATY_WAGR_ID = (SELECT WAGR_ID FROM WAGR WHERE UPPER(WAGR_SHORTDESC) = UPPER('GEN')) WHERE  WATY_WATG_ID = (SELECT WATG_ID FROM WATG WHERE WATG_ROLE = 6);

ALTER TABLE WUSR MODIFY WUSR_NAME VARCHAR2(200);

ALTER TABLE WACT ADD WACT_REQUESTLINKED_ID NUMBER(30);

COMMENT ON COLUMN WACT.WACT_REQUESTLINKED_ID IS 'Request Employee';

ALTER TABLE ZINV ADD ZINV_MANUALNUMBER VARCHAR(60);

UPDATE YRMS SET YRMS_WALKORDER = YRMS_DISPLORDER;

ALTER TABLE WAMT ADD WAMT_WORJ_ID NUMBER(30);

COMMENT ON COLUMN WAMT.WAMT_WORJ_ID IS 'Maintenance Origin';

COMMENT ON COLUMN WORJ.WORJ_ROLE IS 'where role = 1 means guest generated and makes wact_xcms_id mandatory when entering the maintenance task; 2 = Automatic ';

ALTER TABLE YBOM ADD YBOM_YCAD_ID NUMBER(30);

COMMENT ON COLUMN YBOM.YBOM_YCAD_ID IS 'Linked segment id';

ALTER TABLE YEMN MODIFY YEMN_QUANTITY NUMBER(5);

ALTER TABLE WPHS ADD WPHS_MULTIHOTELCONFIG NUMBER(1) DEFAULT 0 NOT NULL;

UPDATE WUSS SET WUSS.WUSS_VALUE=4 WHERE WUSS.WUSS_VALUE=2 AND WUSS.WUSS_NAME LIKE 'confratefilter' AND WUSS.WUSS_XCMS_ID=0;

UPDATE WUSS SET WUSS.WUSS_VALUE=3 WHERE WUSS.WUSS_VALUE=1 AND WUSS.WUSS_NAME LIKE 'confratefilter' AND WUSS.WUSS_XCMS_ID=0;

ALTER TABLE QNRA ADD QNRA_TEXT VARCHAR2(1000);

COMMENT ON COLUMN QNRA.QNRA_TEXT IS 'Guest entered answers/comments';

ALTER TABLE WATY MODIFY WATY_WAGR_ID NUMBER(30) DEFAULT 0;

UPDATE WATY SET WATY_WAGR_ID = 0 WHERE WATY_WAGR_ID IS NULL;

ALTER TABLE MMOD ADD MMOD_USE_SALEDATE NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_USE_SALEDATE IS 'indicates whether the crs booking date is stored in yres_saledate';

ALTER TABLE WATG ADD WATG_TABRES NUMBER(1);  

ALTER TABLE WACT ADD WACT_RRES_ID NUMBER(30);

ALTER TABLE WAVL ADD (
    WAVL_WCOL1 NUMBER(30) 
   ,WAVL_WCOL2 NUMBER(30) 
   ,WAVL_WCOL3 NUMBER(30)
   ,WAVL_WCOL4 NUMBER(30)
   ,WAVL_FULLYOCCUPIED_WCOL NUMBER(30));

ALTER TABLE CART ADD CART_OVERRIDEREDEEMAMOUNT NUMBER(1); 

COMMENT ON COLUMN CART.CART_OVERRIDEREDEEMAMOUNT IS 'if 1, redemption amount will be requested';

ALTER TABLE COUP ADD COUP_REDEMPTIONNOTE VARCHAR2(1000);
