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
