-- ABS-67076/ ABS-81088
DROP TABLE ACTIVESTOCKLOCATIONCONTENT;

CREATE SEQUENCE SEQ_ACTIVESTOCKLOCATIONCONTENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE ACTIVESTOCKLOCATIONCONTENT
(
  ACTIVESTOCKLOCATIONCONTENT_ID NUMBER NOT NULL,
  ACTIVESTOCKLOCATION_ID   NUMBER NOT NULL,
  CUSTOMER_ID              NUMBER NULL,
  PRODUCT_ID               NUMBER NOT NULL,
  SIZEDEFINITION_ID        NUMBER NOT NULL,
  FINISHINGMETHOD_ID       NUMBER NULL,
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
)
;

ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT XPKACTIVESTOCKLOCATIONCONTENT PRIMARY KEY (ACTIVESTOCKLOCATIONCONTENT_ID);

CREATE UNIQUE INDEX XAK1ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (ACTIVESTOCKLOCATION_ID ASC, NVL(CUSTOMER_ID,-1) ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, NVL(FINISHINGMETHOD_ID,-1) ASC);

CREATE INDEX XIF1ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (ACTIVESTOCKLOCATION_ID ASC);
CREATE INDEX XIF2ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (CUSTOMER_ID ASC);
CREATE INDEX XIF3ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (PRODUCT_ID ASC);
CREATE INDEX XIF4ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (SIZEDEFINITION_ID ASC);
CREATE INDEX XIF5ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (FINISHINGMETHOD_ID ASC);
CREATE INDEX XIE1ACTIVESTOCKLOCATIONCONTENT ON ACTIVESTOCKLOCATIONCONTENT (CUSTOMER_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, FINISHINGMETHOD_ID ASC);

ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONCONTENT_AS FOREIGN KEY (ACTIVESTOCKLOCATION_ID)
  REFERENCES ACTIVESTOCKLOCATION (ACTIVESTOCKLOCATION_ID) ON DELETE CASCADE;
ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONCONTENT_C FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONCONTENT_P FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID);
ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONCONTENT_S FOREIGN KEY (SIZEDEFINITION_ID)
  REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);
ALTER TABLE ACTIVESTOCKLOCATIONCONTENT
  ADD CONSTRAINT ACTIVESTOCKLOCATIONCONTENT_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
