I’ve got me a design and implementation problem extracting and formulating the results I need.
What I’m trying to do is build some reporting on forecasted stock commitments in an inventory system to help drive JIT stock procurement and manufacturing. I’m really sorry, there is a fair bit to this so if you stick with me on this explanation, I really appreciate it.
The underlying database for the inventory system I’m using is a 3rd party propriety database that I cannot alter. What I’m doing is using C# I'm executing a number of simple SQL queries via an oleDB connection to extract the data into object collections then manipulate the results via a series of linq queries and lamba functions.
The data model I’m working with is as follows: Please note, this is somewhat simplified
For any one product, what I need to show:
- The Stock on hand
- The list of jobs that make up forecasted demand (Needs to be separated by Job manufacture attribute)
- Calculate the forecasted commitments for products based on business rules
- Calculate the lead time of manufactured products.
Some other notes
- A Kitset is a product that has components (End product is manufactured).
- A component can itself be a kitset.
- A component is a product used in the manufacture of kitsets.
- Any product can be supplied direct to end consumer regardless of being either a kitset, a component or otherwise.
Rules to calculate product Lead Times If product is not a Kitset, use lead time from database, no further change is required Otherwise, will need to calculate based on the lead times for the component products as follows
If Kitset product, Kitset Lead Time = DB LeadTime + Maximum component Lead Time where NonDiminishing = false + the Sum of component LeadTime where NonDiminishing = True
To calculate the future commitments of products
Consider this example.
Easiest to start at the bottom and work my way up.
- Kitset Product 3, I’ve got 22 in stock, and future commitment for 30. This means I need to manufacture a further 8 units to satisfy demand.
- Kitset Product 2, I’ve got 18 in stock and a supply commitment 16. In addition to this I also need 8 units to satisfy the manufacture requirements of Kitset Product 3 giving me a total commitment of 24 meaning I have to manufacture 6 units.
- Kitset Product 1 follows the same sort of path. I’ve got 4 in stock, I need 6 units to satisfy the requirements of Kitset Product 2 so I need to manufacture a further 2 units.
This leads me to where I’m getting hung up. Effectively I’ve got a collection of products, each instance of a product may have a collection of products and so on. I’m really not sure how to implement this cyclic relationship.
Really appreciate any feedback you can give me.