Upcoming Microsoft Dynamics GP Webinars from ISC Software

ISC Software SolutionsEvery month at ISC Software I present a webinar on Microsoft Dynamics GP and related products. We typically have the next three upcoming monthly webinars I’ll be delivering scheduled.

We run these webinars on a monthly basis, with occasional extra webinars added to the schedule so it is worth checking the Webinar Schedule page every so often.

The upcoming webinars are:

Controls and Security in Microsoft Dynamics GP
In March is Controls and Security in Microsoft Dynamics GP; see how to improve controls and security in Dynamics GP using a mix of standard and third party functionality..

Tue, March 16th, 2021 4:00 PM – 4:45 PM BST

Register Here

Powerful Document Generation for Dynamics GP
In April is Powerful Document Generation for Dynamics GP; watch in this webinar with a live dox42 demo and we will show you how to design attractive document templates in MS Office and integrate data from MS Dynamics GP and many other data sources such as Microsoft 365, SharePoint or SAP.

Tue, April 20th, 2021 4:00 PM – 4:45 PM BST

Register Here

Controls and Security in Microsoft Dynamics GP
In May is Controls and Security in Microsoft Dynamics GP; Discover how Business Intelligence can improve visibility and help you make better, more timely, decisions..

Tue, May 18th, 2021 4:00 PM – 4:45 PM BST

Register Here

azurecurve ClassicPress Plugins: Check Plugin Status

ClassicPress PluginsThis is part of the azurecurve ClassicPress Plugins which introduces the plugins I have available for ClassicPress.

The plugin I am going to cover in this post, is a brand new one written for ClassicPress; Check Plugin Status.

Functionality

This plugin allows you to check the status of plugins on your site:
* Are they in the WordPress Repository.
* Do they use a 3rd party update mechanism such as Update Manager from Code Potent.
* Are they supported on ClassicPress or on WordPress 5+.
* Are they up-to-date.

Exception handling has been built in for Contact Form 7 and Google Sitemap Generator which report a different slug in the API to the slug on the Wordpress Repository.

Download

The plugin can be downloaded via my Development site.

Click to show/hide the azurecurve ClassicPress Plugins Series Index

Simple Audit for Microsoft Dynamics GP: Introduction

Microsoft Dynamics GPThis post is part of a series on creating a simple audit for Microsoft Dynamics GP.

I created a simple audit a while ago when a client had a requiremnet to audit the change of EFT details onvendors, but did not have sufficient budget for a full audit product like the Assure Suite from Fastpath. This is not to say that the Assure Suite is expenseive, but the client in question was a very small client with only two users of Microsoft Dynamics GP.

The requirement for the original user was very simple; an audit on changes to vendor EFT in the single company they had in Dynamics GP.

Many of the audit tools available for Dynamics GP work by creating triggers in the database, and this is the same approach I took. The first step in creating the audit was to create a table to hold the audit table, then to create the triggers on the table to be audited and then to provide a method of reporting for users to use.

Over the course of this series, each of these steps will be covered.

Simple Audit for Microsoft Dynamics GP: Series Index

Microsoft Dynamics GPI created a simple audit a while ago when a client had a requirement to audit the change of EFT details on vendors, but did not have sufficient budget for a full audit product like the Assure Suite from Fastpath. This is not to say that the Assure Suite is expensive, but the client in question was a very small client with only two users of Microsoft Dynamics GP.

In this series of posts, I will step through the process of creating the custom audit, using the vendor EFT one as the example. The index, below, will automatically update as the posts are published, unless you’re reading the post via syndication in which case check the original post.

Simple Audit for Microsoft Dynamics GP
Introduction
Create Table
Create Triggers
Create Triggers for Audit of Customer Items
SQL View for Reporting
Conclusion

Receivables HATB With Aging By Due Date (using GL Posting Date)

Microsoft Dynamics GPToday’s script, like yesterdays, is an unusual one for me to post, as it is not a script which I have written; it is a script which returns the data for a Receivables HATB With Aging By Due Date (using GL Posting). It was written by Sivakumar Venkataramanback in 2011 and came up in conversation recently; the site he posted it to no longer exists and I had to hunt the script out from the wayback machine to retrieve a copy.

The script is below is Siva’s script which I am reposting “as is” as it may be useful to others.

/******************************************************************
Created July 21, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is for the Receivables HATB for aging by due date and
picking transactions using the GL Posting Date.
 
Tables used:
 - RM Open File Debit Copy (RM20101)
 - RM History File (RM30101)
 - RM Apply Open File (RM20201)
 - RM Apply History File (RM30201)
 - Document Type Setup File (RM40401)
 - RM Period Setup (RM40201)
 
 Revision History
 No	 Date		Reason for Change
 1	 12/12/2017	Updated the logic for due date for credits
 ******************************************************************/
 
DECLARE @EMPTYDATE AS DATETIME
DECLARE @ASOFDATE AS DATETIME
 
SET @EMPTYDATE = '1900-01-01'
SET @ASOFDATE = '2017-04-12' 
 
SELECT  W.CUSTNMBR AS CUSTOMERNO ,
        W1.DOCDESCR AS DOCTYPE ,
        W.DOCNUMBR ,
        W.DOCDATE ,
        W.TRXSORCE ,
        W.GLPOSTDT AS POSTINGDATE ,
        W.DUEDATE ,
        W.AGINGBUCKET ,
        W.DOCUMENTAMT ,
        W.CURTRXAMT
FROM    ( SELECT    X.CUSTNMBR ,
                    X.RMDTYPAL ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.GLPOSTDT ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            RMPERDSC
                                                     FROM   dbo.RM40201
                                                     ORDER BY RMPEREND DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                RMPERDSC
                                       FROM     dbo.RM40201 AG
                                       WHERE    X.DAYSDUE <= AG.RMPEREND
                                       ORDER BY RMPEREND
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.RMDTYPAL < 6
                         THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
                         ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
                    END AS CURTRXAMT
          FROM      ( SELECT    Z.CUSTNMBR ,
                                Z.RMDTYPAL ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.ORTRXAMT AS DOCUMENTAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     WHEN RMDTYPAL > 6
                                          AND RMDTYPAL <= 9
                                     THEN ISNULL(( SELECT   SUM(Y.APPLDAMT)
                                                   FROM     ( SELECT    CUSTNMBR ,
                                                                        GLPOSTDT ,
                                                                        APPTOAMT AS APPLDAMT ,
                                                                        APFRDCNM ,
                                                                        APFRDCTY ,
                                                                        APTODCNM ,
                                                                        APTODCTY ,
                                                                        ApplyToGLPostDate
                                                              FROM      dbo.RM20201
                                                              WHERE     POSTED = 1
                                                                        AND APTODCTY <> 6
                                                              UNION
                                                              SELECT    CUSTNMBR ,
                                                                        GLPOSTDT ,
                                                                        APPTOAMT ,
                                                                        APFRDCNM ,
                                                                        APFRDCTY ,
                                                                        APTODCNM ,
                                                                        APTODCTY ,
                                                                        ApplyToGLPostDate
                                                              FROM      dbo.RM30201
                                                              WHERE     APTODCTY <> 6
                                                            ) Y
                                                   WHERE    Y.GLPOSTDT <= @ASOFDATE
                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                 ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        GLPOSTDT ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        GLPOSTDT ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.GLPOSTDT ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DUEDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM20101
                                  UNION
                                  SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM30101
                                ) Z
                      WHERE     Z.GLPOSTDT <= @ASOFDATE
                                AND Z.VOIDED = 0
                                AND Z.RMDTYPAL <> 6
                    ) X
          UNION ALL
          SELECT    X.CUSTNMBR ,
                    X.RMDTYPAL ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.PSTGDATE ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            RMPERDSC
                                                     FROM   dbo.RM40201
                                                     ORDER BY RMPEREND DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                RMPERDSC
                                       FROM     dbo.RM40201 AG
                                       WHERE    X.DAYSDUE <= AG.RMPEREND
                                       ORDER BY RMPEREND
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.RMDTYPAL < 6
                         THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
                         ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
                    END AS CURTRXAMT
          FROM      ( SELECT    Z.CUSTNMBR ,
                                Z.RMDTYPAL ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.ORTRXAMT AS DOCUMENTAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     WHEN RMDTYPAL > 6
                                          AND RMDTYPAL <= 9
                                     THEN ISNULL(( SELECT   SUM(Y.APPLDAMT)
                                                   FROM     ( SELECT    CUSTNMBR ,
                                                                        GLPOSTDT ,
                                                                        APPTOAMT AS APPLDAMT ,
                                                                        APFRDCNM ,
                                                                        APFRDCTY ,
                                                                        APTODCNM ,
                                                                        APTODCTY ,
                                                                        ApplyToGLPostDate
                                                              FROM      dbo.RM20201
                                                              WHERE     POSTED = 1
                                                                        AND APTODCTY <> 6
                                                              UNION
                                                              SELECT    CUSTNMBR ,
                                                                        GLPOSTDT ,
                                                                        APPTOAMT ,
                                                                        APFRDCNM ,
                                                                        APFRDCTY ,
                                                                        APTODCNM ,
                                                                        APTODCTY ,
                                                                        ApplyToGLPostDate
                                                              FROM      dbo.RM30201
                                                              WHERE     APTODCTY <> 6
                                                            ) Y
                                                   WHERE    Y.GLPOSTDT <= @ASOFDATE
                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                 ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        GLPOSTDT ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        GLPOSTDT ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.PSTGDATE ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DUEDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT AS PSTGDATE ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM20101
                                  UNION
                                  SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM30101
                                ) Z
                      WHERE     Z.PSTGDATE <= @ASOFDATE
                                AND Z.VOIDED = 1
                                AND Z.RMDTYPAL <> 6
                                AND Z.VOIDPDATE > @ASOFDATE
                    ) X
        ) W
        INNER JOIN dbo.RM40401 W1 ON W.RMDTYPAL = W1.RMDTYPAL
WHERE   W.CURTRXAMT <> 0

Receivables HATB With Aging By Due Date (using Document Date)

Microsoft Dynamics GPToday’s script is an unusual one for me to post, as it is not a script which I have written; it is a script which returns the data for a Receivables HATB With Aging By Due Date (using Document Date). It was written by Sivakumar Venkataraman back in 2011 and came up in conversation recently; the site he posted it to no longer exists and I had to hunt the script out from the wayback machine to retrieve a copy.

The script is below is Siva’s script which I am reposting “as is” as it may be useful to others.

/******************************************************************
Created July 21, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is for the Receivables HATB for aging by due date and
picking transactions using the Document Date.
 
Tables used:
 - RM Open File Debit Copy (RM20101)
 - RM History File (RM30101)
 - RM Apply Open File (RM20201)
 - RM Apply History File (RM30201)
 - Document Type Setup File (RM40401)
 - RM Period Setup (RM40201)
 
Revision History
 No	 Date		Reason for Change
 1	 12/12/2017	Updated the logic for due date for credits
 ******************************************************************/
 
DECLARE @EMPTYDATE AS DATETIME
DECLARE @ASOFDATE AS DATETIME
 
SET @EMPTYDATE = '1900-01-01'
SET @ASOFDATE = '2017-04-12' 
 
SELECT  W.CUSTNMBR AS CUSTOMERNO ,
        W1.DOCDESCR AS DOCTYPE ,
        W.DOCNUMBR ,
        W.DOCDATE ,
        W.TRXSORCE ,
        W.GLPOSTDT AS POSTINGDATE ,
        W.DUEDATE ,
        W.AGINGBUCKET ,
        W.DOCUMENTAMT ,
        W.CURTRXAMT
FROM    ( SELECT    X.CUSTNMBR ,
                    X.RMDTYPAL ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.GLPOSTDT ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            RMPERDSC
                                                     FROM   dbo.RM40201
                                                     ORDER BY RMPEREND DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                RMPERDSC
                                       FROM     dbo.RM40201 AG
                                       WHERE    X.DAYSDUE <= AG.RMPEREND
                                       ORDER BY RMPEREND
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.RMDTYPAL < 6
                         THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
                         ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
                    END AS CURTRXAMT
          FROM      ( SELECT    Z.CUSTNMBR ,
                                Z.RMDTYPAL ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.ORTRXAMT AS DOCUMENTAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     WHEN RMDTYPAL > 6
                                          AND RMDTYPAL <= 9 THEN ISNULL(( SELECT    SUM(Y.APPLDAMT)
                                                                          FROM      ( SELECT    CUSTNMBR ,
                                                                                                DATE1 ,
                                                                                                APPTOAMT AS APPLDAMT ,
                                                                                                APFRDCNM ,
                                                                                                APFRDCTY ,
                                                                                                APTODCNM ,
                                                                                                APTODCTY ,
                                                                                                APTODCDT
                                                                                      FROM      dbo.RM20201
                                                                                      WHERE     POSTED = 1
                                                                                                AND APTODCTY <> 6
                                                                                      UNION
                                                                                      SELECT    CUSTNMBR ,
                                                                                                DATE1 ,
                                                                                                APPTOAMT ,
                                                                                                APFRDCNM ,
                                                                                                APFRDCTY ,
                                                                                                APTODCNM ,
                                                                                                APTODCTY ,
                                                                                                APTODCDT
                                                                                      FROM      dbo.RM30201
                                                                                      WHERE     APTODCTY <> 6
                                                                                    ) Y
                                                                          WHERE     Y.DATE1 <= @ASOFDATE
                                                                                    AND Y.APTODCDT <= @ASOFDATE
                                                                                    AND Y.APTODCDT <> @EMPTYDATE
                                                                                    AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                                    AND Y.APFRDCNM = Z.DOCNUMBR
                                                                                    AND Y.APFRDCTY = Z.RMDTYPAL
                                                                        ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        DATE1 ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        DATE1 ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APTODCDT <= @ASOFDATE
                                                                            AND Y.APTODCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.GLPOSTDT ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DUEDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM20101
                                  UNION
                                  SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM30101
                                ) Z
                      WHERE     Z.DOCDATE <= @ASOFDATE
                                AND Z.VOIDED = 0
                                AND Z.RMDTYPAL <> 6
                    ) X
          UNION ALL
          SELECT    X.CUSTNMBR ,
                    X.RMDTYPAL ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.PSTGDATE ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            RMPERDSC
                                                     FROM   dbo.RM40201
                                                     ORDER BY RMPEREND DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                RMPERDSC
                                       FROM     dbo.RM40201 AG
                                       WHERE    X.DAYSDUE <= AG.RMPEREND
                                       ORDER BY RMPEREND
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.RMDTYPAL < 6
                         THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
                         ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
                    END AS CURTRXAMT
          FROM      ( SELECT    Z.CUSTNMBR ,
                                Z.RMDTYPAL ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.ORTRXAMT AS DOCUMENTAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     WHEN RMDTYPAL > 6
                                          AND RMDTYPAL <= 9 THEN ISNULL(( SELECT    SUM(Y.APPLDAMT)
                                                                          FROM      ( SELECT    CUSTNMBR ,
                                                                                                DATE1 ,
                                                                                                APPTOAMT AS APPLDAMT ,
                                                                                                APFRDCNM ,
                                                                                                APFRDCTY ,
                                                                                                APTODCNM ,
                                                                                                APTODCTY ,
                                                                                                APTODCDT
                                                                                      FROM      dbo.RM20201
                                                                                      WHERE     POSTED = 1
                                                                                                AND APTODCTY <> 6
                                                                                      UNION
                                                                                      SELECT    CUSTNMBR ,
                                                                                                DATE1 ,
                                                                                                APPTOAMT ,
                                                                                                APFRDCNM ,
                                                                                                APFRDCTY ,
                                                                                                APTODCNM ,
                                                                                                APTODCTY ,
                                                                                                APTODCDT
                                                                                      FROM      dbo.RM30201
                                                                                      WHERE     APTODCTY <> 6
                                                                                    ) Y
                                                                          WHERE     Y.DATE1 <= @ASOFDATE
                                                                                    AND Y.APTODCDT <= @ASOFDATE
                                                                                    AND Y.APTODCDT <> @EMPTYDATE
                                                                                    AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                                    AND Y.APFRDCNM = Z.DOCNUMBR
                                                                                    AND Y.APFRDCTY = Z.RMDTYPAL
                                                                        ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        DATE1 ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        DATE1 ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APTODCDT <= @ASOFDATE
                                                                            AND Y.APTODCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.PSTGDATE ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DUEDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT AS PSTGDATE ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM20101
                                  UNION
                                  SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM30101
                                ) Z
                      WHERE     Z.DOCDATE <= @ASOFDATE
                                AND Z.VOIDED = 1
                                AND Z.RMDTYPAL <> 6
                                AND Z.VOIDPDATE > @ASOFDATE
                    ) X
        ) W
        INNER JOIN dbo.RM40401 W1 ON W.RMDTYPAL = W1.RMDTYPAL
WHERE   W.CURTRXAMT <> 0

Recent ISC Software Webinar: Automation in Microsoft Dynamics GP

ISC Software SolutionsIn our most recent webinar, we took a look at Automation in Microsoft Dynamics GP. In this webinar, we covered how automation can be used in Microsoft Dynamics GP to improve efficiencies and accuracy of data. If you want to catch up on this, or any other, webinar, you can do so here.

In this blog post, I am going to recap the webinar and cover the highlights of how automation can be used in Microsoft Dynamics GP to improve efficiencies and improve data accuracy:

  1. Introduction
  2. Integration
  3. Scanning
  4. Approval
  5. Posting
  6. Reporting
  7. Conclusion

Introduction ^

Where possible in this webinar I highlighted standard, or Microsoft supplied, features or additional products where they are available. However, in many cases the standard functionality does not allow for full automation. This is an intentional design choice made when Microsoft Dynamics GP was first created back in the md-90s. The company who created Great Plains, the original name of Dynamics GP, was intended from the very beginning to be extensible with the intention that there be a thriving third-party marketplace for add-ons.

This is the current situation; the core Dynamics GP system has strong core financials and distribution modules, but wider functionality is provided by third party (Independent Software Vendors (ISVs) who have a variety of add-ons and complimentary products which provide the functionality required or automating processes. In each of the areas, there are usually a number of products available from several vendors, but I have selected one in each area. usually an add-on which I have used with several clients across a number of years and which has received positive reviews.

Before implementing one of the solutions, I’d recommend reviewing the functionality it includes, the functionality of competing products and making your own decision about which will best fit your requirements.

Continue reading “Recent ISC Software Webinar: Automation in Microsoft Dynamics GP”