create sequence seq_picklist start with 1 increment by 1;
   
      /*
      ACTION is CREATE Table PICKLIST
      */

CREATE TABLE PICKLIST (
       PICKLIST_ID          NUMBER NOT NULL,
       PICKLISTNUMBER       NUMBER NOT NULL,
       STATUS_ID            NUMBER NOT NULL,
       BUSINESSUNIT_ID      NUMBER NOT NULL,
       PROCESSINGSTOCKROOM_ID NUMBER NULL,
       ROUTE_ID             NUMBER NULL,
       ROUTECONTROL_ID      NUMBER NULL,
       ORIGIN               NUMBER NOT NULL,
       PICKLISTTYPE         NUMBER DEFAULT 1 NOT NULL
                                   CONSTRAINT Picklisttype_valid2
                                          CHECK (PICKLISTTYPE IN (1, 2, 3, 4)),
       PICKLISTPRINTED      VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew97
                                          CHECK (PICKLISTPRINTED IN ('Y', 'N')),
       COMPLETIONDATE       DATE NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       PICK_USER_ID         NUMBER NULL,
       CREATIONUSER_ID      NUMBER NOT NULL,
       CREATIONTIMESTAMP    DATE NOT NULL,
       INPROCESSATWORKSTATION_ID NUMBER NULL,
       INPROCESSSTARTED     DATE NULL
);

COMMENT ON COLUMN PICKLIST.STATUS_ID IS 'Statustype=PL
10 = Proposal
20 = In Progress
30 = Confirmed
40 = Cancelled';
COMMENT ON COLUMN PICKLIST.ORIGIN IS '1 = Generated via Work order status control
2 = Generated via Route status control';
COMMENT ON COLUMN PICKLIST.PICKLISTTYPE IS '1 = Garment Picklist
2 = Stockroom Item Picklist
3 = Stock Delivery Picklist
4 = Route Picklist';
CREATE UNIQUE INDEX XAK1PICKLIST ON PICKLIST
(
       PICKLISTNUMBER                 ,
       BUSINESSUNIT_ID                
);

CREATE INDEX XIF1PICKLIST ON PICKLIST
(
       STATUS_ID                      ASC
);

CREATE INDEX XIF2PICKLIST ON PICKLIST
(
       BUSINESSUNIT_ID                ASC
);

CREATE INDEX XIF3PICKLIST ON PICKLIST
(
       PROCESSINGSTOCKROOM_ID         ASC
);

CREATE INDEX XIF4PICKLIST ON PICKLIST
(
       ROUTE_ID                       ASC
);

CREATE INDEX XIF5PICKLIST ON PICKLIST
(
       ROUTECONTROL_ID                ASC
);

CREATE INDEX XIF6PICKLIST ON PICKLIST
(
       PICK_USER_ID                   ASC
);

CREATE INDEX XIF7PICKLIST ON PICKLIST
(
       INPROCESSATWORKSTATION_ID      ASC
);


ALTER TABLE PICKLIST
       ADD  ( CONSTRAINT XPKPICKLIST PRIMARY KEY (PICKLIST_ID) ) ;

 

create sequence seq_picklistline start with 1 increment by 1;

     
      /*
      ACTION is CREATE Table PICKLISTLINE
      */

CREATE TABLE PICKLISTLINE (
       PICKLISTLINE_ID      NUMBER NOT NULL,
       PICKLIST_ID          NUMBER NOT NULL,
       STOCKRESERVATIONPERGRADE_ID NUMBER NOT NULL,
       STOCKTYPE            NUMBER DEFAULT 1 NOT NULL
                                   CONSTRAINT StockType_valid
                                          CHECK (STOCKTYPE IN (1, 2, 3)),
       QTYTOPICK            NUMBER DEFAULT 0 NOT NULL,
       QTYPICKED            NUMBER DEFAULT 0 NOT NULL,
       CONFIRMEDCOMPLETELY  VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew98
                                          CHECK (CONFIRMEDCOMPLETELY IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN PICKLISTLINE.STOCKTYPE IS '1 = Non Unique
2 = Unique
3 = Unique with Modifications';
CREATE UNIQUE INDEX XAK1PICKLISTLINE ON PICKLISTLINE
(
       PICKLIST_ID                    ,
       STOCKRESERVATIONPERGRADE_ID    ,
       STOCKTYPE                      
);

CREATE INDEX XIF1PICKLISTLINE ON PICKLISTLINE
(
       PICKLIST_ID                    ASC
);

CREATE INDEX XIF2PICKLISTLINE ON PICKLISTLINE
(
       STOCKRESERVATIONPERGRADE_ID    ASC
);


ALTER TABLE PICKLISTLINE
       ADD  ( CONSTRAINT XPKPICKLISTLINE PRIMARY KEY (PICKLISTLINE_ID) ) ;

  
create sequence seq_picklistlinelocation start with 1 increment by 1;  
  
      /*
      ACTION is CREATE Table PICKLISTLINELOCATION
      */

CREATE TABLE PICKLISTLINELOCATION (
       PICKLISTLOCATION_ID  NUMBER NOT NULL,
       PICKLISTLINE_ID      NUMBER NOT NULL,
       STOCKROOMLOCATION_ID NUMBER NOT NULL,
       QTYTOPICK            NUMBER DEFAULT 0 NOT NULL,
       QTYPICKED            NUMBER DEFAULT 0 NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1PICKLISTLINELOCATION ON PICKLISTLINELOCATION
(
       PICKLISTLINE_ID                ,
       STOCKROOMLOCATION_ID           
);

CREATE INDEX XIF1PICKLISTLINELOCATION ON PICKLISTLINELOCATION
(
       PICKLISTLINE_ID                ASC
);

CREATE INDEX XIF2PICKLISTLINELOCATION ON PICKLISTLINELOCATION
(
       STOCKROOMLOCATION_ID           ASC
);


ALTER TABLE PICKLISTLINELOCATION
       ADD  ( CONSTRAINT XPKPICKLISTLINELOCATION PRIMARY KEY (
              PICKLISTLOCATION_ID) ) ;

create sequence seq_picklistlineorigin start with 1 increment by 1; 
     
      /*
      ACTION is CREATE Table PICKLISTLINEORIGIN
      */

CREATE TABLE PICKLISTLINEORIGIN (
       PICKLISTLINEORIGIN_ID NUMBER NOT NULL,
       PICKLISTLINE_ID      NUMBER NOT NULL,
       BASELINETYPE         NUMBER DEFAULT 1 NOT NULL
                                   CONSTRAINT Baselinetype_valid
                                          CHECK (BASELINETYPE IN (1, 2, 3, 4, 5, 6, 8, 9, 90)),
       BASELINETABLE_LINK_ID NUMBER NOT NULL,
       BASELINETABLE        VARCHAR2(30) NOT NULL,
       QTYTOPICK            NUMBER DEFAULT 0 NOT NULL,
       QTYPICKED            NUMBER DEFAULT 0 NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN PICKLISTLINEORIGIN.BASELINETYPE IS '1 = Work Order
2 = Work Order For Pool
3 = Web Work Order
4 = Route Assistant
5 = Conversion
6 = Sales Note
8 = Stock Delivery Order (Internal PO)
9 = Direct Stock Delivery Order
90 = PackingNote';
CREATE UNIQUE INDEX XAK1PICKLISTLINEORIGIN ON PICKLISTLINEORIGIN
(
       PICKLISTLINE_ID                ,
       BASELINETYPE                   ,
       BASELINETABLE_LINK_ID          
);

CREATE INDEX XIF1PICKLISTLINEORIGIN ON PICKLISTLINEORIGIN
(
       PICKLISTLINE_ID                ASC
);


ALTER TABLE PICKLISTLINEORIGIN
       ADD  ( CONSTRAINT XPKPICKLISTLINEORIGIN PRIMARY KEY (
              PICKLISTLINEORIGIN_ID) ) ;


ALTER TABLE PICKLIST
       ADD  ( CONSTRAINT PICKLIST_INPROCESSATWORKSTATIO
              FOREIGN KEY (INPROCESSATWORKSTATION_ID)
                             REFERENCES WORKSTATION ) ;


ALTER TABLE PICKLIST
       ADD  ( CONSTRAINT PICKLIST_PICK_USER_ID
              FOREIGN KEY (PICK_USER_ID)
                             REFERENCES SYSTEMUSER ) ;


ALTER TABLE PICKLIST
       ADD  ( CONSTRAINT PICKLIST_ROUTECONTROL_ID
              FOREIGN KEY (ROUTECONTROL_ID)
                             REFERENCES ROUTECONTROL ) ;


ALTER TABLE PICKLIST
       ADD  ( CONSTRAINT PICKLIST_ROUTE_ID
              FOREIGN KEY (ROUTE_ID)
                             REFERENCES ROUTE ) ;


ALTER TABLE PICKLIST
       ADD  ( CONSTRAINT PICKLIST_PROCESSINGSTOCKROOM_I
              FOREIGN KEY (PROCESSINGSTOCKROOM_ID)
                             REFERENCES STOCKROOM ) ;


ALTER TABLE PICKLIST
       ADD  ( CONSTRAINT PICKLIST_BUSINESSUNIT_ID
              FOREIGN KEY (BUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT ) ;


ALTER TABLE PICKLIST
       ADD  ( CONSTRAINT PICKLIST_STATUS_ID
              FOREIGN KEY (STATUS_ID)
                             REFERENCES STATUS ) ;


ALTER TABLE PICKLISTLINE
       ADD  ( CONSTRAINT PICKLISTLINE_STOCKRESERVATIONP
              FOREIGN KEY (STOCKRESERVATIONPERGRADE_ID)
                             REFERENCES STOCKRESERVATIONPERGRADE ) ;


ALTER TABLE PICKLISTLINE
       ADD  ( CONSTRAINT PICKLISTLINE_PICKLIST_ID
              FOREIGN KEY (PICKLIST_ID)
                             REFERENCES PICKLIST
                             ON DELETE CASCADE ) ;


ALTER TABLE PICKLISTLINELOCATION
       ADD  ( CONSTRAINT PICKLISTLINELOCATION_STOCKROOM
              FOREIGN KEY (STOCKROOMLOCATION_ID)
                             REFERENCES STOCKROOMLOCATION ) ;


ALTER TABLE PICKLISTLINELOCATION
       ADD  ( CONSTRAINT PICKLISTLINELOCATION_PICKLISTL
              FOREIGN KEY (PICKLISTLINE_ID)
                             REFERENCES PICKLISTLINE ) ;


ALTER TABLE PICKLISTLINEORIGIN
       ADD  ( CONSTRAINT PICKLISTLINEORIGIN_PICKLISTLIN
              FOREIGN KEY (PICKLISTLINE_ID)
                             REFERENCES PICKLISTLINE ) ;

INSERT INTO STATUS(STATUS_ID,STATUSTYPE,CODE,LEGACYSTATUSCODE,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STATUS.NEXTVAL, 'UI', '25', '', GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'PICKED');

INSERT INTO STATUS(STATUS_ID,STATUSTYPE,CODE,LEGACYSTATUSCODE,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STATUS.NEXTVAL, 'UI', '35', '', GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'TRANSFER STOCK');


INSERT INTO STATUS_DESC
          (STATUS_ID, LANGUAGE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
          SELECT STATUS_ID,
                 GETMINLANGUAGE(),
                 FACTORYDESCRIPTION,
                 SYSTEMUSER_ID,
                 TIMESTAMP
            FROM STATUS
            WHERE STATUS.STATUSTYPE = 'UI' AND STATUS.CODE IN ('25','35');

INSERT INTO STAY(STAY_ID,STAYTYPE,STAYNR,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STAY.NEXTVAL, 1,11,GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'STOCKROOM/PICKED');

INSERT INTO STAY(STAY_ID,STAYTYPE,STAYNR,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STAY.NEXTVAL, 1,26,GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'OUTSCAN/TRANSFER STOCK');
   INSERT INTO STAY_DESC
          (STAY_ID, LANGUAGE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
          SELECT STAY_ID,
                 GETMINLANGUAGE(),
                 FACTORYDESCRIPTION,
                 SYSTEMUSER_ID,
                 TIMESTAMP
            FROM STAY WHERE STAY.STAYTYPE = 1 AND STAY.STAYNR IN( 11,26);







