--ABS-14132

---BUSINESSUNIT table---
ALTER TABLE BUSINESSUNIT ADD (DEPOSITLOCATIONSTERILE VARCHAR2(12) NULL,
                              DEPOSITLOCATIONNONSTERILE VARCHAR2(12) NULL);
							 
---REASONCODEDIALOGS table---
INSERT INTO REASONCODEDIALOGS
  (REASONCODE_ID, DIALOG_ID, SELECTABLE, SYSTEMUSER_ID, TIMESTAMP)
  SELECT R.REASONCODE_ID,
         D.DIALOG_ID,
         'Y',
         GETMINSYSTEMUSER(),
         GETCURRENTDATETIME()
    FROM REASONCODE R,
         DIALOGS    D WHERE D.CODE = 'ShipmentListStatusControl';
COMMIT;

INSERT INTO REASONCODEDIALOGS
  (REASONCODE_ID, DIALOG_ID, SELECTABLE, SYSTEMUSER_ID, TIMESTAMP)
  SELECT R.REASONCODE_ID,
         D.DIALOG_ID,
         'Y',
         GETMINSYSTEMUSER(),
         GETCURRENTDATETIME()
    FROM REASONCODE R,
         DIALOGS    D WHERE D.CODE = 'DepositORPacks';
COMMIT;

---SHIPMENTLIST table---
ALTER TABLE SHIPMENTLIST ADD (SHIPMENTLISTTYPE NUMBER DEFAULT 1 NOT NULL
                                  CONSTRAINT SHIPMENTLISTTYPE_VALID1_1 CHECK (SHIPMENTLISTTYPE IN (1, 2)),
                              SHIPBYBUSINESSUNIT_ID NUMBER NULL,
                              RECEIVESTATUS NUMBER DEFAULT 0 NOT NULL
                                  CONSTRAINT RECEIVESTATUS_VALID1_1 CHECK (RECEIVESTATUS IN (0, 1, 2)),
                              CLOSEDATE DATE NULL);

COMMENT ON COLUMN SHIPMENTLIST.SHIPMENTLISTTYPE
  IS '1 = Shipment list for Work Orders
2 = Shipment list for OR pack deposits
';
COMMENT ON COLUMN SHIPMENTLIST.SHIPBYBUSINESSUNIT_ID
  IS 'Only used for ShipmentListType = 2';
COMMENT ON COLUMN SHIPMENTLIST.RECEIVESTATUS
  IS '0 = Not received
1 = Partly received
2 = Completely received. Only used for ShipmentListType = 2.';
COMMENT ON COLUMN SHIPMENTLIST.CLOSEDATE
  IS 'Only used for ShipmentListType = 2.';

CREATE INDEX XIF4SHIPMENTLIST ON SHIPMENTLIST (SHIPBYBUSINESSUNIT_ID ASC);

ALTER TABLE SHIPMENTLIST
  ADD CONSTRAINT SHIPMENTLIST_SHIPBU FOREIGN KEY (SHIPBYBUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;
  
---SHIPMENTLISTLINE table---
ALTER TABLE SHIPMENTLISTLINE ADD (QUANTITYRECEIVED NUMBER DEFAULT 0 NOT NULL);

---STATUS table---
insert into status
          (status_id,
           statustype,
           code,
           legacystatuscode,
           systemuser_id,
           timestamp,
           factorydescription)
        values
          (seq_status.nextval,
           'SL',
           '40',
           '',
           getminsystemuser(),
           getcurrentdatetime(),
           'Closed');
commit;

insert into status_desc
  (status_id,
   language_id,
   description, 
   systemuser_id, 
   timestamp)
  select status_id,
         getminlanguage(),
         factorydescription,
         systemuser_id,
         timestamp
    from status
   where status.statustype = 'SL'
     and status.code = '40';
commit;

---STAY table---
insert into stay
  (stay_id, staytype, staynr, factorydescription, systemuser_id, timestamp)
  select seq_stay.nextval,
         3,
         45,
         'Deposit',
         getminsystemuser(),
         getcurrentdatetime()
    from dual;
commit;

insert into stay
  (stay_id, staytype, staynr, factorydescription, systemuser_id, timestamp)
  select seq_stay.nextval,
         3,
         46,
         'Transit',
         getminsystemuser(),
         getcurrentdatetime()
    from dual;
commit;

insert into stay_desc
	(stay_id,language_id,description,systemuser_id,timestamp)
  select s.stay_id,
         l.language_id,
         s.factorydescription,
         getminsystemuser(),
         getcurrentdatetime()
    from stay s, languages l
    where s.staytype = 3 and s.staynr in (45, 46);
commit;

---UNIQUEORPACK table---
ALTER TABLE UNIQUEORPACK ADD (PRODUCEDBYBU_ID NUMBER NULL);
 
COMMENT ON COLUMN UNIQUEORPACK.PRODUCEDBYBU_ID
  IS 'Needs to be nullable because it cannot be filled for past OR packs.';

CREATE INDEX XIF14UNIQUEORPACK ON UNIQUEORPACK (PRODUCEDBYBU_ID ASC);
 
ALTER TABLE UNIQUEORPACK
  ADD CONSTRAINT UNIQUEORPACK_PRODUCEDBYBU FOREIGN KEY (PRODUCEDBYBU_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;
