Day: May 25, 2023

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 was created for a client to return a list of purchase receipts for use in their document management system.

CREATE VIEW [dbo].[uv_AZRCRV_GetPurchaseReceipts] 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
	['Purchase Receipt Work'].POPRCTNM
	,['Purchase Receipt Line'].RCPTLNNM AS RCPTLNNM
	--,['Purchase Receipt Line'].RCPTLNNM/16384 AS RCPTLNNM
	,['Purchase Receipt Line'].PONUMBER
	,['Purchase Receipt Line Quanities'].POLNENUM
	,['Purchase Receipt Work'].VENDORID
	,['Purchase Receipt Line'].ITEMNMBR
	,['Purchase Receipt Line Quanities'].QTYSHPPD
	,['Purchase Receipt Line Quanities'].QTYSHPPD - ['Purchase Receipt Line Quanities'].QTYMATCH AS QTYRMAIN
	,['Purchase Receipt Line'].UNITCOST
	,['Purchase Receipt Line'].EXTDCOST
	,ROUND(['Purchase Receipt Line'].EXTDCOST - (['Purchase Receipt Line'].EXTDCOST * 20 / (100 + 20)),2) AS NET
	,ROUND(['Purchase Receipt Line'].EXTDCOST * 20 / (100 + 20),2) AS VAT
	,['Purchase Receipt Work'].receiptdate
	,['Account Index Master'].ACTINDX
	,['Account Index Master'].ACTNUMST
	,['Purchasing Distributions WORK'].ACTINDX AS ACCINDX
	,(SELECT
		ACTINDX
	FROM
		SY01100 --Posting Account Master (SY01100)
	WHERE
		SERIES = 4
	AND
		PTGACDSC = 'Accounts Payable') AS PAYINDX
	,'PS' AS TAXSCHID
	,'PS' AS TAXDTLID
	,(SELECT TOP 1
		ISNULL(['Workflow Users'].ADDisplayName,['Workflow History'].Workflow_History_User)
	FROM
		WFI10002 ['Workflow Instance Master'] --Workflow Instance Master (WFI10002)
	INNER JOIN 
		WF30100 ['Workflow History'] --Workflow History (WF30100)
			ON
				['Workflow History'].WorkflowInstanceID = ['Workflow Instance Master'].WorkflowInstanceID
			AND
				['Workflow History'].Workflow_Action = 10
	LEFT JOIN
		WF40200 ['Workflow Users'] --Workflow Users (WF40200)
			ON
				['Workflow Users'].ADLogin = ['Workflow History'].Workflow_History_User
	WHERE
		WfBusObjKey = ['SOP_POP Link'].SOPNUMBE) AS Approver
FROM
	POP30300 AS ['Purchase Receipt Work'] --Purchasing Receipt History (POP30300)
INNER JOIN
	POP30310 AS ['Purchase Receipt Line'] --Purchasing Receipt Line History (POP30310)
		ON ['Purchase Receipt Line'].POPRCTNM = ['Purchase Receipt Work'].POPRCTNM
INNER JOIN
	POP30390 AS ['Purchasing Distributions WORK'] --Purchasing Distribution History (POP30390)
		ON
			['Purchasing Distributions WORK'].POPRCTNM = ['Purchase Receipt Work'].POPRCTNM
		AND
			['Purchasing Distributions WORK'].DISTTYPE = 9
LEFT JOIN
	POP10500 AS ['Purchase Receipt Line Quanities'] --Purchasing Receipt Line Quantities (POP10500)
		ON
			['Purchase Receipt Line Quanities'].POPRCTNM = ['Purchase Receipt Line'].POPRCTNM
		AND
			['Purchase Receipt Line Quanities'].RCPTLNNM = ['Purchase Receipt Line'].RCPTLNNM
		AND
			['Purchase Receipt Line Quanities'].PONUMBER = ['Purchase Receipt Line'].PONUMBER
LEFT JOIN
	GL00105 AS ['Account Index Master'] --Account Index Master (GL00105)
		ON
			['Account Index Master'].ACTINDX = ['Purchase Receipt Line'].INVINDX
LEFT JOIN
	POP10360 AS ['Purchasing Tax'] --Purchasing Tax (POP10360)
		ON
			['Purchasing Tax'].POPRCTNM = ['Purchase Receipt Line'].POPRCTNM
		AND
			['Purchasing Tax'].RCPTLNNM = ['Purchase Receipt Line'].RCPTLNNM
LEFT JOIN
	SOP60100 AS ['SOP_POP Link'] --SOP_POPLink (SOP60100)
		ON
			['SOP_POP Link'].PONUMBER = ['Purchase Receipt Line'].PONUMBER
		AND
			['SOP_POP Link'].ORD = 16384
WHERE
	['Purchase Receipt Work'].POPTYPE = 1
AND 
	['Purchase Receipt Line Quanities'].QTYSHPPD - ['Purchase Receipt Line Quanities'].QTYMATCH > 0
GO