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

> Source: https://netray.co/erp-help/qad-erp-tables-and-rest-api
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

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

## Kind

how-to

## System

qad

## Erp

QAD Adaptive ERP (QAD Enterprise Edition)

## Module

Database tables / Integration APIs

## Query

qad erp tables

## Alternate Queries

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

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

## Steps

### How to find and read QAD tables

**Items:**

- Identify your database name and the OpenEdge version with your Progress administrator; QAD usually runs several databases (for example a main and a transactional one).
- Open the OpenEdge Data Dictionary or Data Administration against the database to list every table, field, label and index.
- Search 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_.
- Remember that most tables carry a domain field (for example pt_domain), so always filter by domain in queries, otherwise you mix entities.
- Create 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.
- Test with a bounded query and check that the table is indexed on the columns you filter by before running anything wide.
- For integration beyond reads, check the API options below instead of writing to tables.

## Sections

### Key tables to know

**Body:**

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

**Body:**

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.

**Code:**

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

**Body:**

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.

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

## Ai Angle

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.

## Faq

### What does _mstr mean in QAD table names?

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

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

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

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

## Search terms

qad erp tables, qad pt_mstr, qad so_mstr sod_det, qad database schema, qad erp api documentation, qad erp rest api, qad qxtend, qad openedge sql query, qad data dictionary

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