Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

Inventory value figures

Posted on by

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.

  • GuyUK Profile Picture
    GuyUK 28,363 on at
    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.

  • MattA Profile Picture
    MattA on at
    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?

  • Verified answer
    GuyUK Profile Picture
    GuyUK 28,363 on at
    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.

  • Solozmar Profile Picture
    Solozmar 796 on at
    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

  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    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

  • MattA Profile Picture
    MattA on at
    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).

  • Solozmar Profile Picture
    Solozmar 796 on at
    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)

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,773 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,513 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans