Multiple Location Inventory Design

[Craig Tooker]

On 3/25/10 17:37, Scott Walker wrote:
> I have an order processing system.  Currently it only handles inventory kept
> in one location.  Now a customer wants to start keeping inventory at
> multiple locations.  So the same part# could be in the inventory at the main
> office and also at several branch offices.  I'm just starting to think of
> the design of this.  Should I have a separate record in the inventory file
> for each part#/location combination?  Should I use a qualified file for each
> of the inventory of each location? (I don't use qualified files for anything
> at the moment).   Of course, this design decision permeates itself
> throughout the system (ie.  Order Entry, Shipping, Purchasing, etc).  Any
> design ideas or thoughts would be appreciated.
>    
You should of course, keep the SKU related information (everything but 
vendor, qtys and locations) in the inventory item master file.

A detail level file for vendors that supply the item should be kept to 
allow you to sell items from multiple vendors under one SKU (in the case 
of commodity items or where you have multiple vendors supplying the same 
item).  A vendor ID can then become part of an item available level.  
Each vendor will have their own costing and delivery and this will allow 
you keep them separate as layers of inventory (for purposes of costing 
and implementing FIFO or LIFO).

A detail level file that contains itemID, location, qty, vendor, costing 
and date should be kept - again for purposes of costing and FIFO/LIFO.  
Additionally you can then implement a stock locating system for 
efficient picking and correct allocation under the FIFO or LIFO logic.  
This allows you to direct material handlers for picking items for 
invoicing or stacking items from orders.

You could keep accumulators in the master record for total on-hand, 
available, allocated and on-order if you wish but it is not required 
(although it may make certain operations more efficient.

Efficiency is also helped if you make that item detail level layout the 
same for the PO detail (where you purchase the item) and also on invoice 
detail (where you sell the items).  That will make the management of the 
data straight forward and gives you the ability to make use of COPY (and 
its friends).

Craig Tooker