Transaction Matching in the General Ledger: Dynamics GP
The ability to link a debit to a credit within the General Ledger was introduced in Version 9.0 of Dynamics GP. I looked at it then thinking that this would be a great way to reconcile clearing accounts. The process we used to go through (back in my user days) to clear recharge accounts, was the accountant would download a Smartlist of the new transactions into the clearing account in the month, add it to his last month’s spreadsheet and use Excel to do the offsets and get the remaining amount.
When I saw transaction matching, my initial thought was that you would be able to get a report of unmatched items. Not the case. So that is where I left it. Until now.
One of my clients had asked me about it for use in isolated outstanding items in a clearing account. So, armed with more SQL skills than I had a few years back (although by no means an expert), I set up the following view to link the transaction matching table to the GL open transactions table. It is a pretty crude view, but we are returning the data to Excel and using filters etc to isolate the transactions we want. Obviously the view could be filtered down to specific accounts etc as needed.
SELECT TOP (100) PERCENT
dbo.LK000003.LK_Link_No AS [Link ID],
dbo.GL20000.JRNENTRY AS Journal,
dbo.GL00105.ACTNUMST AS Account,
dbo.GL20000.TRXDATE AS Date,
dbo.GL20000.SOURCDOC AS SourceDoc,
dbo.GL20000.REFRENCE AS Reference,
dbo.GL20000.DSCRIPTN AS Description,
dbo.GL20000.DEBITAMT AS Debit,
dbo.GL20000.CRDTAMNT AS Credit,
dbo.GL20000.ORMSTRID AS [Orig Master ID],
dbo.GL20000.ORMSTRNM AS [Orig Master Name],
dbo.GL20000.ORDOCNUM AS [Orig Doc Num],
dbo.GL20000.CURNCYID AS Currency
FROM
dbo.GL20000 FULL OUTER JOIN dbo.LK000003
ON dbo.GL20000.JRNENTRY = dbo.LK000003.JRNENTRY AND dbo.GL20000.ACTINDX = dbo.LK000003.ACTINDX AND dbo.GL20000.SEQNUMBR = dbo.LK000003.SEQNUMBR
INNER JOIN dbo.GL00105 ON dbo.GL20000.ACTINDX = dbo.GL00105.ACTINDX
If you are not familiar with creating views, you may need to get some help from your GP or SQL guys to set this up to use.