How to display EURO Currency symbol in Dynamics GP?

 

Looks an easy question right? Go to Microsoft Dynamics GP menu >> Tools >> Setup >> System >> Currency >> Currency expansion button and select the € symbol!

Well not that’s easy, if you had languages other that the English or your collation for SQL is not Latin you will get the below:

image

This symbol actually represents the Euro currency symbol, select this symbol and go try to print a report, you will find the symbol there:

image

Enjoy!


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Error Converting Request to Purchase Order–Business Portal 5.1

 

I have installed Business Portal 5.1 for one of my customers and been through the below error in converting Purchase Request into Purchase Order

The specified Protocol is invalid

Doing researches about this subject returned that the web.config of the business portal might be missing from this path “C:\Program Files\Microsoft Dynamics\Business Portal” and it was!

To resolve this I have connected to another client whose running the business portal with no issues and copied the web.config! It worked perfectly, below is the web.config content:

<?xml version="1.0" encoding="utf-8" ?>
<
configuration>

<
system.web>

<
xhtmlConformance mode="Legacy" />

</
system.web>


<
appSettings>

<
add key="Protocol" value="1"/>
<
add key="TaxEngineServiceAssembly0" value="Microsoft.Business.Taxes.Services"/>
<
add key="TaxEngineServiceClass0" value="Microsoft.Business.Taxes.TaxEngine"/>

<
add key="TaxEngineDataServiceAssembly0" value="Microsoft.Dynamics"/>
<
add key="TaxEngineDataServiceClass0" value="Microsoft.Dynamics.Common.TaxEngineData"/>

<
add key="TaxEnginePreCalculateDocumentEventAssembly0" value="Microsoft.Dynamics"/>
<
add key="TaxEnginePreCalculateDocumentEventClass0" value="Microsoft.Dynamics.Common.TaxEngineISV"/>
<
add key="TaxEnginePreCalculateDocumentEventMethod0" value="DocumentPre"/>

<
add key="TaxEnginePreGetTaxGroupIDsEventAssembly0" value="Microsoft.Dynamics"/>
<
add key="TaxEnginePreGetTaxGroupIDsEventClass0" value="Microsoft.Dynamics.Common.TaxEngineISV"/>
<
add key="TaxEnginePreGetTaxGroupIDsEventMethod0" value="PreGetTaxGroupIDs"/>

<
add key="TaxEnginePreCalculateCodeEventAssembly0" value="Microsoft.Dynamics"/>
<
add key="TaxEnginePreCalculateCodeEventClass0" value="Microsoft.Dynamics.Common.TaxEngineISV"/>
<
add key="TaxEnginePreCalculateCodeEventMethod0" value="PreCalculateCode"/>

</
appSettings>

</
configuration>

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Mass Item/Vendor Link

 

I got a request from one of my customers to have the ability to link multiple items with a vendor without the need to add them one by one and I have created this using the .Net toolkit, the screen will look like the below:

image

The user can filter items from the list on the left panel, click on insert and process! The system will link the selected items with the selected vendor!

I am willing to publish this tool soon, will update the article with the download link once doing some fine tuning on the utility.


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Field Level Security is not working!?!?

 

Few months back, one of my clients reported an issue with the field level security module in Dynamics GP that it only works for “Sa”, and this morning I noticed a question in the community reminding me to post about this issue! Below are the circumstances:

1. Only the sa user can see the module on GP even by giving the power user to other users.

2. The restrictions made on the field level security module are applied only to sa user.

Solution clearly shows that this is not an issue with the application and it is an issue with database privileges, simply go to “C:\Program Files\Microsoft Dynamics\GP2010\SQL\Util” and locate a script called “Grant.SQL”, the script will grant the DYNGRP role in SQL the access on all objects in the database and will resolve your issue, below the script if needed:

/*Count : 1 */

declare @cStatement varchar(255)

declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
where (type = 'U' or type = 'V') and uid = 1

set nocount on
OPEN
G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC
(@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE
G_cursor

declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
where type = 'P'

set nocount on
OPEN
G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC
(@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE
G_cursor

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

GL Summary Reconcile

 

One of my clients requested to reconcile their GL Account Summary to GL Account Details periodically using SQL which is not currently available in Dynamics GP, I suggested to schedule a macro run but they wanted this to be done at the SQL level, therefore I have written the SQL command below to empty GL10110 table and repopulate the records to the tables using figures in GL20000.

Find the script below or download it from this location.

Disclaimer: This script works when your fiscal periods are matched with calendar periods only as it uses month of the transaction date as the transaction period, on the other hand, I tested this on limited data, if you find an issue or have any idea for script enhancement, please let feel free to comment.


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mohammad R. Daoud
-- Create date: 2012-01-19
-- Description: Procedure to reconcile GL Summary
-- =============================================
CREATE PROCEDURE DI_GLRECONCILE

AS
BEGIN
DELETE FROM
[GL10110]

INSERT INTO [GL10110]
([ACTINDX]
,[YEAR1]
,[PERIODID]
,[PERDBLNC]
,[ACTNUMBR_1]
,[ACTNUMBR_2]
,[ACTNUMBR_3]
,[ACTNUMBR_4]
,[ACTNUMBR_5]
,[ACTNUMBR_6]
,[ACTNUMBR_7]
,[ACTNUMBR_8]
,[ACTNUMBR_9]
,[ACTNUMBR_10]
,[ACCATNUM]
,[CRDTAMNT]
,[DEBITAMT])

SELECT dbo.GL20000.ACTINDX, YEAR(dbo.GL20000.TRXDATE) AS YEAR1, MONTH(dbo.GL20000.TRXDATE) AS Period,
SUM(dbo.GL20000.DEBITAMT - dbo.GL20000.CRDTAMNT) AS [Period Balance], dbo.GL00100.ACTNUMBR_1, dbo.GL00100.ACTNUMBR_2,
dbo.GL00100.ACTNUMBR_3, dbo.GL00100.ACTNUMBR_4, dbo.GL00100.ACTNUMBR_5, dbo.GL00100.ACTNUMBR_6, dbo.GL00100.ACTNUMBR_7,
dbo.GL00100.ACTNUMBR_8, dbo.GL00100.ACTNUMBR_9, dbo.GL00100.ACTNUMBR_10, dbo.GL00100.ACCATNUM, SUM(dbo.GL20000.CRDTAMNT)
AS CRDTAMNT, SUM(dbo.GL20000.DEBITAMT) AS DEBITAMT
FROM dbo.GL20000 INNER JOIN
dbo.GL00100 ON dbo.GL20000.ACTINDX = dbo.GL00100.ACTINDX
GROUP BY dbo.GL20000.ACTINDX, YEAR(dbo.GL20000.TRXDATE), dbo.GL00100.ACTNUMBR_1, dbo.GL00100.ACTNUMBR_2, dbo.GL00100.ACTNUMBR_3,
dbo.GL00100.ACTNUMBR_4, dbo.GL00100.ACTNUMBR_5, dbo.GL00100.ACTNUMBR_6, dbo.GL00100.ACTNUMBR_7, dbo.GL00100.ACTNUMBR_8,
dbo.GL00100.ACTNUMBR_9, dbo.GL00100.ACTNUMBR_10, dbo.GL00100.ACCATNUM, MONTH(dbo.GL20000.TRXDATE)
END

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Before Yearend Closing Check – Retained Earnings Exception Report

 

It has been long time since I been an active online community member as before, as the day has only 24 hours and is really not enough for 45 customers with many GP issues, offline contribution, online community activities, my masters , family, and fun time!!!

However, I am doing my best to recover and follow up with all these! As a start, I wanted to share one thing to be included in your checks before closing your financial year, if you have several currencies linked to your profit and loss accounts, you will need to make sure that all these currencies are linked to the retained earnings account, so the multicurrency management module can work this with no issues.

If you forgot to do so, the system will generate the accounts along with the currencies not linked to the Retained Earnings in a report called “Retained Earnings Exception Report” and the report will be printed automatically.

Nothing to be done from your side at this point as the system will affect the retained earnings with the amounts, but you need to validate your profit/loss balance and make sure that the RE account affected correctly.

Quoting from the multicurrency management printable manual:

If you haven’t assigned all currency IDs to your retained earnings account using the
Select Account Currencies window, the Multicurrency Retained Earnings Exception
Report also will be printed. This report displays all retained earnings accounts for
which multicurrency transactions were closed, even though the currency for the
multicurrency amounts wasn’t assigned to the retained earnings account. If you
didn’t intend to close these accounts to the retained earnings account, you can use
this report to determine the amounts you’ll need for correcting these transactions.

Hope that this helps.


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

An Error while importing Analytical Accounting Budget

One of my clients is using Analytical Accounting to link cost centers with expense accounts, and therefore they are using the Analytical Accounting budget to specify a budget for both Projects (CAPEX) and to link their expense accounts with cost centers and specify a budget for each cost center on each expense account to manage their OPEX.

While loading the budget into the system, the user reported that he getting the below error:

Error converting data type varchar to numeric

image

Searching around the blogosphere and the partner source, I notice the same issue with version 8.0 and 9.0, but the customer is using version 2010 R2!

Performing deep investigations on the issue returned that one of periods contained a blank line instead of “ZERO”!! which been a tough task to be found through 6,000 lines and 12 periods!

Hope you get rid of this if found!


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com