-- ABS-109578 / ABS-110267

------------ ROUTECONTROLBULKPACKSTOP ------------
CREATE SEQUENCE SEQ_ROUTECONTROLBULKPACKSTOP
START WITH 1
INCREMENT BY 1;

CREATE TABLE ROUTECONTROLBULKPACKSTOP
( ROUTECONTROLBULKPACKSTOP_ID NUMBER NOT NULL,
  ROUTECONTROL_ID             NUMBER NOT NULL,
  STOPNUMBER                  NUMBER NOT NULL,
  STOP_ID                     NUMBER NOT NULL,
  PRODUCT_LINK_ID             NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID      NUMBER NOT NULL,
  FINISHINGMETHOD_ID          NUMBER NOT NULL,
  CUSTOMPRODUCTCODE           VARCHAR2(16),
  CUSTOMPRODUCTDESCRIPTION    VARCHAR2(30),
  BUNDLESIZE                  NUMBER DEFAULT 1 NOT NULL,
  TYPE                        NUMBER NOT NULL CONSTRAINT CHK_RCBULKPACKSTOP_TYPE
                                     CHECK (TYPE IN (1,2,3,4)),
  QUANTITYTOBEPACKED          NUMBER NOT NULL,
  QUANTITYPACKED              NUMBER,
  ACTIVESTOCKLOCATION_ID      NUMBER,
  PASSIVESTOCKLOCATION_ID     NUMBER,
  ACTIVE                      VARCHAR2(1) NOT NULL CONSTRAINT CHK_RCBULKPACKSTOP_ACTIVE
                                          CHECK (ACTIVE IN ('Y','N')),
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL);

COMMENT ON COLUMN ROUTECONTROLBULKPACKSTOP.TYPE
  IS '1 = Customer Logo Mat
2 = Bulk Logo Mat
3 = Pool
4 = One Way ';

ALTER TABLE ROUTECONTROLBULKPACKSTOP
  ADD CONSTRAINT XPKROUTECONTROLBULKPACKINGSTOP PRIMARY KEY (ROUTECONTROLBULKPACKSTOP_ID);

CREATE UNIQUE INDEX XAK1ROUTECONTROLBULKPACKSTOP ON ROUTECONTROLBULKPACKSTOP (ROUTECONTROL_ID ASC, STOPNUMBER ASC, STOP_ID ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF1ROUTECONTROLBULKPACKSTOP ON ROUTECONTROLBULKPACKSTOP (ROUTECONTROL_ID ASC);
CREATE INDEX XIF2ROUTECONTROLBULKPACKSTOP ON ROUTECONTROLBULKPACKSTOP (STOP_ID ASC);
CREATE INDEX XIF3ROUTECONTROLBULKPACKSTOP ON ROUTECONTROLBULKPACKSTOP (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF4ROUTECONTROLBULKPACKSTOP ON ROUTECONTROLBULKPACKSTOP (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF5ROUTECONTROLBULKPACKSTOP ON ROUTECONTROLBULKPACKSTOP (FINISHINGMETHOD_ID ASC);

ALTER TABLE ROUTECONTROLBULKPACKSTOP
  ADD CONSTRAINT FK_RCBULKPACKSTOP_ROUTECONTROL FOREIGN KEY (ROUTECONTROL_ID)
  REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE CASCADE;
ALTER TABLE ROUTECONTROLBULKPACKSTOP
  ADD CONSTRAINT FK_RCBULKPACKSTOP_STOP FOREIGN KEY (STOP_ID)
  REFERENCES STOP (STOP_ID) ON DELETE CASCADE;
ALTER TABLE ROUTECONTROLBULKPACKSTOP
  ADD CONSTRAINT FK_RCBULKPACKSTOP_PRODUCTLINK FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
ALTER TABLE ROUTECONTROLBULKPACKSTOP
  ADD CONSTRAINT FK_RCBULKPACKSTOP_SIZEDEFLINK FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
ALTER TABLE ROUTECONTROLBULKPACKSTOP
  ADD CONSTRAINT FK_RCBULKPACKSTOP_FINISHMETHOD FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
