SYSPRO Analytical Reports and PO Reporting

How to get PO status, receipts and open commitments out of SYSPRO

1. 1Open the Purchase Orders module from the SYSPRO menu and look under its Reports (or Inquiries) section for the standard listings, such as outstanding or open orders, receipts and supplier-based listings. Exact report names depend on version.
2. 2Run the report with filters for supplier, buyer, warehouse, due date range and order status so you only pull open lines, not history.
3. 3Send the output to the screen, printer, or a file; where the report is delivered through SRS, use the export options to save as PDF or Excel.
4. 4For interactive checks use the PO Browse and the Purchase Order Inquiry (PO status, lines, receipts and invoice matching) rather than a printed report.
5. 5For management dashboards, build or use the PO and supplier views in SYSPRO Analytics, if your site licenses it.
6. 6For anything custom, such as open commitment value by supplier, query PorMasterHdr and PorMasterDetail with a read-only SQL login and test against a copy first.
7. 7Schedule the report through SYSPRO's reporting or Windows scheduling so buyers get a weekly overdue-lines file.

SQL for open PO commitments

SELECT d.PurchaseOrder, d.Line, d.MStockCode,
       d.MOrderQty, d.MReceivedQty,
       (d.MOrderQty - d.MReceivedQty) AS OutstandingQty
FROM PorMasterDetail d
JOIN PorMasterHdr h ON h.PurchaseOrder = d.PurchaseOrder
WHERE d.MOrderQty > d.MReceivedQty
ORDER BY d.PurchaseOrder, d.Line;

Common pitfalls

• !Running SQL against the production database with a write-capable login. Use a read-only account and never update SYSPRO tables directly.
• !Assuming a PO report ties to the GL. Commitments, GRN suspense and exchange rates cause differences.
• !Forgetting the company database. SYSPRO keeps each company in its own database.
• !Exporting a report to Excel and losing leading zeros on supplier or stock codes. Format those columns as text.
• !Relying on a column name from a forum post. Check names in your own version before shipping a report.