--ABS-7437

----------- TRANSACTIONTYPE -----------
INSERT INTO TRANSACTIONTYPE (TRANSACTIONTYPE_ID,FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP )
SELECT 19, 'Production Process Scan', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;

COMMIT;

----------- TRANSACTIONTYPE_DESC ----------- 
INSERT INTO TRANSACTIONTYPE_DESC
  (TRANSACTIONTYPE_ID, LANGUAGE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
  SELECT TRANSACTIONTYPE_ID,
         GETMINLANGUAGE(),
         FACTORYDESCRIPTION,
         SYSTEMUSER_ID,
         TIMESTAMP
    FROM TRANSACTIONTYPE
   WHERE TRANSACTIONTYPE.TRANSACTIONTYPE_ID = 19;


COMMIT;

----------- PRODUCTIONPROCESSBUFFER ----------- 
CREATE SEQUENCE SEQ_PRODUCTIONPROCESSBUFFER
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRODUCTIONPROCESSBUFFER
(PRODUCTIONPROCESSBUFFER_ID         NUMBER NOT NULL,
 PLANT_ID                           NUMBER NOT NULL,
 CODE                               VARCHAR2(6) NOT NULL,
 DESCRIPTION                        VARCHAR2(30) NOT NULL,
 SEQUENCENUMBER                     NUMBER NOT NULL,
 ACTIVE                             VARCHAR2(1) NOT NULL CONSTRAINT YN_VALIDATIONFIELD311 CHECK (ACTIVE IN ('Y','N')),
 SYSTEMUSER_ID                      NUMBER NOT NULL,
 TIMESTAMP                          DATE NOT NULL);

ALTER TABLE PRODUCTIONPROCESSBUFFER 
      ADD CONSTRAINT XPKPRODUCTIONPROCESSBUFFER PRIMARY KEY (PRODUCTIONPROCESSBUFFER_ID);
      
ALTER TABLE PRODUCTIONPROCESSBUFFER 
      ADD CONSTRAINT XAK1PRODUCTIONPROCESSBUFFER UNIQUE (PLANT_ID, CODE);
      
ALTER TABLE PRODUCTIONPROCESSBUFFER 
      ADD CONSTRAINT PRODPROCESSBUFFER_PLANT FOREIGN KEY (PLANT_ID)
      REFERENCES PLANT (PLANT_ID);

----------- WORKSTATION -----------      
ALTER TABLE WORKSTATION ADD PRODUCTIONPROCESSBUFFER_ID NUMBER;

ALTER TABLE WORKSTATION
  ADD CONSTRAINT WORKSTATION_PRODPROCESSBUFFER FOREIGN KEY (PRODUCTIONPROCESSBUFFER_ID)
  REFERENCES PRODUCTIONPROCESSBUFFER (PRODUCTIONPROCESSBUFFER_ID) ON DELETE SET NULL;

----------- PLANTOPERATINGHOURS -----------
CREATE SEQUENCE SEQ_PLANTOPERATINGHOURS
START WITH 1
INCREMENT BY 1;

CREATE TABLE PLANTOPERATINGHOURS
(
  PLANTOPERATINGHOURS_ID NUMBER NOT NULL,
  PLANT_ID               NUMBER NOT NULL,
  DAY_ID                 NUMBER NOT NULL,
  STARTTIME              DATE NOT NULL,
  ENDTIME                DATE NOT NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
);

ALTER TABLE PLANTOPERATINGHOURS
  ADD CONSTRAINT XPKPLANTOPERATINGHOURS PRIMARY KEY (PLANTOPERATINGHOURS_ID);
ALTER TABLE PLANTOPERATINGHOURS
  ADD CONSTRAINT XAK1PLANTOPERATINGHOURS UNIQUE (PLANT_ID, DAY_ID);
ALTER TABLE PLANTOPERATINGHOURS
  ADD CONSTRAINT PLANTOPERATINGHOURS_PLANT FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;
ALTER TABLE PLANTOPERATINGHOURS
  ADD CONSTRAINT PLANTOPERATINGHOURS_DAY FOREIGN KEY (DAY_ID)
  REFERENCES DAY (DAY_ID);










