E9 question - %%% Urgent request- migration Time

Vic,



Good we have some common ground J and have both been around from the early days.



I think it is important to establish that data transfer doesn't have to be a weekend process, you separate static and dynamic, and preload data as required.

Extract -> Import,

Extract-> Create Diff ->Import



The benefits of this are while piloting you maintain a pseudo in sync version of your environments and users can easily reflect systems.

Only really dynamic cut over items you do in the critical time window of go live, you set the periodicity of refreshing the data to whatever fits your business needs.



Direct to SQL you bypass the business logic. The problem is when tracing the data and profiling via SQL you are taking that at a point in time; if you patch or change versions or company / configuration options you need to review because something may have changed.

Sysrowid and sysrevID are an example these were introduced in E9.04. I'm not saying you can't handle them, I'm just saying your scripts you provided do not; which is why it is dangerous for users that don't methodically trace and understand how things work to use them, they can't be taken for granted.



With regards to parttran, the comment was that you are not following the business logic, quantity adjustments to bring on hand inventory would result in parttran entries, which you assume are not required, but what if a developer writes a conversion that assumes it should be.

An example when you would want it is if you wanted to bring in aged stock.



"With some creativity, attention to detail, careful testing and a thorough understanding of the database schema and business logic you can do it all in SQL."

Yes of course you can and one oversight and you can also make a compete mess of things. The business object approach is much more forgiving.



I too do many conversions and implementations I have converted direct to SQL and Direct to Progress, Progress to SQL, SQL to Progresss, it is all possible, but with a tool that uses the business objects, you don't have to spend all that time each time establishing and checking how developers have decided to change things or how the detail is implemented, you abstract that detail and work at the BO layer with templates and documentation to follow which is generic and safe.

In some cases direct to SQL is the only feasible option but it should be used only when it needs to be.



I am not saying no to SQL Direct or Progress Direct I just think anyone using this method should fully understand what they are doing (which you obviously do), but it isn't always the case, I just wanted to convey the risks with this as a general approach.



Regards,

Stephen



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Vic Drecchio
Sent: 25 June 2010 14:53
To: vantage@yahoogroups.com
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time





Stephen, respectfully, the only sentence of your email I agree with is expecting someone without SQL skills to perform this is risky. I agree 100%. This isn't for the novice or intermediate developer/admin with introductory knowledge of Epicor's schema. I've been working with various databases for almost 20 years (now I really feel old) and working with various flavors of Epicor software for a decade.

In a perfect world without clocks and time deadlines, yes, using the import tools supplied or FTPing your data templates to Epicor is the way to go; undoubtedly. But with the size of most of our databases these days and the immense amount of legacy data desired in the new system, this is rarely do-able over a weekend especially with a time buffer for errors.

Yes, technically I'm bypassing the business logic. But effectively I'm not. Prior to doing this I traced the data and followed the logic. When I perform the imports I simulate and adhere to the Vantage business logic 100%. I know which columns have default values, which columns can be null/blank and keep the relationship and integrity in tact.

In SQL there are no sysrowid and sysrevid fields. We have Progress_RecID_Ident_ fields which are identity columns that auto assign unique numbers and the Progress_RecID is populated via triggers. So I never have to worry about them. Upon an insert those fields are auto-populated. PartTran table? Nobody should ever populate the PartTran table. Why would you want that transactional history in your new database? I've performed more conversions than I have fingers and toes for many different companies and customers and never imported historical transactions. Epicor even recommends against that.

It most certainly does handle Onhand quantities via the PartBin and PartWhse tables. Serialized parts and Lot controlled parts? No problems.

With some creativity, attention to detail, careful testing and a thorough understanding of the database schema and business logic you can do it all in SQL.

Now..... would I attempt this with a Progress database? Heck no. But then again, that's why I would choose SQL over Progress 10 days out of the week. :-)

By the way, I authored Epicor's "Signature Data Conversion Methodology" document. I'm fairly confident in what can be done and what cannot be done. Epicor doesn't like customers fiddling with raw data and I don't blame them. But if you know what you're doing..... :-)

TGIF...... :-)

Vic

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf Of Stephen Edginton
Sent: Thursday, June 24, 2010 4:11 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time

Vic,

The problem with loading direct into SQL is that you bypass all logic you are doing these weeks of analysis checking data is correct and you still may have missed something say another table is defaulted or you misinterpreted how something worked, or after a patch the developers now make use of another table or update sequence tables. It can be done but has to be done with immense care and can catch even the most experienced out.

Take for instance the T/SQL you have defined below, it will cause issues because it doesn't handle the sysrowid and sysrevid fields.

It doesn't support serialised items, or fifo costing or correctly handle the on hand quantity, it doesn't honour the rounding engine rules, it doesn't handle the parttran table, it doesn't default values as the BO's would.

The list continues.

NOW I am not saying this doesn't work for you but it is not a generalized solution for everyone and is subject to specifically how you have configured your system and profiling.

You could not expect someone without SQL skills to use such an approach.

Loading directly into SQL or Progress is the fastest option without doubt, but it may just seem faster to begin with but then when MRP hangs or you are working with support to resolve an issue or you are live and something stops working, there are more unknowns which may be impossible to replicate.

When the system is working correctly importing via the business objects is the best option, if you miss something or make a mistake it is identified for you.

Yes using the business objects is slower than direct via SQL, but if it is really running slowly then the whole application will also, it's a good benchmark for the application and a good stress test for the server.

In this instance there have been some serious speed issues with 905.600B mainly not directly EPICOR's fault as it was down to an open edge service pack (although it should have been identified during testing) this is why 905.600B was pulled and came out as 905.600B2 and this only impacted on SQL customers.

Regards,

Stephen

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On Behalf Of Vic Drecchio
Sent: 24 June 2010 18:12
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time

I took my csv file and imported it into a holding table in which I analyzed and cleaned. Checked for dupe parts, ran queries for odd characters, ensured product groups, part classes, part types, warehouse codes, etc. were legit. I probably spent upwards of 3 weeks honing my part data in the "holding table". Once it was cleaned and polished to the best of my knowledge I floated it around to key users to inspect. Then I did multiple test imports and after each import I piloted the system with some key users..... ran reports, ran MRP, opened trackers, dashboards etc.

I had zero problems.

Initially, prior to embarking on this task, I simply logged into an empty database and from within Vantage I entered a Part with the bare minimum information/required fields. I analyzed what that entry from the part master created in various tables.

It was great doing it like this because I controlled it. It was easy to make changes, pre-clean the data, load and test multiple times.

It certainly doesn't hurt to try. Pervasive/DMT or Service Connect are horrible for Part Imports. For SQL customers I'm still amazed that they are adamant about using those clunky middleware data loaders when you have the brute power of SQL sitting at your fingertips.

For a certain subset of parts I was forced to use Service Connect. I needed to trigger the PLM checkbox so it then triggered a data sync with the PLM database. I had about 14,000 of those parts and using Service Connect it took me 1000 parts per hour. Horrible.

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On Behalf Of Len Hartka
Sent: Thursday, June 24, 2010 12:23 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time

Good Day Vic:

The aurgument against an SQL load, which would be fast, is that there would be no "Business Logic" checking ( valid Part#, allowed code, null required field, etc.)

How did you handle the checking of the data.

len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com>

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On Behalf Of Vic Drecchio
Sent: Thursday, June 24, 2010 11:45 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time

Do the export from M2K yourself into delimited text file(s) and do the import yourself via SQL. It's not difficult. I did 50K parts in 12 minutes.

This was for ver 8.03 but the part schema for 9 is identical.

-- Procedure to Import INVENTORY PARTS

--

--

--

-- TIMCO

-- Vic Drecchio

-- Created: 2008-12-04

-- Last Modified: 2009-01-01

--

-- ASSUMPTIONS:

-- SQL Table Name for Input: "INV_IN"

-- Structure:

-- ID

-- ITEM

-- PARTNUM

-- DESC

-- TYPE

-- PRODGROUP

-- PARTCLASS

-- UOM

-- ONHAND

-- UNITCOST

-- WAREHOUSE

-- BIN

-- IMPORT777

--

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

use mfgsys803;

DECLARE @COMPANY VARCHAR(10),

@PLANT VARCHAR(10),

@1 INT,

@RECFLAG INT,

@DUPFLAG INT,

@PARTCOUNT INT,

@BINCOUNT INT,

@COSTCOUNT INT,

@PLANTCNT INT,

@WHCOUNT INT,

@PWCOUNT INT,

@ROWID INT,

@STOCKFLAG INT;

SELECT @COMPANY = 'TIMCO',

@PLANT = 'Mfgsys',

@1 = 1,

@RECFLAG = 777, --- USED TO ID IMPORTED RECORDS

@DUPFLAG = 999, --- USED TO ID RECORDS SKIPPED DUE TO NON-UNIQ PN'S

@PARTCOUNT = 0,

@BINCOUNT = 0,

@COSTCOUNT = 0,

@PLANTCNT = 0,

@WHCOUNT = 0,

@PWCOUNT = 0,

@ROWID = 0,

@STOCKFLAG = 0

;

--============================

-- SET VARIABLES FOR CURSOR FETCH

DECLARE

@ITEM NVARCHAR(50),

@PARTNUM NVARCHAR(50),

@DESC NVARCHAR(50),

@TYPE NVARCHAR(20),

@PRODGROUP NVARCHAR(20),

@PARTCLASS NVARCHAR(20),

@UOM NVARCHAR(10),

@ONHAND FLOAT,

@UNITCOST FLOAT,

@WAREHOUSE NVARCHAR(25),

@BIN NVARCHAR(25) ;

------------------------------------------

-- GET LIST OF RECORDS TO IMPORT

-- >> DUMP INTO CURSOR TO SCAN THRU

------------------------------------------

DECLARE PARTLIST CURSOR FOR

SELECT ID, ITEM, PARTNUM, [DESC], [TYPE], PRODGROUP,

PARTCLASS, UOM, ONHAND, UNITCOST, WAREHOUSE, BIN

FROM INV_IN;

OPEN PARTLIST

-------------------------------------------

-- BEGIN SCAN OF CURSOR

FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;

WHILE (@@FETCH_STATUS <> -1)

BEGIN;

-->>>>

-- SEE IF PART ALREADY EXISTS IN PART MASTER

SELECT @PARTCOUNT = COUNT(*) FROM PART P WHERE P.PARTNUM = @PARTNUM

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF @PARTCOUNT = 0

------- PART ----------------

-----------------------------

-- UNIQUE PART MASTER -------

-----------------------------

BEGIN;

-- PREPARE STOCKFLAG:

-- IF TYPE = PURCHASED, *STOCK*

-- IF TYPE = MANUFACTURED, *NON-STOCK*

IF UPPER(LEFT(@TYPE,1)) = 'M'

SELECT @STOCKFLAG = 1

ELSE

SELECT @STOCKFLAG = 0

-- INSERT INTO PART

INSERT INTO PART

(COMPANY, PARTNUM, SEARCHWORD, PARTDESCRIPTION, CLASSID, IUM, PUM, TYPECODE, NONSTOCK,

PURCHASINGFACTOR, UNITPRICE, PRICEPERCODE, INTERNALUNITPRICE, INTERNALPRICEPERCODE,

PRODCODE, COSTMETHOD, SHORTCHAR10, NUMBER20)

VALUES (

@COMPANY,

@PARTNUM,

LEFT(@DESC,8),

@DESC,

@PARTCLASS,

@UOM,

@UOM,

UPPER(LEFT(@TYPE,1)),

@STOCKFLAG, -- NON-STOCK ?

@1, -- PURCHASING FACTOR FLAG

@UNITCOST,

'E',

@UNITCOST,

'E',

@PRODGROUP,

'A',

@ITEM,

@RECFLAG

);

-----------------------

-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED

-----------------------

UPDATE INV_IN SET IMPORT777 = @RECFLAG WHERE ID = @ROWID

-- PUTS A 777 IN THE INPUT TABLE TO SHOW A UNIQUE PART WAS INSERTED

END;

IF @PARTCOUNT > 0

BEGIN;

-----------------------

-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED

-----------------------

UPDATE INV_IN SET IMPORT777 = @DUPFLAG WHERE ID = @ROWID

-- PUTS A 999 IN THE INPUT TABLE TO SHOW RECORD RECORD WAS NON-UNIQ

END;

SELECT @PARTCOUNT = 0 --- RESET IT

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

------- PART BIN ------------

-----------------------------

-- ON HAND QTYS ONLY --------

-----------------------------

IF @ONHAND > 0

BEGIN

---- CHECK TO SEE IF THE PART IS ALREADY IN THIS LOCATION

SELECT @BINCOUNT =

COUNT(*) FROM PARTBIN PB

WHERE PB.PARTNUM = @PARTNUM AND

PB.WAREHOUSECODE = @WAREHOUSE AND

PB.BINNUM = @BIN

IF @BINCOUNT > 0 -- PART ALREADY EXISTS IN THIS WH/BIN... ADD TO IT!

BEGIN

UPDATE PARTBIN

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE

PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE AND

BINNUM = @BIN;

END;

IF @BINCOUNT = 0 --- NOT IN THERE YET! INSERT!

BEGIN

INSERT INTO PARTBIN

(COMPANY, PARTNUM, WAREHOUSECODE, BINNUM, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @BIN, @ONHAND, @RECFLAG);

END;

SELECT @BINCOUNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

---- PARTCOST ---------

SELECT @COSTCOUNT = COUNT(*) FROM PARTCOST PC

WHERE PC.PARTNUM = @PARTNUM AND COSTID = @1

IF @COSTCOUNT = 0

BEGIN

INSERT INTO PARTCOST

(COMPANY, PARTNUM, COSTID, AVGMATERIALCOST, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @1, @UNITCOST, @RECFLAG);

SELECT @COSTCOUNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

----------PARTPLANT--------------------

SELECT @PLANTCNT = COUNT(*) FROM PARTPLANT PP

WHERE PP.PARTNUM = @PARTNUM AND PP.PLANT = @PLANT

IF @PLANTCNT = 0

BEGIN

INSERT INTO PARTPLANT

(COMPANY, PLANT, PARTNUM, PRIMWHSE, MRPRECALCNEEDED, PROCESSMRP, GENERATESUGG, SOURCETYPE,

COSTMETHOD, KITSHIPCOMPLETE, KITPRICING, KITPRINTCOMPSPS, KITPRINTCOMPSINV, QTYBEARING,

NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @1, @1, @1, 'P',

'A', @1, 'P', @1, @1, @1, @RECFLAG);

SELECT @PLANTCNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

------------- PARTWHSE ----------------

SELECT @WHCOUNT = COUNT(*) FROM PARTWHSE PW

WHERE PW.PARTNUM = @PARTNUM AND

PW.WAREHOUSECODE = @WAREHOUSE

IF @WHCOUNT = 0

BEGIN

INSERT INTO PARTWHSE

(COMPANY, PARTNUM, WAREHOUSECODE, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @ONHAND, @RECFLAG);

END

IF @WHCOUNT > 0

BEGIN

UPDATE PARTWHSE

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE;

END

SELECT @WHCOUNT = 0

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

--------------- PLANTWHSE ---------------

SELECT @PWCOUNT = COUNT(*) FROM PLANTWHSE PL

WHERE

PL.PARTNUM = @PARTNUM AND

PL.WAREHOUSECODE = @WAREHOUSE;

IF @PWCOUNT = 0

BEGIN

INSERT INTO PLANTWHSE

(COMPANY, PLANT, PARTNUM, WAREHOUSECODE, PRIMBIN, NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @BIN, @RECFLAG)

END

SELECT @PWCOUNT = 0

---------- DONE THIS LOOP ITERATION

---------- GET NEXT RECORD!

FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;

END

-- END OF BIG LOOP

-- REMEMBER TO UPDATE OH QTYS IN PARTWHSE FROM PARTBIN(S)

--

close PARTList

Deallocate PARTList

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On Behalf Of Len Hartka
Sent: Thursday, June 24, 2010 11:15 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] E9 question - %%% Urgent request- migration Time
Importance: High

Good Day:

Has anyone done their first Pass from Manage-2000 to E9 version 9.05
( 9.05.600B specifically).

Epicor is running now and for 50,000 Part#'s it will take 36 hours,
and we have 300,000 part#'s.
Then we have PartRev, PartPlant,PartWarehouse all with 300,00
records.

Epicor is doing it using DMT tool specific, and brand new, for
9.05.
For 9.05, the DMT tool is the only one they are using.

Having the migration take 2.5 weeks is beyond our time frame and
that assume it finishes. It has not completed any of the 6 csv files of
50,000 each.
We expected it to take about 2 days.

9.05 specific probably, buy I would be interested in 9.04.

We have turned of ALL anti-virus scanning, our servers are over the
specs specs, and we are running on our servers in-house because Epicor
did not have a 9.05 (SQL 2008) server available.

Anybody know a secret?

len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com>
<blocked::blocked::mailto:len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> >
E9 question - %%% Urgent request- migration Time

**************************************************************

Sun Automation Group

Celebrating

25 Years of Service

to the Corrugated Industry

**************************************************************

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
Good Day:

Has anyone done their first Pass from Manage-2000 to E9 version 9.05
( 9.05.600B specifically).

Epicor is running now and for 50,000 Part#'s it will take 36 hours,
and we have 300,000 part#'s.
Then we have PartRev, PartPlant,PartWarehouse all with 300,00
records.

Epicor is doing it using DMT tool specific, and brand new, for
9.05.
For 9.05, the DMT tool is the only one they are using.

Having the migration take 2.5 weeks is beyond our time frame and
that assume it finishes. It has not completed any of the 6 csv files of
50,000 each.
We expected it to take about 2 days.

9.05 specific probably, buy I would be interested in 9.04.

We have turned of ALL anti-virus scanning, our servers are over the
specs specs, and we are running on our servers in-house because Epicor
did not have a 9.05 (SQL 2008) server available.

Anybody know a secret?



len.hartka@...
<blocked::blocked::mailto:len.hartka@...>
E9 question - %%% Urgent request- migration Time


**************************************************************

Sun Automation Group

Celebrating

25 Years of Service

to the Corrugated Industry

**************************************************************




This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.


[Non-text portions of this message have been removed]
I just imported close to 50,000 parts into 8.03.405a last evening using DMT at 5 hrs per file....

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Len Hartka
Sent: Thursday, June 24, 2010 11:15 AM
To: vantage@yahoogroups.com
Subject: [Vantage] E9 question - %%% Urgent request- migration Time
Importance: High

Good Day:

Has anyone done their first Pass from Manage-2000 to E9 version 9.05
( 9.05.600B specifically).

Epicor is running now and for 50,000 Part#'s it will take 36 hours,
and we have 300,000 part#'s.
Then we have PartRev, PartPlant,PartWarehouse all with 300,00
records.

Epicor is doing it using DMT tool specific, and brand new, for
9.05.
For 9.05, the DMT tool is the only one they are using.

Having the migration take 2.5 weeks is beyond our time frame and
that assume it finishes. It has not completed any of the 6 csv files of
50,000 each.
We expected it to take about 2 days.

9.05 specific probably, buy I would be interested in 9.04.

We have turned of ALL anti-virus scanning, our servers are over the
specs specs, and we are running on our servers in-house because Epicor
did not have a 9.05 (SQL 2008) server available.

Anybody know a secret?



len.hartka@...
<blocked::blocked::mailto:len.hartka@...>
E9 question - %%% Urgent request- migration Time


**************************************************************

Sun Automation Group

Celebrating

25 Years of Service

to the Corrugated Industry

**************************************************************




This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.


[Non-text portions of this message have been removed]



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
Do the export from M2K yourself into delimited text file(s) and do the import yourself via SQL. It's not difficult. I did 50K parts in 12 minutes.



This was for ver 8.03 but the part schema for 9 is identical.



-- Procedure to Import INVENTORY PARTS

--

--

--

-- TIMCO

-- Vic Drecchio

-- Created: 2008-12-04

-- Last Modified: 2009-01-01

--

-- ASSUMPTIONS:

-- SQL Table Name for Input: "INV_IN"

-- Structure:

-- ID

-- ITEM

-- PARTNUM

-- DESC

-- TYPE

-- PRODGROUP

-- PARTCLASS

-- UOM

-- ONHAND

-- UNITCOST

-- WAREHOUSE

-- BIN

-- IMPORT777

--



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go



use mfgsys803;





DECLARE @COMPANY VARCHAR(10),

@PLANT VARCHAR(10),

@1 INT,

@RECFLAG INT,

@DUPFLAG INT,

@PARTCOUNT INT,

@BINCOUNT INT,

@COSTCOUNT INT,

@PLANTCNT INT,

@WHCOUNT INT,

@PWCOUNT INT,

@ROWID INT,

@STOCKFLAG INT;



SELECT @COMPANY = 'TIMCO',

@PLANT = 'Mfgsys',

@1 = 1,

@RECFLAG = 777, --- USED TO ID IMPORTED RECORDS

@DUPFLAG = 999, --- USED TO ID RECORDS SKIPPED DUE TO NON-UNIQ PN'S

@PARTCOUNT = 0,

@BINCOUNT = 0,

@COSTCOUNT = 0,

@PLANTCNT = 0,

@WHCOUNT = 0,

@PWCOUNT = 0,

@ROWID = 0,

@STOCKFLAG = 0

;





--============================



-- SET VARIABLES FOR CURSOR FETCH



DECLARE

@ITEM NVARCHAR(50),

@PARTNUM NVARCHAR(50),

@DESC NVARCHAR(50),

@TYPE NVARCHAR(20),

@PRODGROUP NVARCHAR(20),

@PARTCLASS NVARCHAR(20),

@UOM NVARCHAR(10),

@ONHAND FLOAT,

@UNITCOST FLOAT,

@WAREHOUSE NVARCHAR(25),

@BIN NVARCHAR(25) ;



------------------------------------------

-- GET LIST OF RECORDS TO IMPORT

-- >> DUMP INTO CURSOR TO SCAN THRU

------------------------------------------



DECLARE PARTLIST CURSOR FOR

SELECT ID, ITEM, PARTNUM, [DESC], [TYPE], PRODGROUP,

PARTCLASS, UOM, ONHAND, UNITCOST, WAREHOUSE, BIN

FROM INV_IN;



OPEN PARTLIST



-------------------------------------------

-- BEGIN SCAN OF CURSOR



FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;



WHILE (@@FETCH_STATUS <> -1)

BEGIN;

-->>>>

-- SEE IF PART ALREADY EXISTS IN PART MASTER

SELECT @PARTCOUNT = COUNT(*) FROM PART P WHERE P.PARTNUM = @PARTNUM

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF @PARTCOUNT = 0

------- PART ----------------

-----------------------------

-- UNIQUE PART MASTER -------

-----------------------------



BEGIN;



-- PREPARE STOCKFLAG:

-- IF TYPE = PURCHASED, *STOCK*

-- IF TYPE = MANUFACTURED, *NON-STOCK*



IF UPPER(LEFT(@TYPE,1)) = 'M'

SELECT @STOCKFLAG = 1

ELSE

SELECT @STOCKFLAG = 0



-- INSERT INTO PART



INSERT INTO PART

(COMPANY, PARTNUM, SEARCHWORD, PARTDESCRIPTION, CLASSID, IUM, PUM, TYPECODE, NONSTOCK,

PURCHASINGFACTOR, UNITPRICE, PRICEPERCODE, INTERNALUNITPRICE, INTERNALPRICEPERCODE,

PRODCODE, COSTMETHOD, SHORTCHAR10, NUMBER20)

VALUES (

@COMPANY,

@PARTNUM,

LEFT(@DESC,8),

@DESC,

@PARTCLASS,

@UOM,

@UOM,

UPPER(LEFT(@TYPE,1)),

@STOCKFLAG, -- NON-STOCK ?

@1, -- PURCHASING FACTOR FLAG

@UNITCOST,

'E',

@UNITCOST,

'E',

@PRODGROUP,

'A',

@ITEM,

@RECFLAG

);





-----------------------

-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED

-----------------------



UPDATE INV_IN SET IMPORT777 = @RECFLAG WHERE ID = @ROWID

-- PUTS A 777 IN THE INPUT TABLE TO SHOW A UNIQUE PART WAS INSERTED





END;



IF @PARTCOUNT > 0

BEGIN;

-----------------------

-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED

-----------------------



UPDATE INV_IN SET IMPORT777 = @DUPFLAG WHERE ID = @ROWID

-- PUTS A 999 IN THE INPUT TABLE TO SHOW RECORD RECORD WAS NON-UNIQ

END;



SELECT @PARTCOUNT = 0 --- RESET IT



--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



------- PART BIN ------------

-----------------------------

-- ON HAND QTYS ONLY --------

-----------------------------



IF @ONHAND > 0

BEGIN



---- CHECK TO SEE IF THE PART IS ALREADY IN THIS LOCATION



SELECT @BINCOUNT =

COUNT(*) FROM PARTBIN PB

WHERE PB.PARTNUM = @PARTNUM AND

PB.WAREHOUSECODE = @WAREHOUSE AND

PB.BINNUM = @BIN



IF @BINCOUNT > 0 -- PART ALREADY EXISTS IN THIS WH/BIN... ADD TO IT!

BEGIN

UPDATE PARTBIN

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE

PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE AND

BINNUM = @BIN;

END;



IF @BINCOUNT = 0 --- NOT IN THERE YET! INSERT!

BEGIN

INSERT INTO PARTBIN

(COMPANY, PARTNUM, WAREHOUSECODE, BINNUM, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @BIN, @ONHAND, @RECFLAG);

END;



SELECT @BINCOUNT = 0



END;



----------------------------------------------------------

----------------------------------------------------------



---- PARTCOST ---------



SELECT @COSTCOUNT = COUNT(*) FROM PARTCOST PC

WHERE PC.PARTNUM = @PARTNUM AND COSTID = @1



IF @COSTCOUNT = 0

BEGIN

INSERT INTO PARTCOST

(COMPANY, PARTNUM, COSTID, AVGMATERIALCOST, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @1, @UNITCOST, @RECFLAG);



SELECT @COSTCOUNT = 0

END;



---------------------------------------------------------------

---------------------------------------------------------------

---------------------------------------------------------------



----------PARTPLANT--------------------



SELECT @PLANTCNT = COUNT(*) FROM PARTPLANT PP

WHERE PP.PARTNUM = @PARTNUM AND PP.PLANT = @PLANT



IF @PLANTCNT = 0

BEGIN

INSERT INTO PARTPLANT

(COMPANY, PLANT, PARTNUM, PRIMWHSE, MRPRECALCNEEDED, PROCESSMRP, GENERATESUGG, SOURCETYPE,

COSTMETHOD, KITSHIPCOMPLETE, KITPRICING, KITPRINTCOMPSPS, KITPRINTCOMPSINV, QTYBEARING,

NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @1, @1, @1, 'P',

'A', @1, 'P', @1, @1, @1, @RECFLAG);



SELECT @PLANTCNT = 0

END;





-----------------------------------------------------------------

-----------------------------------------------------------------

-----------------------------------------------------------------



------------- PARTWHSE ----------------



SELECT @WHCOUNT = COUNT(*) FROM PARTWHSE PW

WHERE PW.PARTNUM = @PARTNUM AND

PW.WAREHOUSECODE = @WAREHOUSE



IF @WHCOUNT = 0

BEGIN

INSERT INTO PARTWHSE

(COMPANY, PARTNUM, WAREHOUSECODE, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @ONHAND, @RECFLAG);

END



IF @WHCOUNT > 0

BEGIN

UPDATE PARTWHSE

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE;

END



SELECT @WHCOUNT = 0



-------------------------------------------------------------------

-------------------------------------------------------------------

-------------------------------------------------------------------

-------------------------------------------------------------------



--------------- PLANTWHSE ---------------



SELECT @PWCOUNT = COUNT(*) FROM PLANTWHSE PL

WHERE

PL.PARTNUM = @PARTNUM AND

PL.WAREHOUSECODE = @WAREHOUSE;



IF @PWCOUNT = 0

BEGIN

INSERT INTO PLANTWHSE

(COMPANY, PLANT, PARTNUM, WAREHOUSECODE, PRIMBIN, NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @BIN, @RECFLAG)

END



SELECT @PWCOUNT = 0











---------- DONE THIS LOOP ITERATION

---------- GET NEXT RECORD!



FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;







END

-- END OF BIG LOOP











-- REMEMBER TO UPDATE OH QTYS IN PARTWHSE FROM PARTBIN(S)



--



close PARTList

Deallocate PARTList





________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Len Hartka
Sent: Thursday, June 24, 2010 11:15 AM
To: vantage@yahoogroups.com
Subject: [Vantage] E9 question - %%% Urgent request- migration Time
Importance: High





Good Day:

Has anyone done their first Pass from Manage-2000 to E9 version 9.05
( 9.05.600B specifically).

Epicor is running now and for 50,000 Part#'s it will take 36 hours,
and we have 300,000 part#'s.
Then we have PartRev, PartPlant,PartWarehouse all with 300,00
records.

Epicor is doing it using DMT tool specific, and brand new, for
9.05.
For 9.05, the DMT tool is the only one they are using.

Having the migration take 2.5 weeks is beyond our time frame and
that assume it finishes. It has not completed any of the 6 csv files of
50,000 each.
We expected it to take about 2 days.

9.05 specific probably, buy I would be interested in 9.04.

We have turned of ALL anti-virus scanning, our servers are over the
specs specs, and we are running on our servers in-house because Epicor
did not have a 9.05 (SQL 2008) server available.

Anybody know a secret?



len.hartka@... <mailto:len.hartka%40sunautomation.com>
<blocked::blocked::mailto:len.hartka@... <mailto:len.hartka%40sunautomation.com> >
E9 question - %%% Urgent request- migration Time


**************************************************************

Sun Automation Group

Celebrating

25 Years of Service

to the Corrugated Industry

**************************************************************

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
Good Day Vic:

The aurgument against an SQL load, which would be fast, is that there would be no "Business Logic" checking ( valid Part#, allowed code, null required field, etc.)

How did you handle the checking of the data.

len.hartka@...

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Vic Drecchio
Sent: Thursday, June 24, 2010 11:45 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time




Do the export from M2K yourself into delimited text file(s) and do the import yourself via SQL. It's not difficult. I did 50K parts in 12 minutes.

This was for ver 8.03 but the part schema for 9 is identical.

-- Procedure to Import INVENTORY PARTS








-- TIMCO

-- Vic Drecchio

-- Created: 2008-12-04

-- Last Modified: 2009-01-01




-- ASSUMPTIONS:

-- SQL Table Name for Input: "INV_IN"

-- Structure:

-- ID

-- ITEM

-- PARTNUM

-- DESC

-- TYPE

-- PRODGROUP

-- PARTCLASS

-- UOM

-- ONHAND

-- UNITCOST

-- WAREHOUSE

-- BIN

-- IMPORT777




set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

use mfgsys803;

DECLARE @COMPANY VARCHAR(10),

@PLANT VARCHAR(10),

@1 INT,

@RECFLAG INT,

@DUPFLAG INT,

@PARTCOUNT INT,

@BINCOUNT INT,

@COSTCOUNT INT,

@PLANTCNT INT,

@WHCOUNT INT,

@PWCOUNT INT,

@ROWID INT,

@STOCKFLAG INT;

SELECT @COMPANY = 'TIMCO',

@PLANT = 'Mfgsys',

@1 = 1,

@RECFLAG = 777, --- USED TO ID IMPORTED RECORDS

@DUPFLAG = 999, --- USED TO ID RECORDS SKIPPED DUE TO NON-UNIQ PN'S

@PARTCOUNT = 0,

@BINCOUNT = 0,

@COSTCOUNT = 0,

@PLANTCNT = 0,

@WHCOUNT = 0,

@PWCOUNT = 0,

@ROWID = 0,

@STOCKFLAG = 0

;

--============================

-- SET VARIABLES FOR CURSOR FETCH

DECLARE

@ITEM NVARCHAR(50),

@PARTNUM NVARCHAR(50),

@DESC NVARCHAR(50),

@TYPE NVARCHAR(20),

@PRODGROUP NVARCHAR(20),

@PARTCLASS NVARCHAR(20),

@UOM NVARCHAR(10),

@ONHAND FLOAT,

@UNITCOST FLOAT,

@WAREHOUSE NVARCHAR(25),

@BIN NVARCHAR(25) ;




-- GET LIST OF RECORDS TO IMPORT

-- >> DUMP INTO CURSOR TO SCAN THRU




DECLARE PARTLIST CURSOR FOR

SELECT ID, ITEM, PARTNUM, [DESC], [TYPE], PRODGROUP,

PARTCLASS, UOM, ONHAND, UNITCOST, WAREHOUSE, BIN

FROM INV_IN;

OPEN PARTLIST




-- BEGIN SCAN OF CURSOR

FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;

WHILE (@@FETCH_STATUS <> -1)

BEGIN;

-->>>>

-- SEE IF PART ALREADY EXISTS IN PART MASTER

SELECT @PARTCOUNT = COUNT(*) FROM PART P WHERE P.PARTNUM = @PARTNUM

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF @PARTCOUNT = 0

------- PART ----------------




-- UNIQUE PART MASTER -------




BEGIN;

-- PREPARE STOCKFLAG:

-- IF TYPE = PURCHASED, *STOCK*

-- IF TYPE = MANUFACTURED, *NON-STOCK*

IF UPPER(LEFT(@TYPE,1)) = 'M'

SELECT @STOCKFLAG = 1

ELSE

SELECT @STOCKFLAG = 0

-- INSERT INTO PART

INSERT INTO PART

(COMPANY, PARTNUM, SEARCHWORD, PARTDESCRIPTION, CLASSID, IUM, PUM, TYPECODE, NONSTOCK,

PURCHASINGFACTOR, UNITPRICE, PRICEPERCODE, INTERNALUNITPRICE, INTERNALPRICEPERCODE,

PRODCODE, COSTMETHOD, SHORTCHAR10, NUMBER20)

VALUES (

@COMPANY,

@PARTNUM,

LEFT(@DESC,8),

@DESC,

@PARTCLASS,

@UOM,

@UOM,

UPPER(LEFT(@TYPE,1)),

@STOCKFLAG, -- NON-STOCK ?

@1, -- PURCHASING FACTOR FLAG

@UNITCOST,

'E',

@UNITCOST,

'E',

@PRODGROUP,

'A',

@ITEM,

@RECFLAG

);




-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED




UPDATE INV_IN SET IMPORT777 = @RECFLAG WHERE ID = @ROWID

-- PUTS A 777 IN THE INPUT TABLE TO SHOW A UNIQUE PART WAS INSERTED

END;

IF @PARTCOUNT > 0

BEGIN;




-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED




UPDATE INV_IN SET IMPORT777 = @DUPFLAG WHERE ID = @ROWID

-- PUTS A 999 IN THE INPUT TABLE TO SHOW RECORD RECORD WAS NON-UNIQ

END;

SELECT @PARTCOUNT = 0 --- RESET IT

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

------- PART BIN ------------




-- ON HAND QTYS ONLY --------




IF @ONHAND > 0

BEGIN

---- CHECK TO SEE IF THE PART IS ALREADY IN THIS LOCATION

SELECT @BINCOUNT =

COUNT(*) FROM PARTBIN PB

WHERE PB.PARTNUM = @PARTNUM AND

PB.WAREHOUSECODE = @WAREHOUSE AND

PB.BINNUM = @BIN

IF @BINCOUNT > 0 -- PART ALREADY EXISTS IN THIS WH/BIN... ADD TO IT!

BEGIN

UPDATE PARTBIN

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE

PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE AND

BINNUM = @BIN;

END;

IF @BINCOUNT = 0 --- NOT IN THERE YET! INSERT!

BEGIN

INSERT INTO PARTBIN

(COMPANY, PARTNUM, WAREHOUSECODE, BINNUM, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @BIN, @ONHAND, @RECFLAG);

END;

SELECT @BINCOUNT = 0

END;






---- PARTCOST ---------

SELECT @COSTCOUNT = COUNT(*) FROM PARTCOST PC

WHERE PC.PARTNUM = @PARTNUM AND COSTID = @1

IF @COSTCOUNT = 0

BEGIN

INSERT INTO PARTCOST

(COMPANY, PARTNUM, COSTID, AVGMATERIALCOST, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @1, @UNITCOST, @RECFLAG);

SELECT @COSTCOUNT = 0

END;








----------PARTPLANT--------------------

SELECT @PLANTCNT = COUNT(*) FROM PARTPLANT PP

WHERE PP.PARTNUM = @PARTNUM AND PP.PLANT = @PLANT

IF @PLANTCNT = 0

BEGIN

INSERT INTO PARTPLANT

(COMPANY, PLANT, PARTNUM, PRIMWHSE, MRPRECALCNEEDED, PROCESSMRP, GENERATESUGG, SOURCETYPE,

COSTMETHOD, KITSHIPCOMPLETE, KITPRICING, KITPRINTCOMPSPS, KITPRINTCOMPSINV, QTYBEARING,

NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @1, @1, @1, 'P',

'A', @1, 'P', @1, @1, @1, @RECFLAG);

SELECT @PLANTCNT = 0

END;








------------- PARTWHSE ----------------

SELECT @WHCOUNT = COUNT(*) FROM PARTWHSE PW

WHERE PW.PARTNUM = @PARTNUM AND

PW.WAREHOUSECODE = @WAREHOUSE

IF @WHCOUNT = 0

BEGIN

INSERT INTO PARTWHSE

(COMPANY, PARTNUM, WAREHOUSECODE, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @ONHAND, @RECFLAG);

END

IF @WHCOUNT > 0

BEGIN

UPDATE PARTWHSE

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE;

END

SELECT @WHCOUNT = 0










--------------- PLANTWHSE ---------------

SELECT @PWCOUNT = COUNT(*) FROM PLANTWHSE PL

WHERE

PL.PARTNUM = @PARTNUM AND

PL.WAREHOUSECODE = @WAREHOUSE;

IF @PWCOUNT = 0

BEGIN

INSERT INTO PLANTWHSE

(COMPANY, PLANT, PARTNUM, WAREHOUSECODE, PRIMBIN, NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @BIN, @RECFLAG)

END

SELECT @PWCOUNT = 0

---------- DONE THIS LOOP ITERATION

---------- GET NEXT RECORD!

FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;

END

-- END OF BIG LOOP

-- REMEMBER TO UPDATE OH QTYS IN PARTWHSE FROM PARTBIN(S)




close PARTList

Deallocate PARTList

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf Of Len Hartka
Sent: Thursday, June 24, 2010 11:15 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] E9 question - %%% Urgent request- migration Time
Importance: High

Good Day:

Has anyone done their first Pass from Manage-2000 to E9 version 9.05
( 9.05.600B specifically).

Epicor is running now and for 50,000 Part#'s it will take 36 hours,
and we have 300,000 part#'s.
Then we have PartRev, PartPlant,PartWarehouse all with 300,00
records.

Epicor is doing it using DMT tool specific, and brand new, for
9.05.
For 9.05, the DMT tool is the only one they are using.

Having the migration take 2.5 weeks is beyond our time frame and
that assume it finishes. It has not completed any of the 6 csv files of
50,000 each.
We expected it to take about 2 days.

9.05 specific probably, buy I would be interested in 9.04.

We have turned of ALL anti-virus scanning, our servers are over the
specs specs, and we are running on our servers in-house because Epicor
did not have a 9.05 (SQL 2008) server available.

Anybody know a secret?

len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com>
<blocked::blocked::mailto:len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> >
E9 question - %%% Urgent request- migration Time

**************************************************************

Sun Automation Group

Celebrating

25 Years of Service

to the Corrugated Industry

**************************************************************

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]






This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.


[Non-text portions of this message have been removed]
I took my csv file and imported it into a holding table in which I analyzed and cleaned. Checked for dupe parts, ran queries for odd characters, ensured product groups, part classes, part types, warehouse codes, etc. were legit. I probably spent upwards of 3 weeks honing my part data in the "holding table". Once it was cleaned and polished to the best of my knowledge I floated it around to key users to inspect. Then I did multiple test imports and after each import I piloted the system with some key users..... ran reports, ran MRP, opened trackers, dashboards etc.



I had zero problems.



Initially, prior to embarking on this task, I simply logged into an empty database and from within Vantage I entered a Part with the bare minimum information/required fields. I analyzed what that entry from the part master created in various tables.



It was great doing it like this because I controlled it. It was easy to make changes, pre-clean the data, load and test multiple times.



It certainly doesn't hurt to try. Pervasive/DMT or Service Connect are horrible for Part Imports. For SQL customers I'm still amazed that they are adamant about using those clunky middleware data loaders when you have the brute power of SQL sitting at your fingertips.



For a certain subset of parts I was forced to use Service Connect. I needed to trigger the PLM checkbox so it then triggered a data sync with the PLM database. I had about 14,000 of those parts and using Service Connect it took me 1000 parts per hour. Horrible.







________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Len Hartka
Sent: Thursday, June 24, 2010 12:23 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time





Good Day Vic:

The aurgument against an SQL load, which would be fast, is that there would be no "Business Logic" checking ( valid Part#, allowed code, null required field, etc.)

How did you handle the checking of the data.

len.hartka@... <mailto:len.hartka%40sunautomation.com>

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf Of Vic Drecchio
Sent: Thursday, June 24, 2010 11:45 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time




Do the export from M2K yourself into delimited text file(s) and do the import yourself via SQL. It's not difficult. I did 50K parts in 12 minutes.

This was for ver 8.03 but the part schema for 9 is identical.

-- Procedure to Import INVENTORY PARTS

--

--

--

-- TIMCO

-- Vic Drecchio

-- Created: 2008-12-04

-- Last Modified: 2009-01-01

--

-- ASSUMPTIONS:

-- SQL Table Name for Input: "INV_IN"

-- Structure:

-- ID

-- ITEM

-- PARTNUM

-- DESC

-- TYPE

-- PRODGROUP

-- PARTCLASS

-- UOM

-- ONHAND

-- UNITCOST

-- WAREHOUSE

-- BIN

-- IMPORT777

--

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

use mfgsys803;

DECLARE @COMPANY VARCHAR(10),

@PLANT VARCHAR(10),

@1 INT,

@RECFLAG INT,

@DUPFLAG INT,

@PARTCOUNT INT,

@BINCOUNT INT,

@COSTCOUNT INT,

@PLANTCNT INT,

@WHCOUNT INT,

@PWCOUNT INT,

@ROWID INT,

@STOCKFLAG INT;

SELECT @COMPANY = 'TIMCO',

@PLANT = 'Mfgsys',

@1 = 1,

@RECFLAG = 777, --- USED TO ID IMPORTED RECORDS

@DUPFLAG = 999, --- USED TO ID RECORDS SKIPPED DUE TO NON-UNIQ PN'S

@PARTCOUNT = 0,

@BINCOUNT = 0,

@COSTCOUNT = 0,

@PLANTCNT = 0,

@WHCOUNT = 0,

@PWCOUNT = 0,

@ROWID = 0,

@STOCKFLAG = 0

;

--============================

-- SET VARIABLES FOR CURSOR FETCH

DECLARE

@ITEM NVARCHAR(50),

@PARTNUM NVARCHAR(50),

@DESC NVARCHAR(50),

@TYPE NVARCHAR(20),

@PRODGROUP NVARCHAR(20),

@PARTCLASS NVARCHAR(20),

@UOM NVARCHAR(10),

@ONHAND FLOAT,

@UNITCOST FLOAT,

@WAREHOUSE NVARCHAR(25),

@BIN NVARCHAR(25) ;

------------------------------------------

-- GET LIST OF RECORDS TO IMPORT

-- >> DUMP INTO CURSOR TO SCAN THRU

------------------------------------------

DECLARE PARTLIST CURSOR FOR

SELECT ID, ITEM, PARTNUM, [DESC], [TYPE], PRODGROUP,

PARTCLASS, UOM, ONHAND, UNITCOST, WAREHOUSE, BIN

FROM INV_IN;

OPEN PARTLIST

-------------------------------------------

-- BEGIN SCAN OF CURSOR

FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;

WHILE (@@FETCH_STATUS <> -1)

BEGIN;

-->>>>

-- SEE IF PART ALREADY EXISTS IN PART MASTER

SELECT @PARTCOUNT = COUNT(*) FROM PART P WHERE P.PARTNUM = @PARTNUM

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF @PARTCOUNT = 0

------- PART ----------------

-----------------------------

-- UNIQUE PART MASTER -------

-----------------------------

BEGIN;

-- PREPARE STOCKFLAG:

-- IF TYPE = PURCHASED, *STOCK*

-- IF TYPE = MANUFACTURED, *NON-STOCK*

IF UPPER(LEFT(@TYPE,1)) = 'M'

SELECT @STOCKFLAG = 1

ELSE

SELECT @STOCKFLAG = 0

-- INSERT INTO PART

INSERT INTO PART

(COMPANY, PARTNUM, SEARCHWORD, PARTDESCRIPTION, CLASSID, IUM, PUM, TYPECODE, NONSTOCK,

PURCHASINGFACTOR, UNITPRICE, PRICEPERCODE, INTERNALUNITPRICE, INTERNALPRICEPERCODE,

PRODCODE, COSTMETHOD, SHORTCHAR10, NUMBER20)

VALUES (

@COMPANY,

@PARTNUM,

LEFT(@DESC,8),

@DESC,

@PARTCLASS,

@UOM,

@UOM,

UPPER(LEFT(@TYPE,1)),

@STOCKFLAG, -- NON-STOCK ?

@1, -- PURCHASING FACTOR FLAG

@UNITCOST,

'E',

@UNITCOST,

'E',

@PRODGROUP,

'A',

@ITEM,

@RECFLAG

);

-----------------------

-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED

-----------------------

UPDATE INV_IN SET IMPORT777 = @RECFLAG WHERE ID = @ROWID

-- PUTS A 777 IN THE INPUT TABLE TO SHOW A UNIQUE PART WAS INSERTED

END;

IF @PARTCOUNT > 0

BEGIN;

-----------------------

-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED

-----------------------

UPDATE INV_IN SET IMPORT777 = @DUPFLAG WHERE ID = @ROWID

-- PUTS A 999 IN THE INPUT TABLE TO SHOW RECORD RECORD WAS NON-UNIQ

END;

SELECT @PARTCOUNT = 0 --- RESET IT

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

------- PART BIN ------------

-----------------------------

-- ON HAND QTYS ONLY --------

-----------------------------

IF @ONHAND > 0

BEGIN

---- CHECK TO SEE IF THE PART IS ALREADY IN THIS LOCATION

SELECT @BINCOUNT =

COUNT(*) FROM PARTBIN PB

WHERE PB.PARTNUM = @PARTNUM AND

PB.WAREHOUSECODE = @WAREHOUSE AND

PB.BINNUM = @BIN

IF @BINCOUNT > 0 -- PART ALREADY EXISTS IN THIS WH/BIN... ADD TO IT!

BEGIN

UPDATE PARTBIN

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE

PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE AND

BINNUM = @BIN;

END;

IF @BINCOUNT = 0 --- NOT IN THERE YET! INSERT!

BEGIN

INSERT INTO PARTBIN

(COMPANY, PARTNUM, WAREHOUSECODE, BINNUM, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @BIN, @ONHAND, @RECFLAG);

END;

SELECT @BINCOUNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

---- PARTCOST ---------

SELECT @COSTCOUNT = COUNT(*) FROM PARTCOST PC

WHERE PC.PARTNUM = @PARTNUM AND COSTID = @1

IF @COSTCOUNT = 0

BEGIN

INSERT INTO PARTCOST

(COMPANY, PARTNUM, COSTID, AVGMATERIALCOST, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @1, @UNITCOST, @RECFLAG);

SELECT @COSTCOUNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

----------PARTPLANT--------------------

SELECT @PLANTCNT = COUNT(*) FROM PARTPLANT PP

WHERE PP.PARTNUM = @PARTNUM AND PP.PLANT = @PLANT

IF @PLANTCNT = 0

BEGIN

INSERT INTO PARTPLANT

(COMPANY, PLANT, PARTNUM, PRIMWHSE, MRPRECALCNEEDED, PROCESSMRP, GENERATESUGG, SOURCETYPE,

COSTMETHOD, KITSHIPCOMPLETE, KITPRICING, KITPRINTCOMPSPS, KITPRINTCOMPSINV, QTYBEARING,

NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @1, @1, @1, 'P',

'A', @1, 'P', @1, @1, @1, @RECFLAG);

SELECT @PLANTCNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

------------- PARTWHSE ----------------

SELECT @WHCOUNT = COUNT(*) FROM PARTWHSE PW

WHERE PW.PARTNUM = @PARTNUM AND

PW.WAREHOUSECODE = @WAREHOUSE

IF @WHCOUNT = 0

BEGIN

INSERT INTO PARTWHSE

(COMPANY, PARTNUM, WAREHOUSECODE, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @ONHAND, @RECFLAG);

END

IF @WHCOUNT > 0

BEGIN

UPDATE PARTWHSE

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE;

END

SELECT @WHCOUNT = 0

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

--------------- PLANTWHSE ---------------

SELECT @PWCOUNT = COUNT(*) FROM PLANTWHSE PL

WHERE

PL.PARTNUM = @PARTNUM AND

PL.WAREHOUSECODE = @WAREHOUSE;

IF @PWCOUNT = 0

BEGIN

INSERT INTO PLANTWHSE

(COMPANY, PLANT, PARTNUM, WAREHOUSECODE, PRIMBIN, NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @BIN, @RECFLAG)

END

SELECT @PWCOUNT = 0

---------- DONE THIS LOOP ITERATION

---------- GET NEXT RECORD!

FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;

END

-- END OF BIG LOOP

-- REMEMBER TO UPDATE OH QTYS IN PARTWHSE FROM PARTBIN(S)

--

close PARTList

Deallocate PARTList

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On Behalf Of Len Hartka
Sent: Thursday, June 24, 2010 11:15 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] E9 question - %%% Urgent request- migration Time
Importance: High

Good Day:

Has anyone done their first Pass from Manage-2000 to E9 version 9.05
( 9.05.600B specifically).

Epicor is running now and for 50,000 Part#'s it will take 36 hours,
and we have 300,000 part#'s.
Then we have PartRev, PartPlant,PartWarehouse all with 300,00
records.

Epicor is doing it using DMT tool specific, and brand new, for
9.05.
For 9.05, the DMT tool is the only one they are using.

Having the migration take 2.5 weeks is beyond our time frame and
that assume it finishes. It has not completed any of the 6 csv files of
50,000 each.
We expected it to take about 2 days.

9.05 specific probably, buy I would be interested in 9.04.

We have turned of ALL anti-virus scanning, our servers are over the
specs specs, and we are running on our servers in-house because Epicor
did not have a 9.05 (SQL 2008) server available.

Anybody know a secret?

len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com>
<blocked::blocked::mailto:len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> >
E9 question - %%% Urgent request- migration Time

**************************************************************

Sun Automation Group

Celebrating

25 Years of Service

to the Corrugated Industry

**************************************************************

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]






This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
Vic,



The problem with loading direct into SQL is that you bypass all logic you are doing these weeks of analysis checking data is correct and you still may have missed something say another table is defaulted or you misinterpreted how something worked, or after a patch the developers now make use of another table or update sequence tables. It can be done but has to be done with immense care and can catch even the most experienced out.



Take for instance the T/SQL you have defined below, it will cause issues because it doesn't handle the sysrowid and sysrevid fields.

It doesn't support serialised items, or fifo costing or correctly handle the on hand quantity, it doesn't honour the rounding engine rules, it doesn't handle the parttran table, it doesn't default values as the BO's would.

The list continues.



NOW I am not saying this doesn't work for you but it is not a generalized solution for everyone and is subject to specifically how you have configured your system and profiling.

You could not expect someone without SQL skills to use such an approach.

Loading directly into SQL or Progress is the fastest option without doubt, but it may just seem faster to begin with but then when MRP hangs or you are working with support to resolve an issue or you are live and something stops working, there are more unknowns which may be impossible to replicate.



When the system is working correctly importing via the business objects is the best option, if you miss something or make a mistake it is identified for you.

Yes using the business objects is slower than direct via SQL, but if it is really running slowly then the whole application will also, it's a good benchmark for the application and a good stress test for the server.



In this instance there have been some serious speed issues with 905.600B mainly not directly EPICOR's fault as it was down to an open edge service pack (although it should have been identified during testing) this is why 905.600B was pulled and came out as 905.600B2 and this only impacted on SQL customers.



Regards,

Stephen



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Vic Drecchio
Sent: 24 June 2010 18:12
To: vantage@yahoogroups.com
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time





I took my csv file and imported it into a holding table in which I analyzed and cleaned. Checked for dupe parts, ran queries for odd characters, ensured product groups, part classes, part types, warehouse codes, etc. were legit. I probably spent upwards of 3 weeks honing my part data in the "holding table". Once it was cleaned and polished to the best of my knowledge I floated it around to key users to inspect. Then I did multiple test imports and after each import I piloted the system with some key users..... ran reports, ran MRP, opened trackers, dashboards etc.

I had zero problems.

Initially, prior to embarking on this task, I simply logged into an empty database and from within Vantage I entered a Part with the bare minimum information/required fields. I analyzed what that entry from the part master created in various tables.

It was great doing it like this because I controlled it. It was easy to make changes, pre-clean the data, load and test multiple times.

It certainly doesn't hurt to try. Pervasive/DMT or Service Connect are horrible for Part Imports. For SQL customers I'm still amazed that they are adamant about using those clunky middleware data loaders when you have the brute power of SQL sitting at your fingertips.

For a certain subset of parts I was forced to use Service Connect. I needed to trigger the PLM checkbox so it then triggered a data sync with the PLM database. I had about 14,000 of those parts and using Service Connect it took me 1000 parts per hour. Horrible.

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf Of Len Hartka
Sent: Thursday, June 24, 2010 12:23 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time

Good Day Vic:

The aurgument against an SQL load, which would be fast, is that there would be no "Business Logic" checking ( valid Part#, allowed code, null required field, etc.)

How did you handle the checking of the data.

len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com>

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On Behalf Of Vic Drecchio
Sent: Thursday, June 24, 2010 11:45 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time

Do the export from M2K yourself into delimited text file(s) and do the import yourself via SQL. It's not difficult. I did 50K parts in 12 minutes.

This was for ver 8.03 but the part schema for 9 is identical.

-- Procedure to Import INVENTORY PARTS

--

--

--

-- TIMCO

-- Vic Drecchio

-- Created: 2008-12-04

-- Last Modified: 2009-01-01

--

-- ASSUMPTIONS:

-- SQL Table Name for Input: "INV_IN"

-- Structure:

-- ID

-- ITEM

-- PARTNUM

-- DESC

-- TYPE

-- PRODGROUP

-- PARTCLASS

-- UOM

-- ONHAND

-- UNITCOST

-- WAREHOUSE

-- BIN

-- IMPORT777

--

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

use mfgsys803;

DECLARE @COMPANY VARCHAR(10),

@PLANT VARCHAR(10),

@1 INT,

@RECFLAG INT,

@DUPFLAG INT,

@PARTCOUNT INT,

@BINCOUNT INT,

@COSTCOUNT INT,

@PLANTCNT INT,

@WHCOUNT INT,

@PWCOUNT INT,

@ROWID INT,

@STOCKFLAG INT;

SELECT @COMPANY = 'TIMCO',

@PLANT = 'Mfgsys',

@1 = 1,

@RECFLAG = 777, --- USED TO ID IMPORTED RECORDS

@DUPFLAG = 999, --- USED TO ID RECORDS SKIPPED DUE TO NON-UNIQ PN'S

@PARTCOUNT = 0,

@BINCOUNT = 0,

@COSTCOUNT = 0,

@PLANTCNT = 0,

@WHCOUNT = 0,

@PWCOUNT = 0,

@ROWID = 0,

@STOCKFLAG = 0

;

--============================

-- SET VARIABLES FOR CURSOR FETCH

DECLARE

@ITEM NVARCHAR(50),

@PARTNUM NVARCHAR(50),

@DESC NVARCHAR(50),

@TYPE NVARCHAR(20),

@PRODGROUP NVARCHAR(20),

@PARTCLASS NVARCHAR(20),

@UOM NVARCHAR(10),

@ONHAND FLOAT,

@UNITCOST FLOAT,

@WAREHOUSE NVARCHAR(25),

@BIN NVARCHAR(25) ;

------------------------------------------

-- GET LIST OF RECORDS TO IMPORT

-- >> DUMP INTO CURSOR TO SCAN THRU

------------------------------------------

DECLARE PARTLIST CURSOR FOR

SELECT ID, ITEM, PARTNUM, [DESC], [TYPE], PRODGROUP,

PARTCLASS, UOM, ONHAND, UNITCOST, WAREHOUSE, BIN

FROM INV_IN;

OPEN PARTLIST

-------------------------------------------

-- BEGIN SCAN OF CURSOR

FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;

WHILE (@@FETCH_STATUS <> -1)

BEGIN;

-->>>>

-- SEE IF PART ALREADY EXISTS IN PART MASTER

SELECT @PARTCOUNT = COUNT(*) FROM PART P WHERE P.PARTNUM = @PARTNUM

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF @PARTCOUNT = 0

------- PART ----------------

-----------------------------

-- UNIQUE PART MASTER -------

-----------------------------

BEGIN;

-- PREPARE STOCKFLAG:

-- IF TYPE = PURCHASED, *STOCK*

-- IF TYPE = MANUFACTURED, *NON-STOCK*

IF UPPER(LEFT(@TYPE,1)) = 'M'

SELECT @STOCKFLAG = 1

ELSE

SELECT @STOCKFLAG = 0

-- INSERT INTO PART

INSERT INTO PART

(COMPANY, PARTNUM, SEARCHWORD, PARTDESCRIPTION, CLASSID, IUM, PUM, TYPECODE, NONSTOCK,

PURCHASINGFACTOR, UNITPRICE, PRICEPERCODE, INTERNALUNITPRICE, INTERNALPRICEPERCODE,

PRODCODE, COSTMETHOD, SHORTCHAR10, NUMBER20)

VALUES (

@COMPANY,

@PARTNUM,

LEFT(@DESC,8),

@DESC,

@PARTCLASS,

@UOM,

@UOM,

UPPER(LEFT(@TYPE,1)),

@STOCKFLAG, -- NON-STOCK ?

@1, -- PURCHASING FACTOR FLAG

@UNITCOST,

'E',

@UNITCOST,

'E',

@PRODGROUP,

'A',

@ITEM,

@RECFLAG

);

-----------------------

-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED

-----------------------

UPDATE INV_IN SET IMPORT777 = @RECFLAG WHERE ID = @ROWID

-- PUTS A 777 IN THE INPUT TABLE TO SHOW A UNIQUE PART WAS INSERTED

END;

IF @PARTCOUNT > 0

BEGIN;

-----------------------

-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED

-----------------------

UPDATE INV_IN SET IMPORT777 = @DUPFLAG WHERE ID = @ROWID

-- PUTS A 999 IN THE INPUT TABLE TO SHOW RECORD RECORD WAS NON-UNIQ

END;

SELECT @PARTCOUNT = 0 --- RESET IT

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

------- PART BIN ------------

-----------------------------

-- ON HAND QTYS ONLY --------

-----------------------------

IF @ONHAND > 0

BEGIN

---- CHECK TO SEE IF THE PART IS ALREADY IN THIS LOCATION

SELECT @BINCOUNT =

COUNT(*) FROM PARTBIN PB

WHERE PB.PARTNUM = @PARTNUM AND

PB.WAREHOUSECODE = @WAREHOUSE AND

PB.BINNUM = @BIN

IF @BINCOUNT > 0 -- PART ALREADY EXISTS IN THIS WH/BIN... ADD TO IT!

BEGIN

UPDATE PARTBIN

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE

PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE AND

BINNUM = @BIN;

END;

IF @BINCOUNT = 0 --- NOT IN THERE YET! INSERT!

BEGIN

INSERT INTO PARTBIN

(COMPANY, PARTNUM, WAREHOUSECODE, BINNUM, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @BIN, @ONHAND, @RECFLAG);

END;

SELECT @BINCOUNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

---- PARTCOST ---------

SELECT @COSTCOUNT = COUNT(*) FROM PARTCOST PC

WHERE PC.PARTNUM = @PARTNUM AND COSTID = @1

IF @COSTCOUNT = 0

BEGIN

INSERT INTO PARTCOST

(COMPANY, PARTNUM, COSTID, AVGMATERIALCOST, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @1, @UNITCOST, @RECFLAG);

SELECT @COSTCOUNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

----------PARTPLANT--------------------

SELECT @PLANTCNT = COUNT(*) FROM PARTPLANT PP

WHERE PP.PARTNUM = @PARTNUM AND PP.PLANT = @PLANT

IF @PLANTCNT = 0

BEGIN

INSERT INTO PARTPLANT

(COMPANY, PLANT, PARTNUM, PRIMWHSE, MRPRECALCNEEDED, PROCESSMRP, GENERATESUGG, SOURCETYPE,

COSTMETHOD, KITSHIPCOMPLETE, KITPRICING, KITPRINTCOMPSPS, KITPRINTCOMPSINV, QTYBEARING,

NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @1, @1, @1, 'P',

'A', @1, 'P', @1, @1, @1, @RECFLAG);

SELECT @PLANTCNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

------------- PARTWHSE ----------------

SELECT @WHCOUNT = COUNT(*) FROM PARTWHSE PW

WHERE PW.PARTNUM = @PARTNUM AND

PW.WAREHOUSECODE = @WAREHOUSE

IF @WHCOUNT = 0

BEGIN

INSERT INTO PARTWHSE

(COMPANY, PARTNUM, WAREHOUSECODE, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @ONHAND, @RECFLAG);

END

IF @WHCOUNT > 0

BEGIN

UPDATE PARTWHSE

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE;

END

SELECT @WHCOUNT = 0

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

--------------- PLANTWHSE ---------------

SELECT @PWCOUNT = COUNT(*) FROM PLANTWHSE PL

WHERE

PL.PARTNUM = @PARTNUM AND

PL.WAREHOUSECODE = @WAREHOUSE;

IF @PWCOUNT = 0

BEGIN

INSERT INTO PLANTWHSE

(COMPANY, PLANT, PARTNUM, WAREHOUSECODE, PRIMBIN, NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @BIN, @RECFLAG)

END

SELECT @PWCOUNT = 0

---------- DONE THIS LOOP ITERATION

---------- GET NEXT RECORD!

FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;

END

-- END OF BIG LOOP

-- REMEMBER TO UPDATE OH QTYS IN PARTWHSE FROM PARTBIN(S)

--

close PARTList

Deallocate PARTList

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On Behalf Of Len Hartka
Sent: Thursday, June 24, 2010 11:15 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] E9 question - %%% Urgent request- migration Time
Importance: High

Good Day:

Has anyone done their first Pass from Manage-2000 to E9 version 9.05
( 9.05.600B specifically).

Epicor is running now and for 50,000 Part#'s it will take 36 hours,
and we have 300,000 part#'s.
Then we have PartRev, PartPlant,PartWarehouse all with 300,00
records.

Epicor is doing it using DMT tool specific, and brand new, for
9.05.
For 9.05, the DMT tool is the only one they are using.

Having the migration take 2.5 weeks is beyond our time frame and
that assume it finishes. It has not completed any of the 6 csv files of
50,000 each.
We expected it to take about 2 days.

9.05 specific probably, buy I would be interested in 9.04.

We have turned of ALL anti-virus scanning, our servers are over the
specs specs, and we are running on our servers in-house because Epicor
did not have a 9.05 (SQL 2008) server available.

Anybody know a secret?

len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com>
<blocked::blocked::mailto:len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> >
E9 question - %%% Urgent request- migration Time

**************************************************************

Sun Automation Group

Celebrating

25 Years of Service

to the Corrugated Industry

**************************************************************

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
That is absurd. Did they tell you this in time to make an informed decision?
Do you have any leverage (owe them money? - Don't pay and hold it in an escrow acct until someone gets on a plane and suddenly discovers how to speed things up.)

How much cost to you is 2+ weeks of double entry (to catch up on what was missed during import laod)? I'd discuss w/ an atty so you know your options.

Would the world end if you bailed, stayed on M2M & looked again at your 2nd & 3rd selection process finalists?

Better to recognize a mistake now than later (when you're in even deeper). Epicor support doesn't get better with age.

Play some hardball.

--- Original Message ---
From:"Len Hartka" <len.hartka@...>
Sent:Thu 6/24/10 11:14 am
To:<vantage@yahoogroups.com>
Subj:[Vantage] E9 question - %%% Urgent request- migration Time

Good Day:

Has anyone done their first Pass from Manage-2000 to E9 version 9.05
( 9.05.600B specifically).

Epicor is running now and for 50,000 Part#'s it will take 36 hours,
and we have 300,000 part#'s.
Then we have PartRev, PartPlant,PartWarehouse all with 300,00
records.

Epicor is doing it using DMT tool specific, and brand new, for
9.05.
For 9.05, the DMT tool is the only one they are using.

Having the migration take 2.5 weeks is beyond our time frame and
that assume it finishes. It has not completed any of the 6 csv files of
50,000 each.
We expected it to take about 2 days.

9.05 specific probably, buy I would be interested in 9.04.

We have turned of ALL anti-virus scanning, our servers are over the
specs specs, and we are running on our servers in-house because Epicor
did not have a 9.05 (SQL 2008) server available.

Anybody know a secret?



len.hartka@...
<blocked::blocked::mailto:len.hartka@...>
E9 question - %%% Urgent request- migration Time


**************************************************************

Sun Automation Group

Celebrating

25 Years of Service

to the Corrugated Industry

**************************************************************




This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.


[Non-text portions of this message have been removed]
Before/after compare in excel or access.

Not a SQL junkie but can't it be set to track record load fails?

--- Original Message ---
From:"Len Hartka" <len.hartka@...>
Sent:Thu 6/24/10 12:33 pm
To:<vantage@yahoogroups.com>
Subj:RE: [Vantage] E9 question - %%% Urgent request- migration Time

Good Day Vic:

The aurgument against an SQL load, which would be fast, is that there would be no "Business Logic" checking ( valid Part#, allowed code, null required field, etc.)

How did you handle the checking of the data.

len.hartka@...

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Vic Drecchio
Sent: Thursday, June 24, 2010 11:45 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time




Do the export from M2K yourself into delimited text file(s) and do the import yourself via SQL. It's not difficult. I did 50K parts in 12 minutes.

This was for ver 8.03 but the part schema for 9 is identical.

-- Procedure to Import INVENTORY PARTS








-- TIMCO

-- Vic Drecchio

-- Created: 2008-12-04

-- Last Modified: 2009-01-01




-- ASSUMPTIONS:

-- SQL Table Name for Input: "INV_IN"

-- Structure:

-- ID

-- ITEM

-- PARTNUM

-- DESC

-- TYPE

-- PRODGROUP

-- PARTCLASS

-- UOM

-- ONHAND

-- UNITCOST

-- WAREHOUSE

-- BIN

-- IMPORT777




set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

use mfgsys803;

DECLARE @COMPANY VARCHAR(10),

@PLANT VARCHAR(10),

@1 INT,

@RECFLAG INT,

@DUPFLAG INT,

@PARTCOUNT INT,

@BINCOUNT INT,

@COSTCOUNT INT,

@PLANTCNT INT,

@WHCOUNT INT,

@PWCOUNT INT,

@ROWID INT,

@STOCKFLAG INT;

SELECT @COMPANY = 'TIMCO',

@PLANT = 'Mfgsys',

@1 = 1,

@RECFLAG = 777, --- USED TO ID IMPORTED RECORDS

@DUPFLAG = 999, --- USED TO ID RECORDS SKIPPED DUE TO NON-UNIQ PN'S

@PARTCOUNT = 0,

@BINCOUNT = 0,

@COSTCOUNT = 0,

@PLANTCNT = 0,

@WHCOUNT = 0,

@PWCOUNT = 0,

@ROWID = 0,

@STOCKFLAG = 0

;

--============================

-- SET VARIABLES FOR CURSOR FETCH

DECLARE

@ITEM NVARCHAR(50),

@PARTNUM NVARCHAR(50),

@DESC NVARCHAR(50),

@TYPE NVARCHAR(20),

@PRODGROUP NVARCHAR(20),

@PARTCLASS NVARCHAR(20),

@UOM NVARCHAR(10),

@ONHAND FLOAT,

@UNITCOST FLOAT,

@WAREHOUSE NVARCHAR(25),

@BIN NVARCHAR(25) ;




-- GET LIST OF RECORDS TO IMPORT

-- >> DUMP INTO CURSOR TO SCAN THRU




DECLARE PARTLIST CURSOR FOR

SELECT ID, ITEM, PARTNUM, [DESC], [TYPE], PRODGROUP,

PARTCLASS, UOM, ONHAND, UNITCOST, WAREHOUSE, BIN

FROM INV_IN;

OPEN PARTLIST




-- BEGIN SCAN OF CURSOR

FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;
Good Day:

Epicor has really come through and a new version of the DMT tool and a patch 9.05.600B.2.

We are now running with 8 threads and each thread is running at 38 RPM ( records per minute), so now 304 RPM through put.

That gets it down to 4.5 days MAX since some files will run faster. We will know more in a day or two.

So if we do the BOM ( PartMatl) early, it will work out.

It seems that the "Business Logic " checking is the reason for the slowness, but absolutely needed.


Len





________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of robertb_versa@...
Sent: Thursday, June 24, 2010 9:54 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] E9 question - %%% Urgent request- migration Time




That is absurd. Did they tell you this in time to make an informed decision?
Do you have any leverage (owe them money? - Don't pay and hold it in an escrow acct until someone gets on a plane and suddenly discovers how to speed things up.)

How much cost to you is 2+ weeks of double entry (to catch up on what was missed during import laod)? I'd discuss w/ an atty so you know your options.

Would the world end if you bailed, stayed on M2M & looked again at your 2nd & 3rd selection process finalists?

Better to recognize a mistake now than later (when you're in even deeper). Epicor support doesn't get better with age.

Play some hardball.

--- Original Message ---
From:"Len Hartka" <len.hartka@... <mailto:len.hartka%40sunautomation.com> >
Sent:Thu 6/24/10 11:14 am
To:<vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> >
Subj:[Vantage] E9 question - %%% Urgent request- migration Time

Good Day:

Has anyone done their first Pass from Manage-2000 to E9 version 9.05
( 9.05.600B specifically).

Epicor is running now and for 50,000 Part#'s it will take 36 hours,
and we have 300,000 part#'s.
Then we have PartRev, PartPlant,PartWarehouse all with 300,00
records.

Epicor is doing it using DMT tool specific, and brand new, for
9.05.
For 9.05, the DMT tool is the only one they are using.

Having the migration take 2.5 weeks is beyond our time frame and
that assume it finishes. It has not completed any of the 6 csv files of
50,000 each.
We expected it to take about 2 days.

9.05 specific probably, buy I would be interested in 9.04.

We have turned of ALL anti-virus scanning, our servers are over the
specs specs, and we are running on our servers in-house because Epicor
did not have a 9.05 (SQL 2008) server available.

Anybody know a secret?



len.hartka@... <mailto:len.hartka%40sunautomation.com>
<blocked::blocked::mailto:len.hartka@... <mailto:len.hartka%40sunautomation.com> >
E9 question - %%% Urgent request- migration Time


**************************************************************

Sun Automation Group

Celebrating

25 Years of Service

to the Corrugated Industry

**************************************************************

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]






This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.


[Non-text portions of this message have been removed]
Stephen, respectfully, the only sentence of your email I agree with is expecting someone without SQL skills to perform this is risky. I agree 100%. This isn't for the novice or intermediate developer/admin with introductory knowledge of Epicor's schema. I've been working with various databases for almost 20 years (now I really feel old) and working with various flavors of Epicor software for a decade.



In a perfect world without clocks and time deadlines, yes, using the import tools supplied or FTPing your data templates to Epicor is the way to go; undoubtedly. But with the size of most of our databases these days and the immense amount of legacy data desired in the new system, this is rarely do-able over a weekend especially with a time buffer for errors.



Yes, technically I'm bypassing the business logic. But effectively I'm not. Prior to doing this I traced the data and followed the logic. When I perform the imports I simulate and adhere to the Vantage business logic 100%. I know which columns have default values, which columns can be null/blank and keep the relationship and integrity in tact.



In SQL there are no sysrowid and sysrevid fields. We have Progress_RecID_Ident_ fields which are identity columns that auto assign unique numbers and the Progress_RecID is populated via triggers. So I never have to worry about them. Upon an insert those fields are auto-populated. PartTran table? Nobody should ever populate the PartTran table. Why would you want that transactional history in your new database? I've performed more conversions than I have fingers and toes for many different companies and customers and never imported historical transactions. Epicor even recommends against that.



It most certainly does handle Onhand quantities via the PartBin and PartWhse tables. Serialized parts and Lot controlled parts? No problems.



With some creativity, attention to detail, careful testing and a thorough understanding of the database schema and business logic you can do it all in SQL.



Now..... would I attempt this with a Progress database? Heck no. But then again, that's why I would choose SQL over Progress 10 days out of the week. :-)



By the way, I authored Epicor's "Signature Data Conversion Methodology" document. I'm fairly confident in what can be done and what cannot be done. Epicor doesn't like customers fiddling with raw data and I don't blame them. But if you know what you're doing..... :-)





TGIF...... :-)







Vic





________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Stephen Edginton
Sent: Thursday, June 24, 2010 4:11 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time





Vic,

The problem with loading direct into SQL is that you bypass all logic you are doing these weeks of analysis checking data is correct and you still may have missed something say another table is defaulted or you misinterpreted how something worked, or after a patch the developers now make use of another table or update sequence tables. It can be done but has to be done with immense care and can catch even the most experienced out.

Take for instance the T/SQL you have defined below, it will cause issues because it doesn't handle the sysrowid and sysrevid fields.

It doesn't support serialised items, or fifo costing or correctly handle the on hand quantity, it doesn't honour the rounding engine rules, it doesn't handle the parttran table, it doesn't default values as the BO's would.

The list continues.

NOW I am not saying this doesn't work for you but it is not a generalized solution for everyone and is subject to specifically how you have configured your system and profiling.

You could not expect someone without SQL skills to use such an approach.

Loading directly into SQL or Progress is the fastest option without doubt, but it may just seem faster to begin with but then when MRP hangs or you are working with support to resolve an issue or you are live and something stops working, there are more unknowns which may be impossible to replicate.

When the system is working correctly importing via the business objects is the best option, if you miss something or make a mistake it is identified for you.

Yes using the business objects is slower than direct via SQL, but if it is really running slowly then the whole application will also, it's a good benchmark for the application and a good stress test for the server.

In this instance there have been some serious speed issues with 905.600B mainly not directly EPICOR's fault as it was down to an open edge service pack (although it should have been identified during testing) this is why 905.600B was pulled and came out as 905.600B2 and this only impacted on SQL customers.

Regards,

Stephen

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf Of Vic Drecchio
Sent: 24 June 2010 18:12
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time

I took my csv file and imported it into a holding table in which I analyzed and cleaned. Checked for dupe parts, ran queries for odd characters, ensured product groups, part classes, part types, warehouse codes, etc. were legit. I probably spent upwards of 3 weeks honing my part data in the "holding table". Once it was cleaned and polished to the best of my knowledge I floated it around to key users to inspect. Then I did multiple test imports and after each import I piloted the system with some key users..... ran reports, ran MRP, opened trackers, dashboards etc.

I had zero problems.

Initially, prior to embarking on this task, I simply logged into an empty database and from within Vantage I entered a Part with the bare minimum information/required fields. I analyzed what that entry from the part master created in various tables.

It was great doing it like this because I controlled it. It was easy to make changes, pre-clean the data, load and test multiple times.

It certainly doesn't hurt to try. Pervasive/DMT or Service Connect are horrible for Part Imports. For SQL customers I'm still amazed that they are adamant about using those clunky middleware data loaders when you have the brute power of SQL sitting at your fingertips.

For a certain subset of parts I was forced to use Service Connect. I needed to trigger the PLM checkbox so it then triggered a data sync with the PLM database. I had about 14,000 of those parts and using Service Connect it took me 1000 parts per hour. Horrible.

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On Behalf Of Len Hartka
Sent: Thursday, June 24, 2010 12:23 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time

Good Day Vic:

The aurgument against an SQL load, which would be fast, is that there would be no "Business Logic" checking ( valid Part#, allowed code, null required field, etc.)

How did you handle the checking of the data.

len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com>

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On Behalf Of Vic Drecchio
Sent: Thursday, June 24, 2010 11:45 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] E9 question - %%% Urgent request- migration Time

Do the export from M2K yourself into delimited text file(s) and do the import yourself via SQL. It's not difficult. I did 50K parts in 12 minutes.

This was for ver 8.03 but the part schema for 9 is identical.

-- Procedure to Import INVENTORY PARTS

--

--

--

-- TIMCO

-- Vic Drecchio

-- Created: 2008-12-04

-- Last Modified: 2009-01-01

--

-- ASSUMPTIONS:

-- SQL Table Name for Input: "INV_IN"

-- Structure:

-- ID

-- ITEM

-- PARTNUM

-- DESC

-- TYPE

-- PRODGROUP

-- PARTCLASS

-- UOM

-- ONHAND

-- UNITCOST

-- WAREHOUSE

-- BIN

-- IMPORT777

--

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

use mfgsys803;

DECLARE @COMPANY VARCHAR(10),

@PLANT VARCHAR(10),

@1 INT,

@RECFLAG INT,

@DUPFLAG INT,

@PARTCOUNT INT,

@BINCOUNT INT,

@COSTCOUNT INT,

@PLANTCNT INT,

@WHCOUNT INT,

@PWCOUNT INT,

@ROWID INT,

@STOCKFLAG INT;

SELECT @COMPANY = 'TIMCO',

@PLANT = 'Mfgsys',

@1 = 1,

@RECFLAG = 777, --- USED TO ID IMPORTED RECORDS

@DUPFLAG = 999, --- USED TO ID RECORDS SKIPPED DUE TO NON-UNIQ PN'S

@PARTCOUNT = 0,

@BINCOUNT = 0,

@COSTCOUNT = 0,

@PLANTCNT = 0,

@WHCOUNT = 0,

@PWCOUNT = 0,

@ROWID = 0,

@STOCKFLAG = 0

;

--============================

-- SET VARIABLES FOR CURSOR FETCH

DECLARE

@ITEM NVARCHAR(50),

@PARTNUM NVARCHAR(50),

@DESC NVARCHAR(50),

@TYPE NVARCHAR(20),

@PRODGROUP NVARCHAR(20),

@PARTCLASS NVARCHAR(20),

@UOM NVARCHAR(10),

@ONHAND FLOAT,

@UNITCOST FLOAT,

@WAREHOUSE NVARCHAR(25),

@BIN NVARCHAR(25) ;

------------------------------------------

-- GET LIST OF RECORDS TO IMPORT

-- >> DUMP INTO CURSOR TO SCAN THRU

------------------------------------------

DECLARE PARTLIST CURSOR FOR

SELECT ID, ITEM, PARTNUM, [DESC], [TYPE], PRODGROUP,

PARTCLASS, UOM, ONHAND, UNITCOST, WAREHOUSE, BIN

FROM INV_IN;

OPEN PARTLIST

-------------------------------------------

-- BEGIN SCAN OF CURSOR

FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;

WHILE (@@FETCH_STATUS <> -1)

BEGIN;

-->>>>

-- SEE IF PART ALREADY EXISTS IN PART MASTER

SELECT @PARTCOUNT = COUNT(*) FROM PART P WHERE P.PARTNUM = @PARTNUM

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF @PARTCOUNT = 0

------- PART ----------------

-----------------------------

-- UNIQUE PART MASTER -------

-----------------------------

BEGIN;

-- PREPARE STOCKFLAG:

-- IF TYPE = PURCHASED, *STOCK*

-- IF TYPE = MANUFACTURED, *NON-STOCK*

IF UPPER(LEFT(@TYPE,1)) = 'M'

SELECT @STOCKFLAG = 1

ELSE

SELECT @STOCKFLAG = 0

-- INSERT INTO PART

INSERT INTO PART

(COMPANY, PARTNUM, SEARCHWORD, PARTDESCRIPTION, CLASSID, IUM, PUM, TYPECODE, NONSTOCK,

PURCHASINGFACTOR, UNITPRICE, PRICEPERCODE, INTERNALUNITPRICE, INTERNALPRICEPERCODE,

PRODCODE, COSTMETHOD, SHORTCHAR10, NUMBER20)

VALUES (

@COMPANY,

@PARTNUM,

LEFT(@DESC,8),

@DESC,

@PARTCLASS,

@UOM,

@UOM,

UPPER(LEFT(@TYPE,1)),

@STOCKFLAG, -- NON-STOCK ?

@1, -- PURCHASING FACTOR FLAG

@UNITCOST,

'E',

@UNITCOST,

'E',

@PRODGROUP,

'A',

@ITEM,

@RECFLAG

);

-----------------------

-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED

-----------------------

UPDATE INV_IN SET IMPORT777 = @RECFLAG WHERE ID = @ROWID

-- PUTS A 777 IN THE INPUT TABLE TO SHOW A UNIQUE PART WAS INSERTED

END;

IF @PARTCOUNT > 0

BEGIN;

-----------------------

-- UPDATE THE INV_IN TABLE TO SHOW ROW IMPORTED

-----------------------

UPDATE INV_IN SET IMPORT777 = @DUPFLAG WHERE ID = @ROWID

-- PUTS A 999 IN THE INPUT TABLE TO SHOW RECORD RECORD WAS NON-UNIQ

END;

SELECT @PARTCOUNT = 0 --- RESET IT

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

------- PART BIN ------------

-----------------------------

-- ON HAND QTYS ONLY --------

-----------------------------

IF @ONHAND > 0

BEGIN

---- CHECK TO SEE IF THE PART IS ALREADY IN THIS LOCATION

SELECT @BINCOUNT =

COUNT(*) FROM PARTBIN PB

WHERE PB.PARTNUM = @PARTNUM AND

PB.WAREHOUSECODE = @WAREHOUSE AND

PB.BINNUM = @BIN

IF @BINCOUNT > 0 -- PART ALREADY EXISTS IN THIS WH/BIN... ADD TO IT!

BEGIN

UPDATE PARTBIN

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE

PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE AND

BINNUM = @BIN;

END;

IF @BINCOUNT = 0 --- NOT IN THERE YET! INSERT!

BEGIN

INSERT INTO PARTBIN

(COMPANY, PARTNUM, WAREHOUSECODE, BINNUM, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @BIN, @ONHAND, @RECFLAG);

END;

SELECT @BINCOUNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

---- PARTCOST ---------

SELECT @COSTCOUNT = COUNT(*) FROM PARTCOST PC

WHERE PC.PARTNUM = @PARTNUM AND COSTID = @1

IF @COSTCOUNT = 0

BEGIN

INSERT INTO PARTCOST

(COMPANY, PARTNUM, COSTID, AVGMATERIALCOST, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @1, @UNITCOST, @RECFLAG);

SELECT @COSTCOUNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

----------PARTPLANT--------------------

SELECT @PLANTCNT = COUNT(*) FROM PARTPLANT PP

WHERE PP.PARTNUM = @PARTNUM AND PP.PLANT = @PLANT

IF @PLANTCNT = 0

BEGIN

INSERT INTO PARTPLANT

(COMPANY, PLANT, PARTNUM, PRIMWHSE, MRPRECALCNEEDED, PROCESSMRP, GENERATESUGG, SOURCETYPE,

COSTMETHOD, KITSHIPCOMPLETE, KITPRICING, KITPRINTCOMPSPS, KITPRINTCOMPSINV, QTYBEARING,

NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @1, @1, @1, 'P',

'A', @1, 'P', @1, @1, @1, @RECFLAG);

SELECT @PLANTCNT = 0

END;

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

------------- PARTWHSE ----------------

SELECT @WHCOUNT = COUNT(*) FROM PARTWHSE PW

WHERE PW.PARTNUM = @PARTNUM AND

PW.WAREHOUSECODE = @WAREHOUSE

IF @WHCOUNT = 0

BEGIN

INSERT INTO PARTWHSE

(COMPANY, PARTNUM, WAREHOUSECODE, ONHANDQTY, NUMBER10)

VALUES

(@COMPANY, @PARTNUM, @WAREHOUSE, @ONHAND, @RECFLAG);

END

IF @WHCOUNT > 0

BEGIN

UPDATE PARTWHSE

SET ONHANDQTY = (ONHANDQTY + @ONHAND),

NUMBER10 = @RECFLAG

WHERE PARTNUM = @PARTNUM AND

WAREHOUSECODE = @WAREHOUSE;

END

SELECT @WHCOUNT = 0

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

----------------------------------------------------------

--------------- PLANTWHSE ---------------

SELECT @PWCOUNT = COUNT(*) FROM PLANTWHSE PL

WHERE

PL.PARTNUM = @PARTNUM AND

PL.WAREHOUSECODE = @WAREHOUSE;

IF @PWCOUNT = 0

BEGIN

INSERT INTO PLANTWHSE

(COMPANY, PLANT, PARTNUM, WAREHOUSECODE, PRIMBIN, NUMBER10)

VALUES

(@COMPANY, @PLANT, @PARTNUM, @WAREHOUSE, @BIN, @RECFLAG)

END

SELECT @PWCOUNT = 0

---------- DONE THIS LOOP ITERATION

---------- GET NEXT RECORD!

FETCH NEXT FROM PARTLIST

INTO @ROWID,

@ITEM,

@PARTNUM,

@DESC,

@TYPE,

@PRODGROUP,

@PARTCLASS,

@UOM,

@ONHAND,

@UNITCOST,

@WAREHOUSE,

@BIN ;

END

-- END OF BIG LOOP

-- REMEMBER TO UPDATE OH QTYS IN PARTWHSE FROM PARTBIN(S)

--

close PARTList

Deallocate PARTList

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On Behalf Of Len Hartka
Sent: Thursday, June 24, 2010 11:15 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] E9 question - %%% Urgent request- migration Time
Importance: High

Good Day:

Has anyone done their first Pass from Manage-2000 to E9 version 9.05
( 9.05.600B specifically).

Epicor is running now and for 50,000 Part#'s it will take 36 hours,
and we have 300,000 part#'s.
Then we have PartRev, PartPlant,PartWarehouse all with 300,00
records.

Epicor is doing it using DMT tool specific, and brand new, for
9.05.
For 9.05, the DMT tool is the only one they are using.

Having the migration take 2.5 weeks is beyond our time frame and
that assume it finishes. It has not completed any of the 6 csv files of
50,000 each.
We expected it to take about 2 days.

9.05 specific probably, buy I would be interested in 9.04.

We have turned of ALL anti-virus scanning, our servers are over the
specs specs, and we are running on our servers in-house because Epicor
did not have a 9.05 (SQL 2008) server available.

Anybody know a secret?

len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com>
<blocked::blocked::mailto:len.hartka@... <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> <mailto:len.hartka%40sunautomation.com> >
E9 question - %%% Urgent request- migration Time

**************************************************************

Sun Automation Group

Celebrating

25 Years of Service

to the Corrugated Industry

**************************************************************

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]