I found the question about InventValueReportView interesting, so I did some playing around with it.
InventSum can be tied back to InventTrans quite easily, since the current balance for any item, both in terms of quantity and value, is always the sum of all transactions in its history. Of course, InventSum only exists for Released products of type Item (not Service), and InventTrans records that are estimated, i.e. On order, Ordered, Picked, etc., do not contribute to the "accounting" on-hand quantity or value found in InventSum.
Still, I wondered if the InventValueReportView was a faithful transaction breakdown of historical inventory.
I found the following two queries to produce identical results in a substantial environment with multi-year history.
First, a summary of InventSum to determine the total quantity and value of inventory.
SELECT
s.[PARTITION],
s.DATAAREAID,
SUM(s.POSTEDQTY + s.RECEIVED - s.DEDUCTED) AS QTY,
SUM(s.POSTEDVALUE + s.PHYSICALVALUE) AS AMOUNT
FROM INVENTSUM s
GROUP BY s.[PARTITION], s.DATAAREAID
ORDER BY 1, 2
Then, a summary of InventValueReportView to determine the same.
SELECT
v.[PARTITION],
v.DATAAREAID,
SUM(v.QTY) AS QTY,
SUM(v.AMOUNT) AS AMOUNT
FROM INVENTVALUEREPORTVIEW v
JOIN INVENTTABLE i ON i.[PARTITION] = v.[PARTITION]
AND i.DATAAREAID = v.DATAAREAID
AND i.ITEMID = v.ITEMID
WHERE v.TRANSDATE <> CAST(N'21541231' AS DATETIME)
AND i.ITEMTYPE = 0
GROUP BY v.[PARTITION], v.DATAAREAID
ORDER BY 1, 2
Note the filter to ItemType of 0 (Item) to exclude the inventory transaction history for ItemType of service which do not contribute to InventSum. Also note the exclusion of TransDate of 12/31/2154 which appears to include some weighted average adjustments. Some research into how that works is probably warranted.
These queries tie-out to each other precisely for the current on-hand quantity and value.
In theory, and I'll be testing this some more, if you just add a filter on TransDate for InventValueReportView, you should be able to arrive at a quantity and value for the balance as of that date.
