We’re getting ready to transition to Kinetic and I ran a DB purge and summarize to clear out some old data in an effort to reduce the database size (365 GB) prior to the conversion. In doing so, apparently the purge and summarize process must have deleted the entire TranGLC Table, YIKES! As you can imagine we’re in a bit of a pickle. Fortunately, I did grab a daily backup prior to running the purge task… unfortunately, it’s been 2 days since I ran the process. I’ve ran it before for other tables, and never had this problem. I did not select the Journal Details table when running the process. Picture of the results below along with a pic of the SQL table from our production environment showing only 12k rows…
So now we have new transactions in the TranGLC table and I’m not sure if any other tables were unexpectedly affected as well. Anyone insights or advise here would be greatly appreciated. I’ve reached out to Epicor for guidance as well, but I’m curious if anyone’s run into this before and if so, how did you solve it? Thanks in advance for any tips!
I used 12/31/2016 as the cut off and it deleted 10 million rows. Been trying to reimport data into the table, but it fails quickly on duplicate value. Definitely in a major predicament right now. If you’re able/interested to help as a consultant DM me and I’d greatly appreciate it
It would definitely be an issue. we’re pretty heavy transactionally and it would cause some major issues with all the POs and invoices and checks we’ve sent out over the past 2 days. I’ve been trying to import the data from the backup into a test database to see if the process would work, but no luck so far.
The thing is you can’t just import tranglc all those transactions have been summarized at the book level I think your only choice is to restore from backup and redo your transactions
Restoring tranglc will not have matching entries elsewhere in gljoirnal detail etc
I concur. You could probably automate some of the redo if you have dmt or a coding background, but manually may be faster and more thorough especially for only 3 days.
The other. Option is to chuck it in the screw it… bucket as long as your balances are ok then TranGLc is just audit trail you could just shrug and move on.
But if that’s not an option you need to stop like right now take a backup restore your pre purge DB and replay those transactions the longer you wait the deeper this hole is
And unless Epicor support says otherwise I don’t think any consultant that promises to fix this can actually do it reliably there are too many moving parts
The issue I’m running into is that none of our accounting transactions are balancing. Trying to do cash receipts and the transaction doesn’t balance…ie: pull up the journal detail tracker for the invoice and there’s no TranGLC associated with the transaction. The Bank GL pulls in but not the Cash account that the AR invoice posted to. Otherwise i would just chuck it and move on. It definitely seems like the Purge and Summarized messed up.
The other kicker is we were prepping to go live next weekend on Kinetic. So at this point I would probably elect to just accelerate the Go live and re-do the transactions there.
What a mess! Definitely kicking my self for trying to proactively shrink our DB prior to the move.
I do appreciate the advice. We’re on 905.702A. Definitely was not expecting this when I clicked the “Labor” checkbox. I’d run it before on other tables but never that one…
@josecgomez Probably about how you would expect. I bit the bullet and restored to the backup and it’s been metallicy, crunchy and has a very horrible taste. And i’m still chewing it right now!
I do have a DMT question I’ve never noticed before. There’s not DMT option for APInvJob charges, anyone aware of somewhere that may be hidden?
Once we’re through this mess i’ll post some notes about the process should anyone have to go through this again, hopefully they’ll have a little bit of a guide.
I don’t think there’s a DMT for that one, you might be able to replicate it fairly easily via REST (or via an EPicor function) but that’s gonna require some custom coding or tooling usage.
That’s what i was thinking…I don’t see myself doing anything in E9 for this, but i’lll probably write something in application studio for our conversion to kinetic.
Here’s some notes incase anyone ever runs into this again:
If you’re thinking about doing a purge and summarize on the labor table…DON’T!
If you’re still thinking about it after reading bullet point 1, walk away and take a vacation first, then forget that it needs to happen.
If you’ve gotten this far, plan to have a nice bottle of bourbon to soothe your misery. Or be responsible and make sure that your TranGLC table doesn’t get F@&#$D and you can process transactions before you let users back in. Recommend you do some testing and verify row counts.
Seriously though what a PITA! here we go. The trick to this is to make sure you go in order.
Create sales orders firsts (we don’t use quotes really, so I skipped them)
Create Projects next (i missed this step, which cascaded into missed jobs, missed material, missed POs, etc. dumb in retrospect).
Create jobs and assemblies.
Create Job materials - this was a little tricky. The added rows were easy, but had to figure out how to find rows that were updated over the course of the week. Thankfully we had some BPMs where we set a date field when material was moved through some different processes. Ended up being life saver. Without this, I was planning to default to the chglog table, but it’s so large the results set took forever to return. Probably would have been faster to write a SQL query instead of BAQ here, but had lots of data to move. I filtered off that data field and pulled everything beyond a certian point in time.
Side note here, i turned off all BPMS by going to our Server/BPMExec folder and changing the folder name. This helped speed up the data entry and also bypassed some checks/balances we have in place for people normally entering data. Seemed like a good call at the time, but time will tell. I figured it was good since the BPMs were active when the data went in origianlly we should be ok.
Re-create POs. Messed up here b/c when I exported data I pulled over the “openline” field. shouldn’t have done that b/c then i needed to go back and re-open all the lines so they could close naturally when I did the DMT for receipts…DUH!
5.0.1 - make sure you create any new vendors first or POs won’t push in, obvious in hindsight.
5.1 Steps here are really, unapprove all POs, then in company maintenance move the starting PO number forward, this will allow all POs to be created with the same number so we don’t screw ourselves and vendors by sending them a different po number for the same material we already sent.
5.2 Update/add PO headers then lines. We have a 1:1 ratio on all POdetail to releases so that was nice.
5.3. Re-approve POs - got stuck here with the approvals, Can’t really put something back into Pending and the approved amount field couldn’t be set through DMT. So that was a little bit of manual approving POs.
5.4 Did run into some bugs here where the DMT loaded PUR-MTL lines as PUR-STK. not sure why since the purchase type was defined. had to delete and redo those.
Receipts - Create the headers first then the detail lines. No biggie here. Took a sec to remember i had to set the receipt line to “0” since they didn’t exist…
AP invoices - PITA. I chose to basically brute force this one. Recreated Groups, then got stock creating the invoice header. Trick was to make sure the fields were in the EXACT same order as the “required fields” lists them in the DMT…then it worked. I was a little liberal in the invoices I grabbed and was operating under the assumption that If the invoice already existed it would error out during the DMT and sure enough it did.
7.1 AP invoice detail lines. again, total pain, had to do 2 DMTs 1 for receipt lines and then 1 for misc lines. Somehow i accidently loaded some Misc lines twice…not sure how, anyways had to clean that up some.
7.2 AP invoice Job MIsc lines - no way to recreate these which really sucks. We’ll be manually entering this data, fortunately it’s only about 150 lines.
8 - AR invoices - DMT, again move the number up in company Maintenace so you can recreate invoices with the same number and not screw your customers.
8.1 - make sure you re-create any new customers first.
9 UD tables - i had an autoincrementing key setup, so i was able to just find the last line and re-load all data past that point. these were easy and fast.
10 Inventory and Material Issue Transactions - haven’t processed these yet, but there’s a DMT for it, so shouldn’t be to bad.
I’m sure there’s other things as well but that’s what i remember for right now. Thanks!
