--Order 20010666

ALTER TABLE ROUTEASSISTANTTRANSACTION
  DROP CONSTRAINT TRANSACTIONTYPE_VALID;

ALTER TABLE ROUTEASSISTANTTRANSACTION
  ADD CONSTRAINT TRANSACTIONTYPE_VALID
    CHECK (TRANSACTIONTYPE IN (1, 2, 3, 4,5));

COMMENT ON COLUMN ROUTEASSISTANTTRANSACTION.TRANSACTIONTYPE IS '1 = New contract (PLPC) 
2 = Quit product 
3 = Modify quantity 
4 = Add Temporary delivery stop
5 = Permanent stop change';


CREATE SEQUENCE SEQ_PREDEFINEDCONTACTNOTES
START WITH 1
INCREMENT BY 1;

CREATE TABLE PREDEFINEDCONTACTNOTES
(
	PREDEFINEDCONTACTNOTES_ID NUMBER NOT NULL ,
	SEQUENCENUMBER       NUMBER NOT NULL  
		CONSTRAINT  SequenceNumber_valid CHECK (SEQUENCENUMBER BETWEEN 0 AND 99),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;

ALTER TABLE PREDEFINEDCONTACTNOTES
ADD CONSTRAINT  XPKPREDEFINEDCONTACTNOTES PRIMARY KEY (PREDEFINEDCONTACTNOTES_ID);

CREATE UNIQUE INDEX XAK1PREDEFINEDCONTACTNOTES ON PREDEFINEDCONTACTNOTES
(SEQUENCENUMBER   ASC)
TABLESPACE INDX;

CREATE TABLE PREDEFINEDCONTACTNOTES_DESC
(
	PREDEFINEDCONTACTNOTES_ID NUMBER NOT NULL ,
	LANGUAGE_ID          NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(80) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;

ALTER TABLE PREDEFINEDCONTACTNOTES_DESC
ADD CONSTRAINT  XPKPREDEFINEDCONTACTNOTES_DESC PRIMARY KEY (PREDEFINEDCONTACTNOTES_ID,LANGUAGE_ID);

CREATE  INDEX XIF1PREDEFINEDCONTACTNOTES_DES ON PREDEFINEDCONTACTNOTES_DESC
(PREDEFINEDCONTACTNOTES_ID   ASC) TABLESPACE INDX;

CREATE  INDEX XIF2PREDEFINEDCONTACTNOTES_DES ON PREDEFINEDCONTACTNOTES_DESC
(LANGUAGE_ID   ASC) TABLESPACE INDX;

ALTER TABLE PREDEFINEDCONTACTNOTES_DESC
ADD CONSTRAINT PREDEFINEDCONTACTNOTES_DESC_PR FOREIGN KEY (PREDEFINEDCONTACTNOTES_ID) 
	REFERENCES PREDEFINEDCONTACTNOTES (PREDEFINEDCONTACTNOTES_ID) 
		ON DELETE CASCADE;

ALTER TABLE PREDEFINEDCONTACTNOTES_DESC
ADD CONSTRAINT PREDEFINEDCONTACTNOTES_DESC_LA FOREIGN KEY (LANGUAGE_ID) 
	REFERENCES LANGUAGES (LANGUAGE_ID);








