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

> Source: https://netray.co/erp-help/private-ai-layer-epicor-prophet-21-on-prem-architecture
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

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

## Kind

advanced

## System

epicor

## Erp

Epicor Prophet 21 (on-premises)

## Module

Integration / Data Services / SQL Server

## Query

how to add AI to Prophet 21 on-prem

## Alternate Queries

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

## 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

## Steps

### How to build a private AI layer on Prophet 21

**Items:**

- Identify 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.
- Pick 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.
- Create 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.
- Create a dedicated read-only SQL login or a dedicated P21 API user with minimal rights, never a shared administrator login.
- Extract 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.
- Expose parameterised read actions through a tool server, for example check_item_availability and list_open_orders_for_customer, with row caps.
- Serve an open-weight model with vLLM or Ollama on local GPUs and log every request to an audit store.
- Pilot read-only with counter sales and purchasing, then drafts, then approved actions via the transaction layer.

## Sections

### Where the data lives and how to reach it

**Body:**

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.

**Code:**

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

**Body:**

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

**Body:**

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

**Body:**

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.

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

## Ai Angle

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.

## Faq

### Can I use AI with Prophet 21 on-premises?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

Prophet 21 AI integration, Prophet 21 on-prem AI, P21 chat with data, Prophet 21 SQL Server tables, Prophet 21 API, private LLM distribution ERP, Epicor Prophet 21 AI assistant, P21 AI without cloud

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json