AdvancedSYSPROSQL Server / Reporting

SYSPRO SQL Query for Inventory On Hand by Warehouse

Question

SYSPRO SQL query inventory on hand by warehouse

Also searched as

SYSPRO InvMaster InvWarehouse join
SYSPRO SQL table names inventory
SYSPRO read database directly safe
SYSPRO company database SQL report

Short answer

SYSPRO runs on SQL Server with one database per company, typically named like SysproCompanyA, and inventory is split between InvMaster for part-level data and InvWarehouse for quantities per warehouse. Join them on StockCode, filter, and read the quantity columns. Use a read-only login, never write to SYSPRO tables directly, and prefer e.net business objects for any change.

Applies to: SYSPRO 7 and SYSPRO 8 on Microsoft SQL Server; table and column names should be confirmed against the data dictionary for your exact release and any customisations

How to build a safe inventory query

1Ask your DBA for a SQL login or Windows group with db_datareader on the company database only. Do not use the SYSPRO application account.
2Identify the right company database. Company ids such as A and B usually map to databases SysproCompanyA and SysproCompanyB, with shared administration data in a separate admin database.
3Start from InvMaster for descriptions, stock status and unit of measure, then join InvWarehouse on StockCode for per-warehouse quantities.
4Filter by warehouse and by stock status. Remember that obsolete or inactive stock codes can still carry quantity.
5Add the quantity columns you need, typically on hand and allocated, and calculate available as the difference rather than expecting a stored column.
6Use READ UNCOMMITTED or snapshot isolation for reporting queries on busy systems, and understand the dirty-read caveat.
7Compare a sample to the Inventory Query screen in SYSPRO to confirm you read the columns correctly.
8Save the query as a view or a SYSPRO Reporting Services dataset rather than leaving it in a personal script folder.

The core join

InvMaster holds one row per stock code, with description, product class, stocking unit of measure, and status flags. InvWarehouse holds one row per stock code and warehouse. The join is by StockCode. Quantities live on the warehouse row, so a part stocked in three warehouses returns three rows.

Aggregating across warehouses with GROUP BY StockCode gives company-wide on hand, but remember that in-transit and consigned stock may be tracked separately. Do not assume that the sum equals what finance values.

-- Confirm column names against your release's data dictionary
USE SysproCompanyA;

SELECT m.StockCode, m.Description, w.Warehouse,
       w.QtyOnHand, w.QtyAllocated,
       w.QtyOnHand - w.QtyAllocated AS QtyAvailable
FROM InvMaster AS m
JOIN InvWarehouse AS w ON w.StockCode = m.StockCode
WHERE w.Warehouse = 'FG'
  AND w.QtyOnHand <> 0
ORDER BY m.StockCode;

Why not write to the tables

SYSPRO business logic, such as costing, ledger integration, movement records, allocation and audit trails, lives in the application and its business objects, not in database constraints. A direct UPDATE on InvWarehouse changes a number without creating the matching movement or ledger entry, and the inventory sub-ledger no longer reconciles. Vendors generally treat direct writes as unsupported.

For any change, use the e.net business objects or the SYSPRO client programs. For reads, direct SQL is normal and widely used, provided the load is controlled.

Performance and locking

Reporting queries that scan large tables during shift changes or MRP runs compete with SYSPRO transactions. Add predicates on indexed columns, avoid SELECT star across wide tables, and schedule heavy extracts out of hours or against a replica or restored copy.

READ UNCOMMITTED avoids blocking but can return half-posted data. For finance-grade numbers, run on a consistent snapshot or a backup restored to a reporting server.

Customisations and custom forms

Many sites add user-defined fields or custom tables. Those are not in the standard dictionary, so document them. When the application is upgraded, standard tables can change shape, so keep queries in views with a short list of dependencies so you know what to retest.

Common pitfalls

!Using a read-write login for reporting and one day running an UPDATE by mistake.
!Assuming available stock is a stored column when it is derived from on hand, allocations and sometimes reservations.
!Querying the wrong company database and reconciling against the wrong ledger.
!Treating obsolete stock codes as irrelevant and missing the quantity still on the shelf.
!Running unfiltered scans during MRP or period-end and slowing every user.
!Copying a query from another release without checking that the column still exists.

How an ERP-grounded AI assistant handles this

An assistant grounded in SYSPRO translates a question such as which finished goods have stock below the reorder point in the main warehouse into SQL against InvMaster and InvWarehouse, shows the query it ran, runs it through a read-only account, and respects the operator's SYSPRO role. That is the pattern behind ERPray: queries are visible and inspectable, and any change is a separate approved action.

See ERPray

Frequently asked questions

Is it safe to query the SYSPRO database directly?

Reading is common and safe if you use a read-only login and control the load. Writing directly is not recommended and often unsupported, because it bypasses costing, ledger integration and audit. Use business objects for changes.

Where do I find the table and column definitions?

SYSPRO publishes a data dictionary and schema information with each release, and you can also inspect INFORMATION_SCHEMA in SQL Server. Use those for your version rather than trusting examples online, since columns can be added or renamed between releases.

Which database holds the company data?

Each SYSPRO company has its own SQL Server database, commonly SysproCompanyA, SysproCompanyB and so on, plus an administration database for operators and system-wide settings. Names are set during installation, so confirm with your DBA.

Can I use these queries in Power BI or SSRS?

Yes. Create views over the tables, point the report tool at a read-only connection, and schedule refreshes outside peak hours. SYSPRO also offers its own reporting and analytics tools, which may be simpler for standard operational reports.

How-to

How to Find and Fix Negative Quantity on Hand in SYSPRO

Negative on hand in SYSPRO means more stock was issued, shipped or consumed than was ever received into that warehouse. It is almost always a timing or receiving problem, not corrupt data. Find the movements that drove the balance below zero, fix the receiving or unit-of-measure root cause, and then correct the quantity with a proper inventory adjustment so costs stay consistent.

How-to

How to Create a SYSPRO Sales Order with the SORTOI Business Object

SORTOI is the SYSPRO e.net business object that enters sales orders from outside the SYSPRO client. You log on to get a session id, call the business object with a Parameter XML and a Document XML describing the order header and lines, then read the XML that comes back for warnings and the new sales order number. Always test in a non-production company first.

How-to

How to Run Requirements Planning (MRP) in SYSPRO

SYSPRO's MRP engine lives under Planning and Scheduling > Requirements Planning. Set planning parameters per warehouse, run the Requirements Calculation to net demand against supply, then review and release the resulting suggestions as purchase requisitions or work orders. A full regenerative run typically runs on a schedule, with net change runs in between to pick up new activity.

How-to

SYSPRO Job Will Not Close or Leaves a WIP Balance: What to Check

A SYSPRO job leaves a WIP balance or refuses to close when material, labour or overhead is still sitting on the job without a matching receipt of finished goods. Check that the full quantity has been received through job receipts, that all allocated materials are issued or deallocated, and that outstanding labour is posted. Then close the job so SYSPRO transfers the remaining difference to variance.

How-to

How to Create a Generic Inquiry in Acumatica

Generic Inquiries (GI) are Acumatica's no-code query builder for joining tables, adding filters, and exposing the result as an inquiry screen, dashboard data source, or API endpoint. Open System > Customization > Generic Inquiry, add your base table, define joins and conditions, then run and save. No SQL or customization project is required.

Error fix

Fix Odoo AccessError: You Are Not Allowed to Access Records

Odoo raises AccessError when a user's security groups do not grant a CRUD right on a model through ir.model.access.csv, or when an ir.rule record rule filters the specific record out for that user or company. Fix it by adding the user to the group named in the error, editing the model's access rights, or reviewing the record rule's domain under Settings > Technical > Security.

AI for ERP