HI
I'm looking to create a script that will run through data on a table that will allocate incoming stock of a part to customers Back order and give a stock balance before moving on to the next row.
please see the below table where I have the customer orders and the purchases that we ordered to supplier.
I need to check the ordered parts then check the incoming orders then allocate the quantity based on FIFO then put the ETA date accordingly.
| Orders from customer |
| PartNO | Ordered Qty | Customer Order Number | Order Date |
| ABC1 | 5 | 11 | 01-02-2019 |
| ABC1 | 20 | 21 | 25-02-2019 |
| ABC2 | 10 | 21 | 25-02-2019 |
| ABC1 | 12 | 30 | 27-02-2019 |
| Orders from TIQ To Supplier |
| Part Number | Purchased QTY | TIQ Order Number | ETA Date |
| ABC1 | 2 | 1 | 15-02-2019 |
| ABC1 | 15 | 2 | 30-02-2019 |
| ABC2 | 20 | 2 | 30-02-2019 |
| ABC1 | 50 | 7 | 04-04-2019 |
Expected Result:
| Resulting Table |
| PartNO | Customer Order No | Customer Order date | Supply Qty | TIQ Order Number | ETA |
| ABC1 | 11 | 01-02-2019 | 2 | 1 | 15-02-2019 |
| ABC1 | 11 | 01-02-2019 | 3 | 2 | 15-02-2019 |
| ABC1 | 21 | 25-02-2019 | 12 | 2 | 30/02/2019 |
| ABC1 | 21 | 25-02-2019 | 8 | 7 | 04-04-2019 |
| ABC2 | 21 | 25-02-2019 | 10 | 2 | 30/02/2019 |
Kindly advice on the method to achieve it.
