--SO 20012939.320 PDA Picking Assistant Stockroom

----------------------------------------TABLE PICKLISTPICKINGSEQUENCE-------------------------
CREATE SEQUENCE SEQ_PICKLISTPICKINGSEQUENCE
START WITH 1
INCREMENT BY 1;

CREATE TABLE PICKLISTPICKINGSEQUENCE
(
	PICKLISTPICKINGSEQUENCE_ID NUMBER NOT NULL ,
	PICKLIST_ID          NUMBER NOT NULL ,
	PICKINGLINENUMBER    NUMBER DEFAULT  0  NOT NULL ,
	PICKLISTLINE_ID      NUMBER NOT NULL ,
	PICKLISTLINEORIGIN_ID NUMBER NOT NULL ,
	PICKLISTLOCATION_ID  NUMBER NOT NULL ,
	QTYTOPICK            NUMBER DEFAULT  0  NOT NULL ,
	QTYPICKED            NUMBER NULL ,
	CONFIRMED            VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  CONFIRMED_valid CHECK (CONFIRMED IN ('Y','N')),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE PICKLISTPICKINGSEQUENCE
ADD CONSTRAINT  XPKPICKLISTPICKINGSEQUENCE PRIMARY KEY (PICKLISTPICKINGSEQUENCE_ID);

ALTER TABLE PICKLISTPICKINGSEQUENCE
ADD CONSTRAINT PICKLISTPICKSEQ_PICKLIST_ID FOREIGN KEY (PICKLIST_ID) REFERENCES PICKLIST (PICKLIST_ID) ON DELETE CASCADE;

CREATE  INDEX XIF1PICKLISTPICKINGSEQUENCE ON PICKLISTPICKINGSEQUENCE
(PICKLIST_ID   ASC);

ALTER TABLE PICKLISTPICKINGSEQUENCE
ADD CONSTRAINT PICKLISTPICKSEQ_PICKLISTLINE_I FOREIGN KEY (PICKLISTLINE_ID) REFERENCES PICKLISTLINE (PICKLISTLINE_ID) ON DELETE SET NULL;

CREATE  INDEX XIF2PICKLISTPICKINGSEQUENCE ON PICKLISTPICKINGSEQUENCE
(PICKLISTLINE_ID   ASC);

ALTER TABLE PICKLISTPICKINGSEQUENCE
ADD CONSTRAINT PICKLISTPICKSEQ_PICKLISTORIGIN FOREIGN KEY (PICKLISTLINEORIGIN_ID) REFERENCES PICKLISTLINEORIGIN (PICKLISTLINEORIGIN_ID) ON DELETE SET NULL;

CREATE  INDEX XIF3PICKLISTPICKINGSEQUENCE ON PICKLISTPICKINGSEQUENCE
(PICKLISTLINEORIGIN_ID   ASC);


ALTER TABLE PICKLISTPICKINGSEQUENCE
ADD CONSTRAINT PICKLISTPICKSEQ_PICKLISTLOC FOREIGN KEY (PICKLISTLOCATION_ID) REFERENCES PICKLISTLINELOCATION (PICKLISTLOCATION_ID) ON DELETE SET NULL;


CREATE  INDEX XIF4PICKLISTPICKINGSEQUENCE ON PICKLISTPICKINGSEQUENCE
(PICKLISTLOCATION_ID   ASC);

CREATE UNIQUE INDEX XAK1PICKLISTPICKINGSEQUENCE ON PICKLISTPICKINGSEQUENCE
(PICKLIST_ID   ASC,PICKLISTLINE_ID   ASC,PICKLISTLINEORIGIN_ID   ASC,PICKLISTLOCATION_ID   ASC);

CREATE UNIQUE INDEX XAK2PICKLISTPICKINGSEQUENCE ON PICKLISTPICKINGSEQUENCE
(PICKLIST_ID   ASC,PICKINGLINENUMBER   ASC);




