--Order 20013071.110

---STOCKROOM table---

ALTER TABLE STOCKROOM
	ADD (LOCKEDFORPURCHREQGENERATION_ID NUMBER NULL);

COMMENT ON COLUMN STOCKROOM.LOCKEDFORPURCHREQGENERATION_ID IS 'Attribute that indicates whether purchase request lines are currently being generated for this particular stockroom by this specific workstation. Possible values:
Null = No Purchase Request Lines are being generated for this stockroom
Not null = Purchase Request Lines are being generated for this stockroom by this workstation
';

CREATE INDEX XIF4STOCKROOM ON STOCKROOM
(LOCKEDFORPURCHREQGENERATION_ID   ASC);

ALTER TABLE STOCKROOM
ADD CONSTRAINT STOCKROOM_WORKSTATION FOREIGN KEY (LOCKEDFORPURCHREQGENERATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;


---STOCKDEFINITIONPERITEM table---

ALTER TABLE STOCKDEFINITIONPERITEM
	ADD (PRODUCTCONSUMPTIONTYPE NUMBER  DEFAULT 3 NOT NULL);

ALTER TABLE STOCKDEFINITIONPERITEM
	ADD (PRODUCTCONSUMPTIONRANKING NUMBER  DEFAULT 1 NOT NULL);

COMMENT ON COLUMN STOCKDEFINITIONPERITEM.PRODUCTCONSUMPTIONTYPE IS 'Indication whether the consumption ranking for the current product/size combination for a given stockroom will be set manually or determined automatically via a scheduled process. Possibe values:
1 = Slow mover
2 = Fast mover
3 = To be determined
';

COMMENT ON COLUMN STOCKDEFINITIONPERITEM.PRODUCTCONSUMPTIONRANKING IS 'Indication of the consumption ranking for the current product/size combination for a given stockroom. Possible values:
1 = Slow mover
2 = Fast mover
';


---WORKORDERLINEPURCHASEORDERLINE table---

CREATE SEQUENCE SEQ_WORKORDERLINEPURCHORDLINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE WORKORDERLINEPURCHORDLINE
(
  WORKORDERLINEPURCHORDLINE_ID NUMBER NOT NULL ,
  WORKORDERLINE_ID     NUMBER NOT NULL ,
  PURCHASEORDERLINE_ID NUMBER NOT NULL ,
  QTYORDEREDSTOCK      NUMBER DEFAULT  0  NOT NULL ,
  QTYORDEREDDIRECTISSUE NUMBER DEFAULT  0  NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
);

CREATE UNIQUE INDEX XAK1WORKORDERLINEPOL ON WORKORDERLINEPURCHORDLINE
(WORKORDERLINE_ID   ASC,PURCHASEORDERLINE_ID   ASC);

CREATE INDEX XIF1WORKORDERLINEPOL ON WORKORDERLINEPURCHORDLINE
(WORKORDERLINE_ID   ASC);

CREATE INDEX XIF2WORKORDERLINEPOL ON WORKORDERLINEPURCHORDLINE
(PURCHASEORDERLINE_ID   ASC);

ALTER TABLE WORKORDERLINEPURCHORDLINE
ADD CONSTRAINT  XPKWORKORDERLINEPURCHORDLINE PRIMARY KEY (WORKORDERLINEPURCHORDLINE_ID);

ALTER TABLE WORKORDERLINEPURCHORDLINE
ADD CONSTRAINT WOLPOL_WOL FOREIGN KEY (WORKORDERLINE_ID) REFERENCES WORKORDERLINE 
(WORKORDERLINE_ID) ON DELETE CASCADE;

ALTER TABLE WORKORDERLINEPURCHORDLINE
ADD CONSTRAINT WOLPOL_POL FOREIGN KEY (PURCHASEORDERLINE_ID) REFERENCES PURCHASEORDERLINE 
(PURCHASEORDERLINE_ID) ON DELETE CASCADE;


---WORKORDERLINEEMBPURCHREQ table---

CREATE SEQUENCE SEQ_WORKORDERLINEEMBPURCHREQ
START WITH 1
INCREMENT BY 1;

CREATE TABLE WORKORDERLINEEMBPURCHREQ
(
	WORKORDERLINEEMBPURCHREQ_ID NUMBER NOT NULL ,
	WORKORDERLINEEMBLEM_ID NUMBER NOT NULL ,
	PURCHASEREQUEST_ID   NUMBER NOT NULL ,
	REQUESTEDSTOCK       NUMBER DEFAULT  0  NOT NULL ,
	REQUESTEDDIRECTISSUE NUMBER DEFAULT  0  NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

CREATE UNIQUE INDEX XAK1WORKORDERLINEEMBPURCHRE ON WORKORDERLINEEMBPURCHREQ
(WORKORDERLINEEMBLEM_ID   ASC,PURCHASEREQUEST_ID   ASC);

CREATE INDEX XIF1WORKORDERLINEEMBPURCHRE ON WORKORDERLINEEMBPURCHREQ
(WORKORDERLINEEMBLEM_ID   ASC);

CREATE INDEX XIF2WORKORDERLINEEMBPURCHRE ON WORKORDERLINEEMBPURCHREQ
(PURCHASEREQUEST_ID   ASC);

ALTER TABLE WORKORDERLINEEMBPURCHREQ
ADD CONSTRAINT  XPKWORKORDERLINEEMBPURCHREQ PRIMARY KEY (WORKORDERLINEEMBPURCHREQ_ID);

ALTER TABLE WORKORDERLINEEMBPURCHREQ
ADD CONSTRAINT  XAK1WORKORDERLINEEMBPURCHRE UNIQUE (WORKORDERLINEEMBLEM_ID,PURCHASEREQUEST_ID);

ALTER TABLE WORKORDERLINEEMBPURCHREQ
ADD CONSTRAINT WOLEMBLEM_WOLEMBPR FOREIGN KEY (WORKORDERLINEEMBLEM_ID) REFERENCES WORKORDERLINEEMBLEM (WORKORDERLINEEMBLEM_ID) ON DELETE CASCADE;

ALTER TABLE WORKORDERLINEEMBPURCHREQ
ADD CONSTRAINT PURCHASEREQ_WOLEMBLEMPR FOREIGN KEY (PURCHASEREQUEST_ID) REFERENCES PURCHASEREQUEST (PURCHASEREQUEST_ID) ON DELETE CASCADE;

---WORKORDERLINEPURCHREQ table---

CREATE SEQUENCE SEQ_WORKORDERLINEPURCHREQ
START WITH 1
INCREMENT BY 1;

CREATE TABLE WORKORDERLINEPURCHREQ
(
	WORKORDERLINEPURCHREQ_ID NUMBER NOT NULL ,
	WORKORDERLINE_ID     NUMBER NOT NULL ,
	PURCHASEREQUEST_ID   NUMBER NOT NULL ,
	REQUESTEDSTOCK       NUMBER DEFAULT 0 NOT NULL ,
	REQUESTEDDIRECTISSUE NUMBER DEFAULT 0 NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

CREATE INDEX XIF1WORKORDERLINEPURCHREQ ON WORKORDERLINEPURCHREQ
(WORKORDERLINE_ID   ASC);

CREATE INDEX XIF2WORKORDERLINEPURCHREQ ON WORKORDERLINEPURCHREQ
(PURCHASEREQUEST_ID   ASC);

ALTER TABLE WORKORDERLINEPURCHREQ
ADD CONSTRAINT  XPKWORKORDERLINEPURCHREQ PRIMARY KEY (WORKORDERLINEPURCHREQ_ID);

ALTER TABLE WORKORDERLINEPURCHREQ
ADD CONSTRAINT WOLPR_WOL FOREIGN KEY (WORKORDERLINE_ID) REFERENCES WORKORDERLINE (WORKORDERLINE_ID) ON DELETE CASCADE;

ALTER TABLE WORKORDERLINEPURCHREQ
ADD CONSTRAINT WOLPR_PR FOREIGN KEY (PURCHASEREQUEST_ID) REFERENCES PURCHASEREQUEST (PURCHASEREQUEST_ID) ON DELETE CASCADE;

---WORKORDERLINEEMBLEMPOLINE table---

CREATE SEQUENCE SEQ_WORKORDERLINEEMBLEMPOLINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE WORKORDERLINEEMBLEMPOLINE
(
	WORKORDERLINEEMBLEMPOLINE_ID NUMBER NOT NULL ,
	WORKORDERLINEEMBLEM_ID NUMBER NOT NULL ,
	PURCHASEORDERLINE_ID NUMBER NOT NULL ,
	QTYORDEREDSTOCK      NUMBER DEFAULT  0  NOT NULL ,
	QTYORDEREDDIRECTISSUE NUMBER DEFAULT  0  NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

CREATE UNIQUE INDEX XAK1WORKORDERLINEEMBLEMPOLINE ON WORKORDERLINEEMBLEMPOLINE
(WORKORDERLINEEMBLEM_ID   ASC,PURCHASEORDERLINE_ID   ASC);

CREATE INDEX XIF1WORKORDERLINEEMBLEMPOLINE ON WORKORDERLINEEMBLEMPOLINE
(WORKORDERLINEEMBLEM_ID   ASC);

CREATE INDEX XIF2WORKORDERLINEEMBLEMPOLINE ON WORKORDERLINEEMBLEMPOLINE
(PURCHASEORDERLINE_ID   ASC);

ALTER TABLE WORKORDERLINEEMBLEMPOLINE
ADD CONSTRAINT  XPKWORKORDERLINEEMBLEMPOLINE PRIMARY KEY (WORKORDERLINEEMBLEMPOLINE_ID);

ALTER TABLE WORKORDERLINEEMBLEMPOLINE
ADD CONSTRAINT WOLEMBLPOLINE_WOLEMBL FOREIGN KEY (WORKORDERLINEEMBLEM_ID) REFERENCES WORKORDERLINEEMBLEM (WORKORDERLINEEMBLEM_ID) ON DELETE CASCADE;

ALTER TABLE WORKORDERLINEEMBLEMPOLINE
ADD CONSTRAINT WOLEMBLPOLINE_POLINE FOREIGN KEY (PURCHASEORDERLINE_ID) REFERENCES PURCHASEORDERLINE (PURCHASEORDERLINE_ID) ON DELETE CASCADE;

---PURCHASEREQUESTALLOCATION table---

CREATE SEQUENCE SEQ_PURCHASEREQUESTALLOCATION
START WITH 1
INCREMENT BY 1;

CREATE TABLE PURCHASEREQUESTALLOCATION
(
	PURCHASEREQUESTALLOCATION_ID NUMBER NOT NULL ,
	PURCHASEREQUEST_ID   NUMBER NOT NULL ,
	STOCKRESERVATIONPERGRADE_ID NUMBER NOT NULL ,
	QTYALLOCATED         NUMBER NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN PURCHASEREQUESTALLOCATION.QTYALLOCATED IS 'The number of items that have been softly allocated for this purchase request line';

CREATE UNIQUE INDEX XAK1PURCHASEREQUESTALLOCATION ON PURCHASEREQUESTALLOCATION
(PURCHASEREQUEST_ID   ASC,STOCKRESERVATIONPERGRADE_ID   ASC);

CREATE INDEX XIF1PURCHASEREQUESTALLOCATION ON PURCHASEREQUESTALLOCATION
(PURCHASEREQUEST_ID   ASC);

CREATE INDEX XIF2PURCHASEREQUESTALLOCATION ON PURCHASEREQUESTALLOCATION
(STOCKRESERVATIONPERGRADE_ID   ASC);

ALTER TABLE PURCHASEREQUESTALLOCATION
ADD CONSTRAINT  XPKPURCHASEREQUESTALLOCATION PRIMARY KEY (PURCHASEREQUESTALLOCATION_ID);

ALTER TABLE PURCHASEREQUESTALLOCATION
ADD CONSTRAINT  XAK1PURCHASEREQUESTALLOCATION UNIQUE (PURCHASEREQUEST_ID,STOCKRESERVATIONPERGRADE_ID);

ALTER TABLE PURCHASEREQUESTALLOCATION
ADD CONSTRAINT PURCHREQALLOCATION_PURCHREQ FOREIGN KEY (PURCHASEREQUEST_ID) REFERENCES PURCHASEREQUEST (PURCHASEREQUEST_ID) ON DELETE CASCADE;

ALTER TABLE PURCHASEREQUESTALLOCATION
ADD CONSTRAINT PURCHREQALLOCATION_STOCKRESPER FOREIGN KEY (STOCKRESERVATIONPERGRADE_ID) REFERENCES STOCKRESERVATIONPERGRADE (STOCKRESERVATIONPERGRADE_ID) ON DELETE CASCADE;

---insert into process---

insert into process(process_id,description,active) values ('120','Determine Slow/Fast Movers per Stockroom','Y');

commit;

