AdvancedEpicor Prophet 21 (on-premises)Integration / Data Services / SQL Server

How to Add AI to Prophet 21 On-Premises Without Moving Your Data

Question
how to add AI to Prophet 21 on-prem

Also searched as

  • Prophet 21 AI integration without cloud
  • Prophet 21 chat with data SQL Server
  • P21 private LLM distribution ERP
  • Prophet 21 API for AI assistant

Short answer

Prophet 21 runs on SQL Server and offers Epicor-provided API layers for reads and transactions, so a private assistant can be built on a read-only reporting copy plus the documented APIs, with an open-weight model on your own GPUs. Distribution questions about stock, pricing, open orders, customers and purchasing are the strongest fit.

Applies to: Epicor Prophet 21 on-premises with a SQL Server back end; API options and table details vary by P21 version, so verify against your data dictionary

How to build a private AI layer on Prophet 21

  1. 1Identify your P21 version and which API modules are licensed and enabled. P21 documents integration layers for data reads and for transactions; confirm what your install has before you design around them.
  2. 2Pick the first questions: stock and availability by location, open sales orders, backorders, customer last orders, price and cost lookups, open purchase orders and vendor lateness.
  3. 3Create a read-only reporting copy of the SQL Server database (readable secondary, log shipping or restored backup), and build a small set of SQL views over core tables for the assistant to use.
  4. 4Create a dedicated read-only SQL login or a dedicated P21 API user with minimal rights, never a shared administrator login.
  5. 5Extract documents the business keeps outside the database (price sheets, supplier catalogues, contracts, spec sheets, order PDFs) and index them locally with company and location tags.
  6. 6Expose parameterised read actions through a tool server, for example check_item_availability and list_open_orders_for_customer, with row caps.
  7. 7Serve an open-weight model with vLLM or Ollama on local GPUs and log every request to an audit store.
  8. 8Pilot read-only with counter sales and purchasing, then drafts, then approved actions via the transaction layer.

Where the data lives and how to reach it

P21 data sits in SQL Server. Well-known tables include oe_hdr and oe_line for sales orders, inv_mast and inv_loc for items and stock by location, customer, vendor, po_hdr and po_line for purchasing, and invoice_hdr and invoice_line for billing. Identifier columns such as inv_mast_uid link item and location records. Confirm column names against your own data dictionary, since they differ by version.

For reads, SQL views on a reporting copy are the most flexible. For supported application-level access, Epicor documents API layers for P21, including data services and a transaction API for creating and updating records. Use those for any write or for reads that must apply the application's own rules, and use the SQL copy for history and analytics.

Do not give a model a generic SQL console. Give it named actions with typed parameters and let the tool server run reviewed SQL.

-- Open sales order lines for a customer (verify column names for your P21 version)
SELECT TOP 50 h.order_no, h.order_date, c.customer_name, l.item_id, l.qty_ordered
FROM oe_hdr h
JOIN oe_line l ON l.order_no = h.order_no
JOIN customer c ON c.customer_id = h.customer_id AND c.company_id = h.company_id
WHERE h.completed = 'N'
  AND h.delete_flag = 'N'
  AND h.customer_id = @customer_id
ORDER BY h.order_date DESC;

Reference architecture

Components: a read-only SQL Server copy with assistant views, a change feed from SQL Server change tracking or CDC to keep the retrieval index fresh, a document extractor, a local vector store, a tool server, a model server and an audit gateway. All of it runs inside your network, with no outbound calls required.

For an air-gapped or restricted site, stage models, containers and packages once through a controlled transfer, run embeddings locally and pin versions. Counter and branch users reach the assistant over your existing network and single sign-on.

Keep expensive questions off production. Availability checks that must be current can use a short-lag replica; trend and analysis questions can use a nightly copy. State the data freshness in each answer so counter staff know whether to trust the stock figure or confirm in P21.

Roles, locations and write-back

P21 security is organised by user roles, with data restrictions by company and location and by features such as who can see cost, margin or other users' orders. A SQL read login sees whatever it is granted, so the tool server must apply the asking user's company, location and role limits to every query and retrieval filter. Maintain a mapping table from P21 users or roles to those limits and review it when roles change.

Treat cost and margin fields as a separate class that is hidden by default. Distribution companies often keep cost visible only to buyers and managers, and an assistant that answers 'what is our margin on this customer' for a counter user is a breach, not a convenience.

Write-back should use the supported transaction layer under the approving user's identity, after a visible diff. Reasonable first actions are saving a draft quote or a draft purchase order, not changing prices or posting receipts.

Sizing, rollout and honest limits

For 50 to 300 named users, one 24 GB to 48 GB GPU running a 7B to 14B parameter model usually serves a counter-sales and purchasing pilot, with a small vector store. Heavier use with long catalogue documents or larger models pushes to two GPUs. Measure peak concurrent requests; they are driven by shift patterns, so branch opening hours matter.

Rollout in three phases: read-only questions for counter and purchasing teams; summaries and drafts such as a vendor chase email or a quote narrative; then approved actions through the transaction API. Hold each for several weeks and review the audit trail.

Be candid about what will not work well. Pricing in P21 can depend on layered rules, contracts and customer-specific price pages, so an assistant that recomputes price from raw tables will be wrong; call the application for price, or show it as indicative only. For simple stock lookups, P21's own screens and reports are already fast enough.

Try it here, free

Prophet 21 Inventory by Location SQL Generator

Open full tool

Builds a read-only SELECT for on-hand and allocated quantity by item and location in a Prophet 21 database.

Common pitfalls

  • !Recomputing price or cost from tables instead of calling the application's pricing logic.
  • !Showing cost and margin to roles that cannot see them in P21.
  • !Letting a SQL login with broad access sit behind a chat box with no per-user filter.
  • !Reporting stock from a nightly copy as if it were live.
  • !Writing to P21 tables directly, bypassing the application's business rules.

How an ERP-grounded AI assistant handles this

ERPray can answer distribution questions across items, orders, customers and purchasing from a P21 reporting copy and the supported APIs, show the SQL or call it used, and run on your own GPUs. Use it where the questions cross modules; for single-screen lookups the P21 UI is already the better tool.

See ERPray

Frequently asked questions

Can I use AI with Prophet 21 on-premises?

Yes. P21 runs on SQL Server, so a read-only reporting copy plus Epicor's documented APIs can feed a local assistant. The model runs on your own GPUs with open-weight models, so data stays inside your network.

Should the assistant read P21 tables directly?

For history, analytics and document-heavy questions on a reporting copy, yes, through reviewed views and parameterised queries. For pricing or anything that needs application rules, call the supported API instead of recomputing from tables.

How are P21 roles respected?

The tool server applies the asking user's company, location and role limits to every query and retrieval filter, using a mapping you maintain. Cost and margin are hidden unless the role may see them, and denied cases are tested after changes.

What is the best first use case?

Counter sales and purchasing questions: availability by location, open orders and backorders, last price paid, vendor lateness. These are frequent, bounded and easy to verify against the P21 screen, which builds trust before drafts and actions are added.

Related

CIO briefing

Epicor Eclipse or Prophet 21: how a distributor should make the decision

Epicor sells both Eclipse and Prophet 21 to distributors, and they come from different histories and serve somewhat different verticals. Do not decide on feature lists alone: get Epicor's written support and roadmap position for your product, price a full migration honestly, and test your own critical workflows before committing. Staying put is often cheaper than moving, until it is not.

Advanced

Epicor Eclipse AI Integration: A Private, On-Prem Architecture

Eclipse is a distribution ERP whose integration surface is more constrained than SQL-based systems, so the safest design reads from scheduled extracts, vendor-supported interfaces and exported reports into a local reporting store, then indexes and serves an open-weight model on your own hardware. Confirm with Epicor which interfaces your Eclipse release and hosting arrangement allow.

Advanced

How to Add AI to Epicor Kinetic On-Premises Without Moving Your Data

You can run a useful AI assistant on an on-premises Epicor Kinetic estate without sending data to a public cloud. Read through BAQs and the REST v2 OData layer (or a read-only SQL Server replica of the Erp schema), index documents locally, serve an open-weight model with vLLM or Ollama on your own GPUs, and route any write through supported Epicor business objects with a human approval step.

Error fix

Fixing Epicor BusinessObjectException: BPM Directive Errors

A BusinessObjectException that says "A Business Process Management (BPM) directive has raised the following error" means a directive on that business object stopped the transaction, either deliberately via a Raise Exception widget or accidentally via an unhandled .NET error in Custom Code. Expand the InnerException on the error dialog to see the real message and the directive name, then open BPM Designer for that object and method to find the widget that fired.

How-to

Fixing a Slow Epicor BAQ (Business Activity Query)

A slow BAQ in Epicor is usually caused by unindexed join columns, a subquery or calculated field forcing a table scan, or the BAQ pulling far more rows than the dashboard actually displays before filtering client-side. Fix it by reading the SQL Server execution plan Epicor generates, moving filters into the BAQ criteria instead of the dashboard filter panel, and replacing subqueries with joins where possible.

How-to

Epicor MRP Runs but Generates No Suggestions

When Epicor's MRP process completes without a job or purchase suggestion for a part you expect one for, the cause is almost always the part's own configuration (Part Class Type, Make Direct, Non-MRP flag, planning Time Fence) or the demand not being linked in a way MRP recognizes, not a defect in the MRP engine. Work through the part's Planning tab, its safety stock and lead time setup, and the demand source (sales order line status, job material requirement) before assuming the run itself failed.

AI for ERP

AI for Epicor Prophet 21, Built for Distribution Operations

Prophet 21 AI for distributors: on-prem or private-cloud LLM grounded in P21 order, inventory, and purchasing data, for order follow-up, forecasting, and pricing questions.

Stuck on Epicor Prophet 21 (on-premises)?

Talk to engineers who work inside Epicor Prophet 21 (on-premises) every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineerBrowse ERP Help
All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

Guides

AI on your ERP

Integrations

Software