Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Saturday, August 2, 2014

Reconcile Check books to General Ledger – SQL Script

 

On a routine bases, sub ledgers should be reconciled with the General Ledger module, it is part of the accounting best practices that should be applied throughout the monthly financial closing. Now with GP 2013, the Reconcile to GL utility has made it simple for accountants to reconcile AP, AR, CM and Inventory with the General Ledger. Although, with previous versions it was a nightmare.

I have been working recently on developing a reconcile utility for previous GP versions. In this post, a script to reconcile check books to general ledger is provided (CM To GL). The script is still under testing as further revision version are required to stabilize its performance. Despite of its yet weak performance, it could still deliver a bit of an added value for those who are still living with the old versions of Dynamics GP. Of course, this is not to encourage them in any way to stay with old fashioned versions, but still better than nothing.

The script output in general is illustrated below;

Script Output

Digging deeper in the CM and GL parts, the following is more in depths output:

In details output

 

Helping Notes:

  • The script reconciles only one check book at a time, you can not reconcile more than one.
  • In order to run the script, pass the parameters required on the (Where) section which are
    • Checkbook ID
    • Month (Optional but recommended)
    • Year (Optional but recommended)
  • The script may have weak performance on large company database since it brings data from several GP modules (CM, AP, AR and GL)
  • Several additional GP modules have not been tested such as Payment Document Management (PDC), Schedule Payments and others.
  • As an initial test, the script below will run on (TWO) database (Fabrikam) and “Petty Cash” checkbook.

/*----------------------------------------------------------------------------------------

Creation Date: 3rd of August, 2014

Created by: Mahmoud M. AlSaadi

The main purpose of the script is to reconcile CM Transactions resulting from (CM, APAR) to General Ledger

The script has been tested on a very limited sample data. Several modules were not tested such as Schedule Payments, Payment Document Management ...etc.

Revision History:

Revision No.            RevisionDate    Description

1                       03/08/2014      Original Version

------------------------------------------------------------------------------------------ */

 

USE TWO

SELECT  CASE VARIANCE

WHEN 0 THEN 'Matched Transaction'

ELSE 'Unmatched Transaction'

END AS ReconciliationStatus ,

ISNULL(TRXDATE,GLTRXDATE) AS ReconDate,

*

FROM    ( SELECT    CMS.CHEKBKID ,

CMS.ACTINDX ,

CMS.CMTrxNum ,

TRXDATE ,

CASE CMS.CMTrxType

WHEN 1 THEN 'Deposit'

WHEN 3 THEN 'Check'

WHEN 4 THEN 'Withdrawal'

WHEN 5 THEN 'Increase Adjustment'

WHEN 6 THEN 'Decrease Adjustment'

WHEN 7 THEN 'Transfer'

ELSE ''

END AS CMTRXTPE ,

CMS.TRXAMNT ,

CMS.VOIDED ,

CMS.AUDITTRAIL ,

CMS.SRCDOCNUM ,

CMS.Original_DOCNMBR ,

CMS.CM_TRXSOURCE ,

CMS.Original_DOCTYPE ,

CMS.Original_DOCAMNT ,

CMS.Original_CHEKBKID ,

CMS.Original_DESPOSITNMBR ,

CMS.Original_RCPTNMBR ,

CMS.Original_RCPTAMNT ,

CMS.Original_VOIDED ,

CMS.TRX_STATUS ,

CMS.Original_TRXSOURCE ,

CMS.APPLIEDTODCNM ,

CMS.PMAPLTODOC,

CMS.CashReceiptType ,

GLS.ACTDESCR ,

GLS.ACTNUMST ,

GLS.GLTRXDATE,

GLS.JRNENTRY ,

GLS.DEBITAMT ,

GLS.CRDTAMNT ,

GLS.ORGNTSRC ,

GLS.ORTRXSRC ,

GLS.ORCTRNUM ,

GLS.ORDOCNUM ,

GLS.DEBITAMT - GLS.CRDTAMNT AS GLBalance,

ABS(GLS.DEBITAMT - GLS.CRDTAMNT)

( CASE Original_DOCAMNT

WHEN 0 THEN TRXAMNT

ELSE Original_DOCAMNT

END

                              ) AS Variance

FROM      (

 

SELECT X.CHEKBKID ,

X.CMTrxNum ,

CX.ACTINDX ,

X.CMTrxType ,

X.TRXAMNT ,

X.VOIDED ,

X.TRXDATE ,

X.AUDITTRAIL ,

X.SRCDOCNUM ,

ISNULL(Y.TRX_STATUS, ISNULL(Z.TRX_STATUS, '')) AS TRX_STATUS ,

ISNULL(Y.DOCNUMBR, ISNULL(Z.DOCNUMBR, 0)) AS Original_DOCNMBR ,

ISNULL(Y.TRXSORCE, ISNULL(Z.TRXSORCE, 0)) AS CM_TRXSOURCE ,

ISNULL(Y.RMDTYPAL, ISNULL(Z.DOCTYPE, 0)) AS Original_DOCTYPE ,

ISNULL(Y.ORTRXAMT, ISNULL(Z.DOCAMNT, 0)) AS Original_DOCAMNT ,

--ISNULL(Y.VOIDSTTS,ISNULL(Z.VOIDED,0)) AS Original_DOCNMBR,

ISNULL(Y.MSCSCHID, ISNULL(Z.CHEKBKID, 0)) AS Original_CHEKBKID ,

ISNULL(Y.depositnumber, 0) AS Original_DESPOSITNMBR ,

ISNULL(Y.RCPTNMBR, 0) AS Original_RCPTNMBR ,

ISNULL(Y.RCPTAMT, 0) AS Original_RCPTAMNT ,

ISNULL(Y.APTODCNM, ISNULL(APTVCHNM,'')) AS APPLIEDTODCNM ,

Y.CSHRCTYP AS CashReceiptType ,

ISNULL(Y.VOIDED, ISNULL(Z.VOIDED, 0)) AS Original_VOIDED ,

ISNULL(Z.APTODCNM,'') PMAPLTODOC,

ISNULL(Y.SRCDOCNUM, ISNULL(Z.TRXSORCE, 0)) AS Original_TRXSOURCE

FROM      dbo.CM20200 AS X

LEFT OUTER JOIN CM00100 AS CX ON X.CHEKBKID = CX.CHEKBKID

LEFT OUTER JOIN (

SELECT 

A.DOCNUMBR ,

A.TRX_STATUS ,

A.APTODCNM ,

A.TRXSORCE ,

A.RMDTYPAL ,

A.ORTRXAMT ,

A.SLSAMNT ,

A.VOIDSTTS ,

A.MSCSCHID ,

A.CSHRCTYP ,

B.depositnumber ,

B.RCPTNMBR ,

B.RCPTAMT ,

B.VOIDED ,

B.SRCDOCNUM ,

B.AUDITTRAIL ,

B.CHEKBKID

FROM     

(

SELECT

*

FROM

( SELECT

'Open' AS TRX_STATUS,

CUSTNMBR ,

DOCNUMBR ,

CSHRCTYP ,

TRXSORCE ,

RMDTYPAL ,

ORTRXAMT ,

SLSAMNT ,

VOIDSTTS ,

MSCSCHID

FROM

dbo.RM20101

WHERE

RMDTYPAL = 9

UNION ALL

SELECT

'History' AS TRX_STATUS,

CUSTNMBR ,

DOCNUMBR ,

CSHRCTYP ,

TRXSORCE ,

RMDTYPAL ,

ORTRXAMT ,

SLSAMNT ,

VOIDSTTS ,

MSCSCHID

FROM dbo.RM30101

WHERE RMDTYPAL = 9

) AS RMTRX

LEFT OUTER JOIN

( SELECT

CUSTNMBR AS CUST ,

TRXSORCE AS TRXSRC ,

APTODCNM ,

APFRDCNM

FROM

dbo.RM20201

UNION ALL

SELECT

CUSTNMBR ,

TRXSORCE ,

APTODCNM ,

APFRDCNM

FROM

dbo.RM30201

) AS RMAPL ON RMTRX.CUSTNMBR = RMAPL.CUST

AND RMTRX.DOCNUMBR = RMAPL.APFRDCNM

AND RMTRX.TRXSORCE = RMAPL.TRXSRC

) AS A

LEFT OUTER JOIN dbo.CM20300

AS B ON A.DOCNUMBR = B.RCPTNMBR

AND A.MSCSCHID = B.CHEKBKID

) AS Y ON X.CMTrxNum = Y.depositnumber

AND X.CHEKBKID = Y.CHEKBKID

AND CMTrxType = 1

LEFT OUTER JOIN

( SELECT    TRX_STATUS ,

PMTRX.VENDORID ,

PMTRX.VCHRNMBR ,

DOCNUMBR ,

DOCTYPE ,

DOCAMNT ,

TRXSORCE ,

CHEKBKID ,

VOIDED ,

ISNULL(APTVCHNM,'') AS APTVCHNM ,

ISNULL(APTODCNM,'')AS APTODCNM

FROM      (

SELECT

'Open' AS TRX_STATUS ,

VENDORID ,

VCHRNMBR ,

DOCNUMBR ,

DOCTYPE ,

DOCAMNT ,

TRXSORCE ,

CHEKBKID ,

VOIDED

FROM

dbo.PM20000

WHERE

DOCTYPE = 6

UNION ALL

SELECT

'History' AS TRX_STATUS ,

VENDORID ,

VCHRNMBR ,

DOCNUMBR ,

DOCTYPE ,

DOCAMNT ,

TRXSORCE ,

CHEKBKID ,

VOIDED

FROM

dbo.PM30200

WHERE

DOCTYPE = 6

) AS PMTRX

LEFT OUTER JOIN

( SELECT

VENDORID ,

VCHRNMBR ,

APTVCHNM ,

APTODCNM

FROM

PM10200

UNION ALL

SELECT

VENDORID ,

VCHRNMBR ,

APTVCHNM ,

APTODCNM

FROM

PM30300

) AS PMAPL ON PMTRX.VENDORID = PMAPL.VENDORID

AND PMTRX.VCHRNMBR = PMAPL.VCHRNMBR

) AS Z ON Z.DOCNUMBR = X.CMTrxNum

AND Z.CHEKBKID = X.CHEKBKID

AND Z.VENDORID = X.CMLinkID

AND Z.TRXSORCE = X.AUDITTRAIL

) AS CMS

FULL OUTER JOIN (

SELECT    H.CHEKBKID ,

GLA.ACTDESCR ,

GLM.ACTNUMST ,

GLM.ACTINDX ,

GL.TRXDATE AS GLTRXDATE,

GL.SOURCDOC ,

GL.JRNENTRY ,

GL.DEBITAMT ,

GL.CRDTAMNT ,

GL.ORGNTSRC ,

GL.ORCTRNUM ,

ORTRXSRC ,

GL.ORDOCNUM

FROM      CM00100 AS H

LEFT OUTER JOIN dbo.GL20000 AS GL ON H.ACTINDX = GL.ACTINDX

LEFT OUTER JOIN GL00100 AS GLA ON GLA.ACTINDX = GL.ACTINDX

LEFT OUTER JOIN GL00105 AS GLM ON GLM.ACTINDX = GL.ACTINDX

WHERE     SOURCDOC <> 'BBF'

UNION ALL

SELECT    H.CHEKBKID ,

GLA.ACTDESCR ,

GLM.ACTNUMST ,

GLM.ACTINDX ,

GL.TRXDATE AS GLTRXDATE,

GL.SOURCDOC ,

GL.JRNENTRY ,

GL.DEBITAMT ,

GL.CRDTAMNT ,

GL.ORGNTSRC ,

GL.ORCTRNUM ,

ORTRXSRC ,

GL.ORDOCNUM

FROM      CM00100 AS H

LEFT OUTER JOIN dbo.GL30000 AS GL ON H.ACTINDX = GL.ACTINDX

LEFT OUTER JOIN GL00100 AS GLA ON GLA.ACTINDX = GL.ACTINDX

LEFT OUTER JOIN GL00105 AS GLM ON GLM.ACTINDX = GL.ACTINDX

WHERE     SOURCDOC <> 'BBF'

--WHERE H.CHEKBKID = 'CASHBOOK' --AND ORTRXSRC = 'RMCSH00000002' ORDER BY JRNENTRY

                                     

) AS GLS ON

( CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.AUDITTRAIL) = CONVERT(VARCHAR(32), GLS.ORGNTSRC)

AND CONVERT(VARCHAR(32), CMS.SRCDOCNUM) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

AND CMS.CMTrxType IN ( 4, 5,6, 7 )

)

OR ( CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.CM_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORCTRNUM)

AND CMS.CMTrxType IN ( 1 )

AND CONVERT(VARCHAR(32), CMS.Original_DOCNMBR) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

)

OR ( CMS.CMTrxType = 1

AND CMS.CashReceiptType = 1

AND CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.CM_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORTRXSRC)

AND CONVERT(VARCHAR(32), CMS.APPLIEDTODCNM) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

OR CONVERT(VARCHAR(32), CMS.Original_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

)

OR ( CMS.CMTrxType = 1

AND CMS.CashReceiptType = 0

AND CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.CM_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORTRXSRC)

AND CONVERT(VARCHAR(32), CMS.Original_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORCTRNUM)

)

OR ( CMS.CMTrxType = 1

AND CMS.CashReceiptType IS NULL

AND CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.CM_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORTRXSRC)

AND CONVERT(VARCHAR(32), CMS.Original_TRXSOURCE) = CONVERT(VARCHAR(32), GLS.ORCTRNUM)

)

OR ( CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.CMTrxNum) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

AND ( CONVERT(VARCHAR(32), CMS.SRCDOCNUM) = CONVERT(VARCHAR(32), GLS.ORCTRNUM)

OR CONVERT(VARCHAR(32), CMS.AUDITTRAIL) = CONVERT(VARCHAR(32), GLS.ORGNTSRC)                                                          

)

)

OR ( CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.AUDITTRAIL) = CONVERT(VARCHAR(32), GLS.ORTRXSRC)

AND CMS.CMTrxType IN ( 4 )

AND CMS.CM_TRXSOURCE IS NOT NULL

)

OR ( GLS.ACTINDX = CMS.ACTINDX

AND CONVERT(VARCHAR(32), GLS.SOURCDOC) = 'GJ'

AND CMS.CHEKBKID IS NULL

AND CMS.CHEKBKID = GLS.CHEKBKID

)

OR ( CONVERT(VARCHAR(32), CMS.CHEKBKID) = CONVERT(VARCHAR(32), GLS.CHEKBKID)

AND CONVERT(VARCHAR(32), CMS.AUDITTRAIL) = CONVERT(VARCHAR(32), GLS.ORTRXSRC)

AND ( CONVERT(VARCHAR(32), CMS.APPLIEDTODCNM) = CONVERT(VARCHAR(32), GLS.ORCTRNUM)

AND ( CONVERT(VARCHAR(32), CMS.PMAPLTODOC) = CONVERT(VARCHAR(32), GLS.ORDOCNUM)

AND CMS.CMTrxType IN ( 3 )

                                                    

)

)

)

 

WHERE    

(

   CMS.CHEKBKID = 'PETTY CASH'

OR CMS.CHEKBKID IS NULL

)

AND

(

   GLS.CHEKBKID = 'PETTY CASH'

OR GLS.CHEKBKID IS NULL

)

AND MONTH(ISNULL(TRXDATE,GLTRXDATE)) IN ('04','05')

AND YEAR(ISNULL(TRXDATE,GLTRXDATE)) IN ('2017','2016')

) AS REC

ORDER BY RECONCILIATIONSTATUS

 

Click here to download the CM Reconcile to GL SQL Script.

Best Regards,

Mahmoud M. AlSaadi

No comments:

Post a Comment