Archive for the ‘Bank Reconciliation’ Category

Bank Reconciliation and Voided Checks

Recently, I was addressing a discussion in the forums, where a user was seeing voided checks in the Bank Reconciliation window. The user also mentioned that he never used to see voided checks appearing in the Bank Reconciliation window, but it appeared and when he was selecting the check, it gave him an error message as shown below.

image

Lets assume a scenario where a check was posted on 04/12/2017. The check was later voided as of 05/12/2017.

image

When running bank reconciliations in Dynamics GP, if you specify the cut off date as 04/30/2017, the check (even though voided) will still show up on the bank reconciliation window, since the status of the check as of 04/30/2017 is still active as it was only voided as of 05/12/2017.

image

So once we enter a cut off date as 05/12/2017, the voided check does not show up on the Bank Reconciliation window.

image

Note that if you do not specify a cut off date, the system will show all un-reconciled transactions (including voids) in the scrolling window for the reconciliation.

So ensure that you enter a valid cut off date during the reconciliation process.

Hope this helps the community…

Until next post!

September 16, 2011 · veeyeskay · 4 Comments
Tags: , , ,  · Posted in: Bank Reconciliation, Dynamics, Great Plains Total Views: 3,565

SQL Script for Checkbook Balance

Hi all

This time, we have a script to generate checkbook balances in Dynamics GP.

Also, take a look at the checkbook register query posted by Victoria Yudin, here.

 SELECT Z.CHECKBOOKID,
        Z.GPACCOUNTNO,
        Z.DOCUMENTNO,
        Z.DOCTYPE,
        Z.DOCTYPENAME,
        Z.POSTINGDATE,
        Z.DOCUMENTAMOUNT
 FROM   ( SELECT    A.CHEKBKID AS CHECKBOOKID,
                    RTRIM(D.ACTNUMST) AS GPACCOUNTNO,
                    A.CMTrxNum AS DOCUMENTNO,
                    C.DOCABREV AS DOCTYPE,
                    C.DOCTYNAM AS DOCTYPENAME,
                    A.GLPOSTDT AS POSTINGDATE,
                    ( A.TRXAMNT * -1 ) AS DOCUMENTAMOUNT
          FROM      dbo.CM20200 A
                    INNER JOIN dbo.CM00100 B ON A.CHEKBKID = B.CHEKBKID
                    INNER JOIN dbo.CM40101 C ON A.CMTrxType = C.CMTrxType
                    INNER JOIN dbo.GL00105 D ON B.ACTINDX = D.ACTINDX
          WHERE     A.VOIDED = 0
          UNION ALL
          SELECT    A.CHEKBKID AS CHECKBOOKID,
                    RTRIM(D.ACTNUMST) AS GPACCOUNTNO,
                    A.CMTrxNum AS DOCUMENTNO,
                    C.DOCABREV AS DOCTYPE,
                    C.DOCTYNAM AS DOCTYPENAME,
                    A.GLPOSTDT AS POSTINGDATE,
                    A.TRXAMNT AS DOCUMENTAMOUNT
          FROM      dbo.CM20200 A
                    INNER JOIN dbo.CM00100 B ON A.CHEKBKID = B.CHEKBKID
                    INNER JOIN dbo.CM40101 C ON A.CMTrxType = C.CMTrxType
                    INNER JOIN dbo.GL00105 D ON B.ACTINDX = D.ACTINDX
          WHERE     A.VOIDED = 1
          UNION ALL
          SELECT    A.CHEKBKID AS CHECKBOOKID,
                    RTRIM(D.ACTNUMST) AS GPACCOUNTNO,
                    A.RCPTNMBR AS DOCUMENTNO,
                    C.DOCABREV AS DOCTYPE,
                    C.DOCTYNAM AS DOCTYPENAME,
                    A.GLPOSTDT AS POSTINGDATE,
                    A.RCPTAMT AS DOCUMENTAMOUNT
          FROM      dbo.CM20300 A
                    INNER JOIN dbo.CM00100 B ON A.CHEKBKID = B.CHEKBKID
                    INNER JOIN dbo.CM40101 C ON A.RcpType = C.CMTrxType
                    INNER JOIN dbo.GL00105 D ON B.ACTINDX = D.ACTINDX
          WHERE     VOIDED = 0
          UNION ALL
          SELECT    A.CHEKBKID AS CHECKBOOKID,
                    RTRIM(D.ACTNUMST) AS GPACCOUNTNO,
                    A.RCPTNMBR AS DOCUMENTNO,
                    C.DOCABREV AS DOCTYPE,
                    C.DOCTYNAM AS DOCTYPENAME,
                    A.GLPOSTDT AS POSTINGDATE,
                    ( A.RCPTAMT * -1 ) AS DOCUMENTAMOUNT
          FROM      dbo.CM20300 A
                    INNER JOIN dbo.CM00100 B ON A.CHEKBKID = B.CHEKBKID
                    INNER JOIN dbo.CM40101 C ON A.RcpType = C.CMTrxType
                    INNER JOIN dbo.GL00105 D ON B.ACTINDX = D.ACTINDX
          WHERE     VOIDED = 1
        ) Z
 ORDER BY Z.CHECKBOOKID,
        Z.GPACCOUNTNO,
        Z.DOCUMENTNO,
        Z.DOCTYPE,
        Z.DOCTYPENAME,
        Z.POSTINGDATE

Note: We can add a checkbook parameter and a date filter to generate the balance in that checkbook as of that specific date (as a sum of the DOCUMENTAMOUNT field).

Hope this script is useful for all.

Until next post…

November 10, 2010 · veeyeskay · 3 Comments
Tags: ,  · Posted in: Bank Reconciliation, SQL Server, SQL Server 2005, SQL Server 2008 Total Views: 2,221