InventSum Table does not reflect the actual transactions in InventTrans table

(0) Share

Report

Posted on by maram fraij

Dears,

I have just faced the a case where the transactions in InventTrans table were not all reflected to InventSum so that the calculated Physical Inventory value in the Oh-hand screen is not correct.

Would you please help me what is the causing for such issue, and how can it be fixed ????

*This post is locked for comments

I have the same question (0)

All responses (19)

Answers (1)

Suggested answer

Dick Wenning 8,705 Moderator on at

Like (0)

Report

it is important to know what your AX version is.

next did you make changes to the inventmov* and derived classes.

next there is an iron triangle between InventTable, InventSum and InventDim

the other triangle is between InventTable, InventTrans and InventDim.

do you have reservations, those are not part of the physical inventory

I have seen blogs about rebuilding inventSum, I have not done those in the past

mvpdynamicsax.blogspot.nl/.../recalculate-inventsum.html

Was this reply helpful? Yes No
maram fraij on at

Like (0)

Report

Thank you for your reply.

I executed the class for certain item , the value of "Physical Inventory" appearing in the On-hand Inventory screen is updated . but there still difference between the sum of the Quantities appearing in the transactions screen and the value of physical inventory in the On-hand screen.

Please, advice what makes such difference ????

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

Like (0)

Report

Hi Maram,

As Dick wrote, the differences might be caused by customizations. Note that the InventTrans table contains records of open orders as well. You should only take the transactions where the physical date is filled. Can this be a part of your differences?

AX also has a Consistency check. Depending on your AX version you can find in the periodic menu of 'System administration' or other menus. You can set the parameter Check/Fix to the value 'Fix' to have the InventSum being rebuild. Actually this consistency check uses the Logic as mentioned in the blog Dick shared to you.

Without futher details it is almost impossible to explain your problem.

Was this reply helpful? Yes No
maram fraij on at

Like (0)

Report

Dears,

The InventSum table has was rebuilt using consistency check / fix option. All incorrect balances values were resolved, but now the problem appears again "after rebuilding process".

would you please advise me what is the possible reason for such problem , is there any way to track this problem instead of doing regular rebuilding for inventsum?

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

Like (0)

Report

Hi Maram,

Can you check if you have any customizations in your environment? Are some sessions killed sometimes unexpected. Which version of AX are you exactly using? You can try to find out if this is a known bug; probably there is a hotfix available.

Can you see some patterns in the problems? Too low, too high? After e.g. posting sales orders? ...?

I have not seen this before, other than caused by customizations. (until today...)

Was this reply helpful? Yes No
maram fraij on at

Like (0)

Report

I use AX 2012.

During the discussion with functional team, they mentioned no customizations applied for this part but the system was setup such that it allows items balance to become negative. Do you think this may affect inventsum data ?

Just as an additional advice, I ran recalculate class for certain item with check option to get where did the errors occur, do you have any idea how can I use the output resulted from this check to determine in which transactions the problem occured?

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

Like (0)

Report

Hi Maram,

normally there should be no inventsum mismatch in your system. Not even when you allow for negative stock. Normally not, but there must be a reason why it happens. At my customers I normally advice to NOT allow for negative stock. In rare cases we had to allow it.

When you run the check and you see some differences there is no output to check which transactions caused the problem. This is because the check/fix program summarizes the inventory transactions. When you know the date of your last fix, only the new transactions created after the fix could have caused the problem. Looking at the new transactions you might find some information which allows you a reproducable scenario. If you know more, please let us know.

Was this reply helpful? Yes No
maram fraij on at

Like (0)

Report

Hi,

Still I have the same problem after run the consistency check again.

I have the following notes regarding this issue:

-First one: A customized field called Pion_ProductName was added to InventSum table and the below update was added to the method(\Classes\InventUpdateOnhand\insertInventSum) to fill this field

The update is :

inventSumNew.Pion_ProductName=InventTable::find(inventSumNew.ItemId ).productName(CompanyInfo::languageId());

I think such update does not affect the InventSum balences

-Second note: the company have 8 different sites and each site has its own application and DB servers. there is a DB replication between the 8 sites to get one centralized and identical DB in all sites.

Actually I am not included in the replication process, I do not know how it deals with InventSum & InventTrans tables but I think that the problem may come from this external DB process not internally from Dynamics AX .

Please advise regarding these two notes.

Thank you very much.

Was this reply helpful? Yes No
maram fraij on at

Like (0)

Report

Also, I have the following note:

I checked the structure of inventSum table in a new installed dynamics ax environment , I found the folliwng constraints:

CONSTRAINT [I_174ITEMDIMIDX] PRIMARY KEY CLUSTERED

(

[DATAAREAID] ASC,

[ITEMID] ASC,

[INVENTDIMID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

When I checked the structure of the same table in the enviroment where the problem faced, I found the folliwng constraints:

CONSTRAINT [I_174ITEMDIMIDX] PRIMARY KEY CLUSTERED

(

[PARTITION] ASC,

[DATAAREAID] ASC,

[ITEMID] ASC,

[INVENTDIMID] ASC,

[RECID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

PARTITION and RECID are included in the primary key

please, advice if this may effect the balances.

Was this reply helpful? Yes No
Verified answer

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

Like (0)

Report

Hi Maram,

Can you confirm in simple words if the sites have their transactions in their own database? Are transactions created in 8 differeent databases and synchronized around all databases? Are all sites part of only one legal entity?

Are the constraints found somewhere in SQL?

Was this reply helpful? Yes No