AdvancedSYSPROSQL tables / Accounts Payable / Accounts Receivable

SYSPRO Debit Note Table: Finding AP and AR Transaction Tables

Question
syspro debit note table

Also searched as

  • syspro sql table list
  • syspro apinvoice table
  • syspro arinvoice table
  • syspro database tables ap ar

Short answer

SYSPRO stores supplier invoices and related documents in the Accounts Payable tables and customer documents in the Accounts Receivable tables, with the SQL Server names prefixed by module such as Ap and Ar (for example ApSupplier, ApInvoice, ArCustomer). We cannot confirm one dedicated debit note table across releases, so identify yours by querying the catalog and testing with a known document.

Applies to: SYSPRO 7 and 8 on SQL Server. Table and column names can differ by release and modules, so verify them in your own database before building reports.

How to find the debit note data in your SYSPRO database

  1. 1Post a known test debit note in a test company and note the supplier, document number and date.
  2. 2Query INFORMATION_SCHEMA.TABLES for tables starting with Ap to list the Accounts Payable tables in your release.
  3. 3Query INFORMATION_SCHEMA.COLUMNS for the candidate tables and look for document type, invoice number, supplier, amount and date columns.
  4. 4Run a read-only SELECT on the candidate table filtered by your test supplier and document number, and confirm the debit note appears and how its type is flagged.
  5. 5Check sign conventions: debit notes may be stored as negative values or flagged by a document type code, which affects totals.
  6. 6Wrap the working query in a view and document it, then build reports on the view rather than on raw tables.

Discovering the schema

Never rely on a table list copied from another site, because modules, releases and customisations change the schema. Your own database is the reference, and a few catalog queries give a complete picture quickly.

Use a read-only login and, ideally, a restored copy of production. Do not point exploratory queries at the live transaction database during business hours.

-- List Accounts Payable tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'Ap%'
ORDER BY TABLE_NAME;

-- Columns of a candidate table
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ApInvoice'
ORDER BY ORDINAL_POSITION;

AP and AR structure in general

SYSPRO separates master tables, such as the supplier and customer masters, from transaction tables holding documents, and from posted or history tables used for ageing and inquiries. Keys normally combine the supplier or customer code with the document or invoice number, and often a branch.

Debit and credit notes in AP and AR are documents like invoices, flagged by type. Always test totals against a SYSPRO inquiry or report so you know your query handles sign and type the same way.

Safe reporting practice

Build views in a separate schema, grant report users access to the views only, and never write to SYSPRO tables with SQL. Updates must go through SYSPRO or the e.net business objects so that journals, balances and audit data stay consistent.

When an upgrade changes the schema, only the view definition needs maintenance, and the downstream reports continue to work.

Try it here, free

SYSPRO SQL Query Generator

Open full tool

Generate read-only SQL Server queries against common SYSPRO tables. Fill in database and filters, then copy the code.

Common pitfalls

  • !Assuming a table name from a forum without checking your database.
  • !Summing document amounts without handling debit and credit note sign or type.
  • !Writing to SYSPRO tables directly, which breaks reconciliation.
  • !Running heavy queries on the live database during peak hours.
  • !Skipping a reconciliation of the query result to a SYSPRO report.

How an ERP-grounded AI assistant handles this

Netray adds a private AI layer to the SYSPRO you already run, including on-prem installs where the SQL Server and application server never leave your network. ERPray answers questions over live SYSPRO data with read-only SQL or e.net queries it shows you, under the operator's own SYSPRO security, so a planner or accountant can ask the question in plain language and see exactly which tables or business objects produced the answer. Any write-back, such as posting a transaction, goes through a human approval step. The model is open-weight and runs on your own GPUs, so company data is not sent to a public AI service.

See ERPray

Frequently asked questions

What table stores debit notes in SYSPRO?

AP debit notes are held with Accounts Payable documents, but the exact table and type flag should be confirmed in your own database. Post a test debit note, then search the Ap tables for it, and verify against a SYSPRO report.

Are SYSPRO table names consistent?

They follow a module prefix convention such as Ap for payables and Ar for receivables, but details vary by release and installed modules. Use the catalog views on your own database as the source of truth.

Can I update SYSPRO tables with SQL?

No. Direct updates bypass journals and validation and cause reconciliation problems. Use SYSPRO functions or the e.net business objects for any change, and keep SQL access read-only.

How do I get AP aged balances from SQL?

Query the AP document tables and apply the same ageing logic as the SYSPRO report, then reconcile your totals to that report. Document types and signs matter, so test with known suppliers.

Related

Advanced

SYSPRO SQL Query for Inventory On Hand by Warehouse

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.

How-to

How to Do a GRN (Goods Received Note) in SYSPRO

In SYSPRO you receive goods against a purchase order in the Purchase Orders module, which creates a goods received note (GRN), puts stock into the warehouse and posts the value to a GRN suspense account. When the supplier invoice arrives, Accounts Payable matches it to the GRN, which clears the suspense and records any price difference.

How-to

SYSPRO Report Writer, Reporting Services and Crystal: Options and Fixes

SYSPRO offers several reporting routes: built-in operational reports, the Report Writer for custom listings, SYSPRO Reporting Services (SRS, built on SQL Server Reporting Services) for formatted documents, and Crystal Reports in some older and customised setups. When SRS printing or print preview fails, check the report server service, its configured URL and credentials, and the permissions of the SYSPRO service account first.

How-to

SYSPRO API Documentation: e.net Business Objects and REST

SYSPRO's integration layer is e.net Solutions: a set of business objects (queries, transactions and setup objects) that take and return XML, reachable through a COM or .NET interface or through web services hosted by the e.net components. The documentation ships with the product help and the schema (XSD) files for each business object; a typical call is Logon, then Query or Transaction Post, then Logoff.

How-to

SYSPRO reporting API: e.net, SRS server-side reporting, Crystal Reports and SRS preview problems

SYSPRO gives you three reporting routes: the e.net Solutions business object API (XML in, XML out) for programmatic data, SYSPRO Reporting Services (SRS) for designed reports run on the server, and Crystal-based or SQL-based custom reports. If the SRS preview does not work, check the services, the runtime and bitness, the SQL permissions and the client and server versions in that order.

Error fix

Error validating SYSPRO online profile: what it means and how to fix it

A profile validation error means the SYSPRO client or service could not confirm the operator, company, licence or database connection it was handed at logon. Check the service and licence first, then the operator and company, then the database and disk, in that order. Storage errors are usually full disks, a locked SQL file or a tempdb problem rather than a SYSPRO bug.

AI for ERP

AI 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 ERP

AI 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.

Talk to an ERP engineerBrowse ERP Help
All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

AI on your ERP

Integrations

Software