Unable to reconcile Item quantities

(0) Share

Report

Posted on by TracySam

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

All responses (9)

Answers (0)

Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

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 185 on at

Like (0)

Report

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 Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

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 185 on at

Like (0)

Report

RE: Unable to reconcile Item quantities

Hi Beat,

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

Thanks
TracySam 185 on at

Like (0)

Report

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 5,701 on at

Like (0)

Report

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 65,271 on at

Like (0)

Report

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 Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

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 5,985 on at

Like (0)

Report

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