How to add AI agents to Epicor Prophet 21
Short answer
Build AI agents on Prophet 21 by giving a model a small set of tools: read-only SQL views for lookups and the P21 Interactive or Transaction API for approved writes. Start with agents that prepare work (replenishment suggestions, backorder triage, quote drafts) and keep a person approving anything that posts to the ledger or the order file.
Prophet 21 customers are mostly wholesale distributors: electrical, plumbing, industrial supply, fasteners, building products, tools. The work is high volume and repetitive: thousands of order lines a day, constant purchasing decisions, price and cost changes, backorders, returns and credit holds. Most of that work follows rules that a skilled counter or purchasing person applies from memory, which is exactly the shape of work an agent can do a first pass on.
An agent is not a chatbot with a bigger prompt. It is a loop: observe the ERP, decide a next step, call a tool, check the result, and stop at a defined point for a human. For Prophet 21 the observation side is easy because the data sits in a Microsoft SQL Server database that your team can already query. The hard part is the action side, because P21 business logic (pricing, allocation, credit, rebates, commissions) lives in the application, not in the tables, so writes must go through a supported interface.
This page covers the design that works in practice: what tools to expose, where the human stops the loop, and how to run the whole thing on your own hardware if your supplier pricing and customer contracts should not leave the building.
What teams use it for
Replenishment and PO suggestions
The agent reads item and location data, usage history, open POs and lead times, then drafts purchase order lines by supplier with the reasoning for each quantity. A buyer reviews and releases. It handles the long tail of slow items that nobody has time to review weekly.
Backorder and expedite triage
Each morning it walks open backordered sales lines, checks inbound POs and transfers, and sorts them into: will be covered by an existing receipt, needs an expedite call to the supplier, or needs an alternate item. It drafts the customer notes for counter staff to send.
Quote and RFQ drafting
Given an emailed parts list, the agent resolves customer part numbers and supplier cross-references to item IDs, pulls contract pricing and availability across locations, and prepares a quote for a salesperson to review in P21 before it goes out.
Price and cost change review
When a supplier price file arrives, the agent compares new costs to current cost and sell price, flags items that fall below target margin or break a customer contract, and proposes the multiplier or contract changes for pricing to approve.
Credit hold and collections prep
For accounts on credit hold, it summarizes aging, recent payments, open orders and notes, then recommends release, partial release or a call. Credit managers keep the decision, but stop assembling the picture by hand.
Returns and RMA handling
It checks the original invoice, return policy, restocking rules and condition notes, then drafts the RMA and credit memo for approval, catching returns outside the allowed window or with the wrong item ID.
Epicor Prophet 21 integration surface
- Microsoft SQL Server database: P21 stores data in SQL Server, and many teams already expose read-only views (item, inv_loc, oe_hdr, oe_line, po_hdr, po_line, ar and ap tables) for reports. Table and column names vary by version, so confirm against your own schema and put agents on curated views, not raw tables.
- Epicor Prophet 21 Interactive API and Transaction API (SOAP and REST in recent versions, exposed through the P21 middleware or service layer): the supported way to create and update orders, POs, receipts and similar documents while running the application's business rules. Availability and endpoint names depend on version and licensing, so check with Epicor.
- Data Interchange and importer tools: file or table based import for batch creation of documents. Slower than an API but robust and well understood by P21 admins, and a reasonable write path for low-volume agent output.
- Business Rules and Dynachange: P21's own event hooks and field level customization. Useful for triggering an agent from an ERP event or for surfacing an agent suggestion inside a window without building a separate UI.
- Crystal Reports and the report server: existing report definitions encode what users already trust as the right numbers. Reuse their joins as the basis for agent read views.
How it works
- 1
Pick one narrow job
Choose a task with clear inputs, a measurable output and a person who already owns the outcome, such as slow-item replenishment for one product line. Write down what a good result looks like before building anything.
- 2
Create read-only SQL views
Build views over inventory by location, usage, open demand, open supply and supplier lead times. Grant a dedicated SQL login SELECT on those views only. Never hand the agent a credential with write access to the P21 tables.
- 3
Define typed tools
Wrap each view as a tool with a name, parameters and a row limit, for example get_item_availability, list_open_backorders, get_supplier_lead_times. Add one write tool, create_draft_po, that calls the P21 API with a draft or hold status where the version supports it.
- 4
Choose the model and host
Use a hosted model if policy allows, or an open-weight model served on a local GPU with vLLM or Ollama. Smaller models handle tool calling well when tools are few and descriptions are precise.
- 5
Insert the approval gate
The agent writes proposals to a review queue with the evidence it used. A named buyer or CSR approves, edits or rejects. Only an approved item triggers the write through the API under a service user that has a narrow P21 role.
- 6
Log every step
Record the prompt, each tool call, parameters, result row counts and the final proposal. This is what lets you debug a bad suggestion and show an auditor how a PO line came to exist.
- 7
Measure against the manual baseline
Run in shadow mode for a few weeks, comparing agent proposals to what buyers actually did. Widen scope only where acceptance is high and the misses are understood.
Example flows
Morning backorder sweep
Trigger: A scheduled job runs at 6:00 for each branch.
- Agent calls list_open_backorders for the branch, sorted by customer priority and promise date.
- For each line it calls get_inbound_supply to find POs and transfers that cover it and their due dates.
- It classifies the line as covered, at risk, or uncovered, and for uncovered lines checks alternate items.
- It drafts a customer note and, for at-risk lines, an expedite request to the supplier.
Result: Counter staff open one prioritized list with drafted messages instead of scanning the backorder report line by line.
Supplier price file review
Trigger: A purchasing user drops a supplier cost file into a watched folder.
- Agent parses the file and matches supplier part numbers to P21 item IDs.
- It reads current cost, sell price, margin target and any customer contract pricing tied to each item.
- It flags items where the new cost breaks contract margin or exceeds a set increase threshold.
- It writes a proposal sheet with recommended price changes for the pricing manager.
Result: Pricing approves a reviewed change set, and the actual updates go in through the normal P21 price maintenance or importer path.
Draft PO for a stocking shortfall
Trigger: A buyer asks the agent to cover a branch's below-minimum items for one supplier.
- Agent pulls items below minimum with usage and open supply.
- It applies the supplier's order multiples, minimum order value and lead time.
- It calls create_draft_po through the API in a draft state.
- It posts the PO number and rationale to the buyer for review.
Result: The buyer opens a nearly finished PO in P21, adjusts it and releases it, rather than building it from a report.
Keeping data on-prem
Most Prophet 21 estates are either self-hosted on Windows servers with SQL Server or hosted by Epicor, and the agent stack should follow the same boundary. For a self-hosted system, run the agent runtime, tool layer and an open-weight model inside the same network, with read-only views on a SQL Server replica or readable secondary if you have one so analytical queries never slow the counter. Supplier cost files and customer contract pricing are commercially sensitive, which is the usual reason distributors choose a local model. If the system is Epicor-hosted, direct database access may not be available, so plan around the supported APIs and any data export Epicor provides, and confirm the terms before designing.
Security checklist
- Give the agent a dedicated SQL login with SELECT on named views only, and a separate P21 service user with a narrow role for the write tool.
- Keep human approval on every write to orders, POs, prices and credit. Agents propose, people post.
- Store credentials in a secrets manager and rotate them. Do not place connection strings in prompts or tool descriptions.
- Mask or exclude sensitive columns such as customer credit data and cost where the task does not need them.
- Retain tool call logs with user, time and parameters so every agent suggestion is traceable to its data.
Ways to get it done
Use Epicor's own AI and agent features
Effort: Low, mostly configuration and licensing.
Fine when your workflow matches what Epicor ships for your version and you want vendor support. Check which features your license and release include.
Build in-house on SQL views plus the P21 API
Effort: A few weeks for one agent, more for evaluation and hardening.
Good if you have a developer who knows P21 and SQL Server and one clear use case. You own prompts, evaluation and upkeep.
Work with a partner such as Netray
Effort: Typically a scoped pilot over several weeks, then extension by use case.
Useful when you want a private model, a tested tool layer and approval queue, and P21 domain input, without staffing it yourself.
Pitfalls
- !Writing directly to P21 tables with SQL. This skips pricing, allocation and audit logic and can corrupt documents. Use the API or importer.
- !Giving the agent raw tables. Wide joins on order and inventory tables are slow and easy to get wrong, so curate views and test them against known reports.
- !Ignoring multi-location and unit of measure rules. Availability, reorder points and pricing UOM differ by location and item, and agents that skip them produce plausible but wrong quantities.
- !Letting reads hit production at peak times. Point analytical queries at a replica or run them off hours.
- !Skipping a baseline. Without a record of what buyers did before, you cannot tell whether the agent helps.
Frequently asked questions
Can an AI agent create orders in Prophet 21 directly?
Yes, if you use a supported write path such as the Transaction or Interactive API or the importer, which run P21's business rules. Do not insert into tables. Most teams start with drafts or holds and require a person to release the document, then relax that only where results justify it.
Does the agent need to run inside the P21 server?
No. It runs as a separate service that connects to SQL Server for reads and to the P21 API for writes. Keeping it separate means ERP upgrades and agent changes do not collide, and you can scale or replace the model independently.
Which Prophet 21 version do I need?
Read access via SQL works on most versions. API options and names differ by release, and some need specific licensing or middleware. Ask your Epicor contact or check your documentation for the Interactive and Transaction API availability on your build before committing to a design.
Is an agent better than a P21 report or Business Rule?
For fixed logic, a report or Business Rule is simpler and cheaper. Agents earn their place when the task needs judgment across messy inputs, such as emailed parts lists or supplier files, or when the steps vary by case. Use deterministic tools where they are enough.
Can this run without sending data to a cloud model?
Yes. Open-weight models served locally can handle tool-calling agents over P21 data, with some quality trade-off on harder reasoning. Many distributors keep cost and contract data local for that reason, and use a hosted model only for non-sensitive tasks.
Want AI agents working on your Epicor Prophet 21?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment