--Order 20015455.80

ALTER TABLE CUSTOMER ADD (
BONUSPAIDTO     NUMBER DEFAULT 0 NOT NULL CONSTRAINT BONUSPAIDTO_VALID CHECK (BONUSPAIDTO IN (0, 1, 2, 3, 4)),
BONUSCUSTOMER_ID   NUMBER NULL,
BONUSPAYABLE     VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD234 CHECK (BONUSPAYABLE  IN ('Y' , 'N')),
BONUS       NUMBER(5,2) DEFAULT 0.00 NOT NULL CONSTRAINT BONUS_VALID CHECK (BONUS BETWEEN 0.00 AND 100.00),
BONUSINTERVAL     NUMBER(2) DEFAULT 0 NOT NULL CONSTRAINT BONUSINTERVAL_VALID CHECK (BONUSINTERVAL BETWEEN 0 AND 12),
BONUSREMARK     VARCHAR2(500) NULL);

ALTER TABLE CUSTOMER
ADD CONSTRAINT CUSTOMER_BONUSCUST FOREIGN KEY (BONUSCUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE SET NULL;

COMMENT ON COLUMN CUSTOMER.BONUSPAIDTO
  IS '0 = Not applicable
1 = Customer (self)
2 = Bill to Customer
3 = Master Account
4 = Other customer';

COMMENT ON COLUMN CUSTOMER.BONUSCUSTOMER_ID
  IS 'Only filled if BonusPaidTo = 4';

COMMENT ON COLUMN CUSTOMER.BONUS
  IS 'Between 0.00 and 100.00, 2 decimals';

COMMENT ON COLUMN CUSTOMER.BONUSINTERVAL
  IS 'Between 0 and 12 (Monthly interval)';