-------20013295.150 Delivery calculation (counted by product, delivered by size)

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

ALTER TABLE PRODUCT
	ADD (SIZEBREAKDOWN VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT SIZEBREAKDOWN_valid CHECK (SIZEBREAKDOWN IN ('Y','N')));

COMMENT ON COLUMN PRODUCT.SIZEBREAKDOWN IS 'This attribute is used to specify whether size breakdown must be done. Only used for module SOILCOUNTSIZESPLIT.';


--------------------------------TABLE SIZERATIO----------------

CREATE SEQUENCE SEQ_SIZERATIO
START WITH 1
INCREMENT BY 1;

CREATE TABLE SIZERATIO
(
	SIZERATIO_ID         NUMBER NOT NULL ,
	CUSTOMER_ID          NUMBER NOT NULL ,
	PRODUCT_ID           NUMBER NOT NULL ,
	SIZEDEFINITION_ID    NUMBER NOT NULL ,
	SIZERATIO            NUMBER(4,3) DEFAULT  0 NOT NULL  CONSTRAINT  SIZERATIO_valid CHECK (SIZERATIO BETWEEN 0.000 AND 1.000),
	QUANTITY             NUMBER DEFAULT  0 NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);


ALTER TABLE SIZERATIO
ADD CONSTRAINT  XPKSIZERATIO PRIMARY KEY (SIZERATIO_ID);

ALTER TABLE SIZERATIO
ADD CONSTRAINT SIZERATIO_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

CREATE  INDEX XIF1SIZERATIO ON SIZERATIO
(CUSTOMER_ID   ASC);

ALTER TABLE SIZERATIO
ADD CONSTRAINT SIZERATIO_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID);

CREATE  INDEX XIF2SIZERATIO ON SIZERATIO
(PRODUCT_ID   ASC);

ALTER TABLE SIZERATIO
ADD CONSTRAINT SIZERATIO_SIZEDEFINITION FOREIGN KEY (SIZEDEFINITION_ID) REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);

CREATE  INDEX XIF3SIZERATIO ON SIZERATIO
(SIZEDEFINITION_ID   ASC);


