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

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

eConnect POP Integration: Invalid Object Name PA10601

I been trying to workout a receiving integration for one of my customers and faced the below error:

Invalid Object Name “PA10601”

I am sure that the project accounting is not installed and the same tool is installed at more than 20 customers so the code is definitely correct.

In addition, sometimes the same issue exist with integration manager and some orders worked properly using the same utility!

We have failed to find anything on the internet that could resolve this and all suggested answers on the community did not resolve the mystery! While doing deep investigations on the issue and before pressing “Submit” for the Microsoft incident request I noticed the solution!

Issue #1 Receiving Integration:

I noticed that the Purchase Order we been trying to import contains data in “Project Number” and “Cost Category Number” in POP10110 table which made the eConnect to query the Project Accounting tables and validates the information and in return it causes the receiving integration to fail!

Issue #2 Purchase Order Integration:

While researching the internet, I noticed that many people faced issues in Purchase Order Integration which cannot be the same issue we been through.

Resolution #1:

Removing data from the mentioned fields fixed the integration!

Resolution #2:

Folks at SalesPad been through this issue and posted a script that resolves the issue by replacing the eConnect stored procedure, below is the link to the updated procedure:

Troubleshooting

Hope that this saves some time!

---

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

Dynamics GP Workflow–Task Approval Page redirects to local server URL even if the used URL was published!!

 

Looks like the folk at Microsoft forgot to remove the local URL when redirecting from Task Approval page to the actual document behind “This workflow task applied to:”:

So if your workflow was published over the internet and you users tries to open a document, the system will redirect them to the local server and not to the website as shown below:

To workaround this, I had to use SharePoint Designer or any available scripting tool to change the redirection of the below hyperlink in (C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\Dynamics.Workflow.TaskApprovalPage.aspx):

<a href="<%=this.DocumentUrl%>" id="A1" target="_blank"
     title="<%=this.WindowLinkTitle%>">
     <%=this.DocumentUrlText%></a>.

Into the below modified one:    

<a href="Dynamics.Workflow.GP.PurchaseOrderViewer.aspx?org=1&workflow=f01d80eb-d355-460c-8cb6-b2a2162a078b&PoNumber=<%=this.DocumentUrlText%>" id="A1" target="_blank"
     title="<%=this.WindowLinkTitle%>">
     <%=this.DocumentUrlText%></a>.

And it worked 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

Dynamics GP Extended Pricing Error

 

Few weeks back I have posted about this error which was repeated again yesterday with one of my customers:

Microsoft SQL Native Client SQL Server Cannot insert the value NULL into column ‘SEQNUMBR’, table dbo.IV10400, column does not allow nulls. UPDATE fails.

In my previous article I have explained everything about the issue and how it occur, this time I have decided to write a script that re-index the IV10400 table as below:

DECLARE @COUNTER INT
SET @COUNTER = 30
DECLARE CURR CURSOR FOR SELECT SEQNUMBR FROM IV10400
DECLARE @SEQNUMBR BIGINT
OPEN CURR
FETCH NEXT FROM CURR INTO @SEQNUMBR
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE IV10400 SET SEQNUMBR = @COUNTER WHERE SEQNUMBR = @SEQNUMBR
SET @COUNTER = @COUNTER + 30
FETCH NEXT FROM CURR INTO @SEQNUMBR
END
CLOSE CURR
DEALLOCATE CURR

Enjoy!

---

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

Kill All Connections to an SQL Server Database

 

Have you ever tried to restore a database using SQL 2008 and the restore failed since the database is in use? In 2005 we used to open “Detach” database and click on the hyperlink of the existing connection which will open the activity monitor and show existing connections.

Currently in SQL 2008 clicking the hyperlink will only display a message informing you that the database is currently in use without redirecting you to the connections page, and you will have to go to the activity monitor, find connections related to your database and kill them one by one.

However, I found an interesting Stored Procedure that kills all database connections

By Henry Huey's:

http://www.imiscommunity.com/sql_stored_procedure_to_kill_all_connections_to_a_database

Run the script that follows against the master db, then execute the procedure like this:
 
sp_KillSpidsByDBName MyDBName

CREATE PROCEDURE [dbo].[sp_KillSpidsByDBName] 
    @dbname sysname = ''
AS
BEGIN

-- check the input database name
IF DATALENGTH(@dbname) = 0 OR LOWER(@dbname) = 'master' OR LOWER(@dbname) = 'msdb'
    RETURN

DECLARE @sql VARCHAR(30) 
DECLARE @rowCtr INT
DECLARE @killStmts TABLE (stmt VARCHAR(30))

-- find all the SPIDs for the requested db, and create KILL statements 
--   for each of them in the @killStmts table variable
INSERT INTO @killStmts SELECT 'KILL ' + CONVERT (VARCHAR(25), spid)
    FROM master..sysprocesses pr
    INNER JOIN master..sysdatabases db
    ON pr.dbid = db.dbid
    WHERE db.name = @dbname

-- iterate through all the rows in @killStmts, executing each statement
SELECT @rowCtr = COUNT(1) FROM @killStmts
WHILE (@rowCtr > 0)
    BEGIN
        SELECT TOP(1) @sql = stmt FROM @killStmts
        EXEC (@sql)
        DELETE @killStmts WHERE stmt = @sql
        SELECT @rowCtr = COUNT(1) FROM @killStmts
    END

END

GO

---

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

Purchase Order Commitments View

I been working on a report where the customer requested to view the payment voucher with its corresponding commitments information and had a need to have Committed Amount, Actual Amount and Budget Amount, view below details all the needed information about this subject:

SELECT     ACTINDX, BUDGETAMT,  
ISNULL((SELECT     SUM(DEBITAMT - CRDTAMNT) AS Actual FROM          dbo.GL20000 
WHERE      (OPENYEAR = MAIN.YEAR1) AND (ACTINDX = MAIN.ACTINDX)), 0) AS ACTUAL,

ISNULL((SELECT     SUM(DEBITAMT - CRDTAMNT) AS Actual FROM          dbo.GL10001 
WHERE      (YEAR1 = MAIN.YEAR1) AND (ACTINDX = MAIN.ACTINDX)), 0) AS UNPOSTED,

ISNULL((SELECT     SUM(Committed_Amount) FROM         dbo.CPO10110 WHERE      
(YEAR(REQDATE)= MAIN.YEAR1) AND (ACTINDX = MAIN.ACTINDX)), 0) AS Committed_Amount

FROM         
(SELECT     YEAR1, SUM(BUDGETAMT) AS BUDGETAMT, ACTINDX FROM         dbo.GL00201 AS MASTER
WHERE     (BUDGETID = (SELECT     TOP (1) BUDGETID FROM         dbo.CPO40002 
WHERE     (YEAR1 = YEAR(GETDATE())))) GROUP BY ACTINDX, YEAR1) AS MAIN

---

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

Dynamics GP Data Level Security

 

I am getting requests about this subject almost from all my customers, therefore we at Dynamics Innovations have decided to develop an add-on for Dynamics GP to fulfill this request a month ago and my development team is currently in the process of developing the final touches on the utility.

The add-on is basically works on creating new Dynamic lookup that replaces traditional Dynamics GP lookups, the same lookup is used for all kind of data inside Dynamics GP and the rows are filter dynamically at runtime based on user privileges which to be setup by creating a specific view for each user/role per each business entity and the system will display only data from the linked view:

By achieving this, the system administrator will be able to customize columns names and columns to be displayed by using SQL views or direct SQL queries and rows to be displayed, and the lookup will automatically filter rows using "like/contains" operator based on the user selection and based on the clicked column:

The data load were enhanced to load data into .Net data grid that pulls rows from database directly into the user interface, 250,000 records were pulled within less than 7 seconds:

In addition to the lookup functionality we have developed a routine that validates user inputs inside GP texts and will make sure that the user selected data is a part of the user query:

 

The development team is currently working to enhance the navigation buttons as it might violates our security setup.

We been able to automate all the above processes and hanged with a couple of issues, the first is reports and the other is the smart list, and been able to handle smart list security by linking users view with smart list views using smart list builder, so each user will have his smart list configured, not an easy task but doable.

The last part is our challenge for the time being, we cannot figure out a solution for filtering report writer reports using available algorithms, we tried to pass restrictions fields to the reports using VBA but this wasn't a sustainable solution, we still didn't give up but we might not be able have this done.

 

UPDATE: The tool has been released and published over the company website without affecting the Reports, we left this for the IT people to manage, download your copy now from the link below:

http://www.di.jo/GPDLS.aspx

---

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

What is ERP?

Guys,

For those who attended my session last week at Applied Science University, as promised kindly find below materials I presented during the session:

http://di.jo/presentations/What is ERP.pdf

http://di.jo/presentations/Microsoft Dynamics GP 2010 Modules.pdf

---

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

Microsoft Open Door 2011

 

Kindly note that Microsoft Jordan Open Door 2011 this year is on the 1st and 2nd of November 2011.

 

Venue: Sheraton Hotel, Amman.

 

Time: 8:30 AM- 5:00 PM.

 

For registration and further information kindly follow the link below:

Open Door 2011

Event Overview

Learn about the latest IT administration and management, software development and see how to solve actual business tasks. The event is your chance to increase your knowledge, advance your skills and network with your local IT community as well as regional Microsoft and industry experts.

---

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

Microsoft Open Door Event

Guys,

For those who would like to meet and exchange business cards, I will be available at Microsoft Open Door event in Sheraton Amman - Jordan at Tuesday and Wednesday in the MVPs section to answer any inquiries you might have about Dynamics GP related issues.

Meet you there!

Regards,
--
Mohammad R. Daoud MVP - MCT
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

- Posted using BlogPress from my iPhone

Exam Preparation Engine

 

I got an invitation from uCertify team to test their exams engine, I honestly enjoyed going through the engine they built and the way they do help exams attendees to pass the exams, not by the meaning of having actual exam questions which is exist! But instead by providing all the needed information about the exam subject from materials, study notes, question and answers and so on.

Unfortunately they still do not have exams for Dynamics GP nor Dynamics ERP’s, they have exams for CRM only.

I saw the below sections and liked it:

Study Notes Section:

A section with questions and answers for terms about the material you are working on:

How Tos Section:

Information about “How To”:

Learn and Practice Section:

This section contains practice exam questions and best answers, to help exam attendees pass their exam easily:

Enjoy studding guys and do not cheat the exams!

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

Dynamics GP Workflow – Workflow is incorrectly activated

 

Looks like it is the Workflows week! I been in a situation with one of my clients who also had an issue with the workflow, their “Vendors” been suddenly start requesting for approvals once they creates a new vendor and the below message shown on the left panel:

“Unable to Connect to Remote Server”

During the investigation I noticed that the vendor workflow was not activated in the workflow system! Which was very wired situation.

While digging into this I found that the setup they had for the Workflow is incorrect, they had the Microsoft Dynamics Workflow Location is different that the Office Server Location which made this error:

Fixing this miss resolved our issue!

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

Another Visit to Jeddah – KSA

 

Guys,

I am travelling to Jeddah after a couple of hours for long 5 days, if you would like to meet and exchange business cards, I will be normally in Break-time Café most of the time and my phone number below will be available all the time.

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

Dynamics GP Workflow Backup

 

It has been a very tough month, me and all my team which now are 12 consultants in Dynamics Innovations are overloaded with tasks and projects assignments, I am travelling to Jeddah - KSA the upcoming couple of hours and wanted to post some articles before it leave my remaining rooms of my mind!

I been in a situation the last couple of days were one of my customers had a failure in their portals server that holds the Workflow and the Business Portal, after performing the reinstallation and making sure that all systems are up and running, I realized that the workflow I have created for this clients is a tree with 7 levels in depth and 900 lines of conditions that checks the cost center for each purchase order line and redirect the purchase order for the concerned department manager for first line approval! In addition, imagine the documents that will be lost during the maintenance procedure and the documents that will be stuck between the workflow and Dynamics GP!

I been lucky having a planned backup performed on daily basis, where I been able to restore the backup and proceed with the production environment smoothly.

Worth to mention that when I used the backup set I had for the Workflow database it failed to launch the applications! I had to restore the entire web application from the SharePoint Central Administration portal to get this done.

I wanted to share the script I used to schedule the daily backup, it is basically a batch file that will be called by Windows Scheduler daily and will command the STSADM to perform a backup to specific folder, that will be automatically created based on the backup date, replace “<servername>” with your actual server name and “<port>” with your actual port:

@ECHO OFF
@SET STSADM="c:\program files\common files\microsoft shared\web server extensions\12\bin\stsadm.exe"
for /F "tokens=1-4 delims=/- " %%A in ('date/T') do set DATE=%%B%%C%%D
for /F "tokens=1-4 delims=:., " %%a in ('time/T') do set TIME=%%a%%b%%c

echo Workflow Backup Operation Started....

%STSADM% -o backup -url http://<servername>:<port>/sites/DynamicsGPworkflow -filename "C:\WSS Backups\Daily Backups\Workflow\Workflow_%DATE%_%TIME%.dat"

echo Business Portal Backup Operation Started....

%STSADM% -o backup -url http://<servername>:<port> -filename "C:\WSS Backups\Daily Backups\Business Portal\BP_%DATE%_%TIME%.dat"

echo Farm Backup Operation Started....

MD "C:\WSS Backups\Daily Backups\Farm\FARM_%DATE%_%TIME%"
%STSADM% -o backup -directory "C:\WSS Backups\Daily Backups\Farm\FARM_%DATE%_%TIME%" -backupmethod full

:End

Paste this into NOTEPAD and save it with “.CMD” extension to be an executable package and schedule the run periodically.

Enjoy!

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

Dynamics GP Workflow Installation - The selected website is not a SharePoint site

 

I been in a situation yesterday with one of my clients, they had a problem in their portals server and been getting the above message during the uninstallation of the Workflow of Dynamics GP.

The exact message was: "The selected website is not a SharePoint site", but if you open the SharePoint Central Administration you finds the web application that holds the Workflow and it is indeed your Workflow web application.

While digging deeply into this, I noticed that the web applications for Workflows are being identified by unique identifier instead of using the web application name, so the mentioned case might became due to an operation that you have deleted the SharePoint web application from SharePoint Central Administration which workflow installed, and re-added the web application with the same name and same configuration, even with the same data as I have restored the web application from a SharePoint backup I had earlier.

The only way to resolve this is by forcing the workflows to be uninstalled without validating the SharePoint, to do this you will need to download Windows Installer Cleanup Utility which could be downloaded from here, and uninstall the workflow application by clicking on uninstall, this will remove the information of the workflow from your system but will not delete workflow files, normally workflow files will be replaced after doing the reinstallation, but the only thing that will have to be removed manually is the registered workflows and features that was deployed over SharePoint, this will need to be done by running following commands in CMD:

cd C:\Program Files\Common files\Microsoft shared\Web server extensions\12\Bin\         
stsadm -o deactivatefeature -name DynamicsApproval -url <DynamicsGPWorkflow URL> –force
stsadm -o deactivatefeature -name DynamicsWorkflowForms -url <DynamicsGPWorkflow URL> -force
stsadm -o deactivatefeature -name DynamicsWorkflow -url <DynamicsGPWorkflow URL> -force
stsadm -o uninstallfeature -name DynamicsApproval -force
stsadm -o uninstallfeature -name DynamicsWorkflowForms -force
stsadm -o uninstallfeature -name DynamicsWorkflow -force

Replace the <DynamicsGPWorkflow URL> placeholder with the path of the DynamicsGPWorkflow site collection. For example, replace the placeholder with the following path:

http:// Server_name /Sites/DynamicsGPWorkflow

Finally you’ll be able to reinstall the workflow with no issues.

Enjoy!

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

PO Receiving Hangs During Posting

 

Sometimes I feel like an idiot while standing behind such a strange issue! I been in a situation with one of my clients where they have a rare case that occur once a week or once every two weeks.

When they post the Receiving Batch, the posting screen is shown and never finalize posting, GP remain responding and the user can work, process monitor has nothing pending and everything looks like the transaction was successfully posted.

When returning back to the Receiving screen you will find that the transaction remain un-posted, posting it again will return tons of errors that duplicate document numbers are exist.

Investigating this issue further shown that transaction has the below impact:

1. GL Batch was created!

2. Payables invoice was created.

3. Receiving Posted Transactions Table Header (POP30300) is affected while Receiving Posted Transactions Line Items Table (POP30330) was not.

4. Inventory Cost Layers Table (IV10200) was partially affected.

5. Inventory Transactions History Table (IV30300) was partially affected.

Client used to perform manual operation like creating an inventory adjustment with differences to fix this miss, but this actually will cover the issue but will not correct the transaction.

Current installation with existing customizations done internally does not allow for application reinstallation specially since the client is using terminal services environment, therefore we been unable to identify the actual reason behind this issue which most probably due to a corrupted dictionaries that cause such an error.

the good thing is I gave them the script below which will remove the effect of posting the transaction from the inventory and allow to repost the transaction which will be posted correctly on the second try! For sure they will still have to manually void the AP invoice and will need to manually delete the GL Journal.

 

CREATE PROCEDURE FIXPOHANG (

@POPRCTNM VARCHAR(500)

)

AS

DECLARE @BACHNUMB VARCHAR(500)

SELECT @BACHNUMB = BACHNUMB FROM POP30300

DELETE FROM POP30300

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30310

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30330

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30700

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30390

WHERE POPRCTNM = @POPRCTNM

DELETE FROM IV30300

WHERE DOCNUMBR = @POPRCTNM

DELETE FROM IV10200

WHERE RCPTNMBR = @POPRCTNM

DELETE FROM SEE30303

WHERE DOCNUMBR = @POPRCTNM

DELETE FROM DYNAMICS..SY00800

WHERE BACHNUMB= @BACHNUMB

DELETE FROM DYNAMICS..SY00801

UPDATE SY00500 SET MKDTOPST='0', BCHSTTUS='0', USERID=''

WHERE BACHNUMB = @BACHNUMB

 

Hope that this helps people have such an issue.

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

Inventory Reset Utility– HITB Did not match Staging Report and actual GL Journals!

 

While doing the inventory reset for one of my clients, I shocked when finalizing the last step when my Historical Inventory Trial Balance did not match the General Ledger upon finalizing the journals posting which cannot be correct, we have initiated the reset process from the beginning to match GL to Inventory but been able to achieve this.

I did the reset for many clients so far without having any issues, however the client has huge number of daily transactions, around 2.5 records in IV30300 per year and has some issues in uncompleted transactions and posting interruption, but this does not represent the fact that the reset utility much handle such an issue.

While investigating the issue further I have found that many transactions in SEE30303 does not have corresponding GL Journals and found that those transactions are actually duplicated, I used the script below to clear all transactions from SEE30303 that does not have corresponding Journal and managed to tie both numbers:

DELETE FROM SEE30303 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL20000)

It worked! Hope that will help the community.

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

Vendor Notifications on EFT Bank Transfer

 

I got a request from one of my clients to notify vendors by mail once they release his payment to the EFT bank, where I had to create a trigger on CM20202 to monitor payments and send the mail to the vendor, below the script I used:

Create TRIGGER [dbo].[SendVendorMails]
   ON  [dbo].[CM20202]
   FOR INSERT
AS
BEGIN
 
DECLARE @MAILPROFILE VARCHAR(8000)
DECLARE @ToMAIL  VARCHAR(8000)
DECLARE @MESSAGE     VARCHAR(8000)
DECLARE @HEADER         VARCHAR(8000)

SET @HEADER = 'Payment Transfer'
SET @MESSAGE = 'Dear Esteemed Vendor,' + char(10) + char(10)

+ 'Kindly be advised that we have processed payment with the amount of (' + CONVERT(VARCHAR(500), (SELECT [Checkbook_Amount] FROM INSERTED)) + ') to your account. ' + char(10) + char(10)
+ 'Your kind confirmation of subject payment to the following email is highly appreciated (payables@XXXX.com)' + char(10)
+ char(10)
+ char(10)
+ 'Regards,' + char(10) + char(10)
+ 'Accounts Payable Unit' + char(10)

SELECT @ToMAIL = COMMENT1 FROM PM00200 WHERE VENDORID = (SELECT [CustomerVendor_ID] FROM INSERTED)
SET @MAILPROFILE        = 'Administrator'
EXEC msdb.dbo.sp_send_dbmail
@Profile_Name = @MAILPROFILE,
@recipients = @ToMAIL,
@body = @MESSAGE,
@subject = @HEADER;

END

Note: the vendor e-mail address was saved in COMMENT1 field in the vendor card, you can modify the query to pull vendor mail from different field.

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

Inventory Reset - Average Item receipt QTY on hand does not equal IV QTY on hand.

 

As a part of the inventory reset utility you might get an issue with “Run Data Checks” step, that the Average Item Receipt Quantity On Hand (Table IV10200) does not equal Inventory Quantity On Hand (Table IV00102), this is really confusing specially after doing a full inventory reconcile as a part of the inventory reset process.

While investigating this I found indeed that Quantity at IV00102 does not match the IV10200 quantity and the Inventory Reconcile Process does not cover this part, where I had to find an alternative method to proceed.

On of the community users did face this issue and posted this question on one of the community portal and got an answer from WAQAS who went through the stored procedures of the reset tool and extracted a query that detects variances between IV10200 and IV00102, I have used this query to create the below cursor to collect and fix differences automatically:

DECLARE @ITEMNUMBER VARCHAR(500)

DECLARE @DEXROWID BIGINT

DECLARE QTYFIX CURSOR FOR

SELECT 

A.DEX_ROW_ID,

A.ITEMNMBR 

FROM IV10200 A 

INNER JOIN IV00101 B ON A.ITEMNMBR = B.ITEMNMBR

INNER JOIN IV00102 C ON A.ITEMNMBR = C.ITEMNMBR

WHERE B.VCTNMTHD = 3 AND A.QTYTYPE = 1 AND C.LOCNCODE = '' 

AND A.QTYONHND <> C.QTYONHND

AND EXISTS (SELECT 1 FROM IV10200 WHERE QTYTYPE = 1 AND ITEMNMBR = B.ITEMNMBR 

AND VCTNMTHD <> 0

GROUP BY ITEMNMBR HAVING MAX(DATERECD) = A.DATERECD)

AND EXISTS (SELECT 1 FROM IV10200 WHERE QTYTYPE = 1 AND ITEMNMBR =B.ITEMNMBR 

AND DATERECD = A.DATERECD AND VCTNMTHD <> 0

GROUP BY ITEMNMBR, DATERECD HAVING MAX(RCTSEQNM) = A.RCTSEQNM)

AND EXISTS (SELECT 1 FROM IV10200

WHERE QTYTYPE = 1 AND ITEMNMBR = B.ITEMNMBR AND DATERECD = A.DATERECD 

AND RCTSEQNM = A.RCTSEQNM 

AND VCTNMTHD <> 0

GROUP BY ITEMNMBR, DATERECD, RCTSEQNM HAVING MAX(DEX_ROW_ID) = A.DEX_ROW_ID)

OPEN QTYFIX

FETCH NEXT FROM QTYFIX INTO @DEXROWID, @ITEMNUMBER

WHILE @@FETCH_STATUS = 0 

BEGIN

UPDATE IV10200 SET QTYONHND=(SELECT QTYONHND FROM IV00102 WHERE ITEMNMBR=@ITEMNUMBER AND RCRDTYPE='1') WHERE DEX_ROW_ID= @DEXROWID

FETCH NEXT FROM QTYFIX INTO @DEXROWID, @ITEMNUMBER

END

CLOSE QTYFIX

DEALLOCATE QTYFIX

Enjoy!

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

Recovered!

 

Last couple of months, I been very very very much engaged, mainly with the below:

1. Did huge enhancements to my house, it took me around 21 days to finalize.

2. Setup new offices for my Dynamics Innovations www.di.jo, it took years!

3. Upgraded 5 clients to Dynamics GP 2010 and enhanced their environment by implementing Portals, Workflows and Reporting.

4. Went a trip around the Middle East for some clients (Bahrain, Lebanon, Saudi Arabia Jeddah and Makah and finally Syria)

5. Did a big task in reconciling one of my clients inventories to General Ledger along with identifying reasons behind having such a difference which was the nightmare.

6. Answered around 9 RFP’s.

7. Did more than 20 presentations for Microsoft Dynamics GP in my home country, 3 in Lebanon and 1 presentation in Bahrain.

I feel guilty not being an active blogger for the last couple of months, but will make sure to make it up for blog follower in the upcoming days!

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

Dynamics Innovations @ Dead Sea

 

We got a visit from one of our foreign customers and decided to take Thursday as a vacation day for all the company staff! We went to the Dead Sea SPA Hotel and enjoyed amazing three days of hot swimming in the hotel pools and the dead sea itself.

Just wanted to share a shot:

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

Jivtesh Singh–New Dynamics GP MVP

 

I was a great news when I noticed an article from Jivtesh with the subject of “Good News”!! He was granted the MVP award that he deserves, he was always available for the community and earned the award for his usual contributions.

Congrats Jivtesh Singh, wish you all the best!

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

Time Estimation for Packages Installation

 

Sometimes I like the way packages estimates the time required to install! Today I have installed Service Pack 2 for Dynamics GP 2010 R2 Workflow and got the below message:

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

Dynamics GP R2 Reports

 

Have you wondered what are the ~260 reports and KPI’s added in Dynamics GP 2010 R2? Inside Dynamics GP gave the answer, find their article here.

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

Microsoft Dynamics GP Analytical Accounting Vs. Segments

 

I found an interesting question in the community asking what is the actual difference between analytical accounting and account segmentation? Below are the details:

“What are the real advantages of using Analytical Accounting over using segments in a chart of accounts? I have configured many, many GP implementations in the past, and have always used the account structure for segmenting departments, cost centers, locations, etc. One of the nice features about the Dimensions is that they can be a Yes/No, which is helpful for one of the types of information we need to track.”

My Answer:

Basically you need to keep in mind that nothing in AA cannot be done in dimensions, but it is a matter of how and what does it take, take the below scenario as an example:

Lets say that you have 10 Expenses accounts need to be allocated over 10 cost centers, and one of the expenses accounts is related to “Cars” that requires 10 subaccounts to identify expense type and another 50 accounts to identify “Cars”, do the calculation and check how many accounts will need to be added to your chart:

· Cost Center 1>> Expense Account 1>> Car 1 >> Oil Expenses

· Cost Center 1>> Expense Account 1>> Car 1 >> Cleaning Expenses

· Cost Center 1>> Expense Account 1>> Car 1 >> Maintenance Expenses

· Cost Center 1>> Expense Account 1>> Car 2 >> Oil Expenses

· Cost Center 1>> Expense Account 1>> Car 2 >> Cleaning Expenses

· Cost Center 1>> Expense Account 1>> Car 2 >> Maintenance Expenses

10 Expenses Accounts * 10 Cost Centers * 50 Cars * 10 Cars related Expenses Types= 50,000 Account! And calculate how much it takes to add a new expense type.

In AA all what you have to do is the below:

1. Add 10 Expenses accounts to your chart.

2. Create Dimension for cars.

3. Create Dimension for expenses types.

4. Enjoy!

This is the actual difference between traditional accounting and analytical accounting.

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

Payment Document Management – Post Dated Checks

 

Few months ago, I have activated Payment Document Management module for one of my clients here in Jordan to manage their post dated checks, as they have around 200 post dated checks received daily!

The customer enjoyed working on the module and had their checks managed properly with a little limitation as below:

When you do a remittance, the system posts the bank transfer and journal entry per remittance and based on the remittance date, which make it a very hard task and might be undoable to do bank reconciliation by the end of the month.

In the normal business scenarios the customer deposits the payment document in the bank and will notice the effect the next day. However, the bank will include the payment in the statement ordered by the check due date and check by check, this makes it a very hard task to reconcile bank statement with GP specially when you have a big number of payment documents on daily basis.

After working on the module for a couple of months, they been unable to proceed and back to their old utility due to this limitation.

I wanted to share this limitation with the community to be aware that this cannot be done out of the box and would like to encourage you to vote on this to be implemented in the upcoming releases using CONNECT.

Please click on this link to vote.

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

There is no Web named /BP/Administration, The language is not supported on the server

 

When you install business portal, you might face the below error:

Feature Id: BP Home
Location: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\FEATURES\BPHome
Action: Install
Exception: The language is not supported on the server.
Stack Trace: at Microsoft.SharePoint.Library.SPRequest.CreateWeb(String bstrUrl, String bstrTitle, String bstrDescription, UInt32 nLCID, String bstrWebTemplate, Boolean bCreateUniqueWeb, Boolean bConvertIfThere, Guid& pgWebId, Guid& pgRootFolderId, Boolean bCreateSystemCatalogs)
at Microsoft.SharePoint.SPSite.CreateWeb(String strUrl, String strTitle, String strDescription, UInt32 nLCID, String strWebTemplate, Boolean bCreateUniqueSubweb, Boolean bConvertIfThere, Guid webId, Guid rootFolderId, Boolean createSystemCatalogs)
at Microsoft.SharePoint.SPSite.SPWebCollectionProvider.CreateWeb(String strWebUrl, String strTitle, String strDescription, UInt32 nLCID, String strWebTemplate, Boolean bCreateUniqueSubweb, Boolean bConvertIfThere)

Your issue is basically related to your server language, just go and change the language on the server to English / United States and enjoy!

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

New blog in town!

 

Samuel Mathew who’s Currently working as the Manager of Technology and Development division of Eclipse Computing in its US operations has started blogging about Dynamics GP, please join me in welcoming him to the blogosphere and take a look into his blog below:

http://www.smathew-gpblog.com/

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

Dynamics GP Vs. Dynamics AX

 

I been reviewing one of the questions over the internet and noticed an article at ITToolBox portal that compares Dynamics GP to Dynamics AX by “Randy Smith - President - By Design Consulting”.

The question was about a customer who already running Dynamics GP and asking what value they will gain from upgrading to AX, Mr. Smith had the below answer:

I may be a bit bias but I will throw my 2 cents into the ring. I am a Dynamics GP reseller that specializes in Process/Formula manufacturing and I am also a part owner in a product that services process manufacturing for the Dynamics GP, SL and NAV markets - Vicinity Manufacturing (http://www.vicinitymanufacturing.com/)
GP tends to be more out of the box and relative to AX not as easy to customize by the user. It has a tremendous number of ISVs (development firms with specific niche) that support the product. In general GP offers good core functionality and relies on a best of breed approach to address the unique challenges - such as process manufacturing.
From my take on things AX brings some real power and flexibility to the table in the form of customization and security. With that power comes a price. It is my experience that companies wishing to take advantage of what AX offers really need to be able to commit internal development resources to the system. The system is really written and exposed to be customized to address very specific requirements a company may possess. The investment in labor is not insignificant. It is my opinion that AX would require at least one programmer on staff if not a small team. For some organizations that is not an issue and is just part of the make up of the company. For smaller companies this becomes and issue.

My ISV organization has chosen not to go into AX at this time because GP, SL and NAV address the $5-200 million market pretty well. Additionally the functionality in non-AX products is similar to the AX product (short of the customization capabilities previously mentioned) and there are more ISV products available in GP, SL and NAV. So we are sitting on the sidelines watching the AX product mature a bit. As we do that we are seeing more and more AX functionality make it into the GP, SL and NAV products as well. That is keeping the playing field pretty level.

There are situations that AX is a really good fit - don't get me wrong. I find however that there are a number of companies that pick AX when GP, SL or NAV would have done just fine and cost significantly less to implement. The real cost difference is not in software but rather in services. My experience shows that companies not getting what they want out of GP, SL or NAV are probably not working with a strong enough VAR or one that is only partially dedicated to the GP channel. That is not always the case but it is more times then not.

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

How to add attachments to Purchase Order Workflow?

 

I got a request from one of my customers to add attachments to Purchase Order Approval and I thought this might be useful for others, follow steps below to add attachments:

1. Click on Documents in Workflow:

2. Click on any document Title:

3. Click on Edit Item:

4. Click on Attach File:

5. Select file to attach:

6. Notice the attachment icon:

Enjoy!

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

Empty “Table Name” when doing Import/Export for EFT File Format

 

Have you ever tried to import and export EFT File Format and found the table name “empty”? It is actually looks like a bug in the applications, it does not import the series of the EFT file upon import and leave it as “0”.

To resolve this, go to SQL Server and open table CM00103, set the series field to “4” for purchasing and enjoy!

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

SOP Master Numbers not being assigned properly

 

A unique document number could not be found. please check setup.

I been with a situation where my customer were getting the above messages, noticed that the system is updating the master number in SOP40100 to a smaller number which sometimes being exist in SOP10100 or SOP30200.

I workaround this by creating a trigger on SOP10100 and SOP30200 to update the SOP40100 once saving or updating transactions and set the next master number to be current maximum master number plus 1!

Below the scripts I used:

CREATE TRIGGER SOP30200UPDATEMASTER
ON SOP30200
AFTER INSERT, UPDATE
AS
BEGIN

DECLARE @MASTERNUMBER BIGINT
SELECT @MASTERNUMBER = ISNULL(MAX(MSTRNUMB), 0) FROM
(SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP10100
UNION ALL
SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP30200) AS MASTERS

UPDATE SOP40100 SET NXTMSTNO = @MASTERNUMBER
END

GO

CREATE TRIGGER SOP30200UPDATEMASTER
ON SOP30200
AFTER INSERT, UPDATE
AS
BEGIN

DECLARE @MASTERNUMBER BIGINT
SELECT @MASTERNUMBER = ISNULL(MAX(MSTRNUMB), 0) FROM
(SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP10100
UNION ALL
SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP30200) AS MASTERS

UPDATE SOP40100 SET NXTMSTNO = @MASTERNUMBER
END

GO

Enjoy!

UPDATE: Folks at Accolade Publications, Inc has publish an article that contains a modified script to fix this issue, below is the modified script:

/****** Object: Stored Procedure dbo.sopGetMasterNumber ******/
if exists (select * from sysobjects where id = object_id('dbo.sopGetMasterNumber') and sysstat & 0xf = 4)
drop procedure dbo.sopGetMasterNumber
GO

create procedure dbo.sopGetMasterNumber
@O_iOUTMasterNumber int = NULL output,
@O_iErrorState int = NULL output
as

/*
**********************************************************************************************************
* (c) 1994 Great Plains Software, Inc.
**********************************************************************************************************
*
* PROCEDURE NAME: sopGetMasterNumber
*
* SANSCRIPT NAME: Get_Master_Number of form SOP_Entry
*
* PARAMETERS:
* @O_iOUTMasterNumber Retreived Master Number
* @O_iErrorState contains any errors that occur in this procedure
*
* DESCRIPTION:
* Returns the next number field from the given SOP_SETP record and increments
* the next number.
*
* Customization was made to look at SOP40500 to verify the NXTMSTNO is larger than existing values.
*
* TABLES:
* Table Name Access
* ========= =====
* SOP40100 Read/Write
*
* DATABASE:
* Company
*
*
* RETURN VALUE:
*
* 0 = Successful
* non-0 = Not successful
*
* REVISION HISTORY:
*
* Date Who Comments
* ------------- -------- -------------------------------------------------
* 24Jun98 msluke Initial Creation
*****************************************************************************
*/

declare @tTransaction tinyint,
@iError int,
@MaxMSTRNUMB int

/*
* Initialize variables and Output Parameters.
*/
select @O_iOUTMasterNumber = 0,
@O_iErrorState = 0

/*
* Start a transaction if the trancount is 0.
*/
if @@trancount = 0
begin
select @tTransaction = 1
begin transaction
end

/*
* Read record from SOP_SETP table within an update statement so a lock is held
* on the record until the number is updated. This will ensure that only a single
* user is reading this record at any given time.
*/
update
SOP40100 WITH (TABLOCKX,HOLDLOCK)
set
@O_iOUTMasterNumber = NXTMSTNO,
NXTMSTNO= NXTMSTNO + 1

if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
/*
* Do an additional read from SOP40500 to attempt to recover from the situation where the NXTMSTNO
* is less than or equal to the max value in SOP40500.
*/
select @MaxMSTRNUMB = max(MSTRNUMB) from SOP40500 (nolock)
if (@MaxMSTRNUMB >= @O_iOUTMasterNumber)
begin
update
SOP40100
set
@O_iOUTMasterNumber = @MaxMSTRNUMB + 1,
NXTMSTNO= @MaxMSTRNUMB + 2
if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
end

/*
* Reset next master number to 2, if master number has reached max value
* or it is zero.
*/
if (( @O_iOUTMasterNumber = 99999999) or ( @O_iOUTMasterNumber = 0)) and @O_iErrorState = 0
begin
select @O_iOUTMasterNumber = 1
update
SOP40100
set
NXTMSTNO = 2

if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
end

/*
* Determine if a rollback or commit should be executed.
*/
if @O_iErrorState <> 0
begin
select @O_iOUTMasterNumber = 0
/*
* Rollback the transaction if this procedure started it.
*/
if @tTransaction = 1
rollback transaction
end
else
begin
/*
* Commit the transaction if this procedure started it.
*/

if @tTransaction = 1
commit transaction
end

return

GO

GRANT EXECUTE ON dbo.sopGetMasterNumber TO DYNGRP
GO

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

Cannot insert the value NULL into column ‘SEQNUMBR’, table dbo.IV10400, column does not allow nulls. UPDATE fails.

 

Today, one of my customers reported the below error message whenever they try to post any kind of inventory transactions:

Microsoft SQL Native Client SQL Server Cannot insert the value NULL into column ‘SEQNUMBR’, table dbo.IV10400, column does not allow nulls. UPDATE fails.

Nothing modified at their database and the error start appearing suddenly, they are running the latest service pack for GP and running SQL Server 2005 with the latest service pack.

They followed all the known procedures trying to figure out what’s the reason behind this, but unfortunately non of the procedures helped. Worth to mention that the transaction posts properly and does not actually has any obvious issues.

I ran an SQL Server Profiler trace and tried to locate the actual reason behind the issue and noticed the problem in the Extended Pricing module.

The issue is that the customer has 50 concurrent users working on the application and they have big number of transactions daily, which lead to the fact that IV10400 table is no longer accepting new entries, the table contains a field called “SEQNUMBR” which holds the sequence number for transaction and increased by 16XXX each time a new record created.

The maximum number returned for SEQNUMBR returned was 22,000,000 which is too large to be handled by an integer data type, I had to modify the table structure for IV10400 to replace the integer data type with bigint and to update a stored procedure called “sopExtPriceBookSetup” to replace the declaration of integer variables into bigint’s, if you need help in replacing this, just let me know and I will send you the stored procedure I already modified.

Enjoy!

UPDATE: Before applying modifications on the database, I checked the linked table in dexterity and noticed that the Sequence Number is defined as long integer, that’s why I posted the article, but my customer informed me that this did not work as expected and they gets additional errors! I check the maximum number they achieved after 3 working years in the table and it was 2,700,000,000! It works as all sequence numbers in Dynamics GP by multiplying 16384 which allows only 131072 records in the table as the maximum allowed length for long integer is an integral number in the range [–2,147,483,648 to 2,147,483,647].

Therefore, I had to re-index the table to reduce the values by exporting table data to an excel sheet, renumber the SEQNUMBR field and return the data back to GP.

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

The Most Influential People in Microsoft Dynamics for 2011

 

I don’t know how the folks of DynamicsWorld evaluates the votes nor how they rank, but I am ranked #38 in the list! Below what they used to rank:

We base our selections on a number of criteria including: Number of nominations received; Number of Microsoft Dynamics employees employed; Number of clients your ISV/VAR has; As an end user how many user licences taken; Social Media Followers (bloggers/twitter/ linkedin) (bonuses for group owners); Number of years experience of Microsoft Dynamics; MVP Recipients; Developers of Add-Ons and new verticals; Speakers and writers about Microsoft Dynamics; Forum contributors and Number of votes received. We attempt to limit what can actually be described as a sphere of influence. The advantage that we have had in quantifying influence in Microsoft Dynamics is that we are dealing with a group of people with similar interests, and so it is possible to be able to quantify one persons influence over the group against another person’s influence.

Many known names were listed, Erik Ernst was listed on rank 13, while Mark Polino was ranked 16, Mariano Gomez ranked 30, Leslie Vail was ranked 68, Steve Endow was ranked 79 and Victoria Yudin was ranked 85.

Below is the complete list:

http://www.dynamicsworld.co.uk/top-100/

On the other hand, it worth to mention that I couldn’t find many other names that really have huge influence on Dynamics.

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

Cash account is not being loaded in Cash Receipt and Manual Payment if the payment type is “Check”

 

I got an interesting question from one of my blog fellows, the guy been trying to create a manual payment and cash receipt of type check, while “Cash” type work perfectly, “Check” type does not load the cash account.

I have connected to the environment and validated the accounts are properly configured, but noticed that “Payment Document Management” is installed and activated for no reason.

When having payment document management activated, application will look into the accounts in payment document setup screen linked to the checkbook master.

Your available options are either to disable the module from the database as you cannot disable it from the screens by running the below script:

UPDATE     RVLPD001

SET           EnablePmntDocPM = 0, EnablePmntDocRM = 0

Or setup accounts for your checkbook in the required screen.

This will resolve your issue!

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

eConnect Error: The argument type `SqlClient` cannot be converted into parameter type `Microsoft.Dynamics.GP.eConnect.EnumTypes+ConnectionStringType`.

 

After upgrading one of my customers from GP 10.0 to GP 2010, I noticed an error in eConnect adapter developed to integrate receiving, the error was:

The argument type `SqlClient` cannot be converted into parameter type `Microsoft.Dynamics.GP.eConnect.EnumTypes+ConnectionStringType`.

While investigating this out, I noticed that I have upgraded the eConnect on the client’s server but didn’t upgraded the .Net solution DLL to the new eConnect!

Upgrading the eConnect DLL on my solution resolved the problem!

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

Post to General Ledger has not been marked for this batch.

 

I got a call yesterday from one of my customers, they have a case that they are being notified when posting inventory transactions that the transaction will not post to GL:

“Post to General Ledger has not been marked for this batch. Analytical Accounting information that may be created for this batch will be deleted on posting. Do you want to continue?”

First things I used the customer to check is the posting setup, but they confirmed that nothing changed and posting setup is marked to post to GL.

Then I realized the they are posting a “transaction” not a “batch”, which lead to the fact that something is incorrect.

I asked them to go to SY00500 and check if there is any batches with empty batch number and that was the case! They had a batch with empty batch number exists in the table and not marked to post to GL and GP was looking into this table when posting without batches!

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

Guess What is this?

 

It is the Microsoft Dynamics GP Business Analyzer, it really an awesome utility contains tons of adjustable dashboards out of the box, can’t wait to validate all dashboards!

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

Why do I see empty charts in Fabrikam?

 

I have installed Dynamics GP 2010 R2 and really enjoyed walking through dashboards and KPIs added to the application, but most of the KPI’s does not show the actual company data, instead it is showing zeros as below:

This is actually not related to an installation issue, it is due to the fact that the report is using the current date while Fabrikam uses 2017 as the default year, to view the report with data, just click on the “View” icon and select the report:

Change the date:

And enjoy!

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

Dynamics GP 2010 R2 Business Intelligence Installation Error maxRequestLength

 

During Dynamics GP 2010 R2, I have selected to deploy Dynamics GP Business Intelligence Reports over SQL Server Reporting Services, but before starting the installation, the system generated an error that maximum number of retries has been exceeded and was requested to set a maxRequestLength flag in the web.config.

Web.config could be located under the installation path of SQL Server Reporting Services, in my case it was under the following path:

C:\Program Files\Microsoft SQL Server\MSRS10.SQL2008\Reporting Services\ReportServer

in the web.config, search for <httpRuntime executionTimeout="9000" /> and include the variable there to make it looks like the below:

<httpRuntime executionTimeout="9000" maxRequestLength="20690"/>

Somehow, 20690 in specific is required, it will not accept number higher!

Update: Below is the exact error message:

The deployment has exceeded the maximum request length allowed by the target server. Set maxRequestLength = "20690" in the web.config file and try deploying again.

Enjoy!

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

Microsoft Dynamics GP 2010 R2 Just Released!!!

 

Posting LIVE from Dynamics Confessor Blogspot!! Leslie Vail just posted the below article:

http://dynamicsconfessions.blogspot.com/2011/04/gp-2010-r2-ready-to-download.html

It’s HERE!

The covers have been pulled off of the newest version of Dynamics GP. Some fabulous new features have been added that really make it worth your time to install. Links to all of the download are at the bottom of this post.

Take a look at some of the R2 updates to Foundation alone:

Business Analyzer - this is the absolute COOLEST!

You can use Business Analyzer to help you make business decisions by viewing
Microsoft Dynamics GP reports from your desktop. The following illustration
shows Business Analyzer from your desktop.

Home Page - Viewing multiple metrics
Multiple metrics can be displayed in your home page. You also can create your own
metrics to display in your home page.

Sending reports in e-mail messages
You can send most reports in an e-mail message if you’re using a MAPI compliant email
service. All reports sent in an e-mail will be sent in HTML format, which
doesn’t require any other setup procedures. The recipient of the report must be
using Internet Explorer 7 or higher to view the e-mailed report, but no additional
setup is required.
To send a report in an e-mail, you first must set up a report option. Then, you can set
up e-mail options for each report option. Those settings, which include recipients
for the e-mail and settings for embedding or attaching the report are saved with the
report option and you’ll be able to send updated reports to the same recipients.

Sending customized messages
You can create predefined messages to send to your customers and vendors. These
messages can be assigned to the documents that you want to send in e-mail so all
customers or vendors receive the same message for selected documents. For
example, you can send a promotional message to your customers when sending
sales quotes in e-mail. You also can assign a specific message to an individual
customer or vendor. For example, you can send a holiday greeting message to a
customer. When you create a message, you can enter an e-mail address so your
customer or vendor can reply to your e-mail.

You can customize your messages by adding fields that are associated with a
document type. For example, a message can be personalized to address each
customer or vendor by name. You could add the due date for a sales invoice in a
message or the PO number for a purchase order. You also can copy the message
information from an existing message ID to a new message ID.

Viewing Reporting Services metrics
You can use Microsoft® SQL Server® Reporting Services 2008 or Reporting Services
2008 R2 to display graphical representations of Microsoft Dynamics GP data in the
metrics area of your home page. Reporting Services metrics appear in the SQL
Reporting Services Report list.

Viewing additional information
If you want to analyze data in a Reporting Services 2008 or Reporting Services R2
metric displayed in your home page, you can click a data point in the metric to open
a detailed report for additional information. If you want to view additional data in
the detailed report, you can click certain data fields to open the maintenance or
inquiry window related to that data field.

Modifying a metric – At Last!
If you are using Report Builder 3.0 or Report Builder 2.0, you can open the Microsoft
SQL Server Report Builder window from the Metrics Detail window to modify SQL
Server Reporting Services metrics and KPIs (key performance indicators). SQL
Server Reporting Services R2 also must be installed and set up to use with Microsoft
Dynamics GP.

Opening a metric in Report Viewer
If you are using Reporting Services 2008 or Reporting Services 2008 R2 to display
metrics, you can click the View icon to display a metric in Report Viewer.

Installation and deployment features
Electronic Funds Transfer (EFT) for Receivables Management is installed along with
Microsoft Dynamics GP instead of as a separately selectable Microsoft Dynamics GP
feature.

Human Resources and Payroll Suite
This is now installed as a separately selectable Microsoft Dynamics GP feature instead as an additional component.

When deploying Reporting Services reports using the Microsoft SQL Server
Reporting Services Wizard, you can deploy reports to either Native mode (SQL Server Reporting Services) or SharePoint® mode.

You can deploy Reporting Services reports and Excel reports using Microsoft
Dynamics GP Utilities. After creating the system database or company database,
you can select which reports to deploy and the location to deploy the reports to.
Instead of selecting the series that you want to deploy reports for, reports for the
components that you have installed are deployed. If you are upgrading a company,
the deployed reports are automatically upgraded. In previous releases of Microsoft
Dynamics GP, you had to use a separate wizard to deploy SRS reports and a
separate wizard to deploy Microsoft Excel reports to Microsoft SharePoint.
If you aren't ready to deploy reports using Microsoft Dynamics GP Utilities, you can
deploy reports using the Report Tools Setup window in Microsoft Dynamics GP.
You can use the Report Tools Setup window to redeploy reports or deploy reports to
a different location.

List Performance Enhancements YAY! WOO HOO!
List views provide a view of your data that you can customize in a variety of ways.
In Microsoft Dynamics GP 2010 R2, improvements were made to the speed at which
the information is displayed, enabling you to spend your time using the list rather
than waiting for it to display.

Report deployment
You can deploy SQL Services Reporting Services reports and Microsoft Excel report
using the Report Tools Setup window in Microsoft Dynamics GP. You also can use
the window to redeploy reports or deploy reports to a different location.

SmartList Builder Publish multiple Excel reports at once
You can use the new Bulk Deploy Excel Reports window and select multiple Excel
reports to publish at once, rather than publishing them individually.

SmartList Builder Set the default column order in SmartList Builder
You can define default column order using SmartList Builder so that the SmartList
is displayed with the columns in the order you want when it is first opened. A new
window, Default Column Order is added to SmartList Builder for determining the
column order.

SmartList Builder Drill Down to Extender and SmartLists
You can use the Drill Down Builder to create drill downs from Microsoft Dynamics
GP, SmartLists and Extender. A Dynamics GP Form drill down opens a Microsoft
Dynamics GP form and sets values on the form. A SmartList drill down opens a
SmartList and sets search parameters. An Extender drill down opens an Extender
Form or Detail Form and sets the values of the ID fields.

SmartList Builder Multicompany Excel Reports in a Single Excel Worksheet
You can add data from multiple companies into a single Excel Worksheet. In
addition, you can add the Company ID and/or Name to each line so that you can
see which company the data came from.

SmartList Builder Update Multiple field settings at one time
You have the ability to modify field options for multiple fields with the same field
type at once instead of one at a time. For example, you can select display options
such as currency symbols and percentage signs and mark several fields to display
the option for using the Set Field Options window.

Add Microsoft Dynamics GP forms to Extender menus
You can add Microsoft Dynamics GP forms to Extender menus using the Add
Dynamics GP Forms window allowing additional flexibility to the menus you
create.

Unified Communications and Office Communicator 2007 functionality was added. This functionality is also available using the Linked Lookup fields on Extender windows and forms, detailed forms, and extra windows.

Copy lists in Extender
You can copy list field items from one form or window to another one. The Copy
List Items window shows all the lists defined in Extender, so you can choose which
list to copy from; then, select all or individual list items to copy. In addition, you can
append the list you copy to the existing list items.

Matched Table Enhancements
In the Matched Tables window, you have new Table Description fields to help
identify the tables, and the ability to include fields that don't appear in both
matched tables.

GET YOUR COPY NOW!

PartnerSource
Product Release Downloads for Microsoft Dynamics GP 2010
Word Template Generator for Microsoft Dynamics GP 2010 R2
Service Pack, Hotfix, and Compliance Update Patch Releases for Microsoft Dynamics GP 2010
Software Development Kit (SDK) for Microsoft Dynamics GP 2010
Software Development Kit (SDK) for Visual Studio Tools for Microsoft Dynamics GP 2010
Software Development Kit (SDK) for Web Services for Microsoft Dynamics GP 2010
Software Development Kit (SDK) for Workflow for Microsoft Dynamics GP 2010
Service Packs and Hotfixes for eConnect for Microsoft Dynamics GP 2010
Service Packs and Hotfixes for Web Services for Microsoft Dynamics GP 2010
Service Packs and Hotfixes for Integration Manager for Microsoft Dynamics GP 2010
Service Packs and Hotfixes for Workflow for Microsoft Dynamics GP 2010
Service Packs and Hotfixes for Personal Data Keeper for Microsoft Dynamics GP 2010

CustomerSource
Product Release Downloads for Microsoft Dynamics GP 2010
Service Pack, Hotfix, and Compliance Update Patch Releases for Microsoft Dynamics GP 2010
Software Development Kit (SDK) for Microsoft Dynamics GP 2010
Software Development Kit (SDK) for Visual Studio Tools for Microsoft Dynamics GP 2010
Software Development Kit (SDK) for Web Services for Microsoft Dynamics GP 2010
Software Development Kit (SDK) for Workflow for Microsoft Dynamics GP 2010
Service Packs and Hotfixes for eConnect for Microsoft Dynamics GP 2010
Service Packs and Hotfixes for Web Services for Microsoft Dynamics GP 2010
Service Packs and Hotfixes for Integration Manager for Microsoft Dynamics GP 2010
Service Packs and Hotfixes for Workflow for Microsoft Dynamics GP 2010
Service Packs and Hotfixes for Personal Data Keeper for Microsoft Dynamics GP 2010

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