Remind your customer that the HSS has correct quantity numbers, but uses an estimate for cost.
I use this sproc (requires you have already setup and been using the HITB) and then I setup an Excel spreadsheet to call the sproc allowing the end user to change the date. It is basically a trimmed down, simple version of the HITB.
It also omits items with zero cost and zero quantity.
-----------------------------------
Create procedure GPA_HITB (
@ASOFDATE DATETIME
)
as
SELECT RTRIM(A.ITEMNMBR) ITEMNMBR
, RTRIM(C.ITEMDESC) ITEMDESC
, RTRIM(A.LOCNCODE) LOCNCODE
, RTRIM(B.ACTNUMST) ACTNUMST
, SUM(A.TRXQTYInBase) QTY
, SUM(A.EXTDCOST) EXTDCOST
FROM SEE30303 A
JOIN GL00105 B
ON A.IVIVINDX = B.ACTINDX
JOIN IV00101 C
ON A.ITEMNMBR = C.ITEMNMBR
WHERE GLPOSTDT <= @ASOFDATE
GROUP BY A.ITEMNMBR, C.ITEMDESC, A.LOCNCODE, B.ACTNUMST
HAVING SUM(A.TRXQTYInBase) + SUM(A.EXTDCOST) <> 0
ORDER BY ITEMNMBR, LOCNCODE
GO
grant exec on GPA_HITB to DYNGRP
-----------------------------------
Spreadsheet looks like this
