--Order 20015066

---STATUS table---

COMMENT ON COLUMN STATUS.STATUSTYPE IS 'PN=Packing/delivery note
WN=WeighingNote
IN=Invoice
ST=Statement
RB=RemitBatch
WO=WorkOrder
UI=Unique item
SR=Straight Even Return
GD=Garment Dispenser
PO=Purchase Order
BN=Unique Bundle
UR=Unique Residential Item
PL=Pick List
CA=Cart
OR=OR Pack
SL =Shipment List';

insert into status 
select seq_status.nextval,
       'SL',
       10,
       '',
       getminsystemuser(),
       sysdate,
       'New'
from dual;
commit;

insert into status 
select seq_status.nextval,
       'SL',
       20,
       '',
       getminsystemuser(),
       sysdate,
       'In Progress'
from dual;
commit;

insert into status 
select seq_status.nextval,
       'SL',
       30,
       '',
       getminsystemuser(),
       sysdate,
       'Completed'
from dual;
commit;


---REASONCODE table---

ALTER TABLE REASONCODE
	ADD (PURCHASEORDERPRIORITY_ID NUMBER NULL);

COMMENT ON COLUMN REASONCODE.PURCHASEORDERPRIORITY_ID IS 'Only applicable when module POSPLIT is in the license of the plant.';

CREATE INDEX XIF2REASONCODE ON REASONCODE
(PURCHASEORDERPRIORITY_ID   ASC);

ALTER TABLE REASONCODE
ADD CONSTRAINT REASONCODE_ORDERPRIO FOREIGN KEY (PURCHASEORDERPRIORITY_ID) REFERENCES ORDERPRIORITY (ORDERPRIORITY_ID) ON DELETE SET NULL;

---MODIFICATION table---

ALTER TABLE MODIFICATION
	ADD (EXECUTEDBY NUMBER  DEFAULT 1 NOT NULL CONSTRAINT EXECUTEDBY_Valid1_1 CHECK (EXECUTEDBY BETWEEN 1 AND 2));

ALTER TABLE MODIFICATION
	ADD (DATEINACTIVE DATE  DEFAULT to_date('31/12/2099','dd/mm/yyyy') NOT NULL);

COMMENT ON COLUMN MODIFICATION.EXECUTEDBY IS 'Identifies if a garment that requires this modification will be taken from stock when needed or always has to be ordered as make-to-order. Possible values:
1 = Internal
2 = External
';

COMMENT ON COLUMN MODIFICATION.DATEINACTIVE IS 'Defines the date after which the modification may no longer be applied to a new wearer inventory
Default value 31-12-2099
';


---SIZEDEFINITION table---

ALTER TABLE SIZEDEFINITION
	ADD (EXECUTEDBY NUMBER  DEFAULT 1 NOT NULL CONSTRAINT EXECUTEDBY_Valid1_2 CHECK (EXECUTEDBY BETWEEN 0 AND 2));

COMMENT ON COLUMN SIZEDEFINITION.EXECUTEDBY IS 'Identifies if a size will be taken from stock when needed or always has to be ordered as make-to-order. Possible values:
0 = Not applicable
1 = Internal
2 = External ';

update sizedefinition sd
set sd.executedby = 0
where sd.usedforemblem = 'Y';

commit;


---LOCATIONTYPE table---

insert into locationtype
  (locationtype_id,
   code,
   factorydescription,
   systemuser_id,
   timestamp,
   pickinglocation,
   valuecontribution,
   priority,
   active,
   useforpurchreqcalculation)
values
  (12,
   'TRANSIT',
   'Transit',
   getminsystemuser(),
   getcurrentdatetime(),
   'N',
   'Y',
   0,
   'Y',
   1);
commit;

insert into locationtype_desc
  (locationtype_id, language_id, description, systemuser_id, timestamp)
  select locationtype_id, getminlanguage(), code, systemuser_id, timestamp
    from locationtype
   where locationtype.code = 'TRANSIT';
commit;


---CUSTOMER table---

ALTER TABLE CUSTOMER
	ADD (PREWASHFORGARMENTS NUMBER  DEFAULT 0 NOT NULL CONSTRAINT PREWASHFORGARMENTS_Valid1_1 CHECK (PREWASHFORGARMENTS IN (0, 1)));

COMMENT ON COLUMN CUSTOMER.PREWASHFORGARMENTS IS '0 = Not applicable
1 = Pre wash garments
';


---STOCKROOM table---

ALTER TABLE STOCKROOM
	ADD (PODELIVERYTERM NUMBER  DEFAULT 0 NOT NULL);

ALTER TABLE STOCKROOM
	ADD (MOVEITEMSINTOTRANSIT NUMBER  DEFAULT 0 NOT NULL CONSTRAINT MOVEITEMSINTOTRANSIT_Valid1_1 CHECK (MOVEITEMSINTOTRANSIT IN (0, 1)));

ALTER TABLE STOCKROOM
	ADD (TRANSITLOCATION_ID NUMBER NULL);

ALTER TABLE STOCKROOM
	ADD (USEPOSPLITFORGARMENTS NUMBER  DEFAULT 0 NOT NULL CONSTRAINT USEPOSPLITFORGARMENTS_Valid1_1 CHECK (USEPOSPLITFORGARMENTS IN (0, 1)));

COMMENT ON COLUMN STOCKROOM.PODELIVERYTERM IS 'Parameter used to determine the delivery date for a PO';

COMMENT ON COLUMN STOCKROOM.MOVEITEMSINTOTRANSIT IS 'Flag that indicates whether any stock transactions should be created immediately when issueing items or that they should be created later and the items are moved into transit first. Possible values:
0 = Make stocktransactions immediately
1 = Move items into transit, make stocktransactions later
';

COMMENT ON COLUMN STOCKROOM.USEPOSPLITFORGARMENTS IS 'Flag that indicates whether PO Splitting Criteria should be used when generating PO concerning garments for this stockroom. Possible values:
0 = PO Split criterions will not be used
1 = PO Split criterions will be used
';

CREATE INDEX XIF5STOCKROOM ON STOCKROOM
(TRANSITLOCATION_ID   ASC);

ALTER TABLE STOCKROOM
ADD CONSTRAINT STOCKROOM_STOCKROOMLOC FOREIGN KEY (TRANSITLOCATION_ID) REFERENCES STOCKROOMLOCATION (STOCKROOMLOCATION_ID) ON DELETE SET NULL;


---PURCHASEORDERHEADER table---

ALTER TABLE PURCHASEORDERHEADER
	ADD (ORDERPRIORITY_ID     NUMBER NULL ,
	     REQUESTEDDELIVERYDATE DATE DEFAULT  to_date('31/12/2099','dd/mm/yyyy')  NOT NULL ,
	     CREATIONBUSINESSUNIT_ID NUMBER NULL,
	     SPLITCUSTOMER_LINK_ID NUMBER NULL,
	     EXECUTEDBY           NUMBER DEFAULT  1 NOT NULL  CONSTRAINT  EXECUTEDBY_Valid1_3 CHECK 					(EXECUTEDBY BETWEEN 1 AND 2),
	     REASONCODE_ID        NUMBER NULL ,
	     ALLOWPHASEDDELIVERY  VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField118 CHECK (ALLOWPHASEDDELIVERY IN ('Y', 'N'))
	     );

COMMENT ON COLUMN PURCHASEORDERHEADER.ORDERPRIORITY_ID IS 'ID of the purchase order priority that identifies the priority of the current purchase order.
Foreign key to OrderPriority table. Mandatory, so must be filled.
Will be filled by default with the purchase order priority that is marked as being the default for type PurchaseOrder.
Will only be filled with a value <> default value’ when module POSPLIT is in the license
';

COMMENT ON COLUMN PURCHASEORDERHEADER.REQUESTEDDELIVERYDATE IS 'Latest possible delivery date for purchase order based on stockroom PO delivery terms
Default value 31-12-2099
Will only be filled with a value <> 31-12-2099 when module POSPLIT is in the license
';

COMMENT ON COLUMN PURCHASEORDERHEADER.CREATIONBUSINESSUNIT_ID IS 'ID of creation business unit (copied from work orders for which the purchase order has been made).
Foreign key to Business unit table. Optional so may be left empty. Default null
Will only be filled when module POSPLIT is in the license
';

COMMENT ON COLUMN PURCHASEORDERHEADER.SPLITCUSTOMER_LINK_ID IS 'ID of customer for which the purchase order has been made (without being customer owned and/or related)
Foreign key to Customer_Link table. Optional, so may be left empty. Default null
Will only be filled when module POSPLIT is in the license
';

COMMENT ON COLUMN PURCHASEORDERHEADER.EXECUTEDBY IS 'Identifies if this purchase order has been created for work orders that have at least one work order line that has to be executed by an external party. Possible values:
1 = Internal
2 = External
Database constraint: between (and including) 1 and 2';

COMMENT ON COLUMN PURCHASEORDERHEADER.REASONCODE_ID IS 'ID of reason code (copied from work orders for which the purchase order has been made)
Foreign key to ReasonCode table. Optional so may be left empty. Default null
Will only be filled when module POSPLIT is in the license
';

COMMENT ON COLUMN PURCHASEORDERHEADER.ALLOWPHASEDDELIVERY IS 'Y/N (copied from work orders for which the purchase order has been made)
Default value ‘N’
Will only be filled with a value <> ‘N’ when module POSPLIT is in the license
';

CREATE INDEX XIF9PURCHASEORDERHEADER ON PURCHASEORDERHEADER
(ORDERPRIORITY_ID   ASC);

CREATE INDEX XIF10PURCHASEORDERHEADER ON PURCHASEORDERHEADER
(CREATIONBUSINESSUNIT_ID   ASC);

CREATE INDEX XIF11PURCHASEORDERHEADER ON PURCHASEORDERHEADER
(SPLITCUSTOMER_LINK_ID   ASC);

CREATE INDEX XIF12PURCHASEORDERHEADER ON PURCHASEORDERHEADER
(REASONCODE_ID   ASC);

ALTER TABLE PURCHASEORDERHEADER
ADD CONSTRAINT POH_ORDERPRIORITY FOREIGN KEY (ORDERPRIORITY_ID) REFERENCES ORDERPRIORITY (ORDERPRIORITY_ID);

ALTER TABLE PURCHASEORDERHEADER
ADD CONSTRAINT POH_CREATIONBU FOREIGN KEY (CREATIONBUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;

ALTER TABLE PURCHASEORDERHEADER
ADD CONSTRAINT POH_SPLITCUSTOMERLINK FOREIGN KEY (SPLITCUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID) ON DELETE SET NULL;

ALTER TABLE PURCHASEORDERHEADER
ADD CONSTRAINT POH_REASONCODE FOREIGN KEY (REASONCODE_ID) REFERENCES REASONCODE (REASONCODE_ID) ON DELETE SET NULL;

update purchaseorderheader poh
   set poh.orderpriority_id =
       (select opr.orderpriority_id
          from orderpriority opr
         where opr.ordertype = 2
           and opr.standardpriority = 'Y');
commit;

ALTER TABLE PURCHASEORDERHEADER MODIFY (ORDERPRIORITY_ID NOT NULL);


---WORKORDER table---

ALTER TABLE WORKORDER
	ADD (EXECUTEDBY NUMBER  DEFAULT 1 NOT NULL CONSTRAINT EXECUTEDBY_Valid1_4 CHECK (EXECUTEDBY BETWEEN 1 AND 2));

COMMENT ON COLUMN WORKORDER.EXECUTEDBY IS 'Identifies if this work order has  at least one work order line that has to be executed by an external party. Possible values:
1 = Internal
2 = External
Default value 1
Database constraint: between (and including) 1 and 2
';

---WORKORDERLINE table---

ALTER TABLE WORKORDERLINE
	ADD (EXECUTEDBY NUMBER  DEFAULT 1 NOT NULL CONSTRAINT EXECUTEDBY_Valid1_5 CHECK (EXECUTEDBY BETWEEN 1 AND 2));

COMMENT ON COLUMN WORKORDERLINE.EXECUTEDBY IS 'Identifies if this work order line contains at least one modification that has to be executed by an external party. Possible values:
1 = Internal
2 = External
Default value 1
Database constraint: between (and including) 1 and 2
';


---STOCKROOMSERVICINGBU table---

ALTER TABLE STOCKROOMSSERVICINGBU
  ADD (SERVICEDAYS NUMBER  DEFAULT 0 NOT NULL CONSTRAINT SERVICEDAYS_Valid1_1 CHECK 
(SERVICEDAYS BETWEEN 0 AND 14));

ALTER TABLE STOCKROOMSSERVICINGBU
  ADD (CUSTOMSDECLARATION VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT 
YN_ValidationField119 CHECK (CUSTOMSDECLARATION IN ('Y', 'N')));

ALTER TABLE STOCKROOMSSERVICINGBU
  ADD (CUSTOMSEMPLOYEE_ID NUMBER  NULL);

--section break for pack_Script

ALTER TABLE STOCKROOMSSERVICINGBU
  ADD (LOGICALDAYS_DAY1 NUMBER  DEFAULT 0 NOT NULL CONSTRAINT LOGICALDAYS_Valid1_1 CHECK 
(LOGICALDAYS_DAY1 BETWEEN 0 AND 14));

ALTER TABLE STOCKROOMSSERVICINGBU
  ADD (LOGICALDAYS_DAY2 NUMBER  DEFAULT 0 NOT NULL CONSTRAINT LOGICALDAYS_Valid1_2 CHECK 
(LOGICALDAYS_DAY2 BETWEEN 0 AND 14));

ALTER TABLE STOCKROOMSSERVICINGBU
  ADD (LOGICALDAYS_DAY3 NUMBER  DEFAULT 0 NOT NULL CONSTRAINT LOGICALDAYS_Valid1_3 CHECK 
(LOGICALDAYS_DAY3 BETWEEN 0 AND 14));

ALTER TABLE STOCKROOMSSERVICINGBU
  ADD (LOGICALDAYS_DAY4 NUMBER  DEFAULT 0 NOT NULL CONSTRAINT LOGICALDAYS_Valid1_4 CHECK 
(LOGICALDAYS_DAY4 BETWEEN 0 AND 14));

ALTER TABLE STOCKROOMSSERVICINGBU
  ADD (LOGICALDAYS_DAY5 NUMBER  DEFAULT 0 NOT NULL CONSTRAINT LOGICALDAYS_Valid1_5 CHECK 
(LOGICALDAYS_DAY5 BETWEEN 0 AND 14));

ALTER TABLE STOCKROOMSSERVICINGBU
  ADD (LOGICALDAYS_DAY6 NUMBER  DEFAULT 0 NOT NULL CONSTRAINT LOGICALDAYS_Valid1_6 CHECK 
(LOGICALDAYS_DAY6 BETWEEN 0 AND 14));

ALTER TABLE STOCKROOMSSERVICINGBU
  ADD (LOGICALDAYS_DAY7 NUMBER  DEFAULT 0 NOT NULL CONSTRAINT LOGICALDAYS_Valid1_7 CHECK 
(LOGICALDAYS_DAY7 BETWEEN 0 AND 14));

COMMENT ON COLUMN STOCKROOMSSERVICINGBU.SERVICEDAYS IS 'Indicates number of service days needed 

for executing a work order. Allowed values 0..14, default 0';

COMMENT ON COLUMN STOCKROOMSSERVICINGBU.CUSTOMSDECLARATION IS 'Y/N. 
Indicates whether a customs declaration is  needed when delivering items to this business unit.  
Default value ‘N’.
';

COMMENT ON COLUMN STOCKROOMSSERVICINGBU.LOGICALDAYS_DAY1 IS 'Indicates number of logistical days 
needed when delivering items to this business unit on day 1. Allowed values 0..14, default 0';

COMMENT ON COLUMN STOCKROOMSSERVICINGBU.LOGICALDAYS_DAY2 IS 'Indicates number of logistical days 
needed when delivering items to this business unit on day 2. Allowed values 0..14, default 0';

COMMENT ON COLUMN STOCKROOMSSERVICINGBU.LOGICALDAYS_DAY3 IS 'Indicates number of logistical days 
needed when delivering items to this business unit on day 3. Allowed values 0..14, default 0';

COMMENT ON COLUMN STOCKROOMSSERVICINGBU.LOGICALDAYS_DAY4 IS 'Indicates number of logistical days 
needed when delivering items to this business unit on day 2. Allowed values 0..14, default 0';

COMMENT ON COLUMN STOCKROOMSSERVICINGBU.LOGICALDAYS_DAY5 IS 'Indicates number of logistical days 
needed when delivering items to this business unit on day 2. Allowed values 0..14, default 0';

COMMENT ON COLUMN STOCKROOMSSERVICINGBU.LOGICALDAYS_DAY6 IS 'Indicates number of logistical days 
needed when delivering items to this business unit on day 2. Allowed values 0..14, default 0';

COMMENT ON COLUMN STOCKROOMSSERVICINGBU.LOGICALDAYS_DAY7 IS 'Indicates number of logistical days 
needed when delivering items to this business unit on day 2. Allowed values 0..14, default 0';

CREATE INDEX XIF3STOCKROOMSSERVICINGBU ON STOCKROOMSSERVICINGBU
(CUSTOMSEMPLOYEE_ID   ASC);

ALTER TABLE STOCKROOMSSERVICINGBU
ADD CONSTRAINT STOCKROOMSSERVBU_CUSTOMEMPL FOREIGN KEY (CUSTOMSEMPLOYEE_ID) REFERENCES EMPLOYEE 
(EMPLOYEE_ID) ON DELETE SET NULL;


---POSPLITCRITERIONTYPE table---

CREATE SEQUENCE SEQ_POSPLITCRITERIONTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE POSPLITCRITERIONTYPE
(
	POSPLITCRITERIONTYPE_ID     NUMBER NOT NULL ,
	ITEMTYPE             NUMBER DEFAULT  2  NOT NULL  CONSTRAINT  ITEMTYPE_Valid1_1 CHECK (ITEMTYPE BETWEEN 1 AND 4),
	CODE                 VARCHAR2(12) NOT NULL ,
	FACTORYDESCRIPTION   VARCHAR2(40) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL ,
	ACTIVE               VARCHAR2(1) DEFAULT  'Y'  NOT NULL  CONSTRAINT  YN_ValidationField120 CHECK (ACTIVE IN ('Y', 'N'))
);

COMMENT ON COLUMN POSPLITCRITERIONTYPE.ITEMTYPE IS 'Indicates for which type of items this criterion will be used. Possible values :
1 = Linen
2 = Garments
3 = Sales Only / Disposables
4 = Emblems
Default value 2.
For now only type 2 will be supported, but in this way it is ready for future extensions
Database constraint: between (and including) 1 and 2
';

COMMENT ON COLUMN POSPLITCRITERIONTYPE.CODE IS 'Logical identification of a criterion type';

COMMENT ON COLUMN POSPLITCRITERIONTYPE.FACTORYDESCRIPTION IS 'Factory description of the criterion type.';

COMMENT ON COLUMN POSPLITCRITERIONTYPE.ACTIVE IS 'Y/N. Indication whether the criterion type will be used or not by the laundry.
';

ALTER TABLE POSPLITCRITERIONTYPE
ADD CONSTRAINT  XPKPOSPLITCRITERIONTYPE PRIMARY KEY (POSPLITCRITERIONTYPE_ID);

CREATE UNIQUE INDEX XAK1POSPLITCRITERIONTYPE ON POSPLITCRITERIONTYPE
(ITEMTYPE   ASC,CODE   ASC);

--insert into POSPLITCRITERIONTYPE table--

insert into POSPLITCRITERIONTYPE
values
(1,
 2,
 'WOCBU',
 'Work Order Creation Business Unit',
 getminsystemuser(),
 sysdate,
 'N');
commit;

insert into POSPLITCRITERIONTYPE
values
(2,
 2,
 'WOCUSTNO',
 'Work Order Customer Number',
 getminsystemuser(),
 sysdate,
 'N');
commit;

insert into POSPLITCRITERIONTYPE
values
(3,
 2,
 'WORC',
 'Work Order Reason Code',
 getminsystemuser(),
 sysdate,
 'N');
commit;

insert into POSPLITCRITERIONTYPE
values
(4,
 2,
 'WOEXBY',
 'Work Order Executed By',
 getminsystemuser(),
 sysdate,
 'Y');
commit;

insert into POSPLITCRITERIONTYPE
values
(5,
 2,
 'WOREQDEL',
 'Work Order Requested Delivery Date',
 getminsystemuser(),
 sysdate,
 'Y');
commit;

insert into POSPLITCRITERIONTYPE
values
(6,
 2,
 'WOPHASEDDEL',
 'Work Order Phased Delivery Allowed',
 getminsystemuser(),
 sysdate,
 'Y');
commit;

---POSPLITCRITERIONTYPE_DESC table---

CREATE TABLE POSPLITCRITERIONTYPE_DESC
(
	POSPLITCRITERIONTYPE_ID     NUMBER NOT NULL ,
	LANGUAGE_ID          NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(40) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE POSPLITCRITERIONTYPE_DESC
ADD CONSTRAINT  XPKPOSPLITCRITERIONTYPE_DESC PRIMARY KEY (POSPLITCRITERIONTYPE_ID,LANGUAGE_ID);

CREATE INDEX XIF1POSPLITCRITERIONTYPE_DESC ON POSPLITCRITERIONTYPE_DESC
(POSPLITCRITERIONTYPE_ID   ASC);

CREATE INDEX XIF2POSPLITCRITERIONTYPE_DESC ON POSPLITCRITERIONTYPE_DESC
(LANGUAGE_ID   ASC);

ALTER TABLE POSPLITCRITERIONTYPE_DESC
ADD CONSTRAINT POSPLITCRITTYPE_DESC_POSPLITCR FOREIGN KEY (POSPLITCRITERIONTYPE_ID) REFERENCES POSPLITCRITERIONTYPE (POSPLITCRITERIONTYPE_ID) ON DELETE CASCADE;

ALTER TABLE POSPLITCRITERIONTYPE_DESC
ADD CONSTRAINT POSPLITCRITTYPE_DESC_LANGUAGES FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

---BUSINESSUNIT table---

ALTER TABLE BUSINESSUNIT
	ADD (SHIPMENTLISTNUMBERPREFIX VARCHAR2(6) NULL);

ALTER TABLE BUSINESSUNIT
	ADD (SHIPMENTLISTNUMBERLENGTH NUMBER NULL);

---SHIPMENTLIST table---

CREATE SEQUENCE SEQ_SHIPMENTLIST
START WITH 1
INCREMENT BY 1;

CREATE TABLE SHIPMENTLIST
(
	SHIPMENTLIST_ID      NUMBER NOT NULL ,
	SHIPMENTLISTNUMBER   NUMBER NOT NULL ,
	SUPPLYINGSTOCKROOM_ID NUMBER NULL ,
	STATUS_ID            NUMBER NOT NULL ,
	SHIPTOBUSINESSUNIT_ID NUMBER NOT NULL ,
	COMPLETIONDATE       DATE NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN SHIPMENTLIST.SHIPMENTLISTNUMBER IS 'Number of the shipment list. Will be generated based on the Note settings as defined for the business unit of the stockroom where the shipment list is created.';

COMMENT ON COLUMN SHIPMENTLIST.STATUS_ID IS 'ID of status. Possible values :
Status type = SL, linked to status code
10 = New
20 = In Progress
30 = Completed
';

CREATE UNIQUE INDEX XAK1SHIPMENTLIST ON SHIPMENTLIST
(SHIPMENTLISTNUMBER   ASC,SHIPTOBUSINESSUNIT_ID   ASC);

CREATE INDEX XIF1SHIPMENTLIST ON SHIPMENTLIST
(SUPPLYINGSTOCKROOM_ID   ASC);

CREATE INDEX XIF2SHIPMENTLIST ON SHIPMENTLIST
(STATUS_ID   ASC);

CREATE INDEX XIF3SHIPMENTLIST ON SHIPMENTLIST
(SHIPTOBUSINESSUNIT_ID   ASC);

ALTER TABLE SHIPMENTLIST
ADD CONSTRAINT  XPKSHIPMENTLIST PRIMARY KEY (SHIPMENTLIST_ID);

ALTER TABLE SHIPMENTLIST
ADD CONSTRAINT SHIPMENTLIST_STOCKROOM FOREIGN KEY (SUPPLYINGSTOCKROOM_ID) REFERENCES STOCKROOM (STOCKROOM_ID) ON DELETE SET NULL;

ALTER TABLE SHIPMENTLIST
ADD CONSTRAINT SHIPMENTLIST_STATUS FOREIGN KEY (STATUS_ID) REFERENCES STATUS (STATUS_ID);

ALTER TABLE SHIPMENTLIST
ADD CONSTRAINT SHIPMENTLIST_BUSINESSUNIT FOREIGN KEY (SHIPTOBUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);


---SHIPMENTLISTLINE table---

CREATE SEQUENCE SEQ_SHIPMENTLISTLINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE SHIPMENTLISTLINE
(
	SHIPMENTLISTLINE_ID  NUMBER NOT NULL ,
	SHIPMENTLIST_ID      NUMBER NOT NULL ,
	TRANSPORTUNITNUMBER  NUMBER NOT NULL ,
	WORKORDERLINE_ID     NUMBER NOT NULL ,
	QUANTITYSHIPPED      NUMBER DEFAULT  0  NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN SHIPMENTLISTLINE.TRANSPORTUNITNUMBER IS 'Unique generated identification for the transport item (e.g. bag or box number) ';

COMMENT ON COLUMN SHIPMENTLISTLINE.QUANTITYSHIPPED IS 'Quantity confirmed as shipped.';

CREATE UNIQUE INDEX XAK1SHIPMENTLISTLINE ON SHIPMENTLISTLINE
(SHIPMENTLIST_ID   ASC,WORKORDERLINE_ID   ASC);

CREATE INDEX XIF1SHIPMENTLISTLINE ON SHIPMENTLISTLINE
(SHIPMENTLIST_ID   ASC);

CREATE INDEX XIF2SHIPMENTLISTLINE ON SHIPMENTLISTLINE
(WORKORDERLINE_ID   ASC);

ALTER TABLE SHIPMENTLISTLINE
ADD CONSTRAINT  XPKSHIPMENTLISTLINE PRIMARY KEY (SHIPMENTLISTLINE_ID);

ALTER TABLE SHIPMENTLISTLINE
ADD CONSTRAINT SHIPMENTLISTLINE_SHIPMENTLIST FOREIGN KEY (SHIPMENTLIST_ID) REFERENCES SHIPMENTLIST (SHIPMENTLIST_ID) ON DELETE CASCADE;

ALTER TABLE SHIPMENTLISTLINE
ADD CONSTRAINT SHIPMENTLISTLINE_WOL FOREIGN KEY (WORKORDERLINE_ID) REFERENCES WORKORDERLINE (WORKORDERLINE_ID);