Month: December 2022

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 returns a list of work status sales transactions including the different quantity types.

/*
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).
*/
DECLARE @ITEMNMBR VARCHAR(30) = '100XLG'

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) - ISNULL(['Sales Transaction Amounts Work Backorder'].QTYALLOC,0)) AS 'QTYAVAIL'
	,SUM([Item Quantity Master'].QTYONHND) AS QTYONHND
	,SUM([Item Quantity Master'].ATYALLOC)  AS 'QTYALLOC'
	,SUM(['Sales Transaction Amounts Work'].QTYALLOC) AS 'OPENORDERS'
	,SUM([Item Quantity Master'].QTYBKORD) AS 'OPENBACKORDERS'
	,SUM(['Sales Transaction Amounts Work Backorder'].QTYONPO) AS 'QTYONORD'
	,2 AS RCRDTYPE
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)
		WHERE
			['Sales Transaction Amounts Work'].SOPTYPE = 2
		GROUP BY
			ITEMNMBR
			,LOCNCODE
			,SOPTYPE
	) 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
LEFT JOIN
	(
		SELECT
			ITEMNMBR
			,LOCNCODE
			,SUM((QTYREMAI - ATYALLOC) * QTYBSUOM) AS QTYALLOC
			,SUM(QTYONPO) AS QTYONPO
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200)
		WHERE
			['Sales Transaction Amounts Work'].SOPTYPE = 5
		GROUP BY
			ITEMNMBR
			,LOCNCODE
			,SOPTYPE
	) AS ['Sales Transaction Amounts Work Backorder']
		ON
			['Sales Transaction Amounts Work Backorder'].ITEMNMBR = [Item Quantity Master'].ITEMNMBR
		AND
			['Sales Transaction Amounts Work Backorder'].LOCNCODE = [Item Quantity Master'].LOCNCODE
WHERE
	[Item Quantity Master'].RCRDTYPE = 2
AND
	[Item Quantity Master'].ITEMNMBR = @ITEMNMBR
GROUP BY
	[Item Quantity Master'].ITEMNMBR
	,[Item Quantity Master'].LOCNCODE

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 updates inventory item accounts from a CSV file; I have posted a similar script before, but the one in this article is updating more of the account fields.

As with any script which does updates, test the script and make sure you have a good backup before running on a production system.

/*
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 #StockCodes
	(ITEMNMBR VARCHAR(100)
	,ITEMDESC VARCHAR(100)	--Item Description(Not Used)
	,SALSCTGY VARCHAR(100)	--Sales Category (Not Used)
	,COGSCTGY VARCHAR(100)	--Cost of Goods Sold (Not Used)
	,ASMVRIDX VARCHAR(100)	--Assembly Variance
	,IVCOGSIX VARCHAR(100)	--Cost of Goods Sold
	,IVDMGIDX VARCHAR(100)	--Damaged
	,IVINSVIX VARCHAR(100)	--In Service
	,IVINUSIX VARCHAR(100)	--In Use
	,IVRETIDX VARCHAR(100)	--Inventory Returns
	,IVIVINDX VARCHAR(100)	--Inventory
	,IVIVOFIX VARCHAR(100)	--Inventory Offset
	,PURPVIDX VARCHAR(100)	--Purchase Price Variance
	,IVSLSIDX VARCHAR(100)	--Sales
	,IVSLDSIX VARCHAR(100)	--Markdowns
	,IVSLRNIX VARCHAR(100)	--Sales Returns
	,UPPVIDX VARCHAR(100)	--Unrealised Purchase Price Variance
	,IVVARIDX VARCHAR(100)	--Variance
	)
GO

BULK
 INSERT #StockCodes
FROM 'D:\PI Files\Amino Go-Live Scripts\22. Update Accounts.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

UPDATE
	IV
SET
	IVIVINDX = AIMIVIVINIX.ACTINDX	--Inventory
	,IVIVOFIX = AIMIVIVOFIX.ACTINDX	--Inventory Offset
	,IVCOGSIX = AIMIVCOGSIX.ACTINDX	--Cost of Goods Sold
	,IVSLSIDX = AIMIVSLSIDX.ACTINDX	--Sales
	,IVSLDSIX = AIMIVSLDSIX.ACTINDX	--Markdowns
	,IVSLRNIX = AIMIVSLRNIX.ACTINDX	--Sales Returns
	,IVINUSIX = AIMIVINUSIX.ACTINDX	--In Use
	,IVINSVIX = AIMIVINSVIX.ACTINDX	--In Service
	,IVDMGIDX = AIMIVDMGIDX.ACTINDX	--Damaged
	,IVVARIDX = AIMIVVARIDX.ACTINDX	--Variance
	,PURPVIDX = AIMPURPVIDX.ACTINDX	--Purchase Price Variance
	,UPPVIDX = AIMUPPVIDX.ACTINDX	--Unrealised Purchase Price Variance
	,IVRETIDX = AIMIVRETIDX.ACTINDX	--Inventory Returns
	,ASMVRIDX = AIMASMVRIDX.ACTINDX 	--Assembly Variance
FROM
	IV00101 IV --Item Master (IV00101)
INNER JOIN
	#StockCodes As Stock ON Stock.ITEMNMBR = IV.ITEMNMBR
INNER JOIN --Assembly Variance
	GL00105 As AIMASMVRIDX --Item Currency Master (IV00105)
		ON AIMASMVRIDX.ACTNUMST = Stock.ASMVRIDX
INNER JOIN --Cost of Goods Sold
	GL00105 As AIMIVCOGSIX ON AIMIVCOGSIX.ACTNUMST = Stock.IVCOGSIX
INNER JOIN --Damaged
	GL00105 As AIMIVDMGIDX ON AIMIVDMGIDX.ACTNUMST = Stock.IVDMGIDX
INNER JOIN --In Service
	GL00105 As AIMIVINSVIX ON AIMIVINSVIX.ACTNUMST = Stock.IVINSVIX
INNER JOIN --In Use
	GL00105 As AIMIVINUSIX ON AIMIVINUSIX.ACTNUMST = Stock.IVINUSIX
INNER JOIN --Inventory Returns
	GL00105 As AIMIVRETIDX ON AIMIVRETIDX.ACTNUMST = Stock.IVRETIDX
INNER JOIN --Inventory
	GL00105 As AIMIVIVINIX ON AIMIVIVINIX.ACTNUMST = Stock.IVIVINDX
INNER JOIN --Inventory Offset
	GL00105 As AIMIVIVOFIX ON AIMIVIVOFIX.ACTNUMST = Stock.IVIVOFIX
INNER JOIN --Purchase Price Variance
	GL00105 As AIMPURPVIDX ON AIMPURPVIDX.ACTNUMST = Stock.PURPVIDX
INNER JOIN --Sales
	GL00105 As AIMIVSLSIDX ON AIMIVSLSIDX.ACTNUMST = Stock.IVSLSIDX
INNER JOIN --Markdowns
	GL00105 As AIMIVSLDSIX ON AIMIVSLDSIX.ACTNUMST = Stock.IVSLDSIX
INNER JOIN --Sales Returns
	GL00105 As AIMIVSLRNIX ON AIMIVSLRNIX.ACTNUMST = Stock.IVSLRNIX
INNER JOIN --Unrealised Purchase Price Variance
	GL00105 As AIMUPPVIDX ON AIMUPPVIDX.ACTNUMST = Stock.UPPVIDX
INNER JOIN --Variance
	GL00105 As AIMIVVARIDX ON AIMIVVARIDX.ACTNUMST = Stock.IVVARIDX
GO


DROP TABLE #StockCodes
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 updates the location on work status purchase order lines from a CSV file. I vaguely recall this script and after running it we ran the reconcile process against both Purchase Ordering and Inventory Control.

As with any script, before running on production test the script to make sure it works and have a good backup of the database.

/*
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 #SiteXRef
	(PONUMBER VARCHAR(100)
	,ITEMNMBR VARCHAR(100)
	,LOCNCODE VARCHAR(100)
	)
GO

BULK
	INSERT #SiteXRef
FROM
	'C:\TEMP\StockCodes.csv'
WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n'
	)
GO

UPDATE
	POP
SET
	POP.LOCNCODE = SiteXRef.LOCNCODE
FROM
	POP10110 POP
INNER JOIN
	#SiteXRef As SiteXRef ON SiteXRef.PONUMBER = POP.PONUMBER AND SiteXRef.ITEMNMBR = POP.ITEMNMBR
GO

DROP TABLE #SiteXRef
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 updates items with an Item Number starting 0 to a status of Discontinued.

As with any script, before running on production test the script to make sure it works and have a good backup of the database.

/*
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).
*/
UPDATE
	IV
SET
	IV.ITEMTYPE = 2
FROM
	IV00101 IV WHERE IV.ITEMNMBR LIKE '0%' AND IV.ITEMTYPE <> 2
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 will take a CSV file and update the three phone numbers and fax numbers on the vendor address supplied.

/*
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 #PM00200_IMPORT
	(
		VENDORID VARCHAR(100)
		,ADRSCODE VARCHAR(100)
		,PHNUMBR1 VARCHAR(100)
		,PHNUMBR2 VARCHAR(100)
		,PHONE3 VARCHAR(100)
		,FAXNUMBR VARCHAR(100)
	)
GO

BULK INSERT
	#PM00200_IMPORT
FROM
	'C:\Temp\Supplier Phone and Fax.csv'
WITH
	(
		FIELDTERMINATOR = ','
		,ROWTERMINATOR = '\n'
		,FIRSTROW = 2
	)
GO

select * from #PM00200_IMPORT

-- VENDOR MASTER
UPDATE
	PM
SET
	PM.PHNUMBR1 = ISNULL(PM_I.PHNUMBR1,'')
	,PM.PHNUMBR2 = ISNULL(PM_I.PHNUMBR2,'')
	,PM.PHONE3 = ISNULL(PM_I.PHONE3,'')
	,PM.FAXNUMBR = ISNULL(PM_I.FAXNUMBR,'')
FROM
	PM00200 PM
INNER JOIN
	#PM00200_IMPORT As PM_I
		ON UPPER(PM_I.ADRSCODE) = PM.VENDORID
	AND
		PM_I.ADRSCODE = "MAIN"
GO

-- VENDOR ADDRESS MASTER
UPDATE
	PM
SET
	PM.PHNUMBR1 = ISNULL(PM_I.PHNUMBR1,'')
	,PM.PHNUMBR2 = ISNULL(PM_I.PHNUMBR2,'')
	,PM.PHONE3 = ISNULL(PM_I.PHONE3,'')
	,PM.FAXNUMBR = ISNULL(PM_I.FAXNUMBR,'')
FROM
	PM00300 PM
INNER JOIN
	#PM00200_IMPORT As PM_I
		ON PM_I.VENDORID = PM.VENDORID AND PM.ADRSCODE = UPPER(PM_I.ADRSCODE)
GO

DROP TABLE #PM00200_IMPORT
GO