Dynamics GP Reporting Series: Bank Transfers

As well as the bank transactions, bank transfers does not have “Save” operation as well, it directly posts the transaction to your checkbook, where we’ll need to get our report printed after the post operation, below the SQL command needed:

SQL Command:

SELECT    
dbo.CM20600.CMXFRNUM AS CMTrxNum, dbo.CM20600.CMCHKBKID,
dbo.CM20600.CMFRMCHKBKID, dbo.CM20600.CMXFTDATE,
dbo.CM20100.AUDITTRAIL, dbo.CM20200.DSCRIPTN,
dbo.CM20200.POSTEDDT, dbo.CM20200.Xfr_Record_Number,
dbo.CM20400.DistRef, dbo.GL00105.ACTNUMST,
dbo.GL00100.ACTDESCR, dbo.CM20200.ORIGAMT,
dbo.CM20400.ORCRDAMT, dbo.CM20400.ORDBTAMT,
dbo.CM20200.CURNCYID
FROM         dbo.CM20400
INNER JOIN dbo.CM20200 ON dbo.CM20400.CMDNUMWK = dbo.CM20200.CMRECNUM
INNER JOIN dbo.GL00100 ON dbo.CM20400.ACTINDX = dbo.GL00100.ACTINDX
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
INNER JOIN dbo.CM20600
INNER JOIN dbo.CM20100 ON dbo.CM20600.CMXFRNUM = dbo.CM20100.CMTrxNum
ON dbo.CM20200.CMRECNUM = dbo.CM20100.CMDNUMWK
WHERE     (dbo.CM20200.CMTrxType = 7)
AND dbo.CM20100.CMTrxNum = {?CMTRXNUM}

To avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the original Bank Transfer Posting Journal:

Crystal Report Design:

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com