AdvancedEpicor 9 and Vantage (Progress OpenEdge)Integration / OpenEdge SQL / Service Connect

AI for Epicor 9 and Vantage on Progress OpenEdge: A Private Architecture

Question
Epicor 9 AI integration Progress database

Also searched as

  • how to query Epicor 9 database with AI
  • Epicor Vantage OpenEdge ODBC JDBC read only
  • Epicor 9.05 chat with data on-prem
  • private AI for legacy Epicor Progress

Short answer

Epicor 9 and Vantage store data in a Progress OpenEdge database, which can be read through the OpenEdge SQL-92 engine over ODBC or JDBC. The practical design replicates the data to a SQL Server or similar reporting copy, indexes documents locally, serves an open-weight model on your own GPUs, and keeps the legacy system read-only.

Applies to: Epicor 9.05 and earlier Vantage releases on Progress OpenEdge, Windows application servers; exact driver and table names depend on your release and data dictionary

How to add a private AI layer to Epicor 9 or Vantage

  1. 1Confirm the Progress OpenEdge version and whether the SQL-92 broker is running. SQL access is a separate service from the 4GL application broker and must be enabled on the database server.
  2. 2Create a read-only database user for SQL access and set the SQL width for string columns, because OpenEdge SQL truncates data to the declared SQL width and the default can cut descriptions and comments.
  3. 3Install the OpenEdge ODBC or JDBC driver on a dedicated integration server and test with a single query against PUB.JobHead and PUB.PartTran before building anything.
  4. 4Decide the read model. For light use, query the OpenEdge SQL engine directly off-hours. For anything heavier, replicate tables to SQL Server or PostgreSQL (OpenEdge replication tools, Progress Pro2, or a scheduled extract).
  5. 5Extract documents and attachments from the file shares your install uses, embed them locally and tag each chunk with company and plant.
  6. 6Expose a small set of parameterised read actions through a tool server. The model never holds database credentials.
  7. 7Serve a local open-weight model with vLLM or Ollama and log every call to an append-only audit store.
  8. 8Keep the system read-only. If a write is truly needed, route it through Epicor's supported service layer after human approval, not through SQL.

Data access on a Progress OpenEdge database

Epicor 9 and Vantage tables such as JobHead, JobMtl, JobOper, PartTran, Part, PartBin, OrderHed, OrderDtl, POHeader and PODetail live in the PUB schema of the OpenEdge database. The OpenEdge SQL-92 engine lets standard SQL tools read them through ODBC or JDBC, which is the only way to reach the data without going through the application.

Two OpenEdge-specific details bite integrations. First, SQL width: if the database was not prepared with adequate widths, long text fields return truncated. Second, the SQL engine and the 4GL runtime take their own locks and statistics, so a heavy query on the live database can degrade the application. Keep analytics off the live database in business hours.

Epicor 9 also has its own application services and Service Connect for message-style integration. Native REST arrived only in later products, so on 9 expect SQL for reads and Service Connect or the service layer for any sanctioned writes.

-- OpenEdge SQL-92, read only
SELECT TOP 50 jh.JobNum, jh.PartNum, jh.ProdQty, jh.ReqDueDate
FROM PUB.JobHead jh
WHERE jh.Company = 'EPIC03'
  AND jh.JobClosed = 0
ORDER BY jh.ReqDueDate
jdbc:datadirect:openedge://dbhost:20931;databaseName=mfgsys;user=ai_readonly

Replication, retrieval and model serving

For a legacy Progress system, a replica does more than protect performance: it lets you add indexes, flatten tables into reporting views and keep a history that the live database prunes. Progress documents replication options for OpenEdge, and the Pro2 product replicates OpenEdge tables into SQL Server or Oracle. A nightly or hourly extract is often sufficient for planning, quoting and inventory questions.

Build the retrieval index from that copy and from documents on file shares: travelers, drawings, certificates and SOPs. Store source, company, plant and access tags with every chunk. Serve the model with vLLM or Ollama on local GPUs behind a gateway that writes an audit record per call.

An air-gapped option is straightforward because the stack never needs outbound connectivity: stage weights and containers once, pin versions, and run embeddings locally. Many sites with 9 are on isolated networks already, and this design leaves that posture intact.

Permissions and write-back

Epicor 9 security is held in the application (security groups, menu access, company and plant rules), not in the database. A SQL read user sees everything it is granted, so the permission logic has to live in your tool server. Maintain a mapping from Epicor users or groups to allowed companies, plants and actions, and have the tool server inject those filters into every query.

Authenticate users to the assistant against Active Directory, map them to that table, and refuse any request that does not resolve. Apply the same tags as retrieval filters on documents. Treat financial and costing data as a separate permission class and default it to denied.

Write-back is the area to be most cautious on a legacy release. Use only supported service layer or Service Connect paths, put a person in the loop, and consider leaving writes out until after any planned migration.

Sizing and honest limits

Sizing mirrors other ERPs: most 9 and Vantage sites with 30 to 200 named users fit on a single 24 GB GPU running a 7B to 14B parameter model, with a modest vector store and an hourly or nightly replica. The load driver is peak concurrent requests, so measure them in a pilot.

The limits are real. Epicor 9 is out of mainstream support, so the database and OS around it may be old, and SQL width or schema oddities will cost time. Business logic hidden in customisations or BPM may not be visible to SQL, so a number the assistant computes can differ from the one the screen shows. Always show the query and let users compare.

If the real goal is leaving Epicor 9, a migration project with data profiling may deliver more than an assistant on top of it. A good compromise is an assistant that also documents the data model you will need to map.

Try it here, free

Epicor 9 and Vantage Private AI Layer Blueprint Generator

Open full tool

Generate a private AI plan for Epicor 9 or Vantage on Progress OpenEdge, including SQL read path, replica, retrieval, local model serving and rollout.

Common pitfalls

  • !Running heavy model-driven queries on the live OpenEdge database during shifts.
  • !Ignoring SQL width, so descriptions and comments come back truncated and the model reasons over partial text.
  • !Granting a SQL user broad read access with no per-user filtering in the tool layer.
  • !Trusting totals that depend on customisation or BPM logic that SQL cannot see.
  • !Attempting writes by SQL against a Progress database, bypassing application rules.

How an ERP-grounded AI assistant handles this

ERPray can be pointed at a replicated copy of an Epicor 9 or Vantage database and used on-prem to answer inventory, job and order questions, with the query shown so users can check it against the screen. It also helps map legacy tables ahead of a migration, which is where many 9 sites end up anyway.

See ERPray

Frequently asked questions

Can I query an Epicor 9 database directly with SQL?

Yes, through the Progress OpenEdge SQL-92 engine over ODBC or JDBC, using a read-only user. Check SQL width settings, avoid heavy queries on the live database, and prefer a replicated copy for analytics and AI workloads.

Does Epicor 9 have a REST API?

Not in the way Kinetic does. Epicor 9 relies on its application services and Service Connect for integration. For an AI assistant, plan on SQL for reads and supported service paths for any approved writes.

Is it worth adding AI to Epicor 9 instead of upgrading?

Sometimes. A read-only assistant can remove reporting backlog quickly and the replica and document index carry over to a new system. If an upgrade is already funded, align the two projects so the data mapping serves both.

How do I keep data on site?

Run the replica, the vector store and the model server on your own hardware, with no outbound calls from the stack. Open-weight models served by vLLM or Ollama make this possible, including on air-gapped networks.

Related

How-to

Preserving Customizations When Upgrading to Epicor Kinetic

Epicor Kinetic's web UI does not simply inherit classic smart-client customizations - most need to go through the Update Customization / Application Studio conversion process, and some WinForms-specific customizations cannot convert directly and must be rebuilt in the Kinetic designer. Plan the upgrade as a customization inventory and rework project, not a single technical cutover step.

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.

Advanced

Epicor ERP 10 AI Integration Without Cloud: Private Architecture

Epicor ERP 10 can feed a private AI assistant through its REST service layer, executable BAQs and a read-only copy of the SQL Server database, with the model served locally on your GPUs. The limits are version-specific: check which REST generation and security features your 10.x release has before you design the tool layer, and plan for the upgrade path to Kinetic.

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.

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

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 Vantage, Vista, and E9, and a Real Kinetic Upgrade Path

Still on Epicor Vantage, Vista, or E9? Add a private LLM over that Progress OpenEdge database now, and use it to plan a Kinetic upgrade with real data, not guesswork.

Stuck on Epicor 9 and Vantage (Progress OpenEdge)?

Talk to engineers who work inside Epicor 9 and Vantage (Progress OpenEdge) 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

AI on your ERP

Integrations