--UPDATE FROM 8.6.4.4 TO 8.6.4.5


DROP TABLE VERSION_8_6_4_4;



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


CREATE TABLE ZITI (
    ZITI_ID NUMBER(30) NOT NULL
   ,ZITI_TYPE NUMBER(2) NOT NULL
   ,ZITI_INTERFACEID NUMBER(30)
   ,CONSTRAINT ZITI_PK PRIMARY KEY (ZITI_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON COLUMN ZITI.ZITI_TYPE IS 'see constantsandtypes.ttriggerinterfacetypes. in example 0=mmod interfaces';

COMMENT ON COLUMN ZITI.ZITI_INTERFACEID IS 'for mmod interfaces this is mmod_id.';

CREATE TABLE ZITSBACKUP AS SELECT * FROM ZITS;

ALTER TABLE ZITS ADD ZITS_ZITI_ID NUMBER(30);

INSERT INTO ZITI (SELECT SEQ_ZITI.NEXTVAL,0,MMOD_ID FROM MMOD WHERE MMOD_TWOWAY=1 AND NOT EXISTS (SELECT 1 FROM ZITI WHERE MMOD.MMOD_TWOWAY=1 AND MMOD.MMOD_ID=ZITI.ZITI_INTERFACEID));

UPDATE ZITS SET ZITS_ZITI_ID=(SELECT ZITI_ID FROM ZITI WHERE ZITI_INTERFACEID=ZITS_MMOD_ID);

ALTER TABLE ZITS MODIFY ZITS_ZITI_ID NOT NULL;

ALTER TABLE ZITS DROP COLUMN ZITS_MMOD_ID;

DROP TABLE ZITSBACKUP;

ALTER TABLE ZIPF ADD ZIPF_ZITI_ID NUMBER(30);

UPDATE ZIPF SET ZIPF_ZITI_ID=(SELECT ZITI_ID FROM ZITI WHERE ZITI_INTERFACEID=ZIPF_MMOD_ID);

ALTER TABLE ZIPF MODIFY ZIPF_ZITI_ID NOT NULL;

ALTER TABLE ZIPF DROP COLUMN ZIPF_MMOD_ID;

ALTER TABLE ZIPH ADD ZIPH_ZITI_ID NUMBER(30);

UPDATE ZIPH SET ZIPH_ZITI_ID=(SELECT ZITI_ID FROM ZITI WHERE ZITI_INTERFACEID=ZIPH_MMOD_ID);

ALTER TABLE ZIPH MODIFY ZIPH_ZITI_ID NOT NULL;

ALTER TABLE ZIPH DROP COLUMN ZIPH_MMOD_ID;

ALTER TABLE ZITR ADD ZITR_TABLEID2 NUMBER(30);

ALTER TABLE ZIPT ADD ZIPT_TABLEID2 NUMBER(30);

CREATE TABLE ZEMS (
	ZEMS_ID NUMBER(30,0) NOT NULL,
	ZEMS_YRES_ID NUMBER(30,0) NOT NULL,
	ZEMS_ON_NOTIFICATION_TIME DATE,
	ZEMS_OFF_NOTIFICATION_TIME DATE,
	ZEMS_YRMS_ID NUMBER(30,0) NULL,
	CONSTRAINT ZEMS_PK PRIMARY KEY (ZEMS_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

CREATE INDEX ZEMS_YRES_ID ON ZEMS(ZEMS_YRES_ID) TABLESPACE V8INDEX; 

COMMENT ON TABLE ZEMS  IS 'Energy management interface notification';

COMMENT ON COLUMN ZEMS.ZEMS_ID IS 'WMAA PK';

COMMENT ON COLUMN ZEMS.ZEMS_YRES_ID IS 'Reservation link';

COMMENT ON COLUMN ZEMS.ZEMS_ON_NOTIFICATION_TIME IS 'Time of the switching ON';

COMMENT ON COLUMN ZEMS.ZEMS_OFF_NOTIFICATION_TIME IS 'Time of the switching OFF';

COMMENT ON COLUMN ZEMS.ZEMS_YRMS_ID IS 'Room number link';

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';

