Microsoft DynamicsFree generatorPDF, Excel, Word exportMicrosoft Dynamics GP

Dynamics GP Open Orders, AR and AP SQL Query Generator

Migration teams repeatedly need the same three extracts from Dynamics GP: open sales documents, open receivables and open payables. This generator fills in the company database, date cutoff and document type and gives you plain SELECT statements to run.

Fill in

GP company database

Document date cutoff

SOP type

2 = Order, 3 = Invoice, 4 = Return, 5 = Back order in GP SOPTYPE.

Generated code

Download:

Open sales documents (SOP)

SELECT SOPTYPE, SOPNUMBE, CUSTNMBR, CUSTNAME, DOCDATE, DOCAMNT
FROM TWO.dbo.SOP10100
WHERE SOPTYPE = 2
  AND VOIDSTTS = 0
  AND DOCDATE <= '2026-12-31'
ORDER BY DOCDATE, SOPNUMBE;

Open receivables (RM)

SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, DOCDATE, DOCAMNT, CURTRXAM
FROM TWO.dbo.RM20101
WHERE CURTRXAM <> 0
  AND VOIDSTTS = 0
  AND DOCDATE <= '2026-12-31'
ORDER BY CUSTNMBR, DOCDATE;

Open payables (PM)

SELECT VENDORID, DOCNUMBR, DOCTYPE, DOCDATE, DOCAMNT, CURTRXAM
FROM TWO.dbo.PM20000
WHERE CURTRXAM <> 0
  AND VOIDED = 0
  AND DOCDATE <= '2026-12-31'
ORDER BY VENDORID, DOCDATE;

Read-only SELECT statements. Test on a copy of the company database and verify column names and statuses against your GP version. Documents in work or history tables (for example SOP30200, RM30101, PM30200) are not included.

GP extract library

Optional: ask about a set of tested GP extract and reconciliation queries.

Optional. The tool and every export work without this. No spam.

How to use it

Enter the SQL database name of the GP company, not its display name; the database name is the one shown in the company list or in SQL Server Management Studio. Pick the SOP type and cutoff date, then copy the query. Run it on a restored copy, or with a read-only login, to keep production safe.

Use a read-only SQL login
Prefer a restored copy
Match totals with GP reports
Save results with the run timestamp

What the tables hold

SOP10100 is the sales document header work table, RM20101 holds open receivables transactions and PM20000 holds open payables transactions. When documents are fully applied or posted to history they move to history tables, so open items live in these work and open tables. Amounts in CURTRXAM are the current unapplied balance.

Checking your result

Sum CURTRXAM from the RM and PM queries and compare to the control accounts on the trial balance. Differences usually point to unposted batches, multicurrency balances, or documents dated after your cutoff. Do not load extracts into a new system until they reconcile.

Frequently Asked Questions

Is it safe to run these queries on production?

They only read data, but heavy queries can slow a busy server. Run on a restored copy or during a quiet period, and use a read-only login.

Why does the query not show posted history?

Posted and fully applied documents move to history tables. This generator targets open items, which is what most cutovers need.

What does SOPTYPE mean?

It is the document type in sales order processing. In GP, 2 is an order, 3 is an invoice, 4 is a return and 5 is a back order. Confirm in your version before relying on it.

Can I use this for multicurrency?

The amounts are in the document currency in some columns and originating or functional in others. For multicurrency, add the currency ID and originating amounts, and check with your GP DBA.

If you need migration extracts written, tested and reconciled across your whole GP estate, we can help build them.

Talk to an Expert Explore More Free Tools

Related Tools

Microsoft Dynamics

More Microsoft Dynamics tools

See all

Back to All Tools