-- ABS-97243 / ABS-97607
-------------- FLEXIBLESTORAGELOCATION--------------

CREATE SEQUENCE SEQ_FLEXIBLESTORAGELOCATION
START WITH 1
INCREMENT BY 1;


CREATE TABLE FLEXIBLESTORAGELOCATION
( FLEXIBLESTORAGELOCATION_ID           NUMBER NOT NULL,
  PLANTDEPARTMENT_ID                   NUMBER NOT NULL,
  SECTION                              VARCHAR2(12) NOT NULL,
  LOCATION                             VARCHAR2(12) NOT NULL,
  ACTIVE                               VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_FLEXSTORLOC_ACTIVE CHECK (ACTIVE IN ('Y','N')),
  UNIQUEID                             VARCHAR2(24) NULL,
  SYSTEMUSER_ID                        NUMBER NOT NULL,
  TIMESTAMP                            DATE NOT NULL);

ALTER TABLE FLEXIBLESTORAGELOCATION
  ADD CONSTRAINT XPKFLEXIBLESTORAGELOCATION PRIMARY KEY (FLEXIBLESTORAGELOCATION_ID);

CREATE UNIQUE INDEX XAK1FLEXIBLESTORAGELOCATION ON FLEXIBLESTORAGELOCATION (PLANTDEPARTMENT_ID ASC, SECTION ASC, LOCATION ASC);

CREATE INDEX XIE1FLEXIBLESTORAGELOCATION ON FLEXIBLESTORAGELOCATION (PLANTDEPARTMENT_ID ASC, UNIQUEID ASC);

CREATE INDEX XIF1FLEXIBLESTORAGELOCATION ON FLEXIBLESTORAGELOCATION (PLANTDEPARTMENT_ID ASC);

ALTER TABLE FLEXIBLESTORAGELOCATION
  ADD CONSTRAINT FK_FLEXSTORLOC_PLANTDEP FOREIGN KEY (PLANTDEPARTMENT_ID)
  REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID);

-------------- FLEXIBLESTORAGE--------------
CREATE TABLE FLEXIBLESTORAGE
( FLEXIBLESTORAGELOCATION_ID           NUMBER NOT NULL,
  DISTRIBUTIONPOINT_ID                 NUMBER NOT NULL,
  ITEMDESCRIPTION                      VARCHAR2(80) NOT NULL,
  STORAGETIMESTAMP                     DATE NOT NULL,
  SYSTEMUSER_ID                        NUMBER NOT NULL,
  TIMESTAMP                            DATE NOT NULL);

ALTER TABLE FLEXIBLESTORAGE
  ADD CONSTRAINT XPKFLEXIBLESTORAGE PRIMARY KEY (FLEXIBLESTORAGELOCATION_ID);

CREATE INDEX XIF1FLEXIBLESTORAGE ON FLEXIBLESTORAGE (DISTRIBUTIONPOINT_ID ASC);

ALTER TABLE FLEXIBLESTORAGE
  ADD CONSTRAINT FK_FLEXSTOR_FLEXSTORLOC FOREIGN KEY (FLEXIBLESTORAGELOCATION_ID)
  REFERENCES FLEXIBLESTORAGELOCATION (FLEXIBLESTORAGELOCATION_ID) ON DELETE CASCADE;

ALTER TABLE FLEXIBLESTORAGE 
  ADD CONSTRAINT FK_FLEXSTOR_DISTRIBP FOREIGN KEY (DISTRIBUTIONPOINT_ID)
  REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID) ON DELETE CASCADE;











