Other ERPsFree referencePDF, Excel, Word exportQADAll QAD tools

QAD EE Key Fields Reference

This reference lists the QAD Enterprise Edition fields that report writers and 4GL developers use most, at column level, for pt_mstr, so_mstr, sod_det, wo_mstr, po_mstr, pod_det and ld_det. It covers QAD EE from eB2.1, where domains were introduced, through the 2017 releases on Progress OpenEdge.

Download:

Table	Field	Typical screen label	Type	Meaning	Maintained in
pt_mstr	pt_domain	Domain	character	Domain code; part of every key in EE	Set by the session domain
pt_mstr	pt_part	Item Number	character	Item number, primary key with pt_domain	1.4.1 Item Master Maintenance
pt_mstr	pt_desc1	Description	character	Item description line 1	1.4.1 Item Master Maintenance
pt_mstr	pt_desc2	Description (line 2)	character	Item description line 2	1.4.1 Item Master Maintenance
pt_mstr	pt_um	UM	character	Stocking unit of measure	1.4.1 Item Master Maintenance
pt_mstr	pt_prod_line	Prod Line	character	Product line; drives GL accounts	1.4.1 Item Master Maintenance
pt_mstr	pt_part_type	Item Type	character	User-defined item type for selection and reporting	1.4.1 Item Master Maintenance
pt_mstr	pt_status	Status	character	Item status code; restricts transactions via status code rules	1.4.1 Item Master Maintenance
pt_mstr	pt_group	Group	character	Item group for selection and reporting	1.4.1 Item Master Maintenance
pt_mstr	pt_pm_code	Pur/Mfg	character	Purchased or manufactured code used by planning	1.4.7 Item Planning Maintenance
pt_mstr	pt_abc	ABC Class	character	ABC class for cycle counting	1.4.5 Item Inventory Data Maintenance
pt_mstr	pt_ord_pol	Order Policy	character	MRP order policy such as POQ, LFL, FOQ, OTO	1.4.7 Item Planning Maintenance
pt_mstr	pt_buyer	Buyer/Planner	character	Buyer or planner code	1.4.7 Item Planning Maintenance
pt_mstr	pt_vend	Supplier	character	Default supplier	1.4.7 Item Planning Maintenance
pt_mstr	pt_site	Site	character	Default site	1.4.5 Item Inventory Data Maintenance
pt_mstr	pt_loc	Location	character	Default stocking location	1.4.5 Item Inventory Data Maintenance
pt_mstr	pt_lot_ser	Lot/Serial Control	character	Blank = none, L = lot, S = serial	1.4.5 Item Inventory Data Maintenance
pt_mstr	pt_added	Added	date	Date the item was created	Set on creation
so_mstr	so_domain	Domain	character	Domain code	Set by the session domain
so_mstr	so_nbr	Order	character	Sales order number, key with so_domain	7.1.1 Sales Order Maintenance
so_mstr	so_cust	Sold-To	character	Sold-to customer	7.1.1 Sales Order Maintenance
so_mstr	so_bill	Bill-To	character	Bill-to address code	7.1.1 Sales Order Maintenance
so_mstr	so_ship	Ship-To	character	Ship-to address code	7.1.1 Sales Order Maintenance
so_mstr	so_ord_date	Order Date	date	Order date	7.1.1 Sales Order Maintenance
so_mstr	so_due_date	Due Date	date	Header due date, default for lines	7.1.1 Sales Order Maintenance
so_mstr	so_po	Purchase Order	character	Customer's PO reference	7.1.1 Sales Order Maintenance
so_mstr	so_curr	Currency	character	Order currency	7.1.1 Sales Order Maintenance
so_mstr	so_stat	Action Status	character	Order action status; blank is normal, non-blank values such as hold stop shipping	7.1.1 Sales Order Maintenance
so_mstr	so_site	Site	character	Header site, default for lines	7.1.1 Sales Order Maintenance
so_mstr	so_shipvia	Ship Via	character	Carrier or shipping method	7.1.1 Sales Order Maintenance
sod_det	sod_domain	Domain	character	Domain code	Set by the session domain
sod_det	sod_nbr	Order	character	Sales order number; joins to so_nbr	7.1.1 Sales Order Maintenance
sod_det	sod_line	Line	integer	Line number	7.1.1 Sales Order Maintenance
sod_det	sod_part	Item Number	character	Item ordered; joins to pt_part	7.1.1 Sales Order Maintenance
sod_det	sod_qty_ord	Quantity Ordered	decimal	Quantity ordered in line UM	7.1.1 Sales Order Maintenance
sod_det	sod_qty_ship	Quantity Shipped	decimal	Cumulative quantity shipped	Updated by shipment transactions (7.9.15 Sales Order Shipments)
sod_det	sod_qty_inv	Quantity to Invoice	decimal	Shipped quantity not yet invoiced	Updated by shipment and invoice post
sod_det	sod_price	Net Price	decimal	Net unit price	7.1.1 Sales Order Maintenance
sod_det	sod_due_date	Due Date	date	Line due date	7.1.1 Sales Order Maintenance
sod_det	sod_site	Site	character	Shipping site for the line	7.1.1 Sales Order Maintenance
sod_det	sod_um	UM	character	Line unit of measure	7.1.1 Sales Order Maintenance
wo_mstr	wo_domain	Domain	character	Domain code	Set by the session domain
wo_mstr	wo_nbr	Work Order	character	Work order number	16.1 Work Order Maintenance
wo_mstr	wo_lot	ID	character	Work order ID; unique key	Assigned by the system
wo_mstr	wo_part	Item Number	character	Item being made	16.1 Work Order Maintenance
wo_mstr	wo_qty_ord	Quantity Ordered	decimal	Quantity to build	16.1 Work Order Maintenance
wo_mstr	wo_qty_comp	Quantity Completed	decimal	Cumulative quantity completed	Updated by receipts and backflush
wo_mstr	wo_status	Status	character	P planned, F firm planned, B batch, E exploded, A allocated, R released, C closed	16.1 Work Order Maintenance
wo_mstr	wo_due_date	Due Date	date	Work order due date	16.1 Work Order Maintenance
po_mstr	po_nbr	Order	character	Purchase order number	5.7 Purchase Order Maintenance
po_mstr	po_vend	Supplier	character	Supplier code	5.7 Purchase Order Maintenance
po_mstr	po_ord_date	Order Date	date	PO order date	5.7 Purchase Order Maintenance
pod_det	pod_nbr	Order	character	PO number; joins to po_nbr	5.7 Purchase Order Maintenance
pod_det	pod_line	Line	integer	PO line number	5.7 Purchase Order Maintenance
pod_det	pod_part	Item Number	character	Item on the PO line	5.7 Purchase Order Maintenance
pod_det	pod_qty_ord	Quantity Ordered	decimal	Quantity ordered	5.7 Purchase Order Maintenance
pod_det	pod_qty_rcvd	Quantity Received	decimal	Cumulative quantity received	Updated by PO receipts
ld_det	ld_site	Site	character	Site of the inventory balance	Inventory transactions
ld_det	ld_loc	Location	character	Location of the balance	Inventory transactions
ld_det	ld_part	Item Number	character	Item	Inventory transactions
ld_det	ld_lot	Lot/Serial	character	Lot or serial number	Inventory transactions
ld_det	ld_qty_oh	Quantity On Hand	decimal	On-hand quantity for site, location, lot and reference	Inventory transactions

Scope: QAD Enterprise Edition (eB2.1 through the 2017 releases) on Progress OpenEdge. Labels vary by version and language; confirm in your Data Dictionary. In EE every table carries a *_domain field (po_domain, pod_domain, ld_domain as well) and every query should filter on it.

Need a QAD report built from these tables?

Tell us what the report should show and your QAD release, and we will send back the joins and filters to use.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Search for a table prefix (sod_), a field name or a screen label such as Order Policy. The type column tells you how to compare and format the value in 4GL or SQL, and the maintained in column tells you which menu program to open when you need to see the value on screen. Use the table alongside the table-level QAD core tables reference when you are working out joins: header to detail on domain and order number, detail to item on domain and part.

so_mstr to sod_det: so_domain = sod_domain and so_nbr = sod_nbr.
sod_det to pt_mstr: sod_domain = pt_domain and sod_part = pt_part.
po_mstr to pod_det: po_domain = pod_domain and po_nbr = pod_nbr.

Where this lives in QAD

Field definitions sit in the Progress Data Dictionary of the QAD database, which you can open from the Progress Data Administration or Data Dictionary tools with the qaddb connected. In the character UI, Ctrl-F on a field shows its table and field name, which is the fastest way to map a screen label to a column. QAD's Database Definitions guides for each release list tables and fields, and are worth keeping for the version you run.

Worked example: open sales order balance

To list open quantities per line, join the header and detail on domain and order, and subtract shipped from ordered. In Progress 4GL: FOR EACH so_mstr NO-LOCK WHERE so_domain = global_domain, EACH sod_det NO-LOCK WHERE sod_domain = so_domain AND sod_nbr = so_nbr AND sod_qty_ord - sod_qty_ship > 0: DISPLAY so_nbr so_cust sod_line sod_part sod_qty_ord - sod_qty_ship LABEL "Open". END. Add sod_due_date to sort by due date. For value, multiply the open quantity by sod_price, remembering it is in the order currency so_curr.

Gotchas

Forgetting the domain filter is the classic EE mistake: queries run slowly on full index scans and can mix data from several domains. MFG/PRO eB2 and QAD Standard Edition databases have no domain fields, so drop the domain filter there. sod_qty_ord can be negative on return lines, so filter on sign if you only want shipments. wo_status values drive what a work order can do; closed orders (C) are often excluded from reports. Labels change with language and release, so trust field names, not labels.

Frequently Asked Questions

Where can I see QAD tables and fields?

Open the Progress Data Dictionary against the QAD database to browse tables, fields, types and indexes, or use the QAD Database Definitions guide for your release. In the character UI, pressing Ctrl-F on a field shows the table and field behind it. This reference covers the fields most reports need.

What does wo_status mean in QAD?

wo_status holds the work order status: P planned, F firm planned, B batch, E exploded, A allocated, R released and C closed. Status controls which transactions are allowed, for example issues and receipts against released orders. Many reports exclude C and focus on F, A and R orders.

Why do my QAD EE queries need the domain field?

In QAD Enterprise Edition every business table includes a domain field, and it is the first component of most indexes. Filtering on pt_domain, so_domain and so on lets Progress use the indexes and stops data from other domains appearing in results. Set it from global_domain in 4GL or a parameter in SQL.

How do I get on-hand quantity in QAD?

Sum ld_det.ld_qty_oh for the item, filtered by domain and optionally by site and location. ld_det holds one record per site, location, item, lot and reference. in_mstr also carries summary quantities per item and site, but ld_det is the detail behind it and the one to use when lot or location matters.

If you want planners to ask QAD about open orders, work orders and stock in plain English on your own servers, we can set that up over these same tables.

Talk to an Expert Explore More Free Tools