Year: 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