Developer ToolsFree generatorPDF, Excel, Word exportQAD

QAD Progress 4GL Query Generator (pt_mstr, sod_det, pod_det, ld_det)

QAD is built on the Progress OpenEdge database, so a quick extract is usually a short FOR EACH statement. This generator writes four common read-only queries for the item master, open sales orders, open purchase orders and stock by location.

Fill in

Domain

The QAD EE domain code. Domain-aware tables carry a _domain field.

Site

Part number prefix

Stock location

Output file path

Server path the session can write to.

Generated code

Download:

Item master by prefix (pt_mstr)

OUTPUT TO VALUE("/tmp/qad_extract.csv").
FOR EACH pt_mstr NO-LOCK
    WHERE pt_domain = "10USA"
    AND pt_part BEGINS "BRK":
    EXPORT DELIMITER "," pt_part pt_desc1 pt_um pt_prod_line pt_status pt_site.
END.
OUTPUT CLOSE.

Open sales order lines (so_mstr, sod_det)

FOR EACH sod_det NO-LOCK
    WHERE sod_domain = "10USA"
    AND sod_site = "1000"
    AND sod_part BEGINS "BRK"
    AND sod_qty_ord > sod_qty_ship,
    FIRST so_mstr NO-LOCK
    WHERE so_domain = sod_domain AND so_nbr = sod_nbr:
    DISPLAY so_nbr so_cust sod_line sod_part sod_qty_ord sod_qty_ship sod_due_date.
END.

Open purchase order lines (po_mstr, pod_det)

FOR EACH pod_det NO-LOCK
    WHERE pod_domain = "10USA"
    AND pod_site = "1000"
    AND pod_part BEGINS "BRK"
    AND pod_qty_ord > pod_qty_rcvd,
    FIRST po_mstr NO-LOCK
    WHERE po_domain = pod_domain AND po_nbr = pod_nbr:
    DISPLAY po_nbr po_vend pod_line pod_part pod_qty_ord pod_qty_rcvd pod_due_date.
END.

Stock by location (ld_det)

FOR EACH ld_det NO-LOCK
    WHERE ld_domain = "10USA"
    AND ld_site = "1000"
    AND ld_loc = "FG01"
    AND ld_qty_oh <> 0:
    DISPLAY ld_part ld_loc ld_lot ld_ref ld_qty_oh ld_status.
END.

All queries use NO-LOCK and only read data. Run them from a Progress editor or a procedure in a test or reporting database first. Field names are those used by standard QAD EE schemas; custom or older versions may differ, and non-domain releases have no _domain field, so remove those conditions. Always check the field list with the data dictionary before using results.

Want these answers without writing 4GL?

Optional: a short call on putting a read-only question layer over your QAD data.

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

How to use the generator

Enter your domain, site, a part number prefix and the stock location you want to inspect, then copy the code block you need. Paste it into a Progress procedure editor connected to a reporting or test database. Start with the item master query because it returns the fewest rows and confirms that the domain value is correct. The output file field only matters for the first block, which writes a comma separated file you can open in Excel. The other blocks use DISPLAY so you can eyeball results on screen.

Run in a test or reporting copy first.
Keep NO-LOCK on every read-only FOR EACH.
Narrow by domain and site before running on large tables.
Use BEGINS only on indexed fields such as part.

The tables behind each query

pt_mstr is the item master, keyed on domain and part. so_mstr holds the sales order header and sod_det the lines, joined on order number. po_mstr and pod_det do the same for purchasing. ld_det is the location detail table with one row per part, site, location, lot and reference, and holds quantity on hand. Because QAD tables are indexed, queries that start with the leading index fields (domain, then part or order number) are fast, while queries that filter on unindexed fields scan the whole table.

Reading and adapting the results

Open lines are identified by comparing ordered and shipped or received quantity. This is a simple approximation: it ignores closed flags, cancelled lines and unit of measure conversions, so compare a sample against the standard browse or report before trusting totals. To extend a query, add fields to DISPLAY or EXPORT, or add another WHERE condition. Check the data dictionary for the exact field name in your version before you add anything.

Common mistakes

The most frequent errors are leaving out the domain condition in a multi-domain system, which returns rows from other entities, running without NO-LOCK and blocking users, and pointing an extract at a path the database server cannot write to. Another trap is treating pt_status as the same across sites. Item status per site is held on the item-site planning record, so a part can look active in the master but be blocked at a particular site.

Frequently Asked Questions

Is it safe to run these on production?

The queries only read data and use NO-LOCK, so they should not block users, but a large unindexed scan can still load the database. Run them first against a test or reporting copy, and narrow by domain and site before using production.

Why does my query return nothing?

The usual cause is the domain value. Domain codes are case sensitive strings set up in the domain maintenance, so confirm yours. On releases before domains existed, remove the domain conditions entirely.

Can I use SQL instead of 4GL?

Yes. OpenEdge supports SQL-92 through ODBC or JDBC, and many teams use that for reporting tools. Table and column names are the same, but underscores replace hyphens, so check your schema mapping.

Does this work on QAD Adaptive ERP?

Adaptive ERP is built on the same core schema, so table names are mostly the same, but the cloud version may restrict direct database access. Use the supported APIs or data export when you cannot open a Progress session.

If you need these answers on demand without writing 4GL, we can put a read-only natural language layer over your QAD data, showing the query it ran.

Talk to an Expert Explore More Free Tools

Related Tools

Other ERPs

More Developer Tools tools

See all

Back to All Tools