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;
Digging deeper in the CM and GL parts, the following is more in depths 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