RE: Inventory value figures

A quick Trace Parse will show this. Below is the query it runs when the On-Hand is called.

INVENTSUM is where your inventory values are stored but INVENTTRANS plays a part as well which is where you need to be careful as far as pitfalls go. It's not enough to just SUM the inventory but SUM (or subtract) against the right status types, i.e., AVAILPHYSICAL, REGISTERED, ON ORDER, etc.

---

SELECT SUM(T1.POSTEDQTY),

SUM(T1.POSTEDVALUE),

SUM(T1.PHYSICALVALUE),

SUM(T1.DEDUCTED),

SUM(T1.REGISTERED),

SUM(T1.RECEIVED),

SUM(T1.PICKED),

SUM(T1.RESERVPHYSICAL),

SUM(T1.RESERVORDERED),

SUM(T1.ONORDER),

SUM(T1.ORDERED),

SUM(T1.ARRIVED),

SUM(T1.QUOTATIONRECEIPT),

SUM(T1.QUOTATIONISSUE),

SUM(T1.PHYSICALINVENT),

SUM(T1.AVAILPHYSICAL),

SUM(T1.AVAILORDERED),

SUM(T1.PDSCWPOSTEDQTY),

SUM(T1.PDSCWDEDUCTED),

SUM(T1.PDSCWREGISTERED),

SUM(T1.PDSCWRECEIVED),

SUM(T1.PDSCWPICKED),

SUM(T1.PDSCWRESERVPHYSICAL),

SUM(T1.PDSCWRESERVORDERED),

SUM(T1.PDSCWONORDER),

SUM(T1.PDSCWORDERED),

SUM(T1.PDSCWARRIVED),

SUM(T1.PDSCWQUOTATIONRECEIPT),

SUM(T1.PDSCWQUOTATIONISSUE),

SUM(T1.PDSCWPHYSICALINVENT),

SUM(T1.PDSCWAVAILPHYSICAL),

SUM(T1.PDSCWAVAILORDERED),

T1.ITEMID,

T2.INVENTLOCATIONID,

T2.INVENTBATCHID,

T2.WMSLOCATIONID,

T2.WMSPALLETID,

T2.INVENTSERIALID,

T3.NAMEALIAS

FROM INVENTSUM T1 CROSS

JOIN INVENTDIM T2 CROSS

JOIN INVENTTABLE T3

WHERE (((T1.PARTITION=?)

AND (T1.DATAAREAID=?))

AND (T1.CLOSED=?))

AND (((T2.PARTITION=?)

AND (T2.DATAAREAID=?))

AND (T1.INVENTDIMID=T2.INVENTDIMID))

AND (((T3.PARTITION=?)

AND (T3.DATAAREAID=?))

AND (T1.ITEMID=T3.ITEMID))

GROUP BY T1.ITEMID,

T2.INVENTLOCATIONID,

T2.INVENTBATCHID,

T2.WMSLOCATIONID,

T2.WMSPALLETID,

T2.INVENTSERIALID,

T3.NAMEALIAS

ORDER BY T1.ITEMID OPTION(FAST 14)