--Order 20012743.80

------EMAILSETTING-----------

ALTER TABLE EMAILSETTING
	DROP CONSTRAINT DOCUMENTTYPEEMAILSETT_VALID;


ALTER TABLE EMAILSETTING
	ADD CONSTRAINT DOCUMENTTYPEEMAILSETT_VALID  CHECK (DOCUMENTTYPE BETWEEN 1 AND 12);


COMMENT ON COLUMN EMAILSETTING.DOCUMENTTYPE IS '1 = Invoice
2 = Statement
3 = Purchase order
4 = E-service password reminder
5 = Work order via web
6 = Invoice job checklist
7 = Customer
8 = Log Message
9 = Delivery Note / Consignment Note
10 = Stock Count / Order
11 = Wash List
12 = POD (Proof of delivery)';

--------CONTACTPERSON--------

ALTER TABLE CONTACTPERSON
	ADD (PODCONTACT NUMBER  DEFAULT 0 NOT NULL CONSTRAINT PODContact_valid1_1 CHECK (PODCONTACT IN (0, 1, 2)));

COMMENT ON COLUMN CONTACTPERSON.PODCONTACT IS '0 = Not applicable
1 = Person is contact for POD, no automatic email confirmation
2 = Person is contact for POD, send email with POD for confirmation.';


--------PACKINGNOTEPOD-------

CREATE TABLE PACKINGNOTEPOD
(
	PACKINGNOTE_ID       NUMBER NOT NULL ,
	EXTERNALID           VARCHAR2(255) NOT NULL ,
	TASKID               VARCHAR2(255) NOT NULL ,
	BINARYCONTENT        BLOB NULL ,
	DOCUMENTPATH         VARCHAR2(80) NULL ,
	DOCUMENTSIZE         NUMBER DEFAULT  0  NOT NULL ,
	EMAILSTATUS          NUMBER(1) DEFAULT  0  NOT NULL  CONSTRAINT  EmailStatus_valid1_1 CHECK (EMAILSTATUS IN (0, 1, 2, 3)),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN PACKINGNOTEPOD.EXTERNALID IS 'The ExternalID that is used in the OMD system. Used only for redisplaying in the maintain delivery notes screen, and possibly for printing on reports.';

COMMENT ON COLUMN PACKINGNOTEPOD.TASKID IS 'The TaskID that is used in the OMD system.';

COMMENT ON COLUMN PACKINGNOTEPOD.BINARYCONTENT IS 'This field is used to store the PDF file for archiving and possible redisplay.';

COMMENT ON COLUMN PACKINGNOTEPOD.DOCUMENTPATH IS 'In case the document is not stored in the database it can be stored somewhere on the internet. The URL is stored here to retrieve the document.';

COMMENT ON COLUMN PACKINGNOTEPOD.EMAILSTATUS IS '0 = Not processed
1 = Processed / send
2 = Processed / archived
3 = Error, not send';

CREATE  INDEX XIE1PACKINGNOTEPOD ON PACKINGNOTEPOD
(EMAILSTATUS   ASC);

ALTER TABLE PACKINGNOTEPOD
ADD CONSTRAINT  XPKPACKINGNOTEPOD PRIMARY KEY (PACKINGNOTE_ID);

ALTER TABLE PACKINGNOTEPOD
ADD CONSTRAINT PACKINGNOTEPOD_PACKINGNOTE FOREIGN KEY (PACKINGNOTE_ID) REFERENCES PACKINGNOTE (PACKINGNOTE_ID) ON DELETE CASCADE;

-------insert process------

insert into process(process_id,description,active) values ('115','Email POD','Y');
commit;
