Spend reporting by stock items number

Dynamics 365 for Finance and Operations/AX Developer Forum
#1

Hi

I want to be able to extract via ODBC spend by items number. Which tables would people use to do this please? I need to run this for our procurement guys.

Many thanks

#2

Hi,

Spend meaning consumption? Try with InventTrans which contains information about inventory transactions.

#3

Hi - many thanks for your response. It was more the supplier invoices less credits, by item number. The Inventtrans table doesn’t seem to line up with that - even if I only use the transaction type 3, “purchase order”. The report is to show suppliers what we have spent with them and on what

#4

In VendInvoiceTrans you will find item number, quantities, prices and line amounts but no reference to vendor account. Is the tool you are using allowing joining two tables?

#5

I can - it’s essentially SQL

#6

So: VendInvoiceTrans join VendInvoiceJour where VendInvoiceTrans.InvoiceId = VendInvoiceJour.InvoiceId
These two tables contain all data that you need.

#7

That worked perfectly. Apologies more questions but I don’t suppose you have any sort of road map / data dictionary for AX 2009 that shows these type of links?

#8

No. But Microsoft website is very helpful. Look for the table and then go to Relations.

VendInvoiceTrans: msdn.microsoft.com/…/aa625652(v=ax.50).aspx

#9

Again many thanks for your help - that is a fantastic resource