Month: May 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

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

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 review the chart of accounts to make sure that all accounts using segment three had the same category assigned.

CREATE VIEW [dbo].[uv_AZRCRV_AccountCategoryReview] 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 DISTINCT
	['Account Index Master'].ACTNUMBR_3 AS 'Cost Account'
	,['Segment Master'].DSCRIPTN AS 'Cost Account Description'
	,['Account Category Master'].ACCATDSC AS 'Account Category Description'
	,['Account Index Master'].USERDEF2
FROM
	GL00100 AS ['Account Index Master'] --Breakdown Account Master (GL00100)
INNER JOIN
	GL00102 AS ['Account Category Master'] --Account Category Master (GL00102)
		ON
			['Account Category Master'].ACCATNUM = ['Account Index Master'].ACCATNUM
INNER JOIN
	GL40200 AS ['Segment Master'] --Segment Description Master (GL40200)
		ON
			['Segment Master'].SGMNTID = ['Account Index Master'].ACTNUMBR_3
		AND
			['Segment Master'].SGMTNUMB = 3
GO

GRANT SELECT ON uv_AZRCRV_AccountCategoryReview TO DYNGRP
GO

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 in SmartList Builder, and other, reports; it returns the quantity available for items on a location basis.

/*
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).
*/
CREATE VIEW uv_AZRCRV_GetAvailableQuantity 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
	[Item Quantity Master'].ITEMNMBR
	,[Item Quantity Master'].LOCNCODE
	,SUM([Item Quantity Master'].QTYONHND - [Item Quantity Master'].ATYALLOC - ISNULL(['Sales Transaction Amounts Work'].QTYALLOC,0)) AS 'QTYAVAIL'
FROM
	IV00102 AS [Item Quantity Master'] --Item Quantity Master (IV00102)
LEFT JOIN
	(
		SELECT
			ITEMNMBR
			,LOCNCODE
			,SUM((QTYREMAI - ATYALLOC) * QTYBSUOM) AS QTYALLOC
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200)
		GROUP BY
			ITEMNMBR
			,LOCNCODE
	) AS ['Sales Transaction Amounts Work']
		ON
			['Sales Transaction Amounts Work'].ITEMNMBR = [Item Quantity Master'].ITEMNMBR
		AND
			['Sales Transaction Amounts Work'].LOCNCODE = [Item Quantity Master'].LOCNCODE
WHERE
	[Item Quantity Master'].RCRDTYPE = 2
GROUP BY
	[Item Quantity Master'].ITEMNMBR
	,[Item Quantity Master'].LOCNCODE
GO

GRANT SELECT ON uv_AZRCRV_GetAvailableQuantity TO DYNGRP
GO