tag:blogger.com,1999:blog-2189316327430810286.post7079578311977784412..comments2024-02-08T09:29:51.692-08:00Comments on Mohammad R. Daoud: Dynamics GP Reporting Series: Customer StatementMohammad R. Daoudhttp://www.blogger.com/profile/14714266024830204818noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-2189316327430810286.post-39868163629715538132014-10-18T04:49:52.918-07:002014-10-18T04:49:52.918-07:00you can use this modified one to get it in a speci...you can use this modified one to get it in a specified date <br /><br /><br /> select * from( SELECT CUSTNMBR, <br /> DOCNUMBR AS DOCNUM, DOCDATE, <br /> CASE RMDTYPAL <br /> WHEN 1 THEN 'SLS' WHEN 2 THEN 'SCP' <br /> WHEN 3 THEN 'DR' WHEN 4 THEN 'FIN' <br /> WHEN 5 THEN 'SVC' WHEN 6 THEN 'WRN' <br /> WHEN 7 THEN 'CR' WHEN 8 THEN 'RTN' <br /> WHEN 9 THEN 'PMT' END AS CODE, <br /> ISNULL(CASE RMDTYPAL <br /> WHEN 1 THEN ORTRXAMT WHEN 3 THEN ORTRXAMT <br /> WHEN 4 THEN ORTRXAMT WHEN 5 THEN ORTRXAMT <br /> WHEN 6 THEN ORTRXAMT ELSE 0 <br /> END,0) AS INVOICE, <br /><br /> ISNULL(CASE RMDTYPAL <br /> WHEN 7 THEN -(ORTRXAMT) WHEN 8 THEN -(ORTRXAMT) <br /> WHEN 9 THEN -(ORTRXAMT) ELSE 0 <br /> END,0) AS PAYMENT, DOCNUMBR AS APPLIEDTO <br /> FROM RM20101<br /> WHERE VOIDSTTS <> 1 <br /><br /> UNION <br /> SELECT CUSTNMBR, <br /> APFRDCNM AS DOCNUM, DATE1 AS DOCDATE , <br /> CASE APFRDCTY<br /> WHEN 7 THEN 'CR' WHEN 8 THEN 'RTN' <br /> WHEN 9 THEN 'PMT' END AS CODE, <br /> 0 AS INVOICE, <br /> ISNULL(CASE APFRDCTY WHEN 7 THEN -APPTOAMT <br /> WHEN 8 THEN -APPTOAMT WHEN 9 THEN -APPTOAMT <br /> ELSE 0 END,0) AS PAYMENT , <br /> APTODCNM AS APPLIEDTO <br /> FROM RM20201<br /> WHERE POSTED <> 0 <br /><br /> UNION<br /> <br /> SELECT CUSTNMBR, <br /> APTODCNM AS DOCNUM, DATE1 AS DOCDATE , <br /> CASE APFRDCTY<br /> WHEN 7 THEN 'CR' WHEN 8 THEN 'RTN' <br /> WHEN 9 THEN 'PMT' END AS CODE, <br /> 0 AS INVOICE, <br /> ISNULL(CASE APFRDCTY WHEN 7 THEN APPTOAMT <br /> WHEN 8 THEN APPTOAMT WHEN 9 THEN APPTOAMT <br /> ELSE 0 END,0) AS PAYMENT , <br /> APFRDCNM AS APPLIEDTO <br /> FROM RM20201<br /> WHERE POSTED <> 0 ) as moataz <br /> where moataz.CUSTNMBR = 'xx' and mooo.APPLIEDTO= 'xx'Anonymoushttps://www.blogger.com/profile/00333376326819208419noreply@blogger.com