 create sequence SEQ_EMPLOYEEFUNCTION start with 1 increment by 1;     
      /*
      ACTION is CREATE Table EMPLOYEEFUNCTION
      */

CREATE TABLE EMPLOYEEFUNCTION (
       EMPLOYEEFUNCTION_ID  NUMBER NOT NULL,
       CODE                 VARCHAR2(6) NOT NULL,
       DESCRIPTION          VARCHAR2(30) NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_Validation8677
                                          CHECK (ACTIVE IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);


ALTER TABLE EMPLOYEEFUNCTION
       ADD  ( CONSTRAINT XPKEMPLOYEEFUNCTION PRIMARY KEY (
              EMPLOYEEFUNCTION_ID) ) ;

      
      /*
      ACTION is CREATE Table EMPLOYEEFUNCTIONDIALOG
      */

CREATE TABLE EMPLOYEEFUNCTIONDIALOG (
       EMPLOYEEFUNCTION_ID  NUMBER NOT NULL,
       DIALOG_ID            NUMBER NOT NULL,
       SELECTABLE           VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_Validation8678
                                          CHECK (SELECTABLE IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1EMPLOYEEFUNCTIONDIALOG ON EMPLOYEEFUNCTIONDIALOG
(
       EMPLOYEEFUNCTION_ID            ASC
);

CREATE INDEX XIF2EMPLOYEEFUNCTIONDIALOG ON EMPLOYEEFUNCTIONDIALOG
(
       DIALOG_ID                      ASC
);


ALTER TABLE EMPLOYEEFUNCTIONDIALOG
       ADD  ( CONSTRAINT XPKEMPLOYEEFUNCTIONDIALOG PRIMARY KEY (
              EMPLOYEEFUNCTION_ID, DIALOG_ID) ) ;

      
      /*
      CHANGE REPORT for Table EMPLOYEE
          - Adding a new relationship EMPLOYEE_EMPLOYEEFUNCTION_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK EMPLOYEE_EMPLOYEEFUNCTION_ID of table EMPLOYEE, which references table EMPLOYEEFUNCTION).
          - Adding column WORKSPOT
          - Adding column EMPLOYEEFUNCTION_ID
          - Adding index XIF3EMPLOYEE
      ACTION is ALTER or modify Table EMPLOYEE
      */

ALTER TABLE EMPLOYEE ADD (WORKSPOT             NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT Workspot_valid
                                          CHECK (WORKSPOT IN (0, 1)));


ALTER TABLE EMPLOYEE ADD (EMPLOYEEFUNCTION_ID  NUMBER DEFAULT 0 NOT NULL);

CREATE INDEX XIF3EMPLOYEE ON EMPLOYEE
(
       EMPLOYEEFUNCTION_ID            ASC
);


ALTER TABLE EMPLOYEEFUNCTIONDIALOG
       ADD  ( CONSTRAINT EMPLOYEEFUNCTIONDIALOG_DIALOG_
              FOREIGN KEY (DIALOG_ID)
                             REFERENCES DIALOGS ) ;


ALTER TABLE EMPLOYEEFUNCTIONDIALOG
       ADD  ( CONSTRAINT EMPLOYEEFUNCTIONDIALOG_EMPLOYE
              FOREIGN KEY (EMPLOYEEFUNCTION_ID)
                             REFERENCES EMPLOYEEFUNCTION ) ;


INSERT INTO EMPLOYEEFUNCTION(EMPLOYEEFUNCTION_ID,CODE,DESCRIPTION,ACTIVE,SYSTEMUSER_ID,TIMESTAMP)
VALUES(SEQ_EMPLOYEEFUNCTION.NEXTVAL, '-','-', 'Y',getminsystemuser(), getcurrentdatetime());

UPDATE EMPLOYEE E
SET E.EMPLOYEEFUNCTION_ID = (SELECT EMPLOYEEFUNCTION.EMPLOYEEFUNCTION_ID FROM EMPLOYEEFUNCTION WHERE CODE ='-');



insert into employeefunctiondialog (employeefunction_id,dialog_id,selectable,systemuser_id,timestamp)
select e.employeefunction_id , d.dialog_id, 'Y', getminsystemuser(), getcurrentdatetime()
from employeefunction e, dialogs d
 where e.code = '-'  
 and d.code in ( 'Route', 'Truck','RouteStatusControl','DeliveryNote', 'Customer', 'Contract', 'ProductListPerCustomer',  'Batch', 'OutscanSort', 'BatchDeliveryNote','StockRoom');

COMMENT ON COLUMN EMPLOYEE.WORKSPOT IS '0=Own plant only 
1=All plants';


ALTER TABLE EMPLOYEE
       ADD  ( CONSTRAINT EMPLOYEE_EMPLOYEEFUNCTION_ID
              FOREIGN KEY (EMPLOYEEFUNCTION_ID)
                             REFERENCES EMPLOYEEFUNCTION ) ;

