SQL View to Return Purchasing Status for the GP Elementz PO Management Module for Microsoft Dynamics GP

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