If you have a stock value without quantity then the records in table InventTrans are not correct. This is not something you can solve by making financial or inventory transactions through the application. The records will have to be corrected on database level. The first thing you should do is run a “Consistency Check”. That is a program that you can find in Basic> Periodic > “Consistency Check”. Run the program with option “Check” first. That will produce a report with any inconsistencies that are found. This might run quite a while and the error should show up on the report. Check the report and then run the program again in “Fix Error” mode to correct the inconsistencies that were found. (Make sure you have a database backup before running).
Most likely this will solve the problem. If not then someone has to check the records in the InvenTrans table and make adjustments manually if necessary. InventTrans is a quite complicated table and any corrections should only be made by someone with very in-depth knowledge. I’ve seen experienced consultants mess that up.
As there is no guarantee that reversing the database update will solve the issue, I was thinking of phasing out the current part no - and use new part no for future transaction.
Not sure though what to do with the existing transactions that use the old part no - from AX point of view.
If you have other suggestions, please share with us.
I’ve seen this situation before: zero physical qty, with financial value either possitive and negative. What we found was caused becouse in the inventory dimension group of the item, at the whse level, physical inventory was checked and financial not… so financial adjustments are made at the site level only
I met this problem before, and my situation was due to some transactions have wrong markings so they cannot be settled.
You can try to find the item which hav no QTY but hav value. Check its transactions, to see if there are any trans which hav not got “settled qty” (but hav financial value). and try to find out why they cannot be settled.
If that didn’t fix the problem then it’s unfortunately not really possible to give you much more advice without seeing the exact transaction details. You said that the problem has been caused by a consultant who probably made mistakes when he/she manually changed values in the database. Most likely he changed some values and forgot others. Someone will have to look at the tables now and check the inventory transactions and financial transactions for that purchase order to figure out what was done wrong and how to correct it.
To get to the root of the problem these cases usually require some extensive “detective work” by someone with technical knowledge about database structure and business knowledge about inventory and finance - especially if users already tried to make corrections through the application. It would help if you still have contact to the consultant and could ask him what exactly he changed. He/she might also offer further assistance. Every consultant overlooks something sometimes - especially when it comes to tricky “back-door” fixes. If my clients make me aware of something that went wrong because of an oversight on my part then I fix it without further charge. It doesn’t hurt to ask.
If you choose not to go through the effort of fixing the original problem then make sure that at least the financial postings for the purchase order were correct. Is it just the inventory value in the inventory tables that is wrong or is the dollar amount on your inventory account wrong too? (Was it a high value item or are we talking just about a few cents?). As long as the general ledger account values are correct discontinuing the item might be an option.
I realize that my answer is not helping you much. Sorry for that! I wouldn’t want to give further advice though without seeing the details.
Kein Problem, your comment confirmed some of my consideration on the alternative solution as it is risky to fix the database directly - we are also running out of time.
The GL and inventory value seem to be tallied, as user adjusted GL value with manual journal.
But the problem pops up with every closing - and it is accumulative.
Is it possible to know the value of individual stock at GL? Isn’t it just a lump sum figure at one GL account - like one GL account for raw material?
It is not a big ticket item, but from accounting point of view any discrepancy should be rectified.
We have a similar issue and it was first noticed when reporting InventSum in detail (we are on DAX 4.0 SP2 too). Our IT partners suggested that the reason was the same as Héctor Eduardo Cazot answer above. And it makes sense and it at least explained the transactions historically.
But then this month, it sort of happened “before our eyes” and I am left staring at a transaction which I cannot seem to explain. Instead of hoarding this space I have posted it here http://dynamicsuser.net/forums/p/45635/230724.aspx#230724 with a trackback to this OP so you will have a solution should I receive one.
Hi Hector, I have a related question. I ticked physical/financial for site and warehouse, location is not ticked. raw material stored in location A, finished goods at location B. So when a production order and material consumed, the raw materials are deducted but it shows that it is deducted in the Location B. so I have location with original quantity and location B with -ive quantity. I think, this is causing issue with calculating the realized cost price. the cost price shown in both the locations are different!
In the production order, the realized consumption price is much lower than the estimate and it doesn’t seem to be synch with the cost price of that item. Appreciate your thought on this.