Help with a schema, custom object to track product stock on Orders

Question

How can I track product stock levels across locations?

Our business process is basically this:

When an oppy is closed won a trigger will automatically create a contract (not used by us - consider it a dummy record) and an order (which needs the contract). The order will have the price book and products automatically populated from the oppy alongside some other pieces of information like account/contact etc. Anyway when the Order is Activated I want to track our product stock levels decreasing and need to account for the stock being removed from diferent locations (so various locations have various stock levels).

I have created a schema like the following, The Standard Objects Order, Order Line Item, Price Book, Price Book Entry and Product are used. I have created custom objects as lookup relationships from Orders:

• Payment Terms (to relate payment terms to the order (which will have a PDF invoice associated with it (visualforce)).
• Payments (to track payments made to complete the order)
• Deliveries (to store tracking numbers from shipments to fulfill the order)

And I have the following lookup relationship against Products:

• Stock Location (to store locations that can be used to store stock)

  

I really only had two ideas to achieve this, the model above forces me to have to have duplicate products, one for each stock. However this can't be the best method...

The only other idea I had was to store the location on the order so we can see what is shipped from each location. However this doesn't tell us what quantity we have in stock, only what has been shipped out.

I apologies if this is the wrong place to ask, I just need a little advice in database design/schema design. I think I have over complicated it :(

Answer 1

This is a pretty high-level question, so I'll certainly caveat this with "haven't done full discovery/there may be more than one right answer/your mileage may vary".

I think your Product Stock/Stock Location model is not ideal for your use case and is going to cause you some issues, as your question notes.

I would call Product Stock "Stock Movement" and create it as a detail object under Stock Location as the master. Give Stock Movement a number field Quantity and treat negative values as expenditure of stock and positive numbers as an addition of stock.

Add a lookup field (not master-detail) from the Stock Movement to the Order Line, not the Order.

When your order commits, create a Stock Movement for each Order Line, with a negative Quantity equal to the amount order times -1, parented under the Stock Location from which you plan to fulfill the order. When you restock, create a Stock Movement for the Stock Location with a positive Quantity, and don't link it to an Order Line.

Add a Roll-Up Summary Field to Stock Location to roll up the SUM of Quantity on Product Stock Movements, and call it "Inventory". Then you've always got access to the total inventory available in each location. Optionally, you can also roll up the total Inventory across Stock Locations to the Product level.

I think this data model will make your automation and your reporting a good deal easier. It's loosely similar to how the ERP managed package Accounting Seed handles inventory.