-- ABS-14381

----------------- ROUTECONTROL -------------------
ALTER TABLE ROUTECONTROL ADD (EMAILSTATUSCNTUSAGERPT NUMBER DEFAULT 0 NOT NULL CONSTRAINT EMAILSTCNTUSRPT_VALID1_1 CHECK (EMAILSTATUSCNTUSAGERPT IN (0,1,2,3)));

COMMENT ON COLUMN ROUTECONTROL.EMAILSTATUSCNTUSAGERPT
  IS '0 = Not applicable
1 = Needs to create PDF
2 = Ready to be sent
3 = Sent';

----------------- DELIVERYPOINT -------------------
ALTER TABLE DELIVERYPOINT ADD (CNTUSAGEREPORTTOEMAIL VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD266 CHECK (CNTUSAGEREPORTTOEMAIL IN ('Y','N')));

----------------- CUSTOMER ------------------------
ALTER TABLE CUSTOMER ADD (CNTUSAGEREPORTTOEMAIL VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD267 CHECK (CNTUSAGEREPORTTOEMAIL IN ('Y','N')),
						  CNTUSAGEREPORTCONTACTPERSON_ID NUMBER NULL);
						  
CREATE INDEX XIF48CUSTOMER ON CUSTOMER (CNTUSAGEREPORTCONTACTPERSON_ID);

ALTER TABLE CUSTOMER
  ADD CONSTRAINT CUST_CURCP_ID FOREIGN KEY (CNTUSAGEREPORTCONTACTPERSON_ID)
  REFERENCES CONTACTPERSON (CONTACTPERSON_ID);

------------------ PROCESS  ------------------------   
INSERT INTO PROCESS (PROCESS_ID,DESCRIPTION,ACTIVE) VALUES (130, 'Send Container Usage Report by E-mail','Y');
COMMIT;

------------------ STOP  ------------------------   
ALTER TABLE STOP ADD (EMAILSTATUSCNTUSAGERPT NUMBER DEFAULT 0 NOT NULL CONSTRAINT EMAILSTATUSCNTUSAGERPT CHECK (EMAILSTATUSCNTUSAGERPT IN (0,1,2,3)),
					 PDFDOCCNTRPT VARCHAR2(1000) NULL);
 
COMMENT ON COLUMN STOP.EMAILSTATUSCNTUSAGERPT
  IS '0 = Not applicable
1 = Needs to create PDF
2 = Ready to be sent
3 = Sent';