--ABS-142711

ALTER TABLE ROUTECONTROLBULKPACKING 
ADD (PLANTDEPARTMENT_ID NUMBER NULL) ;

ALTER TABLE ROUTECONTROLBULKPACKING 
  ADD CONSTRAINT FK_ROUTECONTROLBULKPACKING_PLANTDEPARTMENT_ID FOREIGN KEY (PLANTDEPARTMENT_ID) REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID);
  
CREATE INDEX XIF6ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (PLANTDEPARTMENT_ID);

--ROUTECONTROLBULKPACKSTOP TABLE 

ALTER TABLE ROUTECONTROLBULKPACKSTOP
ADD (PLANTDEPARTMENT_ID NUMBER NULL);

ALTER TABLE ROUTECONTROLBULKPACKSTOP 
  ADD CONSTRAINT FK_ROUTECONTROLBULKPACKSTOP_PLANTDEPARTMENT_ID FOREIGN KEY (PLANTDEPARTMENT_ID) REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID);
  
CREATE INDEX XIF8ROUTECONTROLBULKPACKSTOP ON ROUTECONTROLBULKPACKSTOP (PLANTDEPARTMENT_ID);

--ROUTECONTROLBPPACKDEPT TABLE

CREATE TABLE ROUTECONTROLBPPACKDEPT(
ROUTECONTROL_ID NUMBER NOT NULL,
PLANTDEPARTMENT_ID NUMBER NOT NULL,
BULKPACKINGSTART DATE NULL,
BULKPACKINGEND DATE NULL,
BULKPACKEDSYSTEMUSER_ID NUMBER NULL,
BULKPACKINGSTATUS NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_ROUTECONTROLBPPACKDEPT_BULKPACKINGSTATUS CHECK (BULKPACKINGSTATUS IN (1, 2, 3, 4)),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN ROUTECONTROLBPPACKDEPT.BULKPACKINGSTART IS 'Timestamp bulk packing was started for this packing department not converted into server timezone';

COMMENT ON COLUMN ROUTECONTROLBPPACKDEPT.BULKPACKINGEND IS 'Timestamp bulk packing was completed for this packing department not converted into server timezone';

COMMENT ON COLUMN ROUTECONTROLBPPACKDEPT.BULKPACKINGSTATUS IS '1 = Not started (default)
                                                               2 = In progress
                                                               3 = Completed
                                                               4 = Complete but Short';

ALTER TABLE ROUTECONTROLBPPACKDEPT
  ADD CONSTRAINT XPKROUTECONTROLBPPACKDEPT PRIMARY KEY (ROUTECONTROL_ID, PLANTDEPARTMENT_ID);
  
ALTER TABLE ROUTECONTROLBPPACKDEPT 
  ADD CONSTRAINT FK_ROUTECONTROLBPPACKDEPT_ROUTECONTROL_ID FOREIGN KEY (ROUTECONTROL_ID) REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE CASCADE;                                                               

ALTER TABLE ROUTECONTROLBPPACKDEPT 
  ADD CONSTRAINT FK_ROUTECONTROLBPPACKDEPT_PLANTDEPARTMENT_ID FOREIGN KEY (PLANTDEPARTMENT_ID) REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID);                                                               

CREATE INDEX XIF1ROUTECONTROLBPPACKDEPT ON ROUTECONTROLBPPACKDEPT (ROUTECONTROL_ID);

CREATE INDEX XIF2ROUTECONTROLBPPACKDEPT ON ROUTECONTROLBPPACKDEPT (PLANTDEPARTMENT_ID);
