Friday, July 30, 2010

Dynamics GP Reporting Series: Customer Statement

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:

 image

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

1 comment:

moataz ahmed said...

you can use this modified one to get it in a specified date


select * from( 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 -(ORTRXAMT) WHEN 8 THEN -(ORTRXAMT)
WHEN 9 THEN -(ORTRXAMT) ELSE 0
END,0) AS PAYMENT, DOCNUMBR AS APPLIEDTO
FROM RM20101
WHERE 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

UNION

SELECT CUSTNMBR,
APTODCNM 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 ,
APFRDCNM AS APPLIEDTO
FROM RM20201
WHERE POSTED <> 0 ) as moataz
where moataz.CUSTNMBR = 'xx' and mooo.APPLIEDTO= 'xx'

Related Posts:

Related Posts with Thumbnails