Thanks for taking time to support on this.
In the factOn-hand Inverntory table, we have a column “total availble quantity” that show the on-hand quantity for each row record (Bom Item)
In “factProductionStart” there are multi row records for each Bom items. Every row have a Production order start date, BOM item and a required quantity for each BOM item.
I want to allocate the on-hand inventoy for each row (BOM item) in factProductionStart table, based on the Production start date and required quantity.
There is also a Index column in factProductionStart I will use this index in case of the start dates are the same date for multi Production orders
Let assume that BOM item “A” have On-hand quantity of 500 pieces in factOn-hand inventory table
I want use this on-hand quantity for item “A” as the first step in order to allocate available oh-hand to all Item “A” in factProductionStart
Starting the allocation to Production order with oldest start date (FIFO principal) , subtract the required quantity from the onhand inventory quantity for that particular item .
Then go to the next Production order start date or index, and allocate the on-hand inventory to Production order number 2
But I need to subtract the required quantity for Production order number 1, from the on-hand qty, before subtract the required quantity for Production order number 2
Production order 1 - BOM Item “A”
Production order with oldest Start date = 01/03/2018
Required Qty = 100 pcs.
On-hand inventory= 500 pcs.
Allocation = 500-100 = 400 pcs left at Inventory
Production order 2 - BOM Item “A”
Production start date = 02/03/2018
Required Qty = 100 pcs.
On-hand inventory = 400 pcs. due to I have already allocated 100 pcs. to previous producton order, so my available inventory is now 400 pcs
Allocation = 400-100 = 300 pcs left at Inventory
Production order 3 … 300-100 = 200
Production order 4 … 200-100 = 100
Production order 5 … 100-100 = 0
Production order 6 … 0-100 = -100
And onwards, until I have allocated all production orders.
The number with bold font are “Total Available inventory after allocation”, should be use for the next Production order as “Total Available inventory before allocation” in the sequence
When the “on-hand inventory available after allocation” start to show quantity less than 1 pcs, then I can easliy see on which date or week number I have a stock out.
Hope this give more clarification