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, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Advertisements

When using Credit Card option in Manual Payment System is using the same account in distribution?!

An interesting question came up to the newsgroup yesterday, it was a case that the the user is trying to create manual payment using credit card option, but the distribution of the manual payment was crediting and debiting the accounts payable account.

That’s correct, actually the normal distribution for the Manual Payment is Crediting the Cash Account and Debiting the Account Payables the AP account is loaded from the Vendor account setup selected in your manual payment, and cash account is loaded from the selected checkbook.

 

Credit —> Cash —> Loaded from the selected Checkbook Cash Account

Debit —> AP —> Loaded from the selected From Vendor account setup

However when using the Credit Card Option, the cash account loaded will be the AP account of the credit card vendor:

image

image

Just make sure that your credit card vendor setup does not use the AP account as account payable, instead use a cash account to fulfill your needs.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Display Extender Data Using VBA

While trying to catch up what I missed in the blogsphere during my last 3 trips outside the country! I enjoyed reading an article posted by David at Developing for Dynamics GP blog, he posted an example on displaying extender data using VBA, checkout his post here.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Creating Dynamics GP Installation Package

Andy Nifong posted an article on how to create Dynamics GP installation package with screenshots per steps, checkout his post here.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Currency Exchange Rates Feed (Importer) for Microsoft Dynamics GP 10.0

Long time ago, I have developed a service that loads currency exchange rates from the internet directly into Dynamics GP, I am willing to share the tool for free if you are interested to send me your information and willing to be on my company reference list!

Worth mentioning that the current source of the rates is this free online web service that provide currency rates on daily bases, if you wish to change the source of rates, feel free to change the configuration.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Recover “Not Reset!” the System Password!?!?!?!?!?!

Well, we used to run a script to reset the system password to “NONE” when forgetting it, but now you can recover the password you forgot by following the method posted in the Key to Information and Knowledge Sharing blog, its by just getting the field out to a new report! Checkout the complete details with steps here.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Microsoft Dynamics GP Window

logo

Jivtesh Singh, I like this guy! Long time ago he came up with a new interesting idea, he created a search engine for GP blogs only, the tool was very much useful when looking to solve a Dynamics GP related issue .

Few weeks ago, he launched a new portal (GP Window), the portal consolidates all GP articles collected from the experts blogs and classified in an interesting algorithms.

My and allot of other experts blogs are there, worth taking a a look to the site, it will be useful to find all GP related tips.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com