How-toQAD Adaptive ERP (QAD Enterprise Edition)Database tables / Integration APIs

QAD ERP Tables and API Access: Key Tables, SQL and Integration Options

Question
qad erp tables

Also searched as

  • qad erp api documentation
  • qad erp rest api
  • qad pt_mstr table fields
  • qad database table list

Short answer

QAD Enterprise Edition stores data in Progress OpenEdge databases with short table names ending in _mstr (master) and _det (detail), for example pt_mstr for items, so_mstr for sales orders and po_mstr for purchase orders. You can read them with OpenEdge SQL-92 over ODBC or JDBC for reporting. For writing data or standard integration, use QAD's supported integration layer rather than direct database writes.

Applies to: QAD Enterprise Edition / Adaptive ERP on Progress OpenEdge (Linux, Unix and Windows). Table coverage varies by version; verify in the data dictionary of your own database

How to find and read QAD tables

  1. 1Identify your database name and the OpenEdge version with your Progress administrator; QAD usually runs several databases (for example a main and a transactional one).
  2. 2Open the OpenEdge Data Dictionary or Data Administration against the database to list every table, field, label and index.
  3. 3Search by business object: items pt_, sales orders so_ and sod_, purchase orders po_ and pod_, work orders wo_, inventory ld_ and tr_, customers cm_, suppliers vd_, addresses ad_.
  4. 4Remember that most tables carry a domain field (for example pt_domain), so always filter by domain in queries, otherwise you mix entities.
  5. 5Create a read-only database user and connect through ODBC or JDBC using the OpenEdge SQL-92 driver; SQL engine names replace hyphens in field names with underscores.
  6. 6Test with a bounded query and check that the table is indexed on the columns you filter by before running anything wide.
  7. 7For integration beyond reads, check the API options below instead of writing to tables.

Key tables to know

Common tables: pt_mstr (item master, pt_part is the item number), ptp_det (item planning by site), in_mstr (inventory by item and site), ld_det (location detail, on-hand by location and lot), tr_hist (inventory transaction history), so_mstr and sod_det (sales order header and lines), po_mstr and pod_det (purchase order header and lines), wo_mstr (work orders), ps_mstr (product structure, the BOM), ro_det (routing operations), cm_mstr (customer master), vd_mstr (supplier master), ad_mstr (addresses), and GL tables such as glt_det and gltr_hist.

The naming is compact and consistent: the prefix is the table family, _mstr is a header or master, _det is detail, _hist is history. Field names repeat the prefix, so sod_qty_ord is quantity ordered on a sales order line. Treat this list as orientation and confirm field names in your own dictionary, because versions and customizations differ.

Querying with SQL

OpenEdge exposes tables to SQL-92 clients, which is how BI tools and reporting databases usually read QAD. Use a dedicated read replica or reporting database when possible, because heavy joins on the live transactional database compete with users.

-- Open sales order lines for one domain
SELECT h.so_nbr, h.so_cust, d.sod_line, d.sod_part, d.sod_qty_ord
FROM PUB.so_mstr h
JOIN PUB.sod_det d ON d.sod_nbr = h.so_nbr AND d.sod_domain = h.so_domain
WHERE h.so_domain = 'US01'
AND d.sod_qty_ord > d.sod_qty_ship;

API options

QAD's supported integration route has historically been its integration layer (QXtend, with inbound and outbound components) and the business logic exposed through service interfaces, rather than SQL writes. Newer cloud-oriented releases add web and REST style interfaces. What exists depends on your version and license, so check the QAD documentation for your release.

QAD documentation and knowledge base articles are served through QAD's customer support portal and community, which generally require a customer login. If you are a partner or customer, ask your QAD administrator for access. Writing directly into tables is not supported: it skips business logic, audit trails and domain rules.

Try it here, free

QAD Core Tables Reference

Open full tool

A searchable lookup of the QAD EE tables you query most, what each holds and how they are keyed.

Common pitfalls

  • !Querying without the domain filter and mixing legal entities.
  • !Running wide joins on the production transactional database during working hours.
  • !Assuming field names are the same across versions or after customization.
  • !Writing to tables to save integration effort, which bypasses business logic.
  • !Using the live admin account for reporting instead of a read-only user.

How an ERP-grounded AI assistant handles this

Because the tables are terse, people waste time translating business questions into pt_, sod_ and ld_ joins. A private AI layer such as ERPray can read your data dictionary, write the SQL for a question like open orders by customer, show the query it ran, and stay read-only under your roles. It can run fully on-prem next to the OpenEdge database.

See ERPray

Frequently asked questions

What does _mstr mean in QAD table names?

_mstr marks a master or header table (pt_mstr, so_mstr, po_mstr, cm_mstr), _det marks detail or line tables, and _hist marks history. The prefix identifies the business area. It is a QAD naming convention that is consistent across most of the schema.

Does QAD have a REST API?

It depends on your version and licensing. QAD has long offered an integration layer (QXtend) and service interfaces, and newer releases add web and REST-style access. Confirm your release in QAD documentation or with QAD support rather than assuming one endpoint set.

Can I connect Power BI or another BI tool directly?

Yes, commonly through the OpenEdge ODBC or JDBC SQL driver, ideally against a reporting copy and with a read-only user. Always filter by domain and keep queries bounded. Many teams replicate to a warehouse so BI load never touches production.

Where is QAD API documentation?

Official documentation and knowledge base articles are on QAD's customer support portal and community, which usually need a customer or partner login. Your QAD administrator or account contact can grant access for your product release.

Related

Error fix

QAD Says "Unable to Connect to the Application Server": How to Fix It

This error almost always means the Progress OpenEdge AppServer broker behind QAD is stopped, unreachable over the network, or out of licensed connections, not a database problem. Confirm the broker process is running and healthy in OpenEdge Explorer, check the port and firewall path from the client, then restart the broker with clean agent settings.

Error fix

QAD "Lock Table Overflow, Increase -L on Server (915)": Cause and Fix

Progress error 915 means the database server ran out of entries in its shared record lock table, which is sized by the -L startup parameter. Either one session holds an enormous number of record locks inside a single transaction, or -L is simply too small for your workload. Find the session first, then raise -L in the database startup parameters and restart the database.

How-to

QAD ERP Software Implementation: Steps, Environments and Knowledge Base

A QAD implementation runs from scoping and design through configuration, data load, testing, training and cutover. A QAD environment is a separate installation (application, database and configuration) used for a purpose: development, test, training or production. Knowledge base articles and product documentation are on QAD's customer support portal and community.

Advanced

How to Add a Private AI Layer to QAD Enterprise Edition On-Prem

QAD EE runs on a Progress OpenEdge database, which can be read through the OpenEdge SQL-92 engine over JDBC or ODBC without touching the 4GL application. A private AI layer reads a replica or reporting copy, indexes documents locally, serves an open-weight model on your own GPUs, and writes back only through QXtend or another supported QAD interface with human approval.

AI for ERP

AI for QAD Adaptive ERP in automotive and industrial manufacturing

Add AI to QAD Adaptive ERP or Enterprise Edition for automotive and industrial manufacturing, grounded on QXtend and QAD's API layer, on-prem or private cloud.

AI for ERP

AI for QAD in Automotive: Schedules, Traceability and IATF Evidence From Your Own OpenEdge Data

Private AI on QAD Adaptive ERP for automotive suppliers: schedule releases, ASN, lot traceability and IATF 16949 evidence, read-only, on-prem or sovereign.

Stuck on QAD Adaptive ERP (QAD Enterprise Edition)?

Talk to engineers who work inside QAD Adaptive ERP (QAD Enterprise Edition) 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

Software