--ABS-90975
---------------------SHIPMENTLISTMAT-------------
CREATE SEQUENCE SEQ_SHIPMENTLISTMAT
START WITH 1
INCREMENT BY 1;

CREATE TABLE SHIPMENTLISTMAT
(
SHIPMENTLISTMAT_ID NUMBER NOT NULL,
SHIPFROMBUSINESSUNIT_ID NUMBER NOT NULL,
SHIPMENTLISTNUMBER NUMBER NOT NULL,
SHIPFROMSTOCKROOM_ID NUMBER NULL,
STATUS_ID NUMBER NOT NULL,
SHIPTOBUSINESSUNIT_ID NUMBER NOT NULL,
SHIPTOSTOCKROOM_ID NUMBER NULL,
CREATIONDATE DATE NOT NULL,
COMPLETIONDATE DATE NULL,
CLOSINGDATE DATE NULL,
REMARK VARCHAR2(80) NULL,
READYFORSHIPMENT VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_SHIPLISTMAT_READY CHECK (READYFORSHIPMENT IN ('Y','N')) ,
SYSTEMUSER_ID   NUMBER NOT NULL,
TIMESTAMP       DATE NOT NULL
);

COMMENT ON COLUMN SHIPMENTLISTMAT.SHIPFROMBUSINESSUNIT_ID  IS 'In case send from a stockroom then this is the BU that manages the stockroom
';

COMMENT ON COLUMN SHIPMENTLISTMAT.SHIPMENTLISTNUMBER IS 'The ShipmentListNumber is generated using the Note number sequence of the BusinessUnit';

COMMENT ON COLUMN SHIPMENTLISTMAT.SHIPFROMSTOCKROOM_ID IS 'Note: when filled the BusinessUnt_ID must be filled with the BU that manages the Stockroom.
';

COMMENT ON COLUMN SHIPMENTLISTMAT.STATUS_ID IS 'Foreign key to Status table.
10 = New
20 = In progress
30 = Completed
40 = Closed
';

COMMENT ON COLUMN SHIPMENTLISTMAT.SHIPTOSTOCKROOM_ID IS 'Note: when filled the ShipToBusinessUnt_ID must be filled with the BU that manages the Stockroom.
';

ALTER TABLE SHIPMENTLISTMAT ADD CONSTRAINT XPKSHIPMENTLISTMAT PRIMARY KEY (SHIPMENTLISTMAT_ID);

CREATE UNIQUE INDEX XAK1SHIPMENTLISTMAT  ON SHIPMENTLISTMAT  (SHIPFROMBUSINESSUNIT_ID ASC, SHIPMENTLISTNUMBER ASC);

CREATE INDEX XIF1SHIPMENTLISTMAT  ON SHIPMENTLISTMAT  (SHIPFROMBUSINESSUNIT_ID ASC);
CREATE INDEX XIF2SHIPMENTLISTMAT  ON SHIPMENTLISTMAT  (SHIPFROMSTOCKROOM_ID ASC);
CREATE INDEX XIF3SHIPMENTLISTMAT  ON SHIPMENTLISTMAT  (STATUS_ID ASC);
CREATE INDEX XIF4SHIPMENTLISTMAT  ON SHIPMENTLISTMAT (SHIPTOBUSINESSUNIT_ID ASC);
CREATE INDEX XIF5SHIPMENTLISTMAT  ON SHIPMENTLISTMAT (SHIPTOSTOCKROOM_ID ASC);

ALTER TABLE SHIPMENTLISTMAT ADD CONSTRAINT FK_SHIPLISTMAT_FROMBU FOREIGN KEY (SHIPFROMBUSINESSUNIT_ID)
REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

ALTER TABLE SHIPMENTLISTMAT ADD CONSTRAINT FK_SHIPLISTMAT_FROMSTOCK FOREIGN KEY (SHIPFROMSTOCKROOM_ID )
REFERENCES STOCKROOM(STOCKROOM_ID) ON DELETE SET NULL;

ALTER TABLE SHIPMENTLISTMAT ADD CONSTRAINT FK_SHIPLISTMAT_STATUS FOREIGN KEY (STATUS_ID)
REFERENCES STATUS(STATUS_ID);

ALTER TABLE SHIPMENTLISTMAT ADD CONSTRAINT FK_SHIPLISTMAT_TOBU FOREIGN KEY (SHIPTOBUSINESSUNIT_ID)
REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;

ALTER TABLE SHIPMENTLISTMAT ADD CONSTRAINT FK_SHIPLISTMAT_TOSTOCK FOREIGN KEY (SHIPTOSTOCKROOM_ID)
REFERENCES STOCKROOM(STOCKROOM_ID) ON DELETE SET NULL;

---------------------SHIPMENTLISTMATLINE-------------
CREATE SEQUENCE SEQ_SHIPMENTLISTMATLINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE SHIPMENTLISTMATLINE
(
SHIPMENTLISTMATLINE_ID NUMBER NOT NULL,
SHIPMENTLISTMAT_ID NUMBER NOT NULL,
CONTAINER_ID NUMBER NULL,
PRIMARYID VARCHAR2(24) NOT NULL,
RECEIVETIMESTAMP DATE NULL,
COMPLETIONSTATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_SHIPLISTMATLN_COMPSTAT CHECK(COMPLETIONSTATUS IN (0, 1, 2)),
SYSTEMUSER_ID   NUMBER NOT NULL,
TIMESTAMP       DATE NOT NULL
);

COMMENT ON COLUMN SHIPMENTLISTMATLINE.CONTAINER_ID IS 'Foreign key to MatCarryUnit table.
Note that MatCarryUnit can be deleted, therefore this attribute is nullable and the PrimaryID is stored.
';

COMMENT ON COLUMN SHIPMENTLISTMATLINE.COMPLETIONSTATUS IS '0 = Shipped not received
1 = Shipped,and received
2 = Received, not shipped
';

ALTER TABLE SHIPMENTLISTMATLINE ADD CONSTRAINT XPKSHIPMENTLISTMATLINE PRIMARY KEY (SHIPMENTLISTMATLINE_ID);

CREATE UNIQUE INDEX XAK1SHIPMENTLISTMATLINE  ON SHIPMENTLISTMATLINE (SHIPMENTLISTMAT_ID ASC, NVL(CONTAINER_ID,0) ASC, PRIMARYID ASC);

CREATE INDEX XIF1SHIPMENTLISTMATLINE ON SHIPMENTLISTMATLINE (SHIPMENTLISTMAT_ID  ASC);
CREATE INDEX XIF2SHIPMENTLISTMATLINE  ON SHIPMENTLISTMATLINE (CONTAINER_ID ASC);

ALTER TABLE SHIPMENTLISTMATLINE ADD CONSTRAINT FK_SHIPLISTMATLN_SHIPLM FOREIGN KEY (SHIPMENTLISTMAT_ID)
REFERENCES SHIPMENTLISTMAT (SHIPMENTLISTMAT_ID) ON DELETE CASCADE;

ALTER TABLE SHIPMENTLISTMATLINE ADD CONSTRAINT FK_SHIPLISTMATLN_MATC FOREIGN KEY (CONTAINER_ID)
REFERENCES MATCARRYUNIT(CONTAINER_ID) ON DELETE SET NULL;

---------------------SHIPMENTLISTMATLINEDETAIL-------------
CREATE TABLE SHIPMENTLISTMATLINEDETAIL
(
SHIPMENTLISTMATLINE_ID NUMBER NOT NULL,
UNIQUEITEM_LINK_ID NUMBER NOT NULL,
SYSTEMUSER_ID   NUMBER NOT NULL,
TIMESTAMP       DATE NOT NULL
);

ALTER TABLE SHIPMENTLISTMATLINEDETAIL ADD CONSTRAINT XPKSHIPMENTLISTMATLINEDETAIL PRIMARY KEY (SHIPMENTLISTMATLINE_ID, UNIQUEITEM_LINK_ID );

CREATE INDEX XIF1SHIPMENTLISTMATLINEDETAIL ON SHIPMENTLISTMATLINEDETAIL (SHIPMENTLISTMATLINE_ID  ASC);
CREATE INDEX XIF2SHIPMENTLISTMATLINEDETAIL  ON SHIPMENTLISTMATLINEDETAIL (UNIQUEITEM_LINK_ID ASC);

ALTER TABLE SHIPMENTLISTMATLINEDETAIL ADD CONSTRAINT FK_SHIPLISTMATLND_SHIPLMLN FOREIGN KEY (SHIPMENTLISTMATLINE_ID)
REFERENCES SHIPMENTLISTMATLINE (SHIPMENTLISTMATLINE_ID) ON DELETE CASCADE;

ALTER TABLE SHIPMENTLISTMATLINEDETAIL ADD CONSTRAINT FK_SHIPLISTMATLND_UNIQ_LK FOREIGN KEY (UNIQUEITEM_LINK_ID)
REFERENCES UNIQUEITEM_LINK(UNIQUEITEM_LINK_ID) ON DELETE CASCADE;
