-- ABS-93571
-------------- ROUTECONTROLINTERNALBU --------------
CREATE TABLE ROUTECONTROLINTERNALBU
( ROUTECONTROL_ID           NUMBER NOT NULL,
  INTERNALPROCESSINGBU_ID   NUMBER NOT NULL,
  PACKINGSTATUS             NUMBER NOT NULL CONSTRAINT CHK_RCINTERNALBU_PACKSTATUS CHECK (PACKINGSTATUS IN (1,2,3,4)),
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL);

COMMENT ON COLUMN ROUTECONTROLINTERNALBU.PACKINGSTATUS
  IS '1 = not put up printed
2 = put up printed (packing notes printed)
3 = in progress (started on first delivery note)
4 = completed (all delivery notes made)';

ALTER TABLE ROUTECONTROLINTERNALBU
  ADD CONSTRAINT XPKROUTECONTROLINTERNALBU PRIMARY KEY (ROUTECONTROL_ID, INTERNALPROCESSINGBU_ID);

CREATE INDEX XIF1ROUTECONTROLINTERNALBU ON ROUTECONTROLINTERNALBU (ROUTECONTROL_ID ASC);
CREATE INDEX XIF2ROUTECONTROLINTERNALBU ON ROUTECONTROLINTERNALBU (INTERNALPROCESSINGBU_ID ASC);

ALTER TABLE ROUTECONTROLINTERNALBU
  ADD CONSTRAINT FK_RCINTERNALBU_ROUTECONTROL FOREIGN KEY (ROUTECONTROL_ID)
  REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE CASCADE;
ALTER TABLE ROUTECONTROLINTERNALBU
  ADD CONSTRAINT FK_RCINTERNALBU_INTERNALBU FOREIGN KEY (INTERNALPROCESSINGBU_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;
