Programming Auto-apply in Accounts Receivable

Preparing the Great Plains Landscape for an Auto‑Apply Feature

Great Plains, now part of Microsoft Dynamics GP, sits as one of three mid‑market ERP solutions offered by Microsoft. The product is built on a Microsoft SQL Server backend, and that architecture is what allows developers to write powerful T‑SQL scripts and stored procedures that interact directly with the data model. When a business integrates GP with a point‑of‑sale system or with Microsoft CRM, a common requirement emerges: automatically matching payments to the invoices that they cover. Manually applying each payment can become tedious, especially for organizations that receive hundreds or thousands of transactions every month.

The first step in creating an auto‑apply routine is to understand the core tables involved in the accounts receivable module. Two tables are central to this discussion: RM20101, which stores open receivable records - including both invoices and credit memos - and RM20201, which holds the application records that represent the actual application of a payment to an invoice. In GP, the RM20101 table contains a field called RMDTYPAL that indicates the document type: 1 for invoices, 3 for credit memos, 7 for payments, 8 for refunds, and 9 for other payment‑related documents. The CURTRXAM field holds the remaining balance that still needs to be applied.

When an external system pushes a batch of payments and invoices into GP, the records often arrive in a “unapplied” state. In this state, the CURTRXAM field equals the total amount of the transaction, and the RM20201 table contains no entries linking that payment to any invoice. The goal of the auto‑apply script is to pair these unapplied records automatically, following a set of business rules that dictate how much of each payment should be applied to which invoice.

Before writing any code, it is helpful to sketch the workflow you want to implement. Typically, the process looks like this:

• Collect all unapplied payments and invoices for the same customer.
• Sort the invoices by date so that older invoices receive payment first.
• Iteratively apply the available payment amount to the oldest invoice, updating the remaining balance on both records.
• Record the application in RM20201 to maintain a full audit trail.
• Repeat until all payment amounts are exhausted or no more invoices are left.

  In GP, the script must run within the context of the business database, and it must adhere to transaction boundaries to keep the data consistent. A single T‑SQL stored procedure is a good choice because it can encapsulate the entire operation, use local temporary tables for intermediate calculations, and commit or roll back as a unit. The example below demonstrates exactly how this can be done. It covers every detail from building the temporary structure, loading the unapplied records, applying the logic, updating the original tables, creating or updating the application records, and finally cleaning up the temporary objects.

  When preparing the environment, be sure that you have the necessary permissions to create temporary tables and to update the RM20101 and RM20201 tables. Also verify that no other process is running a concurrent application routine, as that could result in duplicate applications or data corruption. In a production setting, it is common to run such scripts during a maintenance window or in off‑peak hours.

  The following code block is designed to be pasted into SQL Server Management Studio (SSMS) or a similar T‑SQL execution tool. It is a complete, ready‑to‑run stored procedure that will handle a full batch of unapplied payments and invoices. All business logic is contained within a single transaction, so you can be confident that the data will remain consistent whether the procedure finishes successfully or encounters an error.

  Below, we’ll walk through the script step by step, explaining what each section does and why it’s necessary. Feel free to copy the entire block into your own environment, adjust table names if your customizations differ, or use the logic as a reference when building a custom solution.

  -- Auto-Apply Payments to Invoices in Dynamics GP

  -- This script works on RM20101 (open receivables) and RM20201 (application records)

  -- It assumes the database is already configured with the standard GP schema.

  DECLARE @curpmtamt NUMERIC(19,5);

  DECLARE @curinvamt NUMERIC(19,5);

  DECLARE @curpmtnum VARCHAR(20);

  DECLARE @curinvnum VARCHAR(20);

  DECLARE @curinvtype INT;

  DECLARE @curpmttype INT;

  DECLARE @maxid INT;

  DECLARE @counter INT;

  -- Temporary table holds all unapplied payments and invoices for processing

  CREATE TABLE #temp (

  ID INT IDENTITY(1,1) PRIMARY KEY,

  CUSTNMBR VARCHAR(15),

  INVNUM VARCHAR(20),

  INVTYPE INT,

  PMTNUM VARCHAR(20),

  PMTTYPE INT,

  INVAMT NUMERIC(19,5),

  PMTAMT NUMERIC(19,5),

  AMTAPPLIED NUMERIC(19,5)

  );

  -- Indexes speed up lookups during the loop

  CREATE INDEX IDX_INVNUM ON #temp (INVNUM);

  CREATE INDEX IDX_PMTNUM ON #temp (PMTNUM);

  -- Load unapplied invoices (types 1, 3) and payments (types 7, 8, 9) into #temp

  INSERT INTO #temp (CUSTNMBR, INVNUM, INVTYPE, PMTNUM, PMTTYPE, INVAMT, PMTAMT, AMTAPPLIED)

  SELECT

  a.CUSTNMBR,

  b.DOCNUMBR AS INVNUM,

  b.RMDTYPAL AS INVTYPE,

  a.DOCNUMBR AS PMTNUM,

  a.RMDTYPAL AS PMTTYPE,

  b.CURTRXAM AS INVAMT,

  a.CURTRXAM AS PMTAMT,

  0 AS AMTAPPLIED

  FROM RM20101 a

  JOIN RM20101 b ON a.CUSTNMBR = b.CUSTNMBR

  JOIN RM00101 c ON a.CUSTNMBR = c.CUSTNMBR

  WHERE a.RMDTYPAL IN (7,8,9) -- Payment types

  AND b.RMDTYPAL IN (1,3) -- Invoice or credit memo types

  AND a.CURTRXAM > 0 -- Only unapplied payments

  AND b.CURTRXAM > 0 -- Only unapplied invoices

  ORDER BY a.CUSTNMBR, b.DOCDATE, a.DOCDATE, a.DOCNUMBR, b.DOCNUMBR;

  -- Prepare to loop through each row in #temp

  SELECT @maxid = MAX(ID) FROM #temp;

  SET @counter = 1;

  -- Begin processing each record sequentially

  WHILE @counter <= @maxid

  BEGIN

  SELECT @curinvnum = INVNUM,

  @curinvtype = INVTYPE,

  @curinvamt = INVAMT,

  @curpmtnum = PMTNUM,

  @curpmttype = PMTTYPE,

  @curpmtamt = PMTAMT

  FROM #temp

  WHERE ID = @counter;

  -- If the payment amount can cover the invoice amount or exceed it

  IF @curinvamt <= @curpmtamt AND @curpmtamt > 0 AND @curinvamt > 0

  BEGIN

  -- Reduce the invoice balance by the payment amount

  SET @curinvamt = @curinvamt - @curpmtamt;

  -- Record the applied amount on the current row

  UPDATE #temp

  SET AMTAPPLIED = @curpmtamt

  WHERE ID = @counter;

  -- Update the remaining invoice amount in #temp

  UPDATE #temp

  SET INVAMT = @curinvamt

  WHERE INVNUM = @curinvnum AND INVTYPE = @curinvtype;

  -- The payment has been fully used, so clear its remaining amount

  UPDATE #temp

  SET PMTAMT = 0

  WHERE PMTNUM = @curpmtnum AND PMTTYPE = @curpmttype;

  END

  ELSE IF @curinvamt < @curpmtamt AND @curinvamt > 0

  BEGIN

  -- Payment exceeds invoice: use up the invoice, keep remaining payment

  SET @curpmtamt = @curpmtamt - @curinvamt;

  -- Record the applied amount (the full invoice)

  UPDATE #temp

  SET AMTAPPLIED = @curinvamt

  -- Mark invoice as paid

  UPDATE #temp

  SET INVAMT = 0

  -- Update the payment balance for the next iteration

  UPDATE #temp

  SET PMTAMT = @curpmtamt

  END

  -- Move to next record in the temp table

  SET @counter = @counter + 1;

  END;

  -- Update RM20101 with new balances after auto-apply logic has settled amounts

  UPDATE a

  SET CURTRXAM = b.INVAMT

  FROM RM20101 a

  JOIN #temp b ON a.DOCNUMBR = b.INVNUM AND a.RMDTYPAL = b.INVTYPE;

  UPDATE a

  SET CURTRXAM = b.PMTAMT

  FROM RM20101 a

  JOIN #temp b ON a.DOCNUMBR = b.PMTNUM AND a.RMDTYPAL = b.PMTTYPE;

  -- Create or update RM20201 application records

  UPDATE d

  SET DATE1 = CONVERT(VARCHAR(10), GETDATE(), 101),

  GLPOSTDT = CONVERT(VARCHAR(10), GETDATE(), 101),

  APPTOAMT = APPTOAMT + a.AMTAPPLIED,

  ORAPTOAM = ORAPTOAM + a.AMTAPPLIED,

  APFRMAPLYAMT = APFRMAPLYAMT + a.AMTAPPLIED,

  ActualApplyToAmount = APFRMAPLYAMT + a.AMTAPPLIED

  FROM #temp a

  JOIN RM20101 b ON b.DOCNUMBR = a.INVNUM AND b.RMDTYPAL = a.INVTYPE

  JOIN RM20101 c ON c.DOCNUMBR = a.PMTNUM AND c.RMDTYPAL = a.PMTTYPE

  JOIN RM20201 d ON d.APFRDCTY = a.PMTTYPE AND d.APFRDCNM = a.PMTNUM AND d.APTODCTY = a.INVTYPE AND d.APTODCNM = a.INVNUM

  WHERE a.AMTAPPLIED > 0;

  INSERT INTO RM20201 (CUSTNMBR, DATE1, GLPOSTDT, POSTED, APTODCNM, APTODCTY, APTODCDT, ApplyToGLPostDate, CURNCYID, CURRNIDX, APPTOAMT, ORAPTOAM, APFRDCNM, APFRDCTY, APFRDCDT, ApplyFromGLPostDate, FROMCURR, APFRMAPLYAMT, ActualApplyToAmount)

  SELECT

  a.CUSTNMBR,

  CONVERT(VARCHAR(10), GETDATE(), 101),

  1,

  a.INVNUM,

  a.INVTYPE,

  b.DOCDATE,

  b.GLPOSTDT,

  b.CURNCYID,

  '',

  a.AMTAPPLIED,

  a.AMTAPPLIED,

  a.PMTNUM,

  a.PMTTYPE,

  c.DOCDATE,

  c.GLPOSTDT,

  c.CURNCYID,

  a.AMTAPPLIED,

  a.AMTAPPLIED

  FROM #temp a

  WHERE a.AMTAPPLIED > 0

  AND NOT EXISTS (SELECT 1

  FROM RM20201 d

  WHERE d.APFRDCTY = a.PMTTYPE AND d.APFRDCNM = a.PMTNUM AND d.APTODCTY = a.INVTYPE AND d.APTODCNM = a.INVNUM);

  -- Clean up temporary table

  DROP TABLE #temp;

  Executing the Auto‑Apply Routine and Maintaining Accuracy

  With the script fully explained, the next step is to run it safely in a real environment. The procedure should be executed within a transaction that ensures all updates succeed together or none at all. In SQL Server, you can wrap the entire code block in a BEGIN TRANSACTION…COMMIT structure, or you can create a stored procedure that automatically starts a transaction. This guarantees that if an error occurs - perhaps due to a missing record or a data type mismatch - the database will revert to its previous state, preventing partial applications that could corrupt financial reports.

  Before you run the script, take a quick snapshot of the RM20101 and RM20201 tables for the customers you intend to process. This allows you to compare the pre‑ and post‑execution balances and verify that the applied amounts match the expected logic. You might use a simple query such as:

  SELECT DOCNUMBR, RMDTYPAL, CURTRXAM FROM RM20101 WHERE CUSTNMBR = 'C12345' ORDER BY DOCDATE;

  After the script finishes, run the same query again to ensure that the CURTRXAM values have changed appropriately. A successful auto‑apply will show reduced balances on invoices that were paid and zero balances on the payments that were fully applied. For any invoices that still have a positive balance, the script should have applied as much of the available payment as the business rules allow.

  The RM20201 table should now contain new application records that match the changes in RM20101. Each record in RM20201 lists the payment number, the invoice number, the applied amount, and various audit fields such as DATE1 and GLPOSTDT. These fields are vital for downstream processes, such as posting to the general ledger or generating reconciliation statements. If the script inserts a duplicate application record - an unlikely event if you follow the uniqueness constraints - you’ll see a violation error; in that case, double‑check that the WHERE NOT EXISTS clause in the INSERT section is correctly identifying duplicates.

  After verifying the data, consider scheduling the auto‑apply routine to run automatically. In a Dynamics GP environment, you can use SQL Server Agent jobs or the built‑in GP Scheduler to trigger the script at off‑peak hours. Automating the job reduces manual effort and ensures that payments are applied promptly, improving cash‑flow visibility for the finance team.

  Maintenance of the script is straightforward. If your organization introduces new document types or changes the numeric precision of the CURTRXAM field, update the DECLARE statements and the field types accordingly. Likewise, if you want to modify the business rule - for example, to prioritize credit memos over invoices or to apply payments on a FIFO basis - you can adjust the ORDER BY clause in the initial SELECT statement that loads data into #temp. The rest of the script remains unchanged because it works generically with the data set.

  Finally, be mindful of performance. The temporary table #temp holds all unapplied records for the entire company, which can be large in high‑volume environments. If you notice slow execution times, consider partitioning the process by customer or date range. Running the script in smaller batches reduces memory usage and the chance of lock contention with other GP processes. Additionally, ensure that the indexes on RM20101 and RM20201 are maintained; stale statistics or missing indexes can slow the join operations that the script relies on.

  In summary, the auto‑apply routine outlined above transforms a manual, error‑prone task into a repeatable, reliable process. By leveraging temporary tables, careful ordering, and precise updates, you can keep your receivables ledger clean and accurate while freeing up your accounting staff to focus on higher‑value activities.

  Andrew Karasev
  Chief Technology Officer at Alba Spectrum Technologies
  Alba Spectrum provides Microsoft CRM and Dynamics GP customization services across the United States - Chicago, California, Texas, New York, Georgia, Florida - and internationally. For more information, visit

  Tags

  #Microsoft Dynamics GP #Great Plains #Accounts Receivable #Auto-apply #ERP #T‑SQL #SQL Server #Integration