Weekly Dynamic: Inventory Value via SQL

We're going to go a little technical stuff this week. Buckle up.
Sometimes you need the on hand value of an inventory item for a Crystal Report or some other external use. Since GP maintains the cost layers regardless of your inventory valuation method, this can be a tough number to get. It's not one number sitting in a table.
First some background. When you buy inventory, GP stores the cost of that item. When your sell inventory, it works it's way back through those costs based on your inventory method. If I buy one item for $1, then $2, then $3 my inventory value is $6. If I sell an item under the FIFO method, $1 cost comes out and my new inventory value is $5. Under LIFO, $3 would come out and my new inventory value would be $4. You see that it's different based on the method you choose.
GP maintains these layers to allow you to switch between the various methods. Not only that, it maintains them by location. If you look under Adjust Costs (Tools-Utilities-Adjust Costs) you can see the various layers for a location.
So how the heck do you do all the math to figure out what's been bought and sold and what's remaining at what cost? You don't. You let GP and SQL do it for you. The key is the table IV10200. I know what you're thinking, why is posted inventory data in a table numbered as a work table? Beats me, but it is.
IV10200 holds the Qty In (from purchases or adj) and the Qty Out (from sales or adj) along with the associated costs. So with a pretty simple query you can get the inventory value. Something like this:
SELECT  Itemnmbr, SUM((dbo.IV10200.QTYRECVD - dbo.IV10200.QTYSOLD) * dbo.IV10200.UNITCOST) AS Cost 
FROM    IV10200
Group by itemnmbr
Order by itemnmbr
This little query will give you the inventory value of each item number. You can obviously tune it for location, limit it the number of items, etc. This absolutely works for FIFO and LIFO. It should work for average methods as well but we use FIFO so I haven't tested it. The way to test it is run the Stock Status report for a particular item and then compare that to the query value.
Whew! Technical accounting and technical technology all in one post. And no picture either. I'll have to do something fluffy next week!