-- ABS-43952
----------   DELIVERYAVERAGE   -----------
CREATE SEQUENCE SEQ_DELIVERYAVERAGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE DELIVERYAVERAGE
(
DELIVERYAVERAGE_ID 		NUMBER NOT NULL,
PRODUCTLISTPERDISTRIBPOINT_ID 	NUMBER NOT NULL,
DAY_ID 				NUMBER NOT NULL,
PLANT_ID 			NUMBER NOT NULL,
TOTALDELIVERED			NUMBER NOT NULL,
DELIVERIES			NUMBER NOT NULL,
AVERAGE				NUMBER NOT NULL,
SYSTEMUSER_ID 			NUMBER NOT NULL,
TIMESTAMP			DATE NOT NULL
);

ALTER TABLE DELIVERYAVERAGE ADD CONSTRAINT XPKDELIVERYAVERAGE PRIMARY KEY (DELIVERYAVERAGE_ID);

CREATE UNIQUE INDEX XAK1DELIVERYAVERAGE ON DELIVERYAVERAGE 
(PRODUCTLISTPERDISTRIBPOINT_ID ASC, DAY_ID ASC, PLANT_ID ASC);

CREATE INDEX XIE1DELIVERYAVERAGE ON DELIVERYAVERAGE (PLANT_ID ASC, DAY_ID ASC);

CREATE INDEX XIF1DELIVERYAVERAGE ON DELIVERYAVERAGE (PRODUCTLISTPERDISTRIBPOINT_ID ASC);
CREATE INDEX XIF2DELIVERYAVERAGE ON DELIVERYAVERAGE (DAY_ID ASC);
CREATE INDEX XIF3DELIVERYAVERAGE ON DELIVERYAVERAGE (PLANT_ID ASC);

ALTER TABLE DELIVERYAVERAGE ADD CONSTRAINT DELIVERYAVERAGE_PLPD FOREIGN KEY (PRODUCTLISTPERDISTRIBPOINT_ID)
REFERENCES PRODUCTLISTPERDISTRIBPOINT (PRODUCTLISTPERDISTRIBPOINT_ID) ON DELETE CASCADE;

ALTER TABLE DELIVERYAVERAGE ADD CONSTRAINT DELIVERYAVERAGE_DAY FOREIGN KEY (DAY_ID)
REFERENCES DAY (DAY_ID);

ALTER TABLE DELIVERYAVERAGE ADD CONSTRAINT DELIVERYAVERAGE_PLANT FOREIGN KEY (PLANT_ID)
REFERENCES PLANT (PLANT_ID);

----------   PROCESS   -----------
INSERT INTO PROCESS (PROCESS_ID, DESCRIPTION, ACTIVE)
VALUES (139, 'Fill Delivery Average', 'Y');

COMMIT;
