xTuple.com xTupleU Blog & News Customer Support

Relocateinventory function change

In our test environment, we have just upgraded our xTuple database from 3.8.3 all the way to 4.4.0. We noticed a minor change in the relocateInventory SQL function in the public schema. We have some custom software that calls this relocateinventory function. When we call the function to relocate something to a different location, we get an error stating that we violated a unique db constraint due to a duplicate ls id, when in reality, the ls number is actually unique. When we try to relocate the same ls numbers using the inventory -> transactions -> relocate inventory function in xtuple, we get the same error message, When we compared the relocate inventory function from the 3.8.3 database to 4.4.0, we noticed this was missing about halfway through the function:

DELETE FROM itemloc
WHERE ( (itemloc_qty=0)
AND (itemloc_id=pSourceItemlocid) );

When we add this into the function, ls numbers are able to be relocated just fine. We tried out the upgrade process a few more times to see if maybe we got unlucky during the initial upgrade, but saw that every time, this function is changed, and becomes “broken”. I scoured through the release notes from 3.8.3 all the way through to 4.4.0, and have not seen anything referencing this change, or this function in general. I was just wondering if anyone knew why this function is different in the newer version, and also, where along the lines this changed?

Thanks in advance!

@ryguy,

This is certainly unexpected. The DELETE statement is still in the current source code going back to release 4.4.1, surviving the jump between code repositories that occurred around that time, and going back in an apparently unbroken chain to the original fix for bug 9385.

I also looked at the history of Updater packages and couldn’t find an explanation for your observed results there. Every one that updates this function appears to have this DELETE in it.

Gil