--Order 20014398.80

---PRODUCTLISTPERCUSTOMER table---

ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (COMMISSIONPAYABLE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD231
  CHECK (COMMISSIONPAYABLE IN ('Y', 'N')));
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (COMMISSIONENDDATE DATE NULL);

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.COMMISSIONENDDATE
  IS 'End date for calculation of commission';


---ARTRANSACTIONCOMMISSION table---

ALTER TABLE ARTRANSACTIONCOMMISSION ADD (AMOUNT NUMBER(15,2));

---COMMISSIONDETAIL table---

CREATE SEQUENCE SEQ_COMMISSIONDETAIL
START WITH 1
INCREMENT BY 1;

CREATE TABLE COMMISSIONDETAIL
(
       COMMISSIONDETAIL_ID NUMBER NOT NULL,
       INVOICELINE_ID      NUMBER NOT NULL,
       EMPLOYEE_ID         NUMBER NOT NULL,
       ARTRANSACTION_ID    NUMBER NOT NULL,
       TURNOVERGROUP_ID    NUMBER NOT NULL,
       OVERAMOUNT          NUMBER(15,2) NOT NULL,
       PERCENTAGE          NUMBER(6,2) NOT NULL,
       AMOUNT              NUMBER(15,2) NOT NULL,
       COMMISSIONROLE      NUMBER DEFAULT 0 NOT NULL CONSTRAINT COMMISSIONROLE_Valid1_1 CHECK (COMMISSIONROLE IN (0, 1, 2)),
       SYSTEMUSER_ID       NUMBER NOT NULL,
       TIMESTAMP           DATE NOT NULL
);

COMMENT ON COLUMN COMMISSIONDETAIL.OVERAMOUNT IS
'Amount over which commission is calculated.';

COMMENT ON COLUMN COMMISSIONDETAIL.PERCENTAGE IS
'Commission percentage';

COMMENT ON COLUMN COMMISSIONDETAIL.AMOUNT IS
'Commission amount';

COMMENT ON COLUMN COMMISSIONDETAIL.COMMISSIONROLE IS
'0 = Sales
1 = Account manager
2 = Driver
';

ALTER TABLE COMMISSIONDETAIL
ADD CONSTRAINT  XPKCOMMISSIONDETAIL PRIMARY KEY (COMMISSIONDETAIL_ID);

CREATE UNIQUE INDEX XAK1COMMISSIONDETAIL ON COMMISSIONDETAIL
(INVOICELINE_ID   ASC,EMPLOYEE_ID  ASC,COMMISSIONROLE  ASC);

CREATE INDEX XIE1COMMISSIONDETAIL ON COMMISSIONDETAIL
(ARTRANSACTION_ID   ASC,EMPLOYEE_ID  ASC,TURNOVERGROUP_ID  ASC);

CREATE INDEX XIF1COMMISSIONDETAIL ON COMMISSIONDETAIL
(INVOICELINE_ID   ASC);

CREATE INDEX XIF2COMMISSIONDETAIL ON COMMISSIONDETAIL
(EMPLOYEE_ID   ASC);

CREATE INDEX XIF3COMMISSIONDETAIL ON COMMISSIONDETAIL
(ARTRANSACTION_ID   ASC);

CREATE INDEX XIF4COMMISSIONDETAIL ON COMMISSIONDETAIL
(TURNOVERGROUP_ID   ASC);

ALTER TABLE COMMISSIONDETAIL
ADD CONSTRAINT COMMISSIONDETAIL_INVOICELINE FOREIGN KEY (INVOICELINE_ID) REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;

ALTER TABLE COMMISSIONDETAIL
ADD CONSTRAINT COMMISSIONDETAIL_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE (EMPLOYEE_ID) ON DELETE CASCADE;

ALTER TABLE COMMISSIONDETAIL
ADD CONSTRAINT COMMISSIONDETAIL_ARTRANS FOREIGN KEY (ARTRANSACTION_ID) REFERENCES ARTRANSACTION (ARTRANSACTION_ID) ON DELETE CASCADE;

ALTER TABLE COMMISSIONDETAIL
ADD CONSTRAINT COMMISSIONDETAIL_TURNOVERGR FOREIGN KEY (TURNOVERGROUP_ID) REFERENCES TURNOVERGROUP (TURNOVERGROUP_ID);


---EMAILSETTING table---

COMMENT ON COLUMN EMAILSETTING.DOCUMENTTYPE
  IS '1 = Invoice
2 = Statement
3 = Purchase order
4 = E-service password reminder
5 = Work order via web
6 = Invoice job checklist
7 = Customer
8 = Log Message
9 = Delivery Note / Consignment Note
10 = Stock Count / Order
11 = Wash List
12 = POD (Proof of delivery)
13 = On site soil scan list
14 = Shipment List
15 = Commission Report
16 = Wearer Web Login Credentials';