Update Inventory Item Accounts From CSV

This script was produced for a client who wanted to bulk update the accounts defined against selected Inventory Items in Microsoft Dynamics GP 2010 R2. This particular client did not have Integration Manager so I needed an alternative approach to doing the update.

I could have used a GP Macro to do the job (doing one while recording the macro to create a template to be populated from a CSV using Mail Merge) but it was easier to create a SQL script to do the job directly from the CSV (this is the same view I took for updating the Account Segment Master).

This script runs from a CSV with the Item Number in the first column and then the Inventory, Inventory Offset, COGS, Sales, Sales Returns, Variance and Inventory Returns in the following columns (there are more Accounts which could be added but these were the ones the client wanted to update).

If you use this script then, as always when running a script, please make sure you have a good backup of your company database (I define a good backup as one you have restored to ensure it backed up correctly) and know what you are doing in Management Studio;

/*
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)
   ,IVIVINDX VARCHAR(100)
   ,IVIVOFIX VARCHAR(100)
   ,IVCOGSIX VARCHAR(100)
   ,IVSLSIDX VARCHAR(100)
   ,IVSLRNIX VARCHAR(100)
   ,IVVARIDX VARCHAR(100)
   ,IVRETIDX VARCHAR(100)
   )
GO

BULK INSERT #StockCodes
FROM 'D:\Imports\StockCodes.csv'
WITH
   (
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
   )
GO

UPDATE
   IV
SET
   IVIVINDX = ['Account Index Master - Inventory'].ACTINDX
   ,IVIVOFIX = ['Account Index Master - Inventory Offset'].ACTINDX
   ,IVCOGSIX = ['Account Index Master - Cost Of Goods Sold'].ACTINDX
   ,IVSLSIDX = ['Account Index Master - Sales'].ACTINDX
   ,IVSLRNIX = ['Account Index Master - Sales Returns'].ACTINDX
   ,IVVARIDX = ['Account Index Master - Variance'].ACTINDX
   ,IVRETIDX = ['Account Index Master - Inventory Returns'].ACTINDX
FROM
   IV00101 IV
INNER JOIN
   #StockCodes AS Stock
       ON  Stock.ITEMNMBR = IV.ITEMNMBR
INNER JOIN
   GL00105 AS ['Account Index Master - Inventory']
       ON  ['Account Index Master - Inventory'].ACTNUMST = Stock.IVIVINDX
INNER JOIN
   GL00105 AS ['Account Index Master - Inventory Offset']
       ON  ['Account Index Master - Inventory Offset'].ACTNUMST = Stock.IVIVOFIX
INNER JOIN
   GL00105 AS ['Account Index Master - Cost Of Goods Sold']
       ON  ['Account Index Master - Cost Of Goods Sold'].ACTNUMST = Stock.IVCOGSIX
INNER JOIN
   GL00105 AS ['Account Index Master - Sales']
       ON  ['Account Index Master - Sales'].ACTNUMST = Stock.IVSLSIDX
INNER JOIN
   GL00105 AS ['Account Index Master - Sales Returns']
       ON  ['Account Index Master - Sales Returns'].ACTNUMST = Stock.IVSLRNIX
INNER JOIN
   GL00105 AS ['Account Index Master - Variance']
       ON  ['Account Index Master - Variance'].ACTNUMST = Stock.IVVARIDX
INNER JOIN
   GL00105 AS ['Account Index Master - Inventory Returns']
       ON  ['Account Index Master - Inventory Returns'].ACTNUMST = Stock.IVRETIDX
GO