-- ABS-75847
--- CONTRACTCOMPONENTPAYMENT ---
ALTER TABLE CONTRACTCOMPONENTPAYMENT ADD (CASHDELAYCHARGE VARCHAR2(1) DEFAULT 'N' NOT NULL
					CONSTRAINT CHK_CONTRACTCOMPPAY_CASHDELAY CHECK (CASHDELAYCHARGE IN ('Y','N')));

--- CUSTOMER ---
ALTER TABLE CUSTOMER ADD (CASHDELAYCHARGE VARCHAR2(1) DEFAULT 'N' NOT NULL
			CONSTRAINT CHK_CUSTOMER_CASHDELAY CHECK (CASHDELAYCHARGE IN ('Y','N')));

--- CONTRACTPRICE ---
ALTER TABLE CONTRACTPRICE DROP CONSTRAINT CATEGORY_VALID1_2;

COMMENT ON COLUMN CONTRACTPRICE.CATEGORY IS '1 = Service Costs LOG
2 = Insurance - Weekly fee per piece
3 = Restocking Fee
4 = Stock Charge
5 = Locker
6 = General Prep Charge
7 = Surcharge Price
8 = Label Price
9 = Cash Delay Charge';

ALTER TABLE CONTRACTPRICE ADD CONSTRAINT CHK_CONTRACTPRICE_CATEGORY CHECK (CATEGORY BETWEEN 1 AND 9);

--- CUSTOMERCASHDELAYCHARGE ---
CREATE SEQUENCE SEQ_CUSTOMERCASHDELAYCHARGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERCASHDELAYCHARGE
(
CUSTOMERCASHDELAYCHARGE_ID NUMBER NOT NULL,
CUSTOMER_ID NUMBER NOT NULL,
STARTDATE DATE NOT NULL,
PRICE NUMBER(15,6) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE CUSTOMERCASHDELAYCHARGE ADD CONSTRAINT XPKCUSTOMERCASHDELAYCHARGE PRIMARY KEY (CUSTOMERCASHDELAYCHARGE_ID);

CREATE UNIQUE INDEX XAK1CUSTOMERCASHDELAYCHARGE ON CUSTOMERCASHDELAYCHARGE (CUSTOMER_ID ASC, STARTDATE ASC);
CREATE INDEX XIF1CUSTOMERCASHDELAYCHARGE ON CUSTOMERCASHDELAYCHARGE (CUSTOMER_ID ASC);

ALTER TABLE CUSTOMERCASHDELAYCHARGE ADD CONSTRAINT FK_CUSTCASHDELAY_CUST FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;