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

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id :
Microsoft Dynamics AX (Archived)

Historical On-Hand AX2012

(0) ShareShare
ReportReport
Posted on by

Hi!

I need to get historical on-hand stock level using the AX-database (i.e. using InventTrans). I understand that it works like this (correct me if I'm wrong):

  • In InventSum on-hand (represented in field PHYSICALINVENT) is calculated using POSTEDQTY + RECEIVED - DEDUCTED + REGISTERED - PICKED
  • For InventTransthis means that QTY should be summed up for all records where 
    • DATEFINANCIAL > '1900-01-01' OR
    • STATUSISSUE = 2 OR
    • STATUSISSUE = 3 OR
    • STATUSRECEIPT = 2 OR
    • STATUSRECEIPT = 3
  • If I try the above I get an exact match with InventSum so it seems to be correct for getting present on-hand. To get historical on-hand at date D I understand that I should either sum up all transaction until D, or take present on-hand and subtract transactions back till D. What I then can't understand is what date-field to use for this. First I thought of Financial or Physical, but these are 1900-01-01 for some transaction types so then I can't see how they could render a correct historical on-hand. (Let's say on-hand was 10 on 2016-03-01 and then 8 on 2016-03-02 due to a PICKED-transaction of quantity 2. Now if I want see this how historical on-hand differs for these dates using Financial or Physical date I can't because they don't give me the date for the PICKED-transaction, right?). Could I use DateStatus instead? Or how should I do this?

*This post is locked for comments

I have the same question (0)
  • Ludwig Reinhard Profile Picture
    Microsoft Employee on at

    Hi,

    Have you tried using the standard inventory value report and/or the inventory value cube for AX2012?

    Ludwig

  • Verified answer
    Brandon Wiese Profile Picture
    17,788 on at

    Whether you consider Picked and Registered as On-hand depends on who you are.  

    If you're a warehouse supervisor responsible for fulfillment of product, then you tend to think of On-hand as including Picked and Registered, since On-hand in your mind is what is available for your purposes.  I use the word "available" here loosely, because it is another one of those words you find throughout the AX interface that tends to mean different things.

    If you're an accountant responsible for financial reporting, then you tend to think of On-hand without consideration for Picked and Registered, since those quantities do not have an effect on ledger, do not represent a change of ownership of the inventory (like a packing slip represents a delivery to a customer), and as you have noted do not have a Physical date for exactly those reasons.

    When I write reports, I tend to show On-hand as Posted + Received - Deducted, and then show Picked and Registered separately in their own buckets.  If you don't separate them out, then if you also show inventory value on that same report, it's just confusing because Picked and Registered have no value.  Furthermore, if you're a user that cares about Picked and Registered, you probably also care about other buckets like Reserved physical and Reserved ordered, and Available physical, and very quickly it becomes impossible to represent a useful state of inventory for an item with a single number.  So, you have to show them all so people can make decisions based on them.

    So, having explained all that, to me it makes no sense to talk about Picked, Registered, Reserved, or Available inventory quantities (and of course not value) in the past.

    Finally, it's practically impossible to reconstruct a past Picked or Registered quantity because inventory transactions, as they are split and summed, change state from On order, to Picked, to Delivered, to Sold.  The Delivered state leaves a nice audit trail in the form of a Physical date and a Physical voucher, and the Sold state does the same, and because of this clean audit trail these effects can be "reversed" to achieve point in time reporting.  But what state is left for Picked?  Can you even tell if an inventory transaction ever spent time in the Picked state?  How do you reconstruct an inventory transaction that went from On order, to Picked, back to On order, and then gets deleted entirely because the Sales order was canceled having never been delivered?  What if you pick, un-pick, and pick again, does the single Picked date field reflect that?

    The only practical answer is to entirely ignore Picked and Registered for point in time reporting.

  • Suggested answer
    Vilmos Kintera Profile Picture
    46,149 on at

    Thinking out-of-the-box here, but some SQL Server editions and AX 2012 version support SQL Server Change Tracking. You could then define which table and what columns to monitor for changes going forward once this is enabled. Then you could try to retrieve/reconstruct point in time values for your InventSum table based on the tracking versions per a specific date and time. Of course this would not help with historical transactions. Maybe this is something that you could explore as well.

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

Quick Links

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans