Category: GP (Great Plains)

I’ve previously posted SQL stored procedures to get the next GL Journal Number and the next PO Receipt number and today it is the turn of a stored procedure to get the next PM Voucher Number.

The stored procedure will call the eConnect stored procedure which gets the next number and increments the stored value. This code was written so I could easily call it from VBA in Integration Manager for an integration which needed to insert some data into a custom table. This allowed me to get the voucher number up front and use it in the VBA.

-- drop stored proc if it exists
IF OBJECT_ID(N'usp_AZRCRV_GetNextPMVoucherNumber', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_GetNextPMVoucherNumber
GO
-- create stored proc
CREATE PROCEDURE usp_AZRCRV_GetNextPMVoucherNumber 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
BEGIN
	DECLARE @return_value AS INT
	DECLARE @O_vCNTRLNUM AS VARCHAR(17)
	DECLARE @I_sCNTRLTYP AS TINYINT = 0
	DECLARE @O_iErrorState AS INT

	EXEC @return_value = taGetPMNextVoucherNumber
				@I_sCNTRLTYP = @I_sCNTRLTYP
				,@O_iErrorState = @O_iErrorState OUTPUT
				,@O_vCNTRLNUM = @O_vCNTRLNUM OUTPUT
	SELECT @O_vCNTRLNUM AS VCHRNMBR
END
GO

-- grant execute permission on stored proc to DYNGRP
GRANT EXECUTE ON usp_AZRCRV_GetNextPMVoucherNumber TO DYNGRP
GO

The stored procedure can be executed using this command:

-- execute stored proc
EXEC usp_AZRCRV_GetNextPMVoucherNumber
GO

I helped field a query from a client recently on the Reconcile to GL routine (Financial » Routines » Reconcile to GL) showing Payables Management, Receivales Management and Bank Reconciliation, but not Inventory.

This client has been a long time user of Microsoft Dynamics GP who had recently upgraded. This issue is a known one, addressed on the Dynamics GP Support and Services Blog.

With the support of the helpdesk, the client was able to run through the steps to resolve the issue and perform a reconciliation of the Inventory module to the GL.

I’ve done a lot of work with the Microsoft Dynamics GP Workflow module over the years since it was first released and it still seems to be growing in popularity with clients. Depending on the workflow type being created, the GL account string can be used in conditions to determine the routing for the approver.

However, commonly clients want to check the value for a single segment rather than the full account string. You can, to an extend, accomplish this using filters such as contains or begins with, but sometimes this isn’t feasible. You can get round this problem be using the _ wildcard in your condition.

For example, if I was setting up a purchase requisition workflow and wanted to configure a routing for all advertising expense codes, which is the codes where the Account segment start 66, to the Marketing Director, I could set up a condition where the account string is ___-66_0-00:

This would use this workflow step for all Divisions (segment 1) for department 00, where the Account (segment 2) starts with 66 and ends with 0. The wildcard character allows an account string with any value in the characters replaced with an underscore to be selected.

The Document Attachment feature was introduced in Microsoft Dynamics GP 2013 RTM and has been enhanced a number of times since. One of the features it does not have is the ability to delete attachments; you can flag them as deleted, but they are not removed from the database.

With GDPR rules, clients have become concerned about the information retained in the system without a means to delete it. To that end I created a SQL stored procedure which could be scheduled to run on a regular basis and delete transactions older than the specified number of years (highlighted value is the number of years).

This allows clients to run this on a scheduled basis and remove old documents; it can also serve as the basis for a customised version which deletes on a more controlled basis.

As with any script, please ensure you perform through testing before deploying to a live system.

IF OBJECT_ID (N'usp_AZRCRV_DeleteDocAttachAttachments', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_DeleteDocAttachAttachments
GO

CREATE PROCEDURE dbo.usp_AZRCRV_DeleteDocAttachAttachments
	@iAge INTEGER = 7
AS
/*
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 TEMPORARY TABLE
CREATE TABLE #AttachmentsToDelete(
	Attachment_ID CHAR(37)
)

-- SELECT ATTACHMENTS OVER n YEARS OLD TO DELETE
INSERT INTO #AttachmentsToDelete
	(Attachment_ID)
--VALUES
	(
	SELECT
		Attachment_ID
	FROM
		CO00101
	WHERE
		CREATDDT < DATEADD(yyyy, -@iAge, GETDATE())
	)

-- DELETE FROM Document Attachment Master (CO00101)
DELETE FROM
	CO00101
WHERE
	Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)

-- DELETE FROM Document Attachment Reference (CO00102)
DELETE FROM
	CO00102
WHERE
	Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)

-- DELETE FROM Document Attachment Properties (CO00103)
DELETE FROM
	CO00103
WHERE
	Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)

-- DELETE FROM Document Attachment Status (CO00104)
DELETE FROM
	CO00104
WHERE
	Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)

-- DELETE FROM Document Attachment Email (CO00105)
DELETE FROM
	CO00105
WHERE
	Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)

-- DELETE FROM COATTACHMENTITEMS
DELETE FROM
	coAttachmentItems
WHERE
	Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)

-- DROP TEMPORARY TABLE
DROP TABLE #AttachmentsToDelete

GO

I was recently talking to a client who was looking at creating a SQL script which they could run for the auditors which shows the assigned approvers to the steps of a Microsoft Dynamics GP Workflow process.

I’d written similar code for others before so I was able to provide them with this view:

CREATE VIEW uv_AZRCRV_GetWorkflowSetupStepAssignment AS
/*
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
    ['Workflow Master'].Workflow_name
    ,['Workflow Master'].Workflow_Description
    ,CASE WHEN ['Workflow Master'].ACTIVE = 1 THEN 'Yes' ELSE 'No' END AS ACTIVE
    ,['Workflow Step Instance Table'].WF_Step_Predecessor
    ,['Workflow Step Instance Table'].Workflow_Step_Name
    ,['Workflow Step Instance Table'].WF_Step_Description
    ,['Workflow Step Instance Table'].EmailMessageID
    ,['Workflow Users'].ADLogin
    ,['Workflow Users'].ADDisplayName
FROM
    WF100002 AS ['Workflow Master']
LEFT JOIN
    WF100003 AS ['Workflow Step Instance Table']
        ON
            ['Workflow Step Instance Table'].Workflow_Name= ['Workflow Master'].Workflow_Name
LEFT JOIN
    WF40200 AS ['Workflow Users']
        ON
            ['Workflow Step Instance Table'].Workflow_Step_Assign_To = ['Workflow Users'].UsersListGuid
GO

This can be deployed to a company database and a SmartList object created using either SmartList Designer or SmartList Builder.

Updated 27/2/2020 to add Active column