Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

Unable to reconcile Item quantities

Posted on by 185

Hi,

I have a client whose Inventory Reconcile is not working.  When the reconcile is run, a message is received "Item was in use and could not be reconciled".  I have cleared the activity, sy00800, sy00801 and temp tables as well as restarting the SQL service.  I have also checked that there are no allocated quantities for the items.  If I try to update the item quantities in SQL by updating the iv00102 table, I receive the following message:

Msg 2601, Level 14, State 1, Procedure IFC_tuidIVItem, Line 38
Cannot insert duplicate key row in object 'dbo.IVItem' with unique index 'PKidfItemID'. The duplicate key value is (TL000007 , <NULL>).
The statement has been terminated.

There are no duplicate item numbers in the system.

I have also used the SQL Maintenance and selected all the boxes (drop procedure, create procedure etc.) and then ran check links and reconcile.  I tried the PSTL IV Reconcile but this did not fix it either.

Help please !

*This post is locked for comments

  • Beat Bucher  GP Geek  GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Unable to reconcile Item quantities

    Tracy,

    Let us know when the partner / vendor has fixed the issue, what was the cause and the solution. it may help other with a similar problem in the future.

    Thank you.

  • TracySam Profile Picture
    TracySam 185 on at
    RE: Unable to reconcile Item quantities

    Hi Leslie,

    I checked the table with the issue but I do not see where a duplicate would be created.  However, since I have identified that it a third party table, I have contacted the vendor for this product in order to resolve the issue.

    Thanks

    Tracy

  • Suggested answer
    Beat Bucher  GP Geek  GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Unable to reconcile Item quantities

    Tracy,

    What Tim and I tried to explain is that if there is a 3rd-party product playing in the game, the whole reconcile in GP can be rendered useless.. no matter what.

    You have to figure out if your system was altered in any other way by some customization or other plugin to your inventory process.

    PS: your last answer just crossed mine.. :-) so knowing there is a 3rd-party product in the game, means you'll have to talk to your partner and/or the ISV provider of the solution.. I don't know nothing about Workplace. sorry.

  • TracySam Profile Picture
    TracySam 185 on at
    RE: Unable to reconcile Item quantities

    Hi Beat,

    You are right, the IvItem table is from a third party product called Workplace.

    Thanks

  • TracySam Profile Picture
    TracySam 185 on at
    RE: Unable to reconcile Item quantities

    The quantities are not showing as In Use and if I click on Allocated, there is nothing there.

  • Tim Wappat Profile Picture
    Tim Wappat 5,701 on at
    RE: Unable to reconcile Item quantities

    As Beat said, the stored procedure IFC_tuidIVItem sounds like a stored procedure from a 3rd party product. 

    As you are getting the error even from SQL sounds like there are some SQL triggers set up on your customer's table and this is probably the upsetting item reconcile too.

    Have a look to see if you can figure out whose product it might be (look in the SET file for example), ask the customer then see if they can debug the issue.

    Tim

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: Unable to reconcile Item quantities

    Hi Tracy,

    I think what the system is trying to tell you is that you are trying to insert an item ID that already exists. You won't find any duplicates in the table, that's what the PK prevents. It's been my experience that PK errors are always correct, do you know which item is generating the error? Is TL000007 an item number of yours?

    Leslie

  • Beat Bucher  GP Geek  GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Unable to reconcile Item quantities

    Hi Tracy,

    Do you use any 3rd party ISV product linked to your Inventory management ? the procedure IFC_tuidIVItem doens't ring a bell to me...

  • Suggested answer
    KirkLivermont Profile Picture
    KirkLivermont 5,985 on at
    RE: Unable to reconcile Item quantities

    Hi Tracy,

    If you take a look at the item quantities and sites window is their a value in the in use field? If so transfer the inventory back to on hand and try the reconcile again.

    Best regards,

    Kirk

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,113 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,918 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans