--- ABS-118072 ---

CREATE SEQUENCE SEQ_ROUTEDOCUMENTGENERATIONQ
START WITH 1
INCREMENT BY 1;

CREATE TABLE ROUTEDOCUMENTGENERATIONQ
(ROUTEDOCUMENTGENERATIONQ_ID NUMBER NOT NULL,
 PLANT_ID NUMBER NOT NULL, 
 ROUTE_ID NUMBER NOT NULL, 
 ROUTEDATE DATE NOT NULL, 
 PLANNEDGENERATIONDATE DATE NOT NULL, 
 ROUTECONTROL_ID NUMBER NULL, 
 DOCUMENTTYPE NUMBER NOT NULL CONSTRAINT CHK_RDG_DOCUMENTTYPE CHECK(DOCUMENTTYPE IN (1,2)), 
 GENERATEDTIMESTAMP DATE NULL, 
 TIMESTAMP DATE NOT NULL, 
 SYSTEMUSER_ID NUMBER NOT NULL);

COMMENT ON COLUMN ROUTEDOCUMENTGENERATIONQ.DOCUMENTTYPE IS '1 = Put up docs 2 = Invoices';

ALTER TABLE ROUTEDOCUMENTGENERATIONQ 
ADD CONSTRAINT XPK_ROUTEDOCUMENTGENERATIONQ PRIMARY KEY(ROUTEDOCUMENTGENERATIONQ_ID);

CREATE UNIQUE INDEX XAK1ROUTEDOCUMENTGENERATIONQ ON ROUTEDOCUMENTGENERATIONQ (ROUTE_ID ASC, ROUTEDATE ASC, DOCUMENTTYPE ASC);
CREATE INDEX XFK1ROUTEDOCUMENTGENERATIONQ ON ROUTEDOCUMENTGENERATIONQ (PLANT_ID ASC);
CREATE INDEX XFK2ROUTEDOCUMENTGENERATIONQ ON ROUTEDOCUMENTGENERATIONQ (ROUTE_ID ASC);
CREATE INDEX XFK3ROUTEDOCUMENTGENERATIONQ ON ROUTEDOCUMENTGENERATIONQ (ROUTECONTROL_ID ASC);

ALTER TABLE ROUTEDOCUMENTGENERATIONQ
      ADD CONSTRAINT RDG_PLANT_ID FOREIGN KEY (PLANT_ID)
      REFERENCES PLANT(PLANT_ID) ON DELETE CASCADE;
      
ALTER TABLE ROUTEDOCUMENTGENERATIONQ
      ADD CONSTRAINT RDG_ROUTE_ID FOREIGN KEY (ROUTE_ID)
      REFERENCES ROUTE(ROUTE_ID) ON DELETE CASCADE;
      
ALTER TABLE ROUTEDOCUMENTGENERATIONQ
      ADD CONSTRAINT RDG_ROUTECONTROL_ID FOREIGN KEY (ROUTECONTROL_ID)
      REFERENCES ROUTECONTROL(ROUTECONTROL_ID) ON DELETE CASCADE;       
