Finance | Project Operations, Human Resources, ...

How to get Actual cost from Landed cost Voyages (Costs inquiry) via SQL

(3) Share

Report

Posted on by KK-12030320-0

Hi,

In Dynamics 365 Finance, I want to retrieve via SQL the "Actual cost" value displayed in the following menu

Landed cost > Voyages > All voyages > Costs inquiry

This value is shown per item and cost type (Freight, Duty, Handling charges).

Which table and field store this Actual cost,
and how can it be retrieved using SQL?

Thank you.

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (3)

Answers (0)

Martin Dráb 238,769 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Moved from Integration, Dataverse, and general topics forum to Finance | Project Operations, Human Resources, AX, GP, SL forum.

First of all, find out details of the form control by right-clicking it and clicking on the form name. The Form information dialog can give you details such as which table field the control is bound to.

Was this reply helpful? Yes No
KK-12030320-0 64 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

Thanks for the suggestion.

I checked the form information, and I can see that the data is loaded into the ITMTmpCostSum table.
However, this table has a table type of InMemory, so the data exists only in memory during runtime and cannot be queried directly via SQL.

We are looking for a way to retrieve the Actual cost values displayed on this form from a persistent SQL table, if possible.

Thank you,

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 238,769 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Check out ITMCostSum.calcActualCost(); it may be what you're looking for:

protected Amount calcActualCost(ITMCostTrans _shipCostTrans, ITMLine _shipLine) { ITMCostInvoiceTrans shipCostInvoiceTrans; ITMCostAllocateTrans shipCostAllocateTrans; select sum(AllocatedAmount) from shipCostAllocateTrans where shipCostAllocateTrans.PurchLineRefRecId == _shipLine.RefRecId && shipCostAllocateTrans.PurchRefTableId == _shipLine.RefTableId && shipCostAllocateTrans.PurchLineDataArea == _shipLine.ShipDataArea exists join shipCostInvoiceTrans where shipCostInvoiceTrans.TableId == shipCostAllocateTrans.RefTableId && shipCostInvoiceTrans.RecId == shipCostAllocateTrans.RefRecId && shipCostInvoiceTrans.RefTableId == _shipCostTrans.TableId && shipCostInvoiceTrans.RefRecId == _shipCostTrans.RecId; return shipCostAllocateTrans.AllocatedAmount; }

Note that when you know the field name, you can use Find references in Visual Studio to find out where the field is used.

Was this reply helpful? Yes No

Join the conversation

Community site session details

How to get Actual cost from Landed cost Voyages (Costs inquiry) via SQL

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2025 Community Spotlights

Congratulations to the February Top 10 Community Leaders

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

How to get Actual cost from Landed cost Voyages (Costs inquiry) via SQL

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2025 Community Spotlights

Congratulations to the February Top 10 Community Leaders

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates