Microsoft Dynamics AX 2012 cost prices

HKFuey
HKFuey used Ask the Experts™
on
I am trying to extract data from AX to use in another system. I need Invoice cost by line but can't see where to get it.

INVENTITEMPRICE table has the costs in I need but I don't know how to match the dates to get the correct cost. E.g. if a sale is done after 01/01/2018 and before 26/04/2018 then the correct cost is 15.72 (See below)

Screenshot of INVENTITEMPRICE table
All I'm trying to do is get a cost price per invoice line but I'm not familiar with the AX data structure, can anyone help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
is this for customer or supplier invoice?

Commented:
also stocked or service item?

Commented:
if Customer invoice, you need to go via InventTrans and link through InventTransOrigin for the salesID
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Damon,

Re: is this for customer or supplier invoice? Customer
Re: also stocked or service item? Stocked
Re: if Customer invoice, you need to go via InventTrans and link through InventTransOrigin for the salesID Will that have line cost?

Thanks

Commented:
Yes it will

Author

Commented:
I can see InventTrans but not InventTransOrigin so I'm confused.

Commented:
are you looking from SQL or AX.

in AX, its in the AOT -> data Dictionary ->tables

boo.png

Commented:
just checked, it is in SQL

sql.png

Author

Commented:
Apologies Damon, I've just been informed they are on version 2009, I assume this makes a difference?

Yes, I am looking from SQL management studio.

Commented:
ahh ok that makes a difference then.

you can go from Inventtrans, but will need to use inventTransId to link to the salesLines

Author

Commented:
These are all my InventTrans tables: -
InventTrans
Commented:
i think you misunderstood me, InventTransId is a field on inventTrans, not a table.

you will find it easier to follow the data from within AX, via the AOT as you can see relations

line.png

Author

Commented:
Thank you, that is really helpful!
(I don't have the AX app just the SQL back end)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial