we’re in the process of going through the process of exporting and importing our data from V8 to E10. Things are actually starting to look good (until now). All of the static data seems to come over just fine - Parts, BOMs, BOOs, Customers, Suppliers - all pretty good - nothing major.
For the first time I tried updating open job orders and job structures and ran into a road block. I’m counting on the creativity of this group to come up with an idea that might help us. Due to general business practice in our environment, we occasionally get engineering change notices that cause us to make changes to jobs (sometimes already in progress). When this happens, it is customary for us to delete (potentially) an entire job assembly and then re-create it with the new part number (for example). When this occurs, we get gaps in our job assembly sequences because updated are made to the job order(s) and V8 lets us do it. I’m sure this will not be a problem in E10 either.
The issue occurs when we try to export those assemblies (with gaps) and try to import them into Epicor 10 so that we can maintain the existing job structure (Job Prod, Job Operations and Job Materials, Costs, Etc.) as they were in Vantage. The issue comes when we import the Job assemblies data from V8 into E10. The export CSV file has the gaps and we export them in sorted numerical sequence. HOWEVER, during the import, E10 does not allow us to specify our own job assembly sequence - it is totally ignored by the Business Object(?)…
From what I can tell, this is the only table that Epicor is doing this too and it’s pretty critical. Basically, this issue is causing all of our job structures to be jumbled and will be catastrophic in how we look at and bring over open job orders>>>>
Any ideas? Thoughts? I’m open! Anyone done this before?
Please help!
thank you,
Jeff Henslee
M-B Companies
My example is this.
I have a job number 1000 with assembly sequence Nos: 0 (top level), 1, 2, 3
During the course of the project, we create an ECN to change one of the subassemblies (Assembly Sequence #2), Our current user process is to delete the assembly sequence 2 and create a new assembly sequence (sequence 4) no causing a gap in my assembly sequences.
when I export the job assembly data for Job 1000; everything comes out fine. asm 0,1,3 & 4 - just like it should. When I import job the job assembly data into E10, the DMT completely ignores my assembly sequence and increments it by 1. So my job 1000 in E10 come s out with consecutive sequence number 0,1,2,3. When I export all of my operations, materials and job prod data - it is referencing the original assembly sequence numbers from V8 (0,1,3,4) so when I import, Asm 0 and Asm 1 come in just fine. (They are the same as was in V8) Asm2 has no data in Vantage and therefore will have nothing in E10. Asm 3 in E10 will now be associated to the assembly part number which was Asm 4 in Vantage. The ops and materials are still from Asm 3 from Vantage. The ops and materials from vantage Asm 4 all error out because there Is not an Asm 4 record in E10.
You may be able to do the same process that was discussed for Vendornum. DMT the job with dummy sequence numbers with a filler assembly/operation and then do a second pass of DMT to remove them.
right now we’re on 10.1.500.14.
At this point, we’re not planning on changing (too far into this).
Unless there’s a “real” reason to upgrade (like this has been fixed); that’s what we plan on going live with.
I ran a couple of tests on Vantage 8. It just so happens tat the part number I picked has “natural” gaps in it because of a few assemblies marked as phantom parts in the BOM. The Gaps are created as a result of the “get details” functionality and the layout of the bill of materials. I have a case open with Epicor and it’s operating as intended. However, the part I don’t understand is when you add materials, you can change/assign a new material sequence number. When you add operations, you can change operation sequence numbers, you just can’t do anything with Assembly Sequence numbers. IMO - I feel you should be able to do this.
OK - I tested a single job order (with gaps). Exported Job Assemblies to a CSV file and then manually added in the missing “gap” records, sorted and imported.
IT WORKED!!!
Next issue - I’m going to try to export all of my job numbers, assembly sequences into a table in SQL and then write a quick T-SQL program to
1 - assess the gaps and create the missing records with a dummy part number.
2 - create a SQL statement to select (and then copy paste) my missing gap records into my original spreadsheet - so that I can import not only the job assembly records I need but also the gap records with the dummy art numbers - thus re-creating the test I just ran.
My problem:
Having problems doing a bulk insert into a SQL table.
My exported file (.csv)
Company JobNum AssemblySeq PartNum
MB 907190 0 AMA-00244
MB 907190 1 430-191661
MB 907190 5 430-97274
MB 907190 6 430-97344
MB 907190 7 432-190629
MB 907190 8 432-191656
MB 907190 9 432-201237
MB 907190 10 432-201242
MB 907190 11 432-201418
MB 907190 12 432-201849
I’ve created a table inside of SQL server. My bulk insert command:
bulk insert [dbo].[JobAsmbl]
from ‘E:\DMT Imports\Transactional Data\Job Orders\JobAssemblies\MBTest\MB-E10OpenJobAssemblyGaps.csv’
with
(
FIRSTROW = 2,
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\N’
);
Problem is I don’t get any errors and I don’t get any records inserted into my temporary table.
Any thoughts?
I’m so close to finding a solution I can just taste it.
Please advise.
Thanks!
Jeff
True - but going through an 20K line excel file is less than desirable - especially on a go-live weekend. The stress will be enough just being there.
I’m trying to create a interim table by which I can create a T-SQL program to go through and insert the missing records (gaps) so that I can turn around, export them into excel, sort them (as you stated) and then run the whole file into the Job Assemblies table through the DMT. (real records plus gap records). I’ve created a small test version of this using a single job and it works. I’ just trying to save myself from having to go through 20,000 records in Excel and trying to determine which assembly sequences I’m missing.
Yes, I do. I had done mine so many times I could do it in my sleep by go live. I did two sheets in excel with queries to old data and new data, concatenated the relevant columns in both and used vlookup to find the missing data and copied that.
If you did a sheet with current jobs query and a sheet with gaps query and then merged them it would be done.
Does E10OpenJobAssemblyGaps.csv have just the missing sequences or is it all possible sequences?
Are the jobs and assemblies all in sorted order already? If so, it should be easy to process your CSV file to add the gap records. If you have a newer version of PowerShell, just paste the following into a shell and execute it after changing the input and output file paths to your choosing:
This will create gap records linked to PartNum ‘Dummy’ for the sample CSV file you’ve provided. This assumes the file is comma-separated. What you’ve pasted is not, but I’m guessing that’s because it was copy-pasted from Excel. You can open it in Notepad to check that it’s really comma-separated. Also, if you have non-English characters in your database then you’ll have to use UTF-8 encoding.
That is very nice. I had made a quick macro to create all of the sequences, but that still needed a vlookup or another loop to process before it was ready to import.
Sorry - I’m not versed in Powershell (as yet). But I did create a work around. Basically created a temp table in SQL and imported the primary key values (company, job number, assembly seq and part number) and wrote a procedure to go through and find the gaps. Once I identified the gaps, I was able to copy/paste the gap records back into my original export CSV file and sort them in excel so that the new gap records were put in order so that the assembly sequence records from the original job will remain the same. once imported into Job assemblies, I can easily create another CSV file of the ‘gap records’ to delete them out of the job.
That’s my work-around.
As we’re seeing, I guess there are a gazillion ways to get creative to solve this issue. we’d all like to be consistent in our processes for every table - but I guess that just isn’t in the cards. We persevere and we move on. (divide and conquer!)
Thanks for all of the help and suggestions from everyone.