-- ABS-75843 / ABS-93129
--- CONTRACTCOMPONENTGRMCHARGES ---
ALTER TABLE CONTRACTCOMPONENTGRMCHARGES ADD (CHARGEEXCHANGECHARGE VARCHAR2(1) NULL
						CONSTRAINT CHK_CONCOMPGRM_CHARGEEXCH CHECK (CHARGEEXCHANGECHARGE IN ('Y','N')));

COMMENT ON COLUMN CONTRACTCOMPONENTGRMCHARGES.CHARGEEXCHANGECHARGE IS 'Only used for module GARMENTEXCHANGECHARGE';

--- CONTRACTPRICE ---
ALTER TABLE CONTRACTPRICE DROP CONSTRAINT CHK_CONTRACTPRICE_CATEGORY;

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 = CashDelayCharge
10 = Exchange Charge';

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

--- CUSTOMEREXCHANGECHARGE ---
CREATE SEQUENCE SEQ_CUSTOMEREXCHANGECHARGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMEREXCHANGECHARGE
(
CUSTOMEREXCHANGECHARGE_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 CUSTOMEREXCHANGECHARGE ADD CONSTRAINT XPKCUSTOMEREXCHANGECHARGE PRIMARY KEY (CUSTOMEREXCHANGECHARGE_ID);

CREATE UNIQUE INDEX XAK1CUSTOMEREXCHANGECHARGE ON CUSTOMEREXCHANGECHARGE (CUSTOMER_ID ASC, STARTDATE ASC);
CREATE INDEX XIF1CUSTOMEREXCHANGECHARGE ON CUSTOMEREXCHANGECHARGE (CUSTOMER_ID ASC);

ALTER TABLE CUSTOMEREXCHANGECHARGE ADD CONSTRAINT FK_CUSTEXCHCHARGE_CUST FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

--- CUSTOMEREXTRAINFO ---
ALTER TABLE CUSTOMEREXTRAINFO ADD (CHARGEEXCHANGECHARGE VARCHAR2(1) DEFAULT 'N' NOT NULL
					CONSTRAINT CHK_CUSTEXTRAINFO_CHARGEEXCHA CHECK (CHARGEEXCHANGECHARGE IN ('Y','N')));

COMMENT ON COLUMN CUSTOMEREXTRAINFO.CHARGEEXCHANGECHARGE IS 'Only used for the module GARMENTEXCHANGECHARGE';

--- ISSUEHISTORY ---
ALTER TABLE ISSUEHISTORY ADD (EXCHANGECHARGESTATUS NUMBER DEFAULT 0 NOT NULL
				CONSTRAINT CHK_ISSUEHIST_EXCHANGECH CHECK (EXCHANGECHARGESTATUS BETWEEN 0 AND 6),
			      EXCHANGECHARGEPRICE NUMBER NULL);

COMMENT ON COLUMN ISSUEHISTORY.EXCHANGECHARGESTATUS IS 'Only used for module GARMENTEXCHANGECHARGE 
0 = Charge
1 = Free of Charge
2 = Charge Cancelled
3 = Reason code not charged
4 = not used (but kept for commonality with issuesstatus)
5 = No exchange charge price defined
6 = Customer set as no charge';

COMMENT ON COLUMN ISSUEHISTORY.EXCHANGECHARGEPRICE IS 'Only used for module GARMENTEXCHANGECHARGE';

--- REASONCODE ---
ALTER TABLE REASONCODE ADD (CHARGEEXCHANGECHARGE VARCHAR2(1) DEFAULT 'N' NOT NULL
				CONSTRAINT CHK_REASONCODE_CHARGEEXCH CHECK (CHARGEEXCHANGECHARGE IN ('Y','N')));

COMMENT ON COLUMN REASONCODE.CHARGEEXCHANGECHARGE IS 'Only used with module GARMENTEXCHANGECHARGE. Determines if the exchange charge is being charged for a replacement';



