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

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

Wednesday, July 28, 2010

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

Posting Microsoft Dynamics GP Transactions Automatically “Autopost.DLL"

I used to hear that Microsoft developed a tool the auto posts Dynamics GP transactions, but I never saw anything like this, few days ago I needed such tool to fulfill an automatic integration requested by one of my clients.

The integration uses eConnect to integrate GP forms, and the customer wanted to auto post the transactions to eliminate human interaction, where I started my researches and found a tool with Microsoft that fulfill this request, its a tool developed using Continuum API to post GP transactions, but unfortunately I couldn’t use it due to the fact that GP must be opened and a user must be logged in.

However, the tool does exist, its not free but it worth's its price, at least you will not need to reinvent the wheel and develop such a thing, below what was written in its manual.

Purpose:

This DLL has the ability to several different types of posting methods. This DLL uses the Continuum API to call the posting processes in Great Plains. Because 99% of Great Plains posting code is written in Dexterity, Great Plains must be opened and the user logged into the appropriate company for this to work!

PostGLBatch- posts GL Batches entered in GL Transaction Entry window

PostIVTrxBatch – Post Inventory Trx Batches entered in the IV Item Transaction Entry window.

PostIVTransferBatch - Post Inventory Transfer Batches entered in the IV Item Transfer Entry window.

PostRMCash- posts a cash receipt batch entered from Cash Receipts Entry window

PostSOPBatch- posts a SOP batches entered in Sales Transaction Entry window

PostRecvBatch – posts a POP Receivings Transaction entered in the Receivings Transaction Entry window.

Hope that this helps.

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

Friday, July 23, 2010

Why my taxes not being automatically calculated in Dynamics GP?

A question came up to the Microsoft Dynamics Forums asks about the tax schedules and why it does not automatically calculated while the tax schedules are already defined and correctly linked to items, customers, vendors and sites.

There is an option in Microsoft Dynamics GP to calculate the tax based on the shipment method, this is normally unused in our region but it still being defaulted in company setup, so if you been in a situation that your taxes are correctly defined but the system does not calculate it, make sure that you go to “Microsoft Dynamics GP menu >> Tools >> Setup >> Company >> Company >> Options” and unmark “Use Shipping Method when Selecting Default Tax Schedule”.

image

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

Thursday, July 22, 2010

Analytical Accounting has not been installed. Access denied.

Vaidy posted a script to resolve this error, he’s enforcing GP to accept the fact that AA is installed, checkout his article here.

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

Long time no posts!

I been offline the last few weeks due to my engagement in projects outside the country.

However, I just posted this to inform you that I am back now!

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

Wednesday, July 21, 2010

Dynamics GP Cookbook

Dynamics GPMark Polino’s book “Dynamics GP Cookbook” is becoming well known day after day, readers database is being expanded day after day due to the exciting contents found in the book.

I really did enjoy reading the book and advice you all guys to get your copy, unlike traditional books, the book is designed to cover the topics as cooking receipts!

Download the sample copy now, or click the link below to purchase the book now.

 

 

 

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

Related Posts:

Related Posts with Thumbnails