Developer ToolsFree generatorPDF, Excel, Word exportSYSPROSouth Africa

SYSPRO SQL Query Generator for Stock, Sales Orders and Jobs

SYSPRO stores company data in SQL Server, so reports, dashboards and integrations often start with a query on tables such as InvMaster, InvWarehouse, SorMaster and WipMaster. This generator fills the database name, warehouse and filters into ready queries you can paste into SQL Server Management Studio.

Fill in

Company database name

The SQL Server database that holds the company tables.

Warehouse code

Stock code starts with

Customer code

Minimum quantity on hand

Generated code

Download:

Stock on hand by warehouse

USE SysproCompanyA;

SELECT w.StockCode,
       m.Description,
       w.Warehouse,
       w.QtyOnHand
FROM   InvWarehouse AS w
JOIN   InvMaster   AS m ON m.StockCode = w.StockCode
WHERE  w.Warehouse = '01'
  AND  w.StockCode LIKE 'GBX%'
  AND  w.QtyOnHand >= 1
ORDER BY w.StockCode;

Sales orders for one customer

USE SysproCompanyA;

SELECT s.SalesOrder,
       s.Customer,
       s.OrderStatus,
       s.OrderDate
FROM   SorMaster AS s
WHERE  s.Customer = 'CUST001'
ORDER BY s.OrderDate DESC;

Jobs for a stock code prefix

USE SysproCompanyA;

SELECT j.Job,
       j.StockCode,
       j.JobDescription,
       j.QtyToMake,
       j.QtyManufactured
FROM   WipMaster AS j
WHERE  j.StockCode LIKE 'GBX%'
ORDER BY j.Job;

These queries are read-only SELECT statements. Table and column names are commonly seen in SYSPRO 7 and 8 company databases, but verify them against your version and any custom forms. Run on a reporting copy or with a read-only login, never on a login with write rights.

Ask SYSPRO questions in plain English

Optional: see how a read-only natural language layer could turn questions into reviewed SQL on your own server.

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

How to use the generator

Enter the name of your company database, a warehouse code, a stock code prefix, a customer code and a minimum quantity. The three code blocks update as you type. Copy the one you need into Management Studio, a Crystal report command or a Power BI source. Each block is a single SELECT, so you can extend it with extra columns once you confirm it returns what you expect.

Tables used and why

InvMaster holds the stock code master and description. InvWarehouse holds quantities per stock code and warehouse. SorMaster holds sales order headers, and WipMaster holds work in progress job headers. Joining InvWarehouse to InvMaster on stock code gives a readable stock listing. Always filter by a warehouse or prefix, because unfiltered queries on large companies can return hundreds of thousands of rows.

Safe querying habits

SYSPRO business logic lives in the application, so direct writes to its tables are unsafe and can corrupt data. Use read-only logins, query a reporting copy where possible, and prefer SYSPRO e.net business objects for any change. Keep queries simple and avoid locking hints on production. When a figure must match a SYSPRO report exactly, check whether the report uses additional tables or rules.

Common mistakes

People forget to qualify the database name, join on description instead of stock code, or assume a single warehouse. Another mistake is ignoring custom fields and add-ons that change how stock is held. Test every new query against a known stock code and compare it with the figure shown on screen before sharing it.

Frequently Asked Questions

Is it safe to run these queries on a live SYSPRO database?

They are read-only SELECT statements and are generally safe, but large unfiltered queries can slow a busy server. Use a read-only login, filter by warehouse or prefix, and prefer off-hours or a reporting copy for heavy extracts.

Can I write data back with SQL?

You should not. SYSPRO enforces business rules in the application layer. Direct inserts or updates can break integrity between related tables. Use SYSPRO e.net business objects or the supported import tools for any write.

Why do my quantities differ from the SYSPRO screen?

The screen may show available quantity after allocations, in-transit or reserved stock, while the table column shows on-hand quantity. Check which field the screen uses and whether your query applies the same warehouse and status filters.

Do the names change between versions?

Core tables are stable across SYSPRO 7 and 8, but columns can be added and add-ons create their own tables. Look at the table in Management Studio and adjust the column list for your version.

If you want people to ask questions in plain language and get checked SQL back from your SYSPRO database, Netray builds that on-prem with read-only access.

Talk to an Expert Explore More Free Tools

Related Tools

Costing & Finance

More Developer Tools tools

See all

Back to All Tools