Dynamics GP

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

  Company Data : Each company has its own database (e.g., TWO , Fabrikam , or your company code). Common Table Prefixes : GL → General Ledger PM → Payables Management RM → Receivables Management SOP → Sales Order Processing POP → Purchase Order Processing Series Breakdown : 10000 → Financial 20000 → Sales 30000 → Purchasing 40000 → Inventory Knowing the right prefix saves hours when searching for tables in SQL. 🛠️ Useful Queries You Can Use Today Here are some read-only queries (safe for reporting, won’t update data). Replace TWO with your company database. 1. Find Unposted Batches SELECT BACHNUMB, SERIES, BACHNAME, USERID, BCHSOURC FROM TWO..SY00500 WHERE BCHSTTUS <> 0 ORDER BY SERIES, BACHNUMB; 👉 Great for identifying stuck batches across modules. 2. Reconcile Payables Transactions to GL SELECT PM.VCHRNMBR, PM.DOCDATE, PM.DOCAMNT, GL.DEBITAMT, GL.CRDTAMNT FROM TWO..PM20000 PM LEFT JOIN TWO..GL20000 GL...