SQL Script Linking Purchase Orders to Purchase Requisitions

I 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