How can I connect the main account in the main account table to an item with the main account set in the Released Products / Manage Cost / Posting. I can't seem to see that in Personalize and can't make sense of the IDs that I need to follow.
Thanks again. I see the relationship now between the InventItemGroupItem table and the InventPosting table. I think I'm missing the leap between InventPosting and MainAccount. I've tried comparing the Recversion, the LedgerChartofAccountants and the RecID to the LedgerDimension, Recversion and RecID. None seem to link up. Is there other combinations using itemcode, custvendCode or one of those fields? I think, primarily as far as I can tell 99% of our items are using ALL as the item code. Bet it'd be useful if one was able to create a view for this stuff and simplify it. But I guess there isn't much use besides having to get it like I am.
Hi Brian,
I understand the requirement. It will not be a very straight forward sql statement.
The main account reference is stored in the table InventPosting which has some flexible setup possible. Like shown in the last three record here below.
The main account is presented as code, but the table contains a record reference to the table MainAccount. This table has the main account itself.
The item//item group relation is stored in the table InventItemGroupItem.
Logic in AX is first Table, then Group and All is the last search.
Table means there is a specific account setup for an Item itself.
Group is a relation to the item group linked to the product
All is the escape.
Hope this information will help you furter.
I need to have it at the line item level. I need to rebuild it in sql, unfortunately. This is just part of a much larger project. It's more organizationally driven than AX capability driven.
So I need a "select" statement to get it out of there so I can bring that data into the project as well. Thank you for answering. I've been trying to craft it without success. I appreciate the help.
Hi Brian,
It is possible to set one main account for all items or based on an item group. Can you indicate where you need to use the sql statement? The logic to retrieve a main account is within standard AX. Do you need to rebuild the login in sql?
Andre,
Right. Sorry let me clarify my question. I have the main account set up but I need to find the table that contains the data and how it connects to a table that contains the item number. I need to write a sql statement to return the main account whilst taking in the item number.
Hi Brian,
You can setup the main account for your inventory items in menu Inventory and Warehouse management, Setup. Posting, Posting.
There are several types where you need to setup this main account, depending on the features you are using.
e.g.
Sales order: packing slip (might also be filled with a temporary balance account) and Issue
Purchase order: Product receipt (might also be filled with a temporary balance account) and Purchase, inventory receipt
Inventory: Issue and Receipt
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,353
Super User 2024 Season 2
#2
Martin Dráb
228,251
Most Valuable Professional
#3
nmaenpaa
101,148
Share
Report
All responses (
Answers (