--ABS-134179
CREATE TABLE EMPLOYEEFUNCTION_HISTORY
(
  EMPLOYEE_ID         NUMBER NOT NULL,
  PLANT_ID            NUMBER NOT NULL,
  EMPLOYEENUMBER      NUMBER NOT NULL,
  EMPLOYEEFUNCTION_ID NUMBER NOT NULL,
  DATEACTIVE          DATE NOT NULL,
  DATEINACTIVE        DATE DEFAULT TO_DATE('31122099','DDMMYYYY') NOT NULL,
  TIMESTAMP           DATE DEFAULT SYSDATE NOT NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL
);
ALTER TABLE EMPLOYEEFUNCTION_HISTORY
  ADD CONSTRAINT PK_EMPLOYEEFUNCTION_HISTORY PRIMARY KEY (EMPLOYEE_ID, EMPLOYEEFUNCTION_ID, DATEACTIVE);
CREATE INDEX XIF1EMPLOYEEFUNCTION_HISTORY ON EMPLOYEEFUNCTION_HISTORY (EMPLOYEE_ID);
CREATE INDEX XIF2EMPLOYEEFUNCTION_HISTORY ON EMPLOYEEFUNCTION_HISTORY (PLANT_ID);
CREATE INDEX XIF3EMPLOYEEFUNCTION_HISTORY ON EMPLOYEEFUNCTION_HISTORY (PLANT_ID, EMPLOYEENUMBER);
CREATE INDEX XIF4XIF1EMPLOYEEFUNCTION_HISTORY ON EMPLOYEEFUNCTION_HISTORY (EMPLOYEEFUNCTION_ID);
ALTER TABLE EMPLOYEEFUNCTION_HISTORY
  ADD CONSTRAINT FK_EMPLFUNCT_HISTORY_EMPL FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE (EMPLOYEE_ID);
ALTER TABLE EMPLOYEEFUNCTION_HISTORY ADD CONSTRAINT CHK_EMPLFUNCTIHISTORY_DATES CHECK (DATEINACTIVE >=DATEACTIVE);
