Dynamics GP Reporting Series: Sales Cash Receipt

As a part of the Receivables Management Module, we’ll need to print a cash receipt for the payments we receive from our customers, and due to many requests from the clients, I have included the distribution journal for the transaction, below the SQL command needed:

SQL Command:

SELECT    
dbo.SY00500.NUMOFTRX, dbo.SY00500.BCHCOMNT,
dbo.SY00500.BCHTOTAL, dbo.SY00500.CNTRLTOT,
dbo.SY00500.CNTRLTRX, dbo.SY00500.BACHFREQ,
dbo.SY00500.BACHDATE, dbo.SY00500.APRVLUSERID,
dbo.SY00500.APPRVLDT, dbo.SY00500.APPROVL,
dbo.SY00500.SERIES, dbo.RM10201.CUSTNMBR,
dbo.RM00101.CUSTNAME, dbo.RM10201.DOCNUMBR,
dbo.RM10201.DOCDATE, dbo.RM10201.TRXDSCRN,
dbo.RM10201.GLPOSTDT, dbo.RM10201.ORTRXAMT,
dbo.RM10201.WROFAMNT, dbo.RM10201.DISTKNAM,
dbo.RM10201.CURTRXAM, dbo.RM10201.CHEKNMBR,
dbo.GL00105.ACTNUMST, dbo.GL00100.ACTDESCR,
dbo.GL00100.ACTINDX, dbo.RM10101.CRDTAMNT,
dbo.RM10101.DEBITAMT, dbo.RM10101.ORDBTAMT,
dbo.RM10101.ORCRDAMT, dbo.RM10201.BACHNUMB,
dbo.RM10101.DISTTYPE, dbo.RM10201.CURNCYID
FROM        
dbo.GL00100
INNER JOIN dbo.RM10101 ON dbo.GL00100.ACTINDX = dbo.RM10101.DSTINDX
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
RIGHT OUTER JOIN dbo.RM10201
INNER JOIN dbo.SY00500 ON dbo.RM10201.BCHSOURC = dbo.SY00500.BCHSOURC
AND dbo.RM10201.BACHNUMB = dbo.SY00500.BACHNUMB
INNER JOIN dbo.RM00101 ON dbo.RM10201.CUSTNMBR = dbo.RM00101.CUSTNMBR
ON dbo.RM10101.DOCNUMBR = dbo.RM10201.DOCNUMBR

As all other reports, 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 below:

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