Top 10 Dynamics GP Tables Every Consultant Should Know by Heart

If you live in Dynamics GP long enough, you realize success often comes down to knowing where the data lives. Below are the ten tables I reach for constantly—what they store, how to join them, and a bite-size query you can use right away. All samples are read-only and use the Fabrikam database code TWO; swap it for your company DB.

Quick Reference (what & why)

SY00500 — Batches
Tracks batches across modules; crucial when something’s “stuck” or unposted.
GL20000 — General Ledger Open
Un-year-closed GL detail (current year). Your best friend for recon.
GL30000 — General Ledger History
Prior years’ GL detail. Same shape as GL20000, different period.
PM20000 — Payables Open
Vendor transactions not yet fully applied/closed.
PM30200 — Payables History
Closed/fully applied vendor transactions.
RM20101 — Receivables Open
Customer transactions in open status.
RM30101 — Receivables History
Closed/fully applied customer transactions.
SOP10100 — SOP Work
Sales document headers in work/open (quotes, orders, invoices before transfer/post).
SOP30200 — SOP History
Posted/voided SOP document headers.
IV00101 — Item Master
Core item metadata used across SOP/POP/IV.

1) SY00500 (Batches)

Why it matters: Fastest way to find who “owns” a batch and which series it sits in.

Key columns: BACHNUMB, BCHSOURC, SERIES, USERID, BACHDATE, BCHSTTUS (0=available).


-- Unposted or locked batches by series
SELECT SERIES, BACHNUMB, BCHSOURC, USERID, BACHDATE, BCHSTTUS
FROM TWO..SY00500
WHERE BCHSTTUS <> 0
ORDER BY SERIES, BACHNUMB;

Pro tip: Use SERIES to triage (100=Financial, 200=Sales, 300=Purchasing, 400=Inventory).

2) GL20000 (GL Open)

Why it matters: Current-year GL detail for reconciliations and drill-backs.

Key columns: JRNENTRY, TRXDATE, ACTNUMST, DEBITAMT, CRDTAMT, ORTRXNUM, ORTRXSRC.


-- GL activity for a specific account & month
SELECT TRXDATE, JRNENTRY, ORTRXSRC, ORTRXNUM, DEBITAMT, CRDTAMT
FROM TWO..GL20000
WHERE ACTNUMST = '000-1100-00'
  AND TRXDATE BETWEEN '2025-08-01' AND '2025-08-31'
ORDER BY TRXDATE, JRNENTRY;

Join tip: ORTRXNUM and ORTRXSRC help trace back to subledgers (PM/RM/SOP).

3) GL30000 (GL History)

Why it matters: Everything after year-end close lives here.

Shape: Mirrors GL20000; swap table name in queries.


-- Prior year GL totals by account
SELECT ACTNUMST, SUM(DEBITAMT) AS DR, SUM(CRDTAMT) AS CR
FROM TWO..GL30000
GROUP BY ACTNUMST
ORDER BY ACTNUMST;

4) PM20000 (Payables Open)

Why it matters: Vendor transactions waiting to be paid or fully applied.

Key columns: VENDORID, VCHRNMBR, DOCNUMBR, DOCDATE, CURTRXAM, DOCAMNT, PONUMBER.


-- Vendor aging snapshot (open)
SELECT VENDORID,
       SUM(CASE WHEN DOCDATE >= DATEADD(DAY,-30,GETDATE()) THEN CURTRXAM ELSE 0 END) AS [0-30],
       SUM(CASE WHEN DOCDATE <  DATEADD(DAY,-30,GETDATE()) 
                 AND DOCDATE >= DATEADD(DAY,-60,GETDATE()) THEN CURTRXAM ELSE 0 END) AS [31-60],
       SUM(CASE WHEN DOCDATE <  DATEADD(DAY,-60,GETDATE()) THEN CURTRXAM ELSE 0 END) AS [60+]
FROM TWO..PM20000
GROUP BY VENDORID
ORDER BY VENDORID;

Join tip: Link to GL via ORTRXNUM/VCHRNMBR when tracing postings.

5) PM30200 (Payables History)

Why it matters: Where payments/applies end up after close.


-- Last 20 vendor invoices paid this quarter
SELECT TOP 20 VENDORID, VCHRNMBR, DOCDATE, DOCAMNT
FROM TWO..PM30200
WHERE DOCDATE >= '2025-07-01'
ORDER BY DOCDATE DESC;

6) RM20101 (Receivables Open)

Why it matters: Outstanding AR by customer; perfect for credit control.

Key columns: CUSTNMBR, DOCNUMBR, DOCDATE, CURTRXAM, DOCAMNT.


-- Customers over credit limit (open)
SELECT C.CUSTNMBR, C.CUSTNAME, C.CRLMTAMT,
       SUM(R.CURTRXAM) AS OUTSTANDING
FROM TWO..RM00101 C               -- Customer master
JOIN TWO..RM20101 R ON R.CUSTNMBR = C.CUSTNMBR
GROUP BY C.CUSTNMBR, C.CUSTNAME, C.CRLMTAMT
HAVING SUM(R.CURTRXAM) > C.CRLMTAMT
ORDER BY OUTSTANDING DESC;

7) RM30101 (Receivables History)

Why it matters: Posted/closed AR for historical sales and collections analysis.


-- Monthly AR sales totals (history)
SELECT FORMAT(DOCDATE,'yyyy-MM') AS Period,
       SUM(CASE WHEN RMDTYPAL IN (1,3) THEN DOCAMNT ELSE 0 END) AS Sales
FROM TWO..RM30101
GROUP BY FORMAT(DOCDATE,'yyyy-MM')
ORDER BY Period;

(Common RM types: 1=Sales/Invoice, 3=Debit Memo, 4=Credit Memo, 5=Return, 6=Payment.)

8) SOP10100 (SOP Work Headers)

Why it matters: Where active SOP documents live (quotes/orders/invoices before post).

Key columns: SOPTYPE, SOPNUMBE, CUSTNMBR, DOCDATE, SUBTOTAL, USER2ENT.


-- Open Orders not yet transferred/invoiced
SELECT SOPNUMBE, CUSTNMBR, DOCDATE, SUBTOTAL
FROM TWO..SOP10100
WHERE SOPTYPE = 2           -- 2 = Order
  AND VOIDSTTS = 0
ORDER BY DOCDATE DESC;

Join tip: Pair with SOP10200 (line items) using SOPTYPE + SOPNUMBE.

9) SOP30200 (SOP History Headers)

Why it matters: Auditable record of posted/voided SOP docs.


-- Posted invoices last 7 days with customer
SELECT H.SOPNUMBE, H.DOCDATE, H.SUBTOTAL, H.CUSTNMBR, C.CUSTNAME
FROM TWO..SOP30200 H
LEFT JOIN TWO..RM00101 C ON C.CUSTNMBR = H.CUSTNMBR
WHERE H.SOPTYPE = 3         -- 3 = Invoice
  AND H.DOCDATE >= DATEADD(DAY,-7,CONVERT(date,GETDATE()))
ORDER BY H.DOCDATE DESC;

10) IV00101 (Item Master)

Why it matters: The backbone of inventory across SOP/POP/IV.

Key columns: ITEMNMBR, ITEMDESC, ITMSHNAM, ITMGEDSC, UOMSCHDL, ITMCLSCD.


-- Item list with class and UOM
SELECT ITEMNMBR, ITEMDESC, ITMCLSCD, UOMSCHDL, ITMSHNAM
FROM TWO..IV00101
ORDER BY ITEMNMBR;

Join tip: For quantities & costs, look to IV00102 (UOM detail), IV10200 (IV trx work),
IV30300/IV30200 (IV history), and site/item quantities in IV00112.

Common Joins Cheat Sheet

PM → GL (open): PM20000.VCHRNMBR = GL20000.ORTRXNUM and inspect GL20000.ORTRXSRC (often “PMTRX”, “PMFIN”).
RM → GL (open): RM20101.DOCNUMBR = GL20000.ORTRXNUM (or use DOCNUMBR/ORTRXSRC like “RMTRX”).
SOP Header ↔ Lines (work/history): (SOPTYPE, SOPNUMBE) in SOP10100/SOP10200 and SOP30200/SOP30300.
Customer/Vendor masters: RM00101.CUSTNMBR ↔ RM*, PM00200.VENDORID ↔ PM*.
Items: IV00101.ITEMNMBR ↔ SOP10200.ITEMNMBR / POP10110.ITEMNMBR / IV*.

Safety & Performance Tips

Use a read-only SQL login for reporting.
Never run UPDATE/DELETE in production; if you must, use a test company first.
Add (NOLOCK) only if you truly understand dirty-read implications.
Index-friendly filters: date ranges on TRXDATE/DOCDATE, equality on keys, and TOP (N) for investigations.
Capture ORTRXSRC & ORTRXNUM in reports—they’re gold for audit trails.

Wrap-Up

Memorizing these ten tables—and a handful of joins—covers 80% of day-to-day questions from finance, AR/AP, and operations. Keep this post bookmarked, and consider building a shared SQL snippets library so your team solves repeat questions in seconds.

Search This Blog

Dynamics GP