OVERVIEW:
Microsoft Dynamics GP doesn't give you the ability to assign 1 inventory item to every site. But you can do it using a SQL Server stored procedure if you have installed the eConnect stored procedures. See What is eConnect?
CODE:
CREATE PROCEDURE [dbo].[spAssignItemToAllSites]
(@item nvarchar(20))
AS
BEGIN -- Stored Proc
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @itemnmbr char(30)
DECLARE @Locncode nvarchar(15)
-- Error Handling Variables
DECLARE @O_iErrorState int
DECLARE @oErrString varchar(255)
DECLARE @ErrorState int
DECLARE @ErrString varchar(1000)
-- Open A cursor
DECLARE Item Cursor FOR
SELECT @item, locncode from iv40700
OPEN Item
-- populate the cursor with the first record from the header
Fetch NEXT FROM Item
INTO
@itemnmbr, @locncode
While (@@FETCH_STATUS=0)
BEGIN -- While Loop
-- Set Default starting conditions
SET @ErrString = ''
SET @ErrorState = 0
SET @O_iErrorState = 0
SET @oErrstring = ''
--Begin a sql transaction so we can undo everything if it doesn't work
BEGIN TRANSACTION
if @O_iErrorState = 0
begin --Execute econnect stored proc
EXECUTE taItemSite
@I_vItemnmbr = @itemnmbr,
@I_VLocncode = @locncode,
@O_iErrorState = @O_iErrorstate OUTPUT,
@oErrString = @oErrString OUTPUT
end
IF @O_iErrorState <> 0
Begin
SET @ErrorState = @O_iErrorState
SET @ErrString = @oErrstring
End
--Processing is done. Let's begin the error handling.
if @ErrorState <> 0
begin
ROLLBACK TRANSACTION
--note: this is option. If an error occurs, the error message will be inserted into a custom
--SQL table. Obviously, you must create this table yourself.
Insert into EconnectErrorLog (Item, ErrorState, ErrString)
values (@itemnmbr, @errorstate, @errstring)
end
ELSE
COMMIT TRANSACTION
--Code is done - loop to the next record in the cursor
Fetch NEXT FROM Item
INTO
@itemnmbr, @locncode
END -- While Loop
CLOSE Item
DEALLOCATE Item
END -- Stored Proc