--ABS-92314 / ABS-107396

----------- MATSORTPOSITION ---------------
CREATE SEQUENCE SEQ_MATSORTPOSITION
START WITH 1
INCREMENT BY 1;

CREATE TABLE MATSORTPOSITION
(
  MATSORTPOSITION_ID NUMBER NOT NULL,
  PLANTDEPARTMENT_ID NUMBER NOT NULL,
  CODE  VARCHAR2(6) NOT NULL,
  IDCODE VARCHAR2(24) NOT NULL,
  ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_MATSORTPOS_ACTIVE CHECK (ACTIVE IN ('Y', 'N')),
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;

COMMENT ON COLUMN MATSORTPOSITION.IDCODE
  IS 'Barcode that can be scanned to identify the position';

ALTER TABLE MATSORTPOSITION
  ADD CONSTRAINT XPKMATSORTPOSITION PRIMARY KEY (MATSORTPOSITION_ID);
  
CREATE INDEX XIF1MATSORTPOSITION ON MATSORTPOSITION (PLANTDEPARTMENT_ID  ASC);

CREATE UNIQUE INDEX XAK1MATSORTPOSITION ON MATSORTPOSITION (PLANTDEPARTMENT_ID ASC, CODE ASC);
CREATE UNIQUE INDEX XAK2MATSORTPOSITION ON MATSORTPOSITION (PLANTDEPARTMENT_ID ASC, IDCODE ASC);

ALTER TABLE MATSORTPOSITION
  ADD CONSTRAINT FK_MATSORTPOSITION_PLANTDEP FOREIGN KEY (PLANTDEPARTMENT_ID)
  REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID) ON DELETE CASCADE;
  
----------- MATSORTPOSITIONCONTENTS ---------------
CREATE TABLE MATSORTPOSITIONCONTENTS
(
  MATSORTPOSITION_ID NUMBER NOT NULL,
  CONTAINER_ID NUMBER NOT NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;
ALTER TABLE MATSORTPOSITIONCONTENTS
  ADD CONSTRAINT XPKMATSORTPOSITIONCONTENTS PRIMARY KEY (MATSORTPOSITION_ID);

CREATE UNIQUE INDEX XAK1MATSORTPOSITIONCONTENTS ON MATSORTPOSITIONCONTENTS
(CONTAINER_ID    ASC);

ALTER TABLE MATSORTPOSITIONCONTENTS
  ADD CONSTRAINT FK_MATSORTPOSCONT_MATSORTPOS FOREIGN KEY (MATSORTPOSITION_ID)
  REFERENCES MATSORTPOSITION (MATSORTPOSITION_ID) ON DELETE CASCADE;

ALTER TABLE MATSORTPOSITIONCONTENTS
  ADD CONSTRAINT FK_MATSORTPOSCONT_MATCARRYUN FOREIGN KEY (CONTAINER_ID)
  REFERENCES MATCARRYUNIT(CONTAINER_ID) ON DELETE CASCADE;