--UPDATE FROM 8.8.4.7 TO 8.8.4.9


DROP TABLE VERSION_8_8_4_7;


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

CREATE TABLE WCSP (
   WCSP_ID NUMBER(30) NOT NULL
  ,WCSP_ROLE NUMBER(2) NOT NULL
  ,WCSP_SENDING NUMBER(1) NOT NULL
  ,WCSP_LETTER_XADR_ID NUMBER(30)
  ,WCSP_EMAIL_XCOM_ID NUMBER(30)
  ,WCSP_XMNR_ID NUMBER(30)
  ,CONSTRAINT WCSP_PK PRIMARY KEY (WCSP_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

CREATE SEQUENCE SEQ_WCSP INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;
  
COMMENT ON TABLE WCSP IS 'Correspondence Sending Preferences for Bonus Points etc.'; 

COMMENT ON COLUMN WCSP.WCSP_ROLE IS '1 - Bonus Points';

COMMENT ON COLUMN WCSP.WCSP_SENDING IS '0 = dont send, 1 = Letter, 2 = EMAIL';

UPDATE YRES SET YRES.YRES_ORGDEPTIME=(YRES.YRES_ORGDEPTIME)+1 WHERE YRES.YRES_ID IN (SELECT YRES2.YRES_ID FROM YRES YRES2, WLOG WHERE WLOG.WLOG_TYPE=4 AND WLOG.WLOG_TABLE_ID=YRES2.YRES_ID AND WLOG.WLOG_TABLENAME LIKE 'YRES' AND EXISTS (SELECT 1 FROM WLOG WLOG2 WHERE WLOG2.WLOG_TYPE=2 AND WLOG2.WLOG_TABLE_ID=YRES2.YRES_ID AND WLOG2.WLOG_TABLENAME LIKE 'YRES' AND TRUNC(WLOG2.WLOG_TIME,'MI')=TRUNC(WLOG.WLOG_TIME,'MI'))) AND TRUNC(YRES.YRES_ACTDEPTIME) = TRUNC(YRES.YRES_ORGDEPTIME);

INSERT INTO YMSP SELECT SEQ_YMSP.NEXTVAL,YPAC_MASTER_YPAC_ID,YPAC_ID FROM YPAC WHERE YPAC_MASTER_YPAC_ID IS NOT NULL;

COMMENT ON COLUMN YPAC.YPAC_MASTER_YPAC_ID IS 'Obsolete column, not used anymore, table ymsp used instead';

ALTER TABLE YCRF MODIFY YCRF_CROSSREF_9 VARCHAR2(400);
