Finance | Project Operations, Human Resources, ...

costPrice in SalesLine is updated after Stock Recalculation/Closure ?

(0) Share

Report

Posted on by JauB

352

Hi Everybody

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.

I have the same question (0)

All responses (13)

Answers (0)

Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: costPrice in SalesLine is updated after Stock Recalculation/Closure ?

Hi JauB,

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:

fedotenko.info/.../

Best regards,

Ludwig

Was this reply helpful? Yes No
Suggested answer

Satish Panwar 14,671 Moderator on at

Like (0)

Report

RE: costPrice in SalesLine is updated after Stock Recalculation/Closure ?

Hi JauB,

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.

Was this reply helpful? Yes No
Suggested answer

Pedro Tornich 955 on at

Like (0)

Report

RE: costPrice in SalesLine is updated after Stock Recalculation/Closure ?

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.

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: costPrice in SalesLine is updated after Stock Recalculation/Closure ?

Hello JauB,

Can you let us know if your question has been answered or whether additional support is required?

Many thanks and best regards,

Ludwig

Was this reply helpful? Yes No
Suggested answer

guk1964 10,888 on at

Like (0)

Report

RE: costPrice in SalesLine is updated after Stock Recalculation/Closure ?

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.

Was this reply helpful? Yes No
JauB 352 on at

Like (0)

Report
RE: costPrice in SalesLine is updated after Stock Recalculation/Closure ?

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 :)

SELECT 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 ?

Thanks.

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: costPrice in SalesLine is updated after Stock Recalculation/Closure ?

Hi JauB,

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.

Best regards,

Ludwig

Was this reply helpful? Yes No
JauB 352 on at

Like (0)

Report

RE: costPrice in SalesLine is updated after Stock Recalculation/Closure ?

Hi Ludwig,

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 ?

Was this reply helpful? Yes No
Suggested answer

Satish Panwar 14,671 Moderator on at

Like (0)

Report

RE: costPrice in SalesLine is updated after Stock Recalculation/Closure ?

Hi JauB,

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.

Was this reply helpful? Yes No
JauB 352 on at

Like (0)

Report

RE: costPrice in SalesLine is updated after Stock Recalculation/Closure ?

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 ?

Was this reply helpful? Yes No