--Order 20015066.140

---PURCHASEREQUEST table---

ALTER TABLE PURCHASEREQUEST
	ADD (POAPPROVALTYPE NUMBER  DEFAULT 0 NOT NULL CONSTRAINT POApprovalType_Valid1_1 CHECK (POAPPROVALTYPE BETWEEN 0 AND 1));

COMMENT ON COLUMN PURCHASEREQUEST.POAPPROVALTYPE IS 'Type of PO that should be created from the current purchase request. Possible values:
0 = Standard
1 = PO Split
';


---ISSUEHISTORY table---

ALTER TABLE ISSUEHISTORY
	ADD (SHIPMENTLISTLINE_ID NUMBER NULL);

ALTER TABLE ISSUEHISTORY
	ADD (ORIGINALGRADE_ID NUMBER NULL);

ALTER TABLE ISSUEHISTORY
	ADD (ORIGINALSTOCKDEFPERITEM_ID NUMBER NULL);

ALTER TABLE ISSUEHISTORY
	ADD (STOCKROOMLOCATION_ID NUMBER NULL);

ALTER TABLE ISSUEHISTORY
	ADD (FROMNONUNIQUESTOCK VARCHAR2(1) NULL);

COMMENT ON COLUMN ISSUEHISTORY.SHIPMENTLISTLINE_ID IS 'Only filled when the module SHIPMENTLIST is in the license';

COMMENT ON COLUMN ISSUEHISTORY.FROMNONUNIQUESTOCK IS 'Indicates if item has been made unique upon issue from non-unique stock ';

CREATE INDEX XIF18ISSUEHISTORY ON ISSUEHISTORY
(SHIPMENTLISTLINE_ID   ASC);

CREATE INDEX XIF19ISSUEHISTORY ON ISSUEHISTORY
(ORIGINALGRADE_ID   ASC);

CREATE INDEX XIF21ISSUEHISTORY ON ISSUEHISTORY
(ORIGINALSTOCKDEFPERITEM_ID   ASC);

CREATE INDEX XIF22ISSUEHISTORY ON ISSUEHISTORY
(STOCKROOMLOCATION_ID   ASC);

ALTER TABLE ISSUEHISTORY
ADD CONSTRAINT ISSUEHISTORY_SHIPMENTLISTLINE FOREIGN KEY (SHIPMENTLISTLINE_ID) REFERENCES SHIPMENTLISTLINE (SHIPMENTLISTLINE_ID) ON DELETE SET NULL;

ALTER TABLE ISSUEHISTORY
ADD CONSTRAINT ISSUEHISTORY_QUALITYGRADE FOREIGN KEY (ORIGINALGRADE_ID) REFERENCES QUALITYGRADE (QUALITYGRADE_ID) ON DELETE SET NULL;

ALTER TABLE ISSUEHISTORY
ADD CONSTRAINT ISSUEHISTORY_STOCKDEFPERITEM FOREIGN KEY (ORIGINALSTOCKDEFPERITEM_ID) REFERENCES STOCKDEFINITIONPERITEM (STOCKDEFINITIONPERITEM_ID) ON DELETE SET NULL;

ALTER TABLE ISSUEHISTORY
ADD CONSTRAINT ISSUEHISTORY_STOCKROOMLOC FOREIGN KEY (STOCKROOMLOCATION_ID) REFERENCES STOCKROOMLOCATION (STOCKROOMLOCATION_ID) ON DELETE SET NULL;


---EMAILSETTING table---

ALTER TABLE EMAILSETTING
  DROP CONSTRAINT DOCUMENTTYPEEMAILSETT_VALID;

ALTER TABLE EMAILSETTING
  ADD CONSTRAINT DOCUMENTTYPEEMAILSETT_VALID
  CHECK (DOCUMENTTYPE BETWEEN 1 AND 14);

COMMENT ON COLUMN EMAILSETTING.DOCUMENTTYPE IS '1 = Invoice 
2 = Statement 
3 = Purchase order 
4 = E-service password reminder 
5 = Work order via web 
6 = Invoice job checklist 
7 = Customer 
8 = Log Message 
9 = Delivery Note / Consignment Note 
10 = Stock Count / Order 
11 = Wash List 
12 = POD (Proof of delivery) 
13 = On site soil scan list 
14 = Shipment List';

---POSPLITCRITERIONSTOCKROOM table---

CREATE SEQUENCE SEQ_POSPLITCRITERIONSTOCKROOM
START WITH 1
INCREMENT BY 1;

CREATE TABLE POSPLITCRITERIONSTOCKROOM
(
	POSPLITCRITERIONSTOCKROOM_ID NUMBER NOT NULL ,
	STOCKROOM_ID         NUMBER NOT NULL ,
	ITEMTYPE             NUMBER DEFAULT  2  NOT NULL  CONSTRAINT  ITEMTYPE_Valid1_2 CHECK (ITEMTYPE BETWEEN 1 AND 4),
	POSPLITCRITERIONTYPE_ID NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN POSPLITCRITERIONSTOCKROOM.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

For now only type 2 will be supported, but in this way it is ready for future extensions';

ALTER TABLE POSPLITCRITERIONSTOCKROOM
ADD CONSTRAINT  XPKPOSPLITCRITERIONSTOCKROOM PRIMARY KEY (POSPLITCRITERIONSTOCKROOM_ID);

CREATE UNIQUE INDEX XAK1POSPLITCRITERIONSTOCKROOM ON POSPLITCRITERIONSTOCKROOM
(STOCKROOM_ID   ASC,ITEMTYPE   ASC,POSPLITCRITERIONTYPE_ID   ASC);

CREATE INDEX XIF1POSPLITCRITERIONSTOCKROOM ON POSPLITCRITERIONSTOCKROOM
(STOCKROOM_ID   ASC);

CREATE INDEX XIF2POSPLITCRITERIONSTOCKROOM ON POSPLITCRITERIONSTOCKROOM
(POSPLITCRITERIONTYPE_ID   ASC);

ALTER TABLE POSPLITCRITERIONSTOCKROOM
ADD CONSTRAINT POSPLITCRITSTOCKROOM_STOCK FOREIGN KEY (STOCKROOM_ID) REFERENCES STOCKROOM (STOCKROOM_ID);

ALTER TABLE POSPLITCRITERIONSTOCKROOM
ADD CONSTRAINT POSPLITCRITSTOCKROOM_POSPLITCR FOREIGN KEY (POSPLITCRITERIONTYPE_ID) REFERENCES POSPLITCRITERIONTYPE (POSPLITCRITERIONTYPE_ID);