--- ABS-18462
-------------------------   ORDERPRIORITY   -------------------------------
ALTER TABLE ORDERPRIORITY ADD (PURCHASECATEGORY NUMBER DEFAULT 0 NOT NULL 
				CONSTRAINT PURCHASECATEGORY_VALID1_1 CHECK (PURCHASECATEGORY BETWEEN 0 AND 5));

COMMENT ON COLUMN ORDERPRIORITY.PURCHASECATEGORY IS '0 – Not applicable
1 – All
2 – New contract
3 – Renewal
4 – Replacement
5 – Increase Stock Level';

update orderpriority op
set op.purchasecategory = 1
where op.ordertype = 2;
commit;

-------------------------   PURCHASEORDERAPPROVAL   -------------------------------
CREATE SEQUENCE SEQ_PURCHASEORDERAPPROVAL
START WITH 1
INCREMENT BY 1;

CREATE TABLE PURCHASEORDERAPPROVAL
(
	PURCHASEORDERAPPROVAL_ID NUMBER NOT NULL,
	PURCHASEORDERHEADER_ID NUMBER NOT NULL,
	APPROVALTIMESTAMP DATE NOT NULL,
	EMPLOYEE_ID NUMBER NOT NULL,
	EMPLOYEEFUNCTION_ID NUMBER NOT NULL,
	APPROVALUSER_ID NUMBER NOT NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

ALTER TABLE PURCHASEORDERAPPROVAL ADD CONSTRAINT XPKPURCHASEORDERAPPROVAL PRIMARY KEY (PURCHASEORDERAPPROVAL_ID);

CREATE UNIQUE INDEX XAK1PURCHASEORDERAPPROVAL ON PURCHASEORDERAPPROVAL(PURCHASEORDERHEADER_ID ASC,APPROVALTIMESTAMP ASC);

CREATE INDEX XIF1PURCHASEORDERAPPROVAL ON PURCHASEORDERAPPROVAL(PURCHASEORDERHEADER_ID ASC);

ALTER TABLE PURCHASEORDERAPPROVAL ADD CONSTRAINT PURCHASEORDERAPPROVAL_POHEADER FOREIGN KEY (PURCHASEORDERHEADER_ID)
REFERENCES PURCHASEORDERHEADER (PURCHASEORDERHEADER_ID) ON DELETE CASCADE;

-------------------------   EMPLOYEE   -------------------------------
ALTER TABLE EMPLOYEE ADD (POAPPROVALPINCODE VARCHAR2(80) NULL);

COMMENT ON COLUMN EMPLOYEE.POAPPROVALPINCODE IS 'PIN code for approval of purchase order. Not always applicable, therefore it may be left empty.';

-------------------------   EMPLOYEEFUNCTIONAUTH   -------------------------------
CREATE SEQUENCE SEQ_EMPLOYEEFUNCTIONAUTH
START WITH 1
INCREMENT BY 1;

CREATE TABLE EMPLOYEEFUNCTIONAUTH
(
	EMPLOYEEFUNCTIONAUTH_ID NUMBER NOT NULL,
	EMPLOYEEFUNCTION_ID NUMBER NOT NULL,
	ORDERTYPE NUMBER DEFAULT 1 NOT NULL CONSTRAINT ORDERTYPE_VALID1_1 CHECK (ORDERTYPE BETWEEN 1 AND 3),
	AUTHORIZATIONLEVEL NUMBER DEFAULT 2 NOT NULL CONSTRAINT AUTHORIZATIONLEVEL_VALID1_1 CHECK (AUTHORIZATIONLEVEL BETWEEN 0 AND 2),
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN EMPLOYEEFUNCTIONAUTH.ORDERTYPE IS '1 – Purchase Order
2 – Work Order for Pool
3 – Sales Order';


COMMENT ON COLUMN EMPLOYEEFUNCTIONAUTH.AUTHORIZATIONLEVEL IS '0 – No authorization
1 – Limited authorization
2 – Full authorization';

ALTER TABLE EMPLOYEEFUNCTIONAUTH ADD CONSTRAINT XPKEMPLOYEEFUNCTIONAUTH PRIMARY KEY (EMPLOYEEFUNCTIONAUTH_ID);

CREATE UNIQUE INDEX XAK1EMPLOYEEFUNCTIONAUTH ON EMPLOYEEFUNCTIONAUTH(EMPLOYEEFUNCTION_ID ASC, ORDERTYPE ASC);

CREATE INDEX XIF1EMPLOYEEFUNCTIONAUTH ON EMPLOYEEFUNCTIONAUTH(EMPLOYEEFUNCTION_ID ASC);

ALTER TABLE EMPLOYEEFUNCTIONAUTH ADD CONSTRAINT EMPLOYEEFUNCTIONAUTH_EMPF_ID  FOREIGN KEY (EMPLOYEEFUNCTION_ID) REFERENCES EMPLOYEEFUNCTION (EMPLOYEEFUNCTION_ID)  ON DELETE CASCADE;

INSERT INTO EMPLOYEEFUNCTIONAUTH
SELECT SEQ_EMPLOYEEFUNCTIONAUTH.NEXTVAL,
EMPLOYEEFUNCTION.EMPLOYEEFUNCTION_ID,
1,
2,
GETMINSYSTEMUSER(),
TIMESTAMP
FROM EMPLOYEEFUNCTION;
COMMIT;

INSERT INTO EMPLOYEEFUNCTIONAUTH
SELECT SEQ_EMPLOYEEFUNCTIONAUTH.NEXTVAL,
EMPLOYEEFUNCTION.EMPLOYEEFUNCTION_ID,
2,
2,
GETMINSYSTEMUSER(),
TIMESTAMP
FROM EMPLOYEEFUNCTION;
COMMIT;

INSERT INTO EMPLOYEEFUNCTIONAUTH
SELECT SEQ_EMPLOYEEFUNCTIONAUTH.NEXTVAL,
EMPLOYEEFUNCTION.EMPLOYEEFUNCTION_ID,
3,
2,
GETMINSYSTEMUSER(),
TIMESTAMP
FROM EMPLOYEEFUNCTION;
COMMIT;


-------------------------   EMPLOYEEFUNCTIONAUTHDEF  -------------------------------
CREATE SEQUENCE SEQ_EMPLOYEEFUNCTIONAUTHDEF
START WITH 1
INCREMENT BY 1;

CREATE TABLE EMPLOYEEFUNCTIONAUTHDEF
(
	EMPLOYEEFUNCTIONAUTHDEF_ID NUMBER NOT NULL,
	EMPLOYEEFUNCTIONAUTH_ID NUMBER NOT NULL,
	STARTDATE DATE DEFAULT SYSDATE NOT NULL,
	ORDERPRIORITYCATEGORY NUMBER DEFAULT 1 NOT NULL CONSTRAINT ORDERPRIORITYCATEGORY_VALID1_1 CHECK (ORDERPRIORITYCATEGORY BETWEEN 1 AND 5),
	AUTHORIZATIONTYPE NUMBER DEFAULT 1 NOT NULL CONSTRAINT AUTHORIZATIONTYPE_VALID1_1 CHECK (AUTHORIZATIONTYPE BETWEEN 1 AND 2),
	PERIODDEFINITION NUMBER DEFAULT 0 NOT NULL CONSTRAINT PERIODDEFINITION_VALID1_1 CHECK (PERIODDEFINITION BETWEEN 0 AND 6),
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);


COMMENT ON COLUMN EMPLOYEEFUNCTIONAUTHDEF.ORDERPRIORITYCATEGORY IS '1 – All
2 – New contract
3 – Renewal
4 – Replacement
5 – Increase Stock Level';

COMMENT ON COLUMN EMPLOYEEFUNCTIONAUTHDEF.AUTHORIZATIONTYPE IS '1 – Per order
2 – Per period';

COMMENT ON COLUMN EMPLOYEEFUNCTIONAUTHDEF.PERIODDEFINITION IS '0 – Not applicable
1 – Per day
2 – Per calendar week
3 – Per calendar month
4 – Per calendar quarter
5 – Per calendar half year
6 – Per calendar year';

ALTER TABLE EMPLOYEEFUNCTIONAUTHDEF ADD CONSTRAINT XPKEMPLOYEEFUNCTIONAUTHDEF PRIMARY KEY(EMPLOYEEFUNCTIONAUTHDEF_ID);

CREATE UNIQUE INDEX XAK1EMPLOYEEFUNCTIONAUTHDEF ON EMPLOYEEFUNCTIONAUTHDEF(EMPLOYEEFUNCTIONAUTH_ID ASC, STARTDATE ASC, ORDERPRIORITYCATEGORY ASC, AUTHORIZATIONTYPE ASC);

CREATE INDEX XIF1EMPLOYEEFUNCTIONAUTHDEF ON EMPLOYEEFUNCTIONAUTHDEF(EMPLOYEEFUNCTIONAUTH_ID ASC);

ALTER TABLE EMPLOYEEFUNCTIONAUTHDEF ADD CONSTRAINT EMPLOYEEFUNCTIONAUTHDEF_AUTHID FOREIGN KEY(EMPLOYEEFUNCTIONAUTH_ID) REFERENCES EMPLOYEEFUNCTIONAUTH(EMPLOYEEFUNCTIONAUTH_ID) ON DELETE CASCADE;

ALTER TABLE EMPLOYEEFUNCTIONAUTHDEF ADD CONSTRAINT EMPLOYEEFUNCTIONAUTHDEF_CHECK CHECK ((AUTHORIZATIONTYPE=1 AND PERIODDEFINITION=0) OR (AUTHORIZATIONTYPE=2 AND PERIODDEFINITION BETWEEN 1 AND 6));


-------------------------   EMPLOYEEFUNCTIONAUTHDEFDET  -------------------------------
CREATE SEQUENCE SEQ_EMPLOYEEFUNCTIONAUTHDEFDET
START WITH 1
INCREMENT BY 1;

CREATE TABLE EMPLOYEEFUNCTIONAUTHDEFDET
(
	EMPLOYEEFUNCTIONAUTHDEFDET_ID NUMBER NOT NULL,
	EMPLOYEEFUNCTIONAUTHDEF_ID NUMBER NOT NULL,
	CURRENCY_ID NUMBER NOT NULL,
	MAXORDERAMOUNT NUMBER DEFAULT 0 NOT NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

ALTER TABLE EMPLOYEEFUNCTIONAUTHDEFDET ADD CONSTRAINT XPKEMPLOYEEFUNCTIONAUTHDEFDET PRIMARY KEY (EMPLOYEEFUNCTIONAUTHDEFDET_ID);

CREATE UNIQUE INDEX XAK1EMPLOYEEFUNCTIONAUTHDEFDET ON EMPLOYEEFUNCTIONAUTHDEFDET(EMPLOYEEFUNCTIONAUTHDEF_ID ASC, CURRENCY_ID ASC);

CREATE INDEX XIF1EMPLOYEEFUNCTIONAUTHDEFDET ON EMPLOYEEFUNCTIONAUTHDEFDET(EMPLOYEEFUNCTIONAUTHDEF_ID ASC);

CREATE INDEX XIF2EMPLOYEEFUNCTIONAUTHDEFDET ON EMPLOYEEFUNCTIONAUTHDEFDET(CURRENCY_ID ASC);

ALTER TABLE EMPLOYEEFUNCTIONAUTHDEFDET ADD CONSTRAINT EMPLOYEEFUNCTIONAUTHDEFDET_AD FOREIGN KEY (EMPLOYEEFUNCTIONAUTHDEF_ID) 
REFERENCES EMPLOYEEFUNCTIONAUTHDEF(EMPLOYEEFUNCTIONAUTHDEF_ID) ON DELETE CASCADE;

ALTER TABLE EMPLOYEEFUNCTIONAUTHDEFDET ADD CONSTRAINT EMPLOYEEFUNCTIONAUTHDEFDET_C FOREIGN KEY (CURRENCY_ID)
REFERENCES CURRENCY(CURRENCY_ID) ON DELETE CASCADE;

