Month: June 2023

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will return the approver of a workflow. It complements two other functions which I have previously posted:

• Return approval status
• Return last approval comment

IF OBJECT_ID (N'uf_AZRCRV_GetWorkflowApprover', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetWorkflowApprover
GO
CREATE FUNCTION [dbo].[uf_AZRCRV_GetWorkflowApprover](@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20))
	RETURNS VARCHAR(50)
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).
*/
/*
Author: Ian Grieve
Version: 1.0
Date: 1/3/2017

Returns Workflow Approval status of a specified workflow item.

Requires 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 
			WFU.ADDisplayName
		FROM 
			WF30100 AS WF --Workflow History (WF30100)
		INNER JOIN
			WFI10002 AS WFI --Workflow Instance Master (WFI10002)
				ON WFI.WorkflowInstanceID = WF.WorkflowInstanceID
		INNER JOIN
			WF40200 WFU --Workflow Users (WF40200)
				ON WFU.ADLogin = WF.Workflow_History_User
		WHERE
			WFI.Workflow_Type_Name = @WorkflowTypeName
		AND
			WFI.WfBusObjKey = @WfBusObjKey
		AND
			WF.Workflow_Action IN (3) --APPROVED
		ORDER BY
			WF.DEX_ROW_ID DESC)
	,'')
END
GO

GRANT SELECT ON uf_AZRCRV_GetWorkflowApprover TO DYNGRP
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created when I needed to verify that all work status Payables transactions were attached to a valid batch.

CREATE VIEW [dbo].[uv_AZRCRV_VerifyBatchForPayablesTransactions] 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
	PM.BACHNUMB AS 'PM Batch Number'
	,ISNULL(SY.BACHNUMB, 'Missing') AS 'Batch Record'
	,PM.VCHRNMBR AS 'Voucher Number'
	,PM.DOCDATE AS 'Document Date'
FROM
	PM10000 AS PM WITH (NOLOCK) --PM Transaction WORK File (PM10000)
LEFT JOIN
	SY00500 AS SY WITH (NOLOCK) --Posting Definitions Master Dup (SY00500)
		ON
			PM.BACHNUMB = SY.BACHNUMB
		AND
			SY.BCHSOURC = 'PM_TRXENT'
		AND
			SERIES = 4
WHERE
	SY.BACHNUMB IS NULL
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for a client to use in an external system to verify vendor/document number combinations.

CREATE VIEW [dbo].[uv_AZRCRV_VendorDocumentNumberLookup] 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
	DISTINCT *
FROM
	(
		SELECT
			VENDORID
			,VNDDOCNM AS DOCNUMBR
		FROM
			POP10300 --Purchasing Receipt Work (POP10300)
		WHERE
			POPTYPE = 2
		UNION ALL
			SELECT
				VENDORID
				,VNDDOCNM AS DOCNUMBR
			FROM
				POP30300 --Purchasing Receipt History (POP30300)
			WHERE
				POPTYPE = 2
		UNION ALL
			SELECT
				VENDORID
				,DOCNUMBR
			FROM
				PM00400 --PM Key Master File (PM00400)
	) AS TRX
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns segments which exist, but which are not linked to accounts; specifically it is checking segment 2 which the client used to store the cost centre.

CREATE VIEW [dbo].[uv_AZRCRV_UnlinkedCostCentres] 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
	['Segment Description Master'].SGMNTID
	,['Segment Description Master'].DSCRIPTN
FROM
	GL40200 AS ['Segment Description Master'] WITH (NOLOCK) --Segment Description Master (GL40200)
LEFT JOIN
	GL00105 AS ['Account Index Master'] WITH (NOLOCK) --Account Index Master (GL00105)
		ON
			['Account Index Master'].ACTNUMBR_2 = ['Segment Description Master'].SGMNTID
WHERE
		['Segment Description Master'].SGMTNUMB = 2
AND
	['Account Index Master'].ACTINDX IS NULL
GO

GRANT SELECT ON uv_AZRCRV_UnlinkedCostCentres TO DYNGRP
GO