Geeks With Blogs
Microsoft Dynamics GP (Great Plains) and CRM Implementations with Ryan McBee of iSolutions Partners Microsoft Dynamics GP (Great Plains), Management Reporter, eConnect, SQL Server 2016, Reporting Services

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.


 


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 in Cards>>Inventory>>Site.  Once you are in the window below, you can click the Assign button to assign Items to the Site selected.

 


 

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. 

 

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

 

 

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.

 

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.

Posted on Sunday, March 6, 2011 10:51 PM Dynamics GP | Back to top

Copyright © Ryan McBee | Powered by: GeeksWithBlogs.net