Bank Reconciliation to General Ledger  

In an effort to determine what was out of balance between the Checkbook and the General Ledger, this was like finding a needle in a haystack. In the Microsoft Dynamics GP Bank Reconciliation Module, the table values are very different than the General Ledger GL20000 and GL30000 table values. There is not a one for one matching between the two modules. 


To uncover the 1 or 2 rows that were out of balance, I developed this script to include a summation of the control account in the GL by document number with a join on the CM table. Keep in mind this script was developed for one specific checkbook. Depending on where your transactions come from, you may need to alter this script. Feel free to email me if you need help doing so. 




/*   Developed by Jordan Jimenez 06/14/2016

Directions:   Replace the Checkbook and GL Index fields below that correspond to your bank reconciliation issue


*/


SELECT * FROM (
                    SELECT *, TotalGL-TotalReceipt-TotalWDLTransfer as Differences FROM (

                                                SELECT  ORDOCNUM, SUM(DEBITAMT)-SUM(CRDTAMNT) as DebitCredits, SUM(DEBITAMT) + SUM(CRDTAMNT) as TOTALGL, ISNULL(receipts.TotalReceipt,0.00) as TotalReceipt, ISNULL(wtransfers.TotalWDLTransfer,0.00) as TotalWDLTransfer  FROM GL20000 as gl

                                                LEFT OUTER JOIN (


                                                SELECT RCPTNMBR, SUM(RCPTAMT) as TotalReceipt 

                            
                                                FROM CM20300

                                                WHERE 1=1

                                                and VOIDED = 0
                                                AND CHEKBKID = 'OPTGFTB'
                                                GROUP BY RCPTNMBR

                    ) as Receipts
                    on receipts.RCPTNMBR = gl.ORDOCNUM




                    LEFT OUTER JOIN (


                                            SELECT      CMTRXNUM, SUM(TRXAMNT) as TotalWDLTransfer FROM CM20200

                                            where 1=1

                                            AND CHEKBKID = 'OPTGFTB'


                                            GROUP BY CMTrxNum

                    ) as WTransfers
                    on Wtransfers.CMTrxNum = gl.ORDOCNUM



                    WHERE 1=1

                    AND ACTINDX = '1136'
                    AND SOURCDOC <> 'BBF'
                    --AND gl.TRXDATE BETWEEN '04/01/2016' and '04/30/2016'



                    GROUP BY ORDOCNUM, receipts.TotalReceipt, wtransfers.TotalWDLTransfer


) as main


) as embed
WHERE 1=1


            AND Differences <> 0.00
            AND DebitCredits <> 0.00












Dynamics GP - Jordan Jimenez