# Planning a Prophet 21 data import

> Plan a Prophet 21 data load you can trust, with dependency order, rehearsal loads in a non-production copy and reconciliation that decides when it is done.

Source: https://docs.lumina-erp.com/prophet-21/planning-a-data-import/

**In short.** Load in dependency order, rehearse every load in a non-production copy and decide in advance which reconciliation numbers must match before you call the import done.

A data import into Prophet 21 is rarely hard because of the file format. It is hard because records depend on other records, and because the import tools will sometimes do something other than what you assumed. It is also hard because "the load finished" is not the same as "the data is right." We follow the plan below whether the source is a legacy ERP, an accounting package or a spreadsheet someone has maintained for 10 years.

It applies to go-live migrations and to large one-off loads into a live system, such as a new product line or an acquired customer list.

## The three rules of a trustworthy import

Every import we run follows three rules.

| Rule | Why |
|---|---|
| Load in dependency order | A record cannot reference something that does not exist yet |
| Rehearse in a non-production environment until it is boring | The first load always teaches you something. Learn it in play |
| Reconcile before you declare victory | Counts and balances must tie back to the source, in writing |

Everything below is detail on those three.

## Load in dependency order

Prophet 21 records form a graph. Customers reference terms, tax settings and salespeople. Items reference product groups, units of measure and suppliers. Open orders reference customers, items and locations.

Load a child before its parent and the import either rejects the row or, worse, fills a default you did not intend.

The usual order, from the ground up:

| Tier | What | Why it comes here |
|---|---|---|
| 1. Foundation | Company, accounting periods, chart of accounts, locations | Nearly everything posts to or belongs to these |
| 2. Code tables | Terms, carriers and ship methods, tax setup, salespeople, units of measure, product groups, classes | Master records reference them by code |
| 3. Parties | Customers and ship-to addresses, suppliers and their vendor records, contacts | Transactions and items reference them |
| 4. Items | Item master, then item-location records, then item-supplier links | Stock, cost and purchasing depend on all three |
| 5. Pricing | Price pages, contracts, customer-specific pricing | Needs customers, items and suppliers |
| 6. Balances and open transactions | Inventory quantities and cost, open receivables, open payables, open sales orders, open purchase orders | Must reference everything above |

On an empty system, the foundation tier is required. On a new install, creating suppliers or customers can fail with unhelpful errors until accounting setup exists.

Open balances come last and are loaded as balances. For go-live, you almost never migrate transaction history as live transactions. You bring open items forward and keep history available for lookup elsewhere.

Write your own version of this table for the project, with the file for each tier and the person who owns its data. That table becomes the load runbook.

## Choose the load method per object

Prophet 21 offers more than one way to get data in: the built-in import windows and layouts, APIs and scheduled imports that pick files up from a folder. For a migration, the right choice varies by object:

| Method | Suits |
|---|---|
| Import layouts | Large volumes of master data where a standard layout exists. Usually the fastest route |
| APIs | Objects that need application logic applied as if a user entered them, or per-record error handling |
| Entry in the application, or a small script against an API | Objects with no convenient path |

:::caution[Test how the load matches existing records]
Some load paths will look up an existing record by a business key and update it instead of creating a new one. That is useful on purpose and damaging by accident. Never assume a load is create-only until you have proven it in play.
:::

Do not load by direct inserts into Prophet 21 tables. They skip application logic, leave related tables inconsistent and are hard to unwind.

## Rehearse until it is boring

Plan for at least three full rehearsals in a non-production environment that was refreshed from a clean baseline:

1. **Discovery load.** Expect failures. The goal is a list of every rejection and every field that did not land where you expected.
2. **Corrected load.** Apply the mapping and cleansing fixes. Rejections should be rare and explained.
3. **Dress rehearsal.** Run it exactly as you will on cutover day, from the same scripts, in the same order, with the same people. Time every step.

For each rehearsal, reset the target to the same baseline. Loading on top of a previous attempt hides problems, because the second load finds records the first one created.

Keep a log for every run: files and their row counts, start and end times, rejections with reasons and what changed before the next run. The log is how you estimate the cutover window and how you prove the process is stable.

### Fix bad data in the source or the mapping

When a rehearsal exposes bad data (duplicate customers, items with no unit of measure, suppliers with no remit address), fix it upstream: in the source system, or in a documented transformation step. Do not fix it by hand in the play environment. Hand fixes vanish on the next refresh and resurface on cutover day.

## Reconcile before you call it done

A load is complete when it reconciles, not when the progress bar stops. Decide the reconciliation checks before the first rehearsal, and agree with the business owner which ones must pass.

| Check | How | Pass condition |
|---|---|---|
| Record counts | Rows in the source extract versus records created in P21, per object | Match, or every difference explained |
| Spot checks | A sample of records compared field by field, chosen by the business, not the loader | No unexplained differences |
| Inventory | Quantity and extended value by location, source versus P21 | Ties within an agreed tolerance |
| Receivables | Open AR total and aging buckets by customer | Ties to the source aging at cutover |
| Payables | Open AP total by supplier | Ties to the source at cutover |
| General ledger | Opening trial balance | Ties to the source trial balance |
| Open orders | Count and value of open sales and purchase orders | Match |

The financial checks are the ones that decide whether finance will sign off, so bring the controller in early and use their reports as the source numbers.

Write the reconciliation up. A short document showing each check, the source figure, the P21 figure and the explanation for any difference is what turns "we think it is right" into a go-live decision.

## Cutover day

If the rehearsals did their job, cutover is the least interesting day of the project:

1. Freeze the source system at an agreed time.
2. Extract, transform and load in the rehearsed order.
3. Run the reconciliation checks.
4. Hold a go or no-go decision with the business owner against the agreed pass conditions.

Agree the rollback plan in advance. If a check fails, you need to know before the day starts whether you will fix forward or stay on the old system.

## Common import mistakes

- Loading open transactions before the masters they reference are verified.
- Treating a successful load status as proof of correctness.
- Cleansing data by hand in the target instead of in the source or mapping.
- Skipping rehearsal on "small" objects like terms or carriers, which then block larger loads.
- Reconciling against numbers the migration team produced rather than numbers the business already trusts.

## Sources

- [Epicor Prophet 21 Overview Brochure (Epicor)](https://assets.epicor.com/m/63302c83c86cc1ae/original/Epicor-Prophet-21-Overview-Brochure.pdf)
- [Prophet 21 Release 2024.2 (Epicor blog)](https://www.epicor.com/en-us/blog/industries/prophet-21-release-2024-2/)

---

Epicor, Prophet 21, P21 and DynaChange are trademarks or registered trademarks of Epicor Software Corporation registered in the United States and other countries. Kinetic is a trademark of Epicor Software Corporation. Lumina ERP is an independent consultancy and is not affiliated with, sponsored by or endorsed by Epicor.