--Order 20011683.100 (New Module LOADOPTIMIZEREXTENDED + Changes described in SR 20011923)

--------------------------------CUSTOMER TABLE---------------------------------------

ALTER TABLE CUSTOMER
	ADD (PRINTLOADOPTIMIZERDOCUMENT NUMBER  DEFAULT 0 NOT NULL CONSTRAINT PRINTLOADOP_valid CHECK (PRINTLOADOPTIMIZERDOCUMENT IN (0, 1)));

COMMENT ON COLUMN CUSTOMER.PRINTLOADOPTIMIZERDOCUMENT IS '0 = Not applicable / print packing note
1 = Print dedicated optimized load packing document
';


--------------------------------TRANSPORTARTICLE TABLE---------------------------------------
ALTER TABLE TRANSPORTARTICLE
	ADD (VOLUMEFACTOR NUMBER(4,2)  DEFAULT 1 NOT NULL CONSTRAINT VOLUMEFACTOR_valid CHECK (VOLUMEFACTOR BETWEEN 0.01 AND 10.00),
	     MAXFILLPERCENTAGE NUMBER  DEFAULT 100 NOT NULL CONSTRAINT MAXFILLPERC_valid CHECK (MAXFILLPERCENTAGE BETWEEN 1 AND 100));

COMMENT ON COLUMN TRANSPORTARTICLE.VOLUMEFACTOR IS 'The volume of a transport article when compared to the volume of the standard transport article.';

COMMENT ON COLUMN TRANSPORTARTICLE.MAXFILLPERCENTAGE IS 'The maximum of the total volume space that may be filled for base products';



--------------------------------CUSTOMERTRANSPORTARTICLE TABLE---------------------------------------

CREATE SEQUENCE SEQ_CUSTOMERTRANSPORTARTICLE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERTRANSPORTARTICLE
(
	CUSTOMERTRANSPORTARTICLE_ID NUMBER NOT NULL ,
	CUSTOMER_ID          NUMBER NOT NULL ,
	TRANSPORTARTICLE_ID  NUMBER NOT NULL ,
	DEFAULTTRANSPORTARTICLE VARCHAR2(1) DEFAULT  'N'  NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN CUSTOMERTRANSPORTARTICLE.DEFAULTTRANSPORTARTICLE IS 'Y/N field to indicate whether a certain transport article is the default, to be used when defining a Standard Delivery for a consumption point of a customer. Only one transport article can be the default for a customer.';

ALTER TABLE CUSTOMERTRANSPORTARTICLE
ADD CONSTRAINT  XPKCUSTOMERTRANSPORTARTICLE PRIMARY KEY (CUSTOMERTRANSPORTARTICLE_ID);



CREATE  INDEX XIF1CUSTOMERTRANSPORTARTICLE ON CUSTOMERTRANSPORTARTICLE
(CUSTOMER_ID   ASC);

ALTER TABLE CUSTOMERTRANSPORTARTICLE
ADD CONSTRAINT CUSTOMERTRANSARTICLE_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;



CREATE  INDEX XIF2CUSTOMERTRANSPORTARTICLE ON CUSTOMERTRANSPORTARTICLE
(TRANSPORTARTICLE_ID   ASC);

ALTER TABLE CUSTOMERTRANSPORTARTICLE
ADD CONSTRAINT CUSTOMERTRANSARTICLE_TRANSART FOREIGN KEY (TRANSPORTARTICLE_ID) REFERENCES TRANSPORTARTICLE (TRANSPORTARTICLE_ID);



CREATE UNIQUE INDEX XAK1CUSTOMERTRANSPORTARTICLE ON CUSTOMERTRANSPORTARTICLE
(CUSTOMER_ID   ASC,TRANSPORTARTICLE_ID   ASC);

ALTER TABLE CUSTOMERTRANSPORTARTICLE
ADD CONSTRAINT  XAK1CUSTOMERTRANSPORTARTICLE UNIQUE (CUSTOMER_ID,TRANSPORTARTICLE_ID);






--------------------------------PRODUCT TABLE---------------------------------------

ALTER TABLE PRODUCT
	ADD (PACKINGGROUP NUMBER NULL,
	     PACKINGPRODUCTTYPE NUMBER(1)  DEFAULT 1 NOT NULL CONSTRAINT PACKINGPRODUCTTYPE_ CHECK (PACKINGPRODUCTTYPE IN (1, 2)));

COMMENT ON COLUMN PRODUCT.PACKINGPRODUCTTYPE IS '1 = Base product 
2 = Supplementary product
';

UPDATE PRODUCT P
SET P.PACKINGGROUP=(SELECT PR.PACKINGSEQUENCE FROM PRODUCT PR
WHERE PR.PRODUCT_ID=P.PRODUCT_ID)
WHERE P.PACKINGGROUP IS NULL;

COMMIT;

ALTER TABLE PRODUCT
	MODIFY (PACKINGGROUP NUMBER  DEFAULT 0 NOT NULL);









