Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2020 release wave 1 Discover the latest updates and new features to Dynamics 365 planned through September 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 Timeline
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
I need to get on hand inventory for the beginning and ending of last year for each item. This can be done be an TSQL script, x++ , SSRS report doesn't matter. Does anyone know how to get this information in AX 2012
Thanks for the reply. Would I use the DatePhysical date for the date range and sum by the physical qty?
Well by just querying on inventTrans(based on daterange), you may get only relative value but not absolute value of on hand for a given item.
Initially we need to get the onHand value on end of last year(from today).
Use InventOnHand query to get the current on hand for every item variant, by considering only the
items they were created before/in last year(createdDateTime on InventTable is before/in last year).
Loop through the inventrans table joined with inventDim, group by item
-- get the sum of positive quantity from InventTrans by considering datePhysical as daterange(between today and end of last year)
-- get the sum of negative quantity from InventTrans by considering datePhysical as daterange(between today and end of last year)
For a given item(variant), from the current OnhandInventory deduct the summed up positive quantity and add the summed up negative quantity to get onHand value on end of last year i.e on 12/31/2016.
Perform same logic by(i.e looping through inventTrans table) considering the daterange as 1/1/2016 to 12/31/2016.
Hope this helps you.
Thanks for the reply, Would it be as simple as this below (I don't need by Warehouse):
--Ending balance in 2016
select itemid, sum(qty)
Where DatePhysical < 2017/1/1
Group by ItemID
Yeah, but this
-Ending balance in 2016
select itemid, sum(qty) // Consider sign as I stated two loops
Where DatePhysical < 1/31/2016
and datePhysical > 1/1/2016
Try and let us know if you face any issue.
Be aware that DatePhysical is the date of the physical update (product receipt, packing slip). If you're using 'Picked' or 'Registered' (and not posting the physical update on the same day those things are happening), and if you want Picked or Registered events to be considered in this On-hand quantity calculation, then you'll not be able to do that with InventTrans alone.
I don't actually know how you would do it, but I thought it worth mentioning this limitation!
Right right, I'm having trouble getting the balance for a particular date using just the inventtrans. I tried the two loops like Golla suggested, but no luck. Any suggestions? Thanks for the help.
Can you let us know where you struck/provide your code, will try to fix it.
Below is the code. The beginning of 2016 on hand values are off. I'm also trying to get the amount purchased and sold in 2016. I broke it into positive and negative values like you mentioned. Thanks in advance
select INVENTTABLE.itemid ,
CAST( isnull( BOHI.BegOnHand,0) AS DECIMAL(18,2))BegOnHand ,
CAST( isnull(P.QtyPurchased,0)AS DECIMAL(18,2)) QtyPurchased ,
CAST( isnull(s.QTYSOLD,0)AS DECIMAL(18,2)) QTYSOLD ,
CAST( isnull( EOHI.EndOnHand,0)AS DECIMAL(18,2)) EndOnHand
--Beginning on hand in 2016
left outer join (
select itemid, isnull(SUM(qty),0)BegOnHand
where DATEPHYSICAL < '2016-1-1'
group by ITEMID )BOHI
on bohi.ITEMID = INVENTTABLE.ITEMID
left outer join (
select ITEMID, isnull(SUM(QTY),0)*-1 QTYSOLD
where DATEPHYSICAL >= '2016-1-1' AND DATEPHYSICAL < '2017-1-1' and QTY < 0
GROUP BY ITEMID )S
on s.ITEMID = INVENTTABLE.ITEMID
select ITEMID, isnull(SUM(QTY),0) QtyPurchased
where DATEPHYSICAL >= '2016-1-1' AND DATEPHYSICAL < '2017-1-1' and QTY > 0
GROUP BY ITEMID )P
ON P.ITEMID = INVENTTABLE.ITEMID
--Ending on hand in 2016
left outer join (
select itemid, ISNULL( SUM(qty),0)EndOnHand
where DATEPHYSICAL < '2017-1-1' and STATUSISSUE <> 7
group by ITEMID)EOHI
ON EOHI.ITEMID = INVENTTABLE.ITEMID
on inventtable.PRODUCT = ecoresproduct.RECID
on ECORESPRODUCTTRANSLATION.PRODUCT = ecoresproduct.RECID
where ( BOHI.BegOnHand <> 0) or ( P.QtyPurchased <> 0) or( s.QTYSOLD <> 0) or (EOHI.EndOnHand <> 0 )
Order by inventtable.ITEMID
Some general comments. When writing SQL against AX tables, you should consider PARTITION and DATAAREAID....even if your AX environment has only one Partition and one company. All the SQL indexes will have these two fields in, and you'll want to let SQL use indexes when it can, right?
DATAAREAID is the AX Company ID. You can find the value for Partition in the dbo.Partition table. It's probably the RecId of the 'Initial' partition.
Specifically on this query, you are only interested in inventory transactions which are Sold, Deducted, Purchased or Received. STATUSISSUE and STATUSRECEIPT are the relevant fields.
I have added all this into your SQL. Er, check I did it right; your SQL appears to be of a much higher standard than mine!
Use the class :InventSumDatePhysicalDim
it will give you on hand per date.
Just pass item number, dimensions, and the date.
We tried this and in certain cases it is not matching with the inventory transactions when summed up for the relevant period.. Any idea why... ? We see a certain quantity as Picked when we debugged but we are not able to find that in the front end
The InventSettlement stores recalculations on a cost amount in time and may need to be considered.
When the physical date is later than the financial date on the InventTrans, and the financial date is within the date selection and the physical date not then the numbers will not agree.
if your inventory close is not run regularly then back date posted corrections done by inventory closing per item transaction also need to be considered when you try to reconcile by period..
Try the view behind the inventvaluecube view which should contains all the information.
Hi VS Rao,
If you're referring to the PasteBin link I posted, that query specifically excludes inventory with the status Picked. STATUSISSUE < 3 is responsible for that (and similarly STATUSRECEIPT < 3 will cause inventory with the status Registered to be excluded).
I have re-done the query to include Picked and Registered inventory:
Oh, and bear in mind this is still based on SSRSGuy's original query; I never really did look to see exactly how it works.
Business Applications communities