Get Serial Number Count and Length in Microsoft Dynamics GP

Microsoft Dynamics GPI needed to check how many serial numbers were assigned to sales transactions and the cumulative length of them. I’m not aware of any function in Microsoft Dynamics GP which would give me this information, so I wrote the below SQL statement to return both values along with the transaction type and document number.

It selects from the Sales Serial/Lot Work and History (SOP10201) table which, allowing for a simple statement, includes both the work and history data (SOP transactions don’t have a status of open).

/*
Created by Ian Grieve of azurecurve|Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT SOP.SOPNUMBE AS 'SOP Number' ,SOP.SOPTYPE AS 'SOP Type' ,SOP.LNITMSEQ AS 'Line Item Sequence' ,COUNT(1) AS 'Serial Number Count' ,SUM(LEN(SOP.SERLTNUM)) AS 'Serial Number Length' FROM SOP10201 AS SOP -- Sales Serial/Lot Work and History (SOP10201) GROUP BY SOP.SOPNUMBE ,SOP.SOPTYPE ,SOP.LNITMSEQ ORDER BY SUM(LEN(SOP.SERLTNUM)) DESC

More Efficient Grant Script for Microsoft Dynamics GP

Microsoft Dynamics GPI’m not sure how, but I stumbled across an article on the ERP Software Blog by Michael Krasivsky of The Resource Group where he has posted a more efficient version of the Grant.sql script.

For those who don’t know, the Grant script is used to apply the correct permissions to SQL objects (tables, views and stored procedures) used by Microsoft Dynamics GP; this script can take a while to run as it utilises a cursor to update the permissions on every single SQL objects in the database regardless of the current permissions.

What Michael has done is create a script which only selects the SQL objects which do not have the correct permissions and grant them on those objects only; this makes the script much faster to run, as in most cases the majority of the SQL objects will already have the correct permissions so it is only a minority of objects which need to be updated.

Find SQL View in All Microsoft Dynamics GP Databases

Microsoft Dynamics GPI recently needed to find which databases in SQL Server had a specific view deployed to them. I’ve created scripts in the past to find SQL objects in all databases (table,trigger and functions), but not one for SQL views.

Some of the previous scripts looked in all databases and others were limited to only the Microsoft Dynamics GP databases; this script is one of the latter, using the SY015000 table to only search for a SQL view in the Dynamics GP databases:

/*
Created by Ian Grieve of azurecurve|Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
DECLARE @command NVARCHAR(MAX) DECLARE @SystemDatabase VARCHAR(15) = 'D20' DECLARE @View VARCHAR(50) = 'uv_AZRCRV' CREATE TABLE #ReturnedData( DBNAME VARCHAR(15) ,VIEWNAME VARCHAR(100) ) SELECT @command = 'IF EXISTS (SELECT 1 FROM sys.databases AS dbs LEFT JOIN ' + @SystemDatabase + '..SY01500 SY ON SY.INTERID = dbs.name WHERE dbs.name = ''?'' AND (dbs.name = ''' + @SystemDatabase + ''' OR SY.INTERID IS NOT NULL)) BEGIN USE [?]; INSERT INTO #ReturnedData (dbname, VIEWNAME) (SELECT DB_NAME() AS ''DB_NAME'', o.name FROM sys.objects AS o WHERE o.type = ''V'' AND o.name LIKE ''' + @View + '%'') END' EXEC sp_MSforeachdb @command SELECT * FROM #ReturnedData DROP TABLE #ReturnedData

Error Sending Mail Using SQL Database Mail

Microsoft SQL ServerI was recently doing some testing of a script I created for a client which used SQL Database Mail for sending emails. this was on my one of my demo environments, but I received an error when testing the script:

Error message

Microsoft Dynamics GP

Unhandled database exception: [Microsoft][SQL Server Native Client 11.0][SQL Server]The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

The solution I found was to grant permissions to public, which did work, but I’m not convinced this would be an approach to take on a clients live system:

GRANT EXECUTE on sp_send_dbmail TO public

Update EFT Transfer Method on Creditor EFT Details in Microsoft Dynamics GP

Microsoft Dynamics GPI was doing some work with a client recently where we needed to output two different formats of EFT file for the same bank account. This is possible using the EFT Transfer Method on the bank on the creditors. However, with a large number of creditors we needed a way to update the records in bulk. While it would be possible to do this with a macro, it would be fiddly and take a while.

Instead, I created a SQL script which can be run to update the Address Electronic Funds Transfer Master (SY06000) table:

/*
Created by Ian Grieve of azurecurve|Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
UPDATE ['Address Electronic Funds Transfer Master'] SET EFTTransferMethod = 2 /* 1=Not Specified 2=Business Account 3=Corporate Account 4=Personal Account 5=Foreign Account */ FROM SY06000 AS ['Address Electronic Funds Transfer Master'] WHERE SERIES = 4 AND EFTBankType = 3 /* 2 = Ireland 3 = United Kingdom 26 = Other 1 27 = Other 2 30 = Canada 31 = United States */ GO

The script is currently set up to update only UK banks and to set the EFT Transfer Method to Business Account. The green comments show some of the other values which you can use.

If you use the script, make sure you have a good backup before.

Select Microsoft Dynamics GP GL Accounts for Import

Microsoft Dynamics GPWorking on a recent project for a client we needed to extract the chart of accounts from one Microsoft Dynamics GP company and import it into a couple of others (altering the first segment during the process. The below script was created to extract all of the chart of account data in the format required for import through Integration Manager:


SELECT
	['Account Index Master'].ACTNUMST AS 'Account Number'
	,['Account Master'].ACTDESCR AS 'Account Description'
	,CASE WHEN ['Account Master'].ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END AS 'Active'
	,['Account Master'].ACCTENTR AS 'Allow Account Entry'
	,['Account Master'].ACTALIAS AS 'Account Alias'
	,['Account Category Master'].ACCATDSC AS 'Account Category Description'
	,CASE WHEN ['Account Master'].PSTNGTYP = 0 THEN 'Balance Sheet' ELSE 'Profit and Loss' END AS 'Posting Type'
	,CASE WHEN ['Account Master'].TPCLBLNC = 0 THEN 'Debit' ELSE 'Credit' END AS 'Typical Balance'
	,['Account Master'].USERDEF1 AS 'User Defined 1'
	,['Account Master'].USERDEF2 AS 'User Defined 2'
	,['Account Master'].USRDEFS1 AS 'User Defined 3'
	,['Account Master'].USRDEFS2 AS 'User Defined 4'
FROM
	GL00100 AS ['Account Master']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
INNER JOIN
	GL00102 AS ['Account Category Master']
		ON
			['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM[

Assign a Microsoft SQL Server Role to a User in All Microsoft Dynamics GP Databases

Microsoft SQL ServerI did some work for a client recently which involved creating a report which could be run against any database; we therefore needed to add the user to a database role in all databases which gave access to the relevant SQL objects.

The below script generates a SQL script which can be used to alter the role to assign it to the specified user in all of the Microsoft Dynamics GP company databases.

The script assumes the user already has a server login and that the role exists in all databases:

DECLARE @DatabaseRole VARCHAR(140) = 'db_reports'
DECLARE @Username VARCHAR(140) = 'AZRCRV\iang'

SELECT 'USE [' + RTRIM(INTERID) + ']
GO
ALTER ROLE [' + @DatabaseRole + '] ADD MEMBER [' + @Username + ']
GO'
FROM
	SY01500 AS ['Company Master']
GO

Enable Workflow Step Email Notifications in Microsoft Dynamics GP

Microsoft Dynamics GPI was doing some work for a client to build a workflow process in Microsoft Dynamics GP recently and we only got the SMTP details quite late in the process. This meant that we built quite a large workflow process without being able to enable the email notifications. Once we did have the SMTP available we needed to go back through all of the steps and enable the email notifications.

Rather than do this manually, which would have been a time consuming process, I looked at the tables and identified the table and fields which would need to be updated. This was the Workflow Step Table (WF100003) table; the below script can be used to enable the notifications, set the Message ID and enable the include Document Attachments (this was for a payables batch approval process):

/*
Created by Ian Grieve of azurecurve|Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
UPDATE WF100003 SET EmailMessageID = '^ASSIGN PO APPROVAL' ,Workflow_Step_Send_Email = 1 ,WFIncludeDocumentAttach = 1 WHERE Workflow_Step_Send_Email = 0

As always with scripts, make sure you have a good backup and test before using on a live system.

Script to Help Check For Missing Microsoft Dynamics GP Workflow Customisations

Microsoft Dynamics GPIf you’re a regular reader, you’ll be aware that I am a big fan of the Microsoft Dynamics GP Workflow module which can be used for the approval of batches, documents, security and more. Workflow can easily be customised to allow for extra fields to be available in the workflow conditions and on the notification emails (which I cover in chapters 9 and 11 of my Microsoft Dynamics GP Workflow (3rd Edition) book.

One issue with making these changes is that they can be removed or overwritten when you upgrade Microsoft Dynamics GP. The following script can be used to help check the three tables in which this can have an impact; the way I work is that I export the tables before running the upgrade and then again afterwards to see what the changes are. If any of the customisations are gone, I determine the best way of adding them back.

The highlighted section is the Workflow Type Name which you want to verify.

/*
Created by Ian Grieve of azurecurve|Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
DECLARE @WorkflowTypeName VARCHAR(51) = 'Purchase Requisition Approval' DECLARE @FieldsListGuid VARCHAR(37) = (SELECT FieldsListGuid FROM dbo.WF100001 WHERE Workflow_Type_Name = @WorkflowTypeName) -- Query Designer Fields List SELECT * FROM CO00121 WHERE FieldsListGuid = @FieldsListGuid ORDER BY SEQNUMBR -- QueryDesigner_Relationships SELECT * FROM CO00122 WHERE FieldsListGuid = @FieldsListGuid ORDER BY SEQNUMBR -- Workflow Template Fields - Workflow Assignment SELECT * FROM WF40202 WHERE Workflow_Type_Name = @WorkflowTypeName AND Email_Message_Type = 2 ORDER BY SEQNUMBR -- Workflow Template Fields - Workflow Action Completed SELECT * FROM WF40202 WHERE Workflow_Type_Name = @WorkflowTypeName AND Email_Message_Type = 3 ORDER BY SEQNUMBR

Script to Remove Microsoft Dynamics Workflow History Where No Action Is Required

Microsoft Dynamics GPI have quite a few clients using the Microsoft Dynamics GP Workflow module as standard out of the box with no customisations required, but a fair number of clients do have one customisation or another.

A few of them have a customisation allowing a SQL view to be used in the workflow conditions and a few have a script like the one in this post to remove some of the workflow history.

If you have a workflow process which branches off so not all steps are followed, then a record is record in the history as “no action is required”; if you have a large workflow process, this can result in many history records being created which can significantly slow down some of the handling of workflow in the system (such as viewing history).

I took a look at the data and after some testing determine that entries in two tables could be removed without causing any issues and would allow the workflow history to be viewable.

The tables are Workflow Step Instance Table (WFI10003) and Workflow History (WF30100); in theory this could be set up as a trigger, but after discussion with the client we created it as a stored procedure which runs on a schedule in SQL Server Agent:

/*
Created by Ian Grieve of azurecurve|Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
CREATE PROCEDURE usp_AZRCRV_DeleteWorkflowHistoryNoActionIsRequired AS -- Delete from Workflow Step Instance the lines which do not require approval DELETE FROM WFI10003 -- Workflow Step Instance Table (WFI10003) WHERE Workflow_Step_Status = 1 -- Delete from Workflow History the lines which do not require approval DELETE FROM WF30100 -- Workflow History (WF30100) WHERE Workflow_Comments LIKE 'No approval is required%' GO