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