SQL Script Linking Purchase Orders to Purchase Requisitions

Microsoft Dynamics GPI have been doing a lot of work recently on Purchase Order Processing and Workflow 2.0. One of the requests I had was for a SmartList report which allowed the purchase order (PO) to be compared back to the purchase requisition (PR) it originated from. I had to take a few minutes to investigate as I knew the link from PR to PO was maintained within the system, but I wasn't sure of the table.

It took me a little longer to determine which table held the link as I was expecting a company table, but instead found that the link was stored in the SOP_POPLink table (SOP60100). One fairly quick view later and I have the basis for the required report, and probably for some others in future too.

CREATE VIEW uv_AZRCRV_SOPPOPLink AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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
	['Purchase Order Requisition'].*
	,['Purchase Order Requisition Line'].*
	,['Purchase Order'].*
	,['Purchase Order Line'].*
FROM
	(SELECT
		POPRequisitionNumber
		,DOCDATE
	FROM
		POP10200
	UNION ALL
		SELECT
			POPRequisitionNumber
			,DOCDATE
		FROM
			POP30200) AS ['Purchase Order Requisition Line']
INNER JOIN
	(SELECT
		POPRequisitionNumber
		,ORD
		,ITEMNMBR
		,QTYORDER
	FROM
		POP10210
	UNION ALL
		SELECT
			POPRequisitionNumber
			,ORD
			,ITEMNMBR
			,QTYORDER
		FROM
			POP30210) AS ['Purchase Order Requisition']
				ON ['Purchase Order Requisition'].POPRequisitionNumber = ['Purchase Order Requisition Line'].POPRequisitionNumber
LEFT JOIN
	SOP60100 AS ['SOP_POPLink']
		ON
			['SOP_POPLink'].SOPNUMBE = ['Purchase Order Requisition'].POPRequisitionNumber
		AND
			['SOP_POPLink'].LNITMSEQ = ['Purchase Order Requisition'].ORD
LEFT JOIN
	(SELECT
		PONUMBER
		,ORD
	FROM
		POP10110
	UNION ALL
		SELECT
			PONUMBER
			,ORD
		FROM
			POP30110) AS ['Purchase Order Line']
				ON
					['Purchase Order Line'].PONUMBER = ['SOP_POPLink'].PONUMBER
				AND
					['Purchase Order Line'].ORD = ['SOP_POPLink'].ORD
LEFT JOIN
	(SELECT
		PONUMBER
		,DOCDATE
	FROM
		POP10100
	UNION ALL
		SELECT
			PONUMBER
			,DOCDATE
		FROM
			POP30100) AS ['Purchase Order']
				ON
					['Purchase Order'].PONUMBER = ['Purchase Order Line'].PONUMBER
GO

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?





Your Name (required) -
Your Email (required) -

2 thoughts on “SQL Script Linking Purchase Orders to Purchase Requisitions

Leave a Reply

Your email address will not be published. Required fields are marked *