SYSPRO SQL Query for Inventory On Hand by Warehouse
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.
Try it here, free
SYSPRO SQL Query Generator
Generate read-only SQL Server queries against common SYSPRO tables. Fill in database and filters, then copy the code.
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.
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.
Related
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-toHow 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-toHow 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-toSYSPRO 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-toHow 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 fixFix 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 ERPAI for SYSPRO, Grounded in Your Own Manufacturing Data
Add AI to SYSPRO without moving your manufacturing data to a public model. On-premise and private-cloud LLMs for SYSPRO in Australia, NA, and beyond.
AI for ERPAI for SYSPRO Manufacturing Operations: From Work Centers to Answers
SYSPRO Manufacturing Operations generates data on work centers, routings, and WIP - see how private AI turns it into shop floor answers on-prem.
Stuck on SYSPRO?
Talk to engineers who work inside SYSPRO every week, and who build private AI that answers these questions from your own ERP data.
New to SYSPRO? Start with the SYSPRO guide and toolkit, or plan with Training Path Builder, Upgrade Path Planner, ERP Table and Field Explorer.
Keep exploring
Free tools
- SYSPRO SQL Query GeneratorGenerate read-only SQL Server queries against common SYSPRO tables. Fill in database and filters, then copy the code.
- SYSPRO MRP ChecklistA checklist of the master data, parameters and review steps that decide whether SYSPRO MRP output can be trusted.
- SYSPRO Data Migration Mapping WorksheetTrack each master data object from legacy system to SYSPRO with transform rules, owner and sign off status.
ERP fixes and how-tos
- SYSPRO tables list: core database tables by module and safe queriesSYSPRO database naming, the core tables by module for inventory, sales orders and WIP, and safe read-only SQL patterns for reporting without risking data.
- How to Find and Fix Negative Quantity on Hand in SYSPROWhy SYSPRO shows negative quantity on hand, how to find the transactions that caused it, and how to correct it with adjustments without breaking costing.
- How to Add AI to SYSPRO On-Prem Without Moving Your DataReference architecture for adding a private AI assistant to on-prem SYSPRO: SQL Server read replica, e.net business objects, retrieval, local GPU and audit.
AI on your ERP
- AI for SYSPRO production and job costingHow AI reads SYSPRO WIP jobs, labor and material postings to explain job cost variances and late work orders, with read-only access and on-prem hosting.
- AI for SYSPRO Manufacturing Operations: From Work Centers to AnswersSYSPRO Manufacturing Operations generates data on work centers, routings, and WIP - see how private AI turns it into shop floor answers on-prem.
Integrations
- How to connect Power BI to SYSPROConnect Power BI to SYSPRO: SQL Server direct vs data warehouse, on-prem data gateway, manufacturing dashboards, refresh strategy and natural-language Q&A.
- How to build an MCP server for SYSPROBuild a Model Context Protocol server for SYSPRO: which read-only tools to expose, e.NET and SQL Server access, transport choices, scoping and audit logging.