--Order 20011857.100


--CONTACTRESOLUTION and CONTACTRESOLUTION_DESC tables

CREATE SEQUENCE SEQ_CONTACTRESOLUTION
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTACTRESOLUTION
(
	CONTACTRESOLUTION_ID NUMBER NOT NULL ,
	CODE                 VARCHAR2(6) NOT NULL ,
	DEFAULTRESOLUTION    VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField61 CHECK (DEFAULTRESOLUTION IN ('Y', 'N')),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);


CREATE UNIQUE INDEX XAK1CONTACTRESOLUTION ON CONTACTRESOLUTION
(CODE   ASC);

ALTER TABLE CONTACTRESOLUTION
ADD CONSTRAINT  XPKCONTACTRESOLUTION PRIMARY KEY (CONTACTRESOLUTION_ID);



CREATE TABLE CONTACTRESOLUTION_DESC
(
	CONTACTRESOLUTION_ID NUMBER NOT NULL ,
	LANGUAGE_ID          NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(40) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

CREATE  INDEX XIF1CONTACTRESOLUTION_DESC ON CONTACTRESOLUTION_DESC
(CONTACTRESOLUTION_ID   ASC);

CREATE  INDEX XIF2CONTACTRESOLUTION_DESC ON CONTACTRESOLUTION_DESC
(LANGUAGE_ID   ASC);


ALTER TABLE CONTACTRESOLUTION_DESC
ADD CONSTRAINT  XPKCONTACTRESOLUTION_DESC PRIMARY KEY (CONTACTRESOLUTION_ID,LANGUAGE_ID);

ALTER TABLE CONTACTRESOLUTION_DESC
ADD CONSTRAINT CONTACTRES_CONTACTRES_DESC FOREIGN KEY (CONTACTRESOLUTION_ID) REFERENCES CONTACTRESOLUTION (CONTACTRESOLUTION_ID);

ALTER TABLE CONTACTRESOLUTION_DESC
ADD CONSTRAINT LANGUAGES_CONTACTRES_DESC FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);


--CONTACTCAUSE and CONTACTCAUSE_DESC tableS

CREATE SEQUENCE SEQ_CONTACTCAUSE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTACTCAUSE
(
	CONTACTCAUSE_ID      NUMBER NOT NULL ,
	CODE                 VARCHAR2(6) NOT NULL ,
	DEFAULTCAUSE         VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField62 CHECK (DEFAULTCAUSE IN ('Y', 'N')),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);


CREATE UNIQUE INDEX XAK1CONTACTCAUSE ON CONTACTCAUSE
(CODE   ASC);

ALTER TABLE CONTACTCAUSE
ADD CONSTRAINT  XPKCONTACTCAUSE PRIMARY KEY (CONTACTCAUSE_ID);


CREATE TABLE CONTACTCAUSE_DESC
(
	CONTACTCAUSE_ID      NUMBER NOT NULL ,
	LANGUAGE_ID          NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(40) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);


CREATE  INDEX XIF1CONTACTCAUSE_DESC ON CONTACTCAUSE_DESC
(CONTACTCAUSE_ID   ASC);

CREATE  INDEX XIF2CONTACTCAUSE_DESC ON CONTACTCAUSE_DESC
(LANGUAGE_ID   ASC);

ALTER TABLE CONTACTCAUSE_DESC
ADD CONSTRAINT  XPKCONTACTCAUSE_DESC PRIMARY KEY (CONTACTCAUSE_ID,LANGUAGE_ID);

ALTER TABLE CONTACTCAUSE_DESC
ADD CONSTRAINT CONTACTCAUSE_CONTACTCAUSE_DESC FOREIGN KEY (CONTACTCAUSE_ID) REFERENCES CONTACTCAUSE (CONTACTCAUSE_ID);

ALTER TABLE CONTACTCAUSE_DESC
ADD CONSTRAINT LANGUAGES_CONTACTCAUSE_DESC FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);


--CONTACT table

ALTER TABLE CONTACT
	ADD (CONTACTCAUSE_ID NUMBER NULL);

ALTER TABLE CONTACT
	ADD (CONTACTRESOLUTION_ID NUMBER NULL);

CREATE  INDEX XIF10CONTACT ON CONTACT
(CONTACTCAUSE_ID   ASC);

CREATE  INDEX XIF11CONTACT ON CONTACT
(CONTACTRESOLUTION_ID   ASC);

ALTER TABLE CONTACT
ADD CONSTRAINT CONTACT_CONTACTCAUSE FOREIGN KEY (CONTACTCAUSE_ID) REFERENCES CONTACTCAUSE (CONTACTCAUSE_ID) ON DELETE SET NULL;

ALTER TABLE CONTACT
ADD CONSTRAINT CONTACT_CONTACTRESOLUTION FOREIGN KEY (CONTACTRESOLUTION_ID) REFERENCES CONTACTRESOLUTION (CONTACTRESOLUTION_ID) ON DELETE SET NULL;