Our DB grows about 20GB per year (about 150 GB now, 5.5 years of history), so we decided to start purging our PartTran and Journal Details tables with a 3 year cut-off. We thought we tested everything (and the purged tables are backed up externally), but a week after our first purge, AR let us know they couldn’t post cash receipts for our 4 and 5 year term invoices. The cash receipts were missing the credit side of the transaction. It seems Epicor uses the purged TranGLC records to find the invoice’s AR account for posting.
We got a data fix to add back the missing TranGLC records, but it was manually going through each invoice one by one, rather than adding it back in bulk for millions of records.
I’m betting Epicor development will state “Working As Designed” on our case, but that pretty much makes sure we won’t be able to use the database purge and summarize due to open long term invoices. I’ve purged 2015 & 2016, but will not continue with 2017 until this can be resolved.
Here’s a snapshot from December. We’ve purged our SysActivityLog table already, and change log is purged monthly with a 2 year cut-off:
I’ve been curious about running a Purge on some of the tables in our E10 database too. The last time we performed a purge was when we were in E9 Progress before we upgraded to E10 SQL. Thankfully it didn’t cause any issues for us but I am shocked as to how many times users want to access that purged Data. So sadly I’ve had to keep a copy of our E9 database out there for occasional access. Like our AS400 something might accidentally happen to that database some day…shhhh…LOL (Just kidding…)
But right now our DB is slightly over 400GB. When you perform your purges do you actually see the DB size go down or shrink? I know shrinking the DB is not advised at all but I was wondering how the Epicor E10 Purge worked?
I need to look at our table sizes to see which Tables are the largest and which ones might be able to be purged.
I can’t believe AR couldn’t post cash receipts after the purge of the TranGLC, yikes.
I know there are things you can do to separate out tables in SQL and create Archive Tables but I’m not sure if Epicor would support this kind of thing or not? Following this thread to see if any others are purging some of their tables too…
Here are the steps to recreate it in the Epicor EDU database:
Find an AR invoice that’s still open with a balance. In the Epicor EDU environment, you can use AR Inv#10014 (CustID DALTON) , created on 3/27/2019.
Database Purge & Summarize > Check GL Journal Details & enter a cut-off date (3/31/2019) that’s newer than the invoice date from step 1. Wait for the purge to finish. I created a summary journal (SUM) to summarize into.
Cash Receipt Entry > New Group ‘AES’, Checking account ‘Main’, New Invoice Payment, enter check#1, $100, and CustID from step 1. Select the invoice 10014 & apply. Then Actions > Print Edit List. Notice that it’s unbalanced and missing the credit. If you tried to post, it’d go to the review journal since it’s unbalanced.
Whoa! You guys are killing us! We’ve had so many complaints about slow performance, that we’re going at it from every direction possible (we pass the PDT with flying colors).
We are backing up the PartTran, TranGLC, and JrnlDtl tables externally, just for that. Most of what we are purging is transactional data that isn’t important any more. The dept managers want to know what happened last year, or maybe 2 years ago. Rarely do they need 3 years, but that what we used for the cut-off. 4-6 years is available externally in case we need the full history. Our business evolves so quickly that 4-6 year data is not really relevant any more.
Epicor indeed said that the purge of journal details is working as designed, and to make sure you set a cut-off date older than any open invoices (would have been nice if the KB article on purge, or help documentation stated that).
I submitted a request to the help folks, who usually update documentation quickly, so hopefully it’ll prevent someone else from the same pain, until Epicors fixes Journal Details purge.
Database Purge - Exclude Open Invoices from Journal Details Purge
Exclude open invoices from purged journal details. Epicor uses journal details to determine the GL account when applying cash receipts. Even though the journal details purge doesn’t touch invoices, it still breaks cash receipts.
If your company has invoices with multi-year terms (i.e. 60 month terms), invoices remain open for many years, preventing you from purging journal details (if you don’t want to break Epicor).
Adding a where ‘invoice is not open’ clause to the purge code should be very simple. Similar to how the PartTran purge already handles open jobs…
(note: does anyone else get the impression that most bugs are features, and they are working as designed?
(Just kidding…)
We’ve had so many complaints about slow performance, that we’re going at it from every direction possible (we pass the PDT with flying colors).