web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Inventory Financial Am...
Microsoft Dynamics AX (Archived)

Inventory Financial Amount & Inventory Physical Amount Posted in Inventory Value Report

(0) ShareShare
ReportReport
Posted on by HAIRUL HAZRI 1,214

Hi. 

I have a Stock Ledger Report which most of the fields required are from this query:

 

select * from INVENTTRANS
join INVENTTRANSORIGIN on INVENTTRANS.INVENTTRANSORIGIN = INVENTTRANSORIGIN.RECID
join INVENTDIM on INVENTTRANS.INVENTDIMID = INVENTDIM.INVENTDIMID
join INVENTITEMGROUPITEM on INVENTTRANS.ITEMID = INVENTITEMGROUPITEM.ITEMID
and INVENTTRANS.DATAAREAID = INVENTITEMGROUPITEM.ITEMDATAAREAID


The thing is, I need to add a Cost field which needed to be derived from the InventoryFinancialAmount + InventoryPhysicalPostedAmount fields in Inventory Value Report. 

But I'm unable to properly relate the Inventory Value Report with my current query even though I found out from here that those two fields are from InventTrans table. 

The whole Inventory Value Report itself is pretty confusing for me. There's so much tables related to this report I got lost while searching. I feel like I'm so close yet so far away.

Would appreciate some insights for this.

Thank You.

*This post is locked for comments

I have the same question (0)
  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: Inventory Financial Amount & Inventory Physical Amount Posted in Inventory Value Report

    That makes sense, and I see that all the time when I import data from other systems to AX.

    Good luck!

  • HAIRUL HAZRI Profile Picture
    HAIRUL HAZRI 1,214 on at
    RE: Inventory Financial Amount & Inventory Physical Amount Posted in Inventory Value Report

    You're right. My bad. The values in our report is set to absolute so I got confused there.

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: Inventory Financial Amount & Inventory Physical Amount Posted in Inventory Value Report

    Set what to negative?  If you have negative quantities on your Inventory value report, then you probably have actual negative quantities on-hand.  What's the issue with that?

  • HAIRUL HAZRI Profile Picture
    HAIRUL HAZRI 1,214 on at
    RE: Inventory Financial Amount & Inventory Physical Amount Posted in Inventory Value Report

    Hi Brandon,

    thanks for your suggestion. After reading about Inventory Transactions in this post, it made much more sense to me.

    And I just noticed that on our report query there's a range where DatePhysical != ' ' which means(*correct me if I'm wrong) only transactions that are Physically & Financially posted are being selected. 

    But, I'm still quiet blurry with the term back-dated where the Inventory Value Report shows negative quantity & amounts. Even after reading about it here it's still confusing. Why is it necessary to set to negative? Can you help me understand more about this?

    Thank You.

  • Verified answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: Inventory Financial Amount & Inventory Physical Amount Posted in Inventory Value Report

    Perhaps you should use a test environment to create a sales order, and confirm it, post a packing slip, and finally post an invoice, and inspect the inventory transactions for that sales order at each stage.  I think you would learn a lot about how InventTrans records work, and then you would understand your issue much better.

  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: Inventory Financial Amount & Inventory Physical Amount Posted in Inventory Value Report

    The Inventory value report can be back-dated, which is what makes it so complicated.  If you need to recalculate the value of inventory in the past, you have to start from now and remove the effect of transactions that have occurred after that point in time, or start from 0 and add in transactions until you arrive at a point in time.  That's why there are so many tables and it is so complex.

    The cost of a single inventory transaction (InventTrans record) is very simple.

    The reason you must consider StatusIssue and StatusReceipt is because inventory transactions acquire cost in phases.  First there is an accrued "physical" cost when the status is Deducted/Received.  Then there is a "financial" cost when the status is Sold/Purchased.  Beyond that, there are inventory transactions that do not impact the quantity or value at all, such as Ordered/On Order, and if you're including those in your report, you are really throwing off your results.

  • HAIRUL HAZRI Profile Picture
    HAIRUL HAZRI 1,214 on at
    RE: Inventory Financial Amount & Inventory Physical Amount Posted in Inventory Value Report

    Hi Brandon,

    sorry I'm getting a little bit confused here. 

    By "the cost of that InventTrans record", do you mean it's equivalent to  the Inventory Amount in Inventory Value Report   or   the cost for my Stock Ledger report?

    Actually currently we are setting the cost based on InventTrans.Qty and InventTransOrigin.ReferenceCategory /InventTransType. We have different types of Qty and Cost Amount to set for different conditions. Something like this:

     

    if (inventTrans.Qty > 0)
                {
                    if (inventTransOrigin.ReferenceCategory == 3 || inventTransOrigin.ReferenceCategory == 9 || inventTransOrigin.ReferenceCategory == 10 || inventTransOrigin.ReferenceCategory == 2)
                    {
                        stockLedgerTmp.RPurchaseQty = abs(inventTrans.Qty);
                        stockLedgerTmp.RPurchaseCost = abs(inventTrans.CostAmountPosted + inventTrans.CostAmountAdjustment);
                    }
                    else if (inventTransOrigin.ReferenceCategory == 4 || inventTransOrigin.ReferenceCategory == 5)
                    {
                        stockLedgerTmp.AdjustmentQty = abs(inventTrans.Qty);
                        stockLedgerTmp.AdjustmentCost = abs(inventTrans.CostAmountPosted + inventTrans.CostAmountAdjustment);
                    }
                    else
                    {
                        stockLedgerTmp.ROthersQty = abs(inventTrans.Qty);
                        stockLedgerTmp.ROthersCost = abs(inventTrans.CostAmountPosted + inventTrans.CostAmountAdjustment);
                    }
                }
                else
                {
                    if (inventTransOrigin.ReferenceCategory == 1 || inventTransOrigin.ReferenceCategory == 9 || inventTransOrigin.ReferenceCategory == 10 || inventTransOrigin.ReferenceCategory == 2)
                    {
                        stockLedgerTmp.IConsumedQty = abs(inventTrans.Qty);
                        stockLedgerTmp.IConsumedCost = abs(inventTrans.CostAmountPosted + inventTrans.CostAmountAdjustment);
                    }
                    else if (inventTransOrigin.ReferenceCategory == 4 || inventTransOrigin.ReferenceCategory == 5)
                    {
                        stockLedgerTmp.AdjustmentQty = inventTrans.Qty;
                        stockLedgerTmp.AdjustmentCost = inventTrans.CostAmountPosted + inventTrans.CostAmountAdjustment;
                    }
                    else
                    {
                        stockLedgerTmp.IOthersQty = abs(inventTrans.Qty);
                        stockLedgerTmp.IOthersCost = abs(inventTrans.CostAmountPosted + inventTrans.CostAmountAdjustment);
                    }
                }


    And somehow this logic is wrong for the costs part (RPurchaseCost,AdjustmentCost, IOthersCost, etc..), so that's why I need to get the cost based on Inventory Value Report. 

    Does the StatusIssue  and StatusReceipt  relatable to InventTransType somehow?

    Thank You.

  • Verified answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: Inventory Financial Amount & Inventory Physical Amount Posted in Inventory Value Report

    If you're working with a single InventTrans record, it's actually quite simple.

    If StatusIssue <= 1 (None or Sold) and StatusReceipt <= 1 (None or Purchased), then the cost of that InventTrans record is CostAmountPosted + CostAmountAdjustment.

    If StatusIssue <= 2 (Deducted) and StatusReceipt <= 2 (Received), then the cost of that InventTrans record is CostAmountPhysical.

    If StatusIssue is 3 or more, or StatusReceipt is 3 or more, then there is no cost because the inventory transaction is estimated (Picked, Arrived, Registered, Reserved physical, Reserved ordered, Ordered, On Order, Quotation ordered, etc.)

    The fields are different in InventSum and work slightly differently.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Our Community Code of Conduct has been updated!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Syed Haris Shah Profile Picture

Syed Haris Shah 9

#2
Mea_ Profile Picture

Mea_ 2

#2
Martin Dráb Profile Picture

Martin Dráb 2 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans