-- ABS-64259 / ABS-73482
--- CONTRACTCOMPONENTGRMCHARGES ---
ALTER TABLE CONTRACTCOMPONENTGRMCHARGES ADD (SPECIALSIZECHARGE VARCHAR2(1) NULL 
					CONSTRAINT YN_VALIDATIONFIELD453 CHECK (SPECIALSIZECHARGE IN ('Y','N')));

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


--- CONTRACTPRICELIST ---
COMMENT ON COLUMN CONTRACTPRICELIST.PRICETYPE IS '0 = Undefined / inherited
1 = Wash
2 = Rent
3 = Replacement
4 = Sales
5 = Weight
6 = Repair
7 = Emblem
8 = Emblem Template
9 = Garment Insurance
10 = Special Charge
11 = Special Size Charge';

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

COMMENT ON COLUMN CUSTOMEREXTRAINFO.SPECIALSIZECHARGE IS 'Only used for module SPECIALSIZECHARGE';

--- GENERALPRICELIST ---
COMMENT ON COLUMN GENERALPRICELIST.PRICETYPE IS '1 = Wash
2 = Rent
3 = Replacement
4 = Sales
5 = Weight
6 = Repair
7 = Emblem
8 = Emblem Template
9 = Garment Insurance
10 = Special Charge
11 = Special Size Charge';


--- GENERALSPECSIZECHARGEPRICE ---
CREATE SEQUENCE SEQ_GENERALSPECSIZECHARGEPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE GENERALSPECSIZECHARGEPRICE
(
	GENERALSPECSIZECHARGEPRICE_ID NUMBER NOT NULL,
	GENERALPRICELIST_ID NUMBER NOT NULL,
	PRODUCT_ID NUMBER NOT NULL,
	SIZEDEFINITION_ID NUMBER NOT NULL,
	STARTDATE DATE NOT NULL,
	PRICE NUMBER(11,6) NOT NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

ALTER TABLE GENERALSPECSIZECHARGEPRICE ADD CONSTRAINT XPKGENERALSPECSIZECHARGEPRICE PRIMARY KEY (GENERALSPECSIZECHARGEPRICE_ID);

CREATE UNIQUE INDEX XAK1GENERALSPECSIZECHARGEPRICE ON GENERALSPECSIZECHARGEPRICE (GENERALPRICELIST_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, STARTDATE ASC);

CREATE INDEX XIF1GENERALSPECSIZECHARGEPRICE ON GENERALSPECSIZECHARGEPRICE (GENERALPRICELIST_ID ASC);
CREATE INDEX XIF2GENERALSPECSIZECHARGEPRICE ON GENERALSPECSIZECHARGEPRICE (PRODUCT_ID ASC);
CREATE INDEX XIF3GENERALSPECSIZECHARGEPRICE ON GENERALSPECSIZECHARGEPRICE (SIZEDEFINITION_ID ASC);

ALTER TABLE GENERALSPECSIZECHARGEPRICE ADD CONSTRAINT GENERALSPECSIZECHARGEPRICE_GPL FOREIGN KEY (GENERALPRICELIST_ID)
REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID) ON DELETE CASCADE;

ALTER TABLE GENERALSPECSIZECHARGEPRICE ADD CONSTRAINT GENERALSPECSIZECHARGEPRICE_PID FOREIGN KEY (PRODUCT_ID)
REFERENCES PRODUCT (PRODUCT_ID);

ALTER TABLE GENERALSPECSIZECHARGEPRICE ADD CONSTRAINT GENERALSPECSIZECHARGEPRICE_SID FOREIGN KEY (SIZEDEFINITION_ID)
REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);

--- ISSUEHISTORY ---
ALTER TABLE ISSUEHISTORY ADD (SPECIALSIZECHARGESTATUS NUMBER DEFAULT 0 NOT NULL
				CONSTRAINT SPECIALSIZECHARGESTATUS_VALID1 CHECK (SPECIALSIZECHARGESTATUS BETWEEN 0 AND 6),
			      SPECIALSIZEPRICE NUMBER NULL);

COMMENT ON COLUMN ISSUEHISTORY.SPECIALSIZECHARGESTATUS IS 'Only used for module SPECIALSIZECHARGE
0 = Charge
1 = Free of Charge
2 = Charge Cancelled
3 = Reason code not charged
4 = In free period
5 = No prep price defined
6 = Customer set as no charge';

COMMENT ON COLUMN ISSUEHISTORY.SPECIALSIZEPRICE IS 'Only used for module SPECIALSIZECHARGE';

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

COMMENT ON COLUMN REASONCODE.SPECIALSIZECHARGE IS 'Only used with module SPECIALSIZECHARGE';
