This works with Dynamics GP 2013.

If you’ve ever tried to remove an inventory site from Microsoft Dynamics GP, you know it can be very painful to disassociate the inventory site from all the items with which it is associated. It can take 5 seconds to create associations that will take 5 hours to undo.

Here’s a quicker way.

The SQL script below will create a Dynamics GP macro. The macro disassociates the inventory site from each item with which it is associated, leaving you free to remove the inventory site. Note that all the quantities in the site must be zero, the site must not be on any stock count schedules, etc.

First, replace the inventory site name DRAIN below with the site name you wish to remove. Then execute this script against your company database.

-- The following relies on these substitutions:
--  __ITEMNMBR__
--  __LOCNCODE__

select top 2000000000 * -- Use SELECT TOP in case we want to turn this into a view with a sorted result set
from (

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  0 as SortOrder,
  '# DEXVERSION=12.00.0232.000 2 2' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  1 as SortOrder,
'  CommandExec dictionary ''default''  form ''Command_Inventory'' command ''IV_Item_Qtys_Locs'''
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  2 as SortOrder,
  'NewActiveWin dictionary ''default''  form ''IV_Item_Qtys_Locs'' window ''IV_Item_Qtys_Locs'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  3 as SortOrder,
  replace('  TypeTo field ''Item Number'' , ''__ITEMNMBR__''','__ITEMNMBR__',qty.ITEMNMBR) as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  4 as SortOrder,
  '  MoveTo field ''(L) Display Options'' item 0  # ''All'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  5 as SortOrder,
  '  ClickHit field ''(L) Display Options'' item 1  # ''Site ID:'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  6 as SortOrder,
  replace('  TypeTo field ''Location Code'' , ''__LOCNCODE__''','__LOCNCODE__',qty.LOCNCODE) as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  7 as SortOrder,
  '  MoveTo field ''(L) Location GB'' item 0  # ''All'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  8 as SortOrder,
  '  ClickHit field ''(L) Location GB'' item 1  # ''Assigned'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  9 as SortOrder,
  '  MoveTo field ''Delete Button'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  10 as SortOrder,
  '  ClickHit field ''Delete Button'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  11 as SortOrder,
  '# Are you sure you want to delete this record?' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  12 as SortOrder,
  'NewActiveWin dictionary ''default''  form DiaLog window DiaLog  ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  13 as SortOrder,
  'ClickHit field OK ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  14 as SortOrder,
'CloseWindow dictionary ''default''  form ''IV_Item_Qtys_Locs'' window ''IV_Item_Qtys_Locs'' ' as FinalMacroLine
from dbo.IV00102 qty

union

select
  qty.ITEMNMBR,
  qty.LOCNCODE,
  -1 as ORD,
  15 as SortOrder,
'NewActiveWin dictionary ''default''  form sheLL window sheLL ' as FinalMacroLine
from dbo.IV00102 qty
) qtys

-- The next 2 lines contain the name of an inventory site that we want to disassociate from all items
where qtys.ITEMNMBR in (SELECT ITEMNMBR FROM dbo.IV00102 WHERE LOCNCODE='DRAIN')
and qtys.LOCNCODE='DRAIN'

order by
  qtys.ITEMNMBR,
  qtys.LOCNCODE,
  qtys.ORD,
  qtys.SortOrder
;

You’ll get a result set with 5 columns. The fifth column is a GP macro. Copy and paste it into a file with the extension “.mac”.

Sample result set

Sample result set

Then run that file from GP as a macro, and watch hours of work be completed in minutes 😉 .

If there are any associations that can’t be removed, the macro will stop. You’ll usually get an error message with details of the problem. After you’ve fixed the problem, just remove the macro lines that have already executed, and re-start the macro where it left off.

Hope this helps someone else as much as it helped me!

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available