# Prophet 21 integration options, and how to choose

> How to connect Prophet 21 with other systems through APIs, file imports, database reads or middleware, and the questions that decide between them.

Source: https://docs.lumina-erp.com/prophet-21/integration-options/

**In short.** APIs, file imports, direct database reads and middleware platforms each fit a different shape of problem. Direction, volume, latency and who owns failures decide between them.

Sooner or later Prophet 21 has to talk to something else: an e-commerce storefront, a warehouse or shipping system, a CRM, a supplier's catalog, a customer's purchasing portal, a data warehouse. There are several ways to connect, and the best one depends less on technology preference than on a handful of questions about the data. Below are the options, then the questions that decide between them.

:::note[Confirm the details for your install]
Specific endpoints, licensing and capabilities vary by Prophet 21 version and by what your organization has purchased. Treat this as a map, then confirm the details for your install.
:::

## The options at a glance

Each option fits a different shape of problem and carries its own risks.

| Option | Fits | Watch for |
|---|---|---|
| APIs | New integrations that write to Prophet 21, because they apply the application's rules | Authentication, reaching the middleware and capabilities that may need extra licensing |
| File imports and exports | Batch work where minutes or hours of latency are fine | Latency, a shared location and someone who owns the rejects |
| Direct database reads | Reporting, a data warehouse, analytics | Must stay read-only, and may be restricted or unavailable on hosted systems |
| Middleware and integration platforms | More than two or three integrations that need mapping, retries, logging and alerting | It uses the other options rather than replacing them |
| Purpose-built connectors and EDI | EDI, tax calculation, shipping, e-commerce | What the connector costs to maintain compared to custom work |

## How each option works

### APIs

Prophet 21 exposes web APIs from its middleware tier. They fall into three broad styles:

- **Entity-style endpoints** that read and write business records such as customers, items and orders as structured objects.
- **Query-style endpoints** that expose data for reading with filtering and paging, in the OData style.
- **Transaction-style endpoints** that drive the same business logic a user would trigger in a window, so that defaults, validations and related updates happen as they would on screen.

APIs are the default choice for new integrations that write to Prophet 21, because they apply the application's rules. They require authentication, the middleware must be reachable from the calling system and some API capabilities may require additional licensing. Confirm what your install includes before you design around it.

### File imports and exports

Prophet 21 can import files in defined layouts, either on demand or through a scheduled import that watches a folder and loads files as they arrive. It can also export data and send documents on a schedule.

Files are unfashionable and reliable. They suit batch work where minutes or hours of latency are fine: nightly price updates, periodic item loads, order files from a partner who can only send files. They are also easy to audit, because every exchange leaves a file behind.

The costs are latency, the need for a shared location both systems can reach and error handling that tends to be "check the folder for rejects," which someone has to own.

### Direct database reads

Reading the SQL Server database directly is often the simplest way to feed reporting, a data warehouse or an analytics tool. It should be **read-only**, through a dedicated login, and preferably against a reporting copy of production. See [Reading Prophet 21 data with SQL, safely](/prophet-21/reading-p21-data-with-sql/).

On hosted systems, direct database access may be restricted or unavailable, which pushes reads toward APIs or scheduled exports.

:::danger[Direct writes are not an integration method]
Direct writes to Prophet 21 tables bypass business logic and can leave related tables inconsistent. Use the application's imports or APIs, and check your support agreement before anyone proposes otherwise.
:::

### Middleware and integration platforms

An integration platform, whether a commercial iPaaS, a message queue or a small service you write, sits between Prophet 21 and the other system. It uses the options above rather than replacing them. It adds a place for mapping, retries, logging, alerting and orchestration across several systems.

Once you have more than two or three integrations, a shared platform tends to pay for itself, because every integration otherwise reinvents its own logging and retry logic.

### Purpose-built connectors and EDI

For common needs such as EDI with trading partners, tax calculation, shipping and e-commerce, there are established products built for Prophet 21, from Epicor and from third parties. Before building, check whether a supported connector exists and what it costs to maintain compared to custom work.

## The questions that decide

Work through these for each data flow. A single integration project often has several flows that land on different answers.

### Which direction does the data move?

| Direction | Examples | Methods |
|---|---|---|
| Out of P21 only | Reporting, analytics, publishing a catalog | Database reads, query APIs or scheduled exports |
| Into P21 | Orders, customers, items, receipts | APIs or imports, so business rules apply |
| Both ways | Records either system can change | Design each direction separately, and decide which system owns each field |

### How fresh must it be?

| Need | Typical approach |
|---|---|
| Seconds (a web checkout creating an order) | API call |
| Minutes (inventory on a storefront) | API polling or an event from an integration platform |
| Hours or daily (price files, catalog feeds, warehouse reporting) | Files or scheduled extracts |

:::tip[Do not design for seconds when minutes will do]
Real-time integration is more fragile and more expensive to run.
:::

### What volume?

Thousands of records a day are easy for any method. Hundreds of thousands in a window change the design: batch files, paging and throttling matter, and so does staying out of the way of users during business hours.

### Who owns which fields?

For every shared entity, write down the system of record for each field. If both the storefront and Prophet 21 can change a customer's email, you need a rule for which one wins. Integrations without an ownership table end up overwriting good data with stale data.

### What happens when it fails?

Every integration fails eventually. Decide up front:

| Concern | Decide |
|---|---|
| Idempotency | If a message is sent twice, does it create two orders? Use a stable external reference and check for it before creating |
| Validation before write | Reject malformed data at the edge with a clear error, instead of letting Prophet 21 reject it deep in a batch |
| Retries | Which errors are worth retrying automatically, and which need a person |
| Alerting | Who is told, and how quickly, when records stop flowing |
| Reconciliation | A periodic check that counts and totals match on both sides |

### What changes at upgrade time?

APIs and import layouts can change between versions. Keep a list of every integration, the method it uses and a simple test for each. Run those tests in the play environment before every upgrade.

## Quick decision table for common situations

These are the answers we reach most often for situations we see repeatedly.

| Situation | Usual answer |
|---|---|
| Web store needs to create orders as they are placed | API, through an integration layer with retries and idempotency |
| Supplier sends a weekly price file | Scheduled import |
| Finance wants a nightly data warehouse load | Read-only database extract from a reporting copy, or scheduled export on hosted systems |
| Warehouse system needs picks and returns confirmations | API or files, depending on volume and latency, with a clear owner for each status |
| Trading partner requires EDI | An EDI product built for P21, rather than custom work |

## Before you build

Write a one-page design for each flow: direction, trigger, frequency, volume, the method chosen and why, field ownership and how failures are detected and handled. That page is what lets someone other than the original developer support the integration a year from now.

For how integrations relate to other kinds of customization, see [DynaChange Rules, Designer, a report or an integration?](/prophet-21/choosing-dynachange-or-integration/).

## 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 connector documentation (Jitterbit)](https://docs.jitterbit.com/integration-studio/design/connectors/epicor-prophet-21/)

---

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.