SQL Script to Assign All Items to ALL Sites with Dynamics GP
- by Ryan McBee
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
Normal
0
false
false
false
false
EN-US
X-NONE
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
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.
