Day: May 24, 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 use for lookups on Dynamics GP purchase orders from their document management system.

CREATE VIEW [dbo].[uv_AZRCRV_GetPurchaseOrders] 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 Order Work'].PONUMBER
	,['Purchase Order Line'].ORD/16384 AS ORDLNNM
	,['Purchase Order Work'].POSTATUS
	,CASE WHEN ['Purchase Order Work'].POSTATUS = 1 THEN
			'NEW'
		WHEN ['Purchase Order Work'].POSTATUS = 2 THEN
			'Released'
		WHEN ['Purchase Order Work'].POSTATUS = 3 THEN
			'Change Order'
		WHEN ['Purchase Order Work'].POSTATUS = 4 THEN
			'Received'
		WHEN ['Purchase Order Work'].POSTATUS = 5 THEN
			'Closed'
		WHEN ['Purchase Order Work'].POSTATUS = 6 THEN
			'Cancelled'
	END AS POSTATUSDESC
	,['Purchase Order Work'].DOCDATE
	,['Purchase Order Work'].VENDORID
	,['Purchase Order Work'].SUBTOTAL
	,['Purchase Order Work'].TAXAMNT
	,['Purchase Order Work'].SUBTOTAL + ['Purchase Order Work'].TAXAMNT AS TOTAL
	,['Purchase Order Line'].QTYORDER
	,['Purchase Order Line'].UNITCOST
	,['Purchase Order Line'].EXTDCOST
	,['Purchase Order Work'].USER2ENT
	,REPLACE(RTRIM(['User Master'].USERNAME), ' ' , '.') AS Originator
	,['Account Index Master'].ACTNUMST
	,CASE WHEN LEN(['Purchase Order Line'].Purchase_Item_Tax_Schedu) = 0 THEN
		['Purchase Order Line'].Purchase_Site_Tax_Schedu
	ELSE
		['Purchase Order Line'].Purchase_Item_Tax_Schedu
	END AS TAXSCHID
	,['Purchasing Tax'].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 ORDER BY ['Workflow History'].DEX_ROW_ID DESC) AS Approver
FROM
	POP10100 AS ['Purchase Order Work'] --Purchase Order Work (POP10100)
INNER JOIN
	POP10110 AS ['Purchase Order Line'] --Purchase Order Line (POP10110)
		ON ['Purchase Order Line'].PONUMBER = ['Purchase Order Work'].PONUMBER
LEFT JOIN
	GL00105 AS ['Account Index Master'] --Account Index Master (GL00105)
		ON
			['Account Index Master'].ACTINDX = ['Purchase Order Line'].INVINDX
LEFT JOIN
	POP10160 AS ['Purchasing Tax'] --Purchase Order Tax (POP10160)
		ON
			['Purchasing Tax'].PONUMBER = ['Purchase Order Line'].PONUMBER
		AND
			['Purchasing Tax'].ORD = ['Purchase Order Line'].ORD
LEFT JOIN
	SOP60100 AS ['SOP_POP Link'] --SOP_POPLink (SOP60100)
		ON
			['SOP_POP Link'].PONUMBER = ['Purchase Order Line'].PONUMBER
		AND
			['SOP_POP Link'].ORD = ['Purchase Order Line'].ORD
INNER JOIN
	DYNAMICS..SY01400 AS ['User Master'] --Users Master (SY01400)
		ON
			['User Master'].USERID = ['Purchase Order Work'].USER2ENT
GO