025631 Clear down tool(s), phase 1

KCSL have introduce a clear down tool to roll up old stock movements - where possible - to reduce table size and query times.

 

Phase 1 of the cleardown will allow "rolling up" / "clearing down" of old stock movements. This will only apply to ‘assigned’, ‘issued’, and ‘adjusted’ movements, as they form the bulk of stock movements.

The idea is that if you (e.g.) roll up movements for a given month then all assigned movements for a particular stock item that month (with the same site, warehouse location, batch) are removed and replaced with a single movement that has the same total quantity. So instead of e.g. 200 movements totalling 456 assigned, there's a single movement for quantity 456 assigned.

Only movements with the same site, warehouse location, batch, and stock ID can be combined.

A new KSDMU task has been added for this called ‘Cleardown’. This can also be configured and run from the new task scheduler screen in Khaos Control.

 

An updated application will be provided with this functionality. A batch file can be created to run the KSDMU action ran automatically as usual – by using e.g. Task Scheduler.

Contents of an example batch file:

KSDMU.exe Action=Cleardown  AgeCutoff=3650 ArchiveLocation="C:\025631\archive” OutputFile="C:\ 025631\KSDMU.log"

 

The parameters for the KSDMU option are as follows:

  • Which data type to clear down – currently this can only be set to ‘Stock Movements’, but further options may be added in the future.
  • Clear down data older than X days – this limits the age of data that can be cleared down, so that movements newer than X days are never cleared down. This might be set to e.g. 3650 days, so only data older than 10 years will be cleared down.
  • Which period to clear down data by – this can be set to ‘day’, ‘month’, or ‘year’, and controls what period of time the cleardown function will group movements by.  Setting to ‘year’ will mean all movements for the given year are cleared down into one movement.
  • Do not process more than X days worth of data at a time – this controls how many days the cleardown will cleardown each time it is run. Note that clearing down a large number of days can take a significant amount of time. This can be set to e.g. 366 if you want to clear down by year, and this will then cleardown 1 year every time the action is run. It can be set to e.g. 62 if you want to clear down by month, and this will then cleardown 2 months every time the action is run.

IMPORTANT NOTES:

  • Data is cleared down from oldest to newest, until it reaches the limit set by the ‘Clear down data older than X days’ option
    .
  • Clearing down large amounts of data will take a long time. It is recommended to limit the amount of data cleared down at a single time so that the process can run in a reasonable time.
  • The stock movements cleared down are also stored within the STOCK_MOVEMENT table of a database which the process will auto create. This database will have the same name as the source was an ‘_ARCHIVE’ suffix. The first time that the ‘Clear Down Tool’ is used it must be by a user will the rights to have the archive database be created.   

 

After applying the above Configuration, typical steps for usage would include:

 

  1. Run the KSDMU action either manually or through a scheduled task.
  2. The task will go through all movements of types ‘assigned’, ‘issued’, and ‘adjusted’ for each stock item during the specified time period, and roll them up to show only one total movement for each type.
    • Movements of other types will not be rolled up.
    • If any of the ‘assigned’ movements in the time period for a stock item relate to an invoice that has not yet been completed, then the ‘assigned’ rollup will not occur for that item. The ‘Issued’ and ‘Adjusted’ movements will still be cleared down.

 

5.   An example

Stock item X has the following movements in January 2014:

·        
Movement type ‘Adjusted’: quantity 200.

·        
Movement type ‘Adjusted’: quantity 50.

·        
Movement type ‘Adjusted’: quantity -10.

·        
Movement type: ‘Assigned’: quantity 10.

·        
Movement type ‘Assigned’: quantity 1.

·        
Movement type ‘Issued’: quantity 9.

·        
Movement type ‘Issued: quantity 1.

·        
Movement type ‘Ordered’ quantity 100.

·        
Movement type 'Ordered’ quantity 50.

 

A cleardown is run by month that clears down January 2014. After the cleardown, stock item X has the following movements:

·        
Movement type ‘Adjusted’: quantity 240.

·        
Movement type ‘Assigned’: quantity 11.

·        
Movement type ‘Issued’: quantity 10.

·        
Movement type ‘Ordered’ quantity 100.

·        
Movement type 'Ordered’ quantity 50.

‘Adjusted’, ‘Assigned’, and ‘Issued’ have been rolled up to show only one total movement. Other movement types are not rolled up.

 

Note:

o  
If any of the ‘Assigned’ movements were related to an unissued invoice, the ‘Assigned’ movements would also not be rolled up.

o  
Cleardown movements will have a doc # of _CLEARDOWN_  and an item ID of 0 against them.



Did you find this article helpful?