Invoice Item Main Account

(0) Share

Report

Posted on by Community Member

AX 2012 RTM cu3

I need to get the main account that an item on an invoice is tied to. I have invoice number, item id, voucher, project id and I can get line number I'm sure. But I can't find a way to tie it back to the General Journal tables so I can get the main account and accounting currency amount. I'd prefer to use a standard SQL statement because I need this data to hook to another process so I can't use AX components (I don't think). Thanks

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

André Arnaud de Cal... 301,037 Super User 2025 Season 2 on at

Like (0)

Report

Hi Brian,

If you are looking at posted transactions, you can use the table InventTransPosting to get the actual used main account. For each inventory transaction it has a record for the physical and the financial posting. It contains the main account and offset account used.

You can retrieve the appropriate record by using the InventTransOrigin link. This table is linked with the InventTransId on the invoice line.

If you are using the Item, TransDate, Voucher directly, you could retrieve the wrong records if your invoice contains the item multiple times. But the Main account should be the same, but you never know...

Was this reply helpful? Yes No
lally 8 on at

Like (0)

Report

Hi Andre ,

Your explanation would help to me also .

I have question when user posted any item transactions using Purchase order / Sales order / Transfer order / Item journals will enter the cost price or sales price .

I have checked in the InventTrans table regarding the costpirce or sales price against the particular document, but did not find such cost related infomation.

Could share me the table name of inventory cost related data ?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Has anyone built a true ERD document? I know MS has one but it is incomplete

Was this reply helpful? Yes No
lally 8 on at

Like (0)

Report

Hi Andre ,

Your explanation would help to me also .

I have question when user posted any item transactions using Purchase order / Sales order / Transfer order / Item journals will enter the cost price or sales price .

I have checked in the InventTrans table regarding the costpirce or sales price against the particular document, but did not find such cost related infomation.

Could share me the table name of inventory cost related data ?

Was this reply helpful? Yes No
André Arnaud de Cal... 301,037 Super User 2025 Season 2 on at

Like (0)

Report

Hi Lally,

Can you create a separate thread and provide also more information? What are you business wise exaclty looking for?

Was this reply helpful? Yes No