--Order 20012939.50

-------STOCKROOM--------

ALTER TABLE STOCKROOM ADD (
			CREATEPICKLISTPERAREA NUMBER DEFAULT 0 NOT NULL 
				CONSTRAINT CREATEPICKLISTPERAREA_Valid1_1 CHECK (CREATEPICKLISTPERAREA IN (0, 1))
			  );

ALTER TABLE STOCKROOM ADD (
			PICKGROUPMINBATCHQTY NUMBER DEFAULT 0 NOT NULL
			  );

ALTER TABLE STOCKROOM ADD (
			PICKGROUPMAXBATCHQTY NUMBER DEFAULT 0 NOT NULL
			  );

ALTER TABLE STOCKROOM ADD (
			LOCKEDFORPICKLISTGENERATION NUMBER DEFAULT 0 NOT NULL CONSTRAINT LOCKEDPICKLISTGENER_Valid CHECK (LOCKEDFORPICKLISTGENERATION IN (0, 1))
			  );


COMMENT ON COLUMN STOCKROOM.LOCKEDFORPICKLISTGENERATION IS
'Flag that indicates whether a unique pick list is currently being generated for this particular stockroom. Possible values:
0 = No Pick list is being generated
1 = Pick list is being generated
';

COMMENT ON COLUMN STOCKROOM.CREATEPICKLISTPERAREA IS
'Flag that indicates whether unique pick lists for this stockroom will be generated for the stockroom as a whole, or per stockroom area. Possible values:
0 = Pick list is generated per stockroom
1 = Pick list is generated per stockroom area
';

COMMENT ON COLUMN STOCKROOM.PICKGROUPMINBATCHQTY IS
'This attribute indicates how many items should be picked at least for a unique pick list. A value 0 means that there is no limit so no lower boundary is defined in that case. 
';

COMMENT ON COLUMN STOCKROOM.PICKGROUPMAXBATCHQTY IS
'This attribute indicates how many items should be picked at the max for a unique pick list. A value 0 means that there is no limit so no upper boundary is defined in that case.
';

-------WORKORDER----------

ALTER TABLE WORKORDER ADD (
			ADDEDTOITEMPICKINGPOOL NUMBER DEFAULT 0 NOT NULL
				CONSTRAINT ADDEDTOITEMPICKPOOL_Valid1 CHECK (ADDEDTOITEMPICKINGPOOL IN (0, 1))
			  );

ALTER TABLE WORKORDER ADD (
			ADDTOPICKINGPOOLUSER_ID NUMBER NULL
			  );

ALTER TABLE WORKORDER ADD (ADDTOPICKINGPOOLTIMESTAMP DATE NULL);

ALTER TABLE WORKORDER ADD (DELFROMPICKINGPOOLUSER_ID NUMBER NULL);

ALTER TABLE WORKORDER ADD (DELFROMPICKINGPOOLTIMESTAMP DATE NULL);


COMMENT ON COLUMN WORKORDER.ADDEDTOITEMPICKINGPOOL IS
'Has work order been added to pool for items to be picked. Possible values:
0 = Work order is not added to pool
1 = Work order has been added to pool
(only relevant when module ADVANCEDPICKING is in the license). 
';

COMMENT ON COLUMN WORKORDER.ADDTOPICKINGPOOLUSER_ID IS
'Link to SystemUser table (not a foreign key), optional so may be left empty.';

COMMENT ON COLUMN WORKORDER.ADDTOPICKINGPOOLTIMESTAMP
  IS 'Date/time the work order is added to the item picking pool';

COMMENT ON COLUMN WORKORDER.DELFROMPICKINGPOOLUSER_ID IS
'Link to SystemUser table (not a foreign key), optional so may be left empty.';

COMMENT ON COLUMN WORKORDER.DELFROMPICKINGPOOLTIMESTAMP
  IS 'Date/time the work order is removed from the item picking pool. ';


-----MOBILEDEVICE-----

ALTER TABLE MOBILEDEVICE ADD (PICKINGASSISTANT VARCHAR2(1) DEFAULT 'N' NOT NULL
				CONSTRAINT YN_VALIDATIONFIELD96 CHECK (PICKINGASSISTANT IN ('Y','N'))
			     );

ALTER TABLE MOBILEDEVICE ADD (PICKINGASSISTANTVERSION VARCHAR2(5) NULL);


-----PICKLIST-----
 
COMMENT ON COLUMN PICKLIST.ORIGIN
  IS '1 = Generated via Work order status control,
2 = Generated via Route status control,
3 = Generated via PDA';

COMMENT ON COLUMN PICKLIST.PICKLISTTYPE
  IS '1 = Manual Pick List
2 = Optimized Pick List
4 = Route Pick List
';

ALTER TABLE PICKLIST
  DROP CONSTRAINT PICKLISTTYPE_VALID2;
ALTER TABLE PICKLIST
  ADD CONSTRAINT PICKLISTTYPE_VALID2
  CHECK (PICKLISTTYPE IN (1, 2, 4));

ALTER TABLE PICKLIST ADD (AREACODE VARCHAR2(12) NULL);

ALTER TABLE PICKLIST ADD (MOBILEDEVICE_ID NUMBER NULL);

ALTER TABLE PICKLIST ADD (PICKINGCOMPLETETIMESTAMP DATE NULL);

ALTER TABLE PICKLIST ADD (ISSUECOMPLETETIMESTAMP DATE NULL);

ALTER TABLE PICKLIST ADD (ISSUECOMPLETEUSER_ID NUMBER NULL);

COMMENT ON COLUMN PICKLIST.AREACODE
  IS 'Area code within the stockroom. May only be filled when PickListType = 1 or 3';

COMMENT ON COLUMN PICKLIST.MOBILEDEVICE_ID
  IS 'Will be filled with MobileDevice_ID when pick list is to be processed from PDA. Will only be filled when module PICKINGASSISTANT is active.';

COMMENT ON COLUMN PICKLIST.PICKINGCOMPLETETIMESTAMP
  IS 'Filled with SYSDATE (current time) when the pick list has been completely picked';

COMMENT ON COLUMN PICKLIST.ISSUECOMPLETETIMESTAMP
  IS 'Filled with SYSDATE (current time) when the pick list has been completely issued.';

COMMENT ON COLUMN PICKLIST.ISSUECOMPLETEUSER_ID
  IS 'Filled with SystemUser_ID when the issue based on the pick list is completed. Used for referencing only.';

CREATE INDEX XIF8PICKLIST ON PICKLIST (MOBILEDEVICE_ID);
 
ALTER TABLE PICKLIST
  ADD CONSTRAINT PICKLIST_MOBILEDEVICE_ID FOREIGN KEY (MOBILEDEVICE_ID)
  REFERENCES MOBILEDEVICE (MOBILEDEVICE_ID) ON DELETE SET NULL;


----PICKLISTLINE----
 
ALTER TABLE PICKLISTLINE ADD (QTYCANCELLED NUMBER DEFAULT 0 NOT NULL);

ALTER TABLE PICKLISTLINE ADD (LINENUMBER NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN PICKLISTLINE.QTYCANCELLED
  IS 'Quantity that has been cancelled for pick list line, due to completely cancelling the pick list.';

COMMENT ON COLUMN PICKLISTLINE.LINENUMBER
  IS 'Unique line number of the pick list line within the pick list it belongs to.';

create or replace procedure renumber_picklist_lines
  is
    cursor get_picklists is
      select pll.picklist_id
        from picklistline pll
       group by pll.picklist_id;

    cursor get_picklist_lines(lv_picklist_id number) is
      select pll.picklistline_id
        from picklistline pll
       where pll.picklist_id = lv_picklist_id
       order by pll.picklistline_id;

    lv_linenumber number;
    begin
      -- Outer loop to select picklists
      for pl_rec in get_picklists loop
        -- Initialize line number
        lv_linenumber := 0;
        -- Inner loop to select lines per pick list
        for pll_rec in get_picklist_lines(pl_rec.picklist_id) loop
          -- Increase line number
          lv_linenumber := lv_linenumber + 1;
          -- Update line number
          update picklistline pll
          set pll.linenumber = lv_linenumber
          where pll.picklistline_id = pll_rec.picklistline_id;
          commit;
        end loop; -- inner loop
      end loop; -- outer loop
end renumber_picklist_lines;
/
execute renumber_picklist_lines;

drop procedure renumber_picklist_lines;


CREATE UNIQUE INDEX XAK2PICKLISTLINE ON PICKLISTLINE (PICKLIST_ID, LINENUMBER);


----PICKLISTLINEAS----

ALTER TABLE PICKLISTLINEAS ADD (QTYCANCELLED NUMBER DEFAULT 0 NOT NULL);

ALTER TABLE PICKLISTLINEAS ADD (LINENUMBER NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN PICKLISTLINEAS.QTYCANCELLED
  IS 'Quantity that has been cancelled for pick list line, due to completely cancelling the pick list.';

COMMENT ON COLUMN PICKLISTLINEAS.LINENUMBER
  IS 'Unique line number of the pick list line within the pick list it belongs to.';

create or replace procedure renumber_picklist_linesas
  is
    cursor get_picklists is
      select plls.picklist_id
        from picklistlineas plls
       group by plls.picklist_id;

    cursor get_picklist_linesas(lv_picklist_id number) is
      select plls.picklistlineas_id
        from picklistlineas plls
       where plls.picklist_id = lv_picklist_id
       order by plls.picklistlineas_id;

    lv_linenumber number;
    begin
      -- Outer loop to select picklists
      for pl_rec in get_picklists loop
        -- Initialize line number
        lv_linenumber := 0;
        -- Inner loop to select lines per pick list
        for pll_rec in get_picklist_linesas(pl_rec.picklist_id) loop
          -- Increase line number
          lv_linenumber := lv_linenumber + 1;
          -- Update line number
          update picklistlineas plls
          set plls.linenumber = lv_linenumber
          where plls.picklistlineas_id = pll_rec.picklistlineas_id;
        end loop; -- inner loop
      end loop; -- outer loop
end renumber_picklist_linesas;
/

execute renumber_picklist_linesas;

drop procedure renumber_picklist_linesas;

CREATE UNIQUE INDEX XAK2PICKLISTLINEAS ON PICKLISTLINEAS (PICKLIST_ID, LINENUMBER);


----STATUS----

update status s set s.factorydescription = 'Picking in Progress'
where s.statustype = 'PL' and s.code = 20;
commit;

update status s set s.factorydescription = 'Picking Confirmed'
where s.statustype = 'PL' and s.code = 30;
commit;

insert into status 
select seq_status.nextval,
       'PL',
       50,
       '',
       getminsystemuser(),
       sysdate,
       'Issuing in Progress'
from dual;
commit;

insert into status 
select seq_status.nextval,
       'PL',
       60,
       '',
       getminsystemuser(),
       sysdate,
       'Issue Completed'
from dual;
commit;

----PICKLISTLINEORIGIN----

ALTER TABLE PICKLISTLINEORIGIN ADD (QTYCANCELLEDPL NUMBER DEFAULT 0 NOT NULL);

ALTER TABLE PICKLISTLINEORIGIN ADD (QTYISSUED NUMBER DEFAULT 0 NOT NULL);
 
COMMENT ON COLUMN PICKLISTLINEORIGIN.QTYCANCELLEDPL
  IS 'The number of items to pick for this order line that has been cancelled (due to cancelling the pick list).';

COMMENT ON COLUMN PICKLISTLINEORIGIN.QTYISSUED
  IS 'The number of items that has been issued for this order line via the pick list line.';


----PICKLISTLINEORIGINAS----

ALTER TABLE PICKLISTLINEORIGINAS ADD (QTYCANCELLEDPL NUMBER DEFAULT 0 NOT NULL);

ALTER TABLE PICKLISTLINEORIGINAS ADD (QTYISSUED NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN PICKLISTLINEORIGINAS.QTYCANCELLEDPL
  IS 'The number of items to pick for this order line that has been cancelled (due to cancelling the pick list).';

COMMENT ON COLUMN PICKLISTLINEORIGINAS.QTYISSUED
  IS 'The number of items that has been issued for this order line via the pick list line.';
