# Dynamics GP AR Aging SQL Query Generator

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

## Short answer

Generate a read-only SQL aging report from GP open receivables, with your own as-of date and bucket sizes.

## Category

microsoft-dynamics

## Erp

Microsoft Dynamics GP

## Audience

- GP report writer
- Controller
- Migration consultant

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Company database

**Key:** company_db

**Type:** text

**Default:** TWO

**Help:** The GP company database name (INTERID), for example TWO for the sample company.

#### Aging as-of date

**Key:** as_of

**Type:** date

**Default:** 2026-09-30

#### Bucket 1 upper days

**Key:** b1

**Type:** number

**Default:** 30

#### Bucket 2 upper days

**Key:** b2

**Type:** number

**Default:** 60

#### Bucket 3 upper days

**Key:** b3

**Type:** number

**Default:** 90

#### Extra customer filter

**Key:** extra_filter

**Type:** text

**Default:** AND c.CUSTCLAS = 'USA-ILMO-T1'

**Help:** Optional. Clear the field to include all customer classes.

**Blocks:**

#### Aging by customer

**Body:** USE [{{company_db}}];
GO

WITH open_items AS (
    SELECT
        o.CUSTNMBR,
        o.DUEDATE,
        CASE WHEN o.RMDTYPAL >= 7 THEN -o.CURTRXAM ELSE o.CURTRXAM END AS signed_amt,
        DATEDIFF(day, o.DUEDATE, '{{as_of}}') AS days_past_due
    FROM dbo.RM20101 o
    WHERE o.VOIDSTTS = 0
      AND o.CURTRXAM <> 0
      AND o.DOCDATE <= '{{as_of}}'
)
SELECT
    RTRIM(c.CUSTNMBR) AS customer_id,
    RTRIM(c.CUSTNAME) AS customer_name,
    SUM(CASE WHEN i.days_past_due <= 0 THEN i.signed_amt ELSE 0 END) AS current_not_due,
    SUM(CASE WHEN i.days_past_due BETWEEN 1 AND {{b1}} THEN i.signed_amt ELSE 0 END) AS days_1_to_{{b1}},
    SUM(CASE WHEN i.days_past_due BETWEEN {{b1}} + 1 AND {{b2}} THEN i.signed_amt ELSE 0 END) AS days_{{b1}}_to_{{b2}},
    SUM(CASE WHEN i.days_past_due BETWEEN {{b2}} + 1 AND {{b3}} THEN i.signed_amt ELSE 0 END) AS days_{{b2}}_to_{{b3}},
    SUM(CASE WHEN i.days_past_due > {{b3}} THEN i.signed_amt ELSE 0 END) AS over_{{b3}},
    SUM(i.signed_amt) AS total_open
FROM open_items i
JOIN dbo.RM00101 c ON c.CUSTNMBR = i.CUSTNMBR
WHERE 1 = 1
  {{extra_filter}}
GROUP BY c.CUSTNMBR, c.CUSTNAME
HAVING SUM(i.signed_amt) <> 0
ORDER BY total_open DESC;

**Notes:** Read-only SELECT. In RM20101, RMDTYPAL values 7 and above are credit-type documents (credit memos, returns, payments), which is why they are negated; confirm against your RM setup. CURTRXAM is the current balance in the originating currency. This query reflects the balance today, not a true historical as-of balance, because applications after the as-of date are not unwound. Test on a copy first.

## Content

**Intro:** Receivables aging is the report every GP controller runs and every migration must reconcile. This generator writes a clean SQL aging query over RM20101 and RM00101 that you can run in SSMS or put behind a report.

**Sections:**

#### How to use the generator

**Body:** Enter the company database, the as-of date and the three bucket edges. Copy the SQL into SQL Server Management Studio, connect to the GP instance and run it. The optional filter lets you narrow to a customer class. The output has one row per customer with current, three aged buckets, over the last bucket and a total.

**Bullets:**

- Use read-only SQL permissions for the account that runs it
- Compare the grand total to the GP Aged Trial Balance report
- Export the result set to Excel for the month-end pack

#### How the query works

**Body:** It reads open items from RM20101, the receivables open transaction table, excludes voided documents and zero balances, signs credit documents negatively and computes days past due as the difference between the due date and the as-of date. Conditional SUM expressions then place each amount in a bucket. Joining to RM00101 adds the customer name and lets you filter by class.

#### Reconciling against GP

**Body:** Totals should tie to the GP Aged Trial Balance run with the same date type (due date versus document date) and the same options for unapplied credits. Differences usually come from aging by document date instead of due date, foreign currency documents, or history that was removed. Fix the definition first before suspecting data.

#### Common mistakes

**Body:** Do not sum ORTRXAMT, the original amount, when you want the open balance. Do not forget that credit documents carry positive amounts in GP and need signing. If you use multicurrency, add currency columns rather than adding totals across currencies.

**Cta:** If you need aging, cash and exposure reports rebuilt on a successor ERP or a private reporting layer, we can help build and reconcile them against your GP numbers.

**Faq:**

#### Which GP versions does this work with?

**Answer:** RM20101 and RM00101 exist in all modern GP releases, from GP 2010 through current. Column names used here (CUSTNMBR, DUEDATE, CURTRXAM, RMDTYPAL, VOIDSTTS) are long standing. Always test against your own database since customisations can differ.

#### Why do my totals not match the GP report?

**Answer:** Check the aging date basis (due date versus document date), whether the GP report includes unapplied payments and credits, and whether it uses the functional or originating currency. Match those settings, then compare again.

#### Can I run this on production?

**Answer:** It is a SELECT, so it does not change data, but heavy queries can slow users. Run it against a restored copy or in a quiet period, and use a read-only login.

#### How do I add salesperson or territory?

**Answer:** Add the SLPRSNID or TERRITRY columns from RM00101 to the select list and group by clause. Those columns are on the customer master, so no extra joins are needed.

## Search terms

dynamics gp ar aging sql, gp receivables aging query, RM20101 aging, gp customer aging sql report, dynamics gp sql queries, gp open receivables sql

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