-- Code Fragment 11.21

-- ALTER TABLE LOT DROP COLUMN STOP_DATE
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  ) ;

CREATE TABLE LOT_Archive
   (	FDYD_ID		INTEGER,
	NAME		CHAR(1),
	LOT_ID_NUM	INTEGER,
	LOT_ID		INTEGER,
	GNDR_CODE	CHAR(1),
	PROJ_CLOSEOUT	DATE,
	IN_WEIGHT	INTEGER,
	VALID		NUMBER(1),
	OWNER		CHAR(1),
	ARCHIVE_COMMENT	VARCHAR2(30),
	BKP_ID		INTEGER,
	A_NAME		CHAR(1),
	DBF_NAME	CHAR(1),
	DBF_UPDATE_RECNO	INTEGER,
	FROM_DATE	DATE,
	TO_DATE		DATE,
	START_DATE	DATE,
	STOP_DATE	DATE,
	PRIMARY KEY (FDYD_ID, LOT_ID_NUM, FROM_DATE, STOP_DATE)  );

CREATE OR REPLACE TRIGGER Archive_seq_seq_primary_key
AFTER INSERT OR DELETE OR UPDATE ON LOT_Archive
DECLARE
  valid INTEGER;
  forever DATE;
BEGIN
  forever := TO_DATE('31-DEC-9999', 'DD-MON-YYYY');
  SELECT 1
  INTO valid
  FROM DUAL
  WHERE NOT EXISTS (
    SELECT *
    FROM LOT_Archive L1
    WHERE L1.STOP_DATE = forever
      AND EXISTS (
        SELECT *
        FROM LOT_Archive L2
        WHERE L1.FDYD_ID = L2.FDYD_ID
          AND L1.LOT_ID_NUM = L2.LOT_ID_NUM
          AND L1.FROM_DATE < L2.TO_DATE
          AND L2.FROM_DATE < L1.TO_DATE
          AND L2.STOP_DATE = forever
          AND L2.rowid <> L1.rowid ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20010, 'FDYD_ID and LOT_ID_NUM are primary key' );
END;
/

/* Data for Code Fragment 11.21
INSERT INTO LOT_ARCHIVE VALUES
	(1, 'T', 137, 1, 'K', '30-JAN-99', 100, 1, 'A', 'TEST', 1, 'A', 'A', 12, '02-JAN-99', '10-JAN-99', '20-DEC-98', TO_DATE('31-DEC-9999', 'DD-MON-YYYY'));

INSERT INTO LOT_ARCHIVE VALUES
	(1, 'T', 137, 1, 'K', '30-JAN-99', 100, 1, 'A', 'TEST', 1, 'A', 'A', 12, '05-JAN-99', '12-JAN-99', '20-DEC-98', TO_DATE('31-DEC-9999', 'DD-MON-YYYY'));
*/



