Dynamics GP Reporting Series: Bank Transactions

As you know, bank transactions does not have “Save” operation, 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.CM20200.TRXDATE, dbo.CM20200.GLPOSTDT,

dbo.CM20200.paidtorcvdfrom, dbo.CM20200.DSCRIPTN,

dbo.CM20100.AUDITTRAIL, dbo.CM20400.ACTINDX,

dbo.GL00100.ACTDESCR, dbo.GL00105.ACTNUMST,

dbo.CM40101.DOCTYNAM, dbo.CM20100.CMTrxNum,

dbo.CM20100.CMTrxType, dbo.CM20100.CHEKBKID,

dbo.CM20200.CURNCYID, dbo.CM20400.ORCRDAMT,

dbo.CM20400.ORDBTAMT, dbo.CM20200.ORIGAMT

FROM

dbo.CM20100 INNER JOIN dbo.CM20400

ON dbo.CM20100.CMDNUMWK = dbo.CM20400.CMDNUMWK

INNER JOIN dbo.GL00100 ON dbo.CM20400.ACTINDX = dbo.GL00100.ACTINDX

INNER JOIN dbo.GL00105 ON dbo.CM20400.ACTINDX = dbo.GL00105.ACTINDX

INNER JOIN dbo.CM20200 ON dbo.CM20100.CMTrxNum = dbo.CM20200.CMTrxNum

AND dbo.CM20100.CHEKBKID = dbo.CM20200.CHEKBKID

AND dbo.CM20100.CMTrxType = dbo.CM20200.CMTrxType

INNER JOIN dbo.CM40101 ON dbo.CM20100.CMTrxType = dbo.CM40101.CMTrxType

Where (dbo.CM20100.VOIDED <> 1) AND (dbo.CM20400.VOIDED <> 1) 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 Transaction 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