Personalized Community is here!
Quickly customize your community to find the content you seek.
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 2Discover the latest updates and new features to Dynamics 365 planned through March 2021.
Release overview guides and videos Release Plan | Preview 2020 Release Wave 2 TimelineWatch the 2020 Release Wave 2 virtual launch event
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 want to extract my FIFO costs by Sales Order, I developed one SQL query based on InventTrans but I see that the result is not good. So I found this field CostPrice in SalesLine Table, but i don't know if it is containing the FIFO cost on creating the order without any update later or if it is updated/adjusted after stock recalculation or closing.
Thanks in advance.
The cost price might change after an inventory recalculation / close until the time the transaction is fully settled and closed.
You can find more information about this process here:
the return costprice field on sales table is static in nature and gets populated when order line is created, it doesn't get updated later.
What gets updated later is the cost that's maintained at the inventory on-hand.
If you are trying to extract the cost for the sales line, your best bet is to get that cost from inventTrans... there are couple of fields on inventTrans and (then + - inventSettlement) to come up with the best possible price. Your query will be dynamic to pull these costs and your costs may change until inventory is closed for that specific inventTrans.
To get the actual cost price, as Satish mentioned, you need to get it from InventTrans.
The cost price formula is:
(inventTrans.CostAmountPosted + inventTrans.CostAmountAdjustment) / inventTrans.Qty
Since the InventTransId field is not a unique key you must sum InventTrans records to get the real cost price of a specific sales line.
If you'd like, you can share your SQL query with us and we will try to help.
Can you let us know if your question has been answered or whether additional support is required?
Many thanks and best regards,
The key point in the comments is that you need to understand that InventSettlement will, have multiple records for a single InventTrans record. It is a child table with a 1:n relationship.
For any cost adjustment thai s made that affects a given InventTrans record, a new InventSettlement record is created, and also it simultaneously adjusts either the CostAmountPhysical or CostAmountAdjustment fields on the InventTrans record.. To determine the Inventory value from a point in time in the past..
To better understand the InventSettlement records as part of the "rolling back time" phase of building your report data, look at the code i the Inventory value report.
Hi Everybody and sorry for the delay !
So if I understand, to extract the right FIFO Cost by Transaction/Item, I can not base my queries on CostPrice Field (on SalesLine Table) and I have to work on InventTrans Table. My query is this one :
Correct me if i'm wrong :)
INVENTTRANSORIGIN.REFERENCEID, ReferenceCategory, InventTrans.itemid,
SUM(CASE WHEN STATUSRECEIPT = 2 THEN InventTrans.CostAmountPhysical + InventTrans.CostAmountAdjustment WHEN STATUSRECEIPT = 1 THEN InventTrans.CostAmountPosted + InventTrans.CostAmountAdjustment
WHEN STATUSISSUE = 2 THEN InventTrans.CostAmountPhysical + InventTrans.CostAmountAdjustment WHEN STATUSISSUE = 1 THEN InventTrans.CostAmountPosted + InventTrans.CostAmountAdjustment ELSE 0 END) / IIF(ISNULL(sum(InventTrans.qty), 0) = 0, 1, sum(InventTrans.qty)) as FIFO_10
SUM(InventTrans.Qty) as Qty
FROM InventTrans WITH (NOLOCK)
JOIN INVENTTRANSORIGIN WITH (NOLOCK) ON INVENTTRANSORIGIN.recid = InventTrans.INVENTTRANSORIGIN AND INVENTTRANSORIGIN.DATAAREAID = 'XXXX'
WHERE InventTrans.StatusIssue IN (0, 1, 2) AND InventTrans.StatusReceipt IN (0, 1, 2) AND InventTrans.DataAreaId = 'XXXX'
AND InventTrans.DATEFINANCIAL between '01/10/2019' AND '31/10/2019'
GROUP BY INVENTTRANSORIGIN.REFERENCEID, ReferenceCategory,InventTrans.itemid
PS: How can check on AX the Cost Price by transaction (so I can check if me query is correct) ?Is there a form or a report do do such check ?
You can have a look at the inventory value report and how the item prices are calculated there.
To get the price adjustments incorporated you basically have to add the inventory cost price and the cost price adjustment that you can also find at the inventory transaction level.
I will check the inventory value report as you explained.
When you say : "the inventory transaction level" is it a report ? a Form that can I use to check Transaction/Item cost price updated after stock Recalculation/closure ?
Your question started with 'Sales order' line item cost. The on-hand form / inventory value report etc. is going to give you summarized cost and not by sales order. Above you posted the code is what you need to look at if you still want cost at order line item cost.
Typically, we have seen margin getting reviewed upfront and not afterwards. Suggest that you review your requirement again and look at cost when the report is run and not look at it on order line by order line basis.
My need is to calculate the cost be Transaction/Item. So first i used the query posted, and later i found the CostPrice on SalesLines and when compared them i saw that they don't give the same result. So I posted my question to tell me if the CostPrice on SalesLine is the right cost (i.e updated after stock recalculation/closure).
Reading the answers i understood that is not the case (CostPrice is static on SalesLines). So the question is : my posted query is correct for calculating my Price cost by transaction ? Or i need to create an other post about it ?
The SQL looks to be doing the right thing to me. Just a couple of small points. If the tables in your version of AX 2012 have the Partition field, I think it's good habit to include it in the query (even if you only have one partition). I normally put something like this at the start of the query.
DECLARE @Partition bigint
SELECT @Partition = Par.RECID FROM dbo.PARTITIONS AS Par WHERE Par.PARTITIONKEY = 'initial'
Then you want to add this to your WHERE:
AND InventTrans.Partition = @Partition
and you should add this to your JOIN:
AND INVENTTRANSORIGIN.Partition = InventTrans.Partition
You also have a WHERE on DATEFINANCIAL. For an InventTrans record to have a financial date, it's Issue or Receipt status must = 1 (Sold or Purchased). In which case, you don't need the CostAmountPhysical stuff. You simply won't get any records where you want to use the CostAmountPhysical if you are also insisting that the records should have a financial date.
You can check the cost of an inventory transaction by looking at the Inventory transactions linked to the Order line in AX. For example, from a Sales order line, hit Inventory -> View -> Transactions. On the Inventory transactions form, the Cost amount shown on the Overview tab is the Financial Cost Amount + Adjustment. If you want to see those values separately, or if you want to see the Physical Cost Amount, look on the Update tab.
Thanks GuyUK :)
Business Applications communities