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

Read original post SQL Scripts for Microsoft Dynamics GP: PO Receipt History View at azurecurve|Ramblings of an IT Professional