Dynamics GP Reporting Series: Sales Credit Memo

As a part of the Receivables Management Module, we still have the Customer Credit Memo’s, the good thing is, MS guys already created view for the Receivables Transactions, below the SQL command needed:

SQL Command:

SELECT    
dbo.RM10301.DOCTYPE, dbo.RM10301.DOCDATE,
dbo.RM00102.ADDRESS1, dbo.RM00102.ADDRESS2,
dbo.RM00102.ADDRESS3, dbo.RM00102.CITY,
dbo.RM10301.DOCDESCR, dbo.RM10301.CUSTNMBR,
dbo.RM10301.CUSTNAME, dbo.RM10301.SLPRSNID,
dbo.RM10301.SHIPMTHD, dbo.RM10301.PYMTRMID,
dbo.RM10301.DOCAMNT, dbo.RM10301.SLSAMNT,
dbo.RM10301.MISCAMNT, dbo.RM10301.TAXAMNT,
dbo.RM10301.FRTAMNT, dbo.RM10301.TRDISAMT,
dbo.RM10301.CASHAMNT, dbo.RM10301.CHEKAMNT,
dbo.RM10301.CRCRDAMT, dbo.RM10301.CURNCYID,
dbo.RM10301.DOCNUMBR, dbo.ReceivablesTransactions.[Originating Cash Amount],
dbo.ReceivablesTransactions.[Originating Check Amount],
dbo.ReceivablesTransactions.[Originating Credit Card Amount],
dbo.ReceivablesTransactions.[Originating Current Trx Amount],
dbo.ReceivablesTransactions.[Originating Discount Taken Amount],
dbo.ReceivablesTransactions.[Originating Freight Amount],
dbo.ReceivablesTransactions.[Originating Misc Amount],
dbo.ReceivablesTransactions.[Originating Sales Amount],
dbo.ReceivablesTransactions.[Originating Tax Amount],
dbo.ReceivablesTransactions.[Originating Trade Discount Amount],
dbo.ReceivablesTransactions.[Originating Write Off Amount],
dbo.ReceivablesTransactions.[Currency ID]
FROM         dbo.RM10301
LEFT OUTER JOIN dbo.ReceivablesTransactions
ON dbo.RM10301.DOCNUMBR = dbo.ReceivablesTransactions.[Document Number]
LEFT OUTER JOIN dbo.RM00102 ON dbo.RM10301.CUSTNMBR = dbo.RM00102.CUSTNMBR
AND dbo.RM10301.ADRSCODE = dbo.RM00102.ADRSCODE
WHERE    
(dbo.RM10301.DOCTYPE = 6)
AND (dbo.ReceivablesTransactions.[Document Type] = 'Credit Memos ')
AND dbo.RM10301.DOCNUMBR = {?DOCNUMBER}

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 original Sales Credit Memo:

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