---ABS-14132/ ABS-52958

---SHIPMENTLIST table---
DROP INDEX XIF4SHIPMENTLIST;

ALTER TABLE SHIPMENTLIST
  DROP CONSTRAINT SHIPMENTLIST_SHIPBU;
  
ALTER TABLE SHIPMENTLIST DROP COLUMN SHIPMENTLISTTYPE;
ALTER TABLE SHIPMENTLIST DROP COLUMN SHIPBYBUSINESSUNIT_ID;
ALTER TABLE SHIPMENTLIST DROP COLUMN RECEIVESTATUS;
ALTER TABLE SHIPMENTLIST DROP COLUMN CLOSEDATE;

---SHIPMENTLISTOR table---
CREATE SEQUENCE SEQ_SHIPMENTLISTOR
START WITH 1
INCREMENT BY 1;

CREATE TABLE SHIPMENTLISTOR
(
  SHIPMENTLISTOR_ID     NUMBER NOT NULL,
  SHIPMENTLISTNUMBER    NUMBER NOT NULL,
  STATUS_ID             NUMBER NOT NULL,
  SHIPTOBUSINESSUNIT_ID NUMBER NOT NULL,
  COMPLETIONDATE        DATE NULL,
  SHIPBYBUSINESSUNIT_ID NUMBER NOT NULL,
  RECEIVESTATUS         NUMBER DEFAULT 0 NOT NULL,
  CLOSEDATE             DATE NULL,
  SYSTEMUSER_ID         NUMBER NOT NULL,
  TIMESTAMP             DATE NOT NULL
)
;

COMMENT ON COLUMN SHIPMENTLISTOR.SHIPMENTLISTNUMBER
  IS 'Will be generated based on the Note settings as defined for the business unit of the stockroom where the shipment list is created. Please note: same sequence is used for ShipmentList and ShipmentListOR';
COMMENT ON COLUMN SHIPMENTLISTOR.STATUS_ID
  IS 'Possible values :
Status type = SL, linked to status code
10 = New
20 = In Progress
30 = Completed
40 = Closed
';
COMMENT ON COLUMN SHIPMENTLISTOR.RECEIVESTATUS
  IS '0 = Not received
1 = Partly received
2 = Completely received
';

ALTER TABLE SHIPMENTLISTOR
  ADD CONSTRAINT XPKSHIPMENTLISTOR PRIMARY KEY (SHIPMENTLISTOR_ID);

CREATE UNIQUE INDEX XAK1SHIPMENTLISTOR ON SHIPMENTLISTOR (SHIPMENTLISTNUMBER ASC, SHIPTOBUSINESSUNIT_ID ASC);

CREATE INDEX XIF1SHIPMENTLISTOR ON SHIPMENTLISTOR (STATUS_ID ASC);
CREATE INDEX XIF2SHIPMENTLISTOR ON SHIPMENTLISTOR (SHIPTOBUSINESSUNIT_ID ASC);
CREATE INDEX XIF3SHIPMENTLISTOR ON SHIPMENTLISTOR (SHIPBYBUSINESSUNIT_ID ASC);

ALTER TABLE SHIPMENTLISTOR
  ADD CONSTRAINT SHIPMENTLISTOR_ST FOREIGN KEY (STATUS_ID)
  REFERENCES STATUS (STATUS_ID);
  
ALTER TABLE SHIPMENTLISTOR
  ADD CONSTRAINT SHIPMENTLISTOR_SHIPTOBU FOREIGN KEY (SHIPTOBUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
  
ALTER TABLE SHIPMENTLISTOR
  ADD CONSTRAINT SHIPMENTLISTOR_SHIPBYBU FOREIGN KEY (SHIPBYBUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

---SHIPMENTLISTLINE table---
ALTER TABLE SHIPMENTLISTLINE DROP COLUMN QUANTITYRECEIVED;
  
---SHIPMENTLISTLINEOR table---
CREATE SEQUENCE SEQ_SHIPMENTLISTLINEOR
START WITH 1
INCREMENT BY 1;

CREATE TABLE SHIPMENTLISTLINEOR
(
  SHIPMENTLISTLINEOR_ID  NUMBER NOT NULL,
  SHIPMENTLISTOR_ID      NUMBER NOT NULL,
  TRANSPORTUNITNUMBER    NUMBER NOT NULL,
  PRODUCT_LINK_ID        NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
  FINISHINGMETHOD_ID     NUMBER NOT NULL,
  STERILE                NUMBER NOT NULL CONSTRAINT STERILE_VALID1_1
                         CHECK (STERILE IN (0, 1)),
  QUANTITYSHIPPED        NUMBER DEFAULT 0 NOT NULL,
  QUANTITYRECEIVED       NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
)
;

COMMENT ON COLUMN SHIPMENTLISTLINEOR.TRANSPORTUNITNUMBER
  IS 'Unique generated identification for the transport item (e.g. bag or box number) ';
COMMENT ON COLUMN SHIPMENTLISTLINEOR.STERILE
  IS '0 = Not Sterile
1 = Sterile
';

ALTER TABLE SHIPMENTLISTLINEOR
  ADD CONSTRAINT XPKSHIPMENTLISTLINEOR PRIMARY KEY (SHIPMENTLISTLINEOR_ID);

CREATE UNIQUE INDEX XAK1SHIPMENTLISTLINEOR ON SHIPMENTLISTLINEOR (SHIPMENTLISTOR_ID ASC, TRANSPORTUNITNUMBER ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC);
  
CREATE INDEX XIF1SHIPMENTLISTLINEOR ON SHIPMENTLISTLINEOR (SHIPMENTLISTOR_ID ASC);
CREATE INDEX XIF2SHIPMENTLISTLINEOR ON SHIPMENTLISTLINEOR (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF3SHIPMENTLISTLINEOR ON SHIPMENTLISTLINEOR (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF4SHIPMENTLISTLINEOR ON SHIPMENTLISTLINEOR (FINISHINGMETHOD_ID ASC);

ALTER TABLE SHIPMENTLISTLINEOR
  ADD CONSTRAINT SHIPMENTLISTLINEOR_SLOR FOREIGN KEY (SHIPMENTLISTOR_ID)
  REFERENCES SHIPMENTLISTOR (SHIPMENTLISTOR_ID) ON DELETE CASCADE;
  
ALTER TABLE SHIPMENTLISTLINEOR
  ADD CONSTRAINT SHIPMENTLISTLINEOR_PL FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
  
ALTER TABLE SHIPMENTLISTLINEOR
  ADD CONSTRAINT SHIPMENTLISTLINEOR_SDL FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
  
ALTER TABLE SHIPMENTLISTLINEOR
  ADD CONSTRAINT SHIPMENTLISTLINEOR_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);

---SHIPMENTLISTLINEORPACK table---
ALTER TABLE SHIPMENTLISTLINEORPACK RENAME COLUMN SHIPMENTLISTLINE_ID TO SHIPMENTLISTLINEOR_ID;

DROP INDEX XAK1SHIPMENTLISTLINEORPACK;
CREATE UNIQUE INDEX XAK1SHIPMENTLISTLINEORPACK ON SHIPMENTLISTLINEORPACK (SHIPMENTLISTLINEOR_ID ASC, UNIQUEITEM_ID ASC);

DROP INDEX XIF1SHIPMENTLISTLINEORPACK;
CREATE INDEX XIF1SHIPMENTLISTLINEORPACK ON SHIPMENTLISTLINEORPACK (SHIPMENTLISTLINEOR_ID ASC);

ALTER TABLE SHIPMENTLISTLINEORPACK
  DROP CONSTRAINT SHIPMENTLISTLINEORPACK_SLL;
  
ALTER TABLE SHIPMENTLISTLINEORPACK
  ADD CONSTRAINT SHIPMENTLISTLINEORPACK_SLL FOREIGN KEY (SHIPMENTLISTLINEOR_ID)
  REFERENCES SHIPMENTLISTLINEOR (SHIPMENTLISTLINEOR_ID) ON DELETE CASCADE;
