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

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 created a SQL trigger on the Posting Definitions Master Dup (SY00500) table to change the GL Posting Date to current date when a transaction is created.

This script was created for a client who was using eConnect to create purchase order invoices; unfortunately, we found that the GL Posting Date was being set to the same as the invoice date, but it needed to be the date the transaction was entered.

This script runs on insert and changes the date to the correct value.

CREATE TRIGGER utr_AZRCRV_UpdateGLPOSTDTOnInsert ON [dbo].[SY00500] AFTER INSERT 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).
*/
	BEGIN
		UPDATE
			['Batch Master']
		SET
			GLPOSTDT = FORMAT(GETDATE(), 'yyyy-MM-dd 00:00:00.000')
		FROM
			SY00500 AS ['Batch Master'] --Posting Definitions Master Dup (SY00500)
		INNER JOIN
			inserted as I
				ON
					I.BCHSOURC = ['Batch Master'].BCHSOURC
				AND
					I.BACHNUMB = ['Batch Master'].BACHNUMB
				AND
					I.SERIES = ['Batch Master'].SERIES
		AND
			i.BCHSOURC = 'Rcvg Trx Ivc'
	END

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 update the inactive flag on items from a CSV allowing items to be set to inactive or back to active en masse.

/*
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 TABLE #UploadData
	(ITEMNMBR VARCHAR(100)
	,INACTIVE BIT)
GO

BULK INSERT
	#UploadData
FROM
	'c:\TEMP\Items.txt'
WITH
	(FIELDTERMINATOR = ','
	,ROWTERMINATOR = '\n'
	,FIRSTROW = 2)
GO

UPDATE
	['Item Master']
SET
	INACTIVE = ['Upload Data'].INACTIVE
FROM
	IV00101 AS ['Item Master'] --Item Master (IV00101)
INNER JOIN
	#UploadData AS ['Upload Data']
		ON
			['Upload Data'].ITEMNMBR = ['Item Master'].ITEMNMBR
GO

DROP TABLE #UploadData
GO