🗂 Understanding Dynamics GP Table Structures

 

  • 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 
  ON PM.VCHRNMBR = GL.ORTRXNUM
WHERE PM.DOCDATE BETWEEN '2025-01-01' AND '2025-03-31';

👉 Helps validate whether posted payables are hitting the correct GL accounts.

3. Outstanding SOP Orders

SELECT SOP.SOPNUMBE, SOP.SOPTYPE, SOP.CUSTNMBR, SOP.DOCDATE, SOP.SUBTOTAL
FROM TWO..SOP10100 SOP
WHERE SOP.SOPTYPE = 2 -- Orders
  AND SOP.VOIDSTTS = 0
ORDER BY SOP.DOCDATE DESC;

👉 Quickly shows all open Sales Orders and their amounts.

4. Vendor Balance Summary

SELECT VENDORID, SUM(CURTRXAM) AS OUTSTANDING_BALANCE
FROM TWO..PM20000
GROUP BY VENDORID
ORDER BY OUTSTANDING_BALANCE DESC;

👉 Simple, fast way to see who you owe money to.

5. Customers Over Credit Limit

SELECT CUST.CUSTNMBR, CUST.CUSTNAME, CUST.CRLMTAMT, SUM(RM.CURTRXAM) AS OUTSTANDING
FROM TWO..RM00101 CUST
JOIN TWO..RM20101 RM ON CUST.CUSTNMBR = RM.CUSTNMBR
GROUP BY CUST.CUSTNMBR, CUST.CUSTNAME, CUST.CRLMTAMT
HAVING SUM(RM.CURTRXAM) > CUST.CRLMTAMT;

👉 A real-world credit control report that GP doesn’t provide out-of-the-box.

⚠️ Best Practices

  • Always query with SELECT only — avoid UPDATE or DELETE in production.

  • Use a read-only SQL account for reporting connections.

  • Document your queries — future admins will thank you.

  • Test in a test company first if you’re writing more complex joins.

🎯 Final Thoughts

Dynamics GP’s SQL backend can feel intimidating, but once you learn the table naming patterns and safe shortcuts, you unlock a world of faster reconciliation, custom reporting, and smarter audits.

Instead of struggling with manual exports or missing SmartLists, you can build direct, reliable reports that make finance and operations teams look like heroes.

Comments

Post a Comment

Popular posts from this blog

Creating Macro in Dynamics GP

Image
Cre a ting M a c r o in M i c r o s oft Dyn a mics GP 1. O p en wh i ch s c r e e n y o u w o u l d t o i n s e r t d at a and don ’ t i n s e r t a n y c h a r [Exa m p l e : H R - - - > P os i t i o n] 2.  St art R e c o r d i n g t h e M a c r o 3.  Na m e your M a c r o 4.  St art i ns e r t i o n o f t h e d a t a 5-  Sto p M a c ro Re c o r d ing 2-  F i l l t h e M a cro fi l e wi t h al l r e q ui r e d d at a f o r u p load . a-  O p en   Macro   c r e a t ed   fi l e   wi t h   Mic r oso ft   W ord b- F r o m M a ilin g Tab --- S e l e c t R e c i p ien t s --> Us e Exi st i n g L i s t c- S el e c t D at a So ur c e [ P r e - p r e p a red F i l e . X LS c o n t a in s a l l r e q u i r ed D a t a f o r each r e c o rd] d- if the excel sheet contains multiple sheets,  s elect wh i ch t ab ...
Read more

Delete a Sales order processing document after being printed

You cant see the delete button anymore after printing the batch or the transaction even though it is just saved and not yet posted, so this is a simple query to switch the printing status of the batch or the document to initial status before printing. update SOP10100 set TIMESPRT = 0  where SOPNUMBE='<insert the SOP document>' After executing the query the delete button will appear in the action drop down list.
Read more

Microsoft Dynamics GP 18.5 release and what's next?

  In  #Dynamics  GP general session at  #MySummitNA , Microsoft’s Terry Heley detailed these new features in October’s GP 18.5 release: + account category lookup options + warnings and tools to manage inactive vendor address records + ability to print 1099-NEC forms + new cash receipts option in transaction entries + workflow option for "no approvals needed" In addition,  #microsoft  VP Michael Morton said 18.5 will be the last release with major new features, but the software will continue to receive regulatory/tax and security updates and the company remains committed to GP. New features and investments will primarily target  #businesscentral .
Read more