Hi all,
I am looking for a best practice to allocate available inventory from a plant-inventory table to a set of orders, which will need to be prioritized by the scheduled date of the order. It will only fill the order in the column "Available Qty" if the remaining inventory can fill the entire order.
Example Table of Inventory:
Material
| Plant
| Inventory |
|---|
| 100-1 | A | 1,000 |
| 100-1 | B | 10,000 |
| 500-2 | A | 500 |
| 500-2 | B | 0 |
|
|
|
|
Table of Orders:
Order Number
| Material
| Plant
| Order Qty
| Delivery Date
|
|---|
| 1 | 100-1 | A | 1,000 | 1/5/2013 |
| 2 | 100-1 | A | 5,000 | 1/7/2013 |
| 3 | 500-2 | A | 10 | 1/10/2013 |
| 4 | 100-1 | B | 20,000 | 2/1/2013 |
Final Expected ResultOrder
Order Number
| Material
| Plant
| Order Qty
| Available Qty
| Delivery Date
|
|---|
| 1 | 100-1 | A | 1,000 | 1,000 | 1/5/2013 |
| 2 | 100-1 | A | 5,000 | 0 | 1/7/2013 |
| 3 | 500-2 | A | 10 | 10 | 1/10/2013 |
| 4 | 100-1 | B | 20,000 | 0 | 2/1/2013 |
Number
Order Number
Order Number
