Prophet 21 integration options, and how to choose

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.

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.

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

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

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?.