-- ABS-71966 / ABS-82188
--- SIZEDEFINITION ---
ALTER TABLE SIZEDEFINITION ADD (CUSTOMSIZE VARCHAR2(1) DEFAULT 'N' NOT NULL
				CONSTRAINT CHK_SIZEDEFINITION_CUSTOMSIZE CHECK (CUSTOMSIZE IN ('Y','N')));

--- COLOR ---
ALTER TABLE COLOR ADD (USEDFOREMBLEMDEFINITION NUMBER DEFAULT 0 NOT NULL
			CONSTRAINT CHK_COLOR_USEDFOREMBDEF CHECK (USEDFOREMBLEMDEFINITION IN (0,1,2,3)),
		       USEDFOREMBLEMTYPECOMPANY VARCHAR2(1) DEFAULT 'N' NOT NULL
			CONSTRAINT CHK_COLOR_USEDFOREMBTYPECOMP CHECK (USEDFOREMBLEMTYPECOMPANY IN ('Y','N')),
		       USEDFOREMBLEMTYPENAME VARCHAR2(1) DEFAULT 'N' NOT NULL
			CONSTRAINT CHK_COLOR_USEDFOREMBTYPENAME CHECK (USEDFOREMBLEMTYPENAME IN ('Y','N')),
		       USEDFOREMBLEMTYPEOTHER VARCHAR2(1) DEFAULT 'N' NOT NULL
			CONSTRAINT CHK_COLOR_USEDFOREMBTYPEOTHER CHECK (USEDFOREMBLEMTYPEOTHER IN ('Y','N')),
		       USEDFOREMBLEMTYPETITLE VARCHAR2(1) DEFAULT 'N' NOT NULL
			CONSTRAINT CHK_COLOR_USEDFOREMBTYPETITLE CHECK (USEDFOREMBLEMTYPETITLE IN ('Y','N')),
		       CUSTOMCOLOR VARCHAR2(1) DEFAULT 'N' NOT NULL
			CONSTRAINT CHK_COLOR_CUSTOMCOLOR CHECK (CUSTOMCOLOR IN ('Y','N')));

COMMENT ON COLUMN COLOR.USEDFOREMBLEMDEFINITION IS '0 = Not applicable
1 = Predefined emblem products
2 = User defined emblems
3 = Both';

UPDATE COLOR C
SET C.USEDFOREMBLEMDEFINITION = 3,
    C.USEDFOREMBLEMTYPECOMPANY = 'Y',
    C.USEDFOREMBLEMTYPENAME = 'Y',
    C.USEDFOREMBLEMTYPEOTHER = 'Y',
    C.USEDFOREMBLEMTYPETITLE = 'Y'
WHERE C.USEDFOREMBLEM = 'Y';
COMMIT;

--- EMBLEMTEMPLATELINE ---
ALTER TABLE EMBLEMTEMPLATELINE
DROP CONSTRAINT EMBLEMTYPEEMBLTEMPLLINE_VALID;

ALTER TABLE EMBLEMTEMPLATELINE ADD (CUSTOMCOLOR VARCHAR2(1) DEFAULT 'N' NOT NULL
					CONSTRAINT CHK_EMBTEMPLINE_CUSTOMCOLOR CHECK (CUSTOMCOLOR IN ('Y','N')),
				    CUSTOMSIZE VARCHAR2(1) DEFAULT 'N' NOT NULL
					CONSTRAINT CHK_EMBTEMPLINE_CUSTOMSIZE CHECK (CUSTOMSIZE IN ('Y','N')),
				    DESCRIPTION2 VARCHAR2(80) NULL,
				    STYLECODE NUMBER DEFAULT 0 NOT NULL
					CONSTRAINT CHK_EMBTEMPLINE_STYLECODE CHECK (STYLECODE IN (0,1,2,3)),
				    CUSTOMERSUPPLIED VARCHAR2(1) DEFAULT 'N' NOT NULL
					CONSTRAINT CHK_EMBTEMPLINE_CUSTSUPPLIED CHECK (CUSTOMERSUPPLIED IN ('Y','N')));

COMMENT ON COLUMN EMBLEMTEMPLATELINE.EMBLEMTYPE IS '1= Company
2 = Name
3 = Other/ Not Defined
4 = Title';

COMMENT ON COLUMN EMBLEMTEMPLATELINE.STYLECODE IS '0 = Not applicable
1 = Emblem
2 = Direct embroidery
3 = Screen print';

ALTER TABLE EMBLEMTEMPLATELINE ADD CONSTRAINT CHK_EMBTEMPLINE_EMBTYPE CHECK (EMBLEMTYPE IN (1,2,3,4));