Inventory value figures

(0) Share

Report

Posted on by MattA

We are working with inventory management as part of a phased implementation of AX 2012 (with an upgrade to AX 7 pending for 2017). I would like to display the current inventory value for an item on the on-hand inquiry found in the inventory management module. Where is inventory value stored in AX so that I can direct our development team to the correct data?

Are there any pitfalls that you are aware of when displaying inventory value?

We have not yet finalized our decision on a costing method, but the costing method in our legacy system is Weighted average. We are considering Moving average or FIFO in AX.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Solozmar 796 on at

Like (0)

Report

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)

Was this reply helpful? Yes No
MattA on at

Like (0)

Report

RE: Inventory value figures

Sorry, I should have been more clear. I am looking for the cost value (i.e. the total currency value of the inventory on hand).

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Inventory value figures

Hello Matt,

The inventory value is calculated based on your item model group settings. There is no specific table that holds that value but there is the inventory value report that shows you this value. Please note that you can identify the so-called current running average price via the on-hand inventory form.

What do you mean by pitfalls displaying the inventory value? Do you refer to the inventory closing process that needs to be run or something else?

Regarding the change in your inventory valuation principle; I would suggest that you also discuss the planned change with your auditors beforehand to avoid difficulties later on.

Best regards,

Ludwig

Was this reply helpful? Yes No
Solozmar 796 on at

Like (0)

Report

RE: Inventory value figures

Ludwig's suggestion of the Inventory Value report is a great one. It will show you the quantity and value of all posted and non-posted inventory.

I recently worked with a member in our accounting department regarding the total costs and I suggest reading through the article in the below link. It does a great job breaking down the logic behind the report.

Understanding the Inventory Value Report

Was this reply helpful? Yes No
Verified answer

Guy Terry 28,909 Moderator on at

Like (0)

Report

RE: Inventory value figures

Hi Matt,

To see the value of the inventory of an item with in the 'On-hand' inquiry, you first need to set the 'Dimension display' correctly. Check the Storage dimension group and Tracking dimension group of the item. Look at which dimensions have 'Financial inventory' ticked.

On the On-hand inquiry, you should enable any of the Product dimensions that you use (if any; Config, Size, Colour, Style), and all of the dimensions which have 'Financial inventory' ticked (but not any of the others).

Then pick a record in the On-hand inquiry, and click on the 'On-hand' tab. Details about the value of that line can be seen at the bottom.

Was this reply helpful? Yes No
MattA on at

Like (1)

Report

RE: Inventory value figures

Thank you. This was very helpful.

On the on-hand tab of the On-hand inquiry, could you help clarify what the difference is between the Physical cost amount and the Financial cost amount? Since we are talking about inventory value, does the Physical cost refer to items received but not invoiced where Financial cost refers to items that have been both received and invoiced?

Was this reply helpful? Yes No
Guy Terry 28,909 Moderator on at

Like (0)

Report

RE: Inventory value figures

Yes, you've got it. The value of the inventory is the physical plus the financial, I think. Which should match the cost price x the quantity.

Was this reply helpful? Yes No