I'll appreciate you assistance in writing the appropriate script for the issue I have:
I have two tables:
1st table - contains Quantity data for raw material :
Item Order Warehouse Base Warehouse Quantity Current Warehouse Quantity
1 6 04 17000 0
1 9 05 16000 0
1 11 06 18000 3000
2 4 08 2500 0
2 5 09 6000 0
2 7 10 5500 0
2 8 11 7000 800
2nd table - contains data for finished products:
Item Finished goods Quantity
1 2200
2 2500
What I would like to do is to take for example item 1 and to allocate the 2200 in finished goods to each warehouse by the order as described below:
it should be per item
it should be by reversed order, I mean, 1st I should allocate to warehouse 06 for Item 1 and then to warehouse 05 and then to 04.
the Finished goods Quantity allocation should be as follows:
for warehouse 06 we had 18000 in base quantity and we currently have 3000 so we used 66%, we should take 66% of the 2200 and allocate them to warehouse 06 the rest would be allocated to warehouse 05
I'll very much appreciate you assistance on the script I should use in order to solve this issue.
