--SO 20012307.60 Color Dialog - Country Selection

ALTER TABLE COLOR
	ADD (EMBLEMBACKGROUND VARCHAR2(1)  DEFAULT 'Y' NOT NULL CONSTRAINT EMBLEMBACKGROUND_val CHECK (EMBLEMBACKGROUND IN ('Y','N')),
	     EMBLEMTEXT VARCHAR2(1)  DEFAULT 'Y' NOT NULL CONSTRAINT EMBLEMTEXT_valid CHECK (EMBLEMTEXT IN ('Y','N')),
	     EMBLEMBORDER VARCHAR2(1)  DEFAULT 'Y' NOT NULL CONSTRAINT EMBLEMBORDER_valid CHECK (EMBLEMBORDER IN ('Y','N')),
             USEDFOREMBROIDERY VARCHAR2(1)  DEFAULT 'Y' NOT NULL CONSTRAINT USEDFOREMBROIDERY_v CHECK (USEDFOREMBROIDERY IN ('Y','N')),
	     COUNTRY_ID NUMBER,
	     ONLYINBUSINESSUNIT VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT ONLYINBUSINESSUNIT_ CHECK (ONLYINBUSINESSUNIT IN ('Y','N')));

ALTER TABLE COLOR
ADD CONSTRAINT COLOR_COUNTRY FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRY (COUNTRY_ID);


CREATE  INDEX XIF1COLOR ON COLOR
(COUNTRY_ID   ASC);

UPDATE COLOR C
SET C.EMBLEMBACKGROUND='N', C.EMBLEMTEXT='N', C.EMBLEMBORDER='N', C.USEDFOREMBROIDERY='N'
WHERE C.USEDFOREMBLEM='N';

COMMIT;

CREATE TABLE BUSINESSUNITSPERCOLOR
(
	COLOR_ID             NUMBER NOT NULL ,
	BUSINESSUNIT_ID      NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);


ALTER TABLE BUSINESSUNITSPERCOLOR
ADD CONSTRAINT  XPKBUSINESSUNITSPERCOLOR PRIMARY KEY (COLOR_ID,BUSINESSUNIT_ID);

ALTER TABLE BUSINESSUNITSPERCOLOR
ADD CONSTRAINT BUSINESSUNITSPERCOLOR_COLOR FOREIGN KEY (COLOR_ID) REFERENCES COLOR (COLOR_ID) ON DELETE CASCADE;

CREATE  INDEX XIF1BUSINESSUNITSPERCOLOR ON BUSINESSUNITSPERCOLOR
(COLOR_ID   ASC);

ALTER TABLE BUSINESSUNITSPERCOLOR
ADD CONSTRAINT BUSINESSUNITSPERCOLOR_BU FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;

CREATE  INDEX XIF2BUSINESSUNITSPERCOLOR ON BUSINESSUNITSPERCOLOR
(BUSINESSUNIT_ID   ASC);

