--Order 20011070.80

----------------PACKINGNOTE---------------

ALTER TABLE PACKINGNOTE
	ADD (EMAILSTATUS NUMBER  DEFAULT 0 NOT NULL CONSTRAINT EmailStatus_valid1 CHECK (EMAILSTATUS IN (0, 1, 2, 3)));



ALTER TABLE PACKINGNOTE
	ADD (EMAILMAYBESENT VARCHAR2(1)  DEFAULT 'Y' NOT NULL CONSTRAINT YN_ValidationField39 CHECK (EMAILMAYBESENT IN ('Y', 'N')));


----------------DEPARTMENT---------------

ALTER TABLE DEPARTMENT
	ADD (CONSIGNMENTNOTETOEMAIL VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField41 CHECK (CONSIGNMENTNOTETOEMAIL IN ('Y', 'N')));


COMMENT ON COLUMN DEPARTMENT.CONSIGNMENTNOTETOEMAIL IS 'Flag that indicates whether the consignment note should be send to contact person via e-mail.
Field is only accessible in client when an e-mail address has been specified for contact person and consignment note is to be created on department level (Customer.ConsignmentNoteLevel = 2).';


----------------CONSIGNMENTNOTE----------

ALTER TABLE CONSIGNMENTNOTE
	ADD (EMAILSTATUS NUMBER  DEFAULT 0 NOT NULL CONSTRAINT EmailStatus_valid2 CHECK (EMAILSTATUS IN (0, 1, 2, 3)));


----------------CUSTOMER-----------------
ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE DISABLE;

ALTER TABLE CUSTOMER
	ADD (CONSIGNMENTCONTACTPERSON_ID NUMBER NULL);



ALTER TABLE CUSTOMER
	ADD (CONSIGNMENTNOTETOEMAIL VARCHAR2(1)  DEFAULT 'N' NOT NULL);


COMMENT ON COLUMN CUSTOMER.CONSIGNMENTCONTACTPERSON_ID IS 'Field is only accessible in client when a consignment note is to be created on customer level (ConsignmentNoteLevel = 1)';


COMMENT ON COLUMN CUSTOMER.CONSIGNMENTNOTETOEMAIL IS 'Flag that indicates whether the consignment note should be send to contact person via e-mail.
Field is only accessible in client when an e-mail address has been specified for contact person and consignment note is to be created on customer level (ConsignmentNoteLevel = 1)';


CREATE INDEX XIF40CUSTOMER on CUSTOMER (CONSIGNMENTCONTACTPERSON_ID)
  TABLESPACE INDX;


ALTER TABLE CUSTOMER
ADD CONSTRAINT CUSTOMER_CONTACTPERSON FOREIGN KEY (CONSIGNMENTCONTACTPERSON_ID) REFERENCES CONTACTPERSON (CONTACTPERSON_ID);


----------------DELIVERYPOINT------------

ALTER TABLE DELIVERYPOINT
	ADD (CONTACTPERSON_ID NUMBER NULL);



ALTER TABLE DELIVERYPOINT
	ADD (CONSIGNMENTNOTETOEMAIL VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField40 CHECK (CONSIGNMENTNOTETOEMAIL IN ('Y', 'N')));


COMMENT ON COLUMN DELIVERYPOINT.CONSIGNMENTNOTETOEMAIL IS 'Flag that indicates whether the consignment note should be send to contact person via e-mail.
Field is only accessible in client when an e-mail address has been specified for contact person and consignment note is to be created on delivery point level.';


CREATE INDEX XIF3DELIVERYPOINT on DELIVERYPOINT (CONTACTPERSON_ID)
  TABLESPACE INDX;


ALTER TABLE DELIVERYPOINT
ADD CONSTRAINT DELIVERYPOINT_CONTACTPERSON FOREIGN KEY (CONTACTPERSON_ID) REFERENCES CONTACTPERSON (CONTACTPERSON_ID);

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

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';

----insert into process-----

insert into process
  (process_id, description, active)
values
  ('100', 'Send email with PDF delivery notes in attachment', 'Y');
  
insert into process
  (process_id, description, active)
values
  ('101', 'Send email with PDF consignment notes in attachment', 'Y');


----update packing note table----

update packingnote pn
   set pn.emailmaybesent = 'N'
 where pn.origin in (6, 7)
   and pn.emailmaybesent = 'Y';

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE ENABLE;   
