It seems I totally forgot to publish this article and thought that I finalized the series!
Customer statements are normally dependant on the each client process, end user will normally request the customer statement to be in the way that fulfills their needs, some users will require the statement to integrate the payment document management module and some requires the aging.
However, normally the customer statement option in the utilities covers almost all needed statements, below statement is a standard statement could generate the customer statement in “Payment – Invoice” columns which make easier to the accountant the understanding of customer transactions.
SQL Command:
SELECT CUSTNMBR,
DOCNUMBR AS DOCNUM, DOCDATE,
CASE RMDTYPAL
WHEN 1 THEN 'SLS' WHEN 2 THEN 'SCP'
WHEN 3 THEN 'DR' WHEN 4 THEN 'FIN'
WHEN 5 THEN 'SVC' WHEN 6 THEN 'WRN'
WHEN 7 THEN 'CR' WHEN 8 THEN 'RTN'
WHEN 9 THEN 'PMT' END AS CODE,
ISNULL(CASE RMDTYPAL
WHEN 1 THEN ORTRXAMT WHEN 3 THEN ORTRXAMT
WHEN 4 THEN ORTRXAMT WHEN 5 THEN ORTRXAMT
WHEN 6 THEN ORTRXAMT ELSE 0
END,0) AS INVOICE,ISNULL(CASE RMDTYPAL
WHEN 7 THEN -(CURTRXAM) WHEN 8 THEN -(CURTRXAM)
WHEN 9 THEN -(CURTRXAM) ELSE 0
END,0) AS PAYMENT, DOCNUMBR AS APPLIEDTO
FROM RM20101
WHERE (
(RMDTYPAL = 7 AND CURTRXAM <> 0)
OR (RMDTYPAL = 8 AND CURTRXAM <> 0) OR (RMDTYPAL = 9 AND CURTRXAM <> 0)
OR RMDTYPAL = 1 OR RMDTYPAL = 2 OR RMDTYPAL = 3
OR RMDTYPAL = 4 OR RMDTYPAL = 5 OR RMDTYPAL = 6)
AND VOIDSTTS <> 1
UNION
SELECT CUSTNMBR,
APFRDCNM AS DOCNUM, DATE1 AS DOCDATE ,
CASE APFRDCTY
WHEN 7 THEN 'CR' WHEN 8 THEN 'RTN'
WHEN 9 THEN 'PMT' END AS CODE,
0 AS INVOICE,
ISNULL(CASE APFRDCTY WHEN 7 THEN APPTOAMT
WHEN 8 THEN APPTOAMT WHEN 9 THEN APPTOAMT
ELSE 0 END,0) AS PAYMENT ,
APTODCNM AS APPLIEDTO
FROM RM20201
WHERE POSTED <> 0
ORDER BY DOCDATE
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
