Category: GP (Great Plains)

Back in January 2021 I posted an article which included a SQL view to return accruals from the Payables Management module of Microsoft Dynamics GP. What I forgot at the time was that view included joins into the Revenue/Expense Deferral module which is not part of the standard installation.

I’ve been asked a few times since for aversion which didn;t include the RED joins as a system without this module installed with throw errors. I’ve given the updated script out a few times and have finally got around to posting it here; I’ll add a link to the original article pointing to this one.

The view below can be added to a SQL database and then used in SmartList Designer, SmartList Builder or other reporting tools such as refreshable Excel reports.

The highlighted section is the name of the accrual account which should be used; you may want to hard code this to your accrual account, change the description to the name of your accrual account or change the logic of hw the accrual acount is selected.

IF OBJECT_ID (N'uv_ISC_PayablesAccruals', N'V') IS NOT NULL
    DROP VIEW uv_ISC_PayablesAccruals
GO

CREATE VIEW uv_ISC_PayablesAccruals 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
	*
FROM
	(SELECT
		'PAC ' + FORMAT(DATEADD(month,-1,GETDATE()), 'yyyyMM') AS 'Accruals Batch Number'
		,FORMAT(EOMONTH(DATEADD(month,-1,GETDATE())), 'dd/MM/yyyy') AS 'Transaction Date'
		,FORMAT(DATEADD(day, 1, EOMONTH(DATEADD(month,-1,GETDATE()))), 'dd/MM/yyyy') AS 'Reverse Date'
		,'Purchase Accruals ' + FORMAT(DATEADD(month,-1,GETDATE()), 'MM/yyyy') AS 'Reference'
		,GL00105.ACTNUMST AS 'Account'
		,CASE WHEN DOCTYPE = 1 THEN
			SUM(PM10100.DEBITAMT)
		ELSE
			0
		END AS 'Debit'
		,CASE WHEN DOCTYPE = 1 THEN
			0
		ELSE
			SUM(PM10100.CRDTAMNT)
		END AS 'Credit'
		,LEFT(RTRIM(CAST(PM10100.VCHRNMBR AS VARCHAR(15))) + ' ' + PM10100.DistRef, 30) AS 'Description'
		,PM10100.VCHRNMBR AS 'Voucher Number'
		,PM10000.BACHNUMB AS 'Batch Number'
		,PM10000.TRXDSCRN AS 'Document Description'
		,PM10000.DOCNUMBR AS 'Document Number'
		,PM10000.PORDNMBR AS 'PO Number'
		,PM10100.DistRef AS 'Distribution Reference'
	FROM
		PM10000 AS PM10000 --PM Transaction WORK File (PM10000)
	INNER JOIN
		PM10100 AS PM10100 --PM Distribution WORK OPEN (PM10100)
			on
				PM10100.CNTRLTYP = PM10000.CNTRLTYP
			AND
				PM10100.VCHRNMBR = PM10000.VCHRNMBR
	INNER JOIN
		GL00105 AS GL00105 --Account Index Master (GL00105)
			on
				GL00105.ACTINDX = PM10100.DSTINDX
	WHERE
		PM10000.BCHSOURC = 'PM_Trxent' --Include only normal transactions
	AND
		PM10000.DOCTYPE = 1 --Include only Invoices
	AND
		PM10100.DISTTYPE = 6 --Include only Purchases Distribution
	GROUP BY
		GL00105.ACTNUMST
		,PM10000.DOCTYPE
		,PM10100.DistRef
		,PM10100.VCHRNMBR
		,PM10000.BACHNUMB
		,PM10000.TRXDSCRN
		,PM10000.DOCNUMBR
		,PM10000.PORDNMBR
	UNION ALL
		SELECT
			'PAC ' + FORMAT(DATEADD(month,-1,GETDATE()), 'yyyyMM') AS 'Accruals Batch Number'
			,FORMAT(EOMONTH(DATEADD(month,-1,GETDATE())), 'dd/MM/yyyy') AS 'Transaction Date'
			,FORMAT(DATEADD(day, 1, EOMONTH(DATEADD(month,-1,GETDATE()))), 'dd/MM/yyyy') AS 'Reverse Date'
			,'Purchase Accruals ' + FORMAT(DATEADD(month,-1,GETDATE()),'MM/yyyy') AS 'Reference'
			,GL00105.ACTNUMST AS 'Account'
			,SUM(PM10100.CRDTAMNT) AS 'Debit'
			,SUM(PM10100.DEBITAMT) AS 'Credit'
			,'Accrued Purchases' AS 'Description'
			,'' AS 'Voucher Number'
			,'' AS 'Batch Number'
			,'' AS 'Document Description'
			,'' AS 'Document Number'
			,'' AS 'PO Number'
			,'' AS 'Distribution Reference'
		FROM
			PM10000 AS PM10000 --PM Transaction WORK File (PM10000)
		INNER JOIN
			PM10100 AS PM10100 --PM Distribution WORK OPEN (PM10100)
				on
					PM10100.CNTRLTYP = PM10000.CNTRLTYP
				AND
					PM10100.VCHRNMBR = PM10000.VCHRNMBR
		INNER JOIN
			GL00100 AS GL00100 --Breakdown Account Master (GL00100)
				on
					GL00100.ACTDESCR = 'Accrued Purchases'
		INNER JOIN
			GL00105 AS GL00105 --Account Index Master (GL00105)
				on GL00105.ACTINDX = GL00100.ACTINDX
		WHERE
			PM10000.BCHSOURC = 'PM_Trxent' --Include only normal transactions
		AND
			PM10000.DOCTYPE = 1 --Include only Invoices
		AND
			PM10100.DISTTYPE = 6 --Include only Purchases Distribution
		GROUP BY
			GL00105.ACTNUMST
			,PM10000.DOCTYPE
	) AS Accruals
WHERE
	Accruals.Debit > 0
OR
	Accruals.Credit > 0
GO

GRANT SELECT ON uv_ISC_PayablesAccruals TO DYNGRP
GO

This post is part of the Microsoft Dynamics GP Workflow Approval SQL Views series and contains a SQL view to return the workflow approval status of purchase orders.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_POPOrderApprovalStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_POPOrderApprovalStatus
GO
-- create view
CREATE VIEW uv_AZRCRV_POPOrderApprovalStatus 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).
*/
WITH POApprovals AS
	(
		SELECT
			['Workflow Instance Master'].WfBusObjKey AS PONUMBER
			,['Workflow History'].Workflow_History_User
			,['Workflow History'].Workflow_Completion_Date
			,['Workflow History'].Workflow_Completion_Time
			,['Workflow History'].Workflow_Name
			,['Workflow History'].Workflow_Step_Name
			,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 as Workflow_Approval_Status
			,['Workflow History'].Workflow_Comments
			,ROW_NUMBER() OVER(PARTITION BY ['Workflow Instance Master'].WfBusObjKey ORDER BY ['Workflow History'].Workflow_Completion_Date DESC, ['Workflow History'].Workflow_Completion_Time DESC) AS ROW_ID
		FROM 
			WF30100 AS ['Workflow History'] WITH (NOLOCK)
		INNER JOIN
			WFI10002 AS ['Workflow Instance Master'] WITH (NOLOCK)
				ON
					['Workflow Instance Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
		WHERE
			['Workflow Instance Master'].Workflow_Type_Name = 'Purchase Order Approval'
		AND
			['Workflow History'].Workflow_Action <> 11
	)

SELECT
	-- order headers
	['Purchase Orders'].PONUMBER
	,['Purchase Orders'].DOCDATE
	,['Purchase Orders'].VENDORID
	-- approval information
	,POApprovals.Workflow_Name
	,POApprovals.Workflow_Step_Name
	,POApprovals.Workflow_History_User
	,POApprovals.Workflow_Approval_Status
	,POApprovals.Workflow_Completion_Date
	,POApprovals.Workflow_Completion_Time
	,POApprovals.Workflow_Comments
	-- order lines
	,['Purchase Order Lines'].ITEMNMBR
	,['Purchase Order Lines'].ITEMDESC
	,['Purchase Order Lines'].QTYORDER
	,['Purchase Order Lines'].UNITCOST
	,['Purchase Order Lines'].EXTDCOST
	,['PM Vendor Master'].VENDNAME
FROM
	(
		SELECT
			PONUMBER
			,DOCDATE
			,BUYERID
			,VENDORID
		FROM
			POP10100 AS ['Purchase Order Work'] WITH (NOLOCK)
		UNION ALL
			SELECT
				PONUMBER
				,DOCDATE
				,BUYERID
				,VENDORID
			FROM
				POP30100 AS ['Purchase Order History'] WITH (NOLOCK)
	)  AS ['Purchase Orders']
INNER JOIN
	(
		SELECT
			PONUMBER
			,ITEMNMBR
			,ITEMDESC
			,QTYORDER
			,UNITCOST
			,EXTDCOST
		FROM
			POP10110 AS ['Purchase Order Lines Work'] WITH (NOLOCK)
		UNION ALL
			SELECT
				PONUMBER
				,ITEMNMBR
				,ITEMDESC
				,QTYORDER
				,UNITCOST
				,EXTDCOST
			FROM
				POP30110 AS ['Purchase Order Lines History'] WITH (NOLOCK)
	) AS ['Purchase Order Lines']
		ON
			['Purchase Order Lines'].PONUMBER = ['Purchase Orders'].PONUMBER
LEFT JOIN
	POApprovals
		ON
			POApprovals.PONUMBER = ['Purchase Orders'].PONUMBER
		AND
			POApprovals.ROW_ID = 1
LEFT JOIN
	PM00200 AS ['PM Vendor Master'] WITH (NOLOCK)
		ON
			['PM Vendor Master'].VENDORID = ['Purchase Orders'].VENDORID
GO

GRANT SELECT ON uv_AZRCRV_POPOrderApprovalStatus TO DYNGRP
GO

This post is part of the Microsoft Dynamics GP Workflow Approval SQL Views series and contains a SQL view to return the workflow approval status of purchase requisitions.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_POReqApprovalStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_POReqApprovalStatus
GO
-- create view
CREATE VIEW uv_AZRCRV_POReqApprovalStatus 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).
*/
WITH PRApprovals AS
	(
		SELECT
			['Workflow Instance Master'].WfBusObjKey AS PRRequisitionNumber
			,['Workflow History'].Workflow_History_User
			,['Workflow History'].Workflow_Completion_Date
			,['Workflow History'].Workflow_Completion_Time
			,['Workflow History'].Workflow_Name
			,['Workflow History'].Workflow_Step_Name
			,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 as Workflow_Approval_Status
			,['Workflow History'].Workflow_Comments
			,ROW_NUMBER() OVER(PARTITION BY ['Workflow Instance Master'].WfBusObjKey ORDER BY ['Workflow History'].Workflow_Completion_Date DESC, ['Workflow History'].Workflow_Completion_Time DESC) AS ROW_ID
		FROM 
			WF30100 AS ['Workflow History'] WITH (NOLOCK)
		INNER JOIN
			WFI10002 AS ['Workflow Instance Master'] WITH (NOLOCK)
				ON
					['Workflow Instance Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
		WHERE
			['Workflow Instance Master'].Workflow_Type_Name = 'Purchase Requisition Approval'
		AND
			['Workflow History'].Workflow_Action <> 11
	)

SELECT
	-- requisition headers
	['Purchase Requisitions'].POPRequisitionNumber
	,['Purchase Requisitions'].DOCDATE
	,['Purchase Requisitions'].RequisitionDescription
	,['Purchase Requisitions'].REQSTDBY
	-- approval information
	,PRApprovals.Workflow_Name
	,PRApprovals.Workflow_Step_Name
	,PRApprovals.Workflow_History_User
	,PRApprovals.Workflow_Approval_Status
	,PRApprovals.Workflow_Completion_Date
	,PRApprovals.Workflow_Completion_Time
	,PRApprovals.Workflow_Comments
	-- requisition lines
	,['Purchase Requisition Lines'].ITEMNMBR
	,['Purchase Requisition Lines'].ITEMDESC
	,['Purchase Requisition Lines'].QTYORDER
	,['Purchase Requisition Lines'].UNITCOST
	,['Purchase Requisition Lines'].EXTDCOST
	,['Purchase Requisition Lines'].VENDORID
	,['PM Vendor Master'].VENDNAME
FROM
	(
		SELECT
			POPRequisitionNumber
			,DOCDATE
			,RequisitionDescription
			,REQSTDBY
		FROM
			POP10200 AS ['Purchase Requisition Work'] WITH (NOLOCK)
		UNION ALL
			SELECT
				POPRequisitionNumber
				,DOCDATE
				,RequisitionDescription
				,REQSTDBY
			FROM
				POP30200 AS ['Purchase Requisition History'] WITH (NOLOCK)
	)  AS ['Purchase Requisitions']
INNER JOIN
	(
		SELECT
			POPRequisitionNumber
			,ITEMNMBR
			,ITEMDESC
			,QTYORDER
			,UNITCOST
			,EXTDCOST
			,VENDORID
		FROM
			POP10210 AS ['Purchase Requisition Lines Work'] WITH (NOLOCK)
		UNION ALL
			SELECT
				POPRequisitionNumber
				,ITEMNMBR
				,ITEMDESC
				,QTYORDER
				,UNITCOST
				,EXTDCOST
				,VENDORID
			FROM
				POP30210 AS ['Purchase Requisition Lines History'] WITH (NOLOCK)
	) AS ['Purchase Requisition Lines']
		ON
			['Purchase Requisition Lines'].POPRequisitionNumber = ['Purchase Requisitions'].POPRequisitionNumber
LEFT JOIN
	PRApprovals
		ON
			PRApprovals.PRRequisitionNumber = ['Purchase Requisitions'].POPRequisitionNumber
		AND
			PRApprovals.ROW_ID = 1
LEFT JOIN
	PM00200 AS ['PM Vendor Master'] WITH (NOLOCK)
		ON
			['PM Vendor Master'].VENDORID = ['Purchase Requisition Lines'].VENDORID
GO

GRANT SELECT ON uv_AZRCRV_POReqApprovalStatus TO DYNGRP
GO

This post is part of the Microsoft Dynamics GP Workflow Approval SQL Views series and contains a SQL view to return the workflow approval status of payables transactions.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_PayablesTransactionApprovalStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_PayablesTransactionApprovalStatus
GO
-- create view
CREATE VIEW uv_AZRCRV_PayablesTransactionApprovalStatus 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).
*/
WITH PMTrxApprovals AS
	(
		SELECT
			['Workflow Instance Master'].WfBusObjKey
			,['Workflow History'].Workflow_History_User
			,['Workflow History'].Workflow_Completion_Date
			,['Workflow History'].Workflow_Completion_Time
			,['Workflow History'].Workflow_Name
			,['Workflow History'].Workflow_Step_Name
			,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 as Workflow_Approval_Status
			,['Workflow History'].Workflow_Comments
			,ROW_NUMBER() OVER(PARTITION BY ['Workflow Instance Master'].WfBusObjKey ORDER BY ['Workflow History'].Workflow_Completion_Date DESC, ['Workflow History'].Workflow_Completion_Time DESC) AS ROW_ID
		FROM 
			WF30100 AS ['Workflow History'] WITH (NOLOCK)
		INNER JOIN
			WFI10002 AS ['Workflow Instance Master'] WITH (NOLOCK)
				ON
					['Workflow Instance Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
		WHERE
			['Workflow Instance Master'].Workflow_Type_Name = 'Payables Transaction Approval'
		AND
			['Workflow History'].Workflow_Action <> 11
	)
	
SELECT
	-- transaction information
	['PM Key Master File'].CNTRLNUM AS 'PO Number'
	,['Payables Document Types'].DOCTYNAM AS 'Document Type'
	,['PM Key Master File'].VENDORID AS 'Vendor ID'
	,['PM Vendor Master'].VENDNAME AS 'Vendor Name'
	,['PM Key Master File'].DOCDATE AS 'Document Date'
	-- approval information
	,PMTrxApprovals.Workflow_Name AS 'Workflow Name'
	,PMTrxApprovals.Workflow_Step_Name AS 'Workflow Step Name'
	,PMTrxApprovals.Workflow_History_User AS 'Workflow User'
	,PMTrxApprovals.Workflow_Approval_Status AS 'Workflow Approval Status'
	,PMTrxApprovals.Workflow_Completion_Date AS 'Workflow Completion Date'
	,PMTrxApprovals.Workflow_Completion_Time AS 'Workflow Completion Time'
	,PMTrxApprovals.Workflow_Comments AS 'Workflow_Comments'
FROM
	PM00400 AS ['PM Key Master File'] WITH (NOLOCK)
INNER JOIN
	PM00200 AS ['PM Vendor Master'] WITH (NOLOCK)
		ON
			['PM Vendor Master'].VENDORID = ['PM Key Master File'].VENDORID
INNER JOIN
	PM40102 AS ['Payables Document Types'] WITH (NOLOCK)
		ON
			['Payables Document Types'].DOCTYPE = ['PM Key Master File'].DOCTYPE
LEFT JOIN
	PMTrxApprovals
		ON
			PMTrxApprovals.WfBusObjKey = CAST(RTRIM(['PM Key Master File'].CNTRLNUM) AS VARCHAR(20)) + '~PM_Trxent'
		AND
			PMTrxApprovals.ROW_ID = 1
GO

-- grant permissions
GRANT SELECT ON uv_AZRCRV_PayablesTransactionApprovalStatus TO DYNGRP
GO

A couple of years ago, I posted a SQL view which returned the status of general ledger batches.

I’ve recently been working with a few clients with workflow in Purchasing and a common request was being able to report on the status of documents.

I have three views which were created and which I will post over the next few days. If I create additional ones in future, I’ll tag them into this post.

Microsoft Dynamics GP Workflow Approval SQL Views
Show Workflow Approval Status of posted Microsoft Dynamics GP General Ledger batches
Payables Transactions
Purchase Requisitions
Purchase Orders

I 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

I was on a call with a client the other day looking at a few issues with them in Microsoft Dynamics GP. One of the issues was that they had been setting up the Fixed Asset Management module, but when they ran depreciation, nothing happened.

We did some exploring and the only difference I could find, from who I would have normally configured the module, was that the Fixed Asset Calendar ((Financial » Setup » Fixed Assets » Calendar)) had been configured only for the required years starting in 2020; I usually set it up for a 200 year period.

The first asset being setup was dated in period 12 of 2020 so there is no logical reason why prior years should be needed. However, after we added 2019 into the calendar and re-ran the depreciation, all of the assets depreciated correctly.

I was talking to a client on a Teams meeting a while ago and they took the opportunity to ask about a problem they were having with Integration Manager.

When they tried to run a n integration to import Payables Transactions they were receiving an error:

The resolution to this is as straightforward as the cause is annoying. Sometimes when you make a change in one part of an integration, a configuration in another part disappears. In this case the Record Source on the options tab of the Tax Details node had been erased. Simply setting the option back to the correct source and saving the integration will fix the issue: