-- ABS-35937

--------- MACHINETYPE ---------
CREATE SEQUENCE SEQ_MACHINETYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE MACHINETYPE
( MACHINETYPE_ID NUMBER NOT NULL,
  CODE           VARCHAR2(10) NOT NULL,
  DESCRIPTION    VARCHAR2(60) NOT NULL,
  SYSTEMUSER_ID  NUMBER NOT NULL,
  TIMESTAMP      DATE NOT NULL);

 
ALTER TABLE MACHINETYPE
  ADD CONSTRAINT XPKMACHINETYPE PRIMARY KEY (MACHINETYPE_ID);
  
--------- MACHINE ---------  
CREATE SEQUENCE SEQ_MACHINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE MACHINE
  (MACHINE_ID       NUMBER NOT NULL,
   PLANT_ID         NUMBER NOT NULL,
   CODE             VARCHAR2(10) NOT NULL,
   MACHINETYPE_ID   NUMBER NOT NULL,
   ACTIVE           VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD309 CHECK (ACTIVE IN ('Y','N')),
   DESCRIPTION      VARCHAR2(60) NOT NULL,
   SYSTEMUSER_ID    NUMBER NOT NULL,
   TIMESTAMP        DATE NOT NULL);
 
ALTER TABLE MACHINE
  ADD CONSTRAINT XPKMACHINE PRIMARY KEY (MACHINE_ID);
  
ALTER TABLE MACHINE
  ADD CONSTRAINT XAK1MACHINE UNIQUE (PLANT_ID, CODE);
  
ALTER TABLE MACHINE
  ADD CONSTRAINT MACHINE_PLANT FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;
  
ALTER TABLE MACHINE
  ADD CONSTRAINT MACHINE_TYPE FOREIGN KEY (MACHINETYPE_ID)
  REFERENCES MACHINETYPE (MACHINETYPE_ID);
  
--------- MACHINECOMPARTMENT ---------   
CREATE SEQUENCE SEQ_MACHINECOMPARTMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE MACHINECOMPARTMENT
  (MACHINECOMPARTMENT_ID     NUMBER NOT NULL,
   MACHINE_ID                NUMBER NOT NULL,
   CODE                      VARCHAR2(10) NOT NULL,
   DESCRIPTION               VARCHAR2(60) NOT NULL,
   MAXLOAD                   NUMBER NOT NULL DEFAULT 0,
   SYSTEMUSER_ID   	         NUMBER NOT NULL,
   TIMESTAMP                 DATE NOT NULL);   
   
ALTER TABLE MACHINECOMPARTMENT
  ADD CONSTRAINT XPKMACHINECOMPARTMENT PRIMARY KEY (MACHINECOMPARTMENT_ID);

ALTER TABLE MACHINECOMPARTMENT
  ADD CONSTRAINT MACHINECOMPARTMENT_MACHINE FOREIGN KEY (MACHINE_ID)
  REFERENCES MACHINE (MACHINE_ID) ON DELETE CASCADE;
  
ALTER TABLE MACHINECOMPARTMENT
  ADD CONSTRAINT XAKMACHINECOMPARTMENT UNIQUE (MACHINE_ID, CODE);
  
--------- MACHINECOMPARTMENTLIMIT ---------   
CREATE SEQUENCE SEQ_MACHINECOMPARTMENTLIMIT
START WITH 1
INCREMENT BY 1;

CREATE TABLE MACHINECOMPARTMENTLIMIT
  (MACHINECOMPARTMENTLIMIT_ID NUMBER NOT NULL,
   MACHINECOMPARTMENT_ID      NUMBER NOT NULL,
   LOWERWEIGHTLIMIT           NUMBER NOT NULL,
   COLOR                      VARCHAR2(6) NOT NULL,
   SYSTEMUSER_ID   	          NUMBER NOT NULL,
   TIMESTAMP                  DATE NOT NULL); 
  
ALTER TABLE MACHINECOMPARTMENTLIMIT
  ADD CONSTRAINT XPKMACHINECOMPARTMENTLIMIT PRIMARY KEY (MACHINECOMPARTMENTLIMIT_ID);  

ALTER TABLE MACHINECOMPARTMENTLIMIT
  ADD CONSTRAINT MACHINECOMPARTMENTLIMIT_COMP FOREIGN KEY (MACHINECOMPARTMENT_ID)
  REFERENCES MACHINECOMPARTMENT (MACHINECOMPARTMENT_ID) ON DELETE CASCADE;  

ALTER TABLE MACHINECOMPARTMENTLIMIT
  ADD CONSTRAINT XAKMACHINECOMPARTMENTLIMIT UNIQUE (MACHINECOMPARTMENT_ID, LOWERWEIGHTLIMIT); 
