Month: August 2022

While it isn’t listed on the GP Elementz website, there is a module available called PO Management which replaces the standard POP/SOP link in Microsoft Dynamics GP. The standard functionality requires that all of a sales order line where there is a commitment to a PO must be fulfilled before that line can be invoiced; the PO Management module allows partial invoicing of a line committed to a PO.

After implementing the module for a client, they required a report showing the status of a SO line in terms of whether there was stock, if the SO line had been committed to a PO and if the line had been partially or fully received. As the PO Management module stores it data in a custom table, I had to create a SQL view pulling the relevant tables together to determine the status.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_POMStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_POMStatus
GO
-- create view
CREATE VIEW [dbo].uv_AZRCRV_POMStatus 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
	['Sales Transaction Amounts Work'].SOPNUMBE
	,['Sales Transaction Amounts Work'].SOPTYPE
	,['Sales Transaction Amounts Work'].LNITMSEQ
	,['Sales Transaction Amounts Work'].ITEMNMBR
	,CASE WHEN ['Sales Transaction Amounts Work'].SOPTYPE NOT IN (2,6) THEN
		''
	WHEN ['ISC Back to Back'].PONUMBER IS NULL
		AND
			(['Sales Transaction Amounts Work'].QTYFULFI - ['Sales Transaction Amounts Work'].QTYCANCE) <[/sqlgrey] (['Item Quantity Master'].QTYONHND - ['Item Quantity Master'].ATYALLOC) -- AVAILABLE
		THEN
			'Needs Purchase'
	WHEN ['Purchasing Receipt Line Quantities'].QTYSHPPD IS NULL OR ['Purchasing Receipt Line Quantities'].QTYSHPPD = 0 THEN
		'Purchased'
	WHEN ['Purchasing Receipt Line Quantities'].QTYSHPPD < (['Purchase Order Line'].QTYORDER - ['Purchase Order Line'].QTYCANCE) THEN
		'Partially Received'
	WHEN ['Purchasing Receipt Line Quantities'].QTYSHPPD >= (['Purchase Order Line'].QTYORDER - ['Purchase Order Line'].QTYCANCE) THEN
		'Fully Received'
	ELSE
		'None'
	END AS 'Purchasing Status'
FROM
	SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200)
LEFT JOIN
	IV00102 AS ['Item Quantity Master'] -- Item Quantity Master (IV00102)
		ON
			['Item Quantity Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR
		AND
			['Item Quantity Master'].LOCNCODE = ['Sales Transaction Amounts Work'].LOCNCODE
LEFT JOIN
	ISC_BACK AS ['ISC Back to Back'] -- ISC_Back_to_Back (ISC_Back)
		ON
			['ISC Back to Back'].SOPNUMBE = ['Sales Transaction Amounts Work'].SOPNUMBE
		AND
			['ISC Back to Back'].SOPTYPE = ['Sales Transaction Amounts Work'].SOPTYPE
		AND
			['ISC Back to Back'].CMPNTSEQ = ['Sales Transaction Amounts Work'].CMPNTSEQ
		AND
			['ISC Back to Back'].LNITMSEQ = ['Sales Transaction Amounts Work'].LNITMSEQ
LEFT JOIN
	POP10110 AS ['Purchase Order Line'] -- Purchase Order Line (POP10110)
		ON
			['Purchase Order Line'].PONUMBER = ['ISC Back to Back'].PONUMBER
		AND
			['Purchase Order Line'].ORD = ['ISC Back to Back'].ORD
LEFT JOIN
	(
		SELECT
			PONUMBER
			,POLNENUM
			,SUM(QTYINVCD) AS QTYSHPPD
		FROM
			POP10500 -- Purchasing Receipt Line Quantities (POP10500)
		GROUP BY
			PONUMBER
			,POLNENUM
	)  AS ['Purchasing Receipt Line Quantities']
		ON
			['Purchasing Receipt Line Quantities'].PONUMBER = ['ISC Back to Back'].PONUMBER
		AND
			['Purchasing Receipt Line Quantities'].POLNENUM = ['ISC Back to Back'].ORD
GO

GRANT SELECT ON uv_AZRCRV_POMStatus TO DYNGRP
GO

The workflow module in Microsoft Dynamics GP can send quite a lot of different notification emails, but the one I am often asked for, an email notification when a purchase is created from a purchase requisition, does not exist.

After being asked a few times, I had a think about how this could be done and came up with a SQL trigger on the Purchasing Requisition History (POP30200) table which joins to the SOP_POPLink (SOP60100) table when a PO has been created. This is easily deployed and also easily customised by customers to meet their own needs by, for example, changing the content of the notification message.

The first highlighted section is the email address of the receipient, in the example below I am building the email using the requested by username concatenated with a email domain internal to my test VM; the second highlighted section is the name of the SQL Database Mail profile which will be used to send the email.

/*
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 TRIGGER utr_AZRCRV_POP30200_PurchaseRequisition ON POP30200 AFTER INSERT AS
	
	DECLARE @EmailRecipient VARCHAR(100)
	DECLARE @EmailSubject VARCHAR(1000)
	DECLARE @EmailBody VARCHAR(MAX)
	DECLARE @PRFound BIT = 0

	SELECT
		@EmailRecipient = RTRIM(['Purchase Requisition Work'].REQSTDBY) + '@azurecurve.isc'
		,@EmailSubject = 'Purchase Order ' + RTRIM(CAST(['SOP_POP Link'].PONUMBER AS VARCHAR(100))) + ' has been created'
		,@EmailBody = 'Purchase Order ' + RTRIM(CAST(['SOP_POP Link'].PONUMBER AS VARCHAR(100))) + ' has been created from purchase requisition ' + RTRIM(CAST(['Purchase Requisition Work'].POPRequisitionNumber AS VARCHAR(100)))
		,@PRFound = 1
	FROM
		inserted AS ['Purchase Requisition Work']
	INNER JOIN
		SOP60100 AS ['SOP_POP Link'] -- SOP_POPLink (SOP60100)
			ON
				['SOP_POP Link'].SOPNUMBE = ['Purchase Requisition Work'].POPRequisitionNumber

	/* send email using database mail
	https://msdn.microsoft.com/en-us/library/ms190307.aspx
	*/
	IF (SELECT @PRFound) = 1
	BEGIN
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = 'MDGP Workflow'
			,@recipients = @EmailRecipient
			,@subject = @EmailSubject
			,@body = @EmailBody
			,@body_format ='HTML'
	END
GO

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