Spend reporting by stock items number

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