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:
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 :(