# Dynamics GP Open Orders, AR and AP SQL Query Generator

> Source: https://netray.co/tools/gp-open-transactions-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Build read-only SQL for open SOP orders, open RM receivables and open PM payables in a GP company database for migration extracts.

## Category

microsoft-dynamics

## Erp

Microsoft Dynamics GP

## Audience

- GP administrator
- Database administrator
- Data migration lead

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### GP company database

**Key:** db

**Type:** text

**Default:** TWO

#### Document date cutoff

**Key:** cutoff

**Type:** date

**Default:** 2026-12-31

#### SOP type

**Key:** soptype

**Type:** select

**Options:**

- 2
- 3
- 4
- 5

**Default:** 2

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

**Blocks:**

#### Open sales documents (SOP)

**Body:** SELECT SOPTYPE, SOPNUMBE, CUSTNMBR, CUSTNAME, DOCDATE, DOCAMNT
FROM {{db}}.dbo.SOP10100
WHERE SOPTYPE = {{soptype}}
  AND VOIDSTTS = 0
  AND DOCDATE <= '{{cutoff}}'
ORDER BY DOCDATE, SOPNUMBE;

#### Open receivables (RM)

**Body:** SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, DOCDATE, DOCAMNT, CURTRXAM
FROM {{db}}.dbo.RM20101
WHERE CURTRXAM <> 0
  AND VOIDSTTS = 0
  AND DOCDATE <= '{{cutoff}}'
ORDER BY CUSTNMBR, DOCDATE;

#### Open payables (PM)

**Body:** SELECT VENDORID, DOCNUMBR, DOCTYPE, DOCDATE, DOCAMNT, CURTRXAM
FROM {{db}}.dbo.PM20000
WHERE CURTRXAM <> 0
  AND VOIDED = 0
  AND DOCDATE <= '{{cutoff}}'
ORDER BY VENDORID, DOCDATE;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use it

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

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

**Faq:**

#### Is it safe to run these queries on production?

**Answer:** 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?

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

#### What does SOPTYPE mean?

**Answer:** 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?

**Answer:** 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.

## Search terms

gp sql open sales orders, dynamics gp open ar query sql, dynamics gp open ap query rm20101 pm20000, sop10100 query, gp migration extract sql, dynamics gp sql queries

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json