-- ABS-6675
--- PRODUCTTRANSPORTARTICLE table ---
CREATE SEQUENCE SEQ_PRODUCTTRANSPORTARTICLE
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRODUCTTRANSPORTARTICLE
(
  PRODUCTTRANSPORTARTICLE_ID NUMBER NOT NULL,
  PRODUCT_ID                 NUMBER NOT NULL,
  TRANSPORTARTICLE_ID        NUMBER NOT NULL,
  MAXCARTQTY                 NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
)
;

COMMENT ON COLUMN PRODUCTTRANSPORTARTICLE.MAXCARTQTY
  IS 'This attributes contains the number of items of the current product that can be stored in the current transport article';

ALTER TABLE PRODUCTTRANSPORTARTICLE
  ADD CONSTRAINT XPKPRODUCTTRANSPORTARTICLE PRIMARY KEY (PRODUCTTRANSPORTARTICLE_ID);
  
CREATE UNIQUE INDEX XAK1PRODUCTTRANSPORTARTICLE ON PRODUCTTRANSPORTARTICLE (PRODUCT_ID ASC, TRANSPORTARTICLE_ID ASC);

CREATE INDEX XIF1PRODUCTTRANSPORTARTICLE ON PRODUCTTRANSPORTARTICLE (PRODUCT_ID ASC);

CREATE INDEX XIF2PRODUCTTRANSPORTARTICLE ON PRODUCTTRANSPORTARTICLE (TRANSPORTARTICLE_ID ASC);

ALTER TABLE PRODUCTTRANSPORTARTICLE
  ADD CONSTRAINT PTA_PRODUCT FOREIGN KEY (PRODUCT_ID)
      REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;
      
ALTER TABLE PRODUCTTRANSPORTARTICLE
  ADD CONSTRAINT PTA_TRANSPORTARTICLE FOREIGN KEY (TRANSPORTARTICLE_ID)
      REFERENCES TRANSPORTARTICLE (TRANSPORTARTICLE_ID) ON DELETE CASCADE;
  
--- PRODUCTSIZETRANSPORTARTICLE table ---
CREATE SEQUENCE SEQ_PRODUCTSIZETRANSPARTICLE
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRODUCTSIZETRANSPARTICLE
(
  PRODUCTSIZETRANSPORTARTICLE_ID NUMBER NOT NULL,
  PRODUCT_ID                 NUMBER NOT NULL,
  SIZEDEFINITION_ID          NUMBER NOT NULL,
  TRANSPORTARTICLE_ID        NUMBER NOT NULL,
  MAXCARTQTY                 NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
);

COMMENT ON COLUMN PRODUCTSIZETRANSPARTICLE.MAXCARTQTY
  IS 'This attributes contains the number of items of the current product that can be stored in the current transport article.';

ALTER TABLE PRODUCTSIZETRANSPARTICLE
  ADD CONSTRAINT XPKPRODUCTSIZETRANSPARTICLE PRIMARY KEY (PRODUCTSIZETRANSPORTARTICLE_ID);
  
CREATE UNIQUE INDEX XAK1PRODUCTSIZETRANSPARTICLE ON PRODUCTSIZETRANSPARTICLE (PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, TRANSPORTARTICLE_ID ASC);

CREATE INDEX XIF1PRODUCTSIZETRANSPARTICLE ON PRODUCTSIZETRANSPARTICLE (PRODUCT_ID ASC);

CREATE INDEX XIF2PRODUCTSIZETRANSPARTICLE ON PRODUCTSIZETRANSPARTICLE (SIZEDEFINITION_ID ASC);

CREATE INDEX XIF3PRODUCTSIZETRANSPARTICLE ON PRODUCTSIZETRANSPARTICLE (TRANSPORTARTICLE_ID ASC);

ALTER TABLE PRODUCTSIZETRANSPARTICLE
  ADD CONSTRAINT PSTA_PRODUCT FOREIGN KEY (PRODUCT_ID)
      REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;
  
ALTER TABLE PRODUCTSIZETRANSPARTICLE
  ADD CONSTRAINT PSTA_SIZEDEFINITION FOREIGN KEY (SIZEDEFINITION_ID)
      REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID) ON DELETE CASCADE;
  
ALTER TABLE PRODUCTSIZETRANSPARTICLE
  ADD CONSTRAINT PSTA_TRANSPORTARTICLE FOREIGN KEY (TRANSPORTARTICLE_ID)
      REFERENCES TRANSPORTARTICLE (TRANSPORTARTICLE_ID);
      
--- PLANTPRODUCTTRANSPORTARTICLE table ---
CREATE TABLE PLANTPRODUCTTRANSPORTARTICLE
(
  PLANT_ID            NUMBER NOT NULL,
  PRODUCT_ID          NUMBER NOT NULL,
  TRANSPORTARTICLE_ID NUMBER NOT NULL,
  MAXCARTQTY          NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
);

COMMENT ON COLUMN PLANTPRODUCTTRANSPORTARTICLE.MAXCARTQTY
  IS 'This attributes contains the number of items of the current product that can be stored in the current transport article.';
 
ALTER TABLE PLANTPRODUCTTRANSPORTARTICLE
  ADD CONSTRAINT XPKPLANTPRODUCTTRANSPARTICLE PRIMARY KEY (PLANT_ID, PRODUCT_ID, TRANSPORTARTICLE_ID);

CREATE INDEX XIF1PLANTPRODUCTTRANSPARTICLE ON PLANTPRODUCTTRANSPORTARTICLE (PLANT_ID ASC);

CREATE INDEX XIF2PLANTPRODUCTTRANSPARTICLE ON PLANTPRODUCTTRANSPORTARTICLE (PRODUCT_ID ASC);

CREATE INDEX XIF3PLANTPRODUCTTRANSPARTICLE ON PLANTPRODUCTTRANSPORTARTICLE (TRANSPORTARTICLE_ID ASC);

ALTER TABLE PLANTPRODUCTTRANSPORTARTICLE
  ADD CONSTRAINT PPTA_PLANT FOREIGN KEY (PLANT_ID)
      REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;
  
ALTER TABLE PLANTPRODUCTTRANSPORTARTICLE
  ADD CONSTRAINT PPTA_PRODUCT FOREIGN KEY (PRODUCT_ID)
      REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;
  
ALTER TABLE PLANTPRODUCTTRANSPORTARTICLE
  ADD CONSTRAINT PPTA_TRANSPORTARTICLE FOREIGN KEY (TRANSPORTARTICLE_ID)
      REFERENCES TRANSPORTARTICLE (TRANSPORTARTICLE_ID) ON DELETE CASCADE;
      
--- PLANTPRODUCTSIZETRANSPARTICLE table ---
CREATE TABLE PLANTPRODUCTSIZETRANSPARTICLE
(
  PLANT_ID            NUMBER NOT NULL,
  PRODUCT_ID          NUMBER NOT NULL,
  SIZEDEFINITION_ID   NUMBER NOT NULL,
  TRANSPORTARTICLE_ID NUMBER NOT NULL,
  MAXCARTQTY          NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
);

COMMENT ON COLUMN PLANTPRODUCTSIZETRANSPARTICLE.MAXCARTQTY
  IS 'This attributes contains the number of items of the current product that can be stored in the current transport article.';

ALTER TABLE PLANTPRODUCTSIZETRANSPARTICLE
  ADD CONSTRAINT XPKPLANTPRODUCTSIZETRANSPART PRIMARY KEY (PLANT_ID, PRODUCT_ID, SIZEDEFINITION_ID, TRANSPORTARTICLE_ID);

CREATE INDEX XIF1PLANTPRODUCTSIZETRANSPART ON PLANTPRODUCTSIZETRANSPARTICLE (PLANT_ID ASC);

CREATE INDEX XIF2PLANTPRODUCTSIZETRANSPART ON PLANTPRODUCTSIZETRANSPARTICLE (PRODUCT_ID ASC);

CREATE INDEX XIF3PLANTPRODUCTSIZETRANSPART ON PLANTPRODUCTSIZETRANSPARTICLE (SIZEDEFINITION_ID ASC);

CREATE INDEX XIF4PLANTPRODUCTSIZETRANSPART ON PLANTPRODUCTSIZETRANSPARTICLE (TRANSPORTARTICLE_ID ASC);

ALTER TABLE PLANTPRODUCTSIZETRANSPARTICLE
  ADD CONSTRAINT PPSTA_PLANT FOREIGN KEY (PLANT_ID)
      REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;
      
ALTER TABLE PLANTPRODUCTSIZETRANSPARTICLE
  ADD CONSTRAINT PPSTA_PRODUCT FOREIGN KEY (PRODUCT_ID)
      REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;
      
ALTER TABLE PLANTPRODUCTSIZETRANSPARTICLE
  ADD CONSTRAINT PPSTA_SIZEDEFINITION FOREIGN KEY (SIZEDEFINITION_ID)
      REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID) ON DELETE CASCADE;
      
ALTER TABLE PLANTPRODUCTSIZETRANSPARTICLE
  ADD CONSTRAINT PPSTA_TRANSPORTARTICLE FOREIGN KEY (TRANSPORTARTICLE_ID)
      REFERENCES TRANSPORTARTICLE (TRANSPORTARTICLE_ID) ON DELETE CASCADE;
