aaron.reese,

tldr; Thanks. I think I implemented something very close to what you are suggesting though my implementation has certain flaws.

Your understanding of the problem is correct and quite well explicated.  I believe my line of thinking and approach on how to solve the problem mirrors yours very closely.  I was initially 'thrown for a loop' not knowing how to construct a clever set based solution and decided that I was going to have to iterate via while loop (or cursor). I learned most of my programming in set-based fashion (MS SQL server), so this started to stretch the mind nicely. I made some insight to the problem since initially posted which I will relay here (certainly haven't made it to an optimal solution yet, hence why I very much leave it open...like you indicated it will be 'maybe' 80% optimal):

I decided to seek an algorithmic solution and had the insight that this problem is very similar to the problem of having a dollar bill and finding all possible combinations of half dollars, quarters, dimes, nickels and pennies that add up to a buck.  Since I knew the possible stock sizes (40,20,10,1 for example) for each item, I could use a brute force/recursive approach to initially construct every possible solution (definitely would prefer a query here, but I took what I had at the time to start with).  I did that 'by hand' using the Java code that I leveraged online to construct a base table of solutions (possible combinations that lead up to each item size using smaller sizes and a version of your three proposed temp tables I think).  

Now that I had all the possible solutions (assuming I didn't miss some by my hackish approach), I created a new column and prioritized each solution as youu succinctly explained using SQL ranking functions, handling the case of a 'tie' (20 + 10 + 10 = 40 = 10 + 10 + 10 + 10) likely by choosing randomly at first.  From there I constructed two more temporary tables holding the orders and excess stock at each respective iteration.  I then iterated down in item size through the orders, checking each time to attempt get the highest priority combo if it happened to be available as excess. I added another column to tag each item after it had been iterated and let the while loop proceed until there was nothing left to check.  The excess temp table gets updates as does the order temp table at each iteration.

This solution has some drawbacks but may end up saving the day for the integration need that I have currently (pending coming up with a more optimal approach).  There are some problems worth admitting directly:

1) As new item sizes are added, I'll have to brute force and prioritize the solutions as I had done before (maintenance problem but doable)
2) I picked randomly in the case of a tie.  A smarter solution would actually fork and look at both options and see if either path of the 'tree' resulted in better item utilization at smaller sizes (for example we might choose not to use the 10 + 10 + 10 + 10 = 40 solution since the 10's might be used in another combo more effectively. 
3) Had I implemented part 2), then the remaining election could likely be made at random (fine) or based on item price (better) for each combo at the time of order or some other business logic that is too minute to worry with.
4) If I end up going with my current solution, I am absolutely going to need to create excellent test cases to ensure it doesn't break down or  suggest outlandish things going forward.  Another opportunity for growth on my end of the programming learning world.

Thanks again for looking at this and I hope my rambling makes sense for those of you who read this far.

-Michael