--Order 20012939.400

------WORKORDERITEMDISTRIBUTION TABLE-------


CREATE TABLE WORKORDERITEMDISTRIBUTION
(
	WORKORDER_ID         NUMBER NOT NULL ,
	PICKLIST_ID          NUMBER NOT NULL ,
	WORKORDERLINE_ID     NUMBER NOT NULL ,
	STOCKRESERVATIONPERGRADE_ID NUMBER NOT NULL ,
	STOCKTYPE            NUMBER DEFAULT  1  NOT NULL  CONSTRAINT  STOCKTYPE_valid2 CHECK (STOCKTYPE IN (1, 2, 3)),
	SEQUENCENUMBER       NUMBER NOT NULL ,
	STOCKROOMLOCATION_ID NUMBER NOT NULL ,
	PICKINGSEQUENCE      NUMBER DEFAULT  0  NOT NULL ,
	QTY_TO_PICK          NUMBER DEFAULT  0  NOT NULL ,
	QTY_PICKED           NUMBER DEFAULT  0  NOT NULL 
);

COMMENT ON COLUMN WORKORDERITEMDISTRIBUTION.STOCKTYPE IS 'Stocktype of the items to be picked for the work order line. Possible values :
1 = Not Unique
2 = Unique
3 = Unique With Modifications
';

COMMENT ON COLUMN WORKORDERITEMDISTRIBUTION.PICKINGSEQUENCE IS 'PickingSequence that is defined for the current stockroom location that will be used to fullfill the demand for the work order line when using the current pick list';

COMMENT ON COLUMN WORKORDERITEMDISTRIBUTION.QTY_TO_PICK IS 'Number of items to pick from the current location for the current work order line (reservation) when using the current pick list';

COMMENT ON COLUMN WORKORDERITEMDISTRIBUTION.QTY_PICKED IS 'Number of items that has been confimed as  picked from the current location for the current work order line (reservation) when using the current pick list';

ALTER TABLE WORKORDERITEMDISTRIBUTION
ADD CONSTRAINT  XPKWORKORDERITEMDISTRIBUTION PRIMARY KEY (WORKORDER_ID,PICKLIST_ID,WORKORDERLINE_ID,STOCKRESERVATIONPERGRADE_ID,STOCKTYPE,SEQUENCENUMBER,STOCKROOMLOCATION_ID);

ALTER TABLE WORKORDERITEMDISTRIBUTION
ADD CONSTRAINT WORKORDERITEMDISTRIB_WO_ID FOREIGN KEY (WORKORDER_ID) REFERENCES WORKORDER (WORKORDER_ID) ON DELETE CASCADE;

CREATE  INDEX XIF1WORKORDERITEMDISTRIBUTION ON WORKORDERITEMDISTRIBUTION
(WORKORDER_ID   ASC);

ALTER TABLE WORKORDERITEMDISTRIBUTION
ADD CONSTRAINT WORKORDERITEMDISTRIB_PL_ID FOREIGN KEY (PICKLIST_ID) REFERENCES PICKLIST (PICKLIST_ID) ON DELETE CASCADE;

CREATE  INDEX XIF2WORKORDERITEMDISTRIBUTION ON WORKORDERITEMDISTRIBUTION
(PICKLIST_ID   ASC);

ALTER TABLE WORKORDERITEMDISTRIBUTION
ADD CONSTRAINT WORKORDERITEMDISTRIB_WOL_ID FOREIGN KEY (WORKORDERLINE_ID) REFERENCES WORKORDERLINE (WORKORDERLINE_ID) ON DELETE CASCADE;

CREATE  INDEX XIF3WORKORDERITEMDISTRIBUTION ON WORKORDERITEMDISTRIBUTION
(WORKORDERLINE_ID   ASC);

ALTER TABLE WORKORDERITEMDISTRIBUTION
ADD CONSTRAINT WORKORDERITEMDISTRIB_SRG_ID FOREIGN KEY (STOCKRESERVATIONPERGRADE_ID) REFERENCES STOCKRESERVATIONPERGRADE (STOCKRESERVATIONPERGRADE_ID) ON DELETE CASCADE;

CREATE  INDEX XIF4WORKORDERITEMDISTRIBUTION ON WORKORDERITEMDISTRIBUTION
(STOCKRESERVATIONPERGRADE_ID   ASC);


ALTER TABLE WORKORDERITEMDISTRIBUTION
ADD CONSTRAINT WORKORDERITEMDISTRIB_SRL_ID FOREIGN KEY (STOCKROOMLOCATION_ID) REFERENCES STOCKROOMLOCATION (STOCKROOMLOCATION_ID) ON DELETE CASCADE;

CREATE  INDEX XIF5WORKORDERITEMDISTRIBUTION ON WORKORDERITEMDISTRIBUTION
(STOCKROOMLOCATION_ID   ASC);



------WORKORDERPICKSEQUENCE TABLE-------

CREATE TABLE WORKORDERPICKSEQUENCE
(
	WORKORDER_ID         NUMBER NOT NULL ,
	PICKLIST_ID          NUMBER NOT NULL ,
	PICKINGSEQUENCE      NUMBER DEFAULT  0  NOT NULL 
);

COMMENT ON COLUMN WORKORDERPICKSEQUENCE.PICKINGSEQUENCE IS 'PickingSequence that is defined for one or more stockroom locations that may be used to fullfill the demand for the workorder when using the current pick list.';

ALTER TABLE WORKORDERPICKSEQUENCE
ADD CONSTRAINT  XPKWORKORDERPICKSEQUENCE PRIMARY KEY (WORKORDER_ID,PICKLIST_ID,PICKINGSEQUENCE);


ALTER TABLE WORKORDERPICKSEQUENCE
ADD CONSTRAINT WORKORDERPICKSEQUENCE_WO_ID FOREIGN KEY (WORKORDER_ID) REFERENCES WORKORDER (WORKORDER_ID) ON DELETE CASCADE;

CREATE  INDEX XIF1WORKORDERPICKSEQUENCE ON WORKORDERPICKSEQUENCE
(WORKORDER_ID   ASC);


ALTER TABLE WORKORDERPICKSEQUENCE
ADD CONSTRAINT WORKORDERPICKSEQUENCE_PL_ID FOREIGN KEY (PICKLIST_ID) REFERENCES PICKLIST (PICKLIST_ID) ON DELETE CASCADE;

CREATE  INDEX XIF2WORKORDERPICKSEQUENCE ON WORKORDERPICKSEQUENCE
(PICKLIST_ID   ASC);
















