--Order 20015250
-----------------------CLIENTDELIVERYNOTE--------------------------

ALTER TABLE CLIENTDELIVERYNOTE
	ADD (PRESORTNUMBER NUMBER NULL,
	          INTERNALPROCESSINGBU_ID NUMBER NULL,
	          PACKINGNOTE_ID NUMBER NULL);

ALTER TABLE CLIENTDELIVERYNOTE
ADD CONSTRAINT CLIENTDELNOTE_BU_ID FOREIGN KEY (INTERNALPROCESSINGBU_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;

CREATE INDEX XIF10CLIENTDELIVERYNOTE ON CLIENTDELIVERYNOTE
(INTERNALPROCESSINGBU_ID   ASC);

ALTER TABLE CLIENTDELIVERYNOTE
ADD CONSTRAINT CLIENTDELNOTE_PACKINGNOTE_ID FOREIGN KEY (PACKINGNOTE_ID) REFERENCES PACKINGNOTE (PACKINGNOTE_ID) ON DELETE SET NULL;

CREATE INDEX XIF11CLIENTDELIVERYNOTE ON CLIENTDELIVERYNOTE
(PACKINGNOTE_ID   ASC);

---------------------------CLIENTDELIVERYNOTELINE--------------------------

ALTER TABLE CLIENTDELIVERYNOTELINE
	ADD (SELFPAYMENT NUMBER  DEFAULT 0 NOT NULL CONSTRAINT SELFPAYMENT_valid CHECK (SELFPAYMENT IN (0, 1, 2)));

COMMENT ON COLUMN CLIENTDELIVERYNOTELINE.SELFPAYMENT IS '0 = Not applicable (default value)
1 = Customer Pays,
2 = Client pays
';

---------------------------PACKINGNOTE--------------------------
BEGIN
EXECUTE IMMEDIATE ('ALTER TABLE PACKINGNOTE DROP CONSTRAINT ORIGIN_valid');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

ALTER TABLE PACKINGNOTE
	ADD  CONSTRAINT PNORIGIN_valid  CHECK (ORIGIN IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13));

---------------------------CUSTOMERRESIDENTIALSETTINGS--------------------------
ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
	ADD (DELIVERYNOTELAYOUT_ID NUMBER);

COMMENT ON COLUMN CUSTOMERRESIDENTIALSETTINGS.DELIVERYNOTELAYOUT_ID IS 'Foreign key to FormLayout table. Must be FormType = ‘DN’
Note: can be Null if plant does not have license RESIDENTDELNOTE.
';

CREATE INDEX XIF10CUSTOMERRESIDENTIALSETTIN ON CUSTOMERRESIDENTIALSETTINGS
(DELIVERYNOTELAYOUT_ID   ASC);

ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
ADD CONSTRAINT CUSTOMERRESIDSETTINGS_FORMLAY_ FOREIGN KEY (DELIVERYNOTELAYOUT_ID) REFERENCES FORMLAYOUT (FORMLAYOUT_ID);

---------------------------PRODUCTLISTPERCUSTOMER--------------------------
ALTER TABLE PRODUCTLISTPERCUSTOMER
	ADD (SELFPAYMENT NUMBER  DEFAULT 1 NOT NULL CONSTRAINT SELFPAYMENT_valid1 CHECK (SELFPAYMENT IN (1, 2)));

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.SELFPAYMENT IS '1 = Customer Pays(default value)
2 = Client pays
';

---------------------------CLIENTBILLINGINFO--------------------------
ALTER TABLE CLIENTBILLINGINFO
	ADD (SELFPAYMENT NUMBER  DEFAULT 0 NOT NULL CONSTRAINT SELFPAYMENT_valid2 CHECK (SELFPAYMENT IN (0, 1, 2)));

COMMENT ON COLUMN CLIENTBILLINGINFO.SELFPAYMENT IS '0 = Inherit from PLPC (default value)
1 = Customer Pays,
2 = Client pays
';

--------------------------PRESORTBATCH----------------------------------

CREATE SEQUENCE SEQ_PRESORTBATCH
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRESORTBATCH
(	PRESORTBATCH_ID      NUMBER NOT NULL,
	BATCH_ID             	         NUMBER NOT NULL ,
	CLOSED                          VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  CLOSED_valid CHECK (CLOSED IN ('Y', 'N')),
	DELIVERYDATE             DATE DEFAULT  to_date('31/12/2099','dd/mm/yyyy')  NOT NULL ,
	RDNCREATED                NUMBER DEFAULT  0  NOT NULL  CONSTRAINT  RDNCREATED_valid CHECK (RDNCREATED IN (0, 1, 2)),
	PRESORTNUMBER        NUMBER NOT NULL 
);

COMMENT ON COLUMN PRESORTBATCH.RDNCREATED IS '0: no RDN created, 1=RDN in progress, 2:RDN finalized';

ALTER TABLE PRESORTBATCH
ADD CONSTRAINT  XPKPRESORTBATCH PRIMARY KEY (PRESORTBATCH_ID);

ALTER TABLE PRESORTBATCH
ADD CONSTRAINT PRESORTBATCH_BATCH_ID FOREIGN KEY (BATCH_ID) REFERENCES BATCH (BATCH_ID);

CREATE INDEX XIF1PRESORTBATCH ON PRESORTBATCH
(BATCH_ID   ASC);

CREATE UNIQUE INDEX XAK1PRESORTBATCH ON PRESORTBATCH
(BATCH_ID   ASC,PRESORTNUMBER   ASC);

