v2018.1 Premium.
After a recent upgrade a customer complained about zero quantity items showing on the Valuation report when they chose to suppress zero quantity items. Tracing that back to the TotalWarehouseValue column in IM_ItemWarehouse, which was non-zero for zero QoH lines lead me to look at IM_ItemTransactionHistory. When I add up the "ExtendedCost" column I get the bad value (which the utility calculated as the TotalWarehouseValue). Adding up the ExtendedStandardCost column I get zero (correctly).
This seems like a bug to me. Can anyone else confirm?
Query:
select ItemCode,
WarehouseCode as Whse,
(select Valuation from CI_Item where CI_Item.ItemCode = ITH1.ItemCode) as Val,
(select TotalWarehouseValue from IM_ItemWarehouse
where ITH1.ItemCode = IM_ItemWarehouse.ItemCode
and ITH1.WarehouseCode = IM_ItemWarehouse.WarehouseCode) as BadAmount,
(select sum(ExtendedStandardCost) from IM_ItemTransactionHistory ITH2
where ITH1.ItemCode = ITH2.ItemCode
and ITH1.WarehouseCode = ITH2.WarehouseCode) as GoodValue,
TransactionQty,
UnitCost,
ExtendedCost,
ExtendedStandardCost,
TransactionDate,
TransactionCode,
EntryNo,
IMTransactionEntryComment
from IM_ItemTransactionHistory ITH1
where WarehouseCode + '^' + ItemCode in
(select WarehouseCode + '^' + ItemCode from IM_ItemWarehouse
where QuantityOnHand = 0 and TotalWarehouseValue <> 0)
order by WarehouseCode, ItemCode, TransactionDate