Part Sales History

Epicor ERP 10
,
1

We recently went live on Epicor ERP 10.2.300.

The company President is requesting that I load Part Sales data (3 years) from our previous ERP (SAP).

I pitched a UD Table with a Dashboard idea to him and he wants the data to be loaded directly, so it is not a band aid solution.

What have others done to import part sales history into EpicorERP?

Mike Huffar
Epicor Project Manager
S&S Cycle, Inc.
Viola WI
608-627-0717

2

Hi Mike,

We did not do this when we went live several years ago. However, I have seen recent posts regarding usage of a “historical DMT”, see link here: Special "Historical DMT" license? - #10 by ckrusen

~Perhaps it would help you in this application.

Nancy

1 Like
3

Mike,

If you only need sales information and not actual shipments, you could use (regular) DMT to create Sales Orders for those parts and then close the orders.

1 Like
4

I second @tsmith’s idea.

Just be sure that you and the president are on the same page when it comes to the definition of “Sales data”. Being the President, he may be more interested in the GL aspects of sales (aka - Revenue), over the inventory aspect (Qty’s, turn times, etc…)

Remember a Sales Order is nothing more than “a letter of intent”- as in you intend to ship the items listed, at the prices specified. The packer function actully holds the shipping info, and AR Invoicing has the Sales (revenue) info.

5

@ckrusen agreed.

There are quite a few ways you could go about this, and depending on how granular the information needs to be (are GL Journal entries enough to just capture the $$ for income statements, or do invoices need to be entered and paid/adjusted to closed?) this could end up being quite a large project.

@Mike_Huffar I’m not sure how much you used consultants during your implementation, but it may be worth asking them if they’ve done anything similar. One of our consultants during our upgrade to E10 talked about sitting down and going through many, many DMT templates during an implementation.

6

As others have pointed out… the word “Sales” is often confused (in the English language?).

  1. True “Sales” are those things that you have actually sold AND DELIVERED. Thus, it is a summation of the “Invoice Value”… (see note on Returns below)
  2. BOOKINGS, (also called “Sales”) is the net value of what was “Ordered”… note that you can have a BOOKING today that increases the backlog, but not necessarily a new order. Example, an order placed on June 1 for $100, can have an increase on June 4 to $150… this would be a net booking change of $50. there is no “Sale” yet because it is not shipped… but if you look at the sales order, it says the value is $150 placed on June 1.
  3. BACKLOG, (also called “Open Sales”) is the value of all open sales orders Backlog can be Shipped or cancelled.

Formulas are simple:

  1. Today’s net bookings = all new orders + new changes - cancellations
  2. Todays Sales (Shipments) = all new invoices
  3. Current backlog = all open sales orders - You can also calculate this by taking yesterday’s backlog + Bookings - shipments
  4. Returns does not directly affect any of the above, but has an effect on grand total sales. ie… if you sell 100, but have returns of $80, you really only netted $20 in sales, even though the sales system shows $100.
7

@tsmith - President is not concerned about the GL side, he is requesting quantities only to help all make better decisions when forecasting, planning production etc…

We used Epicor’s services\consultants for the implementation and they strongly recommend against loading historical data.
We used DMT a great deal for the go-live data conversion and just learned about Historical DMT from this posting.

Mike

8

OH… and all that said… We (Epicor) typically suggest keeping historical data in a UD Table rather than importing lots of closed orders/invoices. Of course, you CAN import past historical GL balances for comparisons, which we have seen done quite a bit.
I typically suggest that a simplified historical data set be defined… Example, I dont need EVERYTHING from the invoice table… Minimum fields typically needed for

  • Sales History: Customer, Part, Qty, Price, Order#, Invoice#, Line#, date shipped
  • Purchase History Supplier, Part, Qty, Price, PO#, Receipt #, Line# Date Received
  • MFG History: Job#, Part, Qty, COST, Date Received
  • AR Payment History: Customer, Check#, Amount, Date
  • AP Payment History: Supplier, Check#, Amount, Date
    You may want/need a few additional fields such as Product Group, product Class, etc,
2 Likes
9

Then you want Inventory Part Trans actions from the old system. In the most basic form, Inventory Part Trans increase and decrease the QOH.

Things that increase QOH:

  • PO Receipt to inventory (PUR-STK)
  • Job Receipt to Inventory (MFG-STK)
  • Customer Returns (STK-CUS, but with a negative tran qty)
  • Qty Adjustments (manual or cycle Counting - (ADJ-QTY)

Things that decrease QOH:

  • Material issues to manufacturing (STK-MTL)
  • Customer shipment from Inventory (STK-CUS)
  • Qty Adjustments (manual or cycle Counting - (ADJ-QTY, a negative tran qty)

Those abbreviations in the parenthesis are key Epicor terms. You come to learn them.

10

Sorry for reviving an old post but I have been tasked to do just this. Bring over history from our E9 to our E11 system during our migration. Our consultants have said it can’t be done. Can anyone point me to more information about how to export the history, put it in a table and query to it?
Thanks

11

EXACTLY what “history” do you want to bring over?

As a recommendation, why not just leave your E9 server up and running, but with only Trackers available for users to see? It’s a LOT cheaper… and I’ll wager that within 6 months nobody will be looking at it anymore (been down this road before).

12

My boss (the owner) is livid that we’re not bringing transaction history over via DMT during our migration. My original plan was to have trackers available in E9. He thinks no one will go into it to look up any data.
I’ve been looking at using DMT to export 5 years worth of data from the PartTran table. Then put the data into a UD table and make a dashboard to search through it? My thought was I could add a custom tab to the Part Tracker screen for E9 History and embed the dashboard there. Does this sound reasonable? I’m still trying to learn how to create a UD table then hopefully DMT data into it.

13

He is correct on ONE thing… no one WILL go into it to look up any data… because they probably won’t need to anyway.

Can you give me a specific example of some data that you won’t be importing via DMT that people will need to go back and look up?

14

His main concern is with recent supply chain issues material costs are way more exaggerated than in years before so if we simply migrate in our average and last parts costs everything will be skewed too high.
I’m under the understanding that we cannot DMT any historical data like PartTran from E9 to E11. We were just going to bring in part costs (broken down) and leave it at that.
I could foresee people wanting to look up an old PO for an obscure part to find where we bought it from. Sales looking into an old sales order to see exactly what equipment we built and sold to a customer to help determine pricing for a spare part or a line upgrade.
We are an engineered to order company and don’t have a standard product line.

15

First of all, it’s HIS money, he can spend it where he wants to (even if it’s overtime for the IT staff) … BUT:

Your last paragraph will be solved with leaving E9 running and Trackers available.

And if what he’s really torqued about is “recent supply chain issues”, then that can also be dealt with via leaving E9 running and Trackers… you’ve got your PO Tracker, you’ve got Purchase Advisor, all those tools are still there. Remind him that a year from now, that data’s utility will be virtually non-existent.

16

I agree with your assessment … BUT
Could you humor me with my concept of bringing PartTran data from E9, putting it into a UD table and making a dashboard to view it. Would this work? If I brought this data in would it interfere with anything currently in our E11, for example, the part numbers will still be the same. Am I missing anything in my hypothesis?
Our E9 is currently residing on MS server 2008 which is no longer supported so, yes, I may want to pull so stuff out of it before we migrate it to another server.
Thanks for your help on this.

17

BTW I’m the Manufacturing Manager. I only get to play IT part time here. Its what happens in small companies. I learned E9 from necessity over the last 12 years being the only person here with any kind of computer knack. And yeah, I’m migrating us from E9 to E11. It’s brutal. That’s why I ask some beginner level questions at times.

1 Like
18

What others have done, and this is along the lines of what @Ernie is suggesting, is to go ahead and bring the E9 database over and install it on your new SQL server. Now you can create External BAQs to query that data and have it displayed in E11. If you want to create views in the old DB that also point to the live tables, then you can even get a combined query.

Along with the others, I cannot stress enough that you do NOT want to DMT historical data into your new system - even if you had a a full time IT person or two.

2 Likes
19

Well I know it… most of the people here (myself included) got into this in the first place by being thrown into the deep end with a recommendation that we learn how to swim. I started as an IT guy with ZERO experience in manufacturing. Don’t tell anyone!

1 Like
20

If you are more familiar with SQL than with Epicor BAQs and the DMT, then I would STRONGLY recommend @Mark_Wonsil’s suggestion to bring your entire existing E9 database into the new SQL instance for Kinetic (I’m not skilled in that area but others here certainly are)… if you’re better in BAQs and UD tables and DMT then we’ll go that route.

And if both of those options are frankly terrifying, then buckle up and we’ll see what we can accomplish.

First off, bring in the entire PartTran table via DMT is going to be mostly a waste of time… we need to filter it down and only bring in what your boss will want people to be able to search for. Rule #1 for any Data Conversion: Know what you want and bring in ONLY what you want. So be specific… EXACTLY what records from PartTran do you want to bring in? (hint: the PartTran table has 174 fields, and if you’ve been running E9.04 since, oh, 2010 or so then you probably have well over a million record lines in it)