Recently we were experiencing extreme slowness when it came to DMT loads (specifically Bill of Materials). I submitted a ticket with Epicor and the resolution was to run a SQL Cleanup script which looked at all Erp.ECORev records where CheckedOut = 0, then would join to all ECO* tables and delete the related records (ECOMtl, ECOOpr, ECOOpDtl, etc.). After running this cleanup script we noticed incredible speed increases (50k records went from a couple days to 2-3 hours)
I would have assumed that after an engineer is done using an ECO group and they close/delete that ECO group that the business logic would go through and clean up the related records but it appears it does not work that way. I am wondering if anyone has a process to cleanup these ECO tables outside of running Epicor’s cleanup script?
My understanding of the ECO /Engineer Workbench is that the ECO gets checked in and approved and this process updates the Erp.PartRev table ( possibly other tables also). The ECO can then be closed. This effectivly clears the Eng Workbench. We do not delete the ECO’s as it is a record of what changes have occurred. I am in the middle of Developing an ECO dashboard and in this process I created some UD fields on the ECOGroup table and the first time those fields were edited, it locked the ECO ( prob Checked out = 1, no SQL access as we are in the cloud and I didn’t chase it down with a BAQ) by the person that edited the ECO.We don’t use that anymore… I don’t know if this sheds any light on things or just muddied the water
The issue we’re running into is while the ECO is checked out and any changes are made, they go into the “temporary” ECO Tables (Erp.ECORev, Erp.ECOOpr, Erp.ECOMtl, etc) Then once checked in the changes are “merged” with the Part tables (Erp.PartRev, Erp.PartOpr, Erp.PartMtl, etc). After this I believe those ECO Table records are stale, and once a new ECO Group is created, new ECO Tables records are created.
My assumption is that once the ECO Group was deleted the related records would also get deleted because there is no purpose for them. As in our case is slowed our BOM DMT;s to 10 records per minute. I still have the case open with Epicor, just wanted to reach out to see if anyone else experienced the same or had a better work around the straight T-SQL Deletes
Yeah that was my thought process as well. A co-worker of mine said that back in E9 when they would delete the ECO Group all related ECO Table information was cleaned up as well. Hopefully there comes a better process out of this!
I never gave it much thought as we only have one person that does ECO’s (me), and I use my username (the quick ECO group), for every change.
I always assumed it was like AR Invoice groups. The GroupID ties together all the records being worked on at one time. Once an AR Invoice group is posted, the group is “cleared out”. Nothing keeps you from reusing the same group name again.
And there’s no real connection between the items in the current group (invoices in the AR Group, or BOMs in the ECO group), and items with that same groupID, from a prior time.
One big diff between AR groups and ECO Groups, is that in AR all invoices in the group have to be posted at the same time. With ECO groups you can add (checkout), and remove (check back in) BOM’s individually.
I did a quick test. I used to make parts all day under the same ECO group.
I just deleted my ECO group and all 5000+ ECORev records stayed.
I don’t know if any purge would happen on some sort of scheduled process, but it did not happen immediately.
Ben
We’re currently on 10.1.600.11 but I also tried in 10.2.500.7 and the same results that your getting happened in both versions. The only way we found this is because we were getting 10-12 RPMs on BOM DMT loads.
So … ECOMtl records persist when the BOM in the ECOGroup is checked back in. Are these completely ignored the next time that BOM (same rev) is checked out to that ECO GroupID?
Imagine the following
BOM WID-001, rev 1 is checked out to ECO Group ID ckrusen (my username)
Changes made to the BOM affect the ECOMtl table. This table use GroupID(ckrusen), PartNum (WID-001), and Rev (1), (among others) as the keys.
BOM WID-001, rev 1 is checked back in. The ECOMtl record persists.
BOM WID-001, rev 1 is checked out to a different ECO Group ID (manager).
Changes are made and the BOM is checked back in.
BOM WID-001, rev 1 is checked out to ECO Group ID ckrusen. There are ECOMtl records for this GroupID/PartNum/Rev, from “two revs ago”.
Are these overwritten with then new mtls from when group manager was checked back in?
What if the first instance of group ckrusen had MtlSeq’s that were removed by the second group (manager). Would the second use of group ckrusen now add these back to the BOM?
ECO tables are part of change history for us, including ECOOpr and ECOMtl. They tell us what the method looked like at that point in time. We only reuse a handful of ECO groups now, to avoid overwriting that.
If you want a truly audit-able history, you should make it so that an GroupID cannot be reused. At least not for when ECOxxx records exist with the same ID for the same part and Rev.
Epicor Support replied back saying they checked with development and the only way to keep this table clean is to use their “fix” which is a cursor that joins off ECORev where CheckedOut = 0, and deletes related records in the other ECO Tables.
Just an FYI in case someone else runs into something similar!
Joe, we are seeing the same thing and I’ve opened up a ticket with Epicor. They are being a bit resistant to giving us a purge script. I explained we have over 246K ECOPartRev records and our DMTs take a long time.
I said I didn’t understand why they keep these records, and they said for traceability, but honestly it’s traceability to the last change only, so it realy doesn’t mean much. This is what we use the Approval Change description for. Hopefully the DMT team will be a bit more open to letting us purge the tables.