-- ABS-90973/ ABS-106653

----------CONTAINER table-----------------
ALTER TABLE CONTAINER ADD (MATCARRYALLOWED NUMBER DEFAULT 0 NOT NULL
                                           CONSTRAINT CHK_CONTAINER_MATCARRYALLOWED CHECK (MATCARRYALLOWED IN (0, 1)));

COMMENT ON COLUMN CONTAINER.MATCARRYALLOWED
  IS '0 = Standard Container
1 = Allowed to be used as Mat Carry Unit (pallet)
Option 1 is only used for the module MATTRANSPORT.';

---------- MATCARRYUNIT TABLE -----------
CREATE TABLE MATCARRYUNIT
(
  CONTAINER_ID  NUMBER NOT NULL,
  USAGE         NUMBER NOT NULL CONSTRAINT CHK_MATCARRYUNIT_USAGE
                CHECK (USAGE IN (0, 1, 2)),
  USEDBYAPP     NUMBER NOT NULL CONSTRAINT CHK_MATCARRYUNIT_USEDBYAPP
                CHECK (USEDBYAPP IN (0, 1, 2, 3)),
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP     DATE NOT NULL
);

COMMENT ON COLUMN MATCARRYUNIT.USAGE
  IS '0 = Available
1 = Store Clean Mats
2 = Store Soil Mats
';
COMMENT ON COLUMN MATCARRYUNIT.USEDBYAPP
  IS '0 = Not used
1 = Shipment List
2 = Bulk Packing Assistant
3 = Route Assistant
';

ALTER TABLE MATCARRYUNIT
  ADD CONSTRAINT XPKMATCARRYUNIT PRIMARY KEY (CONTAINER_ID);
  
ALTER TABLE MATCARRYUNIT
  ADD CONSTRAINT FK_MATCARRYUNIT_CONTAINER FOREIGN KEY (CONTAINER_ID)
  REFERENCES CONTAINER (CONTAINER_ID) ON DELETE CASCADE;

----------- MATCARRYUNITCONTENTS TABLE ---------------
CREATE SEQUENCE SEQ_MATCARRYUNITCONTENTS
START WITH 1
INCREMENT BY 1;

CREATE TABLE MATCARRYUNITCONTENTS
(
  MATCARRYUNITCONTENTS_ID NUMBER NOT NULL,
  CONTAINER_ID            NUMBER NOT NULL,
  UNIQUEITEM_LINK_ID      NUMBER NULL,
  MATIDCODE               VARCHAR2(24) NOT NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;

COMMENT ON COLUMN MATCARRYUNITCONTENTS.MATIDCODE
  IS 'Scanned ID code for soil mats';

ALTER TABLE MATCARRYUNITCONTENTS
  ADD CONSTRAINT XPKMATCARRYUNITCONTENTS PRIMARY KEY (MATCARRYUNITCONTENTS_ID);
  
CREATE INDEX XIF1MATCARRYUNITCONTENTS ON MATCARRYUNITCONTENTS (CONTAINER_ID ASC);
CREATE INDEX XIF2MATCARRYUNITCONTENTS ON MATCARRYUNITCONTENTS (UNIQUEITEM_LINK_ID ASC);
CREATE UNIQUE INDEX XAK1MATCARRYUNITCONTENTS ON MATCARRYUNITCONTENTS (CONTAINER_ID ASC, NVL(UNIQUEITEM_LINK_ID,0) ASC, MATIDCODE ASC);

ALTER TABLE MATCARRYUNITCONTENTS
  ADD CONSTRAINT FK_MATCARRYUNITCONT_CONT FOREIGN KEY (CONTAINER_ID)
  REFERENCES MATCARRYUNIT (CONTAINER_ID) ON DELETE CASCADE;
  
ALTER TABLE MATCARRYUNITCONTENTS
  ADD CONSTRAINT FK_MATCARRYUNITCONT_UIL FOREIGN KEY (UNIQUEITEM_LINK_ID)
  REFERENCES UNIQUEITEM_LINK (UNIQUEITEM_LINK_ID) ON DELETE SET NULL;
