-- Add the Transaction Time
ALTER TABLE LOT ADD
   (	START_DATE DATE,
	STOP_DATE DATE  ) ;

ALTER TABLE LOT_MOVE ADD
   (	WHEN_CHANGED DATE,
	OPERATION CHAR(1)  ) ;

ALTER TABLE LOT_LOC ADD
   (	WHEN_CHANGED DATE,
	OPERATION CHAR(1)  ) ;

ALTER TABLE BKP ADD
   (	WHEN_CHANGED DATE,
	OPERATION CHAR(1)  ) ;

-- Drop 4 columns from table LOT, we must first drop the table
-- and then create the table again.

ALTER TABLE LOT_LOC DROP CONSTRAINT FK_LOC_LOT ;
ALTER TABLE LOT_MOVE DROP CONSTRAINT FK_MOVE_LOT ;
ALTER TABLE MASS_TRTMNT DROP CONSTRAINT FK_MASS_LOT ;

DROP TABLE LOT ;

CREATE TABLE LOT
   (	FDYD_ID		INTEGER,
	LOT_ID_NUM	INTEGER,
	LOT_ID		INTEGER,
	GNDR_CODE	CHAR(1),
	PROJ_CLOSE_OUT	DATE,
	IN_WEIGHT	INTEGER,
	VALID		NUMBER(1),
	OWNER		CHAR(1),
	LOT_COMMENT	VARCHAR2(30),
	FROM_DATE DATE,
	TO_DATE DATE,
	START_DATE DATE,
	STOP_DATE DATE,
	PRIMARY KEY (FDYD_ID, LOT_ID_NUM),
	FOREIGN KEY (FDYD_ID) REFERENCES FDYD  ) ;

ALTER TABLE LOT_LOC ADD CONSTRAINT FK_LOC_LOT FOREIGN KEY (FDYD_ID, LOT_ID_NUM) REFERENCES LOT ;
ALTER TABLE LOT_MOVE ADD CONSTRAINT FK_MOVE_LOT FOREIGN KEY (FDYD_ID, LOT_ID_NUM) REFERENCES LOT ;
ALTER TABLE MASS_TRTMNT ADD CONSTRAINT FK_MASS_LOT FOREIGN KEY (FDYD_ID, LOT_ID_NUM) REFERENCES LOT ;

DROP TABLE LOT_CONTAINS CASCADE CONSTRAINTS ;

CREATE TABLE LOT_CONTAINS
   (	FDYD_ID		INTEGER,
	LOT_ID_NUM	INTEGER,
	BKP_ID		INTEGER,
	A_NAME		CHAR(1),
	DBF_NAME	CHAR(1),
	DBF_UPDATE_RECNO	INTEGER,
	WHEN_CHANGED	DATE,
	OPERATION	CHAR(1),
	PRIMARY KEY (FDYD_ID, LOT_ID_NUM),
	CONSTRAINT FK_CO_BKP FOREIGN KEY (FDYD_ID, BKP_ID) REFERENCES BKP,
	CONSTRAINT FK_CO_LOT FOREIGN KEY (FDYD_ID, LOT_ID_NUM) REFERENCES LOT,
	FOREIGN KEY (A_NAME, DBF_NAME) REFERENCES DBF_FILE  ) ;
