Relation between InventTrans and InventSettlement Tables

(0) Share

Report

Posted on by Community Member

Dear All,

I am trying to write a SQL query to find the cost amount posted and Cost amount adjusted for every item from AX database.

In my query, I am using InventTrans, InventTransOrigin and InventSettlement tables. Relation between InventTrans table and InventSettlement table is based on TransRecid in InventSettlement and recid in InventTrans table. But for each line in InventTrans, I am getting multiple lines in InventSettlement. Should I apply any more filter on InventSettlement table.

Regards

Suneel

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (2)

Sort by

Hariharans87 36 on at

Like (0)

Report
Copy link

Link copied!

Please make sure that have you considered the dataareaid column in the join part.

Please also share your code.

Was this reply helpful? Yes No
Guy Terry 29,003 Moderator on at

Like (0)

Report
Copy link

Link copied!

I don't think you need to use InventSettlement. Does the SQL in my comment on this thread help?

community.dynamics.com/.../163560

Was this reply helpful? Yes No
Verified answer

5400 7,162 on at

Like (0)

Report
Copy link

Link copied!

You have to decide first, you want to calculate cost amount adjusted only after invoice or packing slip also. if you are doing packing slip and invoice same lot without partial it will create only one line in inventTrans but InventSettlement will get populated with 2 lines for same . One for adjusted which is assoiciated invoice voucher with inventtrans and another is physical which is associated with physical voucher of invent trans.

Inventrans also have field costAmount adjusted, first tally this value with respect inventsettlementtable and proceed further.

One field is there invnetSettlement called settle model which can take into your consideration and build 1:1 relation between inventtrans and inventsettlement.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thank you for your reply.

I am trying to get the Inventory Value by SQL query to match with the Inventory value cube.

I think Cube considers InventSettlement table to get the adjustments.

Was this reply helpful? Yes No
Hariharans87 36 on at

Like (0)

Report
Copy link

Link copied!

Please let me know that what inventory cube measure you are referring, we can find the measure pointing table field and focus on that.

Note: Cube don't have updated value. Because once it is processed, you can see the updated value.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Hariharan, I am aware of the cube process. I am referring to Amount field in "Inventory Value" Measure group. InventValueCube is the view.

Was this reply helpful? Yes No
Verified answer

Brandon Wiese 17,790 on at

Like (0)

Report
Copy link

Link copied!

The InventTrans table has fields for both CostAmountPosted and CostAmountAdjustment, which when added together arrive at the PostedValue found on the associated InventSum record (across all associated InventTrans records).

InventSettlement can and often will have multiple records for a single InventTrans record. It is a child table with a 1:n relationship. Each time any cost adjustment is made that affects a given InventTrans record, a new InventSettlement record is created as it simultaneously adjusts either the CostAmountPhysical or CostAmountAdjustment fields on the InventTrans record.

If you're interested in the current Inventory value amount, you can arrive at this from InventSum records or InventTrans records alone. If you're interest in the Inventory value from a point in the past, you must consider InventSettlement records as part of the "rolling back time" phase of building your report data, just as the Inventory value report in AX itself must do.

Was this reply helpful? Yes No
Suggested answer

Aparisi82 2,190 on at

Like (0)

Report
Copy link

Link copied!

Dear Suneel Putta

actually this is something I have learned today by analysing the std report Item posting by account.

This applies only if you are using Standard Cost model for your inventory.

The reason why you see multiple records for the same inventrans recid is because the item of that transaction has had its std cost changed multiple times over the time.

Anytime you activate a new std cost , the system will adjust the cost of each posted transaction since day 1 writing the adjustments into the inventsettlement table.

if the new price activated does not change from the previous period there is no adjustment.

You should sum all the adjustments by transrecid if you are doing a stock reconciliation.

Was this reply helpful? Yes No