When we came into work the first day of the year, we were unable to hard allocate to some part numbers. We would get a vague error that just said “Cannot allocate at this time”.
Support has not been able to help so far.
We stumbled upon two tables, PartBinDeferred and PartQtyDeferred, that contained records created on the last work day of the year for the same part numbers and bins that are causing us an issue.
It looks like they were all created at the end of the year during the shipping process.
If we delete one of the records in our test database the error goes away.
I suspect that something happened during the shipping process, on this day, and these tables did not get flushed out.
Does anyone know what these tables are used for? I cannot find a table description.
JOE ROJAS
Epicor Applications Manager
VENTION MEDICAL + DESIGN & DEVELOPMENT
Advancing Your Innovations For Health
This communication may contain information that is confidential, proprietary or exempt from disclosure, and is intended only for the use of the individual and/or entity it is addressed to. If you are not the intended recipient, please note that any other dissemination, distribution, use or copying of this communication is strictly prohibited. If you have received this message in error, please notify the sender immediately by telephone or by return e-mail, and delete this information from your computer.
aidacra
(Nathan your friendly neighborhood Support Engineer)
2
SELF-PROMOTION:
During my “Case of the Unusual” at Insights 2016, one of the examples I used was a Deferred table and the various ways to triage issues with it. So, those on the fence on Insights 2017, come on down! Additionally, I’ll be discussing some of my favorite things about 10.1 at the MN EUG this week, and this will be one of the topics I’ll be speaking to.
Deferred tables are there to eliminate contention. When you have processes like MRP with multiple threads (processors/schedulers) and what happens if two of those both need to change the PartQty? Locking/blocking/deadlocks <-- fun fact: deadlocks are almost 100% software issues assuming of course that there aren’t any external processes in play. So, what we did instead is for processes that have current threads trying to update the same records (MRP, Posting) we created these deferred tables to insert a new record into the deferred table instead of updating the actual record directly to avoid the contention. After the transaction is complete, the actual record is updated - so in practice, it isn’t slower. There is some additional logic that says that after a period of time if that record is in the deferred table too long (~15 minutes I believe) it will process it immediately like if the appserver went down in the middle of processing - this is triggered from the taskagent.
Thanks for the information here Nathan - I attended your Case of the Unusual at Insights 2016 but I think we didn’t quite make it to the Deferred table case. At any rate, I have the PDF and read through it and it was very useful, and even more useful is the information you have provided here.
May I ask another question? We have had previous adventures with these Deferred tables, specifically PartBinDeferred (6810586MPS if you are interested). In summary, we had some parts refuse to let us do Inventory Transactions on them, and got the advice from support (eventually) to run a data fix to clear out the Erp.PartBinDeferred table when/if we run into this issue from time to time.
Now, we are in the process of setting up for testing an 10.1.500.14 environment, which we’ll be upgrading to from E10.0.700.4. Long story as short as possible - we’re experiencing the ol’ “CPU constantly running at 50%+ when Task Agent is created and running” and I think I have figured out by looking at System Log (triggers) that for some reason our E10.0 database has about 1.9 Million records in Erp.PartQtyDeferred - dating all the way back to 2015 - and as soon as we get the task agent going in the upgraded E10.1.500 environment, it seems to start chewing through those - the COUNT(*) shows record count in that table is slowly diminishing.
Here is what floods ServerLog.txt when trigger logging is enabled:
So my question, finally (sorry), is this: Is it safe to delete out these records from PartQtyDeferred like support had me do for PartBinDeferred? Or is it safer to let E10.1.500 do it’s thing with this table? If you think I should open a support ticket, I understand. Either way, thanks for sharing.
aidacra
(Nathan your friendly neighborhood Support Engineer)
4
No, it is not safe to remove those records without careful review. Please submit a ticket to Support and our production team will triage.