-- ABS-109578 / ABS-109580

----------------- ROUTECONTROLCONTAINER -----------------


CREATE TABLE ROUTECONTROLCONTAINER 
(
  ROUTECONTROL_ID NUMBER NOT NULL,
  CONTAINER_ID NUMBER NOT NULL,
  STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_RCCONTAINER_ST CHECK (STATUS  IN (0,1)),
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
);

COMMENT ON COLUMN ROUTECONTROLCONTAINER.STATUS 
  IS '0 = Packed
1 = In transport
';

ALTER TABLE ROUTECONTROLCONTAINER 
  ADD CONSTRAINT XPKROUTECONTROLCONTAINER  PRIMARY KEY (ROUTECONTROL_ID, CONTAINER_ID );
  
CREATE UNIQUE INDEX XAK1ROUTECONTROLCONTAINER ON ROUTECONTROLCONTAINER (CONTAINER_ID  ASC);

CREATE INDEX XIF1ROUTECONTROLCONTAINER ON ROUTECONTROLCONTAINER (ROUTECONTROL_ID ASC);

ALTER TABLE ROUTECONTROLCONTAINER 
  ADD CONSTRAINT FK_RCCONTAINER_RC FOREIGN KEY (ROUTECONTROL_ID )
  REFERENCES ROUTECONTROL(ROUTECONTROL_ID) ON DELETE CASCADE;

ALTER TABLE ROUTECONTROLCONTAINER 
  ADD CONSTRAINT FK_RCCONTAINER_MATCARRYU FOREIGN KEY (CONTAINER_ID)
  REFERENCES MATCARRYUNIT(CONTAINER_ID) ON DELETE CASCADE;


----------------- ROUTECONTROLBULKPACKING TABLE ---------------- 
ALTER TABLE ROUTECONTROLBULKPACKING ADD (QUANTITYPREPACKED NUMBER NULL);

COMMENT ON COLUMN ROUTECONTROLBULKPACKING.QUANTITYPREPACKED
  IS 'Used only for 2 step packing, to store the quantity processed in the first step';
