SQL Script Linking Purchase Orders to Purchase Requisitions

Microsoft Dynamics GPI have been doing a lot of work recently on Purchase Order Processing and Workflow 2.0. One of the requests I had was for a SmartList report which allowed the purchase order (PO) to be compared back to the purchase requisition (PR) it originated from. I had to take a few minutes to investigate as I knew the link from PR to PO was maintained within the system, but I wasn’t sure of the table.

It took me a little longer to determine which table held the link as I was expecting a company table, but instead found that the link was stored in the SOP_POPLink table (SOP60100). One fairly quick view later and I have the basis for the required report, and probably for some others in future too.

Continue reading “SQL Script Linking Purchase Orders to Purchase Requisitions”

You Cannot Print An Unauthorized Purchase Order

Microsoft Dynamics GPThe below error message was reported to me by a user performing UAT (User Acceptance Testing) on an implementation of Purchase Order Processing and Workflow 2.0:

Microsoft Dynamics GP - You cannot print an unauthorized purchase order

Microsoft Dynamics GP

You cannot print an unauthorized purchase order

This error was somewhat surprising as it was produced when trying to print a purchase order (PO) which had been converted from a purchase requisition (PR). There was a workflow process on the PR, which had been final approved and the PO created, but there was not a workflow on the PO.

I double checked to make sure that the old Purchase Order Enhancements (POE) Approvals functionality was disabled (it was) as the customer had previously been using this.

It was when I was checking for POE Approvals, that I realised that, as part of the project, we had enabled the PO Commitments part of POE.

The warning message was being produced as the PO line had exceeded the commitment budget value. Loading the PO, clicking the save button, accepting the override of the commitment budget value, reloading the PO and clicking the Print button successfully printed the PO.

SQL Function To Return Workflow Approval Status

Microsoft Dynamics GPIf you have been paying any sort of attention, you will know that I do quite a lot of work with the Workflow 2.0 module of Microsoft Dynamics GP.

We have created a number of reports in the past which allows the status of transactions or card going through a Workflow Approval process; to simplify matters somewhat, I created a SQL function which can be called with the Workflow Type Name and the Workflow Business Object Key and have the approval status returned.

CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalStatus(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(200))
	RETURNS VARCHAR(14)
AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).

Returns Workflow Approval status of a specified workflow item.

Requires input parameters of WorkflowTypeName and WfBusObjKey

Valid Workflow Type Names are (as of Microsoft Dynamics GP 2016 R2):
	General Ledger Batch Approval
	Receivables Batch Approval
	Payables Batch Approval
	Payables Transaction Approval
	Purchase Order Approval
	Purchase Requisition Approval
	Vendor Approval
	Employee Profile Approval
	Employee Skills Approval
	Payroll Direct Deposit Approval
	Payroll Timecard Approval
	Payroll W4 Approval
	Expense Report Approval
	Timesheet Approval
	Smartlist Designer View Approval
*/
BEGIN
	RETURN ISNULL((
		SELECT TOP 1 
			CASE WHEN ['Workflow History'].Workflow_Action = 1 THEN
				'Submitted'
			WHEN ['Workflow History'].Workflow_Action = 2 THEN
				'Resubmitted'
			WHEN ['Workflow History'].Workflow_Action = 3  THEN
				'Approved'
			WHEN ['Workflow History'].Workflow_Action = 4 THEN
				'Task Complete'
			WHEN ['Workflow History'].Workflow_Action = 5 THEN
				'Rejected'
			WHEN ['Workflow History'].Workflow_Action = 6 THEN
				'Delegated'
			WHEN ['Workflow History'].Workflow_Action = 7 THEN
				'Recalled'
			WHEN ['Workflow History'].Workflow_Action = 8 THEN
				'Escalated'
			WHEN ['Workflow History'].Workflow_Action = 9 THEN
				'Edit'
			WHEN ['Workflow History'].Workflow_Action = 10 THEN
				'Final Approved'
			END
		FROM 
			WF30100 AS ['Workflow History']
		INNER JOIN
			WFI10002 AS ['Workflow Master']
				ON
					['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
		WHERE
			['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName
		AND
			['Workflow Master'].WfBusObjKey = @WfBusObjKey
		AND
			['Workflow History'].Workflow_Action <> 11
		ORDER BY
			['Workflow History'].DEX_ROW_ID DESC)
	,'Not Submitted')
END
GO

GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalStatus TO DYNGRP
GO

This view can be called from a SmartList created using either SmartList Designer, SmartList Builder, via a Reporting Services or Excel Report or any other type of report.

Updated 09/02/2022 – Added the highlighted clause which is needed to filter out no action required status after final approval (thanks to Calum for the reminder).

AD Group Names Have Maximum Lengths If Used in Workflow 2.0

Microsoft Dynamics GPI am working with a few different clients on projects to implement Workflow 2.0 and came across an issue with one of them when selecting an AD Group as an approver. I was able to reproduce the issue on my demo machine so knew it was not a client environment specific issue.

When the lookup was performed on the group name, the group was returned, but the login name was truncated:

Workflow User Selection

Continue reading “AD Group Names Have Maximum Lengths If Used in Workflow 2.0”

Workflow 2.0 Requires Display Names for AD Groups Used as Approver

Microsoft Dynamics GPMicrosoft Dynamics GP supports the use of both Active Directory Groups and Users as approvers, but there is a minor catch in the creation of groups which it is easy to stumble over. If you create the group as usual via the Active Directory Users and Computers applet you set a Group name and Group name (pre-Windows 2000):

Active Directory Users and Computers - New Object Group

Continue reading “Workflow 2.0 Requires Display Names for AD Groups Used as Approver”

eConnect Incoming Service Won’t Start

Microsoft Dynamics GPeConnect is both a very useful tool for integrating data into Microsoft Dynamics GP, and also hugely frustrating. I assisted a client in creating a new test system recently and had some problems with eConnect. I installed it as normal, but the service didn’t start; when I tried to start the service manually, it started and immediately stopped without showing an error message.

I checked in the Windows Event Viewer for any errors and, again, there were none.

I’ve previously had issues with the eConnect service not starting and I figured it was worth a try.

The post linked above, has step by step screenshots, but the gist of what I tried is as follows:

  1. Make the eConnect service account an admin in Local Users and Groups.
  2. Start the service and note that it started correctly and continued to run.
  3. Stop the service
  4. Remove the eConnect Service from the Admins group.
  5. Start the service and again note that it started and continued to run.

The service has continued to run correctly since then.

When exec wfDeployCLRAssemblies Just Isn’t Enough

Microsoft Dynamics GPI’ve posted a few times about errors in Workflow requiring the wfDeployCLRAssemblies stored procedure to be run again (1 2 3), but the Dynamics GP Support and Services Blog has posted an article about how this is sometimes not sufficient to resolve the problem and the procedures, functions and assemblies deployed by the wfDeployCLRAssemblies stored procedure need to be deleted.

To this end, the blog post contains a series of scripts which need to be run before thew wfDeployCLRAssemblies stored procedure is executed. There are versions of the script for Microsoft Dynamics 2013 R2, 2015 (including 2015 R2) and 2016 (including 2016 R2).

If your problem still isn’t fixed, then a support case would need to be raised.

Dexterity Development ‘Unhandled script exception’ on data area

Microsoft Dynamics GPI’ve been doing some work recently on upgrading some Dexterity code to work with Microsoft Dynamics GP 2016 (both the RTM and R2). After compiling the new dictionary, I started Microsoft Dynamics GP and as the home page was loading got the following error:

MIS_AMS_Ext_160.dic - Unhandled script exception: SCRIPTS - data area. EXCEPTION_CLASS_SCRIPT_MISSING

MIS_AMS_Ext_160.dic

Unhandled script exception:
SCRIPTS - data area

EXCEPTION_CLASS_SCRIPT_MISSING

Continue reading “Dexterity Development ‘Unhandled script exception’ on data area”

Error Running Dexterity in Debug Mode

Microsoft Dynamics GPI was working on upgrading some Dexterity code a little while ago and encountered a problem in the code. I tried to enter debug mode and received a message that Utilities for the custom dictionary needed to be run:

You need to run MIS_AMS_Ext_160.dic Utilities before you can run MIS_AMS_Ext_160.dic. Do you want to launch MIS_AMS_Ext_160.dic Utilities now?

I spent a little while looking into why this was happening and found a few posts saying to copy the Dex.ini file into a Data folder in the dictionary location, but this had no effect.

Instead the Dex.ini file should be copied into a folder under the install location of Dexterity, which for Dexterity 16 (Microsoft Dynamics GP 2016) is, by default:

%ProgramFiles(x86)%\Microsoft Dexterity\Dex 16.0

Once the Dex.ini file had been copied into the installation folder of Dexterity, I was able to enter debug mode without further issue.