SQL Scripts for Microsoft Dynamics GP: PO Receipt History View

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script creates a SQL view on the history of PO receipts including the purchase price variance.

CREATE VIEW uv_AZRCRV_POReceiptHistory 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
	['Purchasing Receipt Line History'].LOCNCODE AS 'Site'
	,['PM Creditor Master'].VENDNAME AS 'Vendor Name'
	,['PM Creditor Master'].VENDORID AS 'Vendor ID'
	,['Purchase Receipt History'].POPRCTNM AS 'Receipt Number'
	,['Purchase Receipt History'].receiptdate AS 'Receipt Date'
	,['Purchasing Receipt Line Quantities'].ITEMNMBR AS 'Item Number'
	,['Purchasing Receipt Line Quantities'].PONUMBER as 'PO Number'
	,['Purchasing Receipt Line Quantities'].QTYSHPPD AS 'Quantity Received'
	,['Purchase Orders'].UNITCOST 'PO Unit Cost'
	,['Item Master'].STNDCOST AS 'Standard Cost'
	,['Item Master'].STNDCOST - ['Purchasing Receipt Line History'].UNITCOST AS 'Unrecognised PPV'
	,['Purchasing Receipt Line Quantities'].QTYSHPPD * ['Purchase Orders'].UNITCOST AS 'Extended Cost'
	,['Purchasing Receipt Line History'].ITEMDESC AS 'Item Description'
	,['Item Master'].ITMCLSCD AS 'Item Class'
	,['Purchasing Receipt Line History'].UOFM AS 'Unit of Measure'
	,FORMAT( ['Purchase Receipt History'].receiptdate, 'MMM-yy' ) AS 'Month'
	,FORMAT( ['Purchase Receipt History'].receiptdate, 'yyyy' ) AS 'Year'
	,['Item Master'].ITMGEDSC AS 'Item Generic Description'
FROM
	POP30300 AS ['Purchase Receipt History'] WITH (NOLOCK) --Purchasing Receipt History (POP30300)
INNER JOIN
	POP30310 AS ['Purchasing Receipt Line History'] WITH (NOLOCK) --Purchasing Receipt Line History (POP30310)
		ON
			['Purchasing Receipt Line History'].POPRCTNM = ['Purchase Receipt History'].POPRCTNM
INNER JOIN
	IV00101 AS ['Item Master'] WITH (NOLOCK) --Item Master (IV00101)
		ON
			['Item Master'].ITEMNMBR = ['Purchasing Receipt Line History'].ITEMNMBR
INNER JOIN
	POP10500 AS ['Purchasing Receipt Line Quantities'] WITH (NOLOCK) --Purchasing Receipt Line Quantities (POP10500)
		ON
			['Purchasing Receipt Line Quantities'].POPRCTNM = ['Purchasing Receipt Line History'].POPRCTNM
		AND
			['Purchasing Receipt Line Quantities'].RCPTLNNM = ['Purchasing Receipt Line History'].RCPTLNNM
INNER JOIN
	(
		SELECT
			PONUMBER
			,ORD
			,UNITCOST
		FROM
			POP10110 WITH (NOLOCK) --Purchase Order Line (POP10110)
		UNION
			SELECT
				PONUMBER
				,ORD
				,UNITCOST
			FROM
				POP30110 WITH (NOLOCK) --Purchase Order Line History (POP30110)
	) AS ['Purchase Orders']
		ON
			['Purchase Orders'].PONUMBER = ['Purchasing Receipt Line Quantities'].PONUMBER
		AND
			['Purchase Orders'].ORD = ['Purchasing Receipt Line Quantities'].POLNENUM
INNER JOIN
	PM00200 AS ['PM Creditor Master'] WITH (NOLOCK) --PM Vendor Master File (PM00200)
		ON
			['PM Creditor Master'].VENDORID = ['Purchase Receipt History'].VENDORID
WHERE
	['Purchase Receipt History'].POPTYPE = 1
ORDER BY
	['Purchase Receipt History'].POPRCTNM
	,['Purchasing Receipt Line History'].RCPTLNNM
GO