SQL Script to Assign ALL GP Items to ALL Warehouses

By Tracey BrinkmanInventory, Microsoft Dynamics GP, , , With 0 comments

By Ryan McBee

When setting up new items within Microsoft Dynamics GP, you will often run into the error message below which reads “This site is not assigned to the selected item.  Do you want to assign this site?”

The fix is quite simple given that you simply click the Add button below which opens up the Item Quantities Maintenance window which you will hit the save button and proceed with the entry of your Sales Order or Purchase Order.

Microsoft Dynamics GP_Site

Item Quantities Maintenance

If you have a lot of new items into GP and have just one Site ID setup, the best approach to assigning your items to a particular site is by going to the Site Maintenance Window which is located inCards>>Inventory>>Site.  Once you are in the window below, you can click the Assign button to assign Items to the Site selected.

Site Maintenance

However, if you have you a lot of Sites and Items created, this can be quite a cumbersome and time consuming process.  For that, I have created the following SQL Script below that Assigns all Items to all Site ID’s within Microsoft Dynamics GP 2010.The script below works just for GP 2010 since the columns in the IV00102 have changed from version to version.  If you need it for prior versions, please email me and I will send it to you.

declare @item varchar(100)

, @loc varchar(100)

, @ItemExist int

DECLARE TablePositionCursor CURSOR FOR

SELECT itemnmbr FROM IV00101 i

OPEN TablePositionCursor

FETCH NEXT FROM TablePositionCursor INTO @item

WHILE (@@fetch_status <> -1)

BEGIN

DECLARE TablePositionCursor2 CURSOR FOR

SELECT locncode FROM IV40700

OPEN TablePositionCursor2

FETCH NEXT FROM TablePositionCursor2 INTO  @loc

WHILE (@@fetch_status <> -1)

BEGIN

SELECT @ItemExist = isnull(count(*), 0) FROM IV00102 WHERE ITEMNMBR = @item AND LOCNCODE = @loc

IF @ItemExist  = 0

BEGIN

INSERT INTO iv00102 VALUES(

@item

,@loc

,”

,2

,”

,0

,0

,0

,0

,’01/01/1900′

,”

,’01/01/1900′

,0

,0

,0

,0

,0

,0

,0

,0

,0

,0

,0

,0

,’01/01/1900′

,’01/01/1900′

,’01/01/1900′

,’01/01/1900′

,0

,”

,”

,”

,1

,0

,0

,1

,0

,0

,1

,2

,0

,0

,0

,0

,0

,0

,0

,1

,0

,0

,0

,3

,0

,0

,0

,”

,”

,”

,”

,”

,”

,”

,”

,1

,1

,”

,1

,1

,0

,1

,1

,1

,0

,0

,0

,0

,0

)

END

FETCH NEXT FROM TablePositionCursor2 INTO @loc

END

DEALLOCATE TablePositionCursor2

FETCH NEXT FROM TablePositionCursor INTO  @item

END

DEALLOCATE TablePositionCursor

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.  This blog is provided “AS IS” with no warranties, and confers no rights.